[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_for_show_only_diff(p_request_id IN NUMBER, p_source IN VARCHAR2);
PROCEDURE update_diff_type_counts(p_request_id IN NUMBER, p_source IN VARCHAR2);
SELECT name,
display_name,
hashcode_details
INTO v_am_name_a,
v_am_disp_name_a,
v_hcd_a
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND id = 1
AND type = -1
AND is_primary = 'Y';
INSERT
INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
VALUES(v_am_name_a, -- name
v_am_disp_name_a, p_request_id, p_source, -1, -- type
v_results_id, 0, -- parent_id
v_hcd_a, 1, -- depth
'N', -- isdifferent
'N', -- istransformed
'N', -- show_only_diff
'Y', -- exclude details for AM
NULL);
fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name || to_char(systimestamp), 'Inserting into az_diff_results for request_id: '
|| p_request_id || ' source: ' || p_source);
UPDATE az_reporter_data
SET deleted_flag = 'Y'
WHERE request_id = p_request_id
AND source = p_source
AND id = 1
AND is_primary = 'N';
update_for_show_only_diff(p_request_id, p_source);
update_diff_type_counts(p_request_id, p_source);
SELECT name,
display_name,
id,
hashcode,
hashcode_details bulk collect
INTO v_children_a_name_list,
v_children_a_disp_name_list,
v_children_a_id_list,
v_children_a_hc_list,
v_children_a_hcd_list
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND parent_id = p_data_pid_a
AND is_primary = 'Y';
SELECT id
INTO v_matching_vo_id_b
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND hashcode = v_children_a_hc_list(i)
AND parent_id = p_data_pid_b
AND is_primary = 'N'
AND deleted_flag = 'N'
AND rownum = 1;
UPDATE az_reporter_data
SET deleted_flag = 'Y'
WHERE request_id = p_request_id
AND source = p_source
AND id = v_matching_vo_id_b
AND is_primary = 'N';
SELECT xmlquery('for $a in 1
let $common := for $i in $PRIM/H/V, $j in $SEC/H/V
where ($i/@N eq $j/@N)
return
if ($i/@U = "Y")
then {($i/A)}{($j/B)}
else {($i/A)}{($j/B)}
let $exsec := for $j in $SEC/H/V
return
if (some $i in $common satisfies $i/@N = $j/@N)
then ""
else $j
let $exprim := for $j in $PRIM/H/V
return
if (some $i in $common satisfies $i/@N = $j/@N)
then ""
else $j
return {$common}{$exprim}{$exsec} ' passing
PRIM.attributes as "PRIM", SEC.attributes as "SEC"
returning content).createSchemaBasedXml(DIFF_SCHEMA_URL)
into v_temp_xmltype
from az_reporter_data PRIM,az_reporter_data SEC
where PRIM.request_id = SEC.request_id
and PRIM.source = SEC.source
and PRIM.request_id = p_request_id
and PRIM.source = p_source
and PRIM.id=v_children_A_ID_List(i)
and PRIM.is_primary='Y'
and SEC.id = v_matching_VO_id_B
and SEC.is_primary = 'N';
select decode(existsNode(v_temp_xmltype,'/H/V[((not(./A/text()!="") and ./B/text()!="") or (not(./B/text()!="") and ./A/text()!="") or (./A/text()!=./B/text())) and not(@U="Y")]'),1,'C','N')
INTO v_is_different
FROM dual;
SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
INTO v_exclude_details_temp
FROM dual;
INSERT
INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
VALUES(v_children_a_name_list(i), v_children_a_disp_name_list(i), p_request_id, p_source, v_type, p_results_id, p_results_pid, v_children_a_hcd_list(i), p_depth, -- depth
v_is_different, -- isdifferent
'N', -- istransformed
decode(v_is_different, 'C', 'Y', 'N'), --show only diff--v_show_only_diff, -- show_only_diff
v_exclude_details, v_temp_xmltype);
raise_error_msg(SQLCODE, sqlerrm, 'output_DF', 'get matching VO of B, collect attributes and insert');
SELECT id bulk collect
INTO v_children_b_id_list
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND parent_id = p_data_pid_b
AND is_primary = 'N'
AND deleted_flag = 'N';
SELECT name,
display_name,
id,
hashcode,
hashcode_details
INTO v_name_a,
v_disp_name_a,
v_id_a,
v_hc_a,
v_hcd_a
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND id = p_data_id
AND is_primary = 'Y';
SELECT d.attributes.transform(v_a_xmltype)
INTO v_temp_xmltype
FROM az_reporter_data d
WHERE request_id = p_request_id
AND source = p_source
AND id = p_data_id
AND is_primary = 'Y';
SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
INTO v_exclude_details_temp
FROM dual;
INSERT
INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
VALUES(v_name_a, v_disp_name_a, p_request_id, p_source, v_type, p_results_id, p_results_pid, v_hcd_a, p_depth, -- depth
'A', -- isdifferent
'N', -- istransformed
'Y', -- show_diff_only
v_exclude_details, xmltype(v_temp_xmltype.getclobval(), diff_schema_url, 1, 1));
SELECT id bulk collect
INTO v_children_a_id_list
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND parent_id = p_data_id
AND is_primary = 'Y';
SELECT name,
display_name,
id,
hashcode,
hashcode_details
INTO v_name_b,
v_disp_name_b,
v_id_b,
v_hc_b,
v_hcd_b
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND id = p_data_id
AND is_primary = 'N';
SELECT d.attributes.transform(v_b_xmltype)
INTO v_temp_xmltype
FROM az_reporter_data d
WHERE request_id = p_request_id
AND source = p_source
AND id = p_data_id
AND is_primary = 'N';
SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
INTO v_exclude_details_temp
FROM dual;
INSERT
INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
VALUES(v_name_b, v_disp_name_b, p_request_id, p_source, v_type, p_results_id, p_results_pid, v_hcd_b, p_depth, -- depth
'B', -- isdifferent
'N', -- istransformed
'Y', -- show_diff_only
v_exclude_details, xmltype(v_temp_xmltype.getclobval(), diff_schema_url, 1, 1));
UPDATE az_reporter_data
SET deleted_flag = 'Y'
WHERE request_id = p_request_id
AND source = p_source
AND id = v_id_b
AND is_primary = 'N';
SELECT id bulk collect
INTO v_children_b_id_list
FROM az_reporter_data
WHERE request_id = p_request_id
AND source = p_source
AND parent_id = p_data_id
AND is_primary = 'N'
AND deleted_flag = 'N';
PROCEDURE update_for_show_only_diff(p_request_id IN NUMBER, p_source IN VARCHAR2) IS
v_different_id_list typ_nest_tab_number;
l_api_name constant VARCHAR2(30) := 'update_for_show_only_diff : ';
SELECT id,
parent_id bulk collect
INTO v_different_id_list,
v_different_pid_list
FROM az_diff_results
WHERE request_id = p_request_id
AND source = p_source
AND is_different <> 'N' -- gets you A, B or C
ORDER BY depth;
SELECT parent_id,
is_different,
show_only_diff
INTO v_parent_id,
v_is_different,
v_show_only_diff
FROM az_diff_results
WHERE request_id = p_request_id
AND source = p_source
AND id = v_id;
UPDATE az_diff_results
SET show_only_diff = 'Y',
is_different = 'D'
WHERE request_id = p_request_id
AND source = p_source
AND id = v_id;
raise_error_msg(SQLCODE, sqlerrm, 'update_for_show_only_diff', 'select show_only_diff column of parents');
raise_error_msg(SQLCODE, sqlerrm, 'update_for_show_only_diff', 'procedure end');
END update_for_show_only_diff;
PROCEDURE update_diff_type_counts(p_request_id IN NUMBER, p_source IN VARCHAR2) IS
v_diff_type_list typ_nest_tab_varchar;
l_api_name constant VARCHAR2(40) := 'update_diff_type_counts : ';
fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name || to_char(systimestamp), 'update_diff_type_counts called with p_request_id: ' || p_request_id || ' p_source: ' || p_source);
SELECT nvl(COUNT, 0),
e.column_value.getrootelement() AS
col_name bulk collect
INTO v_diff_count_list,
v_diff_type_list
FROM
(SELECT decode(is_different, 'A', 'P6', 'B', 'P7', 'C', 'P8', 'D', 'P8', 'N', 'P9') name,
COUNT(is_different) COUNT
FROM az_diff_results d
WHERE request_id = p_request_id
AND source = p_source
AND parent_id = 1
GROUP BY is_different)
k,
TABLE(xmlsequence(EXTRACT(xmltype('
' || v_transform_xml || '
';
to_char(systimestamp), 'Query to update selection set with counts : update az_requests d set d.selection_set = d.selection_set.transform(xmltype(''' ||
v_transform_xml || ''')).createSchemabasedxml(d.selection_set.getSchemaURL()) WHERE request_id=' || p_request_id || ' and request_type=''C''');
EXECUTE IMMEDIATE 'update az_requests d set d.selection_set = d.selection_set.transform(xmltype(''' || v_transform_xml || ''')).createSchemabasedxml(d.selection_set.getSchemaURL()) WHERE request_id=' || p_request_id || ' and request_type=''C''';
raise_error_msg(SQLCODE, sqlerrm, 'update_diff_type_counts', 'Error while updating the count based on type of differences');
END update_diff_type_counts;