ITEM_XREF_PYVW2

(SQL View)
 Index Back

Item XREF Payment Line View


SELECT b.business_unit ,a.common_id ,a.sa_id_type ,b.item_nbr_payment ,b.item_nbr_charge ,a.account_nbr ,a.account_term ,a.item_type ,a.item_term ,a.item_amt ,b.effdt ,b.xref_amt ,b.xref_status ,b.payment_id_nbr FROM PS_ITEM_SF a ,ps_item_xref b WHERE a.business_unit=b.business_unit AND a.common_id = b.common_id AND a.sa_id_type = b.sa_id_type AND a.item_nbr=b.item_nbr_payment AND b.effdt = ( SELECT MAX(k.effdt) FROM ps_item_xref k WHERE k.business_unit = b.business_unit AND k.common_id = b.common_id AND k.sa_id_type = b.sa_id_type AND k.item_nbr_charge = b.item_nbr_charge AND k.item_nbr_payment = b.item_nbr_payment AND k.effdt <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
3 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
O=Organization
P=Person
4 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
5 ITEM_NBR_CHARGE Character(15) VARCHAR2(15) NOT NULL Item Nbr Charge
6 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
7 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
8 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
9 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
10 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
11 XREF_DT Date(10) DATE Item Cross Reference Date
12 XREF_AMT Signed Number(16,2) DECIMAL(14,2) NOT NULL Item Cross Reference Amount
13 XREF_STATUS Character(1) VARCHAR2(1) NOT NULL Xref Status
D=Default
O=Override
R=Refund
U=User Directed Payment
14 PAYMENT_ID_NBR Number(12,0) DECIMAL(12) NOT NULL Payment ID Nbr