Microsoft SQL Server本机客户端11.0。无法确定元数据，因为过程中的语句使用临时表。 [英] Microsoft SQL server native client 11.0. The metadata could not be determined because statement in procedure uses a temp table.
本文介绍了Microsoft SQL Server本机客户端11.0。无法确定元数据，因为过程中的语句使用临时表。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试执行包含SSIS包的ETL过程。
下面执行SSIS包时出错我要来了。
来源：Microsoft SQL Server Native Client 11.0
Hresult：0x80004005
描述：无法确定元数据，因为过程'usp_Get_ST_MF_Order'中的语句'INSERT INTO #MFOrderStatus SELECT OrderNo，OrderStatusDescription FROM vw_MFOrderStatus WHERE AssetCl'使用临时表。
下面是我的Sp正在执行。
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
/*
Declare @Entity_Id Int, @From_Date DateTime = Null, @Till_Date DateTime = Null
, @Schema SysName, @Table SysName
Select @Entity_Id = 1, @From_Date = '1900-01-01' , @Till_Date = GetDate()
--*/
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 -- For Full Transformation
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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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.ApplyChequeNo,H.ChequeNo) AS NVARCHAR(10)) Cheque_No
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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 -- For Incremental Transformation
Begin
--Declare @BeginLsn VarBinary(10), @EndLsn VarBinary(10)
-- Get CDC Start Date, if CDC create_date > @From_Date
/* Select @From_Date = create_date
From cdc.change_tables
Where capture_instance = 'dbo_Dtl_MFOrder' And create_date > @From_Date */
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]
---- Source Data
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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]
---- Source Data
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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]
---- Source Data
, 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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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]
---- Source Data
, 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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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 -- For Full Transformation
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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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 -- For Incremental Transformation
Begin
--Declare @BeginLsn VarBinary(10), @EndLsn VarBinary(10)
-- Get CDC Start Date, if CDC create_date > @From_Date
/* Select @From_Date = create_date
From cdc.change_tables
Where capture_instance = 'dbo_Dtl_MFOrder' And create_date > @From_Date */
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]
---- Source Data
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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]
---- Source Data
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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]
---- Source Data
, 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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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]
---- Source Data
, 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(Case H.[Status]
-- When 'E' Then 'AMC Confirmed'
-- When 'R' Then 'Rejected'
-- When 'A' Then 'Applied'
-- When 'C' Then 'Cancelled'
-- When 'P' Then 'Picked'
-- When 'N' Then 'New' End As [nvarchar](50))[Order_Status]
, 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
--------------------------------------------Added by Supriya Bamane for RUDS client(V.11_2)---------------
, 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.
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.
推荐答案
Instead of creating a temp table, create the table in the database and use the created table:
Instead of creating a temp table, create the table in the database and use the created table:
IF OBJECT_ID('MFOrderStatus_TEMP','U') IS NOT NULL
DROP TABLE TABLE mydatabase.dbo.MFOrderStatus_TEMP;
CREATE TABLE MFOrderStatus_TEMP
(
OrderNo BIGINT NOT NULL,
OrderStatusDescription VARCHAR(100) NULL
);
CREATE CLUSTERED INDEX CIDX_FMOrderStatus_OrderNo ON mydatabase.dbo.FMOrderStatus_TEMP( OrderNo );
INSERT INTO mydatabase.dbo.MFOrderStatus_TEMP SELECT OrderNo,OrderStatusDescription FROM mydatabase.dbo.vw_MFOrderStatus WHERE AssetClass='MF'
--
-- blah blah这篇关于Microsoft SQL Server本机客户端11.0。无法确定元数据，因为过程中的语句使用临时表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
