OMBI_LD_AMT_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_LD_AMT_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 DISTINCT ' ' , oh.business_unit_om , oh.order_no , ' ' , 0 , 0 , PRC.SEQ_NUM , 1 , 'A' , oh.bill_to_cust_id , oh.address_seq_num , oh.sold_to_cust_id , oh.sold_TO_ADDR_NUM , ' ' , ' ' , 0 , oh.order_date , oh.direct_invoicing , oh.BI_currency_cd , oh.base_CURRENCY , prc.OM_ADJST_AMT , 1 , %Round(prc.OM_ADJST_AMT ,3) , %Round(prc.OM_ADJST_AMT ,3) , %Round(prc.OM_ADJST_AMT ,3) , %Round(prc.OM_ADJST_AMT ,3) , pi.product_kit_flag , PI.product_id , pi.product_id , pi.descr , pi.tax_trans_type , pi.tax_trans_sub_type , ' ' , oh.payment_method , oh.ship_from_bu , ' ' , oh.subcust_qual1 , oh.subcust_qual2 , 'S' , 'MISC' , 0 , oh.process_instance , OH.DELIVERY_ID FROM PS_PROD_ITEM PI , PS_OMBI_INTFC_TMP OH , PS_ORD_PRCSHP_ADJ PRC , PS_BUS_UNIT_TBL_OM OMBU , PS_IN_DELIVERY_ORD HDR , PS_SET_CNTRL_REC S WHERE OMBU.BUSINESS_UNIT = HDR.SOURCE_BUS_UNIT AND OH.BUSINESS_UNIT_OM = OMBU.BUSINESS_UNIT AND OH.ORDER_NO = HDR.ORDER_NO AND HDR.FREIGHT_APP = 'OM' AND HDR.ADDTL_ADJ_BILLED = 'N' AND OMBU.BUSINESS_UNIT = PRC.BUSINESS_UNIT AND HDR.ORDER_NO = PRC.ORDER_NO AND PRC.OM_ADJST_AMT <> 0 AND OH.LINE_TYPE = 'REV' AND OH.DELIVERY_ID = PRC.DELIVERY_ID AND HDR.DELIVERY_ID = PRC.DELIVERY_ID AND OMBU.MISC_CHRG_PROD_ID = PI.PRODUCT_ID AND S.SETCNTRLVALUE = OMBU.BUSINESS_UNIT AND S.RECNAME = 'PROD_ITEM' AND PI.SETID = S.SETID

# 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 DIRECT_INVOICING Character(1) VARCHAR2(1) NOT NULL Direct Invoicing

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

20 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. "

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

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

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

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

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

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

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

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

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

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

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

32 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

33 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.

34 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

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

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

37 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.

38 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.

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

40 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

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

42 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

43 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery 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	DIRECT_INVOICING	Character(1)	VARCHAR2(1) NOT NULL	Direct Invoicing
19	BI_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Billing Currency Code
20	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. "
21	UNIT_AMT	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Unit Price
22	QTY	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Quantity
23	GROSS_EXTENDED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Extended
24	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
25	AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
26	TOT_LINE_DST_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total Line Dist Amount
27	PRODUCT_KIT_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Product Kit
28	IDENTIFIER	Character(18)	VARCHAR2(18) NOT NULL	Identifier
29	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID
30	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
31	TAX_TRANS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Transaction Type 1=Sale 2=Rental 3=Service 4=Purchase 5=Lease B=Return
32	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
33	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.
34	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
35	SHIP_FROM_BU	Character(5)	VARCHAR2(5) NOT NULL	Ship from INV BU
36	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage
37	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.
38	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.
39	CHARGE_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Shipping Charge Type F=Freight M=Misc Chrgs N=Non-stock
40	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
41	POST_SHIP_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Cancel Instance
42	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
43	DELIVERY_ID	Character(10)	VARCHAR2(10) NOT NULL	Delivery ID