RSS
Facebook
Twitter

Sunday 8 October 2017

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

0 comments:

Post a Comment