TD_PROMO_INV(SQL View) |
Index Back |
|---|---|
Invoices for a promotion |
| SELECT E.SETID , D.PPRC_PROMO_CD , B.SOLD_TO_CUST_ID , D.MERCH_TYPE , A.BUSINESS_UNIT , A.INVOICE , B.INVOICE_LINE , B.LINE_SEQ_NUM , A.INVOICE_AMOUNT , A.INVOICE_TYPE , A.BILL_TYPE_ID , A.BILL_SOURCE_ID , A.SYSTEM_SOURCE , A.BILL_TO_CUST_ID , B.BI_CURRENCY_CD , B.BASE_CURRENCY , B.UNIT_OF_MEASURE , B.QTY , B.UNIT_AMT , B.ORIG_AMOUNT , %DecMult(D.AMOUNT,-1) , D.AMOUNT_BSE , A.DT_INVOICED , ' ' FROM PS_BI_HDR A , PS_BI_LINE B , PS_SET_CNTRL_REC C , PS_BI_LINE_DS_DTL D , PS_PPRC_PROMO_TBL E WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INVOICE = B.INVOICE AND ( A.INVOICE_TYPE = 'RAD' OR A.INVOICE_TYPE = 'REG') AND B.BUSINESS_UNIT = D.BUSINESS_UNIT AND B.INVOICE = D.INVOICE AND B.LINE_SEQ_NUM = D.LINE_SEQ_NUM AND D.PPRC_PROMO_CD <> ' ' AND E.PPRC_PROMO_CD = D.PPRC_PROMO_CD AND A.BILL_STATUS = 'INV' AND C.RECNAME = 'PPRC_PROMO_TBL' AND C.SETCNTRLVALUE = E.SETID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | PPRC_PROMO_CD |
Character(20) | VARCHAR2(20) NOT NULL | Price Promotion Code |
| 3 | SOLD_TO_CUST_ID |
Character(15) | VARCHAR2(15) NOT NULL | Sold To Customer |
| 4 | MERCH_TYPE |
Character(10) | VARCHAR2(10) NOT NULL | Merchandising Type |
| 5 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BUARDS_NONVW |
| 6 | INVOICE |
Character(22) | VARCHAR2(22) NOT NULL |
Invoice
Prompt Table: BI_HDR |
| 7 | INVOICE_LINE |
Number(5,0) | INTEGER NOT NULL | Invoice Line |
| 8 | LINE_SEQ_NUM |
Number(5,0) | INTEGER NOT NULL | Sequence |
| 9 | INVOICE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 10 | 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 |
| 11 | BILL_TYPE_ID | Character(3) | VARCHAR2(3) NOT NULL |
Bill Type Identifier
Prompt Table: BI_TYPE_EF_VW |
| 12 | BILL_SOURCE_ID | Character(10) | VARCHAR2(10) NOT NULL |
Bill Source
Prompt Table: BI_SRC_EF_VW |
| 13 | SYSTEM_SOURCE | Character(3) | VARCHAR2(3) NOT NULL | Identifies the application or source system that generated a journal entry. Release 8.80 - 11/14/2002 - RVlasic - Removed GDM (JrnlGen - Deduction Management) from Translate Values. SUJ---Included a label as Product. Release 8.9 - 05/11/2005 - RVlasic - Added PRV (Variance Pricing) and PKK (KK Budget Journal) and deleted GLK for Project Costing per Brian Cohen. |
| 14 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL |
Customer
Prompt Table: %EDITTABLE3 |
| 15 | BI_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Billing Currency Code |
| 16 | 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. " |
| 17 | UNIT_OF_MEASURE | Character(3) | VARCHAR2(3) NOT NULL |
Used on an approval rule set.
MHR=Muti Hourly PER=Percentage SQF=Square Footage Prompt Table: UNITS_TBL |
| 18 | QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity |
| 19 | UNIT_AMT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Unit Price |
| 20 | ORIG_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Original Amount |
| 21 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 22 | AMOUNT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 23 | DT_INVOICED | Date(10) | DATE | Date Invoiced |
| 24 | FLAG2 | Character(1) | VARCHAR2(1) NOT NULL | Flag2 |