[Home] [Help]
PACKAGE BODY: APPS.CZ_IB_TRANSACTIONS
Source
1 PACKAGE BODY CZ_IB_TRANSACTIONS AS
2 /* $Header: czibtxb.pls 120.8.12010000.4 2008/11/27 11:40:35 kdande ship $ */
3
4 G_IB_TXN_STATUS_PROCESSED CONSTANT VARCHAR2(10) := 'PROCESSED';
5
6 TYPE cv_cursor_type IS REF CURSOR;
7
8 --
9 -- this method add log message to both CZ_DB_LOGS and FND LOG tables
10 --
11 PROCEDURE LOG_REPORT
12 (p_run_id IN VARCHAR2,
13 p_error_message IN VARCHAR2,
14 p_count IN NUMBER) IS
15 PRAGMA AUTONOMOUS_TRANSACTION;
16 BEGIN
17 IF (p_count IS NULL OR p_count<2) THEN
18 m_COUNTER:=m_COUNTER+1;
19 INSERT INTO CZ_DB_LOGS
20 (RUN_ID,
21 LOGTIME,
22 LOGUSER,
23 URGENCY,
24 CALLER,
25 STATUSCODE,
26 MESSAGE,
27 MESSAGE_ID)
28 VALUES (p_run_id,
29 SYSDATE,
30 USER,
31 1,
32 'CZ_IB_TRANSACTIONS',
33 11276,
34 p_error_message,
35 m_COUNTER);
36 COMMIT;
37
38 cz_utils.log_report('CZ_IB_TRANSACTIONS', null, m_COUNTER,
39 p_error_message, fnd_log.LEVEL_ERROR);
40
41 ELSE
42 FOR i IN 1..p_count
43 LOOP
44 m_COUNTER:=m_COUNTER+1;
45 INSERT INTO CZ_DB_LOGS
46 (RUN_ID,
47 LOGTIME,
48 LOGUSER,
49 URGENCY,
50 CALLER,
51 STATUSCODE,
52 MESSAGE,
53 MESSAGE_ID)
54 VALUES
55 (p_run_id,
56 SYSDATE,
57 USER,
58 1,
59 'CZ_IB_TRANSACTIONS',
60 11276,
61 fnd_msg_pub.GET(i,fnd_api.g_false),
62 m_COUNTER);
63 COMMIT;
64
65 cz_utils.log_report('CZ_IB_TRANSACTIONS', null, m_COUNTER,
66 fnd_msg_pub.GET(i,fnd_api.g_false), fnd_log.LEVEL_ERROR);
67 END LOOP;
68
69 END IF;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 NULL;
74 END LOG_REPORT;
75
76 PROCEDURE LOG_REPORT
77 (p_run_id IN VARCHAR2,
78 p_error_message IN VARCHAR2) IS
79 BEGIN
80 LOG_REPORT
81 (p_run_id => p_run_id,
82 p_error_message => p_error_message,
83 p_count => NULL);
84 END LOG_REPORT;
85
86 --
87 -- DEBUG methods
88 --
89 PROCEDURE DEBUG(p_message IN VARCHAR2) IS
90 BEGIN
91 IF debug_mode = DEBUG_OUTPUT THEN
92 --DBMS_OUTPUT.PUT_LINE(p_message);
93 NULL;
94 ELSE
95 LOG_REPORT(m_RUN_ID,p_message);
96 END IF;
97 END DEBUG;
98
99 PROCEDURE DEBUG(p_var_name IN VARCHAR2,p_var_value IN VARCHAR2) IS
100 BEGIN
101 DEBUG(p_var_name||' = '||p_var_value);
102 END DEBUG;
103
104 PROCEDURE DEBUG(p_var_name IN VARCHAR2,p_var_value IN NUMBER) IS
105 BEGIN
106 DEBUG(p_var_name||' = '||TO_CHAR(p_var_value));
107 END DEBUG;
108
109 --
110 -- method to initialize data
111 --
112 PROCEDURE Initialize
113 (
114 p_effective_date IN DATE, -- DEFAULT SYSDATE
115 p_init_fnd IN VARCHAR2 DEFAULT NULL
116 ) IS
117
118 BEGIN
119 IF p_init_fnd IS NULL THEN
120 fnd_msg_pub.initialize;
121 END IF;
122 m_COUNTER:=0; m_CZ_IB_AUTO_EXPIRATION:='Y';
123
124 --
125 -- SYSDATE wull be used for enddating
126 --
127 m_EFFECTIVE_DATE := SYSDATE;
128
129 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO m_RUN_ID FROM dual;
130
131 BEGIN
132 SELECT VALUE INTO DEBUG_MODE FROM CZ_DB_SETTINGS
133 WHERE UPPER(SETTING_ID)='CZ_IB_DEBUG_MODE' AND ROWNUM<2;
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 NULL;
137 END;
138
139 BEGIN
140 m_CZ_IB_AUTO_EXPIRATION:=fnd_profile.VALUE('CZ_IB_AUTO_EXPIRATION');
141
142 IF (m_CZ_IB_AUTO_EXPIRATION IS NULL OR UPPER(m_CZ_IB_AUTO_EXPIRATION) NOT IN('Y','N','YES','NO'))
143 OR UPPER(m_CZ_IB_AUTO_EXPIRATION) IN ('Y','YES') THEN
144 m_CZ_IB_AUTO_EXPIRATION:='Y';
145 END IF;
146 EXCEPTION
147 WHEN OTHERS THEN
148 m_CZ_IB_AUTO_EXPIRATION:='Y';
149 LOG_REPORT(-m_RUN_ID,'Error in getting value of profile "CZ_IB_AUTO_EXPIRATION"');
150 END;
151
152 END Initialize;
153
154 /* *** Probe function to check out CSI *** */
155 FUNCTION CSI_Exists RETURN BOOLEAN IS
156 BEGIN
157 EXECUTE IMMEDIATE 'SELECT config_session_hdr_id FROM CSI_T_TRANSACTION_LINES WHERE rownum<2';
158 RETURN TRUE;
159 EXCEPTION
160 WHEN OTHERS THEN
161 RETURN FALSE;
162 END CSI_Exists;
163
164 PROCEDURE delete_transaction_dtls
165 (
166 p_api_version IN NUMBER
167 ,p_commit IN VARCHAR2
168 ,p_init_msg_list IN VARCHAR2
169 ,p_validation_level IN NUMBER
170 ,p_transaction_line_id IN NUMBER
171 ,p_api_caller_identity IN VARCHAR2
172 ,x_return_status OUT NOCOPY VARCHAR2
173 ,x_msg_count OUT NOCOPY NUMBER
174 ,x_msg_data OUT NOCOPY VARCHAR2
175 ) IS
176
177 BEGIN
178 EXECUTE IMMEDIATE
179 'BEGIN ' ||
180 ' csi_t_txn_details_grp.delete_transaction_dtls ' ||
181 ' ( ' ||
182 ' p_api_version => :1 ' ||
183 ' ,p_commit => :2 ' ||
184 ' ,p_init_msg_list => :3 ' ||
185 ' ,p_validation_level => :4 ' ||
186 ' ,p_api_caller_identity => :5 ' ||
187 ' ,p_transaction_line_id => :6 ' ||
188 ' ,x_return_status => CZ_IB_TRANSACTIONS.m_return_status ' ||
189 ' ,x_msg_count => CZ_IB_TRANSACTIONS.m_msg_count ' ||
190 ' ,x_msg_data => CZ_IB_TRANSACTIONS.m_msg_data ' ||
191 ' ); ' ||
192 ' END;' USING p_api_version,p_commit,p_init_msg_list,p_validation_level,
193 p_api_caller_identity,p_transaction_line_id;
194
195 x_return_status := CZ_IB_TRANSACTIONS.m_return_status;
196 x_msg_count := CZ_IB_TRANSACTIONS.m_msg_count;
197 x_msg_data := CZ_IB_TRANSACTIONS.m_msg_data;
198 EXCEPTION
199 WHEN OTHERS THEN
200 x_return_status := FND_API.g_ret_sts_unexp_error;
201 x_msg_count := 1;
202 x_msg_data := 'CZ_IB_TRANSACTIONS.delete_transaction_dtls : '||SQLERRM;
203 LOG_REPORT(m_RUN_ID,x_msg_data);
204 END delete_transaction_dtls;
205
206
207 PROCEDURE create_transaction_dtls(
208 p_api_version IN NUMBER,
209 p_commit IN VARCHAR2 := fnd_api.g_false,
210 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
211 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
212 px_txn_line_rec IN OUT NOCOPY txn_line_rec ,
213 px_txn_line_detail_tbl IN OUT NOCOPY txn_line_detail_tbl,
214 px_txn_party_detail_tbl IN OUT NOCOPY txn_party_detail_tbl ,
215 px_txn_pty_acct_detail_tbl IN OUT NOCOPY txn_pty_acct_detail_tbl,
216 px_txn_ii_rltns_tbl IN OUT NOCOPY txn_ii_rltns_tbl,
217 px_txn_org_assgn_tbl IN OUT NOCOPY txn_org_assgn_tbl,
218 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY txn_ext_attrib_vals_tbl,
219 px_txn_systems_tbl IN OUT NOCOPY txn_systems_tbl,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2) IS
223
224 BEGIN
225 m_txn_line_rec := px_txn_line_rec;
226 m_txn_line_detail_tbl.DELETE;
227 m_txn_line_detail_tbl := px_txn_line_detail_tbl ;
228 m_txn_ext_attrib_vals_tbl.DELETE;
229 m_txn_ext_attrib_vals_tbl := px_txn_ext_attrib_vals_tbl;
230
231 EXECUTE IMMEDIATE
232 ' DECLARE ' ||
233 ' t_txn_line_rec csi_t_datastructures_grp.txn_line_rec; ' ||
234 ' t_txn_line_detail_tbl csi_t_datastructures_grp.txn_line_detail_tbl; ' ||
235 ' t_txn_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl; ' ||
236 ' t_txn_pty_acct_detail_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl; ' ||
237 ' t_txn_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl; ' ||
238 ' t_txn_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl; ' ||
239 ' t_txn_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl; ' ||
240 ' t_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl; ' ||
241 ' BEGIN ' ||
242 ' t_txn_line_rec.TRANSACTION_LINE_ID := CZ_IB_TRANSACTIONS.m_txn_line_rec.TRANSACTION_LINE_ID; ' ||
243 ' t_txn_line_rec.SOURCE_TRANSACTION_TYPE_ID := CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_TYPE_ID; ' ||
244 ' t_txn_line_rec.SOURCE_TRANSACTION_ID := CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_ID; ' ||
245 ' t_txn_line_rec.SOURCE_TXN_HEADER_ID := CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TXN_HEADER_ID; ' ||
246 ' t_txn_line_rec.SOURCE_TRANSACTION_TABLE := CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_TABLE; ' ||
247 ' t_txn_line_rec.CONFIG_SESSION_HDR_ID := CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_SESSION_HDR_ID; ' ||
248 ' t_txn_line_rec.CONFIG_SESSION_REV_NUM := CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_SESSION_REV_NUM; ' ||
249 ' t_txn_line_rec.CONFIG_SESSION_ITEM_ID := CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_SESSION_ITEM_ID; ' ||
250 ' t_txn_line_rec.CONFIG_VALID_STATUS := CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_VALID_STATUS; ' ||
251 ' t_txn_line_rec.SOURCE_TRANSACTION_STATUS := CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_STATUS; ' ||
252 ' t_txn_line_rec.API_CALLER_IDENTITY := CZ_IB_TRANSACTIONS.m_txn_line_rec.API_CALLER_IDENTITY; ' ||
253 ' t_txn_line_rec.ERROR_CODE := CZ_IB_TRANSACTIONS.m_txn_line_rec.ERROR_CODE; ' ||
254 ' t_txn_line_rec.ERROR_EXPLANATION := CZ_IB_TRANSACTIONS.m_txn_line_rec.ERROR_EXPLANATION; ' ||
255 ' t_txn_line_rec.PROCESSING_STATUS := CZ_IB_TRANSACTIONS.m_txn_line_rec.PROCESSING_STATUS; ' ||
256 ' t_txn_line_rec.OBJECT_VERSION_NUMBER := CZ_IB_TRANSACTIONS.m_txn_line_rec.OBJECT_VERSION_NUMBER; ' ||
257 ' IF CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl.COUNT > 0 THEN ' ||
258 ' FOR i IN CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl.FIRST..CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl.LAST ' ||
259 ' LOOP ' ||
260 ' t_txn_line_detail_tbl(i).config_inst_hdr_id := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).config_inst_hdr_id; ' ||
261 ' t_txn_line_detail_tbl(i).config_inst_rev_num := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).config_inst_rev_num; ' ||
262 ' t_txn_line_detail_tbl(i).config_inst_item_id := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).config_inst_item_id; ' ||
263 ' t_txn_line_detail_tbl(i).source_transaction_flag:= CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).source_transaction_flag; ' ||
264 ' t_txn_line_detail_tbl(i).instance_exists_flag := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).instance_exists_flag; ' ||
265 ' t_txn_line_detail_tbl(i).quantity := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).quantity; ' ||
266 ' t_txn_line_detail_tbl(i).unit_of_measure := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).unit_of_measure; ' ||
267 ' t_txn_line_detail_tbl(i).location_id := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).location_id; ' ||
268 ' t_txn_line_detail_tbl(i).location_type_code := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).location_type_code; ' ||
269 ' t_txn_line_detail_tbl(i).inventory_item_id := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).inventory_item_id; ' ||
270 ' t_txn_line_detail_tbl(i).inv_organization_id := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).inv_organization_id; ' ||
271 ' t_txn_line_detail_tbl(i).mfg_serial_number_flag := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).mfg_serial_number_flag; ' ||
272 ' t_txn_line_detail_tbl(i).sub_type_id := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).sub_type_id; ' ||
273 ' t_txn_line_detail_tbl(i).instance_description := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).instance_description; ' ||
274 ' t_txn_line_detail_tbl(i).config_inst_baseline_rev_num := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).config_inst_baseline_rev_num; ' ||
275 ' t_txn_line_detail_tbl(i).active_end_date := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).active_end_date; ' ||
276 ' t_txn_line_detail_tbl(i).object_version_number := CZ_IB_TRANSACTIONS.m_txn_line_detail_tbl(i).object_version_number; ' ||
277 ' END LOOP; ' ||
278 ' END IF; ' ||
279 ' IF CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl.COUNT > 0 THEN ' ||
280 ' FOR i IN CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl.FIRST..CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl.LAST ' ||
281 ' LOOP ' ||
282 ' t_txn_ext_attrib_vals_tbl(i).TXN_ATTRIB_DETAIL_ID := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).TXN_ATTRIB_DETAIL_ID; ' ||
283 ' t_txn_ext_attrib_vals_tbl(i).TXN_LINE_DETAIL_ID := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).TXN_LINE_DETAIL_ID; ' ||
284 ' t_txn_ext_attrib_vals_tbl(i).ATTRIB_SOURCE_TABLE := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).ATTRIB_SOURCE_TABLE; ' ||
285 ' t_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_SOURCE_ID := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_SOURCE_ID; ' ||
286 ' t_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_VALUE := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_VALUE; ' ||
287 ' t_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_CODE := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_CODE; ' ||
288 ' t_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_LEVEL := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).ATTRIBUTE_LEVEL; ' ||
289 ' t_txn_ext_attrib_vals_tbl(i).API_CALLER_IDENTITY := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).API_CALLER_IDENTITY; ' ||
290 ' t_txn_ext_attrib_vals_tbl(i).PROCESS_FLAG := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).PROCESS_FLAG; ' ||
291 ' t_txn_ext_attrib_vals_tbl(i).ACTIVE_START_DATE := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).ACTIVE_START_DATE; ' ||
292 ' t_txn_ext_attrib_vals_tbl(i).ACTIVE_END_DATE := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).ACTIVE_END_DATE; ' ||
293 ' t_txn_ext_attrib_vals_tbl(i).PRESERVE_DETAIL_FLAG := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).PRESERVE_DETAIL_FLAG; ' ||
294 ' t_txn_ext_attrib_vals_tbl(i).CONTEXT := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).CONTEXT; ' ||
295 ' t_txn_ext_attrib_vals_tbl(i).TXN_LINE_DETAILS_INDEX := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).TXN_LINE_DETAILS_INDEX; ' ||
296 ' t_txn_ext_attrib_vals_tbl(i).OBJECT_VERSION_NUMBER := CZ_IB_TRANSACTIONS.m_txn_ext_attrib_vals_tbl(i).OBJECT_VERSION_NUMBER; ' ||
297 ' END LOOP; ' ||
298 ' END IF; ' ||
299 ' csi_t_txn_details_grp.create_transaction_dtls ' ||
300 ' ( ' ||
301 ' p_api_version => :1, ' ||
302 ' p_commit => :2, ' ||
303 ' p_init_msg_list => :3, ' ||
304 ' p_validation_level => :4, ' ||
305 ' px_txn_line_rec => t_txn_line_rec, ' ||
306 ' px_txn_line_detail_tbl => t_txn_line_detail_tbl, ' ||
307 ' px_txn_party_detail_tbl => t_txn_party_detail_tbl , ' ||
308 ' px_txn_pty_acct_detail_tbl => t_txn_pty_acct_detail_tbl, ' ||
309 ' px_txn_ii_rltns_tbl => t_txn_ii_rltns_tbl, ' ||
310 ' px_txn_org_assgn_tbl => t_txn_org_assgn_tbl, ' ||
314 ' x_msg_count => CZ_IB_TRANSACTIONS.m_msg_count, ' ||
311 ' px_txn_ext_attrib_vals_tbl => t_txn_ext_attrib_vals_tbl, ' ||
312 ' px_txn_systems_tbl => t_txn_systems_tbl, ' ||
313 ' x_return_status => CZ_IB_TRANSACTIONS.m_return_status, ' ||
315 ' x_msg_data => CZ_IB_TRANSACTIONS.m_msg_data ' ||
316 ' ); ' ||
317 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.TRANSACTION_LINE_ID := t_txn_line_rec.TRANSACTION_LINE_ID; ' ||
318 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_TYPE_ID := t_txn_line_rec.SOURCE_TRANSACTION_TYPE_ID; ' ||
319 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_ID := t_txn_line_rec.SOURCE_TRANSACTION_ID; ' ||
320 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TXN_HEADER_ID := t_txn_line_rec.SOURCE_TXN_HEADER_ID; ' ||
321 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_TABLE := t_txn_line_rec.SOURCE_TRANSACTION_TABLE; ' ||
322 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_SESSION_HDR_ID := t_txn_line_rec.CONFIG_SESSION_HDR_ID; ' ||
323 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_SESSION_REV_NUM := t_txn_line_rec.CONFIG_SESSION_REV_NUM; ' ||
324 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_SESSION_ITEM_ID := t_txn_line_rec.CONFIG_SESSION_ITEM_ID; ' ||
325 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.CONFIG_VALID_STATUS := t_txn_line_rec.CONFIG_VALID_STATUS; ' ||
326 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.SOURCE_TRANSACTION_STATUS := t_txn_line_rec.SOURCE_TRANSACTION_STATUS; ' ||
327 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.API_CALLER_IDENTITY := t_txn_line_rec.API_CALLER_IDENTITY; ' ||
328 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.ERROR_CODE := t_txn_line_rec.ERROR_CODE; ' ||
329 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.ERROR_EXPLANATION := t_txn_line_rec.ERROR_EXPLANATION; ' ||
330 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.PROCESSING_STATUS := t_txn_line_rec.PROCESSING_STATUS; ' ||
331 ' CZ_IB_TRANSACTIONS.m_txn_line_rec.OBJECT_VERSION_NUMBER := t_txn_line_rec.OBJECT_VERSION_NUMBER; ' ||
332 ' END;' USING p_api_version,p_commit,p_init_msg_list,p_validation_level;
333
334 px_txn_line_rec:=m_txn_line_rec;
335 px_txn_line_detail_tbl:=m_txn_line_detail_tbl;
336 px_txn_ext_attrib_vals_tbl:=m_txn_ext_attrib_vals_tbl;
337
338 m_txn_line_detail_tbl.DELETE;
339 m_txn_ext_attrib_vals_tbl.DELETE;
340
341 x_return_status := CZ_IB_TRANSACTIONS.m_return_status;
342 x_msg_count := CZ_IB_TRANSACTIONS.m_msg_count;
343 x_msg_data := CZ_IB_TRANSACTIONS.m_msg_data;
344 EXCEPTION
345 WHEN OTHERS THEN
346 x_return_status := FND_API.g_ret_sts_unexp_error;
347 x_msg_count := 1;
348 x_msg_data := 'CZ_IB_TRANSACTIONS.create_transaction_dtls : '||SQLERRM;
349 LOG_REPORT(m_RUN_ID,x_msg_data);
350 END create_transaction_dtls;
351
352
353 PROCEDURE create_txn_ii_rltns_dtls(
354 p_api_version IN NUMBER,
355 p_commit IN VARCHAR2 := fnd_api.g_false,
356 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
357 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
358 px_txn_ii_rltns_tbl IN OUT NOCOPY txn_ii_rltns_tbl,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2) IS
362
363 BEGIN
364 m_txn_ii_rltns_tbl.DELETE;
365 m_txn_ii_rltns_tbl:=px_txn_ii_rltns_tbl;
366
367 EXECUTE IMMEDIATE
368 ' DECLARE ' ||
369 ' t_txn_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl; ' ||
370 ' BEGIN ' ||
371 ' IF CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl.COUNT>0 THEN ' ||
372 ' FOR i IN CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl.FIRST..CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl.LAST ' ||
373 ' LOOP ' ||
374 ' t_txn_ii_rltns_tbl(i).TXN_RELATIONSHIP_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).TXN_RELATIONSHIP_ID; ' ||
375 ' t_txn_ii_rltns_tbl(i).TRANSACTION_LINE_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).TRANSACTION_LINE_ID; ' ||
376 ' t_txn_ii_rltns_tbl(i).CSI_INST_RELATIONSHIP_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).CSI_INST_RELATIONSHIP_ID; ' ||
377 ' t_txn_ii_rltns_tbl(i).SUBJECT_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUBJECT_ID; ' ||
378 ' t_txn_ii_rltns_tbl(i).SUBJECT_INDEX_FLAG := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUBJECT_INDEX_FLAG; ' ||
379 ' t_txn_ii_rltns_tbl(i).SUBJECT_TYPE := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUBJECT_TYPE; ' ||
380 ' t_txn_ii_rltns_tbl(i).OBJECT_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_ID; ' ||
381 ' t_txn_ii_rltns_tbl(i).OBJECT_INDEX_FLAG := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_INDEX_FLAG; ' ||
382 ' t_txn_ii_rltns_tbl(i).OBJECT_TYPE := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_TYPE; ' ||
383 ' t_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_HDR_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_HDR_ID; ' ||
384 ' t_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_REV_NUM := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_REV_NUM; ' ||
385 ' t_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_ITEM_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_ITEM_ID; ' ||
386 ' t_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_HDR_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_HDR_ID; ' ||
390 ' t_txn_ii_rltns_tbl(i).API_CALLER_IDENTITY := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).API_CALLER_IDENTITY; ' ||
387 ' t_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_REV_NUM := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_REV_NUM; ' ||
388 ' t_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_ITEM_ID := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_ITEM_ID; ' ||
389 ' t_txn_ii_rltns_tbl(i).TARGET_COMMITMENT_DATE := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).TARGET_COMMITMENT_DATE; ' ||
391 ' t_txn_ii_rltns_tbl(i).RELATIONSHIP_TYPE_CODE := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).RELATIONSHIP_TYPE_CODE; ' ||
392 ' t_txn_ii_rltns_tbl(i).DISPLAY_ORDER := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).DISPLAY_ORDER; ' ||
393 ' t_txn_ii_rltns_tbl(i).POSITION_REFERENCE := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).POSITION_REFERENCE; ' ||
394 ' t_txn_ii_rltns_tbl(i).MANDATORY_FLAG := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).MANDATORY_FLAG; ' ||
395 ' t_txn_ii_rltns_tbl(i).ACTIVE_START_DATE := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).ACTIVE_START_DATE; ' ||
396 ' t_txn_ii_rltns_tbl(i).ACTIVE_END_DATE := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).ACTIVE_END_DATE; ' ||
397 ' t_txn_ii_rltns_tbl(i).CONTEXT := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).CONTEXT; ' ||
398 ' t_txn_ii_rltns_tbl(i).OBJECT_VERSION_NUMBER := CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_VERSION_NUMBER; ' ||
399 ' END LOOP; ' ||
400 ' END IF; ' ||
401 ' csi_t_txn_rltnshps_grp.create_txn_ii_rltns_dtls ' ||
402 ' ( ' ||
403 ' p_api_version => :1, ' ||
404 ' p_commit => :2, ' ||
405 ' p_init_msg_list => :3, ' ||
406 ' p_validation_level => :4, ' ||
407 ' px_txn_ii_rltns_tbl => t_txn_ii_rltns_tbl, ' ||
408 ' x_return_status => CZ_IB_TRANSACTIONS.m_return_status, ' ||
409 ' x_msg_count => CZ_IB_TRANSACTIONS.m_msg_count, ' ||
410 ' x_msg_data => CZ_IB_TRANSACTIONS.m_msg_data ' ||
411 ' ); ' ||
412 ' IF t_txn_ii_rltns_tbl.COUNT>0 THEN ' ||
413 ' FOR i IN t_txn_ii_rltns_tbl.FIRST..t_txn_ii_rltns_tbl.LAST ' ||
414 ' LOOP ' ||
415 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).TXN_RELATIONSHIP_ID := t_txn_ii_rltns_tbl(i).TXN_RELATIONSHIP_ID; ' ||
416 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).TRANSACTION_LINE_ID := t_txn_ii_rltns_tbl(i).TRANSACTION_LINE_ID; ' ||
417 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).CSI_INST_RELATIONSHIP_ID := t_txn_ii_rltns_tbl(i).CSI_INST_RELATIONSHIP_ID; ' ||
418 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUBJECT_ID := t_txn_ii_rltns_tbl(i).SUBJECT_ID; ' ||
419 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUBJECT_INDEX_FLAG := t_txn_ii_rltns_tbl(i).SUBJECT_INDEX_FLAG; ' ||
420 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUBJECT_TYPE := t_txn_ii_rltns_tbl(i).SUBJECT_TYPE; ' ||
421 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_ID := t_txn_ii_rltns_tbl(i).OBJECT_ID; ' ||
422 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_INDEX_FLAG := t_txn_ii_rltns_tbl(i).OBJECT_INDEX_FLAG; ' ||
423 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_TYPE := t_txn_ii_rltns_tbl(i).OBJECT_TYPE; ' ||
424 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_HDR_ID := t_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_HDR_ID; ' ||
425 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_REV_NUM := t_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_REV_NUM; ' ||
426 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_ITEM_ID := t_txn_ii_rltns_tbl(i).SUB_CONFIG_INST_ITEM_ID; ' ||
427 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_HDR_ID := t_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_HDR_ID; ' ||
428 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_REV_NUM := t_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_REV_NUM; ' ||
429 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_ITEM_ID := t_txn_ii_rltns_tbl(i).OBJ_CONFIG_INST_ITEM_ID; ' ||
430 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).TARGET_COMMITMENT_DATE := t_txn_ii_rltns_tbl(i).TARGET_COMMITMENT_DATE; ' ||
431 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).API_CALLER_IDENTITY := t_txn_ii_rltns_tbl(i).API_CALLER_IDENTITY; ' ||
432 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).RELATIONSHIP_TYPE_CODE := t_txn_ii_rltns_tbl(i).RELATIONSHIP_TYPE_CODE; ' ||
433 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).DISPLAY_ORDER := t_txn_ii_rltns_tbl(i).DISPLAY_ORDER; ' ||
434 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).POSITION_REFERENCE := t_txn_ii_rltns_tbl(i).POSITION_REFERENCE; ' ||
435 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).MANDATORY_FLAG := t_txn_ii_rltns_tbl(i).MANDATORY_FLAG; ' ||
436 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).ACTIVE_START_DATE := t_txn_ii_rltns_tbl(i).ACTIVE_START_DATE; ' ||
437 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).ACTIVE_END_DATE := t_txn_ii_rltns_tbl(i).ACTIVE_END_DATE; ' ||
438 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).CONTEXT := t_txn_ii_rltns_tbl(i).CONTEXT; ' ||
439 ' CZ_IB_TRANSACTIONS.m_txn_ii_rltns_tbl(i).OBJECT_VERSION_NUMBER := t_txn_ii_rltns_tbl(i).OBJECT_VERSION_NUMBER; ' ||
440 ' END LOOP; ' ||
441 ' END IF; ' ||
442 ' END;' USING p_api_version,p_commit,p_init_msg_list,p_validation_level;
443
444 px_txn_ii_rltns_tbl:=m_txn_ii_rltns_tbl;
445 m_txn_ii_rltns_tbl.DELETE;
446
447 x_return_status := CZ_IB_TRANSACTIONS.m_return_status;
451 EXCEPTION
448 x_msg_count := CZ_IB_TRANSACTIONS.m_msg_count;
449 x_msg_data := CZ_IB_TRANSACTIONS.m_msg_data;
450
452 WHEN OTHERS THEN
453 x_return_status := FND_API.g_ret_sts_unexp_error;
454 x_msg_count := 1;
455 x_msg_data := 'CZ_IB_TRANSACTIONS.create_txn_ii_rltns_dtls : '||SQLERRM;
456 LOG_REPORT(m_RUN_ID,x_msg_data);
457 END create_txn_ii_rltns_dtls;
458
459
460 PROCEDURE get_connected_configurations
461 (
462 p_config_query_table IN config_query_table,
463 p_instance_level IN VARCHAR2,
464 x_config_pair_table OUT NOCOPY config_pair_table,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_return_message OUT NOCOPY VARCHAR2
467 ) IS
468
469 l_ndebug NUMBER := 0;
470
471 BEGIN
472
473 m_config_query_table:=p_config_query_table;
474 EXECUTE IMMEDIATE
475 ' DECLARE ' ||
476 ' t_config_query_table CSI_CZ_INT.config_query_table; ' ||
477 ' t_config_pair_table CSI_CZ_INT.config_pair_table; ' ||
478 ' l_ndebug NUMBER := 0; ' ||
479 ' BEGIN ' ||
480 ' IF CZ_IB_TRANSACTIONS.m_config_query_table.COUNT > 0 THEN ' ||
481 ' FOR i IN CZ_IB_TRANSACTIONS.m_config_query_table.FIRST..CZ_IB_TRANSACTIONS.m_config_query_table.LAST ' ||
482 ' LOOP ' ||
483 ' t_config_query_table(i).config_header_id := CZ_IB_TRANSACTIONS.m_config_query_table(i).config_header_id; ' ||
484 ' t_config_query_table(i).config_revision_number := CZ_IB_TRANSACTIONS.m_config_query_table(i).config_revision_number; ' ||
485 ' IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN ' ||
486 ' cz_utils.log_report(''CZ_IB_TRANSACTIONS'', ''get_connected_configurations'', l_ndebug, ' ||
487 ' ''csi_cz_int.get_connected_configurations() : parameters p_config_query_table(''||TO_CHAR(i)|| ' ||
488 ' '').config_header_id = ''||TO_CHAR(t_config_query_table(i).config_header_id)|| ' ||
489 ' '' p_config_query_table(''||TO_CHAR(i)|| ' ||
490 ' '').config_revision_number = ''||TO_CHAR(t_config_query_table(i).config_revision_number)|| ' ||
491 ' '' : current time : ''||TO_CHAR(SYSDATE,''DD-MM-YYYY HH24-MI-SS''), ' ||
492 ' fnd_log.LEVEL_STATEMENT); ' ||
493 ' END IF; ' ||
494 ' END LOOP; ' ||
495 ' END IF; ' ||
496 ' IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN ' ||
497 ' cz_utils.log_report(''CZ_IB_TRANSACTIONS'', ''get_connected_configurations'', l_ndebug, ' ||
498 ' ''csi_cz_int.get_connected_configurations() will be called : current time : ''||TO_CHAR(SYSDATE,''DD-MM-YYYY HH24-MI-SS''), ' ||
499 ' fnd_log.LEVEL_STATEMENT); ' ||
500 ' END IF; ' ||
501 ' csi_cz_int.get_connected_configurations ' ||
502 ' ( ' ||
503 ' p_config_query_table => t_config_query_table, ' ||
504 ' p_instance_level => :1, ' ||
505 ' x_config_pair_table => t_config_pair_table, ' ||
506 ' x_return_status => CZ_IB_TRANSACTIONS.m_return_status, ' ||
507 ' x_return_message => CZ_IB_TRANSACTIONS.m_return_message ' ||
508 ' ); ' ||
509 ' IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN ' ||
510 ' cz_utils.log_report(''CZ_IB_TRANSACTIONS'', ''get_connected_configurations'', l_ndebug, ' ||
511 ' ''csi_cz_int.get_connected_configurations() has been called : current time : ''||TO_CHAR(SYSDATE,''DD-MM-YYYY HH24-MI-SS''), ' ||
512 ' fnd_log.LEVEL_STATEMENT); ' ||
513 ' END IF; ' ||
514 ' IF t_config_pair_table.COUNT > 0 THEN ' ||
515 ' FOR i IN t_config_pair_table.FIRST..t_config_pair_table.LAST ' ||
516 ' LOOP ' ||
517 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).subject_header_id := t_config_pair_table(i).subject_header_id; ' ||
518 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).subject_revision_number:= t_config_pair_table(i).subject_revision_number; ' ||
519 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).subject_item_id := t_config_pair_table(i).subject_item_id; ' ||
520 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).object_header_id := t_config_pair_table(i).object_header_id; ' ||
521 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).object_revision_number := t_config_pair_table(i).object_revision_number; ' ||
522 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).object_item_id := t_config_pair_table(i).object_item_id; ' ||
523 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).root_header_id := t_config_pair_table(i).root_header_id; ' ||
524 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).root_revision_number := t_config_pair_table(i).root_revision_number; ' ||
525 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).root_item_id := t_config_pair_table(i).root_item_id; ' ||
526 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).SOURCE_APPLICATION_ID := t_config_pair_table(i).SOURCE_APPLICATION_ID; ' ||
527 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).SOURCE_TXN_HEADER_REF := t_config_pair_table(i).SOURCE_TXN_HEADER_REF; ' ||
531 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).LOCK_ID := t_config_pair_table(i).LOCK_ID; ' ||
528 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).SOURCE_TXN_LINE_REF1 := t_config_pair_table(i).SOURCE_TXN_LINE_REF1; ' ||
529 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).SOURCE_TXN_LINE_REF2 := t_config_pair_table(i).SOURCE_TXN_LINE_REF2; ' ||
530 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).SOURCE_TXN_LINE_REF3 := t_config_pair_table(i).SOURCE_TXN_LINE_REF3; ' ||
532 ' CZ_IB_TRANSACTIONS.m_config_pair_table(i).LOCK_STATUS := t_config_pair_table(i).LOCK_STATUS; ' ||
533 ' IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN ' ||
534 ' cz_utils.log_report(''CZ_IB_TRANSACTIONS'', ''get_connected_configurations'', l_ndebug, ' ||
535 ' ''Out array for get_connected_configurations() has been populated : current time : ''||TO_CHAR(SYSDATE,''DD-MM-YYYY HH24-MI-SS''), ' ||
536 ' fnd_log.LEVEL_STATEMENT); ' ||
537 ' END IF; ' ||
538 ' END LOOP; ' ||
539 ' END IF; ' ||
540 ' END;' USING p_instance_level;
541
542 x_config_pair_table := m_config_pair_table;
543 m_config_pair_table.DELETE;
544
545 x_return_status := CZ_IB_TRANSACTIONS.m_return_status;
546 x_return_message := CZ_IB_TRANSACTIONS.m_return_message;
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 x_return_status := FND_API.g_ret_sts_unexp_error;
551 x_return_message := 'CZ_IB_TRANSACTIONS.Get_Connected_Configurations : '||SQLERRM;
552
553 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
554 cz_utils.log_report('CZ_IB_TRANSACTIONS', 'get_connected_configurations', l_ndebug,
555 'Fatal error : '||SQLERRM||' : current time : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24-MI-SS'),
556 fnd_log.LEVEL_ERROR);
557 END IF;
558
559 LOG_REPORT(m_RUN_ID,x_return_message);
560 END get_connected_configurations;
561
562 PROCEDURE get_configuration_revision
563 (
564 p_config_header_id IN NUMBER,
565 p_target_commitment_date IN DATE,
566 px_instance_level IN OUT NOCOPY VARCHAR2,
567 x_config_rev_number OUT NOCOPY NUMBER,
568 x_config_rec OUT NOCOPY config_rec,
569 x_return_status OUT NOCOPY VARCHAR2,
570 x_return_message OUT NOCOPY VARCHAR2
571 ) IS
572
573 l_ndebug NUMBER := 1;
574
575 BEGIN
576
577 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 cz_utils.log_report('CZ_IB_TRANSACTIONS', 'get_configuration_revision', l_ndebug,
579 'csi_cz_int.get_configuration_revision() parameters : p_config_header_id='||TO_CHAR(p_config_header_id)||
580 ' p_target_commitment_date='||TO_CHAR(p_target_commitment_date,'DD-MM-YYYY HH24-MI-SS')||
581 ' px_instance_level='||px_instance_level||
582 ' : current time : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24-MI-SS'),
583 fnd_log.LEVEL_STATEMENT);
584 END IF;
585
586 EXECUTE IMMEDIATE
587 ' DECLARE ' ||
588 ' v_instance_level VARCHAR2(255):='''||px_instance_level||'''; ' ||
589 ' l_config_rec CSI_CZ_INT.config_rec; ' ||
590 ' l_ndebug NUMBER := 1; ' ||
591 ' BEGIN ' ||
592 ' IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN ' ||
593 ' cz_utils.log_report(''CZ_IB_TRANSACTIONS'', ''get_configuration_revision'', l_ndebug, ' ||
594 ' ''csi_cz_int.get_configuration_revision() will be called : current time : ''||TO_CHAR(SYSDATE,''DD-MM-YYYY HH24-MI-SS''), ' ||
595 ' fnd_log.LEVEL_STATEMENT); ' ||
596 ' END IF; ' ||
597 ' csi_cz_int.get_configuration_revision ' ||
598 ' ( ' ||
599 ' p_config_header_id => :1, ' ||
600 ' p_target_commitment_date => :2, ' ||
601 ' px_instance_level => v_instance_level, ' ||
602 ' x_install_config_rec => l_config_rec, ' ||
603 ' x_return_status => CZ_IB_TRANSACTIONS.m_return_status, ' ||
604 ' x_return_message => CZ_IB_TRANSACTIONS.m_return_message ' ||
605 ' ); ' ||
606 ' IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN ' ||
607 ' cz_utils.log_report(''CZ_IB_TRANSACTIONS'', ''get_configuration_revision'', l_ndebug, ' ||
608 ' ''csi_cz_int.get_configuration_revision() has been called : current time : ''||TO_CHAR(SYSDATE,''DD-MM-YYYY HH24-MI-SS''), ' ||
609 ' fnd_log.LEVEL_STATEMENT); ' ||
610 ' END IF; ' ||
611 ' CZ_IB_TRANSACTIONS.m_config_rec.source_application_id := l_config_rec.source_application_id; ' ||
612 ' CZ_IB_TRANSACTIONS.m_config_rec.source_txn_header_ref := l_config_rec.source_txn_header_ref; ' ||
613 ' CZ_IB_TRANSACTIONS.m_config_rec.source_txn_line_ref1 := l_config_rec.source_txn_line_ref1; ' ||
614 ' CZ_IB_TRANSACTIONS.m_config_rec.source_txn_line_ref2 := l_config_rec.source_txn_line_ref2; ' ||
615 ' CZ_IB_TRANSACTIONS.m_config_rec.source_txn_line_ref3 := l_config_rec.source_txn_line_ref3; ' ||
616 ' CZ_IB_TRANSACTIONS.m_config_rec.instance_id := l_config_rec.instance_id; ' ||
617 ' CZ_IB_TRANSACTIONS.m_config_rec.lock_id := l_config_rec.lock_id; ' ||
618 ' CZ_IB_TRANSACTIONS.m_config_rec.lock_status := l_config_rec.lock_status; ' ||
622 ' IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN ' ||
619 ' CZ_IB_TRANSACTIONS.m_config_rec.config_inst_hdr_id := l_config_rec.config_inst_hdr_id; ' ||
620 ' CZ_IB_TRANSACTIONS.m_config_rec.config_inst_item_id := l_config_rec.config_inst_item_id; ' ||
621 ' CZ_IB_TRANSACTIONS.m_config_rec.config_inst_rev_num := l_config_rec.config_inst_rev_num; ' ||
623 ' cz_utils.log_report(''CZ_IB_TRANSACTIONS'', ''get_configuration_revision'', l_ndebug, ' ||
624 ' ''Out array for get_configuration_revision() has been populated : current time : ''||TO_CHAR(SYSDATE,''DD-MM-YYYY HH24-MI-SS''), ' ||
625 ' fnd_log.LEVEL_STATEMENT); ' ||
626 ' END IF; ' ||
627 ' END;' USING p_config_header_id,p_target_commitment_date;
628
629 x_config_rev_number := CZ_IB_TRANSACTIONS.m_config_rev_number;
630 x_config_rec := CZ_IB_TRANSACTIONS.m_config_rec;
631 x_return_status := CZ_IB_TRANSACTIONS.m_return_status;
632 x_return_message := CZ_IB_TRANSACTIONS.m_return_message;
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 x_return_status := FND_API.g_ret_sts_unexp_error;
637 x_return_message := 'CZ_IB_TRANSACTIONS.Get_Configuration_Revision : '||SQLERRM;
638 LOG_REPORT(m_RUN_ID,x_return_message);
639
640 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
641 cz_utils.log_report('CZ_IB_TRANSACTIONS', 'get_configuration_revision', l_ndebug,
642 'Fatal error : '||SQLERRM||' : current time : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24-MI-SS'),
643 fnd_log.LEVEL_ERROR);
644 END IF;
645 END get_configuration_revision;
646
647 --
648 -- propogate is_item_changed property
649 --
650 PROCEDURE propogate_Changed_Property
651 (
652 p_config_item_id IN NUMBER,
653 p_is_item_changed IN VARCHAR2,
654 p_config_item_tbl IN int_array_tbl_type,
655 p_parent_config_item_tbl IN int_array_tbl_type,
656 p_is_item_changed_tbl IN char_array_tbl_type,
657 p_ib_trackable_tbl IN char_array_tbl_type,
658 px_hash_changed_item_tbl IN OUT NOCOPY int_array_tbl_type_idx_vc2, -- Bug 6892148;
659 px_hash_src_txn_flag_tbl IN OUT NOCOPY char_array_tbl_type_idx_vc2 -- Bug 6892148;
660 ) IS
661
662 v_is_item_changed VARCHAR2(255);
663
664 BEGIN
665
666 IF p_config_item_tbl.COUNT = 0 THEN
667 RETURN;
668 END IF;
669
670 --
671 -- propogate item_changed property and populate hash map
672 -- with the config_item_id of the nearest changed item
673 --
674 FOR i IN p_config_item_tbl.FIRST..p_config_item_tbl.LAST
675 LOOP
676 IF p_ib_trackable_tbl(i)='1' THEN
677
678 IF p_parent_config_item_tbl(i) = p_config_item_id THEN
679 IF p_is_item_changed_tbl(i) = '0' THEN
680 px_hash_changed_item_tbl(p_config_item_tbl(i)):=p_config_item_id;
681 px_hash_src_txn_flag_tbl(p_config_item_tbl(i)):='N';
682 v_is_item_changed:=p_is_item_changed;
683 END IF;
684
685 IF p_is_item_changed_tbl(i) = '1' THEN
686 px_hash_changed_item_tbl(p_config_item_tbl(i)):=p_config_item_tbl(i);
687 v_is_item_changed:='1';
688 END IF;
689
690 propogate_Changed_Property( p_config_item_tbl(i),
691 v_is_item_changed,
692 p_config_item_tbl,
693 p_parent_config_item_tbl,
694 p_is_item_changed_tbl,
695 p_ib_trackable_tbl,
696 px_hash_changed_item_tbl,
697 px_hash_src_txn_flag_tbl);
698 END IF;
699 END IF;
700 END LOOP;
701
702 END propogate_Changed_Property;
703
704 --
705 -- delete IB data
706 --
707 PROCEDURE remove_IB_Config
708 (
709 p_session_config_hdr_id IN NUMBER DEFAULT NULL,
710 p_session_config_rev_nbr IN NUMBER DEFAULT NULL,
711 p_instance_hdr_id IN NUMBER DEFAULT NULL,
712 p_instance_rev_nbr IN NUMBER DEFAULT NULL,
713 p_instance_item_id IN NUMBER DEFAULT NULL,
714 x_run_id OUT NOCOPY NUMBER
715 ) IS
716
717 cv_csi_txl_line cv_cursor_type;
718
719 t_subject_tbl int_array_tbl_type;
720 t_transaction_line_tbl int_array_tbl_type;
721
722 v_transaction_line_id NUMBER;
723 v_return_status VARCHAR2(255) := fnd_api.g_ret_sts_success;
724 v_msg_data VARCHAR2(2000);
725 v_msg_count NUMBER;
726
727 BEGIN
728 x_run_id:=0;
729
730 --
731 -- fix for the #2860167 :
732 -- if CSI_T_TRANSACTION_LINES does not contain column config_session_hdr_id
733 -- then function CSI_Exists return FALSE
734 -- and algorithm stops ( no errors - we just assume that old IB schema is used)
735 --
736 IF NOT(CSI_Exists) THEN
737 RETURN;
738 END IF;
739
740 Initialize(SYSDATE, '0');
741
745 WHERE config_session_hdr_id = :1 AND config_session_rev_num = :2 AND
742 IF p_session_config_hdr_id IS NOT NULL THEN
743 OPEN cv_csi_txl_line FOR
744 'SELECT transaction_line_id FROM CSI_T_TRANSACTION_LINES
746 config_session_item_id=NVL(:3,config_session_item_id) AND processing_status <> :4'
747 USING p_session_config_hdr_id,p_session_config_rev_nbr,p_instance_item_id,G_IB_TXN_STATUS_PROCESSED;
748 LOOP
749
750 FETCH cv_csi_txl_line INTO v_transaction_line_id;
751 EXIT WHEN cv_csi_txl_line%NOTFOUND;
752
753 -- csi_t_txn_details_grp.delete_transaction_dtls --
754 delete_transaction_dtls
755 (
756 p_api_version => 1.0
757 ,p_commit => fnd_api.g_false
758 ,p_init_msg_list => fnd_api.g_false
759 ,p_validation_level => fnd_api.g_valid_level_none
760 ,p_api_caller_identity => 'CONFIG'
761 ,p_transaction_line_id => v_transaction_line_id
762 ,x_return_status => v_return_status
763 ,x_msg_count => v_msg_count
764 ,x_msg_data => v_msg_data
765 );
766
767 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
768 x_run_id:=m_RUN_ID;
769 LOG_REPORT(m_RUN_ID,
770 'Deleting failed for txn line : transaction_line_id='||TO_CHAR(v_transaction_line_id)||' IB API error message : '||v_msg_data,
771 v_msg_count);
772 END IF;
773 END LOOP;
774 CLOSE cv_csi_txl_line;
775 RETURN;
776 END IF;
777
778 -- bug 5696750: apart from processing_status, csi also checks if csi_transaction_id is not null
779 -- when querying table csi_t_txn_line_details (see csi_t_vldn_routines_pvt.check_ib_creation).
780 -- We may need to do the same thing here as csi does. We only verified with csi team about the
781 -- correctness of the process status check when using sesssion header and csi_t_transaction_lines.
782 -- Currently we do not use instance hdr in this aspect. If in the future instance hdr is used,
783 -- we will need to revisit here.
784 IF p_instance_hdr_id IS NOT NULL THEN
785 OPEN cv_csi_txl_line FOR
786 'SELECT DISTINCT transaction_line_id
787 FROM CSI_T_TXN_LINE_DETAILS
788 WHERE config_inst_hdr_id = :1 AND config_inst_rev_num = :2 AND processing_status <> :3'
789 USING p_instance_hdr_id,p_instance_rev_nbr,G_IB_TXN_STATUS_PROCESSED;
790 LOOP
791 FETCH cv_csi_txl_line INTO v_transaction_line_id;
792 EXIT WHEN cv_csi_txl_line%NOTFOUND;
793
794 -- csi_t_txn_details_grp.delete_transaction_dtls --
795 delete_transaction_dtls
796 (
797 p_api_version => 1.0
798 ,p_commit => fnd_api.g_false
799 ,p_init_msg_list => fnd_api.g_false
800 ,p_validation_level => fnd_api.g_valid_level_none
801 ,p_api_caller_identity => 'CONFIG'
802 ,p_transaction_line_id => v_transaction_line_id
803 ,x_return_status => v_return_status
804 ,x_msg_count => v_msg_count
805 ,x_msg_data => v_msg_data
806 );
807
808 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
809 x_run_id:=m_RUN_ID;
810 LOG_REPORT(m_RUN_ID,'Deleting failed for txn line : transaction_line_id='||
811 TO_CHAR(v_transaction_line_id)||' IB API error message : '||v_msg_data,v_msg_count);
812 END IF;
813 END LOOP;
814 END IF;
815
816 EXCEPTION
817 WHEN OTHERS THEN
818 IF cv_csi_txl_line%ISOPEN THEN
819 CLOSE cv_csi_txl_line;
820 END IF;
821 x_run_id:=m_RUN_ID;
822 LOG_REPORT(m_RUN_ID,'Deleting failed : '||SQLERRM);
823 END remove_IB_Config;
824
825 --
826 -- collect all Config Items from subtree
827 -- with root <=> (p_config_hdr_id, p_new_config_rev_nbr, p_config_item_id)
828 -- and insert data into IB
829 --
830
831 PROCEDURE populate_IB_Txn
832 (
833 p_config_hdr_id IN NUMBER, -- config_hdr_id of root node
834 p_config_rev_nbr IN NUMBER, -- config_rev_nbr of root node
835 p_config_item_id IN NUMBER, -- config_item_id of root node
836 p_hash_dtls_tbl IN OUT NOCOPY int_array_tbl_type_idx_vc2, -- hash array config_item_id <=> txn line id -- Bug 6892148;
837 p_passed_targets_rev_tbl IN int_array_tbl_type_idx_vc2, -- hash array of connectors for which targets are in the same CIO list -- Bug 6892148;
838 p_rltns_tbl IN OUT NOCOPY txn_ii_rltns_tbl, -- array of relationships
839 p_enddated_rltns_tbl IN OUT NOCOPY txn_ii_rltns_tbl, -- array of enddated connect_to relationships
840 p_txn_type_id IN NUMBER,
841 p_item_status IN VARCHAR2,
842 x_run_id OUT NOCOPY NUMBER
843 ) IS
844
845
846 TYPE txn_line_tbl IS TABLE OF txn_line_rec
847 INDEX BY BINARY_INTEGER;
848
849 t_txn_line_tbl txn_line_tbl;
850 v_txn_line txn_line_rec;
851
855 t_txn_org_assgn_tbl txn_org_assgn_tbl;
852 t_txn_line_dtls_tbl txn_line_detail_tbl;
853 t_txn_party_tbl txn_party_detail_tbl ;
854 t_txn_pty_acct_tbl txn_pty_acct_detail_tbl;
856 t_txn_eav_tbl txn_ext_attrib_vals_tbl;
857
858 t_txn_systems_tbl txn_systems_tbl;
859 t_txn_ii_rltns_tbl txn_ii_rltns_tbl;
860
861 ts_txn_ii_rltns_tbl txn_ii_rltns_tbl;
862
863 t_txn_ext_attrib_vals_tbl txn_ext_attrib_vals_tbl;
864
865 tv_txn_line_dtls_tbl txn_line_detail_tbl;
866 tv_txn_party_tbl txn_party_detail_tbl ;
867 tv_txn_pty_acct_tbl txn_pty_acct_detail_tbl;
868 tv_txn_org_assgn_tbl txn_org_assgn_tbl;
869 tv_txn_eav_tbl txn_ext_attrib_vals_tbl;
870 tv_txn_systems_tbl txn_systems_tbl;
871 tv_txn_ii_rltns_tbl txn_ii_rltns_tbl;
872 tv_txn_ext_attrib_vals_tbl txn_ext_attrib_vals_tbl;
873
874 v_return_status VARCHAR2(255) := fnd_api.g_ret_sts_success;
875 v_msg_data VARCHAR2(2000);
876 v_msg_count NUMBER;
877 t_component_instance_type_tbl char_array_tbl_type;
878 t_config_hdr_tbl int_array_tbl_type;
879 t_config_item_tbl int_array_tbl_type;
880 t_parent_config_item_tbl int_array_tbl_type;
881 t_config_rev_nbr_tbl int_array_tbl_type;
882 t_instance_hdr_tbl int_array_tbl_type;
883 t_instance_rev_nbr_tbl int_array_tbl_type;
884 t_instance_item_tbl int_array_tbl_type;
885 t_target_hdr_tbl int_array_tbl_type;
886 t_target_rev_nbr_tbl int_array_tbl_type;
887 t_target_item_tbl int_array_tbl_type;
888 t_location_tbl int_array_tbl_type;
889 t_inventory_item_tbl int_array_tbl_type;
890 t_organization_tbl int_array_tbl_type;
891 t_quantity_tbl int_array_tbl_type; -- sselahi: changed from char_array_tbl_type;
892 t_hash_dtls_tbl int_array_tbl_type_idx_vc2; -- Bug 6892148;
893 t_attribute_level_tbl char_array_tbl_type;
894 t_attribute_group_tbl char_array_tbl_type;
895 t_attribute_name_tbl char_array_tbl_type;
896 t_attribute_value_tbl char_array_tbl_type;
897 t_location_type_code_tbl char_array_tbl_type;
898 t_ib_trackable_tbl char_array_tbl_type;
899 t_ext_activated_flag_tbl char_array_tbl_type;
900 t_config_delta_tbl char_array_tbl_type;
901 t_is_item_changed_tbl char_array_tbl_type;
902 t_uom_code_tbl char_array_tbl_type;
903 t_name_tbl char_array_tbl_type;
904 t_discontinued_flag_tbl char_array_tbl_type;
905 t_del_sub_items_tbl int_array_tbl_type;
906 t_attrib_hash int_array_tbl_type;
907 t_cfg_hash int_array_tbl_type;
908 t_hash_changed_item_tbl int_array_tbl_type_idx_vc2; -- Bug 6892148;
909 t_hash_src_txn_flag_tbl char_array_tbl_type_idx_vc2; -- kdande; 27-Nov-2008; Bug 7599508;
910 t_tangible_item_flag_tbl char_array_tbl_type;
911 v_ib_trackable CZ_CONFIG_ITEMS.ib_trackable%TYPE;
912
913 v_relationship_type_code VARCHAR2(255);
914 v_txn_line_ind NUMBER;
915 v_txn_line_dtls_ind NUMBER;
916 v_rltns_tbl_ind NUMBER;
917 v_eav_ind NUMBER;
918 v_parent_config_item_id NUMBER;
919 v_baseline_rev_nbr NUMBER;
920 v_root_id NUMBER;
921 v_item_id NUMBER;
922 v_attrib_ind NUMBER;
923 v_root_config_item_id NUMBER;
924 v_hash_changed_item_ind NUMBER;
925 v_instance_action_type NUMBER;
926 v_root_instance_hdr_id NUMBER;
927 v_root_instance_rev_nbr NUMBER;
928 v_target_hdr_id NUMBER;
929 v_target_rev_nbr NUMBER;
930 v_target_config_item_id NUMBER;
931 v_enddated_rltns_tbl_ind NUMBER;
932 v_baseline_txn_line_id NUMBER;
933 v_baseline_txn_rltn_id NUMBER;
934
935 BEGIN
936 ERROR_CODE:='0010';
937 x_run_id:=0;
938
939 --
940 -- collect all items from subtree
941 -- starting with p_config_item_id
942 -- nontrackable items ( IB_TRACKABLE = '0' )
943 -- also will be collected here
944 --
945
946 FOR i IN(SELECT DISTINCT config_hdr_id,config_rev_nbr FROM CZ_CONFIG_ITEMS_V
947 WHERE instance_hdr_id=p_config_hdr_id AND
948 instance_rev_nbr=p_config_rev_nbr)
949 LOOP
950 LOG_REPORT(m_RUN_ID,'config_hdr_id='||TO_CHAR(i.config_hdr_id)||' config_rev_nbr='||TO_CHAR(i.config_rev_nbr));
951 END LOOP;
952
953 LOG_REPORT(m_RUN_ID,'instance_hdr_id='||TO_CHAR(p_config_hdr_id)||' instance_rev_nbr='||TO_CHAR(p_config_rev_nbr)||
954 ' config_item_id='||TO_CHAR(p_config_item_id)||' RUN_ID='||TO_CHAR(m_RUN_ID));
955
956 SELECT config_hdr_id,
957 config_rev_nbr,
958 config_item_id,
959 parent_config_item_id,
960 instance_hdr_id,
961 instance_rev_nbr,
962 target_hdr_id,
963 target_rev_nbr,
964 target_config_item_id,
965 NVL(ib_trackable, NO_FLAG),
966 location_id,
970 item_num_val, -- sselahi: changed from item_val
967 location_type_code,
968 inventory_item_id,
969 organization_id,
971 uom_code,
972 ext_activated_flag,
973 config_delta,
974 discontinued_flag,
975 component_instance_type,
976 NVL(tangible_item_flag,NO_FLAG),
977 NAME
978 BULK COLLECT INTO
979 t_config_hdr_tbl,
980 t_config_rev_nbr_tbl,
981 t_config_item_tbl,
982 t_parent_config_item_tbl,
983 t_instance_hdr_tbl,
984 t_instance_rev_nbr_tbl,
985 t_target_hdr_tbl,
986 t_target_rev_nbr_tbl,
987 t_target_item_tbl,
988 t_ib_trackable_tbl,
989 t_location_tbl,
990 t_location_type_code_tbl,
991 t_inventory_item_tbl,
992 t_organization_tbl,
993 t_quantity_tbl,
994 t_uom_code_tbl,
995 t_ext_activated_flag_tbl,
996 t_config_delta_tbl,
997 t_discontinued_flag_tbl,
998 t_component_instance_type_tbl,
999 t_tangible_item_flag_tbl,
1000 t_name_tbl
1001 FROM
1002 (SELECT * FROM CZ_CONFIG_ITEMS ci
1003 START WITH ci.instance_hdr_id=p_config_hdr_id AND
1004 ci.instance_rev_nbr=p_config_rev_nbr AND component_instance_type='I' AND deleted_flag='0'
1005 CONNECT BY PRIOR ci.config_item_id=ci.parent_config_item_id AND
1006 ci.instance_hdr_id=p_config_hdr_id AND
1007 ci.instance_rev_nbr=p_config_rev_nbr AND
1008 PRIOR ci.instance_hdr_id=p_config_hdr_id AND
1009 PRIOR ci.instance_rev_nbr=p_config_rev_nbr AND
1010 deleted_flag='0' AND PRIOR deleted_flag='0' AND
1011 (
1012 (ci.ext_activated_flag='1' OR ci.config_delta <> 0) OR
1013 (PRIOR ci.ext_activated_flag='1' OR PRIOR ci.config_delta <> 0)
1014 )
1015 ) vi
1016 WHERE vi.instance_hdr_id=p_config_hdr_id AND
1017 vi.instance_rev_nbr=p_config_rev_nbr AND
1018 ((vi.item_num_val IS NOT NULL AND vi.item_num_val<>0)OR vi.target_config_item_id IS NOT NULL); --Bug6655994 Added a new condition
1019 --to avoid picking the option class with zero quantity.
1020
1021 ERROR_CODE:='0011';
1022
1023 IF t_config_item_tbl.COUNT=0 THEN
1024 RETURN;
1025 END IF;
1026
1027 FOR i IN t_config_item_tbl.FIRST..t_config_item_tbl.LAST
1028 LOOP
1029 IF t_target_item_tbl(i) IS NOT NULL THEN
1030 v_ib_trackable:=NO_FLAG;
1031 BEGIN
1032 SELECT NVL(ib_trackable,NO_FLAG) INTO v_ib_trackable FROM CZ_CONFIG_ITEMS_V
1033 WHERE instance_hdr_id = t_target_hdr_tbl(i) AND
1034 instance_rev_nbr = t_target_rev_nbr_tbl(i) AND
1035 config_item_id = t_target_item_tbl(i);
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 NULL;
1039 END;
1040 t_ib_trackable_tbl(i):=v_ib_trackable;
1041 END IF;
1042 END LOOP;
1043
1044 ERROR_CODE:='00111';
1045
1046 --
1047 -- exit when subtree is empty
1048 --
1049 IF t_config_item_tbl.COUNT = 0 THEN
1050 RETURN;
1051 END IF;
1052
1053 BEGIN
1054 v_baseline_rev_nbr:=NULL;
1055 SELECT baseline_rev_nbr INTO v_baseline_rev_nbr
1056 FROM cz_config_hdrs
1057 WHERE config_hdr_id=p_config_hdr_id AND
1058 config_rev_nbr=p_config_rev_nbr AND
1059 deleted_flag=NO_FLAG;
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 ERROR_CODE:='00112';
1063 END;
1064
1065 BEGIN
1066 v_root_config_item_id:=NULL;
1067 SELECT instance_hdr_id,instance_rev_nbr,parent_config_item_id
1068 INTO v_root_instance_hdr_id,v_root_instance_rev_nbr,v_root_config_item_id
1069 FROM CZ_CONFIG_ITEMS_V
1070 WHERE instance_hdr_id=p_config_hdr_id AND
1071 instance_rev_nbr=p_config_rev_nbr AND
1072 config_item_id=p_config_item_id;
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 ERROR_CODE:='00113';
1076 END;
1077
1078 --
1079 -- set parent_config_item_id to nearest trackable parent's config_item_id
1080 --
1081 FOR i IN t_config_item_tbl.FIRST..t_config_item_tbl.LAST
1082 LOOP
1083 --
1084 -- check : is the current item changed or not
1085 -- and populate an array with these flags
1086 --
1087 IF (t_ext_activated_flag_tbl(i)='1' OR t_config_delta_tbl(i) NOT IN('0','00','000')) THEN
1088 t_is_item_changed_tbl(i):='1';
1089 ELSE
1090 t_is_item_changed_tbl(i):='0';
1091 END IF;
1092
1093 -- IF t_ib_trackable_tbl(i)=NO_FLAG OR NOT(CZ_UTILS.conv_num(t_quantity_tbl(i)) > 0) THEN
1094 IF t_ib_trackable_tbl(i)=NO_FLAG OR NOT(t_quantity_tbl(i) > 0) THEN -- sselahi: removed conv_num function
1095 FOR k IN t_config_item_tbl.FIRST..t_config_item_tbl.LAST
1099 END IF;
1096 LOOP
1097 IF t_parent_config_item_tbl(k) = t_config_item_tbl(i) THEN
1098 t_parent_config_item_tbl(k):=t_parent_config_item_tbl(i);
1100 END LOOP;
1101 END IF;
1102 END LOOP;
1103
1104 t_hash_changed_item_tbl.DELETE;
1105 t_hash_src_txn_flag_tbl.DELETE;
1106 t_hash_changed_item_tbl(p_config_item_id):=p_config_item_id;
1107
1108 propogate_Changed_Property
1109 (
1110 p_config_item_id => p_config_item_id,
1111 p_is_item_changed => '1',
1112 p_config_item_tbl => t_config_item_tbl,
1113 p_parent_config_item_tbl => t_parent_config_item_tbl,
1114 p_is_item_changed_tbl => t_is_item_changed_tbl,
1115 p_ib_trackable_tbl => t_ib_trackable_tbl,
1116 px_hash_changed_item_tbl => t_hash_changed_item_tbl,
1117 px_hash_src_txn_flag_tbl => t_hash_src_txn_flag_tbl);
1118
1119 FOR i IN t_config_item_tbl.FIRST..t_config_item_tbl.LAST
1120 LOOP
1121
1122 --
1123 -- create tree from trackable nodes
1124 --
1125 IF t_ib_trackable_tbl(i)=YES_FLAG THEN
1126 v_item_id:=t_config_item_tbl(i);
1127
1128 IF t_target_item_tbl(i) IS NOT NULL THEN
1129 v_relationship_type_code:=CONNECTED_TO_RELATIONSHIP;
1130 ELSE
1131 v_relationship_type_code:=COMPONENT_OF_RELATIONSHIP;
1132 END IF;
1133
1134
1135 IF v_relationship_type_code = COMPONENT_OF_RELATIONSHIP THEN
1136
1137 --
1138 -- populate TXN Lines
1139 --
1140
1141 IF t_is_item_changed_tbl(i) = '1' THEN
1142
1143 v_txn_line_ind:=t_txn_line_tbl.COUNT+1;
1144 t_txn_line_tbl(v_txn_line_ind).source_transaction_type_id := p_txn_type_id;
1145 t_txn_line_tbl(v_txn_line_ind).source_transaction_table := CZ_IB_TRANSACTION_TABLE;
1146 t_txn_line_tbl(v_txn_line_ind).source_transaction_status := 'PROPOSED';
1147 t_txn_line_tbl(v_txn_line_ind).config_session_hdr_id := t_config_hdr_tbl(i);
1148 t_txn_line_tbl(v_txn_line_ind).config_session_rev_num := t_config_rev_nbr_tbl(i);
1149 t_txn_line_tbl(v_txn_line_ind).config_session_item_id := t_config_item_tbl(i);
1150 t_txn_line_tbl(v_txn_line_ind).config_valid_status := YES_FLAG;
1151
1152 --
1153 -- IB API is called by CONFIGurator
1154 --
1155 t_txn_line_tbl(v_txn_line_ind).api_caller_identity := 'CONFIG';
1156 t_txn_line_tbl(v_txn_line_ind).object_version_number := 1;
1157
1158 END IF;
1159
1160 --
1161 -- populate TXN Line Details
1162 --
1163 v_txn_line_dtls_ind:=t_txn_line_dtls_tbl.COUNT+1;
1164
1165 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).config_inst_hdr_id := t_instance_hdr_tbl(i);
1166 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).config_inst_rev_num := t_instance_rev_nbr_tbl(i);
1167 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).config_inst_item_id := t_config_item_tbl(i);
1168 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).source_transaction_flag := 'Y';
1169 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).instance_exists_flag := 'N';
1170 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).quantity := t_quantity_tbl(i); -- sselahi: TBD
1171 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).unit_of_measure := t_uom_code_tbl(i);
1172
1173 IF t_component_instance_type_tbl(i)='I' THEN
1174 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).location_id := t_location_tbl(i);
1175 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).location_type_code := t_location_type_code_tbl(i);
1176 ELSE
1177 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).location_id := FND_API.G_MISS_NUM;
1178 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).location_type_code := FND_API.G_MISS_CHAR;
1179 END IF;
1180
1181 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).inventory_item_id := t_inventory_item_tbl(i);
1182 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).inv_organization_id := t_organization_tbl(i);
1183 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).mfg_serial_number_flag := 'N';
1184 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).sub_type_id := 101;
1185 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).instance_description := t_name_tbl(i);
1186 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).config_inst_baseline_rev_num := v_baseline_rev_nbr;
1187
1188 IF ( (t_discontinued_flag_tbl(i)=YES_FLAG AND m_CZ_IB_AUTO_EXPIRATION='Y') AND
1189 t_tangible_item_flag_tbl(i)<>YES_FLAG ) THEN
1190 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).active_end_date := m_EFFECTIVE_DATE;
1191 END IF;
1192
1193 t_txn_line_dtls_tbl(v_txn_line_dtls_ind).object_version_number := 1;
1194
1195 t_hash_dtls_tbl(t_config_item_tbl(i)) := v_txn_line_dtls_ind;
1196
1197 END IF; -- end of v_relationship_type_code = COMPONENT_OF_RELATIONSHIP --
1198
1199 IF v_relationship_type_code = CONNECTED_TO_RELATIONSHIP THEN
1200
1201 v_target_hdr_id:=NULL;
1202 v_target_rev_nbr:=NULL;
1206 SELECT target_hdr_id,target_rev_nbr,target_config_item_id
1203 v_target_config_item_id:=NULL;
1204
1205 BEGIN
1207 INTO v_target_hdr_id,v_target_rev_nbr,v_target_config_item_id
1208 FROM CZ_CONFIG_ITEMS_V
1209 WHERE instance_hdr_id=t_instance_hdr_tbl(i) AND instance_rev_nbr=v_baseline_rev_nbr AND
1210 config_item_id=t_config_item_tbl(i);
1211
1212 IF NOT(v_target_hdr_id=t_target_hdr_tbl(i) AND v_target_config_item_id=t_target_item_tbl(i)) THEN
1213
1214 v_enddated_rltns_tbl_ind:=p_enddated_rltns_tbl.COUNT+1;
1215
1216 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).sub_config_inst_item_id := t_parent_config_item_tbl(i);
1217 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).sub_config_inst_hdr_id := t_instance_hdr_tbl(i);
1218
1219 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).sub_config_inst_rev_num := t_instance_rev_nbr_tbl(i);
1220
1221 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).relationship_type_code := CONNECTED_TO_RELATIONSHIP;
1222
1223 IF p_passed_targets_rev_tbl.EXISTS(v_target_config_item_id) THEN
1224
1225 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).obj_config_inst_item_id := v_target_config_item_id;
1226 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).obj_config_inst_hdr_id := v_target_hdr_id;
1227 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).obj_config_inst_rev_num := p_passed_targets_rev_tbl(v_target_config_item_id);
1228
1229 ELSE -- use data from baseline
1230
1231 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).obj_config_inst_item_id := v_target_config_item_id;
1232 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).obj_config_inst_hdr_id := v_target_hdr_id;
1233 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).obj_config_inst_rev_num := v_target_rev_nbr;
1234
1235 END IF;
1236
1237 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).api_caller_identity := 'CONFIG';
1238 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).object_version_number := 1;
1239
1240 p_enddated_rltns_tbl(v_enddated_rltns_tbl_ind).active_end_date := m_EFFECTIVE_DATE;
1241
1242 END IF;
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 NULL;
1246 END;
1247
1248 IF t_target_hdr_tbl(i) <> t_instance_hdr_tbl(i)
1249 AND NOT(t_discontinued_flag_tbl(i)=YES_FLAG AND NOT(v_target_hdr_id=t_target_hdr_tbl(i) AND v_target_config_item_id=t_target_item_tbl(i)) ) THEN
1250
1251 --
1252 -- CONNECTED-TO relationship
1253 --
1254
1255 v_rltns_tbl_ind:=t_txn_ii_rltns_tbl.COUNT+1;
1256 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).sub_config_inst_item_id := t_parent_config_item_tbl(i);
1257 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).sub_config_inst_hdr_id := t_instance_hdr_tbl(i);
1258 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).sub_config_inst_rev_num := t_instance_rev_nbr_tbl(i);
1259
1260 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).relationship_type_code := CONNECTED_TO_RELATIONSHIP;
1261 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).obj_config_inst_item_id := t_target_item_tbl(i);
1262 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).obj_config_inst_hdr_id := t_target_hdr_tbl(i);
1263 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).obj_config_inst_rev_num := t_target_rev_nbr_tbl(i);
1264
1265 IF (t_discontinued_flag_tbl(i)=YES_FLAG AND m_CZ_IB_AUTO_EXPIRATION='Y') THEN
1266 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).active_end_date := m_EFFECTIVE_DATE;
1267 END IF;
1268
1269 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).api_caller_identity := 'CONFIG';
1270 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).object_version_number := 1;
1271
1272 END IF;
1273
1274
1275
1276 ELSIF v_relationship_type_code = COMPONENT_OF_RELATIONSHIP THEN
1277 --
1278 -- COMPONENT-OF relationship
1279 --
1280 v_rltns_tbl_ind:=t_txn_ii_rltns_tbl.COUNT+1;
1281
1282 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).sub_config_inst_item_id := t_config_item_tbl(i);
1283 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).sub_config_inst_hdr_id := t_instance_hdr_tbl(i);
1284 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).sub_config_inst_rev_num := t_instance_rev_nbr_tbl(i);
1285
1286 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).relationship_type_code := COMPONENT_OF_RELATIONSHIP;
1287 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).obj_config_inst_item_id := t_parent_config_item_tbl(i);
1288 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).obj_config_inst_hdr_id := t_instance_hdr_tbl(i);
1289 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).obj_config_inst_rev_num := t_instance_rev_nbr_tbl(i);
1290
1291
1292 IF (t_discontinued_flag_tbl(i)=YES_FLAG AND m_CZ_IB_AUTO_EXPIRATION='Y') THEN
1293 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).active_end_date := m_EFFECTIVE_DATE;
1294 END IF;
1295
1296 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).api_caller_identity := 'CONFIG';
1297 t_txn_ii_rltns_tbl(v_rltns_tbl_ind).object_version_number := 1;
1301 -- there are no other relationship types yet
1298
1299 ELSE
1300 --
1302 --
1303 NULL;
1304 END IF;
1305
1306 END IF;
1307 END LOOP;
1308
1309 --
1310 -- reinitialize array to store config items
1311 --
1312 t_config_item_tbl.DELETE;
1313 --
1314 -- retreive Extended Attributes
1315 --
1316 SELECT
1317 config_item_id,attribute_group,attribute_name,attribute_value
1318 BULK COLLECT INTO
1319 t_config_item_tbl,t_attribute_group_tbl,t_attribute_name_tbl,t_attribute_value_tbl
1320 FROM CZ_CONFIG_EXT_ATTRIBUTES
1321 WHERE config_hdr_id = p_config_hdr_id AND
1322 config_rev_nbr = p_config_rev_nbr AND deleted_flag=NO_FLAG;
1323
1324 ERROR_CODE:='0031';
1325
1326 IF t_attribute_name_tbl.COUNT > 0 THEN
1327 FOR i IN t_attribute_name_tbl.FIRST..t_attribute_name_tbl.LAST
1328 LOOP
1329 --
1330 -- bug #2692678 ( writting attributes for discontinued or unselected items )
1331 -- has been fixed in OracleInstalledBase class ( writeAttributes() method )
1332 -- this IF statement has been added for safety to avoid exception
1333 -- element does not exist if because of some possible problems/bugs
1334 -- we have a different items in Config Items collection and CZ_EXT_ATTRIBUTES table
1335 --
1336 IF t_hash_dtls_tbl.EXISTS(t_config_item_tbl(i)) THEN
1337 v_eav_ind := t_txn_eav_tbl.COUNT+1;
1338 v_txn_line_dtls_ind:=t_hash_dtls_tbl(t_config_item_tbl(i));
1339 t_txn_eav_tbl(v_eav_ind).attrib_source_table := 'CSI_I_EXTENDED_ATTRIBS';
1340 t_txn_eav_tbl(v_eav_ind).attribute_code := t_attribute_name_tbl(i);
1341 t_txn_eav_tbl(v_eav_ind).attribute_value := t_attribute_value_tbl(i);
1342 t_txn_eav_tbl(v_eav_ind).active_start_date := t_txn_line_dtls_tbl(v_txn_line_dtls_ind).active_start_date;
1343 t_txn_eav_tbl(v_eav_ind).active_end_date := t_txn_line_dtls_tbl(v_txn_line_dtls_ind).active_end_date;
1344
1345 --
1346 -- txn_line_details_index points to the related line detail record
1347 --
1348 t_txn_eav_tbl(v_eav_ind).txn_line_details_index:= v_txn_line_dtls_ind;
1349 t_txn_eav_tbl(v_eav_ind).object_version_number := 1;
1350 t_attrib_hash(v_eav_ind):=t_config_item_tbl(i);
1351 END IF;
1352 END LOOP;
1353 END IF;
1354
1355 ERROR_CODE:='0032';
1356
1357 tv_txn_line_dtls_tbl.DELETE;
1358 tv_txn_ii_rltns_tbl.DELETE;
1359 tv_txn_eav_tbl.DELETE;
1360 tv_txn_party_tbl.DELETE;
1361
1362 FOR i IN t_txn_line_tbl.FIRST..t_txn_line_tbl.LAST
1363 LOOP
1364 v_txn_line:=t_txn_line_tbl(i);
1365
1366 tv_txn_line_dtls_tbl.DELETE;
1367 tv_txn_ii_rltns_tbl.DELETE;
1368
1369 IF t_txn_line_dtls_tbl.COUNT > 0 THEN
1370
1371 FOR l IN t_txn_line_dtls_tbl.FIRST..t_txn_line_dtls_tbl.LAST
1372 LOOP
1373
1374 --
1375 -- populate txn details
1376 --
1377
1378 --
1379 -- old code
1380 -- IF t_txn_line_dtls_tbl(l).config_inst_item_id=t_txn_line_tbl(i).config_session_item_id THEN
1381 --
1382
1383 -- new code
1384 IF t_hash_changed_item_tbl(t_txn_line_dtls_tbl(l).config_inst_item_id)=t_txn_line_tbl(i).config_session_item_id THEN
1385
1386 IF t_hash_src_txn_flag_tbl.EXISTS(t_txn_line_dtls_tbl(l).config_inst_item_id) THEN
1387 t_txn_line_dtls_tbl(l).source_transaction_flag:=t_hash_src_txn_flag_tbl(t_txn_line_dtls_tbl(l).config_inst_item_id);
1388 END IF;
1389 tv_txn_line_dtls_tbl(tv_txn_line_dtls_tbl.COUNT+1):=t_txn_line_dtls_tbl(l);
1390
1391
1392 t_cfg_hash(t_txn_line_dtls_tbl(l).config_inst_item_id):=tv_txn_line_dtls_tbl.COUNT;
1393
1394 --
1395 -- populate txn relations
1396 --
1397 IF t_txn_ii_rltns_tbl.COUNT > 0 THEN
1398 FOR k IN t_txn_ii_rltns_tbl.FIRST..t_txn_ii_rltns_tbl.LAST
1399 LOOP
1400
1401 IF t_txn_ii_rltns_tbl(k).sub_config_inst_hdr_id = t_txn_line_dtls_tbl(l).config_inst_hdr_id AND
1402 t_txn_ii_rltns_tbl(k).sub_config_inst_rev_num = t_txn_line_dtls_tbl(l).config_inst_rev_num AND
1403 t_txn_ii_rltns_tbl(k).sub_config_inst_item_id = t_txn_line_dtls_tbl(l).config_inst_item_id THEN
1404
1405 tv_txn_ii_rltns_tbl(tv_txn_ii_rltns_tbl.COUNT+1):=t_txn_ii_rltns_tbl(k);
1406
1407 END IF;
1408 END LOOP;
1409 END IF;
1410
1411 IF t_txn_eav_tbl.COUNT > 0 THEN
1412 FOR k IN t_txn_eav_tbl.FIRST..t_txn_eav_tbl.LAST
1413 LOOP
1414 IF t_txn_eav_tbl(k).txn_line_details_index = l AND
1415 t_attrib_hash(k) = t_txn_line_dtls_tbl(l).config_inst_item_id THEN
1419 END IF;
1416 tv_txn_eav_tbl(tv_txn_eav_tbl.COUNT+1) := t_txn_eav_tbl(k);
1417 tv_txn_eav_tbl(tv_txn_eav_tbl.COUNT).txn_line_details_index :=t_cfg_hash(t_txn_line_dtls_tbl(l).config_inst_item_id);
1418 tv_txn_eav_tbl(tv_txn_eav_tbl.COUNT).api_caller_identity :='CONFIG';
1420 END LOOP;
1421 END IF;
1422
1423 END IF;
1424
1425 END LOOP; -- end of loop through all txn line details
1426 END IF;
1427
1428
1429 ERROR_CODE:='00334';
1430
1431 -- csi_t_txn_details_grp.create_transaction_dtls --
1432 create_transaction_dtls
1433 (p_api_version => 1.0,
1434 p_commit => fnd_api.g_false,
1435 p_init_msg_list => fnd_api.g_true,
1436 p_validation_level => fnd_api.g_valid_level_full,
1437 px_txn_line_rec => v_txn_line,
1438 px_txn_line_detail_tbl => tv_txn_line_dtls_tbl,
1439 px_txn_party_detail_tbl => tv_txn_party_tbl,
1440 px_txn_pty_acct_detail_tbl => t_txn_pty_acct_tbl,
1441 px_txn_ii_rltns_tbl => ts_txn_ii_rltns_tbl,
1442 px_txn_org_assgn_tbl => t_txn_org_assgn_tbl,
1443 px_txn_ext_attrib_vals_tbl => tv_txn_eav_tbl,
1444 px_txn_systems_tbl => t_txn_systems_tbl,
1445 x_return_status => v_return_status,
1446 x_msg_count => v_msg_count,
1447 x_msg_data => v_msg_data);
1448
1449 -- bug #3646589
1450 tv_txn_eav_tbl.DELETE;
1451
1452 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
1453 x_run_id:=m_RUN_ID;
1454 LOG_REPORT(x_run_id,v_msg_data,v_msg_count);
1455 END IF;
1456
1457 --
1458 -- populate hash array config_item_id <=> txn line id
1459 --
1460 IF tv_txn_line_dtls_tbl.COUNT > 0 THEN
1461 FOR h IN tv_txn_line_dtls_tbl.FIRST..tv_txn_line_dtls_tbl.LAST
1462 LOOP
1463 p_hash_dtls_tbl(tv_txn_line_dtls_tbl(h).config_inst_item_id) := v_txn_line.transaction_line_id;
1464 END LOOP;
1465 END IF;
1466
1467 IF tv_txn_ii_rltns_tbl.COUNT > 0 THEN
1468 FOR h IN tv_txn_ii_rltns_tbl.FIRST..tv_txn_ii_rltns_tbl.LAST
1469 LOOP
1470 IF tv_txn_ii_rltns_tbl(h).obj_config_inst_item_id<>v_root_config_item_id THEN
1471 tv_txn_ii_rltns_tbl(h).transaction_line_id := v_txn_line.transaction_line_id;
1472 p_rltns_tbl(p_rltns_tbl.COUNT+1):=tv_txn_ii_rltns_tbl(h);
1473 END IF;
1474
1475 END LOOP;
1476 END IF;
1477
1478 END LOOP;
1479
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 x_run_id:=m_RUN_ID;
1483 DEBUG('ERROR_CODE='||ERROR_CODE||' '||SQLERRM);
1484 LOG_REPORT(x_run_id,v_msg_data);
1485 END populate_IB_Txn;
1486
1487
1488 FUNCTION get_Instance_Status
1489 (
1490 p_instance_hdr_id IN NUMBER,
1491 p_instance_rev_nbr IN NUMBER
1492 ) RETURN VARCHAR2 IS
1493 v_status VARCHAR2(255):='';
1494 BEGIN
1495 SELECT ext_activated_flag||config_delta
1496 INTO v_status
1497 FROM CZ_CONFIG_ITEMS
1498 WHERE instance_hdr_id = p_instance_hdr_id AND
1499 instance_rev_nbr = p_instance_rev_nbr AND
1500 component_instance_type='I' AND
1501 deleted_flag='0';
1502 LOG_REPORT(m_RUN_ID,'p_instance_hdr_id='||TO_CHAR(p_instance_hdr_id)||' changed_status='||v_status);
1503 RETURN v_status;
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 RETURN '';
1507 END get_Instance_Status;
1508
1509 /**
1510 * INSERT/UPDATE CZ data IN IB Transactions SCHEMA
1511 */
1512
1513 PROCEDURE Update_Instances
1514 (
1515 p_config_instance_tbl IN SYSTEM.cz_config_instance_tbl_type,
1516 p_effective_date IN DATE,
1517 p_txn_type_id IN NUMBER,
1518 x_run_id OUT NOCOPY NUMBER
1519 ) IS
1520 t_rltns_tbl txn_ii_rltns_tbl;
1521 t_enddated_rltns_tbl txn_ii_rltns_tbl;
1522 v_return_status VARCHAR2(255) := fnd_api.g_ret_sts_success;
1523 v_transaction_line_id NUMBER;
1524 v_msg_data VARCHAR2(2000);
1525 v_msg_count NUMBER;
1526 v_run_id NUMBER;
1527 v_txn_type_id NUMBER:=CZ_TRANSACTION_TYPE_ID;
1528 v_baseline_rev_nbr NUMBER;
1529 v_target_hdr_id NUMBER;
1530 v_target_rev_nbr NUMBER;
1531 v_target_config_item_id NUMBER;
1532 v_txn_exp_line_id NUMBER;
1533 v_cfg_item_id NUMBER;
1534 v_ind NUMBER;
1535 v_target_item_flag VARCHAR2(255);
1536 v_status VARCHAR2(255);
1537
1538 t_config_item_tbl int_array_tbl_type;
1539 t_cfg_item_hash_tbl1 int_array_tbl_type_idx_vc2; -- Bug 6892148;
1540 t_cfg_item_hash_tbl2 int_array_tbl_type_idx_vc2; -- Bug 6892148;
1544 t_hash_expired_hdrs int_array_tbl_type;
1541 t_passed_targets_rev_tbl int_array_tbl_type_idx_vc2; -- Bug 6892148;
1542 t_hash_expired_roots int_array_tbl_type;
1543 t_hash_expired_revs int_array_tbl_type;
1545 t_hash_dtls_tbl int_array_tbl_type_idx_vc2;-- Bug 6892148;
1546 t_expired_rltns_tbl txn_ii_rltns_tbl;
1547 t_notexpired_rltns_tbl txn_ii_rltns_tbl;
1548
1549 BEGIN
1550 x_run_id:=0;
1551 Initialize(p_effective_date);
1552
1553 IF p_config_instance_tbl.COUNT = 0 THEN
1554 RETURN;
1555 END IF;
1556
1557 IF (p_txn_type_id IS NULL OR p_txn_type_id=-1) THEN
1558 v_txn_type_id := CZ_TRANSACTION_TYPE_ID;
1559 END IF;
1560
1561 FOR i IN p_config_instance_tbl.FIRST..p_config_instance_tbl.LAST
1562 LOOP
1563 t_config_item_tbl.DELETE;
1564
1565 v_status:=get_Instance_Status(p_config_instance_tbl(i).config_hdr_id,
1566 p_config_instance_tbl(i).new_config_rev_nbr);
1567
1568 --
1569 -- '00' means bitmap mask EXT_ACTIVATED||CONFIG_DELTA
1570 --
1571 IF v_status NOT IN ('00','000','0000')THEN
1572
1573 SELECT config_item_id
1574 BULK COLLECT INTO t_config_item_tbl
1575 FROM CZ_CONFIG_ITEMS_V
1576 WHERE instance_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
1577 instance_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND
1578 ib_trackable='1';
1579
1580 IF t_config_item_tbl.COUNT > 0 THEN
1581 FOR k IN t_config_item_tbl.FIRST..t_config_item_tbl.LAST
1582 LOOP
1583 --
1584 -- populate hash arrays
1585 --
1586 t_cfg_item_hash_tbl1(t_config_item_tbl(k)):=p_config_instance_tbl(i).config_hdr_id;
1587 t_cfg_item_hash_tbl2(t_config_item_tbl(k)):=p_config_instance_tbl(i).new_config_rev_nbr;
1588 END LOOP;
1589 END IF;
1590
1591 END IF;
1592 END LOOP;
1593
1594 FOR i IN p_config_instance_tbl.FIRST..p_config_instance_tbl.LAST
1595 LOOP
1596
1597 SELECT baseline_rev_nbr INTO v_baseline_rev_nbr
1598 FROM CZ_CONFIG_HDRS
1599 WHERE config_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
1600 config_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND deleted_flag=NO_FLAG;
1601
1602 --
1603 -- collect all Connectors that we are passing
1604 --
1605 FOR k IN (SELECT target_hdr_id,target_rev_nbr,target_config_item_id,config_item_id
1606 FROM CZ_CONFIG_ITEMS_V
1607 WHERE instance_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
1608 instance_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND
1609 target_config_item_id IS NOT NULL)
1610 LOOP
1611 BEGIN
1612 IF v_baseline_rev_nbr IS NOT NULL THEN
1613 SELECT target_hdr_id,target_rev_nbr,target_config_item_id
1614 INTO v_target_hdr_id,v_target_rev_nbr,v_target_config_item_id
1615 FROM CZ_CONFIG_ITEMS_V
1616 WHERE instance_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
1617 instance_rev_nbr=v_baseline_rev_nbr AND config_item_id=k.config_item_id;
1618
1619
1620 IF t_cfg_item_hash_tbl1.EXISTS(v_target_config_item_id) THEN
1621
1622
1623 t_passed_targets_rev_tbl(v_target_config_item_id) := t_cfg_item_hash_tbl2(v_target_config_item_id);
1624
1625
1626 END IF;
1627
1628 END IF;
1629
1630 EXCEPTION
1631 WHEN OTHERS THEN
1632 NULL;
1633 END;
1634 END LOOP;
1635
1636 END LOOP;
1637
1638 FOR i IN p_config_instance_tbl.FIRST..p_config_instance_tbl.LAST
1639 LOOP
1640 v_status:=get_Instance_Status(p_config_instance_tbl(i).config_hdr_id,
1641 p_config_instance_tbl(i).new_config_rev_nbr);
1642
1643 --
1644 -- '00' means bitmap mask EXT_ACTIVATED||CONFIG_DELTA
1645 --
1646 IF v_status NOT IN ('00','000','0000')THEN
1647 IF p_config_instance_tbl(i).old_config_rev_nbr = p_config_instance_tbl(i).new_config_rev_nbr THEN
1648 remove_IB_Config
1649 (p_session_config_hdr_id => NULL,
1650 p_session_config_rev_nbr => NULL,
1651 p_instance_hdr_id => p_config_instance_tbl(i).config_hdr_id,
1652 p_instance_rev_nbr => p_config_instance_tbl(i).old_config_rev_nbr,
1653 p_instance_item_id => p_config_instance_tbl(i).config_item_id,
1654 x_run_id => x_run_id);
1655 END IF;
1656 populate_IB_Txn(p_config_hdr_id => p_config_instance_tbl(i).config_hdr_id,
1657 p_config_rev_nbr => p_config_instance_tbl(i).new_config_rev_nbr,
1658 p_config_item_id => p_config_instance_tbl(i).config_item_id,
1659 p_hash_dtls_tbl => t_hash_dtls_tbl,
1660 p_passed_targets_rev_tbl => t_passed_targets_rev_tbl,
1661 p_rltns_tbl => t_rltns_tbl,
1662 p_enddated_rltns_tbl => t_enddated_rltns_tbl,
1666 END IF;
1663 p_txn_type_id => v_txn_type_id,
1664 p_item_status => v_status,
1665 x_run_id => x_run_id);
1667 END LOOP;
1668
1669 --
1670 -- create relatioships only when txn lines/details already created
1671 --
1672 IF t_rltns_tbl.COUNT>0 THEN
1673 FOR t IN t_rltns_tbl.FIRST..t_rltns_tbl.LAST
1674 LOOP
1675 --
1676 -- check the target item
1677 --
1678
1679 IF t_rltns_tbl(t).relationship_type_code = CONNECTED_TO_RELATIONSHIP AND
1680 get_Instance_Status(t_rltns_tbl(t).obj_config_inst_hdr_id,
1681 t_rltns_tbl(t).obj_config_inst_rev_num) IN ('00','000','0000') THEN
1682
1683 v_baseline_rev_nbr:=t_rltns_tbl(t).obj_config_inst_rev_num;
1684
1685 BEGIN
1686 SELECT baseline_rev_nbr INTO v_baseline_rev_nbr FROM CZ_CONFIG_HDRS
1687 WHERE config_hdr_id=t_rltns_tbl(t).obj_config_inst_hdr_id AND
1688 config_rev_nbr=t_rltns_tbl(t).obj_config_inst_rev_num AND
1689 deleted_flag=NO_FLAG;
1690 LOG_REPORT(m_RUN_ID,'baseline_rev_nbr = '||TO_CHAR(v_baseline_rev_nbr)||
1691 ' instance_hdr_id='||TO_CHAR(t_rltns_tbl(t).obj_config_inst_hdr_id)||' and instance_rev_nbr='||
1692 TO_CHAR(t_rltns_tbl(t).obj_config_inst_rev_num));
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 LOG_REPORT(m_RUN_ID,'Error : there is no baseline revision for the instance with '||
1697 'instance_hdr_id='||TO_CHAR(t_rltns_tbl(t).obj_config_inst_hdr_id)||' and instance_rev_nbr='||
1698 TO_CHAR(t_rltns_tbl(t).obj_config_inst_rev_num));
1699 x_run_id:=m_RUN_ID;
1700 END;
1701
1702 IF v_baseline_rev_nbr IS NOT NULL THEN
1703 t_rltns_tbl(t).obj_config_inst_rev_num := v_baseline_rev_nbr;
1704 END IF;
1705 END IF;
1706
1707 END LOOP;
1708 END IF;
1709
1710 --
1711 -- create expired CONNECTED_TO relationships first
1712 --
1713 IF t_enddated_rltns_tbl.COUNT > 0 THEN
1714
1715 FOR n IN t_rltns_tbl.FIRST..t_rltns_tbl.LAST
1716 LOOP
1717 FOR m IN t_enddated_rltns_tbl.FIRST..t_enddated_rltns_tbl.LAST
1718 LOOP
1719 IF t_enddated_rltns_tbl(m).relationship_type_code = CONNECTED_TO_RELATIONSHIP AND
1720 t_rltns_tbl(n).relationship_type_code = CONNECTED_TO_RELATIONSHIP THEN
1721 IF t_enddated_rltns_tbl(m).sub_config_inst_hdr_id = t_rltns_tbl(n).sub_config_inst_hdr_id AND
1722 t_enddated_rltns_tbl(m).sub_config_inst_rev_num = t_rltns_tbl(n).sub_config_inst_rev_num AND
1723 t_enddated_rltns_tbl(m).sub_config_inst_item_id = t_rltns_tbl(n).sub_config_inst_item_id THEN
1724
1725 t_enddated_rltns_tbl(m).transaction_line_id := t_rltns_tbl(n).transaction_line_id;
1726
1727 END IF;
1728 END IF;
1729 END LOOP;
1730 END LOOP;
1731
1732 -- csi_t_txn_rltnshps_grp.create_txn_ii_rltns_dtls --
1733 create_txn_ii_rltns_dtls
1734 (
1735 p_api_version => 1.0,
1736 p_commit => fnd_api.g_false,
1737 p_init_msg_list => fnd_api.g_true,
1738 p_validation_level => fnd_api.g_valid_level_full,
1739 px_txn_ii_rltns_tbl => t_enddated_rltns_tbl,
1740 x_return_status => v_return_status,
1741 x_msg_count => v_msg_count,
1742 x_msg_data => v_msg_data
1743 );
1744
1745 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
1746 x_run_id:=m_RUN_ID;
1747 LOG_REPORT(x_run_id,'DISCONTINUED CONNECTORS FAILED.');
1748 LOG_REPORT(x_run_id,v_msg_data,v_msg_count);
1749 END IF;
1750 END IF;
1751
1752 --
1753 -- create other expired relationships
1754 --
1755 IF t_rltns_tbl.COUNT > 0 THEN
1756
1757 --
1758 -- collect all expired COMPONENT-OF relationships
1759 -- into a separate array
1760 -- ( this is because of requirement from IB API - call
1761 -- create_relationships API for expired relationships FIRST
1762 --
1763 FOR n IN t_rltns_tbl.FIRST..t_rltns_tbl.LAST
1764 LOOP
1765 IF NOT(t_rltns_tbl(n).active_end_date IS NULL OR
1766 t_rltns_tbl(n).active_end_date=FND_API.G_MISS_DATE) THEN
1767 t_expired_rltns_tbl(t_expired_rltns_tbl.COUNT+1):=t_rltns_tbl(n);
1768 END IF;
1769 END LOOP;
1770
1771 IF t_expired_rltns_tbl.COUNT>0 THEN
1772
1773 FOR nn IN t_expired_rltns_tbl.FIRST..t_expired_rltns_tbl.LAST
1774 LOOP
1775 BEGIN
1776 v_txn_exp_line_id := NULL;
1777
1778 v_cfg_item_id := t_expired_rltns_tbl(nn).sub_config_inst_item_id;
1779
1780 -- find the nearest item which has a corresponding txn line
1781 FOR t IN (SELECT config_item_id FROM cz_config_items
1782 WHERE config_item_id<>v_cfg_item_id
1783 START WITH config_item_id=v_cfg_item_id AND
1787 instance_hdr_id=t_expired_rltns_tbl(nn).sub_config_inst_hdr_id AND
1784 instance_hdr_id=t_expired_rltns_tbl(nn).sub_config_inst_hdr_id AND
1785 instance_rev_nbr=t_expired_rltns_tbl(nn).sub_config_inst_rev_num
1786 CONNECT BY PRIOR parent_config_item_id=config_item_id AND
1788 instance_rev_nbr=t_expired_rltns_tbl(nn).sub_config_inst_rev_num)
1789 LOOP
1790 IF t_hash_dtls_tbl.EXISTS(t.config_item_id) THEN
1791 v_txn_exp_line_id := t_hash_dtls_tbl(t.config_item_id);
1792 EXIT;
1793 END IF;
1794 END LOOP;
1795
1796 IF v_txn_exp_line_id IS NULL AND t_hash_dtls_tbl.EXISTS(v_cfg_item_id) THEN
1797 v_txn_exp_line_id := t_hash_dtls_tbl(v_cfg_item_id);
1798 END IF;
1799
1800 t_expired_rltns_tbl(nn).transaction_line_id := v_txn_exp_line_id;
1801
1802 LOG_REPORT(m_RUN_ID,'Relationship for instance_hdr_id/instance_rev_nbr/config_item_id='||to_char(t_expired_rltns_tbl(nn).sub_config_inst_hdr_id)||
1803 '/'||to_char(t_expired_rltns_tbl(nn).sub_config_inst_rev_num)||'/'||to_char(v_cfg_item_id)||' has txn_line_id='||to_char(v_txn_exp_line_id));
1804
1805 EXCEPTION
1806 WHEN NO_DATA_FOUND THEN
1807 NULL;
1808 WHEN OTHERS THEN
1809 LOG_REPORT(m_RUN_ID,'Internal Error : '||SQLERRM);
1810 END;
1811 END LOOP; -- end of nn loop --
1812
1813
1814 -- csi_t_txn_rltnshps_grp.create_txn_ii_rltns_dtls --
1815 create_txn_ii_rltns_dtls
1816 (
1817 p_api_version => 1.0,
1818 p_commit => fnd_api.g_false,
1819 p_init_msg_list => fnd_api.g_true,
1820 p_validation_level => fnd_api.g_valid_level_full,
1821 px_txn_ii_rltns_tbl => t_expired_rltns_tbl,
1822 x_return_status => v_return_status,
1823 x_msg_count => v_msg_count,
1824 x_msg_data => v_msg_data
1825 );
1826
1827 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
1828 x_run_id:=m_RUN_ID;
1829 LOG_REPORT(x_run_id,'CREATING EXPIRED RELATIONSHIPS FAILED...');
1830 LOG_REPORT(x_run_id,v_msg_data,v_msg_count);
1831 ELSE
1832 LOG_REPORT(m_RUN_ID,'CREATING EXPIRED RELATIONSHIPS PASSED...');
1833 END IF;
1834
1835 END IF; -- end of if t_expired_rltns_tbl.COUNT>0 statement --
1836
1837 END IF;
1838
1839 --
1840 -- create nonexpired relationships
1841 --
1842
1843 IF t_rltns_tbl.COUNT > 0 THEN
1844
1845 FOR n IN t_rltns_tbl.FIRST..t_rltns_tbl.LAST
1846 LOOP
1847 IF (t_rltns_tbl(n).active_end_date IS NULL OR
1848 t_rltns_tbl(n).active_end_date=FND_API.G_MISS_DATE) THEN
1849 t_notexpired_rltns_tbl(t_notexpired_rltns_tbl.COUNT+1):=t_rltns_tbl(n);
1850 END IF;
1851 END LOOP;
1852
1853 IF t_notexpired_rltns_tbl.COUNT > 0 THEN
1854
1855 -- csi_t_txn_rltnshps_grp.create_txn_ii_rltns_dtls --
1856 create_txn_ii_rltns_dtls
1857 (
1858 p_api_version => 1.0,
1859 p_commit => fnd_api.g_false,
1860 p_init_msg_list => fnd_api.g_true,
1861 p_validation_level => fnd_api.g_valid_level_full,
1862 px_txn_ii_rltns_tbl => t_notexpired_rltns_tbl,
1863 x_return_status => v_return_status,
1864 x_msg_count => v_msg_count,
1865 x_msg_data => v_msg_data
1866 );
1867
1868 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
1869 x_run_id:=m_RUN_ID;
1870 LOG_REPORT(x_run_id,'CREATING NEW RELATIONSHIPS FAILED...');
1871 LOG_REPORT(x_run_id,v_msg_data,v_msg_count);
1872 ELSE
1873 LOG_REPORT(m_RUN_ID,'CREATING NEW RELATIONSHIPS PASSED...');
1874 END IF;
1875
1876 END IF;
1877
1878 END IF;
1879
1880 LOG_REPORT(m_RUN_ID,'FINAL STATUS : RUN_ID='||TO_CHAR(x_run_id));
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 DEBUG(SQLERRM);
1885 x_run_id:=m_RUN_ID;
1886 LOG_REPORT(x_run_id,'Internal Error (ERROR_CODE='||ERROR_CODE||') : '||SQLERRM);
1887 END Update_Instances;
1888
1889 PROCEDURE update_CSI_Item_Inst_Status
1890 (p_config_hdr_id IN NUMBER,
1891 p_config_rev_nbr IN NUMBER,
1892 p_config_status IN VARCHAR2,
1893 x_run_id OUT NOCOPY NUMBER) IS
1894
1895 BEGIN
1896
1897 EXECUTE IMMEDIATE
1898 'DECLARE ' ||
1899 ' v_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC; ' ||
1900 ' v_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL; ' ||
1901 ' v_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL; ' ||
1902 ' v_party_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL; ' ||
1903 ' v_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL; ' ||
1904 ' v_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL; ' ||
1908 ' v_instance_query_rec CSI_DATASTRUCTURES_PUB.instance_query_rec; ' ||
1905 ' v_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL; ' ||
1906 ' v_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC; ' ||
1907 ' x_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL; ' ||
1909 ' v_party_query_rec CSI_DATASTRUCTURES_PUB.party_query_rec; ' ||
1910 ' v_account_query_rec CSI_DATASTRUCTURES_PUB.party_account_query_rec; ' ||
1911 ' x_instance_header_tbl CSI_DATASTRUCTURES_PUB.instance_header_tbl; ' ||
1912 ' x_return_status VARCHAR2(2000); ' ||
1913 ' x_msg_count NUMBER; ' ||
1914 ' x_msg_data VARCHAR2(2000); ' ||
1915 'BEGIN ' ||
1916 ' v_instance_query_rec.config_inst_hdr_id := :1; ' ||
1917 ' v_instance_query_rec.config_inst_rev_num := :2; ' ||
1918 ' csi_item_instance_pub.get_item_instances ' ||
1919 ' ( ' ||
1920 ' p_api_version => 1.0, ' ||
1921 ' p_commit => ''F'', ' ||
1922 ' p_init_msg_list => ''F'', ' ||
1923 ' p_validation_level => 100, ' ||
1924 ' p_instance_query_rec => v_instance_query_rec, ' ||
1925 ' p_party_query_rec => v_party_query_rec, ' ||
1926 ' p_account_query_rec => v_account_query_rec, ' ||
1927 ' p_transaction_id => NULL, ' ||
1928 ' p_resolve_id_columns => ''F'', ' ||
1929 ' p_active_instance_only => ''F'', ' ||
1930 ' x_instance_header_tbl => x_instance_header_tbl, ' ||
1931 ' x_return_status => x_return_status, ' ||
1932 ' x_msg_count => x_msg_count, ' ||
1933 ' x_msg_data => x_msg_data ' ||
1934 ' ); ' ||
1935 ' FOR i IN x_instance_header_tbl.First..x_instance_header_tbl.Last ' ||
1936 ' LOOP ' ||
1937 ' v_instance_rec.instance_id := x_instance_header_tbl(i).instance_id; ' ||
1938 ' v_instance_rec.object_version_number := x_instance_header_tbl(i).object_version_number; ' ||
1939 ' v_instance_rec.CONFIG_VALID_STATUS := :3; ' ||
1940 ' v_txn_rec.transaction_id := NULL; ' ||
1941 ' v_txn_rec.transaction_date := sysdate; ' ||
1942 ' v_txn_rec.source_transaction_date := sysdate; ' ||
1943 ' v_txn_rec.transaction_type_id := 401; ' ||
1944 ' csi_item_instance_pub.update_item_instance( ' ||
1945 ' p_api_version => 1.0, ' ||
1946 ' p_commit => ''F'', ' ||
1947 ' p_init_msg_list => ''F'', ' ||
1948 ' p_validation_level => 100, ' ||
1949 ' p_instance_rec => v_instance_rec, ' ||
1950 ' p_ext_attrib_values_tbl => v_ext_attrib_values_tbl, ' ||
1951 ' p_party_tbl => v_party_tbl, ' ||
1952 ' p_account_tbl => v_party_account_tbl, ' ||
1953 ' p_pricing_attrib_tbl => v_pricing_attrib_tbl, ' ||
1954 ' p_org_assignments_tbl => v_org_assignments_tbl, ' ||
1955 ' p_asset_assignment_tbl => v_asset_assignment_tbl, ' ||
1956 ' p_txn_rec => v_txn_rec, ' ||
1957 ' x_instance_id_lst => x_instance_id_lst, ' ||
1958 ' x_return_status => CZ_IB_TRANSACTIONS.m_return_status, ' ||
1959 ' x_msg_count => CZ_IB_TRANSACTIONS.m_msg_count, ' ||
1960 ' x_msg_data => CZ_IB_TRANSACTIONS.m_msg_data); ' ||
1961 ' END LOOP; ' ||
1962 'END;' USING p_config_hdr_id, p_config_rev_nbr, p_config_status;
1963
1964 IF (CZ_IB_TRANSACTIONS.m_return_status <> fnd_api.g_ret_sts_success) THEN
1965 x_run_id:=m_RUN_ID;
1966 LOG_REPORT(m_RUN_ID,'csi_item_instance_pub.update_item_instance() failed : '||CZ_IB_TRANSACTIONS.m_msg_data,CZ_IB_TRANSACTIONS.m_msg_count);
1967 END IF;
1968
1969 END update_CSI_Item_Inst_Status;
1970
1971 /**
1972 * The method will UPDATE the status OF the IB instance
1973 * <=> CSI_T_TRANSACTION_LINES.CONFIG_VALID_STATUS / CSI_ITEM_INSTANCES.CONFIG_VALID_STATUS TO be INVALID
1974 * IF either the CZ_CONFIG_HDRS.config_status field IS SET TO INCOMPLETE OR
1975 * the CZ_CONFIG_HDRS.has_failures field IS SET TO TRUE, otherwise, it will be SET TO VALID
1976 */
1977 PROCEDURE Update_Instances_Status
1978 (
1979 p_config_instance_tbl IN SYSTEM.cz_config_instance_tbl_type,
1980 x_run_id OUT NOCOPY NUMBER
1981 ) IS
1982 v_config_status VARCHAR2(255);
1983 v_return_status VARCHAR2(255);
1984 v_msg_count NUMBER;
1985 v_msg_data VARCHAR2(32000);
1986
1987 BEGIN
1988 x_run_id:=0;
1989 Initialize(SYSDATE);
1990 IF p_config_instance_tbl.COUNT = 0 THEN
1991 RETURN;
1992 END IF;
1993
1994 FOR i IN p_config_instance_tbl.FIRST..p_config_instance_tbl.LAST
1995 LOOP
1996 FOR k IN (SELECT config_status,has_failures FROM cz_config_hdrs
1997 WHERE config_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
1998 config_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND deleted_flag=NO_FLAG)
1999 LOOP
2000 IF (k.config_status=INCOMPLETE_CONFIG_STATUS OR k.has_failures=YES_FLAG) THEN
2001 v_config_status:=NO_FLAG;
2002 ELSE
2003 v_config_status:=YES_FLAG;
2004 END IF;
2005
2006 EXECUTE IMMEDIATE
2007 'BEGIN ' ||
2008 ' UPDATE CSI_T_TRANSACTION_LINES ' ||
2009 ' SET config_valid_status = '||v_config_status ||
2010 ' WHERE (config_session_hdr_id,config_session_rev_num) IN ' ||
2014 ' END;' USING p_config_instance_tbl(i).config_hdr_id,p_config_instance_tbl(i).old_config_rev_nbr,p_config_instance_tbl(i).new_config_rev_nbr;
2011 ' (SELECT DISTINCT config_hdr_id,config_rev_nbr FROM CZ_CONFIG_ITEMS ' ||
2012 ' WHERE instance_hdr_id=:1 AND ' ||
2013 ' instance_rev_nbr IN(:2,:3) AND deleted_flag=''0''); ' ||
2015
2016 update_CSI_Item_Inst_Status(p_config_instance_tbl(i).config_hdr_id,
2017 p_config_instance_tbl(i).old_config_rev_nbr,
2018 v_config_status,
2019 x_run_id);
2020 END LOOP;
2021 END LOOP;
2022
2023 EXCEPTION
2024 WHEN OTHERS THEN
2025 DEBUG(SQLERRM);
2026 x_run_id:=m_RUN_ID;
2027 LOG_REPORT(x_run_id, 'Internal Error (ERROR_CODE='||ERROR_CODE||') : '||SQLERRM);
2028 END Update_Instances_Status;
2029
2030 PROCEDURE get_Last_CSI_Revisions
2031 (p_config_inst_hdr_id IN NUMBER,
2032 x_csi_nbr_tbl OUT NOCOPY int_array_tbl_type_idx_vc2) IS-- Bug 6892148;
2033
2034 BEGIN
2035
2036 EXECUTE IMMEDIATE
2037 'BEGIN ' ||
2038 ' FOR i IN(SELECT config_inst_item_id,config_inst_rev_num FROM CSI_ITEM_INSTANCES ' ||
2039 ' WHERE config_inst_hdr_id=:1 ' ||
2040 ' ORDER BY config_inst_item_id,config_inst_rev_num) ' ||
2041 ' LOOP ' ||
2042 ' CZ_IB_TRANSACTIONS.m_csi_rev_nbr_tbl(i.config_inst_item_id) := i.config_inst_rev_num; ' ||
2043 ' END LOOP; ' ||
2044 ' END;'
2045 USING p_config_inst_hdr_id;
2046
2047 x_csi_nbr_tbl := m_csi_rev_nbr_tbl;
2048
2049 m_csi_rev_nbr_tbl.DELETE;
2050
2051 END get_Last_CSI_Revisions;
2052
2053 /**
2054 * return instance_description and location_id for a given item and revision
2055 */
2056 PROCEDURE get_InstDesc_LocId
2057 (
2058 p_config_hdr_id IN NUMBER,
2059 p_config_rev_nbr IN NUMBER,
2060 p_config_item_id IN NUMBER,
2061 x_instance_description OUT NOCOPY VARCHAR2,
2062 x_location_id OUT NOCOPY NUMBER,
2063 x_location_type_code OUT NOCOPY VARCHAR2
2064 ) IS
2065 BEGIN
2066
2067 EXECUTE IMMEDIATE
2068 'SELECT instance_description, location_id, location_type_code ' ||
2069 ' FROM CSI_ITEM_INSTANCES ' ||
2070 ' WHERE config_inst_hdr_id = :1 AND ' ||
2071 ' config_inst_rev_num = :2 AND config_inst_item_id=:3'
2072 INTO x_instance_description, x_location_id, x_location_type_code
2073 USING p_config_hdr_id, p_config_rev_nbr, p_config_item_id;
2074
2075 END get_InstDesc_LocId;
2076
2077 /**
2078 * retreive attributes data for installed item
2079 */
2080 PROCEDURE get_Installed_Attributes_Data
2081 (
2082 p_config_hdr_id IN NUMBER,
2083 p_config_rev_nbr IN NUMBER,
2084 p_config_item_id IN NUMBER,
2085 x_attribute_category_tbl OUT NOCOPY long_char_array_tbl_type,
2086 x_attribute_name_tbl OUT NOCOPY long_char_array_tbl_type,
2087 x_attribute_value_tbl OUT NOCOPY long_char_array_tbl_type
2088 ) IS
2089
2090 BEGIN
2091
2092 m_attribute_category_tbl.DELETE;
2093 m_attribute_name_tbl.DELETE;
2094 m_attribute_value_tbl.DELETE;
2095
2096 EXECUTE IMMEDIATE
2097 'BEGIN ' ||
2098 ' SELECT ' ||
2099 ' a.attribute_category,a.attribute_code,b.attribute_value ' ||
2100 ' BULK COLLECT INTO ' ||
2101 ' CZ_IB_TRANSACTIONS.m_attribute_category_tbl,CZ_IB_TRANSACTIONS.m_attribute_name_tbl, ' ||
2102 ' CZ_IB_TRANSACTIONS.m_attribute_value_tbl ' ||
2103 ' FROM CSI_I_EXTENDED_ATTRIBS a, CSI_IEA_VALUES b, CSI_ITEM_INSTANCES c ' ||
2104 ' WHERE a.attribute_id = b.attribute_id AND ' ||
2105 ' b.instance_id = c.instance_id AND ' ||
2106 ' c.config_inst_hdr_id = :1 AND ' ||
2107 ' c.config_inst_rev_num = :2 AND ' ||
2108 ' c.config_inst_item_id = :3; ' ||
2109 ' END;'
2110 USING p_config_hdr_id, p_config_rev_nbr, p_config_item_id;
2111
2112 x_attribute_category_tbl := m_attribute_category_tbl;
2113 x_attribute_name_tbl := m_attribute_name_tbl;
2114 x_attribute_value_tbl := m_attribute_value_tbl;
2115
2116 m_attribute_category_tbl.DELETE;
2117 m_attribute_name_tbl.DELETE;
2118 m_attribute_value_tbl.DELETE;
2119
2120 END get_Installed_Attributes_Data;
2121
2122 /**
2123 * retreive installed attribute value
2124 */
2125 PROCEDURE get_Installed_Attribute_Value
2126 (
2127 p_config_hdr_id IN NUMBER,
2128 p_config_rev_nbr IN NUMBER,
2129 p_config_item_id IN NUMBER,
2130 p_attribute_name IN VARCHAR2,
2131 x_attribute_value OUT NOCOPY VARCHAR2
2132 ) IS
2133
2134 BEGIN
2135
2136 EXECUTE IMMEDIATE
2137 'SELECT ' ||
2138 ' b.attribute_value ' ||
2139 ' FROM CSI_I_EXTENDED_ATTRIBS a, CSI_IEA_VALUES b, CSI_ITEM_INSTANCES c ' ||
2140 ' WHERE a.attribute_id = b.attribute_id AND ' ||
2141 ' b.instance_id = c.instance_id AND ' ||
2142 ' c.config_inst_hdr_id = :1 AND ' ||
2143 ' c.config_inst_rev_num = :2 AND ' ||
2144 ' c.config_inst_item_id = :3 AND ' ||
2145 ' a.attribute_code = :4'
2146 INTO x_attribute_value
2147 USING p_config_hdr_id, p_config_rev_nbr, p_config_item_id, p_attribute_name;
2148
2149 EXCEPTION
2150 WHEN NO_DATA_FOUND THEN
2151 NULL;
2152 END get_Installed_Attribute_Value;
2153
2154 /**
2158 (
2155 * retreive attributes data for installed item
2156 */
2157 PROCEDURE get_Txn_Attributes_Data
2159 p_config_hdr_id IN NUMBER,
2160 p_config_rev_nbr IN NUMBER,
2161 p_config_item_id IN NUMBER,
2162 x_attribute_category_tbl OUT NOCOPY long_char_array_tbl_type,
2163 x_attribute_name_tbl OUT NOCOPY long_char_array_tbl_type,
2164 x_attribute_value_tbl OUT NOCOPY long_char_array_tbl_type
2165 ) IS
2166
2167 BEGIN
2168
2169 m_attribute_category_tbl.DELETE;
2170 m_attribute_name_tbl.DELETE;
2171 m_attribute_value_tbl.DELETE;
2172
2173 EXECUTE IMMEDIATE
2174 'BEGIN ' ||
2175 ' SELECT ' ||
2176 ' attribute_category, attribute_code, attribute_value ' ||
2177 ' BULK COLLECT INTO ' ||
2178 ' CZ_IB_TRANSACTIONS.m_attribute_category_tbl,CZ_IB_TRANSACTIONS.m_attribute_name_tbl, ' ||
2179 ' CZ_IB_TRANSACTIONS.m_attribute_value_tbl ' ||
2180 ' FROM CSI_T_EXTEND_ATTRIBS_V a ' ||
2181 ' WHERE txn_line_detail_id IN ' ||
2182 ' (SELECT txn_line_detail_id ' ||
2183 ' FROM CSI_T_TXN_LINE_DETAILS ' ||
2184 ' WHERE config_inst_hdr_id = :1 AND ' ||
2185 ' config_inst_rev_num=:2 AND config_inst_item_id=:3); ' ||
2186 ' END;'
2187 USING p_config_hdr_id, p_config_rev_nbr, p_config_item_id;
2188
2189 x_attribute_category_tbl := m_attribute_category_tbl;
2190 x_attribute_name_tbl := m_attribute_name_tbl;
2191 x_attribute_value_tbl := m_attribute_value_tbl;
2192
2193 m_attribute_category_tbl.DELETE;
2194 m_attribute_name_tbl.DELETE;
2195 m_attribute_value_tbl.DELETE;
2196
2197 END get_Txn_Attributes_Data;
2198
2199
2200 /**
2201 * retreive attributes data from CZ ext attributes
2202 */
2203 PROCEDURE get_CZ_Attributes_Data
2204 (
2205 p_config_hdr_id IN NUMBER,
2206 p_config_rev_nbr IN NUMBER,
2207 p_config_item_id IN NUMBER,
2208 x_attribute_category_tbl OUT NOCOPY long_char_array_tbl_type,
2209 x_attribute_name_tbl OUT NOCOPY long_char_array_tbl_type,
2210 x_attribute_value_tbl OUT NOCOPY long_char_array_tbl_type
2211 ) IS
2212
2213 BEGIN
2214
2215 SELECT
2216 attribute_group, attribute_name, attribute_value
2217 BULK COLLECT INTO
2218 x_attribute_category_tbl, x_attribute_name_tbl, x_attribute_value_tbl
2219 FROM CZ_CONFIG_EXT_ATTRIBUTES
2220 WHERE config_hdr_id = p_config_hdr_id AND
2221 config_rev_nbr = p_config_rev_nbr AND
2222 config_item_id = p_config_item_id AND deleted_flag='0';
2223
2224 END get_CZ_Attributes_Data;
2225
2226 /**
2227 * Return array OF attributes OF config items FROM subtree that starts WITH
2228 * config item (p_config_hdr_id,p_config_rev_nbr,p_config_item_id)
2229 */
2230 PROCEDURE Synchronize_Attributes
2231 (
2232 p_config_hdr_id IN NUMBER,
2233 p_config_rev_nbr IN NUMBER,
2234 p_install_rev_nbr IN NUMBER,
2235 p_config_item_id IN NUMBER,
2236 x_config_attribute_tbl OUT NOCOPY SYSTEM.cz_config_attribute_tbl_type,
2237 x_txn_params_tbl OUT NOCOPY SYSTEM.cz_txn_params_tbl_type,
2238 x_run_id OUT NOCOPY INTEGER
2239 ) IS
2240
2241 t_config_hdr_tbl int_array_tbl_type;
2242 t_config_rev_nbr_tbl int_array_tbl_type;
2243 t_config_item_tbl int_array_tbl_type;
2244 t_location_id_tbl int_array_tbl_type;
2245 t_attribute_category_tbl long_char_array_tbl_type;
2246 t_attribute_name_tbl long_char_array_tbl_type;
2247 t_attribute_value_tbl long_char_array_tbl_type;
2248 t_instance_description_tbl long_char_array_tbl_type;
2249 t_csi_nbr_tbl int_array_tbl_type_idx_vc2; -- Bug 6892148;
2250 l_install_attribute_value VARCHAR2(4000);
2251 l_instance_description VARCHAR2(4000);
2252 l_location_id NUMBER;
2253 l_location_type_code VARCHAR2(4000);
2254 l_Item_Is_Installed BOOLEAN;
2255 l_Item_Exists_In_Txn BOOLEAN;
2256 l_last_item_rev NUMBER;
2257 l_attr_counter NUMBER;
2258 l_index NUMBER;
2259
2260 BEGIN
2261
2262 x_run_id:=0;
2263 Initialize(SYSDATE);
2264
2265 ERROR_CODE := '0001';
2266
2267 -- initialize global arrays which are used in dynamic sql blocks
2268 m_config_hdr_tbl.DELETE;m_config_rev_nbr_tbl.DELETE;m_config_item_tbl.DELETE;
2269 m_instance_description_tbl.DELETE;m_location_id_tbl.DELETE;
2270
2271 -- collect config_item_ids for last saved revision ( = p_config_rev_nbr )
2272 EXECUTE IMMEDIATE
2273 'BEGIN ' ||
2274 'SELECT ' ||
2275 ' config_item_id ' ||
2276 'BULK COLLECT INTO ' ||
2277 ' CZ_IB_TRANSACTIONS.m_config_item_tbl ' ||
2278 'FROM CZ_CONFIG_ITEMS_V ' ||
2279 'WHERE instance_hdr_id=:1 AND instance_rev_nbr=:2 ' ||
2280 ' AND CZ_UTILS.conv_num(item_val) IS NOT NULL AND ib_trackable=''1''; ' ||
2281 'END;'
2282 USING p_config_hdr_id,p_config_rev_nbr;
2283
2284 ERROR_CODE := '0002';
2285
2286 -- set local config items array and reinitialize global config items array
2287 t_config_item_tbl:=m_config_item_tbl; m_config_item_tbl.DELETE;
2288
2289 -- if there are no items in config then exit from procedure
2290 IF t_config_item_tbl.COUNT=0 THEN
2291 RETURN;
2292 END IF;
2293
2294 ERROR_CODE := '0003';
2295
2296 --
2300 get_Last_CSI_Revisions(p_config_inst_hdr_id => p_config_hdr_id,
2297 -- get last installed CSI revisions for each item
2298 -- t_csi_nbr_tbl is a hash map : config_item_id -> last CSI revision
2299 --
2301 x_csi_nbr_tbl => t_csi_nbr_tbl);
2302
2303 -- initialize both OUT arrays
2304 x_txn_params_tbl := SYSTEM.cz_txn_params_tbl_type(SYSTEM.cz_txn_params_type(NULL,NULL,NULL,NULL,NULL,NULL));
2305 x_config_attribute_tbl := SYSTEM.cz_config_attribute_tbl_type(SYSTEM.cz_config_attribute_type(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL));
2306
2307 ERROR_CODE := '0004';
2308
2309 --
2310 -- go through items ( MAIN LOOP )
2311 --
2312 FOR i IN t_config_item_tbl.First..t_config_item_tbl.Last
2313 LOOP
2314
2315 -- dbms_output.put_line('config_item_id ***> '||TO_CHAR(t_config_item_tbl(i)));
2316
2317 -- initialize global arrays which are used in dynamic sql
2318 m_attribute_category_tbl.DELETE;
2319 m_attribute_name_tbl.DELETE;
2320 m_attribute_value_tbl.DELETE;
2321
2322 ERROR_CODE := '0005';
2323
2324 -- get installed revision of the current item from CSI_ITEM_INSTANCES table
2325 IF t_csi_nbr_tbl.EXISTS(t_config_item_tbl(i)) THEN
2326 l_last_item_rev := t_csi_nbr_tbl( t_config_item_tbl(i) );
2327 ELSE
2328 l_last_item_rev := 0;
2329 END IF;
2330
2331 --dbms_output.put_line('last installed revision ***> '||TO_CHAR(l_last_item_rev));
2332
2333 IF l_last_item_rev=0 THEN
2334 l_Item_Is_Installed := FALSE;
2335 ELSE
2336 l_Item_Is_Installed := TRUE;
2337 END IF;
2338
2339 -----------------------------------------------------------
2340
2341 ERROR_CODE := '0006';
2342
2343 IF l_Item_Is_Installed THEN
2344
2345 ERROR_CODE := '0007';
2346
2347 get_InstDesc_LocId(p_config_hdr_id => p_config_hdr_id,
2348 p_config_rev_nbr => l_last_item_rev,
2349 p_config_item_id => t_config_item_tbl(i),
2350 x_instance_description => l_instance_description,
2351 x_location_id => l_location_id,
2352 x_location_type_code => l_location_type_code);
2353
2354 ERROR_CODE := '0008';
2355
2356 l_index := x_txn_params_tbl.COUNT;
2357
2358 x_txn_params_tbl(l_index).config_hdr_id := p_config_hdr_id;
2359 x_txn_params_tbl(l_index).config_rev_nbr := l_last_item_rev;
2360 x_txn_params_tbl(l_index).config_item_id := t_config_item_tbl(i);
2361 x_txn_params_tbl(l_index).instance_description := l_instance_description;
2362 x_txn_params_tbl(l_index).location_id := l_location_id;
2363 x_txn_params_tbl(l_index).location_type_code := l_location_type_code;
2364
2365 ERROR_CODE := '0009';
2366
2367 x_txn_params_tbl.EXTEND(1,1);
2368
2369 ERROR_CODE := '0010';
2370
2371 END IF; -- end of IF l_Item_Is_Installed
2372
2373
2374 ERROR_CODE := '0011';
2375
2376 t_attribute_category_tbl.DELETE;
2377 t_attribute_name_tbl.DELETE;
2378 t_attribute_value_tbl.DELETE;
2379
2380 -- retrieve txn attributes data for last saved revision ( = p_config_rev_nbr )
2381 get_Txn_Attributes_Data(p_config_hdr_id => p_config_hdr_id,
2382 p_config_rev_nbr => p_config_rev_nbr,
2383 p_config_item_id => t_config_item_tbl(i),
2384 x_attribute_category_tbl => t_attribute_category_tbl,
2385 x_attribute_name_tbl => t_attribute_name_tbl,
2386 x_attribute_value_tbl => t_attribute_value_tbl);
2387
2388 ERROR_CODE := '0012';
2389
2390 l_Item_Exists_In_Txn := TRUE;
2391 IF t_attribute_name_tbl.COUNT=0 THEN -- config item does not exist in txn CSI with revision = p_config_rev_nbr
2392
2393 ERROR_CODE := '0013';
2394 l_Item_Exists_In_Txn := FALSE;
2395
2396 -- retrieve CZ attributes data for revision = p_config_rev_nbr
2397 get_CZ_Attributes_Data(p_config_hdr_id => p_config_hdr_id,
2398 p_config_rev_nbr => p_config_rev_nbr,
2399 p_config_item_id => t_config_item_tbl(i),
2400 x_attribute_category_tbl => t_attribute_category_tbl,
2401 x_attribute_name_tbl => t_attribute_name_tbl,
2402 x_attribute_value_tbl => t_attribute_value_tbl);
2403
2404 ERROR_CODE := '0014';
2405
2406 END IF;
2407
2408 IF t_attribute_name_tbl.COUNT>0 THEN
2409
2410 ERROR_CODE := '0015';
2411
2412 FOR n IN t_attribute_name_tbl.FIRST..t_attribute_name_tbl.LAST
2413 LOOP
2414
2415 ERROR_CODE := '0016';
2416
2417 l_attr_counter := x_config_attribute_tbl.COUNT;
2418
2419 ERROR_CODE := '0017';
2420
2421 x_config_attribute_tbl(l_attr_counter).config_hdr_id := p_config_hdr_id;
2422 IF l_Item_Exists_In_Txn THEN
2423 x_config_attribute_tbl(l_attr_counter).config_rev_nbr := p_config_rev_nbr;
2424 ELSE
2425 x_config_attribute_tbl(l_attr_counter).config_rev_nbr := l_last_item_rev;
2426 END IF;
2427
2431
2428 ERROR_CODE := '0018';
2429
2430 x_config_attribute_tbl(l_attr_counter).config_item_id := t_config_item_tbl(i);
2432 ERROR_CODE := '0019';
2433
2434 x_config_attribute_tbl(l_attr_counter).attribute_group := t_attribute_category_tbl(n);
2435
2436 ERROR_CODE := '0020';
2437
2438 x_config_attribute_tbl(l_attr_counter).attribute_name := t_attribute_name_tbl(n);
2439
2440 ERROR_CODE := '0021';
2441
2442 x_config_attribute_tbl(l_attr_counter).attribute_value := t_attribute_value_tbl(n);
2443
2444 ERROR_CODE := '0022';
2445
2446 IF l_Item_Is_Installed THEN
2447
2448 ERROR_CODE := '0023';
2449
2450 x_config_attribute_tbl(l_attr_counter).install_rev_nbr:= l_last_item_rev;
2451
2452 ERROR_CODE := '0024';
2453
2454 -- get installed attribute value ( revision = l_last_item_rev )
2455 get_Installed_Attribute_Value(p_config_hdr_id => p_config_hdr_id,
2456 p_config_rev_nbr => l_last_item_rev,
2457 p_config_item_id => t_config_item_tbl(i),
2458 p_attribute_name => t_attribute_name_tbl(n),
2459 x_attribute_value => l_install_attribute_value);
2460
2461 ERROR_CODE := '0025';
2462
2463 x_config_attribute_tbl(l_attr_counter).install_attribute_value := l_install_attribute_value;
2464
2465 ERROR_CODE := '0026';
2466
2467 IF l_Item_Exists_In_Txn=FALSE OR l_last_item_rev=p_config_rev_nbr THEN
2468 x_config_attribute_tbl(l_attr_counter).attribute_value := l_install_attribute_value;
2469 END IF;
2470
2471 ERROR_CODE := '0027';
2472
2473 ELSE
2474 ERROR_CODE := '0028';
2475
2476 x_config_attribute_tbl(l_attr_counter).install_rev_nbr:= 0;
2477 x_config_attribute_tbl(l_attr_counter).install_attribute_value := NULL;
2478 ERROR_CODE := '0029';
2479 END IF;
2480
2481 x_config_attribute_tbl.EXTEND(1,1);
2482
2483 END LOOP;
2484
2485 END IF;
2486
2487 END LOOP;
2488
2489 ERROR_CODE := '0030';
2490
2491 -- remove last element ( which is just NULL based )
2492 x_txn_params_tbl.DELETE(x_txn_params_tbl.COUNT);
2493 x_config_attribute_tbl.DELETE(x_config_attribute_tbl.COUNT);
2494
2495 ERROR_CODE := '0031';
2496
2497 /*
2498 if x_txn_params_tbl.count>0 then
2499 LOG_REPORT(m_RUN_ID,'RUN_ID='||TO_CHAR(m_RUN_ID));
2500 for i in x_txn_params_tbl.first..x_txn_params_tbl.last
2501 loop
2502 LOG_REPORT(m_RUN_ID,'x_txn_params_tbl('||TO_CHAR(x_txn_params_tbl(i).config_item_id)||').config_rev_nbr = '||
2503 TO_CHAR(x_txn_params_tbl(i).config_rev_nbr));
2504
2505 LOG_REPORT(m_RUN_ID,'x_txn_params_tbl('||TO_CHAR(x_txn_params_tbl(i).config_item_id)||').location_id = '||
2506 TO_CHAR(x_txn_params_tbl(i).location_id));
2507
2508 LOG_REPORT(m_RUN_ID,'x_txn_params_tbl('||TO_CHAR(x_txn_params_tbl(i).config_item_id)||').location_type_code = '||
2509 x_txn_params_tbl(i).location_type_code);
2510
2511 LOG_REPORT(m_RUN_ID,'x_txn_params_tbl('||TO_CHAR(x_txn_params_tbl(i).config_item_id)||').instance_description = '||
2512 x_txn_params_tbl(i).instance_description);
2513
2514 end loop;
2515 end if;
2516 */
2517
2518 EXCEPTION
2519 WHEN OTHERS THEN
2520 DEBUG(SQLERRM);
2521 x_run_id:=m_RUN_ID;
2522 LOG_REPORT(x_run_id,'Internal Error (ERROR_CODE='||ERROR_CODE||') : '||SQLERRM);
2523 END Synchronize_Attributes;
2524
2525 /**
2526 * check_CZIB_Item PROCEDURE sets x_in_txn TO '1' IF config item
2527 * (p_config_hdr_id, p_config_rev_nbr, p_config_item_id)
2528 * EXISTS IN IB Transactions subschema
2529 * AND sets x_in_inst TO '1' IF config item
2530 * (p_config_hdr_id, p_config_rev_nbr, p_config_item_id)
2531 * EXISTS IN IB Instances subschema,
2532 * otherwise x_in_txn='0' AND x_in_inst='0'
2533 */
2534 PROCEDURE check_CZIB_Item
2535 (
2536 p_config_hdr_id IN NUMBER,
2537 p_config_rev_nbr IN NUMBER,
2538 p_config_item_id IN NUMBER,
2539 x_in_txn OUT NOCOPY VARCHAR2,
2540 x_in_inst OUT NOCOPY VARCHAR2
2541 ) IS
2542
2543 BEGIN
2544
2545 x_in_txn:=NO_FLAG; x_in_inst:=NO_FLAG;
2546 /*
2547 FOR i IN (SELECT config_inst_item_id FROM CSI_T_TXN_LINE_DETAILS
2548 WHERE config_inst_hdr_id = p_config_hdr_id AND
2549 config_inst_rev_num = p_config_rev_nbr AND
2550 config_inst_item_id = p_config_item_id)
2551 LOOP
2552 x_in_txn:=YES_FLAG;
2553 EXIT;
2554 END LOOP;
2555
2556 FOR i IN (SELECT config_inst_item_id FROM CSI_ITEM_INSTANCES
2557 WHERE config_inst_hdr_id = p_config_hdr_id AND
2558 config_inst_rev_num = p_config_rev_nbr AND
2559 config_inst_item_id = p_config_item_id)
2560 LOOP
2561 x_in_inst:=YES_FLAG;
2562 EXIT;
2563 END LOOP;
2564 */
2565 END check_CZIB_Item;
2566
2567 /**
2568 * wrapper FOR CSI_CZ_INT.Get_Connected_Configurations() PROCEDURE
2569 */
2570 PROCEDURE Get_Connected_Configurations
2571 (
2572 p_Config_Query_Table IN SYSTEM.cz_config_query_table,
2576 ) IS
2573 p_Instance_Level IN VARCHAR2,
2574 x_Config_Pair_Table OUT NOCOPY SYSTEM.cz_config_pair_table,
2575 x_run_id OUT NOCOPY NUMBER
2577
2578 t_Config_Query_Table Config_Query_Table;
2579 t_Config_Pair_Table Config_Pair_Table;
2580 v_config_ind NUMBER:=1;
2581 v_return_status VARCHAR2(255);
2582 v_return_message VARCHAR2(2000);
2583 v_msg_data VARCHAR2(2000);
2584 v_message_count NUMBER;
2585
2586 BEGIN
2587 x_run_id:=0;
2588 Initialize(SYSDATE);
2589
2590 x_Config_Pair_Table:=SYSTEM.cz_config_pair_table(SYSTEM.cz_config_pair_record
2591 (NULL,NULL,NULL,NULL,NULL,NULL,
2592 NULL,NULL,NULL,NULL,NULL,NULL,
2593 NULL,NULL,NULL,NULL));
2594
2595 FOR i IN p_Config_Query_Table.FIRST..p_Config_Query_Table.LAST
2596 LOOP
2597 t_Config_Query_Table(i).CONFIG_HEADER_ID:=p_Config_Query_Table(i).CONFIG_HEADER_ID;
2598 t_Config_Query_Table(i).CONFIG_REVISION_NUMBER:=p_Config_Query_Table(i).CONFIG_REVISION_NUMBER;
2599
2600 LOG_REPORT(m_RUN_ID,'Get_Connected_Configurations IN config_hdr_id / config_rev_nbr : '||
2601 TO_CHAR(t_Config_Query_Table(i).CONFIG_HEADER_ID)||' / '||TO_CHAR(t_Config_Query_Table(i).CONFIG_REVISION_NUMBER));
2602
2603 END LOOP;
2604
2605 -- CSI_CZ_INT.Get_Connected_Configurations --
2606 Get_Connected_Configurations
2607 (
2608 p_Config_Query_Table => t_Config_Query_Table,
2609 p_Instance_Level => p_Instance_Level,
2610 x_Config_Pair_Table => t_Config_Pair_Table,
2611 x_return_status => v_return_status,
2612 x_return_message => v_return_message
2613 );
2614
2615 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
2616 x_run_id:=m_RUN_ID;
2617 LOG_REPORT(x_run_id,'IB API Error : '||v_return_message);
2618 END IF;
2619
2620 --
2621 -- translate internal CSI_CZ_INT type into global SQL type
2622 --
2623 IF t_Config_Pair_Table.COUNT>0 THEN
2624 v_config_ind := 1;
2625 x_Config_Pair_Table:=SYSTEM.cz_config_pair_table(SYSTEM.cz_config_pair_record(NULL,NULL,NULL,NULL,NULL,NULL,
2626 NULL,NULL,NULL,NULL,NULL,NULL,
2627 NULL,NULL,NULL,NULL));
2628
2629 FOR i IN t_Config_Pair_Table.FIRST..t_Config_Pair_Table.LAST
2630 LOOP
2631
2632 x_Config_Pair_Table(v_config_ind).subject_header_id := t_Config_Pair_Table(i).subject_header_id;
2633 x_Config_Pair_Table(v_config_ind).subject_revision_number := t_Config_Pair_Table(i).subject_revision_number;
2634 x_Config_Pair_Table(v_config_ind).subject_item_id := t_Config_Pair_Table(i).subject_item_id;
2635 x_Config_Pair_Table(v_config_ind).object_header_id := t_Config_Pair_Table(i).object_header_id;
2636 x_Config_Pair_Table(v_config_ind).object_revision_number := t_Config_Pair_Table(i).object_revision_number;
2637 x_Config_Pair_Table(v_config_ind).object_item_id := t_Config_Pair_Table(i).object_item_id;
2638 x_Config_Pair_Table(v_config_ind).root_header_id := t_Config_Pair_Table(i).root_header_id;
2639 x_Config_Pair_Table(v_config_ind).root_revision_number := t_Config_Pair_Table(i).root_revision_number;
2640 x_Config_Pair_Table(v_config_ind).root_item_id := t_Config_Pair_Table(i).root_item_id;
2641 x_Config_Pair_Table(v_config_ind).source_application_id := t_Config_Pair_Table(i).source_application_id;
2642 x_Config_Pair_Table(v_config_ind).source_txn_header_ref := t_Config_Pair_Table(i).source_txn_header_ref;
2643 x_Config_Pair_Table(v_config_ind).source_txn_line_ref1 := t_Config_Pair_Table(i).source_txn_line_ref1;
2644 x_Config_Pair_Table(v_config_ind).source_txn_line_ref2 := t_Config_Pair_Table(i).source_txn_line_ref2;
2645 x_Config_Pair_Table(v_config_ind).source_txn_line_ref3 := t_Config_Pair_Table(i).source_txn_line_ref3;
2646 x_Config_Pair_Table(v_config_ind).lock_id := t_Config_Pair_Table(i).lock_id;
2647 x_Config_Pair_Table(v_config_ind).lock_status := t_Config_Pair_Table(i).lock_status;
2648
2649 x_Config_Pair_Table.EXTEND(1,1);
2650 v_config_ind :=v_config_ind+1;
2651 END LOOP;
2652 x_Config_Pair_Table.DELETE(v_config_ind);
2653 END IF;
2654
2655 EXCEPTION
2656 WHEN OTHERS THEN
2657 x_run_id:=m_RUN_ID;
2658 LOG_REPORT(x_run_id,'Internal Error : '||SQLERRM);
2659 END Get_Connected_Configurations;
2660
2661 /**
2662 * wrapper FOR CSI_CZ_INT.Get_Configuration_Revision() PROCEDURE
2663 *
2664 * 1. IF LEVEL IS "Installed", retrieve the "Revision Number" FROM CSI_Item_Instances
2665 * FOR the Config_Header_Id passed.
2666 * 2. IF LEVEL IS NULL OR "PENDING", THEN
2667 * a. Retrive the "Revision Number" FROM Csi_Item_Instances FOR the given Config_Header_Id.
2668 * b. CHECK IN Transcation Details, IF there IS a revision ON TRANSACTION details
2669 * FOR the config_Header_ID, which IS NOT a base revision ON ANY other line IN TRANSACTION details.
2670 * c. IF a revision IS FOUND THEN RETURN Revision AND the LEVEL AS PENDING,
2671 * otherwise RETURN the revision FROM the Csi_Item_Instances AND LEVEL AS INSTALLED.
2672 */
2676 p_target_commitment_date IN DATE,
2673 PROCEDURE Get_Configuration_Revision
2674 (
2675 p_Config_Header_Id IN NUMBER,
2677 px_Instance_Level IN OUT NOCOPY VARCHAR2,
2678 x_config_rec OUT NOCOPY SYSTEM.CZ_CONFIG_REC,
2679 x_run_id OUT NOCOPY NUMBER
2680 ) IS
2681
2682 v_return_status VARCHAR2(255);
2683 v_return_message VARCHAR2(2000);
2684 v_msg_data VARCHAR2(2000);
2685 v_message_count NUMBER;
2686 l_Config_Rev_Number NUMBER;
2687 l_config_rec config_rec;
2688
2689 BEGIN
2690 x_run_id:=0;
2691 x_config_rec := SYSTEM.CZ_CONFIG_REC(NULL,NULL,NULL,NULL,NULL,
2692 NULL,NULL,NULL,NULL,NULL,NULL);
2693
2694 Initialize(SYSDATE);
2695
2696 -- CSI_CZ_INT.Get_Configuration_Revision --
2697 Get_Configuration_Revision
2698 (
2699 p_Config_Header_Id => p_Config_Header_Id,
2700 p_target_commitment_date => p_target_commitment_date,
2701 px_Instance_Level => px_Instance_Level,
2702 x_Config_Rev_Number => l_Config_Rev_Number,
2703 x_config_rec => l_config_rec,
2704 x_return_status => v_return_status,
2705 x_return_message => v_return_message
2706 );
2707
2708 x_config_rec.source_application_id := l_config_rec.source_application_id;
2709 x_config_rec.source_txn_header_ref := l_config_rec.source_txn_header_ref;
2710 x_config_rec.source_txn_line_ref1 := l_config_rec.source_txn_line_ref1;
2711 x_config_rec.source_txn_line_ref2 := l_config_rec.source_txn_line_ref2;
2712 x_config_rec.source_txn_line_ref3 := l_config_rec.source_txn_line_ref3;
2713 x_config_rec.instance_id := l_config_rec.instance_id;
2714 x_config_rec.lock_id := l_config_rec.lock_id;
2715 x_config_rec.lock_status := l_config_rec.lock_status;
2716 x_config_rec.config_inst_hdr_id := l_config_rec.config_inst_hdr_id;
2717 x_config_rec.config_inst_item_id := l_config_rec.config_inst_item_id;
2718 x_config_rec.config_inst_rev_num := l_config_rec.config_inst_rev_num;
2719
2720 IF (v_return_status <> fnd_api.g_ret_sts_success) THEN
2721 DEBUG('Get_Configuration_Revision : '||v_return_message);
2722 x_run_id:=m_RUN_ID;
2723 LOG_REPORT(x_run_id,'IB API Error : '||v_return_message);
2724 END IF;
2725 EXCEPTION
2726 WHEN OTHERS THEN
2727 x_run_id:=m_RUN_ID;
2728 LOG_REPORT(x_run_id,'Internal Error : '||SQLERRM);
2729 END Get_Configuration_Revision;
2730
2731 --
2732 -- this procedure is used in order to create IB data for
2733 -- the copied model
2734 --
2735 PROCEDURE clone_IB_Data
2736 (
2737 p_config_hdr_id IN NUMBER,
2738 p_config_rev_nbr IN NUMBER,
2739 x_run_id OUT NOCOPY NUMBER
2740 ) IS
2741
2742 t_instance_hdr_tbl int_array_tbl_type;
2743 t_instance_rev_nbr_tbl int_array_tbl_type;
2744 t_config_item_tbl int_array_tbl_type;
2745 t_config_instance_tbl SYSTEM.cz_config_instance_tbl_type;
2746 v_txn_type_id NUMBER:=CZ_TRANSACTION_TYPE_ID;
2747
2748 BEGIN
2749 x_run_id:=0;
2750 ERROR_CODE:='0101';
2751
2752 Initialize(SYSDATE);
2753 t_config_instance_tbl := SYSTEM.cz_config_instance_tbl_type(SYSTEM.cz_config_instance_type(NULL,NULL,NULL,NULL));
2754
2755 ERROR_CODE:='0102';
2756 --
2757 -- collect all instances
2758 --
2759 SELECT instance_hdr_id,instance_rev_nbr,config_item_id
2760 BULK COLLECT INTO t_instance_hdr_tbl,t_instance_rev_nbr_tbl,t_config_item_tbl
2761 FROM CZ_CONFIG_ITEMS
2762 WHERE config_hdr_id = p_config_hdr_id AND
2763 config_rev_nbr= p_config_rev_nbr AND
2764 component_instance_type='I' AND
2765 ib_trackable=YES_FLAG AND deleted_flag=NO_FLAG;
2766
2767 ERROR_CODE:='0103';
2768
2769 IF t_instance_hdr_tbl.COUNT=0 THEN
2770 RETURN;
2771 END IF;
2772
2773 ERROR_CODE:='0104';
2774
2775 FOR i IN t_instance_hdr_tbl.FIRST..t_instance_hdr_tbl.LAST
2776 LOOP
2777 t_config_instance_tbl(i).config_hdr_id :=t_instance_hdr_tbl(i);
2778 t_config_instance_tbl(i).config_item_id :=t_config_item_tbl(i);
2779 t_config_instance_tbl(i).old_config_rev_nbr:=t_instance_rev_nbr_tbl(i);
2780 t_config_instance_tbl(i).new_config_rev_nbr:=t_instance_rev_nbr_tbl(i);
2781 t_config_instance_tbl.EXTEND(1,1);
2782 END LOOP;
2783
2784 ERROR_CODE:='0105';
2785
2786 t_config_instance_tbl.DELETE(t_config_instance_tbl.COUNT);
2787
2788 ERROR_CODE:='0106';
2789
2790 --
2791 -- create IB data
2792 --
2793 Update_Instances
2794 (
2795 p_config_instance_tbl => t_config_instance_tbl,
2796 p_effective_date => NULL,
2797 p_txn_type_id => v_txn_type_id,
2798 x_run_id => x_run_id
2799 );
2800
2801 ERROR_CODE:='0107';
2802
2803 EXCEPTION
2804 WHEN OTHERS THEN
2805 DEBUG('ERROR_CODE='||ERROR_CODE||' : '||SQLERRM);
2806 x_run_id:=m_RUN_ID;
2807 LOG_REPORT(x_run_id,'Internal Error (ERROR_CODE='||ERROR_CODE||') : '||SQLERRM);
2808 END clone_IB_Data;
2809
2810
2811 PROCEDURE Test_Update_Instances
2812 (
2813 p_instance_hdr_id NUMBER,
2814 p_config_item_id NUMBER,
2815 p_old_rev_nbr NUMBER,
2816 p_new_rev_nbr NUMBER
2817 ) IS
2818 v_run_id NUMBER;
2819 v_txn_type_id NUMBER:=CZ_TRANSACTION_TYPE_ID;
2820
2821 BEGIN
2822 --DBMS_OUTPUT.enable(2000000);
2823 Update_Instances
2824 (
2825 p_config_instance_tbl => SYSTEM.cz_config_instance_tbl_type(SYSTEM.cz_config_instance_type(p_instance_hdr_id,p_config_item_id,p_old_rev_nbr,p_new_rev_nbr)),
2826 p_effective_date => SYSDATE,
2827 p_txn_type_id => v_txn_type_id,
2828 x_run_id => v_run_id
2829 );
2830 DEBUG('v_run_id='||TO_CHAR(v_run_id));
2831 END Test_Update_Instances;
2832
2833 PROCEDURE Test_Connected_Configurations IS
2834
2835 v_run_id NUMBER;
2836 t_config_pair_table SYSTEM.cz_config_pair_table:=SYSTEM.cz_config_pair_table();
2837 BEGIN
2838 --DBMS_OUTPUT.enable(2000000);
2839 Get_Connected_Configurations
2840 (
2841 p_Config_Query_Table => SYSTEM.cz_config_query_table(SYSTEM.cz_config_query_record(1,1)),
2842 p_Instance_Level => 'PENDING',
2843 x_Config_Pair_Table => t_config_pair_table,
2844 x_run_id => v_run_id
2845 );
2846 DEBUG('v_run_id='||TO_CHAR(v_run_id));
2847 END Test_Connected_Configurations;
2848
2849
2850 PROCEDURE Test_Configuration_Revision
2851 (
2852 p_config_hdr_id IN NUMBER
2853 ) IS
2854
2855 v_rev_nbr NUMBER;
2856 v_run_id NUMBER;
2857 v_level VARCHAR2(255):='INSTALLED';
2858 v_config_rec SYSTEM.cz_config_rec;
2859
2860 BEGIN
2861
2862 Get_Configuration_Revision
2863 (
2864 p_Config_Header_Id => p_config_hdr_id,
2865 p_target_commitment_date => SYSDATE,
2866 px_Instance_Level => v_level,
2867 --x_Config_Rev_Number => v_rev_nbr,
2868 x_config_rec => v_config_rec,
2869 x_run_id => v_run_id
2870 );
2871
2872 DEBUG('Config_Rev_Number='||TO_CHAR(v_rev_nbr));
2873
2874 END Test_Configuration_Revision;
2875
2876
2877 END CZ_IB_TRANSACTIONS;