一.包说明
CREATE OR REPLACE PACKAGE package_assay_report_year authid current_user
is
function f_overrideSeq
return varchar2;
function f_assay_report_yearNo
return varchar2;
--title
procedure pro_assay_report_year_title(
ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor);
--综述
procedure PRO_ASSAY_REPORT_YEAR_VIEW(ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor);
--评级
procedure pro_assay_report_year_rank(
ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor);
--分析项目
procedure pro_assay_report_year_assay(
ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor);
end package_assay_report_year;
二,包体
CREATE OR REPLACE PACKAGE body package_assay_report_year
is
–重载seq
function f_overrideSeq
return varchar2
is
PRAGMA AUTONOMOUS_TRANSACTION;
varSqlA varchar2(1000);
varSqlB varchar2(1000);
year_date varchar2(20);
begin
varSqlA:= ‘drop sequence seq_assay_report_yearNo’;
execute immediate varSqlA;
varSqlB:=‘create sequence seq_assay_report_yearNo minvalue 1 maxvalue 9999999999999999 start with 1 increment by 1 cache 20’;
execute immediate varSqlB;
commit;
select seq_assay_report_yearNo.nextval into year_date
from dual;
return year_date;
end f_overrideSeq;
–年度报告编号
function f_assay_report_yearNo
return varchar2
is
year_date varchar2(20);
year_date_s varchar2(20);
year_no varchar2(20);
begin
select to_char(to_date(max(t.edit_date),‘yyyy-mm-dd HH24:mi:ss’),‘yyyy-mm-dd’) into year_date from ht_doc_file t where t.doc_type=‘船舶年度体检报告’;
select to_char(sysdate, ‘yyyy-mm-dd’) into year_date_s from dual;
case when year_date<>year_date_s
then
select to_char(sysdate, ‘yyyy-mm-dd’) || ‘-’ || lpad(PACKAGE_ASSAY_REPORT_YEAR.f_overrideSeq,2,‘0’) into year_no
from dual;
else
select to_char(sysdate, ‘yyyy-mm-dd’) || ‘-’ || lpad(seq_assay_report_yearNo.nextval,2,‘0’) into year_no
from dual;
end case;
return year_no;
end f_assay_report_yearNo;
–titile
procedure pro_assay_report_year_title(
ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor)
is
begin
OPEN cur_out FOR
select distinct t.locus_desc,
t.shipown_desc,
t.ship_code,
t.ship_zhdesc,
–T.assayplan_sampletime as sample_date,
b.ship_endesc||’ Ship’ as ship_endesc,
to_char(sysdate,‘yyyy-MM-dd’) as dateNow,
(case when t.locus_desc=‘主机’
then
‘主机运行状态评级 Main Engine Status Rank:
主机分析报告 Analysis Report of main engine
主机细项报告 Analysis Item Report of main engine’
when t.locus_desc2=‘No.1副机’
then
‘1#辅机运行状态评级 Auxy.1 Status Rank:
1#辅机分析报告 Analysis Report of Auxy.1
1#辅机细项报告 Analysis Item Report of of Auxy.1’
when t.locus_desc2=‘No.2副机’
then
‘2#辅机运行状态评级 Auxy.2 Status Rank:
2#辅机分析报告 Analysis Report of Auxy.2
2#辅机细项报告 Analysis Item Report of of Auxy.2’
when t.locus_desc2=‘No.3副机’
then
‘3#辅机运行状态评级 Auxy.3 Status Rank:
3#辅机分析报告 Analysis Report of Auxy.3
3#辅机细项报告 Analysis Item Report of of Auxy.3’
end) text,
(case when t.locus_desc=‘主机’
then 1
when t.locus_desc2=‘No.1副机’
then 2
when t.locus_desc2=‘No.2副机’
then 3
when t.locus_desc2=‘No.3副机’
then 4
end
) show_order
from view_assay_report_year_locus t left join ht_base_ship b
on t.ship_code=b.ship_code
where t.locus_desc in
(‘主机’,‘No.1副机’, ‘No.2副机’, ‘No.3副机’, ‘No.4副机’)
and t.Execute_Flag = 0
and t.approve_flag = 0
and t.ship_code=ship_code_
and t.assayplan_sampletime between start_date_ and end_date_
order by show_order;
end pro_assay_report_year_title;
–年度综述
procedure PRO_ASSAY_REPORT_YEAR_VIEW(ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor)
is
begin
OPEN cur_out FOR
select
(select PACKAGE_ASSAY_REPORT_YEAR.f_assay_report_yearNo from dual) as year_report_no
,t.ship_zhdesc,
t.ship_zhdesc||‘船舶润滑油分析结果综述’ as ship_zhdesc_t1,
‘Report Summary of ‘||t.ship_endesc||’ Ship’ as ship_endesc_t1,
t.ship_endesc,
(case when
length(LISTAGG(to_char(t.shipown_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime))
-length(replace(LISTAGG(to_char(t.shipown_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime),’,’,’’))>0
then
substr(
LISTAGG(to_char(t.shipown_desc), ‘,’) WITHIN GROUP(ORDER BY t.shipown_desc desc),0,
instr((LISTAGG(to_char(t.shipown_desc), ‘,’) WITHIN GROUP(ORDER BY t.shipown_desc desc)),’,’,1,1)-1
)
else
LISTAGG(to_char(t.shipown_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime)
end
) as shipown_desc
,
b.SHIP_IMONUMBER,
t.locus_desc,
t.ship_code,
(case when
length(LISTAGG(to_char(t.locus_desc2), ‘,’) WITHIN GROUP(ORDER BY t.locus_desc2))
-length(replace(LISTAGG(to_char(t.locus_desc2), ‘,’) WITHIN GROUP(ORDER BY t.locus_desc2),’,’,’’))>1
then
‘’
else
LISTAGG(to_char(t.locus_desc2), ‘,’) WITHIN GROUP(ORDER BY t.locus_desc2)
end
) as locus_desc2
,replace((case when
length(LISTAGG(to_char(t.assayplan_sampletime), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime))
-length(replace(LISTAGG(to_char(t.assayplan_sampletime), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime),’,’,’’))>3
and
instr((LISTAGG(to_char(t.assayplan_sampletime), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc)),’,’,1,4)<>0
then
substr(
LISTAGG(to_char(t.assayplan_sampletime), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc),0,
instr((LISTAGG(to_char(t.assayplan_sampletime), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc)),’,’,1,4)-1
)
else
LISTAGG(to_char(t.assayplan_sampletime), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime)
end
),’,’,chr(10)) as sample_date
,
replace((case when
length(LISTAGG(to_char(substr(t.audit_date,1,10)), ‘,’) WITHIN GROUP(ORDER BY t.audit_date))
-length(replace(LISTAGG(to_char(substr(t.audit_date,1,10)), ‘,’) WITHIN GROUP(ORDER BY t.audit_date),’,’,’’))>3
and
instr((LISTAGG(to_char(substr(t.audit_date,1,10)), ‘,’) WITHIN GROUP(ORDER BY t.audit_date desc)),’,’,1,4)<>0
then
substr(
LISTAGG(to_char(substr(t.audit_date,1,10)), ‘,’) WITHIN GROUP(ORDER BY t.audit_date desc),0,
instr((LISTAGG(to_char(substr(t.audit_date,1,10)), ‘,’) WITHIN GROUP(ORDER BY t.audit_date desc)),’,’,1,4)-1
)
else
LISTAGG(to_char(substr(t.audit_date,1,10)), ‘,’) WITHIN GROUP(ORDER BY t.audit_date)
end
),’,’,chr(10)) as reportdate
,
(case when
length(LISTAGG(to_char(t.materiel_status_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime))
-length(replace(LISTAGG(to_char(t.materiel_status_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime),’,’,’’))>0
then
substr(
LISTAGG(to_char(t.materiel_status_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc),0,
instr((LISTAGG(to_char(t.materiel_status_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc)),’,’,1,1)-1
)
else
LISTAGG(to_char(t.materiel_status_desc), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime)
end
) as materiel_status_desc
,
SF_SPLIT_ACCOUNT_ID_LIST(case when
length(LISTAGG(to_char(m.materiel_caption), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime))
-length(replace(LISTAGG(to_char(m.materiel_caption), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime),’,’,’’))>0
and instr((LISTAGG(to_char(m.materiel_caption), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc)),’,’,1,4)<>0
then
substr(
LISTAGG(to_char(m.materiel_caption), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc),0,
instr((LISTAGG(to_char(m.materiel_caption), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc)),’,’,1,4)-1
)
else
LISTAGG(to_char(m.materiel_caption), ',') WITHIN GROUP(ORDER BY t.assayplan_sampletime)
end
) as materiel_caption
,(case when t.locus_desc='主机'
then 1
when t.locus_desc='No.1副机'
then 2
when t.locus_desc='No.2副机'
then 3
when t.locus_desc='No.3副机'
then 4
end
) show_order
from view_assay_report_year_locus t
left join ht_base_ship b
on t.ship_code = b.ship_code
left join ht_base_materiel m
on t.materiel_code=m.materiel_code
where
t.locus_desc in
(‘主机’,‘No.1副机’, ‘No.2副机’, ‘No.3副机’, ‘No.4副机’)
and t.ship_code=ship_code_
and t.assayplan_sampletime between start_date_ and end_date_
and t.Execute_Flag = 0
and t.approve_flag = 0
group by
t.ship_code,
t.ship_zhdesc,
t.ship_endesc,
b.SHIP_IMONUMBER,
t.locus_desc;
end pro_assay_report_year_view;
–评级
procedure pro_assay_report_year_rank(
ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor)
is
begin
OPEN cur_out FOR
select tt.“SHIP_CODE”,
tt.“LOCUS_DESC”,
tt.locus_desc2,
tt.“ASSAY_ORDER”,
td.assayplan_sampletime as sample_date,
td.materiel_status_desc,
l.assayitem_desc,
l.assayitem_code,
l.assayitemgroup_code,
(
case l.assayitem_code
when ‘FX1010’
then ‘磨损元素铁/Fe’
when ‘FX1011’
then ‘其他磨损元素/Cu,Pb,Cr,Sn,Al’
when ‘FX1012’
then ‘其他磨损元素/Cu,Pb,Cr,Sn,Al’
when ‘FX1013’
then ‘其他磨损元素/Cu,Pb,Cr,Sn,Al’
when ‘FX1014’
then ‘其他磨损元素/Cu,Pb,Cr,Sn,Al’
when ‘FX1017’
then ‘其他磨损元素/Cu,Pb,Cr,Sn,Al’
when ‘FX1027’
then ‘润滑油添加剂/ZN,P’
when ‘FX1028’
then ‘润滑油添加剂/ZN,P’
when ‘FX1015’
then ‘燃料/Si,Al,V,Mn’
when ‘FX1017’
then ‘燃料/Si,Al,V,Mn’
when ‘FX1020’
then ‘燃料/Si,Al,V,Mn’
when ‘FX1024’
then ‘燃料/Si,Al,V,Mn’
else
l.assayitemgroup_desc
end
) as assayitemgroup_desc,
(
case tt.locus_desc
when ‘主机’
then ‘运行状态评级’||’ ‘||‘Main Engine Status Rank:’
when ‘No.1副机’
then ‘运行状态评级’||’ ‘||‘AE NO.1 Status Rank:’
when ‘No.2副机’
then ‘运行状态评级’||’ ‘||‘AE NO.2 Status Rank:’
when ‘No.3副机’
then ‘运行状态评级’||’ '||‘AE NO.3 Status Rank:’
end
) as statusrank,
l.assay_assayvalue,
(
case l.assayitem_code
when 'FX1001'
then 0
when 'FX1002'
then 0
when 'FX1007'
then 1
when 'FX1008'
then 1
when 'FX1004'
then 2
when 'FX1005'
then 2
when 'FX1006'
then 2
when 'FX1003'
then 3
when 'FX1009'
then 4
when 'FX1010'
then 5
when 'FX1011'
then 6
when 'FX1012'
then 6
when 'FX1013'
then 6
when 'FX1014'
then 6
when 'FX1017'
then 6
when 'FX1027'
then 7
when 'FX1028'
then 7
when 'FX1015'
then 8
when 'FX1017'
then 8
when 'FX1020'
then 8
when 'FX1024'
then 8
else
0
end
) as show_order,
td.ship_zhdesc,
td.ship_endesc,
(case when tt.locus_desc='主机'
then td.ship_zhdesc||tt.locus_desc||'综合结论General Results and Recommendations for main engine：'
when tt.locus_desc2='No.1副机'
then td.ship_zhdesc||tt.locus_desc2||'综合结论General Results and Recommendations for Auxy.1：'
when tt.locus_desc2='No.2副机'
then td.ship_zhdesc||tt.locus_desc2||'综合结论General Results and Recommendations for Auxy.2：'
when tt.locus_desc2='No.3副机'
then td.ship_zhdesc||tt.locus_desc2||'综合结论General Results and Recommendations for Auxy.3：'
end
) as result_title
,td.memo_text
,
(case td.materiel_status_code
when '1'
then
'true'
else
'false'
end) as Safe,
(case td.materiel_status_code
when '2'
then
'true'
else
'false'
end) as Warning,
(case td.materiel_status_code
when '3'
then
'true'
else
'false'
end) as Danger
,'标准/Standard' as Stand
,td.memo_text3
,(case when tt.locus_desc='主机'
then td.ship_zhdesc||tt.locus_desc||'初步结论General Results of main engine'
when tt.locus_desc2='No.1副机'
then td.ship_zhdesc||tt.locus_desc2||'初步结论General Results of Auxy.1'
when tt.locus_desc2='No.2副机'
then td.ship_zhdesc||tt.locus_desc2||'初步结论General Results of Auxy.2'
when tt.locus_desc2='No.3副机'
then td.ship_zhdesc||tt.locus_desc2||'初步结论General Results of Auxy.3'
end
) as result_final
from
(select t.ship_code,
t.locus_desc,
t.locus_desc2,
(case when
length(LISTAGG(to_char(t.assay_order), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime))
-length(replace(LISTAGG(to_char(t.assay_order), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime),’,’,’’))>0
then
substr(
LISTAGG(to_char(t.assay_order), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc),0,
instr((LISTAGG(to_char(t.assay_order), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime desc)),’,’,1,1)-1
)
else
LISTAGG(to_char(t.assay_order), ‘,’) WITHIN GROUP(ORDER BY t.assayplan_sampletime)
end
) as assay_order
from view_assay_report_year_locus t
where t.Execute_Flag = 0
and t.approve_flag = 0
and t.locus_desc in
(‘主机’,‘No.1副机’, ‘No.2副机’, ‘No.3副机’, ‘No.4副机’)
and t.locus_desc=locus_desc_
and t.ship_code=ship_code_
and t.assayplan_sampletime between start_date_ and end_date_
group by t.ship_code,t.locus_desc,t.locus_desc2) tt,ht_assay_orderhead td,view_assay_report_year_orderline l,ht_base_assayitemgroup g
where
tt.assay_order=td.assay_order and tt.assay_order=l.assay_order and l.assayitemgroup_code=g.assayitemgroup_code
order by show_order asc;
end pro_assay_report_year_rank;
–分析项目
procedure pro_assay_report_year_assay(
ship_code_ in varchar2,
locus_desc_ in varchar2,
start_date_ in varchar2,
end_date_ in varchar2,
cur_out out sys_refcursor)
is
begin
OPEN cur_out FOR
select td.assay_order,
td.locus_desc||‘分析报告 Analysis Report of main engine’ as title,
(
case when td.locus_desc=‘主机’
then ‘主机’||‘分析报告 Analysis Report of main engine’
when td.locus_desc2=‘No.1副机’
then ‘No.1副机’||‘分析报告 Analysis Report of Auxy.1’
when td.locus_desc2=‘No.2副机’
then ‘No.2副机’||‘分析报告 Analysis Report of Auxy.2’
when td.locus_desc2=‘No.3副机’
then ‘No.3副机’||‘分析报告 Analysis Report of Auxy.3’
end
) as title1,
td.materiel_status_desc,
td.run_hours,
l.assayitem_desc,
(case l.assayitemgroup_desc
when '碱值'
then
'酸碱值'
when '酸值'
then '酸碱值'
else
l.assayitemgroup_desc
end
) as assayitemgroup_desc,
l.assay_assayvalue,
replace(replace(l.assay_assayvalue,'<',''),'>','') as assay_assayvalue1,
b.show_order,
td.ship_zhdesc,
td.ship_endesc,
td.assayplan_sampletime,
(case when td.locus_desc='主机'
then '主机'
when td.locus_desc2='No.1副机'
then 'No.1副机'
when td.locus_desc2='No.2副机'
then 'No.2副机'
when td.locus_desc2='No.3副机'
then 'No.3副机'
end
) as locus_desc,
(case when td.locus_desc='主机'
then '主机细项报告/Analysis Item Report of main engine：'
when td.locus_desc2='No.1副机'
then '1#辅机细项报告/Analysis Item Report of Auxy.1：'
when td.locus_desc2='No.2副机'
then '2#辅机细项报告/Analysis Item Report of Auxy.2：'
when td.locus_desc2='No.3副机'
then '3#辅机细项报告/Analysis Item Report of Auxy.3：'
end
) as result_title,
td.memo_text4
from
ht_assay_orderhead td,ht_assay_orderline l,ht_base_assayitemgroup g,ht_base_assayitem b
where
td.assay_order=l.assay_order
and l.assayitemgroup_code=g.assayitemgroup_code
and l.assayitem_code=b.assayitem_code
and td.assay_order in
(
select * from (select t.assay_order from view_assay_report_year_locus t
where t.Execute_Flag = 0
and t.approve_flag = 0
and t.locus_desc=locus_desc_
and t.ship_code=ship_code_
and t.assayplan_sampletime between start_date_ and end_date_
order by t.assayplan_sampletime desc) where rownum<5
)
order by td.assayplan_sampletime,b.show_order desc
;
end pro_assay_report_year_assay;
end package_assay_report_year;
1252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
