EXD_SHP_IUT_VW2

(SQL View)
 Index Back

STOCK TRANSFER INVOICE IUT VW

This view is created for India GST to load data for Stock Transfer Invoice page

SELECT DISTINCT SII.BUSINESS_UNIT , SII.DEMAND_SOURCE , SII.SOURCE_BUS_UNIT , SII.ORDER_NO , SII.ORDER_INT_LINE_NO , SII.SCHED_LINE_NBR , SII.INV_ITEM_ID , SII.DEMAND_LINE_NO , SII.DESTIN_BU , SII.INTERUNIT_FLG , SII.LOCATION , SII.PRODUCT_ID , SII.PARENT_PROD_ID , SII.KIT_LINE_NBR , SII.SHIP_TO_CUST_ID , SII.ADDRESS_SEQ_NUM , SII.COUNTRY AS TO_COUNTRY , SII.ADDRESS1 , SII.ADDRESS2 , SII.ADDRESS3 , SII.ADDRESS4 , SII.CITY , SII.NUM1 , SII.NUM2 , SII.HOUSE_TYPE , SII.ADDR_FIELD1 , SII.ADDR_FIELD2 , SII.ADDR_FIELD3 , SII.COUNTY , SII.STATE AS TO_STATE , SII.POSTAL , SII.GEO_CODE , SII.IN_CITY_LIMIT , SII.TRANSFER_COST , SII.CURRENCY_CD , SII.QTY_SHIPPED , SII.QTY_SHIP_BASE , SII.SHIP_ID , SII.SHIP_LINE_NO , SII.UNIT_MEASURE_SHIP , SII.UNIT_OF_MEASURE , SII.IN_FULFILL_STATE , SII.PRICE_PO , ' ' AS BILL_TO_CUST_ID , SBU.COUNTRY , SBU.STATE , ' ' AS RT_TYPE , 1 AS RATE_DIV , 1 AS RATE_MULT , SII.NET_UNIT_PRICE , TOBU.BASE_CURRENCY AS TO_CURRENCY , 'DOM' , 0 AS NET_UNIT_PRICE_BSE , ' ' AS RTV_ID , 0 AS RTV_LN_NBR FROM PS_EXD_SHP_INF_VW1 SII , PS_LOCATION_TBL SBU , PS_SET_CNTRL_REC DBCTL , PS_BUS_UNIT_TBL_IN TOBU WHERE INTERUNIT_FLG = 'Y' AND DBCTL.RECNAME = 'LOCATION_TBL' AND DBCTL.SETCNTRLVALUE = SII.SOURCE_BUS_UNIT AND DBCTL.SETID = SBU.SETID AND SBU.LOCATION = TOBU.LOCATION AND TOBU.BUSINESS_UNIT=SII.SOURCE_BUS_UNIT AND SBU.EFFDT <= ( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL WHERE SETID = SBU.SETID AND LOCATION = SBU.LOCATION AND EFF_STATUS='A' AND EFFDT<=%CurrentDateIn) AND SII.SHIP_ID <>' ' AND SII.QTY_SHIPPED > 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 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
3 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
4 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
5 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
6 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
7 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
8 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
9 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
10 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes
11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
12 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
13 PARENT_PROD_ID Character(18) VARCHAR2(18) NOT NULL Parent Product Id
14 KIT_LINE_NBR Number(3,0) SMALLINT NOT NULL Kit Line Number
15 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
16 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
18 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
19 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
20 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
21 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
22 CITY Character(30) VARCHAR2(30) NOT NULL City
23 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
24 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
25 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
26 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
27 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
28 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
29 COUNTY Character(30) VARCHAR2(30) NOT NULL County
30 STATE Character(6) VARCHAR2(6) NOT NULL State
31 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
32 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
33 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
34 TRANSFER_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price
35 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
36 QTY_SHIPPED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Shipped
37 QTY_SHIP_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Ship Base
38 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID
39 SHIP_LINE_NO Number(10,2) DECIMAL(9,2) NOT NULL Ship Line No
40 UNIT_MEASURE_SHIP Character(3) VARCHAR2(3) NOT NULL Shipment Unit of Measure
41 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
42 IN_FULFILL_STATE Character(2) VARCHAR2(2) NOT NULL Inventory Fulfillment State
10=Pending
20=Unfulfilled
30=Releasable
40=Released
50=Confirmed
60=Shipped
70=Depleted
90=Canceled
43 PRICE_PO Signed Number(17,5) DECIMAL(15,5) NOT NULL Purchase Order Price
44 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
45 COUNTRY_SHIP_FROM Character(3) VARCHAR2(3) NOT NULL Specifies the country from which the invoice contents were shipped (for VAT processing only).
46 STATE_SHIP_FROM Character(6) VARCHAR2(6) NOT NULL Ship From State
47 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
48 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
49 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
50 NET_UNIT_PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL Net Unit Price
51 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
52 EXS_TAX_TXN_TYPE Character(4) VARCHAR2(4) NOT NULL Tax Transaction Type. This field will be used in the Tax determination and calculation of taxes.
DEB=Direct Export With Bond
DEWB=Direct Export Without Bond
DIMP=Direct Import
DOM=Domestic
LEB=Local Export With Bond
LEWB=Local Export Without Bond
LIMP=Local Import
53 NET_UNIT_PRICE_BSE Signed Number(16,4) DECIMAL(14,4) NOT NULL Net Unit Price (base)
54 RTV_ID Character(10) VARCHAR2(10) NOT NULL Return To Vendor ID
55 RTV_LN_NBR Number(5,0) INTEGER NOT NULL Return To Vendor Line