GS1_MST_ITM_VW2(SQL View) |
Index Back |
|---|---|
Item Master View |
| SELECT A.SETID , A.INV_ITEM_ID , A.ITM_STATUS_EFFDT , A.ITM_STATUS_CURRENT , A.ITM_STAT_DT_FUTURE , A.ITM_STATUS_FUTURE , A.DESCR , A.DESCR60 , A.DESCRSHORT , A.UNIT_MEASURE_STD , A.INV_ITEM_GROUP , A.INV_PROD_FAM_CD , A.CATEGORY_ID , A.DATE_ADDED , A.ORIG_OPRID , A.APPROVAL_OPRID , A.APPROVAL_DATE , A.LAST_MAINT_OPRID , A.LAST_DTTM_UPDATE , A.CHANGE_FIELD , A.INVENTORY_ITEM , A.LOT_CONTROL , A.SERIAL_CONTROL , A.SHIP_SERIAL_CNTRL , A.NON_OWN_FLAG , A.APPROV_REQUIRED , A.APPROV_SUBMITTED , A.DENIAL_REASON , A.STAGED_DATE_FLAG , A.DIST_CFG_FLAG , A.PRDN_CFG_FLAG , A.CFG_CODE_OPT , A.CFG_COST_OPT , A.CFG_LOT_OPT , A.CP_TEMPLATE_ID , A.CP_TREE_DIST , A.CP_TREE_PRDN , A.CM_GROUP , A.MATERIAL_RECON_FLG , A.USG_TRCKNG_METHOD , A.CONSIGNED_FLAG , A.pl_prio_family , %subrec(MASTER_ITEM_SBR, A) , A.PROMISE_OPTION , A.DEVICE_TRACKING , B.CATEGORY_CD , A.SERIAL_IN_PRDN , A.TRACE_USAGE , A.TRACE_CHANGE , A.PHYSICAL_NATURE ,E.MFG_ID ,E.MFG_ITM_ID ,F.UPN_ID FROM PS_MASTER_ITEM_TBL A LEFT OUTER JOIN PS_ITEM_MFG E ON (A.SETID = E.SETID AND A.INV_ITEM_ID = E.INV_ITEM_ID ) LEFT OUTER JOIN PS_ITEM_MFG_UPN_FS F ON (E.SETID = F.SETID AND E.INV_ITEM_ID = F.INV_ITEM_ID AND E.MFG_ID =F.MFG_ID AND E.MFG_ITM_ID = F.MFG_ITM_ID AND F.UPN_TYPE_CD = 'GTIN') , PS_ITM_CAT_TBL B WHERE A.SETID = B.SETID AND A.CATEGORY_ID = B.CATEGORY_ID AND B.EFFDT = ( SELECT MAX (CC.EFFDT) FROM PS_ITM_CAT_TBL CC WHERE CC.SETID = B.SETID AND CC.CATEGORY_TYPE = B.CATEGORY_TYPE AND CC.CATEGORY_CD = B.CATEGORY_CD AND CC.CATEGORY_ID = B.CATEGORY_ID AND CC.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 3 | ITM_STATUS_EFFDT | Date(10) | DATE | Item Status Date |
| 4 | ITM_STATUS_CURRENT | Character(1) | VARCHAR2(1) NOT NULL |
Item Status Current
1=Active 2=Hold 3=Discontinue 4=Inactive 5=Pending Approval 6=Denied Approval 7=Under Initialization |
| 5 | ITM_STAT_DT_FUTURE | Date(10) | DATE | Item Date Future |
| 6 | ITM_STATUS_FUTURE | Character(1) | VARCHAR2(1) NOT NULL |
Item Status Future
1=Active 2=Hold 3=Discontinue 4=Inactive |
| 7 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 8 | DESCR60 | Character(60) | VARCHAR2(60) NOT NULL | Description |
| 9 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 10 | UNIT_MEASURE_STD | Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 11 | INV_ITEM_GROUP | Character(15) | VARCHAR2(15) NOT NULL | Item Group |
| 12 | INV_PROD_FAM_CD | Character(10) | VARCHAR2(10) NOT NULL | Family |
| 13 | CATEGORY_ID | Character(5) | VARCHAR2(5) NOT NULL | Category ID |
| 14 | DATE_ADDED | Date(10) | DATE | Date Added |
| 15 | ORIG_OPRID | Character(30) | VARCHAR2(30) NOT NULL | User ID |
| 16 | APPROVAL_OPRID | Character(30) | VARCHAR2(30) NOT NULL | A system generated value that reflects the operator that approved a given transaction. |
| 17 | APPROVAL_DATE | Date(10) | DATE | Approval Date |
| 18 | LAST_MAINT_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Last Maintained By Operator ID |
| 19 | 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. |
| 20 | CHANGE_FIELD | Character(18) | VARCHAR2(18) NOT NULL | Change Reason |
| 21 | INVENTORY_ITEM | Character(1) | VARCHAR2(1) NOT NULL |
Inventory Item
N=No Y=Yes |
| 22 | LOT_CONTROL | Character(1) | VARCHAR2(1) NOT NULL |
Lot Control
N=N Y=Y |
| 23 | SERIAL_CONTROL | Character(1) | VARCHAR2(1) NOT NULL |
Serial Control
N=N Y=Y |
| 24 | SHIP_SERIAL_CNTRL | Character(1) | VARCHAR2(1) NOT NULL | Shipping Serial Control |
| 25 | NON_OWN_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
07/30/1998 WHS CN#ENH8.0: Created Translate Values 'Y'es and 'N'o
N=No Y=Yes |
| 26 | APPROV_REQUIRED | Character(1) | VARCHAR2(1) NOT NULL | Approval Required for Demand |
| 27 | APPROV_SUBMITTED | Character(1) | VARCHAR2(1) NOT NULL | An internal work field that indicates whether a treasury deal review and/or confirmation transac |
| 28 | DENIAL_REASON | Character(250) | VARCHAR2(250) NOT NULL | Item Denial Reason |
| 29 | STAGED_DATE_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Staged Date Control
N=Staged Date Tracking OFF Y=Staged Date Tracking ON |
| 30 | DIST_CFG_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Distribution Configuration |
| 31 | PRDN_CFG_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Production Configuration |
| 32 | CFG_CODE_OPT | Character(1) | VARCHAR2(1) NOT NULL |
Configuration Code Generation
B=Both N=No Y=Yes |
| 33 | CFG_COST_OPT | Character(1) | VARCHAR2(1) NOT NULL | Configuration Costing |
| 34 | CFG_LOT_OPT | Character(1) | VARCHAR2(1) NOT NULL | Default Configured Lot |
| 35 | CP_TEMPLATE_ID | Character(10) | VARCHAR2(10) NOT NULL | Template ID Field |
| 36 | CP_TREE_DIST | Character(18) | VARCHAR2(18) NOT NULL | Distribution Model Field |
| 37 | CP_TREE_PRDN | Character(18) | VARCHAR2(18) NOT NULL | Production Model Field |
| 38 | CM_GROUP | Character(15) | VARCHAR2(15) NOT NULL | 01/18/99 RML Created for CN800-9.0 FIFO/LIFO Enhancements |
| 39 | MATERIAL_RECON_FLG | Character(1) | VARCHAR2(1) NOT NULL | Material Reconciliation Flag |
| 40 | USG_TRCKNG_METHOD | Character(2) | VARCHAR2(2) NOT NULL |
Usage Tracking Method
01=None 02=Billing 03=Costing |
| 41 | CONSIGNED_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Consigned Flag |
| 42 | PL_PRIO_FAMILY | Character(10) | VARCHAR2(10) NOT NULL | Demand Priority Family |
| 43 | ITEM_FIELD_C30_A | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 44 | ITEM_FIELD_C30_B | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 45 | ITEM_FIELD_C30_C | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 46 | ITEM_FIELD_C30_D | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 47 | ITEM_FIELD_C30_E | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 48 | ITEM_FIELD_C30_F | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 49 | ITEM_FIELD_C30_G | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 50 | ITEM_FIELD_C30_H | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 51 | ITEM_FIELD_C30_I | Character(30) | VARCHAR2(30) NOT NULL | User-defined item field - Character 30 |
| 52 | ITEM_FIELD_C1_A | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 53 | ITEM_FIELD_C1_B | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 54 | ITEM_FIELD_C1_C | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 55 | ITEM_FIELD_C1_D | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 56 | ITEM_FIELD_C1_E | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 57 | ITEM_FIELD_C1_F | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 58 | ITEM_FIELD_C1_G | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 59 | ITEM_FIELD_C1_H | Character(1) | VARCHAR2(1) NOT NULL | User-defined item field - Character 1 |
| 60 | ITEM_FIELD_C10_A | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 61 | ITEM_FIELD_C10_B | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 62 | ITEM_FIELD_C10_C | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 63 | ITEM_FIELD_C10_D | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 64 | ITEM_FIELD_C10_E | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 65 | ITEM_FIELD_C10_F | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 66 | ITEM_FIELD_C10_G | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 67 | ITEM_FIELD_C10_H | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 68 | ITEM_FIELD_C10_I | Character(10) | VARCHAR2(10) NOT NULL | User-defined item field - Character 10 |
| 69 | ITEM_FIELD_C2 | Character(2) | VARCHAR2(2) NOT NULL | User-defined item field - Character 2 |
| 70 | ITEM_FIELD_C2_B | Character(2) | VARCHAR2(2) NOT NULL | User-defined item field - Character 2 |
| 71 | ITEM_FIELD_C4 | Character(4) | VARCHAR2(4) NOT NULL | User-defined item field - Character 4 |
| 72 | ITEM_FIELD_C4_B | Character(4) | VARCHAR2(4) NOT NULL | User-defined item field - Character 4 |
| 73 | ITEM_FIELD_C4_C | Character(4) | VARCHAR2(4) NOT NULL | User-defined item field - Character 4 |
| 74 | ITEM_FIELD_C6 | Character(6) | VARCHAR2(6) NOT NULL | User-defined item field - Character 6 |
| 75 | ITEM_FIELD_C6_B | Character(6) | VARCHAR2(6) NOT NULL | User-defined item field - Character 6 |
| 76 | ITEM_FIELD_C8 | Character(8) | VARCHAR2(8) NOT NULL | User-defined item field - Character 8 |
| 77 | ITEM_FIELD_C8_B | Character(8) | VARCHAR2(8) NOT NULL | User-defined item field - Character 8 |
| 78 | ITEM_FIELD_C8_C | Character(8) | VARCHAR2(8) NOT NULL | User-defined item field - Character 8 |
| 79 | ITEM_FIELD_N12_A | Number(16,3) | DECIMAL(15,3) NOT NULL | User-defined item field - Unsigned Decimal 12.3 |
| 80 | ITEM_FIELD_N12_B | Number(16,3) | DECIMAL(15,3) NOT NULL | User-defined item field - Unsigned Decimal 12.3 |
| 81 | ITEM_FIELD_N12_C | Number(16,3) | DECIMAL(15,3) NOT NULL | User-defined item field - Unsigned Decimal 12.3 |
| 82 | ITEM_FIELD_N12_D | Number(16,3) | DECIMAL(15,3) NOT NULL | User-defined item field - Unsigned Decimal 12.3 |
| 83 | ITEM_FIELD_N12_E | Number(16,3) | DECIMAL(15,3) NOT NULL | User-defined item field - Unsigned Decimal 12.3 |
| 84 | ITEM_FIELD_N12_F | Number(16,3) | DECIMAL(15,3) NOT NULL | User-defined item field - Unsigned Decimal 12.3 |
| 85 | ITEM_FIELD_N12_G | Number(16,3) | DECIMAL(15,3) NOT NULL | User-defined item field - Unsigned Decimal 12.3 |
| 86 | ITEM_FIELD_N15_A | Number(15,0) | DECIMAL(15) NOT NULL | User-defined item field - Unsigned Decimal 15.0 |
| 87 | ITEM_FIELD_N15_B | Number(15,0) | DECIMAL(15) NOT NULL | User-defined item field - Unsigned Decimal 15.0 |
| 88 | ITEM_FIELD_N15_C | Number(15,0) | DECIMAL(15) NOT NULL | User-defined item field - Unsigned Decimal 15.0 |
| 89 | ITEM_FIELD_N15_D | Number(15,0) | DECIMAL(15) NOT NULL | User-defined item field - Unsigned Decimal 15.0 |
| 90 | ITEM_FIELD_N15_E | Number(15,0) | DECIMAL(15) NOT NULL | User-defined item field - Unsigned Decimal 15.0 |
| 91 | ITEM_FIELD_N15_F | Number(15,0) | DECIMAL(15) NOT NULL | User-defined item field - Unsigned Decimal 15.0 |
| 92 | ITEM_FIELD_N15_G | Number(15,0) | DECIMAL(15) NOT NULL | User-defined item field - Unsigned Decimal 15.0 |
| 93 | PROMISE_OPTION | Character(1) | VARCHAR2(1) NOT NULL |
Order Promise Option
2=Perform ATP Reservations |
| 94 | DEVICE_TRACKING | Character(1) | VARCHAR2(1) NOT NULL | Device Tracking Indicator |
| 95 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 96 | SERIAL_IN_PRDN | Character(1) | VARCHAR2(1) NOT NULL | Serial in Production |
| 97 | TRACE_USAGE | Character(1) | VARCHAR2(1) NOT NULL |
Trace Usage
0=None 1=Serial Trace 2=Lot Trace |
| 98 | TRACE_CHANGE | Character(1) | VARCHAR2(1) NOT NULL | Trace Changes |
| 99 | PHYSICAL_NATURE | Character(1) | VARCHAR2(1) NOT NULL |
Physical Nature
G=Goods S=Services |
| 100 | MFG_ID | Character(50) | VARCHAR2(50) NOT NULL | Manufacturer ID |
| 101 | MFG_ITM_ID | Character(50) | VARCHAR2(50) NOT NULL | Manufacturer's Item ID |
| 102 | UPN_ID | Character(20) | VARCHAR2(20) NOT NULL | Universal Product Number |