Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @username | varchar | 50 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_loadbc27pasi
(
@username varchar(50)
)
AS
BEGIN
declare @temp_data_inserted table(
id_bc271_hdr bigint, id_bc271_hdr_pasi bigint
)
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
insert into [dbo].[tbl_header_bc27_out] (
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,[PasokNama]
,[PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
,[id_bc271_hdr_pasi]
,[create_by]
,[update_by]
,[create_date]
,[update_date]
,[status]
) output inserted.id_bc271_hdr, inserted.id_bc271_hdr_pasi into @temp_data_inserted
select
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,[PasokNama]
,[PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
,[id_bc271_hdr]
, @username
, @username
,GETDATE()
,GETDATE()
,'New'
from (
select
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,'PASI-AW' as [PasokNama]
,'JL.RAYA SERANG KM. 24 BALARAJA TANGERANG' as [PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
,[id_bc271_hdr]
from AW.DB_PASI_20201106.dbo.tbl_header_bc27_out where (pasoknama like '%PEMI%' or pasoknama like '%EDS%') and ([Nodaf] is not null or [Nodaf] <> '')
-- from [VENUS].AW.DB_PASI_20201106.dbo.tbl_header_bc27_out where pasoknama IN ('PT EDS MANUFACTURING INDONESIA', 'PEMI') and ([Nodaf] is not null or [Nodaf] <> '')
except
SELECT
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,'PASI-AW' as [PasokNama]
,'JL.RAYA SERANG KM. 24 BALARAJA TANGERANG' as [PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
, id_bc271_hdr_pasi
FROM [dbo].[tbl_header_bc27_out]
) as xx
if (select count(*) from @temp_data_inserted) = 0
begin
select '00' as status, 'DATA SUDAH TERPROSES SEMUA ATAU BELUM ADA DATA BARU' as description
return
end
if (select count(*) from @temp_data_inserted) > 0
begin
declare @id_bc271_hdr bigint, @id_bc271_hdr_pasi bigint
DECLARE db_cursor CURSOR FOR
select id_bc271_hdr, id_bc271_hdr_pasi from @temp_data_inserted
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id_bc271_hdr, @id_bc271_hdr_pasi
WHILE @@FETCH_STATUS = 0
BEGIN
insert into tbl_detail_bc27_out (
[id_bc271_hdr]
,[CAR]
,[NoHS]
,[BrgUrai]
,[Merk]
,[Tipe]
,[SpfLain]
,[KdBrg]
,[KdSat]
,[JmlSat]
,[NettoDtl]
,[DSerah]
,[DVol]
,[NoUrut]
,NoInvoice
,TgInvoice
,[part_name_customs]
,[KdBrg1]
)
select
@id_bc271_hdr
,a.[CAR]
,(case when a.[BrgUrai] like '%VO%' or a.[BrgUrai] like '%CO%' then '3917329000' else '8544492100' end) as partnamecustom
,a.[BrgUrai]
,a.[Merk]
,a.[Tipe]
,a.[SpfLain]
,a.[KdBrg]
,a.[KdSat]
,a.[JmlSat]
,a.[NettoDtl]
,a.[DSerah]
,a.[DVol]
,a.[NoUrut]
,a.NoInvoice
,a.TgInvoice
,(case when a.[BrgUrai] like '%VO%' or a.[BrgUrai] like '%CO%' then 'TUBE-M' else 'WIRE' end) as partnamecustom
,(case when a.[BrgUrai] like '%VO%' or a.[BrgUrai] like '%CO%' then 'I21' else 'I01' end) as kdbrg
FROM AW.DB_PASI_20201106.dbo.tbl_detail_bc27_out a where a.id_bc271_hdr = @id_bc271_hdr_pasi
FETCH NEXT FROM db_cursor INTO @id_bc271_hdr, @id_bc271_hdr_pasi
END
CLOSE db_cursor
DEALLOCATE db_cursor
end
select '00' as status, 'DATA SUKSES DIPORSES' as description
END
GO
GO
CREATE PROCEDURE dbo.sp_loadbc27pasi
(
@username varchar(50)
)
AS
BEGIN
declare @temp_data_inserted table(
id_bc271_hdr bigint, id_bc271_hdr_pasi bigint
)
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
insert into [dbo].[tbl_header_bc27_out] (
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,[PasokNama]
,[PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
,[id_bc271_hdr_pasi]
,[create_by]
,[update_by]
,[create_date]
,[update_date]
,[status]
) output inserted.id_bc271_hdr, inserted.id_bc271_hdr_pasi into @temp_data_inserted
select
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,[PasokNama]
,[PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
,[id_bc271_hdr]
, @username
, @username
,GETDATE()
,GETDATE()
,'New'
from (
select
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,'PASI-AW' as [PasokNama]
,'JL.RAYA SERANG KM. 24 BALARAJA TANGERANG' as [PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
,[id_bc271_hdr]
from AW.DB_PASI_20201106.dbo.tbl_header_bc27_out where (pasoknama like '%PEMI%' or pasoknama like '%EDS%') and ([Nodaf] is not null or [Nodaf] <> '')
-- from [VENUS].AW.DB_PASI_20201106.dbo.tbl_header_bc27_out where pasoknama IN ('PT EDS MANUFACTURING INDONESIA', 'PEMI') and ([Nodaf] is not null or [Nodaf] <> '')
except
SELECT
[CAR]
,[KdKpbc]
,[KdKpbcBongkar]
,[KdKpbcAwas]
,'PASI-AW' as [PasokNama]
,'JL.RAYA SERANG KM. 24 BALARAJA TANGERANG' as [PasokAlmt1]
,[Moda]
,[KdVal]
,[Cif]
,[Bruto]
,[Netto]
,[NilSerah]
,[Nodaf]
,[Tgldaf]
,[UsahaNama]
,[UsahaNPWP]
,[UsahaAlamat1]
,[UsahaAlamat2]
,[UsahaSkep]
,[PasokAlmt2]
,[PasokSkep]
,[PasokNPWP]
,[NoInvoice]
,[TgInvoice]
,[NoPackinglist]
,[TgPackinglist]
,[NoKontrak]
,[TgKontrak]
,[SuratJalan]
,[TgSuratjalan]
,[SuratKep]
,[TgSuratKep]
,[SuratLain]
,[TgSuratLain]
,[NoBc27Asal]
,[TgBc27Asal]
,[Nopol]
,[MrkNoKms]
,[JmlJnsKms]
,[NoSeg]
,[CtBcTuj1]
,[CtBcTuj2]
,[Volume]
,[Tgl_Keluar]
,[No_Keluar]
, id_bc271_hdr_pasi
FROM [dbo].[tbl_header_bc27_out]
) as xx
if (select count(*) from @temp_data_inserted) = 0
begin
select '00' as status, 'DATA SUDAH TERPROSES SEMUA ATAU BELUM ADA DATA BARU' as description
return
end
if (select count(*) from @temp_data_inserted) > 0
begin
declare @id_bc271_hdr bigint, @id_bc271_hdr_pasi bigint
DECLARE db_cursor CURSOR FOR
select id_bc271_hdr, id_bc271_hdr_pasi from @temp_data_inserted
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id_bc271_hdr, @id_bc271_hdr_pasi
WHILE @@FETCH_STATUS = 0
BEGIN
insert into tbl_detail_bc27_out (
[id_bc271_hdr]
,[CAR]
,[NoHS]
,[BrgUrai]
,[Merk]
,[Tipe]
,[SpfLain]
,[KdBrg]
,[KdSat]
,[JmlSat]
,[NettoDtl]
,[DSerah]
,[DVol]
,[NoUrut]
,NoInvoice
,TgInvoice
,[part_name_customs]
,[KdBrg1]
)
select
@id_bc271_hdr
,a.[CAR]
,(case when a.[BrgUrai] like '%VO%' or a.[BrgUrai] like '%CO%' then '3917329000' else '8544492100' end) as partnamecustom
,a.[BrgUrai]
,a.[Merk]
,a.[Tipe]
,a.[SpfLain]
,a.[KdBrg]
,a.[KdSat]
,a.[JmlSat]
,a.[NettoDtl]
,a.[DSerah]
,a.[DVol]
,a.[NoUrut]
,a.NoInvoice
,a.TgInvoice
,(case when a.[BrgUrai] like '%VO%' or a.[BrgUrai] like '%CO%' then 'TUBE-M' else 'WIRE' end) as partnamecustom
,(case when a.[BrgUrai] like '%VO%' or a.[BrgUrai] like '%CO%' then 'I21' else 'I01' end) as kdbrg
FROM AW.DB_PASI_20201106.dbo.tbl_detail_bc27_out a where a.id_bc271_hdr = @id_bc271_hdr_pasi
FETCH NEXT FROM db_cursor INTO @id_bc271_hdr, @id_bc271_hdr_pasi
END
CLOSE db_cursor
DEALLOCATE db_cursor
end
select '00' as status, 'DATA SUKSES DIPORSES' as description
END
GO
Depends On
2Used By
No items found