OMBI_MISC_VW (SQL View)	Index Back
OM Bill Addtl Ship Chrg Vw This view is used during the Populate Billing background process to get any additional shipping charges over and above freight charges to pass to the billing system to be invoiced.

OMBI_MISC_VW

(SQL View)

OM Bill Addtl Ship Chrg Vw

This view is used during the Populate Billing background process to get any additional shipping charges over and above freight charges to pass to the billing system to be invoiced.

SELECT ' ' , sfm.source_bus_unit , sfm.order_no , sfm.ship_id , 0 , 0 , sfm.seq_num , sfm.seq_num , sfm.billing_status , oh.bill_to_cust_id , oh.address_seq_num , oh.sold_to_cust_id , oh.address_seq_sold , oh.customer_po , oh.ship_to_cust_id , oh.address_seq_ship , oh.order_date , oh.bill_type_id , sfm.direct_invoicing , oh.currency_cd , oh.currency_cd_base , sfm.unit_amt , sfm.qty , %Round(%DecMult(sfm.unit_amt, sfm.qty) ,3) , %Round(%DecMult(sfm.unit_amt, sfm.qty) ,3) , %Round(%DecMult(sfm.unit_amt, sfm.qty) ,3) , %Round(%DecMult(sfm.unit_amt, sfm.qty) ,3) , pi.product_kit_flag , sfm.product_id , sfm.product_id , sfm.descr , pi.tax_trans_type , pi.tax_trans_sub_type , oh.pymnt_terms_cd , oh.payment_method , sfm.business_unit , sfm.unit_of_measure , oh.subcust_qual1 , oh.subcust_qual2 , sfm.charge_type ,'MISC' , sfm.post_ship_instance , pi.INV_ITEM_ID FROM PS_PROD_ITEM PI , PS_SET_CNTRL_REC S , PS_ORD_HEADER OH , PS_SHIP_FRT_MISC SFM WHERE sfm.demand_source = 'OM' AND sfm.source_bus_unit = oh.business_unit AND sfm.order_no = oh.order_no AND s.setcntrlvalue = sfm.business_unit AND s.recname = 'PROD_ITEM' AND pi.setid = s.setid AND pi.product_kit_flag = 'N' AND pi.product_id = sfm.product_id AND sfm.billing_status = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 BUSINESS_UNIT_OM Character(5) VARCHAR2(5) NOT NULL Order Management Business Unit

3 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

4 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID

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 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence

8 SEQUENCE_NBR Number(2,0) SMALLINT NOT NULL Sequence Number

9 BILLING_STATUS Character(1) VARCHAR2(1) NOT NULL Billing Status
A=Available
B=In Billing
C=Contract
I=Invoiced
N=Not Avail
P=In Process
S=Staged

10 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer

11 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number

12 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

13 SOLD_TO_ADDR_NUM Number(5,0) INTEGER NOT NULL Sold To Address Sequence Num

14 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.

15 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer

16 SHIP_TO_ADDR_NUM Number(5,0) INTEGER NOT NULL Ship To Address Sequence Num

17 ORDER_DATE Date(10) DATE Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop

18 BILL_TYPE_ID Character(3) VARCHAR2(3) NOT NULL Bill Type Identifier

19 DIRECT_INVOICING Character(1) VARCHAR2(1) NOT NULL Direct Invoicing

20 BI_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Billing Currency Code

21 BASE_CURRENCY Character(3) VARCHAR2(3) NOT NULL "Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "

22 UNIT_AMT Signed Number(17,4) DECIMAL(15,4) NOT NULL Unit Price

23 QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity

24 GROSS_EXTENDED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Extended

25 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount

26 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount

27 TOT_LINE_DST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Line Dist Amount

28 PRODUCT_KIT_FLAG Character(1) VARCHAR2(1) NOT NULL Product Kit

29 IDENTIFIER Character(18) VARCHAR2(18) NOT NULL Identifier

30 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID

31 DESCR Character(30) VARCHAR2(30) NOT NULL Description

32 TAX_TRANS_TYPE Character(1) VARCHAR2(1) NOT NULL Transaction Type
1=Sale
2=Rental
3=Service
4=Purchase
5=Lease
B=Return

33 TAX_TRANS_SUB_TYPE Character(1) VARCHAR2(1) NOT NULL Transaction Sub Type
1=Property
2=Freight
3=Service
4=Rental or Lease
5=Expense
6=Misc
N=None

34 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.

35 PAYMENT_METHOD Character(3) VARCHAR2(3) NOT NULL Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft. 10/28/2003 edw 655070000 - Removed LTC and CSH xlats.
ACH=ACH
CC=Credit Card
CHK=Check
CSH=Cash
DD=Direct Debit
DR=Draft
EC=Electronic Check
EFT=Electronic Fund Transfer
GE=Giro - EFT
PL=PayPal
WIR=Wire Report

36 SHIP_FROM_BU Character(5) VARCHAR2(5) NOT NULL Ship from INV BU

37 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

38 SUBCUST_QUAL1 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

39 SUBCUST_QUAL2 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

40 CHARGE_TYPE Character(1) VARCHAR2(1) NOT NULL Shipping Charge Type
F=Freight
M=Misc Chrgs
N=Non-stock

41 LINE_TYPE Character(4) VARCHAR2(4) NOT NULL Line Type 1
ADV=Advance Billing
BAE=Billed Amount in Excess
BRT=Billing Retainage
DAPP=Cust Deposit Applied
DEP=Customer Deposit
DISC=Discount Line
DREF=Cust Deposit Refunded
DRFF=Cust Deposit Refund Fee
FRGT=Freight (Surcharge)
MISC=Miscellaneous Charge
PYCV=Payment with Corporate Voucher
RAE=Reclaimed Amount in Excess
REV=Revenue Line
RSTK=Restock (Surcharge)
SUR=Surcharge Line
UTL=Utilization Line

42 POST_SHIP_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Cancel Instance

43 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	BUSINESS_UNIT_OM	Character(5)	VARCHAR2(5) NOT NULL	Order Management Business Unit
3	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
4	SHIP_ID	Character(10)	VARCHAR2(10) NOT NULL	Shipping ID
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	SEQ_NUM	Number(3,0)	SMALLINT NOT NULL	Sequence
8	SEQUENCE_NBR	Number(2,0)	SMALLINT NOT NULL	Sequence Number
9	BILLING_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Billing Status A=Available B=In Billing C=Contract I=Invoiced N=Not Avail P=In Process S=Staged
10	BILL_TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Customer
11	ADDRESS_SEQ_NUM	Number(5,0)	INTEGER NOT NULL	Address Sequence Number
12	SOLD_TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Sold To Customer
13	SOLD_TO_ADDR_NUM	Number(5,0)	INTEGER NOT NULL	Sold To Address Sequence Num
14	PO_REF	Character(30)	VARCHAR2(30) NOT NULL	Specifies the purchase order number associated with a receivables item.
15	SHIP_TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Ship To Customer
16	SHIP_TO_ADDR_NUM	Number(5,0)	INTEGER NOT NULL	Ship To Address Sequence Num
17	ORDER_DATE	Date(10)	DATE	Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
18	BILL_TYPE_ID	Character(3)	VARCHAR2(3) NOT NULL	Bill Type Identifier
19	DIRECT_INVOICING	Character(1)	VARCHAR2(1) NOT NULL	Direct Invoicing
20	BI_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Billing Currency Code
21	BASE_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	"Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "
22	UNIT_AMT	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Unit Price
23	QTY	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Quantity
24	GROSS_EXTENDED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Extended
25	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
26	AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
27	TOT_LINE_DST_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total Line Dist Amount
28	PRODUCT_KIT_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Product Kit
29	IDENTIFIER	Character(18)	VARCHAR2(18) NOT NULL	Identifier
30	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID
31	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
32	TAX_TRANS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Transaction Type 1=Sale 2=Rental 3=Service 4=Purchase 5=Lease B=Return
33	TAX_TRANS_SUB_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Transaction Sub Type 1=Property 2=Freight 3=Service 4=Rental or Lease 5=Expense 6=Misc N=None
34	PYMNT_TERMS_CD	Character(5)	VARCHAR2(5) NOT NULL	Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
35	PAYMENT_METHOD	Character(3)	VARCHAR2(3) NOT NULL	Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft. 10/28/2003 edw 655070000 - Removed LTC and CSH xlats. ACH=ACH CC=Credit Card CHK=Check CSH=Cash DD=Direct Debit DR=Draft EC=Electronic Check EFT=Electronic Fund Transfer GE=Giro - EFT PL=PayPal WIR=Wire Report
36	SHIP_FROM_BU	Character(5)	VARCHAR2(5) NOT NULL	Ship from INV BU
37	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage
38	SUBCUST_QUAL1	Character(15)	VARCHAR2(15) NOT NULL	Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
39	SUBCUST_QUAL2	Character(15)	VARCHAR2(15) NOT NULL	Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
40	CHARGE_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Shipping Charge Type F=Freight M=Misc Chrgs N=Non-stock
41	LINE_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Line Type 1 ADV=Advance Billing BAE=Billed Amount in Excess BRT=Billing Retainage DAPP=Cust Deposit Applied DEP=Customer Deposit DISC=Discount Line DREF=Cust Deposit Refunded DRFF=Cust Deposit Refund Fee FRGT=Freight (Surcharge) MISC=Miscellaneous Charge PYCV=Payment with Corporate Voucher RAE=Reclaimed Amount in Excess REV=Revenue Line RSTK=Restock (Surcharge) SUR=Surcharge Line UTL=Utilization Line
42	POST_SHIP_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Cancel Instance
43	INV_ITEM_ID	Character(18)	VARCHAR2(18) NOT NULL	Item ID