4bac53b01c
- Implemented `import_prices.py` to import price index values from a CSV file with migration mapping. - Created `import_sale_fees.py` for importing sale contract line fees, including detailed logging and error handling. - Modified `import_purchase_fees.py` to change fee type from 'ordered' to 'budgeted' and added fee ID logging.
1006 lines
30 KiB
SQL
1006 lines
30 KiB
SQL
-- To be imported
|
|
--Forward Curves
|
|
|
|
|
|
|
|
-- All counterparties
|
|
SELECT
|
|
C.Id AS SupplierId,
|
|
C.Name,
|
|
C.Description,
|
|
C.Address,
|
|
C.City,
|
|
C.ZipCode,
|
|
C.Type,
|
|
C2.CountryName,
|
|
L.LocationName
|
|
FROM counterpart.Company AS C
|
|
LEFT JOIN geo.Location AS L ON C.LocationId = L.Id
|
|
LEFT JOIN geo.Country AS C2 ON C.CountryId = C2.Id
|
|
|
|
|
|
-- All counterparties for import
|
|
SELECT
|
|
--ROW_NUMBER() OVER (ORDER BY C.name) AS code,
|
|
C.Name,
|
|
'' AS tax_identifier,
|
|
'' AS vat_code,
|
|
'' AS address_name,
|
|
CASE REPLACE(LTRIM(RTRIM(C.Address)),'"','')
|
|
WHEN '0' THEN ''
|
|
WHEN '.' THEN ''
|
|
WHEN 'a' THEN ''
|
|
ELSE REPLACE(LTRIM(RTRIM(C.Address)),'"','') END
|
|
AS street,
|
|
ISNULL(L.LocationName,'') AS city,
|
|
C.ZipCode AS zip,
|
|
C2.IsoCode2 AS country_code,
|
|
'' AS subdivision_code
|
|
FROM counterpart.Company AS C
|
|
LEFT JOIN geo.Location AS L ON C.LocationId = L.Id
|
|
LEFT JOIN geo.Country AS C2 ON C.CountryId = C2.Id
|
|
ORDER BY c.Name
|
|
|
|
|
|
-- Supplier used in purchase contracts
|
|
SELECT DISTINCT
|
|
C.Id AS SupplierId,
|
|
C.Name,
|
|
C.Description,
|
|
C.Address,
|
|
C.City,
|
|
C.ZipCode,
|
|
C.Type,
|
|
C2.CountryName,
|
|
L.LocationName
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN counterpart.Company AS C ON D.CounterpartId = C.Id
|
|
LEFT JOIN geo.Location AS L ON C.LocationId = L.Id
|
|
LEFT JOIN geo.Country AS C2 ON C.CountryId = C2.Id
|
|
WHERE 1=1
|
|
AND D.Status <> 'Cancelled'
|
|
AND D.BuyOrSell = 1
|
|
|
|
|
|
|
|
-- Customers used in sale contracts
|
|
SELECT DISTINCT
|
|
C.Id AS CustomerId,
|
|
C.Name,
|
|
C.Description,
|
|
C.Address,
|
|
C.City,
|
|
C.ZipCode,
|
|
C.Type,
|
|
C2.CountryName,
|
|
L.LocationName
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN counterpart.Company AS C ON D.CounterpartId = C.Id
|
|
LEFT JOIN geo.Location AS L ON C.LocationId = L.Id
|
|
LEFT JOIN geo.Country AS C2 ON C.CountryId = C2.Id
|
|
WHERE 1=1
|
|
AND D.Status <> 'Cancelled'
|
|
AND D.BuyOrSell <> 1
|
|
|
|
|
|
-- Type de produits tradé -> Desk
|
|
SELECT
|
|
D.Id AS DeskId,
|
|
D.Caption AS Desk,
|
|
D.Description AS Description,
|
|
D.DeskType,
|
|
D.IsDefaultDesk,
|
|
D.DefaultUnitReference,
|
|
D.ActualMT,
|
|
D.QuantityPrecision,
|
|
D.ActualM3,
|
|
D.MT2BBLPrecision
|
|
FROM profiles.Desk AS D
|
|
|
|
|
|
-- Book: utilisé pour classer les deals FY (projection)
|
|
SELECT
|
|
B.Id AS BookId,
|
|
B.Caption AS Book,
|
|
B.Description AS Description,
|
|
D.Caption AS Desk,
|
|
B.Year,
|
|
B.IsActive
|
|
FROM Book AS B
|
|
INNER JOIN profiles.Desk AS D ON B.DeskId = D.Id
|
|
ORDER BY B.Year
|
|
|
|
|
|
-- Lien entre company (supplier, client...) et le produits tradé
|
|
SELECT
|
|
C.Name,
|
|
D.Caption
|
|
FROM dbo.CompanyDeskAndTradingUnit AS CDATU
|
|
INNER JOIN counterpart.Company AS C ON CDATU.CompanyId = C.Id
|
|
INNER JOIN profiles.Desk AS D ON CDATU.DeskId = D.Id
|
|
|
|
|
|
-- Users
|
|
SELECT
|
|
XU.Id AS UserId,
|
|
XU.FirstName,
|
|
XU.LastName,
|
|
XU.Login AS LoginName,
|
|
D.Caption AS Desk,
|
|
D.Description AS DeskDescription,
|
|
P.Caption AS ProfileName,
|
|
P.Description AS ProfileDescription,
|
|
XU.IsAdmin,
|
|
XU.IsActive,
|
|
XU.IsSystemAdministrator
|
|
FROM profiles.XUser AS XU
|
|
INNER JOIN profiles.Desk AS D ON XU.DeskId = D.Id
|
|
INNER JOIN profiles.Profile AS P ON XU.ProfileId = P.Id
|
|
WHERE 1=1
|
|
AND XU.Login NOT IN ('Sylvain.Duvernay','Nicolas.Vignon', 'Walter.Marques', 'Katia.Savtsenko', 'Arnaud.Chevallier')
|
|
|
|
|
|
-- Security Group
|
|
SELECT *
|
|
FROM profiles.Profile AS P
|
|
|
|
|
|
-- Strategy and related book
|
|
SELECT
|
|
S.Id AS StrategyId,
|
|
S.Caption AS StrageyName,
|
|
S.Description AS StrategyDescription,
|
|
B.Caption AS Book,
|
|
B.Year AS YearBook,
|
|
S.IsActive
|
|
FROM dbo.Strategy AS S
|
|
INNER JOIN dbo.Book AS B ON S.BookId = B.Id
|
|
ORDER BY B.Year DESC
|
|
|
|
|
|
-- Product Type
|
|
SELECT
|
|
PT.Id AS ProductTypeId,
|
|
PT.Caption AS ProductType,
|
|
PT.Description
|
|
FROM dbo.ProductType AS PT
|
|
|
|
|
|
-- Product Familly
|
|
SELECT
|
|
PF.Id AS ProductFamilyId,
|
|
PF.Caption AS ProductFamily,
|
|
PF.Description,
|
|
PT.Caption AS ProductType
|
|
FROM dbo.ProductFamily AS PF
|
|
INNER JOIN ProductType AS PT ON PF.ProductTypeId = PT.Id
|
|
|
|
-- Product
|
|
SELECT
|
|
P.Id AS ProductId,
|
|
P.ProductName,
|
|
PF.Caption AS ProductFamily,
|
|
PT.Caption AS ProductType,
|
|
P.DegreeApi,
|
|
p.Density,
|
|
P.Mt2BblFactor,
|
|
P.Density20,
|
|
P.Basis
|
|
FROM dbo.Product AS P
|
|
INNER JOIN dbo.ProductFamily AS PF ON P.FamilyId = PF.Id
|
|
INNER JOIN dbo.ProductType AS PT ON PF.ProductTypeId = PT.Id
|
|
|
|
|
|
-- Product Polluant
|
|
SELECT
|
|
PP.Id AS PolluantId,
|
|
PP.PollutantName,
|
|
PP.EmissionFactor,
|
|
P.ProductName
|
|
FROM dbo.ProductPollutant AS PP
|
|
INNER JOIN dbo.Product AS P ON PP.ProductId = P.Id
|
|
|
|
-- Continent
|
|
SELECT
|
|
C.Id AS ContinentId,
|
|
C.ContinentName
|
|
FROM geo.Continent AS C
|
|
|
|
-- Country
|
|
SELECT
|
|
C.Id AS CountryId,
|
|
C.CountryName,
|
|
C.IsoCode2,
|
|
C.IsoCode3,
|
|
C2.ContinentName,
|
|
C.BoundNorth,
|
|
C.BoundEast,
|
|
C.BoundSouth,
|
|
C.BoundWest
|
|
FROM geo.Country AS C
|
|
INNER JOIN geo.Continent AS C2 ON C.ContinentId = C2.Id
|
|
|
|
|
|
|
|
-- Incoterm
|
|
SELECT
|
|
BD.Id AS IncotermId,
|
|
BD.Caption AS IncotermAbbr,
|
|
Bd.Description AS Incoterm
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'Incoterm'
|
|
|
|
|
|
|
|
-- Payment Term Type
|
|
SELECT
|
|
BD.Id AS PayTermSecurityTypeId,
|
|
BD.Caption AS PayTermSecurityTypeAbbr,
|
|
Bd.Description AS PayTermSecurityType
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'PayTermSecurityType'
|
|
|
|
|
|
SELECT BD.Caption, DPT.PayTermRangeSize, COUNT(*)
|
|
FROM dbo.DealPaymentTerm AS DPT
|
|
INNER JOIN dbo.Deal AS D ON DPT.DealId = D.Id
|
|
INNER JOIN dbo.BaseDefinition AS BD ON D.PayTermSecurityTypeId = BD.Id
|
|
WHERE 1=1
|
|
AND DPT.PayTermRangeSize > 0
|
|
GROUP BY BD.Caption, DPT.PayTermRangeSize
|
|
ORDER BY 3 DESC
|
|
|
|
|
|
|
|
|
|
-- Delivery Period (no relation with time dimension)
|
|
SELECT
|
|
BD.Id AS DeliveryPeriodId,
|
|
BD.Caption AS DeliveryPeriodAbbr,
|
|
Bd.Description AS DeliveryPeriod
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'DeliveryPeriod'
|
|
|
|
|
|
-- Basis Document Type
|
|
SELECT
|
|
BD.Id AS BasisDocumentId,
|
|
BD.Caption AS BasisDocumentAbbr,
|
|
Bd.Description AS BasisDocument
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'BasisDocumentType'
|
|
|
|
|
|
|
|
-- BusinessArea
|
|
SELECT
|
|
BD.Id AS BusinessAreaId,
|
|
BD.Caption AS BusinessAreaAbbr,
|
|
Bd.Description AS BusinessArea
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'BusinessArea'
|
|
|
|
|
|
|
|
|
|
-- CostGroup
|
|
SELECT
|
|
BD.Id AS CostGroupId,
|
|
BD.Caption AS CostGroupAbbr,
|
|
Bd.Description AS CostGroup
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'CostGroup'
|
|
|
|
-- CostType
|
|
SELECT
|
|
--BD.Id AS CostTypeId,
|
|
BD.Caption AS [code],
|
|
Bd.Description AS [name],
|
|
'SERVICES' AS [category],
|
|
'Mt' AS [uom],
|
|
0 AS [sale_price],
|
|
0 AS [cost_price],
|
|
'' AS [description]
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'CostType'
|
|
ORDER BY 1
|
|
|
|
|
|
-- Jurisdiction
|
|
SELECT
|
|
BD.Id AS JurisdictionId,
|
|
BD.Caption AS JurisdictionAbbr,
|
|
Bd.Description AS Jurisdiction
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'Jurisdiction'
|
|
|
|
|
|
-- Exchange
|
|
SELECT
|
|
BD.Id AS ExchangeId,
|
|
BD.Caption AS ExchangeAbbr,
|
|
Bd.Description AS Exchange
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'Exchange'
|
|
|
|
|
|
-- Quotation/Price Provider
|
|
SELECT
|
|
BD.Id AS SourceId,
|
|
BD.Caption AS SourceAbbr,
|
|
Bd.Description AS Source
|
|
FROM dbo.BaseDefinition AS BD
|
|
WHERE BD.ClassType = 'QuoteSource'
|
|
|
|
SELECT * FROM price.Symbol AS S
|
|
SELECT * FROM price.Maturity AS M
|
|
SELECT * FROM price.Quote AS Q
|
|
|
|
-- Terminal
|
|
SELECT
|
|
T.Id AS TerminalId,
|
|
T.TerminalName,
|
|
L.LocationName,
|
|
C.Name AS TerminalOwner
|
|
FROM dbo.Terminal AS T
|
|
INNER JOIN geo.Location AS L ON T.LocationId = L.Id
|
|
INNER JOIN counterpart.Company AS C ON T.OwnerId = C.Id
|
|
|
|
|
|
-- Vessel
|
|
SELECT
|
|
T.Id AS VesselId,
|
|
T.TransporterName,
|
|
T.TransportType,
|
|
T.Draught,
|
|
T.BuildingYear,
|
|
c.Name AS VesselOwner,
|
|
C2.CountryName AS Flag,
|
|
T.ImoNB,
|
|
T.CharterType,
|
|
T.DeadWeightMT
|
|
FROM dbo.Transporter AS T
|
|
LEFT JOIN counterpart.Company AS C ON T.OwnerId = C.Id
|
|
LEFT JOIN geo.Country AS C2 ON T.FlagId = C2.Id
|
|
|
|
-- Vessel for insert in TradOn (CSV file structure)
|
|
SELECT
|
|
(SELECT STRING_AGG(
|
|
UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))),
|
|
' '
|
|
)
|
|
FROM STRING_SPLIT(T.TransporterName, ' ')
|
|
) AS vessel_name,
|
|
T.BuildingYear AS vessel_year,
|
|
T.ImoNB AS vessel_imo
|
|
FROM dbo.Transporter AS T
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- All EUCLID Locations
|
|
SELECT
|
|
L.Id AS LocationId,
|
|
L.LocationName,
|
|
L.CoordinateX,
|
|
L.CoordinateY,
|
|
C.CountryName,
|
|
C2.ContinentName,
|
|
BD.ClassType,
|
|
BD.Caption
|
|
FROM geo.Location AS L
|
|
INNER JOIN geo.Country AS C ON L.CountryId = C.Id
|
|
INNER JOIN geo.Continent AS C2 ON C.ContinentId = C2.Id
|
|
INNER JOIN dbo.BaseDefinition AS BD ON L.TypeId = BD.Id
|
|
|
|
|
|
-- Stock Locations (supplier) - CSV file structure
|
|
SELECT DISTINCT
|
|
LTRIM(RTRIM(LOA.LocationName)) AS [name],
|
|
'supplier' AS [type],
|
|
LOA.CoordinateX AS [lat],
|
|
LOA.CoordinateY AS [lon]
|
|
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN geo.Location AS LOA ON D.LoadLocationId = LOA.Id
|
|
LEFT JOIN geo.Country AS C_L ON LOA.CountryId = C_L.Id
|
|
--LEFT JOIN geo.Location AS DIS ON D.DeliveryLocationId = DIS.Id
|
|
--LEFT JOIN geo.Country AS C_D ON DIS.CountryId = C_D.Id
|
|
WHERE D.Status <> 'Cancelled'
|
|
AND ISNULL(LOA.LocationName, '') <> ''
|
|
AND LOA.Id <> '8855ED73-344C-4F8D-B4D6-DD214D3B7E61'
|
|
ORDER BY 2
|
|
|
|
|
|
-- Stock locations (customer) - CSV file structure
|
|
SELECT DISTINCT
|
|
LTRIM(RTRIM(DIS.LocationName)) AS [name],
|
|
'customer' AS [type],
|
|
DIS.CoordinateX AS [lat],
|
|
DIS.CoordinateY AS [lon]
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN geo.Location AS DIS ON D.DeliveryLocationId = DIS.Id
|
|
LEFT JOIN geo.Country AS C_D ON DIS.CountryId = C_D.Id
|
|
|
|
WHERE D.Status <> 'Cancelled'
|
|
AND LTRIM(RTRIM(DIS.LocationName)) <> ''
|
|
|
|
|
|
|
|
-- Purchase contracts
|
|
;WITH PaymentTerm AS (
|
|
SELECT D.Id AS DealId, BD.Caption, DPT.PayTermRangeSize
|
|
FROM dbo.DealPaymentTerm AS DPT
|
|
INNER JOIN dbo.Deal AS D ON DPT.DealId = D.Id
|
|
LEFT JOIN dbo.BaseDefinition AS BD ON D.PayTermSecurityTypeId = BD.Id
|
|
WHERE 1=1
|
|
AND DPT.PayTermRangeSize > 0
|
|
GROUP BY D.Id , BD.Caption, DPT.PayTermRangeSize
|
|
),
|
|
Geo AS (
|
|
SELECT
|
|
G.CountryName,
|
|
G.ContinentName,
|
|
G.BusinessArea
|
|
FROM singa.VW_DIM_GEOGRAPHY AS G
|
|
GROUP BY
|
|
G.CountryName,
|
|
G.ContinentName,
|
|
G.BusinessArea
|
|
),
|
|
PriceMtM AS
|
|
(
|
|
SELECT
|
|
ERMtM.DealId,
|
|
ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS Maturity ,
|
|
ISNULL(S.Description, '') AS PriceBenchmark,
|
|
M.MaturityYear AS maturity_year,
|
|
M.MaturityPeriod AS maturity_month,
|
|
M.MaturityDay AS maturity_day
|
|
FROM dbo.ExpenseRevenue AS ERMtM
|
|
INNER JOIN dbo.PriceFormulaElement AS PFEMtM ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
|
|
INNER JOIN price.Maturity AS M ON PFEMtM.MaturityId = M.Id
|
|
INNER JOIN price.Symbol AS S ON M.SymbolId = S.Id
|
|
WHERE 1=1
|
|
AND ERMtM.FormulaGroup = 2
|
|
AND PFEMtM.FormulaType LIKE 'MtM%'
|
|
AND S.Description NOT LIKE 'Freight%'
|
|
AND S.Description NOT LIKE 'Discount%'
|
|
AND S.Description NOT LIKE 'BAF%'
|
|
),
|
|
Price AS
|
|
(
|
|
SELECT
|
|
C.ContractNumber,
|
|
C.CostDescription AS pricing_curve,
|
|
AVG(C.Price) AS price
|
|
FROM singa.VW_REP_PHYSICAL_CONTRACT_COSTS AS C
|
|
WHERE 1=1
|
|
AND C.FormulaGroup = 1
|
|
AND C.CostDescription NOT IN ( 'Adjustment' )
|
|
AND C.CostDescription NOT LIKE ('%Discount%')
|
|
|
|
GROUP BY
|
|
C.ContractNumber,
|
|
C.CostDescription
|
|
),
|
|
Pricing AS
|
|
(
|
|
SELECT
|
|
D.Id AS dealID,
|
|
D.Reference AS deal_number,
|
|
MAX(
|
|
IIF(
|
|
S.Caption = 'Fixed Margin',
|
|
'Priced',
|
|
CASE WHEN ISNULL(PFE.FormulaType, '') <> 'Fixed' AND VMP.DealId IS NULL THEN 'Unpriced'
|
|
ELSE 'Priced' END
|
|
)
|
|
)
|
|
AS pricing,
|
|
ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS maturity,
|
|
M.MaturityYear AS maturity_year,
|
|
M.MaturityPeriod AS maturity_month,
|
|
M.MaturityDay AS maturity_day
|
|
FROM dbo.Deal AS D
|
|
INNER JOIN dbo.ExpenseRevenue AS ER ON ER.DealId = d.Id AND ER.FormulaGroup NOT IN (2, 4)
|
|
LEFT JOIN dbo.PriceFormulaElement AS PFE ON PFE.ExpenseRevenueId = ER.Id AND PFE.FormulaType <> 'Adjustment'
|
|
LEFT JOIN singa.VW_MVT_PRICING AS VMP ON D.Id = VMP.DealId
|
|
INNER JOIN dbo.Strategy AS S ON D.StrategyId = S.Id --All deals in Fixed Margin strategy must appear as Fixed
|
|
|
|
LEFT JOIN price.Maturity AS M ON PFE.MaturityId = M.Id
|
|
LEFT JOIN price.Symbol AS SY ON M.SymbolId = SY.Id
|
|
GROUP BY
|
|
D.Id,
|
|
D.Reference,
|
|
ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0),
|
|
M.MaturityYear,
|
|
M.MaturityPeriod,
|
|
M.MaturityDay
|
|
)
|
|
SELECT
|
|
D.Id AS source_id
|
|
, D.Id AS source_line_id
|
|
, D.Reference AS number
|
|
, ISNULL(D.OtherReference, '') AS reference
|
|
, ISNULL(D.ContractNumber, '') AS our_reference
|
|
, C.name AS party_name
|
|
, 'USD' AS currency_code
|
|
, CAST(D.Date AS DATE) AS purchase_date
|
|
|
|
, CASE
|
|
WHEN PT.Caption = 'Open account' THEN CONCAT( 'NET ' , PT.PayTermRangeSize )
|
|
WHEN PT.Caption = 'LC Doc' THEN CONCAT( 'LC ', PT.PayTermRangeSize, ' DAYS')
|
|
ELSE CONCAT( 'NET ' , PT.PayTermRangeSize )
|
|
END AS payment_term
|
|
, '' AS warehouse_code
|
|
, 'NCSW' AS weight_basis --'NCSW'
|
|
|
|
, ROUND(D.QuantityToleranceMinusPercent,2) AS tol_min_pct
|
|
, ROUND(D.QuantityTolerancePlusPercent,2) AS tol_max_pct
|
|
, D.QuantityToleranceMin AS tol_min_qty
|
|
, D.QuantityToleranceMax AS tol_max_qty
|
|
, ISNULL(LOA.LocationName, '') AS from_location_name
|
|
, ISNULL(DIS.LocationName, '') AS to_location_name
|
|
, ISNULL(INCO.Caption, '') AS incoterm_name
|
|
, 'manual' AS invoice_method
|
|
,'' AS [description]
|
|
, CONCAT( D.Reference , ' / ' , B.Description , ' / ' , S.Caption) AS [comment]
|
|
, 'line' AS line_type
|
|
, CASE
|
|
WHEN ISNULL(C2.CountryName,'') <> '' THEN CONCAT('H2SO4','-',C2.CountryName)
|
|
ELSE 'H2SO4'
|
|
END AS line_product_code
|
|
, ISNULL(C2.CountryName,'') AS origin
|
|
, ROUND(D.Quantity , 2) AS line_quantity
|
|
, 'Mt' AS line_unit_code
|
|
, ISNULL(ERV.Price ,0) AS line_price
|
|
--, ISNULL(OLD_PRICE.AvgPrice,0) AS line_old_price
|
|
, CONCAT(
|
|
D.Quantity , ' Mt of sulphuric acid - ',
|
|
CASE
|
|
WHEN ISNULL(D.CommodityBasis, 0) = 0 THEN 'Tel-quel'
|
|
ELSE CAST(D.CommodityBasis AS VARCHAR(MAX)) + '%'
|
|
END) AS line_description
|
|
, cast(D.DeliveryDateFrom AS DATE) AS line_from_del
|
|
, cast(D.DeliveryDateTo AS DATE) AS line_to_del
|
|
, 'bldate' AS pricing_trigger
|
|
, CAST(D.EstimatedDateOfBL AS DATE) AS pricing_estimated_date
|
|
|
|
, ISNULL( CONCAT( TRD.FirstName, ' ', TRD.LastName) , '') AS trader
|
|
, ISNULL( CONCAT( OP.FirstName, ' ', OP.LastName) , '') AS operator
|
|
|
|
, ISNULL(D.CommodityBasis, 0) AS concentration
|
|
, B.Description AS book
|
|
, S.Caption AS strategy
|
|
, ISNULL(BD.Caption , 'Laycan') AS period_at
|
|
, ISNULL(D.Demurrage, '') AS demurrage
|
|
, D.LaytimeHours AS laytime_hours
|
|
, D.NoticeOfReadinessExtraHours AS nor_extra_hours
|
|
, ISNULL(D.PumpingHourlyMTRate,0) AS pumping_rate
|
|
, D.UseOnlyMinAndMax AS use_only_min_max
|
|
, D.DropRemainingQuantity AS drop_remaining_quantity
|
|
, C_CP.CountryName AS counterparty_country
|
|
, GEO.BusinessArea AS business_area
|
|
, ISNULL(PRC.pricing,'') AS pricing_status
|
|
, ISNULL(ERV.pricing_curve, '') AS price_curve
|
|
, CASE
|
|
WHEN PRC.maturity = 0 THEN NULL
|
|
ELSE
|
|
CAST(CONCAT( PRC.maturity_year, '-', PRC.maturity_month, '-', ISNULL(PRC.maturity_day,'1')) AS DATE)
|
|
END AS price_maturity_date
|
|
, ISNULL(MtM.PriceBenchmark, '') AS mtm_curve
|
|
, CASE
|
|
WHEN Mtm.maturity = 0 THEN NULL
|
|
ELSE
|
|
CAST(CONCAT( Mtm.maturity_year, '-', Mtm.maturity_month, '-', ISNULL(Mtm.maturity_day,'1')) AS DATE)
|
|
END AS mtm_maturity_date
|
|
, CAST(D.EstimatedDateOfBL AS DATE) AS estimated_BL_date
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN dbo.Book AS B ON D.BookId = B.Id
|
|
LEFT JOIN dbo.ProductContainer AS PC ON D.Id = PC.Id
|
|
LEFT JOIN geo.Location AS LOA ON D.LoadLocationId = LOA.Id
|
|
LEFT JOIN geo.Country AS C_L ON LOA.CountryId = C_L.Id
|
|
LEFT JOIN geo.Location AS DIS ON D.DeliveryLocationId = DIS.Id
|
|
LEFT JOIN geo.Country AS C_D ON DIS.CountryId = C_D.Id
|
|
LEFT JOIN counterpart.Company AS C ON D.CounterpartId = C.Id
|
|
LEFT JOIN geo.Country AS C_CP ON C.CountryId = C_CP.Id
|
|
LEFT JOIN Geo AS GEO ON C_CP.CountryName = GEO.CountryName
|
|
LEFT JOIN dbo.Product AS P ON D.ProductId = P.Id
|
|
LEFT JOIN dbo.BaseDefinition AS INCO ON D.IncotermId = INCO.Id
|
|
LEFT JOIN dbo.Strategy AS S ON D.StrategyId = S.Id
|
|
LEFT JOIN PaymentTerm AS PT ON D.Id = PT.DealId
|
|
LEFT JOIN (
|
|
SELECT DealId, AVG(MvTPrice) AS AvgPrice
|
|
FROM [singa].[VW_MVT_PRICING]
|
|
GROUP BY DealId) AS OLD_PRICE ON D.Id = OLD_PRICE.DealId
|
|
LEFT JOIN Price AS ERV ON D.Reference = ERV.ContractNumber
|
|
LEFT JOIN Pricing AS PRC ON D.Id = PRC.dealID
|
|
LEFT JOIN profiles.XUser AS TRD ON D.TraderId = TRD.Id
|
|
LEFT JOIN profiles.XUser AS OP ON D.OperatorId = OP.Id
|
|
LEFT JOIN dbo.BaseDefinition AS BD ON D.DeliveryPeriodId = BD.Id
|
|
LEFT JOIN geo.Country AS C2 ON D.CountryOfOriginId = C2.Id
|
|
LEFT JOIN PriceMtM AS MTM ON D.Id = MTM.DealId
|
|
WHERE 1=1
|
|
AND D.Status <> 'Cancelled'
|
|
AND D.BuyOrSell = 1 -- Purchase contracts
|
|
AND ISNULL(D.OtherReference, '') NOT LIKE '%ACCT Matching%'
|
|
AND B.Description LIKE '%2025%'
|
|
ORDER BY 3,1
|
|
|
|
|
|
|
|
-- Sale contracts - Copy/Paste results of this query in CSV file
|
|
;WITH PaymentTerm AS (
|
|
SELECT D.Id AS DealId, BD.Caption, DPT.PayTermRangeSize
|
|
FROM dbo.DealPaymentTerm AS DPT
|
|
INNER JOIN dbo.Deal AS D ON DPT.DealId = D.Id
|
|
LEFT JOIN dbo.BaseDefinition AS BD ON D.PayTermSecurityTypeId = BD.Id
|
|
WHERE 1=1
|
|
AND DPT.PayTermRangeSize > 0
|
|
GROUP BY D.Id , BD.Caption, DPT.PayTermRangeSize
|
|
),
|
|
Geo AS (
|
|
SELECT
|
|
G.CountryName,
|
|
G.ContinentName,
|
|
G.BusinessArea
|
|
FROM singa.VW_DIM_GEOGRAPHY AS G
|
|
GROUP BY
|
|
G.CountryName,
|
|
G.ContinentName,
|
|
G.BusinessArea
|
|
),
|
|
PriceMtM AS
|
|
(
|
|
SELECT ERMtM.DealId,
|
|
ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS maturity ,
|
|
ISNULL(S.Description, '') AS PriceBenchmark,
|
|
M.MaturityYear AS maturity_year,
|
|
M.MaturityPeriod AS maturity_month,
|
|
M.MaturityDay AS maturity_day
|
|
FROM dbo.ExpenseRevenue AS ERMtM
|
|
INNER JOIN dbo.PriceFormulaElement AS PFEMtM ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
|
|
INNER JOIN price.Maturity AS M ON PFEMtM.MaturityId = M.Id
|
|
INNER JOIN price.Symbol AS S ON M.SymbolId = S.Id
|
|
WHERE 1=1
|
|
AND ERMtM.FormulaGroup = 2
|
|
AND PFEMtM.FormulaType LIKE 'MtM%'
|
|
AND S.Description NOT LIKE 'Freight%'
|
|
AND S.Description NOT LIKE 'Discount%'
|
|
AND S.Description NOT LIKE 'BAF%'
|
|
),
|
|
Price AS
|
|
(
|
|
SELECT
|
|
C.ContractNumber,
|
|
C.CostDescription AS pricing_curve,
|
|
AVG(C.Price) AS price
|
|
FROM singa.VW_REP_PHYSICAL_CONTRACT_COSTS AS C
|
|
WHERE 1=1
|
|
AND C.FormulaGroup = 1
|
|
AND C.CostDescription NOT IN ( 'Adjustment' )
|
|
AND C.CostDescription NOT LIKE ('%Discount%')
|
|
|
|
GROUP BY
|
|
C.ContractNumber,
|
|
C.CostDescription
|
|
),
|
|
Pricing AS
|
|
(
|
|
SELECT
|
|
D.Id AS dealID,
|
|
D.Reference AS deal_number,
|
|
MAX(
|
|
IIF(
|
|
S.Caption = 'Fixed Margin',
|
|
'Priced',
|
|
CASE WHEN ISNULL(PFE.FormulaType, '') <> 'Fixed' AND VMP.DealId IS NULL THEN 'Unpriced'
|
|
ELSE 'Priced' END
|
|
)
|
|
)
|
|
AS pricing,
|
|
ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS maturity,
|
|
M.MaturityYear AS maturity_year,
|
|
M.MaturityPeriod AS maturity_month,
|
|
M.MaturityDay AS maturity_day
|
|
FROM dbo.Deal AS D
|
|
INNER JOIN dbo.ExpenseRevenue AS ER ON ER.DealId = d.Id AND ER.FormulaGroup NOT IN (2, 4)
|
|
LEFT JOIN dbo.PriceFormulaElement AS PFE ON PFE.ExpenseRevenueId = ER.Id AND PFE.FormulaType <> 'Adjustment'
|
|
LEFT JOIN singa.VW_MVT_PRICING AS VMP ON D.Id = VMP.DealId
|
|
INNER JOIN dbo.Strategy AS S ON D.StrategyId = S.Id --All deals in Fixed Margin strategy must appear as Fixed
|
|
|
|
LEFT JOIN price.Maturity AS M ON PFE.MaturityId = M.Id
|
|
LEFT JOIN price.Symbol AS SY ON M.SymbolId = SY.Id
|
|
GROUP BY
|
|
D.Id,
|
|
D.Reference,
|
|
ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0),
|
|
M.MaturityYear,
|
|
M.MaturityPeriod,
|
|
M.MaturityDay
|
|
)
|
|
SELECT
|
|
D.Id AS source_id
|
|
, D.Id AS source_line_id
|
|
, D.Reference AS number
|
|
, ISNULL(D.OtherReference, '') AS reference
|
|
, ISNULL(D.ContractNumber, '') AS our_reference
|
|
, C.name AS party_name
|
|
, 'USD' AS currency_code
|
|
, CAST(D.Date AS DATE) AS sale_date
|
|
|
|
, CASE
|
|
WHEN PT.Caption = 'Open account' THEN CONCAT( 'NET ' , PT.PayTermRangeSize )
|
|
WHEN PT.Caption = 'LC Doc' THEN CONCAT( 'LC ', PT.PayTermRangeSize, ' DAYS')
|
|
ELSE CONCAT( 'NET ' , PT.PayTermRangeSize )
|
|
END AS payment_term
|
|
|
|
, '' AS warehouse_code
|
|
, 'NCSW' AS weight_basis
|
|
, ROUND(D.QuantityToleranceMinusPercent,2) AS tol_min_pct
|
|
, ROUND(D.QuantityTolerancePlusPercent,2) AS tol_max_pct
|
|
, D.QuantityToleranceMin AS tol_min_qty
|
|
, D.QuantityToleranceMax AS tol_max_qty
|
|
|
|
, ISNULL(LOA.LocationName, '') AS from_location_name
|
|
, ISNULL(DIS.LocationName, '') AS to_location_name
|
|
, ISNULL(INCO.Caption, '') AS incoterm_name
|
|
, 'manual' AS invoice_method
|
|
,'' AS [description]
|
|
, CONCAT( D.Reference , ' / ' , B.Description , ' / ' , S.Caption) AS [comment]
|
|
, 'line' AS line_type
|
|
, CASE
|
|
WHEN ISNULL(C2.CountryName,'') <> '' THEN CONCAT('H2SO4','-',C2.CountryName)
|
|
ELSE 'H2SO4'
|
|
END AS line_product_code
|
|
, ISNULL(C2.CountryName,'') AS origin
|
|
, ROUND(D.Quantity , 2) AS line_quantity
|
|
, 'Mt' AS line_unit_code
|
|
, ISNULL(ERV.Price ,0) AS line_price
|
|
--, ISNULL(OLD_PRICE.AvgPrice,0) AS line_old_price
|
|
, CONCAT(
|
|
D.Quantity , ' Mt of sulphuric acid - ',
|
|
CASE
|
|
WHEN ISNULL(D.CommodityBasis, 0) = 0 THEN 'Tel-quel'
|
|
ELSE CAST(D.CommodityBasis AS VARCHAR(MAX)) + '%'
|
|
END) AS line_description
|
|
, cast(D.DeliveryDateFrom AS DATE) AS line_from_del
|
|
, cast(D.DeliveryDateTo AS DATE) AS line_to_del
|
|
, 'bldate' AS pricing_trigger
|
|
, CAST(D.EstimatedDateOfBL AS DATE) AS pricing_estimated_date
|
|
|
|
, ISNULL( CONCAT( TRD.FirstName, ' ', TRD.LastName) , '') AS trader
|
|
, ISNULL( CONCAT( OP.FirstName, ' ', OP.LastName) , '') AS operator
|
|
|
|
, ISNULL(D.CommodityBasis, 0) AS concentration
|
|
, B.Description AS book
|
|
, S.Caption AS strategy
|
|
, ISNULL(BD.Caption , 'Laycan') AS period_at
|
|
, ISNULL(D.Demurrage, '') AS demurrage
|
|
, D.LaytimeHours AS laytime_hours
|
|
, D.NoticeOfReadinessExtraHours AS nor_extra_hours
|
|
, ISNULL(D.PumpingHourlyMTRate,0) AS pumping_rate
|
|
, D.UseOnlyMinAndMax AS use_only_min_max
|
|
, D.DropRemainingQuantity AS drop_remaining_quantity
|
|
, C_CP.CountryName AS counterparty_country
|
|
, GEO.BusinessArea AS business_area
|
|
, ISNULL(PRC.pricing,'') AS pricing_status
|
|
, ISNULL(ERV.pricing_curve, '') AS price_curve
|
|
, CASE
|
|
WHEN PRC.maturity = 0 THEN NULL
|
|
ELSE
|
|
CAST(CONCAT( PRC.maturity_year, '-', PRC.maturity_month, '-', ISNULL(PRC.maturity_day,'1')) AS DATE)
|
|
END AS price_maturity_date
|
|
, ISNULL(MtM.PriceBenchmark, '') AS mtm_curve
|
|
, CASE
|
|
WHEN Mtm.maturity = 0 THEN NULL
|
|
ELSE
|
|
CAST(CONCAT( Mtm.maturity_year, '-', Mtm.maturity_month, '-', ISNULL(Mtm.maturity_day,'1')) AS DATE)
|
|
END AS mtm_maturity_date
|
|
, CAST(D.EstimatedDateOfBL AS DATE) AS estimated_BL_date
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN dbo.Book AS B ON D.BookId = B.Id
|
|
LEFT JOIN dbo.ProductContainer AS PC ON D.Id = PC.Id
|
|
LEFT JOIN geo.Location AS LOA ON D.LoadLocationId = LOA.Id
|
|
LEFT JOIN geo.Country AS C_L ON LOA.CountryId = C_L.Id
|
|
LEFT JOIN geo.Location AS DIS ON D.DeliveryLocationId = DIS.Id
|
|
LEFT JOIN geo.Country AS C_D ON DIS.CountryId = C_D.Id
|
|
LEFT JOIN counterpart.Company AS C ON D.CounterpartId = C.Id
|
|
LEFT JOIN geo.Country AS C_CP ON C.CountryId = C_CP.Id
|
|
LEFT JOIN Geo AS GEO ON C_CP.CountryName = GEO.CountryName
|
|
LEFT JOIN dbo.Product AS P ON D.ProductId = P.Id
|
|
LEFT JOIN dbo.BaseDefinition AS INCO ON D.IncotermId = INCO.Id
|
|
LEFT JOIN dbo.Strategy AS S ON D.StrategyId = S.Id
|
|
LEFT JOIN PaymentTerm AS PT ON D.Id = PT.DealId
|
|
LEFT JOIN (
|
|
SELECT DealId, AVG(MvTPrice) AS AvgPrice
|
|
FROM [singa].[VW_MVT_PRICING]
|
|
GROUP BY DealId) AS OLD_PRICE ON D.Id = OLD_PRICE.DealId
|
|
LEFT JOIN Price AS ERV ON D.Reference = ERV.ContractNumber
|
|
LEFT JOIN Pricing AS PRC ON D.Id = PRC.dealID
|
|
LEFT JOIN profiles.XUser AS TRD ON D.TraderId = TRD.Id
|
|
LEFT JOIN profiles.XUser AS OP ON D.OperatorId = OP.Id
|
|
LEFT JOIN dbo.BaseDefinition AS BD ON D.DeliveryPeriodId = BD.Id
|
|
LEFT JOIN geo.Country AS C2 ON D.CountryOfOriginId = C2.Id
|
|
LEFT JOIN PriceMtM AS MTM ON D.Id = MTM.DealId
|
|
WHERE 1=1
|
|
AND D.Status <> 'Cancelled'
|
|
AND D.BuyOrSell = -1 -- Sale contracts
|
|
AND ISNULL(D.OtherReference, '') NOT LIKE '%ACCT Matching%'
|
|
AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
|
|
--AND D.Reference = 2112
|
|
ORDER BY 3,1
|
|
|
|
|
|
|
|
-- Purchase contract costs
|
|
SELECT
|
|
|
|
D.Reference AS contract_number,
|
|
--B.Description,
|
|
--CASE D.BuyOrSell
|
|
-- WHEN '1' THEN 'Purchase'
|
|
-- ELSE 'Sale'
|
|
-- END AS contract_type,
|
|
LTRIM(RTRIM( ISNULL(D.OtherReference, '') )) AS contract_ref,
|
|
1 AS line_sequence,
|
|
CASE
|
|
WHEN CT.Caption = 'Commision' THEN 'Commission'
|
|
WHEN CT.Caption = 'Freight' THEN 'Maritime Freight'
|
|
ELSE CT.Caption
|
|
END AS product,
|
|
LTRIM(RTRIM( ISNULL(C.Name , 'TBD Supplier') )) AS supplier,
|
|
ER.CurrencyCode AS currency,
|
|
CASE ER.IsRevenue
|
|
WHEN 1 THEN 'REC'
|
|
ELSE 'PAY'
|
|
END AS p_r,
|
|
'Per qt' AS mode,
|
|
ROUND (
|
|
(IIF(PFE.Quantity IS NULL, PFE.WeightBalance / 100, 1) * PFE.OutputPriceValue),
|
|
2 ) AS price,
|
|
ER.UnitReference AS unit
|
|
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN dbo.Book AS B ON D.BookId = B.Id
|
|
INNER JOIN dbo.ExpenseRevenue AS ER ON D.Id = ER.DealId
|
|
INNER JOIN dbo.PriceFormulaElement AS PFE ON PFE.ExpenseRevenueId = ER.Id
|
|
INNER JOIN dbo.BaseDefinition AS CT ON ER.CostTypeId = CT.Id
|
|
LEFT JOIN counterpart.Company AS C ON ER.CounterpartId = c.Id
|
|
WHERE 1=1
|
|
AND D.Status <> 'Cancelled'
|
|
AND D.BuyOrSell = 1 -- Purchase contracts
|
|
AND ISNULL(D.OtherReference, '') NOT LIKE '%ACCT Matching%'
|
|
--AND (B.Description LIKE '%2025%')
|
|
AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
|
|
AND ER.FormulaGroup NOT IN (1,2) -- Not Price or MtM
|
|
ORDER BY 1
|
|
|
|
|
|
|
|
|
|
-- Sale contract costs
|
|
SELECT
|
|
|
|
D.Reference AS contract_number,
|
|
--B.Description,
|
|
--CASE D.BuyOrSell
|
|
-- WHEN '1' THEN 'Purchase'
|
|
-- ELSE 'Sale'
|
|
-- END AS contract_type,
|
|
LTRIM(RTRIM( ISNULL(D.OtherReference, '') )) AS contract_ref,
|
|
1 AS line_sequence,
|
|
CASE
|
|
WHEN CT.Caption = 'Commision' THEN 'Commission'
|
|
WHEN CT.Caption = 'Freight' THEN 'Maritime Freight'
|
|
ELSE CT.Caption
|
|
END AS product,
|
|
LTRIM(RTRIM( ISNULL(C.Name , 'TBD Supplier') )) AS supplier,
|
|
ER.CurrencyCode AS currency,
|
|
CASE ER.IsRevenue
|
|
WHEN 1 THEN 'REC'
|
|
ELSE 'PAY'
|
|
END AS p_r,
|
|
'Per qt' AS mode,
|
|
ROUND (
|
|
(IIF(PFE.Quantity IS NULL, PFE.WeightBalance / 100, 1) * PFE.OutputPriceValue),
|
|
2 ) AS price,
|
|
ER.UnitReference AS unit
|
|
|
|
FROM dbo.Deal AS D
|
|
LEFT JOIN dbo.Book AS B ON D.BookId = B.Id
|
|
INNER JOIN dbo.ExpenseRevenue AS ER ON D.Id = ER.DealId
|
|
INNER JOIN dbo.PriceFormulaElement AS PFE ON PFE.ExpenseRevenueId = ER.Id
|
|
INNER JOIN dbo.BaseDefinition AS CT ON ER.CostTypeId = CT.Id
|
|
LEFT JOIN counterpart.Company AS C ON ER.CounterpartId = c.Id
|
|
WHERE 1=1
|
|
AND D.Status <> 'Cancelled'
|
|
AND D.BuyOrSell <> 1 -- Sale contracts
|
|
AND ISNULL(D.OtherReference, '') NOT LIKE '%ACCT Matching%'
|
|
AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
|
|
AND ER.FormulaGroup NOT IN (1,2) -- Not Price or MtM
|
|
ORDER BY 1
|
|
|
|
|
|
|
|
|
|
-- Run that script to generate CSV for price_curve MTM used in physical contract in 2025 and 2026
|
|
SELECT DISTINCT
|
|
CONCAT(
|
|
ISNULL(S.Description, '') , ' ' ,
|
|
M.MaturityYear , '-',
|
|
RIGHT(CONCAT('0', M.MaturityPeriod),2)
|
|
)AS price_index,
|
|
Q.QuoteDate AS price_date,
|
|
Q.Value AS high_price,
|
|
Q.Value AS low_price,
|
|
Q.Value AS open_price,
|
|
Q.Value AS price_value
|
|
FROM dbo.ExpenseRevenue AS ERMtM
|
|
INNER JOIN dbo.Deal AS D ON ERMtM.DealId = D.Id
|
|
INNER JOIN dbo.PriceFormulaElement AS PFEMtM ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
|
|
INNER JOIN price.Maturity AS M ON PFEMtM.MaturityId = M.Id
|
|
INNER JOIN price.Symbol AS S ON M.SymbolId = S.Id
|
|
INNER JOIN dbo.Book AS B ON D.BookId = B.Id
|
|
LEFT JOIN price.Quote AS Q ON M.Id = Q.MaturityId
|
|
WHERE 1=1
|
|
AND ERMtM.FormulaGroup = 2
|
|
AND PFEMtM.FormulaType LIKE 'MtM%'
|
|
AND S.Description NOT LIKE 'Freight%'
|
|
AND S.Description NOT LIKE 'Discount%'
|
|
AND S.Description NOT LIKE 'BAF%'
|
|
AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
|
|
ORDER BY 1,2
|
|
|
|
|
|
SELECT DISTINCT formulaType FROM PriceFormulaElement
|
|
|
|
Trigger, RangeDate
|
|
|
|
|
|
|
|
|
|
-- Run that script to generate CSV for price_curve MTM used in physical contract in 2025 and 2026
|
|
SELECT DISTINCT
|
|
D.Reference,
|
|
PFEMtM.FormulaType,
|
|
CONCAT(
|
|
ISNULL(S.Description, '') , ' ' ,
|
|
M.MaturityYear , '-',
|
|
RIGHT(CONCAT('0', M.MaturityPeriod),2)
|
|
)AS price_index,
|
|
--Q.QuoteDate AS price_date,
|
|
--Q.Value AS high_price,
|
|
--Q.Value AS low_price,
|
|
--Q.Value AS open_price,
|
|
--Q.Value AS price_value,
|
|
|
|
PFEMtM.FirstDate,
|
|
PFEMtM.LastDate,
|
|
PFEMtM.TriggerDate,
|
|
PFEMtM.NbDaysBefore,
|
|
PFEMtM.NbDaysAfter,
|
|
|
|
PFEMtM.*
|
|
FROM dbo.ExpenseRevenue AS ERMtM
|
|
INNER JOIN dbo.Deal AS D ON ERMtM.DealId = D.Id
|
|
INNER JOIN dbo.PriceFormulaElement AS PFEMtM ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
|
|
INNER JOIN price.Maturity AS M ON PFEMtM.MaturityId = M.Id
|
|
INNER JOIN price.Symbol AS S ON M.SymbolId = S.Id
|
|
INNER JOIN dbo.Book AS B ON D.BookId = B.Id
|
|
LEFT JOIN price.Quote AS Q ON M.Id = Q.MaturityId
|
|
WHERE 1=1
|
|
AND ERMtM.FormulaGroup = 1
|
|
AND PFEMtM.FormulaType NOT LIKE 'MtM%'
|
|
AND S.Description NOT LIKE 'Freight%'
|
|
AND S.Description NOT LIKE 'Discount%'
|
|
AND S.Description NOT LIKE 'BAF%'
|
|
AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
|
|
--AND D.Reference = 2097
|
|
ORDER BY 1,2
|
|
|