SF_MT_SHORT_VW1(SQL View) |
Index Back |
|---|---|
Prdn Component Shortage View06/25/98 cam (CN# SF800-1.0) : New view for Material Shortage Inquiry. 07/13/99 ST CN# EN800-1.0 - Added MG_ORIG_COMP_ID 04/22/03 PWF Modified view to use %decmult and %decdiv. |
| SELECT DISTINCT B.BUSINESS_UNIT , B.PRODUCTION_ID , B.COMPONENT_ID , B.OP_SEQUENCE , B.MG_ORIG_COMP_ID , B.QTY_CODE , B.SF_QTY_PER , B.YIELD , B.CURR_SCHED_QTY , B.ISSUE_QTY , B.YIELD_QTY , B.PEND_CONSUME_QTY , B.PEND_LOSS_QTY , B.PICK_QTY , B.ISSUE_METHOD , B.CONFIG_CODE , B.SOURCE_CODE , 0 FROM PS_SF_PRDNID_HEADR A , PS_SF_COMP_LIST B WHERE A.MA_PRDN_ID_FLG = 'Y' AND A.PROD_STATUS IN ('20', '30', '40') AND B.ISSUE_METHOD IN ('KIT', 'ISS') AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PRODUCTION_ID = B.PRODUCTION_ID AND ((B.PEND_CONSUME_QTY + B.PEND_LOSS_QTY > 0) OR ((B.YIELD_QTY <= %DecMult (B.CURR_SCHED_QTY,(1 - (%DecDiv (B.YIELD, 100))))) AND (B.CURR_SCHED_QTY - B.ISSUE_QTY - B.YIELD_QTY > 0)) OR ((B.YIELD_QTY > %DecMult (B.CURR_SCHED_QTY,(1 - (%DecDiv(B.YIELD, 100))))) AND (B.CURR_SCHED_QTY - B.ISSUE_QTY - %DecMult(B.CURR_SCHED_QTY , (1 - (%DecDiv (B.YIELD, 100)))) > 0))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_BUMGOP_NONVW |
| 2 | PRODUCTION_ID |
Character(10) | VARCHAR2(10) NOT NULL | Production ID |
| 3 | COMPONENT_ID |
Character(18) | VARCHAR2(18) NOT NULL | 04/10/00 (Internet Architecture) SCC: Uppercase label Id. |
| 4 | OP_SEQUENCE |
Number(4,0) | SMALLINT NOT NULL | Operation Sequence |
| 5 | MG_ORIG_COMP_ID |
Character(18) | VARCHAR2(18) NOT NULL | original component ID |
| 6 | QTY_CODE |
Character(3) | VARCHAR2(3) NOT NULL |
Per
ASY=Assembly ORD=Order |
| 7 | SF_QTY_PER | Number(16,10) | DECIMAL(15,10) NOT NULL | 07/06/99 SCC CN# SF800-9.0: Added label Quantity |
| 8 | YIELD | Number(8,4) | DECIMAL(7,4) NOT NULL | Component Yield |
| 9 | CURR_SCHED_QTY | Number(12,4) | DECIMAL(11,4) NOT NULL | 07/28/99 SCC CN#SF800-9.0: Added label id TO_CURR_SCHED |
| 10 | ISSUE_QTY | Number(12,4) | DECIMAL(11,4) NOT NULL | 07/28/99 SCC CN#SF800-9.0: Added label id TO_ISSUE_QTY |
| 11 | YIELD_QTY | Number(12,4) | DECIMAL(11,4) NOT NULL | 10/12/99 SCC CN#SF800-9.0: Added label id TO_YIELD_QTY |
| 12 | PEND_CONSUME_QTY | Signed Number(13,4) | DECIMAL(11,4) NOT NULL | 10/12/99 SCC CN#SF800-9.0: Added label id TO_PEND_CONSUME |
| 13 | PEND_LOSS_QTY | Signed Number(13,4) | DECIMAL(11,4) NOT NULL | 10/12/99 SCC CN#SF800-9.0: Added label id TO_PEND_LOSS |
| 14 | PICK_QTY | Number(12,4) | DECIMAL(11,4) NOT NULL | Pending Pick Quantity |
| 15 | ISSUE_METHOD | Character(4) | VARCHAR2(4) NOT NULL |
Component Issue Method
COM=Use Comp. ISS=Issue KIT=Kit REPL=Replenish |
| 16 | CONFIG_CODE | Character(50) | VARCHAR2(50) NOT NULL | Product Configurator |
| 17 | SOURCE_CODE | Character(2) | VARCHAR2(2) NOT NULL |
8/5/97 lee - inactivated source code 4 - service
1=Make 2=Buy 3=Expense 4=Service 5=Floor Stock 6=Planning |
| 18 | QTY_SHORT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity Short |