|
|
header 层没有问题, 我改了下地址层才出来数据
SELECT ASP.VENDOR_ID AS 供应商ID,
ASP.VENDOR_NAME AS 供应商名称,
ASP.SEGMENT1 AS 供应商编号,
ASP.VENDOR_TYPE_LOOKUP_CODE AS 供应商类型,
ASP.START_DATE_ACTIVE AS 供应商起始日期,
ASP.ENABLED_FLAG AS 供应商启用标识,
ASP.END_DATE_ACTIVE AS 供应商终止日期,
ASP.PARTY_ID,
IEB.ext_bank_account_id,
IEB.bank_party_id,
IEB.bank_name AS 银行,
IEB.bank_branch_name AS 分行,
IEB.branch_party_id AS 分行id,
IEB.bank_account_id,
IEB.bank_account_number AS 银行账户,
IEB.primary_acct_owner_party_id AS 账户主要责任人ID,
IEB.primary_acct_owner_name AS 账户主要责任人,
IAO.END_DATE AS 账户责任人终止日期,
IAO.PRIMARY_FLAG AS 账户主要责任人标识,
IEB.start_date AS 银行起始日期,
IEB.end_date AS 银行终止日期,
(SELECT T.Start_Date
FROM apps.IBY_EXT_BANK_BRANCHES_V T
WHERE T.Branch_Party_Id = IEB.branch_party_id) AS 分行起始日期,
(SELECT T.End_Date
FROM apps.IBY_EXT_BANK_BRANCHES_V T
WHERE T.Branch_Party_Id = IEB.branch_party_id) AS 分行终止日期,
IEP.PAYEE_PARTY_ID,
IEP.PAYMENT_FUNCTION,
IEP.PARTY_SITE_ID,
NULL AS 地址名称,
NULL AS 地址是否有效,
IEP.ORG_ID AS 业务实体ID,
NULL AS 业务实体名称,
NULL AS 业务实体生效日期,
NULL AS 业务实体失效日期,
IEP.SUPPLIER_SITE_ID,
NULL,
NULL,
USES.INSTRUMENT_PAYMENT_USE_ID,
USES.EXT_PMT_PARTY_ID,
USES.INSTRUMENT_ID,
USES.PAYMENT_FUNCTION,
USES.START_DATE AS 银行账户起始日期,
USES.END_DATE AS 银行账户终止日期
FROM apps.AP_SUPPLIERS ASP,
apps.IBY_EXT_BANK_ACCOUNTS_V IEB,
apps.IBY_EXTERNAL_PAYEES_ALL IEP,
apps.IBY_ACCOUNT_OWNERS IAO,
apps.IBY_PMT_INSTR_USES_ALL USES
WHERE 1 = 1
AND IEP.EXT_PAYEE_ID = USES.EXT_PMT_PARTY_ID
AND IEP.PAYMENT_FUNCTION = 'PAYABLES_DISB'
AND USES.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEP.PAYEE_PARTY_ID = ASP.PARTY_ID
AND IEP.PARTY_SITE_ID IS NULL
AND IEP.SUPPLIER_SITE_ID IS NULL
AND IEP.ORG_ID IS NULL
-- AND ASP.VENDOR_TYPE_LOOKUP_CODE='VENDOR'
AND USES.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND IAO.ACCOUNT_OWNER_PARTY_ID = ASP.PARTY_ID
AND IAO.EXT_BANK_ACCOUNT_ID(+) = IEB.ext_bank_account_id
and asp.vendor_id in
(select distinct ap.vendor_id from apps.AP_SUPPLIERS ap, apps.AP_SUPPLIER_sites_all aps
where ap.vendor_id=aps.vendor_id
and aps.org_id=963)
--AND ASP.VENDOR_NAME='&VENDOR_NAME'
--and asp.segment1 = '620998'
UNION ALL
--第二层（地址关联银行）
SELECT ASP.VENDOR_ID AS 供应商ID,
ASP.VENDOR_NAME AS 供应商名称,
ASP.SEGMENT1 AS 供应商编号,
ASP.VENDOR_TYPE_LOOKUP_CODE AS 供应商类型,
ASP.START_DATE_ACTIVE AS 供应商起始日期,
ASP.ENABLED_FLAG AS 供应商启用标识,
ASP.END_DATE_ACTIVE AS 供应商终止日期,
ASP.PARTY_ID,
IEB.ext_bank_account_id,
IEB.bank_party_id,
IEB.bank_name AS 银行,
IEB.bank_branch_name AS 分行,
IEB.branch_party_id AS 分行id,
IEB.bank_account_id,
IEB.bank_account_number AS 银行账户,
IEB.primary_acct_owner_party_id AS 账户主要责任人ID,
IEB.primary_acct_owner_name AS 账户主要责任人,
IAO.END_DATE AS 账户责任人终止日期,
IAO.PRIMARY_FLAG AS 账户主要责任人标识,
IEB.start_date AS 银行起始日期,
IEB.end_date AS 银行终止日期,
(SELECT T.Start_Date
FROM apps.IBY_EXT_BANK_BRANCHES_V T
WHERE T.Branch_Party_Id = IEB.branch_party_id) AS 分行起始日期,
(SELECT T.End_Date
FROM apps.IBY_EXT_BANK_BRANCHES_V T
WHERE T.Branch_Party_Id = IEB.branch_party_id) AS 分行终止日期,
IEP.PAYEE_PARTY_ID,
IEP.PAYMENT_FUNCTION,
IEP.PARTY_SITE_ID,
(SELECT HPS.PARTY_SITE_NAME
FROM apps.HZ_PARTY_SITES HPS
WHERE IEP.PARTY_SITE_ID = HPS.PARTY_SITE_ID
--AND HPS.STATUS='A'
AND EXISTS
(SELECT 1
FROM apps.HZ_PARTY_SITES HPS,
apps.hz_party_site_uses PURCHASE,
apps.hz_party_site_uses PAY
WHERE HPS.PARTY_SITE_ID = PURCHASE.PARTY_SITE_ID
AND HPS.PARTY_SITE_ID = PAY.PARTY_SITE_ID
AND PURCHASE.SITE_USE_TYPE = 'PURCHASING'
AND PAY.SITE_USE_TYPE = 'PAY')) AS 地址名称,
DECODE((SELECT HPS.STATUS
FROM apps.HZ_PARTY_SITES HPS
WHERE IEP.PARTY_SITE_ID = HPS.PARTY_SITE_ID
--AND HPS.STATUS='A'
AND EXISTS
(SELECT 1
FROM apps.HZ_PARTY_SITES HPS,
apps.hz_party_site_uses PURCHASE,
apps.hz_party_site_uses PAY
WHERE HPS.PARTY_SITE_ID = PURCHASE.PARTY_SITE_ID
AND HPS.PARTY_SITE_ID = PAY.PARTY_SITE_ID
AND PURCHASE.SITE_USE_TYPE = 'PURCHASING'
AND PAY.SITE_USE_TYPE = 'PAY')),
'A',
'有效',
'无效') AS 地址是否有效,
IEP.ORG_ID,
NULL AS 业务实体,
NULL AS 业务实体生效日期,
NULL AS 业务实体失效日期,
IEP.SUPPLIER_SITE_ID,
NULL,
NULL,
USES.INSTRUMENT_PAYMENT_USE_ID,
USES.EXT_PMT_PARTY_ID,
USES.INSTRUMENT_ID,
USES.PAYMENT_FUNCTION,
USES.START_DATE AS 银行账户起始日期,
USES.END_DATE AS 银行账户终止日期
FROM apps.AP_SUPPLIERS ASP,
apps.IBY_EXT_BANK_ACCOUNTS_V IEB,
apps.IBY_EXTERNAL_PAYEES_ALL IEP,
apps.IBY_ACCOUNT_OWNERS IAO,
apps.IBY_PMT_INSTR_USES_ALL USES
WHERE 1 = 1
AND IEP.EXT_PAYEE_ID = USES.EXT_PMT_PARTY_ID
AND IEP.PAYMENT_FUNCTION = 'PAYABLES_DISB'
AND USES.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEP.PAYEE_PARTY_ID = ASP.PARTY_ID
AND IEP.PARTY_SITE_ID IS NOT NULL
AND IEP.SUPPLIER_SITE_ID IS not NULL
AND IEP.ORG_ID = 963
-- AND ASP.VENDOR_TYPE_LOOKUP_CODE='VENDOR'
AND USES.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND IAO.ACCOUNT_OWNER_PARTY_ID = ASP.PARTY_ID
AND IAO.EXT_BANK_ACCOUNT_ID(+) = IEB.ext_bank_account_id
-- and asp.segment1 = '620998'
-- AND ASP.VENDOR_NAME='&VENDOR_NAME';
; |
|
楼主: b1uepanda
IP卡
狗仔卡
发表于 2013-9-23 10:54
显身卡
........
楼主
