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