ATP_S_REQ3_VW(SQL View) |
Index Back |
|---|---|
ATP Supply -- Requisitions 3View used to gather information for approved requisitions that have been staged through batch sourcing. |
| SELECT d.business_unit_in , l.inv_item_id , 'REQ3' , 'S' , 'PR' , d.req_id , d.line_nbr , d.distrib_line_num , d.sched_nbr , s.due_dt , SUM(t.qty_po_std) , d.peg_status FROM ps_po_itm_stg p , ps_po_distrib_stg t , ps_req_ln_distrib d , ps_req_line_ship s , ps_req_line l , PS_REQ_HDR h WHERE h.hold_status = 'N' AND l.business_unit = h.business_unit AND l.req_id = h.req_id AND l.curr_status = 'A' AND s.due_dt IS NOT NULL AND s.business_unit = l.business_unit AND s.req_id = l.req_id AND s.line_nbr = l.line_nbr AND d.business_unit_in <> ' ' AND d.distrib_ln_status IN ( 'O' ,'P') AND d.business_unit = s.business_unit AND d.req_id = s.req_id AND d.line_nbr = s.line_nbr AND d.sched_nbr = s.sched_nbr AND t.stage_status IN ('S','E','R','B','N','P') AND t.business_unit = d.business_unit AND t.req_id = d.req_id AND t.req_line_nbr = d.line_nbr AND t.req_sched_nbr = d.sched_nbr AND t.req_distrib_nbr = d.distrib_line_num AND p.po_process_id = 'SRC' AND p.process_instance = t.process_instance AND p.business_unit = t.business_unit AND p.po_stg_type = t.po_stg_type AND p.po_stg_id = t.po_stg_id AND p.line_nbr = t.line_nbr AND p.sched_nbr = t.sched_nbr GROUP BY d.business_unit_in, l.inv_item_id, d.req_id, d.line_nbr , d.distrib_line_num , d.sched_nbr, s.due_dt, d.peg_status |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 3 | ATP_SOURCE | Character(6) | VARCHAR2(6) NOT NULL | This field is used in the ATP and Availability calculation to determine where the particular source or demand was fetched from. It will store the up-to-6 character mnemonic used in the associated view that fetched the data. For example, if ATP_S_PO_VW was used to fetch purchase order information, this field will contain "PO". If view ATP_D_PROMIS_VW was used to fetch promised demand information, this field will contain "PROMIS". |
| 4 | AVAIL_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Availability Type
D=Demand S=Supply |
| 5 | DEMAND_SOURCE | Character(2) | VARCHAR2(2) NOT NULL |
Demand Source
IN=Material Request OM=Sales Order PL=Planning Requisition PO=Purchase Order PR=Purchasing Requisition RT=Material Return SF=Production Request WM=Work Order |
| 6 | ORDER_NO | Character(10) | VARCHAR2(10) NOT NULL | Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop |
| 7 | ORDER_INT_LINE_NO | Number(5,0) | INTEGER NOT NULL | Order Line |
| 8 | DISTRIB_LINE_NUM | Number(5,0) | INTEGER NOT NULL | Distribution Line |
| 9 | SCHED_NBR | Number(3,0) | SMALLINT NOT NULL | Schedule Number |
| 10 | SCHED_DATE | Date(10) | DATE | Schedule Date |
| 11 | QTY_REQUESTED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Requested |
| 12 | PEG_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
07/21/04 EGS: Created
10=Open 20=Completed 30=Canceled 40=Not Pegged |