Oracle General Ledeger Certification Stuff

Oracle General Ledeger Intracts With -
----------------------------------------

Oracle Payables sends invoices payments , realizes gain and loss on foreign currencies and invoice price variance to GL

Oracle Receivables sends invoices payments , adjustments,debit memos , credit memos, cash and realizaed gain and loss on foreign currencies and invoice price variance to GL


Oracle Assets sends capital and construction in process asset additions , cost adjustments , transfers , retirements , depreciations and reclassifications to GL

Oracle purchasing sends accruals or receipts not invoiced , purchase orders , final closes and cancellations to GL

Oracle property sends revenues and expenses related to teal estate to GL

Oracle HR shares employee information with GL

Oracle Payroll sends salary , deductions and tax information to GL

Oracle Inventory sends cycle counts , physical inventory adjustments , receiving transactions , delivery transactions , intercompany transfers , sales order issues , internal requsitions , sub-inventory transfers and COGS to GL

BI - Oracle Business Intelligence .

Intract with Subledgers
-----------------------------

Subledgers (by Oracle Subledger Accounting) > Gl_interface > Journal Import > Journals > Post > Gl_balances


4 C's in R12 GL
-----------------------------
Chart of account(COA) - Provides Account Code ,
Calendar - provides date and Period ,
Currency - Transaction currency and
Accounting Conversion - provides Accounting rules and regulations for the company/Country

JV Approval Stuck - NO_MANAGER_RESPONSE

SELECT A.*
FROM (SELECT (SELECT MAX (DOC_SEQUENCE_VALUE)
FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.JE_BATCH_ID =
(SELECT REVERSE(SUBSTR (
REVERSE (ITEM_KEY),
6,
LENGTH (ITEM_KEY)
))
FROM DUAL))
DOC_SEQUENCE_VALUE,
(SELECT NAME
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID =
(SELECT REVERSE(SUBSTR (REVERSE (ITEM_KEY),
6,
LENGTH (ITEM_KEY)))
FROM DUAL))
BATCH_NAME,
(SELECT JE_BATCH_ID
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID =
(SELECT REVERSE(SUBSTR (REVERSE (ITEM_KEY),
6,
LENGTH (ITEM_KEY)))
FROM DUAL))
JE_BATCH_ID,
MESSAGE_NAME,
STATUS,
TO_USER,
SUBJECT
FROM WF_NOTIFICATIONS B
WHERE MESSAGE_TYPE = 'GLBATCH'
AND MESSAGE_NAME = 'NO_MANAGER_RESPONSE'
AND STATUS = 'OPEN'
OR BEGIN_DATE LIKE TO_DATE ('09082005', 'dd/mm/yyyy')
OR B.RECIPIENT_ROLE LIKE '%Approver_name%') A,
GL_JE_BATCHES B
WHERE A.JE_BATCH_ID = B.JE_BATCH_ID AND B.STATUS != 'P'

FAQs About Journal Approval

Questions and Answers

Can the preparer of the journal batch approve the journal?

Yes, preparer can approve the journal when below two conditions are met
1. When the Journals: Allow Preparer Approval profile option is set to ‘Yes’
2. Preparer authorization limit is more than maximum absolute amount of journal batch

Preparer authorization limit can be obtained using below query

R11:
SELECT nvl(authorization_limit, 0)
FROM GL_AUTHORIZATION_LIMITS a, fnd_user b,gl_set_of_books c
WHERE a.employee_id = b.employee_id
AND a.set_of_books_id =c.set_of_books_id
And b.user_name=’&peparer_id’
And c.name=’&set_of_books_name’;

R12:

SELECT nvl(authorization_limit, 0)
FROM GL_AUTHORIZATION_LIMITS a, fnd_user b,gl_ledgers c
WHERE a.employee_id = b.employee_id
AND a.ledger_id =c.ledger_id
And b.user_name=’&preparer_id’
And c.name=’&ledger_name’;

Maximum line amount of journal batch can be found using below query

SELECT max(abs(nvl(JEL.accounted_dr, 0) – nvl(JEL.accounted_cr, 0)))
FROM GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB
WHERE JEH.je_batch_id = JEB.je_batch_id
AND JEH.je_header_id = JEL.je_header_id
AND JEH.currency_code <> ‘STAT’
And JEB.name=&journal_batch_name;

Is preparer same as creator of journal batch?

No, Preparer is the one who initiates the journal batch approval process and he/she need not be the journal
batch creator.

How to find supervisor of the user and the authorization limit of the user and supervisor?

Use below query to get the user’s supervisor details and authorization limits of the user and his supervisor.
R11:
select a.user_name user_id ,b.full_name user_full_name,
c.d_supervisor_id supervisor_user_id ,
d.authorization_limit User_auth_limit ,
e.authorization_limit Supervisor_auth_limit
from
fnd_user a,
per_all_people_f b,
PER_ASSIGNMENTS_V7 c,
gl_authorization_limits_v d,
gl_authorization_limits_v e
Where a.employee_id=b.person_id
and b.person_id=c.person_id(+)
and a.employee_id=d.employee_id(+)
and c.supervisor_id=e.employee_id(+)
and a.user_name=’&preparer_id’;

R12:
select a.user_name user_id ,b.full_name user_full_name,
c.d_supervisor_id supervisor_user_id ,
d.authorization_limit User_auth_limit ,
e.authorization_limit Supervisor_auth_limit
from
fnd_user a,
per_all_people_f b,
PER_ASSIGNMENTS_V7 c,
gl_authorization_limits_v d,
gl_authorization_limits_v e
Where a.employee_id=b.person_id
and b.person_id=c.person_id(+)
and a.employee_id=d.employee_id(+)
and c.supervisor_id=e.employee_id(+)
and a.user_name=’&preparer_id’;

How to find all work flow notification details of specific journal batch?

Use below queries to find work flow activity status & notification details of the journal batch

select * from wf_notifications where notification_id in
(select notification_id from WF_ITEM_ACTIVITY_STATUSES where
item_type=’GLBATCH’ and item_key like ‘&je_batch_id%’ );

Select * from WF_ITEM_ACTIVITY_STATUSES where
item_type=’GLBATCH’ and item_key like ‘&je_batch_id%’;

je_batch_id can be obtained using below query
select je_batch_id from gl_je_batches where name=&je_batch_name;

What are different types of approver methods and how to set approver method?

There are 3 types of approver methods

1.Go Up Management Chain –Goes up in HRMS manager hierarchy
2.Go Direct —Goes directly to the person with authorization limit
3.One Stop Then Go Direct – Goes to the immediate manager then directly to the person with authorization limit

This has to be set using the profile option “Journals: Find Approver Method”

Payroll - Employee look up details extraction

SELECT PAPF.first_name||' '||PAPF.middle_names||' '||PAPF.last_name Emplyee_name
,PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
,TO_CHAR(PACT.EFFECTIVE_DATE,'MON-YY') MONTH
,SUBSTR(OPMTL.ORG_PAYMENT_METHOD_NAME,1,30) PAYMENT_METHOD
,OPM.CURRENCY_CODE PAYMENT_CURRENCY
,NVL(PERPAY.ATTRIBUTE1,PAPF.first_name||' '||PAPF.middle_names||' '||PAPF.last_name) CHEQUE_NAME
,PPT.PAYMENT_TYPE_NAME PAYMENT_TYPE
--,TARGET.SEGMENT1 BANK
,decode(TARGET.TERRITORY_CODE,'AE',(select meaning from hr_lookups h
WHERE h.lookup_type = 'AE_BANK_NAMES'
and h.application_id = 800
and h.enabled_flag = 'Y'
and lookup_code = TARGET.SEGMENT1),(select meaning from hr_lookups h
WHERE h.lookup_type = 'AE_BANK_NAMES'
and h.application_id = 800
and h.enabled_flag = 'Y'
and lookup_code = TARGET.SEGMENT1) --TARGET.SEGMENT1
) BANK
,decode(TARGET.TERRITORY_CODE,'AE',TARGET.SEGMENT4,TARGET.SEGMENT4) ACCOUNT_CODE
--,TARGET.SEGMENT2 BRANCH
,decode(TARGET.TERRITORY_CODE,'AE',(select meaning from hr_lookups h
WHERE LOOKUP_TYPE = 'AE_BRANCH_NAMES'
AND APPLICATION_ID = 800
AND ENABLED_FLAG = 'Y'
and lookup_code = TARGET.SEGMENT2), (select meaning from hr_lookups h
WHERE LOOKUP_TYPE = 'AE_BRANCH_NAMES'
AND APPLICATION_ID = 800
AND ENABLED_FLAG = 'Y'
and lookup_code = TARGET.SEGMENT2)--TARGET.SEGMENT2
) BRANCH
FROM PAY_EXTERNAL_ACCOUNTS TARGET
,PAY_PERSONAL_PAYMENT_METHODS_F PERPAY
, PAY_PRE_PAYMENTS PP
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PER_ALL_ASSIGNMENTS_F PAAF
, PER_ALL_PEOPLE_F PAPF
, PAY_PAYROLL_ACTIONS PACT
, HR_LOOKUPS BANK
, PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_ORG_PAYMENT_METHODS_F OPM
, PAY_PAYMENT_TYPES PPT
, (SELECT employee_number, sum(pay_value) bonus, period_name
FROM xyka_earnings_deductions_v xedv
WHERE xedv.element_name = 'Sales Incentive'
AND xedv.period_name = :p_period_name
GROUP BY employee_number, period_name
) xedv
WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND OPM.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID
AND PACT.ACTION_TYPE IN ('P','U')
AND PP.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND ASSACT.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PERPAY.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID
AND BANK.LOOKUP_CODE(+) = TARGET.SEGMENT1
AND BANK.LOOKUP_TYPE(+) = 'GB_BANKS'
AND PACT.EFFECTIVE_DATE BETWEEN PERPAY.EFFECTIVE_START_DATE AND PERPAY.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE
AND PERPAY.PERSONAL_PAYMENT_METHOD_ID = PP.PERSONAL_PAYMENT_METHOD_ID
AND TARGET.EXTERNAL_ACCOUNT_ID(+) = PERPAY.EXTERNAL_ACCOUNT_ID
-- AND (ASSACT.PAYROLL_ACTION_ID NOT IN (SELECT X_PAYROLL_ACTION_ID FROM XYKA_PAY_BANK_TRANSFER)
-- OR ASSACT.ASSIGNMENT_ACTION_ID NOT IN (SELECT X_ASSIGNMENT_ACTION_ID FROM XYKA_PAY_BANK_TRANSFER))
AND TO_CHAR(PACT.EFFECTIVE_DATE,'MON-YY') =:p_period_name
AND PAPF.EMPLOYEE_NUMBER = xedv.employee_number(+)
AND PPT.PAYMENT_TYPE_NAME not in ('OM Cash','AE Cash')
ORDER BY PP.ASSIGNMENT_ACTION_ID

R12 AR Cash Receipts - By API

mo_global.set_policy_context ('S', j.x_inv_org_id);
ar_receipt_api_pub.create_cash
(p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => l_currency_code,
p_amount => j.amount,
p_receipt_number => j.receipt_number,
p_receipt_date => j.x_date,
p_gl_date => j.x_date,
p_customer_id => j.customer_id,
p_customer_site_use_id => j.bill_to_site_use_id,
p_org_id => j.x_inv_org_id,
p_remittance_bank_account_id => l_bank_account_use_id,
p_receipt_method_id => l_receipt_method_id,
p_cr_id => l_cash_receipt_id,
p_comments => j.comments
);



ar_receipt_api_pub.APPLY
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_cash_receipt_id => l_cash_receipt_id,
p_customer_trx_id => l.customer_trx_id,
p_applied_payment_schedule_id => l.payment_schedule_id,
--p_discount => l_discount_taken,
p_amount_applied => l.applied_amount,
--p_amount_applied_from => l_amt_applied_from,
p_org_id => j.x_inv_org_id,
p_apply_date => j.x_date,
p_apply_gl_date => j.x_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);