Search This Blog

Thursday, September 20, 2018

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;

Wednesday, September 9, 2015

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

Thursday, March 19, 2015

Query for Drop Ship Order

Below query can be helpful to get integrated information for drop ship orders

SELECT h.order_number
     , l.line_number so_line_number
     , ph.segment1 po_number
     , l.ordered_item
     , l.ordered_quantity
     , por.release_num
     , pl.line_num po_line_number
     , ph.authorization_status
     , prh.interface_source_code
     , prh.segment1 requisition_number
     , prl.line_num requisition_line_number
     , ph.closed_date
     , ph.closed_code
     , ods.header_id so_header_id
     , ods.line_id so_line_id
     , prh.requisition_header_id
     , prl.requisition_line_id
     , ph.po_header_id
     , pl.po_line_id
     , pll.line_location_id po_line_location_id
     , por.po_release_id
FROM oe_drop_ship_sources ods
   , oe_order_headers_all h
   , oe_order_lines_all l
   , po_line_locations_all pll
   , po_lines_all pl
   , po_headers_all ph
   , po_requisition_headers_all prh
   , po_requisition_lines_all prl
   , po_releases_all por
WHERE h.header_id = l.header_id
  AND h.header_id = ods.header_id
  AND l.line_id = ods.line_id
  AND por.po_release_id(+) = ods.po_release_id
  AND ods.line_location_id = pll.line_location_id(+)
  AND ods.po_header_id = pl.po_header_id(+)
  AND ods.po_line_id = pl.po_line_id(+)
  AND ph.po_header_id(+) = pl.po_header_id
  AND prl.requisition_header_id(+) = ods.requisition_header_id
  AND prl.requisition_line_id(+) = ods.requisition_line_id
  AND prh.requisition_header_id(+) = prl.requisition_header_id
  AND h.order_number = :sales_order;


Copyright (c) All rights reserved. Presented by Suresh Vaishya