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)?

Every weekend i used to visit this web site, for the reason that i wish for
enjoyment, since this this web site conations in fact nice funny data too.