获取财务系统设置审批链相关信息SQL

SELECT DISTINCT PS.POSITION_STRUCTURE_ID    审批链ID,
                PS.NAME                     审批链名称,
                PE.POS_STRUCTURE_ELEMENT_ID POS_STRUCTURE_ELEMENT_ID,
                HP.POSITION_ID              职位ID,
                HP.NAME                     职位名,
                PPF.PERSON_ID               员工ID,
                PPF.LAST_NAME               用户名,
                PE.ORDER_FLAG               ORDER_FLAG
  FROM PER_POS_STRUCTURE_VERSIONS PV,
       PER_POSITION_STRUCTURES PS,
       (SELECT POS_STRUCTURE_VERSION_ID,
               POS_STRUCTURE_ELEMENT_ID,
               PARENT_POSITION_ID,
               ORDER_FLAG
          FROM (SELECT PE.POS_STRUCTURE_VERSION_ID,
                       PE.POS_STRUCTURE_ELEMENT_ID,
                       PE.PARENT_POSITION_ID,
                       '1' ORDER_FLAG
                  FROM PER_POS_STRUCTURE_ELEMENTS PE
                UNION ALL
                SELECT PE.POS_STRUCTURE_VERSION_ID,
                       PE.POS_STRUCTURE_ELEMENT_ID,
                       PE.SUBORDINATE_POSITION_ID PARENT_POSITION_ID,
                       '2' ORDER_FLAG
                  FROM PER_POS_STRUCTURE_ELEMENTS PE
                 WHERE NOT EXISTS (SELECT 1
                          FROM PER_POS_STRUCTURE_ELEMENTS PE2
                         WHERE PE2.POS_STRUCTURE_VERSION_ID =
                               PE.POS_STRUCTURE_VERSION_ID
                           AND PE.SUBORDINATE_POSITION_ID =
                               PE2.PARENT_POSITION_ID))) PE,
       PER_PEOPLE_F PPF,
       PER_ALL_ASSIGNMENTS_F PAF,
       HR_POSITIONS_F HP
 WHERE PV.POS_STRUCTURE_VERSION_ID = PE.POS_STRUCTURE_VERSION_ID
   AND PV.POSITION_STRUCTURE_ID = PS.POSITION_STRUCTURE_ID
   AND PPF.PERSON_ID = PAF.PERSON_ID
   AND PE.PARENT_POSITION_ID = HP.POSITION_ID
   AND HP.POSITION_ID = PAF.POSITION_ID
   AND (PAF.EFFECTIVE_END_DATE IS NULL OR PAF.EFFECTIVE_END_DATE > SYSDATE)
   AND (HP.EFFECTIVE_END_DATE IS NULL OR HP.EFFECTIVE_END_DATE > SYSDATE)
   AND (PPF.EFFECTIVE_END_DATE IS NULL OR PPF.EFFECTIVE_END_DATE > SYSDATE)
   AND PAF.EFFECTIVE_END_DATE >= SYSDATE
   –AND PS.NAME = '000100预算申请审批层次'
 ORDER BY PS.POSITION_STRUCTURE_ID,
          PE.POS_STRUCTURE_ELEMENT_ID,
          PE.ORDER_FLAG;

获取采购员相关信息SQL

SELECT FFV.DESCRIPTION     机构描述,
       PP.NAME             职位,
       FU.DESCRIPTION      描述,
       PA.AGENT_ID         采购员ID,
       FU.USER_ID          用户ID,
       FU.USER_NAME        系统名称,
       PPF.FULL_NAME       全名,
       PPF.EMPLOYEE_NUMBER 员工编号

  FROM PER_PEOPLE_F        PPF,
       PER_ASSIGNMENTS_F   PAF,
       PER_POSITIONS       PP,
       FND_USER            FU,
       PO_AGENTS           PA,
       FND_FLEX_VALUES_VL  FFV,
       FND_FLEX_VALUE_SETS FF
 WHERE PPF.PERSON_ID = PAF.PERSON_ID
   AND FFV.FLEX_VALUE_SET_ID = FF.FLEX_VALUE_SET_ID
   AND FFV.ENABLED_FLAG = 'Y'
   AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
   AND FFV.FLEX_VALUE = SUBSTR(PP.NAME, 1, INSTR(PP.NAME, '.', 1, 1) – 1)
   AND FF.FLEX_VALUE_SET_NAME = '&ORG_COA'
   AND FFV.SUMMARY_FLAG = 'N'
   AND PA.AGENT_ID = PPF.PERSON_ID
   AND FU.EMPLOYEE_ID = PPF.PERSON_ID
   AND PP.POSITION_ID = PAF.POSITION_ID
   AND PPF.PERSON_ID IN
       (SELECT DISTINCT PA.AGENT_ID
          FROM PO_AGENTS PA, PER_ALL_PEOPLE_F PAPF, FND_USER FU
         WHERE PA.AGENT_ID = PAPF.PERSON_ID
           AND FU.EMPLOYEE_ID = PAPF.PERSON_ID
           AND (PAPF.EMPLOYEE_NUMBER IS NOT NULL OR
               PAPF.NPW_NUMBER IS NOT NULL)
           AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
               PAPF.EFFECTIVE_END_DATE)
   AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
   AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
   AND (FU.END_DATE IS NULL OR FU.END_DATE > SYSDATE)
   AND (PP.DATE_END IS NULL OR PP.DATE_END > SYSDATE)
 ORDER BY PP.NAME, FU.CREATION_DATE DESC;

获取供应商相关信息SQL

SELECT ASP.VENDOR_ID AS 供应商ID,
       ASP.VENDOR_NAME AS 供应商名称,
       ASP.SEGMENT1 AS 供应商编号,
       DECODE(ASP.VENDOR_TYPE_LOOKUP_CODE,
              'EMPLOYEE',
              '员工供应商',
              '外部供应商') AS 供应商类型,
       IEB.BANK_NAME AS 银行,
       IEB.BANK_BRANCH_NAME AS 分行,
       IEB.BRANCH_PARTY_ID,
       IEB.BANK_ACCOUNT_NUMBER AS 银行账户,
       (SELECT T.DESCRIPTION
          FROM FND_FLEX_VALUES_VL T, FND_FLEX_VALUE_SETS S
         WHERE 1 = 1
           AND T.FLEX_VALUE_SET_ID = S.FLEX_VALUE_SET_ID
           AND S.FLEX_VALUE_SET_NAME LIKE '%COMPANY'
           AND T.FLEX_VALUE =
               (SELECT SUBSTR(HOU.SHORT_CODE, 1, 6)
                  FROM HR_OPERATING_UNITS HOU
                 WHERE HOU.ORGANIZATION_ID = ASS.ORG_ID)
           AND T.ENABLED_FLAG = 'Y'
           AND SYSDATE < NVL(T.END_DATE_ACTIVE, SYSDATE + 1)) AS 业务实体名称,
       GCC.SEGMENT3 预付款会计科目,
       GCC1.SEGMENT3 负债会计科目
  FROM AP_SUPPLIERS             ASP,
       IBY_EXT_BANK_ACCOUNTS_V  IEB,
       IBY_EXTERNAL_PAYEES_ALL  IEP,
       IBY_PMT_INSTR_USES_ALL   USES,
       IBY_ACCOUNT_OWNERS       IAO,
       AP_SUPPLIER_SITES_ALL    ASS,
       GL_CODE_COMBINATIONS_KFV GCC,
       GL_CODE_COMBINATIONS_KFV GCC1
 WHERE 1 = 1
   AND IEP.EXT_PAYEE_ID = USES.EXT_PMT_PARTY_ID
   AND GCC.CODE_COMBINATION_ID = ASS.PREPAY_CODE_COMBINATION_ID
   AND GCC1.CODE_COMBINATION_ID = ASS.ACCTS_PAY_CODE_COMBINATION_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 ASP.ENABLED_FLAG = 'Y'
   AND ASP.SUMMARY_FLAG = 'N'
   AND IEP.PARTY_SITE_ID IS NULL
   AND IEP.SUPPLIER_SITE_ID IS NULL
   AND IEP.ORG_ID IS NULL
   AND (ASP.END_DATE_ACTIVE IS NULL OR ASP.END_DATE_ACTIVE > SYSDATE)
   AND (ASS.INACTIVE_DATE IS NULL OR ASS.INACTIVE_DATE > SYSDATE)
   AND ASS.VENDOR_ID = ASP.VENDOR_ID
   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
 ORDER BY ASP.VENDOR_TYPE_LOOKUP_CODE;

Oracle EBS整体介绍

    甲骨文公司的应用产品,全称是Oracle 电子商务套件(E-Business Suit),
是在原来Application(ERP)基础上的扩展,包括ERP(企业资源计划管理)、
HR(人力资源管理)、CRM(客户关系管理)等等多种管理软件的集合,是无缝集成
的一个管理套件。

Oracle ERP笔记(小潘的实施记录博客)