PeopleSoft Financials Tables

You are viewing a wiki page. You are welcome to edit it. Be Bold! Only registered members are allowed to create new Wikis.
Your rating: None Average: 4.6 (28 votes)

A wiki for the community to start organizing PeopleSoft Financials tables in one place. This is different than PeopleSoft meta-tables and they should not be included in this wiki.

General Ledger Tables

PS_LEDGER: The general ledger data table.

PS_JRNL_HEADER: Stores general ledger journal header data.

PS_JRNL_LN: Stores general ledger journal line data.

ps_combo_rule_tbl: Stores ChartField combination rules.

ps_combo_group_tbl: Stores ChartField combination groups.

ps_combo_data_tbl: ChartField Combo data table.

ps_bu_led_comb_tbl: Business Unit CharField Combos for Ledger.

PS_GL_ACCOUNT_TBL: GL Accounts table.

Accounts Payable/Voucher Tables

PS_GRP_AP: Stores AP control groups.

PS_VOUCHER: AP voucher header table.

PS_VOUCHER_LINE: AP voucher line.

PS_DISTRIB_LINE: Voucher distribution table.

PS_PAYMENT_TBL: AP disbursements table.

pymnt_vchr_xref: Voucher Scheduled Payment.

PS_RT_RATE_TBL : Market Rate Data Tale. There is also the market rate default view RT_DFLT_VW.

PS_VCHR_ACCTG_LINE: AP accounting entries.

Voucher Build Staging Tables

PS_VCHR_HDR_STG
PS_VCHR_LINE_STG
PS_VCHR_DIST_STG
PS_VCHR_PYMT_STG

ps_bu_dfhi_opt_vw: Used by voucher build for the defaulting process.

VAT (Value Added Tax) Tables

PS_TAX_CD: Stores VAT Tax codes.

PS_TAX_AUTHORITY: Stores VAT Tax codes percentage rates.

Vendor Tables

VENDOR: Stores vendor related information.

VENDOR_ADDR: Vendor address related info.

VENDOR_LOC: Vendor locations.

VENDOR_PAY: Payment processing sepecifications such as payment method, payment handling code, bank code and so forth.

VNDR_ADDR_SCROL: Index to address table.

VNDR_CNTCT_SCR: Index to contact table.

VNDR_LOC_SCROL: Index to location table.

VENDOR_ID_NBRS: Additional ID numbers (Dun & Bradstreet numbers).

VENDOR_ADDR_PHN: Vendor phone numbers.

VENDOR_CNTCT: Vendor contacts.

VENDOR_CONVER: Vendor converstaions.

VENDOR_INVOICE: Stores vendor invoice related flags.

VENDOR_TYPE: Vendor type codes.

VNDR_BANK_ACCT: Vendor electronic payment information.

VNDR_CNTCT_PHN: Vendor contact phone numbers.

VNDR_IND_CLASS: Vendor Standard Industrial Classification (SIC) codes.

VNDR_URL: Vendor website address.

VNDR_VAT_RGSTRN: Vendor VAT information.

VENDOR_WTHD: Witholding data.

VENDOR_WTHD_JUR: Witholding type.

The following is vendor tables parent-child relationships from PeopleSoft 9.0 PeopleBooks.
PeopleSoft vendor table parnt child relationships

Business Unit Tables

PS_BUS_UNIT_TBL_FS: PeopleSoft financials business units.

PS_BUS_UNIT_TBL_AM: Asset Management business unit table.

PS_BUS_UNIT_TBL_AP: Accounts Payable business unit table.

PS_BUS_UNIT_TBL_AR: Accounts Receivable business unit table.

PS_BUS_UNIT_TBL_GL: General Ledger business unit table.

PS_BUS_UNIT_TBL_BI: Bill business unit table.

Purchasing Tables

Requisition Tables

REQ_HDR: Requisition Header

REQ_LINE: Requisition Line

REQ_LINE_SHIP : Requisition Line Schedule

REQ_LN_DISTRIB: Requistion Distribution

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Debasish Chakraborty's picture
Debasish Chakraborty (not verified)
Re: PeopleSoft Financials Tables--GL_Ledger

Hi,
I am currently involed in a Hyperion migration project. The ERP was PP/Soft and that will be converted to Oracle ERP. My ETL query fires the below query to get Actuals pp/soft record.

------------------------------------------------------------------
SELECT TO_CHAR(A.LEDGER),
A.FISCAL_YEAR,
A.ACCOUNTING_PERIOD,
TO_CHAR(A.BUSINESS_UNIT),
TO_CHAR(A.ACCOUNT),
TO_CHAR(A.DEPTID),
TO_CHAR(A.PRODUCT),
TO_CHAR(A.BUDGET_REF),
TO_CHAR(A.AFFILIATE),
TO_CHAR(A.BASE_CURRENCY),
TO_CHAR(SUM( A.POSTED_BASE_AMT)) AS POSTED_BASE_AMT
FROM PS_LEDGER A
WHERE ( A.LEDGER = 'ACTUALS'
AND((A.FISCAL_YEAR =
CASE WHEN TO_CHAR(SYSDATE, 'DD') > 15 THEN
CASE WHEN TO_CHAR(SYSDATE, 'MM') > 3 THEN TO_CHAR(SYSDATE, 'YYYY') + 1
ELSE CAST(TO_CHAR(SYSDATE, 'YYYY') AS INT) END
ELSE
CASE WHEN TO_CHAR(SYSDATE, 'MM') > 4 THEN TO_CHAR(SYSDATE, 'YYYY') + 1
ELSE CAST(TO_CHAR(SYSDATE, 'YYYY') AS INT) END
END
AND A.ACCOUNTING_PERIOD =
CASE WHEN TO_CHAR(SYSDATE, 'DD') > 15 THEN
CASE WHEN TO_CHAR(SYSDATE, 'MM') > 3 THEN TO_CHAR(SYSDATE, 'MM') - 3
ELSE TO_CHAR(SYSDATE, 'MM') + 9 END
ELSE
CASE WHEN TO_CHAR(SYSDATE, 'MM') > 4 THEN TO_CHAR(SYSDATE, 'MM') - 4
ELSE TO_CHAR(SYSDATE, 'MM') + 8 END
END
) ))
GROUP BY A.LEDGER, A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.BUSINESS_UNIT, A.ACCOUNT, A.DEPTID, A.PRODUCT, A.BUDGET_REF, A.AFFILIATE, A.BASE_CURRENCY
-----------------------------------------------------------------------

But i am unable to find out the similar query in ORACLE ERP. Can you let us know the meaning of the column "POSTED_BASE_AMT" in the PS_LEDGER table and also the purpose of these query ? I tried to find the same in ORACLE ERP and came accross the equivalent of "POSTED_BASE_AMT" in ORACLE as below
"( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)". The corresponing ORACLE query i developed as below

--------------------------------------------------------------------------
Select
'ACTUALS' as LEDGER,
--GLL.NAME,
GB.PERIOD_YEAR AS FISCAL_YEAR,
GB.PERIOD_NUM as ACCOUNTING_PERIOD,
GLC.SEGMENT2 BUSINESS_UNIT,
GLC.SEGMENT4 ACCOUNT,
GLC.SEGMENT3 DEPTID,
GLC.SEGMENT1 RPDOCUT,
GJL.ATTRIBUTE1 || GJL.ATTRIBUTE2 BUDGET_REF,
GLC.SEGMENT5 AFFILIATE,
GB.CURRENCY_CODE,
SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)) POSTED_BASE_AMT

From
GL_BALANCES GB,
GL_CODE_COMBINATIONS glc,
GL_JE_LINES GJL,
GL_LEDGERS GLL

Where
GJL.code_combination_id = GB.code_combination_id and
GJL.code_combination_id = GLC.code_combination_id and
GB.CURRENCY_CODE = GLL.CURRENCY_CODE and
GB.ACTUAL_FLAG = 'A' and
GB.TEMPLATE_ID IS NULL
-- and GB.PERIOD_NAME = 'APR-04'
--and SUBSTR(GLL.SHORT_NAME,1,2) IN ('Pr'')
and
((GB.period_year =
case when to_char(sysdate, 'dd') > 15 then
case when to_char(sysdate, 'mm') > 3 then to_char(sysdate, 'yyyy') + 1
else to_number(to_char(sysdate, 'yyyy')) end
else
case when to_char(sysdate, 'mm') > 4 then to_char(sysdate, 'yyyy') + 1
else cast(to_char(sysdate, 'yyyy') as int) end
end
and

GB.PERIOD_NUM =
case when to_char(sysdate, 'dd') > 15 then
case when to_char(sysdate, 'mm') > 3 then to_char(sysdate, 'mm') - 3
else to_char(sysdate, 'mm') + 9 end
else
case when to_char(sysdate, 'mm') > 4 then to_char(sysdate, 'mm') - 4
else to_char(sysdate, 'mm') + 8 end
end

) )

group by

GB.period_year,
GB.PERIOD_NUM,
GLC.SEGMENT2 ,
GLC.SEGMENT4,
GLC.SEGMENT3,
GLC.SEGMENT1,
GJL.ATTRIBUTE1 || GJL.ATTRIBUTE2,
GLC.SEGMENT5,
GB.CURRENCY_CODE
HAVING SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)) <> 0
-----------------------------------------------------------------
will this quivalent query give the same output as people soft gives.
Please help me out...

murali's picture
murali (not verified)
Peoplesoft-Accounts payable- top 20 vendor list

Hi all,

Can any of you tell me how to get the TOP 20 vendor list from peoplesoft database(transaction numbers). Do we have any query for this?

Thanks,
Murali

Hemali's picture
Hemali (not verified)
PeopleSoft Bank Ledger

Hey:

Can anybody let me know how we can extract bank ledger from People Soft which I can use in my Bank reco with Passbook (bank statements)?

Reply to comment | CompShack.com

Incredible tons of terrific data!

Reply to comment | CompShack.com

You made your position pretty well..

MCM 財布's picture
MCM 財布 (not verified)
Reply to comment | CompShack.com

Nicely put, Thanks a lot.

Reply to comment | CompShack.com

Beneficial advice Thank you.

Reply to comment | CompShack.com

Superb advice, Thanks!

Reply to comment | CompShack.com

Nicely put, Appreciate it!

Reply to comment | CompShack.com

Amazing content With thanks!

HERMES 財布's picture
HERMES 財布 (not verified)
Reply to comment | CompShack.com

Truly a lot of fantastic tips.

モンブラン インク's picture
モンブラン インク (not verified)
Reply to comment | CompShack.com

Excellent tips, Regards!

Reply to comment | CompShack.com

This is nicely put! !

Reply to comment | CompShack.com

Incredible many of fantastic tips.

leaflet delivery company's picture
leaflet delivery company (not verified)
Reply to comment | CompShack.com

It's the best time to make some plans for the longer term and it's time to
be happy. I've learn this submit and if I may I desire to counsel you few fascinating things or advice.
Maybe you can write subsequent articles referring to this
article. I want to read more things approximately it!

Reply to comment | CompShack.com

Wheո examining thеse reviews, it іs not uncommon to fіnd
a couple of negative comments, Ƅut you neeԁ to remember there іs a difference Ƅetween
a couple of negative reviews ɑnd а bunch of tɦem.
It may be cut off fօllowed wіtɦ sutures οr elsе
cut оut usiոg cauterization. Τhe main reason why so maոy people swear
ƅy thіs product is becaսse іt aϲtually ѕeems to wοrk, unlіke sօ many otɦer skin products tɦat make oոly the slightest bit оf difference, if
іndeed tҺey do ɑnything at ɑll.

Reply to comment | CompShack.com

You actually explained it fantastically!

COACH バッグ's picture
COACH バッグ (not verified)
Reply to comment | CompShack.com

Truly....this is a invaluable internet site.

Reply to comment | CompShack.com

Thanks! I appreciate this!

シャネル 財布's picture
シャネル 財布 (not verified)
Reply to comment | CompShack.com

Info effectively regarded!!

プーマ スニーカー's picture
プーマ スニーカー (not verified)
Reply to comment | CompShack.com

Good knowledge, Kudos!

Reply to comment | CompShack.com

You stated it very well!

Reply to comment | CompShack.com

You explained it really well.

Cartier 時計's picture
Cartier 時計 (not verified)
Reply to comment | CompShack.com

You reported that superbly!

Reply to comment | CompShack.com

Many thanks, Ample write ups.

Reply to comment | CompShack.com

Nicely put. Kudos.

Rishi's picture
Rishi (not verified)
Re: PeopleSoft Financials Tables

Great piece of Information.

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
5 + 13 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.