DocumentsByProject.bsl

SELECT ALLOWED
	AdditionalExpenses.Ref AS Ref,
	VALUETYPE(AdditionalExpenses.Ref) AS DocumentType,
	CASE
		WHEN AdditionalExpenses.Posted
			THEN 1
		WHEN AdditionalExpenses.DeletionMark
			THEN 2
		ELSE 0
	END AS DocumentStatus,
	AdditionalExpenses.Number AS Number,
	AdditionalExpenses.Date AS Date,
	AdditionalExpenses.Company AS Company,
	AdditionalExpenses.DocumentAmount AS DocumentAmount,
	AdditionalExpenses.Author AS Author,
	AdditionalExpenses.DocumentCurrency AS Currency,
	CAST(AdditionalExpenses.Comment AS STRING(200)) AS Comment,
	AdditionalExpenses.TransactionType AS TransactionType
FROM
	Document.AdditionalExpenses AS AdditionalExpenses
WHERE
	VALUETYPE(AdditionalExpenses.Ref) IN (&DocumentsListSelectedTypes)
	AND AdditionalExpenses.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.AdditionalExpenses))
{WHERE
	(AdditionalExpenses.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	EmployeeExpenseReport.Ref,
	VALUETYPE(EmployeeExpenseReport.Ref),
	CASE
		WHEN EmployeeExpenseReport.Posted
			THEN 1
		WHEN EmployeeExpenseReport.DeletionMark
			THEN 2
		ELSE 0
	END,
	EmployeeExpenseReport.Number,
	EmployeeExpenseReport.Date,
	EmployeeExpenseReport.Employee,
	EmployeeExpenseReport.DocumentAmount,
	EmployeeExpenseReport.Author,
	EmployeeExpenseReport.DocumentCurrency,
	CAST(EmployeeExpenseReport.Comment AS STRING(200)),
	"-"
FROM
	Document.EmployeeExpenseReport AS EmployeeExpenseReport
WHERE
	VALUETYPE(EmployeeExpenseReport.Ref) IN (&DocumentsListSelectedTypes)
	AND EmployeeExpenseReport.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.EmployeeExpenseReport))
{WHERE
	(EmployeeExpenseReport.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	CustomerOrder.Ref,
	VALUETYPE(CustomerOrder.Ref),
	CASE
		WHEN CustomerOrder.Posted
			THEN 1
		WHEN CustomerOrder.DeletionMark
			THEN 2
		ELSE 0
	END,
	CustomerOrder.Number,
	CustomerOrder.Date,
	CustomerOrder.Company,
	CustomerOrder.DocumentAmount,
	CustomerOrder.Author,
	CustomerOrder.DocumentCurrency,
	CAST(CustomerOrder.Comment AS STRING(200)),
	CustomerOrder.TransactionType
FROM
	Document.CustomerOrder AS CustomerOrder
WHERE
	VALUETYPE(CustomerOrder.Ref) IN (&DocumentsListSelectedTypes)
	AND CustomerOrder.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.CustomerOrder))
{WHERE
	(CustomerOrder.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	InventoryExpense.Ref,
	VALUETYPE(InventoryExpense.Ref),
	CASE
		WHEN InventoryExpense.Posted
			THEN 1
		WHEN InventoryExpense.DeletionMark
			THEN 2
		ELSE 0
	END,
	InventoryExpense.Number,
	InventoryExpense.Date,
	InventoryExpense.Company,
	InventoryExpense.DocumentAmount,
	InventoryExpense.Author,
	InventoryExpense.DocumentCurrency,
	CAST(InventoryExpense.Comment AS STRING(200)),
	InventoryExpense.TransactionType
FROM
	Document.InventoryExpense AS InventoryExpense
WHERE
	VALUETYPE(InventoryExpense.Ref) IN (&DocumentsListSelectedTypes)
	AND InventoryExpense.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.InventoryExpense))
{WHERE
	(InventoryExpense.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	InventoryReceipt.Ref,
	VALUETYPE(InventoryReceipt.Ref),
	CASE
		WHEN InventoryReceipt.Posted
			THEN 1
		WHEN InventoryReceipt.DeletionMark
			THEN 2
		ELSE 0
	END,
	InventoryReceipt.Number,
	InventoryReceipt.Date,
	InventoryReceipt.Company,
	InventoryReceipt.DocumentAmount,
	InventoryReceipt.Author,
	InventoryReceipt.DocumentCurrency,
	CAST(InventoryReceipt.Comment AS STRING(200)),
	InventoryReceipt.TransactionType
FROM
	Document.InventoryReceipt AS InventoryReceipt
WHERE
	VALUETYPE(InventoryReceipt.Ref) IN (&DocumentsListSelectedTypes)
	AND InventoryReceipt.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.InventoryReceipt))
{WHERE
	(InventoryReceipt.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	ChequeExpense.Ref,
	VALUETYPE(ChequeExpense.Ref),
	CASE
		WHEN ChequeExpense.Posted
			THEN 1
		WHEN ChequeExpense.DeletionMark
			THEN 2
		ELSE 0
	END,
	ChequeExpense.Number,
	ChequeExpense.Date,
	ChequeExpense.Company,
	ChequeExpense.DocumentAmount,
	ChequeExpense.Author,
	ChequeExpense.CashCurrency,
	CAST(ChequeExpense.Comment AS STRING(200)),
	ChequeExpense.TransactionType
FROM
	Document.ChequeExpense AS ChequeExpense
WHERE
	VALUETYPE(ChequeExpense.Ref) IN (&DocumentsListSelectedTypes)
	AND ChequeExpense.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.ChequeExpense))
{WHERE
	(ChequeExpense.Date >= &DateBegin),
	(ChequeExpense.Date <= &DateEnd)}

UNION ALL

SELECT
	ChequeReceipt.Ref,
	VALUETYPE(ChequeReceipt.Ref),
	CASE
		WHEN ChequeReceipt.Posted
			THEN 1
		WHEN ChequeReceipt.DeletionMark
			THEN 2
		ELSE 0
	END,
	ChequeReceipt.Number,
	ChequeReceipt.Date,
	ChequeReceipt.Company,
	ChequeReceipt.DocumentAmount,
	ChequeReceipt.Author,
	ChequeReceipt.CashCurrency,
	CAST(ChequeReceipt.Comment AS STRING(200)),
	ChequeReceipt.TransactionType
FROM
	Document.ChequeReceipt AS ChequeReceipt
WHERE
	VALUETYPE(ChequeReceipt.Ref) IN (&DocumentsListSelectedTypes)
	AND ChequeReceipt.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.ChequeReceipt))
{WHERE
	(ChequeReceipt.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	PaymentExpense.Ref,
	VALUETYPE(PaymentExpense.Ref),
	CASE
		WHEN PaymentExpense.Posted
			THEN 1
		WHEN PaymentExpense.DeletionMark
			THEN 2
		ELSE 0
	END,
	PaymentExpense.Number,
	PaymentExpense.Date,
	PaymentExpense.Company,
	PaymentExpense.DocumentAmount,
	PaymentExpense.Author,
	PaymentExpense.CashCurrency,
	CAST(PaymentExpense.Comment AS STRING(200)),
	PaymentExpense.TransactionType
FROM
	Document.PaymentExpense AS PaymentExpense
WHERE
	VALUETYPE(PaymentExpense.Ref) IN (&DocumentsListSelectedTypes)
	AND PaymentExpense.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.PaymentExpense))
{WHERE
	(PaymentExpense.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	PaymentReceipt.Ref,
	VALUETYPE(PaymentReceipt.Ref),
	CASE
		WHEN PaymentReceipt.Posted
			THEN 1
		WHEN PaymentReceipt.DeletionMark
			THEN 2
		ELSE 0
	END,
	PaymentReceipt.Number,
	PaymentReceipt.Date,
	PaymentReceipt.Company,
	PaymentReceipt.DocumentAmount,
	PaymentReceipt.Author,
	PaymentReceipt.CashCurrency,
	CAST(PaymentReceipt.Comment AS STRING(200)),
	PaymentReceipt.TransactionType
FROM
	Document.PaymentReceipt AS PaymentReceipt
WHERE
	VALUETYPE(PaymentReceipt.Ref) IN (&DocumentsListSelectedTypes)
	AND PaymentReceipt.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.PaymentReceipt))
{WHERE
	(PaymentReceipt.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	PettyCashExpense.Ref,
	VALUETYPE(PettyCashExpense.Ref),
	CASE
		WHEN PettyCashExpense.Posted
			THEN 1
		WHEN PettyCashExpense.DeletionMark
			THEN 2
		ELSE 0
	END,
	PettyCashExpense.Number,
	PettyCashExpense.Date,
	PettyCashExpense.Company,
	PettyCashExpense.DocumentAmount,
	PettyCashExpense.Author,
	PettyCashExpense.CashCurrency,
	CAST(PettyCashExpense.Comment AS STRING(200)),
	PettyCashExpense.TransactionType
FROM
	Document.PettyCashExpense AS PettyCashExpense
WHERE
	VALUETYPE(PettyCashExpense.Ref) IN (&DocumentsListSelectedTypes)
	AND PettyCashExpense.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.PettyCashExpense))
{WHERE
	(PettyCashExpense.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	PettyCashReceipt.Ref,
	VALUETYPE(PettyCashReceipt.Ref),
	CASE
		WHEN PettyCashReceipt.Posted
			THEN 1
		WHEN PettyCashReceipt.DeletionMark
			THEN 2
		ELSE 0
	END,
	PettyCashReceipt.Number,
	PettyCashReceipt.Date,
	PettyCashReceipt.Company,
	PettyCashReceipt.DocumentAmount,
	PettyCashReceipt.Author,
	PettyCashReceipt.CashCurrency,
	CAST(PettyCashReceipt.Comment AS STRING(200)),
	PettyCashReceipt.TransactionType
FROM
	Document.PettyCashReceipt AS PettyCashReceipt
WHERE
	VALUETYPE(PettyCashReceipt.Ref) IN (&DocumentsListSelectedTypes)
	AND PettyCashReceipt.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.PettyCashReceipt))
{WHERE
	(PettyCashReceipt.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	PurchaseOrder.Ref,
	VALUETYPE(PurchaseOrder.Ref),
	CASE
		WHEN PurchaseOrder.Posted
			THEN 1
		WHEN PurchaseOrder.DeletionMark
			THEN 2
		ELSE 0
	END,
	PurchaseOrder.Number,
	PurchaseOrder.Date,
	PurchaseOrder.Company,
	PurchaseOrder.DocumentAmount,
	PurchaseOrder.Author,
	PurchaseOrder.DocumentCurrency,
	CAST(PurchaseOrder.Comment AS STRING(200)),
	PurchaseOrder.TransactionType
FROM
	Document.PurchaseOrder AS PurchaseOrder
WHERE
	VALUETYPE(PurchaseOrder.Ref) IN (&DocumentsListSelectedTypes)
	AND PurchaseOrder.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.PurchaseOrder))
{WHERE
	(PurchaseOrder.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	Quotation.Ref,
	VALUETYPE(Quotation.Ref),
	CASE
		WHEN Quotation.Posted
			THEN 1
		WHEN Quotation.DeletionMark
			THEN 2
		ELSE 0
	END,
	Quotation.Number,
	Quotation.Date,
	Quotation.Company,
	Quotation.DocumentAmount,
	Quotation.Author,
	Quotation.DocumentCurrency,
	CAST(Quotation.Comment AS STRING(200)),
	"-"
FROM
	Document.Quotation AS Quotation
WHERE
	VALUETYPE(Quotation.Ref) IN (&DocumentsListSelectedTypes)
	AND Quotation.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.Quotation))
{WHERE
	(Quotation.Date BETWEEN &DateBegin AND &DateEnd)}

UNION ALL

SELECT
	ProjectCostsAllocation.Ref,
	VALUETYPE(ProjectCostsAllocation.Ref),
	CASE
		WHEN ProjectCostsAllocation.Posted
			THEN 1
		WHEN ProjectCostsAllocation.DeletionMark
			THEN 2
		ELSE 0
	END,
	ProjectCostsAllocation.Number,
	ProjectCostsAllocation.Date,
	NULL,
	0,
	ProjectCostsAllocation.Author,
	VALUE(Catalog.Currencies.EmptyRef),
	CAST(ProjectCostsAllocation.Comment AS STRING(200)),
	ProjectCostsAllocation.TransactionType
FROM
	Document.ProjectCostsAllocation AS ProjectCostsAllocation
WHERE
	VALUETYPE(ProjectCostsAllocation.Ref) IN (&DocumentsListSelectedTypes)
	AND ProjectCostsAllocation.Ref IN
			(SELECT
				DocumentsByProject.Ref
			FROM
				FilterCriterion.DocumentsByProject(&Project) AS DocumentsByProject
			WHERE
				VALUETYPE(DocumentsByProject.Ref) = TYPE(Document.ProjectCostsAllocation))
{WHERE
	(ProjectCostsAllocation.Date BETWEEN &DateBegin AND &DateEnd)}