view

CREATE OR REPLACE VIEW public.company_card_combined_data_payment_orders_output_part AS
            WITH root_company_id AS (
            SELECT companies_group_types.company_id AS id
            FROM companies_group_types
            WHERE companies_group_types.group_company_type = 'PARTNER' :: TEXT
            UNION
            SELECT companies.id
            FROM companies
            WHERE companies.is_root = TRUE
            ), salary_orders AS (
            SELECT
            payment_order_1.id      AS order_id,
            ((SELECT payment_request_article.id
            FROM payment_request_article
            JOIN article_expense ON article_expense.id = payment_request_article.article_expense_id
            WHERE payment_request_article.status = 'ACTIVE' :: TEXT AND
            payment_request_article.payment_order_id = payment_order_1.id AND
            article_expense.status = 'ACTIVE' :: TEXT AND article_expense.ignore_in_company_card IS NOT NULL AND
            article_expense.ignore_in_company_card AND (payment_order_1.recipient_id IN (SELECT root_company_id.id
            FROM root_company_id))
            LIMIT 1)) IS NOT NULL AS is_salary
            FROM payment_order payment_order_1
            )
            SELECT
            payment_order.id,
            COALESCE(payment_order.performed_time, payment_order.plan_date) AS time_fact,
            payment_order.update_time,
            0                                                               AS debit,
            COALESCE(payment_order.sum, 0 :: NUMERIC)                       AS credit,
            -COALESCE(payment_order.sum, 0 :: NUMERIC)                      AS value,
            payment_order.comments                                          AS comment,
            payment_order.id                                                AS payment_order_id,
            CASE
            WHEN COALESCE(salary_orders.is_salary, FALSE)
            THEN NULL :: UUID
            ELSE payment_order.recipient_id
            END                                                             AS company_id,
            payment_order.root_owner_id,
            payment_order.number                                            AS document_name,
            'payment_order' :: TEXT                                         AS entity_type,
            payment_order.id                                                AS entity_id,
            CASE
            WHEN account.id IS NULL
            THEN NULL :: UUID
            ELSE payment_order.recipient_account_id
            END                                                             AS contractor_account_id,
            COALESCE(salary_orders.is_salary, FALSE)                        AS is_salary,
            payment_order.report_period,
            payment_order.report_period_to,
            payment_order.period_type,
            payment_order.condition                                         AS payment_order_condition,
            payment_order.payer_id,
            payment_order.create_time AS time_create
            FROM payment_order
            LEFT JOIN account
            ON account.id = payment_order.payer_account_id AND account.account_type != 'bank_account' :: TEXT AND
            (account.account_type != 'cash_account' :: TEXT OR account.cashbox IS NULL OR account.cashbox = FALSE)
            LEFT JOIN salary_orders ON salary_orders.order_id = payment_order.id
            WHERE payment_order.condition != ALL (ARRAY ['CANCELED' :: TEXT, 'PERFORMED' :: TEXT])
            OR (payment_order.condition = 'PERFORMED' AND NOT EXISTS(
            SELECT
            FROM finance_transactions
            WHERE finance_transactions.payment_order_id = payment_order.id
            AND finance_transactions.contractor_id = payment_order.recipient_id
            AND finance_transactions.main_company_id =
            payment_order.payer_id
            )
            );