MUMBAI\n SELECT distinct
PV.Visitdate as  VisitDate
  ,cast (PV.Visitdate as Time) VisitTime
  , oo.name OrgName
  ,OO.OrganizationID OrgID
  ,OAD.Location
  ,CM.ClientCode
  ,CM.ClientName
  , FB.IsCreditBill
  ---- , CM.Iscash
  ,PV.VisitNumber
  ,PV.VisitPurposeID
   ,FB.FinalBillID
    ,PV.ThirdPartyReg
    ,'**' AS PatientName
    , L.LoginName CancelledBy
    ,pv.ReferingPhysicianName
    , BD.FeeID
    , tests.Tcode
	, BD.FeeType
    , BD.FeeDescription
    ,I.InvoiceNumber
, I.ClientID InvoicedClientID
, CM.ClientID
, I.OrgID InvoicedOrg
 ,MV.Description BusinessType

---, PI.Status
 ---, Pi.ApprovedAt 
   , ISNULL(BD.ActualAmount , 0) GrossValue
   , ISNULL(BD.DiscountAmount , 0) PatientDiscountAmount
 -- , SUM (BD.DiscountAmount)  DiscountAmount
 , ISNULL(BD.ActualAmount , 0) - (ISNULL(BD.Amount,0)) B2BDiscount
  , ISNULL(BD.Amount , 0) - (ISNULL(BD.DiscountAmount,0) + ISNULL(BD.RedeemAmount,0) ) Netvalue
    , bd.TaxAmount 
  ---, PV.PatientVisitID
 

  
  ----, L.LoginName CancelledBy
  , sum (ISNULL(BD.Amount , 0) - (ISNULL(BD.DiscountAmount,0) + ISNULL(BD.RedeemAmount,0) )) * -1 CancellationAmount
  , ARD.CreatedAt as  BillCancellationDate

----into daya_vid_cancellation_aug20toaug21
  FROM 
  [LIMS_LIVE].[dbo].AmountRefundDetails ARD (NOLOCK)
INNER JOIN  [LIMS_LIVE].[dbo]. BillingDetails BD (NOLOCK) ON ARD.OrgID = BD.OrgID AND ARD.BillingDetailsID = BD.BillingDetailsID
  INNER JOIN [LIMS_LIVE].[dbo].FINALBILL FB  (NOLOCK) ON BD.OrgID = FB.OrgID
  AND BD.FinalBillID = FB.FinalBillID
  INNER JOIN [LIMS_LIVE].[dbo].[VisitClientMapping] VCM (NOLOCK) ON
        VCM.[FinalBillID] = FB.FinalBillID
		AND VCM.VisitID = FB.VisitID
        AND VCM.[OrgID] = FB.OrgID
  INNER JOIN [LIMS_LIVE].[dbo].[PatientVisit] PV (NOLOCK) ON
        FB.[VisitID] = PV.PatientVisitID
        AND FB.[OrgID] = PV.OrganizationID
  INNER JOIN [LIMS_LIVE].[dbo].[Patient] P (NOLOCK) ON
        P.[PatientID] = PV.PatientID
        AND P.[OrganizationID] = PV.OrganizationID
  INNER JOIN [LIMS_LIVE].[dbo].ClientMaster cm (NOLOCK) on VCM.[OrgID] = cm.OrgID  AND VCM.[ClientID] = cm.ClientID
  inner  join [LIMS_LIVE].[dbo].OrganizationAddress OAD With(nolock)
     ON BD.OrgID = OAD.OrganizationID AND FB.OrgAddressID = OAD.AddressID
     Inner JOin [LIMS_LIVE].[dbo].Organization oo with (nolock) on oo.OrganizationID = OAD.OrganizationID
Left Join [LIMS_LIVE].[dbo].[Login] L with (nolock) on L.loginID = ARD.CreatedBy
--Inner Join [192.168.5.6].[mydbr].[dbo].[RNI_VID_DAYANAND] D with (nolock) on
--D.Vid = PV.VisitNumber
left JOIN [LIMS_LIVE].[dbo].[View_Tests671] Tests
  ON Tests.ACRPID = BD.[FeeId]
     AND Tests.AType = BD.[FeeType]
     AND Tests.OrgID = BD.OrgID
--     LEFT JOIN patientinvestigation_View PI (nolock)
-- ON    PV.[PatientVisitID] = PI.PatientVisitID
-- And  PV.OrganizationID = PI.OrgID 
  left join [LIMS_LIVE].[dbo]. MetaValue_Common MV with (nolock) on MV.OrgID = CM.OrgID
and MV.MetaValueID = Cm.CustomerType

left join [LIMS_LIVE].[dbo].InvoiceDetails ID with (nolock)
	on ID.finalbillid = FB.FinalBillID
	and ID.ActualClientID = cm.ClientID
	Left JOin [LIMS_LIVE].[dbo].Invoice I with (NOlock)
	on I.InvoiceID = ID.InvoiceID


  WHERE 
PV.VisitPurposeID <> 10
  and ClientCode <> 'DCTG'
  AND ClientCode NOT LIKE '%DUMM%'
  AND P.NAME NOT LIKE '%DUMM%'
  AND ISNULL(VCM.[IsActive] , 'Y') = 'Y'
  AND ISNULL(FB.[RefFinalBillID] , 0) = 0
 and   ARD.BillStatus = 'CANCELLED'
AND  CAST(ARD.CreatedAT as Date)  >= cast (getdate () -1 as date)
AND  CAST(ARD.CreatedAT as Date)  <= cast (getdate () -1 as date)

/* and OO.OrganizationID in (280) */


Group by
PV.Visitdate 
  ,cast (PV.Visitdate as Time) 
   , oo.name 
  ,OO.OrganizationID 
  ,OAD.Location
  ,CM.ClientCode
  ,CM.ClientName
  , FB.IsCreditBill
  ---- , CM.Iscash
  ,PV.VisitNumber
  ,PV.VisitPurposeID
   ,FB.FinalBillID
    ,PV.ThirdPartyReg
    ,P.Name 
    , L.LoginName 
    ,pv.ReferingPhysicianName
    , BD.FeeID
    , tests.Tcode
	, BD.FeeType
    , BD.FeeDescription
    ,I.InvoiceNumber
, I.ClientID 
, CM.ClientID
, I.OrgID 
 ,MV.Description ---BusinessType

---, PI.Status
-- , Pi.ApprovedAt 
   , ARD.CreatedAt
    , ISNULL(BD.ActualAmount , 0)
   , ISNULL(BD.DiscountAmount , 0)
 -- , SUM (BD.DiscountAmount)  DiscountAmount
 , ISNULL(BD.ActualAmount , 0) - (ISNULL(BD.Amount,0))
  , ISNULL(BD.Amount , 0) - (ISNULL(BD.DiscountAmount,0) + ISNULL(BD.RedeemAmount,0) )
     , bd.TaxAmount
  ---, PV.PatientVisitID
 

  
  ----, L.LoginName CancelledBy
  ----, CEILING(sum (ISNULL(BD.Amount , 0) - (ISNULL(BD.DiscountAmount,0) + ISNULL(BD.RedeemAmount,0) ))) * -1