Trouble shooting Oracle Apps (EBS) printer issues can be a headache sometimes

Add the printer in Oracle E-business Suite

Login as Oracleerp user
Install -> Printers--> register

Put the printer name and select the printer type 

For example :  HPLJ3-A4

Few Printer related setups and issue at the OS Server level: 
============================================
To confirm whether the issue is from application end or from the Network/Hardware at Physical end.

So as an Apps DBA we could do the following to get to the root Cause
(After checking that all the printer configuration values such as printer name , print style,driver,etc are present as required in EBS Application).
Also if its newly added server check if the printer host details are entered in /etc/hosts

To check whether printer is enabled or disabled  on the unix server

lpstat -p infy_prt1
printer infy_prt1 disabled since Tue 29 Dec 2015 12:20:40 PM GST -
        No %%BoundingBox: comment in header!

 ping infy_prt1
PING infy_prt1.us.com (86.96.203.59) 56(84) bytes of data.

^C
--- infy_prt1.us.com ping statistics ---
60 packets transmitted, 0 received, 100% packet loss, time 59811ms

Login to the Unix server where the EBS application is running and do the following steps

prod apps $ cat test.log
Test print

Below is the command to print  from unix server.
 lp -d 0668 abc.log
request id is 0668-1983004 (1 file(s))
Data file sent successfully

If the request is able to go to the printer queue than there is no issue from Unix level of the printer configuration Else the Unix team needs to check the Printer setup on the server.

Now if the request is going to the queue and then not able to process at the printer level then physical intervention maybe required to see if  what the issue is with the printer

General Process for Printer Setup steps on EBS environment
1.       Windows Group defines the printer on print server
2.       Linux Team takes that Windows printer server name and defines Linux Print Queue Definition
3.       Oracle DBA’s use the Linux printer created to define the printer in E-Business Suite.

Some useful  Printer related commands on Unix servers :
========================================

 "lpq -a" from your unix application server   this will print all jobs in printer queues 

i.e 
[applprod@erpprod ~]$  lpq -a
Rank    Owner   Job     File(s)                                Total Size
1st     applpro 1116    TEST.txt                                 1024 bytes
2nd     applpro 1117    pasta58079_0.tmp                8648704 bytes

3rd     applpro 1118    pasta16518_0.tmp                11264 bytes

alternately you could also use "lpstat -o" 

 lpstat -o
B57-ENG2-1116           applprod          1024   Mon 09 Sep 2019 08:06:00 AM GST
B57-ENG2-1117           applprod       8648704   Mon 09 Sep 2019 08:34:04 AM GST
B57-ENG2-1118           applprod         11264   Mon 09 Sep 2019 09:50:01 AM GST

Cancel the current job on the default printer:

           lprm

       Cancel job 1234:

           lprm 1234

       Cancel all jobs:


           lprm -

Below is example how to  the list of Printers configured on Unix server :

 lpstat -v
device for 9p03-B56: lpd://199.8.8.37
device for 9903-FF: lpd://199.8.8.215
device for 9903-GF: lpd://199.8.8.252/lp
device for infy_prt1MaterialDPT: lpd://199.8.8.246
device for infy_prt_Finance: lpd://1199.8.8.245/lp
device for infy_prt1: hp:/net/HP_LaserJet_5200?ip=199.8.8.247
device for infy_prt2: hp:/net/HP_LaserJet_5200?ip=199.8.8.248

device for Colourcube-9303-B57: lpd://192.6.0.15

Script to Automate Restart/bouncing of Worflow Mailer from backend in EBS

Following Pl/sql Code will restart the Workflow mailer based on the condition given (The condition can vary according to your requirements)


declare
n_component_id fnd_svc_components.component_id%TYPE;
n_status  varchar2(3000);
nummessages  number;
p_retcode number;
p_errbuf varchar2(100);
begin

begin

SELECT COUNT(*) into  nummessages
FROM wf_notifications
WHERE begin_date >= TRUNC(SYSDATE-1)
AND mail_Status IS NOT NULL
AND mail_status = 'MAIL'
AND message_name <> 'PO_PO_HAS_BEEN_APPROVE'
AND (status <> 'CLOSED' and status <> 'CANCELED')
AND 'PROD' = (SELECT name FROM v$database)
HAVING COUNT(*) <> 0;

exception
when no_data_found then
dbms_output.put_line('Null POS found ' );
nummessages:=0;
end;

begin


select component_id,COMPONENT_STATUS into  n_component_id , n_status
from apps.fnd_svc_components
where component_name='Workflow Notification Mailer';

exception
when no_data_found then
dbms_output.put_line('Component not found ' );
end;

IF   nummessages=0 THEN

fnd_svc_component.start_component(n_component_id, p_retcode, p_errbuf);
       commit;

dbms_output.put_line(n_component_id||' '||n_status ||'' || p_retcode||p_errbuf);

end if;

exception
when no_data_found then
dbms_output.put_line('Nothing to do ' );
end;

Sample code or Autmation to check EBS Health check or sanity regularly

Below Shell script should be registered with a concurrent program and scheduled at regular intervals to confirm the sanity check of EBS Components

#! /usr/bin/ksh
SUBJECT="QFIN_SANITY_CHECK"
EMAIL=giriraj.inja@xyz.com

rm sanity.log
rm sanity_check.log
rm s.log
rm qfinsanity.log
touch qfinsanity.log
touch sanity.log
touch sanity_check.log

/opt/oracle/qfin/qfincomn/admin/scripts/qfin_sq099fin02/adcmctl.sh status apps/goofy |grep -i "Internal Concurrent Manager is" >> sanity.log
echo " Active User Request " >> sanity.log

CONCSUB apps/goofy@qfin SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND FNDSCURS >> sanity.log
if [ $? -ne 0 ]
then
    echo failed
mailx -s "QFIN INSTANCE CHECK ERROR "  giriraj.inja@xyz.com < error.log
else
sqlplus apps/goofy@qfin <<END
set echo off
set lines 120
set heading off
set feedback off
set serveroutput on
set echo off
spool s.log
exec test_check.db_check();
exec test_check.comp_check();
exit
END
if [ $? -ne 0 ]
then
    echo failed
mailx -s "QFIN INSTANCE CHECK ERROR "  giriraj.inja@xyz.com < error.log
else
cat s.log |grep -v "SQL>" >> sanity.log
mailx -s $SUBJECT $EMAIL < sanity.log
fi




test_check.db_check is package that query status of db and application nodes

select
NODE_NAME,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database
from fnd_nodes
where node_name != 'AUTHENTICATION';

Query to find Node related information/services on EBS environment

 select
     NODE_NAME,
     decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
     decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
     decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
     decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
     decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
     decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database
   from fnd_nodes
   where node_name != 'AUTHENTICATION';

Script to automate change EBS Login page color (i.e in Cloning Process ,etc )

Either of the following two codes can be used for changing the EBS Login page Color to ditinguish between PROD  and Non-PROD environment

Option 1.

UPDATE fnd_profile_option_values fpv

SET fpv.PROFILE_OPTION_VALUE='RED'                      -----> (specifiy the color prefered by you)

WHERE fpv.PROFILE_OPTION_ID in ( SELECT t2.PROFILE_OPTION_ID

FROM FND_PROFILE_OPTIONS_TL t1,FND_PROFILE_OPTIONS t2

WHERE t1.USER_PROFILE_OPTION_NAME = 'Java Color Scheme'

AND t1.LANGUAGE = 'US' AND t2.PROFILE_OPTION_NAME = t1.PROFILE_OPTION_NAME);

Option 2.

DECLARE

stat boolean;

   BEGIN

  dbms_output.disable;

  dbms_output.enable(100000);

  stat := FND_PROFILE.SAVE('FND_COLOR_SCHEME','RED','SITE');

  IF stat THEN

              dbms_output.put_line( 'Stat = TRUE - profile updated' );

          ELSE

              dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );

         END IF;

           commit;

   END;

  /