CREATE TABLE [frontoffice].[ManagerReports]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [ExpectedOccupancy] INT NULL, [ExpectedDeparture] VARCHAR(MAX) NULL, [ExpectedArrival] INT NULL, [HouseCount] INT NULL, [Occupied] DECIMAL(20, 2) NULL, [RoomPosition] INT NULL, [OOO] INT NULL, [OOS] INT NULL, [Revenue] DECIMAL(20, 2) NULL, [ARR] DECIMAL(20, 2) NULL, [Date] DATETIME NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [frontoffice].[ManagerReports] ADD CONSTRAINT [PK_frontoffice_ManagerReports_Id] PRIMARY KEY([Id])GO ALTER TABLE [frontoffice].[ManagerReports] ADD CONSTRAINT [DF_frontoffice_ManagerReports_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [frontoffice].[ManagerReports] ADD CONSTRAINT [DF_frontoffice_ManagerReports_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO---------------ALTER TABLE frontoffice.ManagerReports ADD Complimentary INT NULL------------------ALTER TABLE frontoffice.ManagerReports ADD TotalRoom INT NULL, RoomSale DECIMAL NULL, FoodBeverageSale DECIMAL NULL, OtherIncome DECIMAL NULL,BillToCompany DECIMAL NULL,Cash DECIMAL NULL,CreditCard DECIMAL NULL , MobileWallet DECIMAL NULL, TotalCollection DECIMAL NULLGO-----------------ALTER TABLE frontoffice.ManagerReports ADD NoShow INT NULL, REVPAR DECIMAL NULL, Cancellation INT NULL, SingleOccupancy INT NULL,DoubleOccupancy INT NULL,TripleOccupancy INT NULL,GrandTotalSales DECIMAL NULL GO-------------------------------------------ALTER TABLE [frontoffice].[fo_g_guest] ADD [Pradesh] VARCHAR(100) NULL, [Zone] VARCHAR(100) NULL, [District] VARCHAR(100) NULL, [Palika] VARCHAR(100) NULL, [WardNo] INT NULLGO----------------------------alter proc frontoffice.sp_chk_pending_Transactions-- [dbo].[sp_chk_pending_Transactions] '2023-05-08',100092,'',NULL,0,0 @date date ,@resvId uniqueidentifier ,@folio_list NVARCHAR(max) ,@hasPending BIT OUTPUT ,@returnBit BIT =0 ,@roomId uniqueidentifierASBEGIN declare @emptyguid uniqueidentifier= '00000000-0000-0000-0000-000000000000' DECLARE @TABLEAVALUE TABLE(TYPE VARCHAR(50), OnDate DATE) DECLARE @compId uniqueidentifier= (SELECT resv_company FROM frontoffice.fo_resv_reservation_master WHERE reservation_id = @resvId) DECLARE @RM NVARCHAR(50),@LAUN NVARCHAR(50),@REST NVARCHAR(50),@MB NVARCHAR(50),@OS NVARCHAR(50) SELECT @RM=rout_rm_rates_mp,@LAUN=rout_laun,@REST=rout_rstrnt,@MB=rout_minibar,@OS=rout_other_services FROM frontoffice.fo_resv_routing WHERE rout_resv_id = @resvId declare @tableFolio table (folioId uniqueidentifier) if(@folio_list<>'') BEGIN insert into @tableFolio select * from [frontoffice].[FN_CONVERT_STRING_TO_LIST](@folio_list,',') --select * from @tableFolio END BEGIN --ROOMRATES IF(ISNULL(@resvId,@emptyguid)!=@emptyguid) BEGIN DECLARE @FromDate DATE, @toDate DATE IF(OBJECT_ID('tempdb..#rooms')) IS NOT NULL DROP TABLE #rooms CREATE TABLE #rooms (Id uniqueidentifier, FROM_Date DATE, To_date DATE) INSERT INTO #rooms SELECT rooms.assign_guest_room_id, stay_date_from, CASE WHEN stay_date_to= stay_date_from THEN stay_date_from WHEN isnull(late_chk_out,0) = 1 then stay_date_to ELSE DATEADD(dd,-1,stay_date_to) END FROM frontoffice.fo_resv_reservation_assign_guest_rooms rooms INNER JOIN frontoffice.fo_resv_guest_reservation_detail resvGst ON rooms.resv_guest_id = resvGst.resv_guest_id LEFT JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON FOLIO.folio_resv_id = @resvId AND FOLIO.folio_type = @RM AND ISNULL(FOLIO.folio_gst_id,@emptyguid) =ISNULL((CASE WHEN folio_type='GUESTFOLIO' THEN resvGst.guest_id ELSE ISNULL(FOLIO.folio_gst_id,@emptyguid) END ),@emptyguid) AND ISNULL(FOLIO.folio_cmp_id,@emptyguid) =ISNULL((CASE WHEN folio_type='COMPANYFOLIO' THEN @compId ELSE ISNULL(FOLIO.folio_cmp_id,@emptyguid) END ),@emptyguid) WHERE ( resvGst.resv_id = @resvId ) AND ISNULL(rooms.reg_status,'')<>'' AND ISNULL(room_transfer,'') = CASE WHEN stay_date_from!=stay_date_to THEN ISNULL(room_transfer,'') ELSE '' END AND ISNULL(rooms.room_walk_out,0)=0 AND (@folio_list='' OR (FOLIO.folio_id IN ( SELECT * FROM @tableFolio) )) AND (@roomId=@emptyguid or room_id =@roomId) --select *from #rooms SELECT @FromDate= MIN(FROM_Date),@toDate = MAX(To_date) FROM #rooms -- SELECT @FromDate , @toDate IF(@toDate>@date) BEGIN SET @toDate = @date END WHILE @FromDate<=@toDate BEGIN IF(EXISTS ( SELECT @FromDate FROM #rooms roomsdat LEFT JOIN frontoffice.fo_posting post ON post.post_date = @FromDate AND post.post_type='ROOMRATES' AND POST.post_ref_id = roomsdat.Id WHERE @FromDate BETWEEN FROM_Date AND To_date AND POST.posting_id IS NULL )) BEGIN INSERT INTO @TABLEAVALUE VALUES ('ROOMRATES',@FromDate) END SET @FromDate = DATEADD(DD,1,@FromDate) END END ELSE BEGIN DECLARE @RSULT TABLE ( GROUPNAME VARCHAR(1000) ,ROOMNUMBER VARCHAR(500) ,GUESTNAME VARCHAR(500) ,MP VARCHAR(100) ,PAX INT , COMPLIMENTARY BIT , ROOMRATE NUMERIC(18,2) , PLANRATE NUMERIC(18,2) , DISCOUNT NUMERIC(18,2) ,CURRENCY VARCHAR(200) ,ISINCLUSIVE INT ,RETENTIONCHARGE NUMERIC(18,2) ,RETENTIONDESC VARCHAR(1000) , SLEEPOUTDEDUCTION NUMERIC(18,2) , SLEEPOUTDESC VARCHAR(1000) ,POSTID uniqueidentifier , COMPID uniqueidentifier , FOLIOID uniqueidentifier , REGISTRATIONID uniqueidentifier , RESVID uniqueidentifier ,ROOMID uniqueidentifier ,CURRID uniqueidentifier ,REFERENCE uniqueidentifier ,RETENTIONDATE DATE ,RETENTIONUSER uniqueidentifier , SLEEPOUTDATE DATE , SLEEPOUTUSER uniqueidentifier ,BILLNO NVARCHAR(200) ,mpId uniqueidentifier ,exchRate numeric(18,2) ,Complimentary_per NUMERIC(18,2) ,SC NUMERIC(18,2) ,VAT NUMERIC(18,2) ) INSERT INTO @RSULT exec [frontoffice].[sp_Posting_RoomList] @date,null,null,@emptyguid,1,@folio_list --select @folio_list IF(( SELECT COUNT(*) FROM @RSULT)>0) BEGIN INSERT INTO @TABLEAVALUE (TYPE) VALUES ('ROOMRATES') END END END begin -- REVENUE BEGIN --LAUNDRY IF( ( select COUNT(*) from frontoffice.fo_g_laun_ServiceTransactionMaster LAUN INNER JOIN frontoffice.fo_resv_guest_reservation_detail GST ON LAUN.lau_resv_guest_Id = GST.resv_guest_id AND ISNULL(LAUN.laun_void,0)=0 left join frontoffice.fo_posting post on post.post_ref_id= LAUN.laun_serviceTransID and post.post_type='REVENUE' AND (POST.post_sub_type ='LAUNDRY') LEFT JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON FOLIO.folio_resv_id = @resvId AND FOLIO.folio_type = @LAUN AND ISNULL(FOLIO.folio_gst_id,@emptyguid) =ISNULL((CASE WHEN folio_type ='GUESTFOLIO' THEN GST.guest_id ELSE ISNULL(FOLIO.folio_gst_id,@emptyguid) END ),@emptyguid) AND ISNULL(FOLIO.folio_cmp_id,@emptyguid) =ISNULL((CASE WHEN folio_type ='COMPANYFOLIO' THEN @compId ELSE ISNULL(FOLIO.folio_cmp_id,@emptyguid) END ),@emptyguid) where ISNULL( POST.posting_id,@emptyguid)=@emptyguid AND (ISNULL(@resvId,@emptyguid) =@emptyguid OR GST.resv_id=@resvId ) AND(ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(Date, laun_TransactionDate) = @date) AND (@folio_list='' OR (FOLIO.FOLIO_ID IN ( SELECT * FROM @tableFolio) )))>0 ) BEGIN INSERT INTO @TABLEAVALUE(TYPE) VALUES ('LAUNDRY') END END BEGIN -- RESTAUANT declare @result_REST table ( GroupName nvarchar(200)NOT NULL , Guestname nvarchar(200)NOT NULL , RoomNo nvarchar(10)NOT NULL , Date date NOT NULL , Currency nvarchar(50)NOT NULL , ExchRate numeric(18,4)NOT NULL , Amt numeric(18,4) NOT NULL , ExchAmt numeric(18,4)NOT NULL , discount nvarchar(50)NOT NULL , SC numeric(18,2) not null default 0 , VAT numeric(18,2)not null default 0 , Particulars nvarchar(max) NOT NULL , receiptNO nvarchar(20)NOT NULL , BillNO nvarchar(20)NOT NULL , RoomId uniqueidentifier NOT NULL , ResvId uniqueidentifier NOT NULL , FolioId uniqueidentifier NOT NULL , CurrId uniqueidentifier NOT NULL , postID uniqueidentifier NOT NULL , FolioType nvarchar(50) NOT NULL ) DECLARE @dtREst DATE = NULL --IF(ISNULL(@resvId,@emptyguid)<>@emptyguid) --BEGIN -- INSERT INTO @result_REST --exec [sp_posting_restaurant_fo] @dtRest,0,1,@resvId,'','',@folio_list --IF(( SELECT COUNT(*) FROM @result_REST)>0) --BEGIN -- INSERT INTO @TABLEAVALUE(TYPE) VALUES ('RESTAURANT') --END --END END END BEGIN -- AMOUNTCOLLECTION BEGIN --ADVANCE/DEPOSOT IF(( select COUNT(*) from frontoffice.fo_Payment_Deposit dep left join frontoffice.fo_posting post on post.post_ref_id= dep.dep_id and post.post_type='AMOUNTCOLLECTION' AND (POST.post_sub_type ='DEPOSIT' OR POST.post_sub_type='ADVANCE') WHERE (ISNULL(@resvId,@emptyguid) =@emptyguid OR DEP.dep_resv_id = @resvId ) AND(ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(DATE, DEP.dep_tr_date)= @date) AND (@folio_list='' OR DEP.dep_folio_id IN ( SELECT * FROM @tableFolio)) AND ISNULL( POST.posting_id,@emptyguid)=@emptyguid)!= 0) BEGIN INSERT INTO @TABLEAVALUE (TYPE) VALUES ('DEPOSIT') END --dep_resv_id, dep_folio_id,dep_tr_date END BEGIN --GENERAL PAYMENT IF(( select COUNT(*) from frontoffice.fo_Payment_General pay inner join frontoffice.fo_folio_master folio on pay.fo_payGen_Folio_Id = folio.folio_id left join frontoffice.fo_posting post on post.post_ref_id= pay.fo_pay_gen_id and post.post_type='AMOUNTCOLLECTION' AND (POST.post_sub_type ='PAYMENT') where (ISNULL(@resvId,@emptyguid) =@emptyguid OR folio.folio_resv_id = @resvID ) AND(ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(DATE, fo_payGen_date)= @date) AND (@folio_list='' OR pay.fo_payGen_Folio_Id IN ( SELECT * FROM @tableFolio)) AND ISNULL( POST.posting_id,@emptyguid)=@emptyguid)!= 0) BEGIN INSERT INTO @TABLEAVALUE (TYPE)VALUES ('PAYMENT') END END IF(ISNULL(@resvId,@emptyguid)=@emptyguid) BEGIN -- CHECKOUTPAYEMENT IF(( select COUNT(*) from frontoffice.fo_sett_details sett INNER JOIN frontoffice.fo_settlement settMaster ON sett.sett_det_sett_id = settMaster.sett_id inner join frontoffice.fo_g_bill_details_type bill on sett.sett_bill_number = bill.bill_details_bill_no and bill_details_type <>'ComplimentaryBill' left join frontoffice.fo_posting post on post.post_ref_id= sett.sett_det_id and post.post_type='AMOUNTCOLLECTION' AND (POST.post_sub_type ='CHECKOUTPAYMENT') where sett.RecordStatus = 1 AND (ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(DATE, settMaster.SETT_DATE)= @date) AND ISNULL( POST.posting_id,@emptyguid)=@emptyguid)!= 0) BEGIN INSERT INTO @TABLEAVALUE(TYPE) VALUES ('CHECKOUTPAYMENT') END END END BEGIN --PAID OUT IF(( SELECT COUNT(*) FROM frontoffice.fo_Payment_CPO pO left join frontoffice.fo_posting post on post.post_ref_id= po.CshPOTransID and post.post_type='paidout' where (ISNULL(@resvId,@emptyguid) =@emptyguid OR po.cpo_resv_id = @resvId ) AND(ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(DATE, CPO_Date)= @date) AND (@folio_list='' OR pO.folio_id IN ( SELECT * FROM @tableFolio)) and ISNULL( POST.posting_id,@emptyguid)=@emptyguid)!= 0) BEGIN INSERT INTO @TABLEAVALUE (TYPE)VALUES ('PAIDOUT') END END BEGIN --ALOWANCEMANUAL IF(( SELECT COUNT(*) FROM frontoffice.fo_alwnc_add MSTR INNER JOIN frontoffice.fo_alwnc_add_det DTL ON MSTR.fo_al_ad_id = DTL.fo_al_ad_msterId left join frontoffice.fo_posting post on post.post_ref_id= DTL.fo_al_ad_det_Id and post.post_type IN( 'ALLOWANCE','MANUAL','ADDITIONAL') WHERE (ISNULL(@resvId,@emptyguid) =@emptyguid OR MSTR.fo_al_ad_resv_id = @resvId) AND(ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(DATE, MSTR.fo_al_ad_date )= @date) AND (@folio_list='' OR DTL.fo_al_ad_det_folio_id IN ( SELECT * FROM @tableFolio)) AND ISNULL( POST.posting_id,@emptyguid)=@emptyguid)!= 0) BEGIN INSERT INTO @TABLEAVALUE(TYPE) VALUES ('ALLOWANCE') END END BEGIN -- OTHER SERVICE IF(ISNULL(@resvId,@emptyguid)<>@emptyguid) BEGIN DECLARE @id uniqueidentifier,@gstId uniqueidentifier DECLARE @DateList TABLE(OnDate DATE, id uniqueidentifier,gstId uniqueidentifier) DECLARE @regId uniqueidentifier= (SELECT registration_id FROM frontoffice.fo_registration_master WHERE reservation_id = @resvId) DECLARE @tableMissedPostings TABLE (FromDate DATE, toDate DATE, Id uniqueidentifier,guestId uniqueidentifier) INSERT INTO @tableMissedPostings SELECT service_date_from, service_date_to,guest_service_id,guest_id FROM ( SELECT service_date_from ,service_date_to ,guest_service_id ,(DATEDIFF(dd,service_date_from,service_date_to)+1 -(SELECT COUNT(*) FROM frontoffice.fo_posting WHERE post_type='Others' AND post_ref_id = guest_service_id)) AS unPosted ,guest_id FROM frontoffice.fo_reg_guest_service gstService WHERE reg_id =@regId AND ISNULL(isVoid,0) = 0) AS cte WHERE unPOsted>0 DECLARE cur CURSOR FAST_FORWARD LOCAL FOR SELECT FromDate ,toDate , Id ,guestId FROM @tableMissedPostings OPEN cur FETCH NEXT FROM cur INTO @fromDate, @toDate,@id,@gstId WHILE(@@FETCH_STATUS =0) BEGIN IF(@toDate>@date) BEGIN SET @toDate = @date END WHILE(@fromDate<= @toDate) BEGIN INSERT INTO @DateList( OnDate ,id,gstId)VALUES ( @fromDate,@id,@gstId) SET @fromDate = DATEADD(dd,1,@fromDate) END FETCH NEXT FROM cur INTO @fromDate, @toDate,@id,@gstId END DEALLOCATE cur INSERT INTO @TABLEAVALUE --('ROOMRATES',@FromDate) SELECT DISTINCT 'OTHERS',OnDate FROM @DateList OS LEFT JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON FOLIO.folio_resv_id = @resvId AND FOLIO.folio_type = @OS AND ISNULL(FOLIO.folio_gst_id,@emptyguid) =ISNULL((CASE WHEN folio_type='GUESTFOLIO' THEN gstId ELSE ISNULL(FOLIO.folio_gst_id,@emptyguid) END ),@emptyguid) AND ISNULL(FOLIO.folio_cmp_id,@emptyguid) =ISNULL((CASE WHEN folio_type='COMPANYFOLIO' THEN @compId ELSE ISNULL(FOLIO.folio_cmp_id,@emptyguid) END ),@emptyguid) LEFT JOIN frontoffice.fo_posting post ON post.post_type = 'Others' AND post.post_ref_id =OS.id WHERE post.posting_id IS NULL AND (@folio_list='' OR (FOLIO.folio_id IN ( SELECT * FROM @tableFolio) )) END ELSE BEGIN IF((SELECT COUNT(*) FROM frontoffice.fo_reg_guest_service SERV LEFT JOIN frontoffice.fo_registration_master REG ON SERV.reg_id = REG.registration_id left join frontoffice.fo_posting post on post.post_ref_id=SERV.guest_service_id and post.post_type = 'OTHERS' AND CONVERT(DATE,post_date) = @date WHERE @date BETWEEN CONVERT(dATE, SERV.service_date_from) AND CONVERT(daTE, SERV.service_date_to) AND ISNULL(serv.isVoid,0)=0 AND (@folio_list='' OR (SELECT [FRONTOFFICE].[fn_GET_FOLIO_ID] (@resvId,@OS,serv.guest_id,@emptyguid)) IN ( SELECT * FROM @tableFolio)) AND ISNULL( POST.posting_id,@emptyguid)=@emptyguid )!=0) BEGIN INSERT INTO @TABLEAVALUE (TYPE) VALUES ('OTHERS') END END END BEGIN IF( ( SELECT COUNT(*) FROM frontoffice.fo_g_package_usage pUse join frontoffice.fo_g_resv_package_service resvPkg on pUse.resv_package_service_id = resvPkg.resv_package_service_id join frontoffice.fo_g_package_service pServ on resvPkg.package_service_id = pServ.package_service_id join frontoffice.fo_g_package pkg on pServ.package_id = pkg.package_id INNER JOIN frontoffice.FO_G_SERVICE_TYPE STYPE ON pServ.package_other_service_id = STYPE.SERVICE_TYPE_ID join frontoffice.fo_resv_reservation_master resvM on resvPkg.reservation_id = resvM.reservation_id LEFT JOIN frontoffice.FO_POSTING PST ON pUse.package_usage_id = PST.POST_REF_ID AND PST.POST_TYPE = 'OTHERS' AND ISNULL(pst.post_receipt_no,'')='' where ISNULL( PST.posting_id,@emptyguid)=@emptyguid AND (ISNULL(@resvId,@emptyguid) =@emptyguid OR resvPkg.reservation_id=@resvId ) AND(ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(Date, pUse.package_usage_date) = @date)) != 0) BEGIN INSERT INTO @TABLEAVALUE (TYPE) VALUES ('PACKAGE') END END BEGIN--Cancellation IF( ( SELECT COUNT(*) FROM frontoffice.FO_RESV_CNCL_RETENTION_DTLS CANCEL LEFT JOIN frontoffice.FO_POSTING POST ON POST.POST_REF_ID=CANCEL.CNCL_RETEN_ID AND POST.POST_TYPE=CHARGE_TYPE AND ISNULL(POST.POST_SUB_TYPE,'') = (CASE WHEN CANCEL.RESV_STAT_ID IS NULL THEN 'ROOM' ELSE 'RESERVATION' END) --AND POST_DATE = @DATE LEFT JOIN frontoffice.fo_folio_master folio ON( folio.folio_type = CASE WHEN CANCEL.charge_apply_to='Agent' THEN 'CompanyFolio' WHEN CANCEL.charge_apply_to='Guest' THEN 'GuestFolio' ELSE 'MasterFolio' END ) AND folio.folio_resv_id = CANCEL.Resv_id AND ISNULL( folio.folio_gst_id ,@emptyguid)= CASE WHEN CANCEL.charge_apply_to='Guest' THEN CANCEL.guest_id ELSE ISNULL( folio.folio_gst_id ,@emptyguid) END AND ISNULL( folio.folio_cmp_id ,@emptyguid)= CASE WHEN CANCEL.charge_apply_to='Agent' THEN @compId ELSE ISNULL( folio.folio_cmp_id ,@emptyguid) END WHERE --@DATE = CONVERT(DATE, CANCEL.CREATED_DATE) (ISNULL(@resvId,@emptyguid) =@emptyguid OR CANCEL.resv_id = @resvId)--AND CONVERT(DATE, DMY.CHECKIN_DATE) AND(ISNULL(@resvId,@emptyguid) <>@emptyguid OR CONVERT(DATE, CANCEL.created_date )= @date)AND ISNULL(CANCEL.void,0)=0 AND (@folio_list='' OR (FOLIO.folio_id IN ( SELECT * FROM @tableFolio) )) AND (ISNULL(CANCEL.charge_applied,0) + ISNULL(CANCEL.additional_charges,0))>0 AND ISNULL( POST.POSTING_ID,@emptyguid)=@emptyguid )>0) BEGIN INSERT INTO @TABLEAVALUE(Type) VALUES ('CANCELLATION_RETENTION') END END IF(@returnBit=1) BEGIN SET @hasPending = CASE WHEN EXISTS(SELECT * FROM @TABLEAVALUE) THEN 1 ELSE 0 END END ELSE BEGIN select distinct * from @TABLEAVALUE END END GO--------Cashier report------------------------alter PROC frontoffice.sp_cash_Rep_Single --[frontoffice].[sp_cash_Rep_Single] '2025-01-21' ,'SETTLEMENT','00000000-0000-0000-0000-000000000000','2025-01-28'@DATE DATE ,@TYPE VARCHAR(50),@userid uniqueidentifier,@DATETO DATE ASBEGIN --DECLARE @DATE DATE ='2026-03-19' -- DECLARE @DATETO DATE ='2026-03-20' --DECLARE @TYPE VARCHAR(50)='settlement' --Declare @userid uniqueidentifier ='e12b3ddc-1214-4eed-bb6c-8392c165f419' DECLARE @LOCAL_CURR NVARCHAR(10) = (SELECT TOP 1 ShortName FROM core.Currencies) DECLARE @RESULT TABLE( ROOM NVARCHAR(max) ,REG NVARCHAR(20) ,BILL_NO NVARCHAR(20) ,GUESTNAME NVARCHAR(100) ,SETT_MODE NVARCHAR(50) ,REMARKS NVARCHAR(Max) ,EMPLOYEE NVARCHAR(100) ,AMOUNT NUMERIC(18,4) ,TIME NVARCHAR(20) ,CURR NVARCHAR(50) --DEFAULT @LOCAL_CURR ,EXCHRATE NUMERIC(18,2) DEFAULT 1 ) DECLARE @SC_ID uniqueidentifier,@VAT_ID uniqueidentifier SELECT @SC_ID = TAX.tax_slab_id FROM frontoffice.g_tax_slab TAX INNER JOIN frontoffice.g_tax_slab_items ITEM ON TAX.tax_slab_id = ITEM.tax_slab_id AND TAX.tax_slab_code='sc' SELECT @VAT_ID= TAX.tax_slab_id FROM frontoffice.g_tax_slab TAX INNER JOIN frontoffice.g_tax_slab_items ITEM ON TAX.tax_slab_id = ITEM.tax_slab_id AND TAX.tax_slab_code='VAT' IF(@TYPE='ADVANCE' OR @TYPE='DEPOSIT') BEGIN INSERT INTO @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM ,ISNULL(CONVERT(VARCHAR(100), REG.registration_number),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,DEP.dep_receipt ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME , CASE WHEN PAYMODE.Name='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME WHEN PAYMODE.Name = 'Wallet' THEN mw.Particular ELSE PAYMODE.Name END SETT_MODE ,DEP.DEP_PARTICULARS ,EMP.FirstName ,DEP.dep_recv_amt ,CONVERT(VARCHAR, DEP_TR_DATE,120) TIME -- SUBSTRING( CONVERT(VARCHAR, DEP_TR_DATE,108),1,5) TIME ,CURR.ShortName ,DEP.dep_exch_rate FROM frontoffice.FO_PAYMENT_DEPOSIT DEP INNER JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON DEP.DEP_FOLIO_ID = FOLIO.FOLIO_ID INNER JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID INNER JOIN core.Users EMP ON DEP.DEP_USER = EMP.ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON DEP.DEP_ROOM_ID = RM.ROOM_MASTER_ID LEFT JOIN core.PaymentModes PAYMODE ON PAYMODE.Id = DEP.dep_pay_type LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON DEP.DEP_CC_ID = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID LEFT JOIN frontoffice.fo_g_mbl_walet mw ON mw.Id =DEP.MobileWalletParticularId INNER JOIN core.Currencies CURR ON DEP.dep_curr_id = CURR.Id WHERE CONVERT( DATE, DEP.DEP_TR_DATE )between @DATE and @DATETO AND DEP.DEP_TYPE =@TYPE AND ( @userid = '00000000-0000-0000-0000-000000000000' OR DEP.DEP_USER = @userid ) ORDER BY CONVERT(TIME,DEP.DEP_TR_DATE) END ELSE IF(@TYPE='PAYMENT') BEGIN insert into @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM, ISNULL(CONVERT(VARCHAR(10), REG.REGISTRATION_ID),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,genPay.fo_payGen_receipt_no ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME --,PAYMODE.PAY_MODE_DESC , CASE WHEN PAYMODE.Name='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME WHEN PAYMODE.Name = 'Wallet' THEN mw.Particular ELSE PAYMODE.Name END SETT_MODE , CASE WHEN ISNULL(pymnt_agnst ,'')<>'' THEN ISNULL(pymnt_agnst,'')+ ' '+ISNULL(os.service_bill_no,'') ELSE genPay.fo_payGen_remarks END ,EMP.FirstName ,genPay.fo_payGen_paidAmount ,CONVERT(VARCHAR, fo_payGen_date,120) TIME-- SUBSTRING( CONVERT(VARCHAR, fo_payGen_date,108),1,5) TIME ,CURR.ShortName ,genPay.fo_payGen_exchRate FROM frontoffice.fo_Payment_General genPay INNER JOIN core.Users EMP ON genPay.created_by = EMP.ID INNER JOIN core.PaymentModes PAYMODE ON PAYMODE.Id = genPay.fo_payGen_pay_mode INNER JOIN core.Currencies CURR ON genPay.fo_payGen_curr_id = CURR.Id LEFT JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON genPay.fo_payGen_Folio_Id = FOLIO.FOLIO_ID left JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON RM.ROOM_MASTER_ID = [frontoffice].fn_getFolioRoomID(FOLIO.folio_id) LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON genPay.fo_payGen_cc_id = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID LEFT JOIN frontoffice.fo_g_mbl_walet mw ON mw.Id =genPay.MobileWalletParticularId LEFT JOIN frontoffice.fo_reg_guest_service os ON genPay.pymnt_agnst='OtherService' AND os.guest_service_id = genPay.fo_payGen_ref_id WHERE CONVERT( DATE, genPay.fo_payGen_date )between @DATE and @DATETO AND genPay.fo_payGen_ref_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR genPay.created_by = @userid) ORDER BY CONVERT(TIME,genPay.fo_payGen_date) END ELSE IF(@TYPE='PAIDOUT') BEGIN INSERT INTO @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM, ISNULL(CONVERT(VARCHAR(10), REG.registration_number),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,ISNULL( paidOut.cpo_receipt_no,'') ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME --,PAYMODE.PAY_MODE_DESC , CASE WHEN PAYMODE.PAY_MODE_DESC='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME ELSE PAYMODE.PAY_MODE_DESC END SETT_MODE ,paidOut.cpo_particulars ,EMP.FirstName ,paidOut.CPO_Amt ,CONVERT(VARCHAR, paidOut.CPO_Date,120) TIME -- SUBSTRING( CONVERT(VARCHAR, paidOut.CPO_Date,108),1,5) TIME ,CURR.ShortName ,paidOut.curr_exch_rate from frontoffice.fo_Payment_CPO paidOut INNER JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON paidOut.folio_id = FOLIO.FOLIO_ID INNER JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID INNER JOIN core.Users EMP ON paidOut.cpo_user = EMP.ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON RM.ROOM_MASTER_ID = [frontoffice].fn_getFolioRoomId(FOLIO.FOLIO_ID) LEFT JOIN frontoffice.FO_G_PAY_MODE PAYMODE ON PAYMODE.PAY_MODE_ID = paidOut.CPO_PayMode_id LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON paidOut.cpo_cc_resv_id = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID INNER JOIN core.Currencies CURR ON paidOut.curr_id = CURR.Id WHERE CONVERT( DATE, paidOut.CPO_Date )between @DATE and @DATETO AND ( @userid = '00000000-0000-0000-0000-000000000000' OR paidOut.cpo_user = @userid) ORDER BY CONVERT(TIME,paidOut.CPO_Date) END ELSE IF(@TYPE ='SETTLEMENT') BEGIN --BEGIN --AMOUNT VISIBLE/NOTVISIBLE TO USER declare @showRoomRate bit = 1 declare @userRole nvarchar(200) set @userRole = ISNULL(@userRole,'') declare @roleList nvarchar(max) select @roleList = config_key_desc from frontoffice.fo_g_configuration where config_key_value = 'RoomRatesVisibility' if(ISNULL(@roleList,'')<>'') BEGIN declare @roleListTable table (roles nvarchar(200)) insert into @roleListTable select * from [frontoffice].[FN_CONVERT_STRING_TO_LIST] (@roleList+',',',') delete from @roleListTable where Isnull(roles,'') ='' IF((SELECT COUNT(* )FROM @roleListTable WHERE roles = @userRole)=0) BEGIN SET @showRoomRate=0 END END --END DECLARE @ROOMNO NVARCHAR(50) ,@REG NVARCHAR(50) ,@BILLNO NVARCHAR(20) ,@GSTNAME NVARCHAR(100) ,@REMARKS NVARCHAR(max) ,@CASHAMT NUMERIC(18,2) ,@CHKAMT NUMERIC(18,2) ,@CCAMT NUMERIC(18,2) ,@CMPAMT NUMERIC(18,2) ,@MOBILEPAYMENT NUMERIC(18,2) ,@EMPNAME NVARCHAR(100) ,@TIME nvarchar(20) ,@SETT_DET_ID uniqueidentifier declare cur cursor fast_forward for SELECT (SELECT ROOMNO FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))ROOM ,(SELECT REGID FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))REG ,SETT_BILL_NUMBER BILLNO ,(SELECT GUEST FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))GUEST ,'' REMARKS ,EMP.FirstName ,CONVERT(VARCHAR, bill.CreatedDate,120) [Time] --,ISNULL( SD.sett_det_cash_amt,0) ,SD.sett_det_cash_amt ,ISNULL( SETT_DET_TOT_CHEQ_AMT,0) ,ISNULL( SETT_DET_TOT_CC_AMT,0) ,ISNULL( SETT_DET_CMP_EXCH_AMT,0) ,ISNULL( S.MobilePayment,0) ,SD.SETT_dET_ID FROM frontoffice.fo_sett_details SD INNER JOIN frontoffice.fo_settlement S ON S.SETT_ID = SD.SETT_DET_SETT_ID INNER JOIN frontoffice.fo_g_bill_details_type bill ON sd.sett_bill_number = bill.bill_details_bill_no INNER JOIN core.Users EMP ON EMP.ID = S.SETT_USER_ID WHERE convert(DAte,S.SETT_DATE ) between @DATE and @DATETO AND ( @userid = '00000000-0000-0000-0000-000000000000' OR S.SETT_USER_ID = @userid) order by S.SETT_DATE , (SUBSTRING( SD.sett_bill_number ,0 ,CASE when bill_details_type='billgeneration' THEN (select len(Code)from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 48) and S.SETT_DATE between startdate and enddate) ELSE (select len(Code) from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 53) and S.SETT_DATE between startdate and enddate)+1 end ) ) , CAST(SUBSTRING( SD.sett_bill_number ,CASE when bill_details_type='billgeneration' THEN (select len(Code)from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 48) and S.SETT_DATE between startdate and enddate)+1 ELSE (select len(Code) from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 53) and S.SETT_DATE between startdate and enddate)+1 end ,len( SD.sett_bill_number )) AS INTEGER) OPEN CUR FETCH NEXT FROM CUR INTO @ROOMNO ,@REG ,@BILLNO ,@GSTNAME ,@REMARKS ,@EMPNAME ,@TIME ,@CASHAMT ,@CHKAMT ,@CCAMT ,@CMPAMT ,@MOBILEPAYMENT ,@SETT_DET_ID WHILE (@@FETCH_STATUS=0) BEGIN IF(@CASHAMT<>0) BEGIN --SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME,'CASH',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'CASH',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR) END IF(@CHKAMT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'CHEQUE',@REMARKS,@EMPNAME,@CHKAMT,@TIME,@LOCAL_CURR) END DECLARE @hideRate bit = 1; --declare @showRoomRate bit =0 if( ( SELECT count(*) FROM frontoffice.fo_g_bill_details_type BILL left join frontoffice.fo_folio_master folio on bill.bill_genrted_to_folio = folio.folio_id left join frontoffice.fo_resv_reservation_master resv on BILL.bill_details_resv_id = resv.reservation_id where (@showRoomRate = 1 or isnull(resv.resv_company,'00000000-0000-0000-0000-000000000000')='00000000-0000-0000-0000-000000000000' or (folio.folio_type not in ('masterfolio','companyfolio'))) AND BILL.bill_details_bill_no = @BILLNO)=0) BEGIN SET @hideRate=1 END IF(@CMPAMT<>0 AND @hideRate=1) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'BILL TO COMPANY',@REMARKS,@EMPNAME,@CMPAMT,@TIME,@LOCAL_CURR) END IF(@CCAMT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME, cc.Name, @REMARKS,@EMPNAME, SUM(ccDet.sett_cc_exch_amt) ,@TIME,@LOCAL_CURR FROM frontoffice.fo_sett_cc_details ccDet left join frontoffice.fo_resv_reservation_credit_card ccResv on ccDet.sett_resv_cc_id = ccResv.resv_credit_card_id left join core.Banks cc on (ccdet.sett_resv_cc_id = cc.Id) where ccDet.sett_cc_sett_det_id = @SETT_DET_ID Group by cc.Name END IF(@MOBILEPAYMENT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) --(ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME, MW.Particular, @REMARKS,@EMPNAME, SUM(mbl.amount) ,@TIME,@LOCAL_CURR FROM frontoffice.fo_sett_mbl_wlet_details MBL --left JOIN frontoffice.fo_g_mbl_walet_prticulrs MBL_PART on MBL_PART.Id = MBL.mbl_wlet_prticlar_id left join frontoffice.fo_g_mbl_walet MW ON MW.Id = MBL.mbl_wlet_prticlar_id --LEFT JOIN core.PaymentModes paymode ON paymode.id = MW where mbl.sett_det_id= @SETT_DET_ID Group by MW.Particular END IF(@CASHAMT = 0 and @CHKAMT = 0 and @CMPAMT = 0 and @CCAMT = 0 AND @MOBILEPAYMENT=0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR) END FETCH NEXT FROM CUR INTO @ROOMNO ,@REG ,@BILLNO ,@GSTNAME ,@REMARKS ,@EMPNAME ,@TIME ,@CASHAMT ,@CHKAMT ,@CCAMT ,@CMPAMT ,@MOBILEPAYMENT ,@SETT_DET_ID END DEALLOCATE CUR INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT ( SELECT TOP 1 rm.room_number FROM frontoffice.fo_resv_reservation_assign_guest_rooms asgRoom INNER JOIN frontoffice.fo_g_room_master rm ON rm.room_master_id = asgRoom.room_id WHERE asgRoom.resv_guest_id = resvGst.resv_guest_id AND ISNULL(asgRoom.room_transfer,'')='') Room ,reg.registration_number ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'ROOM GUEST' payMode ,'' , emp.FirstName --, 0+@CASHAMT+ @CHKAMT+ @CMPAMT+ @CCAMT+@MOBILEPAYMENT ,ROUND( CONVERT(NUMERIC(18,2), ( (100 + ISNULL(sc.taxtn_chrgAmt, 0) + ISNULL(vat.taxtn_chrgAmt, 0)) * (ISNULL(PST.post_chrg_amt, 0) * ISNULL(PST.post_curr_exhc_rate, 0)) / NULLIF(100, 0) ) ), 0) AS Amount,CONVERT(varchar(15),serv.serv_created_date,120) [Time] --,CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv --LEFT JOIN dbo.fo_g_room_master rm ON serv.rm LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_registration_master reg ON serv.reg_id = reg.registration_id LEFT JOIN frontoffice.fo_resv_guest_reservation_detail resvGst ON reg.reservation_id = resvGst.resv_id AND resvGst.guest_id = gst.guest_id --LEFT JOIN dbo.fo_resv_reservation_assign_guest_rooms asgRoom ON asgRoom.resv_guest_id = resvGst.resv_guest_id AND ISNULL(asgRoom.room_transfer,'')='' --LEFT JOIN dbo.fo_g_room_master rm ON rm.room_master_id = asgRoom.room_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN core.PaymentModes payMode ON payMode.Id = pay.fo_payGen_pay_mode LEFT JOIN frontoffice.fo_posting pst ON pst.post_ref_id = serv.guest_service_id AND pst.post_type='OTHERS' LEFT JOIN frontoffice.fo_posting_taxation sc ON PST.posting_id = sc.taxtn_Postin_id AND sc.taxtn_serv_id = @SC_ID LEFT JOIN frontoffice.fo_posting_taxation vat ON PST.posting_id = vat.taxtn_Postin_id AND vat.taxtn_serv_id = @VAT_ID LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and serv.reg_id IS NOT NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --cash INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'CASH' PayMode ,'' , emp.FirstName ,cash_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.cash_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --card INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'CREDIT CARD' PayMode ,'' , emp.FirstName ,serv.GrandTotal Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.card_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --wallet INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'Mobile Wallet' PayMode ,'' , emp.FirstName ,wallet_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.wallet_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --credit INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'BILL TO COMPANY' PayMode ,'' , emp.FirstName ,credit_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.credit_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --CHEQUE INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'BILL TO COMPANY' PayMode ,'' , emp.FirstName ,cheque_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.cheque_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) END SELECT * FROM @RESULT order by BILL_NOEND----GO----------------------------ALTER TABLE frontoffice.fo_reg_guest_service ADD PrintCount INT NULL GOALTER TABLE frontoffice.RoleWiseActionsADD billSummaryReport BIT NOT NULL DEFAULT 1, dashboard BIT NOT NULL DEFAULT 1, guestReservationCard BIT NOT NULL DEFAULT 1, guestRegister BIT NOT NULL DEFAULT 1, stayCalender BIT NOT NULL DEFAULT 1, guestArrivalList BIT NOT NULL DEFAULT 1, guestDeparture BIT NOT NULL DEFAULT 1, dailyMealPlan BIT NOT NULL DEFAULT 1, businessSourceTransaction BIT NOT NULL DEFAULT 1, marketSegmentTransaction BIT NOT NULL DEFAULT 1, materializedViewReport BIT NOT NULL DEFAULT 1, complimentory BIT NOT NULL DEFAULT 1, companyHistory BIT NOT NULL DEFAULT 1, salesSummary BIT NOT NULL DEFAULT 1, salesRegister BIT NOT NULL DEFAULT 1, cancelledReservation BIT NOT NULL DEFAULT 1, keyReport BIT NOT NULL DEFAULT 1, roomrevenuesummary BIT NOT NULL DEFAULT 1, roomTransfer BIT NOT NULL DEFAULT 1, visitorRegister BIT NOT NULL DEFAULT 1, packageSalesReport BIT NOT NULL DEFAULT 1, roomOccupancyReport BIT NOT NULL DEFAULT 1, roomAvailabilityReport BIT NOT NULL DEFAULT 1, auditLogReport BIT NOT NULL DEFAULT 1, managerReport BIT NOT NULL DEFAULT 1,resvConfirmation BIT NOT NULL DEFAULT 1;--------------ALTER TABLE frontoffice.RoleWiseActionsADD flashReport BIT NOT NULL DEFAULT 1, forecastReport BIT NOT NULL DEFAULT 1, salesRegisterReturn BIT NOT NULL DEFAULT 1, dayUse BIT NOT NULL DEFAULT 1, cashierReport BIT NOT NULL DEFAULT 1 ;---------------22apr--------ALTER TABLE frontoffice.fo_g_bill_details_type ADD PrintCount INT NULLGO------------------------------------------CREATE TRIGGER frontoffice.TRG_Check_DepartureDateON frontoffice.fo_resv_reservation_masterAFTER INSERT, UPDATEASBEGIN SET NOCOUNT ON; -- Check invalid rows IF EXISTS ( SELECT 1 FROM inserted WHERE CAST(resv_dep_date AS DATE) < CAST(resv_arr_date AS DATE) ) BEGIN RAISERROR ('Departure date cannot be earlier than arrival date.', 16, 1); ROLLBACK TRANSACTION; RETURN; ENDEND;GO DISABLE TRIGGER frontoffice.TRG_Check_DepartureDate ON frontoffice.fo_resv_reservation_masterGO----------------27apr---------ALTER TABLE frontoffice.ManagerReports ADD BranchId UNIQUEIDENTIFIER NULL, DepartmentId UNIQUEIDENTIFIER NULLGOALTER TABLE frontoffice.fo_resv_reservation_assign_guest_roomsADD CONSTRAINT CK_StayDate_ValidCHECK (stay_date_to >= stay_date_from);alter PROC frontoffice.sp_cash_Rep_Single --[frontoffice].[sp_cash_Rep_Single] '2025-01-21' ,'SETTLEMENT','00000000-0000-0000-0000-000000000000','2025-01-28'@DATE DATE ,@TYPE VARCHAR(50),@userid uniqueidentifier,@DATETO DATE ASBEGIN --DECLARE @DATE DATE ='2026-03-19' -- DECLARE @DATETO DATE ='2026-03-20' --DECLARE @TYPE VARCHAR(50)='settlement' --Declare @userid uniqueidentifier ='e12b3ddc-1214-4eed-bb6c-8392c165f419' DECLARE @LOCAL_CURR NVARCHAR(10) = (SELECT TOP 1 ShortName FROM core.Currencies) DECLARE @RESULT TABLE( ROOM NVARCHAR(max) ,REG NVARCHAR(20) ,BILL_NO NVARCHAR(20) ,GUESTNAME NVARCHAR(100) ,SETT_MODE NVARCHAR(50) ,REMARKS NVARCHAR(Max) ,EMPLOYEE NVARCHAR(100) ,AMOUNT NUMERIC(18,4) ,TIME NVARCHAR(20) ,CURR NVARCHAR(50) --DEFAULT @LOCAL_CURR ,EXCHRATE NUMERIC(18,2) DEFAULT 1 ) DECLARE @SC_ID uniqueidentifier,@VAT_ID uniqueidentifier SELECT @SC_ID = TAX.tax_slab_id FROM frontoffice.g_tax_slab TAX INNER JOIN frontoffice.g_tax_slab_items ITEM ON TAX.tax_slab_id = ITEM.tax_slab_id AND TAX.tax_slab_code='sc' SELECT @VAT_ID= TAX.tax_slab_id FROM frontoffice.g_tax_slab TAX INNER JOIN frontoffice.g_tax_slab_items ITEM ON TAX.tax_slab_id = ITEM.tax_slab_id AND TAX.tax_slab_code='VAT' IF(@TYPE='ADVANCE' OR @TYPE='DEPOSIT') BEGIN INSERT INTO @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM ,ISNULL(CONVERT(VARCHAR(100), REG.registration_number),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,DEP.dep_receipt ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME , CASE WHEN PAYMODE.Name='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME WHEN PAYMODE.Name = 'Wallet' THEN mw.Particular ELSE PAYMODE.Name END SETT_MODE ,DEP.DEP_PARTICULARS ,EMP.FirstName ,DEP.dep_recv_amt ,CONVERT(VARCHAR, DEP_TR_DATE,120) TIME -- SUBSTRING( CONVERT(VARCHAR, DEP_TR_DATE,108),1,5) TIME ,CURR.ShortName ,DEP.dep_exch_rate FROM frontoffice.FO_PAYMENT_DEPOSIT DEP INNER JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON DEP.DEP_FOLIO_ID = FOLIO.FOLIO_ID INNER JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID INNER JOIN core.Users EMP ON DEP.DEP_USER = EMP.ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON DEP.DEP_ROOM_ID = RM.ROOM_MASTER_ID LEFT JOIN core.PaymentModes PAYMODE ON PAYMODE.Id = DEP.dep_pay_type LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON DEP.DEP_CC_ID = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID LEFT JOIN frontoffice.fo_g_mbl_walet mw ON mw.Id =DEP.MobileWalletParticularId INNER JOIN core.Currencies CURR ON DEP.dep_curr_id = CURR.Id WHERE CONVERT( DATE, DEP.DEP_TR_DATE )between @DATE and @DATETO AND DEP.DEP_TYPE =@TYPE AND ( @userid = '00000000-0000-0000-0000-000000000000' OR DEP.DEP_USER = @userid ) ORDER BY CONVERT(TIME,DEP.DEP_TR_DATE) END ELSE IF(@TYPE='PAYMENT') BEGIN insert into @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM, ISNULL(CONVERT(VARCHAR(10), REG.REGISTRATION_ID),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,genPay.fo_payGen_receipt_no ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME --,PAYMODE.PAY_MODE_DESC , CASE WHEN PAYMODE.Name='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME WHEN PAYMODE.Name = 'Wallet' THEN mw.Particular ELSE PAYMODE.Name END SETT_MODE , CASE WHEN ISNULL(pymnt_agnst ,'')<>'' THEN ISNULL(pymnt_agnst,'')+ ' '+ISNULL(os.service_bill_no,'') ELSE genPay.fo_payGen_remarks END ,EMP.FirstName ,genPay.fo_payGen_paidAmount ,CONVERT(VARCHAR, fo_payGen_date,120) TIME-- SUBSTRING( CONVERT(VARCHAR, fo_payGen_date,108),1,5) TIME ,CURR.ShortName ,genPay.fo_payGen_exchRate FROM frontoffice.fo_Payment_General genPay INNER JOIN core.Users EMP ON genPay.created_by = EMP.ID INNER JOIN core.PaymentModes PAYMODE ON PAYMODE.Id = genPay.fo_payGen_pay_mode INNER JOIN core.Currencies CURR ON genPay.fo_payGen_curr_id = CURR.Id LEFT JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON genPay.fo_payGen_Folio_Id = FOLIO.FOLIO_ID left JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON RM.ROOM_MASTER_ID = [frontoffice].fn_getFolioRoomID(FOLIO.folio_id) LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON genPay.fo_payGen_cc_id = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID LEFT JOIN frontoffice.fo_g_mbl_walet mw ON mw.Id =genPay.MobileWalletParticularId LEFT JOIN frontoffice.fo_reg_guest_service os ON genPay.pymnt_agnst='OtherService' AND os.guest_service_id = genPay.fo_payGen_ref_id WHERE CONVERT( DATE, genPay.fo_payGen_date )between @DATE and @DATETO AND genPay.fo_payGen_ref_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR genPay.created_by = @userid) ORDER BY CONVERT(TIME,genPay.fo_payGen_date) END ELSE IF(@TYPE='PAIDOUT') BEGIN INSERT INTO @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM, ISNULL(CONVERT(VARCHAR(10), REG.registration_number),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,ISNULL( paidOut.cpo_receipt_no,'') ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME --,PAYMODE.PAY_MODE_DESC , CASE WHEN PAYMODE.PAY_MODE_DESC='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME ELSE PAYMODE.PAY_MODE_DESC END SETT_MODE ,paidOut.cpo_particulars ,EMP.FirstName ,paidOut.CPO_Amt ,CONVERT(VARCHAR, paidOut.CPO_Date,120) TIME -- SUBSTRING( CONVERT(VARCHAR, paidOut.CPO_Date,108),1,5) TIME ,CURR.curr_short_name ,paidOut.curr_exch_rate from frontoffice.fo_Payment_CPO paidOut INNER JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON paidOut.folio_id = FOLIO.FOLIO_ID INNER JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID INNER JOIN core.Users EMP ON paidOut.cpo_user = EMP.ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON RM.ROOM_MASTER_ID = [frontoffice].fn_getFolioRoomId(FOLIO.FOLIO_ID) LEFT JOIN frontoffice.FO_G_PAY_MODE PAYMODE ON PAYMODE.PAY_MODE_ID = paidOut.CPO_PayMode_id LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON paidOut.cpo_cc_resv_id = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID INNER JOIN frontoffice.g_currency CURR ON paidOut.curr_id = CURR.curr_id WHERE CONVERT( DATE, paidOut.CPO_Date )between @DATE and @DATETO AND ( @userid = '00000000-0000-0000-0000-000000000000' OR paidOut.cpo_user = @userid) ORDER BY CONVERT(TIME,paidOut.CPO_Date) END ELSE IF(@TYPE ='SETTLEMENT') BEGIN --BEGIN --AMOUNT VISIBLE/NOTVISIBLE TO USER declare @showRoomRate bit = 1 declare @userRole nvarchar(200) set @userRole = ISNULL(@userRole,'') declare @roleList nvarchar(max) select @roleList = config_key_desc from frontoffice.fo_g_configuration where config_key_value = 'RoomRatesVisibility' if(ISNULL(@roleList,'')<>'') BEGIN declare @roleListTable table (roles nvarchar(200)) insert into @roleListTable select * from [frontoffice].[FN_CONVERT_STRING_TO_LIST] (@roleList+',',',') delete from @roleListTable where Isnull(roles,'') ='' IF((SELECT COUNT(* )FROM @roleListTable WHERE roles = @userRole)=0) BEGIN SET @showRoomRate=0 END END --END DECLARE @ROOMNO NVARCHAR(50) ,@REG NVARCHAR(50) ,@BILLNO NVARCHAR(20) ,@GSTNAME NVARCHAR(100) ,@REMARKS NVARCHAR(max) ,@CASHAMT NUMERIC(18,2) ,@CHKAMT NUMERIC(18,2) ,@CCAMT NUMERIC(18,2) ,@CMPAMT NUMERIC(18,2) ,@MOBILEPAYMENT NUMERIC(18,2) ,@EMPNAME NVARCHAR(100) ,@TIME nvarchar(20) ,@SETT_DET_ID uniqueidentifier declare cur cursor fast_forward for SELECT (SELECT ROOMNO FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))ROOM ,(SELECT REGID FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))REG ,SETT_BILL_NUMBER BILLNO ,(SELECT GUEST FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))GUEST ,'' REMARKS ,EMP.FirstName ,CONVERT(VARCHAR, bill.CreatedDate,120) [Time] --,ISNULL( SD.sett_det_cash_amt,0) ,SD.sett_det_cash_amt ,ISNULL( SETT_DET_TOT_CHEQ_AMT,0) ,ISNULL( SETT_DET_TOT_CC_AMT,0) ,ISNULL( SETT_DET_CMP_EXCH_AMT,0) ,ISNULL( S.MobilePayment,0) ,SD.SETT_dET_ID FROM frontoffice.fo_sett_details SD INNER JOIN frontoffice.fo_settlement S ON S.SETT_ID = SD.SETT_DET_SETT_ID INNER JOIN frontoffice.fo_g_bill_details_type bill ON sd.sett_bill_number = bill.bill_details_bill_no INNER JOIN core.Users EMP ON EMP.ID = S.SETT_USER_ID WHERE convert(DAte,S.SETT_DATE ) between @DATE and @DATETO AND ( @userid = '00000000-0000-0000-0000-000000000000' OR S.SETT_USER_ID = @userid) order by S.SETT_DATE , (SUBSTRING( SD.sett_bill_number ,0 ,CASE when bill_details_type='billgeneration' THEN (select len(Code)from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 48) and S.SETT_DATE between startdate and enddate) ELSE (select len(Code) from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 53) and S.SETT_DATE between startdate and enddate)+1 end ) ) , CAST(SUBSTRING( SD.sett_bill_number ,CASE when bill_details_type='billgeneration' THEN (select len(Code)from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 48) and S.SETT_DATE between startdate and enddate)+1 ELSE (select len(Code) from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 53) and S.SETT_DATE between startdate and enddate)+1 end ,len( SD.sett_bill_number )) AS INTEGER) OPEN CUR FETCH NEXT FROM CUR INTO @ROOMNO ,@REG ,@BILLNO ,@GSTNAME ,@REMARKS ,@EMPNAME ,@TIME ,@CASHAMT ,@CHKAMT ,@CCAMT ,@CMPAMT ,@MOBILEPAYMENT ,@SETT_DET_ID WHILE (@@FETCH_STATUS=0) BEGIN IF(@CASHAMT<>0) BEGIN --SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME,'CASH',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'CASH',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR) END IF(@CHKAMT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'CHEQUE',@REMARKS,@EMPNAME,@CHKAMT,@TIME,@LOCAL_CURR) END DECLARE @hideRate bit = 1; --declare @showRoomRate bit =0 if( ( SELECT count(*) FROM frontoffice.fo_g_bill_details_type BILL left join frontoffice.fo_folio_master folio on bill.bill_genrted_to_folio = folio.folio_id left join frontoffice.fo_resv_reservation_master resv on BILL.bill_details_resv_id = resv.reservation_id where (@showRoomRate = 1 or isnull(resv.resv_company,'00000000-0000-0000-0000-000000000000')='00000000-0000-0000-0000-000000000000' or (folio.folio_type not in ('masterfolio','companyfolio'))) AND BILL.bill_details_bill_no = @BILLNO)=0) BEGIN SET @hideRate=1 END IF(@CMPAMT<>0 AND @hideRate=1) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'BILL TO COMPANY',@REMARKS,@EMPNAME,@CMPAMT,@TIME,@LOCAL_CURR) END IF(@CCAMT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME, cc.Name, @REMARKS,@EMPNAME, SUM(ccDet.sett_cc_exch_amt) ,@TIME,@LOCAL_CURR FROM frontoffice.fo_sett_cc_details ccDet left join frontoffice.fo_resv_reservation_credit_card ccResv on ccDet.sett_resv_cc_id = ccResv.resv_credit_card_id left join core.Banks cc on (ccdet.sett_resv_cc_id = cc.Id) where ccDet.sett_cc_sett_det_id = @SETT_DET_ID Group by cc.Name END IF(@MOBILEPAYMENT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) --(ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME, MW.Particular, @REMARKS,@EMPNAME, SUM(mbl.amount) ,@TIME,@LOCAL_CURR FROM frontoffice.fo_sett_mbl_wlet_details MBL --left JOIN frontoffice.fo_g_mbl_walet_prticulrs MBL_PART on MBL_PART.Id = MBL.mbl_wlet_prticlar_id left join frontoffice.fo_g_mbl_walet MW ON MW.Id = MBL.mbl_wlet_prticlar_id --LEFT JOIN core.PaymentModes paymode ON paymode.id = MW where mbl.sett_det_id= @SETT_DET_ID Group by MW.Particular END IF(@CASHAMT = 0 and @CHKAMT = 0 and @CMPAMT = 0 and @CCAMT = 0 AND @MOBILEPAYMENT=0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR) END FETCH NEXT FROM CUR INTO @ROOMNO ,@REG ,@BILLNO ,@GSTNAME ,@REMARKS ,@EMPNAME ,@TIME ,@CASHAMT ,@CHKAMT ,@CCAMT ,@CMPAMT ,@MOBILEPAYMENT ,@SETT_DET_ID END DEALLOCATE CUR INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT ( SELECT TOP 1 rm.room_number FROM frontoffice.fo_resv_reservation_assign_guest_rooms asgRoom INNER JOIN frontoffice.fo_g_room_master rm ON rm.room_master_id = asgRoom.room_id WHERE asgRoom.resv_guest_id = resvGst.resv_guest_id AND ISNULL(asgRoom.room_transfer,'')='') Room ,reg.registration_number ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'ROOM GUEST' payMode ,'' , emp.FirstName --, 0+@CASHAMT+ @CHKAMT+ @CMPAMT+ @CCAMT+@MOBILEPAYMENT ,ROUND( CONVERT(NUMERIC(18,2), ( (100 + ISNULL(sc.taxtn_chrgAmt, 0) + ISNULL(vat.taxtn_chrgAmt, 0)) * (ISNULL(PST.post_chrg_amt, 0) * ISNULL(PST.post_curr_exhc_rate, 0)) / NULLIF(100, 0) ) ), 0) AS Amount,CONVERT(varchar(15),serv.serv_created_date,120) [Time] --,CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv --LEFT JOIN dbo.fo_g_room_master rm ON serv.rm LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_registration_master reg ON serv.reg_id = reg.registration_id LEFT JOIN frontoffice.fo_resv_guest_reservation_detail resvGst ON reg.reservation_id = resvGst.resv_id AND resvGst.guest_id = gst.guest_id --LEFT JOIN dbo.fo_resv_reservation_assign_guest_rooms asgRoom ON asgRoom.resv_guest_id = resvGst.resv_guest_id AND ISNULL(asgRoom.room_transfer,'')='' --LEFT JOIN dbo.fo_g_room_master rm ON rm.room_master_id = asgRoom.room_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN core.PaymentModes payMode ON payMode.Id = pay.fo_payGen_pay_mode LEFT JOIN frontoffice.fo_posting pst ON pst.post_ref_id = serv.guest_service_id AND pst.post_type='OTHERS' LEFT JOIN frontoffice.fo_posting_taxation sc ON PST.posting_id = sc.taxtn_Postin_id AND sc.taxtn_serv_id = @SC_ID LEFT JOIN frontoffice.fo_posting_taxation vat ON PST.posting_id = vat.taxtn_Postin_id AND vat.taxtn_serv_id = @VAT_ID LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and serv.reg_id IS NOT NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --cash INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'CASH' PayMode ,'' , emp.FirstName ,cash_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.cash_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --card INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'CREDIT CARD' PayMode ,'' , emp.FirstName ,serv.GrandTotal Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.card_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --wallet INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'Mobile Wallet' PayMode ,'' , emp.FirstName ,wallet_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.wallet_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --credit INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'BILL TO COMPANY' PayMode ,'' , emp.FirstName ,credit_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.credit_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --CHEQUE INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'BILL TO COMPANY' PayMode ,'' , emp.FirstName ,cheque_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.cheque_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) END SELECT * FROM @RESULT order by BILL_NOENDGO----29mar-----------ALTER TABLE frontoffice.fo_resv_reservation_assign_guest_roomsADD CONSTRAINT CK_StayDate_ValidCHECK (stay_date_to >= stay_date_from);-----------alter PROC frontoffice.sp_cash_Rep_Single --[frontoffice].[sp_cash_Rep_Single] '2025-01-21' ,'SETTLEMENT','00000000-0000-0000-0000-000000000000','2025-01-28'@DATE DATE ,@TYPE VARCHAR(50),@userid uniqueidentifier,@DATETO DATE ASBEGIN --DECLARE @DATE DATE ='2026-03-19' -- DECLARE @DATETO DATE ='2026-03-20' --DECLARE @TYPE VARCHAR(50)='settlement' --Declare @userid uniqueidentifier ='e12b3ddc-1214-4eed-bb6c-8392c165f419' DECLARE @LOCAL_CURR NVARCHAR(10) = (SELECT TOP 1 ShortName FROM core.Currencies) DECLARE @RESULT TABLE( ROOM NVARCHAR(max) ,REG NVARCHAR(20) ,BILL_NO NVARCHAR(20) ,GUESTNAME NVARCHAR(100) ,SETT_MODE NVARCHAR(50) ,REMARKS NVARCHAR(Max) ,EMPLOYEE NVARCHAR(100) ,AMOUNT NUMERIC(18,4) ,TIME NVARCHAR(20) ,CURR NVARCHAR(50) --DEFAULT @LOCAL_CURR ,EXCHRATE NUMERIC(18,2) DEFAULT 1 ) DECLARE @SC_ID uniqueidentifier,@VAT_ID uniqueidentifier SELECT @SC_ID = TAX.tax_slab_id FROM frontoffice.g_tax_slab TAX INNER JOIN frontoffice.g_tax_slab_items ITEM ON TAX.tax_slab_id = ITEM.tax_slab_id AND TAX.tax_slab_code='sc' SELECT @VAT_ID= TAX.tax_slab_id FROM frontoffice.g_tax_slab TAX INNER JOIN frontoffice.g_tax_slab_items ITEM ON TAX.tax_slab_id = ITEM.tax_slab_id AND TAX.tax_slab_code='VAT' IF(@TYPE='ADVANCE' OR @TYPE='DEPOSIT') BEGIN INSERT INTO @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM ,ISNULL(CONVERT(VARCHAR(100), REG.registration_number),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,DEP.dep_receipt ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME , CASE WHEN PAYMODE.Name='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME WHEN PAYMODE.Name = 'Wallet' THEN mw.Particular ELSE PAYMODE.Name END SETT_MODE ,DEP.DEP_PARTICULARS ,EMP.FirstName ,DEP.dep_recv_amt ,CONVERT(VARCHAR, DEP_TR_DATE,120) TIME -- SUBSTRING( CONVERT(VARCHAR, DEP_TR_DATE,108),1,5) TIME ,CURR.ShortName ,DEP.dep_exch_rate FROM frontoffice.FO_PAYMENT_DEPOSIT DEP INNER JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON DEP.DEP_FOLIO_ID = FOLIO.FOLIO_ID INNER JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID INNER JOIN core.Users EMP ON DEP.DEP_USER = EMP.ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON DEP.DEP_ROOM_ID = RM.ROOM_MASTER_ID LEFT JOIN core.PaymentModes PAYMODE ON PAYMODE.Id = DEP.dep_pay_type LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON DEP.DEP_CC_ID = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID LEFT JOIN frontoffice.fo_g_mbl_walet mw ON mw.Id =DEP.MobileWalletParticularId INNER JOIN core.Currencies CURR ON DEP.dep_curr_id = CURR.Id WHERE CONVERT( DATE, DEP.DEP_TR_DATE )between @DATE and @DATETO AND DEP.DEP_TYPE =@TYPE AND ( @userid = '00000000-0000-0000-0000-000000000000' OR DEP.DEP_USER = @userid ) ORDER BY CONVERT(TIME,DEP.DEP_TR_DATE) END ELSE IF(@TYPE='PAYMENT') BEGIN insert into @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM, ISNULL(CONVERT(VARCHAR(10), REG.REGISTRATION_ID),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,genPay.fo_payGen_receipt_no ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME --,PAYMODE.PAY_MODE_DESC , CASE WHEN PAYMODE.Name='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME WHEN PAYMODE.Name = 'Wallet' THEN mw.Particular ELSE PAYMODE.Name END SETT_MODE , CASE WHEN ISNULL(pymnt_agnst ,'')<>'' THEN ISNULL(pymnt_agnst,'')+ ' '+ISNULL(os.service_bill_no,'') ELSE genPay.fo_payGen_remarks END ,EMP.FirstName ,genPay.fo_payGen_paidAmount ,CONVERT(VARCHAR, fo_payGen_date,120) TIME-- SUBSTRING( CONVERT(VARCHAR, fo_payGen_date,108),1,5) TIME ,CURR.ShortName ,genPay.fo_payGen_exchRate FROM frontoffice.fo_Payment_General genPay INNER JOIN core.Users EMP ON genPay.created_by = EMP.ID INNER JOIN core.PaymentModes PAYMODE ON PAYMODE.Id = genPay.fo_payGen_pay_mode INNER JOIN core.Currencies CURR ON genPay.fo_payGen_curr_id = CURR.Id LEFT JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON genPay.fo_payGen_Folio_Id = FOLIO.FOLIO_ID left JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON RM.ROOM_MASTER_ID = [frontoffice].fn_getFolioRoomID(FOLIO.folio_id) LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON genPay.fo_payGen_cc_id = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID LEFT JOIN frontoffice.fo_g_mbl_walet mw ON mw.Id =genPay.MobileWalletParticularId LEFT JOIN frontoffice.fo_reg_guest_service os ON genPay.pymnt_agnst='OtherService' AND os.guest_service_id = genPay.fo_payGen_ref_id WHERE CONVERT( DATE, genPay.fo_payGen_date )between @DATE and @DATETO AND genPay.fo_payGen_ref_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR genPay.created_by = @userid) ORDER BY CONVERT(TIME,genPay.fo_payGen_date) END ELSE IF(@TYPE='PAIDOUT') BEGIN INSERT INTO @RESULT SELECT ISNULL(RM.ROOM_NUMBER,'')ROOM, ISNULL(CONVERT(VARCHAR(10), REG.registration_number),'BOOKED ONLY['+CONVERT(VARCHAR(10),RESV.reservation_number )+']') REG ,ISNULL( paidOut.cpo_receipt_no,'') ,(CASE WHEN FOLIO.FOLIO_TYPE='GUESTFOLIO' THEN GST.GUEST_NAME WHEN FOLIO.FOLIO_TYPE ='MASTERFOLIO' THEN RESV.resv_group_name WHEN FOLIO.FOLIO_TYPE = 'COMPANYFOLIO' THEN CMP.COMPANY_NAME END) GUESTNAME --,PAYMODE.PAY_MODE_DESC , CASE WHEN PAYMODE.PAY_MODE_DESC='CREDIT CARD' THEN CCMASTER.CREDIT_CARD_NAME ELSE PAYMODE.PAY_MODE_DESC END SETT_MODE ,paidOut.cpo_particulars ,EMP.FirstName ,paidOut.CPO_Amt ,CONVERT(VARCHAR, paidOut.CPO_Date,120) TIME -- SUBSTRING( CONVERT(VARCHAR, paidOut.CPO_Date,108),1,5) TIME ,CURR.curr_short_name ,paidOut.curr_exch_rate from frontoffice.fo_Payment_CPO paidOut INNER JOIN frontoffice.FO_FOLIO_MASTER FOLIO ON paidOut.folio_id = FOLIO.FOLIO_ID INNER JOIN frontoffice.FO_RESV_RESERVATION_MASTER RESV ON FOLIO.FOLIO_RESV_ID = RESV.RESERVATION_ID INNER JOIN core.Users EMP ON paidOut.cpo_user = EMP.ID LEFT JOIN frontoffice.FO_REGISTRATION_MASTER REG ON FOLIO.FOLIO_RESV_ID = REG.RESERVATION_ID LEFT JOIN frontoffice.AR_G_COMPANY CMP ON RESV.RESV_COMPANY= CMP.COMPANY_ID LEFT JOIN frontoffice.FO_G_GUEST GST ON FOLIO.FOLIO_GST_ID = GST.GUEST_ID LEFT JOIN frontoffice.FO_G_ROOM_MASTER RM ON RM.ROOM_MASTER_ID = [frontoffice].fn_getFolioRoomId(FOLIO.FOLIO_ID) LEFT JOIN frontoffice.FO_G_PAY_MODE PAYMODE ON PAYMODE.PAY_MODE_ID = paidOut.CPO_PayMode_id LEFT JOIN frontoffice.FO_RESV_RESERVATION_CREDIT_CARD CC ON paidOut.cpo_cc_resv_id = CC.RESV_CREDIT_CARD_ID LEFT JOIN frontoffice.G_CREDIT_CARD CCMASTER ON CC.CREDIT_CARD_ID = CCMASTER.CREDIT_CARD_ID INNER JOIN frontoffice.g_currency CURR ON paidOut.curr_id = CURR.curr_id WHERE CONVERT( DATE, paidOut.CPO_Date )between @DATE and @DATETO AND ( @userid = '00000000-0000-0000-0000-000000000000' OR paidOut.cpo_user = @userid) ORDER BY CONVERT(TIME,paidOut.CPO_Date) END ELSE IF(@TYPE ='SETTLEMENT') BEGIN --BEGIN --AMOUNT VISIBLE/NOTVISIBLE TO USER declare @showRoomRate bit = 1 declare @userRole nvarchar(200) set @userRole = ISNULL(@userRole,'') declare @roleList nvarchar(max) select @roleList = config_key_desc from frontoffice.fo_g_configuration where config_key_value = 'RoomRatesVisibility' if(ISNULL(@roleList,'')<>'') BEGIN declare @roleListTable table (roles nvarchar(200)) insert into @roleListTable select * from [frontoffice].[FN_CONVERT_STRING_TO_LIST] (@roleList+',',',') delete from @roleListTable where Isnull(roles,'') ='' IF((SELECT COUNT(* )FROM @roleListTable WHERE roles = @userRole)=0) BEGIN SET @showRoomRate=0 END END --END DECLARE @ROOMNO NVARCHAR(50) ,@REG NVARCHAR(50) ,@BILLNO NVARCHAR(20) ,@GSTNAME NVARCHAR(100) ,@REMARKS NVARCHAR(max) ,@CASHAMT NUMERIC(18,2) ,@CHKAMT NUMERIC(18,2) ,@CCAMT NUMERIC(18,2) ,@CMPAMT NUMERIC(18,2) ,@MOBILEPAYMENT NUMERIC(18,2) ,@EMPNAME NVARCHAR(100) ,@TIME nvarchar(20) ,@SETT_DET_ID uniqueidentifier declare cur cursor fast_forward for SELECT (SELECT ROOMNO FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))ROOM ,(SELECT REGID FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))REG ,SETT_BILL_NUMBER BILLNO ,(SELECT GUEST FROM frontoffice.fn_bill_gstNroom(SETT_BILL_NUMBER))GUEST ,'' REMARKS ,EMP.FirstName ,CONVERT(VARCHAR, bill.CreatedDate,120) [Time] --,ISNULL( SD.sett_det_cash_amt,0) ,SD.sett_det_cash_amt ,ISNULL( SETT_DET_TOT_CHEQ_AMT,0) ,ISNULL( SETT_DET_TOT_CC_AMT,0) ,ISNULL( SETT_DET_CMP_EXCH_AMT,0) ,ISNULL( S.MobilePayment,0) ,SD.SETT_dET_ID FROM frontoffice.fo_sett_details SD INNER JOIN frontoffice.fo_settlement S ON S.SETT_ID = SD.SETT_DET_SETT_ID INNER JOIN frontoffice.fo_g_bill_details_type bill ON sd.sett_bill_number = bill.bill_details_bill_no INNER JOIN core.Users EMP ON EMP.ID = S.SETT_USER_ID WHERE convert(DAte,S.SETT_DATE ) between @DATE and @DATETO AND ( @userid = '00000000-0000-0000-0000-000000000000' OR S.SETT_USER_ID = @userid) order by S.SETT_DATE , (SUBSTRING( SD.sett_bill_number ,0 ,CASE when bill_details_type='billgeneration' THEN (select len(Code)from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 48) and S.SETT_DATE between startdate and enddate) ELSE (select len(Code) from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 53) and S.SETT_DATE between startdate and enddate)+1 end ) ) , CAST(SUBSTRING( SD.sett_bill_number ,CASE when bill_details_type='billgeneration' THEN (select len(Code)from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 48) and S.SETT_DATE between startdate and enddate)+1 ELSE (select len(Code) from core.DocumentNumberings where TransactionTypeId = (select Id from core.TransactionTypes where TempId= 53) and S.SETT_DATE between startdate and enddate)+1 end ,len( SD.sett_bill_number )) AS INTEGER) OPEN CUR FETCH NEXT FROM CUR INTO @ROOMNO ,@REG ,@BILLNO ,@GSTNAME ,@REMARKS ,@EMPNAME ,@TIME ,@CASHAMT ,@CHKAMT ,@CCAMT ,@CMPAMT ,@MOBILEPAYMENT ,@SETT_DET_ID WHILE (@@FETCH_STATUS=0) BEGIN IF(@CASHAMT<>0) BEGIN --SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME,'CASH',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'CASH',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR) END IF(@CHKAMT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'CHEQUE',@REMARKS,@EMPNAME,@CHKAMT,@TIME,@LOCAL_CURR) END DECLARE @hideRate bit = 1; --declare @showRoomRate bit =0 if( ( SELECT count(*) FROM frontoffice.fo_g_bill_details_type BILL left join frontoffice.fo_folio_master folio on bill.bill_genrted_to_folio = folio.folio_id left join frontoffice.fo_resv_reservation_master resv on BILL.bill_details_resv_id = resv.reservation_id where (@showRoomRate = 1 or isnull(resv.resv_company,'00000000-0000-0000-0000-000000000000')='00000000-0000-0000-0000-000000000000' or (folio.folio_type not in ('masterfolio','companyfolio'))) AND BILL.bill_details_bill_no = @BILLNO)=0) BEGIN SET @hideRate=1 END IF(@CMPAMT<>0 AND @hideRate=1) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'BILL TO COMPANY',@REMARKS,@EMPNAME,@CMPAMT,@TIME,@LOCAL_CURR) END IF(@CCAMT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME, cc.Name, @REMARKS,@EMPNAME, SUM(ccDet.sett_cc_exch_amt) ,@TIME,@LOCAL_CURR FROM frontoffice.fo_sett_cc_details ccDet left join frontoffice.fo_resv_reservation_credit_card ccResv on ccDet.sett_resv_cc_id = ccResv.resv_credit_card_id left join core.Banks cc on (ccdet.sett_resv_cc_id = cc.Id) where ccDet.sett_cc_sett_det_id = @SETT_DET_ID Group by cc.Name END IF(@MOBILEPAYMENT<>0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) --(ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME ,CURR ) SELECT @ROOMNO,@REG,@BILLNO,@GSTNAME, MW.Particular, @REMARKS,@EMPNAME, SUM(mbl.amount) ,@TIME,@LOCAL_CURR FROM frontoffice.fo_sett_mbl_wlet_details MBL --left JOIN frontoffice.fo_g_mbl_walet_prticulrs MBL_PART on MBL_PART.Id = MBL.mbl_wlet_prticlar_id left join frontoffice.fo_g_mbl_walet MW ON MW.Id = MBL.mbl_wlet_prticlar_id --LEFT JOIN core.PaymentModes paymode ON paymode.id = MW where mbl.sett_det_id= @SETT_DET_ID Group by MW.Particular END IF(@CASHAMT = 0 and @CHKAMT = 0 and @CMPAMT = 0 and @CCAMT = 0 AND @MOBILEPAYMENT=0) BEGIN INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) VALUES(@ROOMNO,@REG,@BILLNO,@GSTNAME,'',@REMARKS,@EMPNAME,@CASHAMT,@TIME,@LOCAL_CURR) END FETCH NEXT FROM CUR INTO @ROOMNO ,@REG ,@BILLNO ,@GSTNAME ,@REMARKS ,@EMPNAME ,@TIME ,@CASHAMT ,@CHKAMT ,@CCAMT ,@CMPAMT ,@MOBILEPAYMENT ,@SETT_DET_ID END DEALLOCATE CUR INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT ( SELECT TOP 1 rm.room_number FROM frontoffice.fo_resv_reservation_assign_guest_rooms asgRoom INNER JOIN frontoffice.fo_g_room_master rm ON rm.room_master_id = asgRoom.room_id WHERE asgRoom.resv_guest_id = resvGst.resv_guest_id AND ISNULL(asgRoom.room_transfer,'')='') Room ,reg.registration_number ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'ROOM GUEST' payMode ,'' , emp.FirstName --, 0+@CASHAMT+ @CHKAMT+ @CMPAMT+ @CCAMT+@MOBILEPAYMENT ,ROUND( CONVERT(NUMERIC(18,2), ( (100 + ISNULL(sc.taxtn_chrgAmt, 0) + ISNULL(vat.taxtn_chrgAmt, 0)) * (ISNULL(PST.post_chrg_amt, 0) * ISNULL(PST.post_curr_exhc_rate, 0)) / NULLIF(100, 0) ) ), 0) AS Amount,CONVERT(varchar(15),serv.serv_created_date,120) [Time] --,CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv --LEFT JOIN dbo.fo_g_room_master rm ON serv.rm LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_registration_master reg ON serv.reg_id = reg.registration_id LEFT JOIN frontoffice.fo_resv_guest_reservation_detail resvGst ON reg.reservation_id = resvGst.resv_id AND resvGst.guest_id = gst.guest_id --LEFT JOIN dbo.fo_resv_reservation_assign_guest_rooms asgRoom ON asgRoom.resv_guest_id = resvGst.resv_guest_id AND ISNULL(asgRoom.room_transfer,'')='' --LEFT JOIN dbo.fo_g_room_master rm ON rm.room_master_id = asgRoom.room_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN core.PaymentModes payMode ON payMode.Id = pay.fo_payGen_pay_mode LEFT JOIN frontoffice.fo_posting pst ON pst.post_ref_id = serv.guest_service_id AND pst.post_type='OTHERS' LEFT JOIN frontoffice.fo_posting_taxation sc ON PST.posting_id = sc.taxtn_Postin_id AND sc.taxtn_serv_id = @SC_ID LEFT JOIN frontoffice.fo_posting_taxation vat ON PST.posting_id = vat.taxtn_Postin_id AND vat.taxtn_serv_id = @VAT_ID LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and serv.reg_id IS NOT NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --cash INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'CASH' PayMode ,'' , emp.FirstName ,cash_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.cash_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --card INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'CREDIT CARD' PayMode ,'' , emp.FirstName ,serv.GrandTotal Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.card_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --wallet INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'Mobile Wallet' PayMode ,'' , emp.FirstName ,wallet_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.wallet_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --credit INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'BILL TO COMPANY' PayMode ,'' , emp.FirstName ,credit_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.credit_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) --CHEQUE INSERT INTO @RESULT (ROOM ,REG,BILL_NO ,GUESTNAME ,SETT_MODE ,REMARKS ,EMPLOYEE ,AMOUNT ,TIME,CURR ) SELECT '' Room ,null registration_id ,serv.service_bill_no BillNo ,ISNULL(gst.guest_name+ISNULL(' '+gst.guest_mid_name,'') +ISNULL(' '+gst.guest_lst_name,''), cmp.company_name) 'Guest/Company' ,'BILL TO COMPANY' PayMode ,'' , emp.FirstName ,cheque_amt Amount ,CONVERT(varchar(15),serv.serv_created_date,120) [Time]--CONVERT(varchar(15),CONVERT(TIME, serv.serv_created_date),100) [Time] ,@LOCAL_CURR FROM frontoffice.fo_reg_guest_service serv LEFT JOIN frontoffice.fo_g_guest gst ON gst.guest_id = serv.guest_id LEFT JOIN frontoffice.ar_g_company cmp ON cmp.company_id = serv.comp_id LEFT JOIN frontoffice.fo_Payment_General pay ON pay.fo_payGen_ref_id = serv.guest_service_id AND pay.pymnt_agnst='OtherService' LEFT JOIN frontoffice.fo_g_pay_mode payMode ON payMode.pay_mode_id = pay.fo_payGen_pay_mode LEFT JOIN core.Users emp ON emp.Id = serv.serv_created_by WHERE serv.isVoid =0 AND CONVERT(DATE,serv.serv_created_date) between @DATE and @DATETO and pay.cheque_amt >0 and serv.reg_id IS NULL AND ( @userid = '00000000-0000-0000-0000-000000000000' OR serv.serv_created_by = @userid) END SELECT * FROM @RESULT order by BILL_NOENDGO----------------CREATE TABLE [FrontOffice].[GuestTitles]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Title] VARCHAR(MAX) NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [FrontOffice].[GuestTitles] ADD CONSTRAINT [PK_HotelFrontOffice_GuestTitles_Id] PRIMARY KEY([Id])GO ALTER TABLE [FrontOffice].[GuestTitles] ADD CONSTRAINT [DF_HotelFrontOffice_GuestTitles_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [FrontOffice].[GuestTitles] ADD CONSTRAINT [DF_HotelFrontOffice_GuestTitles_RecordStatus] DEFAULT 1 FOR [RecordStatus]GOALTER TABLE frontoffice.fo_resv_guest_reservation_detail ADD BookingTransactionInfo NVARCHAR(MAX) NULLGOALTER TABLE frontoffice.fo_resv_guest_reservation_detail ADD ChannelSubBookingId NVARCHAR(MAX) NULLGO

ALTER TABLE restaurant.OrdersADD IsOrderApprovalRequired BIT NULL;----------------------------- ALTER TABLE restaurant.TablesADD IsApprovalRequired BIT NULL; ------------------------------------------ ALTER TABLE [restaurant].[express]ADD [FiscalYearId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), [FiscalYear] NVARCHAR(50) NOT NULL DEFAULT (''), [BranchId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), [DepartmentId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID());GO ALTER TABLE [restaurant].[expressDetails]ADD [FiscalYearId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), [FiscalYear] NVARCHAR(50) NOT NULL DEFAULT (''), [BranchId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), [DepartmentId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), [Date] DATE NOT NULL DEFAULT (GETDATE());GO----------------------ALTER TABLE [restaurant].[expressDetails]ADD [PaymentModeId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), [PaymentModeName] NVARCHAR(50) NOT NULL DEFAULT ('');GO----------------------------INSERT [restaurant].[GeneralSettings] ([RecordId], [Id], [Key], [Value], [Tracker], [RecordStatus], [Tag], [CompanyId], [BranchId], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [Types], [CreatedRemarks]) VALUES (N'fc7e46d2-3a91-4b68-a512-8f5e9347129d', N'8b2a5f71-9c4d-4e81-a3f0-6d7e2b19c5f8', N'KPT', N'1', NULL, 1, NULL, N'4fa7196b-52f4-4cbc-8a3e-ba576d7198d6', N'dc7bac06-45b1-40ce-a3d5-2936126db196', N'00000000-0000-0000-0000-000000000000', CAST(N'2024-01-01T00:00:00.000' AS DateTime), NULL, NULL, N'KotPrintTimes', NULL)GO----------------INSERT [restaurant].[GeneralSettings] ([RecordId], [Id], [Key], [Value], [Tracker], [RecordStatus], [Tag], [CompanyId], [BranchId], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [Types], [CreatedRemarks]) VALUES (N'3f8e2a1b-5c7d-4e9f-8a2b-1c3d5e7f9a0b', N'3f8e2a1b-5c7d-4e9f-8a2b-1c3d5e7f9a0b', N'BPC', N'1', NULL, 1, NULL, N'4fa7196b-52f4-4cbc-8a3e-ba576d7198d6', N'dc7bac06-45b1-40ce-a3d5-2936126db196', N'00000000-0000-0000-0000-000000000000', CAST(N'2024-01-01T00:00:00.000' AS DateTime), NULL, NULL, N'BotPrintCount', NULL)GO-------------------ALTER TABLE restaurant.RoleWiseDiscountsADD BillPreview BIT Default 1; ALTER TABLE restaurant.RoleWiseDiscountsADD PhonePayQr BIT NULL; ALTER TABLE [restaurant].[express]ADD [PaymentModeName] NVARCHAR(50) NOT NULL DEFAULT ('');GOCREATE TABLE [restaurant].[ReservationAdvanceDeposits]( [RecordId] [uniqueidentifier] NOT NULL, [Id] [uniqueidentifier] NOT NULL, [ReservationId] [uniqueidentifier] NOT NULL, [Amount] [decimal](20, 8) NOT NULL, [Balance] [decimal](20, 8) NOT NULL, [PaymentModeId] [uniqueidentifier] NOT NULL, [Remarks] [nvarchar](500) NULL, [PaidDate] [datetime] NOT NULL, [BankId] [uniqueidentifier] NULL, [WalletId] [uniqueidentifier] NULL, [TransactionId] [nvarchar](255) NULL, [AccountHolderName] [nvarchar](255) NULL, [AccountNumber] [nvarchar](255) NULL, [IssuedTo] [nvarchar](255) NULL, [IssuedDate] [datetime] NULL, [Tracker] [varchar](255) NULL, [RecordStatus] [tinyint] NOT NULL, [Tag] [varchar](255) NULL, [CreatedBy] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedRemarks] [nvarchar](4000) NULL, [DeletedBy] [uniqueidentifier] NULL, [DeletedDate] [datetime] NULL,CONSTRAINT [PK_restaurant_ReservationAdvanceDeposits_Id] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]GO ALTER TABLE [restaurant].[ReservationAdvanceDeposits] WITH CHECK ADD CONSTRAINT [FK_restaurant_ReservationAdvanceDeposits_Reservation] FOREIGN KEY([ReservationId]) REFERENCES [restaurant].[Reservations] ([Id])GO ALTER TABLE [restaurant].[ReservationAdvanceDeposits] ADD CONSTRAINT [DF_restaurant_ReservationAdvanceDeposits_RecordId] DEFAULT (NEWSEQUENTIALID()) FOR [RecordId]GO ALTER TABLE [restaurant].[ReservationAdvanceDeposits] ADD CONSTRAINT [DF_restaurant_ReservationAdvanceDeposits_RecordStatus] DEFAULT ((1)) FOR [RecordStatus]GOCREATE TABLE restaurant.Express( -- Primary Fields Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, RecordId UNIQUEIDENTIFIER NOT NULL, -- Customer Info CustomerId UNIQUEIDENTIFIER NULL, CustomerName NVARCHAR(200) NULL, IsSyncWithAccount bit null, -- Outlet Info OutletId UNIQUEIDENTIFIER NOT NULL, OutletName NVARCHAR(200) NULL, BillNumber NVARCHAR(200) NOT NULL, -- Transaction Info [Date] DATETIME NOT NULL, SubTotal DECIMAL(18,2) NOT NULL, DiscountAmount DECIMAL(18,2) NULL, DiscountPercentage DECIMAL(5,2) NULL, GrandTotal DECIMAL(18,2) NOT NULL, PaymentModeId UNIQUEIDENTIFIER NOT NULL, -- Tracking & Audit Tracker NVARCHAR(100) NULL, RecordStatus TINYINT NOT NULL, Tag NVARCHAR(100) NULL, CreatedBy UNIQUEIDENTIFIER NOT NULL, CreatedDate DATETIME NOT NULL, CreatedRemarks NVARCHAR(500) NULL, DeletedBy UNIQUEIDENTIFIER NULL, DeletedDate DATETIME NULL); CREATE TABLE restaurant.ExpressDetails( -- Primary Keys Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, RecordId UNIQUEIDENTIFIER NOT NULL, -- Business Fields ExpressId UNIQUEIDENTIFIER NOT NULL, MenuId UNIQUEIDENTIFIER NOT NULL, UnitId UNIQUEIDENTIFIER NOT NULL, Quantity DECIMAL(18,2) NOT NULL, Rate DECIMAL(18,2) NOT NULL, Amount DECIMAL(18,2) NOT NULL, TicketTypeId UNIQUEIDENTIFIER NOT NULL, Modifier NVARCHAR(500) NULL,OrderCode NVARCHAR(500), -- Tracking Fields Tracker NVARCHAR(100) NULL, RecordStatus TINYINT NOT NULL, Tag NVARCHAR(100) NULL, -- Audit Fields CreatedBy UNIQUEIDENTIFIER NOT NULL, CreatedDate DATETIME NOT NULL, CreatedRemarks NVARCHAR(500) NULL, DeletedBy UNIQUEIDENTIFIER NULL, DeletedDate DATETIME NULL);----------------------------update restaurant.OrderTypes set TempId = 6,IsActive = 0 where id = '121C378A-E385-49EE-BA29-32D617D2D468'---------------------ALTER TABLE restaurant.EXPRESSDETAILS ADD BillNumber NVARCHAR(MAX) NULL----------------------CREATE TABLE [restaurant].[SchemeDiscounts]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Name] VARCHAR(255) NOT NULL, [Shortname] VARCHAR(100) NULL, [Description] VARCHAR(500) NULL, [Type] VARCHAR(50) NULL, [Value] DECIMAL(16, 2) NOT NULL, [WeekDays] CHAR(7) NOT NULL, [FromDate] DATE NOT NULL, [ToDate] DATE NOT NULL, [FromTime] TIME(0) NOT NULL, [ToTime] TIME(0) NOT NULL, [IsActive] BIT NOT NULL, [IsAllItems] BIT NOT NULL, [OutletId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [DiscountOrder] INT NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL,)GOALTER TABLE [restaurant].[SchemeDiscounts] ADD CONSTRAINT [PK_restaurant_SchemeDiscounts_Id] PRIMARY KEY ([Id])GO ALTER TABLE [restaurant].[SchemeDiscounts] ADD CONSTRAINT [DF_restaurant_SchemeDiscounts_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[SchemeDiscounts] ADD CONSTRAINT [DF_restaurant_SchemeDiscounts_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO ALTER TABLE [restaurant].[SchemeDiscounts] ADD CONSTRAINT [DF_restaurant_SchemeDiscounts_IsActive] DEFAULT 1 FOR [IsActive]GO ALTER TABLE [restaurant].[SchemeDiscounts] ADD CONSTRAINT [DF_restaurant_SchemeDiscounts_IsAllItems] DEFAULT 0 FOR [IsAllItems]GO --schemediscountmenus--CREATE TABLE [restaurant].[SchemeDiscountMenus]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [MenuId] UNIQUEIDENTIFIER NOT NULL, [SchemeDiscountId] UNIQUEIDENTIFIER NOT NULL, [OutletId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GOALTER TABLE [restaurant].[SchemeDiscountMenus] ADD CONSTRAINT [PK_restaurant_SchemeDiscountMenus_Id] PRIMARY KEY ([Id])GOALTER TABLE [restaurant].[SchemeDiscountMenus] ADD CONSTRAINT [DF_restaurant_SchemeDiscountMenus_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[SchemeDiscountMenus] ADD CONSTRAINT [DF_restaurant_SchemeDiscountMenus_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO --schemediscountordertypes--CREATE TABLE [restaurant].[SchemeDiscountOrderTypes]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [SchemeDiscountId] UNIQUEIDENTIFIER NOT NULL, [OrderTypeId] UNIQUEIDENTIFIER NOT NULL, [OrderTypetempId] INT NOT NULL, [HasParty] BIT NOT NULL, [OutletId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GOALTER TABLE [restaurant].[SchemeDiscountOrderTypes]ADD CONSTRAINT [PK_restaurant_SchemeDiscountOrderTypes_Id]PRIMARY KEY ([Id])GOALTER TABLE [restaurant].[SchemeDiscountOrderTypes]ADD CONSTRAINT [DF_restaurant_SchemeDiscountOrderTypes_RecordId]DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[SchemeDiscountOrderTypes]ADD CONSTRAINT [DF_restaurant_SchemeDiscountOrderTypes_RecordStatus]DEFAULT 1 FOR [RecordStatus]GO ALTER TABLE [restaurant].[SchemeDiscountOrderTypes]ADD CONSTRAINT [DF_restaurant_SchemeDiscountOrderTypes_HasParty]DEFAULT 0 FOR [HasParty]GO --schemediscountordertypeparties--CREATE TABLE [restaurant].[SchemeDiscountOrderTypeParties]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [SchemeDiscountId] UNIQUEIDENTIFIER NOT NULL, [SchemeDiscountOrderTypeId] UNIQUEIDENTIFIER NOT NULL, [PartyId] UNIQUEIDENTIFIER NOT NULL, [OutletId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL,)GOALTER TABLE [restaurant].[SchemeDiscountOrderTypeParties]ADD CONSTRAINT [PK_restaurant_SchemeDiscountOrderTypeParties_Id]PRIMARY KEY ([Id])GOALTER TABLE [restaurant].[SchemeDiscountOrderTypeParties]ADD CONSTRAINT [DF_restaurant_SchemeDiscountOrderTypeParties_RecordId]DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[SchemeDiscountOrderTypeParties]ADD CONSTRAINT [DF_restaurant_SchemeDiscountOrderTypeParties_RecordStatus]DEFAULT 1 FOR [RecordStatus]GO --add column scheme discount--alter table restaurant.orderticketitems add SchemeDiscountValue DECIMAL (16,8) NULLalter table restaurant.BillOrderItems add SchemeDiscountAmount DECIMAL (16,8) NULLalter table restaurant.BillOrderItems add SchemeDiscountPercent DECIMAL (16,8) NULL----------------alter table restaurant.expressdetails add UnitName nvarchar(max) null;alter table restaurant.expressdetails add MenuName nvarchar(max) null;alter table restaurant.express add IsVoided bit null;----------------CREATE TABLE [restaurant].[MenuAdditionalCharges]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [DateTime] DATETIME NOT NULL, [RecordedChargesId] UNIQUEIDENTIFIER NOT NULL, [MenuId] UNIQUEIDENTIFIER NOT NULL, [UnitId] UNIQUEIDENTIFIER NOT NULL, [Charge] DECIMAL(20, 2) NOT NULL, [OutletId] UNIQUEIDENTIFIER NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO----------------------------ALTER TABLE [restaurant].[MenuAdditionalCharges] ADD CONSTRAINT [PK_restaurant_MenuAdditionalCharges_Id] PRIMARY KEY([Id])GO ALTER TABLE [restaurant].[MenuAdditionalCharges] ADD CONSTRAINT [DF_restaurant_MenuAdditionalCharges_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[MenuAdditionalCharges] ADD CONSTRAINT [DF_restaurant_MenuAdditionalCharges_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO----------------------alter table restaurant.Settlements add [IsPrinted] [bit] NULL-------------------alter table restaurant.Bookings add VisitorTypeId uniqueIdentifier null ALTER TABLE restaurant.BookingsADD ImagesJson NVARCHAR(MAX) NULL; ALTER TABLE restaurant.bookingsADD Collection DECIMAL(18,2) NULL, CurrencyId UNIQUEIDENTIFIER NULL;--------------------CREATE TABLE [restaurant].[VisitorTypes ]( [RecordId] [uniqueidentifier] NOT NULL, [Id] [uniqueidentifier] NOT NULL, [Name] [varchar](max) NOT NULL, [Tracker] [varchar](255) NULL, [RecordStatus] [tinyint] NOT NULL, [Tag] [varchar](255) NULL, [CompanyId] [uniqueidentifier] NOT NULL, [BranchId] [uniqueidentifier] NOT NULL, [CreatedBy] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [DeletedBy] [uniqueidentifier] NULL, [DeletedDate] [datetime] NULL, [CreatedRemarks] [nvarchar](4000) NULL )GoALTER TABLE [restaurant].[VisitorTypes] ADD CONSTRAINT [PK_restaurant_VisitorTypes_Id] PRIMARY KEY([Id])GO ALTER TABLE [restaurant].[VisitorTypes] ADD CONSTRAINT [DF_restaurant_VisitorTypes_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[VisitorTypes] ADD CONSTRAINT [DF_restaurant_VisitorTypes_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO----------------DECLARE @CompanyId UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000', @BranchId UNIQUEIDENTIFIER = 'DC7BAC06-45B1-40CE-A3D5-2936126DB196', @CreatedBy UNIQUEIDENTIFIER = '4FA7196B-52F4-4CBC-8A3E-BA576D7198D6'; INSERT INTO [restaurant].[VisitorTypes]( Id, Name, Tracker, RecordStatus, Tag, CompanyId, BranchId, CreatedBy, CreatedDate, CreatedRemarks)VALUES-- a. VIP(NEWID(), 'VIP', NULL, 1, 'VIP', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default visitor type'), -- b. Normal(NEWID(), 'Normal', NULL, 1, 'NORMAL', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default visitor type'), -- c. Visitors Pass(NEWID(), 'Visitors Pass', NULL, 1, 'VISITOR_PASS', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default visitor type'), -- d. Poker(NEWID(), 'Poker', NULL, 1, 'POKER', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default visitor type'), -- e. Flush(NEWID(), 'Flush', NULL, 1, 'FLUSH', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default visitor type'); ----------------------ALTER TABLE restaurant.OrderTicketItems ADD ParentOrderItemId UNIQUEIDENTIFIER NULL, IsAddOn BIT NULL; alter table restaurant.OrderTicketItems add HasAddOn bit null ----------------alter table restaurant.RoleWiseDiscountsAdd MergeTableReport bit null alter table restaurant.RoleWiseDiscountsAdd CancelitemvsbillitemReport bit null---------------------------alter table restaurant.Orders Add CancelledDate DateTime null;alter table restaurant.OrderTickets Add CancelledDate DateTime null;alter table restaurant.OrderTicketItems Add CancelledDate DateTime null;-------------update core.TransactionTypes set ModuleId = 'B778CA7B-D103-4A28-BFF8-F27AACA36D52' where Name='RestaurantAbbreviated'-------------alter table restaurant.RoleWiseDiscountsAdd ItemvsMenuReport bit null alter table restaurant.RoleWiseDiscountsAdd BookingRegisterReport bit nullALTER TABLE restaurant.BillsADD RoundOffAmount DECIMAL(16,2) NOT NULL DEFAULT 0;CREATE TRIGGER trg_Settlements_RemoveDuplicatesON restaurant.SettlementsAFTER INSERT, UPDATEASBEGIN SET NOCOUNT ON; ;WITH Duplicates AS ( SELECT s.Id, ROW_NUMBER() OVER ( PARTITION BY s.BillId, s.PaymentModeId ORDER BY s.CreatedDate DESC, s.Id DESC ) AS rn FROM restaurant.Settlements s INNER JOIN inserted i ON s.BillId = i.BillId AND s.PaymentModeId = i.PaymentModeId WHERE s.RecordStatus = 1 ) UPDATE s SET RecordStatus = 0 FROM restaurant.Settlements s INNER JOIN Duplicates d ON s.Id = d.Id WHERE d.rn > 1;END;GOALTER TABLE restaurant.MenusADD ComboStyle INT NULL; CREATE TABLE [restaurant].[MenuComboSets]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Name] VARCHAR(MAX) NOT NULL, [MenuId] UNIQUEIDENTIFIER NOT NULL, [IsActive] bit NOT NULL, [Choice] int NOT NUll, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [restaurant].[MenuComboSets] ADD CONSTRAINT [PK_restaurant_MenuComboSets_Id] PRIMARY KEY([Id])GO ALTER TABLE [restaurant].[MenuComboSets] ADD CONSTRAINT [DF_restaurant_MenuComboSets_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[MenuComboSets] ADD CONSTRAINT [DF_restaurant_MenuComboSets_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO CREATE TABLE [restaurant].[MenuComboItems]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [ComboMenuId] UNIQUEIDENTIFIER NOT NULL, [ComboSetId] UNIQUEIDENTIFIER NULL, [MenuId] UNIQUEIDENTIFIER NOT NULL, [UnitId] UNIQUEIDENTIFIER NOT NULL, [Quantity] DECIMAL (16,8) NULL, [IsActive] bit NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [restaurant].[MenuComboItems] ADD CONSTRAINT [PK_restaurant_MenuComboItems_Id] PRIMARY KEY([Id])GO ALTER TABLE [restaurant].[MenuComboItems] ADD CONSTRAINT [DF_restaurant_MenuComboItems_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[MenuComboItems] ADD CONSTRAINT [DF_restaurant_MenuComboItems_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO CREATE TABLE [restaurant].[OrderComboItems]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [OrderTicketItemId] UNIQUEIDENTIFIER NOT NULL, [OrderTicketId] UNIQUEIDENTIFIER NOT NULL, [OrderId] UNIQUEIDENTIFIER NOT NULL, [TableId] UNIQUEIDENTIFIER NOT NULL, [ReservationId] UNIQUEIDENTIFIER NOT NULL, [ComboMenuId] UNIQUEIDENTIFIER NOT NULL, [ComboSetId] UNIQUEIDENTIFIER NULL, [MenuComboItemId] UNIQUEIDENTIFIER NOT NULL, [MenuId] UNIQUEIDENTIFIER NOT NULL, [TicketId] UNIQUEIDENTIFIER NOT NULL, [UnitId] UNIQUEIDENTIFIER NOT NULL, [Quantity] DECIMAL (16,8) NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [restaurant].[OrderComboItems] ADD CONSTRAINT [PK_restaurant_OrderComboItems_Id] PRIMARY KEY([Id])GO ALTER TABLE [restaurant].[OrderComboItems] ADD CONSTRAINT [DF_restaurant_OrderComboItems_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [restaurant].[OrderComboItems] ADD CONSTRAINT [DF_restaurant_OrderComboItems_RecordStatus] DEFAULT 1 FOR [RecordStatus]GOALTER TABLE restaurant.OrderTicketItemsADD Processing DECIMAL(18, 2) NULL;ALTER TABLE restaurant.UserAccessesADD BlockId UNIQUEIDENTIFIER NULL; ALTER TABLE restaurant.Menus ADD MenuDescription NVARCHAR(MAX) NULL GOALTER TABLE restaurant.RoleWiseDiscountsADD BookingReport BIT NULL; ALTER TABLE restaurant.RoleWiseDiscountsADD StaffWiseCommissionReport BIT NULL; ALTER TABLE restaurant.RoleWiseDiscountsADD ItemWiseCustomerReport BIT NULL; ALTER TABLE restaurant.RoleWiseDiscountsADD DashboardReport BIT NULL;

ALTER TABLE pos.SalesInvoicesADD PlateNumber NVARCHAR(50) NULL, LaneNumber NVARCHAR(50) NULL, InTime DATETIME NULL, OutTime DATETIME NULL, VehicleType NVARCHAR(50) NULL, OrderNo NVARCHAR(50) NULL; -------------------- alter table pos.settings add ShowParkingInfos bit nullalter table pos.Quotations add UpdatedBy uniqueidentifier null,UpdatedDate datetime null -------------------------------------------------alter table pos.Chalans add CityName nvarchar(max) null-------------------------------alter table pos.SalesInvoices add CityName nvarchar(max) null -------------------------------------- Alter table pos.quotations alter column customername nvarchar(max) ----------------------------- Alter Table pos.chalansAlter column customername nvarchar(max) -----------------------------Alter Table pos.salesinvoices Alter column customername nvarchar(max) ---------------------------------alter Table pos.invoicematerializedviews Alter column customername nvarchar(max)ALTER TABLE pos.Chalans ADD ProjectNameId UNIQUEIDENTIFIER NULL, ProjectName NVARCHAR(MAX), PurchaseOrderNumber NVARCHAR(MAX)GOALTER TABLE pos.Settings ADD TaxInvoicePrintTemplateId UNIQUEIDENTIFIER NULL, SalesInvoicePrintTemplateId UNIQUEIDENTIFIER NULLGOALTER TABLE pos.Settings ADD SalesReturnPrintTemplateId UNIQUEIDENTIFIER NULLGOalter table pos.salesinvoices add PostalCode varchar(max) nullalter table pos.salesinvoices add LocationwisePriceId uniqueidentifier null; alter table pos.salesinvoices add LocationwisePrice decimal(20,5) null; alter table pos.SalesInvoices add IsReturnFromOutlet bit null;INSERT INTO pos.ExportTemplates (RecordId, Id, Name, FileName, ReportId, IsDefault, IsActive, ReportHeaderContent, ReportParameterContent, TableHeaderContent, TableDataContent, Tracker, RecordStatus, Tag, CompanyId, BranchId, CreatedBy, CreatedDate, DeletedBy, DeletedDate, CreatedRemarks, IsItemWiseData) VALUES(NEWID(), NEWID(), N'Default', N'Report', 31, CONVERT(bit, 'True'), CONVERT(bit, 'True'), N'[{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","ElementId":1,"Order":1,"Id":"a944af28-1337-44fd-9b75-cbd9278e0dce","IsActive":"true","Attribute":{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","HeaderId":"a944af28-1337-44fd-9b75-cbd9278e0dce","Alignment":0,"FontSize":12,"Color":"#000000","BackgroundColor":"#FFFFFF","Id":"ff2f057d-00c5-4efe-9e48-fab5a69b1c89"}},{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","ElementId":2,"Order":2,"Id":"5be350ca-98fb-4587-aa7f-3498579eb48c","IsActive":"true","Attribute":{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","HeaderId":"5be350ca-98fb-4587-aa7f-3498579eb48c","Alignment":0,"FontSize":10,"Color":"#000000","BackgroundColor":"#FFFFFF","Id":"a944af28-1337-44fd-9b75-cbd9278e0dce"}},{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","ElementId":3,"Order":3,"Id":"5be350ca-98fb-4587-aa7f-3498579eb48c","IsActive":"true","Attribute":{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","HeaderId":"5be350ca-98fb-4587-aa7f-3498579eb48c","Alignment":0,"FontSize":10,"Color":"#000000","BackgroundColor":"#FFFFFF","Id":"a944af28-1337-44fd-9b75-cbd9278e0dce"}},{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","ElementId":4,"Order":4,"Id":"5be350ca-98fb-4587-aa7f-3498579eb48c","IsActive":"true","Attribute":{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","HeaderId":"5be350ca-98fb-4587-aa7f-3498579eb48c","Alignment":0,"FontSize":10,"Color":"#000000","BackgroundColor":"#FFFFFF","Id":"a944af28-1337-44fd-9b75-cbd9278e0dce"}}]', N'[{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","ParameterId":1,"DisplayName":"Date: ","Order":1,"Id":"a944af28-1337-44fd-9b75-cbd9278e0dce","Attribute":{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","HeaderId":"a944af28-1337-44fd-9b75-cbd9278e0dce","Alignment":6,"FontSize":10,"Color":"#000000","BackgroundColor":"#FFFFFF","Id":"ff2f057d-00c5-4efe-9e48-fab5a69b1c89"},"IsActive":true}]', N'[ { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "ItemName", "DisplayName": "product", "Order": 1, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "a944af28-1337-44fd-9b75-cbd9278e0dce", "Alignment": 0, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "ff2f057d-00c5-4efe-9e48-fab5a69b1c89" }, "Id": "a944af28-1337-44fd-9b75-cbd9278e0dce", "IsActive": true }, { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "Code", "DisplayName": "Code", "Order": 2, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "a944af28-1337-44fd-9b76-cbd9278e0dce", "Alignment": 0, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "ff2f057d-00c5-4efe-9e48-fab5a69b1c79" }, "Id": "a944af28-1337-44fd-9b76-cbd9278e0dce", "IsActive": true }, { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "CustomerName", "DisplayName": "Customer", "Order": 3, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "93da2b14-0925-49f1-8e76-c27c4bee8d28", "Alignment": 6, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "43b20b1c-c053-43cd-b661-d1b10212758d" }, "Id": "93da2b14-0925-49f1-8e76-c27c4bee8d28", "IsActive": true }, { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "Quantity", "DisplayName": "Quantity", "Order": 4, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "93da2b14-0925-49f1-8e76-c27c4bee8d28", "Alignment": 6, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "43b20b1c-c053-43cd-b661-d1b10212758d" }, "Id": "93da2b14-0925-49f1-8e76-c27c4bee8d28", "IsActive": true }, { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "GrossAmount", "DisplayName": "GrossAmount", "Order": 5, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "Alignment": 7, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "0888b5b3-37f9-4c56-88b5-c9fd745daf69" }, "Id": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "IsActive": true }, { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "TotalDiscount", "DisplayName": "TotalDiscount", "Order": 6, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "Alignment": 7, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "0888b5b3-37f9-4c56-88b5-c9fd745daf69" }, "Id": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "IsActive": true }, { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "TotalTax", "DisplayName": "TotalTax", "Order": 7, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "Alignment": 7, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "0888b5b3-37f9-4c56-88b5-c9fd745daf69" }, "Id": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "IsActive": true } , { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "ColumnName": "NetAmount", "DisplayName": "NetAmount", "Order": 8, "Attribute": { "ReportExportId": "6c024d90-ad46-4954-a44c-27109f71b9f3", "HeaderId": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "Alignment": 7, "FontSize": 10, "Color": "#000000", "BackgroundColor": "#FFFFFF", "Id": "0888b5b3-37f9-4c56-88b5-c9fd745daf69" }, "Id": "7411cf77-1c58-4037-9ace-6a47c46ccc0c", "IsActive": true }]', N'[{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","Attribute":{"ReportExportId":"6c024d90-ad46-4954-a44c-27109f71b9f3","HeaderId":"a944af28-1337-44fd-9b75-cbd9278e0dce","Alignment":0,"FontSize":10,"Color":"#000000","BackgroundColor":"#FFFFFF","Id":"ff2f057d-00c5-4efe-9e48-fab5a69b1c89"},"Id":"a944af28-1337-44fd-9b75-cbd9278e0dce","IsActive":true}]', NULL, 1, NULL, '4fa7196b-52f4-4cbc-8a3e-ba576d7198d6', 'dc7bac06-45b1-40ce-a3d5-2936126db196', '00000000-0000-0000-0000-000000000000', CONVERT(DATETIME, '2024-06-26 17:01:23.087', 121), NULL, NULL, NULL, CONVERT(bit, 'True'))----------------------------------CREATE TABLE [Pos].[TransactionImages]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [TransactionId] UNIQUEIDENTIFIER NOT NULL, [TransactionDetailId] UNIQUEIDENTIFIER NULL, [TransactionDetailSkuId] UNIQUEIDENTIFIER NULL, [FilePath] VARCHAR(MAX) NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [Pos].[TransactionImages] ADD CONSTRAINT [PK_Pos_TransactionImages_Id] PRIMARY KEY([Id])GO ALTER TABLE [Pos].[TransactionImages] ADD CONSTRAINT [DF_Pos_TransactionImages_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [Pos].[TransactionImages] ADD CONSTRAINT [DF_Pos_TransactionImages_RecordStatus] DEFAULT 1 FOR [RecordStatus]GOalter table pos.SalesInvoices add TransactionReferenceId uniqueidentifier null;UPDATE pos.ChalanDetailsSET ProductId = ISNULL(ItemId, '00000000-0000-0000-0000-000000000000'); UPDATE pos.ChalanDetailsSET ProductId = ItemId; UPDATE pos.ChalanDetailSKUsSET ProductId = ISNULL(ItemId, '00000000-0000-0000-0000-000000000000'); UPDATE pos.ChalanDetailSKUsSET ProductId = ItemId UPDATE pos.QuotationDetailsSET ProductId = ISNULL(ItemId, '00000000-0000-0000-0000-000000000000'); UPDATE pos.QuotationDetailsSET ProductId = ItemId; UPDATE pos.ChalanDetailSKUsSET ProductId = ISNULL(ItemId, '00000000-0000-0000-0000-000000000000'); UPDATE pos.ChalanDetailSKUsSET ProductId = ItemId; update pos.SalesInvoiceItemsSET ProductId = ISNULL(RefNo, '00000000-0000-0000-0000-000000000000'); update pos.SalesInvoiceItemsSET ProductId = RefNo;-------------------------

alter table inventory.purchases add SalesOrderNumber nvarchar(max) null;--------------------------------ALTER TABLE inventory.Purchases ADD ProjectNameId UNIQUEIDENTIFIER NULL -- COLLATE Latin1_General_CI_AS -- CONSTRAINT DF_Purchases_ProjectNameId DEFAULT NULLGO-----------------------------------CREATE TABLE [inventory].[IssuePurchaseConnections]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [PurchaseId] UNIQUEIDENTIFIER NULL, [IssueId] UNIQUEIDENTIFIER NULL, [SupplierId] UNIQUEIDENTIFIER NULL, [Rate] DECIMAL(20, 2) NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [inventory].[IssuePurchaseConnections] ADD CONSTRAINT [PK_inventory_IssuePurchaseConnections_Id] PRIMARY KEY([Id])GO ALTER TABLE [inventory].[IssuePurchaseConnections] ADD CONSTRAINT [DF_inventory_IssuePurchaseConnections_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [inventory].[IssuePurchaseConnections] ADD CONSTRAINT [DF_inventory_IssuePurchaseConnections_RecordStatus] DEFAULT 1 FOR [RecordStatus]GOCREATE TABLE [inventory].[PurchaseRequisites]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [RequisiteCode] VARCHAR(MAX) NULL, [RequisiteDate] DATETIME NULL, [RequisiteDateNP] VARCHAR(MAX) NULL, [Remarks] VARCHAR(MAX) NULL, [IsVoid] BIT NULL, [DepartmentId] UNIQUEIDENTIFIER NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [inventory].[PurchaseRequisites] ADD CONSTRAINT [PK_inventory_PurchaseRequisites_Id] PRIMARY KEY([Id])GO ALTER TABLE [inventory].[PurchaseRequisites] ADD CONSTRAINT [DF_inventory_PurchaseRequisites_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [inventory].[PurchaseRequisites] ADD CONSTRAINT [DF_inventory_PurchaseRequisites_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO ---------------CREATE TABLE [inventory].[AmountRangewiseDiscounts]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Name] VARCHAR(MAX) NOT NULL, [RangeAmount] DECIMAL(20, 2) NOT NULL, [DiscountAmount] DECIMAL(20, 2) NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [inventory].[AmountRangewiseDiscounts] ADD CONSTRAINT [PK_inventory_AmountRangewiseDiscounts_Id] PRIMARY KEY([Id])GO ALTER TABLE [inventory].[AmountRangewiseDiscounts] ADD CONSTRAINT [DF_inventory_AmountRangewiseDiscounts_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [inventory].[AmountRangewiseDiscounts] ADD CONSTRAINT [DF_inventory_AmountRangewiseDiscounts_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO-----------------------------------CREATE TABLE [inventory].[LocationwisePrices]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Name] VARCHAR(MAX) NOT NULL, [Code] VARCHAR(MAX) NULL, [Price] DECIMAL(20, 2) NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [inventory].[LocationwisePrices] ADD CONSTRAINT [PK_inventory_LocationwisePrices_Id] PRIMARY KEY([Id])GO ALTER TABLE [inventory].[LocationwisePrices] ADD CONSTRAINT [DF_inventory_LocationwisePrices_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [inventory].[LocationwisePrices] ADD CONSTRAINT [DF_inventory_LocationwisePrices_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO-----------------------------------CREATE TABLE [inventory].[ItemCatagories]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [ItemId] UNIQUEIDENTIFIER NULL, [CatagoryId] UNIQUEIDENTIFIER NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [inventory].[ItemCatagories] ADD CONSTRAINT [PK_inventory_ItemCatagories_Id] PRIMARY KEY([Id])GO ALTER TABLE [inventory].[ItemCatagories] ADD CONSTRAINT [DF_inventory_ItemCatagories_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [inventory].[ItemCatagories] ADD CONSTRAINT [DF_inventory_ItemCatagories_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO-----------------------------------------CREATE TABLE [inventory].[PurchaseRequisites]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [RequisiteCode] VARCHAR(MAX) NULL, [RequisiteDate] DATETIME NULL, [RequisiteDateNP] VARCHAR(MAX) NULL, [Remarks] VARCHAR(MAX) NULL, [IsVoid] BIT NULL, [DepartmentId] UNIQUEIDENTIFIER NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [inventory].[PurchaseRequisites] ADD CONSTRAINT [PK_inventory_PurchaseRequisites_Id] PRIMARY KEY([Id])GO ALTER TABLE [inventory].[PurchaseRequisites] ADD CONSTRAINT [DF_inventory_PurchaseRequisites_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [inventory].[PurchaseRequisites] ADD CONSTRAINT [DF_inventory_PurchaseRequisites_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO CREATE TABLE [inventory].[PurchaseRequisiteDetails]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [RequisiteId] UNIQUEIDENTIFIER NULL, [ItemId] UNIQUEIDENTIFIER NULL, [ColorId] UNIQUEIDENTIFIER NULL, [SizeId] UNIQUEIDENTIFIER NULL, [IsUsedInOrder] BIT NULL, [UnitId] UNIQUEIDENTIFIER NULL, [Quantity] DECIMAL(32,5) NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [inventory].[PurchaseRequisiteDetails] ADD CONSTRAINT [PK_inventory_PurchaseRequisiteDetails_Id] PRIMARY KEY([Id])GO ALTER TABLE [inventory].[PurchaseRequisiteDetails] ADD CONSTRAINT [DF_inventory_PurchaseRequisiteDetails_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [inventory].[PurchaseRequisiteDetails] ADD CONSTRAINT [DF_inventory_PurchaseRequisiteDetails_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO --------------------ALTER TABLE inventory.PurchaseRequisites ADD RequisiteReferenceId UNIQUEIDENTIFIER NULLGO ALTER TABLE inventory.PurchaseRequisiteDetails ADD RequisiteDetailReferenceId UNIQUEIDENTIFIER NULLGO------------------ALTER TABLE inventory.PurchaseRequisiteDetails ADD Remarks NVARCHAR(MAX) NULLGO-----------------ALTER TABLE inventory.PurchaseOrders ADD PurchaseRequisitesId UNIQUEIDENTIFIER NULLGOALTER TABLE inventory.PurchaseOrderDetails ADD PurchaseRequisiteDetailsId UNIQUEIDENTIFIER NULLGO-------------------ALTER TABLE inventory.PurchaseDetailsADD RequisiteDetailId UNIQUEIDENTIFIER NULL---------------------ALTER TABLE inventory.PurchaseDetails ADD IssuedToBranchId UNIQUEIDENTIFIER NULL GO-----------------ALTER TABLE inventory.PurchaseDetails ADD IssuedToDepartmentId UNIQUEIDENTIFIER NULL GO----------------

CREATE TABLE Parking.ParkingLog( [RecordId] [uniqueidentifier] NOT NULL, [Id] [uniqueidentifier] NOT NULL, [VehicleType] [varchar](max) NOT NULL, [VehicleNumber] [varchar](max) NOT NULL, [InTime] [datetime] NOT NULL, [OutTime] [datetime] NULL, [Charge] [decimal](20, 5) NULL, [Status] [varchar](max) NOT NULL, [Tracker] [varchar](255) NULL, [RecordStatus] [tinyint] NOT NULL, [Tag] [varchar](255) NULL, [CreatedBy] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [DeletedBy] [uniqueidentifier] NULL, [DeletedDate] [datetime] NULL, [CreatedRemarks] [varchar](255) NULL, [ReferenceNumber] [nvarchar](max) NULL, [BranchId] [uniqueidentifier] NULL, [DepartmentId] [uniqueidentifier] NULL, [Contact] [nvarchar](max) NULL, [CheckOutType] [varchar](20) NULL, [Establishment] [varchar](20) NULL, [EstablishmentId] [uniqueidentifier] NULL, [BillNumber] [nvarchar](max) NULL,CONSTRAINT [PK_core_Parkings_Id] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO ALTER TABLE Parking.ParkingLog ADD CONSTRAINT [DF_Parking_ParkingLog_RecordId] DEFAULT (newsequentialid()) FOR [RecordId]GO--------------------------CREATE TABLE [Parking].[VehicleTypes]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Name] VARCHAR(MAX) NULL, [TempId] INT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [Parking].[VehicleTypes] ADD CONSTRAINT [PK_Parking_VehicleTypes_Id] PRIMARY KEY([Id])GO ALTER TABLE [Parking].[VehicleTypes] ADD CONSTRAINT [DF_Parking_VehicleTypes_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [Parking].[VehicleTypes] ADD CONSTRAINT [DF_Parking_VehicleTypes_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO UPDATE Parking.ParkingLogSET BillNumber = REPLACE(BillNumber, 'Park-', 'SIV-')WHERE BillNumber LIKE 'Park-%'DECLARE @CompanyId UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000';DECLARE @BranchId UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000';DECLARE @CreatedBy UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000'; INSERT INTO [Parking].[VehicleTypes]( Id, Name, TempId, Tracker, RecordStatus, Tag, CompanyId, BranchId, CreatedBy, CreatedDate, CreatedRemarks)VALUES-- Bike( NEWID(), 'Bike/Scooter', 2, NULL, DEFAULT, 'BIKE', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default vehicle type'),-- Car( NEWID(), 'Car/Jeep/Limo/Tempo', 4, NULL, DEFAULT, 'CAR', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default vehicle type'),-- Minibus( NEWID(), 'MIcro Bus/Minibus', 6, NULL, DEFAULT, 'MINIBUS/Micro Bus', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default vehicle type'),-- Truck( NEWID(), 'Bus/Truck', 8, NULL, DEFAULT, 'Bus/TRUCK', @CompanyId, @BranchId, @CreatedBy, GETDATE(), 'Default vehicle type');

INSERT [account].[PostingVoucherTypes] ([RecordId], [Id], [Source], [PostingType], [VoucherTypeId],[RecordStatus], [Tag], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [CreatedRemarks],TransactionTypeTempId) VALUES (N'7512DEC3-9923-4BB0-B9D4-3B579BA730BD', N'43FA5715-EE2C-4E3A-8457-CD3E1813B5CE', N'Pos',N'Advance Payment',N'E7EFF473-BF83-4DF8-BE79-22D67E489070', 1, NULL, N'00000000-0000-0000-0000-000000000000', CAST(N'2021-01-21T15:38:05.423' AS DateTime), NULL, NULL, NULL,69)GO ---------25jan------------insert into account.accountgroups VALUES (N'14d91b43-6f24-48a3-ac07-79489ff12640', N'bc33972e-d091-4c44-9cbe-02f10915585e', 34, N'ad0698de-f9ec-4b10-8e6a-072707f1c332', N'Salary Payable', N'Salary Payable', 214000000, 100000, N'LIABILITIES -> CURRENT LIABILITIES', 1, NULL, NULL, 1, NULL, N'00000000-0000-0000-0000-000000000000', CAST(N'2026-01-27T11:23:39.703' AS DateTime), NULL, NULL, 7, N'L000001000004', 1, NULL)----------------INSERT INTO account.Ledgers (RecordId, Id, AccountGroupId, Code, Name, ShortName, CurrencyId, LedgerTypeTempId, Remarks, Status, Tracker, RecordStatus, Tag, CreatedBy, CreatedDate, DeletedBy, DeletedDate, IsAddedByUser, CreatedRemarks, InventorySystemTypeTempId, MappingTypeTempId, Email, Phone, VatPanNumber, HasItemsOrSubLedgers, BankId, BranchName, BankAccountNo, BankAccountName, IsuseInCOGS, ExtraInfo, LedgerMappingNameId, IsAddedFromMobile) VALUES('79725116-8e81-46ce-9add-b653cb981c32', '582c206e-ea1b-4820-805b-ad99677cd623', '5ebc5b6f-090a-4c11-a54d-26793f3841f1', 'AD00628', 'ADDITIONAL SALES', 'ADDITIONAL SALES', 'cc06ecf8-7785-483d-a1e6-60653fcfad09', 3, '', CONVERT(bit, 'True'), NULL, 1, NULL, '00000000-0000-0000-0000-000000000000', CONVERT(DATETIME, '2025-12-15 16:24:33.743', 121), NULL, NULL, CONVERT(bit, 'True'), NULL, NULL, NULL, NULL, NULL, NULL, CONVERT(bit, 'False'), NULL, NULL, NULL, NULL, CONVERT(bit, 'False'), N'{"Email":null,"Phone":null,"VatPanNumber":null}', NULL, NULL)GO---------------------------------------- insert into core.LedgerMappings values('adc9491b-92a1-42e9-848c-14665c4ffe96', '2b478a87-6112-487f-a5e5-c51926e92faa', 'e7230508-5237-4d38-977e-9da67e611622','AddtionalCharge', '582c206e-ea1b-4820-805b-ad99677cd623', 'ADDITIONAL SALES', NULL ,NULL, 4, 'All', 'Additional Charge',NULL, 1 ,NULL ,'00000000-0000-0000-0000-000000000000', GETDATE(), NULL, NULL, NULL, NULL, 0, NULL)INSERT [account].[PostingVoucherTypes] ([RecordId], [Id], [Source], [PostingType], [VoucherTypeId], [RecordStatus], [Tag], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [CreatedRemarks],[TransactionTypeTempId]) VALUES (N'a5082c0b-3fa0-4921-a005-ff7fe6beb970', N'f03b6acb-c09c-401e-83d0-6ec7c887c126', N'RestaurantCollection', N'RestaurantCollection',N'47143D61-BCFE-4B3A-A644-A0A3EA7B8324', 1, NULL, N'00000000-0000-0000-0000-000000000000', CAST(N'2026-01-27T15:38:05.423' AS DateTime), NULL, NULL, NULL,61)GO------------INSERT [account].[PostingVoucherTypes] ([RecordId], [Id], [Source], [PostingType], [VoucherTypeId],[RecordStatus], [Tag], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [CreatedRemarks],TransactionTypeTempId) VALUES (N'7512DEC3-9923-4BB0-B9D4-3B579BA730BD', N'43FA5715-EE2C-4E3A-8457-CD3E1813B5CE', N'Pos',N'Advance Payment',N'E7EFF473-BF83-4DF8-BE79-22D67E489070', 1, NULL, N'00000000-0000-0000-0000-000000000000', CAST(N'2021-01-21T15:38:05.423' AS DateTime), NULL, NULL, NULL,69)GO-------------------------INSERT [account].[Ledgers] ([RecordId], [Id], [AccountGroupId], [Code], [Name], [ShortName], [CurrencyId], [LedgerTypeTempId], [Remarks], [Status], [Tracker], [RecordStatus], [Tag], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [IsAddedByUser], [CreatedRemarks], [InventorySystemTypeTempId], [MappingTypeTempId], [Email], [Phone], [VatPanNumber], [HasItemsOrSubLedgers], [BankId], [BranchName], [BankAccountNo], [BankAccountName], [IsuseInCOGS], [ExtraInfo], [LedgerMappingNameId], [IsAddedFromMobile]) VALUES (N'd7e6a169-4e85-4a52-b950-4cea2b9d77f7', N'b95ce984-b26b-429c-a5f6-78d7eec54ef9', N'485015fa-c1f0-4a98-be1b-3d067ed76b94', N'AD05908', N'ADVANCE PAYMENT', N'ADVANCE PAYMENT', N'cc06ecf8-7785-483d-a1e6-60653fcfad09', 3, N'Advance Payment', 1, NULL, 1, NULL, N'00000000-0000-0000-0000-000000000000', CAST(N'2025-12-22T12:59:47.633' AS DateTime), NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, N'{"Email":null,"Phone":null,"VatPanNumber":null}', NULL, NULL)GO----------------------------CREATE TRIGGER TR_AccountVouchers_SetNarrationON account.VouchersAFTER INSERT, UPDATEASBEGIN SET NOCOUNT ON; UPDATE v SET v.Narration = v.BillNumber FROM account.Vouchers v INNER JOIN inserted i ON v.Id = i.Id WHERE i.BillNumber IS NOT NULL AND (i.Narration IS NULL OR i.Narration = '');END;GO--------------------------------------------CREATE TABLE [account].[UserModuleAccesses]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [ModuleId] UNIQUEIDENTIFIER not NULL, [UserId] UNIQUEIDENTIFIER not NULL, [PageId] UNIQUEIDENTIFIER not NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [account].[UserModuleAccesses] ADD CONSTRAINT [PK_account_UserModuleAccesses_Id] PRIMARY KEY([Id])GO ALTER TABLE [account].[UserModuleAccesses] ADD CONSTRAINT [DF_account_UserModuleAccesses_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [account].[UserModuleAccesses] ADD CONSTRAINT [DF_account_UserModuleAccesses_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO-------------------------------------ALTER TABLE account.FiscalYearSubLedgerClosingBalances ADD TransactionBranchId UNIQUEIDENTIFIER NULL, TransactionDepartmentId UNIQUEIDENTIFIER NULLGO-----------------------------------------UPDATE vdSET vd.LedgerName = l.NameFROM account.VoucherDetails vdJOIN account.Ledgers l ON l.Id = vd.LedgerIdWHERE vd.LedgerName IS NULL;---------------------------alter table account.voucherdetails add ApprovalStatusTempId int null; alter table account.voucherdetails add ProjectNameId uniqueidentifier null; alter table account.voucherdetails add VoucherDate datetime null; alter table account.voucherdetails add VoucherTypeId uniqueidentifier null; alter table account.voucherdetails add LedgerName nvarchar(max) null; alter table account.voucherdetails add Narration nvarchar(max) null; alter table account.voucherdetails add RefId uniqueidentifier null; alter table account.voucherdetails add VoucherNo nvarchar(max) null;--------------------UPDATE vdSET vd.ApprovalStatusTempId = v.ApprovalStatusTempIdFROM account.VoucherDetails vdINNER JOIN account.Vouchers v ON vd.VoucherId = v.Id UPDATE vdSET vd.ProjectNameId = v.ProjectNameIdFROM account.VoucherDetails vdINNER JOIN account.Vouchers v ON vd.VoucherId = v.Id UPDATE vdSET vd.VoucherDate = v.VoucherDateFROM account.VoucherDetails vdINNER JOIN account.Vouchers v ON vd.VoucherId = v.Id UPDATE vdSET vd.VoucherTypeId = v.VoucherTypeIdFROM account.VoucherDetails vdINNER JOIN account.Vouchers v ON vd.VoucherId = v.Id UPDATE vdSET vd.RefId = v.RefIdFROM account.VoucherDetails vdINNER JOIN account.Vouchers v ON vd.VoucherId = v.Id UPDATE vdSET vd.Narration = v.NarrationFROM account.VoucherDetails vdINNER JOIN account.Vouchers v ON vd.VoucherId = v.Id UPDATE vdSET vd.VoucherNo = v.VoucherNoFROM account.VoucherDetails vdINNER JOIN account.Vouchers v ON vd.VoucherId = v.Id UPDATE vdSET vd.LedgerName = l.NameFROM account.VoucherDetails vdINNER JOIN account.Ledgers l ON vd.LedgerId = l.Id ---------------UPDATE account.PostingVoucherTypesSET TransactionTypeTempId = 47WHERE Id = 'aeeb1d51-5e96-4de5-93b8-3699ecf2343c' UPDATE account.PostingVoucherTypesSET TransactionTypeTempId = 51WHERE Id = '343d5ff8-7357-4724-bbc3-79283473a1ce' UPDATE account.PostingVoucherTypesSET TransactionTypeTempId = 52WHERE Id = 'f3b3b364-b3b3-4d1c-aebd-a77d996d12e1' UPDATE account.PostingVoucherTypesSET TransactionTypeTempId = 55WHERE Id = '4b428ae5-2a42-4ca4-8bdf-ff2a413599e3'

------------alter table payroll.EmployeeRegistrations add CreatedRemarks nvarchar(max) null alter table payroll.HeadingSetUps add CreatedRemarks nvarchar(max) null; alter table payroll.HeadingTypes add CreatedRemarks nvarchar(max) null --------------alter table payroll.HeadingSetUps add CreatedRemarks nvarchar(max) null; alter table payroll.HeadingTypes add CreatedRemarks nvarchar(max) null; alter table payroll.AdditionalEntitlementDeductionRule add CreatedRemarks nvarchar(max) null; alter table payroll.AdditionalEntitlementRuleSetUp add CreatedRemarks nvarchar(max) null; alter table payroll.AddititonalDeductionRuleSetUp add CreatedRemarks nvarchar(max) null;alter table payroll.Advances add CreatedRemarks nvarchar(max) null;alter table payroll.EmployeeExemptionConfiguration add CreatedRemarks nvarchar(max) null;alter table payroll.EmployeeRegistrations add CreatedRemarks nvarchar(max) null;alter table payroll.EntitlementDeductionEarningConfiguration add CreatedRemarks nvarchar(max) null;alter table payroll.EntitlementSetUps add CreatedRemarks nvarchar(max) null; alter table payroll.ExemptionConfigurations add CreatedRemarks nvarchar(max) null;alter table payroll.Exemptions add CreatedRemarks nvarchar(max) null; alter table payroll.GeneralSettings add CreatedRemarks nvarchar(max) null;alter table payroll.HeadingSetUps add CreatedRemarks nvarchar(max) null;alter table payroll.HeadingTypes add CreatedRemarks nvarchar(max) null;alter table payroll.LeaveSizes add CreatedRemarks nvarchar(max) null;alter table payroll.PaySlips add CreatedRemarks nvarchar(max) null;alter table payroll.RetirementBenefitRule add CreatedRemarks nvarchar(max) null; alter table payroll.Retirements add CreatedRemarks nvarchar(max) null;alter table payroll.RuleTypes add CreatedRemarks nvarchar(max) null; alter table payroll.SalaryTypes add CreatedRemarks nvarchar(max) null;alter table payroll.SettlementDetails add CreatedRemark nvarchar(max) null; alter table payroll.TaxSlabs add CreatedRemarks nvarchar(max) null; alter table payroll.WorkingDaysHourInMonths add CreatedRemarks nvarchar(max) null; ------------------alter table payroll.AdditionalEntitlementDeductionRules add CreatedRemarks nvarchar(max) null;alter table payroll.AdditionalEntitlementRuleSetUps add CreatedRemarks nvarchar(max) null;alter table payroll.EmployeeExemptionConfigurations add CreatedRemarks nvarchar(max) null;alter table payroll.EntitlementDeductionEarningConfigurations add CreatedRemarks nvarchar(max) null;

alter table core.Receives add IsReceiveReturn bit null;alter table core.users add LocationwisePriceId uniqueidentifier null --------------------------ALTER TABLE core.Requisites ADD CreatedByName NVARCHAR(MAX) NULLGO------------------ALTER TABLE core.CustomersALTER COLUMN Name NVARCHAR(MAX) -------------------------ALTER TABLE core.CustomersALTER COLUMN Address NVARCHAR(MAX) ----------------------------- alter table core.CustomerCreditPayments add WalletId uniqueidentifier null ALTER TABLE core.Users ADD AppleLoginId NVARCHAR(MAX) NULL;ALTER TABLE core.Users ADD GoogleLoginId NVARCHAR(MAX) NULL;----------------INSERT [core].[TransactionTypes] ([RecordId], [Id], [Name], [TempId], [DisplayOrder], [MovementType], [Tracker], [RecordStatus], [Tag], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [ModuleId], [CreatedRemarks], [HasPrinting]) VALUES (N'851C5DE3-4AFE-4564-8CF4-1997ACA8C282', N'80A6F448-F0C9-49A2-96DA-965C4A1A88F1', N'Payroll', 70, 70, 1,NULL, 1, NULL, N'00000000-0000-0000-0000-000000000000', CAST(N'2020-07-07T16:56:38.567' AS DateTime), NULL, NULL, N'aba8c305-a608-4717-8c38-2ff6409eb319', NULL, 1)GO INSERT [core].[MappingTypes] ([RecordId], [Id], [Name], [TempId], [Tracker], [RecordStatus], [Tag], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [CreatedRemarks]) VALUES (N'6785C4F9-A11A-4D22-91E3-00832A6138ED', N'DACD1965-C70A-4ED7-B77C-5D064EA25DB3',N'Payroll', 22, NULL, 1, NULL, N'00000000-0000-0000-0000-000000000000', CAST(N'2021-08-03T11:16:35.770' AS DateTime), NULL, NULL, NULL)GO----------------ALTER TABLE [core].[Items]ADD [IsActive] bit NOT NULL DEFAULT (1), [TaxPercent] decimal(4,2) NOT NULL DEFAULT (0), [IsMapping] bit NOT NULL DEFAULT (0), [PrintName] nvarchar(max) NULL, [IsRefundable] bit NOT NULL DEFAULT (0), [RefundType] varchar(50) NULL, [IsMultiUnit] bit NOT NULL DEFAULT (0), [BaseUnitId] uniqueidentifier NULL, [Attributes] varchar(255) NULL, [IsAttributewisePrice] bit NOT NULL DEFAULT (0), [ManufactureDate] date NULL, [ExpirationDate] date NULL, [IsOnWarranty] bit NOT NULL DEFAULT (0), [WarrantyType] varchar(50) NULL, [WarrantyDuration] int NULL, [Images] varchar(255) NULL, [IsServiceRequiredStartDate] bit NULL, [IsCombo] bit NULL, [IsNonConversableUnit] bit NOT NULL DEFAULT (0), [IsDiscountable] bit NOT NULL DEFAULT (1), [RefNo] uniqueidentifier NULL, [OutletId] varchar(max) NULL, [HasSerialNumber] bit NOT NULL DEFAULT (0), [DefaultUnitId] uniqueidentifier NULL, [IsDiscountItem] bit NULL;GOalter table [core].[Items] add ProductCategoryId uniqueidentifier NULLalter table [core].[Items] add ProductGroupId uniqueidentifier NULL alter table [core].[Items] add ShortDescription nvarchar(max) NULL alter table [core].[Items] add SKU nvarchar(max) NULL alter table [core].[Items] add Quantity decimal(20,5) NULL---------------------------------------------CREATE TABLE [core].[PriceStrategyUnits]( [RecordId] [uniqueidentifier] NOT NULL, [Id] [uniqueidentifier] NOT NULL, [ProductId] [uniqueidentifier] NOT NULL, [UnitId] [uniqueidentifier] NOT NULL, [MarginPercent] [decimal](16, 5) NOT NULL, [Tracker] [varchar](255) NULL, [RecordStatus] [tinyint] NOT NULL, [Tag] [varchar](255) NULL, [CreatedBy] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [DeletedBy] [uniqueidentifier] NULL, [DeletedDate] [datetime] NULL, [CreatedRemarks] [nvarchar](4000) NULL,CONSTRAINT [PK_pos_PriceStrategyUnits_Id] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO ALTER TABLE [core].[PriceStrategyUnits] ADD CONSTRAINT [DF_core_PriceStrategyUnits_RecordId] DEFAULT (newsequentialid()) FOR [RecordId]GO ALTER TABLE [core].[PriceStrategyUnits] ADD CONSTRAINT [DF_core_PriceStrategyUnits_RecordStatus] DEFAULT ((1)) FOR [RecordStatus]GO -------------------------------------- CREATE TABLE [core].[ProductPrices]( [RecordId] [uniqueidentifier] NOT NULL, [Id] [uniqueidentifier] NOT NULL, [ProductId] [uniqueidentifier] NOT NULL, [UnitId] [uniqueidentifier] NOT NULL, [Location] [varchar](50) NULL, [StartDate] [date] NULL, [EndDate] [date] NULL, [StartTime] [time](7) NULL, [EndTime] [time](7) NULL, [Price] [decimal](20, 5) NULL, [LotNo] [varchar](50) NULL, [Tracker] [varchar](255) NULL, [RecordStatus] [tinyint] NOT NULL, [Tag] [varchar](255) NULL, [CreatedBy] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [DeletedBy] [uniqueidentifier] NULL, [DeletedDate] [datetime] NULL, [CreatedRemarks] [nvarchar](4000) NULL, [CustomerId] [uniqueidentifier] NULL, [SizeId] [uniqueidentifier] NULL, [ColorId] [uniqueidentifier] NULL,CONSTRAINT [PK_pos_ProductPrices_Id] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO ALTER TABLE [core].[ProductPrices] ADD CONSTRAINT [DF_core_ProductPrices_RecordId] DEFAULT (newsequentialid()) FOR [RecordId]GO ALTER TABLE [core].[ProductPrices] ADD CONSTRAINT [DF_core_ProductPrices_RecordStatus] DEFAULT ((1)) FOR [RecordStatus]GO ----------------------------------CREATE TABLE [core].[ProductGroups]( [RecordId] [uniqueidentifier] NOT NULL, [Id] [uniqueidentifier] NOT NULL, [Name] [varchar](255) NOT NULL, [IsActive] [bit] NULL, [Tracker] [varchar](255) NULL, [RecordStatus] [tinyint] NOT NULL, [Tag] [varchar](255) NULL, [CreatedBy] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [DeletedBy] [uniqueidentifier] NULL, [DeletedDate] [datetime] NULL, [CreatedRemarks] [nvarchar](4000) NULL, [IsDefault] [bit] NOT NULL,CONSTRAINT [PK_pos_ProductGroups_Id] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO ALTER TABLE [core].[ProductGroups] ADD CONSTRAINT [DF_core_ProductGroups_RecordId] DEFAULT (newsequentialid()) FOR [RecordId]GO ALTER TABLE [core].[ProductGroups] ADD CONSTRAINT [DF_core_ProductGroups_RecordStatus] DEFAULT ((1)) FOR [RecordStatus]GO ALTER TABLE [core].[ProductGroups] ADD DEFAULT ((0)) FOR [IsDefault]GO ---------------------------------- alter table core.ItemWiseRatings add Description nvarchar(max) null; CREATE TABLE core.CrossoverCompanyInfos( Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, Name NVARCHAR(255) NOT NULL, Address NVARCHAR(500) NULL, Phone NVARCHAR(50) NULL, SupportEmail NVARCHAR(255) NULL, Website NVARCHAR(255) NULL, TutorialLink NVARCHAR(255) NULL);------------------INSERT INTO core.CrossoverCompanyInfos( Id, Name, Address, Phone, SupportEmail, Website, TutorialLink)VALUES( '0B887DB9-A4AB-4ABA-8438-3EAEBD115DDE', 'Crossover Nepal Pvt. Ltd.', 'Kupondole, Lalitpur, Nepal', '9700004424,9840028570', 'support@crossovernepal.com', 'https://crossovernepal.com/', 'https://www.youtube.com/playlist?list=PLuTM8hXDaL1L10Ep_q3WqSrbmReYSeVIf');------------------------IF NOT EXISTS (SELECT 1 FROM [core].[PaymentModes] WHERE [TempId] = 8)BEGIN INSERT [core].[PaymentModes] ([RecordId], [Id], [Name], [TempId], [Description], [Tracker], [RecordStatus], [Tag], [CreatedBy], [CreatedDate], [DeletedBy], [DeletedDate], [CreatedRemarks]) VALUES (NEWID(), N'2526D549-DF55-47D9-9FE9-697B4C3C849E', N'Prepaid', 8, N'Prepaid', NULL, 1, N'Restaurant', N'00000000-0000-0000-0000-000000000000', CAST(N'2026-05-15T00:00:00.000' AS DateTime), NULL, NULL, NULL)END------------INSERT INTO core.ledgerMappings VALUES (newid(),newid(),N'2526D549-DF55-47D9-9FE9-697B4C3C849E','Prepaid', '7E46C04B-95DD-4449-9325-6B172A259777', 'Prepaid A/C', 13, 'Sales', 8, 'Restaurant', null,null,1,null ,'00000000-0000-0000-0000-000000000000','2022-12-19 18:18:12.687', null, null, null, null, 0, null )-----------------UPDATE iSET i.BaseUnitId = p.BaseUnitId,i.Name=p.Name,i.PrintName=p.PrintName,i.IsDiscountItem=p.IsDiscountItem,i.Taxable=p.Taxable,i.IsTaxable=p.IsTaxableFROM core.Items iINNER JOIN pos.Products p ON p.RefNo = i.Id;----------------alter table core.ItemSKUsadd BoxNo varchar(225) null alter table core.ItemSKUsadd PackageCode varchar(225) null-------------------alter table core.Companies add TransactionData datetime null;-------------------ALTER TABLE core.customers ALTER COLUMN CountryName NVARCHAR(MAX) NULL; ALTER TABLE core.customers ALTER COLUMN JapaneseName NVARCHAR(MAX) NULL; ALTER TABLE core.users ALTER COLUMN CountryName NVARCHAR(MAX) NULL; ALTER TABLE core.users ALTER COLUMN JapaneseName NVARCHAR(MAX) NULL;----------------------INSERT INTO core.TransactionTypes (RecordId, Id, Name, TempId, DisplayOrder, MovementType, Tracker, RecordStatus, Tag, CreatedBy, CreatedDate, DeletedBy, DeletedDate, ModuleId, CreatedRemarks, HasPrinting) VALUES('85bbd281-6320-47a3-9ac2-5c74b93382dc', 'ab86c7cb-3cb2-4443-bfed-03a0a3ea0f0a', 'FrontOfficeGRC', 72, 72, 2, NULL, 1, 'FrontOffice', '00000000-0000-0000-0000-000000000000', CONVERT(DATETIME, '2026-04-26 09:36:26.357', 121), NULL, NULL, 'efc8dee4-96b9-446d-bba6-73be1a469f98', NULL, CONVERT(bit, 'True'))--------------------SET DATEFORMAT ymd INSERT INTO core.TransactionTypes (RecordId, Id, Name, TempId, DisplayOrder, MovementType, Tracker, RecordStatus, Tag, CreatedBy, CreatedDate, DeletedBy, DeletedDate, ModuleId, CreatedRemarks, HasPrinting) VALUES('6c8eed27-0228-447f-9509-03116779625f', '21ed4d80-e00f-4b51-b4fd-cb40a3de0e79', 'Frontoffice Collection', 73, 73, 1, NULL, 1, 'FrontOffice', '00000000-0000-0000-0000-000000000000', CONVERT(DATETIME, '2026-04-28 16:56:38.567', 121), NULL, NULL, 'efc8dee4-96b9-446d-bba6-73be1a469f98', NULL, CONVERT(bit, 'True'))GO----------------------ALTER TABLE core.DayEnds ADD BranchId UNIQUEIDENTIFIER NULL, DepartmentId UNIQUEIDENTIFIER NULLGO---------------ALTER TABLE core.CustomerCreditPayments ADD BranchId UNIQUEIDENTIFIER NULL, DepartmentId UNIQUEIDENTIFIER NULLGO--------------CREATE TABLE [core].[CustomerDiscountGroupDetails]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Name] VARCHAR(MAX) NOT NULL, [CustomerDiscountGroupId] UNIQUEIDENTIFIER NOT NULL, [CategoryId] UNIQUEIDENTIFIER NULL, [ModuleId] UNIQUEIDENTIFIER NOT NULL, [TempId] INT NULL, [DiscountPercent] DECIMAL(20, 2) NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL,)GO ALTER TABLE [core].[CustomerDiscountGroupDetails] ADD CONSTRAINT [PK_core_CustomerDiscountGroupDetails_Id] PRIMARY KEY([Id])GO ALTER TABLE [core].[CustomerDiscountGroupDetails] ADD CONSTRAINT [DF_core_CustomerDiscountGroupDetails_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [core].[CustomerDiscountGroupDetails] ADD CONSTRAINT [DF_core_CustomerDiscountGroupDetails_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO--------------------ALTER TABLE core.CustomerDiscountGroupsADD HasDetail BIT NOT NULL DEFAULT (0);-----------------------UPDATE core. ItemsSET Quantity = 0---ALTER TABLE core.Issues ADD PurchaseId UNIQUEIDENTIFIER NULLGO-------------alter table core.accountpostings add IsProcess bit null----------ALTER TABLE core.StockTransactions ADD ProjectNameId UNIQUEIDENTIFIER NULL GO-----------ALTER TABLE core.StockTransactionDetailSKUs ADD ProjectNameId UNIQUEIDENTIFIER NULL GOALTER TABLE core.StockTransactionDetails ADD ProjectNameId UNIQUEIDENTIFIER NULL GO-----------------update core.Items set Quantity = '0.00'----update core.Items set ProductCategoryId = CategoryId----------update core.Items set IsDiscountItem = 1----------------------UPDATE iSET i.DefaultUnitId = p.DefaultUnitIdFROM core.items iJOIN pos.products p ON p.RefNo = i.Id;-------------------------UPDATE iSET i.BaseUnitId = p.BaseUnitIdFROM core.Items iINNER JOIN pos.Products p ON p.RefNo = i.Id;----------------CREATE TABLE [core].[NepaliDateAdds]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [Year] VARCHAR(MAX) NOT NULL, [Baisakh] VARCHAR(MAX) NOT NULL, [Jestha] VARCHAR(MAX) NOT NULL, [Ashadh] VARCHAR(MAX) NOT NULL, [Shrawan] VARCHAR(MAX) NOT NULL, [Bhadra] VARCHAR(MAX) NOT NULL, [Ashwin] VARCHAR(MAX) NOT NULL, [Kartik] VARCHAR(MAX) NOT NULL, [Mangsir] VARCHAR(MAX) NOT NULL, [Poush] VARCHAR(MAX) NOT NULL, [Magh] VARCHAR(MAX) NOT NULL, [Falgun] VARCHAR(MAX) NOT NULL, [Chaitra] VARCHAR(MAX) NOT NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [core].[NepaliDateAdds] ADD CONSTRAINT [PK_core_NepaliDateAdds_Id] PRIMARY KEY([Id])GO ALTER TABLE [core].[NepaliDateAdds] ADD CONSTRAINT [DF_core_NepaliDateAdds_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [core].[NepaliDateAdds] ADD CONSTRAINT [DF_core_NepaliDateAdds_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO----------------------INSERT INTO [core].[NepaliDateAdds]( RecordId, Id, [Year], Baisakh, Jestha, Ashadh, Shrawan, Bhadra, Ashwin, Kartik, Mangsir, Poush, Magh, Falgun, Chaitra, Tracker, RecordStatus, Tag, CompanyId, BranchId, CreatedBy, CreatedDate)VALUES(NEWID(),NEWID(),'2000','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2001','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2002','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2003','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2004','30','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2005','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2006','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2007','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2008','31','31','31','32','31','31','29','30','30','29','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2009','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2010','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2011','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2012','31','31','31','32','31','31','29','30','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2013','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2014','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2015','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2016','31','31','31','32','31','31','29','30','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2017','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2018','31','32','31','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2019','31','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2020','31','31','31','32','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2021','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2022','31','32','31','32','31','30','30','30','29','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2023','31','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2024','31','31','31','32','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2025','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2026','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2027','30','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2028','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2029','31','31','32','31','32','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2030','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2031','30','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2032','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2033','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2034','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2035','30','32','31','32','31','31','29','30','30','29','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2036','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2037','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2038','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2039','31','31','31','32','31','31','29','30','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2040','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2041','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2042','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2043','31','31','31','32','31','31','29','30','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2044','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2045','31','32','31','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2046','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2047','31','31','31','32','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2048','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2049','31','32','31','32','31','30','30','30','29','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2050','31','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2051','31','31','31','32','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2052','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2053','31','32','31','32','31','30','30','30','29','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2054','31','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2055','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2056','31','31','32','31','32','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2057','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2058','30','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2059','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2060','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2061','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2062','30','32','31','32','31','31','29','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2063','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2064','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2065','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2066','31','31','31','32','31','31','29','30','30','29','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2067','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2068','31','31','32','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2069','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2070','31','31','31','32','31','31','29','30','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2071','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2072','31','32','31','32','31','30','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2073','31','32','31','32','31','30','30','30','29','29','30','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2074','31','31','31','32','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2075','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2076','31','32','31','32','31','30','30','30','29','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2077','31','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2078','31','31','31','32','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2079','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2080','31','32','31','32','31','30','30','30','29','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2081','31','32','31','32','31','30','30','30','29','30','29','31',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2082','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2083','31','31','32','31','31','31','30','29','30','29','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2084','31','31','32','31','31','30','30','30','29','30','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2085','31','32','31','32','30','31','30','30','29','30','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2086','30','32','31','32','31','30','30','30','29','30','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2087','31','31','32','31','31','31','30','30','29','30','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2088','30','31','32','32','30','31','30','30','29','30','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2089','30','32','31','32','31','30','30','30','29','30','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()),(NEWID(),NEWID(),'2090','30','32','31','32','31','30','30','30','29','30','30','30',NULL,1,NULL,NEWID(),NEWID(),NEWID(),GETDATE()); -----------------------alter table core.items add CountryName nvarchar(max) null----------------ALTER TABLE core.StockTransactions ADD MRP Decimal(20,5) NULL GO -------------------------ALTER TABLE core.StockTransactionDetails ADD MRP Decimal(20,5) NULL GO-----------------SET DATEFORMAT ymd INSERT INTO core.TransactionTypes (RecordId, Id, Name, TempId, DisplayOrder, MovementType, Tracker, RecordStatus, Tag, CreatedBy, CreatedDate, DeletedBy, DeletedDate, ModuleId, CreatedRemarks, HasPrinting) VALUES('ddde1156-2cca-40d1-8dbc-85bae19895a2', 'eee67703-803e-4a88-9670-29e764a794f7', 'PurchaseRequisite', 71, 71, 1, NULL, 1, 'Inventory', '00000000-0000-0000-0000-000000000000', CONVERT(DATETIME, '2026-03-19 16:56:38.567', 121), NULL, NULL, '8c9fb36e-014f-4139-82b2-99b78335a68d', NULL, CONVERT(bit, 'True'))GO--------------alter table core.IssueDetails alter column Rate decimal(20,5) null-------alter table core.parkings add IsAirportTaxi bit null;--------------update core.TransactionTypes set ModuleId = 'B778CA7B-D103-4A28-BFF8-F27AACA36D52' where Name='RestaurantAbbreviated'update core.PrintTemplates set TransactionId = 'F5007D08-61ED-410D-8639-EA8AE6AA3972' where Name ='Abbreviated'alter table core.parkings add DiscountAmount decimal(20,5) null; alter table core.parkings add PaidAmount decimal(20,5) null;-----------alter table core.users add Address varchar(max) nullalter table core.users add Guide varchar(max) nullalter table core.users add AppInformation varchar(max) nullalter table core.users add PhoneNumber varchar(max) nullalter table core.users add PhoneNumberCode varchar(max) null alter table core.users add CountryCode varchar(max) nullalter table core.users add CountryName varchar(max) nullalter table core.users add JapaneseName varchar(max) null----29mar-----------alter table core.customers add Guide varchar(max) nullalter table core.customers add AppInformation varchar(max) nullalter table core.customers add PhoneNumber varchar(max) nullalter table core.customers add PhoneNumberCode varchar(max) nullalter table core.customers add CountryCode varchar(max) nullalter table core.customers add CountryName varchar(max) nullalter table core.customers add JapaneseName varchar(max) null----------------------------alter table core.users add PostalCode varchar(max) null; alter table core.customers add PostalCode varchar(max) null---------------------------ALTER TABLE core.customers ADD AppleLoginId NVARCHAR(MAX) NULL;ALTER TABLE core.customers ADD GoogleLoginId NVARCHAR(MAX) NULL;--------------------ALTER TABLE core.customers ADD SubscribeToNewsletter bit NULL; ALTER TABLE core.users ADD SubscribeToNewsletter bit NULL;---------------------march complete----------------

CREATE TABLE core.ActivityLogs( Id uniqueidentifier not null PRIMARY KEY, UserId NVARCHAR(100) NULL, UserName NVARCHAR(256) NULL, Action NVARCHAR(50) NOT NULL, -- View | Add | Edit | Delete EntityName NVARCHAR(256) NULL, EntityId NVARCHAR(100) NULL, RequestBody NVARCHAR(MAX) NULL, ResponseStatus NVARCHAR(10) NULL, IPAddress NVARCHAR(50) NULL, HttpMethod NVARCHAR(10) NULL, Endpoint NVARCHAR(500) NULL, DurationMs BIGINT NOT NULL DEFAULT 0, Timestamp DATETIME2 NOT NULL DEFAULT GETUTCDATE()); -- ✅ Index for fast queries by user or entityCREATE INDEX IX_ActivityLogs_UserId ON core.ActivityLogs (UserId);CREATE INDEX IX_ActivityLogs_EntityName ON core.ActivityLogs (EntityName);CREATE INDEX IX_ActivityLogs_Timestamp ON core.ActivityLogs (Timestamp DESC);

ALTER TABLE humanresource.Attendances ADD OverTime DECIMAL (18,8) CREATE TABLE [humanresource].[DutyRosters]( [RecordId] UNIQUEIDENTIFIER NOT NULL, [Id] UNIQUEIDENTIFIER NOT NULL, [EmployeeId] UNIQUEIDENTIFIER NULL, [DesignationId] UNIQUEIDENTIFIER NULL, [ShiftId] UNIQUEIDENTIFIER NULL, [DutyDateAD] DATETIME NULL, [DutyDateBS] VARCHAR(MAX) NULL, [Remarks] VARCHAR(MAX) NULL, [Tracker] VARCHAR(255) NULL, [RecordStatus] TINYINT NOT NULL, [Tag] VARCHAR(255) NULL, [CompanyId] UNIQUEIDENTIFIER NOT NULL, [BranchId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] UNIQUEIDENTIFIER NOT NULL, [CreatedDate] DATETIME NOT NULL, [CreatedRemarks] VARCHAR(255) NULL, [DeletedBy] UNIQUEIDENTIFIER NULL, [DeletedDate] DATETIME NULL)GO ALTER TABLE [humanresource].[DutyRosters] ADD CONSTRAINT [PK_humanresource_DutyRosters_Id] PRIMARY KEY([Id])GO ALTER TABLE [humanresource].[DutyRosters] ADD CONSTRAINT [DF_humanresource_DutyRosters_RecordId] DEFAULT NEWSEQUENTIALID() FOR [RecordId]GO ALTER TABLE [humanresource].[DutyRosters] ADD CONSTRAINT [DF_humanresource_DutyRosters_RecordStatus] DEFAULT 1 FOR [RecordStatus]GO----------------------