[Home] [Help]
PACKAGE BODY: APPS.AZ_R12_TRANSFORM_CASCADE
Source
1 PACKAGE BODY AZ_R12_TRANSFORM_CASCADE as
2 /* $Header: aztrfmcascadeb.pls 120.23 2008/05/30 11:29:39 hboda ship $ */
3
4 -- Private type declarations
5 TYPE TYP_ASSOC_ARR IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(4000);
6 TYPE TYP_NEST_TAB_VARCHAR IS TABLE OF VARCHAR2(32767);
7
8 -- Private constant declarations
9 DIFF_SCHEMA_URL VARCHAR2(4000); -- global variable
10 v_dml_count NUMBER;
11 commit_batch_size NUMBER;
12
13 -- Private variable declarations
14 FUNCTION GET_MAPPED_ATTRIBUTES(P_DEPENDANT_API_CODE IN VARCHAR2,
15 P_REQUIRED_API_CODE IN VARCHAR2)
16 RETURN TYP_ASSOC_ARR;
17
18 FUNCTION GET_TRANSFORM_SQL(P_REQUEST_ID IN NUMBER,
19 P_DEPENDANT_SOURCE IN VARCHAR2,
20 P_XSL_STRING IN VARCHAR2,
21 P_PARENT_ID IN NUMBER,
22 P_MASTER_FLAG IN VARCHAR2) RETURN VARCHAR2;
23 PROCEDURE APPLY_TRANSFORM(P_REQUEST_ID IN NUMBER,
24 P_REQUIRED_API_CODE IN VARCHAR2,
25 P_DEPENDANT_API_CODE IN VARCHAR2,
26 P_REQUIRED_SOURCE IN VARCHAR2,
27 P_DEPENDANT_SOURCE IN VARCHAR2,
28 P_DEPENDANT_eo_code IN VARCHAR2);
29
30 PROCEDURE RAISE_ERROR_MSG(ERRCODE IN NUMBER,
31 ERRMSG IN VARCHAR2,
32 PROCEDURENAME IN VARCHAR2,
33 STATEMENT IN VARCHAR2);
34
35 FUNCTION get_transform_all_sql(
36 P_EXISTSNODE_STRING IN VARCHAR2,
37 p_request_id IN NUMBER,
38 p_source IN VARCHAR2)
39 RETURN VARCHAR2;
40 PROCEDURE update_master_flag(P_REQUEST_ID IN NUMBER,
41 P_SOURCE IN VARCHAR2,
42 p_column_name IN VARCHAR2,
43 p_id_list IN TYP_NEST_TAB_VARCHAR, p_upd_entire_tree_flag IN VARCHAR2);
44 ---Proc to update the count in selection set XML
45 PROCEDURE update_conflict_status(P_REQUEST_ID IN NUMBER,
46 P_SOURCE IN VARCHAR2);
47
48 PROCEDURE UPDATE_XSL_EXISTSNODE_STR ( P_REQUEST_ID IN NUMBER,
49 P_SOURCE IN VARCHAR2,
50 P_ATTR_NAME IN VARCHAR2,
51 P_ATTR_NEW_VALUE IN VARCHAR2,
52 P_PARENT_ATTR_NAME IN VARCHAR2,
53 P_UPDATE_XSL IN OUT NOCOPY VARCHAR2,
54 P_EXISTSNODE_STRING IN OUT NOCOPY VARCHAR2);
55
56 c_log_head constant VARCHAR2(200) := 'az.plsql.az_r12_transform_cascade.';
57
58 -- Function and procedure implementations
59 PROCEDURE apply_transform_to_tree(P_REQUEST_ID IN NUMBER,
60 P_REQUIRED_API_CODE IN VARCHAR2,
61 P_DEPENDANT_API_CODE IN VARCHAR2,
62 P_REQUIRED_SOURCE IN VARCHAR2,
63 p_dependant_eo_code IN VARCHAR2,
64 p_diff_schema_url IN VARCHAR2) IS
65 v_eo_name_List TYP_NEST_TAB_VARCHAR;
66 v_eo_code_List TYP_NEST_TAB_VARCHAR;
67 v_ref_eo_code_List TYP_NEST_TAB_VARCHAR;
68 v_source_List TYP_NEST_TAB_VARCHAR;
69 BEGIN
70
71 -- For logging
72 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
73 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ', 'In apply_transform_to_tree procedure');
74 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ', 'P_REQUEST_ID ---> '||P_REQUEST_ID);
75 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ', 'P_REQUIRED_API_CODE ---> '||P_REQUIRED_API_CODE);
76 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ', 'P_DEPENDANT_API_CODE ---> '||P_DEPENDANT_API_CODE);
77 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ', 'P_REQUIRED_SOURCE ---> '||P_REQUIRED_SOURCE);
78 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ', 'p_dependant_eo_code ---> '||p_dependant_eo_code);
79 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ', 'p_diff_schema_url ---> '||p_diff_schema_url);
80 END IF;
81 -- For logging
82
83 DIFF_SCHEMA_URL := p_diff_schema_url;
84 commit_batch_size := fnd_profile.VALUE('AZ_COMMIT_ROWCOUNT');
85 v_dml_count := 0;
86
87 select NAME, code, REF, SOURCE
88 BULK COLLECT INTO
89 v_eo_name_List, v_eo_code_List, v_ref_eo_code_List, v_source_List
90 from (select extractValue(value(e),'/H/V[@N="EntityOccuranceName"]/text()') name,
91 extractValue(value(e),'/H/V[@N="EntityOccuranceCode"]/text()') code,
92 extractValue(value(e),'/H/V[@N="RefEntityOccuranceCode"]/text()') ref,
93 extractValue(value(e),'/H/S/text()') source,
94 to_number(extractValue(value(e),'/H/V[@N="SeqNum"]/text()')) seq_num
95 FROM az_requests d,TABLE(XMLSequence(extract(d.selection_set,'/EXT/H/V[@N="EntityOccuranceCode" and .="'||p_dependant_eo_code||'"]/..'))) e
96 where d.request_id=p_request_id AND d.request_type='T'
97 union all
98 select extractValue(value(e),'/H/V[@N="EntityOccuranceName"]/text()') name,
99 extractValue(value(e),'/H/V[@N="EntityOccuranceCode"]/text()') code,
100 extractValue(value(e),'/H/V[@N="RefEntityOccuranceCode"]/text()') ref,
101 extractValue(value(e),'/H/S/text()') source,
102 to_number(extractValue(value(e),'/H/V[@N="SeqNum"]/text()')) seq_num
103 FROM az_requests d,TABLE(XMLSequence(extract(d.selection_set,'/EXT/H[@A3="Y"]/V[@N="EntityOccuranceName" or @N="EntityOccuranceCode" or @N="RefEntityOccuranceCode"]/..'))) e
104 where d.request_id=p_request_id AND d.request_type='T' ) f
105 start with f.code=p_dependant_eo_code
106 connect by prior f.code=f.ref
107 order siblings by f.seq_num;
108
109 -- propogate the changed attributes to the child VOs (like TLs) for the given source
110 FOR i IN 1 .. v_eo_name_List.COUNT LOOP
111 APPLY_TRANSFORM(p_request_id, P_REQUIRED_API_CODE, P_DEPENDANT_API_CODE, P_REQUIRED_SOURCE, v_source_List(i), v_eo_code_List(i));
112 END LOOP;
113 COMMIT;
114
115 -- For logging
116 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
117 fnd_log.string(fnd_log.level_statement, c_log_head || 'apply_transform_to_tree : ' || to_char(systimestamp), 'apply_transform_to_tree procedure is completed');
118 END IF;
119 -- For logging
120
121 EXCEPTION
122 WHEN application_exception THEN
123 RAISE;
124 WHEN OTHERS THEN
125 raise_error_msg(SQLCODE,
126 SQLERRM,
127 'apply_transform_to_tree',
128 'procedure end');
129 END apply_transform_to_tree;
130
131 PROCEDURE APPLY_TRANSFORM(P_REQUEST_ID IN NUMBER,
132 P_REQUIRED_API_CODE IN VARCHAR2,
133 P_DEPENDANT_API_CODE IN VARCHAR2,
134 P_REQUIRED_SOURCE IN VARCHAR2,
135 P_DEPENDANT_SOURCE IN VARCHAR2,
136 p_dependant_eo_code IN VARCHAR2) IS
137 V_ATTRIBUTES_HASH TYP_ASSOC_ARR;
138 V_EXISTSNODE_STRING VARCHAR2(32767);
139 V_EXISTSNODE_NAMEVAL_STR VARCHAR2(32767);
140 V_CONFLICT_XSL VARCHAR2(32767);
141 V_TRANSFORM_SQL VARCHAR2(32767);
142 V_DEPENDANT_IDS_LIST TYP_NEST_TAB_VARCHAR;
143 V_PARENT_ID_LIST TYP_NEST_TAB_VARCHAR;
144 V_REQ_API_ATTR_NAME_LIST TYP_NEST_TAB_VARCHAR;
145 V_REQ_API_ATTR_NEW_VALUE_LIST TYP_NEST_TAB_VARCHAR;
146 V_REQ_API_ATTR_OLD_VALUE_LIST TYP_NEST_TAB_VARCHAR;
147 V_REQ_API_API_ATTR_NAME VARCHAR2(300);
148 V_DEPENDANT_API_ATTR_NAME VARCHAR2(300);
149 V_REQ_API_ATTR_NEW_VALUE VARCHAR2(300);
150 V_REQ_API_ATTR_OLD_VALUE VARCHAR2(300);
151 V_DEPENDANT_IDS_SQL VARCHAR2(32767);
152 V_DEPENDANT_CHILD_IDS_SQL VARCHAR2(32767);
153 V_DEP_DETAIL_CHILD_IDS_SQL VARCHAR2(32767);
154 V_DEPENDANT_CHILD_IDS_LIST TYP_NEST_TAB_VARCHAR;
155 V_CONFLICT_CHILD_IDS_LIST TYP_NEST_TAB_VARCHAR;
156
157 V_TEMP VARCHAR2(255);
158 V_CHILD_ID_LIST TYP_NEST_TAB_VARCHAR;
159 V_CONFLICT_PARAM3_COUNT NUMBER;
160 V_CHILD_XSL_STRING varchar2(32767);
161 V_TEMP_SQL VARCHAR2(32767);
162 V_CHECK_TRFM_ALL_FLAG NUMBER;
163
164 --Introduced to take care of one-many mappings between mapped attributes
165 V_DEP_API_MAP_ATTR_NAME_LIST TYP_NEST_TAB_VARCHAR;
166 V_REQ_API_MAP_ATTR_NAME_LIST TYP_NEST_TAB_VARCHAR;
167
168 BEGIN
169
170 -- For logging
171 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
172 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'In APPLY_TRANSFORM procedure');
173 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'P_REQUEST_ID ---> '||P_REQUEST_ID);
174 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'P_REQUIRED_API_CODE ---> '||P_REQUIRED_API_CODE);
175 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'P_DEPENDANT_API_CODE ---> '||P_DEPENDANT_API_CODE);
176 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'P_REQUIRED_SOURCE ---> '||P_REQUIRED_SOURCE);
177 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'p_dependant_eo_code ---> '||p_dependant_eo_code);
178 END IF;
179 -- For logging
180
181
182
183 V_ATTRIBUTES_HASH := GET_MAPPED_ATTRIBUTES(P_DEPENDANT_API_CODE,
184 P_REQUIRED_API_CODE);
185 --Introduced to take care of one-many mappings between mapped attributes
186 SELECT REQUIRED_API_ATTRIBUTE, DEPENDANT_API_ATTRIBUTE BULK COLLECT
187 INTO V_REQ_API_MAP_ATTR_NAME_LIST, V_DEP_API_MAP_ATTR_NAME_LIST
188 FROM AZ_API_DEPENDENCY_ATTRIBUTES
189 WHERE REQUIRED_API_CODE = P_REQUIRED_API_CODE
190 AND DEPENDANT_API_CODE = P_DEPENDANT_API_CODE;
191
192 --Introduced to take care of one-many mappings between mapped attributes
193 IF V_ATTRIBUTES_HASH.COUNT <> 0 THEN
194 SELECT ID BULK COLLECT
195 INTO V_PARENT_ID_LIST
196 FROM AZ_DIFF_RESULTS
197 WHERE REQUEST_ID = P_REQUEST_ID
198 AND SOURCE = P_REQUIRED_SOURCE
199 AND IS_TRANSFORMED = 'Y'
200 AND PARENT_ID =1; -- Newly added to ensure all top level VO's Childs are transformed
201
202
203 SELECT count (distinct (EXTRACTVALUE(VALUE(E), '/V/B/text()')))
204 into V_CHECK_TRFM_ALL_FLAG
205 FROM AZ_DIFF_RESULTS D,
206 TABLE(XMLSEQUENCE(EXTRACT(D.ATTR_DIFF, '/H/V'))) E
207 WHERE D.REQUEST_ID = P_REQUEST_ID
208 AND D.SOURCE = P_REQUIRED_SOURCE
209 AND D.IS_TRANSFORMED = 'Y'
210 AND D.TYPE <> -1
211 AND existsNode(VALUE(E),'/V[@A2="Y"]') = 1;
212
213 -- For logging
214 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
215 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_CHECK_TRFM_ALL_FLAG --> ' || V_CHECK_TRFM_ALL_FLAG);
216 END IF;
217 -- For logging
218
219 ---
220 IF V_CHECK_TRFM_ALL_FLAG >1
221 THEN
222 SELECT ID BULK COLLECT
223 INTO V_PARENT_ID_LIST
224 FROM AZ_DIFF_RESULTS
225 WHERE REQUEST_ID = P_REQUEST_ID
226 AND SOURCE = P_REQUIRED_SOURCE
227 AND IS_TRANSFORMED = 'Y'
228 AND PARENT_ID =1 ; -- Newly added to ensure all top level VO's Childs are transformed
229 ELSE
230
231 --Smart optimization -- need not iterate all the parents in case of a transform all case
232 SELECT ID BULK COLLECT
233 INTO V_PARENT_ID_LIST
234 FROM AZ_DIFF_RESULTS
235 WHERE REQUEST_ID = P_REQUEST_ID
236 AND SOURCE = P_REQUIRED_SOURCE
237 AND IS_TRANSFORMED = 'Y'
238 AND PARENT_ID =1 and rownum < 2;
239 END IF;
240
241
242 FOR I IN 1 .. V_PARENT_ID_LIST.COUNT LOOP
243 SELECT EXTRACTVALUE(VALUE(E), '/V/@N'),
244 EXTRACTVALUE(VALUE(E), '/V/B/text()'),
245 EXTRACTVALUE(VALUE(E), '/V/A/text()') BULK COLLECT
246 INTO V_REQ_API_ATTR_NAME_LIST,
247 V_REQ_API_ATTR_NEW_VALUE_LIST,
251 WHERE D.REQUEST_ID = P_REQUEST_ID
248 V_REQ_API_ATTR_OLD_VALUE_LIST
249 FROM AZ_DIFF_RESULTS D,
250 TABLE(XMLSEQUENCE(EXTRACT(D.ATTR_DIFF, '/H/V'))) E
252 AND D.SOURCE = P_REQUIRED_SOURCE
253 AND D.IS_TRANSFORMED = 'Y'
254 AND D.ID = V_PARENT_ID_LIST(I)
255 AND D.TYPE <> -1
256 AND existsNode(VALUE(E),'/V[@A2="Y"]') = 1;
257
258
259 V_CHILD_XSL_STRING := '';
260 V_EXISTSNODE_STRING := '(';
261 V_EXISTSNODE_NAMEVAL_STR := ' AND (';
262
263 --lmathur adding the XSL to update T=1 for attributes which are marked as 'conflicting' for user to edit
264 V_CONFLICT_XSL := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
265 <xsl:template match="H">
266 <H>
267 <xsl:for-each select="V">
268 <V>
269 <xsl:copy-of select="@*[not(name()= ''''T'''' or name()=''''A1'''')]"/>
270 <xsl:choose>';
271 -- LMATHUR - added the copy-of for optimizing the stylesheet, need not copy all the attributes
272
273 --- Change to remove the excess table xmlsequence
274 V_DEPENDANT_IDS_SQL := 'SELECT q.id FROM AZ_DIFF_RESULTS q ';
275 V_DEPENDANT_IDS_SQL := V_DEPENDANT_IDS_SQL ||
276 ' where q.request_id = ' || P_REQUEST_ID ||
277 ' AND ';
278 V_DEPENDANT_IDS_SQL := V_DEPENDANT_IDS_SQL || 'q.source = ''' ||
279 P_DEPENDANT_SOURCE || ''' AND ';
280
281 V_DEPENDANT_CHILD_IDS_SQL := V_DEPENDANT_IDS_SQL || ' ';
282
283 -- For logging
284 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
285 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_REQ_API_ATTR_NAME_LIST Count --> ' || V_REQ_API_ATTR_NAME_LIST.COUNT);
286 END IF;
287 -- For logging
288
289 FOR J IN 1 .. V_REQ_API_ATTR_NAME_LIST.COUNT LOOP
290
291 BEGIN
292 -- For logging
293 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
294 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'For attribute name --> ' || V_REQ_API_ATTR_NAME_LIST(J));
295 END IF;
296 -- For logging
297
298 V_REQ_API_API_ATTR_NAME := V_REQ_API_ATTR_NAME_LIST(J);
299 V_REQ_API_ATTR_NEW_VALUE := V_REQ_API_ATTR_NEW_VALUE_LIST(J);
300 V_REQ_API_ATTR_OLD_VALUE := V_REQ_API_ATTR_OLD_VALUE_LIST(J);
301
302 V_DEPENDANT_API_ATTR_NAME := V_ATTRIBUTES_HASH(V_REQ_API_API_ATTR_NAME);
303
304 V_DEPENDANT_IDS_SQL := V_DEPENDANT_IDS_SQL||' ( ';
305
306 -- For logging
307 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
308 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_REQ_API_MAP_ATTR_NAME_LIST Count --> ' || V_REQ_API_MAP_ATTR_NAME_LIST.COUNT);
309 END IF;
310 -- For logging
311
312 --Introduced to take care of one-many mappings between mapped attributes
313 FOR K IN 1 .. V_REQ_API_MAP_ATTR_NAME_LIST.COUNT LOOP
314 BEGIN
315
316 IF V_REQ_API_MAP_ATTR_NAME_LIST(K) = V_REQ_API_API_ATTR_NAME
317
318 THEN
319 V_DEPENDANT_API_ATTR_NAME := V_DEP_API_MAP_ATTR_NAME_LIST(K);
320 V_DEPENDANT_IDS_SQL := V_DEPENDANT_IDS_SQL ||
321 ' existsnode(q.attr_diff, ''/H/V[@N="' ||
322 V_DEPENDANT_API_ATTR_NAME ||
323 '"]/A[.="' ||
324 V_REQ_API_ATTR_OLD_VALUE ||
325 '"]/text()'')=1 OR ';
326
327 V_CHILD_XSL_STRING := V_CHILD_XSL_STRING ||
328 ' <xsl:when test="@N=''''' ||
329 V_DEPENDANT_API_ATTR_NAME ||
330 ''''' and ./B=''''' ||V_REQ_API_ATTR_OLD_VALUE||'''''">';
331 V_CHILD_XSL_STRING := V_CHILD_XSL_STRING ||'<xsl:attribute name="A2">Y</xsl:attribute><xsl:copy-of select ="A"/> <xsl:element name="B">'||
332 V_REQ_API_ATTR_NEW_VALUE ||
333 '</xsl:element></xsl:when> ';
334
338 '"]/B[.="' ||
335 V_EXISTSNODE_STRING := V_EXISTSNODE_STRING||
336 ' existsnode(q.attr_diff, ''/H/V[@N!="' ||
337 V_DEPENDANT_API_ATTR_NAME ||
339 V_REQ_API_ATTR_OLD_VALUE ||
340 '"]'')=1 OR ';
341
342 V_EXISTSNODE_NAMEVAL_STR := V_EXISTSNODE_NAMEVAL_STR||
343 ' existsnode(e.attr_diff, ''/H/V[@N="' ||
344 V_DEPENDANT_API_ATTR_NAME ||
345 '"]/B[.="' ||
346 V_REQ_API_ATTR_OLD_VALUE ||
347 '"]'')=1 OR ';
348
349
350 --construct the conflict XSL here
351 V_CONFLICT_XSL := V_CONFLICT_XSL||
352 ' <xsl:when test="@N!='''''||V_DEPENDANT_API_ATTR_NAME||''''' and ./B/text()='''''||V_REQ_API_ATTR_OLD_VALUE||''''' "> '
353 ||' <xsl:attribute name="T">1</xsl:attribute>'
354 ||' <xsl:attribute name="A1">Y</xsl:attribute>'
355 ||'</xsl:when>';
356 END IF;
357 END;
358 END LOOP;
359 V_DEPENDANT_IDS_SQL := V_DEPENDANT_IDS_SQL||' (1=0) ) AND ';
360
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363
364 IF V_REQ_API_ATTR_NEW_VALUE <> V_REQ_API_ATTR_OLD_VALUE
365 THEN
366 V_EXISTSNODE_STRING := V_EXISTSNODE_STRING||
367 ' existsnode(q.attr_diff, ''/H/V/B[.="' ||
368 V_REQ_API_ATTR_OLD_VALUE ||
369 '"]'')=1 OR ';
370
371 V_EXISTSNODE_NAMEVAL_STR := V_EXISTSNODE_NAMEVAL_STR||
372 ' existsnode(e.attr_diff, ''/H/V[@N="' ||
373 V_REQ_API_API_ATTR_NAME ||
374 '"]/B[.="' ||
375 V_REQ_API_ATTR_OLD_VALUE ||
376 '"]'')=1 OR ';
377 ---LMATHUR -- need not mark the attributes as Conflicting and transformable unless the value is different
378 -- putting it outside the IF was resulting in excess attributes being marked as Conflicting and transformable
379 V_CONFLICT_XSL := V_CONFLICT_XSL||
380 ' <xsl:when test="./B/text()='''''||V_REQ_API_ATTR_OLD_VALUE||''''' ">
381 <xsl:attribute name="T">1</xsl:attribute>
382 <xsl:attribute name="A1">Y</xsl:attribute>
383 </xsl:when>';
384
385 V_CHILD_XSL_STRING := V_CHILD_XSL_STRING ||
386 ' <xsl:when test="@N=''''' ||
387 V_REQ_API_API_ATTR_NAME ||
388 '''''and ./B=''''' ||V_REQ_API_ATTR_OLD_VALUE||'''''">';
389 V_CHILD_XSL_STRING := V_CHILD_XSL_STRING ||'<xsl:attribute name="A2">Y</xsl:attribute><xsl:copy-of select ="A"/> <xsl:element name="B">'||
390 V_REQ_API_ATTR_NEW_VALUE ||
391 '</xsl:element></xsl:when> ';
392
393 END IF;
394
395 END;
396 END LOOP;
397
398
399
400 IF V_REQ_API_ATTR_NAME_LIST.COUNT=0
401 THEN
402 V_EXISTSNODE_STRING := '';
403
404 -- New changes for the single attribute transformation
405 V_CONFLICT_XSL := '';
406
407 ELSE
408 V_EXISTSNODE_STRING := V_EXISTSNODE_STRING|| ' 1<>1 ) AND ';
409
410
411 V_CONFLICT_XSL := V_CONFLICT_XSL||
412 ' <xsl:otherwise>
413 <xsl:attribute name="T"><xsl:value-of select="@T"/></xsl:attribute>
414 </xsl:otherwise>
415 </xsl:choose>
416 <xsl:copy-of select="A"/>
417 <xsl:copy-of select="B"/>
418 </V>
419 </xsl:for-each>
420 </H>
421 </xsl:template>
422 </xsl:stylesheet>';
423
424 END IF;
425
426 --V_DEPENDANT_IDS_SQL := V_DEPENDANT_IDS_SQL||' 1=1 ';
427 V_DEPENDANT_IDS_SQL := V_DEPENDANT_IDS_SQL||' 1=1 AND PARENT_ID=1 ';
428
429 -- For logging
430 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
431 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_DEPENDANT_IDS_SQL --> ' || V_DEPENDANT_IDS_SQL);
432 END IF;
433 -- For logging
434
435 EXECUTE IMMEDIATE V_DEPENDANT_IDS_SQL BULK COLLECT
436 INTO V_DEPENDANT_IDS_LIST;
437
438 -- For logging
439 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
440 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_DEPENDANT_IDS_LIST Count --> ' || V_DEPENDANT_IDS_LIST.COUNT);
441 END IF;
442 -- For logging
443
447 -- For logging
444 V_EXISTSNODE_NAMEVAL_STR := V_EXISTSNODE_NAMEVAL_STR|| ' 1<>1 )';
445
446 FOR K IN 1 .. V_DEPENDANT_IDS_LIST.COUNT LOOP
448 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
449 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'For V_DEPENDANT_IDS_LIST(K)--> ' || V_DEPENDANT_IDS_LIST(K));
450 END IF;
451 -- For logging
452 V_TRANSFORM_SQL := GET_TRANSFORM_SQL(P_REQUEST_ID,
453 P_DEPENDANT_SOURCE,
454 V_CHILD_XSL_STRING,
455 V_DEPENDANT_IDS_LIST(K),'Y');
456
457
458
459 V_TRANSFORM_SQL := V_TRANSFORM_SQL||V_EXISTSNODE_NAMEVAL_STR;
460
461 -- For logging
462 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
463 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_TRANSFORM_SQL --> ' || V_TRANSFORM_SQL);
464 END IF;
465 -- For logging
466
467 EXECUTE IMMEDIATE V_TRANSFORM_SQL;
468
469 v_dml_count := v_dml_count + 1;
470
471
472 V_DEP_DETAIL_CHILD_IDS_SQL := ' q.parent_id in' ;
473 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' (' ;
474 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' select id from' ;
475 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' (';
476 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' select id, parent_id from ';
477 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' (';
478 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' SELECT d.id id,';
479 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' d.parent_id parent_id';
480 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' FROM az_diff_results d ';
481 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' WHERE d.request_id = ' || p_request_id ;
482 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' AND d.source = '''|| P_DEPENDANT_SOURCE || '''';
483 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' ) f START WITH f.id = ' || V_DEPENDANT_IDS_LIST(K) || ' CONNECT BY PRIOR f.id = f.parent_id';
484 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' )';
485 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' )';
486
487
488 V_DEP_DETAIL_CHILD_IDS_SQL := V_DEPENDANT_CHILD_IDS_SQL || V_DEP_DETAIL_CHILD_IDS_SQL;
489
490 -- For logging
491 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
492 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_DEP_DETAIL_CHILD_IDS_SQL --> ' || V_DEP_DETAIL_CHILD_IDS_SQL);
493 END IF;
494 -- For logging
495
496 EXECUTE IMMEDIATE V_DEP_DETAIL_CHILD_IDS_SQL BULK COLLECT
497 INTO V_DEPENDANT_CHILD_IDS_LIST;
498 -- For logging
499 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
500 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_DEPENDANT_CHILD_IDS_LIST Count --> ' || V_DEPENDANT_CHILD_IDS_LIST.COUNT);
501 END IF;
502 -- For logging
503 FOR Z IN 1 .. V_DEPENDANT_CHILD_IDS_LIST.COUNT LOOP
504 -- For logging
505 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
506 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'For V_DEPENDANT_CHILD_IDS_LIST(Z)--> ' || V_DEPENDANT_CHILD_IDS_LIST(Z));
507 END IF;
508 -- For logging
509 V_TRANSFORM_SQL := GET_TRANSFORM_SQL(P_REQUEST_ID,
510 P_DEPENDANT_SOURCE,
511 V_CHILD_XSL_STRING,
512 V_DEPENDANT_CHILD_IDS_LIST(Z),'N');
513 -- For logging
514 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
515 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'V_TRANSFORM_SQL --> ' || V_TRANSFORM_SQL);
516 END IF;
517 -- For logging
518
519 EXECUTE IMMEDIATE V_TRANSFORM_SQL;
520 END LOOP;
521
522 IF length(V_CONFLICT_XSL)>0
523 THEN
524 V_TEMP_SQL := 'UPDATE az_diff_results q SET param3 = ''Y'', q.attr_diff = q.attr_diff.transform(xmltype('''||V_CONFLICT_XSL||''')).createSchemaBasedXml(''' ||
525 DIFF_SCHEMA_URL || ''') WHERE '|| ' q.id = ' || V_DEPENDANT_IDS_LIST(K)
526 ||' AND q.request_id = '||P_REQUEST_ID||
527 ' AND ' || V_EXISTSNODE_STRING || ' q.source = '''||p_dependant_source||'''';
528
529 -- For logging
530 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
531 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'For V_DEPENDANT_IDS_LIST(K) --> ' || V_DEPENDANT_IDS_LIST(K)||'V_TEMP_SQL --> ' || V_TEMP_SQL);
532 END IF;
533 -- For logging
534
535
536 EXECUTE IMMEDIATE V_TEMP_SQL;
540 FROM az_diff_results d
537
538 select id BULK collect into V_CONFLICT_CHILD_IDS_LIST from (select id,parent_id from (SELECT d.id id,
539 d.parent_id parent_id
541 WHERE d.request_id = P_REQUEST_ID
542 AND d.source = p_dependant_source
543 ) f START WITH f.id = V_DEPENDANT_IDS_LIST(K) CONNECT BY PRIOR f.id = f.parent_id) ;
544 -- For logging
545 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
546 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'For V_DEPENDANT_IDS_LIST(K) --> ' || V_DEPENDANT_IDS_LIST(K)||'V_CONFLICT_CHILD_IDS_LIST.COUNT --> ' || V_CONFLICT_CHILD_IDS_LIST.COUNT);
547 END IF;
548 -- For logging
549
550 FOR Y IN 1 .. V_CONFLICT_CHILD_IDS_LIST.COUNT LOOP
551 V_TEMP_SQL :='UPDATE az_diff_results q SET param3 = ''Y'', q.attr_diff = q.attr_diff.transform(xmltype('''||V_CONFLICT_XSL||''')).createSchemaBasedXml(''' ||
552 DIFF_SCHEMA_URL || ''') WHERE '|| ' q.id = ' || V_CONFLICT_CHILD_IDS_LIST(Y)
553 ||' AND q.request_id = '||P_REQUEST_ID||
554 ' AND '|| V_EXISTSNODE_STRING || ' q.source = '''||p_dependant_source||'''';
555
556 -- For logging
557 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
558 fnd_log.string(fnd_log.level_statement, c_log_head || 'APPLY_TRANSFORM : ', 'For V_CONFLICT_CHILD_IDS_LIST(Y) --> ' || V_CONFLICT_CHILD_IDS_LIST(Y)||' V_TEMP_SQL --> ' || V_TEMP_SQL);
559 END IF;
560 -- For logging
561
562
563 EXECUTE IMMEDIATE V_TEMP_SQL;
564 END LOOP;
565
566 END IF; -- no need to do this if V_CONFLICT_XSL is zero length
567
568
569 IF MOD(v_dml_count,
570 commit_batch_size) = 0 THEN
571 COMMIT;
572 END IF;
573 END LOOP;
574
575 -- select id bulk collect into v_child_id_list
576 -- from az_diff_results where request_id=p_request_id and source=P_DEPENDANT_SOURCE
577 -- and param2 ='Y';
578 -- -- Now we need to update the master is_transformed flag for the changed child records
579 -- update_master_flag(p_request_id,P_DEPENDANT_SOURCE,'IS_TRANSFORMED',v_child_id_list,'N');
580 -- --update the param2 for the entire tree to denote the transformed records
581 -- update_master_flag(p_request_id,P_DEPENDANT_SOURCE,'PARAM2',v_child_id_list,'Y');
582 END LOOP;
583
584 IF V_PARENT_ID_LIST.COUNT >0
585 then
586 UPDATE_REGEN_REQD(P_REQUEST_ID, P_DEPENDANT_eo_code, p_dependant_source);
587 END IF;
588 IF length(V_EXISTSNODE_STRING) > 0
589 THEN
590 -- LMATHUR -> update T=1 for all the attributes which can be marked as conflicting so that they can be edited from the UI
591
592 --Redundancy removal
593 -- LMATHUR -> Now Update the AZ_REQUESTS selection_set XML to ensure that the conflicted attributes are open for editing
594 -- select count(1) into V_CONFLICT_PARAM3_COUNT from az_diff_results d where d.param3 = 'Y' and
595 -- d.request_id = P_REQUEST_ID
596 -- and d.source=p_dependant_source;
597 --
598 -- IF V_CONFLICT_PARAM3_COUNT > 0
599 -- THEN
600 -- --LMATHUR - To show conflict icon in the VIEW mode, we need an indicator for conflicts- A5=C indicate conflicts
601 -- EXECUTE IMMEDIATE 'UPDATE az_requests d
602 -- SET d.selection_set = updatexml(d.selection_set, ''/EXT/H[S="'||p_dependant_source||'"]/@A5'',''C'')
603 -- WHERE d.request_id = '||P_REQUEST_ID||
604 -- ' AND d.request_type = ''T''';
605 --
606 -- END IF;
607
608 select id bulk collect into v_child_id_list
609 from az_diff_results where request_id=p_request_id and source=P_DEPENDANT_SOURCE
610 and param3 ='Y';
611
612 -- LMATHUR -Now we need to update the entire tree's param3 flag which are having conflicts in childs
613 update_master_flag(p_request_id,P_DEPENDANT_SOURCE,'PARAM3',v_child_id_list,'Y');
614
615 --LMATHUR and HBODA -- Changes to update the Conflict status in the selection Set XML so as to be used in UI
616 update_conflict_status(p_request_id,P_DEPENDANT_SOURCE);
617
618 COMMIT;
619 END IF;
620
621
622 END IF; -- IF V_ATTRIBUTES_HASH.COUNT <> 0 closes
623
624 EXCEPTION
625 WHEN APPLICATION_EXCEPTION THEN
626 RAISE;
627 WHEN OTHERS THEN
628 RAISE_ERROR_MSG(SQLCODE, SQLERRM, 'APPLY_TRANSFORM', 'procedure end');
629 END APPLY_TRANSFORM;
630
631 PROCEDURE UPDATE_REGEN_REQD(P_REQUEST_ID IN NUMBER,
632 P_DEPENDANT_eo_code IN VARCHAR2,
633 p_dependant_source IN VARCHAR2) IS
634 V_AUTO_SELECTED VARCHAR2(1);
635 V_EO_CODE VARCHAR2(4000);
636 V_REF_EO_CODE VARCHAR2(4000);
637 v_count NUMBER;
638 BEGIN
639
640 SELECT EXTRACTVALUE(VALUE(E), '/H/@A2')
641 INTO V_AUTO_SELECTED
642 FROM AZ_REQUESTS D,
643 TABLE(XMLSEQUENCE(EXTRACT(D.SELECTION_SET,
644 '/EXT/H/V[@N="EntityOccuranceCode" and .="' || P_DEPENDANT_eo_code || '"]/..'))) E
645 WHERE D.REQUEST_ID = P_REQUEST_ID
646 AND D.REQUEST_TYPE = 'T';
647
648
649 SELECT COUNT(*)
650 INTO v_count
651 FROM az_diff_results
652 WHERE REQUEST_ID = P_REQUEST_ID
656 IF v_count>0 THEN
653 AND SOURCE = p_dependant_source
654 AND is_transformed='Y';
655
657
658 UPDATE AZ_REQUESTS D
659 SET D.SELECTION_SET = UPDATEXML(D.SELECTION_SET,
660 '/EXT/H[S="'||P_DEPENDANT_SOURCE||'"]/T/text()',v_count)
661 WHERE D.REQUEST_ID = P_REQUEST_ID
662 AND d.REQUEST_TYPE = 'T';
663 END IF;
664
665
666 IF V_AUTO_SELECTED = 'Y' AND v_count>0 THEN
667
668
669 UPDATE AZ_REQUESTS D
670 SET D.SELECTION_SET = UPDATEXML(D.SELECTION_SET,
671 '/EXT/H/V[@N="EntityOccuranceCode" and .="' || P_DEPENDANT_eo_code || '"]/../@A4', 'Y')
672 WHERE D.REQUEST_ID = P_REQUEST_ID
673 AND D.REQUEST_TYPE = 'T';
674
675 ELSIF V_AUTO_SELECTED <> 'Y' AND v_count>0 THEN
676 SELECT EXTRACTVALUE(VALUE(E), '/H/V[@N="EntityOccuranceCode"]/text()'),
677 EXTRACTVALUE(VALUE(E), '/H/V[@N="RefEntityOccuranceCode"]/text()')
678 INTO V_EO_CODE, V_REF_EO_CODE
679 FROM AZ_REQUESTS D,
680 TABLE(XMLSEQUENCE(EXTRACT(D.SELECTION_SET,
681 '/EXT/H/V[@N="EntityOccuranceCode" and .="' || P_DEPENDANT_eo_code || '"]/..'))) E
682 WHERE D.REQUEST_ID = P_REQUEST_ID
683 AND D.REQUEST_TYPE = 'T';
684
685 LOOP
686 BEGIN
687 SELECT EXTRACTVALUE(VALUE(E),
688 '/H/V[@N="EntityOccuranceCode"]/text()'),
689 EXTRACTVALUE(VALUE(E),
690 '/H/V[@N="RefEntityOccuranceCode"]/text()'),
691 EXTRACTVALUE(VALUE(E), '/H/@A2')
692 INTO V_EO_CODE, V_REF_EO_CODE, V_AUTO_SELECTED
693 FROM AZ_REQUESTS D,
694 TABLE(XMLSEQUENCE(EXTRACT(D.SELECTION_SET,
695 '/EXT/H/V[@N="EntityOccuranceCode" and .="' ||
696 V_REF_EO_CODE || '"]/..'))) E
697 WHERE D.REQUEST_ID = P_REQUEST_ID
698 AND D.REQUEST_TYPE = 'T';
699
700
701 IF V_AUTO_SELECTED = 'Y' THEN
702
703
704 UPDATE AZ_REQUESTS D
705 SET D.SELECTION_SET = UPDATEXML(D.SELECTION_SET,
706 '/EXT/H/V[@N="EntityOccuranceCode" and .="' ||
707 V_EO_CODE || '"]/../@A4',
708 'Y')
709 WHERE D.REQUEST_ID = P_REQUEST_ID
710 AND D.REQUEST_TYPE = 'T';
711 EXIT;
712 END IF;
713 EXCEPTION
714 WHEN NO_DATA_FOUND THEN
715 EXIT;
716 END;
717 END LOOP;
718 END IF;
719 EXCEPTION
720 WHEN APPLICATION_EXCEPTION THEN
721 RAISE;
722 WHEN OTHERS THEN
723 RAISE_ERROR_MSG(SQLCODE, SQLERRM, 'UPDATE_REGEN_REQD', 'procedure end');
724 END UPDATE_REGEN_REQD;
725
726 FUNCTION GET_MAPPED_ATTRIBUTES(P_DEPENDANT_API_CODE IN VARCHAR2,
727 P_REQUIRED_API_CODE IN VARCHAR2)
728 RETURN TYP_ASSOC_ARR IS
729 V_ATTRIBUTES_HASH TYP_ASSOC_ARR;
730 V_REQ_API_ATTR_LIST TYP_NEST_TAB_VARCHAR;
731 V_DEP_API_ATTR_LIST TYP_NEST_TAB_VARCHAR;
732 BEGIN
733
734 SELECT REQUIRED_API_ATTRIBUTE, DEPENDANT_API_ATTRIBUTE BULK COLLECT
735 INTO V_REQ_API_ATTR_LIST, V_DEP_API_ATTR_LIST
736 FROM AZ_API_DEPENDENCY_ATTRIBUTES
737 WHERE REQUIRED_API_CODE = P_REQUIRED_API_CODE
738 AND DEPENDANT_API_CODE = P_DEPENDANT_API_CODE;
739
740 FOR I IN 1 .. V_REQ_API_ATTR_LIST.COUNT LOOP
741 V_ATTRIBUTES_HASH(V_REQ_API_ATTR_LIST(I)) := V_DEP_API_ATTR_LIST(I);
742 END LOOP;
743
744
745
746 RETURN V_ATTRIBUTES_HASH;
747 EXCEPTION
748 WHEN APPLICATION_EXCEPTION THEN
749 RAISE;
750 WHEN OTHERS THEN
751 RAISE_ERROR_MSG(SQLCODE,
752 SQLERRM,
753 'GET_MAPPED_ATTRIBUTES',
754 'procedure end');
755 END;
756
757
758
759 PROCEDURE transform_all(p_job_name IN VARCHAR2,
760 p_request_id IN NUMBER,
761 p_user_id IN NUMBER,
762 p_source IN VARCHAR2,
763 p_is_cascade IN VARCHAR2,
764 p_diff_schema_url IN VARCHAR2) IS
765
766 v_eo_code_list typ_nest_tab_varchar;
767
768 v_ref_eo_code_list typ_nest_tab_varchar;
769
770 v_source_list typ_nest_tab_varchar;
771
772 v_attribute_name_list typ_nest_tab_varchar;
773
774 v_child_attribute_name_list typ_nest_tab_varchar := typ_nest_tab_varchar();
775
776 -- v_child_attribute_value_list typ_nest_tab_varchar := typ_nest_tab_varchar();
777
778 v_attribute_value_list typ_nest_tab_varchar;
779
780 v_entity_code_list typ_nest_tab_varchar;
781 v_split_flag_list typ_nest_tab_varchar;
782 v_master_ids_trans_list typ_nest_tab_varchar;
783
784 v_transform_all_sql VARCHAR2(32767);
785
786 v_parent_api_code VARCHAR2(255);
787
788 v_current_api_code VARCHAR2(255);
789
790 v_mapped_attributes_map typ_assoc_arr;
791
792 v_child_attribute_name VARCHAR2(255);
793
794 v_child_count NUMBER;
795
796 --lmathur added
800 V_ATTR_NAME VARCHAR2(255);
797 V_EXISTSNODE_STRING VARCHAR2(32767);
798 V_UPDATE_XSL CLOB := '';
799 V_CHILD_ID_LIST TYP_NEST_TAB_VARCHAR;
801
802
803 --Introduced to take care of one-many mappings between mapped attributes
804 V_DEP_API_MAP_ATTR_NAME_LIST TYP_NEST_TAB_VARCHAR;
805 V_REQ_API_MAP_ATTR_NAME_LIST TYP_NEST_TAB_VARCHAR;
806
807
808 BEGIN
809
810 -- For logging
811 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
812 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'In transform_all procedure ...');
813 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'p_job_name ---> '||p_job_name);
814 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'p_user_id ---> '||p_user_id);
815 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'p_source ---> '||p_source);
816 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'p_is_cascade ---> '||p_is_cascade);
817 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'p_diff_schema_url ---> '||p_diff_schema_url);
818 END IF;
819 -- For logging
820
821 diff_schema_url := p_diff_schema_url;
822 commit_batch_size := fnd_profile.VALUE('AZ_COMMIT_ROWCOUNT');
823 v_dml_count := 0;
824
825
826 SELECT entity_occurance_code,
827 ref_entity_occurance_code,
828 SOURCE,
829 entity_code,
830 split_flag BULK COLLECT
831 INTO v_eo_code_list,
832 v_ref_eo_code_list,
833 v_source_list,
834 v_entity_code_list,
835 v_split_flag_list
836 FROM ( SELECT extractvalue(VALUE(e),
837 '/H/V[@N="EntityOccuranceName"]/text()'),
838 extractvalue(VALUE(e),
839 '/H/V[@N="EntityOccuranceCode"]/text()') entity_occurance_code,
840 extractvalue(VALUE(e),
841 '/H/V[@N="RefEntityOccuranceCode"]/text()') ref_entity_occurance_code,
842 extractvalue(VALUE(e),
843 '/H/V[@N="EntityCode"]/text()') entity_code,
844 extractvalue(VALUE(e),'/H/S/text()') SOURCE,
845 nvl(extractvalue(VALUE(e),'/H/@A3'),'N') split_flag
846
847 FROM az_requests d,
848 TABLE(xmlsequence(extract(d.selection_set,
849 '/EXT/H'))) e
850 WHERE existsnode(VALUE(e),
851 '/H/V[@N="EntityOccuranceName" or @N="EntityOccuranceCode" or @N="RefEntityOccuranceCode"]') = 1
852 AND
853 existsnode(VALUE(e),
854 '/H[@A2="Y" or @A3="Y" or @A1="Y"]') = 1
855 AND d.job_name = p_job_name
856 AND d.request_type = 'T'
857 AND d.user_id = p_user_id
858 )
859 START WITH SOURCE = p_source
860 CONNECT BY PRIOR entity_occurance_code = ref_entity_occurance_code;
861
862
863 SELECT extractvalue(VALUE(e),
864 '/V/@N'),
865 extract(VALUE(e),
866 '/V/text()').getstringval() BULK COLLECT
867 INTO v_attribute_name_list, v_attribute_value_list
868 FROM az_requests d,
869 TABLE(xmlsequence(extract(d.selection_set,
870 '/EXT/H/S[.="' || p_source ||
871 '"]/../V[@T="1"]'))) e
872 WHERE job_name = p_job_name
873 AND user_id = p_user_id
874 AND request_type = 'T';
875
876 -- For logging
877 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
878 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'v_eo_code_list.COUNT --> '||v_eo_code_list.COUNT);
879 END IF;
880 -- For logging
881
882 FOR i IN 1 .. v_eo_code_list.COUNT
883 LOOP
884 IF p_source = v_source_list(i) THEN
885 --This is the root source
886 V_UPDATE_XSL := '';
887 V_EXISTSNODE_STRING := '';
888
889 FOR j IN 1 .. v_attribute_name_list.COUNT
890 LOOP
891 IF LENGTH(v_attribute_value_list(j))>0
892 THEN
893 IF LENGTH(V_EXISTSNODE_STRING)>0
894 THEN
895 V_EXISTSNODE_STRING := V_EXISTSNODE_STRING|| ' OR ';
896 END IF;
897
898 V_UPDATE_XSL := V_UPDATE_XSL ||'<xsl:when test="@N='''||v_attribute_name_list(j)||'''"><xsl:attribute name = "A2">Y</xsl:attribute>
899 <xsl:copy-of select = "A"/>
900 <xsl:element name = "B">'
901 ||v_attribute_value_list(j)||'</xsl:element></xsl:when>';
902 V_EXISTSNODE_STRING := V_EXISTSNODE_STRING||'existsnode(e.attr_diff,''/H/V[@N="'||v_attribute_name_list(j)||'"] '')=1 ';
903
904 END IF;
905 END LOOP;
906
907 IF LENGTH(V_UPDATE_XSL)>0
908 THEN
909 --LMATHUR -- added to prevent the varchar overflow for Xmltype
910 V_UPDATE_XSL := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
911 <xsl:template match="H">
912 <xsl:copy>
913 <xsl:copy-of select="@*"/>
914 <xsl:apply-templates/>
915 </xsl:copy>
919 <xsl:copy-of select="@*[not(name()=''A2'')]"/>
916 </xsl:template>
917 <xsl:template match="V">
918 <xsl:copy>
920 <xsl:choose>'||V_UPDATE_XSL;
921
922 V_UPDATE_XSL := V_UPDATE_XSL ||'<xsl:otherwise>
923 <xsl:copy-of select="A"/><xsl:copy-of select="B"/>
924 </xsl:otherwise>
925 </xsl:choose>
926 </xsl:copy>
927 </xsl:template>
928 </xsl:stylesheet>';
929 v_transform_all_sql := get_transform_all_sql(
930 V_EXISTSNODE_STRING,
931 p_request_id,
932 v_source_list(i));
933
934 -- For logging
935 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
936 fnd_log.string(fnd_log.level_statement, c_log_head || 'transform_all : ', 'For --> '||v_source_list(i)||' v_transform_all_sql ---> '||v_transform_all_sql );
937 END IF;
938 -- For logging
939
940 EXECUTE IMMEDIATE v_transform_all_sql using V_UPDATE_XSL;
941
942 -- LMATHUR - now for the given source, update the IS_TRANSFORMED for the master records
943 select d.id bulk collect into V_CHILD_ID_LIST
944 from az_diff_results d where d.request_id=p_request_id and d.source=p_source
945 and d.param2 = 'Y';
946 update_master_flag(p_request_id,v_source_list(i),'IS_TRANSFORMED',v_child_id_list,'N');
947 END IF;
948 V_UPDATE_XSL :='';
949 V_EXISTSNODE_STRING := '';
950 --Now we need to mark conflicts and cascade values to the child VOs/TLs for the root source
951 --mugsrin to transform all other matching attributes other than mapped attributes.
952 SELECT ID bulk collect into v_master_ids_trans_list FROM AZ_DIFF_RESULTS
953 WHERE REQUEST_ID = p_request_id
954 AND SOURCE = p_source AND parent_id = 1;
955
956 FOR i IN 1 .. v_master_ids_trans_list.COUNT
957 LOOP
958
959 TRANSFORM_ALL_ATTR_SOURCE(p_request_id, p_source, v_master_ids_trans_list(i), p_diff_schema_url);
960
961 END LOOP;
962 ELSE
963 SELECT f.api_code
964 INTO v_parent_api_code
965 FROM az_requests d,
966 TABLE(xmlsequence(extract(d.selection_set,
967 '/EXT/H'))) e,
968 az_structure_apis_b f
969 WHERE existsnode(VALUE(e),
970 '/H/V[@N="EntityOccuranceName" or @N="EntityOccuranceCode" or @N="RefEntityOccuranceCode"]') = 1
971 -- AND existsnode(VALUE(e),
972 -- '/H/V[@N="SelectionFlag" and .="Y"]') = 1
973 AND extractvalue(VALUE(e),
974 '/H/V[@N="EntityOccuranceCode" and .="' ||
975 v_ref_eo_code_list(i) ||
976 '"]/../V[@N="EntityCode"]/text()') =
977 f.entity_code
978 AND extractvalue(d.selection_set,
979 '/EXT/H[N="SelectionSetsVO"]/V[@N="StructureCode"]/text()') =
980 f.structure_code
981 AND d.job_name = p_job_name
982 AND d.request_type = 'T'
983 AND d.user_id = p_user_id;
984 ---here check if this is a split or an original entity
985 if v_split_flag_list(i)='Y'
986 then
987 --Thy shalt retain thy Parent's API Code
988 v_current_api_code := v_parent_api_code;
989 else
990
991 SELECT f.api_code
992 INTO v_current_api_code
993 FROM az_requests d,
994 TABLE(xmlsequence(extract(d.selection_set,
995 '/EXT/H'))) e,
996 az_structure_apis_b f
997 WHERE existsnode(VALUE(e),
998 '/H/V[@N="EntityOccuranceName" or @N="EntityOccuranceCode" or @N="RefEntityOccuranceCode"]') = 1
999 -- AND existsnode(VALUE(e),
1000 -- '/H/V[@N="SelectionFlag" and .="Y"]') = 1
1001 AND extractvalue(VALUE(e),
1002 '/H/V[@N="EntityOccuranceCode" and .="' ||
1003 v_eo_code_list(i) ||
1004 '"]/../V[@N="EntityCode"]/text()') =
1005 f.entity_code
1006 AND extractvalue(d.selection_set,
1007 '/EXT/H[N="SelectionSetsVO"]/V[@N="StructureCode"]/text()') =
1008 f.structure_code
1009 AND d.job_name = p_job_name
1010 AND d.request_type = 'T'
1011 AND d.user_id = p_user_id;
1012
1013 end if;
1014 v_mapped_attributes_map := get_mapped_attributes(v_current_api_code,
1015 v_parent_api_code);
1016 v_child_attribute_name_list.TRIM(v_child_attribute_name_list.COUNT);
1017 v_child_count := 1;
1018 if v_mapped_attributes_map.COUNT>0
1019 then
1020
1021 --Introduced to take care of one-many mappings between mapped attributes
1022 SELECT REQUIRED_API_ATTRIBUTE, DEPENDANT_API_ATTRIBUTE BULK COLLECT
1023 INTO V_REQ_API_MAP_ATTR_NAME_LIST, V_DEP_API_MAP_ATTR_NAME_LIST
1024 FROM AZ_API_DEPENDENCY_ATTRIBUTES
1025 WHERE REQUIRED_API_CODE = v_parent_api_code
1026 AND DEPENDANT_API_CODE = v_current_api_code;
1027
1028
1032 v_attr_name := v_attribute_name_list(j);
1029 FOR j IN 1 .. v_attribute_name_list.COUNT
1030 LOOP
1031 BEGIN
1033 v_child_attribute_name := v_mapped_attributes_map(v_attr_name);
1034
1035 --Introduced to take care of one-many mappings between mapped attributes
1036 FOR K IN 1 .. V_REQ_API_MAP_ATTR_NAME_LIST.COUNT LOOP
1037 BEGIN
1038 IF V_REQ_API_MAP_ATTR_NAME_LIST(K) = v_attr_name
1039
1040 THEN
1041 v_child_attribute_name_list.EXTEND;
1042
1043 v_child_attribute_name_list(v_child_count) := V_DEP_API_MAP_ATTR_NAME_LIST(K);
1044
1045 -- LMATHUR - for each of the mapped attribute get the set of values and construct the condition string
1046 IF LENGTH(v_attribute_value_list(j))>0
1047 THEN
1048 UPDATE_XSL_EXISTSNODE_STR(p_request_id, v_ref_eo_code_list(i), v_child_attribute_name_list(v_child_count),v_attribute_value_list(j), v_attribute_name_list(j), V_UPDATE_XSL,V_EXISTSNODE_STRING);
1049 END IF;
1050 v_child_count := v_child_count + 1;
1051
1052 END IF;
1053 END;
1054 END LOOP;
1055
1056 EXCEPTION
1057 WHEN no_data_found THEN
1058 ---LMATHUR - For Non-mapped attributes
1059 IF LENGTH(v_attribute_value_list(j))>0
1060 THEN
1061 UPDATE_XSL_EXISTSNODE_STR(p_request_id, v_ref_eo_code_list(i), v_attribute_name_list(j),v_attribute_value_list(j),v_attribute_name_list(j), V_UPDATE_XSL,V_EXISTSNODE_STRING);
1062 END IF;
1063 --NULL;
1064 END;
1065 END LOOP;
1066 IF LENGTH(V_UPDATE_XSL)>0
1067 THEN
1068 V_UPDATE_XSL := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
1069 <xsl:template match="H">
1070 <xsl:copy>
1071 <xsl:copy-of select="@*"/>
1072 <xsl:apply-templates/>
1073 </xsl:copy>
1074 </xsl:template>
1075 <xsl:template match="V">
1076 <xsl:copy>
1077 <xsl:copy-of select="@*[not(name()=''A2'')]"/>
1078 <xsl:choose>'||V_UPDATE_XSL;
1079
1080 V_UPDATE_XSL := V_UPDATE_XSL ||'<xsl:otherwise>
1081 <xsl:copy-of select="A"/><xsl:copy-of select="B"/>
1082 </xsl:otherwise>
1083 </xsl:choose>
1084 </xsl:copy>
1085 </xsl:template>
1086 </xsl:stylesheet>';
1087 v_transform_all_sql := get_transform_all_sql(
1088 V_EXISTSNODE_STRING,
1089 p_request_id,
1090 v_source_list(i));
1091
1092 EXECUTE IMMEDIATE v_transform_all_sql using V_UPDATE_XSL;
1093
1094
1095 -- LMATHUR - now for the given source, update the IS_TRANSFORMED for the master records
1096 select d.id bulk collect into V_CHILD_ID_LIST
1097 from az_diff_results d where d.request_id=p_request_id and d.source=v_source_list(i)
1098 and d.param2 = 'Y';
1099 update_master_flag(p_request_id,v_source_list(i),'IS_TRANSFORMED',v_child_id_list,'N');
1100
1101 END IF;
1102 V_UPDATE_XSL :='';
1103 V_EXISTSNODE_STRING := '';
1104
1105 end if;
1106 END IF;
1107
1108 update_regen_reqd(p_request_id,
1109 v_eo_code_list(i),
1110 v_source_list(i));
1111
1112 END LOOP;
1113
1114 COMMIT;
1115
1116 EXCEPTION
1117 WHEN application_exception THEN
1118 RAISE;
1119 WHEN OTHERS THEN
1120 raise_error_msg(SQLCODE,
1121 SQLERRM,
1122 'transform_all',
1123 'procedure end');
1124
1125 END transform_all;
1126
1127 -- LMATHUR added - procedure to update the XSL String construction and modify the ExistsNode String while taking into consideration
1128 --- the set of values for that attribute in the Parent.
1129
1130 PROCEDURE UPDATE_XSL_EXISTSNODE_STR ( P_REQUEST_ID IN NUMBER,
1131 P_SOURCE IN VARCHAR2,
1132 P_ATTR_NAME IN VARCHAR2,
1133 P_ATTR_NEW_VALUE IN VARCHAR2,
1134 P_PARENT_ATTR_NAME IN VARCHAR2,
1135 P_UPDATE_XSL IN OUT NOCOPY VARCHAR2,
1136 P_EXISTSNODE_STRING IN OUT NOCOPY VARCHAR2) IS
1137
1138 v_child_attribute_value_list typ_nest_tab_varchar := typ_nest_tab_varchar();
1139 BEGIN
1140 IF LENGTH(P_EXISTSNODE_STRING)>0
1141 THEN
1142 P_EXISTSNODE_STRING := P_EXISTSNODE_STRING|| ' OR ';
1143 END IF;
1144
1145 P_UPDATE_XSL := P_UPDATE_XSL ||'<xsl:when test="@N='''||p_attr_name||''' "><xsl:attribute name = "A2">Y</xsl:attribute>
1146 <xsl:copy-of select = "A"/>
1147 <xsl:element name = "B">'
1148 ||P_ATTR_NEW_VALUE||'</xsl:element></xsl:when>';
1152 EXCEPTION
1149 P_EXISTSNODE_STRING := P_EXISTSNODE_STRING||'existsnode(e.attr_diff,''/H/V[@N="'||p_attr_name||'" ] '')=1 ';
1150
1151
1153 WHEN NO_DATA_FOUND
1154 THEN
1155 NULL;
1156
1157
1158 END UPDATE_XSL_EXISTSNODE_STR;
1159
1160 FUNCTION GET_TRANSFORM_SQL(P_REQUEST_ID IN NUMBER,
1161 P_DEPENDANT_SOURCE IN VARCHAR2,
1162 P_XSL_STRING IN VARCHAR2,
1163 P_PARENT_ID IN NUMBER,
1164 P_MASTER_FLAG IN VARCHAR2 ) RETURN VARCHAR2 IS
1165 V_TRANSFORM_SQL VARCHAR2(32767);
1166 BEGIN
1167 V_TRANSFORM_SQL := V_TRANSFORM_SQL ||
1168 'update az_diff_results e set e.param2 = ''Y'',';
1169
1170 IF P_MASTER_FLAG = 'Y'
1171 THEN
1172 V_TRANSFORM_SQL := V_TRANSFORM_SQL ||' IS_TRANSFORMED = ''Y'',';
1173 END IF;
1174 V_TRANSFORM_SQL := V_TRANSFORM_SQL ||' e.attr_diff = (select d.attr_diff.transform( xmltype(''<?xml version="1.0" ?> ';
1175
1176 V_TRANSFORM_SQL := V_TRANSFORM_SQL ||
1177 ' <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> ';
1178 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' <xsl:template match="H"> ';
1179 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' <H> ';
1180 V_TRANSFORM_SQL := V_TRANSFORM_SQL ||
1181 ' <xsl:for-each select="V"> ';
1182 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' <V> ';
1183
1184 v_transform_sql := v_transform_sql || ' <xsl:copy-of select="@*[not(name()=''''T'''' or name()=''''A2'''')]"/> ';
1185 v_transform_sql := v_transform_sql || ' <xsl:attribute name="T"> ';
1186 v_transform_sql := v_transform_sql || ' 0 ';
1187 v_transform_sql := v_transform_sql || ' </xsl:attribute> ';
1188 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' <xsl:choose> ';
1189 V_TRANSFORM_SQL := V_TRANSFORM_SQL || P_XSL_STRING;
1190 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' <xsl:otherwise> ';
1191 V_TRANSFORM_SQL := V_TRANSFORM_SQL ||
1192 ' <xsl:copy-of select ="A"/> <xsl:copy-of select ="B"/>';
1193 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </xsl:otherwise> ';
1194 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </xsl:choose> ';
1195 -- V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </B> ';
1196 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </V> ';
1197 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </xsl:for-each> ';
1198 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </H> ';
1199 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </xsl:template> ';
1200 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' </xsl:stylesheet> ';
1201 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' '')).createSchemaBasedXml(''' ||
1202 DIFF_SCHEMA_URL || ''')';
1203 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' from az_diff_results d ';
1204 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' where d.request_id = ' ||
1205 P_REQUEST_ID;
1206 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' AND d.source = ''' ||
1207 P_DEPENDANT_SOURCE || '''';
1208 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' and d.id = ' || P_PARENT_ID || ')';
1209 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' where e.request_id = ' ||
1210 P_REQUEST_ID;
1211 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' AND e.source = ''' ||
1212 P_DEPENDANT_SOURCE || '''';
1213 V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' AND e.id = ' || P_PARENT_ID;
1214 RETURN V_TRANSFORM_SQL;
1215 EXCEPTION
1216 WHEN APPLICATION_EXCEPTION THEN
1217 RAISE;
1218 WHEN OTHERS THEN
1219 RAISE_ERROR_MSG(SQLCODE,
1220 SQLERRM,
1221 'GET_TRANSFORM_SQL',
1222 'procedure end');
1223 END;
1224
1225 PROCEDURE raise_error_msg(errcode IN NUMBER, errmsg IN VARCHAR2, procedurename IN VARCHAR2, statement IN VARCHAR2) IS
1226
1227 v_message VARCHAR2(2048);
1228
1229 BEGIN
1230
1231 IF(fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1232 fnd_message.set_name('AZ', 'AZ_R12_PLSQL_EXCEPTION');
1233 -- Seeded Message
1234 -- Runtime Information
1235 fnd_message.set_token('ERROR_CODE', errcode);
1236 fnd_message.set_token('ERROR_MESG', errmsg);
1237 fnd_message.set_token('ERROR_PROC', 'az_r12_transform_cascade.' || procedurename);
1238
1239 IF(statement IS NOT NULL) THEN
1240 fnd_message.set_token('ERROR_STMT', statement);
1241 ELSE
1242 fnd_message.set_token('ERROR_STMT', 'none');
1243 END IF;
1244
1245 raise_application_error(-20001, fnd_message.GET);
1246 END IF;
1247
1248 END raise_error_msg;
1249
1250 FUNCTION get_transform_all_sql(
1251 P_EXISTSNODE_STRING IN VARCHAR2,
1252 p_request_id IN NUMBER,
1253 p_source IN VARCHAR2)
1254 RETURN VARCHAR2 IS
1255 v_transform_all_sql VARCHAR2(32767);
1256 BEGIN
1257 v_transform_all_sql := 'update az_diff_results e set e.param2 = ''Y'', e.attr_diff =e.attr_diff.transform( xmltype(:1';
1258
1259
1260 v_transform_all_sql := v_transform_all_sql ||
1261 ')).createSchemaBasedXml(''' || diff_schema_url ||
1262 ''')';
1263 v_transform_all_sql := v_transform_all_sql || ' where e.request_id = ' ||
1264 p_request_id || ' AND e.source ='''|| p_source ||''' and ('||P_EXISTSNODE_STRING||')';
1265
1266 RETURN v_transform_all_sql;
1267
1268 EXCEPTION
1269 WHEN application_exception THEN
1270 RAISE;
1271 WHEN OTHERS THEN
1272 raise_error_msg(SQLCODE,
1276
1273 SQLERRM,
1274 'get_transform_all_sql',
1275 'procedure end');
1277 END get_transform_all_sql;
1278
1279
1280
1281 --lmathur added
1282 -- procedure to modify the attributes in the Child VOs of the Root Source based on the same attribute and value being present
1283 -- This fixes the issue where the same attribute name-value is not transformed in the TL or child records for a given source
1284 -- Function and procedure implementations
1285 PROCEDURE TRANSFORM_ALL_ATTR_SOURCE(P_REQUEST_ID IN NUMBER,
1286 P_SOURCE IN VARCHAR2,
1287 P_ID NUMBER,
1288 p_diff_schema_url IN VARCHAR2) IS
1289 v_child_id_list TYP_NEST_TAB_VARCHAR;
1290 v_attr_name_list TYP_NEST_TAB_VARCHAR;
1291 v_attr_old_value_list TYP_NEST_TAB_VARCHAR;
1292 v_attr_new_value_list TYP_NEST_TAB_VARCHAR;
1293 V_EXISTSNODE_STRING VARCHAR2(32767);
1294 V_CONFLICT_XSL VARCHAR2(32767);
1295 V_CONFLICT_PARAM3_COUNT NUMBER;
1296 V_QUERY_STR VARCHAR2(32767);
1297
1298 BEGIN
1299
1300
1301 SELECT EXTRACTVALUE(VALUE(E), '/V/@N'),
1302 EXTRACTVALUE(VALUE(E), '/V/B/text()'),
1303 EXTRACTVALUE(VALUE(E), '/V/A/text()') BULK COLLECT
1304 INTO v_attr_name_list,
1305 v_attr_new_value_list,
1306 v_attr_old_value_list
1307 FROM AZ_DIFF_RESULTS D,
1308 TABLE(XMLSEQUENCE(EXTRACT(D.ATTR_DIFF, '/H/V'))) E
1309 WHERE D.REQUEST_ID = p_request_id
1310 AND D.SOURCE = p_source
1311 AND D.param2 = 'Y'
1312 AND D.ID = P_ID
1313 AND D.TYPE <> -1
1314 AND existsNode(VALUE(E),'/V[@A2="Y"]') = 1;
1315
1316
1317 -- populate the list of child VO IDs
1318
1319 SELECT id BULK COLLECT INTO v_child_id_list
1320 FROM
1321 (SELECT d.id id,
1322 d.parent_id parent_id
1323 FROM az_diff_results d
1324 WHERE d.request_id = p_request_id
1325 AND d.source = p_source)
1326 f START WITH f.id = P_ID CONNECT BY PRIOR f.id = f.parent_id
1327 ORDER BY f.parent_id;
1328 FOR j IN 1 .. v_child_id_list.COUNT LOOP
1329
1330 V_CONFLICT_XSL := '';
1331 V_EXISTSNODE_STRING := '';
1332 --- For each of the child, check and update for each of the transformed attribute
1333 FOR i IN 1 .. v_attr_name_list.COUNT LOOP
1334 UPDATE az_diff_results d
1335 SET d.attr_diff = updatexml(d.attr_diff, '/H/V[@N="'||v_attr_name_list(i)||'" and ./A/text()="'||v_attr_old_value_list(i)||'"]/B/text()',v_attr_new_value_list(i) )
1336 WHERE existsnode(d.attr_diff, '/H/V[@N="'||v_attr_name_list(i)||'" and ./A/text()="'||v_attr_old_value_list(i)||'"]') = 1
1337 AND d.request_id = p_request_id
1338 AND d.source = p_source
1339 AND d.id = v_child_id_list(j);
1340 ---Also create the conflict XSL and ExistsNode String
1341 V_CONFLICT_XSL := V_CONFLICT_XSL||
1342 ' <xsl:when test="@N!='''''||v_attr_name_list(i)||''''' and ./B/text()='''''||v_attr_old_value_list(i)||''''' "> '
1343 ||' <xsl:attribute name="T">1</xsl:attribute>'
1344 ||' <xsl:if test="not(@A1)"><xsl:attribute name="A1">Y</xsl:attribute></xsl:if>'
1345 ||'</xsl:when>';
1346
1347 IF LENGTH(V_EXISTSNODE_STRING) >0
1348 THEN
1349 V_EXISTSNODE_STRING := V_EXISTSNODE_STRING||' OR ';
1350 END IF;
1351 V_EXISTSNODE_STRING := V_EXISTSNODE_STRING||
1352 ' existsnode(d.attr_diff, ''/H/V[@N!="' ||
1353 v_attr_name_list(i) ||
1354 '"]/B[.="' ||
1355 v_attr_old_value_list(i) ||
1356 '"]'')=1 ';
1357 END LOOP;
1358 --LMATHUR - Now for each of the CHILD, we need to update the PARAM3 and Conflicts at the attribute level, if exists
1359 IF LENGTH(V_CONFLICT_XSL)>0
1360 THEN
1361 --make the conflict XSL well-formed
1362 V_CONFLICT_XSL :='<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
1363 <xsl:template match="H">
1364 <H>
1365 <xsl:for-each select="V">
1366 <V>
1367 <xsl:copy-of select="@*[not(name()=''''T'''')]"/>
1368 <xsl:choose>'||V_CONFLICT_XSL||
1369 ' <xsl:otherwise>
1370 <xsl:attribute name="T"><xsl:value-of select="@T"/></xsl:attribute>
1371 </xsl:otherwise>
1372 </xsl:choose>
1373 <xsl:copy-of select="A"/>
1374 <xsl:copy-of select="B"/>
1375 </V>
1376 </xsl:for-each>
1377 </H>
1378 </xsl:template>
1379 </xsl:stylesheet>';
1383 WHERE '||V_EXISTSNODE_STRING||'
1380 V_EXISTSNODE_STRING := '('||V_EXISTSNODE_STRING||')';
1381 V_QUERY_STR := 'UPDATE az_diff_results d
1382 SET d.attr_diff = d.attr_diff.transform(xmltype('''||V_CONFLICT_XSL||''') ).createSchemaBasedXML('''||p_diff_schema_url||''')
1384 AND d.request_id ='|| p_request_id||'
1385 AND d.source ='''|| p_source||'''
1386 AND d.id = '||v_child_id_list(j);
1387
1388 EXECUTE IMMEDIATE V_QUERY_STR;
1389
1390 END IF;
1391 END LOOP;
1392 -- Now we need to update the master is_transformed flag for the changed child records
1393 update_master_flag(p_request_id,p_source,'IS_TRANSFORMED',v_child_id_list,'N');
1394
1395
1396 -- Now we need to update the master record's param3 flag which are having conflicts in childs
1397 update_master_flag(p_request_id,p_source,'PARAM3',v_child_id_list,'Y');
1398 --- Redundancy removal
1399
1400
1401 -- Check if there were any records which were marked for conflict as this is the basis for
1402 -- indicating the conflict flag (A5=C) in the Selection Set XML
1403 -- select count(1) into V_CONFLICT_PARAM3_COUNT from az_diff_results d where d.param3 = 'Y' and
1404 -- d.request_id = P_REQUEST_ID
1405 -- and d.source=p_source;
1406 --
1407 -- IF V_CONFLICT_PARAM3_COUNT > 0
1408 -- THEN
1409 -- --LMATHUR - To show conflict icon in the VIEW mode, we need an indicator for conflicts- A5=C indicate conflicts
1410 -- EXECUTE IMMEDIATE 'UPDATE az_requests d
1411 -- SET d.selection_set = updatexml(d.selection_set, ''/EXT/H[S="'||p_source||'"]/@A5'',''C'')
1412 -- WHERE d.request_id = '||P_REQUEST_ID||
1413 -- ' AND d.request_type = ''T''';
1414 -- END IF;
1415 --LMATHUR and HBODA -- Changes to update the Conflict count in the selection Set XML so as to be used in UI
1416 update_conflict_status(p_request_id,P_SOURCE);
1417 EXCEPTION
1418 WHEN NO_DATA_FOUND THEN
1419 NULL;
1420 WHEN OTHERS THEN
1421 raise_error_msg(SQLCODE,
1422 SQLERRM,
1423 'TRANSFORM_ALL_ATTR_SOURCE',
1424 'procedure end');
1425
1426
1427 END TRANSFORM_ALL_ATTR_SOURCE;
1428 PROCEDURE update_master_flag(P_REQUEST_ID IN NUMBER,
1429 P_SOURCE IN VARCHAR2,
1430 p_column_name IN VARCHAR2,
1431 p_id_list IN TYP_NEST_TAB_VARCHAR,
1432 p_upd_entire_tree_flag IN VARCHAR2 ) IS
1433 v_entire_tree_string varchar2(255) := ' k.parent_id = 1 AND ';
1434 v_has_conflicts varchar2(1) := 'Y';
1435 v_has_conflicts_sql varchar2(5000) := '';
1436 v_additional_where_clause varchar2(255) := 'Y';
1437 BEGIN
1438 IF p_upd_entire_tree_flag = 'Y'
1439 THEN
1440 v_entire_tree_string := '';
1441 END IF;
1442 --Change to ensure that the only those records flag are updated where the conflicts or transformation has actually happened
1443 IF p_column_name = 'PARAM3'
1444 THEN
1445 v_additional_where_clause := ' decode(existsNode(d.attr_diff, ''/H/V[@A1="Y"]''),''1'',''Y'',''N'') ';
1446 END IF;
1447 IF p_column_name = 'PARAM2'
1448 THEN
1449 v_additional_where_clause := ' decode(existsNode(d.attr_diff, ''/H/V[@T="1"]''),''1'',''Y'',''N'') ';
1450 END IF;
1451
1452 FOR i IN 1 .. p_id_list.COUNT LOOP
1453 IF p_column_name <> 'IS_TRANSFORMED'
1454 THEN
1455
1456 v_has_conflicts_sql := 'select ' || v_additional_where_clause || '
1457 from az_diff_results d
1458 WHERE d.request_id = '||p_request_id||'
1459 AND d.source = '''||p_source||'''
1460 AND d.id =' || p_id_list(i) || '';
1461 EXECUTE IMMEDIATE v_has_conflicts_sql into v_has_conflicts;
1462
1463 END IF;
1464 IF v_has_conflicts = 'Y' THEN
1465
1466 EXECUTE IMMEDIATE 'UPDATE az_diff_results g
1467 SET g.'||p_column_name||' = ''Y''
1468 WHERE g.id in
1469 (SELECT k.id
1470 FROM
1471 (SELECT parent_id, id, '||p_column_name||'
1472 FROM
1473 (SELECT d.parent_id, d.id, d.'||p_column_name||'
1474 FROM az_diff_results d
1475 WHERE d.request_id = '||p_request_id||'
1476 AND d.source = '''||p_source||'''
1477 AND d.parent_id >0) f
1478 CONNECT BY PRIOR f.parent_id = f.id START WITH f.id = '||p_id_list(i)||') k
1479 WHERE '||v_entire_tree_string||' (k.'||p_column_name||' IS NULL OR k.'||p_column_name||' <> ''Y''))
1480 AND g.request_id = '||p_request_id||'
1481 AND g.source = '''||p_source||'''';
1482 END IF;
1483 END LOOP;
1484 END update_master_flag;
1485
1486 PROCEDURE update_conflict_status(P_REQUEST_ID IN NUMBER, P_SOURCE IN VARCHAR2) IS
1487 v_count number := 0;
1488 BEGIN
1489 select count(1) into v_count from az_diff_results e where request_id= p_request_id
1493 -- Now check if all the conflicts are resolved for this source
1490 and source = p_source and existsnode(e.attr_diff,'/H/V[@A1="Y"]')=1;
1491 IF v_count >0 --this source atleast had one conflict
1492 THEN
1494 select count(1) into v_count from az_diff_results e where request_id= p_request_id
1495 and source = p_source and existsnode(e.attr_diff,'/H/V[@A1="Y" and (./A/text()=./B/text())]') = 1;
1496
1497 EXECUTE IMMEDIATE 'UPDATE az_requests g
1498 SET g.selection_set = updateXML(g.selection_set, ''/EXT/H/V[@N="EntityOccuranceCode" and .="'|| P_SOURCE||'"]/../@A5'',decode('||v_count||',0,''Y'',''C'') )
1499 WHERE g.request_id = '||p_request_id||' and request_type=''T''';
1500
1501
1502 END IF;
1503
1504
1505 EXCEPTION
1506 WHEN NO_DATA_FOUND
1507 THEN
1508 RAISE_ERROR_MSG(SQLCODE,
1509 SQLERRM,
1510 'UPDATE_CONFLICT_STATUS: Could not update the conflict status for Source:'||p_source,
1511 'UPDATE_CONFLICT_STATUS');
1512 END update_conflict_status;
1513
1514 END AZ_R12_TRANSFORM_CASCADE;
1515
1516