3 Replies Latest reply: Feb 15, 2013 2:10 PM by Etbin RSS

    Column is summing up all the rows and displaying same value in all rows.

    988505
      Hi,

      Kindly see the below query. I have a problem in this query to fetch the abs_qty when the parameter P:item ie)msib.segment1 is not given as the input parameter. Its just summing up the entire quantity for all the items.When I pass P:item ie)msib.segment1 as input parameter, It fetches the exact value for abs_qty. Kindly help me to resolve this.

      Note only : Manufacturing plant ,Start date and End Date are Mandatory(required) parameters. Others like Item,Base Model and Planner code are optional.

      SELECT mmt.transaction_date date_produced, msib.segment1 item,
      cic.cost_type_id, msib.description item_description,
      mc.segment6 base_model, mc.segment7 trade_brand,
      mmt.subinventory_code subinventory,
      mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 LOCATOR,
      ood.organization_code
      || '-'
      || ood.organization_name manufacturing_plant,
      mmt.transaction_quantity quantity_produced,
      mtt.transaction_type_name transaction_type, msib.inventory_item_id,
      msib.organization_id, cic.material_cost COST,
      cic.material_overhead_cost freight, csc.standard_cost,
      *(select (NVL((SELECT SUM (mmt.transaction_quantity) total_NET_qty*
      FROM mtl_material_transactions mmt,
      mtl_system_items_b msib,
      mtl_transaction_types mtt,
      org_organization_definitions ood,
      mtl_txn_source_types mts
      WHERE mmt.organization_id = msib.organization_id
      AND msib.inventory_item_id = mmt.inventory_item_id
      AND mmt.transaction_type_id = mtt.transaction_type_id
      AND mmt.organization_id = ood.organization_id
      AND mtt.transaction_type_name = 'WIP Completion'
      AND mts.transaction_source_type_name = 'Job or Schedule'
      AND msib.segment1 = NVL (:p_item, msib.segment1)
      AND ood.organization_code = :p_manufacturing_plant
      AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date
      AND :p_end_date ),0)
      -
      *(NVL((SELECT SUM (mmt.transaction_quantity) total_NET_qty*
      FROM mtl_material_transactions mmt,
      mtl_system_items_b msib,
      mtl_transaction_types mtt,
      org_organization_definitions ood,
      mtl_txn_source_types mts
      WHERE mmt.organization_id = msib.organization_id
      AND msib.inventory_item_id = mmt.inventory_item_id
      AND mmt.transaction_type_id = mtt.transaction_type_id
      AND mmt.organization_id = ood.organization_id
      AND mtt.transaction_type_name = 'WIP Issue'
      AND mts.transaction_source_type_name = 'Job or Schedule'
      AND msib.segment1 = NVL (:p_item, msib.segment1)
      AND ood.organization_code = :p_manufacturing_plant
      AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date
      AND :p_end_date
      *),0))) abs_qty from dual) AS ABS_qtyy,*(select (NVL((SELECT SUM (mmt.transaction_quantity) total_NET_qty
      FROM mtl_material_transactions mmt,
      mtl_system_items_b msib,
      mtl_transaction_types mtt,
      org_organization_definitions ood,
      mtl_txn_source_types mts
      WHERE mmt.organization_id = msib.organization_id
      AND msib.inventory_item_id = mmt.inventory_item_id
      AND mmt.transaction_type_id = mtt.transaction_type_id
      AND mmt.organization_id = ood.organization_id
      AND mtt.transaction_type_name = 'WIP Completion'
      AND mts.transaction_source_type_name = 'Job or Schedule'
      AND msib.segment1 = NVL (:p_item, msib.segment1)
      AND ood.organization_code = :p_manufacturing_plant
      AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date
      AND :p_end_date ),0)
      -
      (NVL((SELECT SUM (mmt.transaction_quantity) total_NET_qty
      FROM mtl_material_transactions mmt,
      mtl_system_items_b msib,
      mtl_transaction_types mtt,
      org_organization_definitions ood,
      mtl_txn_source_types mts
      WHERE mmt.organization_id = msib.organization_id
      AND msib.inventory_item_id = mmt.inventory_item_id
      AND mmt.transaction_type_id = mtt.transaction_type_id
      AND mmt.organization_id = ood.organization_id
      AND mtt.transaction_type_name = 'WIP Issue'
      AND mts.transaction_source_type_name = 'Job or Schedule'
      AND msib.segment1 = NVL (:p_item, msib.segment1)
      AND ood.organization_code = :p_manufacturing_plant
      AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date
      AND :p_end_date
      ),0))) abs_qty from dual)*csc.standard_cost AS abs_val
      FROM mtl_material_transactions mmt,
      mtl_system_items_b msib,
      mtl_item_locations mil,
      mtl_transaction_types mtt,
      org_organization_definitions ood,
      mtl_txn_source_types mts,
      mtl_categories mc,
      mtl_item_categories mic,
      mtl_category_sets mcs,
      cst_item_costs cic,
      cst_standard_costs csc,
      cst_cost_types ct,
      mfg_lookups mlo
      WHERE mmt.organization_id = msib.organization_id
      AND msib.inventory_item_id = mmt.inventory_item_id
      AND mmt.locator_id = mil.inventory_location_id
      AND mil.organization_id = msib.organization_id
      AND mil.subinventory_code = mmt.subinventory_code
      AND mmt.transaction_type_id = mtt.transaction_type_id
      AND msib.organization_id = ood.organization_id
      AND mic.category_set_id = mcs.category_set_id
      AND mc.category_id = mic.category_id
      AND mc.structure_id = mcs.structure_id
      AND mic.inventory_item_id = msib.inventory_item_id
      AND mic.organization_id = msib.organization_id
      AND mcs.category_set_id = 52487965
      AND msib.inventory_item_id = cic.inventory_item_id
      AND msib.organization_id = cic.organization_id
      AND ct.cost_type_id = cic.cost_type_id
      AND cic.organization_id = ood.organization_id
      AND mil.inventory_location_id = mmt.locator_id
      AND mmt.transaction_source_type_id = mtt.transaction_source_type_id
      AND mmt.transaction_action_id = mtt.transaction_action_id
      AND mmt.transaction_source_type_id = mts.transaction_source_type_id
      AND mmt.transaction_action_id = mlo.lookup_code
      AND mmt.inventory_item_id = csc.inventory_item_id
      AND mmt.organization_id = csc.organization_id
      AND csc.last_update_date >=
      (SELECT MAX (csc1.last_update_date)
      FROM cst_standard_costs csc1
      WHERE csc1.inventory_item_id = mmt.inventory_item_id
      AND csc1.organization_id = mmt.organization_id)
      AND msib.segment1 = NVL (:p_item, msib.segment1)
      AND NVL (mc.segment6, 'X') = NVL (:p_base_model, NVL (mc.segment6, 'X'))
      AND NVL (msib.planner_code, 'Y') =
      NVL (:p_planner_code, NVL (msib.planner_code, 'Y'))
      AND UPPER (mlo.meaning) = 'ASSEMBLY COMPLETION'
      AND mtt.transaction_type_name = 'WIP Completion'
      AND ct.cost_type = 'Frozen'
      AND mts.transaction_source_type_name = 'Job or Schedule'
      AND ood.organization_code = :p_manufacturing_plant
      AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date AND :p_end_date
        • 1. Re: Column is summing up all the rows and displaying same value in all rows.
          AlbertoFaenza
          Hi,

          you posted a long query, did not provide any input data and did not even post a valid code.
          How do you expect us to help you?

          Please read SQL and PL/SQL FAQ

          Additionally when you put some code or output please enclose it between two lines starting with {noformat}
          {noformat}
          
          i.e.:
          {noformat}
          {noformat}
          SELECT ...
          {noformat}
          {noformat}
          
          
          
          Regards.
          Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: Column is summing up all the rows and displaying same value in all rows.
            Karthick_Arp
            I have a problem in this query to fetch the abs_qty

            when the parameter :p_item ie)msib.segment1 is not given as the input parameter. Its just summing up the entire quantity for all the items.

            When I pass :p_item ie)msib.segment1 as input parameter, It fetches the exact value for abs_qty. Kindly help me to resolve this.
            You have writern the code like this
            AND msib.segment1 = NVL (:p_item, msib.segment1)
            So if :p_item is empty then all records will be fetched. And if :p_itemp is passed only that rows will be fetched. So i guess its working properly.

            What is your actual question? I dont find a specific problem or a question in your post. I would suggest you to read {message:id=9360002} and post accordingly.
            • 3. Re: Column is summing up all the rows and displaying same value in all rows.
              Etbin
              If <tt>abs_qty</tt> and consequently <tt>abs_val</tt> should not be the same for all rows returned, you'll have to rewrite your query so that the <tt>abs_qty</tt> won't be computed in isolation (separately) with no interaction with data from the main from table list.
              Your query is treated the same as the one below (note the changed table aliases by appending numbers to the originals of yours)
              SELECT mmt.transaction_date date_produced,
                     msib.segment1 item,
                     cic.cost_type_id,
                     msib.description item_description,
                     mc.segment6 base_model,
                     mc.segment7 trade_brand,
                     mmt.subinventory_code subinventory,
                     mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 LOCATOR,
                     ood.organization_code || '-' || ood.organization_name manufacturing_plant,
                     mmt.transaction_quantity quantity_produced,
                     mtt.transaction_type_name transaction_type,
                     msib.inventory_item_id,
                     msib.organization_id,
                     cic.material_cost COST,
                     cic.material_overhead_cost freight,
                     csc.standard_cost,
                     (select (NVL(
                                  (SELECT SUM (mmt1.transaction_quantity) total_NET_qty
                                     FROM mtl_material_transactions mmt1,
                                          mtl_system_items_b msib1,
                                          mtl_transaction_types mtt1,
                                          org_organization_definitions ood1,
                                          mtl_txn_source_types mts1
                                    WHERE mmt1.organization_id = msib1.organization_id
                                      AND msib1.inventory_item_id = mmt1.inventory_item_id
                                      AND mmt1.transaction_type_id = mtt1.transaction_type_id
                                      AND mmt1.organization_id = ood1.organization_id
                                      AND mtt1.transaction_type_name = 'WIP Completion'
                                      AND mts1.transaction_source_type_name = 'Job or Schedule'
                                      AND msib1.segment1 = NVL (:p_item, msib1.segment1)
                                      AND ood1.organization_code = :p_manufacturing_plant
                                      AND TRUNC (mmt1.transaction_date) BETWEEN :p_start_date AND :p_end_date 
                                  ),0
                                 ) - NVL(
                                         (SELECT SUM (mmt2.transaction_quantity) total_NET_qty
                                            FROM mtl_material_transactions mmt2,
                                                 mtl_system_items_b msib2,
                                                 mtl_transaction_types mtt2,
                                                 org_organization_definitions ood2,
                                                 mtl_txn_source_types mts2
                                           WHERE mmt2.organization_id = msib2.organization_id
                                             AND msib2.inventory_item_id = mmt2.inventory_item_id
                                             AND mmt2.transaction_type_id = mtt2.transaction_type_id
                                             AND mmt2.organization_id = ood.2organization_id
                                             AND mtt2.transaction_type_name = 'WIP Issue'
                                             AND mts2.transaction_source_type_name = 'Job or Schedule'
                                             AND msib2.segment1 = NVL (:p_item, msib2.segment1)
                                             AND ood2.organization_code = :p_manufacturing_plant
                                             AND TRUNC (mmt.2transaction_date) BETWEEN :p_start_date AND :p_end_date
                                         ),0
                                        )
                             ) abs_qty
                        from dual
                     ) AS ABS_qtyy,
                     (select (NVL(
                                  (SELECT SUM (mmt3.transaction_quantity) total_NET_qty
                                     FROM mtl_material_transactions mmt3,
                                          mtl_system_items_b msib3,
                                          mtl_transaction_types mtt3,
                                          org_organization_definitions ood3,
                                          mtl_txn_source_types mts3
                                    WHERE mmt3.organization_id = msib3.organization_id
                                      AND msib3.inventory_item_id = mmt3.inventory_item_id
                                      AND mmt3.transaction_type_id = mtt3.transaction_type_id
                                      AND mmt3.organization_id = ood3.organization_id
                                      AND mtt3.transaction_type_name = 'WIP Completion'
                                      AND mts3.transaction_source_type_name = 'Job or Schedule'
                                      AND msib3.segment1 = NVL (:p_item, msib3.segment1)
                                      AND ood3.organization_code = :p_manufacturing_plant
                                      AND TRUNC (mmt3.transaction_date) BETWEEN :p_start_date AND :p_end_date
                                  ),0
                                 ) - NVL(
                                         (SELECT SUM (mmt4.transaction_quantity) total_NET_qty
                                            FROM mtl_material_transactions mmt4,
                                                 mtl_system_items_b msib4,
                                                 mtl_transaction_types mtt4,
                                                 org_organization_definitions ood4,
                                                 mtl_txn_source_types mts4
                                           WHERE mmt4.organization_id = msib4.organization_id
                                             AND msib4.inventory_item_id = mmt4.inventory_item_id
                                             AND mmt4.transaction_type_id = mtt4.transaction_type_id
                                             AND mmt4.organization_id = ood4.organization_id
                                             AND mtt4.transaction_type_name = 'WIP Issue'
                                             AND mts4.transaction_source_type_name = 'Job or Schedule'
                                             AND msib4.segment1 = NVL (:p_item, msib4.segment1)
                                             AND ood4.organization_code = :p_manufacturing_plant
                                             AND TRUNC (mmt4.transaction_date) BETWEEN :p_start_date AND :p_end_date
                                         ),0
                                        )
                             ) abs_qty
                        from dual
                     ) * csc.standard_cost AS abs_val
                FROM mtl_material_transactions mmt,
                     mtl_system_items_b msib,
                     mtl_item_locations mil,
                     mtl_transaction_types mtt,
                     org_organization_definitions ood,
                     mtl_txn_source_types mts,
                     mtl_categories mc,
                     mtl_item_categories mic,
                     mtl_category_sets mcs,
                     cst_item_costs cic,
                     cst_standard_costs csc,
                     cst_cost_types ct,
                     mfg_lookups mlo
               WHERE mmt.organization_id = msib.organization_id
                 AND msib.inventory_item_id = mmt.inventory_item_id
                 AND mmt.locator_id = mil.inventory_location_id
                 AND mil.organization_id = msib.organization_id
                 AND mil.subinventory_code = mmt.subinventory_code
                 AND mmt.transaction_type_id = mtt.transaction_type_id
                 AND msib.organization_id = ood.organization_id
                 AND mic.category_set_id = mcs.category_set_id
                 AND mc.category_id = mic.category_id
                 AND mc.structure_id = mcs.structure_id
                 AND mic.inventory_item_id = msib.inventory_item_id
                 AND mic.organization_id = msib.organization_id
                 AND mcs.category_set_id = 52487965
                 AND msib.inventory_item_id = cic.inventory_item_id
                 AND msib.organization_id = cic.organization_id
                 AND ct.cost_type_id = cic.cost_type_id
                 AND cic.organization_id = ood.organization_id
                 AND mil.inventory_location_id = mmt.locator_id
                 AND mmt.transaction_source_type_id = mtt.transaction_source_type_id
                 AND mmt.transaction_action_id = mtt.transaction_action_id
                 AND mmt.transaction_source_type_id = mts.transaction_source_type_id
                 AND mmt.transaction_action_id = mlo.lookup_code
                 AND mmt.inventory_item_id = csc.inventory_item_id
                 AND mmt.organization_id = csc.organization_id
                 AND csc.last_update_date >= (SELECT MAX (csc1.last_update_date)
                                                FROM cst_standard_costs csc1
                                               WHERE csc1.inventory_item_id = mmt.inventory_item_id
                                                 AND csc1.organization_id = mmt.organization_id
                                             )
                 AND msib.segment1 = NVL (:p_item, msib.segment1)
                 AND NVL (mc.segment6, 'X') = NVL (:p_base_model, NVL (mc.segment6, 'X'))
                 AND NVL (msib.planner_code, 'Y') = NVL (:p_planner_code, NVL (msib.planner_code, 'Y'))
                 AND UPPER (mlo.meaning) = 'ASSEMBLY COMPLETION'
                 AND mtt.transaction_type_name = 'WIP Completion'
                 AND ct.cost_type = 'Frozen'
                 AND mts.transaction_source_type_name = 'Job or Schedule'
                 AND ood.organization_code = :p_manufacturing_plant
                 AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date AND :p_end_date
              Regards

              Etbin