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)

FA:SQL: Query to get inventory organization/inventory organization location and business unit and Legal entity

SELECT iod.organization_code,
       iod.organization_name,
       iod.business_unit_name,
       iod.business_unit_id,
       iod.legal_entity,
       xep.NAME legal_entity_name,
       hla.location_code,
       hla.location_name,
       hla.internal_location_code ,
              Concat(Concat(Concat(Concat(Concat(Concat( Concat(Concat(hla.address_line_1
              || ', ', Nvl2(hla.address_line_2, hla.address_line_2
              || ', ', hla.address_line_2)), Nvl2(hla.address_line_3, hla.address_line_3
              || ', ', hla.address_line_3)), Nvl2(hla.address_line_4, hla.address_line_4
              || ', ', hla.address_line_4)), Nvl2(hla.town_or_city, hla.town_or_city
              || ', ', hla.town_or_city)), Nvl2(hla.region_1, hla.region_1
              || ', ', hla.region_1)), Nvl2(hla.region_2, hla.region_2
              || ', ', hla.region_2)), Nvl2(hla.country, country
              || ', ', hla.country)), hla.postal_code) concatenated_address,
       iod.inventory_enabled_flag,
       iod.inventory_flag,
       iod.distributed_organization_flag,
       iod.profit_center_bu_id,
       iod.mfg_plant_flag,
       iod.contract_mfg_flag,
       iod.eam_enabled_flag,
       iod.timezone_code
 FROM inv_organization_definitions_v iod,
      hr_locations_all hla,
      xle_entity_profiles xep
WHERE iod.location_id = hla.location_id
  AND iod.legal_entity = xep.legal_entity_id
  AND NVL(iod.disable_date, sysdate+1) > sysdate;

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; 

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);

Fusion:SCM:MFG: SQL to Validat the Componenet Issue and WO Completion transactions against Work Order

SELECT imt.transaction_id,iop.organization_code warehouse_code
     , TO_CHAR(imt.creation_date,'DD-MON-YYYY') txn_creation_date
     , ittv.transaction_type_name
     , flva.meaning transaction_action
     , itst.transaction_source_type_name
     , esi.item_number
     , imt.subinventory_code
     , imt.transaction_quantity
     , imt.transaction_uom
     , imt.transaction_date
     , imt.transaction_source_name     
     , iut.serial_number   
  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
 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 UPPER(ittv.transaction_type_name) IN ('WORK IN PROCESS MATERIAL ISSUE', 'WORK IN PROCESS PRODUCT COMPLETION')
   AND UPPER(itst.transaction_source_type_name) = 'WORK ORDER'
   AND esi.item_number = NVL(:p_item_number,esi.item_number)
   AND iop.organization_code = NVL(:p_org_code,iop.organization_code)
   AND imt.transaction_source_name = NVL(:p_wo_number, imt.transaction_source_name)
   AND esi.item_number = NVL(:p_item_number, esi.item_number)
   ORDER BY imt.creation_date desc;

Oracle Fusion SQL : SQL to Extract Transfer Order details

SELECT d.header_number
       , e.line_number
       , so.organization_code source_inv_org
       , do.organization_code dest_inv_org
       , sku.item_number
       , e.requested_qty
       , e.shipped_qty
       , e.received_qty
       , e.delivered_qty
       , e.source_subinventory_code source_subinventory
       , e.destination_subinventory_code dest_subinventory
       , d.fulfill_orchestration_required
       , d.source_type_lookup  hdr_source_type_lookup
       , e.source_organization_id
       , e.destination_organization_id
       , e.line_id
       , e.interface_status_lookup
       , e.source_type_lookup
       , e.destination_type_lookup
       , e.destination_location_id
       , e.status_lookup
       , e.need_by_date
       , e.scheduled_ship_date
       , e.qty_uom_code
       , e.unit_price
       , e.req_bu_id
       , e.supply_order_reference_number
       , e.supply_order_ref_line_number
       , d.header_id
       , sku.inventory_item_id
       , d.creation_date
  FROM fusion.inv_transfer_order_headers d
       , fusion.inv_transfer_order_lines e
       , fusion.inv_org_parameters so
       , fusion.inv_org_parameters do
       , fusion.egp_system_items_b sku
 WHERE 1 = 1
   AND sku.inventory_item_id = e.inventory_item_id
   AND sku.organization_id = e.source_organization_id
   AND do.organization_id = e.destination_organization_id
   AND so.organization_id = e.source_organization_id
   AND d.header_id = e.header_id
   AND so.organization_code = :p_from_organization
   AND do.organization_code = :p_to_organization
   AND sku.item_number = NVL(:p_part_number, sku.item_number)
   AND e.supply_order_reference_number = Nvl(:p_supply_order, e.supply_order_reference_number)
 ORDER BY d.creation_date DESC
          , 1
          , Lpad(e.line_number, 2);

Oracle Fusion SQL: OSQL to Extract Inventory Pending Transactions (Records stuck in Oracle inventory Interface table)

 SELECT iop.organization_code,
    iop.organization_name,
    ittv.transaction_type_name,
    flva.meaning transaction_action,
    mti.tRANSACTION_SOURCE_NAME,
    itst.transaction_source_type_name,
    mti.transaction_interface_id txn_iface_id,
    mti.transaction_header_id txn_hdr_id,
    mti.inventory_item_id inv_item_id,
    egp.item_number,
    mti.subinventory_code subinv_code,
    mti.transaction_quantity txn_qty,
    mti.transaction_uom uom,
    mti.transaction_type_id txn_typ_id,
    mti.transaction_action_id txn_act_id,
    mti.transaction_source_type_id txn_sty_id,
    mti.transaction_source_id txn_src_id,
    TO_CHAR(mti.transaction_date, 'DD-MON-YYYY HH24:MI:SS') txn_date,
    mti.source_code src_code,
    mti.process_flag pflag,
    decode(to_char(nvl(mti.process_flag, 0)),'1', 'Ready', '2', 'Not Ready', '3', 'Error', to_char(mti.process_flag) ) pflag_desc,
    mti.transaction_mode tmode,
    decode( mti.transaction_mode, '2', 'Immediate', '3', 'Background', to_char(mti.transaction_mode) ) tmode_desc,
    NVL(mti.lock_flag, 'N') lflag,
    decode(mti.lock_flag, '1', 'Locked', '2', 'Not Locked', 'Not Locked') lflag_desc,
    mti.error_code error_code,
    mti.error_explanation error_explanation
FROM fusion.inv_transactions_interface mti,
    fusion.inv_transaction_types_vl ittv,
    fusion.fnd_lookup_values_vl flva,
    fusion.inv_txn_source_types_vl itst,
    fusion.egp_system_items_b egp,
    fusion.inv_organization_definitions_v iop
WHERE 1 = 1
  AND ittv.transaction_source_type_id = itst.transaction_source_type_id
  AND mti.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 iop.organization_id = egp.organization_id
  AND mti.organization_id = egp.organization_id
  AND mti.inventory_item_id = egp.inventory_item_id
  AND iop.organization_code = NVL(:p_organization_code, iop.organization_code)
  AND egp.item_number = NVL(:p_item_number, egp.item_number)
  AND mti.error_code IS NOT NULL
ORDER BY transaction_date;

Oracle Fusion: SQL to extract Inventory Organization Parameters Details

 

SELECT a.organization_id,
       a.organization_code,
       g.name organization_name,
       a.master_organization_id,
       b.organization_code master_organization_code,
       a.business_unit_id,
       a.legal_entity_id,
       a.negative_inv_receipt_code,
       e.meaning neg_meaning,
       a.stock_locator_control_code,
       c.meaning stock_locator_meaning,
       a.serial_number_type,
       d.meaning serial_number_type_meaning,
       a.lot_number_uniqueness,
       f.meaning lot_uniq_meaning
FROM inv_org_parameters a,
     inv_org_parameters b,
     fnd_lookups c,
     fnd_lookups d,
     fnd_lookups e,
     fnd_lookups f,
     hr_organization_units_f_tl g
WHERE a.master_organization_id = b.organization_id
  AND c.lookup_type = 'INV_LOCATION_CONTROL'
  AND a.stock_locator_control_code = c.lookup_code (+)
  AND d.lookup_type = 'INV_SERIAL_NUMBER'
  AND a.serial_number_type = d.lookup_code (+)
  AND e.lookup_type = 'INV_YES_NO_NUMERIC'
  AND a.negative_inv_receipt_code = e.lookup_code (+)
  AND f.lookup_type = 'INV_LOT_UNIQUENESS'
  AND a.lot_number_uniqueness = f.lookup_code (+)
  AND g.organization_id = a.organization_id
  AND g.language = 'US'
ORDER BY a.organization_id

Oralce Fusion: SQL to Get Concatenated Address example

Inventory Organization Location

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

SELECT z.organization_code,
       z.organization_name,
       b.location_id,
       b.active_status,
       b.effective_start_date,
       b.effective_end_date,
       b.internal_location_code,
       b.location_use,
       b.location_code,
       b.location_name,
       b.description,
       b.style,
       Concat(Concat(Concat(Concat(Concat(Concat(
                     Concat(Concat(b.address_line_1 || ', ',
                     Nvl2(b.address_line_2, b.address_line_2 || ', ', b.address_line_2)),
                     Nvl2(b.address_line_3, b.address_line_3 || ', ', b.address_line_3)),
                     Nvl2(b.address_line_4, b.address_line_4 || ', ', b.address_line_4)),
                     Nvl2(b.town_or_city, b.town_or_city || ', ', b.town_or_city)),
                     Nvl2(b.region_1, b.region_1 || ', ', b.region_1)),
                     Nvl2(b.region_2, b.region_2 || ', ', b.region_2)),
                     Nvl2(b.country, country || ', ', b.country)), b.postal_code) concatenated_address
FROM   inv_organization_definitions_v z,
       hr_locations b
WHERE  z.location_id = b.location_id;

 

Party/Customer/HZ Location

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

SELECT  concat(concat(concat(concat(concat(concat(concat(concat(address1||', ',NVL2(address2,address2||', ',address2)),NVL2(address3,address3||', ',address3)),NVL2(address4,address4||', ',address4)),NVL2(city,city||', ',city)),NVL2(county,county||', ',county)),NVL2(state,state||', ',state)),NVL2(province, province||', ',province)),postal_code) concatenated_address
  FROM hz_locations
 WHERE country ='US';  

SELECT hzp.party_name
       || ' '
       || hzp.party_number,
       hzp.party_id,
       hzps.party_site_number "ShipToPartySiteNumber",
       HZA.account_number,
       HZA.account_name,
       hza.cust_account_id,
       HZA.status "Account Status",
       hzp.status "Party Status",
       hzps.status  "Party Site Status",
       hzps.party_site_id  "PARTY SITE ID - for SHIP_TO",
       hzcasa.status "Account Site Status",
       hzcsua.site_use_id "Account Site ID - for BILL_TO",
       hzcasa.start_date,
       hzcasa.end_date,
       hzcsua.site_use_code,
       hzcasa.bill_to_flag,
       hzcasa.ship_to_flag,
       hzcsua.primary_flag,
       hzcsua.status "Account Site USE Status",
       hzcsua.location,
     concat(concat(concat(concat(concat(concat(concat(concat(hzl.address1||', ', NVL2(hzl.address2, hzl.address2||', ', hzl.address2)), NVL2(hzl.address3, hzl.address3||', ', hzl.address3)), NVL2(hzl.address4, hzl.address4||', ', hzl.address4)), NVL2(hzl.city, hzl.city||', ', hzl.city)), NVL2(hzl.county, hzl.county||', ', hzl.county)), NVL2(hzl.state, hzl.state||', ', hzl.state)), NVL2(hzl.province, hzl.province||', ', hzl.province)), hzl.postal_code) concatenated_address,
       hzl.location_id
FROM   fusion.hz_parties HZP,
       fusion.hz_party_sites hzps,
       fusion.hz_cust_accounts HZA,
       fusion.hz_cust_acct_sites_all hzcasa,
       fusion.hz_cust_site_uses_all hzcsua,
       fusion.hz_locations HZL
WHERE  hzP.party_id = HZA.party_id (+)
       AND hza.cust_account_id = hzcasa.cust_account_id (+)
       AND hzcasa.party_site_id = hzps.party_site_id (+)
       AND hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id (+)
       AND hzps.location_id = hzl.location_id (+)
       AND hzp.party_number = :p_party_number
       AND hzcsua.primary_flag = 'Y'
ORDER  BY hzp.party_number,
          hza.account_number,
          hzl.location_id

Script to Get Application user password from backend

--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = :p_user_name;

Joins and Subqueries

This article is the second in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine. In the previous article in the Beyond SQL 101 series, you learned how to use table aliases to reduce the amount of code necessary to formulate a table join. You saw, too, that a Cartesian product can result from missing join criteria, and you learned how to double-check that you’ve included the correct number of necessary join conditions against the number of tables in your FROM clause. You also got an introduction to ANSI syntax for inner joins, and you learned how the USING and ON clauses can be used.
In this article, you’ll learn about
·    Outer joins and self-joins
·    Scalar subqueries
·    Correlated subqueries
Outer joins and self-joins help you perform more-complex actions to obtain results that are difficult to get with an ordinary equijoin. The results of an outer join include the rows returned by an equijoin plus certain rows from one table for which no rows in the other table satisfy the join condition. A self-join joins a table to itself. And subqueries—whether they are scalar or correlated—enable you to use output from one query as input to another query or SQL statement.
To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 1 (12.1.0.2.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.
Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.)

All Inclusive
Suppose that your business requirement is to obtain a list of all employees in your company along with their assigned departments, if any. The list needs to include any employees who don’t have an assigned department value. The query in Listing 1 obtains a result set of all employees in the EMPLOYEE table alongside their DEPARTMENT_ID values. You can see that one employee, Frances Newton, does not have an assigned DEPARTMENT_ID value, so that employee’s DEPARTMENT_ID value is NULL.
Code Listing 1: Obtaining a list of employees with their DEPARTMENT_ID values
SQL> set feedback on
SQL> set lines 32000
SQL> select rtrim(first_name||' '||last_name) "Employee Name", department_id
  2    from employee
  3  order by last_name, first_name;

Employee Name                            DEPARTMENT_ID
———————————————————————————————————————— —————————————
Lori Dovichi                                        10
Emily Eckhardt                                      10
Roger Friedli                                       10
Betsy James                                         10
Thomas Jeffrey                                      30
Matthew Michaels                                    10
Donald Newton                                       10
Frances Newton
Theresa Wong                                        30
mark leblanc                                        20
michael peterson                                    20

11 rows selected.


A more meaningful result set, though, would include department names instead of DEPARTMENT_ID values. You can obtain the name of each employee’s department by joining the EMPLOYEE table to the DEPARTMENT table. But if you use an equijoin statement—one that joins the DEPARTMENT_ID column of the EMPLOYEE table with the DEPARTMENT_ID column of the DEPARTMENT table—you won’t meet your business requirement. An equijoin relies on the fact that a column value that exists in one column also exists in the column with which it is being joined. But as you learned in “United Relations: Accessing More Than One Table at Once,” a NULL value cannot be equal to another value—not even another NULL value. The row with a value of NULL in the DEPARTMENT_ID column would be ignored, so the result set would not include a row for Frances Newton.
You can get the full result set by using an outer join instead of an equijoin. Like an equijoin, an outer join returns all the records that match the join criteria. But an outer join can also return records from one table that have no matching records in another table. For example, you can use an outer join when you want to show records with NULL values for nonmatching rows. When an outer join operator is placed on a table.column combination, the query returns NULL column values when an equijoin match is not found.
One outer join syntax option uses Oracle’s outer join operator, (+). The query in Listing 2 uses this operator to obtain every employee record in the EMPLOYEE table alongside the name of the employee’s assigned department. The outer join operator is placed on the D.DEPARTMENT_ID table.column combination. Therefore, even if a DEPARTMENT_ID value from the EMPLOYEE table has no match in the DEPARTMENT table, the query result should include any records from the EMPLOYEE table with a NULL value for the unmatched DEPARTMENT_ID value. The result includes the record for Frances Newton, demonstrating that the requested outer join operation took place.
Code Listing 2: An outer join that uses Oracle’s (+) operator
SQL> select d.name "Department Name", rtrim(first_name||' '||last_name) "Employee Name"
  2    from department d, employee e
  3   where e.department_id = d.department_id(+)
  4  order by d.name, last_name, first_name;

Department Name          Employee Name
———————————————————————— ————————————————————————————————
Accounting               Lori Dovichi
Accounting               Emily Eckhardt
Accounting               Roger Friedli
Accounting               Betsy James
Accounting               Matthew Michaels
Accounting               Donald Newton
IT                       Thomas Jeffrey
IT                       Theresa Wong
Payroll                  mark leblanc
Payroll                  michael peterson
                         Frances Newton

11 rows selected.


Listings 3 and 4 demonstrate the same query and results as Listing 2, but they use ANSI outer join syntax instead of the Oracle-specific (+) operator. Listing 3 uses the ANSI RIGHT OUTER JOIN keywords to indicate that the outer join operation should be placed on the table that is listed to the right of the keywords (the EMPLOYEE table). Listing 4 reverses the order of the tables listed in the FROM clause, to demonstrate the use of the ANSI LEFT OUTER JOIN keywords. In this case, the outer join operation is placed on the EMPLOYEE table, because it is the table listed to the left of the outer join keywords.
Code Listing 3: An outer join that uses the ANSI RIGHT OUTER JOIN syntax
SQL> select d.name "Department Name", rtrim(first_name||' '||last_name) "Employee Name"
  2    from department d RIGHT OUTER JOIN employee e
  3      ON d.department_id = e.department_id
  4  order by d.name, last_name, first_name;

Department Name          Employee Name
———————————————————————— ————————————————————————————————
Accounting               Lori Dovichi
Accounting               Emily Eckhardt
Accounting               Roger Friedli
Accounting               Betsy James
Accounting               Matthew Michaels
Accounting               Donald Newton
IT                       Thomas Jeffrey
IT                       Theresa Wong
Payroll                  mark leblanc
Payroll                  michael peterson
                         Frances Newton

11 rows selected.

Code Listing 4: An outer join that uses the ANSI LEFT OUTER JOIN syntax
SQL> select d.name "Department Name", rtrim(first_name||' '||last_name) "Employee Name"
  2    from employee e LEFT OUTER JOIN department d
  3      ON d.department_id = e.department_id
  4  order by d.name, last_name, first_name;

Department Name          Employee Name
———————————————————————— ————————————————————————————————
Accounting               Lori Dovichi
Accounting               Emily Eckhardt
Accounting               Roger Friedli
Accounting               Betsy James
Accounting               Matthew Michaels
Accounting               Donald Newton
IT                       Thomas Jeffrey
IT                       Theresa Wong
Payroll                  mark leblanc
Payroll                  michael peterson
                         Frances Newton

11 rows selected.


Coming Full Circle
Whereas an equijoin always joins two or more different tables, a self-join joins a table to itself. You list the table multiple times in the FROM clause, using table aliases to distinguish the multiple instances of the table name from one another.
Suppose that your business requirement is to generate a list of managers alongside the employees who report to those managers. The EMPLOYEE table contains both an EMPLOYEE_ID column and a MANAGER column. A quick query of this table, shown in Listing 5, shows that some EMPLOYEE_ID values can be found in the MANAGER column for other employees. For example, you can see that Emily Eckhardt (28) is the manager of five other employees.
Code Listing 5: Query showing that certain employees are the managers of other employees
SQL> select employee_id, rtrim(first_name||' '||last_name) "Employee Name", manager
  2    from employee
  3  order by last_name, first_name;

EMPLOYEE_ID Employee Name                 MANAGER
——————————— ————————————————————————————— ———————
       6573 Lori Dovichi                       28
         28 Emily Eckhardt
       6567 Roger Friedli                      28
       6568 Betsy James                        28
       6571 Thomas Jeffrey
       7895 Matthew Michaels                   28
       1234 Donald Newton                      28
         37 Frances Newton
       6572 Theresa Wong                     6571
       6570 mark leblanc                     6569
       6569 michael peterson

11 rows selected.

Listing 6 uses a self-join of the EMPLOYEE table to display all managers alongside the names of the employees who report to them. Listing 7 uses both a self-join and an outer join to obtain a list of all employees, whether or not they have an assigned manager.
Code Listing 6: A self-join of the EMPLOYEE table to itself
SQL> select rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name",
rtrim(emp.first_name||' '||emp.last_name) "Employee Name"
  2    from employee mgr, employee emp
  3   where mgr.employee_id = emp.manager
  4  order by emp.last_name, emp.first_name, mgr.last_name, mgr.first_name;

Manager Name             Employee Name
———————————————————————— ————————————————————————————————
Emily Eckhardt           Lori Dovichi
Emily Eckhardt           Roger Friedli
Emily Eckhardt           Betsy James
Emily Eckhardt           Matthew Michaels
Emily Eckhardt           Donald Newton
Thomas Jeffrey           Theresa Wong
michael peterson         mark leblanc

7 rows selected.

Code Listing 7: A self-join and outer join listing managers alongside their employees
SQL> select rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name",
rtrim(emp.first_name||' '||emp.last_name) "Employee Name"
  2   from employee mgr, employee emp
  3  where mgr.employee_id (+) = emp.manager
  4  order by emp.last_name, emp.first_name, mgr.last_name, mgr.first_name;

Manager Name             Employee Name
———————————————————————— ————————————————————————————————
Emily Eckhardt           Lori Dovichi
                         Emily Eckhardt
Emily Eckhardt           Roger Friedli
Emily Eckhardt           Betsy James
                         Thomas Jeffrey
Emily Eckhardt           Matthew Michaels
Emily Eckhardt           Donald Newton
                         Frances Newton
Thomas Jeffrey           Theresa Wong
michael peterson         mark leblanc
                         michael peterson

11 rows selected.

In Listings 6 and 7, note that the EMPLOYEE table is listed twice in the FROM clause. Because the two table name instances have different aliases—mgr and emp—Oracle Database treats them as two different tables. Primary and foreign key constraints will be more fully outlined in a future article in this series. For now, note that in the EMPLOYEE table, the MANAGER column would typically be created as a foreign key to the primary key column of the EMPLOYEE_ID column, illustrating a self-referencing, or recursive, relationship between the two columns.
The Ins and Outs of Subqueries
Some problems are best solved when they are broken down into individual pieces. Subqueries can help you break a SQL statement into multiple components, in divide-and-conquer fashion, by nesting queries. A subquery is often referred to as an inner query, and the surrounding statement that invokes it is often called an outer query. In its simplest form, an inner query is conceptually executed once, before the outer query is executed. A scalar subquery, also known as a single-row subquery, returns a single column value with zero rows or one row.
Suppose you want to show all employees with the lowest salary. You could query the EMPLOYEE table and sort by the SALARY value in ascending order, listing NULLs last. This type of query would return the lowest-salary earners first, but the result set would also include data that is not meaningful to your goal.
Listings 8 and 9 illustrate another option for obtaining the result. The query in Listing 8 obtains the minimum SALARY value (60000) from the EMPLOYEE table as a first step. The query in Listing 9then uses the result from Listing 8 to finish answering the question WHERE SALARY = 60000. The ultimate answer is correct, and it doesn’t include unnecessary extra rows, but the two separate queries require more work than is necessary.
Code Listing 8: Obtaining the minimum salary value from the EMPLOYEE table
SQL> select min(salary)
  2    from employee;

MIN(SALARY)
———————————
      60000

1 row selected.

Code Listing 9: Obtaining a list of the employees who earn the known minimum salary
SQL> select rtrim(first_name||' '||last_name) "Employee Name", salary
  2    from employee
  3   where salary = 60000
  4  order by last_name, first_name;

Employee Name                SALARY
———————————————————————— ——————
Roger Friedli             60000
Betsy James               60000

2 rows selected.

By using a subquery, you can write both queries in one statement. The intermediate step of feeding the result of the first query to the second query is performed automatically as part of the statement. In Listing 10, which uses a subquery, the same ultimate result that is obtained in two parts in Listings 8 and 9 is obtained in one part. The inner query, which obtains the lowest salary value from the EMPLOYEE table, is conceptually executed first. Its result is fed to the outer query, which retrieves all rows that satisfy the minimum-salary-value condition.
Code Listing 10: Using a subquery to obtain the employees who earn the minimum salary

SQL> select rtrim(first_name||' '||last_name) "Employee Name", salary
  2    from employee
  3   where salary =
  4                  (select min(salary)
  5                     from employee)
  6  order by last_name, first_name;

Employee Name            SALARY
———————————————————————— ——————
Roger Friedli             60000
Betsy James               60000

2 rows selected.

Listing 10’s subquery uses the = operator. Subqueries can also use other operators, such as >, <, >=, <=, and <>. However, as with the = operator, such comparisons work only when the subquery returns at most a single row. Listing 11 demonstrates the type of error message you receive when you attempt a scalar subquery action on a query that does not return a single-row result. Because five employee records satisfy the condition of having salary values of less than 75000, the error occurs. You must use operators such as IN or NOT IN for subqueries that return multiple rows. By using IN instead of the single-row < operator that Listing 11 uses, Listing 12 generates the correct result set.
Code Listing 11: The error message returned when a scalar subquery returns more than one row

SQL> select rtrim(first_name||' '||last_name) "Employee Name", salary
  2    from employee
  3   where salary < (select salary
  4                     from employee
  5                    where salary < 75000)
  6  order by last_name, first_name;
where salary < (select salary
                 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

Code Listing 12: A subquery that uses the IN operator to return multiple rows

SQL> select rtrim(first_name||' '||last_name) "Employee Name", salary
  2    from employee
  3   where salary IN (select salary
  4                      from employee
  5                     where salary < 75000)
  6  order by last_name, first_name;

Employee Name            SALARY
———————————————————————— ——————
Roger Friedli             60000
Betsy James               60000
Matthew Michaels          70000
Theresa Wong              70000
mark leblanc              65000

5 rows selected.


Meeting Again and Again
Statements that include simple subqueries—both single-row and multirow—conceptually execute their inner queries first, feed the results of those queries to their outer queries, and then execute the outer queries. Statements that include correlated subqueries conceptually execute the inner query repeatedly, because correlated subqueries can reference columns from the outer query (hence the term correlated). A correlated subquery is useful when you need to compare every row of the outer query’s results with the result of the inner query.
Listing 13 displays the employees who earn the highest salary in their respective departments. The inner query is correlated with the outer query on the DEPARTMENT_ID value. For each outer query iteration, the DEPARTMENT_ID value from the outer query is compared with the DEPARTMENT_ID value from the inner query. Every time the DEPARTMENT_ID value in the outer query finds a match in the inner query, the maximum salary value for the matching department is obtained and fed to the outer query to obtain a row result. Thus, only the top-employee-salary earners for each department are returned.

Code Listing 13: A correlated subquery returning top salary earners by department

SQL> select rtrim(first_name||' '||last_name) "Employee Name", salary, d.name
  2    from employee e, department d
  3   where e.department_id = d.department_id
  4     and salary = (select max(salary)
  5                     from employee
  6                   where department_id = d.department_id);

Employee Name            SALARY NAME
———————————————————————— —————— ———————————————
Emily Eckhardt           100000 Accounting
michael peterson          90000 Payroll
Thomas Jeffrey           300000 IT

3 rows selected.

To test whether a correlated subquery returns at least one row, you can use the EXISTS operator. This operator returns TRUE or FALSE but never UNKNOWN. To obtain a list of only those employees who have assigned managers, you can use a correlated subquery similar to the one in Listing 14. Because you use the EXISTS operator only to test whether a row exists, the columns included in the SELECT list of the subquery have no relevance. Note that the NULL value is included in the SELECT list of the subquery in Listing 14. Conversely, the NOT EXISTS operator—a frequently used correlated subquery construct—tests whether a matching row cannot be found. The query in Listing 15 displays all employees not yet assigned to any department, so only the employee record for Frances Newton is returned.
Code Listing 14: A correlated subquery that uses the EXISTS operator

SQL> select rtrim(first_name||' '||last_name) "Employee Name"
  2    from employee e
  3   where EXISTS (select NULL
  4                   from employee
  5                  where manager is not null
  6                    and employee_id = e.employee_id)
  7  order by last_name, first_name;

Employee Name
————————————————————————
Lori Dovichi
Roger Friedli
Betsy James
Matthew Michaels
Donald Newton
Theresa Wong
mark leblanc

7 rows selected.


Code Listing 15: A correlated subquery that uses the NOT EXISTS operator

SQL> select rtrim(first_name||' '||last_name) "Employee Name"
  2    from employee e
  3   where NOT EXISTS (select 1
  4                       from employee
  5                      where department_id is not null
  6                        and employee_id = e.employee_id)
  7  order by last_name, first_name;

Employee Name
————————————————————————
Frances Newton

1 row selected.


Conclusion
This article introduced you to Oracle SQL outer joins, self-joins, scalar subqueries, multirow subqueries, and correlated subqueries. It also illustrated two types of outer join operators: the Oracle-specific (+) operator and the ANSI outer join keywords. You’ve seen how self-joins can be used to obtain results from tables with recursive relationships. You’ve learned how subqueries help solve a business problem in one SQL statement. You’ve discovered how a scalar subquery differs from a multirow subquery and which operators are appropriate for each. And you’ve also been introduced to correlated subqueries and the EXISTS and NOT EXISTS correlated subquery operators. The next article in this series introduces inline views and set operators.

Matrix/Crosstab SQL using PIVOT functionality

Sample SQL's 

Using Decode and aggregate functions.

SELECT item
     , inventory
     , product_family
  FROM ( SELECT msi.segment1 item
, MAX( DECODE(mcs.category_set_name, 'Inventory', mc.segment1))       inventory
, MAX( DECODE(mcs.category_set_name, 'Product Family', mc.segment1))   product_family
            FROM mtl_system_items_b msi
               , mtl_item_categories mic
               , mtl_categories mc
               , mtl_category_sets mcs
           WHERE msi.organization_id = mic.organization_id
             AND msi.inventory_item_id = mic.inventory_item_id
             AND msi.segment1 = :p_item
             AND msi.organization_id = 103
             AND mic.category_set_id = mcs.category_set_id
             AND mic.category_id = mc.category_id
             AND mc.structure_id = mcs.structure_id
        GROUP BY msi.segment1)
                       


SELECT *
  FROM (SELECT msi.segment1 item
             , mcs.category_set_name category_set
             , mc.segment1
          FROM mtl_system_items_b msi
             , mtl_item_categories mic
             , mtl_categories mc
             , mtl_category_sets mcs
         WHERE 1 = 1
           AND msi.inventory_item_id = mic.inventory_item_id
           AND msi.organization_id = mic.organization_id
           AND msi.segment1 = :p_item
           AND msi.organization_id = 103
           AND mic.category_set_id = mcs.category_set_id
           AND mic.category_id = mc.category_id
           AND mc.structure_id = mcs.structure_id) 
PIVOT XML (MAX( segment1) AS category  --<-- pivot_clause
FOR( category_set)   --<-- pivot_for_clause
IN (select category_set_name from mtl_category_sets)) 
--<-- pivot_in_clause
                                                   
                                                   
                                                   
SELECT *
  FROM (SELECT msi.segment1 item
             , mcs.category_set_name category_set
             , mc.segment1
          FROM mtl_system_items_b msi
             , mtl_item_categories mic
             , mtl_categories mc
             , mtl_category_sets mcs
         WHERE 1 = 1
           AND msi.inventory_item_id = mic.inventory_item_id
           AND msi.organization_id = mic.organization_id
           AND msi.segment1 = :p_item
           AND msi.organization_id = 103
           AND mic.category_set_id = mcs.category_set_id
           AND mic.category_id = mc.category_id
           AND mc.structure_id = mcs.structure_id) 
PIVOT (MAX( segment1) AS category  --<-- pivot_clause
FOR( category_set) --<-- pivot_for_clause
IN  ('Inventory', 'Series', 'Model')) --<-- pivot_in_clause

SQL to get DB Server Details

SELECT (SELECT UTL_INADDR.get_host_address FROM DUAL) local_host_address
     , (SELECT UTL_INADDR.get_host_name( (SELECT UTL_INADDR.get_host_address FROM DUAL)) FROM DUAL) host_name
     , (SELECT UTL_INADDR.get_host_address( 'google.com') FROM DUAL) host_address
     , (SELECT UTL_INADDR.get_host_name FROM DUAL) local_host_name
     , (SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM DUAL) host_terminal
     , (SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL) host
     , (SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL) ip_Address
     , (SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') FROM DUAL) server_host
     , (SELECT host_name FROM v$instance) local_host_name_v$instance
     , (SELECT LOWER(USER|| '@' || SYS_CONTEXT('USERENV', 'INSTANCE_NAME')) x FROM DUAL) instance_name
  FROM DUAL

Build Dynamic block for DML operations....

PROCEDURE execute_dml_dynamically(
          p_table_name         IN VARCHAR2
        , p_where_column       IN VARCHAR2
        , p_where_column_val   IN INTEGER
        , p_upd_col_name       IN VARCHAR2
        , p_set_value          IN VARCHAR2)
IS
   l_cursor            PLS_INTEGER := DBMS_SQL.open_cursor;
   l_execute           PLS_INTEGER;
BEGIN
   DBMS_SQL.parse(l_cursor
                ,    'BEGIN update '
                  || p_table_name
                  || ' set '
                  || p_upd_col_name
                  || ' = '
                  || p_set_value
                  || ' where '
                  || p_where_column
                  || ' = '
                  || p_where_column_val
                  || '; END;'
                , DBMS_SQL.native);

   l_execute       := DBMS_SQL.execute( l_cursor);

   IF l_execute > 0
   THEN
      DBMS_OUTPUT.put_line(   'Value of '
                           || p_upd_col_name
                           || ' updated to '
                           || p_set_value);
   ELSE
      DBMS_OUTPUT.put_line(   'Update of '
                           || p_upd_col_name
                           || '  to '
                           || p_set_value
                           || ' failed.');
   END IF;

   DBMS_SQL.close_cursor( l_cursor);
END;
/