EBS: COST 资源

-- N: BOM>>BILL>>工艺路线>>资源 
-- N: 成本>>设置>>子要素>>资源  
SELECT OOD.ORGANIZATION_ID AS 组织ID,
       OOD.ORGANIZATION_CODE AS "ORG_CODE", 
         OOD.ORGANIZATION_NAME AS "组织名",
         BR.RESOURCE_ID AS 资源ID,
         BR.RESOURCE_CODE AS 资源,
         BR.DESCRIPTION AS 说明,
         BR.DISABLE_DATE 失效日期,
         BR.RESOURCE_TYPE AS "类型ID", 
         DECODE(BR.RESOURCE_TYPE,'1','设备','2','人员','4','杂项','5','金额','6','币种',BR.RESOURCE_TYPE) AS "类型",
         BR.UNIT_OF_MEASURE AS "单位",
         BR.AUTOCHARGE_TYPE AS "计费类型ID",
         DECODE(BR.AUTOCHARGE_TYPE,'1','WIP 移动','2','人工','3','PO 接收','4','PO 移动',
         BR.AUTOCHARGE_TYPE) AS "计费类型",
         DECODE(BR.DEFAULT_BASIS_TYPE,'1','物料','3','批次',BR.DEFAULT_BASIS_TYPE) AS "基准",
         BR.COST_ELEMENT_ID AS 要素ID,
         CCE.COST_ELEMENT AS "要素",
         PURCHASE_ITEM_ID AS 外协加工物料ID,
         (SELECT SEGMENT1 FROM INV.MTL_SYSTEM_ITEMS_B MSI
           WHERE MSI.INVENTORY_ITEM_ID =BR.PURCHASE_ITEM_ID 
           AND MSI.ORGANIZATION_ID= BR.organization_id
           AND ROWNUM =1)  AS "外协加工物料",
         (SELECT MSI.DESCRIPTION FROM INV.MTL_SYSTEM_ITEMS_B MSI
           WHERE MSI.INVENTORY_ITEM_ID =BR.PURCHASE_ITEM_ID 
           AND MSI.ORGANIZATION_ID= BR.organization_id
           AND ROWNUM =1)  AS "外协加工物料描述",
         br.absorption_account 吸收帐户ID,
         apps.fnd_flex_ext.get_segs('SQLGL','GL#',50350, br.absorption_account) AS 吸收帐户,
         APPS.xla_oa_functions_pkg.get_ccid_description(50350, br.absorption_account) AS 吸收帐户描述,
         br.rate_variance_account 差异帐户ID,
         apps.fnd_flex_ext.get_segs('SQLGL','GL#',50350, br.rate_variance_account) AS 差异帐户,
         APPS.xla_oa_functions_pkg.get_ccid_description(50350, br.rate_variance_account) AS 差异帐户描述
        -- BR.*
 FROM APPS.BOM_RESOURCES_V BR,
   APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
   BOM.CST_COST_ELEMENTS CCE
 WHERE BR.ORGANIZATION_ID = OOD.ORGANIZATION_ID
--  AND OOD.ORGANIZATION_CODE IN (401,402)
  AND BR.COST_ELEMENT_ID = CCE.COST_ELEMENT_ID 
  AND BR.COST_ELEMENT_ID IN (3,4) -- 5：制造费用, 1:物料   
  AND CCE.COST_ELEMENT IN ('资源','外协加工')
 ORDER BY   OOD.ORGANIZATION_CODE, BR.RESOURCE_CODE