USE [edison_prc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Load_alert_Rpt] (@loadtype varchar(50),@officecode char(10),@consigneestr varchar(300),@movement varchar(10),@containertypelist varchar(500),@day1 int,@day2 int)
AS
BEGIN
declare @database_name varchar(100),@sql varchar(6000),@current_db varchar(100),@p int,@q int,@tm_len int,@etd_sql varchar(200),
@movement_sql varchar(100),@rpt_sql varchar(2000),@cbm_sql varchar(1000),@min_cbm varchar(10),@max_kgs varchar(10),@typelist varchar(500)
create table #t_Loadalert_report ([ID] [int] IDENTITY(1,1) NOT NULL,officecode varchar(10),sono varchar(20),
so_seq int,po_no varchar(50),ref_no varchar(50),[Item Reference] varchar(100),shippername char(50),
actshippername char(50),bookingdate varchar(19),cargoreadydate varchar(10),CY_ClosingDate varchar(10),lportname char(50),
dportname char(50),Movement char(7),linercode char(10),Vesselname varchar(50),Voyage char(20),etd varchar(10),
eta varchar(10),QTY int,unitcode char(2),CBM decimal(12,3),KGS decimal(18,2),
[No.of 20GP] int,[No.of 20HC] int,[No.of 40GP] int,[No.of 40HC] int,
CONSTRAINT [PK_t_Loadalert_report] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
create table #consignee(companycode varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS )
select @p=1,@q=1,@tm_len = len(ltrim(rtrim(@consigneestr)))
while @p<=@tm_len
begin
if substring(@consigneestr,@p,1)='/'
begin
insert into #consignee values(substring(@consigneestr,@q,@p - @q))
select @q = @p + 1
end
select @p = @p + 1
end
if @tm_len>=6
begin
insert into #consignee values(substring(@consigneestr,@q ,@p - @q))
end
if(charindex('/',@containertypelist)=0)
select @typelist='[NO.of '+@containertypelist+']'
else
begin
select @typelist=',[NO.of '+substring(@containertypelist,1,charindex('/',@containertypelist)-1)+']'
select @containertypelist=substring(@containertypelist,charindex('/',@containertypelist)+1,len(@containertypelist)-charindex('/',@containertypelist))
while charindex('/',@containertypelist)>0
begin
select @typelist=@typelist+',[NO.of '+substring(@containertypelist,1,charindex('/',@containertypelist)-1)+']'
select @containertypelist=substring(@containertypelist,charindex('/',@containertypelist)+1,len(@containertypelist)-charindex('/',@containertypelist))
end
select @typelist=@typelist+(',[NO.of '+@containertypelist+']')
select @typelist=substring(@typelist,2,len(@typelist))
end
SET @rpt_sql = 'select [ID],officecode,sono,so_seq,po_no,ref_no,[Item Reference],shippername,actshippername,
bookingdate,cargoreadydate,CY_ClosingDate,lportname,dportname,Movement,linercode,Vesselname,Voyage,etd,eta,
QTY,unitcode,CBM,KGS,'+@typelist+'
from #t_Loadalert_report'
if @movement is null
select @movement_sql=''
else
begin
if @movement='CY/CY'
select @movement_sql=' and a.movement=''CY/CY'''
else if @movement='CFS/CY'
select @movement_sql=' and a.movement=''CFS/CY'''
else
select @movement_sql=' '
end
if @day1 is null and @day2 is null
select @etd_sql=' '
else
begin
if @day1 is null
select @etd_sql=' and a.createdate<=convert(char(10),getdate() + '+convert(varchar(3),@day2)+',121)'
else
if @day2 is null
select @etd_sql=' and a.createdate>=convert(char(10),getdate() + '+convert(varchar(3),@day1)+',121)'
else
select @etd_sql=' and a.createdate>=convert(char(10),getdate() + '+convert(varchar(3),@day1)+',121) and a.createdate<=convert(char(10),getdate()+ '+convert(varchar(3),@day2)+',121)'
end
if @loadtype='MIN_CBM'
begin
if @movement='CY/CY'
begin
select @max_kgs='si'
select @cbm_sql='join (select d.sono,d.so_seq,d.seq from
(select s.sono,s.so_seq,min(si.itemseq) seq,sum(si.cbm) si_cbm
from so_header s,so_item si
where s.sono=si.sono and s.so_seq=si.so_seq
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) d,
(select s.sono,s.so_seq,sum(bc.qty*cast(isnull(spec_char,''0'') as int)) bc_cbm
from so_header s,bookingcontainer bc,edispecialchecking ec
where s.sono=bc.bookingno and s.so_seq=bc.bookingseq and ec.checktype=''SO_CNTR_TYPE''
and ec.checkvalue=s.consigneecode and bc.containertype=ec.checkvalue_2
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) b
where d.sono=b.sono and d.so_seq=b.so_seq
and d.si_cbm<b.bc_cbm) c on a.sono=c.sono and a.so_seq=c.so_seq'
end
else
begin
select @max_kgs='ld'
select @cbm_sql='join lp_detail ld (nolock) on a.sono=ld.sono and a.so_seq=ld.so_seq
join (select d.sono,d.so_seq,d.seq from
(select s.sono,s.so_seq,min(itemseq) seq,sum(ld.cbm) ld_cbm
from so_header s,lp_detail ld
where s.sono=ld.sono and s.so_seq=ld.so_seq
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) d,
(select lh.lp_no,bs.max_cbm bs_cbm
from lp_header lh,base bs,edispecialchecking ec
where lh.containertype=bs.basecode and ec.checktype=''SO_CNTR_TYPE''
and ec.checkvalue=lh.consigneecode and bs.basecode=ec.checkvalue_2
and lh.consigneecode in (select companycode from #consignee)) b
where d.ld_cbm<b.bs_cbm) c on a.sono=c.sono and a.so_seq=c.so_seq'
end
end
else if @loadtype='MAX_KGS'
begin
if @movement='CY/CY'
begin
select @max_kgs='si'
select @cbm_sql='join (select d.sono,d.so_seq,d.seq from
(select s.sono,s.so_seq,min(si.itemseq) seq,sum(si.kgs) si_kgs
from so_header s,so_item si
where s.sono=si.sono and s.so_seq=si.so_seq
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) d,
(select s.sono,s.so_seq,sum(bc.qty*cast(isnull(spec_char2,''0'') as int)) bc_kgs
from so_header s,bookingcontainer bc,edispecialchecking ec
where s.sono=bc.bookingno and s.so_seq=bc.bookingseq and ec.checktype=''SO_CNTR_TYPE''
and ec.checkvalue=s.consigneecode and bc.containertype=ec.checkvalue_2
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) b
where d.sono=b.sono and d.so_seq=b.so_seq
and d.si_kgs>b.bc_kgs) c on a.sono=c.sono and a.so_seq=c.so_seq'
end
else
begin
select @max_kgs='ld'
select @cbm_sql='join lp_detail ld (nolock) on a.sono=ld.sono and a.so_seq=ld.so_seq
join (select d.sono,d.so_seq,d.seq from
(select s.sono,s.so_seq,min(itemseq) seq,sum(ld.kgs) ld_kgs
from so_header s,lp_detail ld
where s.sono=ld.sono and s.so_seq=ld.so_seq
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) d,
(select lh.lp_no,bs.max_kgs bs_kgs
from lp_header lh,base bs,edispecialchecking ec
where lh.containertype=bs.basecode and ec.checktype=''SO_CNTR_TYPE''
and ec.checkvalue=lh.consigneecode and bs.basecode=ec.checkvalue_2
and lh.consigneecode in (select companycode from #consignee)) b
where d.ld_kgs<b.bs_kgs) c on a.sono=c.sono and a.so_seq=c.so_seq'
end
end
else
begin
select @max_kgs='si'
select @cbm_sql='join (select d.sono,d.so_seq,d.seq from
(select s.sono,s.so_seq,min(si.itemseq) seq,sum(si.cbm) si_cbm
from so_header s,so_item si
where s.sono=si.sono and s.so_seq=si.so_seq
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) d,
(select s.sono,s.so_seq,sum(bc.qty*cast(isnull(spec_char,''0'') as int)) bc_cbm
from so_header s,bookingcontainer bc,edispecialchecking ec
where s.sono=bc.bookingno and s.so_seq=bc.bookingseq and ec.checktype=''SO_CNTR_TYPE_2''
and ec.checkvalue=s.consigneecode and bc.containertype=ec.checkvalue_2
and s.consigneecode in (select companycode from #consignee)
group by s.sono,s.so_seq) b
where d.sono=b.sono and d.so_seq=b.so_seq
and d.si_cbm=b.bc_cbm) c on a.sono=c.sono and a.so_seq=c.so_seq'
end
set @current_db = db_name()
if @officecode=''
declare database_cur cursor for
select db_name,officecode from systeminfo where linerflag<>'Y' and linercode='nvocc'
and status='Y' and officecode not in ('ASH','SGP','STO')
and db_name not like '%test%' and db_name not like '%imp%'
else
declare database_cur cursor for
select db_name,officecode from systeminfo where linerflag<>'Y' and linercode='nvocc'
and status='Y' and officecode=@officecode
and db_name not like '%test%' and db_name not like '%imp%'
--select db_name,officecode from systeminfo where db_name='edison_prc_hkg_nvo_test2'
open database_cur
fetch database_cur into @database_name,@officecode
while @@fetch_status=0 begin
set @sql='insert into #t_Loadalert_report' + ' select ''' + @officecode + ''',a.sono,
a.so_seq,
si.po_no,
si.ref_no,
(case when (select itemremarks from companyvalidpo (nolock) where si.po_no=companyvalidpo.pono and si.ref_no=companyvalidpo.itemno
and si.shipmentno=companyvalidpo.shipmentno and a.consigneecode=companyvalidpo.companycode and si.destcode=companyvalidpo.destcode
and companyvalidpo.companytype =''C'')='''' then '''' else (select itemremarks from companyvalidpo (nolock) where si.po_no=companyvalidpo.pono and si.ref_no=companyvalidpo.itemno
and si.shipmentno=companyvalidpo.shipmentno and a.consigneecode=companyvalidpo.companycode and si.destcode=companyvalidpo.destcode
and companyvalidpo.companytype =''C'') end) as [Item Reference],
company.companyname,
company_c.companyname,
convert(varchar(19),a.bookingdate,120),
convert(varchar(10),a.cargoreadydate,120),
convert(varchar(10),vv.CY_ClosingDate,120),
a.lportname,
a.dportname,
a.Movement,
vv.linercode,
ve.Vesselname,
vv.Voyage,
convert(varchar(10),vv.etd_date,120),
convert(varchar(10),vv.eta_datedport,120),
'+@max_kgs+'.QTY,
'+@max_kgs+'.unitcode,
'+@max_kgs+'.CBM,
'+@max_kgs+'.KGS,
(select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''20GP'') as [No.of20GP],
(select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''20HC'') as [No.of20HC],
(select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''40GP'') as [No.of40GP],
(select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''40HC'') as [No.of40HC]
from so_header a
join so_item si (nolock) on
a.sono=si.sono and a.so_seq=si.so_seq
join vesvoyforwarder vv (nolock) on vv.vesvoyno=a.vesvoyno
join vessel ve (nolock) on ve.vesselcode=vv.vesselcode
join companyheader company (nolock) on a.shippercode=company.companycode
join companyheader company_c (nolock) on a.act_shippercode=company_c.companycode
'+@cbm_sql+' where a.consigneecode in (select companycode from #consignee) '+ @etd_sql + @movement_sql
exec( 'USE ' + @database_name + ' ' + @sql)
fetch database_cur into @database_name ,@officecode
end
close database_cur
deallocate database_cur
Exec(@rpt_sql)
drop table #t_Loadalert_report
drop table #consignee
end
GO
