Query to find Oracle Alert

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
       alr.alert_id,
       alr.alert_name,
       alr.start_date_active,
       alr.description,
       alr.sql_statement_text
  FROM alr.alr_alerts alr
 WHERE 1=1
   AND alr.created_by <> 1      -- show only custom alerts
   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

Ur's
AmarAlam

Active Responsibility List with Active User Count

 SELECT
       fat.application_name         "Application Name",
       frv.responsibility_name      "Active Responsibility Name",
       COUNT(fu.user_name)          "Active User Count"
  FROM
       fnd_user                     fu,
       fnd_user_resp_groups_direct  furgd,
       fnd_responsibility_vl        frv,
       fnd_application_tl           fat
 WHERE
       1=1
   --
   AND furgd.end_date IS NULL
   --
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(furgd.start_date) AND TRUNC(NVL(furgd.end_date, SYSDATE+1))
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(frv.start_date)   AND TRUNC(NVL(frv.end_date, SYSDATE+1))
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(fu.start_date)    AND TRUNC(NVL(fu.end_date, SYSDATE+1))
   --
   AND fat.application_id       =  frv.application_id
   --
   AND furgd.responsibility_id  =  frv.responsibility_id
   AND furgd.user_id            =  fu.user_id
   --
 GROUP BY fat.application_name, frv.responsibility_name
 ORDER BY fat.application_name, frv.responsibility_name;



Ur's
AmarAlam

Query to find the first word from a string

 WITH qry
AS
(
   SELECT NULL field_name FROM dual
      UNION ALL
   SELECT 'AMAR' FROM dual
      UNION ALL
   SELECT 'AMAR ALAM' FROM dual
      UNION ALL
   SELECT 'Abdul M Faheem' FROM dual
      UNION ALL
   SELECT 'Sravan P. Kumar' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt, Ltd' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt, Ltd.' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt Ltd' FROM dual
)
SELECT field_name,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\1')  exp1,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\2')  exp2,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\3')  exp3
  FROM qry;




Ur's
AmarAlam

Delete Concurrent Program from the Back-End

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

 -------------------------------------------------------------------------------
-- delete concurrent program definition and executable from back-end
-------------------------------------------------------------------------------
-- syntax:
--     delete_program    (program_short_name, application_short_name)
--     delete_executable (program_short_name, application_short_name)
-------------------------------------------------------------------------------
DECLARE
  lv_prog_short_name    VARCHAR2(240);
  lv_appl_short_name    VARCHAR2(240);

BEGIN
   -- set the variables first
   lv_prog_short_name := 'XXAJ_IFFCO_COST_SUMARY';     -- concurrent program short name
   lv_appl_short_name := 'XXIFF';          -- application short name
 
   -- see if the program exists. if found, delete the program
   IF fnd_program.program_exists    (lv_prog_short_name, lv_appl_short_name) AND
      fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)   
   THEN
    
      fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);
      fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);
    
      COMMIT;
 
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');
 
   -- if the program does not exist in the system
   ELSE
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
 
END;

Ur's
AmarALam

Find Orcale Discoverer Reports in DataBase

SELECT DISTINCT doc_name, obj.obj_name folder_name, bas.ba_name,
                DECODE (doc.doc_created_by,
                        'EUL5_US', 'N/A',
                        (SELECT user_name
                           FROM fnd_user
                          WHERE user_id = SUBSTR (doc.doc_created_by, 2))
                       ) user_name,
                DECODE (doc.doc_created_by,
                        'EUL5_US', 'N/A',
                        (SELECT description
                           FROM fnd_user
                          WHERE user_id = SUBSTR (doc.doc_created_by, 2))
                       ) user_description
           FROM eul_us.eul5_documents doc,
                eul_us.eul5_elem_xrefs xref,
                eul_us.eul5_expressions EXP,
                eul_us.eul5_objs obj,
                eul_us.eul5_ba_obj_links bol,
                eul_us.eul5_bas bas
          WHERE xref.ex_from_id = doc.doc_id
            AND xref.ex_to_id = EXP.exp_id
            AND obj.obj_id = EXP.it_obj_id
            AND bol.bol_obj_id = obj.obj_id
            AND bas.ba_id = bol.bol_ba_id
            AND doc.doc_created_by != 'ORACLE_APPS'
            AND UPPER (doc_name) LIKE UPPER ('%CONS%TRACK%')
--and BAS.BA_NAME like '%Business Area%'
ORDER BY        user_name


Thanks
AmarAlam

Order 2 Cash Cycle Technical Flow in Oracle Apps

SELECT *
  FROM oe_order_headers_all
 WHERE order_number = 66161;

SELECT *
  FROM oe_order_lines_all
 WHERE header_id = (SELECT header_id
                      FROM oe_order_headers_all
                     WHERE order_number = 66161);

SELECT *
  FROM wsh_delivery_details
 WHERE source_header_id = (SELECT header_id
                             FROM oe_order_headers_all
                            WHERE order_number = 66161);

SELECT *
  FROM wsh_delivery_assignments
 WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161));

SELECT *
  FROM wsh_new_deliveries
 WHERE delivery_id IN (
          SELECT delivery_id
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)));

SELECT *
  FROM ra_customer_trx_all
 WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute3) IN (
          SELECT TO_CHAR (delivery_id)
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)))

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id IN (
          SELECT cash_receipt_id
            FROM ar_payment_schedules_all
           WHERE customer_trx_id IN (
                    SELECT DISTINCT customer_trx_id
                               FROM ra_customer_trx_all
                              WHERE (interface_header_attribute1 =
                                        (SELECT TO_CHAR (order_number)
                                           FROM oe_order_headers_all
                                          WHERE header_id =
                                                   (SELECT header_id
                                                      FROM oe_order_headers_all
                                                     WHERE order_number =
                                                                         66162))
                                    )));

SELECT *
  FROM ar_receivable_applications_all
 WHERE customer_trx_id =
          (SELECT customer_trx_id
             FROM ra_customer_trx_all
            WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162)));

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id =
          (SELECT cash_receipt_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));

SELECT *
  FROM gl_code_combinations
 WHERE code_combination_id =
          (SELECT code_combination_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));


Ur's
AmarAlam

Order Sales Returns Closed Report Query

SELECT (SELECT NAME
          FROM apps.hr_operating_units
         WHERE organization_id = ooh.org_id) sales_org,
       (SELECT ood1.organization_name
          FROM apps.org_organization_definitions ood1
         WHERE ood1.organization_id =
                                     ool.ship_from_org_id)
                                                          ship_from_warehouse,
       hcs_ship.attribute1 sales_channel,
       NVL
          (jrs.NAME,
           (SELECT c.full_name
              FROM apps.per_all_people_f c
             WHERE 1 = 1
               AND c.employee_number = jrs.salesrep_number
               AND TRUNC (SYSDATE) BETWEEN c.effective_start_date
                                       AND c.effective_end_date)
          ) sales_person,
       ooh.flow_status_code header_status, ool.flow_status_code line_status,
       ota.order_category_code order_category, ott.NAME order_type_code,
       NVL (ott.description, ott.NAME) description, ooh.order_number,
       NVL ((SELECT full_name
               FROM apps.per_all_people_f a, apps.fnd_user b
              WHERE a.person_id = b.employee_id
                AND b.user_id = ooh.created_by
                AND ROWNUM < 2),
            (SELECT user_name
               FROM apps.fnd_user
              WHERE user_id = ooh.created_by)
           ) created_by,
       ooh.transactional_curr_code currency_code, ooh.fob_point_code,
       ooh.freight_terms_code, ooh.cust_po_number, msi.segment1 item_code,
       msi.description item_description, msi.weight_uom_code, msi.unit_weight,
       CASE
          WHEN xxfcc.channel_level1 = 'CONSUMER'
          AND xxfcc.channel_level2 IN ('LOC_TT', 'EXP_TT')
             THEN 'GT'
          WHEN xxfcc.channel_level1 = 'CONSUMER'
          AND xxfcc.channel_level2 IN ('LOC_MT', 'EXP_MT')
             THEN 'MT'
          ELSE xxfcc.channel_level1
       END channel_group,
       ool.unit_selling_price, ool.line_number, ool.order_quantity_uom,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ool.ordered_quantity
       END return_ordered_quantity,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ((ool.ordered_quantity * msi.unit_weight) / 1000
                  )
       END return_ordered_wt_mt,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ool.shipped_quantity
       END return_shipped_quantity
  FROM ont.oe_order_headers_all ooh,
       ont.oe_transaction_types_tl ott,
       ont.oe_transaction_types_all ota,
       ont.oe_order_lines_all ool,
       apps.org_organization_definitions ood,
       apps.hr_operating_units hou,
       inv.mtl_system_items_b msi,
       ar.hz_cust_site_uses_all hcs_ship,
       jtf.jtf_rs_salesreps jrs,
       ar.hz_cust_acct_sites_all hca_ship,
       ar.hz_party_sites hps_ship,
       ar.hz_parties hp_ship,
       ar.hz_locations hl_ship,
       ar.hz_cust_site_uses_all hcs_bill,
       ar.hz_cust_acct_sites_all hca_bill,
       ar.hz_party_sites hps_bill,
       ar.hz_parties hp_bill,
       ar.hz_locations hl_bill,
       inv.mtl_parameters mp,
       apps.xxiff_fin_customer_channel xxfcc
 WHERE 1 = 1
   AND ooh.ship_to_org_id = hcs_ship.site_use_id
   AND ott.transaction_type_id = ota.transaction_type_id
   AND ott.transaction_type_id = ooh.order_type_id
   AND ott.LANGUAGE = 'US'
   AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
   AND hca_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.party_id = hp_ship.party_id
   AND hps_ship.location_id = hl_ship.location_id
   AND ooh.invoice_to_org_id = hcs_bill.site_use_id
   AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
   AND hca_bill.party_site_id = hps_bill.party_site_id
   AND hcs_bill.site_use_id = xxfcc.site_use_id(+)
   AND msi.inventory_item_id = ool.inventory_item_id
   AND msi.organization_id = ood.organization_id
   AND ood.organization_id = ooh.org_id
   AND hou.organization_id = ood.operating_unit
   AND ooh.salesrep_id = jrs.salesrep_id
   AND ooh.org_id = jrs.org_id
   AND ooh.header_id = ool.header_id
   AND hps_bill.party_id = hp_bill.party_id
   AND ooh.org_id = fnd_profile.VALUE ('ORG_ID')
   --AND trunc(ooh.creation_date)='31-JUL-2013'
   --AND ooh.order_number='8880101739'
   AND ota.order_category_code = 'RETURN'
   AND ool.flow_status_code = 'CLOSED'
   AND hps_bill.location_id = hl_bill.location_id
   AND UPPER (ott.NAME) NOT LIKE '%IDS%ISO%'
   AND mp.organization_id(+) = ooh.ship_from_org_id


Ur's
AmarAlam