Thursday, July 16, 2020

EAM SQL Statement to fetch the Short Term attached text for a particular EAM Work Order

-------------------------------------------------------------------------------------------------------------------
--EAM SQL Statement to fetch the Short Term attached text for a particular EAM Work Order
-- Parameters P_WIP_ENTITY_ID
--            P_ORGANIZATION_ID
-------------------------------------------------------------------------------------------------------------------
SELECT fdst.short_text,
       fdv.title,
       ad.pk1_value organization_id,
       ad.pk2_value wip_entity_id,
       fdv.media_id
  FROM fnd_documents_vl fdv,
       fnd_attached_documents ad,
       fnd_documents_short_text fdst
 WHERE 1=1
   AND fdv.document_id = ad.document_id
   AND ad.entity_name = 'EAM_WORK_ORDERS'
   AND fdv.datatype_name = 'Short Text'
   AND (security_type = 4 OR publish_flag = 'Y')
   AND fdst.media_id = fdv.media_id
   AND ad.pk2_value = :P_Wip_Entity_Id
   AND ad.pk1_value = :P_Organization_Id;

Sunday, June 22, 2014

SQL Statement to fetch AME Rules based on a condition across different transaction types

---------------------------------------------------------------------
--SQL Statement to get AME Rules based on a condition across transaction types
-- Parameter:P_AME_CONDITION -- Input condition name(partial-match works)
---------------------------------------------------------------------

SELECT   ame.rulename,ame.creation_date,
         ame.condition_and_action "condition/action",
         atl.val "Condition_Desc"
    FROM (SELECT r.description rulename, r.creation_date,
                 condition_id, 'Condition' condition_and_action,
                 ame_utility_pkg.get_condition_description (condition_id) val,
                 NULL action_type
            FROM ame_rules_tl rtl, ame_rules r, ame_condition_usages cu
           WHERE r.rule_id = rtl.rule_id
             AND cu.rule_id = r.rule_id
             AND rtl.LANGUAGE = 'US'
          UNION ALL
          SELECT DISTINCT r.description rulename, r.creation_date,
                          a.action_id, 'Action' condition_and_action,
                          atl.description val,
                          actl.user_action_type_name action_type
                     FROM ame_rules_tl rtl,
                          ame_rules r,
                          ame_action_usages au,
                          ame_actions a,
                          ame_actions_tl atl,
                          ame_action_types act,
                          ame_action_types_tl actl
                    WHERE r.rule_id = rtl.rule_id
                      AND au.rule_id = r.rule_id
                      AND rtl.LANGUAGE = 'US'
                      AND au.action_id = a.action_id
                      AND a.action_id = atl.action_id
                      AND act.action_type_id = actl.action_type_id
                      AND act.action_type_id = a.action_type_id
                      ) ame
WHERE ame_utility_pkg.get_condition_description (condition_id) LIKE  '%&P_AME_CONDITION%'
ORDER BY rulename, condition_and_action DESC;

Cloned date of Oracle application and Version


--Query to get Version details..
SELECT product, version, status
  FROM product_component_version;



--Query to  get cloned date of an oracle instance
SELECT resetlogs_time,name

  FROM v$database;

Select statement to derive all the POs, Reqs delegated by user/s with WF Admin privileges in Oracle EBS

-------------------------------------------------------------------------------------------------------------------------------------
--Query to fetch all the POs,Requisitions delegated by User with WF Admin privileges
--Parameters:
-- p_po_or_req_num -- Input PO/Requisition Number (Optional parameter)
-- p_delegated_by_racfid -- Input RACFID of person who delegated the PO/Requisitions (Optional parameter)
-- p_delegate_from_date -- Input date to fetch records on/after the input date(Defaults to 03/Aug/2014 if left blank)
-------------------------------------------------------------------------------------------------------------------------------------
 
SELECT   papf2.full_name, pah3.sequence_num seq, pah3.action_code,
         pha.segment1, pah3.object_type_code, pah3.note,
         pah3.last_update_date, fu.user_name delegated_by,
         fu.description delegated_by_person, fu1.user_name delegated_from,
         fu1.description delegated_from_person, fu.employee_id,
         pah3.employee_id, prh.wf_item_key
    FROM hr.per_all_people_f papf2,
         po.po_action_history pah3,
         po.po_headers_all pha,
         fnd_user fu,
         fnd_user fu1,
         po_requisition_headers_all prh
   WHERE pah3.employee_id = papf2.person_id
     AND pah3.last_updated_by = fu.user_id
     AND pah3.object_id = pha.po_header_id
     AND pah3.employee_id = fu1.employee_id
     AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date
                             AND papf2.effective_end_date
     AND pah3.action_code = 'DELEGATE'
     AND TRUNC (pah3.action_date) >= NVL(:p_delegate_from_date,'01-JAN-1951')
     AND pah3.object_type_code = 'REQUISITION'
     AND pah3.object_id = prh.requisition_header_id
     AND fu.user_name = NVL (:p_delegated_by_racfid, fu.user_name)
     AND prh.segment1 = NVL (:p_po_or_req_num, prh.segment1)
UNION
SELECT   papf2.full_name, pah3.sequence_num seq, pah3.action_code,
         pha.segment1, pah3.object_type_code, pah3.note,
         pah3.last_update_date, fu.user_name delegated_by,
         fu.description delegated_by_person, fu1.user_name delegated_from,
         fu1.description delegated_from_person, fu.employee_id,
         pah3.employee_id, prh.wf_item_key
    FROM hr.per_all_people_f papf2,
         po.po_action_history pah3,
         po.po_headers_all pha,
         fnd_user fu,
         fnd_user fu1,
         po_headers_all prh
   WHERE pah3.employee_id = papf2.person_id
     AND pah3.last_updated_by = fu.user_id
     AND pah3.object_id = pha.po_header_id
     AND pah3.employee_id = fu1.employee_id
     AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date
                             AND papf2.effective_end_date
     AND pah3.action_code = 'DELEGATE'
     AND TRUNC (pah3.action_date) >= NVL(:p_delegate_from_date,'01-JAN-1951')
     AND pah3.object_type_code = 'PO'
     AND pah3.object_id = prh.po_header_id
     AND prh.segment1 = NVL (:p_po_or_req_num, prh.segment1)
     AND fu.user_name = NVL (:p_delegated_by_racfid, fu.user_name)
ORDER BY 5 DESC, 7 DESC;

Saturday, May 24, 2014

How to fetch Oracle AME Rule details based on Condition for across transaction types(SQL QUERY)

-------------------------------------------------------------------------
--SQL Statement to get AME Rules based on a condition across transaction types
-- Parameter:P_AME_CONDITION -- Input condition name(partial-match works)
-------------------------------------------------------------------------

SELECT   ame.rulename,ame.creation_date,
         ame.condition_and_action "condition/action",
         atl.val "Condition_Desc"
    FROM (SELECT r.description rulename, r.creation_date,
                 condition_id, 'Condition' condition_and_action,
                 ame_utility_pkg.get_condition_description (condition_id) val,
                 NULL action_type
            FROM ame_rules_tl rtl, ame_rules r, ame_condition_usages cu
           WHERE r.rule_id = rtl.rule_id
             AND cu.rule_id = r.rule_id
             AND rtl.LANGUAGE = 'US'
          UNION ALL
          SELECT DISTINCT r.description rulename, r.creation_date,
                          a.action_id, 'Action' condition_and_action,
                          atl.description val,
                          actl.user_action_type_name action_type
                     FROM ame_rules_tl rtl,
                          ame_rules r,
                          ame_action_usages au,
                          ame_actions a,
                          ame_actions_tl atl,
                          ame_action_types act,
                          ame_action_types_tl actl
                    WHERE r.rule_id = rtl.rule_id
                      AND au.rule_id = r.rule_id
                      AND rtl.LANGUAGE = 'US'
                      AND au.action_id = a.action_id
                      AND a.action_id = atl.action_id
                      AND act.action_type_id = actl.action_type_id
                      AND act.action_type_id = a.action_type_id
                      ) ame
WHERE ame_utility_pkg.get_condition_description (condition_id) LIKE  '%&P_AME_CONDITION%'
ORDER BY rulename, condition_and_action DESC;

How to remove Scientific Notation for a numeric value in Toad for Oracle



How to remove Scientific Notation for a numeric value in Toad in Oracle
Example of Scientific Notation:-



View/Toad Options/Data Grids/Data
Uncheck "Display large numbers in Scientific Notation" checkbox as mentioned below:-



Run the query again to view full numeric value in the result:-