|
SELECT A.BUSINESS_UNIT_GL , A.JOURNAL_ID , A.JOURNAL_DATE , A.JOURNAL_LINE , A.RECEIVER_ID , A.RECV_LN_NBR , A.DISTRIB_LINE_NUM , A.ACCOUNTING_PERIOD , A.FISCAL_YEAR , A.BUSINESS_UNIT , A.RECV_SHIP_SEQ_NBR , A.DST_ACCT_TYPE , A.GL_DISTRIB_STATUS , A.MERCHANDISE_AMT , A.PO_ID , A.ACCOUNTING_DT , A.CURRENCY_CD , A.BASE_CURRENCY , A.MONETARY_AMOUNT , A.FOREIGN_AMOUNT , A.FOREIGN_CURRENCY , A.BUSINESS_UNIT_PO , A.LINE_NBR ,%subrec(CF16_AN_SBR,A) ,%subrec(PC_CF1_N_SBR,A) , A.DEPTID , A.ACCOUNT , A.STATISTICS_CODE , A.BUDGET_HDR_STATUS ,B.LEDGER_GROUP ,L.LEDGER FROM PS_RECV_LN_ACCTG a ,PS_SET_CNTRL_REC S , PS_LED_GRP_LED_TBL L , PS_BU_LED_GRP_TBL B WHERE S.RECNAME='LED_GRP_LED_TBL' AND S.SETID=L.SETID AND S.SETCNTRLVALUE=B.BUSINESS_UNIT AND L.LEDGER_GROUP=B.LEDGER_GROUP AND PRIMARY_LEDGER = 'Y' AND B.DEFAULT_LEDGER_GRP='Y' AND B.BUSINESS_UNIT= A.BUSINESS_UNIT_GL AND NOT EXISTS ( SELECT 'X' FROM PS_RECV_LN_ACCTG ACC WHERE A.BUSINESS_UNIT = ACC.BUSINESS_UNIT AND A.RECEIVER_ID = ACC.RECEIVER_ID AND A.RECV_LN_NBR = ACC.RECV_LN_NBR AND A.RECV_SHIP_SEQ_NBR = ACC.RECV_SHIP_SEQ_NBR AND A.DISTRIB_LINE_NUM = ACC.DISTRIB_LINE_NUM AND A.APPL_JRNL_ID = ACC.APPL_JRNL_ID AND A.ACCOUNTING_PERIOD = ACC.ACCOUNTING_PERIOD AND A.FISCAL_YEAR = ACC.FISCAL_YEAR AND ACC.BUDGET_LINE_STATUS <> 'V' AND ACC.BUDGET_LINE_STATUS <> 'W' AND ACC.BUDGET_LINE_STATUS <> 'B')
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 BUSINESS_UNIT_GL |
Character(5) |
VARCHAR2(5) NOT NULL |
GL Business Unit
Prompt Table:
SP_BUPRCR_NONVW
|
| 2 |
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.
|
| 3 |
JOURNAL_DATE |
Date(10) |
DATE |
Specifies the date the journal was created.
|
| 4 |
JOURNAL_LINE |
Number(9,0) |
DECIMAL(9) NOT NULL |
Uniquely identifies a journal line with a sequence number. Within a single journal entry, the line sequence begins with 1 and automatically increments by 1 for each succeeding journal line. There can be any number of lines associated with a journal header.
|
| 5 |
RECEIVER_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Receiver number
|
| 6 |
RECV_LN_NBR |
Number(5,0) |
INTEGER NOT NULL |
Receipt Line
|
| 7 |
DISTRIB_LINE_NUM |
Number(5,0) |
INTEGER NOT NULL |
Distribution Line
|
| 8 |
ACCOUNTING_PERIOD |
Number(3,0) |
SMALLINT NOT NULL |
Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.
|
| 9 |
FISCAL_YEAR |
Number(4,0) |
SMALLINT NOT NULL |
Fiscal Year
|
| 10 |
BUSINESS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Business Unit
|
| 11 |
RECV_SHIP_SEQ_NBR |
Number(3,0) |
SMALLINT NOT NULL |
Receiver Shipping Sequence
|
| 12 |
DST_ACCT_TYPE |
Character(4) |
VARCHAR2(4) NOT NULL |
07/20/04 st: added RCN for Advance Reconciliation
02/16/98 ebn CN#CM800-2.0 : Added for MISC type for Landed Cost Enhancement
11/11/99 llr: Added ADVP for Payables Advance Payment
|
| 13 |
GL_DISTRIB_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Distribution Status
C=Available to Contracts
D=Distributed
G=Generated
H=Hold
I=Ignored
J=Creating Journals
M=Modified
N=None
P=To be processed
X=Waiting for Reversal
|
| 14 |
MERCHANDISE_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Merchandise Amt
F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
|
| 15 |
PO_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Purchase Order
|
| 16 |
ACCOUNTING_DT |
Date(10) |
DATE |
The accounting entry construction date for a given transaction (a generic field that crosses multiple
|
| 17 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|
| 18 |
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. "
|
| 19 |
MONETARY_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
|
| 20 |
FOREIGN_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Specifies the monetary amount of a debit or credit (accounting entry line) in the entry currency of the item. The sum of FOREIGN_AMOUNT values on AR lines should equal the ENTRY_AMT on the pending item.
|
| 21 |
FOREIGN_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
Foreign Currency Code
|
| 22 |
BUSINESS_UNIT_PO |
Character(5) |
VARCHAR2(5) NOT NULL |
PO Business Unit
|
| 23 |
LINE_NBR |
Number(5,0) |
INTEGER NOT NULL |
Line Number:
11/24/08 - Added TARGET label [PC product]
|
| 24 |
OPERATING_UNIT |
Character(8) |
VARCHAR2(8) NOT NULL |
Operating Unit ChartField
Prompt Table:
OPERUNT_BUGL_VW
|
| 25 |
PRODUCT |
Character(6) |
VARCHAR2(6) NOT NULL |
Product ChartField
Prompt Table:
PRODUCT_BUGL_VW
|
| 26 |
FUND_CODE |
Character(5) |
VARCHAR2(5) NOT NULL |
Fund Code
Prompt Table:
FUND_BUGL_VW
|
| 27 |
CLASS_FLD |
Character(5) |
VARCHAR2(5) NOT NULL |
Class Field
Prompt Table:
CLASSCF_BUGL_VW
|
| 28 |
PROGRAM_CODE |
Character(5) |
VARCHAR2(5) NOT NULL |
Program Code ChartField
Prompt Table:
PROGRAM_BUGL_VW
|
| 29 |
BUDGET_REF |
Character(8) |
VARCHAR2(8) NOT NULL |
Budget Reference
Prompt Table:
BUD_REF_BUGL_VW
|
| 30 |
AFFILIATE |
Character(5) |
VARCHAR2(5) NOT NULL |
Affiliate
Prompt Table:
AFFILIATE_VW
|
| 31 |
AFFILIATE_INTRA1 |
Character(10) |
VARCHAR2(10) NOT NULL |
IntraUnit Affiliate1
Prompt Table:
%EDIT_INTRA01
|
| 32 |
AFFILIATE_INTRA2 |
Character(10) |
VARCHAR2(10) NOT NULL |
Operating Unit Affiliate
Prompt Table:
%EDIT_INTRA02
|
| 33 |
CHARTFIELD1 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion chartfield 1
Prompt Table:
CF1_BUGL_VW
|
| 34 |
CHARTFIELD2 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion Chartfield 2
Prompt Table:
CF2_BUGL_VW
|
| 35 |
CHARTFIELD3 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion Chartfield 3
Prompt Table:
CF3_BUGL_VW
|
| 36 |
BUSINESS_UNIT_PC |
Character(5) |
VARCHAR2(5) NOT NULL |
PC Business Unit
Prompt Table:
%EDIT_BU_PC
|
| 37 |
PROJECT_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Project Id ChartField
Prompt Table:
%EDIT_PROJECT
|
| 38 |
ACTIVITY_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Activity ID
Prompt Table:
%EDIT_ACTIVITY
|
| 39 |
RESOURCE_TYPE |
Character(5) |
VARCHAR2(5) NOT NULL |
Source Type
Prompt Table:
PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC
|
| 40 |
RESOURCE_CATEGORY |
Character(5) |
VARCHAR2(5) NOT NULL |
Category
Prompt Table:
%EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC
|
| 41 |
RESOURCE_SUB_CAT |
Character(5) |
VARCHAR2(5) NOT NULL |
Subcategory
Prompt Table:
%EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC
|
| 42 |
ANALYSIS_TYPE |
Character(3) |
VARCHAR2(3) NOT NULL |
Analysis Type
Prompt Table:
%EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC
|
| 43 |
DEPTID |
Character(10) |
VARCHAR2(10) NOT NULL |
Department
Prompt Table:
DEPTID_BUGL_VW
|
| 44 |
ACCOUNT |
Character(10) |
VARCHAR2(10) NOT NULL |
Account
|
| 45 |
STATISTICS_CODE |
Character(3) |
VARCHAR2(3) NOT NULL |
Statistics Code
|
| 46 |
BUDGET_HDR_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid
|
| 47 |
LEDGER_GROUP |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger Group
|
| 48 |
LEDGER |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger
|