i create these sp but when i load it in cryatl report for link at that time it shows only first table object ,but i want to access whole object for invoice link ,,,what should i do
ALTER proc [dbo].[Acc_spVoucher_By_Voucher_Id_Get]
(
@Voucher_ID int = null
)
as
Begin
-- get voucher details
select v.Voucher_No, v.Voucher_Date, v.Effective_Date, v.Reference_No, v.Narration, v.Voucher_Amount,
v.Voucher_Status, v.Approved_or_Rejected_By, v.Voucher_Type_ID, v.Voucher_ID,v.Is_Cancelled,VT.Voucher_Type
,(select * from formatcurrency(v.Voucher_Amount )) as Voucher_Amount1,ISNULL(v.GST_Branch_State_ID,0)GST_Branch_State_ID
from Acc_Voucher v
Inner Join Acc_Voucher_Type VT on V.Voucher_Type_ID=VT.Voucher_Type_ID
where v.Voucher_ID = @Voucher_ID
-- get voucher ledger details
select vl.Voucher_Ledger_Id, vl.Ledger_Id , vl.Ref_Dr_Cr, ABS(vl.Ledger_Amount)Ledger_Amount, vl.Currency, vl.Conversion_Rate, vl.Working_Period_Id,
l.Ledger_Name, l.Maintain_Bill, l.Inventory_Applicable, l.Cost_Center_Applicable
---,LG.Isbank
, cast(Case When LGT.Ledger_Group_Type='Bank' then 1 else 0 end as bit) as Isbank,C.Currency_Id
,abs(round(Ledger_Amount*Conversion_Rate,2,1)) as FinalTotalConvAmt
,(select * from formatcurrency(abs(round(Ledger_Amount*Conversion_Rate,2,1)))) as FinalTotalConvAmt1
,(select * from formatcurrency( ABS(vl.Ledger_Amount)) )as Ledger_Amount1
,vl.Comments,ISNULL(VL.Is_GST_Ledger_Row,0)Is_GST_Ledger_Row,ISNULL(VL.Is_Tds_Ledger_Row,0)Is_Tds_Ledger_Row,
ISNULL(LGT.Ledger_Group_Type_ID,0)Ledger_Group_Type_ID
from Acc_Voucher_Ledger vl
inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID Left join Acc_Ledger_Group LG on L.Ledger_Group_ID=LG.Ledger_Group_ID
Inner Join Acc_Currency C on C.Currency_Code =vl.Currency
left Join Acc_Ledger_Group_Type LGT on LG.Ledger_Group_Type_ID =LGT.Ledger_Group_Type_ID
where vl.Voucher_Id = @Voucher_ID --and Currency_Id <>177
order by SUBSTRING(Ledger_Name, 1, CHARINDEX('Service', Ledger_Name))
-- get voucher ledger billwise details
select vlr.Voucher_Ledger_Ref_Id, vlr.Voucher_Ledger_Id, vlr.Method_of_Adjustment, vlr.particulars,vlr.ParticularsId,
vlr.Credit_Days, vlr.Ref_Dr_Cr, ABS(vlr.Ref_Amount)Ref_Amount, vlr.Working_Period_Id, l.Ledger_Name, l.Ledger_ID,Ref_Date
,(select * from formatcurrency(ABS(vlr.Ref_Amount)))as Ref_Amount1
from Acc_Voucher_Ledger_Ref vlr
inner join Acc_Voucher_Ledger vl on vlr.Voucher_Ledger_Id = vl.Voucher_Ledger_Id
inner join Acc_Voucher v on v.Voucher_ID = vl.Voucher_Id
inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID
where v.Voucher_ID = @Voucher_ID
-- get voucher ledger cost center details
select vlcc.Voucher_Ledger_CC_Id, vlcc.Voucher_Ledger_Id, vlcc.Cost_Center_Id, vlcc.Ref_Dr_Cr,ABS( vlcc.Cost_Center_Amount)Cost_Center_Amount,
vlcc.Working_Period_Id, cc.Cost_Center_Name, ccc.Cost_Center_Category_Name,
cc.Cost_Center_Category_ID, l.Ledger_Name, l.Ledger_ID,
--,ROW_NUMBER()over( order by vlcc.Cost_Center_Id )as MainRowIndex,
cast(DENSE_RANK() OVER (partition by vlcc.Voucher_Ledger_Id order By cc.Cost_Center_Category_ID asc) as int)MainRowIndex
,cast(ROW_NUMBER() over(PARTITION BY vlcc.Voucher_Ledger_Id, cc.Cost_Center_Category_ID order by vlcc.Cost_Center_Id) as int)as
SubRowIndex,(select * from formatcurrency(ABS( vlcc.Cost_Center_Amount)))as Cost_Center_Amount1
from dbo.Acc_Voucher_Ledger_Cost_Center vlcc
inner join Acc_Voucher_Ledger vl on vlcc.Voucher_Ledger_Id = vl.Voucher_Ledger_Id
inner join Acc_Cost_Center cc on vlcc.Cost_Center_Id = cc.Cost_Center_ID
inner join Acc_Cost_Center_Category ccc on cc.Cost_Center_Category_ID = ccc.Cost_Center_Category_ID
inner join Acc_Voucher v on v.Voucher_ID = vl.Voucher_Id
inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID
where v.Voucher_ID = @Voucher_ID order by Voucher_Ledger_Id,cc.Cost_Center_Category_ID,vlcc.Cost_Center_Id
---Get Voucher Instrument Details
select AI.Instrument_Id,AI.Voucher_ID,AI.Ledger_Id ,AI.Instrument_No ,AI.Instrument_Bank_Name
,AI.Instrument_Date,AI.Instrument_Amount ,Ai.Clearence_Date ,AI.Instrument_ModeOfPayment ,AL.Ledger_Name
,(select * from formatcurrency(Instrument_Amount))as Instrument_Amount1
from Acc_Instrument_Info AI
Inner Join Acc_Ledger AL on AI.Ledger_Id =AL.Ledger_ID
Where AI.Voucher_ID =@Voucher_ID
select (select * from formatcurrency(PP.Debit )) as Debit,(select * from formatcurrency(PP.credit)) as credit from(
select sum(abs(P.Debit)) as Debit,Sum(P.Credit) as Credit from
(
select case When VL.Ledger_Amount*Conversion_Rate <0 then round(Vl.Ledger_Amount*Conversion_Rate,2,1) else 0 end as Debit
,case When VL.Ledger_Amount*Conversion_Rate >0 then round(Vl.Ledger_Amount*Conversion_Rate,2,1) else 0 end as Credit
from Acc_Voucher_Ledger vl
inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID Left join Acc_Ledger_Group LG on L.Ledger_Group_ID=LG.Ledger_Group_ID
Inner Join Acc_Currency C on C.Currency_Code =vl.Currency
left Join Acc_Ledger_Group_Type LGT on LG.Ledger_Group_Type_ID =LGT.Ledger_Group_Type_ID
where vl.Voucher_Id = @Voucher_ID --and Currency_Id <>177
)p
)PP
Select VLI.Stock_Item_Id,VLI.Voucher_Ledger_Id,VLI.Voucher_Ledger_Inventory_Id
,VLI.Quantity,VLI.Rate,VLI.Inventory_Total,SI.Stock_Name + ' - ' + gcc.SAC_HSN_Code as Stock_Name
,L.Ledger_ID,L.Ledger_Name,VLI.Inventory_Total as Final_Inventory_Total,VLI.PCS,VLI.Discount,VLI.UOM,VLI.Comments
from Acc_Voucher_Ledger_Inventory VLI
Inner Join Acc_Stock_Item SI on VLI.Stock_Item_Id=SI.Stock_Item_Id
Inner join Acc_GST_Charge_Category gcc on gcc.GST_Charge_Category_Id=SI.GST_Charge_Category_Id
--Inner Join Acc_Stock_Category C on SI.Stock_Category_Id=C.Stock_Category_Id
Inner Join Acc_Voucher_Ledger VL on VLI.Voucher_Ledger_Id=VL.Voucher_Ledger_Id
Inner Join Acc_Ledger L on L.Ledger_ID=VL.Ledger_Id
Where VL.Voucher_Id=@Voucher_ID
Select Schedule_Name,Scheduled_Date,Email_Id,Notification_To,Occurrence_Count,Occurrence_Type from Acc_Voucher_Scheduler where Voucher_Id=141
End
Faisal PathanPosted Mar 1, 2019, 12:57 AM