USE [CentralDataStoreSDL]
GO
/****** Object: StoredProcedure [Daily].[SpUFAFinancialTransactionBRACCLoad] Script Date: 2021/09/29 13:28:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Daily].[SpUFAFinancialTransactionBRACCLoad]
--@SourceSystem VARCHAR(10) = NULL
AS
DECLARE @SourceSystem VARCHAR(10) = 'IN1035'
SELECT distinct
--top 10
Binary_CHECKSUM
(
[FinancialTransaction]
, [OrganisationUnit]
, [Portfolio]
, [TradeStatus]
, [NPLStatus]
, [SourceSystemID]
, [Product]
, [StartDate]
, [EndDate]
, [MaturityDate]
, [PrimaryCurrency]
, [PrimaryAmount]
, [SecondaryCurrency]
, [SecondaryAmount]
, [BusinessPartnerID]
, [LegalEntity]
, [BuySellIndicator]
, [PutCallIndicator]
, [OriginalContractNumber]
, [PartnerLegalEntity]
, [PartnerProfitCentre]
, [InternalFlag]
, [Form50Code]
, [SectorCode]
, [CounterpartyClass]
, [MTMCurrency]
, [MTM]
, [OriginalTradeID]
, [PackageNumber]
, [ExposureLGDDownTurn]
, [ExposureLGDExpected]
, [OptionTimeToExerciseDate]
, [OptionPriceCurrency]
, [UnderlyingTimeToEnd]
, [UnderlyingTimeToStart]
, [ArrearsStatus]
, [MarginCollateralType]
, [InterestSuspenseStatus]
, [FeeSuspenseStatus]
, [AmortisingSuspenseStatus]
, [SettlementDate]
, [AccountingBook]
, [PrimaryPaymentFrequency]
, [SecondaryPaymentFrequency]
, [PrimaryInterestRate]
, [SecondaryInterestRate]
, [PrimaryPreviousPayDate]
, [SecondaryPreviousPayDate]
, [PrimaryNextReset]
, [SecondayNextReset]
, [PrimaryInterestRateType]
, [SecondaryInterestRateType]
, [PrimaryDayCountConvention]
, [SecondaryDayCountConvention]
, [PrimaryNetMargin]
, [SecondaryNetMargin]
, [PrimaryRateIndex]
, [SecondaryRateIndex]
, [PrimaryResetInterval]
, [SecondaryResetInterval]
, [FarPrimaryAmount]
, [FarPrimaryAmountCurrency]
, [FarSecondaryAmount]
, [FarSecondaryAmountCurrency]
, [CalendarForWorkingDayShifts]
, [AgreementTerm]
, [MaturityTerm]
, [DefaultFlag]
, [NetLifeCap]
, [NetLifeFloor]
, [OriginalInterestRate]
, [DormancyIndicator]
, [AmortizationType]
, [RepricingIndicator]
, [IFRS9Classification]
, [CompoundingFrequency]
, [InternalCounterpartyPortfolio]
, [CreatedDate]
, [SecondTradeID]
, [BookingCountry]
, [ReportingDate]
, [Seniority]
, [OptionTimeToExercise]
, [SourceTradeReference]
, [InstrumentID]
, [IssuerID]
, [ISIN]
, [PartnerLegalEntityIssuer]
, [PartnerProfitCentreIssuer]
, [InternalFlagIssuer]
, [Form50CodeIssuer]
, [SectorCodeIssuer]
, [CounterpartyClassIssuer]
, [Commodity]
, [Strike]
, [FacilityReference]
, [ProductDescription]
, [NettingID]
, [NettingAssetLiabilityIndicator]
, [CurrentPricePerUnit]
, [NumberUnits]
, [OptionUnderlyingPrice]
, [ContractTypeStructureType]
, [StartDateFixedInterestPeriod]
, [EndDateFixedInterestPeriod]
, [JVCompanyCode]
, [DomicileBranch]
, [BundledOptionCA]
, [FurtherAdvanceDate]
, [LastRegistrationDate]
, [ReAdvanceDate]
, [GoodsSoldIndicator]
, [GoodsType]
, [BalloonDate]
, [BalloonAmount]
, [LTV]
, [RestructureIndicator]
, [DistressedRestructureIndicator]
, [AssetLiabilityIndicator]
, [CalendarForInterestRateCalculation]
, [OriginatingChannel]
, [InstalmentDate]
, [InstalmentAmount]
, [FCFIndicator]
, [HoldCode]
, [OptionPremium]
, [UnitPriceCurrency]
, [EffectiveMaturity]
, [RepaymentCode]
, [DelinquencyStatus]
, [OSDCustN]
, [RevocableFacility]
, [SourceCustN]
, [TransferDate]
) AS CDSDeltaChecksum
,*
FROM
(
SELECT
NULL AS [CDSPrimaryKey]
,'JHB' AS [CDSSourceRegion]
, CONVERT(varchar(50),suser_name()) AS [CDSLoadUser]
,getdate() AS [CDSLoadDate]
,1 AS [CDSStatusFlag]
,BRACC.CDSBusinessDate AS [CDSBusinessDate]
,BRACC.CDSBusinessDate AS [CDSPartition]
,@SourceSystem + '_' + BRACC.AccountNo +'_'+BRACC.Serial AS [FinancialTransaction]
,PortProfMap.ProfitCentreCode AS [OrganisationUnit]
,PortProfMap.ProfitCentreCode AS [Portfolio]
,CASE WHEN BRACC.Active = 'Y' THEN '01'
WHEN BRACC.Active = 'N' THEN '00'
end
as [TradeStatus]
,NULL AS [NPLStatus]
,@SourceSystem AS [SourceSystemID]
,PortProfMap.ProductSegment AS [Product]
--,BRACC.[OPEN DATE] AS [StartDate]
, BT.[OPEN DATE]
AS startdate
,BRACC.ExpiryDate AS [EndDate]
,
CASE
WHEN
SUBSTRING(BRACC.fixeddepositmaturitydate,9,2) = 29 AND SUBSTRING(BRACC.fixeddepositmaturitydate,6,2) = 2 AND ISDATE(BRACC.fixeddepositmaturitydate)=0
THEN LEFT(BRACC.fixeddepositmaturitydate,4) + '-'+ CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(BRACC.fixeddepositmaturitydate,6,2)) +1) + '-'+ CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(BRACC.fixeddepositmaturitydate,9,2)) +1)
WHEN BRACC.fixeddepositmaturitydate = '9999-12-31' THEN '9999-12-31'
WHEN
DATEPART(DW, BRACC.FixedDepositMaturityDate) IN (1) OR
(BRACC.FixedDepositMaturityDate) IN
(SELECT holidaydate from CreditDataStore.daily.assetcontrolholidays WHERE
region='Johannesburg-ZAR')
THEN ([CreditDataStore].[daily].[ufn_Get_Calculate_Next_WorkingDay] (BRACC.FixedDepositMaturityDate))
WHEN (DATEPART(DW, BRACC.FixedDepositMaturityDate) IN (2,3,4,5,6,7)) THEN FixedDepositMaturityDate
ELSE BRACC.fixeddepositmaturitydate
END
AS [MaturityDate]
--[MaturityDate]
,'ZAR' AS [PrimaryCurrency]
,BRACC.BookBalance AS [PrimaryAmount]
,'ZAR' AS [SecondaryCurrency]
,BRACC.RetroBalance AS [SecondaryAmount]
,BRACC.[BP-ID] AS [BusinessPartnerID]
,FMIPC.CompanyCode AS [LegalEntity]
,'1' AS [BuySellIndicator]
,NULL AS [PutCallIndicator]
,BRACC.AccountNo AS [OriginalContractNumber]
,NULL AS [PartnerLegalEntity]
,NULL AS [PartnerProfitCentre]
,
CASE
WHEN TRIM(UFACC.Resident) IS NULL THEN 'N'
ELSE TRIM(UFACC.Resident)
END AS [InternalFlag]
,SUBSTRING (BRACC.RBCode, 4, 2) AS [Form50Code]
,LEFT(BRACC.RBCode, 3) AS [SectorCode]
,CASE
WHEN UFACC.CpartyClass IS NULL THEN 'EXTERNAL'
ELSE UFACC.CpartyClass
END AS [CounterpartyClass]
,NULL AS [MTMCurrency]
,NULL AS [MTM]
,(BRACC.AccountNo+'_'+BRACC.Serial) AS [OriginalTradeID]
,NULL AS [PackageNumber]
,NULL AS [ExposureLGDDownTurn]
,NULL AS [ExposureLGDExpected]
,NULL AS [OptionTimeToExerciseDate]
,NULL AS [OptionPriceCurrency]
,NULL AS [UnderlyingTimeToEnd]
,NULL AS [UnderlyingTimeToStart]
,NULL AS [ArrearsStatus]
,NULL AS [MarginCollateralType]
,NULL AS [InterestSuspenseStatus]
,NULL AS [FeeSuspenseStatus]
,NULL AS [AmortisingSuspenseStatus]
,NULL AS [SettlementDate]
,NULL AS [AccountingBook]
,NULL AS [PrimaryPaymentFrequency]
,NULL AS [SecondaryPaymentFrequency]
,BRACC.GDCurrRate AS [PrimaryInterestRate]
,NULL AS [SecondaryInterestRate]
,NULL AS [PrimaryPreviousPayDate]
,NULL AS [SecondaryPreviousPayDate]
,NULL AS [PrimaryNextReset]
,NULL AS [SecondayNextReset]
,NULL AS [PrimaryInterestRateType]
,NULL AS [SecondaryInterestRateType]
,NULL AS [PrimaryDayCountConvention]
,NULL AS [SecondaryDayCountConvention]
,NULL AS [PrimaryNetMargin]
,NULL AS [SecondaryNetMargin]
,NULL AS [PrimaryRateIndex]
,NULL AS [SecondaryRateIndex]
,NULL AS [PrimaryResetInterval]
,NULL AS [SecondaryResetInterval]
,NULL AS [FarPrimaryAmount]
,NULL AS [FarPrimaryAmountCurrency]
,NULL AS [FarSecondaryAmount]
,NULL AS [FarSecondaryAmountCurrency]
,NULL AS [CalendarForWorkingDayShifts]
,NULL AS [AgreementTerm]
,
CASE
WHEN BRACC.fixeddepositmaturitydate = '9999-12-31' THEN '0'
ELSE
cast(
DATEDIFF(MONTH,
(
CASE
WHEN
SUBSTRING(BRACC.fixeddepositmaturitydate,9,2) = 29 AND SUBSTRING(BRACC.fixeddepositmaturitydate,6,2) = 2 AND ISDATE(BRACC.fixeddepositmaturitydate)=0
THEN LEFT(BRACC.fixeddepositmaturitydate,4) + '-'+ CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(BRACC.fixeddepositmaturitydate,6,2)) +1) + '-'+ CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(BRACC.fixeddepositmaturitydate,9,2)) +1)
WHEN BRACC.fixeddepositmaturitydate = '9999-12-31' THEN '9999-12-31'
WHEN
DATEPART(DW, BRACC.FixedDepositMaturityDate) IN (1) OR
(BRACC.FixedDepositMaturityDate) IN
(SELECT holidaydate from CreditDataStore.daily.assetcontrolholidays WHERE
region='Johannesburg-ZAR')
THEN ([CreditDataStore].[daily].[ufn_Get_Calculate_Next_WorkingDay] (BRACC.FixedDepositMaturityDate))
WHEN (DATEPART(DW, BRACC.FixedDepositMaturityDate) IN (2,3,4,5,6,7)) THEN FixedDepositMaturityDate
ELSE BRACC.fixeddepositmaturitydate
END)
, BT.[OPEN DATE]
) AS numeric(12,7))
END
as
[MaturityTerm]
,NULL AS [DefaultFlag]
,NULL AS [NetLifeCap]
,NULL AS [NetLifeFloor]
,NULL AS [OriginalInterestRate]
,NULL AS [DormancyIndicator]
,NULL AS [AmortizationType]
,NULL AS [RepricingIndicator]
,NULL AS [IFRS9Classification]
,NULL AS [CompoundingFrequency]
,NULL AS [InternalCounterpartyPortfolio]
,BRACC.[OPEN DATE] AS [CreatedDate]
,NULL AS [SecondTradeID]
,NULL AS [BookingCountry]
,BRACC.CDSBusinessDate AS [ReportingDate]
,NULL AS [Seniority]
,NULL AS [OptionTimeToExercise]
,NULL AS [SourceTradeReference]
,NULL AS [InstrumentID]
,NULL AS [IssuerID]
,NULL AS [ISIN]
,NULL AS [PartnerLegalEntityIssuer]
,NULL AS [PartnerProfitCentreIssuer]
,NULL AS [InternalFlagIssuer]
,NULL AS [Form50CodeIssuer]
,NULL AS [SectorCodeIssuer]
,NULL AS [CounterpartyClassIssuer]
,NULL AS [Commodity]
,NULL AS [Strike]
,NULL AS [FacilityReference]
,BRACC.ProductNo AS [ProductDescription]
,NULL AS [NettingID]
,NULL AS [NettingAssetLiabilityIndicator]
,NULL AS [CurrentPricePerUnit]
,NULL AS [NumberUnits]
,NULL AS [OptionUnderlyingPrice]
,NULL AS [ContractTypeStructureType]
,NULL AS [StartDateFixedInterestPeriod]
,NULL AS [EndDateFixedInterestPeriod]
,NULL AS [JVCompanyCode]
,NULL AS [DomicileBranch]
,NULL AS [BundledOptionCA]
,NULL AS [FurtherAdvanceDate]
,NULL AS [LastRegistrationDate]
,NULL AS [ReAdvanceDate]
,NULL AS [GoodsSoldIndicator]
,NULL AS [GoodsType]
,NULL AS [BalloonDate]
,NULL AS [BalloonAmount]
,NULL AS [LTV]
,NULL AS [RestructureIndicator]
,NULL AS [DistressedRestructureIndicator]
,CASE WHEN BRACC.BookBalance >= 0 THEN 'L'
WHEN BRACC.BookBalance < 0 THEN 'A'
ELSE
'A'
END AS [AssetLiabilityIndicator]
,NULL AS [CalendarForInterestRateCalculation]
,NULL AS [OriginatingChannel]
,NULL AS [InstalmentDate]
,NULL AS [InstalmentAmount]
,NULL AS [FCFIndicator]
,NULL AS [HoldCode]
,NULL AS [OptionPremium]
,NULL AS [UnitPriceCurrency]
,NULL AS [EffectiveMaturity]
,NULL AS [RepaymentCode]
,NULL AS [DelinquencyStatus]
,NULL AS [OSDCustN]
,NULL AS [RevocableFacility]
,NULL AS [SourceCustN]
,NULL AS [TransferDate]
/*-----------------------------------------------------------------*/
FROM
CreditDataStore.Monthly.UFABranchAccountingDateChange AS BT
--JOIN 1
INNER JOIN
CreditDataStore.Daily.BranchAccountingBalances AS BRACC on
BT.AccountNo= (BRACC.AccountNo+BRACC.Serial)
--JOIN 2
/*BranchAccountingBalances_CR_DR_Indicator new join to calculate DR CR indicator */
LEFT JOIN
CreditDataStore.daily.BranchAccountingBalances_CR_DR_Indicator AS IND on
(IND.AccountNo + IND.Serial) = (BRACC.AccountNo+BRACC.Serial)
and IND.CDSStatusFlag = 1
--JOIN 3
/*2. join UFAPortfolioProfitCentreMap*/
--LEFT JOIN
--LEFT OUTER HASH JOIN
INNER JOIN
CreditDataStore.Monthly.UFAPortfolioProfitCentreMap AS PortProfMap ON
BRACC.ProductNo=PortProfMap.book and
BRACC.MarketSegment=PortProfMap.SourceDimension0 AND
IND.DRCRIndicator =
CASE WHEN PortProfMap.SourceDimension2 ='NULL' THEN 0 ELSE PortProfMap.SourceDimension2 END
AND
PortProfMap.SourceSystemCode = @SourceSystem AND
PortProfMap.CDSStatusFlag = 1
--JOIN 4
/*3.Monthly.TCMFMIProfitCentre*/
LEFT OUTER HASH JOIN CreditDataStore.Monthly.UFACounterpartyClassificationMap UFACC
ON UFACC.Form50Code =CAST(SUBSTRING (BRACC.RBCode, 4, 2) AS NUMERIC)
AND UFACC.CDSStatusFlag = 1
INNER JOIN
--JOIN 5
/*4.Monthly.TCMFMIProfitCentre*/
CreditDataStore.Monthly.TCMFMIProfitCentre AS FMIPC
ON CONVERT(NUMERIC, FMIPC.ProfitCentre) = PortProfMap.ProfitCentreCode
AND ISNUMERIC(FMIPC.ProfitCentre) = 1
AND FMIPC.CDSStatusFlag = 1
where
BRACC.CDSStatusFlag=1
AND
BRACC.ProductNo NOT IN ('09999')
) AS A
No comments:
Post a Comment