eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AZ_R12_TRANSFORM_CASCADE dependencies on AZ_DIFF_RESULTS

Line 196: FROM AZ_DIFF_RESULTS

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

Line 205: FROM AZ_DIFF_RESULTS D,

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'

Line 224: FROM AZ_DIFF_RESULTS

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

Line 234: FROM AZ_DIFF_RESULTS

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;

Line 249: FROM AZ_DIFF_RESULTS D,

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,
248: V_REQ_API_ATTR_OLD_VALUE_LIST
249: FROM AZ_DIFF_RESULTS D,
250: TABLE(XMLSEQUENCE(EXTRACT(D.ATTR_DIFF, '/H/V'))) E
251: WHERE D.REQUEST_ID = P_REQUEST_ID
252: AND D.SOURCE = P_REQUIRED_SOURCE
253: AND D.IS_TRANSFORMED = 'Y'

Line 274: V_DEPENDANT_IDS_SQL := 'SELECT q.id FROM AZ_DIFF_RESULTS q ';

270: ';
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 = ''' ||

Line 480: V_DEP_DETAIL_CHILD_IDS_SQL := V_DEP_DETAIL_CHILD_IDS_SQL || ' FROM az_diff_results d ';

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 || ' )';

Line 524: V_TEMP_SQL := 'UPDATE az_diff_results q SET param3 = ''Y'', q.attr_diff = q.attr_diff.transform(xmltype('''||V_CONFLICT_XSL||''')).createSchemaBasedXml(''' ||

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:

Line 540: FROM az_diff_results d

536: EXECUTE IMMEDIATE V_TEMP_SQL;
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
540: FROM az_diff_results d
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

Line 551: V_TEMP_SQL :='UPDATE az_diff_results q SET param3 = ''Y'', q.attr_diff = q.attr_diff.transform(xmltype('''||V_CONFLICT_XSL||''')).createSchemaBasedXml(''' ||

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:

Line 576: -- from az_diff_results where request_id=p_request_id and source=P_DEPENDANT_SOURCE

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

Line 594: -- select count(1) into V_CONFLICT_PARAM3_COUNT from az_diff_results d where d.param3 = 'Y' and

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

Line 609: from az_diff_results where request_id=p_request_id and source=P_DEPENDANT_SOURCE

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');

Line 651: FROM az_diff_results

647:
648:
649: SELECT COUNT(*)
650: INTO v_count
651: FROM az_diff_results
652: WHERE REQUEST_ID = P_REQUEST_ID
653: AND SOURCE = p_dependant_source
654: AND is_transformed='Y';
655:

Line 944: from az_diff_results d where d.request_id=p_request_id and d.source=p_source

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 :='';

Line 952: SELECT ID bulk collect into v_master_ids_trans_list FROM AZ_DIFF_RESULTS

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

Line 1097: from az_diff_results d where d.request_id=p_request_id and d.source=v_source_list(i)

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;

Line 1168: 'update az_diff_results e set e.param2 = ''Y'',';

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'',';

Line 1203: V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' from az_diff_results d ';

1199: V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' ';
1200: V_TRANSFORM_SQL := V_TRANSFORM_SQL || ' ';
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 || '''';

Line 1257: v_transform_all_sql := 'update az_diff_results e set e.param2 = ''Y'', e.attr_diff =e.attr_diff.transform( xmltype(:1';

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 ||

Line 1307: FROM AZ_DIFF_RESULTS D,

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'

Line 1323: FROM az_diff_results d

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;

Line 1334: UPDATE az_diff_results d

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

Line 1381: V_QUERY_STR := 'UPDATE az_diff_results d

1377:
1378:
1379: ';
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||''')
1383: WHERE '||V_EXISTSNODE_STRING||'
1384: AND d.request_id ='|| p_request_id||'
1385: AND d.source ='''|| p_source||'''

Line 1403: -- select count(1) into V_CONFLICT_PARAM3_COUNT from az_diff_results d where d.param3 = 'Y' and

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

Line 1457: from az_diff_results d

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;

Line 1466: EXECUTE IMMEDIATE 'UPDATE az_diff_results g

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

Line 1474: FROM az_diff_results d

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

Line 1489: select count(1) into v_count from az_diff_results e where request_id= p_request_id

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
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
1493: -- Now check if all the conflicts are resolved for this source

Line 1494: select count(1) into v_count from az_diff_results e where request_id= p_request_id

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
1493: -- Now check if all the conflicts are resolved for this source
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'') )