USE [ACCOUNTINGDB]
GO
/****** Object: StoredProcedure [dbo].[spvw_SummFreight_ShippingCharge] Script Date: 05/28/2014 08:18:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- Create store procedure
-- By Kusdi Mei-2014
CREATE PROCEDURE [dbo].[spvw_SummFreight_ShippingCharge]
(
@month int,
@year int
)
AS
--deklarasi variable
declare
@FRD_FRDCode int,
@FI_CurrencyCode int,
@air_import numeric(18,2),
@air_export numeric(18,2),
@sea_import numeric(18,2),
@sea_export numeric(18,2)
--set @month=5
--set @year=2014
begin
create table #Temp_FreightInvoice(
no_urut int,
forwadercode int,
CurrencyCode int,
air_import numeric(18,2) null,
air_export numeric(18,2) null,
sea_import numeric(18,2) null,
sea_export numeric(18,2) null)
--select * from #Temp_FreightInvoice
--insert from select
insert into #Temp_FreightInvoice
(forwadercode,CurrencyCode)
select FRD_FRDCode,FI_CurrencyCode
from accountingdb..TBTI_FreightInvoice
inner join accountingdb..vw_mst_currency on FI_CurrencyCode = cast(CRC_CurrencyCode as int)
inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode
where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year
group by FRD_FRDCode,FI_CurrencyCode
--buat deklarasi cursor
declare FreightInvoice cursor fast_forward for
select FRD_FRDCode,FI_CurrencyCode
from accountingdb..TBTI_FreightInvoice
inner join accountingdb..vw_mst_currency on FI_CurrencyCode = cast(CRC_CurrencyCode as int)
inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode
where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year
group by FRD_FRDCode,FI_CurrencyCode
Open FreightInvoice
Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode
while @@Fetch_status = 0
begin
--summ air - import
select @air_import= sum(FI_Amount)
from accountingdb..TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year
and FI_ShippingMode='Air - Import' and FI_ForwarderCode=@FRD_FRDCode
and FI_CurrencyCode=@FI_CurrencyCode
--summ Air - Export
select @air_export= sum(FI_Amount)
from TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year
and FI_ShippingMode='Air - Export' and FI_ForwarderCode=@FRD_FRDCode
and FI_CurrencyCode=@FI_CurrencyCode
--summ Sea - Import
select @sea_import= sum(FI_Amount)
from TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year
and FI_ShippingMode='Sea - Import' and FI_ForwarderCode=@FRD_FRDCode
and FI_CurrencyCode=@FI_CurrencyCode
--summ Sea - Import
select @sea_export= sum(FI_Amount)
from TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year
and FI_ShippingMode='Sea - Export' and FI_ForwarderCode=@FRD_FRDCode
and FI_CurrencyCode=@FI_CurrencyCode
--update temp tabel
update #Temp_FreightInvoice
set
air_import=@air_import,
air_export=@air_export,
sea_import=@sea_import,
sea_export=@sea_export
Where forwadercode =@FRD_FRDCode and CurrencyCode =@FI_CurrencyCode
Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode
end
Close FreightInvoice
Deallocate FreightInvoice
end
--select data temporary
select FRD_FRDName, CRC_CurrencyName, air_import, air_export ,sea_import , sea_export
from #Temp_FreightInvoice
inner join accountingdb..vw_mst_currency on CurrencyCode = cast(CRC_CurrencyCode as int)
inner join PPSDB..TB_MST_Forwarders on forwadercode=FRD_FRDCode
--drop temporary table
Drop table #Temp_FreightInvoice
