BI_HDR_ATT_VW

(SQL View)
 Index Back

Invoices with File Attachments

This view is used to retrieve all invoices that have a file attachment associated with it.

SELECT BUSINESS_UNIT , INVOICE , INVOICE_TYPE , BILL_TYPE_ID , BILL_STATUS , BILL_SOURCE_ID , BILL_TO_CUST_ID , CONTRACT_NUM , TEMPLATE_IVC_FLG FROM PS_BI_HDR H WHERE PROCESS_INSTANCE = 0 AND BILL_STATUS IN ('PND', 'CAN', 'FNL', 'HLD','INV', 'NEW','RDY') AND (EXISTS ( SELECT 'X' FROM PS_BI_HDR_ATTACH AH WHERE H.BUSINESS_UNIT = AH.BUSINESS_UNIT AND H.INVOICE = AH.INVOICE) OR EXISTS ( SELECT 'X' FROM PS_BI_LINE_ATTACH AL WHERE H.BUSINESS_UNIT = AL.BUSINESS_UNIT AND H.INVOICE = AL.INVOICE))

# 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

Prompt Table: SP_BU_BI_NONVW

2 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice

Prompt Table: BI_HDR_ATTSCHVW

3 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

Default Value: REG

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

Prompt Table: BI_TYPE

5 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
6 BILL_SOURCE_ID Character(10) VARCHAR2(10) NOT NULL Bill Source

Prompt Table: BI_SRC

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

Prompt Table: CUST_BI_A2_VW

8 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
9 TEMPLATE_IVC_FLG Character(1) VARCHAR2(1) NOT NULL Template Invoice Flag
I=Installment Bill
N=No
R=Recurring Bill