[Home] [Help]
736: loop
737:
738: select nvl(sum(risk_count), 0), nvl(sum(control_count), 0)
739: into l_risk_count, l_control_count
740: from amw_process_organization amwp, Amw_Org_Hierarchy_Denorm aodenorm
741: where aodenorm.organization_id = l_org
742: and aodenorm.process_id = v_index
743: and aodenorm.up_down_ind = 'D'
744: and amwp.process_id = aodenorm.parent_child_id
809: l_dummy number;
810:
811:
812: begin
813: delete from Amw_Org_Hierarchy_Denorm where organization_id = p_org_id;
814: open c11(p_org_id);
815: loop
816: fetch c11 bulk collect into v_apo_id;
817: exit when c11%notfound;
841: FOR i IN j..v_org_parent_id.count LOOP
842:
843: begin
844: select 1 into l_dummy
845: from Amw_Org_Hierarchy_Denorm
846: where Organization_Id = p_org_id
847: and Process_Id = v_apo_id(v_index)
848: and Parent_Child_Id = v_org_parent_id(i)
849: and Up_Down_Ind = 'U';
849: and Up_Down_Ind = 'U';
850:
851: exception
852: when no_data_found then
853: insert into Amw_Org_Hierarchy_Denorm
854: (Organization_Id,
855: Process_Id,
856: Parent_Child_Id,
857: Up_Down_Ind,
886: FOR i IN jDown..v_org_child_id.count LOOP
887:
888: begin
889: select 1 into l_dummy
890: from Amw_Org_Hierarchy_Denorm
891: where Organization_Id = p_org_id
892: and Process_Id = v_apo_id(v_index)
893: and Parent_Child_Id = v_org_child_id(i)
894: and Up_Down_Ind = 'D';
894: and Up_Down_Ind = 'D';
895:
896: exception
897: when no_data_found then
898: insert into Amw_Org_Hierarchy_Denorm
899: (Organization_Id,
900: Process_Id,
901: Parent_Child_Id,
902: Up_Down_Ind,