User assigned to a responsibility

Below query can be helpful to find users assigned to a specific responsibility

SELECT * FROM (
SELECT   DISTINCT
         user_name
       , DECODE (
            GREATEST (u.start_date
                    , ur.start_date
                    , r.start_date
                    , TO_DATE ('01/01/1000', 'DD/MM/YYYY'))
          , TO_DATE ('01/01/1000', 'DD/MM/YYYY'), ''
          , TO_CHAR (GREATEST (u.start_date, ur.start_date, r.start_date))) resp_start_date
       , DECODE (
            LEAST (NVL (u.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY'))
                 , NVL (ur.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY'))
                 , NVL (r.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY')))
          , TO_DATE ('01/01/4712', 'DD/MM/YYYY'), ''
          , TO_CHAR (
               LEAST (NVL (u.end_date, NVL (ur.end_date, r.end_date))
                    , NVL (ur.end_date, NVL (u.end_date, r.end_date))
                    , NVL (r.end_date, NVL (u.end_date, ur.end_date))))) resp_end_date
       , s.security_group_name
       , u.description
FROM     fnd_user u
       , fnd_user_resp_groups_all ur
       , fnd_responsibility r
       , fnd_security_groups_vl s
       , fnd_responsibility_tl frt
WHERE        1 = 1
         AND frt.responsibility_name = :resp_name
         AND ur.responsibility_application_id = r.application_id
         AND ur.responsibility_id = r.responsibility_id
         AND u.user_id = ur.user_id
         AND ur.security_group_id = s.security_group_id
         AND frt.responsibility_id = r.responsibility_id
         AND frt.language = userenv('lang')
) a
WHERE Nvl(resp_end_date,sysdate + 1) > sysdate         
ORDER BY USER_NAME, SECURITY_GROUP_NAME

Generate Nested XML using SQL Statement

Below is a sample query can be used to generate nested XML tag using SQL Statement

SELECT XMLELEMENT(
          "ASSEMBLY"
        , XMLFOREST(msi.segment1 assembly_name
                  , msi.description item_description
                  , ood.organization_code organization_code
                  , bom.assembly_type assembly_type)
        , XMLELEMENT(
             "COMPONENTS_ROWSET"
           , (SELECT XMLAGG(
                        XMLELEMENT(
                           "COMPONENTS"
                         , XMLFOREST(
                              bic.bill_sequence_id bill_sequence_id
                            , msi1.segment1 component_name
                            , msi1.description component_description
                            , bic.item_num item_num
                            , bic.operation_seq_num operation_seq_num
                            , (SELECT XMLAGG(
                                         XMLELEMENT("SUBSTITUTES"
                                                  , XMLFOREST(bsc.component_sequence_id, msi2.segment1 substitute_id))
                                         ORDER BY bsc.component_sequence_id)
                               FROM   bom_substitute_components bsc, mtl_system_items_b msi2
                               WHERE  bsc.component_sequence_id = bic.component_sequence_id
                               AND    bsc.substitute_component_id = msi2.inventory_item_id
                               AND    bom.organization_id = msi2.organization_id) substitute_rowset
                            , (SELECT XMLAGG(
                                         XMLELEMENT(
                                            "REFERENCE"
                                          , XMLFOREST(bsc.component_reference_designator, ref_designator_comment))
                                         ORDER BY bsc.component_sequence_id)
                               FROM   bom_reference_designators bsc
                               WHERE  bsc.component_sequence_id = bic.component_sequence_id) reference_rowset)))
              --                                   )
              FROM   bom_inventory_components bic, mtl_system_items_b msi1
              WHERE  bom.bill_sequence_id = bic.bill_sequence_id
              AND    bic.component_item_id = msi1.inventory_item_id
              AND    bom.organization_id = msi1.organization_id
              AND    bic.item_num < 85))).transform(xmltype('
 
 
  
   
  
 
'))
          c
FROM   bom_bill_of_materials bom, mtl_system_items_b msi, org_organization_definitions ood
WHERE  msi.organization_id = bom.organization_id
AND    msi.inventory_item_id = bom.assembly_item_id
AND    ood.organization_id = msi.organization_id
AND    ood.organization_code = :p_org
AND    msi.segment1 = :p_assembly_item;

Query for Standard and Blanket Purchase order Details

Below query can be handy to retrieve details for Standard Purchase order and Blanket Purchase order with releases. The query is not completely tested and is only for reference.

SELECT poh.segment1 po_number
     , poh.type_lookup_code
     , pr.release_num
     , poh.creation_date
     , pv.vendor_name supplier
     , pvs.vendor_site_code supplier_site_code
     , hl.location_code ship_to_location_code
     , pb.agent_name buyer_name
     , msi.segment1 item_number
     , msi.description item_desc
     , msi.inventory_item_status_code item_status
     , pll.quantity
     , pll.quantity_received
     , pll.quantity_cancelled
     , pll.quantity_billed
     , pol.unit_price
     , mp.organization_code receiving_org_code
     , (SELECT mc.concatenated_segments
        FROM mtl_categories_kfv mc, mtl_item_categories mic, mtl_category_sets mcs
        WHERE mcs.category_set_name = 'PURCHASING'
          AND mcs.category_set_id = mic.category_set_id
          AND mic.inventory_item_id = msi.inventory_item_id
          AND mic.organization_id = msi.organization_id
          AND mic.category_id = mc.category_id)
          po_category
FROM po_headers_all poh
   , po_lines_all pol
   , po_line_locations_all pll
   , po_releases_all pr
   , mtl_system_items msi
   , org_organization_definitions mp
   , po_vendors pv
   , po_vendor_sites_all pvs
   , po_agents_v pb
   , hr_locations hl
   , hr_operating_units hou
WHERE poh.type_lookup_code IN ('BLANKET', 'STANDARD')
  AND msi.inventory_item_id = pol.item_id
  AND msi.organization_id = pll.ship_to_organization_id
  AND mp.organization_id = msi.organization_id
  AND poh.po_header_id = pol.po_header_id
  AND pol.po_line_id = pll.po_line_id
  AND pr.po_header_id(+) = poh.po_header_id
  AND NVL (pll.po_release_id, 1) = NVL (pr.po_release_id, 1)
  AND poh.vendor_id = pv.vendor_id
  AND poh.vendor_site_id = pvs.vendor_site_id
  AND pvs.vendor_id = pv.vendor_id
  AND pb.agent_id = poh.agent_id
  AND hl.location_id = poh.ship_to_location_id
  AND poh.org_id = hou.organization_id
  AND hou.short_code = 'VIS-US'
ORDER BY poh.segment1, pr.release_num