ALTER PROCEDURE [dbo].[mnrPrcSaudiIntGetBills] 	
			        	@DateStartSync    DATETIME ='2024-10-12',
						@MatDefaultUnit   NVARCHAR(512)    ='PCE'

	AS 
	 SET NOCOUNT ON      
--update mnrfileinfo set value='2024-10-31' where type ='EINVOICE_DATESTARTSYNC'
--SystemFlag = 5 saudi
--BEBillType -- 0 = simple , 1 = normal taxed

	 DECLARE @SARCurID UNIQUEIDENTIFIER
	 Set @SARCurID= 0x0
	 SELECT @SARCurID= ID FROM mnrCurrency WHERE ISOCode='SAR'



           Create table #CustAcc(	   
                            --AccID		          UNIQUEIDENTIFIER ,
							CustID		          UNIQUEIDENTIFIER ,
							Schemid               NVARCHAR(256) COLLATE Arabic_CI_AI,
						    RegistrationNum       NVARCHAR(256) COLLATE Arabic_CI_AI,
							street                NVARCHAR(256) COLLATE Arabic_CI_AI,
							BuildingNumber        NVARCHAR(256) COLLATE Arabic_CI_AI,
							CityName              NVARCHAR(256) COLLATE Arabic_CI_AI,
							PostalZone            NVARCHAR(256) COLLATE Arabic_CI_AI,
							CitySubdivisionName   NVARCHAR(256) COLLATE Arabic_CI_AI,
							Country               NVARCHAR(256) COLLATE Arabic_CI_AI,
							RegistrationName      NVARCHAR(256) COLLATE Arabic_CI_AI,
							CompanyID             NVARCHAR(256) COLLATE Arabic_CI_AI,
				  	 )
	INSERT INTO #CustAcc				  	 
		SELECT 
			ISNULL(cu.ID,0X0),
			CASE WHEN ISNULL(cu.ID,0X0) !=0X0   AND ISNULL(cu.RegistrationNum,'') !='' AND ISNULL(cu.ActivityType,0) =1 THEN 'CRN'  ELSE 'NAT' END,
			CASE WHEN ISNULL(cu.RegistrationNum,'')  !=''  THEN REPLACE(ISNULL(cu.RegistrationNum,'') , ' ', '')  ELSE ISNULL(CU.IdentityNum,'') END,
			ISNULL(cu.Street,''),
			ISNULL(cu.License1,''),
			ISNULL(cu.City,''),
			ISNULL(cu.PoBox,''),
			ISNULL(cu.Area,''),
			ISNULL(cu.Country,''),
			ISNULL(cu.Name,''),
			REPLACE(ISNULL (cu.TaxNum,''), ' ', '')
		FROM
			mnrCust cu 
		

		INSERT INTO #CustAcc VALUES (0x0, 'NAT','12345678','','','','','','SA','','')
 
 --SELECT * FROM #CustAcc

 	Create table #IntBillType(	    ID		           UNIQUEIDENTIFIER,
	                                BillType		   INT,
									EFaturaType        INT,
				  			  )
	insert into #IntBillType				  	 
				SELECT 
			intbt.ID,
			CASE
				WHEN intbt.BillType = 4   THEN 5 ELSE intbt.BillType END ,
			CASE
				WHEN intbt.BillType = 3        THEN 388        --'Invoice'
				WHEN (intbt.BillType = 4 )  OR  (intbt.BillType = 5 )   THEN 381        --'Credit Note'

            END			
		FROM
			mnrEGIntBillType intbt
       
--	 SELECT * FROM  mnrEGIntBillType


	  DECLARE @TaxNum        NVARCHAR(256) ; 
	  DECLARE @RegistrationName         NVARCHAR(256) ;   -- اسم المنشاة 
	  DECLARE @CommericalReg            NVARCHAR(256); -- رقم المنشاة 
	  DECLARE @Country                 NVARCHAR(256);
	  DECLARE @CityName NVARCHAR(256);
	  DECLARE @CitySubdivisionName NVARCHAR(256);
	  DECLARE @Street NVARCHAR(256);
	  DECLARE @BuildingNumber NVARCHAR(256);
	  DECLARE @PostalZone NVARCHAR(256);
	  DECLARE @bCoreFlag  bit;	 
			
			        set   @TaxNum              = (select  isnull(value , '') as value from mnrFileInfo where Type = 'COMPANY_TAXNUM' ) ;
				    set   @RegistrationName    = (select  isnull(value , '') as value from mnrFileInfo where Type = 'COMPANY_NAME' )  ; 
				    set   @CommericalReg       = (select  isnull(value, '' ) as value from mnrFileInfo where Type = 'COMPANY_COMMERCIALREG') ;
				    set   @Country             = (select  isnull(value, '' ) as value from mnrFileInfo where Type = 'COMPANY_COUNTRY')  ;
					set   @CityName            = (select  isnull(value, '' ) as value from mnrFileInfo where Type = 'COMPANY_CITY') ;
					set   @CitySubdivisionName = (select  isnull(value, '' ) as value from mnrFileInfo where Type = 'COMPANY_AREA')  ;
					set   @Street              = (select  isnull(value, '' ) as value from mnrFileInfo where Type = 'COMPANY_STREET')  ;
					set   @BuildingNumber      = (select  isnull(value, '' ) as value from mnrFileInfo where Type = 'COMPANY_BUILDING')  ;
					set   @PostalZone          = (select  isnull(value, '' ) as value from mnrFileInfo where Type = 'COMPANY_EMAILADDRESS')  

					SET @bCoreFlag = (
					SELECT CASE 
							   WHEN CHARINDEX('core', value) > 0 THEN 1
							   ELSE 0
						   END
					FROM mnrFileInfo
					WHERE Type = 'EINVOICE_URL');





          Create table #Branch(	   
                              ID		          UNIQUEIDENTIFIER ,
							  Suplier_TaxNum                   NVARCHAR(256) COLLATE Arabic_CI_AI,
							  Suplier_RegistrationName         NVARCHAR(256) COLLATE Arabic_CI_AI,  
	                          Suplier_CommericalReg            NVARCHAR(256) COLLATE Arabic_CI_AI, 
	                          Suplier_Country                  NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_CityName                 NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_CitySubdivisionName      NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_Street                   NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_BuildingNumber           NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_PostalZone               NVARCHAR(256) COLLATE Arabic_CI_AI
				  	 )
	INSERT INTO #Branch				  	 
		SELECT 
		    br.id,
			ISNULL(@TaxNum,''),
			CASE 
				WHEN ISNULL(br.CommercialRegistrationNum,'')='' 
						THEN @RegistrationName
					ELSE br.Name
			END ,
			CASE 
				WHEN ISNULL(br.CommercialRegistrationNum,'')='' 
						THEN @CommericalReg
					ELSE br.CommercialRegistrationNum
			END,
			CASE 
				WHEN ISNULL(br.Country,'')='' 
						THEN @Country
					ELSE br.Country
			END,

			CASE 
			WHEN ISNULL(br.City,'')='' 
					THEN @CityName
				ELSE br.City
		    END,

			CASE 
			 WHEN ISNULL(br.Area,'')='' 
					THEN @CitySubdivisionName
				ELSE br.Area
		    END,

			CASE 
			 WHEN ISNULL(br.Street,'')='' 
					THEN @Street
				ELSE br.Street
		    END,

			CASE 
			 WHEN ISNULL(br.Building,'')='' 
					THEN @BuildingNumber
				ELSE br.Building
		    END,

			CASE 
			 WHEN ISNULL(br.PostalCode,'')='' 
					THEN @PostalZone
				ELSE br.PostalCode
		    END
		FROM
			mnrbranch br 
		

		INSERT INTO #Branch VALUES (0x0,ISNULL(@TaxNum,''),
		ISNULL(@RegistrationName,''),
		ISNULL(@CommericalReg,''),
		ISNULL(@Country,''),
		ISNULL(@CityName,''),
		ISNULL(@CitySubdivisionName,''),
		ISNULL(@Street,''),
		ISNULL(@BuildingNumber,''),
		ISNULL(@PostalZone,'')
		)

		--select * from mnrbranch
	--   BillType =3  sell,  BillType =5 return
	Create table #BillType(	   
	                            ID		        UNIQUEIDENTIFIER,
		                        BillTypePrefix  NVARCHAR(256) NULL,
								bvat            INT,
								bttc            INT,
								EFaturaType     INT,
								BillType        INT,
						      ---------------------
							  Suplier_TaxNum                   NVARCHAR(256) COLLATE Arabic_CI_AI,
							  Suplier_RegistrationName         NVARCHAR(256) COLLATE Arabic_CI_AI,  
	                          Suplier_CommericalReg            NVARCHAR(256) COLLATE Arabic_CI_AI, 
	                          Suplier_Country                  NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_CityName                 NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_CitySubdivisionName      NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_Street                   NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_BuildingNumber           NVARCHAR(256) COLLATE Arabic_CI_AI,
	                          Suplier_PostalZone               NVARCHAR(256) COLLATE Arabic_CI_AI
				  		   )
	INSERT INTO #BillType				  	 
		SELECT 
			bt.ID,
			ISNULL(bt.Prefix,''),
			ISNULL(bt.bVAT,0),
			ISNULL(bt.bTTC,0),
			INTT.EFaturaType,
			bt.Type,
			-------------------
			br.Suplier_TaxNum                  ,
			br.Suplier_RegistrationName         ,  
	        br.Suplier_CommericalReg           , 
	        br.Suplier_Country                  ,
	        br.Suplier_CityName                ,
	        br.Suplier_CitySubdivisionName      ,
	        br.Suplier_Street                   ,
	        br.Suplier_BuildingNumber          ,
	        br.Suplier_PostalZone               
		FROM
			mnrBillType bt
			INNER JOIN #Branch br       ON ISNULL(bt.DefBranchID,0x0) = (br.ID)
			INNER JOIN #IntBillType INTT ON INTT.BillType              = (bt.Type)
        WHERE (bt.Type =3) OR (bt.Type =5)
		
--select * from #BillType
--SELECT * FROM mnrBillType --WHERE DefBranchID !=0X0


	Create table #BillIntegration (	   
									BillID		   UNIQUEIDENTIFIER,
				  				  )
	INSERT INTO #BillIntegration				  	 
		SELECT 
			bin.BillID
		FROM
			mnrBillIntegration bin
		WHERE    bin.SystemFlag = 5	 AND (Status !=0) --AND bin.bCoreFlag = @bCoreFlag


--select * from #BillIntegration
 

	--Create table #TaxClassification (	   
	--								ID		   UNIQUEIDENTIFIER,
	--								Number     INT NULL,
	--								Code       NVARCHAR(256)       COLLATE Arabic_CI_AI NULL, 
	--							    Type       NVARCHAR(256)       COLLATE Arabic_CI_AI NULL,
	--								Rate       FLOAT NULL,  
	--								Name       NVARCHAR(256)       COLLATE Arabic_CI_AI NULL, 
	--								LatinName  NVARCHAR(256)       COLLATE Arabic_CI_AI NULL, 
	--			  				  )
	--INSERT INTO #TaxClassification				  	 
	--	SELECT 
	--		taxtype.ID,
	--		taxtype.Number,
	--		taxtype.Code,
	--		taxtype.Type,
	--		taxtype.Rate,
	--		taxtype.Name,
	--		taxtype.LatinName
	--	FROM
	--		mnrTaxClassification taxtype ORDER BY Number
	
--select * from #TaxClassification

    CREATE TABLE #BillNotSent(
                               BillID		         UNIQUEIDENTIFIER  NULL,
							   BillDate            DATETIME NULL,
							   bCoreFlag            INT NULL,
							   EinvoicePayType       INT NULL,
							   Number                FLOAT NULL,
							   BillTypePrefix        NVARCHAR(256) NULL,
							   InvoiceTypeCodeid     INT NULL,
							   InvoiceTypeCodeName   NVARCHAR(256) COLLATE Arabic_CI_AI,
							   --suuplier info
							   Suplier_TaxNum                    NVARCHAR(256) COLLATE Arabic_CI_AI,
							   Suplier_RegistrationName         NVARCHAR(256) COLLATE Arabic_CI_AI,  
	                           Suplier_CommericalReg            NVARCHAR(256) COLLATE Arabic_CI_AI, 
	                           Suplier_Country                  NVARCHAR(256) COLLATE Arabic_CI_AI,
	                           Suplier_CityName                 NVARCHAR(256) COLLATE Arabic_CI_AI,
	                           Suplier_CitySubdivisionName      NVARCHAR(256) COLLATE Arabic_CI_AI,
	                           Suplier_Street                   NVARCHAR(256) COLLATE Arabic_CI_AI,
	                           Suplier_BuildingNumber           NVARCHAR(256) COLLATE Arabic_CI_AI,
	                           Suplier_PostalZone               NVARCHAR(256) COLLATE Arabic_CI_AI ,
							   --cust info
								--AccID		          UNIQUEIDENTIFIER ,
								CustID		          UNIQUEIDENTIFIER ,
								Schemid               NVARCHAR(256) COLLATE Arabic_CI_AI,
								RegistrationNum       NVARCHAR(256) COLLATE Arabic_CI_AI,
								street                NVARCHAR(256) COLLATE Arabic_CI_AI,
								BuildingNumber        NVARCHAR(256) COLLATE Arabic_CI_AI,
								CityName              NVARCHAR(256) COLLATE Arabic_CI_AI,
								PostalZone            NVARCHAR(256) COLLATE Arabic_CI_AI,
								CitySubdivisionName   NVARCHAR(256) COLLATE Arabic_CI_AI,
								Country               NVARCHAR(256) COLLATE Arabic_CI_AI,
								RegistrationName      NVARCHAR(256) COLLATE Arabic_CI_AI,
								CompanyID             NVARCHAR(256) COLLATE Arabic_CI_AI,
								---------------
								BExeclusiveAmount          FLOAT,
								BTotalItemDisc             FLOAT,
								BNetAmount                 FLOAT,
								BInclusiveAmount           FLOAT,
								BTotalDisc                 FLOAT,
								BTotalExtra                FLOAT,
								BTotal                     FLOAT,
								BVAT                       FLOAT,
								bttc                       INT,
								----
								BillType                 INT ,
								LinkID		             UNIQUEIDENTIFIER,
								ReferenceNumber          FLOAT ,
								ReferenceDate            DATETIME,
								------for items--
								bvat_check INT 
				  	         ) 
				 
		INSERT INTO #BillNotSent
		SELECT 
			b.bID,
			b.BTimeIn,
			@bCoreFlag,
			CASE
				WHEN b.BPayType =0     THEN 10 --10 In cash
				WHEN b.BPayType =1     THEN 30--30 Credit
            END AS InvoicePayType,
			b.bNumber,
			isnull(bt.BillTypePrefix,''),
            bt.EFaturaType  AS InvoiceTypeCodeid,
			CASE	WHEN ISNULL(b.BEBillType ,0)=0  THEN  '0200000' ELSE CASE WHEN  LOWER(bt.BillTypePrefix) like'%export%' AND bt.bttc = 0 AND bt.bvat = 0 then '0100100' else '0100000'   END END, -- 0200000 = simple , 0100000 = normal taxed ,0100100  exported
		  ------
			--suplier info 
			bt.Suplier_TaxNum,
			bt.Suplier_RegistrationName,
			bt.Suplier_CommericalReg         ,
	        bt.Suplier_Country ,
	        bt.Suplier_CityName      ,
	        bt.Suplier_CitySubdivisionName ,
	        bt.Suplier_Street         ,
	        bt.Suplier_BuildingNumber ,
	        bt.Suplier_PostalZone   ,
			ac.*,
			---
			CASE WHEN bt.bttc=1 THEN sum(round((b.FixbiPrice*b.biBillQty  - ISNULL(b.FixBiDisc,0)  + ISNULL(b.FixBiExtra,0) - b.FixBiVAT),2) ) 
			                    ELSE sum(round(b.FixbiPrice*b.biBillQty  - ISNULL(b.FixBiDisc,0)  + ISNULL(b.FixBiExtra ,0),2) ) END AS BExeclusiveAmount   ,--TaxExclusiveAmount
			 
			ISNULL(b.FixBItemsDisc ,0),

			CASE WHEN bt.bttc=1 THEN sum(round(((b.FixbiPrice*b.biBillQty) -ISNULL(b.FixBiDisc,0) + ISNULL(b.FixBiExtra,0)  -((b.FixbiPrice*b.biBillQty) -((b.FixbiPrice*b.biBillQty)/(1+b.biVATRatio)))),2) ) 
			                    ELSE sum(round(b.FixbiPrice*b.biBillQty  - ISNULL(b.FixBiDisc,0)  + ISNULL(b.FixBiExtra,0),2) )  END AS BNetAmount,--linextention
			
			CASE WHEN bt.bttc=1 THEN sum(round((b.FixbiPrice*b.biBillQty  - ISNULL(b.FixBiDisc,0)  + ISNULL(b.FixBiExtra,0) ),2) ) 
			                    ELSE sum(round(b.FixbiPrice*b.biBillQty  - ISNULL(b.FixBiDisc,0)  + ISNULL(b.FixBiExtra,0) ,2))  END AS BInclusiveAmount,--TaxInclusiveAmount
	
			round(ISNULL(b.FixBTotalDisc,0),2)  ,
			round(ISNULL(b.FixBTotalExtra,0),2),
			0,--b.FixBTotal, --faild sometime,
			0,-- b.FixBVAT,--faild sometime,
			bt.bttc,
			bt.BillType,
			ISNULL(b.BLinkID ,0x0),
			b.bNumber,
		    b.BDate,
			ISNULL(bt.bvat,0)
		 FROM [dbo].[mnrFnBI_Fixed](@SARCurID) b
		 	 INNER JOIN  #BillType         bt   ON b.BBillTypeID  = bt.ID
		     INNER JOIN  mnrEntry          ent  ON ent.ParentID   = b.BID
			 INNER JOIN  #CustAcc          ac   ON ac.CustID      = b.BCustID
			 LEFT  JOIN  #BillIntegration  bin  ON bin.BillID     = b.BID
		 WHERE   
		     ((bt.BillType= 3) OR (bt.BillType = 5) ) 
			 AND ( ISNULL(ent.IsPosted,0)  = 1 )
		     AND ( b.BTimeIn>= @DateStartSync )
			-- AND   ISNULL(b.BEFaturaNumber,'') =''
				--AND ( ISNULL(b.BLinkID,0x0)!=0x0)OR( ISNULL(b.BLinkID,0x0) = 0x0) )
		       AND (bin.BillID IS NULL) 
         Group by b.BID,
		 		    b.BTimeIn,
		 	CASE
				WHEN b.BPayType =0     THEN 10 --10 In cash
				WHEN b.BPayType =1     THEN 30--30 Credit
            END,
		 b.BNumber,
		 bt.BillTypePrefix,
		 bt.EFaturaType ,
	      CASE	WHEN ISNULL(b.BEBillType ,0)=0  THEN  '0200000' ELSE CASE WHEN  LOWER(bt.BillTypePrefix) like'%export%' AND bt.bttc = 0 AND bt.bvat = 0 then '0100100' else '0100000'   END END, -- 0200000 = simple , 0100000 = normal taxed ,0100100  exported
		  ------
		    bt.Suplier_TaxNum,
		    bt.Suplier_RegistrationName,
		  	bt.Suplier_CommericalReg         ,
	        bt.Suplier_Country ,
	        bt.Suplier_CityName      ,
	        bt.Suplier_CitySubdivisionName ,
	        bt.Suplier_Street         ,
	        bt.Suplier_BuildingNumber ,
	        bt.Suplier_PostalZone   ,
		  -----
             ac.CustID,ac.Schemid,ac.RegistrationNum,ac.street ,ac.BuildingNumber,
				      ac.CityName,ac.PostalZone,ac.CitySubdivisionName,ac.Country,
					  ac.RegistrationName,ac.CompanyID,
			ISNULL(b.FixBItemsDisc ,0),

			round(ISNULL(b.FixBTotalDisc,0),2)  ,
			round(ISNULL(b.FixBTotalExtra,0),2),
			-- b.FixBVAT,
			bttc,
			bt.BillType,
			ISNULL(b.BLinkID ,0x0),
			b.BDate,
			ISNULL(bt.bvat,0)
------
		UPDATE 
			r
		SET 
			r.ReferenceNumber  = b.Number,
			r.ReferenceDate    = b.Date		
		FROM 
			#BillNotSent  r
			INNER JOIN mnrbill b  ON (b.ID = r.LinkID)
        WHERE  (r.BillType= 5)


   SELECT * FROM #BillNotSent  r
   	 ORDER  BY  r.BillDate            , 
	            r.Number ,
			    r.BillID


	
-- ************************ #Result ************************ --
		CREATE TABLE #Result(
		                        BNumber                  FLOAT NULL,
		                        BillID		             UNIQUEIDENTIFIER NULL,
								 BillDate            DATETIME,
								biNumber                 FLOAT NULL,
								billItemID		         UNIQUEIDENTIFIER NULL,
								------billitem  info
								MatName                  NVARCHAR(256)       COLLATE Arabic_CI_AI,
								MatUnit                  NVARCHAR(256)       COLLATE Arabic_CI_AI,
								TaxType				     NVARCHAR(512)       COLLATE ARABIC_CI_AI ,--type
								TaxLatinName             NVARCHAR(512)       COLLATE ARABIC_CI_AI,--Name,LatinName
								TaxCode                  NVARCHAR(512)       COLLATE ARABIC_CI_AI,--code
								biQty                    FLOAT,
								biPrice                  FLOAT,

								biVATRatio               FLOAT,
								ItemFixbiVAT             FLOAT,
								biVAT                    FLOAT,
								biDisc                   FLOAT,
								LineExtensionAmount      FLOAT,--LineExtensionAmount 
								biTotal                  FLOAT,							
	                           -- RoundingAmount           FLOAT,
								bttc                       INT,

							 )
 			INSERT INTO #Result
			   SELECT DISTINCT
			    bi.BNumber  ,
				bi.BID,
				bi.BTimeIn,
				bi.biNumber,
				bi.biID,
REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(mt.Name, '-', ''), 
                            '&', ''
                        ), 
                        '>', ''
                    ), 
                    '<', ''
                ), 
                '+', ''
            ), 
            '.', ''
        ), 
        '*', ''
    ),
    '''', ''  -- This removes the apostrophe character
),
				@MatDefaultUnit,--CASE WHEN ISNULL(mtu.EInvoiceUnitCode,'')='' THEN @MatDefaultUnit ElSE mtu.EInvoiceUnitCode END as EInvoiceUnitCode  ,
				CASE WHEN  LOWER(nsent.BillTypePrefix) like'%export%' AND nsent.bttc = 0 AND nsent.bvat_check = 0  THEN 'Z'  ELSE ISNULL(taxtype.Type,'') END,
				CASE WHEN  LOWER(nsent.BillTypePrefix) like'%export%' AND nsent.bttc = 0 AND nsent.bvat_check = 0 AND mt.type = 1 THEN 'Export of goods' WHEN  nsent.bttc = 0 AND nsent.bvat_check = 0 AND mt.type = 0 THEN 'Export of services'  ELSE ISNULL(taxtype.Type,'') END,
				CASE WHEN  LOWER(nsent.BillTypePrefix) like'%export%' AND nsent.bttc = 0 AND nsent.bvat_check = 0 AND mt.type = 1 THEN 'VATEX-SA-32'     WHEN  nsent.bttc = 0 AND nsent.bvat_check = 0 AND mt.type = 0 THEN 'VATEX-SA-33'         ELSE ISNULL(taxtype.Code,'') END,
				bi.biBillQty ,
				CASE WHEN nsent.bttc=1 THEN bi.FixbiPrice - (((bi.FixbiPrice*bi.biBillQty) -((bi.FixbiPrice*bi.biBillQty)/(1+bi.biVATRatio)))/biBillQty )else bi.FixbiPrice end ,
				CASE WHEN  LOWER(nsent.BillTypePrefix) like'%export%' AND nsent.bttc = 0 AND nsent.bvat_check = 0  THEN 0 ELSE bi.biVATRatio*100 END ,--CASE WHEN ISNULL(bi.fixbiVAT,0)=0 THEN 0 ELSE bi.biVATRatio*100 END,
				CASE WHEN  LOWER(nsent.BillTypePrefix) like'%export%' then 0 else CASE WHEN nsent.bttc=1 THEN   (bi.FixbiPrice*bi.biBillQty) -((bi.FixbiPrice*bi.biBillQty)/(1+bi.biVATRatio)) else  (( bi.FixbiPrice*bi.biBillQty) -bi.FixBiDisc+bi.FixBiExtra)*bi.biVATRatio  end end AS ItemfixbiVAT,  
				bi.fixbiVAT  ,
				bi.FixbiDisc  ,
				CASE WHEN nsent.bttc=1 THEN ((bi.FixbiPrice - (((bi.FixbiPrice*bi.biBillQty) -((bi.FixbiPrice*bi.biBillQty)/(1+bi.biVATRatio)))/biBillQty))*bi.biBillQty) -bi.FixBiDisc + bi.FixBiExtra  
				ELSE ( bi.FixbiPrice*bi.biBillQty) - bi.FixBiDisc+bi.FixBiExtra END  AS lineExtention,--LineExtensionAmount

				CASE WHEN nsent.bttc=1 THEN (bi.FixbiPrice*bi.biBillQty)  - bi.FixBiDisc  + bi.FixBiExtra 
				     ELSE round(( bi.FixbiPrice*bi.biBillQty),2)- bi.FixBiDisc +bi.FixBiExtra + round(bi.fixbiVAT ,2)  END  AS bitotal,

				nsent.bttc
		        FROM [dbo].[mnrFnBI_Fixed](@SARCurID) bi  
				 INNER JOIN #BillNotSent         nsent      ON bi.BID                    = nsent.BillID 
				 INNER JOIN mnrMaterial          mt         ON mt.ID                     = bi.biMatID
				 INNER JOIN mnrTaxClassification   taxtype  ON mt.TaxClassificationID    = taxtype.ID
				 --LEFT JOIN mnrMatUnitIntegration mtu        ON 				CASE
					--WHEN bi.biUnit =1 THEN mt.Unit1
					--WHEN bi.biUnit =2 THEN mt.Unit2
					--WHEN bi.biUnit =3 THEN mt.Unit3
					--WHEN bi.biUnit =4 THEN mt.Unit4
					--WHEN bi.biUnit =5 THEN mt.Unit5
					--END = mtu.ManaraMatUnit
				

				 SELECT distinct r.* 
				   FROM #Result  r
				   	where ISNULL(r.biQty,0) !=0 and ISNULL(r.biPrice,0)!=0
				   ORDER BY 
						  r. BillDate ,   
						  r.BNumber ,
				          r.BillID, 
						  r.BINumber		   

--#END#