KK_K_ACTLOG_VW (SQL View)	Index Back
KK Activity Log KK Activity Log View for Analytics

KK_K_ACTLOG_VW

(SQL View)

KK Activity Log

KK Activity Log View for Analytics

SELECT A.BUSINESS_UNIT , %DateIn('1900-01-01') , 0 , ' ' , ' ' , A.LEDGER_GROUP , ' ' , 0 , 0 , A.SEQ_NBR , ' ' , ' ' , A.BUSINESS_UNIT , A.ACCOUNT , A.DEPTID , A.OPERATING_UNIT , A.PRODUCT , A.FUND_CODE , A.CLASS_FLD , A.PROGRAM_CODE , A.BUDGET_REF , A.AFFILIATE , A.AFFILIATE_INTRA1 , A.AFFILIATE_INTRA2 , A.CHARTFIELD1 , A.CHARTFIELD2 , A.CHARTFIELD3 , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.ACTIVITY_ID , A.RESOURCE_TYPE , A.BUDGET_PERIOD , A.CURRENCY_CD , 0 , A.CURRENCY_CD , A.POSTED_TOTAL_AMT , ' ' , 0 , 0 , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , 0 , A.DTTM_STAMP_SEC , %DateIn('1900-01-01') , ' ' , 'E' , ' ' , A.POSTED_TOTAL_AMT , ' ' , ' ' , ' ' , ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = '18021' AND M.MESSAGE_NBR = '327') , ' ' FROM PS_KK_K_ASCREV_STG A WHERE A.DTTM_STAMP_SEC IS NOT NULL UNION ALL SELECT A.KK_TRAN_ID , A.KK_TRAN_DT , A.KK_TRAN_LN , A.REFERENCED_BUDGET , A.BALANCING_LINE , A.LEDGER_GROUP , A.LEDGER , A.FISCAL_YEAR , A.ACCOUNTING_PERIOD , A.SEQNBR , A.RVRSL_FLG , A.CLOSED_VALUE , A.BUSINESS_UNIT , A.ACCOUNT , A.DEPTID , A.OPERATING_UNIT , A.PRODUCT , A.FUND_CODE , A.CLASS_FLD , A.PROGRAM_CODE , A.BUDGET_REF , A.AFFILIATE , A.AFFILIATE_INTRA1 , A.AFFILIATE_INTRA2 , A.CHARTFIELD1 , A.CHARTFIELD2 , A.CHARTFIELD3 , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.ACTIVITY_ID , A.RESOURCE_TYPE , A.BUDGET_PERIOD , A.FOREIGN_CURRENCY , A.FOREIGN_AMOUNT , A.BASE_CURRENCY ,(CASE WHEN B.LEDGER_GROUP_TYPE = 'E' AND B.LEDGER_TYPE_KK = 0 THEN (- A.MONETARY_AMOUNT) ELSE A.MONETARY_AMOUNT END) , A.STATISTICS_CODE , A.STATISTIC_AMOUNT , A.KK_QUANTITY , A.ENTRY_EVENT , A.EE_PROC_STATUS , A.EXPIRED_YEAR , A.KK_BUDG_TRANS_TYPE , A.KK_GEN_PARENT , A.KK_AUTO_GEN , A.ACTIVITY , C.KK_DATETIME_STAMP , A.ACCOUNTING_DT , %Cast(A.FISCAL_YEAR,Number,Character) , B.LEDGER_GROUP_TYPE , B.LEDGER_TYPE_KK , (CASE WHEN (B.LEDGER_GROUP_TYPE = 'E' AND B.LEDGER_TYPE_KK = '3' AND (B.KK_INCLUDE_PRE_ENC = '1' OR (B.KK_INCLUDE_PRE_ENC = '3' AND B.AFFECT_SPEND_OPTN = 'Y'))) THEN (-A.MONETARY_AMOUNT) WHEN (B.LEDGER_GROUP_TYPE = 'E' AND B.LEDGER_TYPE_KK <> '3' AND B.AFFECT_SPEND_OPTN = 'Y') THEN (-A.MONETARY_AMOUNT) ELSE 0 END) , C.KK_SOURCE_TRAN , C.KK_PROCESS_STATUS , D.DESCR , B.DESCR , CASE WHEN D.KK_SOURCE_TRAN IN ('AP_ACCTDSE' , 'AP_ACCT_LN' , 'AP_ACENC' , 'AP_ACEXP' , 'AP_ACTDSEC' , 'AP_VCHR_NP' , 'AP_VOUCHER') THEN C.VOUCHER_ID WHEN D.KK_SOURCE_TRAN IN ('AR_MISCPAY') THEN C.DEPOSIT_ID WHEN D.KK_SOURCE_TRAN IN ('AR_REVEST') THEN C.ITEM WHEN D.KK_SOURCE_TRAN IN ('BI_INVOICE') THEN C.INVOICE WHEN D.KK_SOURCE_TRAN IN ('CM_TRNXTN') THEN C.INV_ITEM_ID WHEN D.KK_SOURCE_TRAN IN ('EX_ACENC' , 'EX_ACEXP') THEN C.EX_DOC_ID WHEN D.KK_SOURCE_TRAN IN ('EX_EXCLOSE' , 'EX_EXSHEET') THEN C.SHEET_ID WHEN D.KK_SOURCE_TRAN IN ('EX_TRVAUTH') THEN C.TRAVEL_AUTH_ID WHEN D.KK_SOURCE_TRAN IN ('GENERIC') THEN C.TRANS_NBR WHEN D.KK_SOURCE_TRAN IN ('GL_BD_JRNL' , 'GL_JOURNAL') THEN C.JOURNAL_ID WHEN D.KK_SOURCE_TRAN IN ('GM_FA' , 'GM_FA_UPG') THEN C.PROJECT_ID WHEN D.KK_SOURCE_TRAN IN ('HP_PAY_ENC') THEN %NUMTOCHAR(C.HP_KK_DOC_ID) WHEN D.KK_SOURCE_TRAN IN ('HYP_CHECK' , 'HYP_POST') THEN C.HYP_TRAN_ID WHEN D.KK_SOURCE_TRAN IN ('PC_BUDGET' , 'PC_JOURNAL') THEN C.PROJECT_ID WHEN D.KK_SOURCE_TRAN IN ('PO_CCBATCH' , 'PO_PROCARD') THEN C.EMPLID WHEN D.KK_SOURCE_TRAN IN ('PO_POENC' , 'PO_POENCNP') THEN C.PO_ID WHEN D.KK_SOURCE_TRAN IN ('PO_RAENC' , 'PO_RAEXP') THEN C.RECEIVER_ID WHEN D.KK_SOURCE_TRAN IN ('REQ_PRECNP' , 'REQ_PREENC') THEN C.REQ_ID ELSE ' ' END FROM PS_KK_ACTIVITY_LOG A , PS_KK_K_LEDTYPE_VW B , PS_KK_SOURCE_HDR C , PS_KK_SOURCE_TRAN D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.LEDGER_GROUP = B.LEDGER_GROUP AND A.LEDGER = B.LEDGER AND A.KK_TRAN_ID = C.KK_TRAN_ID AND A.KK_TRAN_DT = C.KK_TRAN_DT AND C.KK_SOURCE_TRAN = D.KK_SOURCE_TRAN AND A.BALANCING_LINE = 'N' AND B.LEDGER_GROUP_TYPE = 'E'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 KK_TRAN_ID Character(10) VARCHAR2(10) NOT NULL Commitment Control Foreign Key for Transaction Identification.

2 KK_TRAN_DT Date(10) DATE NOT NULL Commitment Control Transaction Original processing date. Primary key with KK_TRAN_ID.

3 KK_TRAN_LN Number(9,0) DECIMAL(9) NOT NULL Commitment Control Transaction Line number.

4 REFERENCED_BUDGET Character(1) VARCHAR2(1) NOT NULL Referenced Budget
Y/N Table Edit
Default Value: N

5 BALANCING_LINE Character(1) VARCHAR2(1) NOT NULL Balancing Line
Y/N Table Edit
Default Value: N

6 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group

7 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger

8 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year

9 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.

10 SEQNBR Number(5,0) INTEGER NOT NULL Sequence Number

11 RVRSL_FLG Character(1) VARCHAR2(1) NOT NULL Commitment Control Reversal Flag

12 CLOSED_VALUE Character(1) VARCHAR2(1) NOT NULL Closed Status field value
Y/N Table Edit

13 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

14 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account

15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

16 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

32 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period

33 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code

34 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.

35 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. "

36 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.

37 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code

38 STATISTIC_AMOUNT Signed Number(17,2) DECIMAL(15,2) NOT NULL Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.

39 KK_QUANTITY Signed Number(20,4) DECIMAL(18,4) NOT NULL Commitment Control Quantity

40 ENTRY_EVENT Character(10) VARCHAR2(10) NOT NULL Entry Event

41 EE_PROC_STATUS Character(1) VARCHAR2(1) NOT NULL Entry Event Line Status

42 EXPIRED_YEAR Character(1) VARCHAR2(1) NOT NULL Expired Year
Y/N Table Edit
Default Value: N

43 KK_BUDG_TRANS_TYPE Character(1) VARCHAR2(1) NOT NULL Budget Entry Type
0=Original
1=Adjustment
2=Transfer Adjustment
3=Transfer Original
4=Closing
5=Roll Forward

44 KK_GEN_PARENT Character(1) VARCHAR2(1) NOT NULL Yes/No Indicator - Generate Parent Budgets
Default Value: N

45 KK_AUTO_GEN Character(1) VARCHAR2(1) NOT NULL Budget Processor Generated Ln
Default Value: N

46 ACTIVITY Signed Number(28,3) DECIMAL(26,3) NOT NULL Activity

47 DTTM_STAMP_SEC DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
Default Value: %Datetime

48 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

49 YEAR_CHAR Character(4) VARCHAR2(4) NOT NULL Year Char

50 LEDGER_GROUP_TYPE Character(1) VARCHAR2(1) NOT NULL Ledger Group Type
A=Average Daily Balance
B=Budget
E=Commitment Control Expense
R=Commitment Control Revenue
S=Standard
T=Translation

51 LEDGER_TYPE_KK Character(1) VARCHAR2(1) NOT NULL Commitment Control Ledger Type
0=Budget
1=Expense or Recognized Revenue
2=Encumbrance
3=Pre-Encumbrance
4=Collected Revenue
5=Planned

52 AVAILABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Prepayment Available Amount

53 KK_SOURCE_TRAN Character(10) VARCHAR2(10) NOT NULL Source Transaction Type

54 KK_PROCESS_STATUS Character(1) VARCHAR2(1) NOT NULL Process Status
E=Errors Exist
I=Document in process
N=Process Unsuccessful
P=Provisionally Valid
V=No Errors or Warnings
W=Only Warnings Exist
X=Unrecorded Errors Exist

55 DESCR Character(30) VARCHAR2(30) NOT NULL Description

56 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr

57 DOCUMENT Character(30) VARCHAR2(30) NOT NULL Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	KK_TRAN_ID	Character(10)	VARCHAR2(10) NOT NULL	Commitment Control Foreign Key for Transaction Identification.
2	KK_TRAN_DT	Date(10)	DATE NOT NULL	Commitment Control Transaction Original processing date. Primary key with KK_TRAN_ID.
3	KK_TRAN_LN	Number(9,0)	DECIMAL(9) NOT NULL	Commitment Control Transaction Line number.
4	REFERENCED_BUDGET	Character(1)	VARCHAR2(1) NOT NULL	Referenced Budget Y/N Table Edit Default Value: N
5	BALANCING_LINE	Character(1)	VARCHAR2(1) NOT NULL	Balancing Line Y/N Table Edit Default Value: N
6	LEDGER_GROUP	Character(10)	VARCHAR2(10) NOT NULL	Ledger Group
7	LEDGER	Character(10)	VARCHAR2(10) NOT NULL	Ledger
8	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
9	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.
10	SEQNBR	Number(5,0)	INTEGER NOT NULL	Sequence Number
11	RVRSL_FLG	Character(1)	VARCHAR2(1) NOT NULL	Commitment Control Reversal Flag
12	CLOSED_VALUE	Character(1)	VARCHAR2(1) NOT NULL	Closed Status field value Y/N Table Edit
13	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
14	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account
15	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
16	OPERATING_UNIT	Character(8)	VARCHAR2(8) NOT NULL	Operating Unit ChartField
17	PRODUCT	Character(6)	VARCHAR2(6) NOT NULL	Product ChartField
18	FUND_CODE	Character(5)	VARCHAR2(5) NOT NULL	Fund Code
19	CLASS_FLD	Character(5)	VARCHAR2(5) NOT NULL	Class Field
20	PROGRAM_CODE	Character(5)	VARCHAR2(5) NOT NULL	Program Code ChartField
21	BUDGET_REF	Character(8)	VARCHAR2(8) NOT NULL	Budget Reference
22	AFFILIATE	Character(5)	VARCHAR2(5) NOT NULL	Affiliate
23	AFFILIATE_INTRA1	Character(10)	VARCHAR2(10) NOT NULL	IntraUnit Affiliate1
24	AFFILIATE_INTRA2	Character(10)	VARCHAR2(10) NOT NULL	Operating Unit Affiliate
25	CHARTFIELD1	Character(10)	VARCHAR2(10) NOT NULL	Expansion chartfield 1
26	CHARTFIELD2	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 2
27	CHARTFIELD3	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 3
28	BUSINESS_UNIT_PC	Character(5)	VARCHAR2(5) NOT NULL	PC Business Unit
29	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField
30	ACTIVITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Activity ID
31	RESOURCE_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Source Type
32	BUDGET_PERIOD	Character(8)	VARCHAR2(8) NOT NULL	Budget Period
33	FOREIGN_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Foreign Currency Code
34	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.
35	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. "
36	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.
37	STATISTICS_CODE	Character(3)	VARCHAR2(3) NOT NULL	Statistics Code
38	STATISTIC_AMOUNT	Signed Number(17,2)	DECIMAL(15,2) NOT NULL	Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.
39	KK_QUANTITY	Signed Number(20,4)	DECIMAL(18,4) NOT NULL	Commitment Control Quantity
40	ENTRY_EVENT	Character(10)	VARCHAR2(10) NOT NULL	Entry Event
41	EE_PROC_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Entry Event Line Status
42	EXPIRED_YEAR	Character(1)	VARCHAR2(1) NOT NULL	Expired Year Y/N Table Edit Default Value: N
43	KK_BUDG_TRANS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Budget Entry Type 0=Original 1=Adjustment 2=Transfer Adjustment 3=Transfer Original 4=Closing 5=Roll Forward
44	KK_GEN_PARENT	Character(1)	VARCHAR2(1) NOT NULL	Yes/No Indicator - Generate Parent Budgets Default Value: N
45	KK_AUTO_GEN	Character(1)	VARCHAR2(1) NOT NULL	Budget Processor Generated Ln Default Value: N
46	ACTIVITY	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Activity
47	DTTM_STAMP_SEC	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. Default Value: %Datetime
48	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
49	YEAR_CHAR	Character(4)	VARCHAR2(4) NOT NULL	Year Char
50	LEDGER_GROUP_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Ledger Group Type A=Average Daily Balance B=Budget E=Commitment Control Expense R=Commitment Control Revenue S=Standard T=Translation
51	LEDGER_TYPE_KK	Character(1)	VARCHAR2(1) NOT NULL	Commitment Control Ledger Type 0=Budget 1=Expense or Recognized Revenue 2=Encumbrance 3=Pre-Encumbrance 4=Collected Revenue 5=Planned
52	AVAILABLE_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Prepayment Available Amount
53	KK_SOURCE_TRAN	Character(10)	VARCHAR2(10) NOT NULL	Source Transaction Type
54	KK_PROCESS_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Process Status E=Errors Exist I=Document in process N=Process Unsuccessful P=Provisionally Valid V=No Errors or Warnings W=Only Warnings Exist X=Unrecorded Errors Exist
55	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
56	DESCR1	Character(30)	VARCHAR2(30) NOT NULL	Descr
57	DOCUMENT	Character(30)	VARCHAR2(30) NOT NULL	Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.