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.
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.
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.
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.
PS_TAX_CD: Stores VAT Tax codes.
PS_TAX_AUTHORITY: Stores VAT Tax codes percentage rates.
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.
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.
REQ_HDR: Requisition Header
REQ_LINE: Requisition Line
REQ_LINE_SHIP : Requisition Line Schedule
REQ_LN_DISTRIB: Requistion Distribution
Title | Under | Posted on |
---|---|---|
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
Search Results | PeopleSoft Technical | 11/15/2017 - 10:24am |
PS Query - Need Dependency and Prunable examples | PeopleSoft Technical | 10/19/2017 - 11:28am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
Comments
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...
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
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)?
Incredible tons of terrific data!
You made your position pretty well..
Nicely put, Thanks a lot.
Beneficial advice Thank you.
Superb advice, Thanks!
Nicely put, Appreciate it!
Amazing content With thanks!
Truly a lot of fantastic tips.
Excellent tips, Regards!
This is nicely put! !
Incredible many of fantastic tips.
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!
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.
You actually explained it fantastically!
Truly....this is a invaluable internet site.
Thanks! I appreciate this!
Info effectively regarded!!
Good knowledge, Kudos!
You stated it very well!
You explained it really well.
You reported that superbly!
Many thanks, Ample write ups.
Nicely put. Kudos.
Great piece of Information.
Thanks for this post! I’m still a learner on this technology, this info seems to be really helpful. Thanks again!
Post new comment