How to enable the “Operating Unit” Parameter for a Concurrent Program in Oracle Apps R12

We notice that the new field as 'Operating Unit' added in R12 and this is by default is in disabled mode:

An applications responsibility can access multiple OUs. Some concurrent programs have been enhanced to process multiple operating units simultaneously, while for other requests the operating unit must be specified when you run the program.

To support this, concurrent programs are defined with an operating unit mode of

'S' for single operating unit and 'M'(concurrent program will be in this mode by default) for multiple operating units.

If the 'Operating Unit Mode' is not set for the concurrent program it will fail.

The ‘Operating Unit’ field/parameter is known as ‘Reporting Context’ in MOAC(Multi Org Access Control) terminology.

How we can achieve from the Oracle Applications Front-End.

1. Login into application with System Administration responsibility (NOT System
     Administrator)
2. Navigate: Concurrent è Programs

3. Query for Short Name or Program Name of the concurrent program like as below.

4. Click on Update icon of your program.

5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit Mode' field.
7. Save changes by clicking on 'Apply' button.

8. Change responsibility where the ‘Concurrent Program’ can be run, in this example ‘TESTOU’ is assigned to ‘AR Super User’ responsibility                              

9. Select the ‘Concurrent Program’ from the ‘SRS Window’, now you can see that ‘Operating 

    Unit’ field is enabled and you can see the Operating Units in the LOV.

 

10. This particular selected Operating Unit value can be accessed using the standard MOAC API:MO_GLOBAL.GET_CURRENT_ORG_ID

Thanks

Amar Alam

Query to get user manager and department details in Oracle Cloud

SELECT DISTINCT
    pu.username,
    pea.email_address employee_email,
    ppnf.first_name
    || ' '
    || ppnf.last_name employee_name,
    hauft.name department,
    (
        SELECT
            ppnf1.full_name
        FROM
            per_assignment_supervisors_f pasf,
            per_person_names_f ppnf1
        WHERE
            1 = 1
            AND   pasf.manager_type = 'LINE_MANAGER'
            AND   pasf.manager_id = ppnf1.person_id
            AND   SYSDATE BETWEEN ppnf1.effective_start_date AND ppnf1.effective_end_date
            AND   SYSDATE BETWEEN pasf.effective_start_date AND pasf.effective_end_date
            AND   ppnf1.name_type = 'GLOBAL'
            AND   pasf.person_id = pu.person_id
            AND   ROWNUM = 1
    ) supervisorname,
    pea1.email_address supervisor_email
FROM
    ase_user_b u,
    per_users pu,
    per_all_people_f papf,
    per_person_names_f ppnf,
    per_email_addresses pea,
    hr_org_unit_classifications_f houcf,
    hr_all_organization_units_f haouf,
    hr_organization_units_f_tl hauft,
    per_all_assignments_m paam,
    per_assignment_supervisors_f pasf,
    per_email_addresses pea1
WHERE
    u.user_guid = pu.user_guid
    AND   nvl(u.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(u.effective_end_date,SYSDATE) >= SYSDATE
    AND   nvl(pu.active_flag,'Y') = 'Y'
    AND   nvl(pu.suspended,'N') = 'N'
    AND   pu.person_id = papf.person_id
    AND   nvl(papf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(papf.effective_end_date,SYSDATE) >= SYSDATE
    AND   papf.person_id = ppnf.person_id
    AND   nvl(ppnf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(ppnf.effective_end_date,SYSDATE) >= SYSDATE
    AND   papf.person_id = pea.person_id
    AND   papf.primary_email_id = pea.email_address_id
    AND   papf.person_id = paam.person_id
    AND   haouf.organization_id = houcf.organization_id
    AND   haouf.organization_id = hauft.organization_id
    AND   nvl(haouf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(haouf.effective_end_date,SYSDATE) >= SYSDATE
    AND   hauft.language = 'US'
    AND   hauft.effective_start_date = haouf.effective_start_date
    AND   hauft.effective_end_date = haouf.effective_end_date
    AND   houcf.classification_code = 'DEPARTMENT'
    AND   nvl(hauft.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(hauft.effective_end_date,SYSDATE) >= SYSDATE
    AND   hauft.organization_id = paam.organization_id
    AND   paam.primary_assignment_flag = 'Y'
    AND   paam.assignment_type IN (
        'E',
        'C'
    )
    AND   paam.effective_latest_change = 'Y'
    AND   nvl(paam.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(paam.effective_end_date,SYSDATE) >= SYSDATE
    AND   papf.person_id = pasf.person_id
    AND   pasf.manager_type = 'LINE_MANAGER'
    AND   nvl(pasf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(pasf.effective_end_date,SYSDATE) >= SYSDATE
    /*AND   pasf.manager_id=ppnf1.person_id
    AND   ppnf1.name_type = 'GLOBAL'
    AND   nvl(ppnf1.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(ppnf1.effective_end_date,SYSDATE) >= SYSDATE*/
    AND   pasf.manager_id = pea1.person_id
   --AND upper(pu.username) like 'AALAM%'

Thanks
Amar Alam

Oracle Cloud + Query to Find List of Users for The Specific Role

SELECT pu.username,
                 papf.person_number,
                 r.code,
                 r.role_type_code,
                 rtl.role_name,
                 rtl.description
FROM  ase_user_b u,
              per_users pu,
              per_all_people_f papf,
              ase_user_role_mbr ur,
              ase_role_b r,
              ase_role_tl rtl
WHERE 1=1
AND u.user_guid = pu.user_guid AND
NVL(u.effective_start_date,sysdate) <= sysdate AND
NVL(u.effective_end_date,sysdate) >= sysdate AND
NVL(pu.active_flag,'Y') = 'Y' AND
nvl(pu.suspended,'N') = 'N' AND
pu.person_id=papf.person_id AND
nvl(papf.effective_start_date,sysdate) <= sysdate and
nvl(papf.effective_end_date,sysdate) >= sysdate and
r.role_id = rtl.role_id AND
r.role_id = ur.role_id AND
u.user_id = ur.user_id AND
rtl.language = 'US' and
nvl(r.effective_start_date,sysdate) <= sysdate and
nvl(r.effective_end_date,sysdate) >= sysdate and
nvl(ur.effective_start_date,sysdate) <= sysdate and
nvl(ur.effective_end_date,sysdate) >= sysdate and
rtl.role_name = 'ARRIS Employee'

Your's
Amar Alam

Oracle apps + Query to get Customer Contact details

select account_number "Account Number"
     , obj.party_name "Customer Name"
     , sub.party_name "Contact Name"
     , hcp.contact_point_type || ': ' ||
       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address
                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number
                                    , 'WEB'  , hcp.url
                                    , 'Unknow contact Point Type ' || hcp.contact_point_type
             ) "How to Contact"
  from apps.hz_cust_accounts  hca
     , apps.hz_parties        obj
     , apps.hz_relationships  rel
     , apps.hz_contact_points hcp
     , apps.hz_parties        sub
 where hca.party_id           = rel.object_id
   and hca.party_id           = obj.party_id
   and rel.subject_id         = sub.party_id
   and rel.relationship_type  = 'CONTACT'
   and rel.directional_flag   = 'F'
   and rel.party_id           = hcp.owner_table_id
   and hcp.owner_table_name   = 'HZ_PARTIES'
   and hca.account_number=1258;

Thanks
Amar Alam

Query to Get Organization Location Details

SELECT  ood.organization_code ,
        ood.organization_name,
        hou.name,
        hou.address_line_1,
        hou.country,
        hou.town_or_city
FROM org_organization_definitions ood,
     hr_organization_units_v hou
WHERE ood.disable_date IS NULL
AND ood.organization_id    = hou.organization_id;

Your's
Amar Alam