Can't run vanilla Oracle "Packing Slip Report" from Report Builder

[kzhangkzhang]

Hi All:

I am kind of new to Oracle Report development.

Here is my case:

My company is running Oracle 11.5.9 Oracle application with 9i database engine.

I have Oracle Report Builder 6i (with latest patchset 15 applied) installed on my desktop (Windows 2000)

Step:
1) I copied the vanilla Oracle report rdf file, WSHRDPAK.rdf from our code tree wsh/11.5.0/reports/US/ directory to my desktop.
2) Open the WSHRDPACK.rdf with Oracle Report Builder 6i
3) Connect to the database inside of Report Builder 6i (File ==> Connect)
4) Compile the rdf file without any error
5) Run the report (Program ==> Run) with proper input parameters

Then I got the error, "REP-1415:'beforereport': unknown user exit".

Then I click the Help button on Error message dialog box, the online help menu explained as following:

Cause: A user exit was called, but it could not be found.
Action: Ensure that you are using the correct name for the user exit and that it has been compiled and linked.

Since this is a vanilla Oracle report and we didn't change it at all, I thought I should be able to run this report from my desktop using Report Builder 6i without any problem.

Then I look at the source code for BEFORE REPORT of Report Triggers. I can't tell anything wrong.

Can anybody help me with this?

Thanks in advance.

Source Code:

function BeforeReport return boolean is
l_result VARCHAR2(1);
l_return_status VARCHAR2(5);
l_msg_count NUMBER;
l_msg_data VARCHAR2(3000);
l_cum_profile VARCHAR2(5);
begin

-----------------------------------------------------------------------------------------
-- Check to see if Final status on any of the delivery doc (including children is set. --
-- If set, abort report. If not, continue. --
-----------------------------------------------------------------------------------------
BEGIN
l_result:= WSH_Document_PVT.is_final(1.0,NULL,NULL,NULL,l_return_status,l_msg_count,l_msg_data,_delivery_id,'PACK_TYPE',_print_all);
IF FND_API.to_boolean(l_result)
THEN
SRW.message(1,'The current delivery or any of its child deliveries already has FINAL print packing slip.');
SRW.message(1,'Cannot print such packing slip. Fatal Error!');
RAISE SRW.program_abort;
END IF;
END;

---------------------------------
-- FND Userexit Initialization --
---------------------------------
BEGIN
SRW.user_exit('FND SRWINIT');
EXCEPTION
WHEN SRW.USER_EXIT_FAILURE THEN
SRW.message(1,'Failed FND SRWINIT.');
RAISE SRW.program_abort;
END;

-------------------------------------------
-- Determine if we need to print CUM Qty --
-------------------------------------------
BEGIN
l_cum_profile:=FND_PROFILE.value('RLM_PRINT_CUM_DATA');
IF l_cum_profile='Y'
THEN
:cp_rlm_print_cum_data:='Y';
ELSE
:cp_rlm_print_cum_data:='N';
END IF;
END;

----------------------------------
-- Populate Placeholder Columns --
----------------------------------
BEGIN
-- get 'Draft' or 'Final' boilerplate label
IF _print_mode IS NOT NULL
THEN
SELECT meaning
INTO :cp_draft_or_final
FROM wsh_lookups
WHERE lookup_type='PACK_MODE'
AND lookup_code=_print_mode;
ELSE
SRW.message(1,'Fatal error: Cannot initialize Draft or Final print variable!');
RAISE SRW.program_abort;
END IF;
-- get warehouse name for boilerplate label
IF _delivery_id IS NOT NULL THEN
SELECT hr.name
INTO :cp_warehouse_name
FROM hr_organization_units hr,
wsh_new_deliveries del
WHERE del.organization_id = hr.organization_id
AND del.delivery_id = _delivery_id;
ELSIF _organization_id IS NOT NULL
THEN
SELECT name
INTO :cp_warehouse_name
FROM hr_organization_units
WHERE organization_id=_organization_id;
_organizationid:='AND det.organization_id = _organization_id';
ELSE
SRW.message(1,'Fatal error: Cannot initialize Warehouse Name print variable!');
RAISE SRW.program_abort;
END IF;
-- populate print_date placeholder column; this date may be used later in the after report trigger
:cp_print_date:=SYSDATE;
EXCEPTION
WHEN OTHERS THEN
SRW.message(1,'Failed Placeholder Columns Initialization!');
RAISE SRW.program_abort;
END;

-------------------------------------------------
-- Construct Order By depends on p_sort option --
-------------------------------------------------
BEGIN
IF _sort='CUST' -- if we are instructed to sort by customer item information, we need to see if it is selected to be printed
THEN
IF _print_cust_item='Y'
THEN
_order_by:='c_customer_item_id';
ELSE -- if it is not selected to be printed then we order by inventory item information, either item num or description
IF _item_display='D'
THEN
_order_by:='c_item_description'; -- sort by item description if only description is displayed
ELSE
_order_by:='c_item_flex';
END IF;
END IF;
ELSE -- we are instructed to order by inventory item information only, either by item num or description
IF _item_display='D'
THEN
_order_by:='c_item_description'; -- sort by item description if only description is displayed
ELSE
_order_by:='c_item_flex';
END IF;
END IF;
END;

-------------------------------
--Code for Bill To Fields
-------------------------------
/*anxsharm - code for billto details */
/* Bug 2198828 address_line_4 included */
DECLARE
l_location_id NUMBER;
l_address_line_1 hz_locations.address1%TYPE;
l_address_line_2 hz_locations.address2%TYPE;
l_address_line_3 hz_locations.address3%TYPE;
l_address_line_4 hz_locations.address4%TYPE;
l_town_or_city hz_locations.city%TYPE;
l_region hz_locations.county%TYPE;
l_postal_code hz_locations.postal_code%TYPE;
l_country fnd_territories_tl.territory_short_name%TYPE;

BEGIN

SELECT loc_bill.address1,
loc_bill.address2,
loc_bill.address3,
loc_bill.address4,
loc_bill.city,
nvl(nvl(loc_bill.province,loc_bill.state),loc_bill.county),
loc_bill.postal_code,
terr_bill.territory_short_name
INTO l_address_line_1,
l_address_line_2,
l_address_line_3,
l_address_line_4,
l_town_or_city,
l_region,
l_postal_code,
l_country
FROM hz_locations loc_bill,
fnd_territories_tl terr_bill
WHERE loc_bill.country = terr_bill.territory_code(+)
AND decode(loc_bill.country,null,userenv('LANG'),terr_bill.language) = userenv('LANG')
AND loc_bill.location_id = :cf_bill_to_loc;

:cp_bill_address_line_1 := l_address_line_1;
:cp_bill_address_line_2 := l_address_line_2;
:cp_bill_address_line_3 := l_address_line_3;
:cp_bill_address_line_4 := l_address_line_4;
:cp_bill_town_or_city := l_town_or_city;
:cp_bill_region := l_region;
:cp_bill_postal_code := l_postal_code;
:cp_bill_country := l_country;
EXCEPTION
when others then
return(null);
END;




-------------------------------
-- Intialize Item Flex Field --
-------------------------------
BEGIN
SRW.USER_EXIT('FND FLEXSQL
CODE="MSTK"
APPL_SHORT_NAME="INV"
OUTPUT="_ITEM_FLEX"
TABLEALIAS="MSI"
MODE="SELECT"
DISPLAY="ALL"
');
EXCEPTION
WHEN SRW.USER_EXIT_FAILURE THEN
SRW.message(1,'Failed in SRW FLEX INV INIT');
RAISE SRW.program_abort;
END;

-- SRW.message(1,'after all before report checks');
return(true);
end;

 

[nagornyi]

Application reports are all like this. They won't run if taken out of applications. Oracle support says it is not supported feature to run them on the desktop, though it is not clear how to modify them. Sometimes you can comment out those user exits and run the report, then uncomments them before saving and sending to applications. I would really like to know more about this issue.

 

[kzhangkzhang]

Hi Nagornyi:

I went to the Metalink and found out this is an old issue and solution from Oracle is to run the report from Oracle concurrent manager.

I really hope this issue can be resloved someday so that we can run the report from desktop instead of doing a lot of file ftp.