CUSTOM.pll

1. Take the back up of the CUSTOM.pll present in path "/djak/uat/app/apps/apps_st/appl/au/12.0.0/resource" to CUSTOM.pll08Oct2012

2. Port the attached CUSTOM.pll to above path


3. Compile the CUSTOM.pll from above path using below command(please provide apps database username/password)

frmcmp_batch module=CUSTOM.pll userid=apps/<pwd> output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

Query ORG_ID vs ORGANIZATION_ID

select FSP.INVENTORY_ORGANIZATION_ID,
       FSP.ORG_ID ,
       HOU.name   ,
       mp.organization_code
      
from FINANCIALS_SYSTEM_PARAMS_ALL  FSP,
     HR_OPERATING_UNITS HOU      ,
     mtl_parameters mp
where 1=1
and   FSP.ORG_ID                    =  HOU.ORGANIZATION_ID
and   FSP.INVENTORY_ORGANIZATION_ID =  MP.ORGANIZATION_ID(+);
and   mp.organization_code = 'XXX';

Query to retrieve active Alert information

SELECT aa.alert_name, aa.start_date_active, al.meaning "FREQUENCY_TYPE",
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code

Oracle Alert History

SELECT
    alv . *
    FROM ALR_ALERTS al ,
    ALR_ACTION_HISTORY aah ,
    ALR_OUTPUT_HISTORY aoh ,
    ALR_ACTIONS_V alv ,
    alr_alert_historY_view aahv
    WHERE al . alert_name = <name of alert > --'XPEP_PENDING_APPROVALS'
    AND al . alert_id = aah . alert_id
    AND aah . check_id = aoh . check_id
    AND alv . ALERT_ID = aah . alert_id
    AND aahv . alert_name = al . alert_name;

Qury to find Related Items for an existing Item

declare
a varchar2(1000):= null;
b varchar2(1000):= null;
CURSOR c_cur IS
 select
ito.segment1 item,
--ito.description,
ITR.SEGMENT1 RELATED_ITEM,
--itr.description,
ML.MEANING RELATION
--ri.reciprocal_flag
from
mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
where ri.inventory_item_id=ito.inventory_item_id
and ri.organization_id=ito.organization_id
and ri.related_item_id=itr.inventory_item_id
and ri.organization_id=itr.organization_id
and ri.relationship_type_id=ml.lookup_code
AND ML.LOOKUP_TYPE(+)='MTL_RELATIONSHIP_TYPES'
AND ITO.SEGMENT1  ='XXXX';
begin
a:=  'Related Items exists for the Item '  ||'. Please enter Related Item from below list'|| CHR(10);
a := a||rpad('Related Item',19,' ')||rpad('Relation',20,' ')|| CHR(10)||rpad('*************',22,' ')||rpad('******************',23,' ')|| CHR(10);
FOR r_line IN c_cur
    LOOP
     b:=b||rPAD(R_LINE.RELATED_ITEM,25,' ')||rPAD(R_LINE.RELATION,20,' ')||CHR(10);
    end LOOP;
   dbms_output.put_line( a||b);
    EXCEPTION
when OTHERS then
dbms_output.put_line( 'No data found');
end;

Query to find User who is locking the table

SELECT objects.owner,
       objects.object_name,
       objects.object_type,
       user1.user_name         locking_fnd_user_name,
       login.start_time        locking_fnd_user_login_time,
       vs.module,
       vs.machine,
       vs.osuser,
       vlocked.oracle_username,
       vs.sid,
       vp.pid,
       vp.spid                 os_process,
       vs.serial#,
       vs.status,
       vs.saddr,
       vs.audsid,
       vs.process
  FROM fnd_logins      login,
       fnd_user        user1,
       v$locked_object vlocked,
       v$process       vp,
       v$session       vs,
       dba_objects     objects
 WHERE vs.sid = vlocked.session_id
   AND vlocked.object_id = objects.object_id
   AND vs.paddr = vp.addr
   AND vp.spid = login.process_spid(+)
   AND vp.pid = login.pid(+)
   AND login.user_id = user1.user_id(+)
--change the table name below

   AND objects.object_name LIKE '%' || upper('PO_HEADERS_ALL') || '%'
   AND nvl(vs.status,
           'XX') != 'KILLED';

Discoverer Universal Query

SELECT DISTINCT d.ba_name business_area
               ,c.obj_name folder_name
               ,decode(c.obj_type, 'SOBJ','From Database', 'CUO', 'Custom Query', 'COBJ', 'Complex Folder') obj_type_desc
               ,c.sobj_ext_table view_name
               ,disco_docs.doc_name "Discoverer Workbook",
                TRUNC (disco_docs.doc_created_date) "Workbook Create Date",
                CASE
                   WHEN INSTR
                          (disco_docs.doc_created_by,
                           '#'
                          ) = 0
                      THEN disco_docs.doc_created_by
                   WHEN INSTR (disco_docs.doc_created_by, '#') > 0
                   AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                      SUBSTR (disco_docs.doc_created_by, 2, 5))
                   ELSE NULL
                END "Workbook Owner/Creator",
                disco_users.eu_username,
                CASE
                   WHEN INSTR
                          (disco_users.eu_username,
                           '#'
                          ) = 0
                      THEN disco_users.eu_username
                   WHEN INSTR (disco_users.eu_username, '#') > 0
                   AND INSTR (disco_users.eu_username, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                   ELSE (SELECT resp.responsibility_name
                           FROM fnd_responsibility_tl resp
                          WHERE resp.responsibility_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                END AS "Shared Name / Responsibility"
              
              
              
           FROM biseul_us.eul5_documents disco_docs,
                biseul_us.eul5_access_privs disco_shares,
                biseul_us.eul5_eul_users disco_users,
                --
                biseul_us.eul5_qpp_stats  a,
                biseul_us.eul5_bas d,
                biseul_us.eul5_objs c,
                biseul_us.eul5_ba_obj_links f
               
                
          WHERE 1=1
            AND a.qs_doc_name= disco_docs.doc_name
            AND c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
            AND c.obj_id = f.bol_obj_id
            AND d.ba_id = f.bol_ba_id
            AND disco_docs.doc_id = disco_shares.gd_doc_id
            AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
            AND DISCO_USERS.EU_ID(+) = DISCO_SHARES.AP_EU_ID
            AND upper(disco_docs.doc_name) LIKE upper('% :Report sheet name here%');