SELECT  SUBSTR('2019-16',1,4) || '-W' || SUBSTR('2019-16',6,7)        AS year_week
     , ffv.flex_value     AS project
     , fct.forecasted_qty
     , NULL AS scheduled_qty
     , fct.item
     , fct.item_description
     , NULL AS shipped_quantity
  FROM fnd_flex_value_sets ffvs
     , fnd_flex_values     ffv
     , (SELECT frm.project
             , frm.item_number AS item
             , frm.item_description
             , frm.inventory_item_id
             , ROUND(SUM(frm.quantity)/(30.5/7)) AS forecasted_qty
          FROM xxmlx_forecast_report_mat_v frm
         WHERE frm.demand_plan_name   = fnd_profile.VALUE('XXMLX_DATA_GATHERING_FCT_DEMAND_PLAN_NAME')
           AND frm.scenario_name      = fnd_profile.VALUE('XXMLX_DATA_GATHERING_FCT_SCENARIO_NAME')
           AND frm.revision           = scenario_last_rev
           AND '2019-16'             >= TO_CHAR(frm.period_date, 'YYYY-WW')
           AND '2019-16'             <  TO_CHAR(ADD_MONTHS(frm.period_date, 1), 'YYYY-WW')
         GROUP BY frm.project,
                  FRM.item_number,
                  frm.item_description,
                  frm.inventory_item_id) fct
 WHERE ffvs.flex_value_set_id                = ffv.flex_value_set_id
   AND ffvs.flex_value_set_name              = 'MLX_PROJECT'
   AND ffv.enabled_flag                      = 'Y'
   AND NVL(ffv.start_date_active, SYSDATE)   <= SYSDATE
   AND NVL(ffv.end_date_active, SYSDATE + 1) > SYSDATE
   AND fct.project(+)                        = ffv.flex_value
 UNION ALL
  SELECT  SUBSTR('2019-16',1,4) || '-W' || SUBSTR('2019-16',6,7)        AS year_week
     , ffv.flex_value     AS project
     , NULL AS forecasted_qty
     , NULL AS scheduled_qty
     , spp.item
     , spp.description AS item_description
     , SUM(spp.shipped_quantity) AS shipped_quantity
  FROM
        fnd_flex_value_sets ffvs,
        fnd_flex_values     ffv,
        (SELECT   cat.project,
                  msi.segment1 AS item,
                  msi.description,
                  SUM(ship.shipped_qty) AS shipped_quantity,
                  msi.inventory_item_id
          FROM XXMLX_SHIPMENTS_OVERVIEW_V ship
             , xxmlx_item_cat_inventory_v cat
             , mtl_system_items           msi
         WHERE   cat.master_item_id                         = msi.inventory_item_id
             AND msi.segment1                            = ship.item_number
             AND msi.organization_id = 83
             AND msi.segment1 > '500000000'
             AND msi.segment1 < '700000000'
           AND TO_CHAR(ship.confirm_date+1, 'YYYY-WW') =  '2019-16'
           GROUP BY cat.project,
                    msi.segment1,
                    msi.inventory_item_id,
                    msi.description) spp
   WHERE ffvs.flex_value_set_id                = ffv.flex_value_set_id
   AND ffvs.flex_value_set_name              = 'MLX_PROJECT'
   AND ffv.enabled_flag                      = 'Y'
   AND NVL(ffv.start_date_active, SYSDATE)   <= SYSDATE
   AND NVL(ffv.end_date_active, SYSDATE + 1) > SYSDATE
   AND spp.project(+)                        = ffv.flex_value
   GROUP BY ffv.flex_value,
            spp.item,
            spp.description
   UNION ALL
  SELECT  SUBSTR('2019-16',1,4) || '-W' || SUBSTR('2019-16',6,7)        AS year_week
     , ffv.flex_value     AS project
     , NULL AS forecasted_qty
     , so.scheduled_qty AS scheduled_qty
     , so.item
     , so.description AS item_description
     , NULL AS shipped_quantity
  FROM
        fnd_flex_value_sets ffvs,
        fnd_flex_values     ffv,
        (SELECT itm.project
             , SUM(ool.ordered_quantity) AS scheduled_qty
             , msi.segment1              AS item
             , msi.description
             , msi.inventory_item_id
          FROM oe_order_lines_all         ool
             , oe_transaction_types_tl    ott
             , xxmlx_item_cat_inventory_v itm
             , mtl_system_items           msi
         WHERE ott.transaction_type_id                    = ool.line_type_id
           AND itm.master_item_id                         = ool.inventory_item_id
           AND msi.inventory_item_id                      = ool.inventory_item_id
           AND msi.organization_id                        = ool.ship_from_org_id
           AND ool.flow_status_code                       = 'AWAITING_SHIPPING'
           AND TO_CHAR(ool.schedule_ship_date+1, 'YYYY-WW') =  '2019-16'
         GROUP BY itm.project,
                  msi.segment1,
                  msi.description,
                  msi.inventory_item_id) so
   WHERE ffvs.flex_value_set_id                = ffv.flex_value_set_id
   AND ffvs.flex_value_set_name              = 'MLX_PROJECT'
   AND ffv.enabled_flag                      = 'Y'
   AND NVL(ffv.start_date_active, SYSDATE)   <= SYSDATE
   AND NVL(ffv.end_date_active, SYSDATE + 1) > SYSDATE
   AND so.project(+)                        = ffv.flex_value