Top 3 Query (1C)

SELECT ALLOWED
	CurrencyRatesSliceLast.Currency AS Currency,
	CurrencyRatesSliceLast.ExchangeRate AS ExchangeRate,
	CurrencyRatesSliceLast.Multiplicity AS Multiplicity
INTO Rates
FROM
	InformationRegister.CurrencyRates.SliceLast(&Period, ) AS CurrencyRatesSliceLast
;

////////////////////////////////////////////////////////////////////////////////
SELECT
	ContractsLimitsSliceLast.Contract AS Contract,
	ContractsLimitsSliceLast.LimitType AS LimitType,
	ContractsLimitsSliceLast.Amount AS Amount,
	ContractsLimitsSliceLast.Currency AS CurrencyLimit,
	ISNULL(Rates.ExchangeRate, 1) AS RateLimit,
	ISNULL(Rates.Multiplicity, 1) AS MultiplicityLimit
INTO Limits
FROM
	InformationRegister.ContractsLimits.SliceLast(
			&Period,
			Entity = &Entity
				AND Company = &Company
				AND LimitType IN (&LimitTypes)
				AND Contract IN (&Contract, VALUE(Catalog.CompanyContracts.EmptyRef))) AS ContractsLimitsSliceLast
		LEFT JOIN Rates AS Rates
		ON ContractsLimitsSliceLast.Currency = Rates.Currency
WHERE
	ContractsLimitsSliceLast.Status
;

////////////////////////////////////////////////////////////////////////////////
SELECT
	CustomerAccountsBalance.AmountCurBalance AS AmountCurBalance,
	CustomerAccountsBalance.Contract AS Contract
INTO ARWithCheques
FROM
	AccumulationRegister.CustomerAccounts.Balance(
			&Period,
			Entity = &Entity
				AND Company = &Company) AS CustomerAccountsBalance

UNION ALL

SELECT
	AccountsReceivableDeferredBalance.AmountCurBalance,
	AccountsReceivableDeferredBalance.Contract
FROM
	AccumulationRegister.AccountsReceivableDeferred.Balance(
			&Period,
			Entity = &Entity
				AND Company = &Company) AS AccountsReceivableDeferredBalance
;

////////////////////////////////////////////////////////////////////////////////
SELECT
	Limits.LimitType AS LimitType,
	Limits.Amount AS Amount,
	Limits.CurrencyLimit AS CurrencyLimit,
	SUM(ISNULL(CASE
				WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
					THEN CAST(ARWithCheques.AmountCurBalance * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2))
				ELSE ARWithCheques.AmountCurBalance
			END, 0)) AS AmountBalance,
	Limits.Contract AS Contract
INTO LimitBalance
FROM
	Limits AS Limits
		LEFT JOIN ARWithCheques AS ARWithCheques
			LEFT JOIN Rates AS Rates
			ON ARWithCheques.Contract.AccountsCurrency = Rates.Currency
		ON (CASE
				WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
					THEN TRUE
				ELSE Limits.Contract = ARWithCheques.Contract
			END)
WHERE
	Limits.LimitType = VALUE(enum.limittype.debit)

GROUP BY
	Limits.LimitType,
	Limits.Amount,
	Limits.Contract,
	Limits.CurrencyLimit

UNION ALL

SELECT
	Limits.LimitType,
	Limits.Amount,
	Limits.CurrencyLimit,
	SUM(ISNULL(CASE
				WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
					THEN CAST(VendorAccountsBalance.AmountCurBalance * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2))
				ELSE VendorAccountsBalance.AmountCurBalance
			END, 0)),
	Limits.Contract
FROM
	Limits AS Limits
		LEFT JOIN AccumulationRegister.VendorAccounts.Balance(
				&Period,
				Entity = &Entity
					AND Company = &Company) AS VendorAccountsBalance
			LEFT JOIN Rates AS Rates
			ON VendorAccountsBalance.Contract.AccountsCurrency = Rates.Currency
		ON (CASE
				WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
					THEN TRUE
				ELSE Limits.Contract = VendorAccountsBalance.Contract
			END)
WHERE
	Limits.LimitType = VALUE(enum.limittype.credit)

GROUP BY
	Limits.LimitType,
	Limits.Amount,
	Limits.Contract,
	Limits.CurrencyLimit

UNION ALL

SELECT
	Limits.LimitType,
	Limits.Amount,
	Limits.CurrencyLimit,
	SUM(ISNULL(CASE
				WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
					THEN CAST(SalesByCustomerOrdersTurnovers.AmountCurTurnover * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2))
				ELSE SalesByCustomerOrdersTurnovers.AmountCurTurnover
			END, 0)),
	Limits.Contract
FROM
	Limits AS Limits
		LEFT JOIN AccumulationRegister.SalesByCustomerOrders.Turnovers(
				,
				&Period,
				,
				Entity = &Entity
					AND Company = &Company) AS SalesByCustomerOrdersTurnovers
			LEFT JOIN Rates AS Rates
			ON SalesByCustomerOrdersTurnovers.Contract.AccountsCurrency = Rates.Currency
		ON (CASE
				WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
					THEN TRUE
				ELSE Limits.Contract = SalesByCustomerOrdersTurnovers.Contract
			END)
WHERE
	Limits.LimitType = VALUE(enum.limittype.income)

GROUP BY
	Limits.LimitType,
	Limits.Amount,
	Limits.Contract,
	Limits.CurrencyLimit

UNION ALL

SELECT
	Limits.LimitType,
	Limits.Amount,
	Limits.CurrencyLimit,
	SUM(ISNULL(CAST(PurchasingTurnovers.AmountTurnover * Rates.ExchangeRate * Limits.MultiplicityLimit / (Limits.RateLimit * ISNULL(Rates.Multiplicity, 1)) AS NUMBER(15, 2)), 0)),
	Limits.Contract
FROM
	Limits AS Limits
		LEFT JOIN AccumulationRegister.Purchasing.Turnovers(
				,
				&Period,
				,
				Entity = &Entity
					AND Document.Company = &Company) AS PurchasingTurnovers
			LEFT JOIN Rates AS Rates
			ON (Rates.Currency = &RegCurr)
		ON (CASE
				WHEN Limits.Contract = VALUE(Catalog.CompanyContracts.EmptyRef)
					THEN TRUE
				ELSE Limits.Contract = PurchasingTurnovers.Document.Contract
			END)
WHERE
	Limits.LimitType = VALUE(enum.limittype.expense)

GROUP BY
	Limits.LimitType,
	Limits.Amount,
	Limits.Contract,
	Limits.CurrencyLimit
;

////////////////////////////////////////////////////////////////////////////////
SELECT
	LimitBalance.LimitType AS LimitType,
	LimitBalance.Amount AS Amount,
	LimitBalance.CurrencyLimit AS CurrencyLimit,
	LimitBalance.AmountBalance AS AmountBalance,
	LimitBalance.Amount - LimitBalance.AmountBalance AS ExceededAmount,
	(LimitBalance.Amount - LimitBalance.AmountBalance) / LimitBalance.Amount AS CreditLimitBalance,
	LimitBalance.Contract AS Contract
FROM
	LimitBalance AS LimitBalance
WHERE
	(LimitBalance.Amount - LimitBalance.AmountBalance) / LimitBalance.Amount < 1 / 10