SII_HDR_AR_VW(SQL View) |
Index Back |
|---|
| SELECT B.business_unit , B.cust_id , B.item , B.item_line , B.item_seq_num , I.VAT_ENTITY , B.Group_ID , B.GROUP_SEQ_NUM , B.Accounting_dt , %Substring(%Cast(B.Accounting_dt, DATE, Character) , 1 , 4) ,%Substring(%Cast(B.Accounting_dt, DATE, Character) , 6 , 2) ,I.VAT_TREATMENT_GRP ,I.VAT_DCLRTN_POINT , B.ENTRY_AMT , B.ENTRY_AMT_BASE , B.ENTRY_TYPE , S.setid , I.COUNTRY_SHIP_FROM , I.COUNTRY_SHIP_TO , B.rt_type , B.ENTRY_CURRENCY , B.CURRENCY_CD , NULL ,C.JOURNAL_ID , 'N' ,'N' , 'N' , 'N' FROM PS_ITEM_activity B , PS_ITEM I ,PS_SET_CNTRL_REC S , PS_ITEM_act_vat A ,PS_ITEM_DST C WHERE B.business_unit = I.business_unit AND B.cust_id = I.cust_id AND B.item = I.item AND B.item_line = I.ITEM_line AND B.business_unit = A.business_unit AND B.cust_id = A.cust_id AND B.item = A.item AND B.item_line = A.ITEM_line AND B.ITEM_SEQ_NUM = A.ITEM_SEQ_NUM AND B.business_unit = C.Business_unit AND B.cust_id = C.Cust_id AND B.item = C.Item AND B.item_line = C.ITEM_line AND B.ITEM_SEQ_NUM = C.ITEM_SEQ_NUM AND A.VAT_DISTRIB_STATUS <> 'I' AND S.SETCNTRLVALUE = B.BUSINESS_UNIT AND S.RECNAME = 'CUSTOMER' AND I.VAT_ENTITY <> ' ' AND B.ENTRY_USE_ID IN ('IT-01', 'IT-02') AND EXISTS ( SELECT 'X' FROM PS_ORIGIN_TBL R , PS_SET_CNTRL_REC S1 WHERE R.origin_id=b.origin_id AND R.origin_method = 'S' AND R.setid = ( SELECT setid FROM ps_set_cntrl_rec WHERE setcntrlvalue = a.business_unit AND recname = 'ORIGIN_TBL') AND R.effdt=( SELECT MAX(effdt) FROM ps_origin_tbl WHERE setid = R.setid AND origin_id = R.origin_id AND effdt <= %CurrentDateIn AND eff_status = 'A') ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | BILL_TO_CUST_ID |
Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 3 | ITEM |
Character(30) | VARCHAR2(30) NOT NULL | Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number. |
| 4 | ITEM_LINE |
Number(6,0) | INTEGER NOT NULL | Item Line |
| 5 | ITEM_SEQ_NUM |
Number(3,0) | SMALLINT NOT NULL | Item Sequence Number |
| 6 | 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. |
| 7 | GROUP_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies a collection of pending items that are to be posted to accounts receivable. The identifier is manually entered or automatically generated by the system. |
| 8 | GROUP_SEQ_NUM | Number(6,0) | INTEGER NOT NULL | Specifies the system-assigned sequence number of a pending item within a group. A group is a postable entity that may be entered online or created by the system. In either case the group sequence number is automatically generated. |
| 9 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 10 | RPT_YEAR | Character(4) | VARCHAR2(4) NOT NULL | Reporting Year |
| 11 | RPT_PERIOD | Character(2) | VARCHAR2(2) NOT NULL | Reporting Period |
| 12 | VAT_TREATMENT_GRP | Character(4) | VARCHAR2(4) NOT NULL | VAT Treatment Group |
| 13 | 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 |
| 14 | INVOICE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 15 | INVOICE_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 16 | ENTRY_TYPE | Character(5) | VARCHAR2(5) NOT NULL | Specifies the type of transaction associated with a pending item. The entry type remains with the item after it is posted and affects many areas of Receivables processing. Some examples of entry types are Invoices Credit Memo Prepayment and Deduction. |
| 17 | CUST_SETID | Character(5) | VARCHAR2(5) NOT NULL | Customer SetID |
| 18 | COUNTRY_SHIP_FROM | Character(3) | VARCHAR2(3) NOT NULL | Specifies the country from which the invoice contents were shipped (for VAT processing only). |
| 19 | COUNTRY_SHIP_TO | Character(3) | VARCHAR2(3) NOT NULL | Specifies the country to which the invoice contents were shipped (for VAT processing only). |
| 20 | 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. |
| 21 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 22 | 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. " |
| 23 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |
| 24 | 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. |
| 25 | SII_MACRODATO_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
MACRODATO FLAG
Y/N Table Edit Default Value: N |
| 26 | SII_FACTURASIM_FLG | Character(1) | VARCHAR2(1) NOT NULL |
FacturaSimp Flag
Y/N Table Edit Default Value: N |
| 27 | SII_REGPREVIO_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
RegPrevio Flag
Y/N Table Edit Default Value: N |
| 28 | SII_FACDIS_FLG | Character(1) | VARCHAR2(1) NOT NULL |
FacturacionDisp
Y/N Table Edit Default Value: N |