BI_PVG_ARGL_VW

(SQL View)
 Index Back

BI invoice process


SELECT business_unit , invoice , invoice_dt , accounting_dt , bi_currency_cd , bill_status , invoice_amount , bill_to_cust_id , bill_source_id , bill_type_id , billing_frequency , billing_specialist , contract_num , project_id , gen_ar_item_flg , gen_ap_vchr_flg , ap_created_dt , ar_entry_created , gl_entry_created , ar_lvl , business_unit_to , consol_hdr , acct_month , acct_year , 'R' FROM ps_bi_PVG_IVC_VW A WHERE A.AR_ENTRY_CREATED <> 'Y' AND A.AR_LVL <> 'N' UNION SELECT business_unit , invoice , invoice_dt , accounting_dt , bi_currency_cd , bill_status , invoice_amount , bill_to_cust_id , bill_source_id , bill_type_id , billing_frequency , billing_specialist , contract_num , project_id , gen_ar_item_flg , gen_ap_vchr_flg , ap_created_dt , ar_entry_created , gl_entry_created , ar_lvl , business_unit_to , consol_hdr , acct_month , acct_year , 'A' FROM ps_bi_PVG_IVC_VW B WHERE B.GL_ENTRY_CREATED <> 'Y' UNION SELECT business_unit , invoice , invoice_dt , accounting_dt , bi_currency_cd , bill_status , invoice_amount , bill_to_cust_id , bill_source_id , bill_type_id , billing_frequency , billing_specialist , contract_num , project_id , gen_ar_item_flg , gen_ap_vchr_flg , ap_created_dt , ar_entry_created , gl_entry_created , ar_lvl , business_unit_to , consol_hdr , acct_month , acct_year , 'G' FROM ps_bi_PVG_IVC_VW G WHERE G.GL_ENTRY_CREATED = 'Y' AND EXISTS ( SELECT 'X' FROM PS_BI_ACCT_ENTRY E , PS_INSTALLATION I WHERE E.BUSINESS_UNIT = G.BUSINESS_UNIT AND E.INVOICE = G.INVOICE AND E.JOURNAL_ID = ' ' AND (E.BUDGET_HDR_STATUS = 'V' OR I.COMMITMENT_CNTL_BI <> 'Y')) UNION SELECT business_unit , invoice , invoice_dt , accounting_dt , bi_currency_cd , bill_status , invoice_amount , bill_to_cust_id , bill_source_id , bill_type_id , billing_frequency , billing_specialist , contract_num , project_id , gen_ar_item_flg , gen_ap_vchr_flg , ap_created_dt , ar_entry_created , gl_entry_created , ar_lvl , business_unit_to , consol_hdr , acct_month , acct_year , 'B' FROM ps_bi_PVG_IVC_VW K WHERE K.GL_ENTRY_CREATED = 'Y' AND EXISTS ( SELECT 'X' FROM PS_BI_ACCT_ENTRY AE , PS_INSTALLATION AI WHERE AE.BUSINESS_UNIT = K.BUSINESS_UNIT AND AE.INVOICE = K.INVOICE AND AE.BUDGET_HDR_STATUS <> 'V' AND AI.COMMITMENT_CNTL_BI = 'Y' AND AE.JOURNAL_ID = ' ') UNION SELECT business_unit , invoice , invoice_dt , accounting_dt , bi_currency_cd , bill_status , invoice_amount , bill_to_cust_id , bill_source_id , bill_type_id , billing_frequency , billing_specialist , contract_num , project_id , gen_ar_item_flg , gen_ap_vchr_flg , ap_created_dt , ar_entry_created , gl_entry_created , ar_lvl , business_unit_to , consol_hdr , acct_month , acct_year , 'P' FROM ps_bi_PVG_IVC_VW C WHERE C.AP_CREATED_DT IS NULL AND C.GEN_AP_VCHR_FLG = 'Y' AND C.BUSINESS_UNIT_TO <> ' '

# 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

Default Value: NEXT

Prompt Table: BI_HDR_IVC_VW

3 INVOICE_DT Date(10) DATE Invoice Date
4 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
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_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount
8 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer

Prompt Table: CUST_BI_A2_VW

9 BILL_SOURCE_ID Character(10) VARCHAR2(10) NOT NULL Bill Source
10 BILL_TYPE_ID Character(3) VARCHAR2(3) NOT NULL Bill Type Identifier
11 BILLING_FREQUENCY Character(3) VARCHAR2(3) NOT NULL Billing Frequency
INS=Installment
ONC=Once
REC=Recurring
12 BILLING_SPECIALIST Character(8) VARCHAR2(8) NOT NULL Billing Specialist
13 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
14 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
15 GEN_AR_ITEM_FLG Character(1) VARCHAR2(1) NOT NULL Generate AR Open Item
16 GEN_AP_VCHR_FLG Character(1) VARCHAR2(1) NOT NULL Generate AP Voucher
17 AP_CREATED_DT Date(10) DATE AP Voucher Created Date
18 AR_ENTRY_CREATED Character(1) VARCHAR2(1) NOT NULL AR Accounting Entry Created
19 GL_ENTRY_CREATED Character(1) VARCHAR2(1) NOT NULL GL Accounting Entry Created
20 AR_LVL Character(1) VARCHAR2(1) NOT NULL AR Level
H=Header is AR Open Item
L=Bill Line is AR Open Item
N=No AR Open Items
21 BUSINESS_UNIT_TO Character(5) VARCHAR2(5) NOT NULL 04/10/00 SCC (Internet Architecture): Add label BUSINESS_UNIT
22 CONSOL_HDR Character(1) VARCHAR2(1) NOT NULL Consolidated Hdr?
N=No
Y=Yes
23 MONTH1 Character(2) VARCHAR2(2) NOT NULL Month
24 YEAR1 Character(4) VARCHAR2(4) NOT NULL Year 1
25 BI_PROCESS_GRP Character(1) VARCHAR2(1) NOT NULL Process Group field is used to identify data group in the application of Billing's Chartfield Inheritance rules. 5/10/2012 Added translates for pivot grid = post invoice process
A=Accounting Entry
B=Budget Check
G=GL Journal
I=Recently Invoiced
N=Pending Invoices
P=Accounts Payable
R=Accounts Receivable