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

I didn’t find any documentation for it.

Below are database objects for characteristic codes:

Technical_Object_Reference_tab

Technical_Object_Reference_api

Technical_Object_Reference

To use them, you need to check what method/procedures may be useful to you and what is the structure of the data in the table. If you have any problem, describe it and we will try to solve it.

Best regards

Bart

Thank you very much Bart :) 

We are on Apps 9.  I use the System Info to see the view and api.

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

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

 

 

 

 

Thank you Michael