BI_CURR_LST2_VW(SQL View) |
Index Back |
|---|---|
Bill Curcnv Select List Vw |
| SELECT A.OPRID , A.RUN_CNTL_ID , H.BUSINESS_UNIT , H.INVOICE , H.BILL_TO_CUST_ID , H.BILL_STATUS , H.PROCESS_INSTANCE , H.CONSOL_HDR , H.CONSOL_BUS_UNIT , H.CONSOL_INVOICE , A.SEQ_NUM , A.INVOICE_DT_OPT , A.INVOICE_DT , A.BI_CURCNV_ACTION , H.FINAL_CURCNV_FLG , H.BI_CURRENCY_CD , H.BUSINESS_UNIT_TO FROM PS_RUN_BI_CURCNV A , PS_BI_HDR H WHERE H.TEMPLATE_IVC_FLG = 'N' AND (H.BUSINESS_UNIT = A.BUSINESS_UNIT) AND (H.BILL_TYPE_ID = A.BILL_TYPE_ID OR A.BILL_TYPE_ID = ' ') AND (H.BILL_SOURCE_ID = A.BILL_SOURCE_ID OR A.BILL_SOURCE_ID = ' ') AND (H.BILL_CYCLE_ID = A.BILL_CYCLE_ID OR A.BILL_CYCLE_ID = ' ') AND (H.RANGE_SELECTION_ID = A.RANGE_SELECTION_ID OR A.RANGE_SELECTION_ID = ' ') AND (H.BILL_TO_CUST_ID = A.CUST_ID OR A.CUST_ID = ' ') AND (H.INVOICE BETWEEN A.START_INVOICE AND A.END_INVOICE OR A.START_INVOICE = ' ') AND H.ADD_DTTM >= A.DTTM_FROM AND A.SEQ_NUM = ( SELECT MIN(A2.SEQ_NUM) FROM PS_RUN_BI_CURCNV A2 , PS_BI_HDR H2 WHERE A2.OPRID = A.OPRID AND A2.RUN_CNTL_ID = A.RUN_CNTL_ID AND H2.BUSINESS_UNIT = H.BUSINESS_UNIT AND H2.INVOICE = H.INVOICE AND H2.BILL_TYPE_ID = H.BILL_TYPE_ID AND (H2.BUSINESS_UNIT = A2.BUSINESS_UNIT) AND (H2.BILL_TYPE_ID = A2.BILL_TYPE_ID OR A2.BILL_TYPE_ID = ' ') AND (H2.BILL_SOURCE_ID = A2.BILL_SOURCE_ID OR A2.BILL_SOURCE_ID = ' ') AND (H2.BILL_CYCLE_ID = A2.BILL_CYCLE_ID OR A2.BILL_CYCLE_ID = ' ') AND (H2.RANGE_SELECTION_ID = A2.RANGE_SELECTION_ID OR A2.RANGE_SELECTION_ID = ' ') AND (H2.BILL_TO_CUST_ID = A2.CUST_ID OR A2.CUST_ID = ' ') AND (H2.INVOICE BETWEEN A2.START_INVOICE AND A2.END_INVOICE OR A2.START_INVOICE = ' ') AND H2.ADD_DTTM >= A2.DTTM_FROM) |
| # | 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_BI |
Character(5) | VARCHAR2(5) NOT NULL | Billing Business Unit |
| 4 | INVOICE |
Character(22) | VARCHAR2(22) NOT NULL | Invoice |
| 5 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 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 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 8 | CONSOL_HDR | Character(1) | VARCHAR2(1) NOT NULL |
Consolidated Hdr?
N=No Y=Yes |
| 9 | CONSOL_BUS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Specifies the Billing business unit associated with a consolidated bill. |
| 10 | CONSOL_INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Specifies the invoice identifier associated with a consolidated bill. |
| 11 | SEQ_NUM | Number(3,0) | SMALLINT NOT NULL | Sequence |
| 12 | INVOICE_DT_OPT | Character(1) | VARCHAR2(1) NOT NULL |
Invoice Date Option
P=Processing Date U=User Defined |
| 13 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 14 | BI_CURCNV_ACTION | Character(1) | VARCHAR2(1) NOT NULL |
Currency Conversion Action
B=Estimate and Finalize E=Estimate F=Finalize |
| 15 | FINAL_CURCNV_FLG | Character(1) | VARCHAR2(1) NOT NULL | Final Currency Conversion Flag |
| 16 | BI_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Billing Currency Code |
| 17 | BUSINESS_UNIT_TO | Character(5) | VARCHAR2(5) NOT NULL | 04/10/00 SCC (Internet Architecture): Add label BUSINESS_UNIT |