EE_PO_CLS_VW

(SQL View)
 Index Back

PO Entry Event Close View

PRJ707756: Populate CURRENCY_CD field by value of KK_ACTIVITY_LOG.BASE_CURRENCY.

SELECT A.BUSINESS_UNIT , A.PO_ID , C.UNPOST_SEQ , C.LINE_NBR , C.SCHED_NBR , C.DISTRIB_LINE_NUM , 0 , C.DST_ACCT_TYPE , B.VOUCHER_ID , 'POCLOSE' , E.KK_TRAN_ID , E.KK_TRAN_DT , E.KK_TRAN_LN , E.RVRSL_FLG , E.SEQNBR , E.BALANCING_LINE , E.REFERENCED_BUDGET , E.CLOSED_VALUE , E.STATISTICS_CODE , E.FOREIGN_CURRENCY , E.STATISTIC_AMOUNT , ' ' , E.BASE_CURRENCY , E.ACCOUNTING_DT , E.ACCOUNTING_PERIOD , E.FISCAL_YEAR , E.BUSINESS_UNIT , F.ASSOCIATED_LED_GRP , ' ' , ' ' , 0 , E.ACCOUNT , E.DEPTID , %subrec(CF16_AN_SBR, E) , %subrec(CFCC1_AN_SBR, E) ,E.MONETARY_AMOUNT , E.FOREIGN_AMOUNT , 'N' , B.PROCESS_INSTANCE , E.ENTRY_EVENT , ' ' , ' ' , E.EE_PROC_STATUS ,A.MID_ROLL_STATUS , D.ROLL_STAT_R ,C.EE_SEQ_NUM FROM PS_PO_HDR A , PS_KK_SOURCE_HDR B , PS_KK_SOURCE_LN C , PS_PO_LINE_DISTRIB D , PS_KK_ACTIVITY_LOG E , PS_BU_LED_GRP_TBL F , PS_BUL_CNTL_BUD G WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND B.KK_SOURCE_TRAN = 'PO_POENC' AND B.KK_TRAN_ID = C.KK_TRAN_ID AND B.KK_TRAN_DT = C.KK_TRAN_DT AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.PO_ID = D.PO_ID AND C.LINE_NBR = D.LINE_NBR AND C.SCHED_NBR = D.SCHED_NBR AND C.DST_ACCT_TYPE = D.DST_ACCT_TYPE AND C.DISTRIB_LINE_NUM = D.DISTRIB_LINE_NUM AND B.KK_TRAN_ID = E.KK_TRAN_ID AND B.KK_TRAN_DT = E.KK_TRAN_DT AND C.KK_TRAN_LN = E.KK_TRAN_LN AND E.ENTRY_EVENT <> ' ' AND E.EE_PROC_STATUS = 'N' AND E.RVRSL_FLG = ' ' AND E.EXPIRED_YEAR = 'N' AND E.REFERENCED_BUDGET = 'N' AND E.BUSINESS_UNIT = F.BUSINESS_UNIT AND E.LEDGER_GROUP = F.LEDGER_GROUP AND F.BUSINESS_UNIT = G.BUSINESS_UNIT AND F.LEDGER_GROUP = G.KK_LEDGER_GROUP AND G.KK_DETAIL_LED = 'Y' UNION ALL SELECT A1.BUSINESS_UNIT , A1.PO_ID , C1.UNPOST_SEQ , C1.LINE_NBR , C1.SCHED_NBR , C1.DISTRIB_LINE_NUM , 0 , C1.DST_ACCT_TYPE , B1.VOUCHER_ID , 'POCLOSE' , E1.KK_TRAN_ID , E1.KK_TRAN_DT , E1.KK_TRAN_LN , E1.RVRSL_FLG , E1.SEQNBR , E1.BALANCING_LINE , E1.REFERENCED_BUDGET , E1.CLOSED_VALUE , E1.STATISTICS_CODE , E1.FOREIGN_CURRENCY , %DecMult(-1, E1.STATISTIC_AMOUNT) , ' ' , E1.BASE_CURRENCY , E1.ACCOUNTING_DT , E1.ACCOUNTING_PERIOD , E1.FISCAL_YEAR , E1.BUSINESS_UNIT , F1.ASSOCIATED_LED_GRP , ' ' , ' ' , 0 , E1.ACCOUNT , E1.DEPTID , %subrec(CF16_AN_SBR, E1) , %subrec(CFCC1_AN_SBR, E1) , %DecMult(- 1, (E1.MONETARY_AMOUNT)) , %DecMult(- 1, (E1.FOREIGN_AMOUNT)) , 'N' , B1.PROCESS_INSTANCE , E1.ENTRY_EVENT , ' ' , ' ' , E1.EE_PROC_STATUS ,A1.MID_ROLL_STATUS ,D1.ROLL_STAT_R ,C1.EE_SEQ_NUM FROM PS_PO_HDR A1 , PS_KK_SOURCE_HDR B1 , PS_KK_SOURCE_LN C1 , PS_PO_LINE_DISTRIB D1 , PS_KK_ACTIVITY_LOG E1 , PS_BU_LED_GRP_TBL F1 , PS_BUL_CNTL_BUD G1 WHERE A1.BUSINESS_UNIT = B1.BUSINESS_UNIT AND A1.PO_ID = B1.PO_ID AND B1.KK_SOURCE_TRAN = 'PO_POENC' AND B1.KK_TRAN_ID = C1.KK_TRAN_ID AND B1.KK_TRAN_DT = C1.KK_TRAN_DT AND A1.BUSINESS_UNIT = D1.BUSINESS_UNIT AND A1.PO_ID = D1.PO_ID AND C1.LINE_NBR = D1.LINE_NBR AND C1.SCHED_NBR = D1.SCHED_NBR AND C1.DST_ACCT_TYPE = D1.DST_ACCT_TYPE AND C1.DISTRIB_LINE_NUM = D1.DISTRIB_LINE_NUM AND B1.KK_TRAN_ID = E1.KK_TRAN_ID AND B1.KK_TRAN_DT = E1.KK_TRAN_DT AND C1.KK_TRAN_LN = E1.KK_TRAN_LN AND E1.ENTRY_EVENT <> ' ' AND E1.EE_PROC_STATUS = 'N' AND E1.RVRSL_FLG = 'Y' AND E1.EXPIRED_YEAR = 'N' AND E1.REFERENCED_BUDGET = 'N' AND E1.BUSINESS_UNIT = F1.BUSINESS_UNIT AND E1.LEDGER_GROUP = F1.LEDGER_GROUP AND F1.BUSINESS_UNIT = G1.BUSINESS_UNIT AND F1.LEDGER_GROUP = G1.KK_LEDGER_GROUP AND G1.KK_DETAIL_LED = 'Y' AND NOT EXISTS( SELECT 'X' FROM PS_KK_ACTIVITY_LOG E2 WHERE E2.EE_PROC_STATUS = 'N' AND E2.EXPIRED_YEAR = 'N' AND E2.REFERENCED_BUDGET = 'N' AND E2.BUSINESS_UNIT = E1.BUSINESS_UNIT AND E2.LEDGER_GROUP = E1.LEDGER_GROUP AND E2.SEQNBR=E1.SEQNBR AND E2.CLOSED_VALUE='Y' AND E2.RVRSL_FLG = 'Y')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
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 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
5 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
6 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
7 MOD_SEQ_NBR Number(3,0) SMALLINT NOT NULL Modification Sequence Nbr
8 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
9 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
10 EE_EVENT_STEP Character(10) VARCHAR2(10) NOT NULL Entry Event Step
11 KK_TRAN_ID Character(10) VARCHAR2(10) NOT NULL Commitment Control Foreign Key for Transaction Identification.
12 KK_TRAN_DT Date(10) DATE Commitment Control Transaction Original processing date. Primary key with KK_TRAN_ID.
13 KK_TRAN_LN Number(9,0) DECIMAL(9) NOT NULL Commitment Control Transaction Line number.
14 RVRSL_FLG Character(1) VARCHAR2(1) NOT NULL Commitment Control Reversal Flag
15 SEQNBR Number(5,0) INTEGER NOT NULL Sequence Number
16 BALANCING_LINE Character(1) VARCHAR2(1) NOT NULL Balancing Line
17 REFERENCED_BUDGET Character(1) VARCHAR2(1) NOT NULL Referenced Budget
18 CLOSED_VALUE Character(1) VARCHAR2(1) NOT NULL Closed Status field value
19 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
20 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
21 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.
22 JRNL_LN_REF Character(10) VARCHAR2(10) NOT NULL Identifies a document, person, invoice, date, or any other piece of information that is associated with a journal line. It can be manually entered, or be defaulted from an accounting entry definition or from a Journal Generator Template. The journal line reference is optional, but helpful if you need to trace back to the source of the transaction.
23 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
24 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
25 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.
26 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
27 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
28 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group
29 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger
30 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.
31 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.
32 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
33 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
34 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPERUNT_BUGL_VW

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

Prompt Table: PRODUCT_BUGL_VW

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

Prompt Table: FUND_BUGL_VW

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

Prompt Table: CLASSCF_BUGL_VW

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

Prompt Table: PROGRAM_BUGL_VW

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

Prompt Table: BUD_REF_BUGL_VW

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

Prompt Table: AFFILIATE_VW

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

Prompt Table: %EDIT_INTRA01

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

Prompt Table: %EDIT_INTRA02

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

Prompt Table: CF1_BUGL_VW

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

Prompt Table: CF2_BUGL_VW

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

Prompt Table: CF3_BUGL_VW

46 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
47 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
48 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
49 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
50 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period
51 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.
52 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.
53 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
54 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
55 ENTRY_EVENT Character(10) VARCHAR2(10) NOT NULL Entry Event
56 JRNL_DESCR Character(30) VARCHAR2(30) NOT NULL Journal Description
57 OPEN_ITEM_KEY Character(30) VARCHAR2(30) NOT NULL Open Item Key
58 EE_PROC_STATUS Character(1) VARCHAR2(1) NOT NULL Entry Event Line Status
59 MID_ROLL_STATUS Character(1) VARCHAR2(1) NOT NULL Mid Roll Status
60 ROLL_STAT_R Character(1) VARCHAR2(1) NOT NULL Rolled
61 EE_SEQ_NUM Number(8,0) INTEGER NOT NULL Entry Event Sequence Number