REQ_ACCTG_LN_VW

(SQL View)
 Index Back

Requisition Acctng Line Inqery

Date Initials Reference # Description 12/23/2010 VVBK 1975675002 Modified view definition

SELECT H.BUSINESS_UNIT , H.REQ_ID , H.UNPOST_SEQ , H.KK_SOURCE_TRAN , L.LINE_NBR , L.SCHED_NBR , L.DISTRIB_LINE_NUM , 0 , L.DST_ACCT_TYPE , ' ' , HDR.REQUESTOR_ID , HDR.CHNG_ORD_BATCH , HDR.REQ_STATUS , A.KK_TRAN_ID , A.KK_TRAN_DT , H.KK_SOURCE_TRAN , 'D' , ' ' , D.BUSINESS_UNIT_GL , A.LEDGER_GROUP , A.LEDGER , A.KK_TRAN_ID , A.ACCOUNT , D.ALTACCT , A.MONETARY_AMOUNT , A.BASE_CURRENCY , A.CLOSED_VALUE , A.BUDGET_PERIOD , A.ACCOUNTING_PERIOD , A.FISCAL_YEAR , A.DEPTID , %subrec(CF16_AN_SBR , A) , %subrec(PC_CF1_N_SBR , d) , A.FOREIGN_AMOUNT , A.FOREIGN_CURRENCY , A.BALANCING_LINE FROM PS_KK_ACTIVITY_LOG A , PS_KK_SOURCE_HDR H , PS_KK_SOURCE_LN L , PS_REQ_LN_DISTRIB D , PS_REQ_HDR HDR WHERE H.REQ_ID <> ' ' AND H.KK_TRAN_ID = L.KK_TRAN_ID AND H.KK_TRAN_DT = L.KK_TRAN_DT AND L.KK_TRAN_ID = A.KK_TRAN_ID AND L.KK_TRAN_DT = A.KK_TRAN_DT AND L.KK_TRAN_LN = A.KK_TRAN_LN AND H.BUSINESS_UNIT = D.BUSINESS_UNIT AND H.BUSINESS_UNIT = HDR.BUSINESS_UNIT AND H.REQ_ID = D.REQ_ID AND H.REQ_ID = HDR.REQ_ID AND L.LINE_NBR = D.LINE_NBR AND L.SCHED_NBR = D.SCHED_NBR AND L.DISTRIB_LINE_NUM = D.DISTRIB_LINE_NUM UNION ALL SELECT H.BUSINESS_UNIT , H.REQ_ID , H.UNPOST_SEQ , H.KK_SOURCE_TRAN , 0 , 0 , 0 , 0 , ' ' , ' ' , HDR.REQUESTOR_ID , HDR.CHNG_ORD_BATCH , HDR.REQ_STATUS , A.KK_TRAN_ID , A.KK_TRAN_DT , H.KK_SOURCE_TRAN , 'D' , ' ' , A.BUSINESS_UNIT , A.LEDGER_GROUP , A.LEDGER , A.KK_TRAN_ID , A.ACCOUNT , ' ' , A.MONETARY_AMOUNT , A.BASE_CURRENCY , A.CLOSED_VALUE , A.BUDGET_PERIOD , A.ACCOUNTING_PERIOD , A.FISCAL_YEAR , A.DEPTID , %subrec(CF16_AN_SBR , A) , '' , '' , '' , '' , '' , '' , '' , A.FOREIGN_AMOUNT , A.FOREIGN_CURRENCY , A.BALANCING_LINE FROM PS_KK_ACTIVITY_LOG A , PS_KK_SOURCE_HDR H , PS_REQ_HDR HDR WHERE H.REQ_ID <> ' ' AND H.KK_TRAN_ID = A.KK_TRAN_ID AND H.KK_TRAN_DT = A.KK_TRAN_DT AND H.BUSINESS_UNIT = HDR.BUSINESS_UNIT AND H.REQ_ID = HDR.REQ_ID AND NOT EXISTS ( SELECT 'X' FROM PS_KK_SOURCE_LN L , PS_REQ_LN_DISTRIB D WHERE H.REQ_ID <> ' ' AND H.KK_TRAN_ID = L.KK_TRAN_ID AND H.KK_TRAN_DT = L.KK_TRAN_DT AND L.KK_TRAN_ID = A.KK_TRAN_ID AND L.KK_TRAN_DT = A.KK_TRAN_DT AND L.KK_TRAN_LN = A.KK_TRAN_LN AND H.BUSINESS_UNIT = D.BUSINESS_UNIT AND H.REQ_ID = D.REQ_ID AND L.LINE_NBR = D.LINE_NBR AND L.SCHED_NBR = D.SCHED_NBR AND L.DISTRIB_LINE_NUM = D.DISTRIB_LINE_NUM )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
3 UNPOST_SEQ Number(2,0) SMALLINT NOT NULL Identifies the sequence of journal entries when a journal is "unposted". When a journal is posted the UnPost Sequence is automatically set to "0". When a journal is unposted, a new reversing entry is automatically created with an UnPost Sequence of "1".
4 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template
5 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
6 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
7 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
8 MOD_SEQ_NBR Number(3,0) SMALLINT NOT NULL Modification Sequence Nbr
9 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
10 REF_REVERSAL_ID Character(10) VARCHAR2(10) NOT NULL Reference Reversal ID
11 REQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
12 CHNG_ORD_BATCH Number(3,0) SMALLINT NOT NULL Change Order Number
13 REQ_STATUS Character(4) VARCHAR2(4) NOT NULL Req status
A=Approved
C=Complete
D=Denied
I=Initial
LA=Line Approved
O=Open
P=Pending Approval
V=Preview
X=Canceled
14 KK_TRAN_ID Character(10) VARCHAR2(10) NOT NULL Commitment Control Foreign Key for Transaction Identification.
15 KK_TRAN_DT Date(10) DATE Commitment Control Transaction Original processing date. Primary key with KK_TRAN_ID.
16 KK_SOURCE_TRAN Character(10) VARCHAR2(10) NOT NULL Source Transaction Type
17 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
18 ENTRY_EVENT Character(10) VARCHAR2(10) NOT NULL Entry Event
19 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
20 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group
21 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger
22 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.
23 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
24 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
25 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.
26 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. "
27 CLOSED_VALUE Character(1) VARCHAR2(1) NOT NULL Closed Status field value
28 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period
29 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.
30 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
31 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
32 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPERUNT_BUGL_VW

33 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: PRODUCT_BUGL_VW

34 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: FUND_BUGL_VW

35 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: CLASSCF_BUGL_VW

36 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: PROGRAM_BUGL_VW

37 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: BUD_REF_BUGL_VW

38 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

39 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

40 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

41 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: CF1_BUGL_VW

42 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: CF2_BUGL_VW

43 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: CF3_BUGL_VW

44 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: %EDIT_BU_PC

45 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: %EDIT_PROJECT

46 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: %EDIT_ACTIVITY

47 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

48 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

49 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

50 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

51 PRE_ENCUM_BALANCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Pre-Encumbrance Balance
52 CURRENCY_CD1 Character(3) VARCHAR2(3) NOT NULL Currency Code
53 BALANCING_LINE Character(1) VARCHAR2(1) NOT NULL Balancing Line