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  |
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  |
skooman
Messages: 913
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 |
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 |
skooman
Messages: 913
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 #312744 is a reply to message #312693] |
Wed, 09 April 2008 12:48 |
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
+---------------------------------------------------------------------------+
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 18:18:19 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
