Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick, Simple and Absolutely Free.

Thread Rating:
  • 21 Vote(s) - 3.14 Average
  • 1
  • 2
  • 3
  • 4
  • 5
GL Approval Action History from Workflow tables
#1
Hi All,
I have prepared the query to retrieve the Journal Approval Action History from Workflow tables. Below is the Query. This is the Form Personalization. In the Journal Screen i have attached the Menu Action History and placed this query for form personalization.
Now the problem is in GL_JE_BATCHES i have 80 thousand records. In the query which i have written i am getting only 1000 records. How can i achieve all the records.

SELECT DISTINCT gjb.NAME batch, default_period_name period,
wn.recipient_role approver,
DECODE (gjb.approval_status_code,
'A', 'Approved',
'I', 'In Process',
'J', 'Rejected',
'R', 'Required',
'V', 'Validation Failed',
'Z', 'N/A'
) status,
wn.begin_date approval_start_date,
wn.end_date approval_end_date,
wn.due_date approval_due_date
FROM wf_notifications wn, wf_items wi, gl_je_batches gjb
WHERE wn.CONTEXT LIKE
'GLBATCH%'
|| (SELECT item_key
FROM wf_items a
WHERE a.user_key = wi.user_key
AND a.item_type = 'GLBATCH'
AND ROWNUM = 1)
|| '%'
AND wi.item_type = wn.MESSAGE_TYPE
AND wi.user_key = gjb.NAME;
Can any one please suggest me how to proceed on this issue ASAP?

Regards,
Reply
Thanks given by:
#2
(12-06-2013, 01:05 AM)Sneha Rahul Wrote: Hi All,
I have prepared the query to retrieve the Journal Approval Action History from Workflow tables. Below is the Query. This is the Form Personalization. In the Journal Screen i have attached the Menu Action History and placed this query for form personalization.
Now the problem is in GL_JE_BATCHES i have 80 thousand records. In the query which i have written i am getting only 1000 records. How can i achieve all the records.

SELECT DISTINCT gjb.NAME batch, default_period_name period,
wn.recipient_role approver,
DECODE (gjb.approval_status_code,
'A', 'Approved',
'I', 'In Process',
'J', 'Rejected',
'R', 'Required',
'V', 'Validation Failed',
'Z', 'N/A'
) status,
wn.begin_date approval_start_date,
wn.end_date approval_end_date,
wn.due_date approval_due_date
FROM wf_notifications wn, wf_items wi, gl_je_batches gjb
WHERE wn.CONTEXT LIKE
'GLBATCH%'
|| (SELECT item_key
FROM wf_items a
WHERE a.user_key = wi.user_key
AND a.item_type = 'GLBATCH'
AND ROWNUM = 1)
|| '%'
AND wi.item_type = wn.MESSAGE_TYPE
AND wi.user_key = gjb.NAME;
Can any one please suggest me how to proceed on this issue ASAP?

Regards,
Did you explored FND: View Object Max Fetch Size? can you try this
Reply
Thanks given by:


Possibly Related Threads...
Thread Author Replies Views Last Post
  AME Approval Rules for Updates to Supplier Master? sadams01 2 303 02-25-2017, 12:30 AM
Last Post: sadams01
  Want to use shrink for Tables in oracle Gopal Ayog 1 1,217 03-03-2015, 07:00 PM
Last Post: Saleem Akhtar
  How to change AP approval hierarchy Archita Jartin 1 905 01-08-2015, 08:50 PM
Last Post: Kashif Manzoor
  How can set approval hierarchy in Ap r12 Colin Roger 1 3,835 10-24-2014, 05:25 PM
Last Post: shirfanarshad
  landing tables in oracle erp R12 sharpan 3 4,659 02-23-2013, 05:11 AM
Last Post: sharpan



Users browsing this thread: 1 Guest(s)
OTech Talks, Lets Talk OPEN! Get Oracle Tutorials, Tips! Post a Question!