SF_INVITM_EVW

(SQL View)
Index Back

INV ITM stg vw for ITEM SYNC

SELECT B.BUSINESS_UNIT , A.EIP_CTL_ID , A.SETID , A.INV_ITEM_ID , A.EFFDT, %List(FIELD_LIST,INV_ITM_ESR) , A.PROCESS_INSTANCE, A.AUDIT_ACTN , A.IN_PROCESS_FLG FROM PS_SF_ITM_MSG_STG A , PS_BU_ITEM_MSG_STG B WHERE A.EIP_CTL_ID = B.EIP_CTL_ID AND A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID

SELECT B.BUSINESS_UNIT , A.EIP_CTL_ID , A.SETID , A.INV_ITEM_ID , A.EFFDT , %subrec(INV_ITM_ESR, A) , A.PROCESS_INSTANCE , A.AUDIT_ACTN , A.IN_PROCESS_FLG FROM PS_SF_ITM_MSG_STG A , PS_BU_ITEM_MSG_STG B WHERE A.EIP_CTL_ID = B.EIP_CTL_ID AND A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 EIP_CTL_ID Character(25) VARCHAR2(25) NOT NULL Enterprise Components EIP (enterprise integration point) control field
3 SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
5 EFFDT Date(10) DATE Effective Date

Default Value: %date

6 UPC_ID Character(18) VARCHAR2(18) NOT NULL Uniform Product Code ID
7 POTENCY_RATING Character(4) VARCHAR2(4) NOT NULL Potency Code

Prompt Table: POTENCY_CD_INV

8 INV_ITEM_TEMPLATE Character(5) VARCHAR2(5) NOT NULL Template ID

Prompt Table: INV_ITEM_TEMPL

9 INV_ITEM_TYPE Character(5) VARCHAR2(5) NOT NULL Item Type

Default Value: INV_ITEM_DEFLT.INV_ITEM_TYPE

Prompt Table: TYPE_ITEM_INV

10 RECYCLE_FLAG Character(1) VARCHAR2(1) NOT NULL Item Recycle Flag

Y/N Table Edit

Default Value: N

11 REUSABLE_FLAG Character(1) VARCHAR2(1) NOT NULL Reusable Item Flag

Y/N Table Edit

Default Value: N

12 DISPOSABLE_FLAG Character(1) VARCHAR2(1) NOT NULL Special Disposable Flag

Y/N Table Edit

Default Value: N

13 INV_STOCK_TYPE Character(4) VARCHAR2(4) NOT NULL Stock Type

Prompt Table: STOCK_TYPE_INV

14 PACKING_CD Character(4) VARCHAR2(4) NOT NULL Packing Code

Prompt Table: PACK_CODE_INV

15 STOR_RULES_ID Character(4) VARCHAR2(4) NOT NULL Storage Rule ID

Prompt Table: STOR_RULES_INV

16 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code

Prompt Table: SHIP_METHOD

17 RECOM_STOR_TEMP Signed Number(7,2) DECIMAL(5,2) NOT NULL Storage Temp
18 RECOM_HUMIDITY_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Storage Humidity
19 INV_PROD_GRADE Character(4) VARCHAR2(4) NOT NULL Grade

Prompt Table: INV_ITEM_GRADE

20 MAX_CAPACITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Max Capacity
21 HAZ_CLASS_CD Character(4) VARCHAR2(4) NOT NULL Hazard Code

Prompt Table: HAZ_TBL_INV

22 INTL_HAZARD_ID Character(15) VARCHAR2(15) NOT NULL Intl Hazard ID
23 CHARGE_CODE Character(20) VARCHAR2(20) NOT NULL Charge Code

Prompt Table: CHARGE_CODE_INV

24 SHELF_LIFE Number(4,0) SMALLINT NOT NULL Shelf Life (Days)
25 AVAIL_STATUS Character(1) VARCHAR2(1) NOT NULL Availability Status
1=Open
2=Restricted
3=Hold
4=Rejected
26 INV_ITEM_HEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Height
27 INV_ITEM_LENGTH Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Length
28 INV_ITEM_WIDTH Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Width
29 INV_ITEM_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Weight
30 INV_ITEM_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL Volume
31 INV_ITEM_SIZE Character(5) VARCHAR2(5) NOT NULL Item Size
32 INV_ITEM_COLOR Character(10) VARCHAR2(10) NOT NULL Item Color
33 UNIT_MEASURE_DIM Character(3) VARCHAR2(3) NOT NULL Dimension UOM

Prompt Table: UNITS_TBL

34 UNIT_MEASURE_WT Character(3) VARCHAR2(3) NOT NULL Weight UOM

Prompt Table: UNITS_TBL

35 UNIT_MEASURE_TEMP Character(3) VARCHAR2(3) NOT NULL Temperature UOM

Prompt Table: UNITS_TBL

36 UNIT_MEASURE_VOL Character(3) VARCHAR2(3) NOT NULL Volume UOM

Prompt Table: UNITS_TBL

37 COMMODITY_CD Character(10) VARCHAR2(10) NOT NULL Commodity Code

Prompt Table: COMMOD_CODE

38 HARMONIZED_CD Character(14) VARCHAR2(14) NOT NULL Harmonized Code

Prompt Table: HRMN_TARIFF_CD

39 MSDS_ID Character(10) VARCHAR2(10) NOT NULL MSDS ID

Prompt Table: MSDS_ID_INV

40 LAST_MAINT_OPRID Character(30) VARCHAR2(30) NOT NULL Last Maintained By Operator ID
41 LAST_DTTM_UPDATE DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
42 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
43 COMMODITY_CD_EU Character(10) VARCHAR2(10) NOT NULL EU Commodity Code

Prompt Table: COMMOD_CODE_EU

44 AVAIL_LEAD_TIME Number(4,0) SMALLINT NOT NULL Availability Lead Time (Days)
45 RETEST_LEAD_TIME Number(4,0) SMALLINT NOT NULL Retest Lead Time (Days)
46 CHARGE_MARKUP_PCNT Signed Number(16,2) DECIMAL(14,2) NOT NULL Charge Markup %
47 CHARGE_MARKUP_AMT Signed Number(17,4) DECIMAL(15,4) NOT NULL Charge Markup Amt
48 CONSUMABLE_FLG Character(1) VARCHAR2(1) NOT NULL A Consumable part is a part that will not be considered as an Installed Product record. Types of consumables are rages, tie ends, screws, and other inexpensive parts.

Y/N Table Edit

Default Value: N

49 RETURNABLE_FLG Character(1) VARCHAR2(1) NOT NULL Repairable Items are those items that can be returned to the Depot, or a Vendor for repair.

Y/N Table Edit

Default Value: N

50 SERVICEABLE_FLG Character(1) VARCHAR2(1) NOT NULL The Master Inventory Item will be created, updated, and maintained from the PS/Inventory; and since the EIP the Item Sync will only be from PeopleSoft Inventory to CRM Inventory this will enable the service company to only download "service" inventory. For example, if we did not have this flag and the user could have 300,000 parts that were all encompassing for manufacturing and service; therefore we are simplifying the parts list with only parts that the service company would want to be concerned with.

Y/N Table Edit

Default Value: N

51 SERVICE_PRICE Signed Number(22,5) DECIMAL(20,5) NOT NULL Service Price
52 SERVICE_EXCH_AMT Signed Number(22,5) DECIMAL(20,5) NOT NULL Service Exchange Amount
53 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

54 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
55 AUDIT_ACTN Character(1) VARCHAR2(1) NOT NULL Audit Action Code for Application Messages. This tells the subscriber whether the row has been added, changed, or deleted.
A=Add
C=Change Old (PPR Only)
D=Delete
K=Change - Old Values
N=Change - New Values
O=Change - Original Values
56 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag