ЖурналПоЗВ385

WITH un AS (
SELECT
        CLAIM.R_DECL_DEB_REFUND_ID,
        CLAIM.NUM_DECLARATION,
        CLAIM.DATE_DECL,
        claim.statement_type,
        claim.source_system,
        TAX_ORG_SRC.CODE_NK,
        claim.bank_account_ret,
        TAX_ORG_ADM_INFO_DST.iin_bin,
        TAX_ORG_ADM_INFO_DST.RNN,
        CLAIM.AMOUNT,
        claim.status_decl,
        claim.date_process,
        CLAIM.ERROR_DESC,
        CLAIM.DECLINE_CODE,
        CLAIM.R_TAX_PAYER_DECLARANT_ID,
        CLAIM.M_KBK_SRC_ID,
        CLAIM.M_ASSIGNMENT_PAYMENT_ID,
        CLAIM.M_KBK_DST_ID,
        CLAIM.M_OPERATION_TYPE_DST_ID,
        claim.m_bank_id
FROM            R_DECL_DEB_REFUND           CLAIM
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_SRC      ON CLAIM.M_TAX_ORG_ADM_INFO_SRC_ID      = TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_SRC               ON TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ID    = TAX_ORG_SRC.M_TAX_ORG_ID
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_DST      ON CLAIM.M_TAX_ORG_ADM_INFO_DST_ID      = TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_DST               ON TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ID    = TAX_ORG_DST.M_TAX_ORG_ID
WHERE
claim.r_tax_payer_declarant_id = 19372754 AND claim.source_system IN (0, 1, 2, 3, 4, 5 ,6, 7) AND TAX_ORG_SRC.M_TAX_ORG_ID IN (SELECT MT.M_TAX_ORG_ID FROM M_TAX_ORG MT START WITH MT.M_TAX_ORG_ID = 383 CONNECT BY MT.PARENT_TAX_ORG_ID = PRIOR M_TAX_ORG_ID AND MT.IS_ACTIVE = 1) AND claim.status_decl IN (6, 9, 8, 5, 7, 10, 11)
UNION ALL
SELECT
        CLAIM.R_DECL_DEB_REFUND_ID,
        CLAIM.NUM_DECLARATION,
        CLAIM.DATE_DECL,
        claim.statement_type,
        claim.source_system,
        TAX_ORG_SRC.CODE_NK,
        claim.bank_account_ret,
        TAX_ORG_ADM_INFO_DST.iin_bin,
        TAX_ORG_ADM_INFO_DST.RNN,
        CLAIM.AMOUNT,
        claim.status_decl,
        claim.date_process,
        CLAIM.ERROR_DESC,
        CLAIM.DECLINE_CODE,
        CLAIM.R_TAX_PAYER_DECLARANT_ID,
        CLAIM.M_KBK_SRC_ID,
        CLAIM.M_ASSIGNMENT_PAYMENT_ID,
        CLAIM.M_KBK_DST_ID,
        CLAIM.M_OPERATION_TYPE_DST_ID,
        claim.m_bank_id
FROM            R_DECL_DEB_REFUND           CLAIM
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_SRC      ON CLAIM.M_TAX_ORG_ADM_INFO_SRC_ID      = TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_SRC               ON TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ID    = TAX_ORG_SRC.M_TAX_ORG_ID
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_DST      ON CLAIM.M_TAX_ORG_ADM_INFO_DST_ID      = TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_DST               ON TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ID    = TAX_ORG_DST.M_TAX_ORG_ID
WHERE
claim.r_tax_payer_declarant_id = 19372754 AND claim.source_system IN (0, 1, 2, 3, 4, 5 ,6, 7) AND CLAIM.M_TAX_ORG_FRMD_ID = 1 AND TAX_ORG_DST.M_TAX_ORG_ID IN (SELECT MT.M_TAX_ORG_ID FROM M_TAX_ORG MT START WITH MT.M_TAX_ORG_ID = 385 CONNECT BY MT.PARENT_TAX_ORG_ID = PRIOR M_TAX_ORG_ID AND MT.IS_ACTIVE = 1) AND claim.status_decl IN (6, 9, 8, 5, 7, 10, 11))
SELECT
        un.R_DECL_DEB_REFUND_ID,
        un.NUM_DECLARATION,
        un.DATE_DECL,
        un.statement_type,
        un.source_system,
        un.CODE_NK,
        tp_info.iin_bin,
        tp_info.rnn,
        TP_INFO.NAME_R,
        TP_INFO.NAME_K,
        TP_INFO.NAME_E,
        TP_INFO.TYPE_TP,
        KBK_SRC.KBK_CODE,
        NVL(bank.bik, bank.head_bik),
        un.bank_account_ret,
        kbk_dst.kbk_code,
        operation_type_dst.name_r,
        operation_type_dst.name_k,
        un.iin_bin,
        un.RNN,
        un.AMOUNT,
        assign_payment.payment_code,
        un.status_decl,
        un.date_process,
        un.ERROR_DESC,
        un.DECLINE_CODE,
         court.judge,
         court.doc_num
FROM un
LEFT OUTER JOIN V_TP_NAME_INFO              TP_INFO                   ON un.R_TAX_PAYER_DECLARANT_ID       = TP_INFO.R_TAX_PAYER_ID
LEFT OUTER JOIN M_KBK                       KBK_SRC                   ON KBK_SRC.M_KBK_ID                     = un.M_KBK_SRC_ID
LEFT OUTER JOIN M_ASSIGNMENT_PAYMENT        ASSIGN_PAYMENT            ON un.M_ASSIGNMENT_PAYMENT_ID        = ASSIGN_PAYMENT.M_ASSIGNMENT_PAYMENT_ID
LEFT OUTER JOIN M_KBK                       KBK_DST                   ON un.M_KBK_DST_ID                   = KBK_DST.M_KBK_ID
LEFT OUTER JOIN M_OPERATION_TYPE            OPERATION_TYPE_DST        ON un.M_OPERATION_TYPE_DST_ID        = OPERATION_TYPE_DST.M_OPERATION_TYPE_ID
LEFT OUTER JOIN (SELECT bank.m_bank_id, MAX(bank.bik) AS bik, MAX(head_bank.bik) AS head_bik
                FROM m_bank bank
                left outer join r_tax_payer   tp        ON bank.r_tax_payer_id          = tp.r_tax_payer_id
                left outer join r_tp_main     tp_main   ON tp.r_tp_main_id              = tp_main.r_tp_main_id
                left outer join m_bank        head_bank ON tp_main.r_tax_payer_head_id  = head_bank.r_tax_payer_id
                GROUP BY bank.m_bank_id)    bank                      ON un.m_bank_id                      = bank.m_bank_id
left outer join r_decl_deb_court            court                     ON court.r_decl_deb_refund_id           = un.r_decl_deb_refund_id

ORDER BY un.num_declaration ASC;