Query to find customer site level contact points

Customer Site level contact points

/* *******************************************************************
Modules Involved : AR
Description      : get the customer site level communication details
****************************************************************** */
select HP.PARTY_NAME               PARTY_NAME,
       hps.party_site_number       Site_Number,
       hcp.contact_point_type      Contact_Type,
       hcp.phone_line_type         Phone_Line_Type,
       hcp.phone_country_code      Country_Code,
       hcp.phone_number            Phone_number,
       hcp1.contact_point_type     Contact_Type,
       hcp1.email_address          Email_Address ,
       HCP1.EMAIL_FORMAT           EMAIL_FORMAT,
       HCP1.CONTACT_POINT_PURPOSE  CONTACT_POINT_PURPOSE,
       HL.ADDRESS1||','||HL.ADDRESS2||' '||HL.ADDRESS3||' '||HL.ADDRESS4||' '||HL.CITY||','||HL.STATE||','
       ||hl.POSTAL_CODE||','||hl.country SITE_ADDRESS
  FROM apps.hz_parties             hp,
       apps.hz_party_sites         hps,
       APPS.HZ_CONTACT_POINTS      HCP,
       APPS.HZ_CONTACT_POINTS      HCP1,
       apps.hz_locations           hl
WHERE 1=1  
  AND hp.party_id              = hps.party_id
  AND hcp.owner_table_name     = 'HZ_PARTY_SITES'
  AND hcp.owner_table_id       = hps.party_site_id
  AND hcp.contact_point_type   = 'PHONE'
  AND hcp1.owner_table_name    = 'HZ_PARTY_SITES'
  AND hcp1.owner_table_id      = hps.party_site_id
  and HCP1.CONTACT_POINT_TYPE  = 'EMAIL'  
  and hps.location_id          = hl.location_id
  and HPS.PARTY_SITE_NUMBER    = '100095'
  ;

   

Query to get the order and invoice details

Joins between OM and AR

/* *******************************************************************
Modules Involved : AR, OM
Description      : get the invoices and order details
****************************************************************** */

SELECT ooha.order_number        Order_Number,
       rct.trx_number           Invoice_number,
       rctl.line_type           Invoice_Line_Type,
       msi.segment1             Item_Number,
       rctl.quantity_ordered    Quantity_ordered,
       rctl.quantity_invoiced   Quantity_invoiced,
       rctl.unit_standard_price Unit_standard_price,
       rctl.unit_selling_price  Unit_selling_price,
       RCTL.EXTENDED_AMOUNT     EXTENDED_AMOUNT
       --,rctl.*
  FROM apps.oe_order_headers_all      ooha,
       apps.ra_customer_trx_all       rct,
       APPS.RA_CUSTOMER_TRX_LINES_ALL RCTL,
       apps.mtl_system_items_b          msi
 WHERE 1=1
   AND to_char(ooha.order_number)      = rct.interface_header_attribute1
   AND rct.customer_trx_id             = rctl.customer_trx_id
   and RCTL.INVENTORY_ITEM_ID          = MSI.INVENTORY_ITEM_ID  
   AND msi.organization_id             = 65 -- Master
   and RCT.TRX_NUMBER                  = '284968'
   --and RCTL.INTERFACE_LINE_ATTRIBUTE2  = 'Test_Order_Type'
   ;

Query to find the scheduled concurrent programs

Scheduled concurrent programs

/* *******************************************************************
Modules Involved : Application Object Library (AOL)
Purpose : Finding out the Scheduled concurrent program details from a particular responsibility
Description      : Needs one parameter (responsibility_name)
****************************************************************** */

SELECT   fcr.REQUEST_ID
        ,NVL(fcr.DESCRIPTION,CPT.USER_CONCURRENT_PROGRAM_NAME) CONCURRENT_PROGRAM_NAME
        ,SUBSTR (fcr.ARGUMENT_TEXT, 1, 30) ARGUMENT_TEXT
        ,USR.USER_NAME REQUESTED_BY
        ,RESPT.RESPONSIBILITY_NAME
        ,fcrc.DATE1 START_DATE
        ,fcrc.DATE2 END_DATE
        ,DECODE(fcrc.class_type,
              'P', 'Periodic',
              'S', 'On Specific Days',
              'X', 'Advanced',
              fcrc.CLASS_TYPE
             ) SCHEDULE_TYPE
        ,CASE
         when fcrc.class_type = 'P' then
            'Repeat every ' ||
             substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
             DECODE(SUBSTR(fcrc.CLASS_INFO, INSTR(fcrc.CLASS_INFO, ':', 1, 1) + 1, 1),
                   'N', ' minutes',
                   'M', ' months',
                   'H', ' hours',
                   'D', ' days') ||
             decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
                  'S', ' from the start of the prior run',
                  'C', ' from the completion of the prior run')
         WHEN fcrc.CLASS_TYPE = 'S' THEN
              DECODE(SUBSTR(fcrc.CLASS_INFO, 32, 1), '1', 'Last day of month ') ||
              decode(sign(to_number(substr(fcrc.class_info, 33))),
                    '1',  'Days of week: ' ||
                    decode(substr(fcrc.class_info, 33, 1), '1', 'Su ') ||
                    decode(substr(fcrc.class_info, 34, 1), '1', 'Mo ') ||
                    decode(substr(fcrc.class_info, 35, 1), '1', 'Tu ') ||
                    decode(substr(fcrc.class_info, 36, 1), '1', 'We ') ||
                    decode(substr(fcrc.class_info, 37, 1), '1', 'Th ') ||
                    decode(substr(fcrc.class_info, 38, 1), '1', 'Fr ') ||
                    DECODE(SUBSTR(fcrc.CLASS_INFO, 39, 1), '1', 'Sa '))
        END SCHEDULE,
        fcrc.CLASS_INFO "Class Info"
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl cpt,
         fnd_responsibility_tl RESPT,
         fnd_conc_release_classes fcrc,
         fnd_user usr
   WHERE fcr.concurrent_program_id = cpt.concurrent_program_id
     AND fcr.program_application_id = cpt.application_id
     AND fcr.responsibility_id = respt.responsibility_id
     AND fcr.requested_by = usr.user_id
     AND fcr.STATUS_CODE IN ('Q','I')-- 'P'
     AND RESPT.RESPONSIBILITY_NAME = :RESPONSIBILITY_NAME
     AND fcr.RELEASE_CLASS_APP_ID = fcrc.APPLICATION_ID
     AND fcr.RELEASE_CLASS_ID = fcrc.RELEASE_CLASS_ID
     AND fcrc.CLASS_TYPE IS NOT NULL
    --AND fcr.concurrent_program_id = 36888
    --AND TRUNC (actual_start_date) >= TRUNC (SYSDATE) - 1
ORDER BY 1 DESC
;

Query to find the scheduled concurrent programs details

/* *******************************************************************
Modules Involved : Application Object Library (AOL)
Purpose : Finding out the Scheduled concurrent program details from a particular responsibility
Description      : Needs one parameter (responsibility_name)
****************************************************************** */

SELECT   fcr.REQUEST_ID
        ,NVL(fcr.DESCRIPTION,CPT.USER_CONCURRENT_PROGRAM_NAME) CONCURRENT_PROGRAM_NAME
        ,SUBSTR (fcr.ARGUMENT_TEXT, 1, 30) ARGUMENT_TEXT
        ,USR.USER_NAME REQUESTED_BY
        ,RESPT.RESPONSIBILITY_NAME
        ,fcrc.DATE1 START_DATE
        ,fcrc.DATE2 END_DATE
        ,DECODE(fcrc.class_type,
              'P', 'Periodic',
              'S', 'On Specific Days',
              'X', 'Advanced',
              fcrc.CLASS_TYPE
             ) SCHEDULE_TYPE
        ,CASE
         when fcrc.class_type = 'P' then
            'Repeat every ' ||
             substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
             DECODE(SUBSTR(fcrc.CLASS_INFO, INSTR(fcrc.CLASS_INFO, ':', 1, 1) + 1, 1),
                   'N', ' minutes',
                   'M', ' months',
                   'H', ' hours',
                   'D', ' days') ||
             decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
                  'S', ' from the start of the prior run',
                  'C', ' from the completion of the prior run')
         WHEN fcrc.CLASS_TYPE = 'S' THEN
              DECODE(SUBSTR(fcrc.CLASS_INFO, 32, 1), '1', 'Last day of month ') ||
              decode(sign(to_number(substr(fcrc.class_info, 33))),
                    '1',  'Days of week: ' ||
                    decode(substr(fcrc.class_info, 33, 1), '1', 'Su ') ||
                    decode(substr(fcrc.class_info, 34, 1), '1', 'Mo ') ||
                    decode(substr(fcrc.class_info, 35, 1), '1', 'Tu ') ||
                    decode(substr(fcrc.class_info, 36, 1), '1', 'We ') ||
                    decode(substr(fcrc.class_info, 37, 1), '1', 'Th ') ||
                    decode(substr(fcrc.class_info, 38, 1), '1', 'Fr ') ||
                    DECODE(SUBSTR(fcrc.CLASS_INFO, 39, 1), '1', 'Sa '))
        END SCHEDULE,
        fcrc.CLASS_INFO "Class Info"
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl cpt,
         fnd_responsibility_tl RESPT,
         fnd_conc_release_classes fcrc,
         fnd_user usr
   WHERE fcr.concurrent_program_id = cpt.concurrent_program_id
     AND fcr.program_application_id = cpt.application_id
     AND fcr.responsibility_id = respt.responsibility_id
     AND fcr.requested_by = usr.user_id
     AND fcr.STATUS_CODE IN ('Q','I')-- 'P'
     AND RESPT.RESPONSIBILITY_NAME = :RESPONSIBILITY_NAME
     AND fcr.RELEASE_CLASS_APP_ID = fcrc.APPLICATION_ID
     AND fcr.RELEASE_CLASS_ID = fcrc.RELEASE_CLASS_ID
     AND fcrc.CLASS_TYPE IS NOT NULL
    --AND fcr.concurrent_program_id = 36888
    --AND TRUNC (actual_start_date) >= TRUNC (SYSDATE) - 1
ORDER BY 1 DESC
;

Query to find the profile option values at all levels

The below query is used to get the profile option values at all levels (Site,Application,Responsibility, User).

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : To get the Profile option information at all levels(Site,Application,Responsibility, User).                
Description     : It also shows the created by and Last updated by infn.Need to pass the profile option name either system name (Ex:'ORG_ID') or
                  user defined name (Ex:'MO: Operating Unit')
 It provides
 1)The value assigned at site level
 2)The value assigned at Application level and Application Name If it is assigned to it.
 3)The value assigned at Responsibility level and Responsibility Name If it is assigned to any resp.
 4)The value assigned at User level and User Name If it is assigned to any user.
****************************************************************** */

select FPO.PROFILE_OPTION_ID, FPOT.PROFILE_OPTION_NAME PROFILE_SHORT_NAME
, fpot.user_profile_option_name profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name,10003,fr.responsibility_name,10004,fu.user_name) level_value
, FPOV.PROFILE_OPTION_VALUE PROFILE_VALUE
, FPOV.CREATION_DATE
,(select USER_NAME from FND_USER
   where USER_ID = FPOV.CREATED_BY) "Created By"
, FPOV.LAST_UPDATE_DATE
,(select USER_NAME from FND_USER
   WHERE user_id = fpov.last_updated_by) "Last Update By"
, fpov.*
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, FND_RESPONSIBILITY_TL FR
, FND_USER FU  
where 1=1 and (FPO.PROFILE_OPTION_NAME like NVL(:PROFILE_OPTION_NAME,FPO.PROFILE_OPTION_NAME)
and fpot.user_profile_option_name like nvl(:User_Profile_Option_Name,fpot.user_profile_option_name))
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
AND FR.RESPONSIBILITY_ID(+)=FPOV.LEVEL_VALUE
and FU.USER_ID(+)=FPOV.LEVEL_VALUE
order by 3
;

Query to find all the responsibility names from which we can run a particular concurrent program

The below query lists all the responsibilities from which we can run a particular concurrent program as well as lists all the concurrent programs which you can run from a particular responsibility.

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : To extract Concurrent Program, Responsibility and Request Group Info.
Description     : We can find the list of Responsibilities from which we can run the specified concurrent program.

This query need any one of the 2 parameters (Conc_Prog_Name, Responsibility_Name):
1)If you pass Conc_Prog_Name, You will get all the Responsibilities from which you can run that Program.
2)If you pass Responsibility_Name, You will get all the Concurrent Programs which you can run from that responsibility.
****************************************************************** */

SELECT fcpt.user_concurrent_program_name,
  frg.request_group_name,
  fcp.concurrent_program_name,
  FRT.RESPONSIBILITY_NAME,
  FAT.APPLICATION_NAME,
  FE.EXECUTABLE_NAME "EXECUTABLE CODE",
  FL.MEANING "EXECUTION METHOD",
  FE.EXECUTION_FILE_NAME
FROM
  APPS.FND_REQUEST_GROUP_UNITS FRGU,
  APPS.FND_CONCURRENT_PROGRAMS FCP,
  APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
  APPS.FND_REQUEST_GROUPS FRG,
  APPS.FND_EXECUTABLES FE,
  APPS.FND_RESPONSIBILITY FR,
  APPS.FND_RESPONSIBILITY_TL FRT,
  APPS.FND_APPLICATION_TL FAT,
  APPS.FND_LOOKUPS FL
WHERE 1                       = 1
AND fat.application_id        = frgu.application_id
AND frgu.request_unit_id      = fcp.concurrent_program_id
AND frgu.request_group_id     = frg.request_group_id
AND fe.executable_id          = fcp.executable_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frg.request_group_id      = fr.request_group_id
AND FR.RESPONSIBILITY_ID      = FRT.RESPONSIBILITY_ID
and FCPT.USER_CONCURRENT_PROGRAM_NAME = NVL(:CONC_PROG_NAME, FCPT.USER_CONCURRENT_PROGRAM_NAME)
and FRT.RESPONSIBILITY_NAME = NVL(:RESPONSIBILITY_NAME,FRT.RESPONSIBILITY_NAME)
and FL.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
and FE.EXECUTION_METHOD_CODE = FL.LOOKUP_CODE
;

Query to find all the responsibilities assigned to a user/ all the users who have access to a responsibility

The below query lists either all the responsibilities assigned to a particular user or all the users who have access to a particular responsibility.

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : User and Responsibilities mapping
Description     : We can find the list of Responsibilities assigned to a specific user and
 list of users have a specific responsibility access.

It needs any one of two parameters (User_Name, Responsibility_Name):
1)If you pass User_Name, You will get all the Responsibilities assigned to that user.
2)If you pass Responsibility_Name, You will get all the Users Who has access to that responsibility.
****************************************************************** */

SELECT FU.USER_ID,FU.USER_NAME, FU.EMAIL_ADDRESS, FR.RESPONSIBILITY_NAME,FURG.start_date, FURG.END_DATE,fu.employee_id
from APPS.FND_USER_RESP_GROUPS_DIRECT FURG
, FND_USER FU
, FND_RESPONSIBILITY_TL FR
where 1=1
and FU.USER_NAME = nvl(:User_Name,FU.USER_NAME)
and FURG.USER_ID = FU.USER_ID
and FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
and FR.RESPONSIBILITY_NAME = nvl(:RESPONSIBILITY_NAME,FR.RESPONSIBILITY_NAME)
AND nvl(FURG.END_DATE, sysdate+1)>sysdate
and FR.LANGUAGE = 'US'
ORDER BY 4
;