SII_HDR_BI_VW(SQL View) |
Index Back |
|---|
| SELECT h.business_unit , h.invoice , h.VAT_ENTITY ,h.invoice_dt , %Substring(%Cast(H.invoice_dt, DATE, Character) , 1 , 4) ,%Substring(%Cast(H.invoice_dt, DATE, Character) , 6 , 2) ,h.VAT_TREATMENT_GRP ,v.VAT_DCLRTN_POINT ,h.INVOICE_TYPE ,h.INVOICE_AMOUNT ,h.INVOICE_AMT_BSE ,h.bill_type_id , s.setid , h.BILL_TO_CUST_ID , h.COUNTRY_SHIP_FROM , h.COUNTRY_SHIP_TO ,h.CUR_RT_TYPE ,h.BI_CURRENCY_CD , h.BASE_CURRENCY ,h.tot_vat , h.tot_vat_bse ,h.ORIGINAL_INVOICE , h1.invoice_dt , NULL , CASE WHEN C.JOURNAL_ID IS NULL THEN ' ' ELSE C.JOURNAL_ID END ,'N' ,'N' ,'N' ,'N' FROM PS_BI_HDR h , PS_BI_HDR_vat v ,PS_SET_CNTRL_REC S ,PS_BI_HDR h1 LEFT OUTER JOIN PS_BI_ACCT_ENTRY C ON C.INVOICE = H1.INVOICE AND C.ACCT_ENTRY_TYPE = 'AR' WHERE H.BILL_STATUS = 'INV' AND H.FINAL_CURCNV_FLG = 'Y' AND h.business_unit = v.business_unit AND h.invoice = v.invoice AND h.business_unit = h1.business_unit AND h.ORIGINAL_INVOICE = h1.invoice AND S.SETCNTRLVALUE = h.BUSINESS_UNIT AND S.RECNAME = 'CUSTOMER' AND h.VAT_ENTITY <> ' ' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | INVOICE_ID |
Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
| 3 | VAT_ENTITY | Character(20) | VARCHAR2(20) NOT NULL | Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements. |
| 4 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 5 | RPT_YEAR | Character(4) | VARCHAR2(4) NOT NULL | Reporting Year |
| 6 | RPT_PERIOD | Character(2) | VARCHAR2(2) NOT NULL | Reporting Period |
| 7 | VAT_TREATMENT_GRP | Character(4) | VARCHAR2(4) NOT NULL | VAT Treatment Group |
| 8 | VAT_DCLRTN_POINT | Character(1) | VARCHAR2(1) NOT NULL |
A flag that specifies when VAT information for sales or purchase transaction is recognized for reporting to the appropriate VAT authority. VAT is declared at invoice time, delivery time, accounting date or the time of payment.
A=At Accounting Date D=At Delivery Time I=At Invoice Time P=At Payment Time |
| 9 | INVOICE_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Invoice Type
ACR=Credit Bill ADJ=Adjustment Only bill ARB=Rebill Bill PRF=Pro Forma Invoice RAD=Regular bill with adjustments REG=Regular Bill |
| 10 | INVOICE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 11 | INVOICE_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 12 | BILL_TYPE_ID | Character(3) | VARCHAR2(3) NOT NULL | Bill Type Identifier |
| 13 | CUST_SETID | Character(5) | VARCHAR2(5) NOT NULL | Customer SetID |
| 14 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 15 | COUNTRY_SHIP_FROM | Character(3) | VARCHAR2(3) NOT NULL | Specifies the country from which the invoice contents were shipped (for VAT processing only). |
| 16 | COUNTRY_SHIP_TO | Character(3) | VARCHAR2(3) NOT NULL | Specifies the country to which the invoice contents were shipped (for VAT processing only). |
| 17 | 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. |
| 18 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 19 | 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. " |
| 20 | TOT_VAT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total VAT Amount |
| 21 | TOT_VAT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total VAT Amount |
| 22 | SII_INVOICE_ID_ORG | Character(60) | VARCHAR2(60) NOT NULL | Invoice ID |
| 23 | SII_INVOICE_DT_ORG | Date(10) | DATE | Invoice Date |
| 24 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |
| 25 | JOURNAL_ID | Character(10) | VARCHAR2(10) NOT NULL | Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier. |
| 26 | SII_MACRODATO_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
MACRODATO FLAG
Y/N Table Edit Default Value: N |
| 27 | SII_FACTURASIM_FLG | Character(1) | VARCHAR2(1) NOT NULL |
FacturaSimp Flag
Y/N Table Edit Default Value: N |
| 28 | SII_REGPREVIO_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
RegPrevio Flag
Y/N Table Edit Default Value: N |
| 29 | SII_FACDIS_FLG | Character(1) | VARCHAR2(1) NOT NULL |
FacturacionDisp
Y/N Table Edit Default Value: N |