SELECT
a.fiscal_year,
a.tenant_id,
a.mof_dep_code,
a.bgt_id,
bbi.cor_bgt_doc_no,
a.agency_code,
a.pro_code,
a.pro_name,
a.fund_type_code,
a.exp_func_code,
a.dep_bgt_eco_code,
a.gov_bgt_eco_code,
a.pay_type_code,
a.set_mode_code,
bbi.amount,
NULL AS 'gatherBankCode',
a.payee_acct_bank_name,
a.payee_acct_no,
NULL AS 'gatherBankacctName',
NULL AS 'paymentBankCode',
a.pay_acct_bank_name,
a.pay_acct_no,
a.pay_acct_name,
NULL AS 'acctTpye',
a.pay_alloc_cert_id,
a.pay_alloc_cert_no,
NULL AS 'sourceguid',
a.pay_amt,
a.exp_pay_date,
a.USE,
NULL AS 'sysId',
UNIX_TIMESTAMP( NOW( ) ),
JSON_EXTRACT ( bbi.manage_categories, '$.isPovery' ),
JSON_EXTRACT ( bbi.manage_categories, '$.isDirectFund' ),
JSON_EXTRACT ( bbi.manage_categories, '$.isIndAndEnterSub' ),
JSON_EXTRACT ( bbi.manage_categories, '$.isDirectAndSuppFund')
FROM
(
SELECT
pac.*
FROM
pay_allocation_cert pac
INNER JOIN pay_appr_apply paa ON pac.pay_alloc_cert_id = paa.pay_alloc_cert_id
WHERE
pac.pay_alloc_cert_id NOT IN ( SELECT pay_alloc_cert_id FROM pay_allocation_cert WHERE JSON_EXTRACT ( extend, '$."PovertyAlleviationStatus"' ) = '1' )
AND pac.is_delete = 0
AND pac.is_deleted = 0
AND paa.is_delete = 0
AND paa.is_deleted = 0
AND pac.vt_status = '12'
) AS a,
bmp_quota.ba_bgt_info AS bbi
WHERE
bbi.bgt_id = a.bgt_id
AND bbi.is_deleted = 0
AND bbi.FISCAL_YEAR = 2021
AND bbi.MOF_DIV_CODE = '440000000'
AND a.fiscal_year = 2021
AND a.mof_dep_code = '440000000'
