This discussion is archived
3 Replies Latest reply: Feb 15, 2013 12:10 PM by Etbin RSS

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

988505 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points