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

Oracle Fusion:ESS: SQL to Search Privileges required to run a ESS job

 Try using below SQL to search required Priviliges to run a specific ESS job in Oracle Fusion when user find below error message while submitting the ESS job.

ESS-02002: User 'XYZ' doesnot have sufficient priviliges to perform operation submitrequest 'ess job name'

SELECT p.code privilege
     , t.name
     , perm.code code
     , perm.resource_type_name
     , perm.action
  FROM fusion.ase_privilege_b p
     , fusion.ase_permission_b perm
     , fusion.ase_privilege_tl t
 WHERE p.privilege_id = perm.privilege_id
   AND p.privilege_id = t.privilege_id (+)
   AND t.language = 'US'
   AND sysdate BETWEEN p.effective_start_date AND nvl(p.effective_end_date, sysdate)
   AND sysdate BETWEEN perm.effective_start_date AND nvl(perm.effective_end_date, sysdate)
   AND perm.code LIKE '%'|| :p_ess_job_name|| '%'

FA:OM:SQL To List EFF Segments and attribute Columns mapping for Specific Context

 SELECT DISTINCT fdcb.context_code
              , fdcb.context_identifier
              , fdcb.enabled_flag
              , fdsb.segment_code
              , fdsb.segment_identifier
              , fdsb.column_name
  FROM fusion.fnd_df_contexts_tl fdct
     , fusion.fnd_df_contexts_b fdcb
     , fusion.fnd_df_segments_tl fdst
     , fusion.fnd_df_segments_b fdsb
 WHERE fdct.context_code = fdcb.context_code
   AND fdcb.context_code = fdsb.context_code
   AND fdst.context_code = fdsb.context_code
   AND fdst.segment_code = fdsb.segment_code
   AND fdct.application_id = fdcb.application_id
   AND fdct.language = userenv('Lang')
   AND fdst.language = userenv('Lang')
   AND fdct.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
   AND fdcb.context_code = '<Context_Code>'
 ORDER BY fdcb.context_identifier
        , fdsb.segment_identifier

FA:Query - List of Assigned Roles For a Given User

SELECT ase_user_vl.user_id user_id
     , ase_user_vl.user_login user_login
     , ase_user_vl.user_display_name user_display_name
     , ase_role_vl.code code
     , ase_role_vl.role_name role_name
     , ase_role_vl.description description
     , ase_user_role_mbr.role_id role_id
  FROM fusion.ase_user_vl
     , fusion.ase_role_vl
     , fusion.ase_user_role_mbr
 WHERE ase_user_vl.user_id = ase_user_role_mbr.user_id
   AND ase_user_role_mbr.role_id = ase_role_vl.role_id
   AND ase_user_vl.effective_end_date IS NULL
   AND ase_role_vl.effective_end_date IS NULL
   AND ase_user_role_mbr.effective_end_date IS NULL
   AND upper(user_login) = upper('&user_name')
 ORDER BY role_name

FA:SQL:Manage Data Access for Users

 SELECT username
     , role
     , security_context
     , security_context_value
  FROM ( SELECT pu.username
              , prd.role_name role
              , 'Data Access Set' security_context
              , gl.name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.gl_access_sets gl
            , fusion.per_users pu
        WHERE gl.access_set_id = role.access_set_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Business Unit' security_context
            , bu.bu_name security_context_value
         FROM fusion.fun_all_business_units_v bu
            , fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.per_users pu
        WHERE role.org_id = bu.bu_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Ledgers' security_context
            , led.name security_context_value
         FROM fusion.gl_ledgers led
            , fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.per_users pu
        WHERE role.ledger_id = led.ledger_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Asset Book' security_context
            , book.book_type_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fa_book_controls book
            , fusion.per_users pu
        WHERE book.book_control_id = role.book_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Intercompany Organization' security_context
            , interco.interco_org_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fun_interco_organizations interco
            , fusion.per_users pu
        WHERE interco.interco_org_id = role.interco_org_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Cost Organization' sercurity_context
            , cost.cost_org_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.cst_cost_orgs_v cost
            , fusion.per_users pu
        WHERE cost.cost_org_id = role.cst_organization_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Manufacturing Plant' security_context
            , iop.organization_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.rcs_mfg_parameters mfg
            , inv_org_parameters iop
            , fusion.per_users pu
        WHERE mfg.organization_id = role.mfg_organization_id
          AND mfg.organization_id = iop.organization_id        
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Control Budget' security_context
            , budget.name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.xcc_control_budgets budget
            , fusion.per_users pu
        WHERE budget.control_budget_id = role.control_budget_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Reference data Set' security_context
            , st.set_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fnd_setid_sets_vl st
            , fusion.per_users pu
        WHERE st.set_id = role.set_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Inventory Organization' security_context
            , inv.organization_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.inv_org_parameters inv
            , fusion.per_users pu
        WHERE inv.organization_id = role.inv_organization_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Project Organization Classification' security_context
            , hr.classification_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.hr_org_unit_classifications_f hr
            , fusion.per_users pu
        WHERE hr.org_unit_classification_id = role.org_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid)
 WHERE username = :p_user_name
 ORDER BY 3
        --, 4

FA:RCV:SQL to get RMA Receipts Details in Oracle Fusion

 REM RMA Receipts Details
SELECT DHA.SOURCE_ORDER_NUMBER
     , HDR_LKP.MEANING ORDER_TYPE
     , DFLA.SOURCE_LINE_NUMBER
     , LINE_LKP.MEANING LINE_TYPE     
     , INV.ORGANIZATION_CODE
     , DSV.DISPLAY_NAME FUL_LINE_STATUS
     , (SELECT TO_CHAR(CREATION_DATE, 'DD-MON-YYYY') FROM INV_MATERIAL_TXNS WHERE RCV_TRANSACTION_ID = RMA_RECEIPT_TRANSACTION_ID) INV_TXN_CREATION_DATE
     , ESIB.ITEM_NUMBER
     , DLA.ORDERED_QTY LINE_ORDERED_QTY
     , DFLA.ORDERED_QTY FULFILL_LINE_ORDERED_QTY
     , DFLA.RMA_DELIVERED_QTY
     , DFLD.RMA_RECEIPT_NUMBER
     , DFLD.RMA_RECEIPT_LINE_NUMBER
     , DFLD.RMA_RECEIPT_DATE
     , DLA.DISPLAY_LINE_NUMBER
         || '.'
         || DFLA.FULFILL_LINE_NUMBER LINE_NUM
  FROM FUSION.INV_ORG_PARAMETERS INV
     , FUSION.DOO_FULFILL_LINES_ALL DFLA
     , FUSION.DOO_FULFILL_LINE_DETAILS DFLD
     , FUSION.FND_LOOKUP_VALUES_VL LINE_LKP     
     , FUSION.DOO_LINES_ALL DLA
     , FUSION.FND_LOOKUP_VALUES_VL HDR_LKP
     , FUSION.DOO_HEADERS_ALL DHA
     , FUSION.EGP_SYSTEM_ITEMS_B ESIB
     , FUSION.DOO_STATUSES_VL DSV
     , FUSION.DOO_ORCHESTRATION_APPLICATIONS DOA
 WHERE DHA.HEADER_ID = DFLA.HEADER_ID
   AND DHA.HEADER_ID = DLA.HEADER_ID
   AND DFLA.LINE_ID = DLA.LINE_ID
   AND DHA.SUBMITTED_FLAG = 'Y'
   AND DHA.ORDER_TYPE_CODE = HDR_LKP.LOOKUP_CODE
   AND HDR_LKP.LOOKUP_TYPE = 'ORA_DOO_ORDER_TYPES'
   AND HDR_LKP.ENABLED_FLAG = 'Y'   
   AND DFLA.LINE_TYPE_CODE = LINE_LKP.LOOKUP_CODE
   AND LINE_LKP.LOOKUP_TYPE IN ('ORA_DOO_LINE_TYPES', 'ORA_DOO_RETURN_LINE_TYPES')
   AND LINE_LKP.ENABLED_FLAG = 'Y'   
   AND DOA.ORCHESTRATION_APPLICATION_CODE = 'DOO'
   AND DOA.ORCHESTRATION_APPLICATION_ID = DSV.ORCHESTRATION_APPLICATION_ID
   AND DFLA.STATUS_CODE = DSV.STATUS_CODE
   AND DLA.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
   AND DLA.INVENTORY_ORGANIZATION_ID = ESIB.ORGANIZATION_ID
   AND DFLA.FULFILL_ORG_ID = INV.ORGANIZATION_ID
   AND UPPER(DFLD.TASK_TYPE(+)) = 'RETURN'
   AND DFLA.FULFILL_LINE_ID = DFLD.FULFILL_LINE_ID(+)
   AND DHA.SOURCE_ORDER_NUMBER = :P_ORDER_NUMBER
   --AND ESIB.ITEM_NUMBER  = NVL(:P_ITEM_NUMBER, ESIB.ITEM_NUMBER)
  ORDER BY DHA.SOURCE_ORDER_NUMBER, LPAD(DLA.DISPLAY_LINE_NUMBER, 2) ASC;