Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- c.[ContractId],
- c.[ReportingDate],
- c.[Entity],
- cm.[ContractMasterId],
- cm.[OriginContractId],
- cm.[OriginSystemId],
- c.[ProductType],
- c.[ProductSubtype],
- c.[BusinessUnit],
- c.[CounterpartyId],
- c.[BaseLgd],
- c.[BasePd],
- c.[CreditRating],
- c.[CreditRatingAgency],
- c.[Collectibility],
- c.[IsRestructured],
- c.[Currency],
- c.[CurrentPd],
- c.[DaysPastDue],
- c.[DiscountRate],
- c.[EffectiveInterestRate],
- c.[EffectiveInterestRateIncludingFeesAndCosts],
- c.[Segment],
- c.[IsSecured],
- c.[InitialPd],
- c.[InterestRate],
- c.[InterestSpread],
- c.[InterestCurve],
- c.[StartDate],
- c.[MaturityDate],
- c.[PrincipalAmount],
- c.[OutstandingAmount],
- c.[LimitAmount],
- c.[AccruedInterest],
- c.[PresentValue],
- c.[PresentValueIncludingFeesAndCosts],
- cn.[CounterpartyType],
- cnm.[OriginCounterpartyId],
- JSON_QUERY((
- SELECT
- ra.[Code] AS [ratingAgency],
- rs.[Code] AS [ratingScale],
- r.[Code] AS [rating]
- FROM [ContractRatings] cr
- JOIN [Ratings] r
- ON cr.[RatingId] = r.[RatingId]
- JOIN [RatingScales] rs
- ON r.[RatingScaleId] = rs.[RatingScaleId]
- JOIN [RatingAgencies] ra
- ON rs.[RatingAgencyId] = ra.[RatingAgencyId]
- WHERE cr.[ContractId] = c.[ContractId]
- FOR JSON PATH
- )) AS [ContractRatings],
- JSON_QUERY((
- SELECT
- ra.[Code] AS [ratingAgency],
- rs.[Code] AS [ratingScale],
- r.[Code] AS [rating]
- FROM [CounterpartyRatings] cr
- JOIN [Ratings] r
- ON cr.[RatingId] = r.[RatingId]
- JOIN [RatingScales] rs
- ON r.[RatingScaleId] = rs.[RatingScaleId]
- JOIN [RatingAgencies] ra
- ON rs.[RatingAgencyId] = ra.[RatingAgencyId]
- WHERE cr.[CounterpartyId] = c.[CounterpartyId]
- FOR JSON PATH
- )) AS [CounterpartyRatings],
- JSON_QUERY((
- SELECT
- cl.[CollateralId] AS [collateralId],
- clm.[OriginSystemId] AS [originSystemId],
- clm.[OriginCollateralId] AS [originCollateralId],
- cl.[CollateralType] AS [collateralType],
- JSON_VALUE(cl.DATA, '$.additionalProperties.collateralTypeProperty') AS [collateralTypeProperty],
- JSON_VALUE(cl.DATA, '$.additionalProperties.collateralMaturityDate') AS [collateralMaturityDate],
- cl.[CollateralAmount] AS [collateralAmount],
- cl.[Currency] AS [currency],
- @ReportingDate AS [reportingDate]
- FROM [CollateralContracts] cc
- JOIN [Collaterals] cl
- ON cc.[CollateralId] = cl.[CollateralId]
- JOIN [CollateralMaster] clm
- ON clm.[CollateralMasterId] = cl.[CollateralMasterId]
- WHERE cc.[ContractId] = c.[ContractId]
- AND cl.[CollateralType] IN ('101', '102', '282', '288', '289', '290', '291', '292','CDI')
- FOR JSON PATH
- )) AS [Collaterals],
- ce.[GolonganDebitur],
- ce.[LBUForm],
- ce.[GolonganDebiturRWA],
- ce.[LBUFormRWA],
- ce.[SpecificProvisionAmount],
- ce.[Stage],
- ce.[Level],
- ce.[OutstandingAmountRWA],
- ce.[AccruedInterestRWA],
- ce.[LtvRatio],
- ce.[LtvRatioRWA],
- ce.[LtvRatioRWAB4],
- ce.[SecId],
- ce.[MarketRate],
- ce.[LendingType],
- ce.[LandType],
- ce.[GASReporting],
- ce.[CommittedUndrawnAmount],
- ce.[UncommittedUndrawnAmount],
- ce.[GlNumber],
- ce.[GlNumberRWA],
- ce.[Financing],
- ce.[FinancingB4],
- ce.[LimitDebtor],
- ce.[LimitDebtorB4],
- ce.[BaselCustomerType],
- ce.[BaselCustomerTypeB4],
- ce.[BaselProductSubtype],
- ce.[BaselProductSubtypeB4],
- ce.[BaselEconomicSector],
- ce.[BranchCode],
- cpsd.DATA AS [ContractPropsSpotDerivativeData],
- cpsz.DATA AS [ContractPropsSecuritizationData],
- JSON_VALUE(cnp.DATA, '$.netSalesCust') AS [NetSalesAmount],
- mbca.[TransactorFlag],
- mbca.[CollateralTypeProperty],
- 'Fix' AS [PayLegInterestType],
- 'Floating' AS [ReceiveLegInterestType]
- FROM [Contracts] c
- JOIN [ContractMaster] cm
- ON cm.[ContractMasterId] = c.[ContractMasterId]
- JOIN [Counterparties] cn
- ON cn.[CounterpartyId] = c.[CounterpartyId]
- JOIN [CounterpartyMaster] cnm
- ON cn.[CounterpartyMasterId] = cnm.[CounterpartyMasterId]
- JOIN [ContractsExtension] ce
- ON c.[ContractId] = ce.[ContractId]
- LEFT JOIN [ContractPropsSpotDerivative] cpsd
- ON c.[ContractId] = cpsd.[ContractId]
- LEFT JOIN [ContractPropsSecuritization] cpsz
- ON c.[ContractId] = cpsz.[ContractId]
- LEFT JOIN [CounterpartyProps] cnp
- ON c.[CounterpartyId] = cnp.[CounterpartyId]
- LEFT JOIN [MandiriBaselContractAggregate] mbca
- ON c.[ContractId] = mbca.[ContractId]
- "
- +
- // This section is for querying standalone contracts
- (SupportedContractType.Single.Equals(contractType) ?
- @"
- LEFT JOIN (SELECT DISTINCT cc.[ContractId]
- FROM [CollateralContracts] cc
- JOIN [CollateralClusters] clc
- ON clc.[ClusterId] = cc.[ClusterId]
- WHERE clc.[NumContracts] > 1) AS nn
- ON c.[ContractId] = nn.[ContractId]
- "
- :
- // This section is for querying contracts in a specified cluster
- @"
- JOIN (SELECT DISTINCT cc.[ContractId]
- FROM [CollateralContracts] cc
- WHERE cc.[ClusterId] = @ClusterId) AS clct
- ON c.[ContractId] = clct.[ContractId]
- ")
- +
- @"
- WHERE 1=1
- AND c.[ReportingDate] = @ReportingDate
- AND c.[Entity] = @Entity
- AND cm.[OriginSystemId] <> 'ICS-IRB'
- AND (ce.[PsakClassification] <> 'T' OR ce.[PsakClassification] IS NULL)
- "
- +
- // This section is for querying standalone contracts
- (SupportedContractType.Single.Equals(contractType) ?
- @"
- AND nn.[ContractId] IS NULL
- "
- :
- // This section is for querying contracts in a specified cluster
- @"")
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement