Собственный клиент Microsoft SQL server 11.0. Метаданные не могут быть определены, поскольку оператор в процедуре использует временную таблицу.
Привет,
Я пытаюсь выполнить процесс ETL, который включает в себя пакеты SSIS.
В то время как exeucting SSIS package ниже - это ошибка, которую я получаю.
Источник: "собственный клиент Microsoft SQL Server 11.0"
Hresult: 0x80004005
Описание: "метаданные не могут быть определены, поскольку оператор 'INSERT INTO #MFOrderStatus SELECT OrderNo,OrderStatusDescription FROM vw_MFOrderStatus WHERE AssetCl' в процедуре 'usp_Get_ST_MF_Order' использует временную таблицу.
Ниже приведен мой Sp, который выполняется.
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]
Kornfeld Eliyahu Peter
Ты же не думаешь, что мы должны это прочесть? Убери его! Уберите ненужные строки!