Oracle Apps Technical Stuff: AOL And Generic SQLs

Showing posts with label AOL And Generic SQLs. Show all posts

Thursday, June 23, 2022

FA:OM:SQL: To get the RMA Order receipt details along with the Serial numbers

WITH sn_tbl as ( SELECT dha.header_id
                            , dla.line_id
                            , dfla.fulfill_line_id
                            , dla.display_line_number | | '.' | | dfla.fulfill_line_number line_num
                            , LISTAGG(iut.serial_number, ',' on overflow truncate) WITHIN GROUP (
                                         ORDER BY iut.serial_number
                                        ) Serial_numbers
                         FROM fusion.inv_material_txns imt
                            , fusion.inv_org_parameters iop
                            , fusion.egp_system_items_b esi
                            , fusion.inv_transaction_types_vl ittv
                            , fusion.fnd_lookup_values_vl flva
                            , fusion.inv_txn_source_types_vl itst
                            , fusion.inv_unit_transactions iut
                            , fusion.rcv_shipment_headers rsh
                            , fusion.rcv_shipment_lines rsl
                            , fusion.rcv_transactions rt
                            , fusion.doo_fulfill_line_details dfld
                            , fusion.doo_fulfill_lines_all dfla
                            , fusion.doo_lines_all dla
                            , fusion.doo_headers_all dha
                        WHERE imt.organization_id = iop.organization_id
                          AND imt.organization_id = esi.organization_id
                          AND imt.inventory_item_id = esi.inventory_item_id
                          AND imt.transaction_type_id = ittv.transaction_type_id
                          AND ittv.transaction_action_id = flva.lookup_code
                          AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
                          AND flva.enabled_flag = 'Y'
                          AND ittv.transaction_source_type_id = itst.transaction_source_type_id
                          AND imt.transaction_id = iut.transaction_id(+)
                          AND ittv.transaction_type_name = 'RMA Receipt'
                          AND itst.transaction_source_type_name = 'RMA'
                          AND rsh.shipment_header_id = rsl.shipment_header_id
                          AND rsh.shipment_header_id = rt.shipment_header_id
                          AND rsl.shipment_header_id = rt.shipment_header_id
                          AND rsl.shipment_line_id = rt.shipment_line_id
                          AND rt.transaction_id = imt.rcv_transaction_id
                          AND imt.rcv_transaction_id = dfld.rma_receipt_transaction_id
                          AND dfld.fulfill_line_id = dfla.fulfill_line_id
                          AND dfla.line_id = dla.line_id
                          AND dla.header_id = dha.header_id
                          AND dfla.header_id = dha.header_id
                          AND dha.submitted_flag = 'Y'
                     GROUP BY dla.display_line_number | | '.' | | dfla.fulfill_line_number
                            , dha.header_id
                            , dla.line_id
                            , dfla.fulfill_line_id)
   SELECT hauft.NAME BusinessUnit
        , a.order_type_code
        , o.organization_code | | ' ' | | flv.meaning warehouse
        , a.order_number
        , f.source_line_number
        , i.item_number
        , b.ordered_qty l_qty
        , f.rma_delivered_qty f_del_qty
        , f.shipped_qty s_qty
        , f.CANCELED_QTY
        , initcap(f.status_code) f_status_code
        , decode(f.canceled_flag, 'Y', 'Line Canceled', 'Not Canceled') Line_cancel_status
        , a.creation_date
        , i.description
        , A.SOURCE_ORDER_SYSTEM | | ':' | | A.SOURCE_ORDER_ID ORDER_KEY
        , a.ordered_date
        , DECODE(i.serial_number_control_code, 1, 'No', 'Yes') serial_number_control_code
        , sn_tbl.Serial_numbers
     FROM fusion.doo_headers_all a
        , fusion.doo_lines_All b
        , fusion.doo_fulfill_lines_all f
        , fusion.egp_system_items_v i
        , fusion.inv_org_parameters o
        , fusion.fnd_lookup_values_vl flv
        , fusion.hr_org_unit_classifications_f houcf
        , fusion.hr_all_organization_units_f haouf
        , fusion.hr_organization_units_f_tl hauft
        , sn_tbl
    WHERE 1 = 1
      AND a.submitted_flag = 'Y'
      AND a.header_id = b.header_id
      AND b.header_id = f.header_id
      AND b.line_id = f.line_id
      AND a.order_type_code = flv.lookup_code
      AND flv.lookup_type = 'ORA_DOO_ORDER_TYPES'
      AND flv.enabled_flag = 'Y'
      AND f.fulfill_org_id = i.organization_id
      AND f.fulfill_org_id = o.organization_id
      AND f.inventory_item_id = i.inventory_item_id
      AND haouf.organization_id = houcf.organization_id
      AND haouf.organization_id = hauft.organization_id
      AND hauft.organization_id = a.org_id
      AND hauft.language = 'US'
      AND sysdate BETWEEN houcf.effective_start_date AND NVL(houcf.effective_end_date, SYSDATE + 1)
      AND sysdate BETWEEN haouf.effective_start_date AND NVL(haouf.effective_end_date, SYSDATE + 1)
      AND sysdate BETWEEN hauft.effective_start_date AND NVL(hauft.effective_end_date, SYSDATE + 1)
      AND hauft.effective_start_date = haouf.effective_start_date
      AND hauft.effective_end_date = haouf.effective_end_date
      AND houcf.classification_code = 'FUN_BUSINESS_UNIT'
      AND f.header_id = sn_tbl.header_id
      AND f.line_id = sn_tbl.line_id
      and f.fulfill_line_id = sn_tbl.fulfill_line_id
      AND a.order_number = :p_order_number
ORDER BY a.creation_date
        , LPAD(f.source_line_number, 2)

Tuesday, May 24, 2022

Fusion: SQL: Query to Extract the Business Units and its related Inventory Organizations, Business Units and Costing Organizations

SELECT hou.organization_id
       , hou.name organization_name
       , iop.organization_code organization_code
       , lgr.ledger_id set_of_book_id
       , lgr.chart_of_accounts_id chart_of_account_id
       , lgr.currency_code
       , lgr.period_set_name
       , Decode(hoi.status, 'A', 'Y', 'N') inv_enabled_flag
       , bu.bu_name business_unit_name
       , iop.business_unit_id business_unit_id
       , iop.legal_entity_id legal_entity
       , hou.type organization_type
FROM hr_all_organization_units_x hou
       , hr_org_unit_classifications_x hoi
       , inv_org_parameters iop
       , gl_ledgers lgr
       , fun_all_business_units_v bu
WHERE hou.organization_id = hoi.organization_id
   AND hou.organization_id = iop.organization_id
   AND hoi.classification_code = 'INV'
   AND bu.primary_ledger_id = lgr.ledger_id(+)
   AND lgr.object_type_code(+) = 'L'
   AND NVL(lgr.complete_flag, 'Y') = 'Y'
   AND bu.bu_id(+) = iop.business_unit_id;

SQL : Get Business Unit Details

===================================

SELECT hauft.NAME BusinessUnit, hauft.organization_id
FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date    AND houcf.effective_end_date
   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 = 'FUN_BUSINESS_UNIT'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
ORDER BY hauft.NAME ASC nulls first ;

SQL : Get Costing Organization Details

===================================

SELECT hauft.NAME BusinessUnit
FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   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 = 'CST'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
ORDER BY hauft.NAME ASC nulls first;

Wednesday, April 13, 2022

FA:SCM:CST: SQL to Get Item Standard Cost

SELECT houft.name cost_organization_name
       , esi.item_number
       , csc.total_cost
       , uom.uom_code
       , cvu.val_unit_code
       , cvs.val_structure_code
       , cvs.val_structure_type_code
       , ccb.cost_book_code
FROM cst_std_costs csc
       , cst_val_units_b cvu
       , cst_val_structures_b cvs
       , egp_system_items_vl esi
       , cst_cost_org_parameters ccop
       , hr_organization_units_f_tl houft
       , cst_cost_books_b ccb
       , inv_units_of_measure_vl uom
WHERE csc.cost_book_id = ccb.cost_book_id
   AND csc.cost_org_id = houft.organization_id
   AND ccop.cost_org_id = houft.organization_id
   AND cvu.val_structure_id = cvs.val_structure_id
   AND csc.val_unit_id = cvu.val_unit_id
   AND esi.inventory_item_id = csc.inventory_item_id
   AND esi.organization_id = ccop.master_organization_id
   AND houft.LANGUAGE = Userenv('LANG')
   AND csc.uom_code = uom.uom_code
   AND csc.status_code = 'PUBLISHED'
   AND esi.item_number = :p_item_number
   AND houft.name = :p_cost_org_name
   AND trunc(nvl(csc.effective_end_date,sysdate+1)) > trunc(sysdate);

Tuesday, May 2, 2017

Script to Submit the Request Set using API in Oracle

Using below script One can submit the request set. But to execute the below script successfully define the Concurrent program for the following script and the either submit it through SRS window or using API FND_REQUEST.SUBMIT_REQUEST.

CREATE OR REPLACE PROCEDURE apps.xx_fnd_submit(errbuf OUT VARCHAR2
, retcode OUT VARCHAR2)
AS
/*+==========================================================================
| Concurrent Processing Sample Code
|
| FILE:
| fnd_submit_test.pls
|
| REVISION:
| $Id$
|
| DESCRIPTION:
| FND_SUBMIT test procedure and sample code
| Creates a procedure called fnd_submit_test that can be registered
| and run as a concurrent program.
| This procedure will use the FND_SUBMIT API to submit a request set.
| (Function Security Reports - This request set should be seeded, if
| it is not available the values in the script may need to be changed.)
| The procedure will then place itself in a Paused status until the
| request set completes.
|
| INSTRUCTIONS:
|
| 1. Install this procedure in the APPS schema.
|
| 2. Register the procedure as a concurrent program
|
|
+==========================================================================*/
success BOOLEAN;
req_id NUMBER;
req_data VARCHAR2(10);
srs_failed EXCEPTION;
submitprog_failed EXCEPTION;
submitset_failed EXCEPTION;
BEGIN
-- Use FND_FILE to output messages at each stage
DBMS_OUTPUT.put_line( 'Starting test...');

-- Read fnd_conc_global.request_data, if available then we have been
-- reawakened after the request set has completed.
-- If so, exit.
req_data := fnd_conc_global.request_data;
DBMS_OUTPUT.put_line( 'req_data : '
|| req_data);

IF (req_data IS NOT NULL)
THEN
errbuf := 'Done!';
retcode := 0;
RETURN;
END IF;

-- Step 1 - call set_request_set
DBMS_OUTPUT.put_line( 'Calling set_request_set...');

success :=
fnd_submit.set_request_set('XX'
, 'XX_CRE_CONT');

IF (NOT success)
THEN
RAISE srs_failed;
END IF;

DBMS_OUTPUT.put_line( 'Calling submit program first time...');

-- Step 2 - call submit program for each program in the set
success :=
fnd_submit.submit_program('XX'
, 'XX_OM_CREATE_CONTRACT'
, 'STAGE10');

IF (NOT success)
THEN
RAISE submitprog_failed;
END IF;

DBMS_OUTPUT.put_line( 'Calling submit program second time...');

success :=
fnd_submit.submit_program('OKS'
, 'OKSREPROC'
, 'OKCCONTORD'
, ''
, '');

IF (NOT success)
THEN
RAISE submitprog_failed;
END IF;

-- Step 3 - call submit_set
DBMS_OUTPUT.put_line( 'Calling submit_set...');

req_id :=
fnd_submit.submit_set(NULL
, TRUE);

IF (req_id = 0)
THEN
RAISE submitset_failed;
END IF;

DBMS_OUTPUT.put_line( 'Finished. with request_id : '
|| req_id);

-- Set conc_status to PAUSED, set request_data to 1 and exit
fnd_conc_global.set_req_globals(conc_status => 'PAUSED'
, request_data => '1');

errbuf :=
'Request set submitted. id = '
|| req_id;
retcode := 0;
COMMIT;
EXCEPTION
WHEN srs_failed
THEN
errbuf :=
'Call to set_request_set failed: '
|| fnd_message.get;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
WHEN submitprog_failed
THEN
errbuf :=
'Call to submit_program failed: '
|| fnd_message.get;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
WHEN submitset_failed
THEN
errbuf :=
'Call to submit_set failed: '
|| fnd_message.get;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
WHEN OTHERS
THEN
errbuf :=
'Request set submission failed - unknown error: '
|| SQLERRM;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
END;
/

Friday, April 28, 2017

How to Pass Parameters from One Program to the Next Program in a Request Set

How to Pass Parameters from One Program to the Next Program in a Request Set (Doc ID 239808.1)

APPLIES TO:

Oracle Application Object Library - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

GOAL

How to pass parameters from one program to the next program in a request set

SOLUTION

1. Query up your request set.

2. Select the 'Define Stages' button.

3. Select 'Requests' button for the stage.

4. Select 'Parameters' button for the report.

5. Enter a short name for the 'Shared Parameter' field.

5. Select the name for the parameter to be shared in the other reports in the request set.

For an example to see how this is done, query up the seeded report set: Function Security Reports

Friday, January 9, 2015

Script to Create New FND_USER and Assign Responsilities and also Update/Create Profile For ICX_FORMS_LAUNCHER

------------------------------------------------------------------------------------------------
-- Replace the Value for l_user_name for creating multiple users and assign the responsibilities
------------------------------------------------------------------------------------------------

DECLARE
   l_user_name                VARCHAR2 (100);
   l_user_password            VARCHAR2 (100) := 'welcome1';
   l_user_start_date          DATE := SYSDATE;
   l_user_end_date            VARCHAR2 (100) := NULL;
   l_password_date            VARCHAR2 (100) := SYSDATE;
   l_password_lifespan_days   NUMBER;
   l_person_id                NUMBER;
   l_email_address            VARCHAR2 (100);
   l_count                    NUMBER;
   l_change_password          BOOLEAN;
   l_user_id                  NUMBER;

   PROCEDURE assign_responsibility (p_user_name IN VARCHAR2)
   IS
      CURSOR c_resp
      IS
         SELECT fav.application_short_name,
                fav.application_name,
                frv.responsibility_key,
                frv.responsibility_name
           FROM fnd_application_vl fav, fnd_responsibility_vl frv
          WHERE     frv.application_id = fav.application_id
                AND frv.responsibility_name IN ('US Receivables Super User',
                                                'US Service Contracts Super User',
                                                'US Order Management Super User',
                                                'Installed Base User');

      l_appl_short_name   fnd_application_vl.application_short_name%TYPE;
      l_resp_name         fnd_responsibility_vl.responsibility_name%TYPE;
      l_resp_key          fnd_responsibility_vl.responsibility_key%TYPE;
      l_description       VARCHAR2 (100)
         := 'Adding Responsibility to the user using script';
      l_count             NUMBER;
   BEGIN
      FOR resp_rec IN c_resp
      LOOP
         SELECT COUNT (1)
           INTO l_count
           FROM fnd_user fuser,
                per_people_f per,
                fnd_user_resp_groups furg,
                fnd_responsibility_tl frt
          WHERE     fuser.employee_id = per.person_id
                AND fuser.user_id = furg.user_id
                AND (   TO_CHAR (fuser.end_date) IS NULL
                     OR fuser.end_date > SYSDATE)
                AND frt.responsibility_id = furg.responsibility_id
                AND (   TO_CHAR (furg.end_date) IS NULL
                     OR furg.end_date > SYSDATE)
                AND frt.language = 'US'
                AND fuser.user_name = p_user_name
                AND frt.responsibility_name = resp_rec.responsibility_name;

         IF l_count = 0
         THEN
            l_appl_short_name := resp_rec.application_short_name;
            l_resp_key := resp_rec.responsibility_key;
            l_resp_name := resp_rec.responsibility_name;

            BEGIN
               fnd_user_pkg.addresp (username         => p_user_name,
                                     resp_app         => l_appl_short_name,
                                     resp_key         => l_resp_key,
                                     security_group   => 'STANDARD',
                                     description      => l_description,
                                     start_date       => SYSDATE,
                                     end_date         => NULL);
               COMMIT;
               DBMS_OUTPUT.put_line (
                     'The responsibility : '
                  || l_resp_name
                  || ' is added to the user '
                  || p_user_name);
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (
                        'Responsibility '
                     || l_resp_name
                     || ' IS NOT added to the user '
                     || p_user_name
                     || ' due to '
                     || SQLCODE
                     || '; '
                     || SUBSTR (SQLERRM, 1, 250));
                  DBMS_OUTPUT.put_line ('');
                  ROLLBACK;
            END;
         ELSE
            DBMS_OUTPUT.put_line (
                  'Responsibility : '
               || resp_rec.responsibility_name
               || ' Already assigned');
         END IF;
      END LOOP;
   END;

   PROCEDURE update_profile (p_user_id IN NUMBER)
   IS
      l_result   BOOLEAN;
   BEGIN
      l_result :=
         fnd_profile.save (
            x_name                 => 'ICX_FORMS_LAUNCHER',
            x_value                => 'https://www.google.com',
            x_level_name           => 'USER',
            x_level_value          => p_user_id,
            x_level_value_app_id   => NULL,
            x_level_value2         => NULL);

      IF l_result
      THEN
         DBMS_OUTPUT.put_line ('Profile Updated');
      ELSE
         DBMS_OUTPUT.put_line ('Profile Not Updated');
      END IF;

      COMMIT;
   END;
BEGIN
   fnd_global.apps_initialize (17959, 20420, 1);

   FOR i IN 1 .. 200
   LOOP
      l_user_name := 'TESTUSER' || i;
      fnd_user_pkg.createuser (
         x_user_name                => l_user_name,
         x_owner                    => NULL,
         x_unencrypted_password     => l_user_password,
         x_start_date               => l_user_start_date,
         x_end_date                 => l_user_end_date,
         x_password_date            => l_password_date,
         x_password_lifespan_days   => l_password_lifespan_days,
         x_employee_id              => 12345,
         x_email_address            => 'xxxx@gmail.com');

      COMMIT;

      SELECT user_id
        INTO l_user_id
        FROM fnd_user
       WHERE user_name = l_user_name;

      IF l_user_id IS NOT NULL AND l_user_id > 0
      THEN
         assign_responsibility (l_user_name);
         update_profile (l_user_id);

         BEGIN
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_NORMAL_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name, 'CSI_ADMIN_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_READ_ONLY_USER');
            COMMIT;
            DBMS_OUTPUT.put_line ('Assign IB Roles Successfully!!!');
         END;

         l_change_password :=
            fnd_user_pkg.changepassword (l_user_name, 'welcome123');

         IF l_change_password
         THEN
            DBMS_OUTPUT.put_line ('Password Changed successfully!!!');
            COMMIT;
         ELSE
            DBMS_OUTPUT.put_line ('Unable to Change Password!!!');
            ROLLBACK;
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (
               'User : '
            || l_user_name
            || ' Not found, hence unable to assign the responsibilities');
      END IF;

      l_user_name := NULL;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

Thursday, December 4, 2014

SQL query to get the details of Scheduled Concurrent Requests in Oracle applications

SELECT r.request_id
, p.user_concurrent_program_name
|| NVL2 (r.description
, ' ('
|| r.description
|| ')'
, NULL)
conc_prog
, s.user_name requestor
, r.argument_text arguments
, r.requested_start_date next_run
, r.last_update_date last_run
, r.hold_flag on_hold
, r.increment_dates
, DECODE (c.class_type, 'P', 'Periodic', 'S', 'On Specific Days', 'X', 'Advanced', c.class_type) schedule_type
, CASE
WHEN c.class_type = 'P'
THEN
'Repeat every '
|| SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
|| DECODE (SUBSTR (c.class_info
, INSTR (c.class_info
, ':'
, 1
, 1)
+ 1
, 1)
, 'N', ' minutes'
, 'M', ' months'
, 'H', ' hours'
, 'D', ' days')
|| DECODE (SUBSTR (c.class_info
, INSTR (c.class_info
, ':'
, 1
, 2)
+ 1
, 1)
, 'S', ' from the start of the prior run'
, 'C', ' from the completion of the prior run')
WHEN c.class_type = 'S'
THEN
NVL2 (dates.dates
, 'Dates: '
|| dates.dates
|| '. '
, NULL)
|| DECODE (SUBSTR (c.class_info, 32, 1), '1', 'Last day of month ')
|| DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33)))
, '1', 'Days of week: '
|| DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ')
|| DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ')
|| DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ')
|| DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ')
|| DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ')
|| DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ')
|| DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa '))
END
AS schedule
, c.date1 start_date
, c.date2 end_date
, c.class_info
FROM apps.fnd_concurrent_requests r
, apps.fnd_conc_release_classes c
, apps.fnd_concurrent_programs_tl p
, apps.fnd_user s
, (WITH date_schedules
AS (SELECT release_class_id, RANK () OVER (PARTITION BY release_class_id ORDER BY s) a, s
FROM (SELECT c.class_info
, l
, c.release_class_id
, DECODE (SUBSTR (c.class_info, l, 1), '1', TO_CHAR (l)) s
FROM ( SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL <= 31)
, apps.fnd_conc_release_classes c
WHERE c.class_type = 'S'
AND INSTR (SUBSTR (c.class_info, 1, 31), '1') > 0)
WHERE s IS NOT NULL)
SELECT release_class_id, SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates
FROM apps.date_schedules
START WITH a = 1
CONNECT BY NOCYCLE PRIOR a = a - 1
GROUP BY release_class_id) dates
WHERE r.phase_code = 'P'
AND c.application_id = r.release_class_app_id
AND c.release_class_id = r.release_class_id
AND NVL (c.date2, SYSDATE + 1) > SYSDATE
AND c.class_type IS NOT NULL
AND p.concurrent_program_id = r.concurrent_program_id
AND p.language = 'US'
AND dates.release_class_id(+) = r.release_class_id
AND r.requested_by = s.user_id
ORDER BY conc_prog, on_hold, next_run;

Saturday, August 23, 2014

Script to Compile Invalid Objects in a Schema

This procedure recompile invalid objects in a given schema or all invalid objects in the database.

Parameter :
schema     (IN) - Schema in which to recompile invalid objects If NULL, all invalid objects in the database are recompiled.

BEGIN
UTL_RECOMP.recomp_serial(p_schema_name);
END;

DESCRIPTION:
This procedure is the main driver that recompiles invalid objects
in the database (or in a given schema) in parallel in dependency
order. It uses information in dependency$ to order recompilation
of dependents after parents.

NOTES:
The parallel recompile exploits multiple CPUs to reduce the time taken to recompile invalid objects. However, please note that recompilation writes significant amounts of data to system tables,
so the disk system may be a bottleneck and prevent significant speedups.

   PARAMETERS:
Threads    (IN) - Number of recompile threads to run in parallel If NULL, 0, or negative, RECOMP_PARALLEL computes a default degree of parallelism as the product of Oracle parameters "cpu_count" and "parallel_threads_per_cpu". On a Real Application Clusters installation, the degree of parallelism is the sum of individual settings on each node in the cluster.
Schema     (IN) - Schema in which to recompile invalid objects If NULL, all invalid objects in the database
                         are recompiled.
Flags      (IN) - Option flags supported (as described above).


BEGIN
UTL_RECOMP.recomp_parallel(p_schema_name);
END;

Tuesday, August 19, 2014

How to use comma separated string build dynamically and pass to IN clause of select statement

In some cases, we get a comma separated string as output (say from another select statement) that we would need to pass to the IN clause of a select statement.

This article explains how to achieve that using regexp_substr (DB >=10g).

For example, assume a select statement returns the following

'SMITH,ALLEN,WARD,JONES'

Now, we would need to pass this to another select statement as IN clause and get the output.

SQL> select * from emp where ename in ('SMITH,ALLEN,WARD,JONES');

no rows selected

Well, this is not our expected output. We expect the query to return 4 rows.

This can be achieved by splitting the comma separated string to individual strings and pass it to the IN clause.

Oracle provides regexp_substr function, which comes handy for this scenario.

First, we will form a query, that splits this comma separated string and gives the individual strings as rows.

SQL> select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
2 connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

REGEXP_SUBSTR('SMITH,A
----------------------
SMITH
ALLEN
WARD
JONES

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.

We can pass this query to our select statement to get the desired output.

SQL> select * from emp where ename in (
2 select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
3 connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

Now, the query returns what we expected.

Monday, July 7, 2014

Function to convert number to Word

FUNCTION to_word_i( amount IN NUMBER)
RETURN VARCHAR2
AS
v_length INTEGER := 0;
v_num2 VARCHAR2(50) := NULL;
v_amount VARCHAR2(50) := TO_CHAR( TRUNC( amount));
v_word VARCHAR2(4000) := NULL;
v_word1 VARCHAR2(4000) := NULL;

TYPE myarray IS TABLE OF VARCHAR2(255);

v_str myarray
:= myarray(
' thousand '
, ' lakh '
, ' crore '
, ' arab '
, ' kharab '
, ' shankh ');
BEGIN
IF TO_CHAR( amount) LIKE '%.%'
THEN
IF SUBSTR( amount, INSTR( amount, '.') + 1) > 0
THEN
v_num2 := SUBSTR( amount, INSTR( amount, '.') + 1);

IF LENGTH( v_num2) < 2
THEN
v_num2 := v_num2 * 10;
END IF;

v_length := LENGTH( v_num2);
v_word1 :=
' AND '
|| (TO_CHAR( TO_DATE( SUBSTR( v_num2, LENGTH( v_num2) - 1, 2), 'j'), 'Jsp'))
|| ' paise ';
END IF;
END IF;

IF v_amount = '0'
THEN
v_word := 'zero';
ELSE
IF (SUBSTR( v_amount, LENGTH( v_amount) - 2, 3) <> 0)
THEN
v_word :=
TO_CHAR( TO_DATE( SUBSTR( v_amount, LENGTH( v_amount) - 2, 3), 'J'), 'Jsp')
|| v_word;
END IF;

v_amount := SUBSTR( v_amount, 1, LENGTH( v_amount) - 3);

FOR i IN 1 .. v_str.COUNT
LOOP
EXIT WHEN v_amount IS NULL;

IF (SUBSTR( v_amount, LENGTH( v_amount) - 1, 2) <> 0)
THEN
v_word :=
TO_CHAR( TO_DATE( SUBSTR( v_amount, LENGTH( v_amount) - 1, 2), 'J'), 'Jsp')
|| v_str( i)
|| v_word;
END IF;

v_amount := SUBSTR( v_amount, 1, LENGTH( v_amount) - 2);
END LOOP;
END IF;

v_word :=
v_word
|| ' '
|| v_word1
|| ' only ';
v_word := REPLACE( RTRIM( v_word), ' ', ' ');
v_word := REPLACE( RTRIM( v_word), '-', ' ');
RETURN INITCAP( v_word);
END to_word_i;

Friday, March 21, 2014

Script to Set the Global Info (FND_GLOBAL.APPS_INITIALIZE)

PROCEDURE set_global_info(
p_responsibility_id IN NUMBER := fnd_api.g_miss_num
, p_user_id IN NUMBER := fnd_api.g_miss_num
, p_resp_appl_id IN NUMBER := 275
, p_msg_count OUT NUMBER
, p_msg_data OUT VARCHAR2
, p_return_status OUT VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Set_Global_Info';
l_value_conversion_error BOOLEAN := FALSE;
l_return_status VARCHAR2(1);
l_dummy VARCHAR2(1);
l_temp_num NUMBER;

CURSOR l_resp_csr
IS
SELECT 'x'
FROM fnd_responsibility
WHERE responsibility_id = p_responsibility_id
AND application_id = p_resp_appl_id;

CURSOR l_user_csr
IS
SELECT 'x'
FROM fnd_user
WHERE user_id = p_user_id;

l_resp_csr_rec l_resp_csr%ROWTYPE;
BEGIN
IF p_responsibility_id IS NULL
OR p_responsibility_id = fnd_api.g_miss_num
THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
THEN
-- Define the Custom message as XX_RESP_ID_REQD with
-- Description as "A valid responsibility is required."
fnd_message.set_name('XX_APPL_NAME', 'XX_RESP_ID_REQD');
fnd_msg_pub.ADD;
END IF;

RAISE fnd_api.g_exc_error;
END IF;

OPEN l_resp_csr;

FETCH l_resp_csr
INTO l_dummy;

IF l_resp_csr%NOTFOUND
THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
THEN
-- Define the Custom message as XX_RESP_ID_INVALID with
-- Description as "The specified responsibility is invalid."
fnd_message.set_name('XX_APPL_NAME', 'XX_RESP_ID_INVALID');
fnd_msg_pub.ADD;
END IF;

CLOSE l_resp_csr;

RAISE fnd_api.g_exc_error;
ELSE
CLOSE l_resp_csr;
END IF;

IF p_user_id IS NULL
OR p_user_id = fnd_api.g_miss_num
THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
THEN
-- Define the Custom message as XX_USER_ID_REQD with
-- Description as "A valid user ID is required."
fnd_message.set_name('XX_APPL_NAME', 'XX_USER_ID_REQD');
fnd_msg_pub.ADD;
END IF;

RAISE fnd_api.g_exc_error;
END IF;

OPEN l_user_csr;

FETCH l_user_csr
INTO l_dummy;

IF l_user_csr%NOTFOUND
THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
THEN
-- Define the Custom message as XX_USER_ID_REQD with
-- Description as "The specified user ID is invalid."
fnd_message.set_name('XX_APPL_NAME', 'XX_USER_ID_INVALID');
fnd_msg_pub.ADD;
END IF;

CLOSE l_user_csr;

RAISE fnd_api.g_exc_error;
ELSE
CLOSE l_user_csr;
END IF;

fnd_global.apps_initialize(user_id => p_user_id
, resp_id => p_responsibility_id
, resp_appl_id => p_resp_appl_id
);
EXCEPTION
WHEN fnd_api.g_exc_error
THEN
p_return_status := fnd_api.g_ret_sts_error;
fnd_msg_pub.count_and_get(p_count => p_msg_count,
p_data => p_msg_data);
WHEN fnd_api.g_exc_unexpected_error
THEN
p_return_status := fnd_api.g_ret_sts_unexp_error;
fnd_msg_pub.count_and_get(p_count => p_msg_count,
p_data => p_msg_data);
WHEN OTHERS
THEN
p_return_status := fnd_api.g_ret_sts_unexp_error;

IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
THEN
fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name, p_procedure_name => l_api_name);
END IF;

fnd_msg_pub.count_and_get(p_count => p_msg_count,
p_data => p_msg_data);
END set_global_info;

Oracle Apps Technical Stuff