Query to get list of Users and Responsibilities got access to a Concurrent Program through Request Group in Oracle Applications

Query to get list of users and responsibilities that got access to a Concurrent Program through Request Group

SELECT   fcpt.user_concurrent_program_name, 
         frt.responsibility_name,
         SUBSTR (fu.description, 1, 49) user_name
    FROM apps.fnd_request_group_units frgu,
         apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_request_groups frg,
         apps.fnd_responsibility fr,
         apps.fnd_responsibility_tl frt,
         apps.fnd_user_resp_groups_all furg,
         apps.fnd_user fu
   WHERE fcpt.user_concurrent_program_name LIKE :p_user_concurrent_program_name
     AND frgu.unit_application_id = fcpt.application_id
     AND frgu.request_unit_id = fcpt.concurrent_program_id
     AND frg.application_id = frgu.application_id
     AND frg.request_group_id = frgu.request_group_id
     AND fr.request_group_id = frg.request_group_id
     AND fr.application_id = frg.application_id
     AND frt.responsibility_id = fr.responsibility_id
     AND frt.application_id = fr.application_id
     AND furg.responsibility_id = fr.responsibility_id
     AND furg.user_id = fu.user_id
     AND NVL (furg.end_date, SYSDATE) > SYSDATE - 1
     AND NVL (fu.end_date, SYSDATE) > SYSDATE - 1
     AND (SYSDATE BETWEEN fr.start_date AND fr.end_date OR fr.end_date IS NULL)
ORDER BY fcpt.user_concurrent_program_name, frgu.request_group_id


***Please leave a comment***

Query to get the Concurrent Requests of a Concurrent Program in Oracle Applications

Below the Query to get the Concurrent Requests of given Concurrent Program

    SELECT   fcpt.user_concurrent_program_name,

           fcr.request_id,

           fcr.parent_request_id,

           fu.description,

           frt.responsibility_name,

           phase_lookup.meaning,

           status_lookup.meaning,

           fcr.requested_start_date,

           fcr.actual_start_date,

           fcr.actual_completion_date,

           fcr.argument_text,

           fcr.logfile_name,

           fcr.outfile_name

    FROM   apps.fnd_concurrent_programs_tl fcpt,

           apps.fnd_concurrent_requests fcr,

           apps.fnd_lookups status_lookup,

           apps.fnd_lookups phase_lookup,

           apps.fnd_user fu,

           apps.fnd_responsibility_tl frt

   WHERE       fcpt.concurrent_program_id = fcr.concurrent_program_id

           AND fcpt.application_id = fcr.program_application_id

           AND fcr.status_code = status_lookup.lookup_code

           AND status_lookup.lookup_type = 'CP_STATUS_CODE'

           AND fcr.phase_code = phase_lookup.lookup_code

           AND phase_lookup.lookup_type = 'CP_PHASE_CODE'

           AND fcr.requested_by = fu.user_id

           AND fcr.responsibility_application_id = frt.application_id

           AND fcr.responsibility_id = frt.responsibility_id

           AND frt.language = USERENV('LANG')  

           AND fcpt.user_concurrent_program_name =

                 :P_User_Concurrent_Program_Name
           AND fcpt.language = USERENV('LANG')  

ORDER BY   actual_completion_date DESC

***Please leave a comment***

Oracle Applications Sales Order Holds Query (Holds Applied and Released)

Below the Query to get the holds information on a Sales Order at header level and line level.

SELECT   ooha.header_id,
         order_number,
         hold_lines.line_id,
         oe_order_misc_pub.get_concat_line_number (hold_lines.line_id) so_line_number, 
      /* Uncomment below to get the concatenated line number if you are running from non apps user */

        /*hold_lines.line_number || '.' || hold_lines.shipment_number

         || DECODE (hold_lines.option_number,

                    NULL, NULL,

                    '.' || hold_lines.option_number)

         || DECODE (

               hold_lines.component_number,

               NULL,

               NULL,

                  DECODE (hold_lines.option_number, NULL, '.')

               || '.'

               || hold_lines.component_number

            )

         || DECODE (

               hold_lines.service_number,

               NULL,

               NULL,

                  DECODE (hold_lines.option_number, NULL, '.')

               || DECODE (hold_lines.component_number, NULL, '.')

               || '.'

               || hold_lines.service_number

            )

            so_line_number, */
         ohd.name "Hold Name",

         holds.released_flag,
         holds.creation_date "Hold Applied Date",
         ohr.creation_date "Hold Released date",
         ohr.release_reason_code,
         ohr.release_comment
  FROM   oe_order_lines_all hold_lines,
         oe_order_headers_all ooha,
         oe_order_holds_all holds,
         oe_hold_sources_all ohsa,
         oe_hold_releases ohr,
         oe_hold_definitions ohd
 WHERE       holds.line_id = hold_lines.line_id(+)
         AND holds.header_id = hold_lines.header_id(+)
         AND holds.hold_release_id = ohr.hold_release_id(+)
         AND holds.hold_source_id = ohsa.hold_source_id
         AND ohsa.hold_id = ohd.hold_id
         AND holds.header_id = ooha.header_id
         AND ooha.order_number = :P_Order_Number

ORDER BY NVL(hold_lines.line_id,0), ohsa.hold_source_id

***Please leave a comment***

Links between HZ module and other ERP Modules (CSI,IB &OM )

Links between csi and hz

1. In Order Management table, customer account, bill to and ship to are stored as

OE_ORDER_LINES_ALL.sold_to_org_id = HZ_CUST_ACCOUNTS.cust_account_id

OE_ORDER_LINES_ALL.ship_to_org_id = HZ_CUST_SITE_USES_ALL.site_use_id

OE_ORDER_LINES_ALL.invoice_to_org_id = HZ_CUST_SITE_USES_ALL.site_use_id

2. In Installed Base table, party, customer account, bill to and ship to are stored as

CSI_ITEM_INSTANCES.owner_party_id = HZ_PARTIES.party_id

CSI_ITEM_INSTANCES.location_id = HZ_PARTY_SITES.party_site_id

CSI_I_PARTIES.party_id = HZ_PARTIES.party_id

CSI_IP_ACCOUNTS.party_account_id = HZ_CUST_ACCOUNTS.cust_account_id

CSI_IP_ACCOUNTS.ship_to_address = HZ_CUST_SITE_USES_ALL.site_use_id

CSI_IP_ACCOUNTS.bill_to_address = HZ_CUST_SITE_USES_ALL.site_use_id

3. The mapping between TCA, OM and IB tables are

CSI_ITEM_INSTANCES.owner_party_id =

                                         (SELECT party_id
                                             FROM hz_cust_accounts
                                           WHERE cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id) ;



CSI_I_PARTIES.party_id =

                                        (SELECT party_id
                                            FROM hz_cust_accounts
                                          WHERE cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id)

CSI_IP_ACCOUNTS.party_account_id = OE_ORDER_LINES_ALL.sold_to_org_id


CSI_IP_ACCOUNTS.ship_to_address = OE_ORDER_LINES_ALL.ship_to_org_id

CSI_IP_ACCOUNTS.bill_to_address = OE_ORDER_LINES_ALL.invoice_to_org_id

OE_ORDER_LINES_ALL.ship_to_org_id =

                                       (SELECT site_use_id
                                          FROM hz_cust_site_uses_all hczu,
                                                      hz_cust_acct_sites_all hcas,
                                                      hz_cust_accounts hca
                                        WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
                                             AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
                                             AND hczu.site_use_code = 'SHIP_TO');


OE_ORDER_LINES_ALL.invoice_to_org_id =

                                      (SELECT site_use_id
                                          FROM hz_cust_site_uses_all hczu,
                                                      hz_cust_acct_sites_all hcas,
                                                      hz_cust_accounts hca
                                       WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
                                            AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
                                            AND hczu.site_use_code = 'BILL_TO');  

***Please leave a comment***

How to identify sessions from a Database Object

Oracle Session (SID) Details on a database object:
=====================================

SELECT   sid, serial#, vs.*
  FROM   v$session vs
 WHERE   vs.sid IN (SELECT   SID
                   FROM   v$access
                  WHERE   object = <object_name>)

***Please leave a comment***

How to check if a product is implemented or installed and Patch Level in Oracle Applications

How to check if a product is implemented / installed in Oracle Applications

SELECT   fa.application_short_name,

         fat.application_name,

         fpi.patch_level,

         DECODE (fpi.status,

                 'I', 'Installed',

                 'S', 'Shared',

                 'N', 'Inactive',

                 fpi.status)

            Status,

         fpi.db_status

  FROM   fnd_product_installations fpi, 

         fnd_application fa,

         fnd_application_tl fat

 WHERE   fa.application_id = fpi.application_id 

   --AND   fa.application_short_name LIKE '<APPL SHORT NAME>' /* Replace Application Short Name with a product to restrict the query for a specific product. Ex: AR for Receivables */

   AND   fa.application_id = fat.application_id

   AND   fat.language = USERENV ('LANG')

 ORDER BY fa.application_short_name

Details on Status Column from the Query:

Installed - Implemented

Shared - Not fully Implemented

Inactive --> Not fully implemented and not used
L --> Custom 

***Please leave a comment***