Copy inventory part characteristics values

Hello,

I also struggle with this issue from time to time - this is how you can find view/table/package names for it:

 

Hi, thank you for the reply. Could you please elaborate this a bit (or please send any documentation link if available). Thanks in advance.

Best Regards,

Hari

Thank you very much Bart :) 

Hi Hari,

I would like to add:
The View brakes down to the inventory_part_char_tab:

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "IFSAPP"."INVENTORY_PART_CHAR_ALL" ("CONTRACT", "PART_NO", "CHARACTERISTIC_CODE", "UNIT_MEAS", "ATTR_VALUE", "ATTR_VALUE_NUMERIC", "ATTR_VALUE_ALPHA", "OBJID", "OBJTYPE", "OBJVERSION", "OBJKEY") AS
  SELECT contract                       contract,
       part_no                        part_no,
       characteristic_code            characteristic_code,
       unit_meas                      unit_meas,
       attr_value                     attr_value,
       TO_NUMBER(DECODE(characteristic_API.Get_Search_Type_Db(characteristic_code), 'N',attr_value))  attr_value_numeric,
       DECODE(characteristic_API.Get_Search_Type_Db(characteristic_code), 'A',attr_value)  attr_value_alpha,
       rowid                         objid,
       rowtype                       objtype,
       ltrim(lpad(to_char(rowversion,'YYYYMMDDHH24MISS'),2000))                    objversion,
       rowkey                        objkey
FROM   inventory_part_char_tab
WHERE  EXISTS (SELECT 1 FROM user_allowed_site_pub WHERE inventory_part_char_tab.contract = site)
WITH   READ ONLY

We analyzed the metadata as follow:

And created a helping table with PartKey to connect within the following DIM_Part script:

-- sql_hlp_squaremeter.sql
--
-- Letzte Änderungen:
-- 02.09.2021: Erstellung.
--

if object_id('[sql_hlp_squaremeter]') is not null drop table  sql_hlp_squaremeter

SELECT
 DP01.ATTR_VALUE                            as DepthValue
,WD01.ATTR_VALUE                            as LengthValue
,HG01.ATTR_VALUE                            as HigthValue
,WG01.ATTR_VALUE                            as WeightValue
,CAST(DP01.ATTR_VALUE AS float) / 1000 *
 CAST(WD01.ATTR_VALUE AS float)    / 1000        as SquareMeter
,CAST(DP01.ATTR_VALUE AS float) / 1000 *
 CAST(WD01.ATTR_VALUE AS float) / 1000 *
 CAST(HG01.ATTR_VALUE AS float) / 1000        as CubicMeter

,DP01.CONTRACT + '_' + DP01.PART_NO            as PartKey

 into sql_hlp_squaremeter

FROM
 extr_IFS_INVENTORY_PART_CHAR_TAB as DP01
LEFT OUTER JOIN
 extr_IFS_INVENTORY_PART_CHAR_TAB as WD01
ON
 DP01.CONTRACT = WD01.CONTRACT
AND
 DP01.PART_NO = WD01.PART_NO
LEFT OUTER JOIN
 extr_IFS_INVENTORY_PART_CHAR_TAB as HG01
ON
 DP01.CONTRACT = HG01.CONTRACT
AND
 DP01.PART_NO = HG01.PART_NO
LEFT OUTER JOIN
 extr_IFS_INVENTORY_PART_CHAR_TAB as WG01
ON
 DP01.CONTRACT = WG01.CONTRACT
AND
 DP01.PART_NO = WG01.PART_NO
WHERE
 DP01.CHARACTERISTIC_CODE = 'DP01'
AND
 WD01.CHARACTERISTIC_CODE = 'WD01'
AND
 HG01.CHARACTERISTIC_CODE = 'HG01'
AND
 WG01.CHARACTERISTIC_CODE = 'WG01'
-- AND DP01.PART_NO = '010000006'
ORDER BY
 DP01.CONTRACT + '_' + DP01.PART_NO

Join:

Use the help table to create new fields in table DIM_Part

Hope that helps!

CU
Michael