BI_LOC_SUM_VW

(SQL View)
 Index Back

LOC Summary view

Letter of Credit Summary view

SELECT A.LC_ID , A.LOC_DOC_ID , COUNT(*) , MIN(A.BILL_STATUS) , MIN(A.CONTRACT_NUM) , SUM(CASE WHEN A.BILL_STATUS = 'INV' THEN B.NET_EXTENDED_AMT ELSE 0.0 END) AS Invoiced , SUM(CASE WHEN B.ANALYSIS_TYPE NOT IN ('DEF' , 'OLT' , 'WTO' , 'DEL' , 'WWO') AND A.BILL_STATUS IN ('TMP' , 'TMR') THEN B.NET_EXTENDED_AMT ELSE 0.0 END) AS Unbilled_Temp , SUM(CASE WHEN A.BILL_STATUS IN ('NEW' , 'PND' , 'RDY' , 'HLD') THEN B.NET_EXTENDED_AMT ELSE 0.0 END) AS Unbilled_Real , MIN(A.BI_CURRENCY_CD) FROM PS_BI_HDR A , PS_BI_LINE B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT > ' ' AND A.INVOICE = B.INVOICE AND A.BILL_STATUS IN ('TMP', 'TMR', 'NEW', 'PND', 'RDY', 'HLD', 'FNL', 'INV') AND A.PC_DISTRIB_STATUS <> 'I' AND A.LOC_DOC_ID <> ' ' AND A.LC_ID <> ' ' AND A.CONSOL_HDR = 'N' GROUP BY A.LC_ID, A.LOC_DOC_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LC_ID Character(12) VARCHAR2(12) NOT NULL Treasury Letter of Credit Number Assigned by the System
2 LOC_DOC_ID Character(30) VARCHAR2(30) NOT NULL Letter of Credit Document ID
3 COUNT_7 Number(7,0) INTEGER NOT NULL Count Work Field
4 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
5 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
6 INVOICE_AMT_PRETAX Signed Number(28,3) DECIMAL(26,3) NOT NULL Pretax Invoice Amount
7 UNBILLED_AMT_TMP Signed Number(28,3) DECIMAL(26,3) NOT NULL Unbilled Amount (Temp Bills)
8 UNBILLED_AMT_REAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Unbilled Amount (Real Bills)
9 BI_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Billing Currency Code