Home » Applications » Oracle Fusion Apps & E-Business Suite » Converting customized reports in 6i to be multi-org compliant (Oracle Apps 11.5.10, Oracle reports builder 6i)
Converting customized reports in 6i to be multi-org compliant [message #312394] Tue, 08 April 2008 15:01 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
My client just converted to a multi-org environment. Oracle Apps version 11.5.10 Patch set M, database version 10g. In some of our customized Oracle reports we had to put this piece of code (see below) in the before report trigger for data to be populated into the report. Otherwise, no data would be populated into the report. The problem is one particular report is not populating with the before report trigger or without.

So I'm not sure what the problem is now. The profile option MO: Operating Unit is set for the responsibility in which we run the repot. Therefore, the client_info should get picked up from the before report trigger. Any suggestions on what could be the problem or something I should look at?

function BeforeReport return boolean is
 x_org_id   NUMBER;
begin

 
  fnd_profile.get ('ORG_ID', x_org_id);

   fnd_client_info.set_org_context (x_org_id);

   DBMS_OUTPUT.PUT_LINE (x_org_id);

  
  
  return (TRUE);
end;


Re: Converting customized reports in 6i to be multi-org compliant [message #312503 is a reply to message #312394] Wed, 09 April 2008 01:33 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Did you verify that the org_id is indeed picked up? (in any way show it in the report itself just for verification?)

If so, I might be able to help you if you could give the sql statement for the report (if reasonably possible, we don't want 1000's of lines of code on the forum...). Not sure, but I'm willing to try.
Re: Converting customized reports in 6i to be multi-org compliant [message #312623 is a reply to message #312503] Wed, 09 April 2008 08:17 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Hi,

What do you mean by show it on the report? and how would I do that by using a select statement or something?

Here is the code for this report:

SELECT DISTINCT h.full_name pi,
                d.segment_value,
                d.segment_value_lookup,
                e.segment1,
                e.NAME,
                e.completion_date exp_date,
                SUM (a.bud_amount) budget,
                SUM (DECODE (a.c_grp, 'Personnel', a.avail_amount, 0))
                                                                    personnel,
                SUM (DECODE (a.c_grp, 'Consultant', a.avail_amount, 0))
                                                                   consultant,
                SUM (DECODE (a.c_grp, 'Supplies', a.avail_amount, 0))
                                                                     supplies,
                SUM (DECODE (a.c_grp, 'Other', a.avail_amount, 0)) other,
                SUM (DECODE (a.c_grp, 'Travel', a.avail_amount, 0)) travel,
                SUM (DECODE (a.c_grp, 'Capital Expense', a.avail_amount, 0))
                                                              capital_expense,
                SUM (DECODE (a.c_grp, 'Subcontract', a.avail_amount, 0))
                                                                  subcontract,
                SUM (DECODE (a.c_grp,
                             'Personnel', a.avail_amount,
                             'Consultant', a.avail_amount,
                             'Supplies', a.avail_amount,
                             'Other', a.avail_amount,
                             'Travel', a.avail_amount,
                             'Capital Expense', a.avail_amount,
                             'Subcontract', a.avail_amount,
                             0
                            )
                    ) total_direct
FROM            apps.pa_projects e,
                pa.pa_segment_value_lookups d,
                apps.pa_project_players p,
                hr.per_all_people_f h,
                hr.hr_all_organization_units ho,
                --gl.gl_code_combinations c,
                (SELECT   DECODE (bud.NAME,
                                  'Salaries & Wages', 'Personnel',
                                  'Fringe Benefits', 'Personnel',
                                  'Supplies & Expenses', 'Supplies',
                                  'Consulting', 'Consultant',
                                  'Subcontract Expenses', 'Subcontract',
                                  'Land/Building/Improvements', 'Capital Expense',
                                  'Renovations/Alterations', 'Capital Expense',
                                  'Equipment', 'Capital Expense',
                                  'Travel', 'Travel',
                                  'Other'
                                 ) c_grp,
                          bud.project_id,
                          NVL (SUM (bud.amount), 0) bud_amount,
                          NVL (ptd.amount, 0),
                            NVL (SUM (bud.amount), 0)
                          - NVL (ptd.amount, 0)
                          - NVL (comm.amount, 0) avail_amount
                 FROM     (SELECT   pr.NAME,
                   SUM (NVL (ab.base_burdened_cost_tot, 0)) amount,
                   ah.project_id
          FROM     pa.pa_project_accum_headers ah,
                   pa.pa_project_accum_budgets ab,
                   pa.pa_resources pr
          WHERE    ah.resource_id != 0
          AND      ah.task_id = 0
          AND      ab.project_accum_id = ah.project_accum_id
          AND      pr.resource_id = ah.resource_id
          AND      ab.budget_type_code = 'AC'
          AND      pr.NAME NOT IN
                      ('F & A Costs',
                       'Award Revenue Category',
                       'Cost Share',
                       'Cost Share-Direct',
                       'Cost Share-Indirect'
                      )
          GROUP BY ah.project_id,
                   pr.NAME
          UNION
          SELECT pr.NAME,
                 0,
                 p.project_id
          FROM   pa.pa_resources pr,
                 apps.pa_projects p
          WHERE  pr.resource_type_id = 109
          AND    pr.NAME NOT IN
                    ('F & A Costs',
                     'Award Revenue Category',
                     'Cost Share',
                     'Cost Share-Direct',
                     'Cost Share-Indirect'
                    )) bud,
         (SELECT   gmsac.project_id,
                   pet.revenue_category_code,
                   SUM (DECODE (gmsac.pa_period, :period_name, cdl.amount, 0))
                                                                     m_amount,
                   SUM (DECODE (gcc.segment3, '6321', cdl.amount, 0))
                                                                    ap_amount,
                   SUM (NVL (cdl.amount, 0)) amount
          FROM     apps.pa_periods paper,
                   apps.pa_periods pp,
                   apps.pa_expenditure_types pet,
                   apps.pa_cost_distribution_lines cdl,
                   apps.gl_code_combinations gcc,
                   tams.tams_gms_status_actuals gmsac,
                   apps.pa_projects pap
          WHERE    gmsac.project_id = pap.project_id
          AND      gmsac.expenditure_type = pet.expenditure_type
          AND      paper.period_name = gmsac.pa_period
          AND      pp.period_name = UPPER (:period_name)
          AND      paper.end_date <= pp.end_date
          AND      cdl.expenditure_item_id = gmsac.expenditure_item_id
          AND      gcc.code_combination_id = cdl.dr_code_combination_id
          GROUP BY gmsac.project_id,
                   pet.revenue_category_code) ptd,
         (SELECT   pct.project_id,
                   pct.revenue_category,
                   SUM (NVL (pct.tot_cmt_burdened_cost, 0)) amount
          FROM     apps.pa_commitment_txns pct
          GROUP BY pct.project_id,
                   pct.revenue_category) comm,
         apps.pa_projects p
WHERE    bud.project_id= p.project_id
AND      ptd.project_id (+)= bud.project_id
AND      ptd.revenue_category_code (+)= bud.NAME
AND      comm.project_id (+)= bud.project_id
AND      comm.revenue_category(+) = bud.NAME
group by bud.project_id,ptd.amount,comm.amount,
                          DECODE (bud.NAME,
                                  'Salaries & Wages', 'Personnel',
                                  'Fringe Benefits', 'Personnel',
                                  'Supplies & Expenses', 'Supplies',
                                  'Consulting', 'Consultant',
                                  'Subcontract Expenses', 'Subcontract',
                                  'Land/Building/Improvements', 'Capital Expense',
                                  'Renovations/Alterations', 'Capital Expense',
                                  'Equipment', 'Capital Expense',
                                  'Travel', 'Travel',
                                  'Other'
                                 )) a
WHERE           e.carrying_out_organization_id = ho.organization_id
AND              (ho.NAME LIKE 'TNPRC%' or ho.Name like 'Tulane Natl Primate Rsch Center')
--AND             e.segment1 = c.segment2
--AND             c.segment1 between '44000' and '44999'
AND             ho.NAME = d.segment_value_lookup(+)
AND             h.person_id(+) = p.person_id
AND             p.project_id = e.project_id
AND             e.project_type = 'Sponsored Program'
AND             p.project_role_type = 'PROJECT MANAGER'
AND             e.project_status_code != 'CLOSED'
AND             a.project_id = e.project_id
AND             e.completion_date >= :PARM_DATE
AND             e.NAME NOT LIKE 'C/S%'
AND              p.end_date_active is null
AND             e.start_date <= trunc(sysdate)
&BASE_GRANTS
GROUP BY        h.full_name,
                d.segment_value,
                d.segment_value_lookup,
                e.segment1,
                e.NAME,
                e.completion_date
ORDER BY        h.full_name,
                d.segment_value,
                d.segment_value_lookup,
                e.segment1,
                e.NAME

Re: Converting customized reports in 6i to be multi-org compliant [message #312639 is a reply to message #312623] Wed, 09 April 2008 09:06 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
if you do the following:

- start sql*plus
- type in:
exec dbms_application_info.set_client_info('123');

(where 123 should be replaced by a meaning ful organization unit id in your environment)
- copy/paste the whole statement you posted

does this give any result data?

If not, start eliminating causes in your code.

BTW, I saw one thing in your code you might want to alter:
          UNION
                SELECT pr.NAME
                      ,0
                      ,p.project_id
                FROM   pa.pa_resources  pr
                      ,apps.pa_projects p -- you're not joining this to pr!!
                WHERE  pr.resource_type_id = 109
                
Re: Converting customized reports in 6i to be multi-org compliant [message #312658 is a reply to message #312639] Wed, 09 April 2008 09:57 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Thanks I will apply your recommendations.

Californiagirl
Re: Converting customized reports in 6i to be multi-org compliant [message #312693 is a reply to message #312658] Wed, 09 April 2008 10:59 Go to previous messageGo to next message
fiery_hanzy
Messages: 10
Registered: December 2007
Junior Member
jus try
SRW.USER_EXIT('FND SRWINIT'); ==> in b4 rep triggr
and
SRW.USER_EXIT('FND SRWEXIT'); ==> in after rep triggr

Re: Converting customized reports in 6i to be multi-org compliant [message #312744 is a reply to message #312693] Wed, 09 April 2008 12:48 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I got the following error message in the log file:

Current system time is 09-APR-2008 12:46:53

+---------------------------------------------------------------------------+


+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
PERIOD_NAME='AUG-07'
P_BASE_GRANTS='Y'
------------

Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.WE8ISO8859P1

'.,'

REP-1433: 'beforereport': Error running user exit 'FND'.

Report Builder: Release 6.0.8.27.0 - Production on Wed Apr 9 12:46:53 2008

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter Username:
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Program exited with status 1
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 1632309.

Review your concurrent request log and/or report output file for more detailed information.


+---------------------------------------------------------------------------+
Executing request completion options...


Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 09-APR-2008 12:46:55

+---------------------------------------------------------------------------+
Re: Converting customized reports in 6i to be multi-org compliant [message #312943 is a reply to message #312744] Thu, 10 April 2008 03:14 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hi,

I've asked the moderators to move this thread to the EBS forum, because this is getting very specific for EBS. Perhaps the members there can help you better then we can.

Regards,
Sabine
Re: Converting customized reports in 6i to be multi-org compliant [message #313055 is a reply to message #312943] Thu, 10 April 2008 09:07 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Hi,

I put this in the before report trigger below and all the data came into the report.

dbms_application_info.set_client_info('0');


Re: Converting customized reports in 6i to be multi-org compliant [message #313241 is a reply to message #313055] Fri, 11 April 2008 03:53 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
You aware of the fact that you have made your report non-multi-org by doing so?
Re: Converting customized reports in 6i to be multi-org compliant [message #313358 is a reply to message #313241] Fri, 11 April 2008 08:40 Go to previous message
californiagirl
Messages: 79
Registered: May 2007
Member
why is that?

Previous Topic: DDF LOV-Query based on Form Item
Next Topic: Oracle Discoverer Version
Goto Forum:
  


Current Time: Wed Dec 07 10:51:12 CST 2016

Total time taken to generate the page: 0.25287 seconds