BI_ACCR_RSN4_VW(SQL View) |
Index Back |
|---|---|
Not Ready to Accrue - Reason 4This view shows you all the lines that are not ready to accrue because the line is not in a status eligible to accrue based on the business unit setup. |
| SELECT C.OPRID ,C.RUN_CNTL_ID ,B.BUSINESS_UNIT , B.INVOICE , B.LINE_SEQ_NUM , C.ACCOUNTING_DT , 'Bill Status Not Eligible' , A.BUSINESS_UNIT_GL , B.NET_EXTENDED_AMT , B.BI_CURRENCY_CD , B.NET_EXTENDED_BSE , B.BASE_CURRENCY , C.INVOICE_RANGE_OPT , C.CUST_ID , C.BILL_CYCLE_ID , C.BILL_SOURCE_ID , C.BILL_TYPE_ID , C.RANGE_SELECTION_ID , C.START_INVOICE , C.END_INVOICE , C.STARTING_DT , C.DTTM_FROM , C.BIACCR_RSN1 , C.BIACCR_RSN2 , C.BIACCR_RSN3 , C.BIACCR_RSN4 FROM PS_BI_HDR A , PS_BI_LINE B , PS_RUN_BI_ACCR_RPT C WHERE C.BIACCR_RSN4 = 'Y' AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INVOICE = B.INVOICE AND ( A.BILL_STATUS NOT IN ( SELECT D.BILL_STATUS FROM PS_BI_BU_ACCR_STS D WHERE D.BUSINESS_UNIT = A.BUSINESS_UNIT) AND A.BILL_STATUS NOT IN ('TMP', 'INV', 'CAN') ) AND A.CONSOL_HDR = 'N' AND A.TEMPLATE_IVC_FLG = 'N' AND A.GL_LVL = 'B' AND A.ACCRUE_UNBILLED = 'Y' AND ( C.CUST_ID = ' ' OR C.CUST_ID = A.BILL_TO_CUST_ID) AND ( C.BILL_CYCLE_ID = ' ' OR C.BILL_CYCLE_ID = A.BILL_CYCLE_ID) AND ( C.BILL_SOURCE_ID = ' ' OR C.BILL_SOURCE_ID = A.BILL_SOURCE_ID) AND ( C.BILL_TYPE_ID = ' ' OR C.BILL_TYPE_ID = A.BILL_TYPE_ID) AND ( C.RANGE_SELECTION_ID = ' ' OR C.RANGE_SELECTION_ID = A.RANGE_SELECTION_ID) AND ( A.INVOICE BETWEEN C.START_INVOICE AND C.END_INVOICE OR C.START_INVOICE = ' ') AND A.ADD_DTTM >= C.DTTM_FROM AND ( B.ACCRUE_DT IS NULL OR B.ACCRUE_DT < ( SELECT F.BEGIN_DT FROM PS_BU_LED_GRP_TBL E , PS_CAL_DETP_TBL F , PS_BUS_UNIT_TBL_BI G WHERE F.CALENDAR_ID = E.CALENDAR_ID AND E.BUSINESS_UNIT = G.BUSINESS_UNIT_GL AND E.DEFAULT_LEDGER_GRP = 'Y' AND G.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.ACCOUNTING_DT BETWEEN F.BEGIN_DT AND F.END_DT AND F.SETID = ( SELECT H.SETID FROM PS_SET_CNTRL_REC H WHERE H.SETCNTRLVALUE = E.BUSINESS_UNIT AND H.RECNAME = 'CAL_DETP_TBL'))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | RUN_CNTL_ID | Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |
| 3 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 4 | INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Invoice |
| 5 | LINE_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Sequence |
| 6 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |
| 7 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 8 | BUSINESS_UNIT_GL | Character(5) | VARCHAR2(5) NOT NULL | GL Business Unit |
| 9 | NET_EXTENDED_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Net Extended Amt |
| 10 | BI_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Billing Currency Code |
| 11 | NET_EXTENDED_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Net Extended Amount |
| 12 | BASE_CURRENCY | Character(3) | VARCHAR2(3) NOT NULL | "Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. " |
| 13 | INVOICE_RANGE_OPT | Character(3) | VARCHAR2(3) NOT NULL |
Invoice Range Option
ALI=All Invoiced Bills ALL=All CUS=Cust ID CYC=Bill Cycle DT=Date Bill Added DTI=Date GRP=Copy Group ID INV=Invoice ID MUL=Multi-Select Table PVN=Public Voucher Number RGP=Reprint Group SEL=Range ID SHP=Ship ID SRC=Bill Source TYP=Bill Type |
| 14 | CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations. |
| 15 | BILL_CYCLE_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Cycle Identifier |
| 16 | BILL_SOURCE_ID | Character(10) | VARCHAR2(10) NOT NULL | Bill Source |
| 17 | BILL_TYPE_ID | Character(3) | VARCHAR2(3) NOT NULL | Bill Type Identifier |
| 18 | RANGE_SELECTION_ID | Character(30) | VARCHAR2(30) NOT NULL | Range Selection ID |
| 19 | START_INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Starting Invoice Number |
| 20 | END_INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Ending Invoice Number |
| 21 | STARTING_DT | Date(10) | DATE | Bills Added On or After |
| 22 | DTTM_FROM | DateTime(26) | TIMESTAMP | From Date/Time |
| 23 | BIACCR_RSN1 | Character(1) | VARCHAR2(1) NOT NULL | Not Marked to Accrue |
| 24 | BIACCR_RSN2 | Character(1) | VARCHAR2(1) NOT NULL | < 100% Revenue Distribution |
| 25 | BIACCR_RSN3 | Character(1) | VARCHAR2(1) NOT NULL | No Disc/Sur Chartfields |
| 26 | BIACCR_RSN4 | Character(1) | VARCHAR2(1) NOT NULL | Bill Status not Eligible |