CREATE OR REPLACE PROCEDURE SP_BI_NR_REPORT_01_01
( in_Year  in varchar2,
in_Month  in varchar2,
in_orgId  in varchar2,
in_orgNo  in varchar2
)
AS
var_orgid          varchar2(60);--机构ID
var_orgno          varchar2(60);--机构代码
var_orgname        varchar2(60);--机构名称
var_schemecode    varchar2(60);--方案代码
var_schemename    varchar2(60);--方案名称
var_tablename      varchar2(60);--表名
var_pprojectcode  varchar2(60);--大类项目代码
var_pprojectname  varchar2(60);--大类项目名称
oracle手动调用存储过程var_projectcode    varchar2(60);--项目代码
var_projectname    varchar2(60);--项目名称
var_subfid        varchar2(60);--实体机构ID
var_suborgname    varchar2(60);--实体机构名称
var_suborgno      varchar2(60);--实体机构代码
var_dateid        varchar2(60);--报表时间主键
var_personid      varchar2(60);--员工ID
var_FieldValue  number(18,2);--单元格值
var_sqlString    varchar2(600);--SQLY字符串
var_fvaluetype  varchar2(600);
--项目信息
cursor cur_projects is
select t4.fid          as orgid,
t4.fnumber      as orgno,
t4.fname_l2      as orgname,
t3.fnumber      as schemecode,
t3.fname_l2      as schemename,
t3.fhistablename as tablename,
t2.fnumber      as pprojectcode,
t2.fname_l2      as pprojectname,
t1.fnumber      as projectcode,
t1.fname_l2      as projectname
from hr.T_HR_CMPSCHEMEITEM t
left join hr.T_HR_CMPITEM t1
on t.FCMPITEMID = t1.FID
LEFT JOIN hr.T_HR_CMPITEMCAT t2
on t1.FCMPITEMCATALOGID = t2.fid
inner join hr.T_HR_CMPSCHEME t3
ON t.FCMPSCHEMEID = T3.FID
inner join hr.T_ORG_Admin t4
on t4.fid = t3.fadminorgid
WHERE t4.fnumber=in_orgNo
and  t2.fnumber not in('FPER','FCHK') and t3.fenable='1'
and  t3.faudit in ('10','30','40') and t3.fhistablename is not null
and  t1.FDataType in ('40','50','60');
--实体机构
cursor cur_suborg is
select ad.fid,ad.fname_l2,ad.fnumber from hr.t_org_admin ad where ad.fparentid=var_orgid;
--查询日期
cursor cur_date  is
select hc.fid from hr.t_hr_cmpperiod hc where hc.fnumber=in_Year||in_Month;
--用户信息
cursor cur_person is
select bp.fid
from hr.t_bd_person bp
left outer join hr.T_ORG_PositionMember op
on bp.fid = op.fpersonid
left outer join hr.T_HR_PersonPosition hp
ON bp.FID = hp.FPersonID
left outer join hr.T_ORG_Position top
ON op.FPositionID = top.fid
LEFT OUTER JOIN hr.T_ORG_Admin oa
ON top.FAdminOrgUnitID = oa.FID
where oa.fparentid = in_orgId;
begin
open cur_projects;
fetch cur_projects into
var_orgid,var_orgno,var_orgname,var_schemecode,var_schemename,var_tablename,
var_pprojectcode,var_pprojectname,var_projectcode,var_projectname;
while cur_projects%found loop
--DBMS_OUTPUT.PUT_LINE(var_orgno);
open cur_suborg;
fetch cur_su
borg into  var_subfid,var_suborgname,var_suborgno;
while cur_suborg%found loop
open cur_date;
fetch cur_date into  var_dateid;
while cur_date%found loop
open cur_person;
fetch cur_person into  var_personid;
while cur_person%found loop
BEGIN
/**初始化指标值*/
var_FieldValue:=0;
var_sqlString :='';
var_sqlString :='select '|| var_projectcode ||' from '|| var_tablename ||' WHERE fadminorgunitid='''||var_subfid ||'''and FPERIOD='''||var_dateid ||'''and fpersonid='''||var_personid ||'''';
--DBMS_OUTPUT.PUT_LINE(var_tablename);
BEGIN
EXECUTE IMMEDIATE var_sqlString  into var_FieldValue;
EXCEPTION
WHEN NO_DATA_FOUND THEN
var_FieldValue := 0;
END;
--DBMS_OUTPUT.PUT_LINE('到这里..');
insert into temp_bi_nr_report_01_01
(
personid,
byear,
bmonth,
account,
orgno,
orgname,
suborgno,
suborgname,
schemecode,
schemename,
pprojectcode,
pprojectname,
tablename,
projectcode,
projectname,
fieldvalue,
SQLtsring
)
values(
var_personid,
in_Year,
in_Month,
in_Year||in_Month,
var_orgno,
var_orgname,
var_suborgno,
var_suborgname,
var_schemecode,
var_schemename,
var_pprojectcode,
var_pprojectname,
var_tablename,
var_projectcode,
var_projectname,
var_FieldValue,
var_sqlString
);
END;
fetch cur_person into var_personid;
end loop;
close cur_person;
fetch cur_date into var_dateid;
end loop;
close cur_date;
fetch cur_suborg into var_subfid,var_suborgname,var_suborgno;
end loop;
close cur_suborg;
fetch cur_projects into
var_orgid,var_orgno,var_orgname,var_schemecode,var_schemename,var_tablename,
var_pprojectcode,var_pprojectname,var_projectcode,var_projectname;
end loop;
close cur_projects;
end SP_BI_NR_REPORT_01_01;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。