Query of Business Structure

select * from per_business_groups
where business_group_id= 101

select * from hr_operating_units
where organization_id = 112 -- (ORG_ID)

select * from org_organization_definitions
where OPERATING_UNIT = 112  -- Organization_id exists in INV tables

select * from hr_locations
where location_id = 142

select * from mtl_system_items_b
where ORGANIZATION_ID = 133 -- (Organization id of org_organization_definitions table)

How to get Oracle Workflow Approvers Comments Code

PROCEDURE get_approver_comments_doc (
      document_id     IN       VARCHAR2,
      display_type    IN       VARCHAR2,
      document        IN OUT   VARCHAR2,
      document_type   IN OUT   VARCHAR2
   )
   IS
   BEGIN
      document := NULL;
     
      FOR var IN
         (SELECT '<tr><td class="x1l x4x">'
                   || papf.full_name||'('||wnv.responder||')'
                   || '</td><td class="x1l x4x">'
                   ||nvl((select attribute_display_value from wf_notification_attr_resp_v where group_id = wnar.group_id AND ATTRIBUTE_NAME != 'COMMENETS' ),'Withdrawn' )
                   --|| nvl(ATTRIBUTE_DISPLAY_VALUE, case when wc.USER_COMMENT = 'Withdrawn by the Requestor' then 'Withdraw' end)
                   || '</td><td class="x1l x4x">'
                   || nvl(wnar.attribute_display_value,wc.USER_COMMENT)
                   || '</td><td class="x1l x4x">'
                   || TO_CHAR (wnv.end_date, 'DD-Mon-YYYY HH:MI:SS AM')
                   || '<br></td></tr>' AS NEWLINE

FROM WF_NOTIFICATIONS WNV
,wf_notification_attr_resp_v wnar
,fnd_user fu
,per_all_people_f papf
,wf_comments wc
WHERE 1=1
AND wnv.MESSAGE_TYPE like '%XXXX%'
AND wnar.MESSAGE_TYPE = wnv.MESSAGE_TYPE
and wnv.notification_id = wc.notification_id
and wc.ACTION = 'RESPOND'
AND wnv.GROUP_ID = wnar.GROUP_ID
and wnv.responder = fu.user_name
and fu.PERSON_PARTY_ID = papf.party_id(+)
AND wnar.ATTRIBUTE_NAME = 'COMMENETS'
AND wnar.MESSAGE_TYPE LIKE '%XXXX%'
AND EFFECTIVE_START_DATE <= SYSDATE
AND EFFECTIVE_END_DATE >= SYSDATE
and substr(item_key,1,instr(item_key,'/',-1)-1) = SUBSTR (wnv.CONTEXT,INSTR (wnv.CONTEXT, ':', 1)+1,INSTR (wnv.CONTEXT, '/', 1,3)- INSTR (wnv.CONTEXT, ':', 1)-1)
AND wnv.CONTEXT LIKE  (SELECT DISTINCT SUBSTR (wn.CONTEXT,1,INSTR (wn.CONTEXT, '/', 1,3)-1)
                                     FROM wf_notifications wn
                                    WHERE wn.notification_id = document_id)|| '%'
          ORDER BY wnv.end_date)
      LOOP
         document := document || var.NEWLINE;
      END LOOP;

      IF LENGTH (document) > 0
      THEN
         document :=
               '<table cellpadding="1" cellspacing="0" border="1" width="100%" style="font-family:arial;font-size:12px;" bgcolor=white><tr><th align=CENTER colspan=4 ><span style="color:#005588; font-family=arial;font-size:12">Comments of previous approvers</span></th></tr><tr><th scope="col" class="x1r">Action Owner</th><th scope="col" class="x1r x4j">Action</th><th scope="col" class="x1r x4j">Comments</th><th scope="col" class="x1r x4j">Approval Date</th></tr>'
            || document;
         document := document || '</table>';
      END IF;
   END get_approver_comments_doc;

GET ORACLE WORKFLOW ACTION HISTORY DETAILS

CREATE OR REPLACE FUNCTION APPS.XXXX_get_hist (
   p_nid         IN   PLS_INTEGER,
   p_disp_type   IN   VARCHAR2 DEFAULT wf_notification.doc_text
)
   RETURN VARCHAR2
AS
   v_hist   VARCHAR2 (32767);
BEGIN
   wf_notification.getcomments2
                            (p_nid                   => 157531211,
                             p_display_type          => NVL
                                                           (p_disp_type,
                                                            wf_notification.doc_text
                                                           ),
                             p_hide_reassign         => 'Y',
                             p_hide_requestinfo      => 'Y',
                             p_action_history        => v_hist
                            );
   RETURN v_hist;
END XXXX_get_hist;
/

Move order header different status and their meaning

select lookup_code, substr(meaning, 1, 60) "Meaning"
from mfg_lookups
where lookup_type = 'MTL_TXN_REQUEST_STATUS'
order by lookup_code

1 = Incomplete
2 = Pending Approval
3 = Approved
4 = Not Approved
5 = Closed
6 = Cancelled
7 = Pre-Approved
8 = Partially Approved
9 = Cancelled by Source

Disable/Enable Oracle Application Login User API

How to Disable(end date) large number of Users in Oracle Apps
How to end date large number of Users - fnd_users in Oracle Apps

To Disable/Enable bulk number of users in oracle Applications, we have a API

apps.fnd_user_pkg.EnableUser =>To Enable Users
apps.fnd_user_pkg.DisableUser =>To Disable Users,

Below is the syntax which can used to Huge number of Users in one GO !!
-------------------------------------------------------------------------------------

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.EnableUser(all_user.user_name);
commit;
end loop;
End;


-------------------------------------------------------------------------------------------


declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.DisableUser(all_user.user_name);
commit;
end loop;
End;

Delete Responsibility From Backend in Oracle Apps

/* Formatted on 2017/04/20 12:15 (Formatter Plus v4.8.8) */
DECLARE
   v_user_name             VARCHAR2 (100) := 'SAJAL';
   v_responsibility_name   VARCHAR2 (100) := 'Application Developer';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key    VARCHAR2 (100) := NULL;
   v_security_group        VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name, fr.responsibility_key,
          frg.security_group_key
     INTO v_application_name, v_responsibility_key,
          v_security_group
     FROM fnd_responsibility fr,
          fnd_application fa,
          fnd_security_groups frg,
          fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND fr.data_group_id = frg.security_group_id
      AND fr.responsibility_id = frt.responsibility_id
      AND frt.LANGUAGE = USERENV ('LANG')
      AND frt.responsibility_name = v_responsibility_name;

   fnd_user_pkg.delresp (username            => v_user_name,
                         resp_app            => v_application_name,
                         resp_key            => v_responsibility_key,
                         security_group      => v_security_group
                        );
   COMMIT;
   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is removed from the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while deleting responsibilty from the user and the error is '
          || SQLERRM
         );
END;
/

Delete Request Set from Backend in Oracle Apps

BEGIN
FND_SET.DELETE_SET(request_set => 'XX_RG', application => 'XXAPPL');
COMMIT;
end;

In the code above, XX_RG is an example request set name and XXAPPL is an example application name.