[Home] [Help]
PACKAGE BODY: APPS.CZ_PSFT_INTEGRATION_PVT
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cz_psft_integration_pvt';
1 PACKAGE BODY cz_psft_integration_pvt AS
2 /* $Header: czpsintb.pls 120.11.12010000.2 2008/11/10 07:15:39 kksriram ship $ */
3
5
6 -- config type, status
7 ROOT CONSTANT VARCHAR2(1) := 'R';
8 COMPLETE CONSTANT VARCHAR2(1) := '2';
9
10 NO_VALUE CONSTANT NUMBER := -1;
11
15 PS_NODE_TYPE_CONNECTOR CONSTANT NUMBER := 264;
12 PS_NODE_TYPE_FEATURE CONSTANT NUMBER := 261;
13 PS_NODE_TYPE_OPTION CONSTANT NUMBER := 262;
14 PS_NODE_TYPE_REFERENCE CONSTANT NUMBER := 263;
16 PS_NODE_TYPE_TOTAL CONSTANT NUMBER := 272;
17 PS_NODE_TYPE_RESOURCE CONSTANT NUMBER := 273;
18
19 -- configratuon vocabuary and xml tags
20 TAG_SECTION CONSTANT VARCHAR2(30) := 'SECTION';
21 TAG_COMPONENTS CONSTANT VARCHAR2(30) := 'COMPONENTS';
22 TAG_STRUCTURE CONSTANT VARCHAR2(30) := 'STRUCTURE';
23 TAG_CONFIGURABLE_COMPONENT CONSTANT VARCHAR2(30) := 'CONFIGURABLE_COMPONENT';
24 TAG_CONFIGURATION CONSTANT VARCHAR2(30) := 'CONFIGURATION';
25 TAG_CHOICES CONSTANT VARCHAR2(30) := 'CHOICES';
26 TAG_VIOLATIONS CONSTANT VARCHAR2(30) := 'VIOLATIONS';
27 TAG_EXPLANATION CONSTANT VARCHAR2(30) := 'EXPLANATION';
28 TAG_NUMERIC_VALUES CONSTANT VARCHAR2(30) := 'NUMERIC_VALUES';
29 TAG_ATTR CONSTANT VARCHAR2(30) := 'ATTR';
30 TAG_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE'; -- <COMPLETE STATUS="TRUE"/>
31
32 TAG_DELTA_INFO CONSTANT VARCHAR2(30) := 'DELTA_INFO';
33 TAG_COMPONENT CONSTANT VARCHAR2(30) := 'COMPONENT';
34 TAG_CONFIG_DELTA CONSTANT VARCHAR2(30) := 'CONFIG_DELTA';
35 TAG_DELTA_CHOICE CONSTANT VARCHAR2(30) := 'DELTA_CHOICE';
36 TAG_CHOICE_ADDS CONSTANT VARCHAR2(30) := 'CHOICE_ADDS';
37 TAG_CHOICE_DELETES CONSTANT VARCHAR2(30) := 'CHOICE_DELETES';
38 TAG_CHOICE_CHANGES CONSTANT VARCHAR2(30) := 'CHOICE_CHANGES';
39 TAG_PREVIOUS CONSTANT VARCHAR2(30) := 'PREVIOUS';
40 TAG_CH CONSTANT VARCHAR2(30) := 'CH';
41 TAG_CURRENT CONSTANT VARCHAR2(30) := 'CURRENT';
42 TAG_NUM CONSTANT VARCHAR2(30) := 'NUM';
43 TAG_EXPR_CHANGES CONSTANT VARCHAR2(30) := 'EXPR_CHANGES';
44 TAG_DELTA_EXPR CONSTANT VARCHAR2(30) := 'DELTA_EXPR';
45 TAG_COMPONENT_ADDS CONSTANT VARCHAR2(30) := 'COMPONENT_ADDS';
46 TAG_COMPONENT_DELETES CONSTANT VARCHAR2(30) := 'COMPONENT_DELETES';
47 ADD_ACTION CONSTANT VARCHAR2(30) := 'ADD';
48 CHANGE_ACTION CONSTANT VARCHAR2(30) := 'CHANGE';
49 DELETE_ACTION CONSTANT VARCHAR2(30) := 'DELETE';
50
51 v_all_psft_solutions_id_tbl t_ref;
52 v_all_psft_solutions_name_tbl t_name;
53 v_all_psft_solutions_desc_tbl t_name;
54 v_all_psft_solutions_prod_tbl t_name;
55 doc xmldom.DOMDocument;
56
57 -------------------------------------------------------------------------------
58
59 procedure load_all_solutions
60 AS
61 BEGIN
62 select devl_project_id,
63 name,
64 desc_text,
65 prj.product_key
66 BULK COLLECT INTO
67 v_all_psft_solutions_id_tbl,
68 v_all_psft_solutions_name_tbl,
69 v_all_psft_solutions_desc_tbl,
70 v_all_psft_solutions_prod_tbl
71 from cz_devl_projects prj, cz_model_publications pub
72 where prj.deleted_flag = '0' and prj.product_key like 'PSFT%'
73 and pub.deleted_flag = '0' and pub.object_id = prj.devl_project_id and object_type = 'PRJ'
74 and export_status = 'OK' and source_target_flag = 'T';
75 END load_all_solutions;
76
77 procedure createExpressionsInfo(solutionId IN NUMBER, parent_node IN OUT NOCOPY xmldom.DOMNode)
78 AS
79 l_expressions_info_tbl t_name;
80 l_expr_type_tbl t_ref;
81 l_expr_sub_type_tbl t_ref;
82 item_node xmldom.DOMNode;
83 numeric_node xmldom.DOMNode;
84 item_elmt xmldom.DOMElement;
85 TP VARCHAR2(2000);
86 BEGIN
87 select name,
88 ps_node_type,
89 feature_type
90 BULK COLLECT INTO
91 l_expressions_info_tbl,
92 l_expr_type_tbl,
93 l_expr_sub_type_tbl
94 from cz_rul_typedpsn_v
95 where detailed_type_id in (272, 273, 502, 504, 505, 506)
96 and devl_project_id = component_id
97 and devl_project_id = solutionId;
98
99 IF (l_expressions_info_tbl.COUNT > 0) THEN
100 item_elmt := xmldom.createElement(doc, 'NUMERIC_VALUES');
101 numeric_node := xmldom.appendChild(parent_node, xmldom.makeNode(item_elmt));
102 FOR comp_ref IN l_expressions_info_tbl.FIRST ..l_expressions_info_tbl.LAST
103 LOOP
104 item_elmt := xmldom.createElement(doc, 'NUM');
105 -- setAttributes
106 xmldom.setAttribute(item_elmt, 'NM', l_expressions_info_tbl(comp_ref) );
107 if ( l_expr_sub_type_tbl(comp_ref) = 1 ) then
108 TP := 'INTEGER';
109 elsif ( l_expr_sub_type_tbl(comp_ref) = 2 ) then
110 TP := 'DOUBLE';
111 elsif (l_expr_sub_type_tbl(comp_ref) = 3 or l_expr_type_tbl(comp_ref) = 272 or l_expr_type_tbl(comp_ref) = 273) then
112 TP := 'STRING';
113 end if;
114 xmldom.setAttribute(item_elmt, 'TP', TP );
115 item_node := xmldom.appendChild(numeric_node, xmldom.makeNode(item_elmt));
116 END LOOP;
117 END IF;
118 END createExpressionsInfo;
119
120 procedure get_solutions_details(solutionsXML OUT NOCOPY CLOB, x_run_id IN OUT NOCOPY NUMBER, x_pb_status IN OUT NOCOPY VARCHAR2)
121 AS
122 -- Nodes
123 main_node xmldom.DOMNode;
124 root_node xmldom.DOMNode;
125 item_node xmldom.DOMNode;
126 text_node xmldom.DOMNode;
127 solutionInfo_node xmldom.DOMNode;
128
129 --elements
130 root_elmt xmldom.DOMElement;
131 item_elmt xmldom.DOMElement;
132
133 item_text xmldom.DOMText;
134 l_lob CLOB;
135
139 v_all_psft_solutions_id_tbl.delete;
136 solutionCount NUMBER;
137 BEGIN
138 -- clear all previous arrays
140 v_all_psft_solutions_name_tbl.delete;
141 v_all_psft_solutions_desc_tbl.delete;
142 load_all_solutions;
143 solutionCount := v_all_psft_solutions_id_tbl.count;
144 x_run_id := 1;
145 x_pb_status := 'SUCCESS';
146
147 doc := xmldom.newDOMDocument();
148 main_node := xmldom.makeNode(doc);
149 -- create root element (SolutionList)
150 root_elmt := xmldom.createElement(doc, 'SolutionList');
151 -- set element attributes (solutions=solutionCount)
152 xmldom.setAttribute(root_elmt, 'solutions', solutionCount );
153 -- set top element on the document
154 root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));
155
156 IF solutionCount > 0 THEN
157
158 FOR s IN v_all_psft_solutions_id_tbl.FIRST .. v_all_psft_solutions_id_tbl.LAST
159 LOOP
160 -- create solutionIfo element
161 item_elmt := xmldom.createElement(doc, 'SolutionInfo');
162 -- setAttributes
163 xmldom.setAttribute(item_elmt, 'name', v_all_psft_solutions_prod_tbl(s) );
164 xmldom.setAttribute(item_elmt, 'version', '' );
165 xmldom.setAttribute(item_elmt, 'compound', '' );
166 xmldom.setAttribute(item_elmt, 'internal', 'false' );
167 xmldom.setAttribute(item_elmt, 'allNew', 'true' );
168 solutionInfo_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));
169
170 -- create description element
171 item_elmt := xmldom.createElement(doc, 'Description');
172 item_node := xmldom.appendChild(solutionInfo_node, xmldom.makeNode(item_elmt));
173 item_text := xmldom.createTextNode(doc, v_all_psft_solutions_desc_tbl(s));
174 text_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
175
176 -- create solutionUI element
177 item_elmt := xmldom.createElement(doc, 'solutionUI');
178 xmldom.setAttribute(item_elmt, 'root', '');
179 xmldom.setAttribute(item_elmt, 'localeLanguage', '' );
180 xmldom.setAttribute(item_elmt, 'localeCountry', '' );
181 xmldom.setAttribute(item_elmt, 'pageStart', '/OA_HTML/CfgOCI.jsp' );
182 xmldom.setAttribute(item_elmt, 'path', '' );
183 item_node := xmldom.appendChild(solutionInfo_node, xmldom.makeNode(item_elmt));
184
185 -- create ComponentInfo element
186 item_elmt := xmldom.createElement(doc, 'ComponentInfo');
187 -- setAttributes
188 xmldom.setAttribute(item_elmt, 'modelName', v_all_psft_solutions_name_tbl(s) );
189 xmldom.setAttribute(item_elmt, 'modelVersion', '' );
190 xmldom.setAttribute(item_elmt, 'restorePolicy', '' );
191 item_node := xmldom.appendChild(solutionInfo_node, xmldom.makeNode(item_elmt));
192
193 -- createReferenceTreeStructure
194 --createReferenceTreeStructure(v_all_psft_solutions_id_tbl(s), solutionInfo_node);
195 END LOOP;
196 END IF;
197
198 -- create output parameter
199 SYS.DBMS_LOB.createtemporary(l_lob, TRUE, DBMS_LOB.SESSION);
200 SYS.DBMS_LOB.OPEN(l_lob, DBMS_LOB.lob_readwrite);
201 xmldom.writetoclob(doc, l_lob);
202 solutionsXML := l_lob;
203 SYS.DBMS_LOB.CLOSE(l_lob);
204 SYS.DBMS_LOB.freetemporary(l_lob);
205 xmldom.freeDocument(doc);
206 EXCEPTION
207 WHEN OTHERS THEN
208 x_run_id := 0;
209 x_pb_status := 'FAILED';
210 RAISE;
211 END get_solutions_details;
212
213 procedure get_models_details(modelsXML OUT NOCOPY CLOB, x_run_id IN OUT NOCOPY NUMBER, x_pb_status IN OUT NOCOPY VARCHAR2)
214 AS
215 -- define arrays here to collect
216 main_node xmldom.DOMNode;
217 root_node xmldom.DOMNode;
218 item_node xmldom.DOMNode;
219 text_node xmldom.DOMNode;
223 root_elmt xmldom.DOMElement;
220 modelInfo_node xmldom.DOMNode;
221
222 --elements
224 item_elmt xmldom.DOMElement;
225
226 item_text xmldom.DOMText;
227 l_lob CLOB;
228
229 solutionCount NUMBER;
230 BEGIN
231 -- clear all previous arrays
232 v_all_psft_solutions_id_tbl.delete;
233 v_all_psft_solutions_name_tbl.delete;
234 v_all_psft_solutions_desc_tbl.delete;
235 load_all_solutions;
236 solutionCount := v_all_psft_solutions_id_tbl.count;
237 x_run_id := 1;
238 x_pb_status := 'SUCCESS';
239 doc := xmldom.newDOMDocument();
240 main_node := xmldom.makeNode(doc);
241 -- create root element (ModelList)
242 root_elmt := xmldom.createElement(doc, 'ModelList');
243 -- set element attributes (solutions=solutionCount)
244 xmldom.setAttribute(root_elmt, 'models', solutionCount );
245 -- set top element on the document
246 root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));
247
248 IF solutionCount > 0 THEN
249
250 FOR s IN v_all_psft_solutions_id_tbl.FIRST .. v_all_psft_solutions_id_tbl.LAST
251 LOOP
252 -- create solutionIfo element
253 item_elmt := xmldom.createElement(doc, 'ModelInfo');
254 -- setAttributes
255 xmldom.setAttribute(item_elmt, 'name', v_all_psft_solutions_name_tbl(s) );
256 xmldom.setAttribute(item_elmt, 'version', '' );
257 modelInfo_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));
258
259 -- create DECISION_POINTS element
260 item_elmt := xmldom.createElement(doc, 'DECISION_POINTS');
261 item_node := xmldom.appendChild(modelInfo_node, xmldom.makeNode(item_elmt));
262
263 createExpressionsInfo(v_all_psft_solutions_id_tbl(s),modelInfo_node);
264
265 -- create EXTERN_VARS element
266 item_elmt := xmldom.createElement(doc, 'EXTERN_VARS');
267 item_node := xmldom.appendChild(modelInfo_node, xmldom.makeNode(item_elmt));
268 END LOOP;
269 END IF;
270
271 -- create output parameter
272 SYS.DBMS_LOB.createtemporary(l_lob, TRUE, DBMS_LOB.SESSION);
273 SYS.DBMS_LOB.OPEN(l_lob, DBMS_LOB.lob_readwrite);
274 xmldom.writetoclob(doc, l_lob);
275 modelsXML := l_lob;
276 SYS.DBMS_LOB.CLOSE(l_lob);
277 SYS.DBMS_LOB.freetemporary(l_lob);
278 xmldom.freeDocument(doc);
279 EXCEPTION
280 WHEN OTHERS THEN
281 x_run_id := 0;
282 x_pb_status := 'FAILED';
283 RAISE;
284 END get_models_details;
285
286 --------------------------------------------------------------------------------
287
288 PROCEDURE get_config_details(p_api_version IN NUMBER
289 ,p_config_hdr_id IN NUMBER
290 ,p_config_rev_nbr IN NUMBER
291 ,p_product_key IN VARCHAR2
292 ,p_application_id IN NUMBER
293 ,p_price_info_list IN SYSTEM.VARCHAR_TBL_TYPE
294 ,p_check_violation_flag IN VARCHAR2
295 ,p_check_connection_flag IN VARCHAR2
296 ,p_baseline_config_hdr_id IN NUMBER
297 ,p_baseline_config_rev_nbr IN NUMBER
298 ,x_config_details OUT NOCOPY CLOB
299 ,x_return_status OUT NOCOPY VARCHAR2
300 ,x_msg_count OUT NOCOPY NUMBER
301 ,x_msg_data OUT NOCOPY VARCHAR2
302 )
303 IS
304 l_api_version CONSTANT NUMBER := 1.0;
305 l_api_name CONSTANT VARCHAR2(30) := 'get_config_details';
306
307 -- item type code
308 COMPONENT CONSTANT NUMBER := 4;
309 TEXT CONSTANT NUMBER := 2;
310 CONNECTOR CONSTANT NUMBER := 7;
311
312 -- prop data type
313 TRANSLATABLE_TEXT CONSTANT NUMBER := 8;
314 NON_TRANSLATABLE_TEXT CONSTANT NUMBER := 4;
315
316 -- Feature type: 0-option;1-integer;2-decimal;3-boolean;4-text
317 -- BOOLEAN_FEATURE CONSTANT NUMBER := 1;
318 -- INTEGER_FEATURE CONSTANT NUMBER := 2;
319 -- DOUBLE_FEATURE CONSTANT NUMBER := 3;
320 OPTION_FEATURE CONSTANT NUMBER := 0;
321 OPTION_FEATURE_1_1 CONSTANT NUMBER := 1;
322 OPTION_FEATURE_0_N CONSTANT NUMBER := 2;
323 NUM_FEATURE CONSTANT NUMBER := 3; -- bool, int, double, total, resource
324 TEXT_FEATURE CONSTANT NUMBER := 4;
325
326 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
327 TYPE num_tbl_type_idx_vc2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
328 TYPE v2k_tbl_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
329 TYPE psn_tbl_type IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
330 TYPE prj_psn_tbl_type IS TABLE OF psn_tbl_type INDEX BY VARCHAR2(15);
331 TYPE prp_rec_type IS RECORD (name cz_properties.name%TYPE, val cz_ps_prop_vals.data_value%TYPE);
332 TYPE prp_tbl_type IS TABLE OF prp_rec_type INDEX BY BINARY_INTEGER;
333 TYPE psn_prp_tbl_type IS TABLE OF prp_tbl_type INDEX BY VARCHAR2(15);
334 TYPE prj_psn_prp_tbl_type IS TABLE OF psn_prp_tbl_type INDEX BY VARCHAR2(15);
335 TYPE osr_tbl_type IS TABLE OF cz_item_masters.orig_sys_ref%TYPE INDEX BY VARCHAR2(15);
336 TYPE pkg_osr_tbl_type IS TABLE OF osr_tbl_type INDEX BY VARCHAR2(15);
337
338 TYPE cfg_msg_tbl_type IS TABLE of v2k_tbl_type INDEX BY VARCHAR2(15);-- Bug 6892148;
339
340 TYPE conn_rec_type IS RECORD
341 (id NUMBER
342 ,name cz_config_items.name%TYPE
343 ,ref cz_config_items.name%TYPE
344 ,fromCompID NUMBER
345 ,fromCompName cz_config_items.name%TYPE
346 ,fromCompType cz_config_items.name%TYPE
347 ,toCompID NUMBER
351 TYPE conn_tbl_type IS TABLE OF conn_rec_type INDEX BY VARCHAR2(15);-- Bug 6892148;
348 ,toCompName cz_config_items.name%TYPE
349 ,toCompType cz_config_items.name%TYPE
350 );
352
353 TYPE chg_rec_type IS RECORD (old_val cz_config_items.item_val%TYPE, new_val cz_config_items.item_val%TYPE);
354 TYPE chg_tbl_type IS TABLE OF chg_rec_type INDEX BY BINARY_INTEGER;
355
356 TYPE domnode_tbl_type IS TABLE OF XMLDOM.DOMNode INDEX BY VARCHAR2(15);-- Bug 6892148;
357
358 l_delta_choice_Node XMLDOM.DOMNode;
359
360 l_ndebug INTEGER;
361 l_log_stmt BOOLEAN;
362 l_msg VARCHAR2(2000);
363 l_count INTEGER;
364
365 l_config_status cz_config_hdrs.config_status%TYPE;
366 l_has_failures cz_config_hdrs.has_failures%TYPE;
367 l_model_id NUMBER;
368 l_creation_date DATE;
369 l_lookup_date DATE;
370 l_effective_date DATE;
371
372 l_is_valid BOOLEAN;
373 l_rpt_total_price BOOLEAN;
374 l_rpt_recur_price BOOLEAN;
375 l_check_delta BOOLEAN;
376 l_is_root_comp BOOLEAN;
377 l_has_pkg_component BOOLEAN;
378 l_is_pkg_component BOOLEAN;
379 l_exists_in_baseline BOOLEAN;
380 l_psn_type INTEGER;
381 l_comp_name VARCHAR2(1000);
382 l_parent_item NUMBER;
383 l_parent_name cz_config_items.ps_node_name%TYPE;
384
385 l_ref_node_map num_tbl_type_idx_vc2;
386 l_prj_psn_type_map prj_psn_tbl_type;
387 l_prj_psn_osr_map pkg_osr_tbl_type;
388 l_prj_psn_prp_map prj_psn_prp_tbl_type;
389 l_item_prj_map num_tbl_type_idx_vc2;-- Bug 6892148;
390 l_prp_tbl prp_tbl_type;
391
392 l_msg_tbl v2k_tbl_type;
393 l_com_msg_tbl v2k_tbl_type;
394 l_com_att_tbl prp_tbl_type;
395
396 l_conn_map conn_tbl_type;
397
398 l_cfg_msg_map cfg_msg_tbl_type;
399
400 l_chg_item_map chg_tbl_type;
401 l_add_item_map num_tbl_type;
402 l_del_item_map num_tbl_type;
403 l_pkg_node_map num_tbl_type;
404
405 l_domnode_map domnode_tbl_type;
406
407 l_init_xml_str VARCHAR2(2000);
408 l_typeAttrVal VARCHAR2(10);
409 l_doc XMLDOM.DOMDocument;
410 l_docelm XMLDOM.DOMElement;
411 l_sections XMLDOM.DOMNodeList;
412 l_templateNode XMLDOM.DOMNode;
413 l_tmplNodes XMLDOM.DOMNodeList;
414 l_sectionMainNode XMLDOM.DOMNode;
415 l_componentsNode XMLDOM.DOMNode;
416 l_cfgCompNode XMLDOM.DOMNode;
417 l_configurationNode XMLDOM.DOMNode;
418 l_structNode XMLDOM.DOMNode;
419 l_structCfgCompNode XMLDOM.DOMNode;
420 l_connNode XMLDOM.DOMNode;
421 l_violationNode XMLDOM.DOMNode;
422 l_choicesNode XMLDOM.DOMNode;
423 l_chNode XMLDOM.DOMNode;
424 l_numericValuesNode XMLDOM.DOMNode;
425 l_numNode XMLDOM.DOMNode;
426 l_attrNode XMLDOM.DOMNode;
427
428 l_pkgComponentsNode XMLDOM.DOMNode;
429 l_pkgCfgCompNode XMLDOM.DOMNode;
430 l_pkgConfigurationNode XMLDOM.DOMNode;
431 l_pkgChoicesNode XMLDOM.DOMNode;
432 l_pkgNumericValuesNode XMLDOM.DOMNode; -- check DTD if needed
433 l_pkgChNode XMLDOM.DOMNode;
434 l_pkgAttrNode XMLDOM.DOMNode;
435 l_leadAttrNodeCreated BOOLEAN;
436
437 l_prcNumericValuesNode XMLDOM.DOMNode;
438 l_sectionDeltaNode XMLDOM.DOMNode;
439 l_tempNode XMLDOM.DOMNode;
440
441 l_tmplCcNode XMLDOM.DOMNode; -- configurable component
442 l_tmplChNode XMLDOM.DOMNode; -- <CH DP="OF7" DM="O13"> QTY="1.0"</CH>
443 l_tmplAtNode XMLDOM.DOMNode; -- <ATTR NM="">a1</ATTR>
444 l_tmplNumNode XMLDOM.DOMNode; -- <NUM NM="fxRecurringFrequencyCode" VL="10" TY="STRING"/>
445 l_tmplXplNode XMLDOM.DOMNode; -- <EXPLANATION>what</EXPLANATION>
446 l_tmplComNode XMLDOM.DOMNode; -- <COMPONENT id="" name="" type="" isPkgCmpnt="false"/>
447 l_tmplDeNode XMLDOM.DOMNode; -- <DELTA_EXPR NM="DF1" PREVIOUS="1" CURRENT="2"/>
448 l_tmplConNode XMLDOM.DOMNode; -- <CONNECTION id="1676995111" name="BtF" ref="FloorInBuilding" fromCompId="1676995126" fromCompName="Building-1" fromCompType="Building" toCompId="1676995128" toCompName="Floor-1" toCompType="Floor"/>
449
450 /* *** delta_info part *** */
451
452 TYPE cfg_item_rec_type IS RECORD
453 (
454 parent_config_item_id CZ_CONFIG_ITEMS.parent_config_item_id%TYPE,
455 ps_node_id CZ_CONFIG_ITEMS.ps_node_id%TYPE,
456 name CZ_CONFIG_ITEMS.name%TYPE,
457 ps_node_name CZ_CONFIG_ITEMS.ps_node_name%TYPE,
458 comp_name CZ_CONFIG_ITEMS.ps_node_name%TYPE,
459 item_val CZ_CONFIG_ITEMS.item_val%TYPE,
460 item_num_val CZ_CONFIG_ITEMS.item_num_val%TYPE
461 );
462
463 TYPE cfg_item_tbl_type IS TABLE OF cfg_item_rec_type INDEX BY VARCHAR2(15);-- Bug 6892148;
464 TYPE vrchr_tbl_type IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);
465
466 l_handled_nodes_tbl vrchr_tbl_type;
467
468 l_baseline_cfg_tbl cfg_item_tbl_type;
469 l_baseline_cfg_item cfg_item_rec_type;
470
471 l_delta_info_Node xmldom.DOMNode;
472 l_cfg_delta_Node xmldom.DOMNode;
473
474 l_components_Node xmldom.DOMNode;
475 l_choice_adds_Node xmldom.DOMNode;
476 l_choice_deletes_Node xmldom.DOMNode;
477 l_choice_changes_Node xmldom.DOMNode;
478 l_expr_changes_Node xmldom.DOMNode;
479 l_choice_prev_changes_Node xmldom.DOMNode;
480 l_choice_curr_changes_Node xmldom.DOMNode;
481 l_delta_expr_Node xmldom.DOMNode;
482 l_delta_cfgcompnode xmldom.DOMNode;
483
484 l_component_adds_Node XMLDOM.DOMNode;
485 l_component_deletes_Node XMLDOM.DOMNode;
489 l_changes_prev_chNode XMLDOM.DOMNode;
486 l_tmp_Node XMLDOM.DOMNode;
487 l_baseline_item_id XMLDOM.DOMNode;
488 l_parent_psn_type NUMBER;
490 l_changes_curr_chNode XMLDOM.DOMNode;
491 l_delta_chNode XMLDOM.DOMNode;
492 l_name_xml_attr xmldom.DOMAttr;
493 l_delta_curr_numNode XMLDOM.DOMNode;
494 /* *** end of delta_info part *** */
495
496 --------------------------------------------------------------------------------
497 /* *** delta_info part *** */
498 PROCEDURE get_config_item_data
499 (p_cfg_hdr_id IN NUMBER,
500 p_cfg_rev_nbr IN NUMBER,
501 x_cfg_tbl OUT NOCOPY cfg_item_tbl_type) IS
502
503 BEGIN
504
505 FOR i IN
506 (SELECT config_item_id, instance_nbr, ps_node_id, name, value_type_code,
507 ps_node_name, parent_config_item_id, item_val, item_num_val
508 FROM cz_config_items
509 START WITH config_hdr_id = p_cfg_hdr_id AND config_rev_nbr = p_cfg_rev_nbr
510 AND component_instance_type = ROOT AND deleted_flag='0'
511 CONNECT BY config_hdr_id = p_cfg_hdr_id AND config_rev_nbr = p_cfg_rev_nbr
512 AND deleted_flag = '0' AND PRIOR config_item_id = parent_config_item_id)
513 LOOP
514 x_cfg_tbl(i.config_item_id).parent_config_item_id := i.parent_config_item_id;
515 x_cfg_tbl(i.config_item_id).ps_node_id := i.ps_node_id;
516 x_cfg_tbl(i.config_item_id).name := i.name;
517 x_cfg_tbl(i.config_item_id).ps_node_name := i.ps_node_name;
518 x_cfg_tbl(i.config_item_id).item_val := i.item_val;
519 x_cfg_tbl(i.config_item_id).item_num_val := i.item_num_val;
520
521 IF i.value_type_code = COMPONENT THEN
522
523 IF i.parent_config_item_id IS NULL OR i.parent_config_item_id = NO_VALUE THEN
524 x_cfg_tbl(i.config_item_id).comp_name := NVL(i.name, i.ps_node_name);
525 ELSE
526 x_cfg_tbl(i.config_item_id).comp_name := NVL(i.name, i.ps_node_name||'-'||TO_CHAR(i.instance_nbr));
527 END IF;
528
529 END IF;
530
531 END LOOP;
532
533 END get_config_item_data;
534
535 /* *** end of delta_info part *** */
536
537 --------------------------------------------------------------------------------
538 -- <CONFIGURABLE_COMPONENT name="C1" component="C1"/>
539 FUNCTION newCfgComponentNode(p_name IN VARCHAR2, p_component IN VARCHAR2)
540 RETURN XMLDOM.DOMNode
541 IS
542 node XMLDOM.DOMNode;
543 attrs XMLDOM.DOMNamedNodeMap;
544 BEGIN
545 node := XMLDOM.cloneNode(l_tmplCcNode, FALSE);
546 attrs := XMLDOM.getAttributes(node);
547 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 0)), p_name);
548 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 1)), p_component);
549 RETURN node;
550 END newCfgComponentNode;
551
552 --------------------------------------------------------------------------------
553 /* *** delta_info part *** */
554 -- <COMPONENT name="C1" />
555 -- <CONFIG_DELTA>
556 -- <CHOICE_ADDS/>
557 -- <CHOICE_DELETES/>
558 -- <CHOICE_CHANGES>
559 -- <PREVIOUS/>
560 -- <CURRENT/>
561 -- </CHOICE_CHANGES>
562 -- <EXPR_CHANGES/>
563 -- </CONFIG_DELTA>
564 -- </COMPONENT>
565 PROCEDURE addDeltaComponentNode(p_id IN VARCHAR2, p_name IN VARCHAR2) IS
566
567 l_name_xml_attr xmldom.DOMAttr;
568 l_tmp_xml_node xmldom.DOMNode;
569
570 BEGIN
571
572 --
573 -- create tag <COMPONENTS>
574 --
575 l_components_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_COMPONENT));
576
577 l_name_xml_attr := xmldom.createAttribute(l_doc, 'id');
578 xmldom.setValue(l_name_xml_attr, p_id);
579 l_name_xml_attr := xmldom.SetAttributeNode(xmldom.makeElement(l_components_Node), l_name_xml_attr );
580
581 l_name_xml_attr := xmldom.createAttribute(l_doc, 'name');
582 xmldom.setValue(l_name_xml_attr, p_name);
583 l_name_xml_attr := xmldom.SetAttributeNode(xmldom.makeElement(l_components_Node), l_name_xml_attr );
584
585 l_name_xml_attr := xmldom.createAttribute(l_doc, 'type');
586 xmldom.setValue(l_name_xml_attr, '');
587 l_name_xml_attr := xmldom.SetAttributeNode(xmldom.makeElement(l_components_Node), l_name_xml_attr );
588
589 --
590 -- create tag <CONFIG_DELTA>
591 --
592 l_cfg_delta_Node := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_CONFIG_DELTA));
593
594 --
595 -- attach tag <CONFIG_DELTA> to tag <COMPONENTS> :
596 -- <COMPONENTS>
597 -- |--- <CONFIG_DELTA>
598 --
599 l_tmp_xml_node := XMLDOM.appendChild(l_components_Node, l_cfg_delta_Node);
600
601 --
602 -- attach tag <CHOICE_ADDS> to tag <CONFIG_DELTA> :
603 -- <COMPONENTS>
604 -- |--- <CONFIG_DELTA>
605 -- |-- <CHOICE_ADDS>
606 --
607 l_choice_adds_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_CHOICE_ADDS));
608 l_tmp_xml_node := XMLDOM.appendChild(l_cfg_delta_Node, l_choice_adds_Node);
609
610 --
611 -- attach tag <CHOICE_DELETES> to tag <CONFIG_DELTA> :
612 -- <COMPONENTS>
613 -- |--- <CONFIG_DELTA>
614 -- |-- <CHOICE_ADDS>
615 -- |-- <CHOICE_DELETES>
616 --
617 l_choice_deletes_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_CHOICE_DELETES));
618 l_tmp_xml_node := XMLDOM.appendChild(l_cfg_delta_Node, l_choice_deletes_Node);
619
620 --
621 -- attach tag <CHOICE_CHANGES> to tag <CONFIG_DELTA> :
622 -- <COMPONENTS>
623 -- |--- <CONFIG_DELTA>
627 --
624 -- |-- <CHOICE_ADDS>
625 -- |-- <CHOICE_DELETES>
626 -- |-- <CHOICE_CHANGES>
628 l_choice_changes_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_CHOICE_CHANGES));
629 l_tmp_xml_node := XMLDOM.appendChild(l_cfg_delta_Node, l_choice_changes_Node);
630
631 --
632 -- attach tag <EXPR_CHANGES> to tag <CONFIG_DELTA> :
633 -- <COMPONENTS>
634 -- |--- <CONFIG_DELTA>
635 -- |-- <CHOICE_ADDS>
636 -- |-- <CHOICE_DELETES>
637 -- |-- <CHOICE_CHANGES>
638 -- |-- <PREVIOUS>
639 -- |-- <CURRENT>
640 -- |-- <EXPR_CHANGES>
641 l_expr_changes_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_EXPR_CHANGES));
642 l_tmp_xml_node := XMLDOM.appendChild(l_cfg_delta_Node, l_expr_changes_Node);
643
644 --
645 -- attach tag <COMPONENT> to tag <DELTA_INFO> :
646 -- <COMPONENTS>
647 l_tmp_xml_node := XMLDOM.appendChild(l_delta_info_Node, l_components_Node);
648
649 END addDeltaComponentNode;
650 /* *** end of delta_info part *** */
651
652 --------------------------------------------------------------------------------
653 -- <CH DP="OF1" DM="O2"> QTY="1.0"</CH>
654 FUNCTION newChoiceNode(p_optionFeature IN VARCHAR2
655 ,p_option IN VARCHAR2
656 ,p_quantity IN VARCHAR2)
657 RETURN XMLDOM.DOMNode
658 IS
659 node XMLDOM.DOMNode;
660 attrs XMLDOM.DOMNamedNodeMap;
661 BEGIN
662 node := XMLDOM.cloneNode(l_tmplChNode, FALSE);
663 attrs := XMLDOM.getAttributes(node);
664 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 0)), p_optionFeature);
665 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 1)), p_option);
666 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 3)), p_quantity);
667 RETURN node;
668 END newChoiceNode;
669
670 --------------------------------------------------------------------------------
671 /* *** delta_info part *** */
672 PROCEDURE attachPrevChoices(p_parent_config_item_id IN NUMBER, p_parent_name IN VARCHAR2, p_choice_Node xmldom.DOMNode) IS
673 l_tmp_changes_chNode xmldom.DOMNode;
674 l_tmp_xml_Node xmldom.DOMNode;
675 BEGIN
676 FOR fitm IN (SELECT ps_node_name,item_num_val FROM CZ_CONFIG_ITEMS
677 WHERE config_hdr_id=p_baseline_config_hdr_id AND
678 config_rev_nbr=p_baseline_config_rev_nbr AND
679 deleted_flag='0' AND parent_config_item_id=p_parent_config_item_id AND
680 config_item_id NOT IN
681 (SELECT b.config_item_id FROM CZ_CONFIG_ITEMS b
682 WHERE b.config_hdr_id=p_config_hdr_id AND
683 b.config_rev_nbr=p_config_rev_nbr AND
684 b.deleted_flag='0' AND b.parent_config_item_id=p_parent_config_item_id))
685 LOOP
686 l_tmp_changes_chNode := newChoiceNode(p_parent_name, fitm.ps_node_name, fitm.item_num_val);
687 l_tmp_xml_Node := XMLDOM.appendChild(p_choice_Node, l_tmp_changes_chNode);
688 END LOOP;
689 END attachPrevChoices;
690 /* *** end of delta_info part *** */
691
692 --------------------------------------------------------------------------------
693 -- <ATTR NM="">attrval</ATTR>
694 FUNCTION newAttributeNode(p_name IN VARCHAR2, p_value IN VARCHAR2)
695 RETURN XMLDOM.DOMNode
696 IS
697 node XMLDOM.DOMNode;
698 BEGIN
699 node := XMLDOM.cloneNode(l_tmplAtNode, TRUE);
700 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(XMLDOM.getAttributes(node), 0)), p_name);
701 XMLDOM.setNodeValue(XMLDOM.getFirstChild(node), p_value);
702 RETURN node;
703 END newAttributeNode;
704
705 --------------------------------------------------------------------------------
706 -- <NUM NM="fxRecurringFrequencyCode" VL="10" TY="STRING"/>
707 FUNCTION newNumNode(p_name IN VARCHAR2
708 ,p_value IN VARCHAR2
709 ,p_type IN VARCHAR2)
710 RETURN XMLDOM.DOMNode
711 IS
712 node XMLDOM.DOMNode;
713 attrs XMLDOM.DOMNamedNodeMap;
714 BEGIN
715 node := XMLDOM.cloneNode(l_tmplNumNode, FALSE);
716 attrs := XMLDOM.getAttributes(node);
717 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 0)), p_name);
718 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 1)), p_value);
719 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 2)), p_type);
720 RETURN node;
721 END newNumNode;
722
723 --------------------------------------------------------------------------------
724
725 /* *** delta_info part *** */
726
727 PROCEDURE add_Expr_Node
728 (p_name IN VARCHAR2,
729 p_old_value IN VARCHAR2,
730 p_new_value IN VARCHAR2,
731 p_type IN VARCHAR2) IS
732
733 l_delta_curr_numNode xmldom.DOMNode;
734 l_tempNode xmldom.DOMNode;
735 l_delta_expr_Node xmldom.DOMNode;
736 l_name_xml_attr xmldom.DOMAttr;
737
738 BEGIN
739 l_delta_curr_numNode := newNumNode(p_name, p_new_value, p_type);
740 l_tempNode := XMLDOM.appendChild(l_choice_curr_changes_Node, l_delta_curr_numNode);
741
742 l_delta_expr_Node := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_DELTA_EXPR));
743
744 l_name_xml_attr := xmldom.createAttribute(l_doc, 'NM');
745 xmldom.setValue(l_name_xml_attr, p_name);
746 l_name_xml_attr := xmldom.SetAttributeNode( xmldom.makeElement(l_delta_expr_Node), l_name_xml_attr );
747
748 l_name_xml_attr := xmldom.createAttribute(l_doc, 'PREVIOUS');
749 xmldom.setValue(l_name_xml_attr, p_old_value);
750 l_name_xml_attr := xmldom.SetAttributeNode( xmldom.makeElement(l_delta_expr_Node), l_name_xml_attr );
751
752 l_name_xml_attr := xmldom.createAttribute(l_doc, 'CURRENT');
756 l_tempNode := XMLDOM.appendChild(l_expr_changes_Node, l_delta_expr_Node);
753 xmldom.setValue(l_name_xml_attr, p_new_value);
754 l_name_xml_attr := xmldom.SetAttributeNode( xmldom.makeElement(l_delta_expr_Node), l_name_xml_attr );
755
757 END add_Expr_Node;
758
759 /* *** end of delta_info part *** */
760
761 --------------------------------------------------------------------------------
762 -- <EXPLANATION>what</EXPLANATION>
763 FUNCTION newExplanationNode(p_explanation IN VARCHAR2)
764 RETURN XMLDOM.DOMNode
765 IS
766 node XMLDOM.DOMNode;
767 BEGIN
768 node := XMLDOM.cloneNode(l_tmplXplNode, TRUE);
769 XMLDOM.setNodeValue(XMLDOM.getFirstChild(node), p_explanation);
770 RETURN node;
771 END newExplanationNode;
772
773 --------------------------------------------------------------------------------
774 FUNCTION newViolationNode(p_msg_tbl v2k_tbl_type)
775 RETURN XMLDOM.DOMNode
776 IS
777 node XMLDOM.DOMNode;
778 tempNode XMLDOM.DOMNode;
779 BEGIN
780 node := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_VIOLATIONS));
781 FOR i IN p_msg_tbl.FIRST .. p_msg_tbl.LAST LOOP
782 tempNode := XMLDOM.appendChild(node, newExplanationNode(p_msg_tbl(i)));
783 END LOOP;
784 RETURN node;
785 END newViolationNode;
786
787 --------------------------------------------------------------------------------
788 -- <CONNECTION id="1676995111" name="BtF" ref="FloorInBuilding"
789 -- fromCompId="1676995126" fromCompName="Building-1" fromCompType="Building"
790 -- toCompId="1676995128" toCompName="Floor-1" toCompType="Floor"/>
791 FUNCTION newConnectorNode(p_conn_rec conn_rec_type)
792 RETURN XMLDOM.DOMNode
793 IS
794 node XMLDOM.DOMNode;
795 attrs XMLDOM.DOMNamedNodeMap;
796 BEGIN
797 node := XMLDOM.cloneNode(l_tmplConNode, FALSE);
798 attrs := XMLDOM.getAttributes(node);
799 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 0)), p_conn_rec.id);
800 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 1)), p_conn_rec.name);
801 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 2)), p_conn_rec.ref);
802 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 3)), p_conn_rec.fromCompID);
803 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 4)), p_conn_rec.fromCompName);
804 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 5)), p_conn_rec.fromCompType);
805 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 6)), p_conn_rec.toCompID);
806 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 7)), p_conn_rec.toCompName);
807 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(attrs, 8)), p_conn_rec.toCompType);
808 RETURN node;
809 END newConnectorNode;
810
811 --------------------------------------------------------------------------------
812 PROCEDURE get_config_msg(p_hdr_id IN NUMBER, p_rev_nbr IN NUMBER)
813 IS
814 l_msg_tbl v2k_tbl_type;
815 BEGIN
816 FOR i IN (SELECT config_item_id, message
817 FROM cz_config_messages
818 WHERE config_hdr_id = p_hdr_id AND config_rev_nbr = p_rev_nbr AND deleted_flag = '0')
819 LOOP
820 IF l_cfg_msg_map.EXISTS(i.config_item_id) THEN
821 l_cfg_msg_map(i.config_item_id)(l_cfg_msg_map(i.config_item_id).COUNT+1) := i.message;
822 ELSE
823 l_msg_tbl(1) := i.message;
824 l_cfg_msg_map(i.config_item_id) := l_msg_tbl;
825 END IF;
826 END LOOP;
827 END get_config_msg;
828
829 --------------------------------------------------------------------------------
830 PROCEDURE get_connector_info(p_hdr_id IN NUMBER, p_rev_nbr IN NUMBER)
831 IS
832 l_target_item_map v2k_tbl_type;
833 l_conn_rec conn_rec_type;
834 BEGIN
835 FOR i IN (SELECT con.config_item_id conid, con.name conname, con.ps_node_name conpsname,
836 tgt.config_item_id tgtid, tgt.name tgtname, tgt.ps_node_name tgtpsname, tgt.instance_nbr tgtinsnbr
837 FROM cz_config_items con, cz_config_items tgt
838 WHERE con.config_hdr_id = p_hdr_id AND con.config_rev_nbr = p_rev_nbr AND con.deleted_flag = '0'
839 AND tgt.config_hdr_id = p_hdr_id and tgt.config_rev_nbr = p_rev_nbr AND tgt.deleted_flag = '0'
840 AND con.target_config_item_id = tgt.config_item_id
841 AND con.value_type_code = CONNECTOR)
842 LOOP
843 l_conn_rec.id := i.conid;
844 l_conn_rec.name := i.conpsname;
845 l_conn_rec.ref := NVL(i.conname, i.conpsname);
846 l_conn_rec.toCompID := i.tgtid;
847 l_conn_rec.toCompName := NVL(i.tgtname, i.tgtpsname||'-'||i.tgtinsnbr);
848 l_conn_rec.toCompType := i.tgtpsname;
849 l_conn_map(i.conid) := l_conn_rec;
850 END LOOP;
851 END get_connector_info;
852
853 --------------------------------------------------------------------------------
854
855 PROCEDURE get_ps_nodes(p_model_id IN NUMBER)
856 IS
857 l_type INTEGER;
858 BEGIN
859 FOR i IN (SELECT devl_project_id, ps_node_id, persistent_node_id, ps_node_type,
860 reference_id, feature_type, minimum, maximum, itm.orig_sys_ref osr
861 FROM cz_ps_nodes psn, cz_item_masters itm
862 WHERE psn.deleted_flag = '0' AND psn.devl_project_id IN
863 (SELECT component_id FROM cz_model_ref_expls
864 WHERE deleted_flag = '0' AND model_id = p_model_id
865 AND (model_id = component_id OR ps_node_type = PS_NODE_TYPE_REFERENCE))
866 AND psn.item_id = itm.item_id(+) AND '0' = itm.deleted_flag(+))
867 LOOP
868 l_type := i.ps_node_type;
869 IF l_type = PS_NODE_TYPE_REFERENCE THEN
870 l_ref_node_map(i.persistent_node_id) := i.reference_id;
871 ELSIF l_type = PS_NODE_TYPE_FEATURE THEN
872 IF i.feature_type = OPTION_FEATURE THEN
873 IF i.minimum = 1 AND i.maximum = 1 THEN
874 l_type := OPTION_FEATURE_1_1;
875 ELSE
876 l_type := OPTION_FEATURE_0_N;
877 END IF;
881 l_type := NUM_FEATURE;
878 ELSIF i.feature_type = TEXT_FEATURE THEN
879 l_type := TEXT_FEATURE;
880 ELSE
882 END IF;
883 ELSIF l_type = PS_NODE_TYPE_TOTAL OR l_type = PS_NODE_TYPE_RESOURCE THEN
884 l_type := NUM_FEATURE;
885 END IF;
886
887 IF i.osr IS NOT NULL AND instr(i.osr, 'PKG:') > 0 THEN
888 l_prj_psn_osr_map(i.devl_project_id)(i.persistent_node_id) := substr(i.osr, 5);
889 l_has_pkg_component := TRUE;
890 END IF;
891 l_prj_psn_type_map(i.devl_project_id)(i.persistent_node_id) := l_type;
892 END LOOP;
893 END get_ps_nodes;
894
895 --------------------------------------------------------------------------------
896 PROCEDURE get_properties(p_model_id IN NUMBER)
897 IS
898 l_val cz_ps_prop_vals.data_value%TYPE;
899 l_prp_count INTEGER;
900 BEGIN
901 FOR i IN (SELECT prp.property_id id, prp.name name, prp.data_type type, prp.def_value defval,
902 psp.data_value strval, prp.def_num_value defnumval, psp.data_num_value numval,
903 psn.devl_project_id project, psn.persistent_node_id persistentid
904 FROM cz_ps_nodes psn, cz_ps_prop_vals psp, cz_properties prp
905 WHERE psn.deleted_flag = '0' AND psn.devl_project_id IN
906 (SELECT component_id FROM cz_model_ref_expls
907 WHERE deleted_flag = '0' AND model_id = p_model_id
908 AND (model_id = component_id OR ps_node_type = PS_NODE_TYPE_REFERENCE))
909 AND psp.deleted_flag = '0' AND psp.ps_node_id = psn.ps_node_id
910 AND prp.deleted_flag = '0' AND prp.property_id = psp.property_id
911 AND prp.data_type <> TRANSLATABLE_TEXT
912 UNION ALL
913 SELECT prp.property_id id, prp.name name, prp.data_type type, deftxt.text_str defval,
914 txt.text_str strval, prp.def_num_value defnumval, psp.data_num_value numval,
915 psn.devl_project_id project, psn.persistent_node_id persistentid
916 FROM cz_ps_nodes psn, cz_ps_prop_vals psp, cz_properties prp,
917 cz_intl_texts deftxt, cz_intl_texts txt
918 WHERE psn.deleted_flag = '0' AND psn.devl_project_id IN
919 (SELECT component_id FROM cz_model_ref_expls
920 WHERE deleted_flag = '0' AND model_id = p_model_id
921 AND (model_id = component_id OR ps_node_type = PS_NODE_TYPE_REFERENCE))
922 AND psp.deleted_flag = '0' AND psp.ps_node_id = psn.ps_node_id
923 AND prp.deleted_flag = '0' AND prp.property_id = psp.property_id
924 AND prp.data_type = TRANSLATABLE_TEXT
925 AND prp.def_num_value = deftxt.intl_text_id
926 AND psp.data_num_value = txt.intl_text_id )
927 LOOP
928 IF i.type = NON_TRANSLATABLE_TEXT OR i.type = TRANSLATABLE_TEXT THEN
929 l_val := NVL(i.strval, i.defval);
930 ELSE
931 l_val := NVL(i.numval, i.defnumval);
932 END IF;
933
934 IF l_prj_psn_prp_map.EXISTS(i.project) AND l_prj_psn_prp_map(i.project).EXISTS(i.persistentid) THEN
935 l_prp_count := l_prj_psn_prp_map(i.project)(i.persistentid).COUNT + 1;
936 ELSE
937 l_prp_count := 1;
938 END IF;
939 -- l_prj_psn_prp_map(i.project)(i.persistentid)(i.id).name := i.name;
940 -- l_prj_psn_prp_map(i.project)(i.persistentid)(i.id).val := l_val;
941 l_prj_psn_prp_map(i.project)(i.persistentid)(l_prp_count).name := i.name;
942 l_prj_psn_prp_map(i.project)(i.persistentid)(l_prp_count).val := l_val;
943 END LOOP;
944 END get_properties;
945
946 --------------------------------------------------------------------------------
947
948 FUNCTION construct_init_xml(p_hdr_id IN NUMBER
949 ,p_rev_nbr IN NUMBER
950 ,p_product_key IN VARCHAR2
951 ,p_is_valid IN BOOLEAN
952 ,p_rpt_recur_prc IN BOOLEAN
953 ,p_rpt_delta IN BOOLEAN
954 )
955 RETURN VARCHAR2
956 IS
957 l_config VARCHAR2(30);
958 l_rootComp VARCHAR2(400);
959 l_valid VARCHAR2(50);
960 l_sectionMain VARCHAR2(1000);
961 l_sectionPkg VARCHAR2(100);
962 l_sectionPrice VARCHAR2(1000);
963 l_sectionDelta VARCHAR2(1000);
964 l_xml VARCHAR2(10000);
965 l_templates VARCHAR2(1000);
966 BEGIN
967 l_config := to_char(p_hdr_id) || ':' || to_char(p_rev_nbr);
968 l_rootComp := '<CONFIGURABLE_COMPONENT name="'||p_product_key||'" component="'||p_product_key||'">';
969
970 l_sectionPkg := '<SECTION nm="Package Components">' || fnd_global.newline
971 || '<COMPONENTS>' || fnd_global.newline
972 || '</COMPONENTS>' || fnd_global.newline
973 || '</SECTION>';
974
975 IF p_rpt_recur_prc THEN
976 l_sectionPrice := '<SECTION nm="Recurring Price">' || fnd_global.newline
977 || '<COMPONENTS>' || fnd_global.newline
978 || l_rootComp || fnd_global.newline
979 || '<CONFIGURATION>' || fnd_global.newline
980 || '<NUMERIC_VALUES>' || fnd_global.newline
981 -- || '<NUM NM="fxRecurringFrequencyCode" VL="10" TY="STRING"/>' || fnd_global.newline
982 -- || '<NUM NM="fxRecurringFrequencyText" VL="Monthly" TY="STRING"/>' || fnd_global.newline
983 -- || '<NUM NM="exRecurringPrice" VL="52.0" TY="DOUBLE"/>' || fnd_global.newline
984 || '</NUMERIC_VALUES>' || fnd_global.newline
985 || '</CONFIGURATION>' || fnd_global.newline
986 || '</CONFIGURABLE_COMPONENT>' || fnd_global.newline
987 || '</COMPONENTS>' || fnd_global.newline
988 || '</SECTION>';
989 END IF;
990
991 /*
992 IF p_rpt_delta THEN
993 l_sectionDelta := '<DELTA_INFO>' || fnd_global.newline
994 || '<COMPONENT id="" name="" type="" isPkgCmpnt="false">' || fnd_global.newline
998 || '</DELTA_INFO>';
995 || '<CONFIG_DELTA>' || fnd_global.newline
996 || '</CONFIG_DELTA>' || fnd_global.newline
997 || '</COMPONENT>' || fnd_global.newline
999 END IF;
1000 */
1001
1002 IF p_is_valid THEN
1003 l_valid := 'hasViolations="false" isValid="true"';
1004 ELSE
1005 l_valid := 'hasViolations="true" isValid="false"';
1006 END IF;
1007 l_sectionMain := '<SECTION nm="Main">' || fnd_global.newline
1008 || '<COMPONENTS>' || fnd_global.newline
1009 -- || l_rootComp || fnd_global.newline
1010 -- || '<CONFIGURATION><CHOICES/></CONFIGURATION>' || fnd_global.newline
1011 -- || '</CONFIGURABLE_COMPONENT>'||fnd_global.newline
1012 || '</COMPONENTS>' || fnd_global.newline
1013 || '<STRUCTURE>' || fnd_global.newline
1014 -- || l_rootComp || fnd_global.newline
1015 -- || '</CONFIGURABLE_COMPONENT>' || fnd_global.newline
1016 || '</STRUCTURE>' || fnd_global.newline
1017 || '</SECTION>';
1018
1019 l_templates := '<templates>' || fnd_global.newline
1020 || '<CONFIGURABLE_COMPONENT name="" component=""></CONFIGURABLE_COMPONENT>' || fnd_global.newline
1021 || '<CH DP="" DM="" ST="66" QTY=""/>' || fnd_global.newline
1022 || '<ATTR NM="">a</ATTR>' || fnd_global.newline
1023 || '<NUM NM="" VL="" TY=""/>' || fnd_global.newline
1024 || '<EXPLANATION>Whatever</EXPLANATION>' || fnd_global.newline
1025 || '<COMPONENT id="" name="" type="" isPkgCmpnt="false"/>' || fnd_global.newline
1026 || '<DELTA_EXPR NM="" PREVIOUS="" CURRENT=""/>' || fnd_global.newline
1027 || '<CONNECTION id="" name="" ref="" fromCompId="" fromCompName="" fromCompType="" toCompId="" toCompName="" toCompType=""/>' || fnd_global.newline
1028 || '</templates>';
1029
1030 l_xml := '<CONFIG_DETAILS configId="' || l_config ||'" solutionId="'||p_product_key||'" '
1031 || l_valid || ' TOTAL_PRICE="">' || fnd_global.newline
1032 || l_templates || fnd_global.newline
1033 || l_sectionMain || fnd_global.newline
1034 || l_sectionPkg;
1035 IF l_sectionPrice IS NOT NULL THEN
1036 l_xml := l_xml || fnd_global.newline || l_sectionPrice;
1037 END IF;
1038
1039 /*
1040 IF l_sectionDelta IS NOT NULL THEN
1041 l_xml := l_xml || fnd_global.newline || l_sectionDelta;
1042 END IF;
1043 */
1044
1045 l_xml := l_xml || fnd_global.newline || '</CONFIG_DETAILS>';
1046 RETURN l_xml;
1047 END construct_init_xml;
1048
1049 --------------------------------------------------------------------------------
1050 --------------------------------------------------------------------------------
1051 -- begin get_config_details
1052 BEGIN
1053 IF (NOT FND_API.compatible_api_call(l_api_version
1054 ,p_api_version
1055 ,l_api_name
1056 ,G_PKG_NAME
1057 )) THEN
1058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1059 END IF;
1060
1061 l_ndebug:=0;
1062 l_log_stmt := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1063 IF l_log_stmt THEN
1064 l_msg := 'inputs: hdr=' || p_config_hdr_id ||':' || p_config_rev_nbr
1065 || ', prd=' || p_product_key || ', appl=' || p_application_id;
1066 END IF;
1067
1068 BEGIN
1069 SELECT component_id, config_status, has_failures
1070 INTO l_model_id, l_config_status, l_has_failures
1071 FROM cz_config_hdrs
1072 WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
1073 AND deleted_flag = '0' AND component_instance_type = ROOT;
1074 EXCEPTION
1075 WHEN NO_DATA_FOUND THEN
1076 fnd_message.set_name('CZ', 'CZ_NO_CFG_HDR');
1077 fnd_message.set_token('id', p_config_hdr_id);
1078 fnd_message.set_token('rev', p_config_rev_nbr);
1079 fnd_message.set_token('type', 'R');
1080 fnd_msg_pub.add;
1081 RAISE FND_API.G_EXC_ERROR;
1082 END;
1083
1084 l_ndebug:=10;
1085 -- check if config source model as well as all its child models is available
1086 BEGIN
1087 SELECT devl_project_id INTO l_model_id
1088 FROM cz_devl_projects
1089 WHERE devl_project_id = l_model_id AND deleted_flag = '0';
1090 EXCEPTION
1091 WHEN NO_DATA_FOUND THEN
1092 l_model_id := NULL;
1093 END;
1094
1095 IF l_model_id IS NOT NULL THEN
1096 BEGIN
1097 SELECT NULL INTO l_model_id
1098 FROM cz_model_ref_expls re
1099 WHERE deleted_flag = '0' AND model_id = l_model_id
1100 AND ps_node_type = PS_NODE_TYPE_REFERENCE
1101 AND NOT EXISTS (SELECT 1 FROM cz_devl_projects
1102 WHERE deleted_flag = '0' AND devl_project_id = re.component_id)
1103 AND ROWNUM < 2;
1104
1105 EXCEPTION
1106 WHEN NO_DATA_FOUND THEN
1107 NULL;
1108 END;
1109 END IF;
1110
1111 l_ndebug:=20;
1112 IF l_model_id IS NULL THEN
1113 l_msg := 'Perform publication lookup';
1114 cz_cf_api.default_restored_cfg_dates(p_config_hdr_id => p_config_hdr_id
1115 ,p_config_rev_nbr => p_config_rev_nbr
1116 ,p_creation_date => l_creation_date
1117 ,p_lookup_date => l_lookup_date
1118 ,p_effective_date => l_effective_date);
1119 l_model_id := cz_cf_api.config_model_for_product(product_key => p_product_key
1120 ,config_lookup_date => l_lookup_date
1121 ,calling_application_id => p_application_id
1122 ,usage_name => NULL);
1123 IF l_model_id IS NULL THEN
1127 fnd_msg_pub.add;
1124 fnd_message.set_name('CZ', 'CZ_NO_PUB_MODEL');
1125 fnd_message.set_token('PRDKEY', p_product_key);
1126 fnd_message.set_token('APPLID', p_application_id);
1128 RAISE FND_API.G_EXC_ERROR;
1129 END IF;
1130 END IF;
1131
1132 l_ndebug:=30;
1133 l_has_pkg_component := FALSE;
1134 get_ps_nodes(l_model_id);
1135 -- print_refnode_map(l_ref_node_map);
1136 -- print_psnode_map(l_prj_psn_type_map);
1137 -- print_psnode_map(l_prj_psn_osr_map);
1138
1139 l_ndebug:=40;
1140 get_properties(l_model_id);
1141 -- print_prp_map(l_prj_psn_prp_map);
1142
1143 l_ndebug:=50;
1144 l_is_valid := l_config_status = COMPLETE AND l_has_failures = '0';
1145
1146 IF p_check_violation_flag = FND_API.G_TRUE AND l_has_failures <> '0' THEN
1147 get_config_msg(p_config_hdr_id, p_config_rev_nbr);
1148 -- print_msg_map;
1149 END IF;
1150
1151 l_ndebug:=60;
1152 IF p_check_connection_flag = FND_API.G_TRUE THEN
1153 get_connector_info(p_config_hdr_id, p_config_rev_nbr);
1154 END IF;
1155
1156 l_ndebug:=70;
1157 l_check_delta := FALSE;
1158 IF p_baseline_config_hdr_id IS NOT NULL AND p_baseline_config_rev_nbr IS NOT NULL THEN
1159 l_check_delta := TRUE;
1160 BEGIN
1161 SELECT 1 INTO l_count
1162 FROM cz_config_hdrs
1163 WHERE config_hdr_id = p_baseline_config_hdr_id AND config_rev_nbr = p_baseline_config_rev_nbr
1164 AND deleted_flag = '0' AND component_instance_type = ROOT;
1165 EXCEPTION
1166 WHEN NO_DATA_FOUND THEN
1167 fnd_message.set_name('CZ', 'CZ_NO_BASE_CFG_HDR');
1168 fnd_message.set_token('id', p_config_hdr_id);
1169 fnd_message.set_token('rev', p_config_rev_nbr);
1170 fnd_message.set_token('type', 'R');
1171 fnd_msg_pub.add;
1172 RAISE FND_API.G_EXC_ERROR;
1173 END;
1174
1175 END IF;
1176
1177 l_ndebug:=80;
1178 l_rpt_total_price := FALSE;
1179 l_rpt_recur_price := FALSE;
1180 IF p_price_info_list IS NOT NULL AND p_price_info_list.COUNT > 0 THEN
1181 IF p_price_info_list(1) IS NOT NULL THEN
1182 l_rpt_total_price := TRUE;
1183 END IF;
1184
1185 IF p_price_info_list.COUNT > 1 AND p_price_info_list(2) IS NOT NULL THEN
1186 l_rpt_recur_price := TRUE;
1187 END IF;
1188 END IF;
1189
1190 l_init_xml_str := construct_init_xml(p_config_hdr_id
1191 ,p_config_rev_nbr
1192 ,p_product_key
1193 ,l_is_valid
1194 ,l_rpt_recur_price
1195 ,l_check_delta
1196 );
1197
1198 l_ndebug:=90;
1199 l_doc := XMLDOM.newDomDocument(XMLType(l_init_xml_str));
1200 l_docelm := XMLDOM.getDocumentElement(l_doc);
1201 l_sections := XMLDOM.getChildNodes(XMLDOM.makeNode(l_docelm));
1202
1203 l_templateNode := XMLDOM.item(l_sections, 0);
1204 l_tmplNodes := XMLDOM.getChildNodes(l_templateNode);
1205 l_tmplCcNode := xmldom.item(l_tmplNodes, 0);
1206 l_tmplChNode := xmldom.item(l_tmplNodes, 1);
1207 l_tmplAtNode := xmldom.item(l_tmplNodes, 2);
1208 l_tmplNumNode := xmldom.item(l_tmplNodes, 3);
1209 l_tmplXplNode := xmldom.item(l_tmplNodes, 4);
1210 l_tmplComNode := xmldom.item(l_tmplNodes, 5);
1211 l_tmplDeNode := xmldom.item(l_tmplNodes, 6);
1212 l_tmplConNode := xmldom.item(l_tmplNodes, 7);
1213
1214 l_sectionMainNode := XMLDOM.item(l_sections, 1); -- <SECTION nm="Main">
1215 l_componentsNode := XMLDOM.getFirstChild(l_sectionMainNode); -- <COMPONENTS>
1216 l_structNode := XMLDOM.getNextSibling(l_componentsNode); -- <STRUCTURE>
1217
1218 IF l_has_pkg_component THEN
1219 l_pkgComponentsNode := XMLDOM.getFirstChild(XMLDOM.item(l_sections, 2));
1220 END IF;
1221
1222 IF l_rpt_recur_price THEN -- <SECTION nm="Recurring Price">
1223 IF l_has_pkg_component THEN
1224 l_prcNumericValuesNode := XMLDOM.getFirstChild(XMLDOM.getFirstChild(XMLDOM.getFirstChild(XMLDOM.getFirstChild(XMLDOM.item(l_sections, 3)))));
1225 ELSE
1226 l_prcNumericValuesNode := XMLDOM.getFirstChild(XMLDOM.getFirstChild(XMLDOM.getFirstChild(XMLDOM.getFirstChild(XMLDOM.item(l_sections, 2)))));
1227 END IF;
1228 END IF;
1229
1230 /* *** delta_info part *** */
1231 IF l_check_delta THEN
1232 l_delta_info_Node := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_DELTA_INFO));
1233
1234 l_component_adds_Node := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_COMPONENT_ADDS));
1235
1236 l_component_deletes_Node := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_COMPONENT_DELETES));
1237
1238 l_tmp_Node := XMLDOM.appendChild(l_delta_info_Node, l_component_adds_Node);
1239
1240 l_tmp_Node := XMLDOM.appendChild(l_delta_info_Node, l_component_deletes_Node);
1241
1242 l_tmp_Node := XMLDOM.appendChild(xmldom.makeNode(l_docelm), l_delta_info_Node);
1243
1244 get_config_item_data(p_baseline_config_hdr_id, p_baseline_config_rev_nbr, l_baseline_cfg_tbl);
1245
1246 FOR itm IN(SELECT parent_config_item_id,name,ps_node_name,instance_nbr FROM CZ_CONFIG_ITEMS
1247 WHERE config_hdr_id=p_baseline_config_hdr_id AND
1248 config_rev_nbr=p_baseline_config_rev_nbr AND
1249 value_type_code = COMPONENT AND
1250 deleted_flag='0' AND
1251 (config_item_id,item_num_val) NOT IN
1252 (SELECT a.config_item_id,a.item_num_val FROM CZ_CONFIG_ITEMS a
1253 WHERE a.config_hdr_id=p_config_hdr_id AND
1254 a.config_rev_nbr=p_config_rev_nbr AND
1255 a.value_type_code = COMPONENT AND
1256 a.deleted_flag='0')
1260 l_comp_name := NVL(itm.name, itm.ps_node_name);
1257 )
1258 LOOP
1259 IF itm.parent_config_item_id IS NULL OR itm.parent_config_item_id = NO_VALUE THEN
1261 ELSE
1262 l_comp_name := NVL(itm.name, itm.ps_node_name||'-'||itm.instance_nbr);
1263 END IF;
1264
1265 l_delta_cfgCompNode := newCfgComponentNode(l_comp_name, itm.ps_node_name);
1266 l_tmp_Node := XMLDOM.appendChild(l_component_deletes_Node, l_delta_cfgCompNode);
1267 END LOOP;
1268
1269 END IF;
1270 /* *** end of delta_info part *** */
1271
1272 l_ndebug:=100;
1273 -- Todo: may be we can use a single query for both i and j
1274 FOR i IN
1275 (SELECT config_item_id, instance_nbr, ps_node_id, name, ps_node_name, parent_config_item_id
1276 FROM cz_config_items
1277 WHERE deleted_flag = '0' AND value_type_code = COMPONENT
1278 START WITH config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
1279 AND component_instance_type = ROOT
1280 CONNECT BY config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
1281 AND deleted_flag = '0' AND PRIOR config_item_id = parent_config_item_id)
1282 LOOP
1283 l_configurationNode := NULL;
1284 l_choicesNode := NULL;
1285 l_numericValuesNode := NULL;
1286 l_is_root_comp := FALSE;
1287 IF i.parent_config_item_id IS NULL OR i.parent_config_item_id = NO_VALUE THEN
1288 l_is_root_comp := TRUE;
1289 END IF;
1290
1291 -- create comp node for components and structure
1292 IF l_is_root_comp THEN
1293 l_comp_name := NVL(i.name, i.ps_node_name);
1294 ELSE
1295 l_model_id := l_item_prj_map(i.parent_config_item_id);
1296 l_comp_name := NVL(i.name, i.ps_node_name||'-'||i.instance_nbr);
1297 END IF;
1298 l_cfgCompNode := newCfgComponentNode(l_comp_name, i.ps_node_name);
1299 l_tempNode := XMLDOM.appendChild(l_componentsNode, l_cfgCompNode);
1300 l_structCfgCompNode := XMLDOM.cloneNode(l_cfgCompNode, TRUE);
1301 l_domnode_map(i.config_item_id) := l_structCfgCompNode;
1302 IF l_is_root_comp THEN
1303 l_tempNode := XMLDOM.appendChild(l_structNode, l_structCfgCompNode);
1304 ELSE
1305 l_tempNode := XMLDOM.appendChild(l_domnode_map(i.parent_config_item_id), l_structCfgCompNode);
1306 END IF;
1307
1308 l_pkgConfigurationNode := NULL;
1309 l_pkgCfgCompNode := NULL;
1310 l_is_pkg_component := l_prj_psn_osr_map.EXISTS(l_model_id) AND l_prj_psn_osr_map(l_model_id).EXISTS(i.ps_node_id);
1311 IF l_is_pkg_component THEN
1312 l_pkgCfgCompNode := XMLDOM.cloneNode(l_cfgCompNode, TRUE);
1313 l_tempnode := XMLDOM.appendChild(l_pkgComponentsNode, l_pkgCfgCompNode);
1314 END IF;
1315
1316 l_com_msg_tbl.DELETE;
1317 IF l_cfg_msg_map.EXISTS(i.config_item_id) THEN
1318 l_com_msg_tbl := l_cfg_msg_map(i.config_item_id);
1319 END IF;
1320
1321 -- Could non-option node (e.g., features) have attribute?
1322 -- If no, we can check attrs for this component here
1323 -- Else need to do the check at the end of processing this subtree
1324
1325 IF l_ref_node_map.EXISTS(i.ps_node_id) THEN
1326 l_model_id := l_ref_node_map(i.ps_node_id);
1327 END IF;
1328 l_item_prj_map(i.config_item_id) := l_model_id;
1329
1330 /* *** delta_info part *** */
1331 IF l_check_delta THEN
1332
1333 IF NOT(l_baseline_cfg_tbl.EXISTS(i.config_item_id)) THEN
1334 l_delta_cfgCompNode := newCfgComponentNode(l_comp_name, i.ps_node_name);
1335 l_tmp_Node := XMLDOM.appendChild(l_component_adds_Node, l_delta_cfgCompNode);
1336 ELSE
1337 addDeltaComponentNode(TO_CHAR(i.config_item_id), l_comp_name);
1338 END IF;
1339 END IF;
1340 /* *** end of delta_info part *** */
1341
1342 l_ndebug:=200;
1343 l_parent_item := NULL;
1344 l_parent_name := NULL;
1345
1346 l_handled_nodes_tbl.DELETE;
1347
1348 FOR j IN
1349 (SELECT config_item_id, parent_config_item_id, ps_node_id, ps_node_name, item_num_val, item_val
1350 FROM cz_config_items
1351 WHERE deleted_flag = '0'
1352 START WITH config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
1353 AND parent_config_item_id = i.config_item_id AND value_type_code <> COMPONENT
1354 -- AND config_item_id = i.config_item_id
1355 CONNECT BY config_hdr_id = p_config_hdr_id and config_rev_nbr = p_config_rev_nbr
1356 AND deleted_flag = '0' AND value_type_code <> COMPONENT
1357 AND PRIOR config_item_id = parent_config_item_id)
1358 LOOP
1359
1360 l_exists_in_baseline := l_baseline_cfg_tbl.EXISTS(j.config_item_id);
1361
1362 l_chNode := NULL;
1363 l_numNode := NULL;
1364 l_connNode := NULL;
1365 l_pkgChNode := NULL;
1366 IF l_is_root_comp THEN
1367 IF l_rpt_total_price AND j.ps_node_name = p_price_info_list(1) THEN
1368 XMLDOM.setValue(XMLDOM.makeAttr(XMLDOM.item(XMLDOM.getAttributes(XMLDOM.makeNode(l_docelm)), 4)), j.item_num_val);
1369 ELSIF l_rpt_recur_price AND
1370 (p_price_info_list.COUNT > 1 AND j.ps_node_name = p_price_info_list(2) OR
1371 p_price_info_list.COUNT > 2 AND j.ps_node_name = p_price_info_list(3) OR
1372 p_price_info_list.COUNT > 3 AND j.ps_node_name = p_price_info_list(4)) THEN
1373 l_typeAttrVal := 'STRING';
1374 IF j.item_val IS NULL AND j.item_num_val IS NOT NULL THEN
1375 l_typeAttrVal := 'DOUBLE';
1376 END IF;
1377 l_tempnode := XMLDOM.appendChild(l_prcNumericValuesNode,
1378 newNumNode(j.ps_node_name, nvl(j.item_val, to_char(j.item_num_val)), l_typeAttrVal));
1379 END IF;
1380 END IF;
1381
1382 l_ndebug:=210;
1383 l_is_pkg_component := l_prj_psn_osr_map.EXISTS(l_model_id) AND
1387 IF l_psn_type = OPTION_FEATURE_1_1 OR l_psn_type = OPTION_FEATURE_0_N THEN
1384 l_prj_psn_osr_map(l_model_id).EXISTS(j.ps_node_id);
1385 l_psn_type := l_prj_psn_type_map(l_model_id)(j.ps_node_id);
1386
1388 l_parent_item := j.config_item_id;
1389 l_parent_name := j.ps_node_name;
1390 l_parent_psn_type := l_psn_type;
1391 ELSIF l_psn_type = PS_NODE_TYPE_OPTION THEN
1392 IF XMLDOM.isNull(l_configurationNode) THEN
1393 l_configurationNode := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_CONFIGURATION));
1394 l_tempNode := XMLDOM.appendChild(l_cfgCompNode, l_configurationNode);
1395 END IF;
1396
1397 IF XMLDOM.isNull(l_choicesNode) THEN
1398 l_choicesNode := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_CHOICES));
1399 l_tempNode := XMLDOM.appendChild(l_configurationNode, l_choicesNode);
1400 END IF;
1401
1402 l_chNode := newChoiceNode(l_parent_name, j.ps_node_name, j.item_num_val);
1403 l_tempNode := XMLDOM.appendChild(l_choicesNode, l_chNode);
1404
1405 /* *** delta_info part *** */
1406 IF l_check_delta AND l_exists_in_baseline=FALSE THEN
1407
1408 IF l_parent_psn_type=OPTION_FEATURE_1_1 THEN
1409
1410 l_delta_choice_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_DELTA_CHOICE));
1411 l_tempNode := XMLDOM.appendChild(l_choice_changes_Node, l_delta_choice_Node);
1412 xmldom.setAttribute(xmldom.makeElement(l_delta_choice_Node), 'DP', l_parent_name);
1413
1414 l_choice_prev_changes_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_PREVIOUS));
1415 l_tempNode := XMLDOM.appendChild(l_delta_choice_Node, l_choice_prev_changes_Node);
1416
1417 l_choice_curr_changes_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_CURRENT));
1418 l_tempNode := XMLDOM.appendChild(l_delta_choice_Node, l_choice_curr_changes_Node);
1419
1420 l_changes_curr_chNode := newChoiceNode(l_parent_name, j.ps_node_name, j.item_num_val);
1421 l_tempNode := XMLDOM.appendChild(l_choice_curr_changes_Node, l_changes_curr_chNode);
1422
1423 IF NOT(l_handled_nodes_tbl.EXISTS(l_parent_name)) THEN
1424
1425 attachPrevChoices(j.parent_config_item_id,l_parent_name,l_choice_prev_changes_Node);
1426 l_handled_nodes_tbl(l_parent_name) := l_parent_name;
1427 END IF;
1428
1429 ELSIF l_parent_psn_type=OPTION_FEATURE_0_N THEN
1430
1431 l_delta_chNode := newChoiceNode(l_parent_name,
1432 j.ps_node_name,
1433 j.item_num_val);
1434 l_tempNode := XMLDOM.appendChild(l_choice_adds_Node, l_delta_chNode);
1435
1436 IF NOT(l_handled_nodes_tbl.EXISTS(l_parent_name)) THEN
1437 attachPrevChoices(j.parent_config_item_id,l_parent_name,l_choice_deletes_Node);
1438 l_handled_nodes_tbl(l_parent_name) := l_parent_name;
1439 END IF;
1440
1441 END IF;
1442
1443 END IF;
1444 /* *** end of delta_info part *** */
1445
1446 IF l_is_pkg_component THEN
1447 IF XMLDOM.isNull(l_pkgCfgCompNode) THEN
1448 l_pkgCfgCompNode := newCfgComponentNode(NVL(i.name, i.ps_node_name||'-'||i.instance_nbr), i.ps_node_name);
1449 l_tempnode := XMLDOM.appendChild(l_pkgComponentsNode, l_pkgCfgCompNode);
1450 END IF;
1451 IF XMLDOM.isNull(l_pkgConfigurationNode) THEN
1452 l_pkgConfigurationNode := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_CONFIGURATION));
1453 l_tempNode := XMLDOM.appendChild(l_pkgCfgCompNode, l_pkgConfigurationNode);
1454 END IF;
1455 IF XMLDOM.isNull(l_pkgChoicesNode) THEN
1456 l_pkgChoicesNode := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_CHOICES));
1457 l_tempNode := XMLDOM.appendChild(l_pkgConfigurationNode, l_pkgChoicesNode);
1458 END IF;
1459 l_pkgChNode := XMLDOM.cloneNode(l_chNode, true);
1460 l_tempNode := XMLDOM.appendChild(l_pkgChoicesNode, l_pkgChNode);
1461 END IF;
1462 ELSIF l_psn_type = PS_NODE_TYPE_CONNECTOR THEN -- l_conn_map.EXISTS(j.config_item_id)
1463 l_conn_map(j.config_item_id).fromCompID := i.config_item_id;
1464 l_conn_map(j.config_item_id).fromCompName := NVL(i.name, i.ps_node_name||'-'||i.instance_nbr);
1465 l_conn_map(j.config_item_id).fromCompType := i.ps_node_name;
1466 l_connNode := newConnectorNode(l_conn_map(j.config_item_id));
1467 l_tempNode := XMLDOM.appendChild(l_CfgCompNode, l_connNode);
1468 l_tempNode := XMLDOM.appendChild(l_structCfgCompNode, XMLDOM.cloneNode(l_connNode, FALSE));
1469 ELSE
1470
1471 IF XMLDOM.isNull(l_configurationNode) THEN
1472 l_configurationNode := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_CONFIGURATION));
1473 l_tempNode := XMLDOM.appendChild(l_cfgCompNode, l_configurationNode);
1474 END IF;
1475 IF XMLDOM.isNull(l_numericValuesNode) THEN
1476 l_numericValuesNode := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_NUMERIC_VALUES));
1477 l_tempNode := XMLDOM.appendChild(XMLDOM.getFirstChild(l_cfgCompNode), l_numericValuesNode);
1478 END IF;
1479 IF l_prj_psn_type_map(l_model_id)(j.ps_node_id) = TEXT_FEATURE THEN
1480 l_numNode := newNumNode(j.ps_node_name, j.item_val, 'String');
1481
1482 /* *** delta_info part *** */
1483 IF l_check_delta AND l_exists_in_baseline THEN
1484
1485 IF l_baseline_cfg_tbl(j.config_item_id).item_val<>j.item_val THEN
1486
1487 add_Expr_Node(p_name => j.ps_node_name,
1488 p_old_value => l_baseline_cfg_tbl(j.config_item_id).item_val,
1489 p_new_value => j.item_val,
1490 p_type => 'STRING');
1491
1492 END IF;
1493
1497 ELSE
1494 END IF;
1495 /* *** end of delta_info part *** */
1496
1498 l_numNode := newNumNode(j.ps_node_name, j.item_num_val, 'Double');
1499
1500 /* *** delta_info part *** */
1501 IF l_check_delta AND l_exists_in_baseline THEN
1502
1503 IF l_baseline_cfg_tbl(j.config_item_id).item_num_val<>j.item_num_val THEN
1504
1505 add_Expr_Node(p_name => j.ps_node_name,
1506 p_old_value => TO_CHAR(l_baseline_cfg_tbl(j.config_item_id).item_num_val),
1507 p_new_value => TO_CHAR(j.item_num_val),
1508 p_type => 'DOUBLE');
1509
1510 END IF;
1511
1512 END IF;
1513 /* *** end of delta_info part *** */
1514
1515 END IF;
1516 l_tempNode := XMLDOM.appendChild(l_numericValuesNode, l_numNode);
1517 END IF;
1518
1519 l_ndebug:=220;
1520 l_msg_tbl.DELETE;
1521 IF l_cfg_msg_map.EXISTS(j.config_item_id) THEN
1522 l_msg_tbl := l_cfg_msg_map(j.config_item_id);
1523 IF l_psn_type IN (PS_NODE_TYPE_OPTION,NUM_FEATURE,TEXT_FEATURE,PS_NODE_TYPE_CONNECTOR) THEN
1524 l_violationNode := newViolationNode(l_msg_tbl);
1525 END IF;
1526 IF l_psn_type = PS_NODE_TYPE_OPTION THEN
1527 l_tempnode := XMLDOM.appendChild(l_chNode, l_violationNode);
1528 ELSIF l_psn_type = NUM_FEATURE OR l_psn_type = TEXT_FEATURE THEN
1529 l_tempNode := XMLDOM.appendChild(l_numNode, l_violationNode);
1530 ELSIF l_psn_type = PS_NODE_TYPE_CONNECTOR THEN
1531 l_tempNode := XMLDOM.appendChild(l_connNode, l_violationNode);
1532 ELSE
1533 l_count := l_com_msg_tbl.COUNT;
1534 FOR m IN l_msg_tbl.FIRST .. l_msg_tbl.LAST LOOP
1535 l_count := l_count + 1;
1536 l_com_msg_tbl(l_count) := l_msg_tbl(m);
1537 END LOOP;
1538 END IF;
1539 END IF;
1540
1541 l_ndebug:=230;
1542 IF l_prj_psn_prp_map.EXISTS(l_model_id) AND l_prj_psn_prp_map(l_model_id).EXISTS(j.ps_node_id) THEN
1543 l_prp_tbl := l_prj_psn_prp_map(l_model_id)(j.ps_node_id);
1544 l_count := l_com_att_tbl.COUNT;
1545 l_leadAttrNodeCreated := FALSE;
1546 FOR n IN l_prp_tbl.FIRST .. l_prp_tbl.LAST LOOP
1547 IF l_psn_type IN (PS_NODE_TYPE_OPTION,NUM_FEATURE,TEXT_FEATURE,PS_NODE_TYPE_CONNECTOR) THEN
1548 l_attrNode := newAttributeNode(l_prp_tbl(n).name, l_prp_tbl(n).val);
1549 END IF;
1550 IF l_psn_type = PS_NODE_TYPE_OPTION THEN
1551 l_tempnode := XMLDOM.appendChild(l_chNode, l_attrNode);
1552 IF l_is_pkg_component THEN
1553 IF NOT l_leadAttrNodeCreated THEN
1554 l_pkgAttrNode := newAttributeNode('0101', l_prj_psn_osr_map(l_model_id)(j.ps_node_id));
1555 l_tempnode := XMLDOM.appendChild(l_pkgChNode, l_pkgAttrNode);
1556 l_leadAttrNodeCreated := TRUE;
1557 END IF;
1558 l_tempnode := XMLDOM.appendChild(l_pkgChNode, XMLDOM.cloneNode(l_attrNode, true));
1559 END IF;
1560 ELSIF l_psn_type = NUM_FEATURE OR l_psn_type = TEXT_FEATURE THEN
1561 l_tempNode := XMLDOM.appendChild(l_numNode, l_attrNode);
1562 ELSIF l_psn_type = PS_NODE_TYPE_CONNECTOR THEN
1563 l_tempNode := XMLDOM.appendChild(l_connNode, l_attrNode);
1564 ELSIF l_psn_type <> OPTION_FEATURE_1_1 AND l_psn_type <> OPTION_FEATURE_0_N THEN
1565 l_count := l_count + 1;
1566 l_com_att_tbl(l_count) := l_prp_tbl(n);
1567 END IF;
1568 END LOOP;
1569 END IF;
1570 END LOOP;
1571
1572 IF l_is_root_comp AND l_config_status <> COMPLETE THEN
1573 l_com_msg_tbl(0):=cz_utils.get_text('CZ_CFG_INCOMPLETE', 'id', p_config_hdr_id, 'rev', p_config_rev_nbr);
1574 END IF;
1575
1576 IF l_com_msg_tbl.COUNT > 0 THEN
1577 l_violationNode := newViolationNode(l_com_msg_tbl);
1578 l_tempnode := XMLDOM.appendChild(XMLDOM.getFirstChild(l_cfgCompNode), l_violationNode);
1579 END IF;
1580
1581 IF l_prj_psn_prp_map.EXISTS(l_model_id) AND l_prj_psn_prp_map(l_model_id).EXISTS(i.ps_node_id) THEN
1582 l_prp_tbl := l_prj_psn_prp_map(l_model_id)(i.ps_node_id);
1583 l_count := l_com_att_tbl.COUNT;
1584 FOR k IN l_prp_tbl.FIRST .. l_prp_tbl.LAST LOOP
1585 l_count := l_count + 1;
1586 l_com_att_tbl(l_count) := l_prp_tbl(k);
1587 END LOOP;
1588
1589 -- IF l_com_att_tbl IS NOT NULL THEN
1590 FOR k IN l_com_att_tbl.FIRST .. l_com_att_tbl.LAST LOOP
1591 l_attrNode := newAttributeNode(l_com_att_tbl(k).name, l_com_att_tbl(k).val);
1592 l_tempnode := XMLDOM.appendChild(l_cfgCompNode, l_attrNode);
1593 END LOOP;
1594 END IF;
1595
1596 /* *** delta_info part *** */
1597 IF l_check_delta THEN
1598 IF NOT(xmldom.hasChildNodes(l_components_Node)) THEN
1599 l_tempNode := xmldom.removeChild(l_delta_info_Node, l_components_Node);
1600 END IF;
1601
1602 IF NOT(xmldom.hasChildNodes(l_choice_adds_Node)) THEN
1603 l_tempNode := xmldom.removeChild(l_cfg_delta_Node, l_choice_adds_Node);
1604 END IF;
1605
1606 IF NOT(xmldom.hasChildNodes(l_choice_deletes_Node)) THEN
1607 l_tempNode := xmldom.removeChild(l_cfg_delta_Node, l_choice_deletes_Node);
1608 END IF;
1609
1610 IF NOT(xmldom.hasChildNodes(l_choice_changes_Node)) THEN
1611 l_tempNode := xmldom.removeChild(l_cfg_delta_Node, l_choice_changes_Node);
1612 END IF;
1613
1614 IF NOT(xmldom.hasChildNodes(l_expr_changes_Node)) THEN
1615 l_tempNode := xmldom.removeChild(l_cfg_delta_Node, l_expr_changes_Node);
1616 END IF;
1617
1618 END IF;
1619 /* *** end of delta_info part *** */
1620
1621 END LOOP;
1622
1623 /* *** delta_info part *** */
1624 IF l_check_delta THEN
1625 IF NOT(xmldom.hasChildNodes(l_component_adds_Node)) THEN
1626 l_tempNode := xmldom.removeChild(l_delta_info_Node, l_component_adds_Node);
1627 END IF;
1628 IF NOT(xmldom.hasChildNodes(l_component_deletes_Node)) THEN
1629 l_tempNode := xmldom.removeChild(l_delta_info_Node, l_component_deletes_Node);
1630 END IF;
1631 END IF;
1632 /* *** end of delta_info part *** */
1633
1634 l_ndebug:=250;
1635 l_tempNode := xmldom.removeChild(xmldom.makeNode(l_docelm), l_templateNode);
1636
1637 x_config_details := ' ';
1638 XMLDOM.writeToClob(l_doc, x_config_details);
1639 XMLDOM.freeDocument(l_doc);
1640 x_return_status := FND_API.G_RET_STS_SUCCESS;
1641
1642 EXCEPTION
1643 WHEN FND_API.G_EXC_ERROR THEN
1644 XMLDOM.freeDocument(l_doc);
1645 x_return_status := FND_API.G_RET_STS_ERROR;
1646 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1647 p_data => x_msg_data);
1648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1649 XMLDOM.freeDocument(l_doc);
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1651 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1652 p_data => x_msg_data);
1653 WHEN OTHERS THEN
1654 XMLDOM.freeDocument(l_doc);
1655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1656 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1657 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name||'.'||l_ndebug);
1658 END IF;
1659
1660 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1661 p_data => x_msg_data);
1662
1663 END get_config_details;
1664
1665 END cz_psft_integration_pvt;