Query to find all the Parameters and their respective Value sets for a given Concurrent Program

Many times we would like to retrieve the list of the parameters and their respective Value sets that a concurrent Program has, the below Sql would help you :

SELECT    fcpl.user_concurrent_program_name "Concurrent Program Name",
                   fcp.concurrent_program_name "Short Name",
                   fdfcuv.column_seq_num "Column Seq Number",
                   fdfcuv.end_user_column_name "Parameter Name",
                   fdfcuv.form_left_prompt "Prompt",
                   fdfcuv.enabled_flag " Enabled Flag",
                   fdfcuv.required_flag "Required Flag",
                   fdfcuv.display_flag "Display Flag",
                   fdfcuv.flex_value_set_id "Value Set Id",
                   ffvs.flex_value_set_name "Value Set Name",
                   flv.meaning "Default Type",
                   fdfcuv.DEFAULT_VALUE "Default Value"
     FROM   fnd_concurrent_programs fcp,
                   fnd_concurrent_programs_tl fcpl,
                   fnd_descr_flex_col_usage_vl fdfcuv,
                   fnd_flex_value_sets ffvs,
                   fnd_lookup_values flv
    WHERE   fcp.concurrent_program_id = fcpl.concurrent_program_id
      AND      fcpl.user_concurrent_program_name = :conc_prg_name
      AND      fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
      AND      ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
      AND      flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
      AND      flv.lookup_code(+) = fdfcuv.default_type
      AND      fcpl.LANGUAGE = USERENV ('LANG')
      AND      flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY   fdfcuv.column_seq_num;

Query to find the Purchase Requisition and Purchase Order Details

Query to get the Purchase Requisition and Purchase Order Details

SELECT DISTINCT prha.creation_date req_creation_date
              , prha.segment1 req_no
              , prha.authorization_status
              , prha.apps_source_code /* POR means req came from iProcurment */
              , papf2.full_name req_prepaper
              , prla.line_num req_line_number
              , prla.item_description
              , prla.need_by_date
              , pha.segment1 po_number
           FROM po.po_requisition_headers_all prha
              , po.po_requisition_lines_all prla
              , po.po_line_locations_all plla
              , hr.per_all_people_f papf2
              , po.po_headers_all pha
              , po.po_lines_all pla
          WHERE prha.preparer_id = papf2.person_id
            AND prha.requisition_header_id = prla.requisition_header_id
            AND prla.line_location_id = plla.line_location_id(+)
            AND plla.po_line_id = pla.po_line_id(+)
            AND pla.po_header_id = pha.po_header_id(+)
            AND pha.segment1 = 'xxxxxx' /* Change this value to PO# per your needs*/
            AND prha.segment1 = 'xxxxxx' /* Change this value to Requisition# per your needs*/
            AND prha.authorization_status = 'APPROVED'
            AND prla.closed_code IS NULL
            AND (   prla.cancel_flag = 'N'
                       OR prla.cancel_flag IS NULL)
       ORDER BY 1 DESC;

POR in apps_source_code of Purchase Requisition Header denotes it is a iProc Requisition.


***Please leave a comment**

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***

Script to Assign the Profile option for a specific Level

 The below script is to enable profile option at USER level, for a given list of Users.

For setting a Profile Option Value from back end we need the Following three parameters;
    1. Name of Profile Option
    2. Value for the profile option which we want to set.
    3. The level at which we want to set the profile option value.

    Parameters Explanation:
    Profile_Option_Name  : Profile name you are setting
    Profile_Option_Value : Profile value you are setting
    Level                : Level that you're setting at. It could be 'SITE', 'APPL','RESP', or 'USER'
    Level_Value     : Level value that you are setting at,
                           e.g. user id for 'USER' level. This is not used at site level
    Level_Value_App_id   : Only used for 'RESP' level. Application_Id of the responsibility.

DECLARE

   l_result    BOOLEAN;

   l_profile   VARCHAR2 (150);

BEGIN

   SELECT   profile_option_name

     INTO   l_profile

     FROM   FND_PROFILE_OPTIONS_TL

    WHERE   user_profile_option_name = 'ABC'; 
/* An example of the value to be used in the above condition to match with user_profile_option_name. Use MO: Operating Unit if you are trying to set operating unit. If you use MO: Operating Unit you get ORG_ID in l_profile variable */

   FOR rec IN (SELECT   user_id

                 FROM   fnd_user

                WHERE   user_name IN ('XXXX', 'YYYY', 'ZZZZ'))

   LOOP

      l_result :=

         FND_PROFILE.SAVE (l_profile,

                           'Y',

                           'USER',

                           rec.user_id,

                           NULL,

                           NULL);

      IF l_result

      THEN

         DBMS_OUTPUT.put_line ('Success');

         COMMIT;

      ELSE

         DBMS_OUTPUT.put_line ('Error');

      END IF;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' Exception in the Main');

END;

/

***Please leave a comment***

Concatenated Sales Order Line Number - Oracle Applications OrderManagement

Concatenated Sales Order Line Number in Oracle Applications Order Management

Wondering there should be an easy way to get the concatenated sales order line number in Oracle Applications Order Management? Tired of writing a concatenation logic with NVL/DECODE conditions? Here is the amazing solution.

Syntax:

SELECT oe_order_misc_pub.get_concat_line_number (<line_id>) so_line_number

  FROM DUAL

Example:

SELECT oe_order_misc_pub.get_concat_line_number (22366203) so_line_number

  FROM DUAL 

If you follow the traditional way here how it looks like

SELECT oola.line_number

             || '.'

             || oola.shipment_number

             || DECODE (oola.option_number, NULL, NULL, '.' || oola.option_number)

             || DECODE (oola.component_number,

                   NULL, NULL,

                   DECODE (oola.option_number, NULL, '.') || '.' || oola.component_number)

             || DECODE (oola.service_number,

                        NULL,

                        NULL,

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

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

                        || '.'

                        || oola.service_number

                ) so_line_number

FROM oe_order_lines_all oola

WHERE oola.line_id = 22366203;

***Please leave a comment***