SELECT
bd_bankaccbas.pk_bankaccbas AS id,
bd_bankaccbas.CODE AS CODE,
bd_bankaccbas.NAME AS NAME,
bd_custbank.pk_cust AS pk_supplier,
bd_bankaccbas.accnum AS account,
bd_bankaccbas.accname AS acctname,
bd_bankaccbas.pk_banktype AS bank,
bd_bankaccbas.pk_bankdoc AS bankname,
bd_bankaccbas.province AS bankprovince,
bd_bankdoc.combinenum AS linenumber,
bd_bankaccsub.pk_currtype AS currency,
bd_supplier.pk_country as country,
CASE
WHEN bd_custbank.isdefault = 'Y' THEN 1
ELSE 0
END AS isdefault,
bd_bankaccbas.memo AS description,
bd_bankaccbas.dr as dr,
bd_bankaccbas.ts as synchts,
bd_custbank.pk_custbank AS objid,
bd_bankaccbas.accountproperty as acctproperty,
bd_bankaccbas.enablestate AS enable
FROM
bd_custbank
INNER JOIN bd_bankaccbas ON bd_custbank.pk_bankaccbas = bd_bankaccbas.pk_bankaccbas
LEFT JOIN bd_bankaccsub ON bd_custbank.pk_bankaccsub = bd_bankaccsub.pk_bankaccsub
left join bd_supplier on bd_supplier.pk_supplier_v = bd_custbank.pk_cust
left join bd_bankdoc on bd_bankdoc.pk_bankdoc = bd_bankaccbas.pk_bankdoc
WHERE
bd_custbank.pk_cust in (
select
pk_supplier
from
bd_supplier
where
(
pk_org = 'GLOBLE00000000000000' ----所属组织是全局
OR pk_group IN (
SELECT
pk_group
FROM
org_group
WHERE
CODE = @groupcode   ----------------或所属集团是水泥
)
)
and (
bd_supplier.pk_supplier in (
select
pk_supplier
from
bd_suporg   --------------------------供应商分配关系
where
pk_org in (
'00011110000000005FG3',
'000111100000000EURMU',
'00011110000000005FPV',
'00011110000000005FIF'
)
)
and bd_supplier.supprop = 0    -----------0=外部单位;1=内部单位;
)
)
and bd_bankaccbas.dr = 0       ------------客商银行账户信息删除标识
and bd_custbank.accclass = 3 -----供应商银行账号-账户分类	0=个人;1=客户;2=公司;3=供应商;
and bd_supplier.pk_country is not null   ------供应商国家不能为空
and bd_bankdoc.combinenum is not null    ------银行档案联行号不能为空
and bd_bankaccbas.enablestate = '2'   -----客商银行账户信息 1=未启用;2=已启用;3=已停用;