Dear friends
I need to sum of the total fixed lead time in the BOM Hierarchy Query. As per the highlighted Fixed Lead time need to sum group by classification . as here in the example fixed lead time should be 13 lead time in seperate column,Please suggest.
Query
SELECT
ecn_v.id,
ecn_v.ecn_no,
ecn_v.item_type,
to_char(ecn_v.parent_item) parent_item,
( ecn_v.description ) parent_name,
ecn_v.prod_uom,
ecn_v.produced_qty,
ecn_v.consumed_qty,
round(ecn_v.consumed_qty / ecn_v.produced_qty, 7) ratio,
to_char(ecn_v.child_item) child_item,
ecn_v.child_description,
rpad('.',(level - 1) * 1, '.')
|| ecn_v.child_item AS "Level",
ecn_v.con_uom,
CONNECT_BY_ROOT ecn_v.parent_item AS fg_item,
CONNECT_BY_ROOT erp.salesorder_o_view.pending_qty AS pending,
CONNECT_BY_ROOT erp.salesorder_o_view.pending_qty * round(ecn_v.consumed_qty / ecn_v.produced_qty, 2) AS "Pend_Qty_Ratio",
CONNECT_BY_ROOT erp.salesorder_o_view.due_date AS duedate,
CONNECT_BY_ROOT erp.salesorder_o_view.pending_qty * round(ecn_v.consumed_qty / ecn_v.produced_qty, 2) / ( 1 - ( ecn_v.rejection
/ 100 ) ) AS "Morder_Qty",
ecn_v.fixed_lead_time,
ecn_v.variable_lead_time,
ecn_v.classification,
ecn_v.rejection
FROM
ecn_v,
erp.salesorder_o_view
WHERE
ecn_v.parent_item = erp.salesorder_o_view.item_code (+)
AND CONNECT_BY_ROOT erp.salesorder_o_view.pending_qty > 0
CONNECT BY
PRIOR ecn_v.child_item = ecn_v.parent_item
START WITH ecn_v.item_type = 'A'
Sanjay