BI_CUST_IVC_VW

(SQL View)
 Index Back

Bill Activity by Customer Vw


SELECT DISTINCT a.business_unit , a.bill_to_cust_id , a.invoice , a.invoice_amount , a.bi_currency_cd , a.bill_status , a.invoice_type , a.billing_specialist , %DatePart(a.add_dttm) , %Sql(EOEW_FUNCLIB_DATE_MONTH_UPD, a.add_dttm) , %Sql(EOEW_FUNCLIB_DATE_YEAR_UPD, a.add_dttm) FROM PS_BI_HDR a , ps_customer b WHERE a.bill_to_cust_id = b.cust_id AND b.setid = ( SELECT setid FROM ps_set_cntrl_rec WHERE recname = 'CUSTOMER' AND setcntrlvalue = a.business_unit) AND a.bill_status NOT IN ('CAN', 'TMP', 'TMR') OR ((a.BILL_STATUS = 'TMP' OR a.BILL_STATUS = 'TMR') AND (a.PC_DISTRIB_STATUS <> 'I')) AND a.CONSOL_HDR <> 'Y' AND a.TEMPLATE_IVC_FLG = 'N'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

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

Prompt Table: CUSTOMER

3 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
4 INVOICE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount
5 BI_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Billing Currency Code
6 BILL_STATUS Character(3) VARCHAR2(3) NOT NULL Bill Status
CAN=Canceled
FNL=Finalized Bill
HLD=Hold Bill
INV=Invoiced Bill
NEW=New Bill
PND=Pending Approval
RDY=Ready to Invoice
TMP=Temporary Bill
TMR=Temporary Ready Bill
7 INVOICE_TYPE Character(3) VARCHAR2(3) NOT NULL Invoice Type
ACR=Credit Bill
ADJ=Adjustment Only bill
ARB=Rebill Bill
PRF=Pro Forma Invoice
RAD=Regular bill with adjustments
REG=Regular Bill
8 BILLING_SPECIALIST Character(8) VARCHAR2(8) NOT NULL Billing Specialist
9 DATE_ADDED Date(10) DATE Date Added
10 ADD_MONTH Character(2) VARCHAR2(2) NOT NULL Add Month
01=January
02=February
03=March
04=April
05=May
06=June
07=July
08=August
09=September
10=October
11=November
12=December
11 ADD_YEAR Character(4) VARCHAR2(4) NOT NULL Add Year