Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ALTER Procedure [dbo].[AccountDetailsgetByBillNo]
- @fromBillNo nvarchar(max),
- @toBillNo nvarchar(max) ,
- @userId int = null
- AS
- declare @patientLedger decimal(18,5)
- declare @frombillId int
- declare @toBillID int
- set @frombillId= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@fromBillNo),0))
- set @toBillID= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@toBillNo),0))
- set @patientLedger = (select
- sum(BillDiscount) + sum(DiscountAmt) + sum(ReceiveAmt) - sum(TaxAmt) - sum(total + DiscountAmt -taxAmt)
- from tblbillingmaster WHERE AcledgerID=22 and billingMasterID between @frombillId and @toBillID
- and UserID = case when @userId is null or @userId = 0 then UserID else @userId end )
- ; with tempCash as
- (select accountGroupId from tbl_AccountGroup where accountGroupId = 27
- union
- select accountGroupId from tbl_AccountGroup where groupUnder = 27)
- , tempBank as (select accountGroupId from tbl_AccountGroup where accountGroupId = 28
- union
- select accountGroupId from tbl_AccountGroup where groupUnder = 28)
- select 'sales' as heading,16 as ledgerid,
- --sum(total + DiscountAmt) as amount,
- sum(total + DiscountAmt -taxAmt) as amount,
- 'Cr' as DrCR
- from tblbillingmaster Where billingMasterID between @frombillId and @toBillID
- and UserID = case when @userId is null or @userId = 0 then UserID else @userId end
- Union ALL
- select 'cash' as heading,1 as ledgerid, isnull( sum(receiveAmt) ,0) as amount, 'Dr' as DrCR
- from tblbillingmaster bm
- inner join tempCash c on c.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId = ReceiveLedgerID)
- Where billingMasterID between @frombillId and @toBillID
- and UserID = case when @userId is null or @userId = 0 then UserID else @userId end
- Union ALL
- select 'Discount' as heading, 8 as ledgerid,
- sum(discountAmt+BillDiscount )
- as amount, 'Dr' as DrCR
- from tblbillingmaster Where billingMasterID between @frombillId and @toBillID
- and UserID = case when @userId is null or @userId = 0 then UserID else @userId end
- Union ALL
- select 'tax' as heading, 36 as ledgerid, sum(taxAmt) as tax, 'Cr' as DrCR from tblbillingmaster Where billingMasterID between @frombillId and @toBillID
- and UserID = case when @userId is null or @userId = 0 then UserID else @userId end
- Union ALL
- select 'patientLedger' as heading,22 as ledgerid, ABS(@patientLedger) as amount, case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end as DrCR
- Union ALL
- select ac.ledgerName as heading,
- (select AccRefId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) as ledgerid,
- --(sum(total + case when billdispercent = 100 or BillingType = 'Credit' then 0 else
- --discountAmt+BillDiscount
- --end-taxAmt)+sum(taxAmt))-(sum(receiveAmt)+sum(taxAmt))
- --(sum(total) - sum(receiveAmt))
- sum(receiveAmt)
- as amount,
- --case when (select accountGroupId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) = 28 then 'Dr'
- --else case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end
- --end
- 'Dr'
- as DrCR
- --case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end as DrCR
- from tblbillingmaster tm
- inner join tbl_accountledger ac on tm.ReceiveLedgerID = ac.ledgerid
- inner join tempBank bank on bank.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId = ReceiveLedgerID)
- WHERE tm.billingMasterID between @frombillId and @toBillID
- and tm.UserID = case when @userId is null or @userId = 0 then tm.UserID else @userId end
- group by tm.ReceiveLedgerID, ac.ledgerName
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement