Contract Price Component Prices

Contract Price Component Prices zijn de prijzen van de prijs componenten vastgelegd bij een contract uit ERP.

Onderstaande query kan gebruikt worden om de benodigde data te selecteren.

erp_cpp.sql

WITH CTE AS(

	SELECT
	c.ConnectionId,
	c.EAN,
	c.ProductType,
	co.ContractId,
	co.ContractReference,
	p.Description,
	p.PresentationDescription,
	pcp.Price,
	pcp.PriceComponentId,
	pc.Description as ComponentDescription,
	pc.PresentationDescription as ComponentPresentationDescription,
	ippcp.PriceComponentPriceId,
	pcp.StartDate,
	pcp.EndDate,
	'Propositie' as Type,
	o1.Name

	FROM tbl_Contract co

	INNER JOIN itbl_Contract_Connection						AS icc		ON co.ContractId = icc.ContractId
	INNER JOIN tbl_Connection								AS c		ON icc.ConnectionId = c.ConnectionId
	LEFT JOIN itbl_Contract_Proposition						AS icop		ON co.ContractId = icop.ContractId
	LEFT JOIN tbl_Proposition								AS p		ON icop.PropositionId = p.PropositionId
	LEFT JOIN itbl_Proposition_PriceComponentPrice			AS ippcp	ON p.PropositionId = ippcp.PropositionId
	LEFT JOIN tbl_PriceComponentPrice						AS pcp		ON ippcp.PriceComponentPriceId = pcp.PriceComponentPriceId
	INNER JOIN tbl_PriceComponent							AS pc		ON pcp.PriceComponentId = pc.PriceComponentId

	INNER JOIN itbl_Organization_Connection					AS ioc		ON c.ConnectionId = ioc.ConnectionId
	INNER JOIN tbl_Organization								AS o		ON ioc.OrganizationId = o.OrganizationId
	INNER JOIN itbl_Collective_Organization					AS ico		ON o.OrganizationId = ico.OrganizationId
	INNER JOIN itbl_Company_Collective						AS icoco	ON ico.CollectiveId = icoco.CollectiveId
	INNER JOIN tbl_Organization								AS o1		ON icoco.CompanyId = o1.OrganizationId

	where 1=1

	AND icc.ValidEndDate > GETDATE()
	AND icop.ValidEndDate > GETDATE()
	AND ippcp.ValidEndDate > GETDATE()
	AND ioc.ValidEndDate > GETDATE()
	AND ico.ValidEndDate > GETDATE()
	AND icoco.ValidEndDate > GETDATE()

	and CO.EndDate > '1-1-2021'
	AND pc.CalculationMethodType <> 'AdvancePaymentCalculationDetail'

	UNION ALL

	SELECT
	c.ConnectionId,
	c.EAN,
	c.ProductType,
	co.ContractId,
	co.ContractReference,
	p.Description,
	p.PresentationDescription,
	pcp.Price,
	pcp.PriceComponentId,
	pc.Description as ComponentDescription,
	pc.PresentationDescription as ComponentPresentationDescription,
	icpcp.PriceComponentPriceId,
	pcp.StartDate,
	pcp.EndDate,
	'Contract' as Type,
	o1.Name

	FROM tbl_Contract co

	INNER JOIN itbl_Contract_Connection						AS icc		ON co.ContractId = icc.ContractId
	INNER JOIN tbl_Connection								AS c		ON icc.ConnectionId = c.ConnectionId
	LEFT JOIN itbl_Contract_Proposition						AS icop		ON co.ContractId = icop.ContractId
	LEFT JOIN tbl_Proposition								AS p		ON icop.PropositionId = p.PropositionId
	LEFT join itbl_Contract_PriceComponentPrice				AS icpcp	ON co.ContractId = icpcp.ContractId
	LEFT JOIN tbl_PriceComponentPrice						AS pcp		ON icpcp.PriceComponentPriceId = pcp.PriceComponentPriceId
	LEFT JOIN tbl_PriceComponent							AS pc		ON pcp.PriceComponentId = pc.PriceComponentId

	INNER JOIN itbl_Organization_Connection					AS ioc		ON c.ConnectionId = ioc.ConnectionId
	INNER JOIN tbl_Organization								AS o		ON ioc.OrganizationId = o.OrganizationId
	INNER JOIN itbl_Collective_Organization					AS ico		ON o.OrganizationId = ico.OrganizationId
	INNER JOIN itbl_Company_Collective						AS icoco	ON ico.CollectiveId = icoco.CollectiveId
	INNER JOIN tbl_Organization								AS o1		ON icoco.CompanyId = o1.OrganizationId

	where 1=1

	AND icc.ValidEndDate > GETDATE()
	AND icop.ValidEndDate > GETDATE()
	AND icpcp.ValidEndDate > GETDATE()
	AND ico.ValidEndDate > GETDATE()
	AND icoco.ValidEndDate > GETDATE()

	and CO.EndDate > '1-1-2021'

	AND pc.CalculationMethodType <> 'AdvancePaymentCalculationDetail'
	ORDER BY StartDate
)

SELECT * FROM CTE

De ingelezen data wordt opgeslagen in de database in udq.contractpricecomponentprice:

connection_id
price_component_price_id
contract_reference
ean
product_type
contract_id
description
presentation_description
price
price_component_id
component_description
component_presentation_description
start_date
end_date
type
name
datafile_id

Controles

Bij het inlezen wordt gecontroleerd of een Contract Price Component Price voor een specifieke connection_id, price_component_price_id en contract_reference al is ingelezen, mocht dit zo zijn dan wordt de betreffende Contract Price Component Price bijgewerkt met de aangeleverde data.