SAC_SS_ERP_SH_V(SQL View) |
Index Back |
|---|---|
Ship View |
| SELECT A.BUSINESS_UNIT ,A.AUC_ID ,A.AUC_ROUND ,A.AUC_VERSION ,A.BIDNUM ,A.BIDDER_SETID ,A.BIDDER_ID ,A.BIDDER_TYPE ,A.BIDDER_LOC , A.AUC_AWARD_NBR ,A.LINE_NBR ,A.SCHED_NBR ,A.CANCEL_STATUS ,A.CHANGE_STATUS ,A.CHNG_ORD_SEQ ,A.PRICE_PO ,A.DUE_DT ,A.DUE_TIME ,A.SHIPTO_SETID ,A.SHIPTO_ID ,A.QTY_PO ,A.CURRENCY_CD ,A.CURRENCY_CD_BASE ,A.CONVERSION_RATE ,A.FREIGHT_TERMS ,A.SHIP_TYPE_ID ,A.DISTRIB_MTHD_FLG ,A.BUSINESS_UNIT_OM ,A.SCHED_LINE_NBR ,A.SHIP_TO_CUST_ID ,A.BUSINESS_UNIT_IN ,A.CARRIER_ID ,A.VAT_TREATMENT_GRP ,A.VAT_TREATMENT ,A.COUNTRY_VAT_SHIPTO ,A.COUNTRY_VAT_BILLTO ,A.COUNTRY_VAT_BILLFR ,A.VAT_TXN_TYPE_CD ,A.TAX_CD_VAT ,A.SHIP_DATE ,B.DESCR ,B.COUNTRY ,B.ADDRESS1 ,B.ADDRESS2 ,B.ADDRESS3 ,B.CITY ,B.STATE ,B.POSTAL ,B.ATTN_TO ,B.COUNTRY_CODE ,B.PHONE ,B.EXTENSION ,B.FAX FROM PS_SAC_SS_LN_SH_VW A , PS_SAC_SS_SHPTO_VW B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.AUC_ID = B.AUC_ID AND A.AUC_ROUND = B.AUC_ROUND AND A.AUC_VERSION = B.AUC_VERSION AND A.BIDNUM = B.BIDNUM AND A.BIDDER_SETID = B.BIDDER_SETID AND A.BIDDER_ID = B.BIDDER_ID AND A.BIDDER_TYPE = B.BIDDER_TYPE AND A.BIDDER_LOC = B.BIDDER_LOC AND A.AUC_AWARD_NBR = B.AUC_AWARD_NBR AND A.LINE_NBR = B.LINE_NBR AND A.SCHED_NBR = B.SCHED_NBR UNION SELECT A.BUSINESS_UNIT ,A.AUC_ID ,A.AUC_ROUND ,A.AUC_VERSION ,A.BIDNUM ,A.BIDDER_SETID ,A.BIDDER_ID ,A.BIDDER_TYPE ,A.BIDDER_LOC , A.AUC_AWARD_NBR ,A.LINE_NBR ,A.SCHED_NBR ,A.CANCEL_STATUS ,A.CHANGE_STATUS ,A.CHNG_ORD_SEQ ,A.PRICE_PO ,A.DUE_DT ,A.DUE_TIME ,A.SHIPTO_SETID ,A.SHIPTO_ID ,A.QTY_PO ,A.CURRENCY_CD ,A.CURRENCY_CD_BASE ,A.CONVERSION_RATE ,A.FREIGHT_TERMS ,A.SHIP_TYPE_ID ,A.DISTRIB_MTHD_FLG ,A.BUSINESS_UNIT_OM ,A.SCHED_LINE_NBR ,A.SHIP_TO_CUST_ID ,A.BUSINESS_UNIT_IN ,A.CARRIER_ID ,A.VAT_TREATMENT_GRP ,A.VAT_TREATMENT ,A.COUNTRY_VAT_SHIPTO ,A.COUNTRY_VAT_BILLTO ,A.COUNTRY_VAT_BILLFR ,A.VAT_TXN_TYPE_CD ,A.TAX_CD_VAT ,A.SHIP_DATE ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' FROM PS_SAC_SS_LN_SH_VW A WHERE NOT EXISTS( SELECT 'X' FROM PS_SAC_SS_SHPTO_VW B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.AUC_ID = B.AUC_ID AND A.AUC_ROUND = B.AUC_ROUND AND A.AUC_VERSION = B.AUC_VERSION AND A.BIDNUM = B.BIDNUM AND A.BIDDER_SETID = B.BIDDER_SETID AND A.BIDDER_ID = B.BIDDER_ID AND A.BIDDER_TYPE = B.BIDDER_TYPE AND A.BIDDER_LOC = B.BIDDER_LOC AND A.AUC_AWARD_NBR = B.AUC_AWARD_NBR AND A.LINE_NBR = B.LINE_NBR AND A.SCHED_NBR = B.SCHED_NBR ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | AUC_ID | Character(10) | VARCHAR2(10) NOT NULL | Event ID |
| 3 | AUC_ROUND | Number(5,0) | INTEGER NOT NULL | Event Round |
| 4 | AUC_VERSION | Number(5,0) | INTEGER NOT NULL | Event Version |
| 5 | BIDNUM | Signed Number(6,0) | DECIMAL(5) NOT NULL | Bid ID |
| 6 | BIDDER_SETID | Character(5) | VARCHAR2(5) NOT NULL | Bidder Setid |
| 7 | BIDDER_ID | Character(15) | VARCHAR2(15) NOT NULL | Bidder ID |
| 8 | BIDDER_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Bidder Type
B=Bidder C=Customer P=Public U=Uploaded V=Supplier |
| 9 | BIDDER_LOC | Character(10) | VARCHAR2(10) NOT NULL | Bidder Location |
| 10 | AUC_AWARD_NBR | Number(3,0) | SMALLINT NOT NULL | Award Number |
| 11 |  LINE_NBR |
Number(5,0) | INTEGER NOT NULL | Line Number: 11/24/08 - Added TARGET label [PC product] |
| 12 | SCHED_NBR |
Number(3,0) | SMALLINT NOT NULL |
Schedule Number
Default Value: 1 |
| 13 | CANCEL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Cancel Status
A=Active C=Closed D=Denied H=Hold O=Open P=Pending Approval X=Canceled Default Value: A |
| 14 | CHANGE_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Change Order Status
C=Changed U=Unchanged Default Value: U |
| 15 | CHNG_ORD_SEQ | Number(3,0) | SMALLINT NOT NULL | Change Order Sequence Number |
| 16 | PRICE_PO | Signed Number(17,5) | DECIMAL(15,5) NOT NULL | Purchase Order Price |
| 17 | DUE_DT | Date(10) | DATE NOT NULL | Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received. |
| 18 | DUE_TIME | Time(15) | TIMESTAMP | Due Time |
| 19 | SHIPTO_SETID | Character(5) | VARCHAR2(5) NOT NULL | ShipTo SetID |
| 20 | SHIPTO_ID | Character(10) | VARCHAR2(10) NOT NULL | Ship To Location |
| 21 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 22 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 23 | CURRENCY_CD_BASE | Character(3) | VARCHAR2(3) NOT NULL | Business Unit Base Currency |
| 24 | CONVERSION_RATE | Signed Number(17,8) | DECIMAL(15,8) NOT NULL | Conversion Rate |
| 25 | FREIGHT_TERMS | Character(10) | VARCHAR2(10) NOT NULL | Freight Terms Code |
| 26 | SHIP_TYPE_ID | Character(10) | VARCHAR2(10) NOT NULL | Ship Via Code |
| 27 | DISTRIB_MTHD_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Distribute by
A=Amount Q=Quantity Default Value: Q |
| 28 | BUSINESS_UNIT_OM | Character(5) | VARCHAR2(5) NOT NULL | Order Management Business Unit |
| 29 | SCHED_LINE_NBR | Number(6,0) | INTEGER NOT NULL | Schedule Line Number |
| 30 | SHIP_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Ship To Customer |
| 31 | BUSINESS_UNIT_IN | Character(5) | VARCHAR2(5) NOT NULL |
Inventory Unit
Default Value: SHIPTO_TBL.BUSINESS_UNIT_IN |
| 32 | CARRIER_ID | Character(10) | VARCHAR2(10) NOT NULL | Carrier ID |
| 33 | VAT_TREATMENT_GRP | Character(4) | VARCHAR2(4) NOT NULL | VAT Treatment Group |
| 34 | VAT_TREATMENT | Character(4) | VARCHAR2(4) NOT NULL | VAT Treatment |
| 35 | COUNTRY_VAT_SHIPTO | Character(3) | VARCHAR2(3) NOT NULL | Ship To VAT Rgstrn Country |
| 36 | COUNTRY_VAT_BILLTO | Character(3) | VARCHAR2(3) NOT NULL | Specifies the country where an invoice is sent (for VAT processing only). |
| 37 | COUNTRY_VAT_BILLFR | Character(3) | VARCHAR2(3) NOT NULL | Specifies the country where an invoice is generated (for VAT processing only). |
| 38 | VAT_TXN_TYPE_CD | Character(4) | VARCHAR2(4) NOT NULL | Specifies a user-defined category of business transaction that is subject to VAT accounting and reporting. The VAT code and the VAT transaction type are used in conjunction with the VAT account type to obtain the ChartFields for accounting entries. Some examples of VAT Transaction Types are Exempt Sales Exempt Purchases Triangulation EU Sales and Domestic Sales. |
| 39 | TAX_CD_VAT | Character(8) | VARCHAR2(8) NOT NULL | Defines the VAT (Value Added Tax) percentage (via link to the Tax Authority) and is used to retrieve VAT accounting ChartFields. The VAT code determines how the VAT amount is calculated on a transaction and how that amount is accounted and reported for. |
| 40 | SHIP_DATE | Date(10) | DATE | Item Shipping Date |
| 41 | SHIPTO_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Recepient Party Name1 |
| 42 | SHIPTO_COUNTRY | Character(30) | VARCHAR2(30) NOT NULL | Recepient Country |
| 43 | SHIPTO_ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Recepient Street |
| 44 | SHIPTO_ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Recepient Street Suppliment 1 |
| 45 | SHIPTO_ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Recepient Street Suppliment 2 |
| 46 | SHIPTO_CITY | Character(30) | VARCHAR2(30) NOT NULL | Recepient City |
| 47 | SHIPTO_STATE | Character(6) | VARCHAR2(6) NOT NULL | Ship To State |
| 48 | SHIPTO_POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Recepient POSTAL |
| 49 | ATTN_TO | Character(30) | VARCHAR2(30) NOT NULL | Attention |
| 50 | SHIP_COUNTRY_CODE | Character(3) | VARCHAR2(3) NOT NULL | Int'l Prefix |
| 51 | SHIPTO_PHONE | Character(24) | VARCHAR2(24) NOT NULL | Buyer Phone |
| 52 | SHIPTO_EXTENSION | Character(6) | VARCHAR2(6) NOT NULL | Phone Extension |
| 53 | SHIPTO_FAX | Character(24) | VARCHAR2(24) NOT NULL | Fax Number |