所有由小潘发布的文章

喜欢分享的小潘.
PO模块

PO最终关闭失败

留下评论

   之前做最终关闭PO单，释放保留款的时候，出现图所示错误，查了metalink上，方法如下：
      1. Go into the responsibility: General Ledger.
      2. Navigate to the GL Accounts form:
         Nav: Setup -> Accounts -> Combinations.
      3. Query the accounts used on the affected PO and ensure that Allow Posting and Allow Budgeting              flags are checked.
      4. Retest the issue.
      5. Migrate the solution as appropriate to other environments. 
但是没有效果……

最后发现是更改某标准的功能后，需要重新验证一下应用产品。（错误如图），需运行请求名：验证应用产品会计定义，参数
根据各自的需求选择即可。

po

EBS_PO_SQL

获取财务系统设置审批链相关信息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;

EBS_PO_SQL

获取采购员相关信息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;

EBS_AP_SQL

获取供应商相关信息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;