TC数据库相关整理(持续更新)

向右看齐 2023-05-31 02:44 292阅读 0赞

### TC数据库相关整理 ###

*  1.常用的查找表的方法
 *  2.用到过的表
 *  3.使用过的语句
 *   *  1.分类相关
     *  2.创建视图
     *  3.创建package
     *  4.BOM相关
 *  4.写在后面

# 1.常用的查找表的方法 #

在bmide中，对于BusinessObject的子类，都存在一个存储类与之对应，例如常用的ItemRevision类，存在ItemRevision存储类，如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E5NzQ5ODYwNDI_size_16_color_FFFFFF_t_70]  
与之相对应的数据库表名即为P+ItemRevision，也就是说，存储类名开头加上"P"，就是数据库表名。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E5NzQ5ODYwNDI_size_16_color_FFFFFF_t_70 1]  
在bmide中点击进入存储类，就能找到属性对应存储在哪个存储类里，也就能找到相应的数据表。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E5NzQ5ODYwNDI_size_16_color_FFFFFF_t_70 2]  
例如上图的H9AsmFinPrdRevision类，h9InputVA存储与PH9AsmFinPrdRevision表中，而其他的属性继承于ItemRevision，存储于PItemRevision中。

# 2.用到过的表 #

<table> 
 <thead> 
  <tr> 
   <th>表名</th> 
   <th>表的作用</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>PPOM_USER</td> 
   <td>存储用户信息</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>PPOM_GROUP</td> 
   <td>存储分组信息</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>PSMLB0</td> 
   <td>分类信息</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>PLISTOFVALUES</td> 
   <td>LOV存储表</td> 
   <td>这个表下面还有细分，比如PLISTOFVALUESSTRING等。</td> 
  </tr> 
  <tr> 
   <td>PFND0LOVDICTIONARYENTRY</td> 
   <td>LOV数据字典</td> 
   <td>存储了所有lov的真实值</td> 
  </tr> 
  <tr> 
   <td>PL10N_FND0ENTRY</td> 
   <td>LOV显示值</td> 
   <td>存储了所有lov的真实值对应的显示值</td> 
  </tr> 
  <tr> 
   <td>PNOTETYPE 、PNOTE_TYPES_0 、PNOTE_TEXTS_0</td> 
   <td>BOM注释属性</td> 
   <td>存储BOM行注释属性</td> 
  </tr> 
  <tr> 
   <td>持续更新</td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

# 3.使用过的语句 #

## 1.分类相关 ##

1.分类树获取

"SELECT t_01.puid, t_01.pcid, t_01.pcflags, t_01.pname "
    			+ "FROM PSMLB0 t_01 "
    			+ "WHERE (t_01.pklassi = ?) "
    			+ "UNION SELECT t_03.puid, t_03.pcid, t_03.pcflags, t_03.pname "
    			+ "FROM PPARENT t_02 , PSMLB0 t_03 "
    			+ "WHERE (((t_02.pseq = 0) AND (t_02.pval_0 = ?)) AND (t_02.puid = t_03.puid)) ORDER BY 2 ASC";

来源：辉哥tech

## 2.创建视图 ##

1.查找最新版本，去除基线小版本

CREATE OR REPLACE VIEW LATESTREV AS 
    SELECT 
      REV.PUID, 
      ITEM.PITEM_ID, 
      REV.PITEM_REVISION_ID 
    FROM 
      PITEMREVISION REV, 
      PITEM ITEM, 
      PWORKSPACEOBJECT OBJ 
    WHERE 
      ITEM.PUID = REV.RITEMS_TAGU 
      AND OBJ.PUID = REV.PUID 
      AND OBJ.PACTIVE_SEQ != '0' 
      AND REV.PITEM_REVISION_ID = (
        SELECT 
          MAX(TREV.PITEM_REVISION_ID) 
        FROM 
          PITEMREVISION TREV, 
          PITEM TITEM 
        WHERE 
          TREV.PITEM_REVISION_ID NOT LIKE '%.%' 
          AND TITEM.PUID = TREV.RITEMS_TAGU 
          AND ITEM.PUID = TITEM.PUID
      );

2.查找最新有状态的版本 排除基线小版本 不指定状态

CREATE 
    OR REPLACE VIEW LATESTRELEASEDREV AS 
    SELECT 
      REV.PUID, 
      ITEM.PITEM_ID, 
      REV.PITEM_REVISION_ID, 
      RELEASE.PNAME 
    FROM 
      PITEMREVISION REV, 
      PITEM ITEM, 
      PRELEASE_STATUS_LIST RELEASE_LIST, 
      PWORKSPACEOBJECT OBJ, 
      PRELEASESTATUS RELEASE 
    WHERE 
      ITEM.PUID = REV.RITEMS_TAGU 
      AND OBJ.PUID = REV.PUID 
      AND OBJ.AOID = RELEASE_LIST.PUID 
      AND RELEASE_LIST.PVALU_0 = RELEASE.PUID 
      AND OBJ.PACTIVE_SEQ != '0' 
      AND REV.PITEM_REVISION_ID = (
        SELECT 
          MAX(TREV.PITEM_REVISION_ID) 
        FROM 
          PITEMREVISION TREV, 
          PITEM TITEM, 
          PRELEASE_STATUS_LIST TRELEASE_LIST, 
          PWORKSPACEOBJECT TOBJ, 
          PRELEASESTATUS TRELEASE 
        WHERE 
          TREV.PITEM_REVISION_ID NOT LIKE '%.%' 
          AND TOBJ.PUID = TREV.PUID 
          AND TOBJ.AOID = TRELEASE_LIST.PUID 
          AND TRELEASE_LIST.PVALU_0 = TRELEASE.PUID 
          AND TITEM.PUID = TREV.RITEMS_TAGU 
          AND ITEM.PUID = TITEM.PUID
      )

3.查找最新有状态的版本 排除基线小版本 指定状态"TCM Released"

CREATE 
      OR REPLACE VIEW LATESTRELEASEDREV_TCM_Released AS 
    SELECT 
      REV.PUID, 
      ITEM.PITEM_ID, 
      REV.PITEM_REVISION_ID, 
      RELEASE.PNAME 
    FROM 
      PITEMREVISION REV, 
      PITEM ITEM, 
      PRELEASE_STATUS_LIST RELEASE_LIST, 
      PWORKSPACEOBJECT OBJ, 
      PRELEASESTATUS RELEASE 
    WHERE 
      ITEM.PUID = REV.RITEMS_TAGU 
      AND OBJ.PUID = REV.PUID 
      AND OBJ.AOID = RELEASE_LIST.PUID 
      AND RELEASE_LIST.PVALU_0 = RELEASE.PUID 
      AND OBJ.PACTIVE_SEQ != '0' 
      AND REV.PITEM_REVISION_ID = (
        SELECT 
          MAX(TREV.PITEM_REVISION_ID) 
        FROM 
          PITEMREVISION TREV, 
          PITEM TITEM, 
          PRELEASE_STATUS_LIST TRELEASE_LIST, 
          PWORKSPACEOBJECT TOBJ, 
          PRELEASESTATUS TRELEASE 
        WHERE 
          TREV.PITEM_REVISION_ID NOT LIKE '%.%' 
          AND TOBJ.PUID = TREV.PUID 
          AND TOBJ.AOID = TRELEASE_LIST.PUID 
          AND TRELEASE_LIST.PVALU_0 = TRELEASE.PUID 
          AND TITEM.PUID = TREV.RITEMS_TAGU 
          AND TRELEASE.PNAME = 'TCM Released' 
          AND ITEM.PUID = TITEM.PUID
      )

4.创建子-父关系的视图

CREATE OR REPLACE VIEW CHILD_PARENT AS
    SELECT 
      CREV.PUID AS PCHILD_PUID, 
      PREV.PUID AS PPARENT_PUID, 
      OBJ.POBJECT_TYPE AS PPARENT_TYPE 
    FROM 
      PITEMREVISION CREV, 
      PITEMREVISION PREV, 
      PITEM CITEM, 
      PPSOCCURRENCE OCC, 
      PSTRUCTURE_REVISIONS STR, 
      PWORKSPACEOBJECT OBJ 
    WHERE 
      (
        CITEM.PUID = OCC.RCHILD_ITEMU 
        OR CREV.PUID = OCC.RCHILD_ITEMU
      ) 
      AND OCC.RPARENT_BVRU = STR.PVALU_0 
      AND STR.PUID = PREV.PUID 
      AND OBJ.PUID = PREV.PUID 
      AND CITEM.PUID = CREV.RITEMS_TAGU 
      AND OBJ.PACTIVE_SEQ != '0'

## 3.创建package ##

1.根据LOV名称及真实值获取显示值

create or replace package CMService is
    
      function getLovValueDes(typeName in varchar2,LovValue in varchar2) return varchar2;
     
    end CMService;
    
    
    create or replace package body CMService is
      
      --根据LOV类型及真实值获取显示值
      function getLovValueDes(typeName in varchar2,LovValue in varchar2) return varchar2 is
        result varchar2(50);
      begin
          SELECT substr(t3.pval_0,49) into result
        FROM PLISTOFVALUES t1, PFND0LOVDICTIONARYENTRY t2, PL10N_FND0ENTRY t3
       where t1.puid = t2.rfnd0lovu
         and t2.puid = t3.puid
         and t1.plov_name = typeName
         and t3.pseq = 1
         and t2.pfnd0entry= LovValue;
         return result;
      end;
      
    end CMService;

来源：储老师

## 4.BOM相关 ##

1.查询指定物料下层

SELECT *
    FROM (
    	SELECT t.*, ROW_NUMBER() OVER (PARTITION BY pitem_id ORDER BY pitem_revision_id DESC) AS num
    	FROM (
    		SELECT c.pitem_id, d.pitem_revision_id, a.pqty_value, l.pobject_name
    		FROM PPSOCCURRENCE a, pworkspaceobject l, (
    			SELECT c.pitem_id, e.pitem_revision_id, e.psequence_id, e.puid AS rev_id, b.puid AS bv_id
    				, a.puid AS bvr_id
    			FROM ppsbomviewrevision a
    			JOIN ppsbomview b ON a.rbom_viewu = b.puid 
    			JOIN pitem c ON b.rparent_itemu = c.puid 
    			JOIN pstructure_revisions d ON a.puid = d.pvalu_0 
    				JOIN pitemrevision e ON d.puid = e.puid 
    		) b, pitem c, pitemrevision d
    		WHERE a.rparent_bvru = b.bvr_id
    			AND a.rchild_itemu = c.puid
    			AND a.rchild_itemu = d.ritems_tagu
    			AND b.pitem_id = 'item_id'
    			AND b.pitem_revision_id = '版本号'
    			AND l.puid = c.puid
    	) t
    )

SELECT c.pitem_id, d.pitem_revision_id, d.puid, a.pqty_value, l.pobject_name
    	, rev_id, l.pobject_type, a.RNOTES_REFU, a.PSEQ_NO, a.pext_transform_rot00
    	, a.pext_transform_rot10, a.pext_transform_rot20, a.pext_transform_per0, a.pext_transform_rot01, a.pext_transform_rot11
    	, a.pext_transform_rot21, a.pext_transform_per1, a.pext_transform_rot02, a.pext_transform_rot12, a.pext_transform_rot22
    	, a.pext_transform_per2, a.pext_transform_tra0, a.pext_transform_tra1, a.pext_transform_tra2, a.pext_transform_invscale
    FROM PPSOCCURRENCE a, pworkspaceobject l, (
    	SELECT IT.PITEM_ID, REV.PITEM_REVISION_ID, REV.PSEQUENCE_ID, REV.PUID AS REV_ID, RL.PVALU_0 AS BVR_ID
    	FROM PITEM IT, PITEMREVISION REV, PSTRUCTURE_REVISIONS RL
    	WHERE IT.PUID = REV.RITEMS_TAGU
    		AND REV.PUID = RL.PUID
    ) b, pitem c, pitemrevision d
    WHERE a.rparent_bvru = b.bvr_id
    	AND a.rchild_itemu = c.puid
    	AND a.rchild_itemu = d.ritems_tagu
    	AND b.pitem_id = '父id'
    	AND b.pitem_revision_id = '版本号'
    	AND l.puid = c.puid
    	AND l.pactive_seq != 0
    	AND d.PITEM_REVISION_ID = (
    		SELECT MAX(PITEM_REVISION_ID)
    		FROM PITEMREVISION
    		WHERE RITEMS_TAGU = c.PUID
    	);

2.通过子件id，查找父项最新版本(LASTESTREV是上面的最新版本视图)

SELECT prev.puid, prev.pitem_revision_id, pitem.pitem_id
    FROM pitem citem, PPSOCCURRENCE pps, PSTRUCTURE_REVISIONS pst, pitemrevision prev, pitem pitem, LASTESTREV
    WHERE citem.pitem_id = 'LSV2223FFGG00001_G001L01'
    	AND citem.puid = pps.rchild_itemu
    	AND pps.rparent_bvru = pst.pvalu_0
    	AND pst.puid = prev.puid
    	AND pitem.puid = prev.ritems_tagu
    	AND lastestrev.pitem_id = pitem.pitem_id;

3.由子找到所有上层（CHILD\_PARENT是上面提到的视图）

SELECT 
      DISTINCT PCHILD_PUID, 
      PPARENT_PUID, 
      PPARENT_TYPE 
    FROM 
      CHILD_PARENT START WITH PCHILD_PUID IN ('子件版本uid') CONNECT BY PCHILD_PUID = PRIOR PPARENT_PUID

# 4.写在后面 #

此tech积累TC数据库相关知识，持续更新。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E5NzQ5ODYwNDI_size_16_color_FFFFFF_t_70]: /images/20230531/f18b4eb929a94dedaeabefcafebc7e41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E5NzQ5ODYwNDI_size_16_color_FFFFFF_t_70 1]: /images/20230531/cadf9b4757dc46e395e3994ce4cef77a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E5NzQ5ODYwNDI_size_16_color_FFFFFF_t_70 2]: /images/20230531/35b56c6b835d4c1e8355df57685c52a5.png