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

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

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

Script to Assign Responsibility in Oracle Applications

Below is the script that can be used to assign responsibility for a user.

DECLARE

   l_user_id   NUMBER;

   l_resp_id   NUMBER;

   l_appl_id   NUMBER;

   CURSOR cur_resp

   IS

      SELECT   responsibility_id, application_id -- INTO   l_resp_id, l_appl_id

        FROM   fnd_responsibility_vl

       WHERE   UPPER (responsibility_name) LIKE UPPER ('XXXXX');

BEGIN

   SELECT   user_id

     INTO   l_user_id

     FROM   fnd_user

    WHERE   user_name = 'UUUU';

   FOR rec_cur IN cur_resp

   LOOP

      fnd_user_resp_groups_api.insert_assignment (

         user_id                         => l_user_id,

         responsibility_id               => rec_cur.responsibility_id,

         responsibility_application_id   => rec_cur.application_id,

         security_group_id               => 0,

         start_date                      => SYSDATE - 1,

         end_date                        => NULL,

         description                     => NULL

      );

      COMMIT;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;

***Please leave a comment***

Difference Between Length and Vsize

Difference Between Length and Vsize

It appears that the Oracle Functions Length ( ) and Vsize () return the same results, but practically there is a difference in their usage

For Example :

Select Length ('Have a Nice Day') , Vsize ( 'Have a Nice Day') From Dual; Result : 15 , 15 

Well the result is same for both , but let’s see another example :

Select Length (9.234), Vsize ( 9.234) from Dual;
Result : 5 , 4


More Examples :
Select Length ( Sysdate) , Vsize ( Sysdate)  From Dual;
Result : 9 , 7  ( Current sysdate is '05-May-2013')

So we can see difference in the results, this is because  Vsize returns the number of bytes used to store the internal representation in any datatype, whereas Length returns the length of the passed string. Length function takes the character arguments (Char, Varchar2, Nchar, NVarchar2, Clob or Nclob) , so even if you pass a non-character argument, the Length Function converts the data type implicitly into a string and then returns its length.

***Please leave a comment***

How to convert number of seconds into Oracle date format ( Days Hours :Minutes : Seconds )

How to convert number of seconds into Oracle date format (Days Hours:Minutes:Seconds):

SELECT TO_CHAR(TO_DATE(ROUND('<variable>'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

SELECT ROUND('<variable>'/'86399') || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('<variable>','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Replace <variable> with number of seconds. ROUND function is used to round to nearest integer if you provide seconds in a decimal format

Example1: 

SELECT TO_CHAR(TO_DATE(ROUND('3000'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

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

00:50:00

3000 seconds is displayed in the hours, minutes and seconds format: read as 50 minutes

Example2: 

SELECT TO_CHAR(TO_DATE(ROUND('30007.345'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

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

08:20:07

30007.345 seconds is displayed in the hours, minutes and seconds format: read as 8 hours 20 minutes and 7 seconds

Example3: 
Above examples cover the seconds for a day. Lets take a large number of seconds which goes beyond a day.  Below query provides the output for 90740 seconds

SELECT ROUND('90740'/86399) || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('90740','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Output:

Days_Hours_Minutes_Format

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

1 Day(s) 01:12:21

90740 seconds is displayed in the days, hours, minutes and seconds format: read as 1 Day 1 hours 12 minutes and 21 seconds

Tip: 

This is very useful if you are trying to get the run time of a concurrent request in a readable format in Oracle Applications. Sample query below

SELECT request_id, (actual_completion_date - actual_start_date)*24*60*60 run_time_secs,

       TO_CHAR(TO_DATE((actual_completion_date - actual_start_date)*24*60*60, 'SSSSS'), 'HH24:MI:SS') run_time_Hours_Minutes_Format

FROM fnd_concurrent_requests

WHERE request_id  = 89110175;

Sample Output:

***Please leave a comment***