Hello guys,
Happy New Year!
Today we will see how we can use CUSTOM.pll for enabling special menus and printing a custom report by invoking the special menu that we activate using the library.
We are going to use Oracle’s seeded form “POXPOEPO” AKA Purchase Orders.
Please make sure that you make a backup for the CUSTOM.pll prior making below said modifications. CUSTOM.pll is “always” found uner $AU_TOP/resources folder
We’ll enable the SPECIAL15 menu item for the exercise.
Load up CUSTOM.pll using Oracle Forms Designer & make sure you are connected to database before loading the library file.
Attach APPCORE, APPCORE2 libraries with your copy of CUSTOM.pll
Attach FNDCONC.pll library for calling printing related activities
Your CUSTOM.pll should look like the above after attaching said libraries.
Add the block as seen with the image by the bottom of your CUSTOM.pll package body.
BEGIN
IF (event_name = 'WHEN-NEW-FORM-INSTANCE') THEN
IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN
-- fnd_message.debug(l_form_name);
app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
END IF;
END IF;
END;
Now you can proceed with writing code for what happens when “SPECIAL15” event happens
BEGIN
IF (event_name = 'SPECIAL15') THEN
IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN
print_po(name_in('PO_HEADERS.ORG_ID'),name_in('PO_HEADERS.SEGMENT1'));
--fnd_message.debug('Will Print This Order');
--app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
END IF;
END IF;
END;
Here, I am calling a procedure that I defined with CUSTOM.pll for handling print requests.
and the package body is as following (not another image, I am going to save some efforts for you)
procedure print_po(p_org_id IN NUMBER, p_order_number IN VARCHAR2) is
l_ord_num NUMBER := 0;
l_ord_type_name VARCHAR2 (240);
l_req_id_Rep NUMBER;
l_request_completed BOOLEAN := FALSE;
l_req_phase VARCHAR2 (20);
l_req_status VARCHAR2 (1000);
l_req_dev_phase VARCHAR2 (1000);
l_req_dev_status VARCHAR2 (1000);
l_req_message VARCHAR2 (1000);
l_conc_mgr_status NUMBER;
p_call_stat NUMBER;
p_activep_stat NUMBER;
l_order_category_code NUMBER;
l_report_name VARCHAR2 (40);
l_ret_report_name VARCHAR2 (40);
l_req_id NUMBER;
l_order_type_name VARCHAR2(30);
--
l_signing_person VARCHAR2(240);
l_person_designation VARCHAR2(240);
BEGIN
--fnd_message.debug('Will Print This Order');
/*This is a custom procedure that checks whether the concurrent manager is online or not, you can safely comment this line
--Check the status of Concurrent Manager
apps.xx_conc_mgr_status_p (p_call_stat, p_activep_stat);
IF p_call_stat <> 0
THEN
fnd_message.set_string ('Concurrent Manager Status Unknown');
fnd_message.show;
ELSE
IF p_activep_stat > 0
THEN
NULL; --Message('ICM is running' || l_activep);
ELSE
fnd_message.set_string (
'Concurrent Manager is down, Please try printing the invoice later');
fnd_message.show;
RAISE form_trigger_failure;
END IF;
END IF;
--Checking concurrent manager status end----
-- MESSAGE ('Concurrent manager status: up & running');
*/
BEGIN
/* I am picking up the reports names (concurrent_program_name from FND_CONCURRENT_PROGRAMS_VL view as we have different layouts for companies
you can set up a value for l_report_name while variable is defined
---
--
SELECT execution_file_name,STRING1, STRING2
INTO l_report_name, l_signing_person, l_person_designation
FROM omspoprintreg
WHERE 1 = 1 AND organization_id = p_org_id
AND TRUNC (SYSDATE) BETWEEN start_date_active
AND NVL (end_date_active, SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_message.set_string (
'No reports defined for this type of transaction, Please contact OM Super User');
fnd_message.show;
RAISE form_trigger_failure;
END;
-- FND_MESSAGE.DEBUG('Printing Order '||p_org_id||' order number '||p_order_number);
*/
--
--
l_req_id :=
fnd_request.submit_request ('PO',
l_report_name,
NULL,
SYSDATE,
FALSE,
P_ORG_ID,
P_ORDER_NUMBER,
l_signing_person,
l_person_designation,
chr(0));
--You cannot setup :SYSTEM.MESSAGE_LEVEL within CUSTOM.pll, hence use COPY
--to suppress messages like 'Two records saved'
COPY('25','SYSTEM.MESSAGE_LEVEL');
COMMIT;
-- FND_MESSAGE.DEBUG('Your request id is '||l_req_id);
l_request_completed :=
fnd_concurrent.wait_for_request (request_id => l_req_id,
INTERVAL => 1,
phase => l_req_phase,
status => l_req_status,
dev_phase => l_req_dev_phase,
dev_status => l_req_dev_status,
MESSAGE => l_req_message);
--:SYSTEM.Message_Level := '25';
COMMIT;
editor_pkg.report (l_req_id, 'Y');
end print_po;
That’s it!
Now copy the CUSTOM.pll to $AU_TOP/resources & compile it
frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special
If you don’t have syntax errors or other, you must have the fresh CUSTOM.pll
Please make sure that no users are currently online while you are compiling the CUSTOM.pll (This is only applicable to cases where the CUSTOM.pll is already deployed for different forms)
Log on to the instance, access Purchase Orders form & you should see a new menu item under “Tools” menu enabled
While CUSTOM.pll implements “editor_pkg.report” by attaching FNDCONC.pll, FORMS personalization will fail to provide the same functionality as most of the seeded forms do not have FNDCONC library attached to them by default. If you don’t want to use editor_pkg.report to open the completed report, you may create a database level procedure to submit the request(s) and call the same against SPECIAL(n) menu item through FORMS personalization.
Enjoy!
rajesh