USE [uattcstravelmaster]
GO
/****** Object: StoredProcedure [dbo].[uspGetBookingsDetailsFor_TA] Script Date: 11/20/2015 16:47:09 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBookingsDetailsFor_TA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetBookingsDetailsFor_TA]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetDealMileage] Script Date: 11/20/2015 16:47:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGetDealMileage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnGetDealMileage]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCityMapping] Script Date: 11/20/2015 16:47:01 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCityMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageCityMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCountryMapping] Script Date: 11/20/2015 16:47:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCountryMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageCountryMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCurrencyMapping] Script Date: 11/20/2015 16:47:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCurrencyMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageCurrencyMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageHotelMapping] Script Date: 11/20/2015 16:47:03 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageHotelMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageHotelMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMarketMapping] Script Date: 11/20/2015 16:47:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMarketMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageMarketMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMealMapping] Script Date: 11/20/2015 16:47:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMealMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageMealMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManagePointMapping] Script Date: 11/20/2015 16:47:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManagePointMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManagePointMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomCategoryMapping] Script Date: 11/20/2015 16:47:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomCategoryMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageRoomCategoryMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomTypeMapping] Script Date: 11/20/2015 16:47:06 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomTypeMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageRoomTypeMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageSupplierMapping] Script Date: 11/20/2015 16:47:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageSupplierMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageSupplierMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageVehicleMapping] Script Date: 11/20/2015 16:47:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageVehicleMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageVehicleMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_TaxMapping] Script Date: 11/20/2015 16:47:08 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TaxMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TaxMapping]
GO
/****** Object: StoredProcedure [dbo].[uspUpdateExportStatus_TA] Script Date: 11/20/2015 16:47:10 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspUpdateExportStatus_TA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspUpdateExportStatus_TA]
GO
/****** Object: StoredProcedure [dbo].[Usp_ManagePointTypeMapping] Script Date: 11/20/2015 16:47:08 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_ManagePointTypeMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_ManagePointTypeMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageAgentMapping] Script Date: 11/20/2015 16:47:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageAgentMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageAgentMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageBranchMapping] Script Date: 11/20/2015 16:47:01 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageBranchMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageBranchMapping]
GO
/****** Object: Table [dbo].[VehicleMapping] Script Date: 11/20/2015 16:15:58 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_VechileMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[VehicleMapping] DROP CONSTRAINT [DF_VechileMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VehicleMapping]') AND type in (N'U'))
DROP TABLE [dbo].[VehicleMapping]
GO
/****** Object: Table [dbo].[SupplierMapping] Script Date: 11/20/2015 16:15:36 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SupplierMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SupplierMapping] DROP CONSTRAINT [DF_SupplierMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SupplierMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SupplierMapping] DROP CONSTRAINT [DF_SupplierMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SupplierMapping]') AND type in (N'U'))
DROP TABLE [dbo].[SupplierMapping]
GO
/****** Object: Table [dbo].[TaxMapping] Script Date: 11/20/2015 16:15:47 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxMapping]') AND type in (N'U'))
DROP TABLE [dbo].[TaxMapping]
GO
/****** Object: Table [dbo].[AgentMapping] Script Date: 11/20/2015 16:06:10 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AgentMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[AgentMapping] DROP CONSTRAINT [DF_AgentMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AgentMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[AgentMapping] DROP CONSTRAINT [DF_AgentMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AgentMapping]') AND type in (N'U'))
DROP TABLE [dbo].[AgentMapping]
GO
/****** Object: Table [dbo].[BranchMapping] Script Date: 11/20/2015 16:07:40 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BranchMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BranchMapping] DROP CONSTRAINT [DF_BranchMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BranchMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BranchMapping] DROP CONSTRAINT [DF_BranchMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BranchMapping]') AND type in (N'U'))
DROP TABLE [dbo].[BranchMapping]
GO
/****** Object: Table [dbo].[CityMapping] Script Date: 11/20/2015 16:07:53 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CityMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CityMapping] DROP CONSTRAINT [DF_CityMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CityMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CityMapping] DROP CONSTRAINT [DF_CityMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CityMapping]') AND type in (N'U'))
DROP TABLE [dbo].[CityMapping]
GO
/****** Object: Table [dbo].[CountryMapping] Script Date: 11/20/2015 16:08:04 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CountryMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CountryMapping] DROP CONSTRAINT [DF_CountryMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CountryMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CountryMapping] DROP CONSTRAINT [DF_CountryMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryMapping]') AND type in (N'U'))
DROP TABLE [dbo].[CountryMapping]
GO
/****** Object: Table [dbo].[CurrencyMapping] Script Date: 11/20/2015 16:08:37 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CurrencyMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CurrencyMapping] DROP CONSTRAINT [DF_CurrencyMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CurrencyMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CurrencyMapping] DROP CONSTRAINT [DF_CurrencyMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CurrencyMapping]') AND type in (N'U'))
DROP TABLE [dbo].[CurrencyMapping]
GO
/****** Object: Table [dbo].[Exported_TA] Script Date: 11/20/2015 16:09:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Exported_TA]') AND type in (N'U'))
DROP TABLE [dbo].[Exported_TA]
GO
/****** Object: UserDefinedTableType [dbo].[ExportTableType] Script Date: 11/20/2015 16:48:39 ******/
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ExportTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[ExportTableType]
GO
/****** Object: Table [dbo].[HotelMapping] Script Date: 11/20/2015 16:09:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HotelMapping]') AND type in (N'U'))
DROP TABLE [dbo].[HotelMapping]
GO
/****** Object: Table [dbo].[marketmapping] Script Date: 11/20/2015 16:10:14 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[marketmapping]') AND type in (N'U'))
DROP TABLE [dbo].[marketmapping]
GO
/****** Object: Table [dbo].[MealMapping] Script Date: 11/20/2015 16:10:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MealMapping]') AND type in (N'U'))
DROP TABLE [dbo].[MealMapping]
GO
/****** Object: Table [dbo].[PointsMapping] Script Date: 11/20/2015 16:11:01 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PointsMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PointsMapping] DROP CONSTRAINT [DF_PointsMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PointsMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PointsMapping] DROP CONSTRAINT [DF_PointsMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointsMapping]') AND type in (N'U'))
DROP TABLE [dbo].[PointsMapping]
GO
/****** Object: Table [dbo].[PointTypeMapping] Script Date: 11/20/2015 16:12:11 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointTypeMapping]') AND type in (N'U'))
DROP TABLE [dbo].[PointTypeMapping]
GO
/****** Object: Table [dbo].[RoomCategoryMapping] Script Date: 11/20/2015 16:14:40 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_RoomCategoryMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RoomCategoryMapping] DROP CONSTRAINT [DF_RoomCategoryMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_RoomCategoryMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RoomCategoryMapping] DROP CONSTRAINT [DF_RoomCategoryMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomCategoryMapping]') AND type in (N'U'))
DROP TABLE [dbo].[RoomCategoryMapping]
GO
/****** Object: Table [dbo].[RoomTypeMapping] Script Date: 11/20/2015 16:15:18 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_RoomTypeMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RoomTypeMapping] DROP CONSTRAINT [DF_RoomTypeMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomTypeMapping]') AND type in (N'U'))
DROP TABLE [dbo].[RoomTypeMapping]
GO
/****** Object: Table [dbo].[RoomTypeMapping] Script Date: 11/20/2015 16:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomTypeMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RoomTypeMapping](
[RoomTypeMappingID] [int] IDENTITY(1,1) NOT NULL,
[RoomTypeCode] [varchar](50) NULL,
[OtherMappingCode] [varchar](50) NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_RoomTypeMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[RoomCategoryMapping] Script Date: 11/20/2015 16:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomCategoryMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RoomCategoryMapping](
[RoomCategoryMappingID] [int] IDENTITY(1,1) NOT NULL,
[RoomCategoryCode] [varchar](20) NULL,
[OtherMappingCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_RoomCategoryMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_RoomCategoryMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PointTypeMapping] Script Date: 11/20/2015 16:12:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointTypeMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PointTypeMapping](
[PointTypeMappingID] [int] IDENTITY(1,1) NOT NULL,
[PointTypeCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PointsMapping] Script Date: 11/20/2015 16:11:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointsMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PointsMapping](
[PointsMappingID] [int] IDENTITY(1,1) NOT NULL,
[PointCode] [varchar](50) NOT NULL,
[OtherMappingCode] [varchar](50) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_PointsMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PointsMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[MealMapping] Script Date: 11/20/2015 16:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MealMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MealMapping](
[MealMappingID] [bigint] IDENTITY(1,1) NOT NULL,
[MealCode] [varchar](20) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CompanyId] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[marketmapping] Script Date: 11/20/2015 16:10:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[marketmapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[marketmapping](
[MarketMappingId] [bigint] IDENTITY(1,1) NOT NULL,
[MarketCode] [varchar](20) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[HotelMapping] Script Date: 11/20/2015 16:09:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HotelMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HotelMapping](
[HotelMappingID] [bigint] IDENTITY(1,1) NOT NULL,
[HotelCode] [varchar](20) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: UserDefinedTableType [dbo].[ExportTableType] Script Date: 11/20/2015 16:48:39 ******/
IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ExportTableType' AND ss.name = N'dbo')
CREATE TYPE [dbo].[ExportTableType] AS TABLE(
[TCS_UniqueID] [varchar](50) NULL,
[RecordType] [varchar](10) NULL,
[ServiceType] [varchar](10) NULL,
[ReservationRef] [varchar](100) NULL,
[ReservationId] [bigint] NULL,
[ServiceReservationID] [bigint] NULL,
[BookingDate] [varchar](100) NULL,
[BookingType] [varchar](10) NULL,
[MappedAgentCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NULL,
[MappedBaseCurrency] [varchar](10) NULL,
[MappedBranchCode] [varchar](20) NULL,
[ServiceLeadPax] [varchar](100) NULL,
[MappedServiceCode] [varchar](20) NULL,
[ServiceStartDate] [varchar](100) NULL,
[ServiceEndDate] [varchar](100) NULL,
[ServiceDuration] [int] NULL,
[MappedServiceStartCityCode] [varchar](100) NULL,
[MappedServiceEndCityCode] [varchar](100) NULL,
[MappedServiceCountryCode] [varchar](10) NULL,
[MappedServiceMarketCode] [varchar](10) NULL,
[ServiceVoucherMailed] [bit] NULL,
[ServiceVoucherPrint] [bit] NULL,
[ServiceNoOfAdult] [int] NULL,
[ServiceNoOfChild] [int] NULL,
[ServiceNoOfInfant] [int] NULL,
[SupplierConfirmationNo] [varchar](100) NULL,
[MappedSupplierCode] [varchar](10) NULL,
[SupplierPrice] [money] NULL,
[SupplierCurrency] [varchar](10) NULL,
[SupplierCurrencyROE] [decimal](18, 8) NULL,
[ServiceNetAmount] [money] NULL,
[ServiceVatAmount] [money] NULL,
[ServiceChargeAmount] [money] NULL,
[ServiceGrossAmount] [money] NULL,
[IPAddress] [varchar](100) NULL,
[AgentRef] [varchar](30) NULL,
[NoOfRoom] [int] NULL,
[MappedRoomCategory] [varchar](500) NULL,
[MappedRoomType] [varchar](500) NULL,
[MappedMealTypeCode] [varchar](500) NULL,
[PickUpTime] [varchar](20) NULL,
[MaxPax] [int] NULL,
[MaxLuggage] [int] NULL,
[FlightNumber] [varchar](20) NULL,
[FlightTime] [varchar](20) NULL,
[HandLuggage] [varchar](5) NULL,
[HandLuggageQuantity] [int] NULL,
[DateOfTransfer] [varchar](500) NULL,
[MappedPickUpPointCode] [varchar](500) NULL,
[MappedDropOffPointCode] [varchar](500) NULL,
[MappedPickUpPointType] [varchar](500) NULL,
[MappedDropOffPointType] [varchar](500) NULL,
[SplRequest] [varchar](500) NULL,
[VATCharge] [int] NULL,
[VATChargeType] [char](1) NULL
)
GO
/****** Object: Table [dbo].[Exported_TA] Script Date: 11/20/2015 16:09:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Exported_TA]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Exported_TA](
[ExportId] [int] IDENTITY(1,1) NOT NULL,
[ReservationId] [bigint] NOT NULL,
[ServiceReservationId] [bigint] NOT NULL,
[ServiceType] [varchar](10) NOT NULL,
[IsExported] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL,
[ModifyBy] [int] NULL,
[ModifiedDate] [datetime] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CurrencyMapping] Script Date: 11/20/2015 16:08:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CurrencyMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CurrencyMapping](
[CurrencyMappingID] [int] IDENTITY(1,1) NOT NULL,
[CurrencyCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_CurrencyMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_CurrencyMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CountryMapping] Script Date: 11/20/2015 16:08:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CountryMapping](
[CountryMappingID] [int] IDENTITY(1,1) NOT NULL,
[CountryCode] [varchar](10) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_CountryMapping_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [bigint] NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_CountryMapping_ModifiedDate] DEFAULT (getdate()),
[CompanyID] [varchar](5) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CityMapping] Script Date: 11/20/2015 16:07:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CityMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CityMapping](
[CityMappingID] [int] IDENTITY(1,1) NOT NULL,
[CityCode] [varchar](15) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_CityMapping_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [bigint] NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_CityMapping_ModifiedDate] DEFAULT (getdate()),
[CompanyID] [varchar](5) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[BranchMapping] Script Date: 11/20/2015 16:07:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BranchMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BranchMapping](
[BranchMappingID] [int] IDENTITY(1,1) NOT NULL,
[BranchID] [bigint] NOT NULL,
[OtherMappedCode] [varchar](20) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_BranchMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [date] NULL CONSTRAINT [DF_BranchMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[AgentMapping] Script Date: 11/20/2015 16:06:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AgentMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AgentMapping](
[AgentMappingID] [int] IDENTITY(1,1) NOT NULL,
[AgentID] [bigint] NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_AgentMapping_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [bigint] NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_AgentMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TaxMapping] Script Date: 11/20/2015 16:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TaxMapping](
[TaxMappingID] [bigint] IDENTITY(1,1) NOT NULL,
[TaxCode] [varchar](20) NULL,
[OtherMappingCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NULL,
[CreatedBy] [bigint] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL,
CONSTRAINT [PK_TaxMapping] PRIMARY KEY CLUSTERED
(
[TaxMappingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SupplierMapping] Script Date: 11/20/2015 16:15:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SupplierMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SupplierMapping](
[SupplierMappingID] [int] IDENTITY(1,1) NOT NULL,
[SupplierCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_SupplierMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SupplierMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[VehicleMapping] Script Date: 11/20/2015 16:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VehicleMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[VehicleMapping](
[VehicleMappingID] [int] IDENTITY(1,1) NOT NULL,
[VehicleCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_VechileMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[sp_ManageBranchMapping] Script Date: 11/20/2015 16:47:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageBranchMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageBranchMapping]
@BranchMappingID Bigint =0,
@BranchID Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.BranchID, tbl.BranchName,ISNULL(BranchMappingId,0)BranchMappingId,
IsnUll(b.BranchID,'''') BranchID,ISNULL(OtherMappedCode,'''')OtherMappingCode from (select DISTINCT BranchID,BranchName from Company_Branch Where BranchID !='''' ANd BranchID !=''0'' and CompanyId=@CompanyID)tbl
Left Outer join BranchMapping b
ON b.BranchID=tbl.BranchID
Order By tbl.BranchName
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM BranchMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappedCode=@OtherMappingCode)
BEGIN
If @BranchMappingID =0 BEGIN
INSERT INTO BranchMapping (BranchID,OtherMappedCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@BranchID,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE BranchMapping SET BranchID=@BranchID,OtherMappedCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE BranchMappingID=@BranchMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping Branch.''
END
ELSE BEGIN
SET @Result=''Branch code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Branch.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageAgentMapping] Script Date: 11/20/2015 16:47:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageAgentMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageAgentMapping]
@AgentMappingID Bigint =0,
@AgentID Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
SELECT
CM.ClientId, SB.BranchId as SABranchID, SB.BranchName SABranchName,
CB.BranchName, CM.BranchId as CompanyBranchId, SM.AgencyName, CM.CityName,
CM.PhoneNo, CM.Email, SM.AgencyLogo,
case when CM.[Status]=1 then ''Active'' else ''Inactive'' end as [Status],
CASE WHEN LEN(SM.AgencyName)>20 THEN SUBSTRING(SM.AgencyName,1,18)+''..'' ELSE SM.AgencyName END AS ShortAgencyName,
SM.AgencyCurr, SM.IsCorporate,CONVERT(varchar(50),CM.CreatedOn,105)CreatedOn,CONVERT(varchar(50),CM.ModifyOn,105)ModifyOn,
(case when ISNULL(sm.IsCorporate, 0) = ''0'' then ''B2B'' else ''Corporate'' end) AgencyType,SM.PANNo
INTO #Results
from dbo.Client_Master CM
inner join Company_Branch CB on CB.BranchId=CM.BranchId
inner join Subagent_Master SM on SM.ClientId=CM.ClientId
INNER JOIN (select SM.ClientId, MIN(SB.BranchId) BranchId from Subagent_Master SM
inner join Subagent_Branch SB on SB.AgentId=SM.ClientId
group by SM.ClientId
)SM1 on SM1.ClientId=SM.ClientId
inner join Subagent_Branch SB on SB.BranchId=SM1.BranchId
where CM.CompanyId=@CompanyID and cm.Type=''SA''
Select * from #Results tmpR
left outer join AgentMapping AM on AM.AgentID=tmpR.ClientId
Drop table #Results
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM AgentMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @AgentMappingID =0 BEGIN
INSERT INTO AgentMapping (AgentID,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@AgentID,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE AgentMapping SET AgentID=@AgentID,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE AgentMappingID=@AgentMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping Agent.''
END
ELSE BEGIN
SET @Result=''Agent code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Agent.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[Usp_ManagePointTypeMapping] Script Date: 11/20/2015 16:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_ManagePointTypeMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_ManagePointTypeMapping]
@PointTypeMappingID int =0,
@PointTypeCode Varchar(10)='''',
@OtherMappingCode Varchar(50)='''',
@CompanyID varchar(5)='''',
@CreatedBy bigint=0,
@CreatedDate datetime='''',
@ModifiedBy int=0,
@ModifiedDate datetime='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL ROOM TYPE INFORMATION
--==============================================================================================================
IF @QueryNo=1
BEGIN
Select tbl.PointCode as PointTypeCode, tbl.PointName as PointTypeName,ISNULL(PointTypeMappingID,0)PointTypeMappingID,
IsnUll(b.PointTypeCode,'''') VehicleCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT PointCode,PointName from uattcsnego..Trf_Nego_TransferPoints Where PointCode !='''' ANd PointCode !=''0'')tbl
Left Outer join PointTypeMapping b
ON b.PointTypeCode=tbl.PointCode
Order By tbl.PointName
END
ELSE
IF @QueryNo=2
BEGIN
IF NOT EXISTS (SELECT 1 FROM PointTypeMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @PointTypeMappingID =0 BEGIN
INSERT INTO PointTypeMapping (PointTypeCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate)
VALUES(@PointTypeCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE(),@ModifiedBy,GETDATE())
END
ELSE BEGIN
UPDATE PointTypeMapping SET PointTypeCode=@PointTypeCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE PointTypeMappingID=@PointTypeMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Point code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Point.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[uspUpdateExportStatus_TA] Script Date: 11/20/2015 16:47:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspUpdateExportStatus_TA]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:<Author: Ajay Agrahari>
-- Create date: <Create Date,24 Nov 2014>
-- Description: <[SP_InsertExportDataFor_TA]>
-- =============================================
CREATE procedure [dbo].[uspUpdateExportStatus_TA]
(
@Table ExportTableType READONLY,
@CreatedBy int,
@ModifiedBy int,
@isExported int
)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
MERGE Exported_TA AS ETA
USING (select ReservationId, ServiceReservationId, ServiceType from @Table) AS ETT
ON ETT.ReservationId = ETA.ReservationId and ETT.ServiceReservationId = ETA.ServiceReservationId
WHEN MATCHED THEN
UPDATE SET ETA.IsExported = @isExported, ETA.ModifyBy = @ModifiedBy, ETA.ModifiedDate = getdate()
WHEN NOT MATCHED THEN
INSERT(ReservationId, ServiceReservationId, ServiceType, IsExported, IsDeleted, CreatedBy, CreatedDate, ModifyBy, ModifiedDate)
VALUES(ETT.ReservationId, ETT.ServiceReservationId, ETT.ServiceType, @isExported, 0, @CreatedBy, Getdate(), @ModifiedBy, Getdate());
COMMIT TRANSACTION
SELECT 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 0
END CATCH
end
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_TaxMapping] Script Date: 11/20/2015 16:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TaxMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Ajay Agrahari
-- Create date: May 16 2015
-- Modified By: Ajay Agrahari
-- Modified Date : May 16 2015
-- Description: This stored procedure used to Tax Mapping
-- =============================================================================================
create PROCEDURE [dbo].[sp_TaxMapping]
@TaxMappingID Bigint =0,
@TaxCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get tax Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.TaxCode, tbl.TaxName,ISNULL(TaxMappingId,0)TaxMappingId,
IsnUll(b.TaxCode,'''') TaxCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT TaxCode,TaxName from uattcsnego..MstTax Where TaxCode !='''' ANd TaxCode !=''0'' and CompanyCode=@CompanyID)tbl
Left Outer join TaxMapping b
ON b.TaxCode=tbl.TaxCode
Order By tbl.TaxName
END
--======================================================================================================================
-- #Query No#2 Save Tax Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM TaxMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @TaxMappingID =0 BEGIN
INSERT INTO TaxMapping (TaxCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@TaxCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE TaxMapping SET TaxCode=@TaxCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE TaxMappingID=@TaxMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in Tax Mapping.''
END
ELSE BEGIN
SET @Result=''Tax code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Tax.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageVehicleMapping] Script Date: 11/20/2015 16:47:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageVehicleMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageVehicleMapping]
@VehicleMappingID Bigint =0,
@VehicleCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.VehicleCode, tbl.VehicleName,ISNULL(VehicleMappingId,0)VehicleMappingId,
IsnUll(b.VehicleCode,'''') VehicleCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT VehicleCode,uattcsnego.dbo.getXMLDescription(VEHICLETYPE,''GB'') as VehicleName from uattcsnego.TRF_EXC.MstVehicle Where VehicleCode !='''' ANd VehicleCode
!=''0'' and CompanyCode=@CompanyID)tbl
Left Outer join VehicleMapping b
ON b.VehicleCode=tbl.VehicleCode
Order By tbl.VehicleName
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM VehicleMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @VehicleMappingId =0 BEGIN
INSERT INTO VehicleMapping (VehicleCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@VehicleCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE VehicleMapping SET VehicleCode=@VehicleCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE VehicleMappingId=@VehicleMappingId AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Vehicle code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other vehicle.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageSupplierMapping] Script Date: 11/20/2015 16:47:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageSupplierMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Kapil Dev Tripathi>
-- Create date: <JAN 13 2014>
-- Modified By: Ajay Agrahari
-- Modified Date : 19 Dec 2014
-- Description: <This sp manage mappings of suppliers>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageSupplierMapping]
@SupplierCode Varchar(10)='''',
@OtherMappingCode Varchar(10)='''',
@SupplierMappingID int =0,
@CreatedBy bigint=0,
@CompanyID varchar(5)='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL SUPPLIER INFORMATION ON BASE OF COMPANY
--==============================================================================================================
IF @QueryNo=1 BEGIN
Select DISTINCT SupplierId,a.SupplierCode,a.SupplierCompany,a.CompanyId,ISNULL(SupplierMappingId,0)SupplierMappingId,
ISNULL(b.OtherMappingcode,'''') OtherMappingcode from Supplier_Master a
Left Outer join SupplierMapping b
on a.SupplierCode =b.SupplierCode and a.CompanyID=b.CompanyID
Where a.CompanyId =@CompanyID AND a.SupplierCode Is NOT NULL
END
--==============================================================================================================
-- #QUERY No#2- MAPPED SUPPLIER # HALF
--==============================================================================================================
ELSE IF @QueryNo=2 BEGIN
--IF NOT EXISTS (SELECT * FROM Supplier_Master With(NoLock) Where ((SupplierCode=@SupplierCode) OR (OtherMappingCode=@OtherMappingCode)) and CompanyID=@CompanyID ) BEGIN
--IF NOT EXISTS (SELECT * FROM Supplier_Master With(NoLock) WHERE SupplierCode=@SupplierCode and CompanyID=@CompanyID ) BEGIN
IF NOT EXISTS (SELECT 1 FROM SupplierMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @SupplierMappingID =0 BEGIN
INSERT INTO SupplierMapping (SupplierCode,OtherMappingCode,CreatedBy,CreatedDate,CompanyID) VALUES(@SupplierCode,@OtherMappingCode,@CreatedBy,GETDATE(),@CompanyID)
--UPDATE Supplier_Master SET OtherMappingCode=@OtherMappingCode WHERE SupplierCode=@SupplierCode AND CompanyID=@CompanyID
END
ELSE BEGIN
UPDATE SupplierMapping SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE() WHERE SupplierMappingID=@SupplierMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping.''
END
ELSE BEGIN
SET @Result=''Supplier has been mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other supplier.''
END
--END
--ELSE BEGIN
-- SET @Result=''This mapping code already exist for other supplier.''
--END
Select @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomTypeMapping] Script Date: 11/20/2015 16:47:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomTypeMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-------------------------
-- =============================================
-- Author: Amit Singh
-- Create date: <DEC 19 2014>
-- Description: <This sp manage mappings of Room Types>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageRoomTypeMapping]
@RoomTypeMappingID int =0,
@RoomTypeCode Varchar(10)='''',
@OtherMappingCode Varchar(50)='''',
@CompanyID varchar(5)='''',
@CreatedBy bigint=0,
@CreatedDate datetime='''',
@ModifiedBy int=0,
@ModifiedDate datetime='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL ROOM TYPE INFORMATION
--==============================================================================================================
IF @QueryNo=1
BEGIN
SELECT RoomTypeMappingID,RoomTypeCode,OtherMappingCode FROM RoomTypeMapping WITH (NOLOCK) WHERE CompanyID=@CompanyID ORDER BY CreatedDate DESC
END
ELSE
IF @QueryNo=2
BEGIN
IF NOT EXISTS (SELECT 1 FROM RoomTypeMapping With(NoLock)
WHERE RoomTypeMappingID <> @RoomTypeMappingID AND CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
UPDATE RoomTypeMapping
SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@ModifiedBy,ModifiedDate=GETDATE()
WHERE RoomTypeMappingID=@RoomTypeMappingID AND CompanyID=@CompanyID
set @Result=''Room Type has been mapped successfully.''
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Room Type.''
END
Select @Result
END
END'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomCategoryMapping] Script Date: 11/20/2015 16:47:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomCategoryMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageRoomCategoryMapping]
@RoomCategoryMappingID Bigint =0,
@RoomCategoryCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Meal Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.RoomCategoryCode, tbl.RoomCategoryDesc,ISNULL(RoomCategoryMappingId,0)RoomCategoryMappingId,
IsnUll(b.RoomCategoryCode,'''') RoomCategoryCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT RoomCategoryCode,RoomCategoryDesc from uattcsnego..MstRoomCategory Where RoomCategoryCode !='''' ANd RoomCategoryCode !=''0'' )tbl
Left Outer join RoomCategoryMapping b
ON b.RoomCategoryCode=tbl.RoomCategoryCode
Order By tbl.RoomCategoryDesc
END
--======================================================================================================================
-- #Query No#2 Save Meal Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM RoomCategoryMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @RoomCategoryMappingID =0 BEGIN
INSERT INTO RoomCategoryMapping (RoomCategoryCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@RoomCategoryCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE RoomCategoryMapping SET RoomCategoryCode=@RoomCategoryCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE RoomCategoryMappingID=@RoomCategoryMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Room Category code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Room Category.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManagePointMapping] Script Date: 11/20/2015 16:47:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManagePointMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManagePointMapping]
@PointsMappingID Bigint =0,
@PointCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.PointCode, tbl.PointName,ISNULL(PointsMappingId,0)PointsMappingId,
IsnUll(b.PointCode,'''') VehicleCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT PointCode,uattcsnego.dbo.getXMLDescription(POINTNAME,''GB'') as PointName from uattcsnego.TRF_EXC.MstPoint Where PointCode !='''' ANd PointCode !=''0'' and CompanyCode=@CompanyID)tbl
Left Outer join PointsMapping b
ON b.PointCode=tbl.PointCode
Order By tbl.PointName
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM PointsMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @PointsMappingID =0 BEGIN
INSERT INTO PointsMapping (PointCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@PointCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE PointsMapping SET PointCode=@PointCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE PointsMappingID=@PointsMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Point code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Point.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMealMapping] Script Date: 11/20/2015 16:47:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMealMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageMealMapping]
@MealMappingID Bigint =0,
@MealCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Meal Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.MealCode, tbl.MealDesc,ISNULL(MealMappingId,0)MealMappingId,
IsnUll(b.MealCode,'''') MealCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT MealCode,MealDesc from Hotel_Room_Details Where MealCode !='''' ANd MealCode !=''0'' )tbl
Left Outer join MealMapping b
ON b.MealCode=tbl.MealCode
Order By tbl.MealDesc
END
--======================================================================================================================
-- #Query No#2 Save Meal Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM MealMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @MealMappingID =0 BEGIN
INSERT INTO MealMapping (MealCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@MealCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE MealMapping SET MealCode=@MealCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE MealMappingID=@MealMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Meal code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Meal.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMarketMapping] Script Date: 11/20/2015 16:47:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMarketMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Ajay Agrahari
-- Create date: 19 Dec 2014
-- Description: This stored procedure used to map Currency Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageMarketMapping]
@MarketMappingId Bigint =0,
@MarketCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Market Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.RateCode, tbl.RateName,ISNULL(MarketMappingId,0)MarketMappingId,
IsnUll(b.MarketCode,'''') MarketCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT RateCode,RateName from uattcsnego..MstRate Where RateCode !='''' ANd RateCode !=''0'' and CompanyCode=@CompanyID )tbl
Left Outer join marketmapping b
ON b.MarketCode=tbl.RateCode
Order By tbl.RateName
END
--======================================================================================================================
-- #Query No#2 Save market Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM marketmapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @MarketMappingId =0 BEGIN
INSERT INTO marketmapping (MarketCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@MarketCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE marketmapping SET MarketCode=@MarketCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE MarketMappingId=@MarketMappingId AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Market code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Market.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageHotelMapping] Script Date: 11/20/2015 16:47:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageHotelMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Description: This stored procedure used to map hotel code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageHotelMapping]
@HotelMappingID bigint=0,
@HotelCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Hotel Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
SELECT DISTINCT a.HotelCode,a.HotelName,ISNULL(HotelMappingID,0)HotelMappingID,ISNULL(b.HotelCode,'''')MapHotelCode
,IsNULL(OtherMappingCode,'''')OtherMappingCode FROM uattcsnego..MstHotel a With(NoLock)
Left Outer Join HotelMapping b ON a.HotelCode =b.HotelCode AND b.CompanyID=@CompanyID
Order By a.HotelName
END
--======================================================================================================================
-- #Query No#2 Save Hotel Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM HotelMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @HotelMappingID =0 BEGIN
INSERT INTO HotelMapping (HotelCode,OtherMappingCode,CompanyID,CreatedBy,CreatedDate)
VALUES(@HotelCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE HotelMapping SET HotelCode=@HotelCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE HotelMappingID=@HotelMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping branch.''
END
ELSE BEGIN
SET @Result=''Hotel code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Hotel.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCurrencyMapping] Script Date: 11/20/2015 16:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCurrencyMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Ajay Agrahari
-- Create date: 19 Dec 2014
-- Description: This stored procedure used to map Currency Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageCurrencyMapping]
@CurrencyMappingID Bigint =0,
@CurrencyCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Meal Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.CurrencyCode, tbl.CurrencyName,ISNULL(CurrencyMappingId,0)CurrencyMappingId,
IsnUll(b.CurrencyCode,'''') MealCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT CurrencyCode,CurrencyName from Currency_Master Where CurrencyCode !='''' ANd CurrencyCode !=''0'' )tbl
Left Outer join CurrencyMapping b
ON b.CurrencyCode=tbl.CurrencyCode Order By tbl.CurrencyName
END
--======================================================================================================================
-- #Query No#2 Save Meal Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM CurrencyMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @CurrencyMappingId =0 BEGIN
INSERT INTO CurrencyMapping (CurrencyCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@CurrencyCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE CurrencyMapping SET CurrencyCode=@CurrencyCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE CurrencyMappingID=@CurrencyMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Currency code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other currency.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCountryMapping] Script Date: 11/20/2015 16:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCountryMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Kapil Dev Tripathi>
-- Create date: <JAN 13 2014>
-- Description: <This sp manage mappings of countries>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageCountryMapping]
@CountryCode Varchar(10)='''',
@OtherMappingCode Varchar(10)='''',
@CountryMappingID int =0,
@CreatedBy bigint=0,
@CompanyID varchar(5)='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL COUNTRY INFORMATION
--==============================================================================================================
IF @QueryNo=1 BEGIN
Select Distinct Isnull(CountryMappingID,0) CountryMappingID,a.CountryCode,CountryName,IsNull(b.OtherMappingCode,'''') as OtherMappingCode from st_Country a With(NoLock)
Left Outer Join CountryMapping b With(NoLock)
ON a.CountryCode =b.CountryCode and b.CompanyID=@CompanyID Order By CountryName
END
--==============================================================================================================
-- #QUERY No#2- SELECT ALL COUNTRY INFORMATION WHICH IS NOT MAPPED
--==============================================================================================================
ELSE IF @QueryNo=2 BEGIN
Select Isnull(CountryMappingID,0) CountryMappingID,a.CountryCode,CountryName,IsNull(b.OtherMappingCode,'''') as OtherMappingCode From
(Select a.CountryCode,CountryName from st_Country a With(NoLock)
Where a.CountryCode Not In (Select CountryCode From CountryMapping WHERE CompanyID=@CompanyID ) )a Left Outer join
CountryMapping b With(NoLock)
ON a.CountryCode =b.CountryCode and b.CompanyID=@CompanyID Order By CountryName
END
--==============================================================================================================
-- #QUERY No#3- MAPPED COUNTRY INFORMATION
--==============================================================================================================
ELSE IF @QueryNo=3 BEGIN
IF NOT EXISTS (SELECT * FROM CountryMapping With(NoLock) Where ((@CountryMappingID=0 AND CountryCode=@CountryCode) OR (CountryMappingID!=@CountryMappingID AND OtherMappingCode=@OtherMappingCode)) and CompanyID=@CompanyID ) BEGIN
IF NOT EXISTS (SELECT * FROM CountryMapping With(NoLock) WHERE CountryCode=@CountryCode and CompanyID=@CompanyID ) BEGIN
INSERT INTO CountryMapping (CountryCode,OtherMappingCode,CreatedBy,CreatedDate,CompanyID) VALUES(@CountryCode,@OtherMappingCode,@CreatedBy,GETDATE(),@CompanyID)
END
ELSE BEGIN
UPDATE CountryMapping SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE() WHERE CountryCode=@CountryCode and CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping.''
END
ELSE BEGIN
SET @Result=''Country has been mapped successfully.''
END
END
ELSE BEGIN
SET @Result=''This mapping code already exist for other country.''
END
Select @Result as Result
END
--==============================================================================================================
-- #QUERY No#4- SELECT ALL COUNTRY
--==============================================================================================================
ELSE IF @QueryNo=4 BEGIN
Select Distinct a.CountryCode,a.CountryName from st_Country a With(NoLock) Order By CountryName
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCityMapping] Script Date: 11/20/2015 16:47:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCityMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Kapil Dev Tripathi>
-- Create date: <JAN 13 2014>
-- Description: <Manage mapping of cities>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageCityMapping]
@CityCode Varchar(15)='''',
@OtherMappingCode Varchar(15)='''',
@CityMappingId int=0,
@CountryCode varchar(10)='''',
@CreatedBy bigint=0,
@CompanyID varchar(5)='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL CITIES OF PARTICULAR COUNTRY
--==============================================================================================================
IF @QueryNo=1 BEGIN
Select DISTINCT tbl.CityCode,tbl.CityName,tbl.CountryCode,ISNULL(CityMappingID,0)CityMappingID,
Isnull(OtherMappingCode,'''') as OtherMappingCode from (Select a.CityCode,b.CityName,a.CountryCode from st_City a WITH(NOLOCK)
Inner Join st_CityName b WITH(NOLOCK) on a.CityCode =b.CityCode AND a.CountryCode= @CountryCode)tbl Left Join CityMapping d WITH(NOLOCK)
On d.CityCode=tbl.Citycode AND d.CompanyId=@CompanyID Order By CityName
END
--==============================================================================================================
-- #QUERY No#2- SELECT ALL CITIES
--==============================================================================================================
ELSE IF @QueryNo=2 BEGIN
Select DISTINCT tbl.CityCode,tbl.CityName,tbl.CountryCode,ISNULL(CityMappingID,0)CityMappingID,
Isnull(OtherMappingCode,'''') as OtherMappingCode from (Select a.CityCode,b.CityName,a.CountryCode from st_City a WITH(NOLOCK)
Inner Join st_CityName b WITH(NOLOCK) on a.CityCode =b.CityCode )tbl Left Join CityMapping d WITH(NOLOCK)
On d.CityCode=tbl.Citycode AND d.CompanyId=@CompanyID Order By CityName
END
--==============================================================================================================
-- #QUERY No#3- MAPPED CITY
--==============================================================================================================
ELSE IF @QueryNo=3 BEGIN
IF NOT EXISTS (SELECT * FROM CityMapping With(NoLock) Where ((@CityMappingID=0 AND CityCode=@CityCode) OR (CityMappingID!=@CityMappingID AND OtherMappingCode=@OtherMappingCode))AND CompanyID=@CompanyID ) BEGIN
IF NOT EXISTS (SELECT * FROM CityMapping With(NoLock) WHERE CityCode=@CityCode AND CompanyID=@CompanyID) BEGIN
INSERT INTO CityMapping (CityCode,OtherMappingCode,CreatedBy,CreatedDate,CompanyID)
VALUES(@CityCode,@OtherMappingCode,@CreatedBy,GETDATE(),@CompanyID)
END
ELSE BEGIN
UPDATE CityMapping SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,Modifieddate=GETDATE()
WHERE CityCode=@CityCode AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping city.''
END
ELSE BEGIN
SET @Result=''City has been mapped successfully.''
END
END
ELSE BEGIN
SET @Result=''This mapping code already exist for other city.''
END
Select @Result as Result
END
END
'
END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetDealMileage] Script Date: 11/20/2015 16:47:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGetDealMileage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
-- =============================================
-- Author: Ajay
-- Create date: July 12 2015
-- Description: get deal mileage
-- =============================================
/*
Select * from Transfer_P2P_Points Where TransferP2PReservationId=''313''
select dbo.fnGetDealMileage(13,1,''PT4'',''PT26'')
select dbo.fnGetDealMileage(313,2,''PT18'',''PT55'')
select dbo.fnGetDealMileage(313,3,''PT55'',''PT18'')
*/
CREATE FUNCTION [dbo].[fnGetDealMileage]
(
@TransferP2PReservationId INT,
@VisitOrder INT,
@PickupPointCode varchar(10),
@DropOffPointCode varchar(10)
)
RETURNS INT
AS
BEGIN
DECLARE @Distance INT =0
IF (SELECT MIN(visitOrder) FROM Transfer_P2P_Points(NOLOCK) WHERE TransferP2PReservationId=@TransferP2PReservationId)=@VisitOrder
BEGIN
SET @Distance=(SELECT DIST_FROM_GARAGE FROM uattcsnego.TRF_EXC.MstPoint(NOLOCK) where PointCode=@PickupPointCode)
END
IF (SELECT MAX(visitOrder) FROM Transfer_P2P_Points(NOLOCK) WHERE TransferP2PReservationId=@TransferP2PReservationId) =@VisitOrder
BEGIN
SET @Distance +=(SELECT DIST_FROM_GARAGE FROM uattcsnego.TRF_EXC.MstPoint(NOLOCK) where PointCode=@DropOffPointCode)
END
RETURN isnull(@Distance,0)
END
'
END
GO
/****** Object: StoredProcedure [dbo].[uspGetBookingsDetailsFor_TA] Script Date: 11/20/2015 16:47:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBookingsDetailsFor_TA]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--uspGetBookingsDetailsFor_TA @CompId=''ATU'', @IsExported =0
CREATE PROC [dbo].[uspGetBookingsDetailsFor_TA]
(
@From datetime=null,
@To datetime=null,
@ReservationRef varchar(50)=null,
@BookingStatus varchar(50)=''HK'',
@ServiceType varchar(50)= ''ALL'',
@IsExported bit = 0,
@CompId varchar(10)
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Sql varchar(max)
Create table #temptbl (
/*3*/ RecordType varchar(10),
/*13*/ ServiceType varchar(10),
/*4*/ ReservationRef varchar(100),
/*56*/ ReservationId bigint,
/*11*/ ServiceReservationID bigint,
/*5*/ BookingDate varchar(100),
/*5A*/ BookingDateDisplay DATETIME,
/*6*/ BookingType varchar(10),
/*7*/ MappedAgentCode varchar(20),
/*8*/ CompanyID varchar(5),
/*9*/ MappedBaseCurrency varchar(10),
/*10*/ MappedBranchCode varchar(20),
/*12*/ ServiceLeadPax varchar(100),
/*14*/ MappedServiceCode varchar(20),
/*15*/ ServiceStartDate varchar(100),
/*16*/ ServiceEndDate varchar(100),
/*17*/ ServiceDuration int,
/*18*/ MappedServiceStartCityCode varchar(100),
/*19*/ MappedServiceEndCityCode varchar(100),
/*20*/ MappedServiceCountryCode varchar(10),
/*21*/ MappedServiceMarketCode varchar(10),
/*22*/ ServiceVoucherMailed bit,
/*23*/ ServiceVoucherPrint bit,
/*24*/ ServiceNoOfAdult int,
/*25*/ ServiceNoOfChild int,
/*26*/ ServiceNoOfInfant int,
/*27*/ SupplierConfirmationNo varchar(100),
/*28*/ MappedSupplierCode varchar(10),
/*29*/ SupplierCurrency varchar(10),
/*30*/ SupplierCurrencyROE decimal(18,8),
/*31*/ SupplierPrice money,
/*32*/ ServiceNetAmount money,
/*34*/ ServiceVatAmount money,
/*34*/ ServiceChargeAmount money,
/*33*/ ServiceGrossAmount money,
/*35*/ IPAddress varchar(100),
/*37*/ AgentRef varchar(30),
/*38*/ NoOfRoom int,
/*39*/ MappedRoomCategory varchar(500),
/*40*/ MappedRoomType varchar(500),
/*41*/ MappedMealTypeCode varchar(500),
/*44*/ PickUpTime varchar(20),
/*45*/ MaxPax int,
/*46*/ MaxLuggage int,
/*47*/ FlightNumber varchar(20),
/*48*/ FlightTime varchar(20),
/*49*/ HandLuggage varchar(5),
/*50*/ HandLuggageQuantity int,
/*51*/ DateOfTransfer varchar(500),
/*52*/ MappedPickUpPointCode varchar(500),
/*53*/ MappedDropOffPointCode varchar(500),
/*54*/ MappedPickUpPointType varchar(500),
/*55*/ MappedDropOffPointType varchar(500),
/*56*/ SplRequest varchar(500),
/*57*/ ComponentCost decimal,
/*58*/ DriverSubsistance decimal,
/*59*/ PerKmCost decimal,
/*60*/ TotalBlockSize int,
/*61*/ RoomID int,
/*62*/ VATCharge int,
/*63*/ VATChargeType CHAR(1),
/*64*/ Distance int,
/*65*/ TaxCode varchar(10),
/*66*/ HotelName varchar(100),
/*67*/ SupplierName varchar(50),
/*68*/ PickUpPointName varchar(50),
/*69*/ DropOffPointName varchar(50),
/*70*/ TransCurrency varchar(10),
/*71*/ TransCurrROE decimal(18,8),
/*72*/ TransPrice money,
/*73*/ DeadMileage int,
/*74*/ ChildPrice1 money,
/*75*/ ExtrabedPrice1 money,
/*76*/ ChildPrice2 money,
/*77*/ ExtrabedPrice2 money,
/*78*/ ChildTransPrice1 money,
/*79*/ ExtrabedTransPrice1 money,
/*80*/ ChildTransPrice2 money,
/*81*/ ExtrabedTransPrice2 money,
/*82*/ Extrabed bit
)
INSERT INTO #temptbl (RecordType, ReservationRef, ReservationId,BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode, ServiceLeadPax)
SELECT DISTINCT ''PNRHEAD'', RM.ReservationRef, RM.ReservationId, HR.Channel, AM.OtherMappingCode, @CompId, CM.OtherMappingCode, BM.OtherMappedCode, RP.Title + SPACE(1) + RP.FirstName + SPACE(1) + RP.LastName as LeadPaxName
FROM Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Hotel_Reservation HR on HR.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId
inner join (select HotelReservationId, SUM(NoOfRooms) NoOfRooms from Hotel_Room_Details group by HotelReservationId) HRD on HRD.HotelReservationId = HR.HotelReservationId
inner join Hotel_Passenger HP on HP.HotelReservationId = HR.HotelReservationId
inner join Reservation_Passenger RP on RP.PaxId = HP.PaxId and RP.isLeadPax = 1
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join HotelMapping HM ON HM.HotelCode = HR.HotelCode and HM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = HR.CityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = HR.CountryCode and COM.CompanyId = RM.CompanyId
inner join MarketMapping HMM ON HMM.MarketCode = HR.MarketCode and HMM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = HR.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = HR.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode having TaxCode != ''VAT'' ) RC ON RC.ProductId = HR.HotelReservationId
left join Reservation_Charge TAX ON TAX.ProductId = HR.HotelReservationId and TAX.TaxCode = ''VAT''
WHERE RM.CompanyId = ''ATU'' AND RI.ProductType = ''HTL'' and HR.Status = ''HK'' and HR.ChkInDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(HR.ChkInDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ServiceType, ReservationRef, ReservationId,HotelName, ServiceReservationID, BookingDate,BookingDateDisplay,
MappedServiceCode, ServiceStartDate, ServiceEndDate,ServiceDuration, MappedServiceStartCityCode, MappedServiceCountryCode, MappedServiceMarketCode, ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo, MappedSupplierCode,SupplierName,
/*SupplierPrice*/TransPrice, /*SupplierCurrency*/ TransCurrency, /*SupplierCurrencyROE*/TransCurrROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount,IPAddress, AgentRef, NoOfRoom, SplRequest, VATCharge, VATChargeType,TaxCode)
SELECT ''ATU'', ''BKGHEAD'',''HTL'', RM.ReservationRef, RM.ReservationId,HR.HotelName, HR.HotelReservationId, CONVERT(varchar(10), HR.BookingDate, 105) ,HR.BookingDate ,
isNull(HM.OtherMappingCode,'''') ServiceCode, CONVERT(varchar(10), HR.ChkInDate,105) as ServiceStartDate, CONVERT(varchar(10), HR.ChkOutDate, 105) as ServiceEndDate,
DATEDIFF(day, HR.ChkInDate, HR.ChkOutDate) AS ServiceDuration, isNull(CIM.OtherMappingCode,'''') MappedServiceStartCityCode, isNull(COM.OtherMappingCode,'''') MappedServiceCountryCode, isNull(HMM.OtherMappingCode,'''') MappedMarketCode, isNull(HR.VoucherMail,0)
VoucherMail, isNull(HR.VoucherPrint,0) VoucherPrint, isNull(HR.AdultCount,0) AdultCount, isNull(HR.ChildCount,0) ChildCount, isNull(HR.InfantCount,0) InfantCount, isNull(HR.ConfirmationNo,''''), isNull(SM.OtherMappingCode,'''') MappedSupplierCode,SUPM.SupplierCompany ,/*isNull(HR.SupplierPrice,0) SupplierBookedPrice,*/
isNull((isnull(HR.SupplierPrice,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) TransPrice,isNull(CM.OtherMappingCode,'''') MappedSupplierCurrency,/*isNull(HR.SupplierCurrROE,0) SupplierCurrencyROE,*/
isNull(HR.TransCurrROE,0) TransCurrROE,isNull(isnull(HR.WSNetAmt,0) + isnull(TAX.GrossAmt,0) + isnull(RC.GrossAmt,0),0) ServiceNetAmount,
isnull(TAX.GrossAmt, 0)ServiceVatAmount,isNull(RC.GrossAmt,0)ServiceChargeAmount,isnull(HR.SupplierPrice,0) ServiceGrossAmount,isNull(HR.IPAddress,''10.0.0.0'') IPAddress,isNull(HR.AgentRef,'''') AgentRef,isNull(HRD.NoOfRooms,0) NoOfRooms,
HR.SplRequest, ACT.Charge, ACT.ChargeType,isNull(TM.OtherMappingCode,'''') MappedTaxCode
from Reservation_Master RM
inner join Hotel_Reservation HR on HR.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId
inner join (select HotelReservationId, SUM(NoOfRooms) NoOfRooms from Hotel_Room_Details group by HotelReservationId) HRD on HRD.HotelReservationId = HR.HotelReservationId
inner join Hotel_Passenger HP on HP.HotelReservationId = HR.HotelReservationId
inner join Reservation_Passenger RP on RP.PaxId = HP.PaxId and RP.isLeadPax = 1
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join HotelMapping HM ON HM.HotelCode = HR.HotelCode and HM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = HR.CityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = HR.CountryCode and COM.CompanyId = RM.CompanyId
inner join MarketMapping HMM ON HMM.MarketCode = HR.MarketCode and HMM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = HR.CCode and SM.CompanyId = RM.CompanyId
inner join Supplier_Master SUPM ON SUPM.SupplierCode= SM.SupplierCode and SUPM.CompanyID=SM.CompanyID
inner join CurrencyMapping CM ON CM.CurrencyCode = HR.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, Isnull(SUM(GrossAmt),0) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode having TaxCode != ''VAT'') RC ON RC.ProductId = HR.HotelReservationId
left join Reservation_Charge TAX ON TAX.ProductId = HR.HotelReservationId and TAX.TaxCode = ''VAT''
left join AutoCharges_Master ACM ON ACM.ChargeCode = TAX.ChargeCode and ACM.CompanyId = RM.CompanyId
left join AutoCharges_Transaction ACT ON ACT.AutoChargeId = ACM.AutoChargeId and ACT.ServiceType = ''HTL''
left join Cancellation_Charge CC ON CC.Reservationid=RM.Reservationid and CC.ProductCode=''HTL''
left join TaxMapping TM ON TM.TaxCode=CC.ChargeCode
where RM.CompanyId = ''ATU'' and HR.Status = ''HK'' and HR.ChkInDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(HR.ChkInDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ReservationRef, ReservationId, ServiceReservationID, MappedRoomCategory, MappedRoomType, MappedMealTypeCode, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant,RoomID,TransPrice,ServiceGrossAmount,SupplierCurrency,ChildPrice1,ChildPrice2,ExtrabedPrice1,Extrabed,ChildTransPrice1,ChildTransPrice2,ExtrabedTransPrice1)
SELECT @CompId, ''BKGLINE'', RM.ReservationRef, RM.ReservationId, HR.HotelReservationId, RCM.OtherMappingCode, RTM.OtherMappingCode,MM.OtherMappingCode,
HRD.NoOfAdults, HRD.NoOfChild, isnull(HRD.NoOfInfant,0) ,HRD.RoomID,
/*HRD.SupplierPrice,*/isNull((isnull(HRD.SupplierPrice,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) TransPrice,
HRD.SupplierPrice,HR.SupplierCurr,isnull(HRC.ChildPrice1,0),isnull(HRC.ChildPrice2,0),isnull(HRC.ExtrabedPrice1,0),
(CASE WHEN HRD.Extrabed = ''YES'' THEN ''1'' WHEN HRD.Extrabed = ''NO'' THEN ''0'' END) AS Extrabed,
isNull((isnull(HRC.ChildPrice1,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) ChildTransPrice1,
isNull((isnull(HRC.ChildPrice2,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) ChildTransPrice2,
isNull((isnull(HRC.ExtrabedPrice1,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) ExtrabedTransPrice1
from Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Hotel_Reservation HR on HR.ReservationId = RM.ReservationId
inner join Hotel_Room_Details HRD ON HR.HotelReservationId = HRD.HotelReservationId
left join Hotel_RoomWise_ChildPrice HRC on HRD.HotelReservationId=HRC.HotelReservationId and HRC.RoomID=HRD.RoomID
inner join RoomCategoryMapping RCM ON RCM.RoomCategoryCode = HRD.RoomCode and RCM.CompanyID = RM.CompanyId
inner join RoomTypeMapping RTM ON RTM.RoomTypeCode = HRD.RoomType and RTM.CompanyID = RM.CompanyId
inner join MealMapping MM ON MM.MealCode = HRD.MealCode and MM.CompanyId = Rm.CompanyId
inner join AgentMapping AM on AM.AgentId = RM.ClientId
inner join (select HotelReservationId, SUM(NoOfRooms) NoOfRooms from Hotel_Room_Details group by HotelReservationId) HRD1 on HRD1.HotelReservationId = HR.HotelReservationId
inner join Hotel_Passenger HP on HP.HotelReservationId = HR.HotelReservationId
inner join Reservation_Passenger RP on RP.PaxId = HP.PaxId and RP.isLeadPax = 1
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join HotelMapping HM ON HM.HotelCode = HR.HotelCode and HM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = HR.CityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = HR.CountryCode and COM.CompanyId = RM.CompanyId
inner join MarketMapping HMM ON HMM.MarketCode = HR.MarketCode and HMM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = HR.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = HR.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode,productcode having TaxCode != ''VAT'' and productcode = ''HTL'' ) RC ON RC.ProductId = HR.HotelReservationId
left join Reservation_Charge TAX ON TAX.ProductId = HR.HotelReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''HTL''
where RM.CompanyId = ''ATU'' AND RI.ProductType = ''HTL'' and HR.Status = ''HK'' and HR.ChkInDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(HR.ChkInDate as date)) < = 2
DELETE #temptbl WHERE RecordType = ''BKGHEAD'' and ServiceReservationID NOT IN (SELECT ServiceReservationID FROM #temptbl WHERE RecordType = ''BKGLINE'')
DELETE #temptbl WHERE RecordType = ''PNRHEAD'' and ReservationId NOT IN (SELECT ReservationId FROM #temptbl WHERE RecordType = ''BKGHEAD'')
-- HOTEL END --
-- TRANSFER START P2P--
INSERT INTO #temptbl (RecordType, ReservationRef, ReservationId, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode,ServiceLeadPax)
SELECT DISTINCT ''PNRHEAD'', RM.ReservationRef, RM.ReservationId, TRP.Channel, AM.OtherMappingCode, ''ATU'', CM.OtherMappingCode, BM.OtherMappedCode, RP.Title + SPACE(1) + RP.FirstName + SPACE(1) + RP.LastName as LeadPaxName
FROM Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Transfer_P2P_Reservation TRP on TRP.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRP.TransferP2PReservationId and OPP.ProductType = ''P2P''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''P2P''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRP.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRP.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRP.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRP.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRP.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''P2P'' ) RC ON RC.ProductId = TRP.TransferP2PReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRP.TransferP2PReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''P2P''
WHERE RM.CompanyId = ''ATU'' AND RI.ProductType = ''P2P'' and TRP.Status = ''HK'' and TRP.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRP.StartDate as date)) < = 3
INSERT INTO #temptbl (CompanyID, RecordType, ServiceType, ReservationRef,ReservationId,ServiceReservationID,BookingDate,BookingDateDisplay,
MappedServiceCode, ServiceStartDate, ServiceEndDate,ServiceDuration,MappedServiceStartCityCode,MappedServiceEndCityCode,MappedServiceCountryCode, MappedServiceMarketCode,
ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo,MappedSupplierCode,SupplierName,
/*SupplierPrice*/ TransPrice, /*SupplierCurrency*/TransCurrency, /*SupplierCurrencyROE*/TransCurrROE,ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount,IPAddress, AgentRef,ComponentCost,DriverSubsistance,
PickUpTime,MaxPax,MaxLuggage,FlightNumber,FlightTime,HandLuggage,HandLuggageQuantity,SplRequest, VATCharge, VATChargeType,TaxCode)
SELECT @CompId, ''BKGHEAD'',''P2P'', RM.ReservationRef, RM.ReservationId, TRP.TransferP2PReservationId, CONVERT(varchar(10), TRP.BookingDate, 105) ,TRP.BookingDate ,
isNull(VM.OtherMappingCode,'''') ServiceCode, CONVERT(varchar(10), TRP.StartDate,105) as ServiceStartDate, CONVERT(varchar(10), TRP.EndDate, 105) as ServiceEndDate,
DATEDIFF(day, TRP.StartDate, TRP.EndDate) AS ServiceDuration,isnull(CIMS.OtherMappingCode,'''') MappedServiceStartCityCode,isnull('''','''') MappedServiceEndCityCode,
isNull(COM.OtherMappingCode,'''') MappedServiceCountryCode,'''' MappedMarketCode, isNull(TRP.VoucherMail,0)VoucherMail, isNull(TRP.VoucherPrint,0) VoucherPrint, isNull(TRP.AdultCount,0) AdultCount, isNull(TRP.ChildCount,0) ChildCount,
isNull(TRP.InfantCount,0) InfantCount, isNull(TRP.ConfirmationNo,''''), isNull(SM.OtherMappingCode,'''') MappedSupplierCode,SUPM.SupplierCompany,
/*isNull(TRP.SupplierPrice,0) SupplierBookedPrice,*/isNull((isnull(TRP.SupplierPrice,0) / isnull(TRP.SupplierCurrROE,0)) * isnull(TRP.TransCurrROE,0),0) TransPrice,
isNull(CM.OtherMappingCode,'''') MappedSupplierCurrency,/* isNull(TRP.SupplierCurrROE,0) SupplierCurrencyROE,*/isNull(TRP.TransCurrROE,0) TransCurrROE,
isNull(isnull(TRP.WSNetAmt,0) + isnull(TAX.GrossAmt,0) + isnull(RC.GrossAmt,0),0) ServiceNetAmount,isnull(TAX.GrossAmt, 0)ServiceVatAmount, isNull(RC.GrossAmt,0)ServiceChargeAmount,
isNull(TRP.SupplierPrice,0) ServiceGrossAmount,''10.0.0.0'' IPAddress, isNull(TRP.AgentRef,'''') AgentRef ,TRP.ComponentCost,TRP.DriverSubsistance,
TRP.PickTime,TRP.MaxPax,TRP.MaxLuggage,TRP.FlightNumber,TRP.FligtTime,TRP.IsHandLuggage,TRP.LuggageQuantity,TRP.SplRequest,ACT.Charge, ACT.ChargeType,isNull(TM.OtherMappingCode,'''') MappedTaxCode
from Reservation_Master RM
inner join Transfer_P2P_Reservation TRP on TRP.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRP.TransferP2PReservationId and OPP.ProductType = ''P2P''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''P2P''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRP.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIMS ON CIMS.CityCode = TRP.StartCityCode and CIMS.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRP.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRP.CCode and SM.CompanyId = RM.CompanyId
inner join Supplier_Master SUPM ON SUPM.SupplierCode= SM.SupplierCode and SUPM.CompanyID=SM.CompanyID
inner join CurrencyMapping CM ON CM.CurrencyCode = TRP.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''P2P'' ) RC ON RC.ProductId = TRP.TransferP2PReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRP.TransferP2PReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''P2P''
left join AutoCharges_Master ACM ON ACM.ChargeCode = TAX.ChargeCode and ACM.CompanyId = RM.CompanyId
left join AutoCharges_Transaction ACT ON ACT.AutoChargeId = ACM.AutoChargeId and ACT.ServiceType = ''P2P''
left join Cancellation_Charge CC ON CC.Reservationid=RM.Reservationid and CC.ProductCode=''P2P''
left join TaxMapping TM ON TM.TaxCode=CC.ChargeCode
where RM.CompanyId = ''ATU'' and TRP.Status = ''HK'' and TRP.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRP.StartDate as date)) < = 3
INSERT INTO #temptbl (CompanyID, RecordType, ReservationRef, ReservationId, ServiceReservationID, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, MappedPickUpPointCode, MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,DateOfTransfer,Distance,PickUpPointName,DropOffPointName,SupplierCurrency,DeadMileage)
SELECT @CompId, ''BKGLINE'', RM.ReservationRef, RM.ReservationId, TRP.TransferP2PReservationId, TRP.AdultCount, TRP.ChildCount, isnull(TRP.InfantCount,0), PMP.OtherMappingCode,PMD.OtherMappingCode, PTMP.OtherMappingCode, PTMD.OtherMappingCode,CONVERT(varchar(10), TPP.TransferDate, 105),
((Select Distance from uattcsnego.TRF_EXC.Point_Mapping TEPM INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP1 ON TEPM.FPOINTID = TEMP1.POINTID INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP2 ON TEPM.TPOINTID = TEMP2.POINTID WHERE(Temp1.PointCode=TPP.PickupPointCode and Temp2.PointCode=TPP.DropOffPointCode ) OR (Temp1.PointCode=TPP.DropOffPointCode and Temp2.PointCode=TPP.PickupPointCode) )) + dbo.fnGetDealMileage(TRP.TransferP2PReservationId,TPP.visitOrder,TPP.PickupPointCode,TPP.DropOffPointCode) AS ''Distance'',
--(Select Distance from uattcsnego.TRF_EXC.Point_Mapping TEPM INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP1 ON TEPM.FPOINTID = TEMP1.POINTID INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP2 ON TEPM.TPOINTID = TEMP2.POINTID WHERE (Temp1.PointCode=TPP.PickupPointCode and Temp2.PointCode=TPP.DropOffPointCode ) OR (Temp1.PointCode=TPP.DropOffPointCode and Temp2.PointCode=TPP.PickupPointCode) ) Distance,
uattcsnego.dbo.getXMLDescription(TRFMST.POINTNAME,''GB'') as PickUpPointName,uattcsnego.dbo.getXMLDescription(TRFDMST.POINTNAME,''GB'') as DropOffPointName,TRP.SupplierCurr,
TRFMST.DIST_FROM_GARAGE from Reservation_Master RM
inner join Transfer_P2P_Reservation TRP on TRP.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRP.TransferP2PReservationId and OPP.ProductType = ''P2P''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''P2P''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRP.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRP.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRP.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRP.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRP.TransCurr and CM.CompanyId = RM.CompanyId
inner join Transfer_P2P_Points TPP ON TPP.TransferP2PReservationId = TRP.TransferP2PReservationId
inner join PointsMapping PMP ON PMP.PointCode = TPP.PickupPointCode and PMP.CompanyId = RM.CompanyId
inner join PointsMapping PMD ON PMD.PointCode = TPP.DropOffPointCode and PMD.CompanyId = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFMST ON TRFMST.PointCode=TPP.PickupPointCode and TRFMST.CompanyCode = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFDMST ON TRFDMST.PointCode=TPP.DropOffPointCode and TRFDMST.CompanyCode = RM.CompanyId
inner join PointTypeMapping PTMP ON PTMP.PointTypeCode = Tpp.PickupPointType and PMP.CompanyId = RM.CompanyId
inner join PointTypeMapping PTMD ON PTMD.PointTypeCode = Tpp.DropOffPointType and PMD.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''P2P'' ) RC ON RC.ProductId = TRP.TransferP2PReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRP.TransferP2PReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''P2P''
where RM.CompanyId = ''ATU'' and TRP.Status = ''HK'' and TRP.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRP.StartDate as date)) < = 3
DELETE #temptbl WHERE RecordType = ''BKGHEAD'' and ServiceReservationID NOT IN (SELECT ServiceReservationID FROM #temptbl WHERE RecordType = ''BKGLINE'')
DELETE #temptbl WHERE RecordType = ''PNRHEAD'' and ReservationId NOT IN (SELECT ReservationId FROM #temptbl WHERE RecordType = ''BKGHEAD'')
--TRANSFER END P2P---
--TRANSFER START MILEAGE--
INSERT INTO #temptbl (RecordType, ReservationRef, ReservationId, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency,
MappedBranchCode, ServiceLeadPax)
SELECT DISTINCT ''PNRHEAD'', RM.ReservationRef, RM.ReservationId, TRM.Channel, AM.OtherMappingCode, ''ATU'', CM.OtherMappingCode,
BM.OtherMappedCode, RP.Title + SPACE(1) + RP.FirstName + SPACE(1) + RP.LastName as LeadPaxName
FROM Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Transfer_Mileage_Reservation TRM on TRM.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRM.TransferMileageReservationId and OPP.ProductType = ''MILEAGE''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''MILEAGE''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRM.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRM.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRM.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRM.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRM.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''MILEAGE'' ) RC ON RC.ProductId = TRM.TransferMileageReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRM.TransferMileageReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''MILEAGE''
WHERE RM.CompanyId = ''ATU'' AND RI.ProductType = ''MILEAGE'' and TRM.Status = ''HK'' and TRM.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRM.StartDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ServiceType, ReservationRef, ReservationId, ServiceReservationID, BookingDate,BookingDateDisplay,
MappedServiceCode, ServiceStartDate, ServiceEndDate,ServiceDuration,MappedServiceStartCityCode, MappedServiceEndCityCode,MappedServiceCountryCode, MappedServiceMarketCode,
ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo,
MappedSupplierCode,SupplierName,/*SupplierPrice*/TransPrice, /*SupplierCurrency*/TransCurrency, /*SupplierCurrencyROE*/TransCurrROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount,
IPAddress, AgentRef,ComponentCost,DriverSubsistance,PerKmCost,TotalBlockSize,PickUpTime,MaxPax,MaxLuggage,FlightNumber,FlightTime,HandLuggage,HandLuggageQuantity,SplRequest, VATCharge, VATChargeType,TaxCode)
SELECT @CompId, ''BKGHEAD'',''MILEAGE'', RM.ReservationRef, RM.ReservationId, TRM.TransferMileageReservationId, CONVERT(varchar(10),
TRM.BookingDate, 105) ,TRM.BookingDate,isNull(VM.OtherMappingCode,'''') ServiceCode, CONVERT(varchar(10), TRM.StartDate,105) as ServiceStartDate, CONVERT(varchar(10), TRM.EndDate, 105) as ServiceEndDate,
DATEDIFF(day, TRM.StartDate, TRM.EndDate) AS ServiceDuration,isnull(CIMS.OtherMappingCode,'''') MappedServiceStartCityCode,isnull(CIME.OtherMappingCode,'''') MappedServiceEndCityCode,
isNull(COM.OtherMappingCode,'''') MappedServiceCountryCode,'''' MappedMarketCode, isNull(TRM.VoucherMail,0) VoucherMail, isNull(TRM.VoucherPrint,0) VoucherPrint, isNull(TRM.AdultCount,0) AdultCount,
isNull(TRM.ChildCount,0) ChildCount, isNull(TRM.InfantCount,0) InfantCount, isNull(TRM.ConfirmationNo,''''),isNull(SM.OtherMappingCode,'''') MappedSupplierCode,SUPM.SupplierCompany,
/*isNull(TRM.SupplierPrice,0) SupplierBookedPrice,*/isNull((isnull(TRM.SupplierPrice,0) / isnull(TRM.SupplierCurrROE,0)) * isnull(TRM.TransCurrROE,0),0) TransPrice,
isNull(CM.OtherMappingCode,'''') MappedSupplierCurrency,/*isNull(TRM.SupplierCurrROE,0) SupplierCurrencyROE,*/isNull(TRM.TransCurrROE,0) TransCurrROE,isNull(isnull(TRM.WSNetAmt,0) + isnull(TAX.GrossAmt,0) + isnull(RC.GrossAmt,0),0) ServiceNetAmount,
isnull(TAX.GrossAmt, 0)ServiceVatAmount, isNull(RC.GrossAmt,0)ServiceChargeAmount,isNull(TRM.SupplierPrice,0) ServiceGrossAmount, ''10.0.0.0'' IPAddress, isNull(TRM.AgentRef,'''') AgentRef,TRM.ComponentCost,TRM.DriverSubsistance,TRM.PerKmCost,TRM.TotalBlockSize,TRM.PickTime,TRM.MaxPax,TRM.MaxLuggage,TRM.FlightNumber,TRM.FligtTime,TRM.IsHandLuggage,TRM.LuggageQuantity,TRM.SplRequest, ACT.Charge, ACT.ChargeType,isNull(TM.OtherMappingCode,'''') MappedTaxCode
from Reservation_Master RM
inner join Transfer_Mileage_Reservation TRM on TRM.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRM.TransferMileageReservationId and OPP.ProductType = ''MILEAGE''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''MILEAGE''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRM.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIMS ON CIMS.CityCode = TRM.StartCityCode and CIMS.CompanyId = RM.CompanyId
inner join CityMapping CIME ON CIME.CityCode = TRM.EndCityCode and CIME.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRM.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRM.CCode and SM.CompanyId = RM.CompanyId
inner join Supplier_Master SUPM ON SUPM.SupplierCode= SM.SupplierCode and SUPM.CompanyID=SM.CompanyID
inner join CurrencyMapping CM ON CM.CurrencyCode = TRM.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''MILEAGE'' ) RC ON RC.ProductId = TRM.TransferMileageReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRM.TransferMileageReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''MILEAGE''
left join AutoCharges_Master ACM ON ACM.ChargeCode = TAX.ChargeCode and ACM.CompanyId = RM.CompanyId
left join AutoCharges_Transaction ACT ON ACT.AutoChargeId = ACM.AutoChargeId and ACT.ServiceType = ''MILEAGE''
left join Cancellation_Charge CC ON CC.Reservationid=RM.Reservationid and CC.ProductCode=''MILEAGE''
left join TaxMapping TM ON TM.TaxCode=CC.ChargeCode
where RM.CompanyId = ''ATU'' and TRM.Status = ''HK'' and TRM.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRM.StartDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ReservationRef, ReservationId, ServiceReservationID, ServiceNoOfAdult, ServiceNoOfChild,
ServiceNoOfInfant, MappedPickUpPointCode, MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,PickUpPointName,DropOffPointName,Distance,SupplierCurrency,DeadMileage)
SELECT @CompId, ''BKGLINE'', RM.ReservationRef, RM.ReservationId, TRM.TransferMileageReservationId, TRM.AdultCount, TRM.ChildCount,
isnull(TRM.InfantCount,0), PMP.OtherMappingCode,PMD.OtherMappingCode, PTMP.OtherMappingCode, PTMD.OtherMappingCode,
uattcsnego.dbo.getXMLDescription(TRFMST.POINTNAME,''GB'') as PickUpPointName,uattcsnego.dbo.getXMLDescription(TRFDMST.POINTNAME,''GB'') as DropOffPointName,
isNull(isnull(TRFMST.DIST_FROM_GARAGE,0) + isnull(TRFDMST.DIST_FROM_GARAGE,0) + isnull(TRM.TotalBlockSize,0),0),TRM.SupplierCurr,isNull(isnull(TRFMST.DIST_FROM_GARAGE,0) + isnull(TRFDMST.DIST_FROM_GARAGE,0),0) as DeadMileage
from Reservation_Master RM
inner join Transfer_Mileage_Reservation TRM on TRM.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRM.TransferMileageReservationId and OPP.ProductType = ''MILEAGE''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''MILEAGE''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRM.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRM.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRM.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRM.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRM.TransCurr and CM.CompanyId = RM.CompanyId
inner join PointsMapping PMP ON PMP.PointCode = TRM.PickUpCode and PMP.CompanyId = RM.CompanyId
inner join PointsMapping PMD ON PMD.PointCode = TRM.EndPointCode and PMD.CompanyId = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFMST ON TRFMST.PointCode=TRM.PickUpCode and TRFMST.CompanyCode = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFDMST ON TRFDMST.PointCode=TRM.EndPointCode and TRFDMST.CompanyCode = RM.CompanyId
inner join PointTypeMapping PTMP ON PTMP.PointTypeCode = TRM.PickType and PMP.CompanyId = RM.CompanyId
inner join PointTypeMapping PTMD ON PTMD.PointTypeCode = TRM.EndPointType and PMD.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''MILEAGE'' ) RC ON RC.ProductId = TRM.TransferMileageReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRM.TransferMileageReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''MILEAGE''
where RM.CompanyId = ''ATU'' and TRM.Status = ''HK'' and TRM.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRM.StartDate as date)) < = 2
DELETE #temptbl WHERE RecordType = ''BKGHEAD'' and ServiceReservationID NOT IN (SELECT ServiceReservationID FROM #temptbl WHERE RecordType = ''BKGLINE'')
DELETE #temptbl WHERE RecordType = ''PNRHEAD'' and ReservationId NOT IN (SELECT ReservationId FROM #temptbl WHERE RecordType = ''BKGHEAD'')
--TRANSFER END MILEAGE---
--Code For Show in grid
-- NOTE : when we add there we have to add in type table.
;WITH CTE AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY ReservationId, RecordType ORDER BY ReservationId, RecordType)''RowRank'' FROM #temptbl)
DELETE FROM CTE WHERE RowRank > 1 and RecordType = ''PNRHEAD''
SELECT NEWID() TCS_UniqueID, RecordType, TTBL.ServiceType, ReservationRef, TTBL.ReservationId, TTBL.ServiceReservationID, BookingDate, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode, ServiceLeadPax, MappedServiceCode, ServiceStartDate, ServiceEndDate, ServiceDuration, MappedServiceStartCityCode, MappedServiceEndCityCode, MappedServiceCountryCode, MappedServiceMarketCode, ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo, MappedSupplierCode, SupplierPrice, SupplierCurrency, SupplierCurrencyROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount, IPAddress, AgentRef, NoOfRoom ,MappedRoomCategory, MappedRoomType, MappedMealTypeCode, PickUpTime, MaxPax, MaxLuggage, FlightNumber, FlightTime, HandLuggage, HandLuggageQuantity, DateOfTransfer, TTBL.MappedPickUpPointCode, TTBL.MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,REPLACE(SplRequest, '','', '''') as SplRequest, VATCharge, VATChargeType from #temptbl TTBL
full outer join Exported_TA ETA
ON TTBL.ReservationId = ETA.ReservationId and TTBL.ServiceReservationID = ETA.ServiceReservationID
where CompanyId = @CompId
and TTBL.RecordType in (''BKGHEAD'',''BKGLINE'',''PNRHEAD'')
and isNull(ETA.isExported,0) = @IsExported
and ( TTBL.ServiceType = CASE WHEN ISNULL(@ServiceType,'''') <> ''ALL'' THEN @ServiceType ELSE TTBL.ServiceType END )
and ( TTBL.ReservationRef = CASE WHEN ISNULL(@ReservationRef,'''') <> '''' THEN @ReservationRef ELSE TTBL.ReservationRef END )
AND 1 = CASE WHEN (ISNULL(@From,'''') = '''' OR ISNULL(@To,'''')='''') THEN 1
WHEN CONVERT(DATE,TTBL.BookingDateDisplay) BETWEEN CONVERT(dATE,@From) AND CONVERT(DATE,@To) THEN 1 ELSE 0 END
ORDER BY TTBL.ReservationId, TTBL.ServiceReservationID, TTBL.RecordType
--Code For Show in grid
-- NOTE : when we add there we have to add in type table.
;WITH CTE1 as (SELECT NEWID() TCS_UniqueID, RecordType, TTBL.ServiceType, ReservationRef, TTBL.ReservationId, TTBL.ServiceReservationID, BookingDate, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode, ServiceLeadPax, MappedServiceCode, ServiceStartDate, ServiceEndDate, ServiceDuration, MappedServiceStartCityCode, MappedServiceEndCityCode, MappedServiceCountryCode, MappedServiceMarketCode, ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo, MappedSupplierCode, SupplierPrice, SupplierCurrency, SupplierCurrencyROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount, IPAddress, AgentRef, NoOfRoom ,MappedRoomCategory, MappedRoomType, MappedMealTypeCode, PickUpTime, MaxPax, MaxLuggage, FlightNumber, FlightTime, HandLuggage, HandLuggageQuantity, DateOfTransfer, TTBL.MappedPickUpPointCode, TTBL.MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,REPLACE(SplRequest, '','', '''') as SplRequest, VATCharge, VATChargeType from #temptbl TTBL
full outer join Exported_TA ETA
ON TTBL.ReservationId = ETA.ReservationId and TTBL.ServiceReservationID = ETA.ServiceReservationID
where CompanyId = @CompId
and TTBL.RecordType in (''BKGHEAD'',''BKGLINE'',''PNRHEAD'')
and isNull(ETA.isExported,0) = @IsExported
and ( TTBL.ServiceType = CASE WHEN ISNULL(@ServiceType,'''') <> ''ALL'' THEN @ServiceType ELSE TTBL.ServiceType END )
and ( TTBL.ReservationRef = CASE WHEN ISNULL(@ReservationRef,'''') <> '''' THEN @ReservationRef ELSE TTBL.ReservationRef END )
AND 1 = CASE WHEN (ISNULL(@From,'''') = '''' OR ISNULL(@To,'''')='''') THEN 1
WHEN CONVERT(DATE,TTBL.BookingDateDisplay) BETWEEN CONVERT(dATE,@From) AND CONVERT(DATE,@To) THEN 1 ELSE 0 END
)
--Code For Insert PNR HEAD ,BKG Head and booking line .
SELECT DISTINCT TTBL.RecordType, TTBL.ServiceType, TTBL.ReservationRef, TTBL.ReservationId,TTBL.HotelName, TTBL.ServiceReservationID, TTBL.BookingDate, TTBL.BookingType, TTBL.MappedAgentCode, TTBL.CompanyID, TTBL.MappedBaseCurrency, TTBL.MappedBranchCode, TTBL.ServiceLeadPax, TTBL.MappedServiceCode, TTBL.ServiceStartDate, TTBL.ServiceEndDate, TTBL.ServiceDuration, TTBL.MappedServiceStartCityCode, TTBL.MappedServiceEndCityCode, TTBL.MappedServiceCountryCode, TTBL.MappedServiceMarketCode, TTBL.ServiceVoucherMailed, TTBL.ServiceVoucherPrint, TTBL.ServiceNoOfAdult, TTBL.ServiceNoOfChild, TTBL.ServiceNoOfInfant, TTBL.SupplierConfirmationNo, TTBL.MappedSupplierCode,TTBL.SupplierName, TTBL.TransCurrency, TTBL.TransCurrROE,TTBL.TransPrice, TTBL.ServiceNetAmount, TTBL.ServiceVatAmount, TTBL.ServiceChargeAmount, TTBL.ServiceGrossAmount as SuppPrice, TTBL.IPAddress, TTBL.AgentRef, TTBL.NoOfRoom ,TTBL.MappedRoomCategory, TTBL.MappedRoomType, TTBL.MappedMealTypeCode, TTBL.PickUpTime, TTBL.MaxPax, TTBL.MaxLuggage, TTBL.FlightNumber, TTBL.FlightTime, TTBL.HandLuggage, TTBL.HandLuggageQuantity, TTBL.DateOfTransfer, TTBL.MappedPickUpPointCode as PPC,TTBL.PickUpPointName as PPN, TTBL.MappedDropOffPointCode as DPC,TTBL.DropOffPointName as DPN, TTBL.MappedPickUpPointType, TTBL.MappedDropOffPointType ,REPLACE(TTBL.SplRequest, '','', '''') as SplRequest,TTBL.ComponentCost,TTBL.DriverSubsistance,TTBL.PerKmCost,TTBL.TotalBlockSize,TTBL.RoomID,TTBL.VATCharge, TTBl.VATChargeType,TTBl.Distance,TTBL.TaxCode,TTBL.SupplierCurrency as SuppCurr,TTBL.ChildPrice1 as ChS1,TTBL.ChildPrice2 as ChS2,TTBL.ExtrabedPrice1 as EBS,TTBL.Extrabed as IsEB,TTBL.ChildTransPrice1 as ChT1,TTBL.ChildTransPrice2 as ChT2,TTBL.ExtrabedTransPrice1 as EBT
from #temptbl TTBL
inner join CTE1 on CTE1.ReservationId = TTBL.ReservationId
WHERE 1= CASE WHEN ISNULL(TTBL.ServiceReservationID,''0'')=0 AND CTE1.ReservationRef=TTBL.ReservationRef THEN 1
WHEN CTE1.ServiceReservationID=TTBL.ServiceReservationID AND CTE1.ReservationRef=TTBL.ReservationRef THEN 1
ELSE 0 END
ORDER BY TTBL.ReservationId, TTBL.ServiceReservationID, TTBL.RecordType
drop table #temptbl;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END '
END
GO
GO
/****** Object: StoredProcedure [dbo].[uspGetBookingsDetailsFor_TA] Script Date: 11/20/2015 16:47:09 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBookingsDetailsFor_TA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetBookingsDetailsFor_TA]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetDealMileage] Script Date: 11/20/2015 16:47:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGetDealMileage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnGetDealMileage]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCityMapping] Script Date: 11/20/2015 16:47:01 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCityMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageCityMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCountryMapping] Script Date: 11/20/2015 16:47:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCountryMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageCountryMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCurrencyMapping] Script Date: 11/20/2015 16:47:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCurrencyMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageCurrencyMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageHotelMapping] Script Date: 11/20/2015 16:47:03 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageHotelMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageHotelMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMarketMapping] Script Date: 11/20/2015 16:47:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMarketMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageMarketMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMealMapping] Script Date: 11/20/2015 16:47:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMealMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageMealMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManagePointMapping] Script Date: 11/20/2015 16:47:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManagePointMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManagePointMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomCategoryMapping] Script Date: 11/20/2015 16:47:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomCategoryMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageRoomCategoryMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomTypeMapping] Script Date: 11/20/2015 16:47:06 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomTypeMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageRoomTypeMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageSupplierMapping] Script Date: 11/20/2015 16:47:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageSupplierMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageSupplierMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageVehicleMapping] Script Date: 11/20/2015 16:47:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageVehicleMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageVehicleMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_TaxMapping] Script Date: 11/20/2015 16:47:08 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TaxMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TaxMapping]
GO
/****** Object: StoredProcedure [dbo].[uspUpdateExportStatus_TA] Script Date: 11/20/2015 16:47:10 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspUpdateExportStatus_TA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspUpdateExportStatus_TA]
GO
/****** Object: StoredProcedure [dbo].[Usp_ManagePointTypeMapping] Script Date: 11/20/2015 16:47:08 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_ManagePointTypeMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_ManagePointTypeMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageAgentMapping] Script Date: 11/20/2015 16:47:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageAgentMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageAgentMapping]
GO
/****** Object: StoredProcedure [dbo].[sp_ManageBranchMapping] Script Date: 11/20/2015 16:47:01 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageBranchMapping]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ManageBranchMapping]
GO
/****** Object: Table [dbo].[VehicleMapping] Script Date: 11/20/2015 16:15:58 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_VechileMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[VehicleMapping] DROP CONSTRAINT [DF_VechileMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VehicleMapping]') AND type in (N'U'))
DROP TABLE [dbo].[VehicleMapping]
GO
/****** Object: Table [dbo].[SupplierMapping] Script Date: 11/20/2015 16:15:36 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SupplierMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SupplierMapping] DROP CONSTRAINT [DF_SupplierMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SupplierMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SupplierMapping] DROP CONSTRAINT [DF_SupplierMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SupplierMapping]') AND type in (N'U'))
DROP TABLE [dbo].[SupplierMapping]
GO
/****** Object: Table [dbo].[TaxMapping] Script Date: 11/20/2015 16:15:47 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxMapping]') AND type in (N'U'))
DROP TABLE [dbo].[TaxMapping]
GO
/****** Object: Table [dbo].[AgentMapping] Script Date: 11/20/2015 16:06:10 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AgentMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[AgentMapping] DROP CONSTRAINT [DF_AgentMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AgentMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[AgentMapping] DROP CONSTRAINT [DF_AgentMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AgentMapping]') AND type in (N'U'))
DROP TABLE [dbo].[AgentMapping]
GO
/****** Object: Table [dbo].[BranchMapping] Script Date: 11/20/2015 16:07:40 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BranchMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BranchMapping] DROP CONSTRAINT [DF_BranchMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BranchMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BranchMapping] DROP CONSTRAINT [DF_BranchMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BranchMapping]') AND type in (N'U'))
DROP TABLE [dbo].[BranchMapping]
GO
/****** Object: Table [dbo].[CityMapping] Script Date: 11/20/2015 16:07:53 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CityMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CityMapping] DROP CONSTRAINT [DF_CityMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CityMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CityMapping] DROP CONSTRAINT [DF_CityMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CityMapping]') AND type in (N'U'))
DROP TABLE [dbo].[CityMapping]
GO
/****** Object: Table [dbo].[CountryMapping] Script Date: 11/20/2015 16:08:04 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CountryMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CountryMapping] DROP CONSTRAINT [DF_CountryMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CountryMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CountryMapping] DROP CONSTRAINT [DF_CountryMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryMapping]') AND type in (N'U'))
DROP TABLE [dbo].[CountryMapping]
GO
/****** Object: Table [dbo].[CurrencyMapping] Script Date: 11/20/2015 16:08:37 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CurrencyMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CurrencyMapping] DROP CONSTRAINT [DF_CurrencyMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CurrencyMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CurrencyMapping] DROP CONSTRAINT [DF_CurrencyMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CurrencyMapping]') AND type in (N'U'))
DROP TABLE [dbo].[CurrencyMapping]
GO
/****** Object: Table [dbo].[Exported_TA] Script Date: 11/20/2015 16:09:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Exported_TA]') AND type in (N'U'))
DROP TABLE [dbo].[Exported_TA]
GO
/****** Object: UserDefinedTableType [dbo].[ExportTableType] Script Date: 11/20/2015 16:48:39 ******/
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ExportTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[ExportTableType]
GO
/****** Object: Table [dbo].[HotelMapping] Script Date: 11/20/2015 16:09:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HotelMapping]') AND type in (N'U'))
DROP TABLE [dbo].[HotelMapping]
GO
/****** Object: Table [dbo].[marketmapping] Script Date: 11/20/2015 16:10:14 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[marketmapping]') AND type in (N'U'))
DROP TABLE [dbo].[marketmapping]
GO
/****** Object: Table [dbo].[MealMapping] Script Date: 11/20/2015 16:10:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MealMapping]') AND type in (N'U'))
DROP TABLE [dbo].[MealMapping]
GO
/****** Object: Table [dbo].[PointsMapping] Script Date: 11/20/2015 16:11:01 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PointsMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PointsMapping] DROP CONSTRAINT [DF_PointsMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PointsMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PointsMapping] DROP CONSTRAINT [DF_PointsMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointsMapping]') AND type in (N'U'))
DROP TABLE [dbo].[PointsMapping]
GO
/****** Object: Table [dbo].[PointTypeMapping] Script Date: 11/20/2015 16:12:11 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointTypeMapping]') AND type in (N'U'))
DROP TABLE [dbo].[PointTypeMapping]
GO
/****** Object: Table [dbo].[RoomCategoryMapping] Script Date: 11/20/2015 16:14:40 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_RoomCategoryMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RoomCategoryMapping] DROP CONSTRAINT [DF_RoomCategoryMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_RoomCategoryMapping_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RoomCategoryMapping] DROP CONSTRAINT [DF_RoomCategoryMapping_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomCategoryMapping]') AND type in (N'U'))
DROP TABLE [dbo].[RoomCategoryMapping]
GO
/****** Object: Table [dbo].[RoomTypeMapping] Script Date: 11/20/2015 16:15:18 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_RoomTypeMapping_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RoomTypeMapping] DROP CONSTRAINT [DF_RoomTypeMapping_CreatedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomTypeMapping]') AND type in (N'U'))
DROP TABLE [dbo].[RoomTypeMapping]
GO
/****** Object: Table [dbo].[RoomTypeMapping] Script Date: 11/20/2015 16:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomTypeMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RoomTypeMapping](
[RoomTypeMappingID] [int] IDENTITY(1,1) NOT NULL,
[RoomTypeCode] [varchar](50) NULL,
[OtherMappingCode] [varchar](50) NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_RoomTypeMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[RoomCategoryMapping] Script Date: 11/20/2015 16:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoomCategoryMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RoomCategoryMapping](
[RoomCategoryMappingID] [int] IDENTITY(1,1) NOT NULL,
[RoomCategoryCode] [varchar](20) NULL,
[OtherMappingCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_RoomCategoryMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_RoomCategoryMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PointTypeMapping] Script Date: 11/20/2015 16:12:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointTypeMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PointTypeMapping](
[PointTypeMappingID] [int] IDENTITY(1,1) NOT NULL,
[PointTypeCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PointsMapping] Script Date: 11/20/2015 16:11:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PointsMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PointsMapping](
[PointsMappingID] [int] IDENTITY(1,1) NOT NULL,
[PointCode] [varchar](50) NOT NULL,
[OtherMappingCode] [varchar](50) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_PointsMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PointsMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[MealMapping] Script Date: 11/20/2015 16:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MealMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MealMapping](
[MealMappingID] [bigint] IDENTITY(1,1) NOT NULL,
[MealCode] [varchar](20) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CompanyId] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[marketmapping] Script Date: 11/20/2015 16:10:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[marketmapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[marketmapping](
[MarketMappingId] [bigint] IDENTITY(1,1) NOT NULL,
[MarketCode] [varchar](20) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[HotelMapping] Script Date: 11/20/2015 16:09:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HotelMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HotelMapping](
[HotelMappingID] [bigint] IDENTITY(1,1) NOT NULL,
[HotelCode] [varchar](20) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: UserDefinedTableType [dbo].[ExportTableType] Script Date: 11/20/2015 16:48:39 ******/
IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ExportTableType' AND ss.name = N'dbo')
CREATE TYPE [dbo].[ExportTableType] AS TABLE(
[TCS_UniqueID] [varchar](50) NULL,
[RecordType] [varchar](10) NULL,
[ServiceType] [varchar](10) NULL,
[ReservationRef] [varchar](100) NULL,
[ReservationId] [bigint] NULL,
[ServiceReservationID] [bigint] NULL,
[BookingDate] [varchar](100) NULL,
[BookingType] [varchar](10) NULL,
[MappedAgentCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NULL,
[MappedBaseCurrency] [varchar](10) NULL,
[MappedBranchCode] [varchar](20) NULL,
[ServiceLeadPax] [varchar](100) NULL,
[MappedServiceCode] [varchar](20) NULL,
[ServiceStartDate] [varchar](100) NULL,
[ServiceEndDate] [varchar](100) NULL,
[ServiceDuration] [int] NULL,
[MappedServiceStartCityCode] [varchar](100) NULL,
[MappedServiceEndCityCode] [varchar](100) NULL,
[MappedServiceCountryCode] [varchar](10) NULL,
[MappedServiceMarketCode] [varchar](10) NULL,
[ServiceVoucherMailed] [bit] NULL,
[ServiceVoucherPrint] [bit] NULL,
[ServiceNoOfAdult] [int] NULL,
[ServiceNoOfChild] [int] NULL,
[ServiceNoOfInfant] [int] NULL,
[SupplierConfirmationNo] [varchar](100) NULL,
[MappedSupplierCode] [varchar](10) NULL,
[SupplierPrice] [money] NULL,
[SupplierCurrency] [varchar](10) NULL,
[SupplierCurrencyROE] [decimal](18, 8) NULL,
[ServiceNetAmount] [money] NULL,
[ServiceVatAmount] [money] NULL,
[ServiceChargeAmount] [money] NULL,
[ServiceGrossAmount] [money] NULL,
[IPAddress] [varchar](100) NULL,
[AgentRef] [varchar](30) NULL,
[NoOfRoom] [int] NULL,
[MappedRoomCategory] [varchar](500) NULL,
[MappedRoomType] [varchar](500) NULL,
[MappedMealTypeCode] [varchar](500) NULL,
[PickUpTime] [varchar](20) NULL,
[MaxPax] [int] NULL,
[MaxLuggage] [int] NULL,
[FlightNumber] [varchar](20) NULL,
[FlightTime] [varchar](20) NULL,
[HandLuggage] [varchar](5) NULL,
[HandLuggageQuantity] [int] NULL,
[DateOfTransfer] [varchar](500) NULL,
[MappedPickUpPointCode] [varchar](500) NULL,
[MappedDropOffPointCode] [varchar](500) NULL,
[MappedPickUpPointType] [varchar](500) NULL,
[MappedDropOffPointType] [varchar](500) NULL,
[SplRequest] [varchar](500) NULL,
[VATCharge] [int] NULL,
[VATChargeType] [char](1) NULL
)
GO
/****** Object: Table [dbo].[Exported_TA] Script Date: 11/20/2015 16:09:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Exported_TA]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Exported_TA](
[ExportId] [int] IDENTITY(1,1) NOT NULL,
[ReservationId] [bigint] NOT NULL,
[ServiceReservationId] [bigint] NOT NULL,
[ServiceType] [varchar](10) NOT NULL,
[IsExported] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL,
[ModifyBy] [int] NULL,
[ModifiedDate] [datetime] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CurrencyMapping] Script Date: 11/20/2015 16:08:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CurrencyMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CurrencyMapping](
[CurrencyMappingID] [int] IDENTITY(1,1) NOT NULL,
[CurrencyCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_CurrencyMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_CurrencyMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CountryMapping] Script Date: 11/20/2015 16:08:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CountryMapping](
[CountryMappingID] [int] IDENTITY(1,1) NOT NULL,
[CountryCode] [varchar](10) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_CountryMapping_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [bigint] NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_CountryMapping_ModifiedDate] DEFAULT (getdate()),
[CompanyID] [varchar](5) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CityMapping] Script Date: 11/20/2015 16:07:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CityMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CityMapping](
[CityMappingID] [int] IDENTITY(1,1) NOT NULL,
[CityCode] [varchar](15) NOT NULL,
[OtherMappingCode] [varchar](20) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_CityMapping_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [bigint] NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_CityMapping_ModifiedDate] DEFAULT (getdate()),
[CompanyID] [varchar](5) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[BranchMapping] Script Date: 11/20/2015 16:07:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BranchMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BranchMapping](
[BranchMappingID] [int] IDENTITY(1,1) NOT NULL,
[BranchID] [bigint] NOT NULL,
[OtherMappedCode] [varchar](20) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_BranchMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [date] NULL CONSTRAINT [DF_BranchMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[AgentMapping] Script Date: 11/20/2015 16:06:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AgentMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AgentMapping](
[AgentMappingID] [int] IDENTITY(1,1) NOT NULL,
[AgentID] [bigint] NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_AgentMapping_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [bigint] NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_AgentMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TaxMapping] Script Date: 11/20/2015 16:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TaxMapping](
[TaxMappingID] [bigint] IDENTITY(1,1) NOT NULL,
[TaxCode] [varchar](20) NULL,
[OtherMappingCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NULL,
[CreatedBy] [bigint] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedBy] [bigint] NULL,
[ModifiedDate] [datetime] NULL,
CONSTRAINT [PK_TaxMapping] PRIMARY KEY CLUSTERED
(
[TaxMappingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SupplierMapping] Script Date: 11/20/2015 16:15:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SupplierMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SupplierMapping](
[SupplierMappingID] [int] IDENTITY(1,1) NOT NULL,
[SupplierCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](20) NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_SupplierMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SupplierMapping_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[VehicleMapping] Script Date: 11/20/2015 16:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VehicleMapping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[VehicleMapping](
[VehicleMappingID] [int] IDENTITY(1,1) NOT NULL,
[VehicleCode] [varchar](5) NOT NULL,
[OtherMappingCode] [varchar](10) NOT NULL,
[CompanyID] [varchar](5) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_VechileMapping_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[sp_ManageBranchMapping] Script Date: 11/20/2015 16:47:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageBranchMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageBranchMapping]
@BranchMappingID Bigint =0,
@BranchID Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.BranchID, tbl.BranchName,ISNULL(BranchMappingId,0)BranchMappingId,
IsnUll(b.BranchID,'''') BranchID,ISNULL(OtherMappedCode,'''')OtherMappingCode from (select DISTINCT BranchID,BranchName from Company_Branch Where BranchID !='''' ANd BranchID !=''0'' and CompanyId=@CompanyID)tbl
Left Outer join BranchMapping b
ON b.BranchID=tbl.BranchID
Order By tbl.BranchName
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM BranchMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappedCode=@OtherMappingCode)
BEGIN
If @BranchMappingID =0 BEGIN
INSERT INTO BranchMapping (BranchID,OtherMappedCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@BranchID,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE BranchMapping SET BranchID=@BranchID,OtherMappedCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE BranchMappingID=@BranchMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping Branch.''
END
ELSE BEGIN
SET @Result=''Branch code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Branch.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageAgentMapping] Script Date: 11/20/2015 16:47:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageAgentMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageAgentMapping]
@AgentMappingID Bigint =0,
@AgentID Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
SELECT
CM.ClientId, SB.BranchId as SABranchID, SB.BranchName SABranchName,
CB.BranchName, CM.BranchId as CompanyBranchId, SM.AgencyName, CM.CityName,
CM.PhoneNo, CM.Email, SM.AgencyLogo,
case when CM.[Status]=1 then ''Active'' else ''Inactive'' end as [Status],
CASE WHEN LEN(SM.AgencyName)>20 THEN SUBSTRING(SM.AgencyName,1,18)+''..'' ELSE SM.AgencyName END AS ShortAgencyName,
SM.AgencyCurr, SM.IsCorporate,CONVERT(varchar(50),CM.CreatedOn,105)CreatedOn,CONVERT(varchar(50),CM.ModifyOn,105)ModifyOn,
(case when ISNULL(sm.IsCorporate, 0) = ''0'' then ''B2B'' else ''Corporate'' end) AgencyType,SM.PANNo
INTO #Results
from dbo.Client_Master CM
inner join Company_Branch CB on CB.BranchId=CM.BranchId
inner join Subagent_Master SM on SM.ClientId=CM.ClientId
INNER JOIN (select SM.ClientId, MIN(SB.BranchId) BranchId from Subagent_Master SM
inner join Subagent_Branch SB on SB.AgentId=SM.ClientId
group by SM.ClientId
)SM1 on SM1.ClientId=SM.ClientId
inner join Subagent_Branch SB on SB.BranchId=SM1.BranchId
where CM.CompanyId=@CompanyID and cm.Type=''SA''
Select * from #Results tmpR
left outer join AgentMapping AM on AM.AgentID=tmpR.ClientId
Drop table #Results
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM AgentMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @AgentMappingID =0 BEGIN
INSERT INTO AgentMapping (AgentID,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@AgentID,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE AgentMapping SET AgentID=@AgentID,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE AgentMappingID=@AgentMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping Agent.''
END
ELSE BEGIN
SET @Result=''Agent code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Agent.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[Usp_ManagePointTypeMapping] Script Date: 11/20/2015 16:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_ManagePointTypeMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_ManagePointTypeMapping]
@PointTypeMappingID int =0,
@PointTypeCode Varchar(10)='''',
@OtherMappingCode Varchar(50)='''',
@CompanyID varchar(5)='''',
@CreatedBy bigint=0,
@CreatedDate datetime='''',
@ModifiedBy int=0,
@ModifiedDate datetime='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL ROOM TYPE INFORMATION
--==============================================================================================================
IF @QueryNo=1
BEGIN
Select tbl.PointCode as PointTypeCode, tbl.PointName as PointTypeName,ISNULL(PointTypeMappingID,0)PointTypeMappingID,
IsnUll(b.PointTypeCode,'''') VehicleCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT PointCode,PointName from uattcsnego..Trf_Nego_TransferPoints Where PointCode !='''' ANd PointCode !=''0'')tbl
Left Outer join PointTypeMapping b
ON b.PointTypeCode=tbl.PointCode
Order By tbl.PointName
END
ELSE
IF @QueryNo=2
BEGIN
IF NOT EXISTS (SELECT 1 FROM PointTypeMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @PointTypeMappingID =0 BEGIN
INSERT INTO PointTypeMapping (PointTypeCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate)
VALUES(@PointTypeCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE(),@ModifiedBy,GETDATE())
END
ELSE BEGIN
UPDATE PointTypeMapping SET PointTypeCode=@PointTypeCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE PointTypeMappingID=@PointTypeMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Point code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Point.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[uspUpdateExportStatus_TA] Script Date: 11/20/2015 16:47:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspUpdateExportStatus_TA]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:<Author: Ajay Agrahari>
-- Create date: <Create Date,24 Nov 2014>
-- Description: <[SP_InsertExportDataFor_TA]>
-- =============================================
CREATE procedure [dbo].[uspUpdateExportStatus_TA]
(
@Table ExportTableType READONLY,
@CreatedBy int,
@ModifiedBy int,
@isExported int
)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
MERGE Exported_TA AS ETA
USING (select ReservationId, ServiceReservationId, ServiceType from @Table) AS ETT
ON ETT.ReservationId = ETA.ReservationId and ETT.ServiceReservationId = ETA.ServiceReservationId
WHEN MATCHED THEN
UPDATE SET ETA.IsExported = @isExported, ETA.ModifyBy = @ModifiedBy, ETA.ModifiedDate = getdate()
WHEN NOT MATCHED THEN
INSERT(ReservationId, ServiceReservationId, ServiceType, IsExported, IsDeleted, CreatedBy, CreatedDate, ModifyBy, ModifiedDate)
VALUES(ETT.ReservationId, ETT.ServiceReservationId, ETT.ServiceType, @isExported, 0, @CreatedBy, Getdate(), @ModifiedBy, Getdate());
COMMIT TRANSACTION
SELECT 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 0
END CATCH
end
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_TaxMapping] Script Date: 11/20/2015 16:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TaxMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Ajay Agrahari
-- Create date: May 16 2015
-- Modified By: Ajay Agrahari
-- Modified Date : May 16 2015
-- Description: This stored procedure used to Tax Mapping
-- =============================================================================================
create PROCEDURE [dbo].[sp_TaxMapping]
@TaxMappingID Bigint =0,
@TaxCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get tax Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.TaxCode, tbl.TaxName,ISNULL(TaxMappingId,0)TaxMappingId,
IsnUll(b.TaxCode,'''') TaxCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT TaxCode,TaxName from uattcsnego..MstTax Where TaxCode !='''' ANd TaxCode !=''0'' and CompanyCode=@CompanyID)tbl
Left Outer join TaxMapping b
ON b.TaxCode=tbl.TaxCode
Order By tbl.TaxName
END
--======================================================================================================================
-- #Query No#2 Save Tax Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM TaxMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @TaxMappingID =0 BEGIN
INSERT INTO TaxMapping (TaxCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@TaxCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE TaxMapping SET TaxCode=@TaxCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE TaxMappingID=@TaxMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in Tax Mapping.''
END
ELSE BEGIN
SET @Result=''Tax code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Tax.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageVehicleMapping] Script Date: 11/20/2015 16:47:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageVehicleMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageVehicleMapping]
@VehicleMappingID Bigint =0,
@VehicleCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.VehicleCode, tbl.VehicleName,ISNULL(VehicleMappingId,0)VehicleMappingId,
IsnUll(b.VehicleCode,'''') VehicleCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT VehicleCode,uattcsnego.dbo.getXMLDescription(VEHICLETYPE,''GB'') as VehicleName from uattcsnego.TRF_EXC.MstVehicle Where VehicleCode !='''' ANd VehicleCode
!=''0'' and CompanyCode=@CompanyID)tbl
Left Outer join VehicleMapping b
ON b.VehicleCode=tbl.VehicleCode
Order By tbl.VehicleName
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM VehicleMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @VehicleMappingId =0 BEGIN
INSERT INTO VehicleMapping (VehicleCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@VehicleCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE VehicleMapping SET VehicleCode=@VehicleCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE VehicleMappingId=@VehicleMappingId AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Vehicle code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other vehicle.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageSupplierMapping] Script Date: 11/20/2015 16:47:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageSupplierMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Kapil Dev Tripathi>
-- Create date: <JAN 13 2014>
-- Modified By: Ajay Agrahari
-- Modified Date : 19 Dec 2014
-- Description: <This sp manage mappings of suppliers>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageSupplierMapping]
@SupplierCode Varchar(10)='''',
@OtherMappingCode Varchar(10)='''',
@SupplierMappingID int =0,
@CreatedBy bigint=0,
@CompanyID varchar(5)='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL SUPPLIER INFORMATION ON BASE OF COMPANY
--==============================================================================================================
IF @QueryNo=1 BEGIN
Select DISTINCT SupplierId,a.SupplierCode,a.SupplierCompany,a.CompanyId,ISNULL(SupplierMappingId,0)SupplierMappingId,
ISNULL(b.OtherMappingcode,'''') OtherMappingcode from Supplier_Master a
Left Outer join SupplierMapping b
on a.SupplierCode =b.SupplierCode and a.CompanyID=b.CompanyID
Where a.CompanyId =@CompanyID AND a.SupplierCode Is NOT NULL
END
--==============================================================================================================
-- #QUERY No#2- MAPPED SUPPLIER # HALF
--==============================================================================================================
ELSE IF @QueryNo=2 BEGIN
--IF NOT EXISTS (SELECT * FROM Supplier_Master With(NoLock) Where ((SupplierCode=@SupplierCode) OR (OtherMappingCode=@OtherMappingCode)) and CompanyID=@CompanyID ) BEGIN
--IF NOT EXISTS (SELECT * FROM Supplier_Master With(NoLock) WHERE SupplierCode=@SupplierCode and CompanyID=@CompanyID ) BEGIN
IF NOT EXISTS (SELECT 1 FROM SupplierMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @SupplierMappingID =0 BEGIN
INSERT INTO SupplierMapping (SupplierCode,OtherMappingCode,CreatedBy,CreatedDate,CompanyID) VALUES(@SupplierCode,@OtherMappingCode,@CreatedBy,GETDATE(),@CompanyID)
--UPDATE Supplier_Master SET OtherMappingCode=@OtherMappingCode WHERE SupplierCode=@SupplierCode AND CompanyID=@CompanyID
END
ELSE BEGIN
UPDATE SupplierMapping SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE() WHERE SupplierMappingID=@SupplierMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping.''
END
ELSE BEGIN
SET @Result=''Supplier has been mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other supplier.''
END
--END
--ELSE BEGIN
-- SET @Result=''This mapping code already exist for other supplier.''
--END
Select @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomTypeMapping] Script Date: 11/20/2015 16:47:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomTypeMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-------------------------
-- =============================================
-- Author: Amit Singh
-- Create date: <DEC 19 2014>
-- Description: <This sp manage mappings of Room Types>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageRoomTypeMapping]
@RoomTypeMappingID int =0,
@RoomTypeCode Varchar(10)='''',
@OtherMappingCode Varchar(50)='''',
@CompanyID varchar(5)='''',
@CreatedBy bigint=0,
@CreatedDate datetime='''',
@ModifiedBy int=0,
@ModifiedDate datetime='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL ROOM TYPE INFORMATION
--==============================================================================================================
IF @QueryNo=1
BEGIN
SELECT RoomTypeMappingID,RoomTypeCode,OtherMappingCode FROM RoomTypeMapping WITH (NOLOCK) WHERE CompanyID=@CompanyID ORDER BY CreatedDate DESC
END
ELSE
IF @QueryNo=2
BEGIN
IF NOT EXISTS (SELECT 1 FROM RoomTypeMapping With(NoLock)
WHERE RoomTypeMappingID <> @RoomTypeMappingID AND CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
UPDATE RoomTypeMapping
SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@ModifiedBy,ModifiedDate=GETDATE()
WHERE RoomTypeMappingID=@RoomTypeMappingID AND CompanyID=@CompanyID
set @Result=''Room Type has been mapped successfully.''
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Room Type.''
END
Select @Result
END
END'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageRoomCategoryMapping] Script Date: 11/20/2015 16:47:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageRoomCategoryMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageRoomCategoryMapping]
@RoomCategoryMappingID Bigint =0,
@RoomCategoryCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Meal Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.RoomCategoryCode, tbl.RoomCategoryDesc,ISNULL(RoomCategoryMappingId,0)RoomCategoryMappingId,
IsnUll(b.RoomCategoryCode,'''') RoomCategoryCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT RoomCategoryCode,RoomCategoryDesc from uattcsnego..MstRoomCategory Where RoomCategoryCode !='''' ANd RoomCategoryCode !=''0'' )tbl
Left Outer join RoomCategoryMapping b
ON b.RoomCategoryCode=tbl.RoomCategoryCode
Order By tbl.RoomCategoryDesc
END
--======================================================================================================================
-- #Query No#2 Save Meal Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM RoomCategoryMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @RoomCategoryMappingID =0 BEGIN
INSERT INTO RoomCategoryMapping (RoomCategoryCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@RoomCategoryCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE RoomCategoryMapping SET RoomCategoryCode=@RoomCategoryCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE RoomCategoryMappingID=@RoomCategoryMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Room Category code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Room Category.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManagePointMapping] Script Date: 11/20/2015 16:47:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManagePointMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManagePointMapping]
@PointsMappingID Bigint =0,
@PointCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Vehicle Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.PointCode, tbl.PointName,ISNULL(PointsMappingId,0)PointsMappingId,
IsnUll(b.PointCode,'''') VehicleCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT PointCode,uattcsnego.dbo.getXMLDescription(POINTNAME,''GB'') as PointName from uattcsnego.TRF_EXC.MstPoint Where PointCode !='''' ANd PointCode !=''0'' and CompanyCode=@CompanyID)tbl
Left Outer join PointsMapping b
ON b.PointCode=tbl.PointCode
Order By tbl.PointName
END
--======================================================================================================================
-- #Query No#2 Save Vehicle Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM PointsMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @PointsMappingID =0 BEGIN
INSERT INTO PointsMapping (PointCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@PointCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE PointsMapping SET PointCode=@PointCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE PointsMappingID=@PointsMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Point code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Point.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMealMapping] Script Date: 11/20/2015 16:47:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMealMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Modified By: Ajay Agrahari
-- Modified Date : DEC 19 2014
-- Description: This stored procedure used to map Meal Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageMealMapping]
@MealMappingID Bigint =0,
@MealCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Meal Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.MealCode, tbl.MealDesc,ISNULL(MealMappingId,0)MealMappingId,
IsnUll(b.MealCode,'''') MealCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT MealCode,MealDesc from Hotel_Room_Details Where MealCode !='''' ANd MealCode !=''0'' )tbl
Left Outer join MealMapping b
ON b.MealCode=tbl.MealCode
Order By tbl.MealDesc
END
--======================================================================================================================
-- #Query No#2 Save Meal Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM MealMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @MealMappingID =0 BEGIN
INSERT INTO MealMapping (MealCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@MealCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE MealMapping SET MealCode=@MealCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE MealMappingID=@MealMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Meal code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Meal.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageMarketMapping] Script Date: 11/20/2015 16:47:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageMarketMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Ajay Agrahari
-- Create date: 19 Dec 2014
-- Description: This stored procedure used to map Currency Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageMarketMapping]
@MarketMappingId Bigint =0,
@MarketCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Market Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.RateCode, tbl.RateName,ISNULL(MarketMappingId,0)MarketMappingId,
IsnUll(b.MarketCode,'''') MarketCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT RateCode,RateName from uattcsnego..MstRate Where RateCode !='''' ANd RateCode !=''0'' and CompanyCode=@CompanyID )tbl
Left Outer join marketmapping b
ON b.MarketCode=tbl.RateCode
Order By tbl.RateName
END
--======================================================================================================================
-- #Query No#2 Save market Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM marketmapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @MarketMappingId =0 BEGIN
INSERT INTO marketmapping (MarketCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@MarketCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE marketmapping SET MarketCode=@MarketCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE MarketMappingId=@MarketMappingId AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Market code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Market.''
END
SELECT @Result as Result
END
END '
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageHotelMapping] Script Date: 11/20/2015 16:47:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageHotelMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Kapil Dev Tripathi
-- Create date: JAN 17 2014
-- Description: This stored procedure used to map hotel code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageHotelMapping]
@HotelMappingID bigint=0,
@HotelCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Hotel Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
SELECT DISTINCT a.HotelCode,a.HotelName,ISNULL(HotelMappingID,0)HotelMappingID,ISNULL(b.HotelCode,'''')MapHotelCode
,IsNULL(OtherMappingCode,'''')OtherMappingCode FROM uattcsnego..MstHotel a With(NoLock)
Left Outer Join HotelMapping b ON a.HotelCode =b.HotelCode AND b.CompanyID=@CompanyID
Order By a.HotelName
END
--======================================================================================================================
-- #Query No#2 Save Hotel Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM HotelMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @HotelMappingID =0 BEGIN
INSERT INTO HotelMapping (HotelCode,OtherMappingCode,CompanyID,CreatedBy,CreatedDate)
VALUES(@HotelCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE HotelMapping SET HotelCode=@HotelCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE HotelMappingID=@HotelMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping branch.''
END
ELSE BEGIN
SET @Result=''Hotel code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other Hotel.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCurrencyMapping] Script Date: 11/20/2015 16:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCurrencyMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================================================================
-- Author: Ajay Agrahari
-- Create date: 19 Dec 2014
-- Description: This stored procedure used to map Currency Code
-- =============================================================================================
CREATE PROCEDURE [dbo].[sp_ManageCurrencyMapping]
@CurrencyMappingID Bigint =0,
@CurrencyCode Varchar(20)='''',
@OtherMappingCode Varchar(20)='''',
@CompanyID Varchar(5)='''',
@CreatedBy bigint=0,
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result as Varchar(200)=''''
--======================================================================================================================
-- #Query No#1 Get Meal Code mapping information
--======================================================================================================================
IF @QueryNo=1 BEGIN
Select tbl.CurrencyCode, tbl.CurrencyName,ISNULL(CurrencyMappingId,0)CurrencyMappingId,
IsnUll(b.CurrencyCode,'''') MealCode,ISNULL(OtherMappingCode,'''')OtherMappingCode from (select DISTINCT CurrencyCode,CurrencyName from Currency_Master Where CurrencyCode !='''' ANd CurrencyCode !=''0'' )tbl
Left Outer join CurrencyMapping b
ON b.CurrencyCode=tbl.CurrencyCode Order By tbl.CurrencyName
END
--======================================================================================================================
-- #Query No#2 Save Meal Map Information
--======================================================================================================================
ELSE IF @QueryNo=2 BEGIN
IF NOT EXISTS (SELECT 1 FROM CurrencyMapping With(NoLock)
WHERE CompanyID=@CompanyID AND OtherMappingCode=@OtherMappingCode)
BEGIN
If @CurrencyMappingId =0 BEGIN
INSERT INTO CurrencyMapping (CurrencyCode,OtherMappingCode,CompanyId,CreatedBy,CreatedDate)
VALUES(@CurrencyCode,@OtherMappingCode,@CompanyID,@CreatedBy,GETDATE())
END
ELSE BEGIN
UPDATE CurrencyMapping SET CurrencyCode=@CurrencyCode,OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE()
WHERE CurrencyMappingID=@CurrencyMappingID AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping meal.''
END
ELSE BEGIN
SET @Result=''Currency code mapped successfully.''
END
END
ELSE
BEGIN
select @Result=''This mapping code already exist for other currency.''
END
SELECT @Result as Result
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCountryMapping] Script Date: 11/20/2015 16:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCountryMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Kapil Dev Tripathi>
-- Create date: <JAN 13 2014>
-- Description: <This sp manage mappings of countries>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageCountryMapping]
@CountryCode Varchar(10)='''',
@OtherMappingCode Varchar(10)='''',
@CountryMappingID int =0,
@CreatedBy bigint=0,
@CompanyID varchar(5)='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL COUNTRY INFORMATION
--==============================================================================================================
IF @QueryNo=1 BEGIN
Select Distinct Isnull(CountryMappingID,0) CountryMappingID,a.CountryCode,CountryName,IsNull(b.OtherMappingCode,'''') as OtherMappingCode from st_Country a With(NoLock)
Left Outer Join CountryMapping b With(NoLock)
ON a.CountryCode =b.CountryCode and b.CompanyID=@CompanyID Order By CountryName
END
--==============================================================================================================
-- #QUERY No#2- SELECT ALL COUNTRY INFORMATION WHICH IS NOT MAPPED
--==============================================================================================================
ELSE IF @QueryNo=2 BEGIN
Select Isnull(CountryMappingID,0) CountryMappingID,a.CountryCode,CountryName,IsNull(b.OtherMappingCode,'''') as OtherMappingCode From
(Select a.CountryCode,CountryName from st_Country a With(NoLock)
Where a.CountryCode Not In (Select CountryCode From CountryMapping WHERE CompanyID=@CompanyID ) )a Left Outer join
CountryMapping b With(NoLock)
ON a.CountryCode =b.CountryCode and b.CompanyID=@CompanyID Order By CountryName
END
--==============================================================================================================
-- #QUERY No#3- MAPPED COUNTRY INFORMATION
--==============================================================================================================
ELSE IF @QueryNo=3 BEGIN
IF NOT EXISTS (SELECT * FROM CountryMapping With(NoLock) Where ((@CountryMappingID=0 AND CountryCode=@CountryCode) OR (CountryMappingID!=@CountryMappingID AND OtherMappingCode=@OtherMappingCode)) and CompanyID=@CompanyID ) BEGIN
IF NOT EXISTS (SELECT * FROM CountryMapping With(NoLock) WHERE CountryCode=@CountryCode and CompanyID=@CompanyID ) BEGIN
INSERT INTO CountryMapping (CountryCode,OtherMappingCode,CreatedBy,CreatedDate,CompanyID) VALUES(@CountryCode,@OtherMappingCode,@CreatedBy,GETDATE(),@CompanyID)
END
ELSE BEGIN
UPDATE CountryMapping SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,ModifiedDate=GETDATE() WHERE CountryCode=@CountryCode and CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping.''
END
ELSE BEGIN
SET @Result=''Country has been mapped successfully.''
END
END
ELSE BEGIN
SET @Result=''This mapping code already exist for other country.''
END
Select @Result as Result
END
--==============================================================================================================
-- #QUERY No#4- SELECT ALL COUNTRY
--==============================================================================================================
ELSE IF @QueryNo=4 BEGIN
Select Distinct a.CountryCode,a.CountryName from st_Country a With(NoLock) Order By CountryName
END
END
'
END
GO
/****** Object: StoredProcedure [dbo].[sp_ManageCityMapping] Script Date: 11/20/2015 16:47:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ManageCityMapping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Kapil Dev Tripathi>
-- Create date: <JAN 13 2014>
-- Description: <Manage mapping of cities>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManageCityMapping]
@CityCode Varchar(15)='''',
@OtherMappingCode Varchar(15)='''',
@CityMappingId int=0,
@CountryCode varchar(10)='''',
@CreatedBy bigint=0,
@CompanyID varchar(5)='''',
@QueryNo int=0
AS
BEGIN
SET NOCOUNT ON;
Declare @Result Varchar(200)=''''
--==============================================================================================================
-- #QUERY No#1- SELECT ALL CITIES OF PARTICULAR COUNTRY
--==============================================================================================================
IF @QueryNo=1 BEGIN
Select DISTINCT tbl.CityCode,tbl.CityName,tbl.CountryCode,ISNULL(CityMappingID,0)CityMappingID,
Isnull(OtherMappingCode,'''') as OtherMappingCode from (Select a.CityCode,b.CityName,a.CountryCode from st_City a WITH(NOLOCK)
Inner Join st_CityName b WITH(NOLOCK) on a.CityCode =b.CityCode AND a.CountryCode= @CountryCode)tbl Left Join CityMapping d WITH(NOLOCK)
On d.CityCode=tbl.Citycode AND d.CompanyId=@CompanyID Order By CityName
END
--==============================================================================================================
-- #QUERY No#2- SELECT ALL CITIES
--==============================================================================================================
ELSE IF @QueryNo=2 BEGIN
Select DISTINCT tbl.CityCode,tbl.CityName,tbl.CountryCode,ISNULL(CityMappingID,0)CityMappingID,
Isnull(OtherMappingCode,'''') as OtherMappingCode from (Select a.CityCode,b.CityName,a.CountryCode from st_City a WITH(NOLOCK)
Inner Join st_CityName b WITH(NOLOCK) on a.CityCode =b.CityCode )tbl Left Join CityMapping d WITH(NOLOCK)
On d.CityCode=tbl.Citycode AND d.CompanyId=@CompanyID Order By CityName
END
--==============================================================================================================
-- #QUERY No#3- MAPPED CITY
--==============================================================================================================
ELSE IF @QueryNo=3 BEGIN
IF NOT EXISTS (SELECT * FROM CityMapping With(NoLock) Where ((@CityMappingID=0 AND CityCode=@CityCode) OR (CityMappingID!=@CityMappingID AND OtherMappingCode=@OtherMappingCode))AND CompanyID=@CompanyID ) BEGIN
IF NOT EXISTS (SELECT * FROM CityMapping With(NoLock) WHERE CityCode=@CityCode AND CompanyID=@CompanyID) BEGIN
INSERT INTO CityMapping (CityCode,OtherMappingCode,CreatedBy,CreatedDate,CompanyID)
VALUES(@CityCode,@OtherMappingCode,@CreatedBy,GETDATE(),@CompanyID)
END
ELSE BEGIN
UPDATE CityMapping SET OtherMappingCode=@OtherMappingCode,ModifiedBy=@CreatedBy,Modifieddate=GETDATE()
WHERE CityCode=@CityCode AND CompanyID=@CompanyID
END
IF @@ERROR <>0 BEGIN
SET @Result=''Problem in mapping city.''
END
ELSE BEGIN
SET @Result=''City has been mapped successfully.''
END
END
ELSE BEGIN
SET @Result=''This mapping code already exist for other city.''
END
Select @Result as Result
END
END
'
END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetDealMileage] Script Date: 11/20/2015 16:47:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGetDealMileage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
-- =============================================
-- Author: Ajay
-- Create date: July 12 2015
-- Description: get deal mileage
-- =============================================
/*
Select * from Transfer_P2P_Points Where TransferP2PReservationId=''313''
select dbo.fnGetDealMileage(13,1,''PT4'',''PT26'')
select dbo.fnGetDealMileage(313,2,''PT18'',''PT55'')
select dbo.fnGetDealMileage(313,3,''PT55'',''PT18'')
*/
CREATE FUNCTION [dbo].[fnGetDealMileage]
(
@TransferP2PReservationId INT,
@VisitOrder INT,
@PickupPointCode varchar(10),
@DropOffPointCode varchar(10)
)
RETURNS INT
AS
BEGIN
DECLARE @Distance INT =0
IF (SELECT MIN(visitOrder) FROM Transfer_P2P_Points(NOLOCK) WHERE TransferP2PReservationId=@TransferP2PReservationId)=@VisitOrder
BEGIN
SET @Distance=(SELECT DIST_FROM_GARAGE FROM uattcsnego.TRF_EXC.MstPoint(NOLOCK) where PointCode=@PickupPointCode)
END
IF (SELECT MAX(visitOrder) FROM Transfer_P2P_Points(NOLOCK) WHERE TransferP2PReservationId=@TransferP2PReservationId) =@VisitOrder
BEGIN
SET @Distance +=(SELECT DIST_FROM_GARAGE FROM uattcsnego.TRF_EXC.MstPoint(NOLOCK) where PointCode=@DropOffPointCode)
END
RETURN isnull(@Distance,0)
END
'
END
GO
/****** Object: StoredProcedure [dbo].[uspGetBookingsDetailsFor_TA] Script Date: 11/20/2015 16:47:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBookingsDetailsFor_TA]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--uspGetBookingsDetailsFor_TA @CompId=''ATU'', @IsExported =0
CREATE PROC [dbo].[uspGetBookingsDetailsFor_TA]
(
@From datetime=null,
@To datetime=null,
@ReservationRef varchar(50)=null,
@BookingStatus varchar(50)=''HK'',
@ServiceType varchar(50)= ''ALL'',
@IsExported bit = 0,
@CompId varchar(10)
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Sql varchar(max)
Create table #temptbl (
/*3*/ RecordType varchar(10),
/*13*/ ServiceType varchar(10),
/*4*/ ReservationRef varchar(100),
/*56*/ ReservationId bigint,
/*11*/ ServiceReservationID bigint,
/*5*/ BookingDate varchar(100),
/*5A*/ BookingDateDisplay DATETIME,
/*6*/ BookingType varchar(10),
/*7*/ MappedAgentCode varchar(20),
/*8*/ CompanyID varchar(5),
/*9*/ MappedBaseCurrency varchar(10),
/*10*/ MappedBranchCode varchar(20),
/*12*/ ServiceLeadPax varchar(100),
/*14*/ MappedServiceCode varchar(20),
/*15*/ ServiceStartDate varchar(100),
/*16*/ ServiceEndDate varchar(100),
/*17*/ ServiceDuration int,
/*18*/ MappedServiceStartCityCode varchar(100),
/*19*/ MappedServiceEndCityCode varchar(100),
/*20*/ MappedServiceCountryCode varchar(10),
/*21*/ MappedServiceMarketCode varchar(10),
/*22*/ ServiceVoucherMailed bit,
/*23*/ ServiceVoucherPrint bit,
/*24*/ ServiceNoOfAdult int,
/*25*/ ServiceNoOfChild int,
/*26*/ ServiceNoOfInfant int,
/*27*/ SupplierConfirmationNo varchar(100),
/*28*/ MappedSupplierCode varchar(10),
/*29*/ SupplierCurrency varchar(10),
/*30*/ SupplierCurrencyROE decimal(18,8),
/*31*/ SupplierPrice money,
/*32*/ ServiceNetAmount money,
/*34*/ ServiceVatAmount money,
/*34*/ ServiceChargeAmount money,
/*33*/ ServiceGrossAmount money,
/*35*/ IPAddress varchar(100),
/*37*/ AgentRef varchar(30),
/*38*/ NoOfRoom int,
/*39*/ MappedRoomCategory varchar(500),
/*40*/ MappedRoomType varchar(500),
/*41*/ MappedMealTypeCode varchar(500),
/*44*/ PickUpTime varchar(20),
/*45*/ MaxPax int,
/*46*/ MaxLuggage int,
/*47*/ FlightNumber varchar(20),
/*48*/ FlightTime varchar(20),
/*49*/ HandLuggage varchar(5),
/*50*/ HandLuggageQuantity int,
/*51*/ DateOfTransfer varchar(500),
/*52*/ MappedPickUpPointCode varchar(500),
/*53*/ MappedDropOffPointCode varchar(500),
/*54*/ MappedPickUpPointType varchar(500),
/*55*/ MappedDropOffPointType varchar(500),
/*56*/ SplRequest varchar(500),
/*57*/ ComponentCost decimal,
/*58*/ DriverSubsistance decimal,
/*59*/ PerKmCost decimal,
/*60*/ TotalBlockSize int,
/*61*/ RoomID int,
/*62*/ VATCharge int,
/*63*/ VATChargeType CHAR(1),
/*64*/ Distance int,
/*65*/ TaxCode varchar(10),
/*66*/ HotelName varchar(100),
/*67*/ SupplierName varchar(50),
/*68*/ PickUpPointName varchar(50),
/*69*/ DropOffPointName varchar(50),
/*70*/ TransCurrency varchar(10),
/*71*/ TransCurrROE decimal(18,8),
/*72*/ TransPrice money,
/*73*/ DeadMileage int,
/*74*/ ChildPrice1 money,
/*75*/ ExtrabedPrice1 money,
/*76*/ ChildPrice2 money,
/*77*/ ExtrabedPrice2 money,
/*78*/ ChildTransPrice1 money,
/*79*/ ExtrabedTransPrice1 money,
/*80*/ ChildTransPrice2 money,
/*81*/ ExtrabedTransPrice2 money,
/*82*/ Extrabed bit
)
INSERT INTO #temptbl (RecordType, ReservationRef, ReservationId,BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode, ServiceLeadPax)
SELECT DISTINCT ''PNRHEAD'', RM.ReservationRef, RM.ReservationId, HR.Channel, AM.OtherMappingCode, @CompId, CM.OtherMappingCode, BM.OtherMappedCode, RP.Title + SPACE(1) + RP.FirstName + SPACE(1) + RP.LastName as LeadPaxName
FROM Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Hotel_Reservation HR on HR.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId
inner join (select HotelReservationId, SUM(NoOfRooms) NoOfRooms from Hotel_Room_Details group by HotelReservationId) HRD on HRD.HotelReservationId = HR.HotelReservationId
inner join Hotel_Passenger HP on HP.HotelReservationId = HR.HotelReservationId
inner join Reservation_Passenger RP on RP.PaxId = HP.PaxId and RP.isLeadPax = 1
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join HotelMapping HM ON HM.HotelCode = HR.HotelCode and HM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = HR.CityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = HR.CountryCode and COM.CompanyId = RM.CompanyId
inner join MarketMapping HMM ON HMM.MarketCode = HR.MarketCode and HMM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = HR.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = HR.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode having TaxCode != ''VAT'' ) RC ON RC.ProductId = HR.HotelReservationId
left join Reservation_Charge TAX ON TAX.ProductId = HR.HotelReservationId and TAX.TaxCode = ''VAT''
WHERE RM.CompanyId = ''ATU'' AND RI.ProductType = ''HTL'' and HR.Status = ''HK'' and HR.ChkInDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(HR.ChkInDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ServiceType, ReservationRef, ReservationId,HotelName, ServiceReservationID, BookingDate,BookingDateDisplay,
MappedServiceCode, ServiceStartDate, ServiceEndDate,ServiceDuration, MappedServiceStartCityCode, MappedServiceCountryCode, MappedServiceMarketCode, ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo, MappedSupplierCode,SupplierName,
/*SupplierPrice*/TransPrice, /*SupplierCurrency*/ TransCurrency, /*SupplierCurrencyROE*/TransCurrROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount,IPAddress, AgentRef, NoOfRoom, SplRequest, VATCharge, VATChargeType,TaxCode)
SELECT ''ATU'', ''BKGHEAD'',''HTL'', RM.ReservationRef, RM.ReservationId,HR.HotelName, HR.HotelReservationId, CONVERT(varchar(10), HR.BookingDate, 105) ,HR.BookingDate ,
isNull(HM.OtherMappingCode,'''') ServiceCode, CONVERT(varchar(10), HR.ChkInDate,105) as ServiceStartDate, CONVERT(varchar(10), HR.ChkOutDate, 105) as ServiceEndDate,
DATEDIFF(day, HR.ChkInDate, HR.ChkOutDate) AS ServiceDuration, isNull(CIM.OtherMappingCode,'''') MappedServiceStartCityCode, isNull(COM.OtherMappingCode,'''') MappedServiceCountryCode, isNull(HMM.OtherMappingCode,'''') MappedMarketCode, isNull(HR.VoucherMail,0)
VoucherMail, isNull(HR.VoucherPrint,0) VoucherPrint, isNull(HR.AdultCount,0) AdultCount, isNull(HR.ChildCount,0) ChildCount, isNull(HR.InfantCount,0) InfantCount, isNull(HR.ConfirmationNo,''''), isNull(SM.OtherMappingCode,'''') MappedSupplierCode,SUPM.SupplierCompany ,/*isNull(HR.SupplierPrice,0) SupplierBookedPrice,*/
isNull((isnull(HR.SupplierPrice,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) TransPrice,isNull(CM.OtherMappingCode,'''') MappedSupplierCurrency,/*isNull(HR.SupplierCurrROE,0) SupplierCurrencyROE,*/
isNull(HR.TransCurrROE,0) TransCurrROE,isNull(isnull(HR.WSNetAmt,0) + isnull(TAX.GrossAmt,0) + isnull(RC.GrossAmt,0),0) ServiceNetAmount,
isnull(TAX.GrossAmt, 0)ServiceVatAmount,isNull(RC.GrossAmt,0)ServiceChargeAmount,isnull(HR.SupplierPrice,0) ServiceGrossAmount,isNull(HR.IPAddress,''10.0.0.0'') IPAddress,isNull(HR.AgentRef,'''') AgentRef,isNull(HRD.NoOfRooms,0) NoOfRooms,
HR.SplRequest, ACT.Charge, ACT.ChargeType,isNull(TM.OtherMappingCode,'''') MappedTaxCode
from Reservation_Master RM
inner join Hotel_Reservation HR on HR.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId
inner join (select HotelReservationId, SUM(NoOfRooms) NoOfRooms from Hotel_Room_Details group by HotelReservationId) HRD on HRD.HotelReservationId = HR.HotelReservationId
inner join Hotel_Passenger HP on HP.HotelReservationId = HR.HotelReservationId
inner join Reservation_Passenger RP on RP.PaxId = HP.PaxId and RP.isLeadPax = 1
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join HotelMapping HM ON HM.HotelCode = HR.HotelCode and HM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = HR.CityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = HR.CountryCode and COM.CompanyId = RM.CompanyId
inner join MarketMapping HMM ON HMM.MarketCode = HR.MarketCode and HMM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = HR.CCode and SM.CompanyId = RM.CompanyId
inner join Supplier_Master SUPM ON SUPM.SupplierCode= SM.SupplierCode and SUPM.CompanyID=SM.CompanyID
inner join CurrencyMapping CM ON CM.CurrencyCode = HR.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, Isnull(SUM(GrossAmt),0) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode having TaxCode != ''VAT'') RC ON RC.ProductId = HR.HotelReservationId
left join Reservation_Charge TAX ON TAX.ProductId = HR.HotelReservationId and TAX.TaxCode = ''VAT''
left join AutoCharges_Master ACM ON ACM.ChargeCode = TAX.ChargeCode and ACM.CompanyId = RM.CompanyId
left join AutoCharges_Transaction ACT ON ACT.AutoChargeId = ACM.AutoChargeId and ACT.ServiceType = ''HTL''
left join Cancellation_Charge CC ON CC.Reservationid=RM.Reservationid and CC.ProductCode=''HTL''
left join TaxMapping TM ON TM.TaxCode=CC.ChargeCode
where RM.CompanyId = ''ATU'' and HR.Status = ''HK'' and HR.ChkInDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(HR.ChkInDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ReservationRef, ReservationId, ServiceReservationID, MappedRoomCategory, MappedRoomType, MappedMealTypeCode, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant,RoomID,TransPrice,ServiceGrossAmount,SupplierCurrency,ChildPrice1,ChildPrice2,ExtrabedPrice1,Extrabed,ChildTransPrice1,ChildTransPrice2,ExtrabedTransPrice1)
SELECT @CompId, ''BKGLINE'', RM.ReservationRef, RM.ReservationId, HR.HotelReservationId, RCM.OtherMappingCode, RTM.OtherMappingCode,MM.OtherMappingCode,
HRD.NoOfAdults, HRD.NoOfChild, isnull(HRD.NoOfInfant,0) ,HRD.RoomID,
/*HRD.SupplierPrice,*/isNull((isnull(HRD.SupplierPrice,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) TransPrice,
HRD.SupplierPrice,HR.SupplierCurr,isnull(HRC.ChildPrice1,0),isnull(HRC.ChildPrice2,0),isnull(HRC.ExtrabedPrice1,0),
(CASE WHEN HRD.Extrabed = ''YES'' THEN ''1'' WHEN HRD.Extrabed = ''NO'' THEN ''0'' END) AS Extrabed,
isNull((isnull(HRC.ChildPrice1,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) ChildTransPrice1,
isNull((isnull(HRC.ChildPrice2,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) ChildTransPrice2,
isNull((isnull(HRC.ExtrabedPrice1,0) / isnull(HR.SupplierCurrROE,0)) * isnull(HR.TransCurrROE,0),0) ExtrabedTransPrice1
from Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Hotel_Reservation HR on HR.ReservationId = RM.ReservationId
inner join Hotel_Room_Details HRD ON HR.HotelReservationId = HRD.HotelReservationId
left join Hotel_RoomWise_ChildPrice HRC on HRD.HotelReservationId=HRC.HotelReservationId and HRC.RoomID=HRD.RoomID
inner join RoomCategoryMapping RCM ON RCM.RoomCategoryCode = HRD.RoomCode and RCM.CompanyID = RM.CompanyId
inner join RoomTypeMapping RTM ON RTM.RoomTypeCode = HRD.RoomType and RTM.CompanyID = RM.CompanyId
inner join MealMapping MM ON MM.MealCode = HRD.MealCode and MM.CompanyId = Rm.CompanyId
inner join AgentMapping AM on AM.AgentId = RM.ClientId
inner join (select HotelReservationId, SUM(NoOfRooms) NoOfRooms from Hotel_Room_Details group by HotelReservationId) HRD1 on HRD1.HotelReservationId = HR.HotelReservationId
inner join Hotel_Passenger HP on HP.HotelReservationId = HR.HotelReservationId
inner join Reservation_Passenger RP on RP.PaxId = HP.PaxId and RP.isLeadPax = 1
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join HotelMapping HM ON HM.HotelCode = HR.HotelCode and HM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = HR.CityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = HR.CountryCode and COM.CompanyId = RM.CompanyId
inner join MarketMapping HMM ON HMM.MarketCode = HR.MarketCode and HMM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = HR.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = HR.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode,productcode having TaxCode != ''VAT'' and productcode = ''HTL'' ) RC ON RC.ProductId = HR.HotelReservationId
left join Reservation_Charge TAX ON TAX.ProductId = HR.HotelReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''HTL''
where RM.CompanyId = ''ATU'' AND RI.ProductType = ''HTL'' and HR.Status = ''HK'' and HR.ChkInDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(HR.ChkInDate as date)) < = 2
DELETE #temptbl WHERE RecordType = ''BKGHEAD'' and ServiceReservationID NOT IN (SELECT ServiceReservationID FROM #temptbl WHERE RecordType = ''BKGLINE'')
DELETE #temptbl WHERE RecordType = ''PNRHEAD'' and ReservationId NOT IN (SELECT ReservationId FROM #temptbl WHERE RecordType = ''BKGHEAD'')
-- HOTEL END --
-- TRANSFER START P2P--
INSERT INTO #temptbl (RecordType, ReservationRef, ReservationId, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode,ServiceLeadPax)
SELECT DISTINCT ''PNRHEAD'', RM.ReservationRef, RM.ReservationId, TRP.Channel, AM.OtherMappingCode, ''ATU'', CM.OtherMappingCode, BM.OtherMappedCode, RP.Title + SPACE(1) + RP.FirstName + SPACE(1) + RP.LastName as LeadPaxName
FROM Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Transfer_P2P_Reservation TRP on TRP.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRP.TransferP2PReservationId and OPP.ProductType = ''P2P''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''P2P''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRP.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRP.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRP.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRP.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRP.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''P2P'' ) RC ON RC.ProductId = TRP.TransferP2PReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRP.TransferP2PReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''P2P''
WHERE RM.CompanyId = ''ATU'' AND RI.ProductType = ''P2P'' and TRP.Status = ''HK'' and TRP.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRP.StartDate as date)) < = 3
INSERT INTO #temptbl (CompanyID, RecordType, ServiceType, ReservationRef,ReservationId,ServiceReservationID,BookingDate,BookingDateDisplay,
MappedServiceCode, ServiceStartDate, ServiceEndDate,ServiceDuration,MappedServiceStartCityCode,MappedServiceEndCityCode,MappedServiceCountryCode, MappedServiceMarketCode,
ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo,MappedSupplierCode,SupplierName,
/*SupplierPrice*/ TransPrice, /*SupplierCurrency*/TransCurrency, /*SupplierCurrencyROE*/TransCurrROE,ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount,IPAddress, AgentRef,ComponentCost,DriverSubsistance,
PickUpTime,MaxPax,MaxLuggage,FlightNumber,FlightTime,HandLuggage,HandLuggageQuantity,SplRequest, VATCharge, VATChargeType,TaxCode)
SELECT @CompId, ''BKGHEAD'',''P2P'', RM.ReservationRef, RM.ReservationId, TRP.TransferP2PReservationId, CONVERT(varchar(10), TRP.BookingDate, 105) ,TRP.BookingDate ,
isNull(VM.OtherMappingCode,'''') ServiceCode, CONVERT(varchar(10), TRP.StartDate,105) as ServiceStartDate, CONVERT(varchar(10), TRP.EndDate, 105) as ServiceEndDate,
DATEDIFF(day, TRP.StartDate, TRP.EndDate) AS ServiceDuration,isnull(CIMS.OtherMappingCode,'''') MappedServiceStartCityCode,isnull('''','''') MappedServiceEndCityCode,
isNull(COM.OtherMappingCode,'''') MappedServiceCountryCode,'''' MappedMarketCode, isNull(TRP.VoucherMail,0)VoucherMail, isNull(TRP.VoucherPrint,0) VoucherPrint, isNull(TRP.AdultCount,0) AdultCount, isNull(TRP.ChildCount,0) ChildCount,
isNull(TRP.InfantCount,0) InfantCount, isNull(TRP.ConfirmationNo,''''), isNull(SM.OtherMappingCode,'''') MappedSupplierCode,SUPM.SupplierCompany,
/*isNull(TRP.SupplierPrice,0) SupplierBookedPrice,*/isNull((isnull(TRP.SupplierPrice,0) / isnull(TRP.SupplierCurrROE,0)) * isnull(TRP.TransCurrROE,0),0) TransPrice,
isNull(CM.OtherMappingCode,'''') MappedSupplierCurrency,/* isNull(TRP.SupplierCurrROE,0) SupplierCurrencyROE,*/isNull(TRP.TransCurrROE,0) TransCurrROE,
isNull(isnull(TRP.WSNetAmt,0) + isnull(TAX.GrossAmt,0) + isnull(RC.GrossAmt,0),0) ServiceNetAmount,isnull(TAX.GrossAmt, 0)ServiceVatAmount, isNull(RC.GrossAmt,0)ServiceChargeAmount,
isNull(TRP.SupplierPrice,0) ServiceGrossAmount,''10.0.0.0'' IPAddress, isNull(TRP.AgentRef,'''') AgentRef ,TRP.ComponentCost,TRP.DriverSubsistance,
TRP.PickTime,TRP.MaxPax,TRP.MaxLuggage,TRP.FlightNumber,TRP.FligtTime,TRP.IsHandLuggage,TRP.LuggageQuantity,TRP.SplRequest,ACT.Charge, ACT.ChargeType,isNull(TM.OtherMappingCode,'''') MappedTaxCode
from Reservation_Master RM
inner join Transfer_P2P_Reservation TRP on TRP.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRP.TransferP2PReservationId and OPP.ProductType = ''P2P''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''P2P''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRP.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIMS ON CIMS.CityCode = TRP.StartCityCode and CIMS.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRP.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRP.CCode and SM.CompanyId = RM.CompanyId
inner join Supplier_Master SUPM ON SUPM.SupplierCode= SM.SupplierCode and SUPM.CompanyID=SM.CompanyID
inner join CurrencyMapping CM ON CM.CurrencyCode = TRP.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''P2P'' ) RC ON RC.ProductId = TRP.TransferP2PReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRP.TransferP2PReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''P2P''
left join AutoCharges_Master ACM ON ACM.ChargeCode = TAX.ChargeCode and ACM.CompanyId = RM.CompanyId
left join AutoCharges_Transaction ACT ON ACT.AutoChargeId = ACM.AutoChargeId and ACT.ServiceType = ''P2P''
left join Cancellation_Charge CC ON CC.Reservationid=RM.Reservationid and CC.ProductCode=''P2P''
left join TaxMapping TM ON TM.TaxCode=CC.ChargeCode
where RM.CompanyId = ''ATU'' and TRP.Status = ''HK'' and TRP.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRP.StartDate as date)) < = 3
INSERT INTO #temptbl (CompanyID, RecordType, ReservationRef, ReservationId, ServiceReservationID, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, MappedPickUpPointCode, MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,DateOfTransfer,Distance,PickUpPointName,DropOffPointName,SupplierCurrency,DeadMileage)
SELECT @CompId, ''BKGLINE'', RM.ReservationRef, RM.ReservationId, TRP.TransferP2PReservationId, TRP.AdultCount, TRP.ChildCount, isnull(TRP.InfantCount,0), PMP.OtherMappingCode,PMD.OtherMappingCode, PTMP.OtherMappingCode, PTMD.OtherMappingCode,CONVERT(varchar(10), TPP.TransferDate, 105),
((Select Distance from uattcsnego.TRF_EXC.Point_Mapping TEPM INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP1 ON TEPM.FPOINTID = TEMP1.POINTID INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP2 ON TEPM.TPOINTID = TEMP2.POINTID WHERE(Temp1.PointCode=TPP.PickupPointCode and Temp2.PointCode=TPP.DropOffPointCode ) OR (Temp1.PointCode=TPP.DropOffPointCode and Temp2.PointCode=TPP.PickupPointCode) )) + dbo.fnGetDealMileage(TRP.TransferP2PReservationId,TPP.visitOrder,TPP.PickupPointCode,TPP.DropOffPointCode) AS ''Distance'',
--(Select Distance from uattcsnego.TRF_EXC.Point_Mapping TEPM INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP1 ON TEPM.FPOINTID = TEMP1.POINTID INNER JOIN uattcsnego.TRF_EXC.MstPoint TEMP2 ON TEPM.TPOINTID = TEMP2.POINTID WHERE (Temp1.PointCode=TPP.PickupPointCode and Temp2.PointCode=TPP.DropOffPointCode ) OR (Temp1.PointCode=TPP.DropOffPointCode and Temp2.PointCode=TPP.PickupPointCode) ) Distance,
uattcsnego.dbo.getXMLDescription(TRFMST.POINTNAME,''GB'') as PickUpPointName,uattcsnego.dbo.getXMLDescription(TRFDMST.POINTNAME,''GB'') as DropOffPointName,TRP.SupplierCurr,
TRFMST.DIST_FROM_GARAGE from Reservation_Master RM
inner join Transfer_P2P_Reservation TRP on TRP.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRP.TransferP2PReservationId and OPP.ProductType = ''P2P''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''P2P''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRP.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRP.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRP.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRP.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRP.TransCurr and CM.CompanyId = RM.CompanyId
inner join Transfer_P2P_Points TPP ON TPP.TransferP2PReservationId = TRP.TransferP2PReservationId
inner join PointsMapping PMP ON PMP.PointCode = TPP.PickupPointCode and PMP.CompanyId = RM.CompanyId
inner join PointsMapping PMD ON PMD.PointCode = TPP.DropOffPointCode and PMD.CompanyId = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFMST ON TRFMST.PointCode=TPP.PickupPointCode and TRFMST.CompanyCode = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFDMST ON TRFDMST.PointCode=TPP.DropOffPointCode and TRFDMST.CompanyCode = RM.CompanyId
inner join PointTypeMapping PTMP ON PTMP.PointTypeCode = Tpp.PickupPointType and PMP.CompanyId = RM.CompanyId
inner join PointTypeMapping PTMD ON PTMD.PointTypeCode = Tpp.DropOffPointType and PMD.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''P2P'' ) RC ON RC.ProductId = TRP.TransferP2PReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRP.TransferP2PReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''P2P''
where RM.CompanyId = ''ATU'' and TRP.Status = ''HK'' and TRP.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRP.StartDate as date)) < = 3
DELETE #temptbl WHERE RecordType = ''BKGHEAD'' and ServiceReservationID NOT IN (SELECT ServiceReservationID FROM #temptbl WHERE RecordType = ''BKGLINE'')
DELETE #temptbl WHERE RecordType = ''PNRHEAD'' and ReservationId NOT IN (SELECT ReservationId FROM #temptbl WHERE RecordType = ''BKGHEAD'')
--TRANSFER END P2P---
--TRANSFER START MILEAGE--
INSERT INTO #temptbl (RecordType, ReservationRef, ReservationId, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency,
MappedBranchCode, ServiceLeadPax)
SELECT DISTINCT ''PNRHEAD'', RM.ReservationRef, RM.ReservationId, TRM.Channel, AM.OtherMappingCode, ''ATU'', CM.OtherMappingCode,
BM.OtherMappedCode, RP.Title + SPACE(1) + RP.FirstName + SPACE(1) + RP.LastName as LeadPaxName
FROM Reservation_Master RM
inner join Reservation_item RI ON RI.ReservationId = RM.ReservationId
inner join Transfer_Mileage_Reservation TRM on TRM.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRM.TransferMileageReservationId and OPP.ProductType = ''MILEAGE''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''MILEAGE''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRM.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRM.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRM.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRM.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRM.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''MILEAGE'' ) RC ON RC.ProductId = TRM.TransferMileageReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRM.TransferMileageReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''MILEAGE''
WHERE RM.CompanyId = ''ATU'' AND RI.ProductType = ''MILEAGE'' and TRM.Status = ''HK'' and TRM.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRM.StartDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ServiceType, ReservationRef, ReservationId, ServiceReservationID, BookingDate,BookingDateDisplay,
MappedServiceCode, ServiceStartDate, ServiceEndDate,ServiceDuration,MappedServiceStartCityCode, MappedServiceEndCityCode,MappedServiceCountryCode, MappedServiceMarketCode,
ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo,
MappedSupplierCode,SupplierName,/*SupplierPrice*/TransPrice, /*SupplierCurrency*/TransCurrency, /*SupplierCurrencyROE*/TransCurrROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount,
IPAddress, AgentRef,ComponentCost,DriverSubsistance,PerKmCost,TotalBlockSize,PickUpTime,MaxPax,MaxLuggage,FlightNumber,FlightTime,HandLuggage,HandLuggageQuantity,SplRequest, VATCharge, VATChargeType,TaxCode)
SELECT @CompId, ''BKGHEAD'',''MILEAGE'', RM.ReservationRef, RM.ReservationId, TRM.TransferMileageReservationId, CONVERT(varchar(10),
TRM.BookingDate, 105) ,TRM.BookingDate,isNull(VM.OtherMappingCode,'''') ServiceCode, CONVERT(varchar(10), TRM.StartDate,105) as ServiceStartDate, CONVERT(varchar(10), TRM.EndDate, 105) as ServiceEndDate,
DATEDIFF(day, TRM.StartDate, TRM.EndDate) AS ServiceDuration,isnull(CIMS.OtherMappingCode,'''') MappedServiceStartCityCode,isnull(CIME.OtherMappingCode,'''') MappedServiceEndCityCode,
isNull(COM.OtherMappingCode,'''') MappedServiceCountryCode,'''' MappedMarketCode, isNull(TRM.VoucherMail,0) VoucherMail, isNull(TRM.VoucherPrint,0) VoucherPrint, isNull(TRM.AdultCount,0) AdultCount,
isNull(TRM.ChildCount,0) ChildCount, isNull(TRM.InfantCount,0) InfantCount, isNull(TRM.ConfirmationNo,''''),isNull(SM.OtherMappingCode,'''') MappedSupplierCode,SUPM.SupplierCompany,
/*isNull(TRM.SupplierPrice,0) SupplierBookedPrice,*/isNull((isnull(TRM.SupplierPrice,0) / isnull(TRM.SupplierCurrROE,0)) * isnull(TRM.TransCurrROE,0),0) TransPrice,
isNull(CM.OtherMappingCode,'''') MappedSupplierCurrency,/*isNull(TRM.SupplierCurrROE,0) SupplierCurrencyROE,*/isNull(TRM.TransCurrROE,0) TransCurrROE,isNull(isnull(TRM.WSNetAmt,0) + isnull(TAX.GrossAmt,0) + isnull(RC.GrossAmt,0),0) ServiceNetAmount,
isnull(TAX.GrossAmt, 0)ServiceVatAmount, isNull(RC.GrossAmt,0)ServiceChargeAmount,isNull(TRM.SupplierPrice,0) ServiceGrossAmount, ''10.0.0.0'' IPAddress, isNull(TRM.AgentRef,'''') AgentRef,TRM.ComponentCost,TRM.DriverSubsistance,TRM.PerKmCost,TRM.TotalBlockSize,TRM.PickTime,TRM.MaxPax,TRM.MaxLuggage,TRM.FlightNumber,TRM.FligtTime,TRM.IsHandLuggage,TRM.LuggageQuantity,TRM.SplRequest, ACT.Charge, ACT.ChargeType,isNull(TM.OtherMappingCode,'''') MappedTaxCode
from Reservation_Master RM
inner join Transfer_Mileage_Reservation TRM on TRM.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRM.TransferMileageReservationId and OPP.ProductType = ''MILEAGE''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''MILEAGE''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRM.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIMS ON CIMS.CityCode = TRM.StartCityCode and CIMS.CompanyId = RM.CompanyId
inner join CityMapping CIME ON CIME.CityCode = TRM.EndCityCode and CIME.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRM.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRM.CCode and SM.CompanyId = RM.CompanyId
inner join Supplier_Master SUPM ON SUPM.SupplierCode= SM.SupplierCode and SUPM.CompanyID=SM.CompanyID
inner join CurrencyMapping CM ON CM.CurrencyCode = TRM.TransCurr and CM.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''MILEAGE'' ) RC ON RC.ProductId = TRM.TransferMileageReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRM.TransferMileageReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''MILEAGE''
left join AutoCharges_Master ACM ON ACM.ChargeCode = TAX.ChargeCode and ACM.CompanyId = RM.CompanyId
left join AutoCharges_Transaction ACT ON ACT.AutoChargeId = ACM.AutoChargeId and ACT.ServiceType = ''MILEAGE''
left join Cancellation_Charge CC ON CC.Reservationid=RM.Reservationid and CC.ProductCode=''MILEAGE''
left join TaxMapping TM ON TM.TaxCode=CC.ChargeCode
where RM.CompanyId = ''ATU'' and TRM.Status = ''HK'' and TRM.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRM.StartDate as date)) < = 2
INSERT INTO #temptbl (CompanyID, RecordType, ReservationRef, ReservationId, ServiceReservationID, ServiceNoOfAdult, ServiceNoOfChild,
ServiceNoOfInfant, MappedPickUpPointCode, MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,PickUpPointName,DropOffPointName,Distance,SupplierCurrency,DeadMileage)
SELECT @CompId, ''BKGLINE'', RM.ReservationRef, RM.ReservationId, TRM.TransferMileageReservationId, TRM.AdultCount, TRM.ChildCount,
isnull(TRM.InfantCount,0), PMP.OtherMappingCode,PMD.OtherMappingCode, PTMP.OtherMappingCode, PTMD.OtherMappingCode,
uattcsnego.dbo.getXMLDescription(TRFMST.POINTNAME,''GB'') as PickUpPointName,uattcsnego.dbo.getXMLDescription(TRFDMST.POINTNAME,''GB'') as DropOffPointName,
isNull(isnull(TRFMST.DIST_FROM_GARAGE,0) + isnull(TRFDMST.DIST_FROM_GARAGE,0) + isnull(TRM.TotalBlockSize,0),0),TRM.SupplierCurr,isNull(isnull(TRFMST.DIST_FROM_GARAGE,0) + isnull(TRFDMST.DIST_FROM_GARAGE,0),0) as DeadMileage
from Reservation_Master RM
inner join Transfer_Mileage_Reservation TRM on TRM.ReservationId = RM.ReservationId
inner join AgentMapping AM on AM.AgentId = RM.ClientId and AM.CompanyId = RM.CompanyId
inner join Other_Product_Passenger OPP on OPP.ProductId = TRM.TransferMileageReservationId and OPP.ProductType = ''MILEAGE''
inner join Reservation_Passenger RP on RP.PaxId = OPP.PaxId and RP.isLeadPax = 1 and OPP.ProductType = ''MILEAGE''
inner join BranchMapping BM ON BM.BranchId = RM.BranchId and BM.CompanyId = RM.CompanyId
inner join VehicleMapping VM ON VM.VehicleCode = TRM.VehicleCode and VM.CompanyId = RM.CompanyId
inner join CityMapping CIM ON CIM.CityCode = TRM.StartCityCode and CIM.CompanyId = RM.CompanyId
inner join CountryMapping COM ON COM.CountryCode = TRM.CountryCode and COM.CompanyId = RM.CompanyId
inner join SupplierMapping SM ON SM.SupplierCode = TRM.CCode and SM.CompanyId = RM.CompanyId
inner join CurrencyMapping CM ON CM.CurrencyCode = TRM.TransCurr and CM.CompanyId = RM.CompanyId
inner join PointsMapping PMP ON PMP.PointCode = TRM.PickUpCode and PMP.CompanyId = RM.CompanyId
inner join PointsMapping PMD ON PMD.PointCode = TRM.EndPointCode and PMD.CompanyId = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFMST ON TRFMST.PointCode=TRM.PickUpCode and TRFMST.CompanyCode = RM.CompanyId
inner join uattcsnego.TRF_EXC.MstPoint TRFDMST ON TRFDMST.PointCode=TRM.EndPointCode and TRFDMST.CompanyCode = RM.CompanyId
inner join PointTypeMapping PTMP ON PTMP.PointTypeCode = TRM.PickType and PMP.CompanyId = RM.CompanyId
inner join PointTypeMapping PTMD ON PTMD.PointTypeCode = TRM.EndPointType and PMD.CompanyId = RM.CompanyId
left join (select ProductId, SUM (GrossAmt) GrossAmt, TaxCode from Reservation_Charge group by ProductId, TaxCode, productcode having TaxCode != ''VAT'' and productcode = ''MILEAGE'' ) RC ON RC.ProductId = TRM.TransferMileageReservationId
left join Reservation_Charge TAX ON TAX.ProductId = TRM.TransferMileageReservationId and TAX.TaxCode = ''VAT'' and TAX.productcode = ''MILEAGE''
where RM.CompanyId = ''ATU'' and TRM.Status = ''HK'' and TRM.StartDate >=GETDATE()and DATEDIFF(dd,getdate(),cast(TRM.StartDate as date)) < = 2
DELETE #temptbl WHERE RecordType = ''BKGHEAD'' and ServiceReservationID NOT IN (SELECT ServiceReservationID FROM #temptbl WHERE RecordType = ''BKGLINE'')
DELETE #temptbl WHERE RecordType = ''PNRHEAD'' and ReservationId NOT IN (SELECT ReservationId FROM #temptbl WHERE RecordType = ''BKGHEAD'')
--TRANSFER END MILEAGE---
--Code For Show in grid
-- NOTE : when we add there we have to add in type table.
;WITH CTE AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY ReservationId, RecordType ORDER BY ReservationId, RecordType)''RowRank'' FROM #temptbl)
DELETE FROM CTE WHERE RowRank > 1 and RecordType = ''PNRHEAD''
SELECT NEWID() TCS_UniqueID, RecordType, TTBL.ServiceType, ReservationRef, TTBL.ReservationId, TTBL.ServiceReservationID, BookingDate, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode, ServiceLeadPax, MappedServiceCode, ServiceStartDate, ServiceEndDate, ServiceDuration, MappedServiceStartCityCode, MappedServiceEndCityCode, MappedServiceCountryCode, MappedServiceMarketCode, ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo, MappedSupplierCode, SupplierPrice, SupplierCurrency, SupplierCurrencyROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount, IPAddress, AgentRef, NoOfRoom ,MappedRoomCategory, MappedRoomType, MappedMealTypeCode, PickUpTime, MaxPax, MaxLuggage, FlightNumber, FlightTime, HandLuggage, HandLuggageQuantity, DateOfTransfer, TTBL.MappedPickUpPointCode, TTBL.MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,REPLACE(SplRequest, '','', '''') as SplRequest, VATCharge, VATChargeType from #temptbl TTBL
full outer join Exported_TA ETA
ON TTBL.ReservationId = ETA.ReservationId and TTBL.ServiceReservationID = ETA.ServiceReservationID
where CompanyId = @CompId
and TTBL.RecordType in (''BKGHEAD'',''BKGLINE'',''PNRHEAD'')
and isNull(ETA.isExported,0) = @IsExported
and ( TTBL.ServiceType = CASE WHEN ISNULL(@ServiceType,'''') <> ''ALL'' THEN @ServiceType ELSE TTBL.ServiceType END )
and ( TTBL.ReservationRef = CASE WHEN ISNULL(@ReservationRef,'''') <> '''' THEN @ReservationRef ELSE TTBL.ReservationRef END )
AND 1 = CASE WHEN (ISNULL(@From,'''') = '''' OR ISNULL(@To,'''')='''') THEN 1
WHEN CONVERT(DATE,TTBL.BookingDateDisplay) BETWEEN CONVERT(dATE,@From) AND CONVERT(DATE,@To) THEN 1 ELSE 0 END
ORDER BY TTBL.ReservationId, TTBL.ServiceReservationID, TTBL.RecordType
--Code For Show in grid
-- NOTE : when we add there we have to add in type table.
;WITH CTE1 as (SELECT NEWID() TCS_UniqueID, RecordType, TTBL.ServiceType, ReservationRef, TTBL.ReservationId, TTBL.ServiceReservationID, BookingDate, BookingType, MappedAgentCode, CompanyID, MappedBaseCurrency, MappedBranchCode, ServiceLeadPax, MappedServiceCode, ServiceStartDate, ServiceEndDate, ServiceDuration, MappedServiceStartCityCode, MappedServiceEndCityCode, MappedServiceCountryCode, MappedServiceMarketCode, ServiceVoucherMailed, ServiceVoucherPrint, ServiceNoOfAdult, ServiceNoOfChild, ServiceNoOfInfant, SupplierConfirmationNo, MappedSupplierCode, SupplierPrice, SupplierCurrency, SupplierCurrencyROE, ServiceNetAmount, ServiceVatAmount, ServiceChargeAmount, ServiceGrossAmount, IPAddress, AgentRef, NoOfRoom ,MappedRoomCategory, MappedRoomType, MappedMealTypeCode, PickUpTime, MaxPax, MaxLuggage, FlightNumber, FlightTime, HandLuggage, HandLuggageQuantity, DateOfTransfer, TTBL.MappedPickUpPointCode, TTBL.MappedDropOffPointCode, MappedPickUpPointType, MappedDropOffPointType,REPLACE(SplRequest, '','', '''') as SplRequest, VATCharge, VATChargeType from #temptbl TTBL
full outer join Exported_TA ETA
ON TTBL.ReservationId = ETA.ReservationId and TTBL.ServiceReservationID = ETA.ServiceReservationID
where CompanyId = @CompId
and TTBL.RecordType in (''BKGHEAD'',''BKGLINE'',''PNRHEAD'')
and isNull(ETA.isExported,0) = @IsExported
and ( TTBL.ServiceType = CASE WHEN ISNULL(@ServiceType,'''') <> ''ALL'' THEN @ServiceType ELSE TTBL.ServiceType END )
and ( TTBL.ReservationRef = CASE WHEN ISNULL(@ReservationRef,'''') <> '''' THEN @ReservationRef ELSE TTBL.ReservationRef END )
AND 1 = CASE WHEN (ISNULL(@From,'''') = '''' OR ISNULL(@To,'''')='''') THEN 1
WHEN CONVERT(DATE,TTBL.BookingDateDisplay) BETWEEN CONVERT(dATE,@From) AND CONVERT(DATE,@To) THEN 1 ELSE 0 END
)
--Code For Insert PNR HEAD ,BKG Head and booking line .
SELECT DISTINCT TTBL.RecordType, TTBL.ServiceType, TTBL.ReservationRef, TTBL.ReservationId,TTBL.HotelName, TTBL.ServiceReservationID, TTBL.BookingDate, TTBL.BookingType, TTBL.MappedAgentCode, TTBL.CompanyID, TTBL.MappedBaseCurrency, TTBL.MappedBranchCode, TTBL.ServiceLeadPax, TTBL.MappedServiceCode, TTBL.ServiceStartDate, TTBL.ServiceEndDate, TTBL.ServiceDuration, TTBL.MappedServiceStartCityCode, TTBL.MappedServiceEndCityCode, TTBL.MappedServiceCountryCode, TTBL.MappedServiceMarketCode, TTBL.ServiceVoucherMailed, TTBL.ServiceVoucherPrint, TTBL.ServiceNoOfAdult, TTBL.ServiceNoOfChild, TTBL.ServiceNoOfInfant, TTBL.SupplierConfirmationNo, TTBL.MappedSupplierCode,TTBL.SupplierName, TTBL.TransCurrency, TTBL.TransCurrROE,TTBL.TransPrice, TTBL.ServiceNetAmount, TTBL.ServiceVatAmount, TTBL.ServiceChargeAmount, TTBL.ServiceGrossAmount as SuppPrice, TTBL.IPAddress, TTBL.AgentRef, TTBL.NoOfRoom ,TTBL.MappedRoomCategory, TTBL.MappedRoomType, TTBL.MappedMealTypeCode, TTBL.PickUpTime, TTBL.MaxPax, TTBL.MaxLuggage, TTBL.FlightNumber, TTBL.FlightTime, TTBL.HandLuggage, TTBL.HandLuggageQuantity, TTBL.DateOfTransfer, TTBL.MappedPickUpPointCode as PPC,TTBL.PickUpPointName as PPN, TTBL.MappedDropOffPointCode as DPC,TTBL.DropOffPointName as DPN, TTBL.MappedPickUpPointType, TTBL.MappedDropOffPointType ,REPLACE(TTBL.SplRequest, '','', '''') as SplRequest,TTBL.ComponentCost,TTBL.DriverSubsistance,TTBL.PerKmCost,TTBL.TotalBlockSize,TTBL.RoomID,TTBL.VATCharge, TTBl.VATChargeType,TTBl.Distance,TTBL.TaxCode,TTBL.SupplierCurrency as SuppCurr,TTBL.ChildPrice1 as ChS1,TTBL.ChildPrice2 as ChS2,TTBL.ExtrabedPrice1 as EBS,TTBL.Extrabed as IsEB,TTBL.ChildTransPrice1 as ChT1,TTBL.ChildTransPrice2 as ChT2,TTBL.ExtrabedTransPrice1 as EBT
from #temptbl TTBL
inner join CTE1 on CTE1.ReservationId = TTBL.ReservationId
WHERE 1= CASE WHEN ISNULL(TTBL.ServiceReservationID,''0'')=0 AND CTE1.ReservationRef=TTBL.ReservationRef THEN 1
WHEN CTE1.ServiceReservationID=TTBL.ServiceReservationID AND CTE1.ReservationRef=TTBL.ReservationRef THEN 1
ELSE 0 END
ORDER BY TTBL.ReservationId, TTBL.ServiceReservationID, TTBL.RecordType
drop table #temptbl;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END '
END
GO
