CA_PV_ALL_VW

(SQL View)
 Index Back

Unpaid Txns for OnLine PV Summ

View used by Online page summary for bringing in the unpaid transactions

SELECT CHDR.BUSINESS_UNIT , CHDR.CONTRACT_NUM , CD.CONTRACT_LINE_NUM , PR.ANALYSIS_TYPE , PR.BI_DISTRIB_STATUS , PR.GL_DISTRIB_STATUS , PR.FOREIGN_CURRENCY , CHDR.CURRENCY_CD , CHDR.RT_TYPE , SUM(PR.FOREIGN_AMOUNT) , SUM(CASE WHEN PR.FOREIGN_CURRENCY = CHDR.CURRENCY_CD THEN PR.FOREIGN_AMOUNT ELSE (%Sql(CA_PV_CURRCONV, PR.FOREIGN_CURRENCY, CHDR.CURRENCY_CD, CHDR.RT_TYPE, PR.FOREIGN_AMOUNT, CASE WHEN INSTCA.CURR_TRANS_DATE = 'ACT' THEN PR.ACCOUNTING_DT ELSE PR.TRANS_DT END)) END) , 'PRCS' FROM PS_CA_CONTR_HDR CHDR , PS_CA_DETAIL CD , PS_PROJ_RESOURCE PR , PS_CA_LMT_FEE_DTL LMT , PS_CUSTOMER CUST , PS_SET_CNTRL_REC REC , PS_INSTALLATION_CA INSTCA WHERE CHDR.CONTRACT_NUM = CD.CONTRACT_NUM AND CHDR.CONTRACT_NUM = PR.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM = PR.CONTRACT_LINE_NUM AND CD.BUSINESS_UNIT_PC = PR.BUSINESS_UNIT AND CD.CONTRACT_NUM = LMT.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM = LMT.CONTRACT_LINE_NUM AND CHDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND CHDR.BUSINESS_UNIT = REC.SETCNTRLVALUE AND REC.RECNAME = 'CUSTOMER' AND REC.SETID = CUST.SETID AND((PR.ANALYSIS_TYPE = 'BIL' AND PR.BI_DISTRIB_STATUS = 'P' AND PR.GL_DISTRIB_STATUS NOT IN ('G','D')) OR (PR.ANALYSIS_TYPE = 'REV' AND PR.GL_DISTRIB_STATUS NOT IN ('G','D')) OR (PR.ANALYSIS_TYPE IN ('OLT','ROL'))) AND (CD.HOLD_UNPAID_BI = 'Y' OR CD.HOLD_UNPAID_REV = 'Y') AND ((PR.VOUCHER_ID <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_VOUCHER VCHR , PS_PYMNT_VCHR_XREF XREF WHERE VCHR.BUSINESS_UNIT = PR.BUSINESS_UNIT_AP AND VCHR.VOUCHER_ID = PR.VOUCHER_ID AND VCHR.BUSINESS_UNIT = XREF.BUSINESS_UNIT AND VCHR.VOUCHER_ID = XREF.VOUCHER_ID AND CLOSE_STATUS = 'O' AND VOUCHER_STYLE NOT IN ('JRNL','CLBK') AND PYMNT_SELCT_STATUS NOT IN ('C','E','P','X','S'))) OR (SHEET_ID <> ' ' AND EXISTS ( SELECT 'X' FROM PS_EX_SHEET_HDR SHEET WHERE SHEET.SHEET_ID = PR.SHEET_ID AND SHEET.SHEET_STATUS = 'PD'))) AND PR.CA_FEE_STATUS NOT IN ('2','3','4','5') GROUP BY CHDR.BUSINESS_UNIT, CHDR.CONTRACT_NUM, CD.CONTRACT_LINE_NUM, PR.ANALYSIS_TYPE, PR.BI_DISTRIB_STATUS, PR.GL_DISTRIB_STATUS, PR.FOREIGN_CURRENCY, CHDR.CURRENCY_CD, CHDR.RT_TYPE UNION SELECT CHDR.BUSINESS_UNIT , CHDR.CONTRACT_NUM , CD.CONTRACT_LINE_NUM , PR.ANALYSIS_TYPE , PR.BI_DISTRIB_STATUS , PR.GL_DISTRIB_STATUS , PR.FOREIGN_CURRENCY , CHDR.CURRENCY_CD , CHDR.RT_TYPE , SUM(PR.FOREIGN_AMOUNT) , SUM(CASE WHEN PR.FOREIGN_CURRENCY = CHDR.CURRENCY_CD THEN PR.FOREIGN_AMOUNT ELSE (%Sql(CA_PV_CURRCONV, PR.FOREIGN_CURRENCY, CHDR.CURRENCY_CD, CHDR.RT_TYPE, PR.FOREIGN_AMOUNT, CASE WHEN INSTCA.CURR_TRANS_DATE = 'ACT' THEN PR.ACCOUNTING_DT ELSE PR.TRANS_DT END)) END) , 'UNPD' FROM PS_CA_CONTR_HDR CHDR , PS_CA_DETAIL CD , PS_PROJ_RESOURCE PR , PS_CA_LMT_FEE_DTL LMT , PS_CUSTOMER CUST , PS_SET_CNTRL_REC REC , PS_INSTALLATION_CA INSTCA WHERE CHDR.CONTRACT_NUM = CD.CONTRACT_NUM AND CHDR.CONTRACT_NUM = PR.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM = PR.CONTRACT_LINE_NUM AND CD.BUSINESS_UNIT_PC = PR.BUSINESS_UNIT AND CD.CONTRACT_NUM = LMT.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM = LMT.CONTRACT_LINE_NUM AND CHDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND CHDR.BUSINESS_UNIT = REC.SETCNTRLVALUE AND REC.RECNAME = 'CUSTOMER' AND REC.SETID = CUST.SETID AND ((PR.ANALYSIS_TYPE = 'BIL' AND PR.BI_DISTRIB_STATUS = 'P' AND PR.GL_DISTRIB_STATUS NOT IN ('G','D')) OR (PR.ANALYSIS_TYPE = 'REV' AND PR.GL_DISTRIB_STATUS NOT IN ('G','D')) OR (PR.ANALYSIS_TYPE IN ('OLT','ROL'))) AND (CD.HOLD_UNPAID_BI = 'Y' OR CD.HOLD_UNPAID_REV = 'Y') AND ((PR.VOUCHER_ID <> ' ' AND EXISTS ( SELECT 'X' FROM PS_VOUCHER VCHR , PS_PYMNT_VCHR_XREF XREF WHERE VCHR.BUSINESS_UNIT = PR.BUSINESS_UNIT_AP AND VCHR.VOUCHER_ID = PR.VOUCHER_ID AND VCHR.BUSINESS_UNIT = XREF.BUSINESS_UNIT AND VCHR.VOUCHER_ID = XREF.VOUCHER_ID AND CLOSE_STATUS = 'O' AND VOUCHER_STYLE NOT IN ('JRNL','CLBK') AND PYMNT_SELCT_STATUS NOT IN ('C','E','P','X','S'))) OR (SHEET_ID <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_EX_SHEET_HDR SHEET WHERE SHEET.SHEET_ID = PR.SHEET_ID AND SHEET.SHEET_STATUS = 'PD'))) AND PR.CA_FEE_STATUS NOT IN ('2','3','4','5') GROUP BY CHDR.BUSINESS_UNIT, CHDR.CONTRACT_NUM, CD.CONTRACT_LINE_NUM, PR.ANALYSIS_TYPE, PR.BI_DISTRIB_STATUS, PR.GL_DISTRIB_STATUS, PR.FOREIGN_CURRENCY, CHDR.CURRENCY_CD, CHDR.RT_TYPE UNION SELECT CHDR.BUSINESS_UNIT , CHDR.CONTRACT_NUM , CD.CONTRACT_LINE_NUM , PR.ANALYSIS_TYPE , PR.BI_DISTRIB_STATUS , PR.GL_DISTRIB_STATUS , PR.FOREIGN_CURRENCY , CHDR.CURRENCY_CD , CHDR.RT_TYPE , SUM(PR.FOREIGN_AMOUNT) , SUM(CASE WHEN PR.FOREIGN_CURRENCY = CHDR.CURRENCY_CD THEN PR.FOREIGN_AMOUNT ELSE (%Sql(CA_PV_CURRCONV, PR.FOREIGN_CURRENCY, CHDR.CURRENCY_CD, CHDR.RT_TYPE, PR.FOREIGN_AMOUNT, CASE WHEN INSTCA.CURR_TRANS_DATE = 'ACT' THEN PR.ACCOUNTING_DT ELSE PR.TRANS_DT END)) END) , 'CMTD' FROM PS_CA_CONTR_HDR CHDR , PS_CA_DETAIL CD , PS_PROJ_RESOURCE PR , PS_CA_LMT_FEE_DTL LMT , PS_CUSTOMER CUST , PS_SET_CNTRL_REC REC , PS_INSTALLATION_CA INSTCA WHERE CHDR.CONTRACT_NUM = CD.CONTRACT_NUM AND CHDR.CONTRACT_NUM = PR.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM = PR.CONTRACT_LINE_NUM AND CD.BUSINESS_UNIT_PC = PR.BUSINESS_UNIT AND CD.CONTRACT_NUM = LMT.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM = LMT.CONTRACT_LINE_NUM AND CHDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND CHDR.BUSINESS_UNIT = REC.SETCNTRLVALUE AND REC.RECNAME = 'CUSTOMER' AND REC.SETID = CUST.SETID AND (CD.HOLD_UNPAID_BI = 'Y' OR CD.HOLD_UNPAID_REV = 'Y') AND ((PR.ANALYSIS_TYPE = 'BLD') OR (PR.ANALYSIS_TYPE = 'BIL' AND BI_DISTRIB_STATUS = 'W') OR (PR.ANALYSIS_TYPE = 'BIL' AND BI_DISTRIB_STATUS = 'P' AND PR.GL_DISTRIB_STATUS IN ('G','D')) OR (PR.ANALYSIS_TYPE = 'REV' AND PR.GL_DISTRIB_STATUS IN ('G','D'))) AND PR.CA_FEE_STATUS NOT IN ('2','3','4','5') GROUP BY CHDR.BUSINESS_UNIT, CHDR.CONTRACT_NUM, CD.CONTRACT_LINE_NUM, PR.ANALYSIS_TYPE, PR.BI_DISTRIB_STATUS, PR.GL_DISTRIB_STATUS, PR.FOREIGN_CURRENCY, CHDR.CURRENCY_CD, CHDR.RT_TYPE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
3 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
4 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
5 BI_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Billing Distribution Status
D=Distributed
I=Ignore
N=Not Distributed
P=Priced
U=Unbillable/Non-billable
W=Billing Worksheet
6 GL_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Distribution Status
C=Available to Contracts
D=Distributed
G=Generated
H=Hold
I=Ignored
J=Creating Journals
M=Modified
N=None
P=To be processed
X=Waiting for Reversal
7 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
8 CONTRACT_CURRENCY Character(3) VARCHAR2(3) NOT NULL Currency Code
9 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
10 FOREIGN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit (accounting entry line) in the entry currency of the item. The sum of FOREIGN_AMOUNT values on AR lines should equal the ENTRY_AMT on the pending item.
11 CONTRACT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
12 CA_PV_VWFLAG Character(4) VARCHAR2(4) NOT NULL View Type Flag