555555555555555

CREATE OR REPLACE PROCEDURE P_ACH_DATARIG_TREE(pParentId  IN ACH_Archives_Folder.Archives_Folder_Id%TYPE,
                                               userId     IN PLT_Employee.Emp_Id%TYPE,
                                               pOutCursor OUT CHZMB_DT.CurType) IS

  /*--------------------------------------------
  *  过程：取档案目录的下一层节点记录集
  *  说明：档案目录一共分四层：档案单元，档案目录，案卷，文件
  *  入参；parentId 上级节点ID
  *        userId    当前用户ID
  *  出参：pOutCursor  返回下一层节点记录集

  * --------------------------------------------*/

  t_vDebugFlag VARCHAR2(10) := '0'; --是否记录错误日志标志
  t_iErrNum    INT := 0; --记录错误位置
  t_vErrMsg    VARCHAR2(512) := ''; --错误描述
BEGIN
  --将数据权限存入临时表
  insert into tmp_pla_report
    (c1, c10)
    (SELECT bu.BU_Data_ID_Value, 'buID'
       FROM PLT_Data_PERM p
       JOIN PLT_BU_Data bu ON bu.bu_data_id = p.bu_data_id
       JOIN PLT_BU_Data_Type t ON t.BU_Data_Type_ID = bu.bu_data_type_id
      WHERE (p.emp_id = userId OR
            p.role_id IN (SELECT ur.role_id
                             FROM PLT_User_Role ur
                            WHERE ur.emp_id = userId))
        AND t.Data_Type_Code = 'ACH_ARCHIVE');
  --根结点档案单元,存入临时表
  insert into tmp_pla_report
    (c1, c2, c3, c4, c5, c6,c7, c10)
    select distinct u.unit_id as id,
                    '0' as parentid,
                    u.unit_name as name,
                    'UNIT' as nodeType,
                    u.unit_id,
                    null,
                    0 as sortCode,
                    'tree'
      from ach_unit u
     inner join ach_archives_folder f on f.ach_unit_id = u.unit_id
     where f.archives_folder_id in
           (select c1 from tmp_pla_report where c10 = 'buID');
  --档案目录,存入临时表
  insert into tmp_pla_report
    (c1, c2, c3, c4, c5, c6,c7, c10)
    select f.archives_folder_id as id,
           nvl(f.archives_folder_id_parent, f.ach_unit_id) as parnetId,
           folder_name as name,
           'FOLDER' as nodeType,
           f.ach_unit_id,
           f.folder_template_id,
           f.sort_code as sortCode,
           'tree'
      from ach_archives_folder f
     where f.archives_folder_id in
           (select c1 from tmp_pla_report where c10 = 'buID')
       and f.node_type = 'C157_1';

  --案卷,存入临时表
  insert into tmp_pla_report
    (c1, c2, c3, c4, c5, c6,c7, c10)
    select f.archives_folder_id as id,
           nvl(f.archives_folder_id_parent, f.ach_unit_id) as parnetId,
           folder_name as name,
           'ARCHIVE' as nodeType,
           f.ach_unit_id,
           f.folder_template_id,
           f.sort_code as sortCode,
           'tree'
      from ach_archives_folder f
     where f.node_type = 'C157_2';

  --已归档文件
  insert into tmp_pla_report
    (c1, c2, c3, c4, c5, c6,c7, c10)
    select fi.gather_folder_id,
           r.archives_folder_id,
           fi.folder_name,
           case when fi.node_type='C157_3' then
           'FILE' else 'DRAWING' end,
           ach.ach_unit_id,
           ach.folder_template_id,
           r.sort_code as sortCode,
           'tree'
      from ACH_File_Folder fi
     inner join ACH_File_Archives_Folder r on fi.Gather_Folder_ID =
                                              r.file_folder_id
     inner join ACH_Archives_Folder ach on ach.archives_folder_id =
                                           r.archives_folder_id;
  --未组卷文件
  insert into tmp_pla_report
    (c1, c2, c3, c4, c5, c6,c7, c10)
    select cfile.gather_folder_id,
           ach.archives_folder_id,
           cfile.folder_name,
           case when cfile.node_type='C157_3' then
           'FILE' else 'DRAWING' end ,
           ach.ach_unit_id,
           ach.folder_template_id,
           1000+cfile.sort_code as sortCode,
           'tree'
      from ACH_File_Folder fi
     inner join ach_folder_template_rela re on fi.folder_template_id =
                                               re.file_folder_template_id
     inner join ACH_Archives_Folder ach on ach.folder_template_id =
                                           re.ach_folder_template_id
     inner join ACH_File_Folder cfile on cfile.gather_folder_id_parent =
                                         fi.gather_folder_id
     where cfile.status = 'C188_3'
       and ach.archives_folder_id in
           (select c1 from tmp_pla_report where c10 = 'buID')
       and cfile.gather_folder_id not in
           (select distinct File_Folder_ID from ACH_File_Archives_Folder);

  --处理返回记录
  OPEN pOutCursor FOR
    select t.c1 as id,
           t.c2 as parentId,
           t.c3 as name,
           (select case
                     when count(*) > 0 then
                      '0'
                     else
                      '1'
                   end
              from tmp_pla_report r
             where r.c2 = t.c1) as isLeaf,
           t.c4 as nodeType,
           t.c5 as unitId,
           t.c6 as templateId
      from tmp_pla_report t
     where t.c10 = 'tree'
       and t.c2 = pParentId
       order by t.c7;
   --默认返回值
  <<defaultMethod>>

  IF t_vDebugFlag = '1' THEN
    t_vErrMsg := '执行失败！';
  END IF;
  RETURN;

EXCEPTION
  WHEN OTHERS THEN
    IF pOutCursor% ISOPEN THEN
      CLOSE pOutCursor;
    END IF;
 
    IF t_vDebugFlag = '1' THEN
      t_iErrNum := 3;
      t_vErrMsg := 'SQLCODE：' || TO_CHAR(SQLCODE) || '|' || SQLERRM;
   
    END IF;
 
    RETURN;
 
END;