oracle业务实体表,地址业务实体关联银行

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

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 IBY_EXT_BANK_BRANCHES_V T WHERE T.Branch_Party_Id=IEB.branch_party_id) AS 分行起始日期, (SELECT T.End_Date FROM 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 HZ_PARTY_SITES HPS WHERE IEP.PARTY_SITE_ID=HPS.PARTY_SITE_ID --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM HZ_PARTY_SITES HPS,hz_party_site_uses PURCHASE,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 HZ_PARTY_SITES HPS WHERE IEP.PARTY_SITE_ID=HPS.PARTY_SITE_ID --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM HZ_PARTY_SITES HPS,hz_party_site_uses PURCHASE,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, (SELECT TRIM(SUBSTR(HOU.name,4)) FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.organization_id=IEP.ORG_ID) --AND SYSDATE BETWEEN NVL(HOU.DATE_FROM,SYSDATE-1) AND NVL(HOU.DATE_TO,SYSDATE+1) AS 业务实体, (SELECT HOU.date_from FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.organization_id=IEP.ORG_ID)AS 业务实体生效日期, (SELECT HOU.date_to FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.organization_id=IEP.ORG_ID) AS 业务实体失效日期, IEP.SUPPLIER_SITE_ID, NULL AS 地点, NULL AS 地点失效日期, 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 AP_SUPPLIERS ASP, IBY_EXT_BANK_ACCOUNTS_V IEB, IBY_EXTERNAL_PAYEES_ALL IEP, IBY_ACCOUNT_OWNERS IAO, 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 NULL AND IEP.ORG_ID IS NOT NULL AND USES.INSTRUMENT_TYPE = 'BANKACCOUNT' AND ASP.VENDOR_TYPE_LOOKUP_CODE='VENDOR' AND IAO.ACCOUNT_OWNER_PARTY_ID=ASP.PARTY_ID AND IAO.EXT_BANK_ACCOUNT_ID(+)=IEB.ext_bank_account_id AND ASP.VENDOR_NAME='&VENDOR_NAME'