Hi,
I am trying to execute the ETL process which includes SSIS packages.
While exeucting SSIS package below is the error i am getting.
Source: "Microsoft SQL Server Native Client 11.0"
Hresult: 0x80004005
Description: "The metadata could not be determined because statement 'INSERT INTO #MFOrderStatus SELECT OrderNo,OrderStatusDescription FROM vw_MFOrderStatus WHERE AssetCl' in procedure 'usp_Get_ST_MF_Order' uses a temp table.
Below is my Sp which is getting executed.
Create Procedure [ST].[usp_Get_ST_MF_Order]
@Entity_Id Int
,@From_Date DateTime
,@Till_Date DateTime
With Recompile
As
BEGIN
Set FMTOnly Off;
Set NoCount On;
Set Transaction Isolation Level Read Uncommitted;
Declare @BaseCurrency Int, @MultiCurrency Int, @BaseCurrencyCode nchar(3)
Select @BaseCurrency = basecurrency, @MultiCurrency = multiplecurrency From hdr_user
Select @BaseCurrencyCode = isocode From hdr_Currency Where CurrencyCode = @BaseCurrency
Declare @Last_Sync_VersionD BigInt, @Last_Sync_VersionH BigInt;
Select @Last_Sync_VersionD = ST.ST_FS_Get_Last_Sync_Version(@From_Date, N'DTL_MFOrder');
Select @Last_Sync_VersionH = ST.ST_FS_Get_Last_Sync_Version(@From_Date, N'Hdr_MFOrder');
IF (OBJECT_ID('tempdb..#MFOrderStatus') IS NOT NULL)
DROP TABLe #MFOrderStatus
CREATE TABLE #MFOrderStatus
(
OrderNo BIGINT NOT NULL,
OrderStatusDescription VARCHAR(100)
)
CREATE CLUSTERED INDEX CIDX_#MFOrderStatus_OrderNo ON #MFOrderStatus( OrderNo )
INSERT INTO #MFOrderStatus SELECT OrderNo,OrderStatusDescription FROM vw_MFOrderStatus WHERE AssetClass='MF'
If @MultiCurrency = 1
Begin
If @From_Date Is Null And @Till_Date Is Null
Begin
Select 0 [Operation]
, Cast('All' As NVarchar(50)) [Operation_Type]
, Cast(D.[OrderNo] As [int])[Order_No]
, Cast(D.[Tranno] As [int])[Tran_No]
, Cast(D.[Clientcode] As [bigint])[Account_Code]
, Cast(D.[SchemeCode] As [int])[Scheme_Code]
, Cast(D.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When D.[TranType] = 'B' Then 'Buy'
When D.[TranType] = 'S' Then 'Redemption'
When D.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, Cast(TRD.[isocode] As [Nvarchar](3)) [Trade_Currency]
, Cast(REP.[isocode] As [Nvarchar](3)) [Reporting_Currency]
, Cast(H.[Amount] /Repo.exchrate As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] /Base.exchrate As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case H.sipstpswp when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(H.fk_systematiccode As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, Cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From dbo.Dtl_MFOrder D (NoLock)
Inner Join HDR_MFOrder H (NoLock) On H.OrderNo = D.OrderNo
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = D.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = D.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = D.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = D.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=D.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
INNER JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
union all
Select 0 [Operation]
, Cast('All' As NVarchar(50)) [Operation_Type]
, Cast(H.[OrderNo] As [int])[Order_No]
, Cast(0 As [int])[Tran_No]
, Cast(H.[Clientcode] As [bigint])[Account_Code]
, Cast(H.[SchemeCode] As [int])[Scheme_Code]
, Cast(H.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When H.[TranType] = 'B' Then 'Buy'
When H.[TranType] = 'S' Then 'Redemption'
When H.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, Cast(TRD.[isocode] As [Nvarchar](3)) [Trade_Currency]
, Cast(REP.[isocode] As [Nvarchar](3)) [Reporting_Currency]
, Cast(H.[Amount] /Repo.exchrate As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] /Base.exchrate As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case isnull(H.sipstpswp,'') when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(isnull(H.fk_systematiccode,0) As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, Cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From HDR_MFOrder H (NoLock)
Left join dbo.Dtl_MFOrder D On H.OrderNo = D.OrderNo
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = H.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = H.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = H.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = H.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=H.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
Left JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
where D.OrderNo is null
End
Else
Begin
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(0 As [int])[Order_No]
, Cast(L.[Tranno] As [int])[Tran_No]
, Null [Account_Code]
, Null [Scheme_Code]
, Null [Order_Date]
, Null [Value_Date]
, Null [Solicited]
, Null [Order_Value]
, Null [Payment_Type]
, Null [Mode_Type]
, Null [Order_Status]
, Null [Order_Read]
, Null [Mode_Of_Transaction_Request]
, Null [Order_Taken_By]
, Null [Transaction_Type]
, Null [Risk_Warning_Delivered]
, Null [POA_Order]
, Null [Nature_Trans]
, Null [OMT_Required]
, Null [OMT_Approved]
, Null [Saleable_OMT_Remarks]
, Null [Rejection_Cancellation_Remarks]
, Null [Approved_Qty]
, Null [Application_Form_Sent_To_Operations]
, Null [Payment_Mode]
, NULL [RM_Code]
, NULL [Client_Prospect_Code]
, Null [Trade_Currency]
, Null [Reporting_Currency]
, Null [Reporting_Order_Value]
, Null [Base_Currency]
, Null [Base_Order_Value]
, Null Bank_Name
, Null Bank_Branch
, Null Cheque_No
, Null Particulars
, Null Cheque_Date
, Null Sip_Stp_Swp
, Null FK_Systematic_Code
, Null [AMC_Confirm_On]
, Null [Bank_Code]
, Null [Remarks]
, Null [Contribution_Type]
, Null [Maturity_Purchase_ID]
, Null [Maturity_Redemption_ID]
, Null [Order_Maturity_Period]
, Null [Order_Maturity_Date]
, Null [Maturity_Date]
, Null [Dividend_Rate]
, Null [Expected_Rate]
, NULL Transaction_Currency_Code
, NULL Transaction_Currency_Amount
, Null [Entry_Load_Amt]
, Null [Exit_Load_Amt]
, Null [Reporting_Entry_Load_Amt]
, Null [Reporting_Exit_Load_Amt]
, Null [Base_Entry_Load_Amt]
, Null [Base_Exit_Load_Amt]
, Null [Auth_Remarks]
, Null [Sett_Date_Remarks]
, Null [Switch_Scheme]
, Null [MF_Scheme_Code]
, Null [Switch_Flag]
, Null [NAV]
, Null [Reporting_NAV]
, Null [Base_NAV]
, Null [Invest_Account]
, NULL Refund_Amount
, NULL Accrued_Dividend_Amount
, NULL Penalty_Amount
, NULL Unit_Encashment
, NULL Settlement_Bank_Name
, NULL Settlement_Bank_Branch
, NULL Settlement_Bank_Account_No
, NULL Settlement_Bank_Account_Type
, NULL Batch_No
, NULL Bank_Map_Code
, NULL as BeneficiaryBankCode
, NULL as BeneficiaryBankAcctNo
, NULL AS BeneficiaryName
From ChangeTable(Changes [dbo].[Dtl_MFOrder], @Last_Sync_VersionD) L
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionD) >= @Last_Sync_VersionD
And L.[Sys_Change_Operation] = 'D'
Union All
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(L.[OrderNo] As [int])[Order_No]
, Cast(0 As [int])[Tran_No]
, Null [Account_Code]
, Null [Scheme_Code]
, Null [Order_Date]
, Null [Value_Date]
, Null [Solicited]
, Null [Order_Value]
, Null [Payment_Type]
, Null [Mode_Type]
, Null [Order_Status]
, Null [Order_Read]
, Null [Mode_Of_Transaction_Request]
, Null [Order_Taken_By]
, Null [Transaction_Type]
, Null [Risk_Warning_Delivered]
, Null [POA_Order]
, Null [Nature_Trans]
, Null [OMT_Required]
, Null [OMT_Approved]
, Null [Saleable_OMT_Remarks]
, Null [Rejection_Cancellation_Remarks]
, Null [Approved_Qty]
, Null [Application_Form_Sent_To_Operations]
, Null [Payment_Mode]
, NULL [RM_Code]
, NULL [Client_Prospect_Code]
, Null [Trade_Currency]
, Null [Reporting_Currency]
, Null [Reporting_Order_Value]
, Null [Base_Currency]
, Null [Base_Order_Value]
, Null Bank_Name
, Null Bank_Branch
, Null Cheque_No
, Null Particulars
, Null Cheque_Date
, Null Sip_Stp_Swp
, Null FK_Systematic_Code
, Null [AMC_Confirm_On]
, Null [Bank_Code]
, Null [Remarks]
, Null [Contribution_Type]
, Null [Maturity_Purchase_ID]
, Null [Maturity_Redemption_ID]
, Null [Order_Maturity_Period]
, Null [Order_Maturity_Date]
, Null [Maturity_Date]
, Null [Dividend_Rate]
, Null [Expected_Rate]
, NULL Transaction_Currency_Code
, NULL Transaction_Currency_Amount
, Null [Entry_Load_Amt]
, Null [Exit_Load_Amt]
, Null [Reporting_Entry_Load_Amt]
, Null [Reporting_Exit_Load_Amt]
, Null [Base_Entry_Load_Amt]
, Null [Base_Exit_Load_Amt]
, Null [Auth_Remarks]
, Null [Sett_Date_Remarks]
, Null [Switch_Scheme]
, Null [MF_Scheme_Code]
, Null [Switch_Flag]
, Null [NAV]
, Null [Reporting_NAV]
, Null [Base_NAV]
, Null [Invest_Account]
, NULL Refund_Amount
, NULL Accrued_Dividend_Amount
, NULL Penalty_Amount
, NULL Unit_Encashment
, NULL Settlement_Bank_Name
, NULL Settlement_Bank_Branch
, NULL Settlement_Bank_Account_No
, NULL Settlement_Bank_Account_Type
, NULL Batch_No
, Null Bank_Map_Code
, NULL as BeneficiaryBankCode
, NULL as BeneficiaryBankAcctNo
, NULL AS BeneficiaryName
From ChangeTable(Changes [dbo].[Hdr_MFOrder], @Last_Sync_VersionH) L
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionH) >= @Last_Sync_VersionH
And L.[Sys_Change_Operation] = 'D'
Union All
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(D.[OrderNo] As [int])[Order_No]
, Cast(L.[Tranno] As [int])[Tran_No]
, Cast(D.[Clientcode] As [bigint])[Account_Code]
, Cast(D.[SchemeCode] As [int])[Scheme_Code]
, Cast(D.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When D.[TranType] = 'B' Then 'Buy'
When D.[TranType] = 'S' Then 'Redemption'
When D.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, Cast(TRD.[isocode] As [Nvarchar](3)) [Trade_Currency]
, Cast(REP.[isocode] As [Nvarchar](3)) [Reporting_Currency]
, Cast(H.[Amount] /Repo.exchrate As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] /Base.exchrate As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case H.sipstpswp when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(H.fk_systematiccode As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From ChangeTable(Changes [dbo].[DTL_MFOrder], @Last_Sync_VersionD) L
Inner Join [dbo].[DTL_MFOrder] d (NoLock) On L.[Tranno] = d.[Tranno]
Inner Join HDR_MFOrder H (NoLock) On H.OrderNo = D.[OrderNo]
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = D.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = D.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = D.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = D.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=D.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
Left JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionD) >= @Last_Sync_VersionD
And L.[Sys_Change_Operation] <> 'D'
UNION ALL
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(L.[OrderNo] As [int])[Order_No]
, Cast(0 As [int])[Tran_No]
, Cast(H.[Clientcode] As [bigint])[Account_Code]
, Cast(H.[SchemeCode] As [int])[Scheme_Code]
, Cast(H.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When H.[TranType] = 'B' Then 'Buy'
When H.[TranType] = 'S' Then 'Redemption'
When H.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, Cast(TRD.[isocode] As [Nvarchar](3)) [Trade_Currency]
, Cast(REP.[isocode] As [Nvarchar](3)) [Reporting_Currency]
, Cast(H.[Amount] /Repo.exchrate As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] /Base.exchrate As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case H.sipstpswp when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(H.fk_systematiccode As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From ChangeTable(Changes [dbo].[Hdr_MFOrder], @Last_Sync_VersionH) L
Inner Join [dbo].[Hdr_MFOrder] H (NoLock) On L.[OrderNo] = H.[OrderNo]
left Join Dtl_MFOrder D (NoLock) On H.[OrderNo] = D.OrderNo
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = H.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = H.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = H.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = H.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=H.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
Left JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionH) >= @Last_Sync_VersionH
And L.[Sys_Change_Operation] <> 'D'
and D.OrderNo is null
End
End
Else
Begin
If @From_Date Is Null And @Till_Date Is Null
Begin
Select 0 [Operation]
, Cast('All' As NVarchar(50)) [Operation_Type]
, Cast(D.[OrderNo] As [int])[Order_No]
, Cast(D.[Tranno] As [int])[Tran_No]
, Cast(D.[Clientcode] As [bigint])[Account_Code]
, Cast(D.[SchemeCode] As [int])[Scheme_Code]
, Cast(D.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When D.[TranType] = 'B' Then 'Buy'
When D.[TranType] = 'S' Then 'Redemption'
When D.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, @BaseCurrencyCode [Trade_Currency]
, @BaseCurrencyCode [Reporting_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case H.sipstpswp when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(H.fk_systematiccode As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From dbo.Dtl_MFOrder D (NoLock)
Inner Join HDR_MFOrder H (NoLock) On H.OrderNo = D.OrderNo
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = D.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = D.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = D.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = D.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=D.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
Left JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
UNION ALL
Select 0 [Operation]
, Cast('All' As NVarchar(50)) [Operation_Type]
, Cast(H.[OrderNo] As [int])[Order_No]
, Cast(0 As [int])[Tran_No]
, Cast(H.[Clientcode] As [bigint])[Account_Code]
, Cast(H.[SchemeCode] As [int])[Scheme_Code]
, Cast(H.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When H.[TranType] = 'B' Then 'Buy'
When H.[TranType] = 'S' Then 'Redemption'
When H.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, @BaseCurrencyCode [Trade_Currency]
, @BaseCurrencyCode [Reporting_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case H.sipstpswp when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(H.fk_systematiccode As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From HDR_MFOrder H(NoLock)
Left Join dbo.Dtl_MFOrder D (NoLock) On H.OrderNo = D.OrderNo
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = H.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = H.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = H.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = H.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=H.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
Left JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
where D.OrderNo is null
End
Else
Begin
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(0 As [int])[Order_No]
, Cast(L.[Tranno] As [int])[Tran_No]
, Null [Account_Code]
, Null [Scheme_Code]
, Null [Order_Date]
, Null [Value_Date]
, Null [Solicited]
, Null [Order_Value]
, Null [Payment_Type]
, Null [Mode_Type]
, Null [Order_Status]
, Null [Order_Read]
, Null [Mode_Of_Transaction_Request]
, Null [Order_Taken_By]
, Null [Transaction_Type]
, Null [Risk_Warning_Delivered]
, Null [POA_Order]
, Null [Nature_Trans]
, Null [OMT_Required]
, Null [OMT_Approved]
, Null [Saleable_OMT_Remarks]
, Null [Rejection_Cancellation_Remarks]
, Null [Approved_Qty]
, Null [Application_Form_Sent_To_Operations]
, Null [Payment_Mode]
, NULL [RM_Code]
, NULL [Client_Prospect_Code]
, Null [Trade_Currency]
, Null [Reporting_Currency]
, Null [Reporting_Order_Value]
, Null [Base_Currency]
, Null [Base_Order_Value]
, Null Bank_Name
, Null Bank_Branch
, Null Cheque_No
, Null Particulars
, Null Cheque_Date
, Null Sip_Stp_Swp
, Null FK_Systematic_Code
, Null [AMC_Confirm_On]
, Null [Bank_Code]
, Null [Remarks]
, Null [Contribution_Type]
, Null [Maturity_Purchase_ID]
, Null [Maturity_Redemption_ID]
, Null [Order_Maturity_Period]
, Null [Order_Maturity_Date]
, Null [Maturity_Date]
, Null [Dividend_Rate]
, Null [Expected_Rate]
, NULL Transaction_Currency_Code
, NULL Transaction_Currency_Amount
, Null [Entry_Load_Amt]
, Null [Exit_Load_Amt]
, Null [Reporting_Entry_Load_Amt]
, Null [Reporting_Exit_Load_Amt]
, Null [Base_Entry_Load_Amt]
, Null [Base_Exit_Load_Amt]
, Null [Auth_Remarks]
, Null [Sett_Date_Remarks]
, Null [Switch_Scheme]
, Null [MF_Scheme_Code]
, Null [Switch_Flag]
, Null [NAV]
, Null [Reporting_NAV]
, Null [Base_NAV]
, Null [Invest_Account]
, NULL Refund_Amount
, NULL Accrued_Dividend_Amount
, NULL Penalty_Amount
, NULL Unit_Encashment
, NULL Settlement_Bank_Name
, NULL Settlement_Bank_Branch
, NULL Settlement_Bank_Account_No
, NULL Settlement_Bank_Account_Type
, NULL Batch_No
, NULL Bank_Map_Code
, NULL as BeneficiaryBankCode
, NULL as BeneficiaryBankAcctNo
, NULL AS BeneficiaryName
From ChangeTable(Changes [dbo].[Dtl_MFOrder], @Last_Sync_VersionD) L
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionD) >= @Last_Sync_VersionD
And L.[Sys_Change_Operation] = 'D'
Union All
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(L.[OrderNo] As [int])[Order_No]
, Cast(0 As [int])[Tran_No]
, Null [Account_Code]
, Null [Scheme_Code]
, Null [Order_Date]
, Null [Value_Date]
, Null [Solicited]
, Null [Order_Value]
, Null [Payment_Type]
, Null [Mode_Type]
, Null [Order_Status]
, Null [Order_Read]
, Null [Mode_Of_Transaction_Request]
, Null [Order_Taken_By]
, Null [Transaction_Type]
, Null [Risk_Warning_Delivered]
, Null [POA_Order]
, Null [Nature_Trans]
, Null [OMT_Required]
, Null [OMT_Approved]
, Null [Saleable_OMT_Remarks]
, Null [Rejection_Cancellation_Remarks]
, Null [Approved_Qty]
, Null [Application_Form_Sent_To_Operations]
, Null [Payment_Mode]
, NULL [RM_Code]
, NULL [Client_Prospect_Code]
, Null [Trade_Currency]
, Null [Reporting_Currency]
, Null [Reporting_Order_Value]
, Null [Base_Currency]
, Null [Base_Order_Value]
, Null Bank_Name
, Null Bank_Branch
, Null Cheque_No
, Null Particulars
, Null Cheque_Date
, Null Sip_Stp_Swp
, Null FK_Systematic_Code
, Null [AMC_Confirm_On]
, Null [Bank_Code]
, Null [Remarks]
, Null [Contribution_Type]
, Null [Maturity_Purchase_ID]
, Null [Maturity_Redemption_ID]
, Null [Order_Maturity_Period]
, Null [Order_Maturity_Date]
, Null [Maturity_Date]
, Null [Dividend_Rate]
, Null [Expected_Rate]
, NULL Transaction_Currency_Code
, NULL Transaction_Currency_Amount
, Null [Entry_Load_Amt]
, Null [Exit_Load_Amt]
, Null [Reporting_Entry_Load_Amt]
, Null [Reporting_Exit_Load_Amt]
, Null [Base_Entry_Load_Amt]
, Null [Base_Exit_Load_Amt]
, Null [Auth_Remarks]
, Null [Sett_Date_Remarks]
, Null [Switch_Scheme]
, Null [MF_Scheme_Code]
, Null [Switch_Flag]
, Null [NAV]
, Null [Reporting_NAV]
, Null [Base_NAV]
, Null [Invest_Account]
, NULL Refund_Amount
, NULL Accrued_Dividend_Amount
, NULL Penalty_Amount
, NULL Unit_Encashment
, NULL Settlement_Bank_Name
, NULL Settlement_Bank_Branch
, NULL Settlement_Bank_Account_No
, NULL Settlement_Bank_Account_Type
, NULL Batch_No
, NULL Bank_Map_Code
, NULL as BeneficiaryBankCode
, NULL as BeneficiaryBankAcctNo
, NULL AS BeneficiaryName
From ChangeTable(Changes [dbo].[Hdr_MFOrder], @Last_Sync_VersionH) L
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionH) >= @Last_Sync_VersionH
And L.[Sys_Change_Operation] = 'D'
Union All
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(D.[OrderNo] As [int])[Order_No]
, Cast(L.[Tranno] As [int])[Tran_No]
, Cast(D.[Clientcode] As [bigint])[Account_Code]
, Cast(D.[SchemeCode] As [int])[Scheme_Code]
, Cast(D.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When D.[TranType] = 'B' Then 'Buy'
When D.[TranType] = 'S' Then 'Redemption'
When D.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, @BaseCurrencyCode [Trade_Currency]
, @BaseCurrencyCode [Reporting_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case H.sipstpswp when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(H.fk_systematiccode As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, Cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From ChangeTable(Changes [dbo].[DTL_MFOrder], @Last_Sync_VersionD) L
Inner Join [dbo].[DTL_MFOrder] d (NoLock) On L.[Tranno] = d.[Tranno]
Inner Join HDR_MFOrder H (NoLock) On H.OrderNo = D.[OrderNo]
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = D.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = D.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = D.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = D.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=D.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
Left JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionD) >= @Last_Sync_VersionD
And L.[Sys_Change_Operation] <> 'D'
UNION ALL
Select Case L.[Sys_Change_Operation] When N'D' Then 1 When N'I' Then 2 When N'U' Then 4 End [Operation]
, Cast(Case L.[Sys_Change_Operation]
When N'D' Then 'Delete' When N'I' Then 'Insert' When N'U' Then 'Update'
End As NVarchar(50)) [Operation_Type]
, Cast(L.[OrderNo] As [int])[Order_No]
, Cast(0 As [int])[Tran_No]
, Cast(H.[Clientcode] As [bigint])[Account_Code]
, Cast(H.[SchemeCode] As [int])[Scheme_Code]
, Cast(H.[OrderDate] As [datetime])[Order_Date]
, Cast(H.[valuedate] As [date])[Value_Date]
, Cast(Case H.[Solicited]
When 'Y' Then 1
When 'N' Then 0 End As [bit])[Solicited]
, Cast(H.[Amount] As [numeric](18,2))[Order_Value]
, CAST( COALESCE( PM.[Description],'' ) AS [NVARCHAR](200))[Payment_Type]
, Cast('' As [nvarchar](50))[Mode_Type]
, Cast(MFOrderStatus.OrderStatusDescription As [nvarchar](100)) [Order_Status]
, Cast(H.[OrderRead] As [nvarchar](50))[Order_Read]
, Cast('' As [nvarchar](50))[Mode_Of_Transaction_Request]
, Cast(H.[OrderTakenBY] As [nvarchar](10))[Order_Taken_By]
, Cast(Case When H.[TranType] = 'B' Then 'Buy'
When H.[TranType] = 'S' Then 'Redemption'
When H.[TranType] = 'B' And D.[Switch] = 1 Then 'Switch' End As [nvarchar](20))[Transaction_Type]
, Cast(H.[RiskDelWarn] As [tinyint])[Risk_Warning_Delivered]
, Cast(Case H.[POAOrder]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[POA_Order]
, Cast('' As [nvarchar](50))[Nature_Trans]
, Cast(Case H.[OMTRequired]
When 0 Then 'No'
When 1 Then 'Yes' End As [nvarchar](10))[OMT_Required]
, Cast(Case H.[OMTAccepted]
When 'Y' Then 'Yes'
When 'N' Then 'No' End As [nvarchar](10))[OMT_Approved]
, Cast(H.[saleableOMTRemarks] As [nvarchar](50))[Saleable_OMT_Remarks]
, Cast(Case H.[Status] When 'R' Then H.RejectReason When 'C' Then H.CancelReason End As [nvarchar](50))[Rejection_Cancellation_Remarks]
, Cast(D.[deliv_qty] As [numeric](18,4))[Approved_Qty]
, Cast('' As [nvarchar](10))[Application_Form_Sent_To_Operations]
, Cast('' As [nvarchar](10))[Payment_Mode]
, Cast(FN.[RM_Code] As [int])[RM_Code]
, Cast(Cast(CLNT.Head_ClientCode As [Nvarchar](30)) + 'C' As Nvarchar(50)) [Client_Prospect_Code]
, @BaseCurrencyCode [Trade_Currency]
, @BaseCurrencyCode [Reporting_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Reporting_Order_Value]
, @BaseCurrencyCode [Base_Currency]
, Cast(H.[Amount] As [numeric](18,2))[Base_Order_Value]
, Cast(H.ApplyBankName As Nvarchar(100)) Bank_Name
, Cast(H.ApplyBankBranch As Nvarchar(50)) Bank_Branch
, CAST(COALESCE(H.ChequeNo,H.ApplyChequeNo) AS NVARCHAR(10)) Cheque_No
, Cast(H.ApplyParticulars As Nvarchar(250)) Particulars
, Cast(H.ApplyChequeDate As datetime) Cheque_Date
, Cast((case H.sipstpswp when 'I' then 'SIP'
when 'T' then 'STP'
when 'W' then 'SWP' end )As Nvarchar(10))Sip_Stp_Swp
, Cast(H.fk_systematiccode As int)FK_Systematic_Code
, Cast(H.[AMCConfirmon] As [datetime])[AMC_Confirm_On]
, CAST(H.mfpoolaccount as Int) [Bank_Code]
, Cast(H.[Remarks] As [nvarchar](255))[Remarks]
, Cast(case H.ContributionType
when 0 then 'Normal Order'
when 1 then 'Employee Contribution'
when 2 then 'Employer Contribution' end As Nvarchar(50))[Contribution_Type]
, Cast(CASE ISNULL(TWMR.[TWMRODID],0) When 0 THEN TWMP.[TWMPODID] ELSE TWMR.[TWMPODID] END As [int])[Maturity_Purchase_ID]
, Cast(TWMR.[TWMRODID] As [int])[Maturity_Redemption_ID]
, Cast(TWMP.[OrdMaturityPeriod] As [int])[Order_Maturity_Period]
, Cast(TWMP.[OrdMaturityDate] As [date])[Order_Maturity_Date]
, Cast(TWMP.[MaturityDate] As [date])[Maturity_Date]
, Cast(TWMP.[DivRate] As [numeric](24,6))[Dividend_Rate]
, Cast(TWMP.[ExpectedRate] As [numeric](24,6))[Expected_Rate]
, cast(H.TxnCCYCode As [numeric](18,0)) Transaction_Currency_Code
, cast(H.TxnCCYAmount As [numeric](24,6)) Transaction_Currency_Amount
, Cast(DMA.[EntryLoadAmt] As [numeric](25,8))[Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] As [numeric](25,8))[Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt]/Repo.exchrate As [numeric](25,8))[Reporting_Exit_Load_Amt]
, Cast(DMA.[EntryLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Entry_Load_Amt]
, Cast(DT.[ExitLoadAmt] /Base.exchrate As [numeric](25,8))[Base_Exit_Load_Amt]
, Cast(DMA.[Remarks] As Nvarchar(4000))[Auth_Remarks]
, Cast(DMA.[DateRemarks] As Nvarchar(255))[Sett_Date_Remarks]
, Cast(D.[SwitchScheme] As [int])[Switch_Scheme]
, Cast(H.[MFSchemeCode] As [int])[MF_Scheme_Code]
, Cast(Case When D.[Switch] = 1 Then 'Yes' Else 'No' End As Nvarchar(10))[Switch_Flag]
, Cast(Null As [numeric](25,8))[NAV]
, Cast(Null As [numeric](25,8))[Reporting_NAV]
, Cast(Null As [numeric](25,8))[Base_NAV]
, CAST(H.mfinvestmentaccount as Int) [Invest_Account]
, Cast(H.mforefamount As [numeric](25,8)) Refund_Amount
, Cast(D.AccruedDivAmt As [numeric](25,8)) Accrued_Dividend_Amount
, Cast(D.PenaltyAmt As [numeric](25,8)) Penalty_Amount
, Cast(Case When H.Unitencashment = 1 Then 'Yes' Else 'No' End As Nvarchar(10)) as Unit_Encashment
, Cast(CB.BankName as Nvarchar(255)) Settlement_Bank_Name
, Cast(CB.BankBranch as Nvarchar(50)) Settlement_Bank_Branch
, Cast(CB.BankAccountNo as Nvarchar(50)) Settlement_Bank_Account_No
, Cast(CB.BankAccountType as Nvarchar(20)) Settlement_Bank_Account_Type
, Cast(mfbatch.BatchNo as Nvarchar(100)) Batch_No
, Cast(CB.Mapcode as Nvarchar(50)) Bank_Map_Code
, cast(COALESCE(TRH.[BeneficiaryBankCode] ,INVBK.[BankCode]) as int) as BeneficiaryBankCode
, cast(COALESCE(case when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'O' then [PYCB].BankAccountNo
when COALESCE([TRH].[BeneficiaryBankSrcTbl], 'B') = 'B' then [PYBM].BankAcctNo
end,INVBK.BankAccountNo) as Nvarchar(50)) as BeneficiaryBankAcctNo
, cast(CH.[ClientName] as Nvarchar(255)) AS [BeneficiaryName]
From ChangeTable(Changes [dbo].[Hdr_MFOrder], @Last_Sync_VersionH) L
Inner Join [dbo].[Hdr_MFOrder] H (NoLock) On L.[OrderNo] = H.[OrderNo]
Left Join dbo.DTL_MFOrder D (NoLock) On H.[OrderNo] = D.OrderNo
Left Join HDR_Client CLNT (NoLock) On CLNT.Client_Code = H.[Clientcode]
Left Join tblTrxnWiseMatPurOrdDtl TWMP (NoLock) On TWMP.OrderNo = H.[OrderNo]
Left Join tblTrxnWiseMatRedOrdDtl TWMR (NoLock) On TWMR.OrderNo = H.[OrderNo]
Left Join ST.ufn_Get_RM_Tenure_L4_Wise() As FN On FN.Client_Code = H.[Clientcode]
And H.[valuedate] Between FN.From_Date And FN.To_Date
Left Join dbo.Hdr_currency TRD (NoLock) On CLNT.[hcCurrencyCode] = TRD.[CurrencyCode]
Left Join dbo.Hdr_currency REP (NoLock) On CLNT.[hcreportingcurrency] = REP.[CurrencyCode]
Left Join (Select HMA.OrderNo, HMA.Remarks, HMA.DateRemarks, DTMA.EntryLoadAmt
From Hdr_MutAppl HMA (NoLock)
Inner Join DTL_MutAppl DTMA (NoLock) ON DTMA.ref_no = HMA.ref_no
) DMA ON DMA.OrderNo = H.OrderNo
Left Join (Select HTM.OrderNo, Sum(DTM.ExitLoadAmt) ExitLoadAmt
From hdr_mut_trans HTM (NoLock)
Inner Join Dtl_mut_trans DTM (NoLock) ON DTM.tranno = HTM.tranno And DTM.srno = 1
Group By HTM.OrderNo) DT ON DT.OrderNo = H.OrderNo
Left Join #MFOrderStatus MFOrderStatus ON MFOrderStatus.OrderNo=H.OrderNo
LEFT JOIN tblPaymentMode PM ( NOLOCK ) ON H.PaymentType = PM.PaymentModeId
Left join HDR_CommonBank CB on H.mfpoolaccount=CB.BankCode
LEFT JOIN vw_MFOrderBatchDtl mfbatch on mfbatch.OrderNo=D.OrderNo
Left join tblTrxnRefundDetails [TRDtl] on [TRDtl].[TransactionID] = H.[OrderNo] and
[TRDtl].[TransactionType] = 'RD'
Left JOIN tblTrxnRefundHeader TRH ON [TRDtl].[HeaderId] = [TRH].[HeaderId]
LEFT JOIN [dbo].[HDR_CommonBank] PYCB
ON [PYCB].[BankCode] = [TRH].[BeneficiaryBankCode]
LEFT JOIN [dbo].[HDR_BankMaster] PYBM
ON [PYBM].[BankCode] = [TRH].[BeneficiaryBankCode]
Left JOIN [dbo].[Hdr_ClientHead] CH
ON CLNT.[Head_ClientCode] = [CH].[Client_Code]
LEFT JOIN [dbo].[HDR_CommonBank] INVBK
ON [INVBK].[BankCode] = H.[DebitBankCode]
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], REP.[CurrencyCode], H.[valuedate]) Repo
Cross Apply ST.ST_FT_Get_Currency_Rate(TRD.[CurrencyCode], @BaseCurrency, H.[valuedate]) Base
Where IsNull(L.[Sys_Change_Creation_Version], @Last_Sync_VersionH) >= @Last_Sync_VersionH
And L.[Sys_Change_Operation] <> 'D'
and D.OrderNo is null
End
End
IF (OBJECT_ID('tempdb..#MFOrderStatus') IS NOT NULL)
DROP TABLe #MFOrderStatus
END
I am not getting the meaning of this.
I tried converting the Temp table in CTE but no use as getting the same error.
Help would be appreciated.
Thanks and regards,
Swapnil Kadam
What I have tried:
As i googled for some help. Then found that SSIS package fir executes an internal SP which returns the Meta Data of the SP to Execute.
To avoid the same we should put SET FMTONLY OFF at the start of SP, But in case still there was no change.
Submit an article or tip