KK_CBJ_MAP_VW(SQL View) |
Index Back |
---|---|
CBJ Header Data For MAPControl Budget Journal Line Data For Mobile AF |
SELECT DISTINCT H.BUSINESS_UNIT , H.JOURNAL_ID , H.JOURNAL_DATE , B.SETID1 , H.LEDGER_GROUP , B.EFFDT , B.BUDG_TYPE , H.FISCAL_YEAR , H.ACCOUNTING_PERIOD , H.JRNL_TOTAL_DEBITS , H.JRNL_TOT_CREDITS , H.KK_BUDG_TRANS_TYPE , O.OPRDEFNDESC , H.DESCR , H.CURRENCY_CD , H.OPRID , %DatePart(H.JRNL_CREATE_DTTM) , H.ATTACHMENT_EXIST , %DatePart(U.EOAWDTTM_MODIFIED) , U.OPRID , U.EOAWORIG_OPRID , U.EOAWDTTM_COMPLETE , S.EOAWTHREAD_ID , U.EOAWSTEP_STATUS , S.EOAWPRCS_ID , S.EOAWDEFN_ID , S.EFFDT , S.EOAWSTEP_NBR , X.ORIGINATORID , X.EOAWREQUESTOR_ID , X.EOAWTHREAD_STATUS , X.EOAWDTTM_MODIFIED , U.DTTM_CREATED , ' ' FROM PS_KK_BUDGET_HDR H , PS_KK_AF_XREF X , PS_EOAW_STEPINST S , PS_EOAW_USERINST U , PSOPRDEFN O , PS_SET_CNTRL_REC R , PS_KK_BUDGET_TYPE B WHERE H.BUSINESS_UNIT=X.BUSINESS_UNIT AND H.JOURNAL_ID=X.JOURNAL_ID AND H.JOURNAL_DATE=X.JOURNAL_DATE AND X.EOAWTHREAD_ID=S.EOAWTHREAD_ID AND X.EOAWPRCS_ID=S.EOAWPRCS_ID AND X.EOAWDEFN_ID=S.EOAWDEFN_ID AND X.JOURNAL_LINE = 0 AND S.EOAWSTEP_INSTANCE=U.EOAWSTEP_INSTANCE AND O.OPRID = X.EOAWREQUESTOR_ID AND R.SETCNTRLVALUE = H.BUSINESS_UNIT AND R.RECNAME = 'KK_BUDGET_TYPE' AND B.SETID1 = R.SETID AND B.LEDGER_GROUP = H.LEDGER_GROUP AND B.EFF_STATUS <> 'I' AND B.EFFDT=( SELECT MAX(Y.EFFDT) FROM PS_KK_BUDGET_TYPE Y WHERE Y.SETID1 = B.SETID1 AND Y.LEDGER_GROUP = B.LEDGER_GROUP AND Y.EFFDT<=H.JOURNAL_DATE) AND X.EOAWTHREAD_ID=( SELECT MAX(X1.EOAWTHREAD_ID) FROM PS_KK_AF_XREF X1 WHERE X1.BUSINESS_UNIT=X.BUSINESS_UNIT AND X1.JOURNAL_ID=X.JOURNAL_ID AND X1.JOURNAL_DATE=X.JOURNAL_DATE AND X1.JOURNAL_LINE = X.JOURNAL_LINE) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_GL_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 | SETID1 | Character(5) | VARCHAR2(5) NOT NULL | SetID Value |
5 | LEDGER_GROUP | Character(10) | VARCHAR2(10) NOT NULL | Ledger Group |
6 | EFFDT_LDGR_GRP1 | Date(10) | DATE | Effective Date for a ledger group |
7 | BUDG_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Budget Type
E=Expense R=Revenue |
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 | JRNL_TOTAL_DEBITS | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Represents a running total of the debit amounts in a journal entry. It is maintained by PeopleSoft General Ledger as lines are entered. |
11 | JRNL_TOT_CREDITS | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Represents a running total of the credit amounts in a journal entry. It is maintained by PeopleSoft General Ledger as lines are entered. |
12 | 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 |
13 | OPRDEFNDESC | Character(30) | VARCHAR2(30) NOT NULL | Description field for a user |
14 | JRNL_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Journal Description |
15 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
16 | ENTERED_BY | Character(30) | VARCHAR2(30) NOT NULL | Entered by |
17 | ENTERED_DT | Date(10) | DATE | Entered on |
18 | ATTACHMENT_EXIST | Character(1) | VARCHAR2(1) NOT NULL |
Attachments Exist
N=N Y=Y |
19 | EOAWSTART_DT | Date(10) | DATE | Start Date |
20 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
21 | EOAWORIG_OPRID | Character(30) | VARCHAR2(30) NOT NULL | User ID |
22 | EOAWDTTM_COMPLETE | DateTime(26) | TIMESTAMP | Date/Time Completed |
23 | EOAWTHREAD_ID | Number(15,0) | DECIMAL(15) NOT NULL | Approval Framework Thread ID |
24 | EOAWSTEP_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Step Instance Status
A=Approved B=Pushed back C=Auto Approved D=Denied E=Administrator denied F=Deleted G=Bypassed H=On Hold I=No Action Taken K=Skipped L=Terminated M=Administrator approved N=Not Active P=Pending R=No longer in role T=Terminated U=Auto approved X=Deleted Z=Pending Denial |
25 | EOAWPRCS_ID | Character(30) | VARCHAR2(30) NOT NULL | Approval Process ID |
26 | EOAWDEFN_ID | Character(30) | VARCHAR2(30) NOT NULL | Approval Framework Definition ID |
27 | EFFDT | Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
28 | EOAWSTEP_NBR | Number(6,2) | DECIMAL(5,2) NOT NULL | Sequence number of step in its path |
29 | ORIGINATORID | Character(30) | VARCHAR2(30) NOT NULL | Originator Identifier |
30 | EOAWREQUESTOR_ID | Character(30) | VARCHAR2(30) NOT NULL | Requestor id |
31 | EOAWTHREAD_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Thread Status
A=Approved C=Complete D=Denied E=Pending Denial H=Hard Deny I=Initial N=Not Active P=Pending S=Awaiting Further Approvals T=Terminated W=Canceled X=Suspended/Pending Denial |
32 | EOAWDTTM_MODIFIED | DateTime(26) | TIMESTAMP | Datetime modified |
33 | DTTM_CREATED | DateTime(26) | TIMESTAMP | Datetime Created |
34 | PRIORITY_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Priority Flag
1=High 2=Medium 3=Low |