CST Periodic Inventory Value

with
q_ic_main as
(
select
 cpic.cost_group_id,
 cpic.pac_period_id period_id,
 decode(:p_sort_option,2 , mc.concatenated_segments,msi.concatenated_segments) ic_order,
 ccg.cost_group cost_group,
 msi.inventory_item_id ic_item_id_p,
 mc.padded_concatenated_segments ic_cat_pseg,
 msi.padded_concatenated_segments ic_item_pseg,
 mc.concatenated_segments ic_category_segment,
 msi.concatenated_segments ic_item_segment,
 msi.description ic_description,
 msi.primary_uom_code ic_uom,
 round(nvl(cpic.total_layer_quantity,0),:p_qty_precision) ic_qty,
 round((nvl(cpic.item_cost,0)* :p_exchange_rate),:p_ext_prec) ic_unit_cost,
 round((nvl(cpic.item_cost,0)* :p_exchange_rate),:p_precision) ic_stdunit_cost,
 nvl(cpic.item_cost,0)*nvl(cpic.total_layer_quantity,0)*:p_exchange_rate ic_total_cost,
 round((nvl(cpic.material_cost,0)* :p_exchange_rate),:p_ext_prec) ic_matl_cost,
 round((nvl(cpic.material_overhead_cost,0)* :p_exchange_rate),:p_ext_prec) ic_mo_cost,
 round((nvl(cpic.resource_cost,0)* :p_exchange_rate),:p_ext_prec) ic_res_cost,
 round((nvl(cpic.overhead_cost,0)* :p_exchange_rate),:p_ext_prec) ic_ovhd_cost,
 round((nvl(cpic.outside_processing_cost,0)* :p_exchange_rate),:p_ext_prec) ic_osp_cost,
 nvl(cpic.material_cost,0)*nvl(cpic.total_layer_quantity,0)*:p_exchange_rate ic_tot_matl_cost,
 nvl(cpic.material_overhead_cost,0)*nvl(cpic.total_layer_quantity,0)*:p_exchange_rate ic_tot_mo_cost,
 nvl(cpic.resource_cost,0)*nvl(cpic.total_layer_quantity,0)*:p_exchange_rate ic_tot_res_cost,
 nvl(cpic.overhead_cost,0)*nvl(cpic.total_layer_quantity,0)*:p_exchange_rate ic_tot_ovhd_cost,
 nvl(cpic.outside_processing_cost,0)*nvl(cpic.total_layer_quantity,0)*:p_exchange_rate ic_tot_osp_cost,
 1 ic_count,
 bom_cstrpicr_xmlp_pkg.ic_total_cost_rformula(nvl(cpic.item_cost,0) * nvl(cpic.total_layer_quantity,0) * :p_exchange_rate) ic_total_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_res_cost_rformula(round((nvl(cpic.resource_cost,0) * :p_exchange_rate) , :p_ext_prec)) ic_res_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_osp_cost_rformula(round((nvl(cpic.outside_processing_cost,0) * :p_exchange_rate),:p_ext_prec)) ic_osp_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_ovhd_cost_rformula(round((nvl(cpic.overhead_cost,0) * :p_exchange_rate),:p_ext_prec)) ic_ovhd_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_tot_osp_cost_rformula(nvl(cpic.outside_processing_cost,0) * nvl(cpic.total_layer_quantity,0) * :p_exchange_rate) ic_tot_osp_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_tot_ovhd_cost_rformula(nvl(cpic.overhead_cost,0) * nvl(cpic.total_layer_quantity,0) * :p_exchange_rate) ic_tot_ovhd_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_matl_cost_rformula(round((nvl(cpic.material_cost,0) * :p_exchange_rate ), :p_ext_prec)) ic_matl_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_tot_res_cost_rformula(nvl(cpic.resource_cost,0) * nvl(cpic.total_layer_quantity,0) * :p_exchange_rate) ic_tot_res_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_mo_cost_rformula(round((nvl( cpic.material_overhead_cost,0) * :p_exchange_rate ),:p_ext_prec)) ic_mo_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_tot_matl_cost_rformula(nvl(cpic.material_cost,0) * nvl(cpic.total_layer_quantity,0) * :p_exchange_rate) ic_tot_matl_cost_r,
 bom_cstrpicr_xmlp_pkg.ic_tot_mo_cost_rformula(nvl(cpic.material_overhead_cost,0) * nvl(cpic.total_layer_quantity,0) * :p_exchange_rate) ic_tot_mo_cost_r
from
 mtl_item_categories mic,
 mtl_categories_kfv mc,
 mtl_system_items_vl msi,
 cst_pac_item_costs cpic,
 cst_cost_groups ccg
where
 1=1 and
 cpic.pac_period_id = :p_period_id and
 cpic.inventory_item_id = mic.inventory_item_id and
 mic.organization_id = :p_item_master_org_id and
 mic.category_set_id = :p_category_set and
 mc.category_id = mic.category_id and
 msi.inventory_item_id = mic.inventory_item_id and
 msi.organization_id = mic.organization_id and
 cpic.cost_group_id = ccg.cost_group_id
),
q_ic_rcv as
(
select
 cprvv.cost_group_id,
 cpp.pac_period_id period_id,
 cprvv.rcv_transaction_id ic_txn_id,
 cpp.pac_period_id ic_pac_period,
 cpp.cost_type_id ic_cost_type,
 cpp.period_start_date ic_start_date,
 cpp.period_end_date ic_end_date,
 msi.segment1 ic_item_num,
 mic.category_id ic_category,
 cprvv.receipt_date ic_receipt_date,
 cprvv.receipt_num ic_receipt_num,
 cprvv.shipment_num ic_shipment_num,
 hr.location_code ic_current_location,
 hr2.location_code ic_deliver_to_location,
 cprvv.packing_slip ic_packing_slip,
 pol.displayed_field ic_document_type,
 cprvv.source_document ic_document_number,
 cprvv.document_line_num ic_document_line_num,
 cstppacq.get_net_undel_qty(cprvv.rcv_transaction_id, (cpp.period_end_date + 1 )) ic_quantity,
 cprvv.acquisition_cost ic_acquisition_cost,
 cstppacq.get_net_undel_qty(cprvv.rcv_transaction_id, (cpp.period_end_date +1)) *cprvv.acquisition_cost ic_value,
 cprvv.txn_unit_of_measure ic_uom_code,
 cprvv.item_revision ic_revision,
 cprvv.item_description ic_description,
 msi.inventory_item_id ic_item_id_c,
 bom_cstrpicr_xmlp_pkg.ic_value_rformula(cstppacq.get_net_undel_qty ( cprvv.rcv_transaction_id , ( cpp.period_end_date + 1 ) ) * cprvv.acquisition_cost) ic_value_r,
 bom_cstrpicr_xmlp_pkg.ic_acquisition_cost_rformula(cprvv.acquisition_cost) ic_acquisition_cost_r
from
 cst_pac_receiving_values_v cprvv,
 mtl_system_items msi,
 mtl_item_categories mic,
 mtl_categories mc,
 hr_locations hr,
 hr_locations hr2,
 po_lookup_codes pol,
 cst_pac_periods cpp,
 po_vendors pov
where
 2=2 and
 cprvv.location_id = hr.location_id (+) and
 cprvv.deliver_to_location_id = hr2.location_id (+) and
 mic.organization_id(+) = cprvv.txn_organization_id and
 mic.inventory_item_id(+) = cprvv.item_id and
 mc.category_id(+) = mic.category_id and
 pol.lookup_type = 'DOCUMENT TYPE' and
 pol.lookup_code = cprvv.source_document_code and
 pov.vendor_id(+) = cprvv.vendor_id and
 cpp.legal_entity = cprvv.legal_entity and
 cprvv.transaction_date >= cpp.period_start_date and
 cprvv.transaction_date < (cpp.period_end_date + 1 ) and
 msi.inventory_item_id = cprvv.item_id and
 msi.organization_id = cprvv.txn_organization_id and
 cpp.pac_period_id = :p_period_id and
 mic.category_set_id = :p_category_set
)
--
-- Main Query Starts Here
--
select
 :p_legal_entity legal_entity,
 :p_cost_type cost_type,
 :p_period_name period,
 :p_currency_dsp currency,
 :p_exchange_rate exchange_rate,
 x.cost_group org_cost_group,
 x.item,
 x.description,
 x.category,
 x.uom,
 case when x.row_num = 1 then x.onhand_quantity end onhand_quantity,
 case when x.row_num = 1 then x.onhand_unit_cost end onhand_unit_cost,
 case when x.row_num = 1 then x.onhand_value end onhand_value,
 case when x.row_num = 1 then x.rcv_quantity end rcv_quantity,
 case when x.row_num = 1 then x.rcv_avg_acquisition_cost end rcv_avg_acquisition_cost,
 case when x.row_num = 1 then x.rcv_value end rcv_value,
 case when x.row_num = 1 then x.total_value end total_value,
 --
 case when x.row_num = 1 then x.material_cost end material_cost,
 case when x.row_num = 1 then x.material_value end material_value,
 case when x.row_num = 1 then x.material_overhead_cost end material_overhead_cost,
 case when x.row_num = 1 then x.material_overhead_value end material_overhead_value,
 case when x.row_num = 1 then x.resource_cost end resource_cost,
 case when x.row_num = 1 then x.resource_value end resource_value,
 case when x.row_num = 1 then x.outside_processing_cost end outside_processing_cost,
 case when x.row_num = 1 then x.outside_processing_value end outside_processing_value,
 case when x.row_num = 1 then x.overhead_cost end overhead_cost,
 case when x.row_num = 1 then x.overhead_value end overhead_value,
 case when x.row_num = 1 then x.cost_elements_total_cost end cost_elements_total_cost,
 case when x.row_num = 1 then x.cost_elements_total_value end cost_elements_total_value,
 --q_ic_rcv.*
 x.receipt_date,
 x.receipt_number,
 x.shipment_number,
 x.current_location,
 x.deliver_to_location,
 x.packing_slip,
 x.document_type,
 x.document_number,
 x.document_line_number,
 x.receipt_qty,
 x.acquisition_cost,
 x.value,
 --
 x.ic_txn_id,
 x.item_id,
 x.row_num item_seq
from
(
select /*+ push_pred(q_ic_rcv) */
 --q_ic_main.*,
 q_ic_main.cost_group cost_group,
 q_ic_main.ic_item_segment item,
 q_ic_main.ic_description description,
 q_ic_main.ic_category_segment category,
 q_ic_main.ic_uom uom,
 q_ic_main.ic_qty onhand_quantity,
 q_ic_main.ic_stdunit_cost onhand_unit_cost,
 q_ic_main.ic_total_cost_r onhand_value,
 sum(q_ic_rcv.ic_quantity) over (partition by q_ic_main.ic_item_id_p) rcv_quantity,
 avg(q_ic_rcv.ic_acquisition_cost) over (partition by q_ic_main.ic_item_id_p) rcv_avg_acquisition_cost,
 case
 when sum(q_ic_rcv.ic_quantity) over (partition by q_ic_main.ic_item_id_p) is not null
 and  avg(q_ic_rcv.ic_acquisition_cost) over (partition by q_ic_main.ic_item_id_p) is not null
 then round( (  sum(q_ic_rcv.ic_quantity) over (partition by q_ic_main.ic_item_id_p)
              * avg(q_ic_rcv.ic_acquisition_cost) over (partition by q_ic_main.ic_item_id_p)
             ) / :round_unit
           ) * :round_unit
 end  rcv_value,
 case
 when sum(q_ic_rcv.ic_quantity) over (partition by q_ic_main.ic_item_id_p) is not null
 and  avg(q_ic_rcv.ic_acquisition_cost) over (partition by q_ic_main.ic_item_id_p) is not null
 then round( ( (  sum(q_ic_rcv.ic_quantity) over (partition by q_ic_main.ic_item_id_p)
                * avg(q_ic_rcv.ic_acquisition_cost) over (partition by q_ic_main.ic_item_id_p)
               ) +
               q_ic_main.ic_total_cost_r
             ) / :round_unit
           ) * :round_unit
 else round(q_ic_main.ic_total_cost_r / :round_unit) * :round_unit
 end  total_value,
 --
 q_ic_main.ic_matl_cost material_cost,
 q_ic_main.ic_tot_matl_cost_r material_value,
 q_ic_main.ic_mo_cost material_overhead_cost,
 q_ic_main.ic_tot_mo_cost_r material_overhead_value,
 q_ic_main.ic_res_cost resource_cost,
 q_ic_main.ic_tot_res_cost_r resource_value,
 q_ic_main.ic_osp_cost outside_processing_cost,
 q_ic_main.ic_osp_cost_r outside_processing_value,
 q_ic_main.ic_ovhd_cost overhead_cost,
 q_ic_main.ic_ovhd_cost_r overhead_value,
 q_ic_main.ic_unit_cost cost_elements_total_cost,
 q_ic_main.ic_total_cost_r cost_elements_total_value,
 --
 q_ic_main.ic_item_id_p item_id,
 q_ic_main.ic_order,
 row_number() over (partition by ic_item_id_p order by q_ic_rcv.ic_txn_id) row_num,
 --q_ic_rcv.*
 q_ic_rcv.ic_receipt_date receipt_date,
 q_ic_rcv.ic_receipt_num receipt_number,
 q_ic_rcv.ic_shipment_num shipment_number,
 q_ic_rcv.ic_current_location current_location,
 q_ic_rcv.ic_deliver_to_location deliver_to_location,
 q_ic_rcv.ic_packing_slip packing_slip,
 q_ic_rcv.ic_document_type document_type,
 q_ic_rcv.ic_document_number document_number,
 q_ic_rcv.ic_document_line_num document_line_number,
 q_ic_rcv.ic_quantity receipt_qty,
 q_ic_rcv.ic_acquisition_cost_r acquisition_cost,
 q_ic_rcv.ic_value_r value,
 q_ic_rcv.ic_txn_id,
 --
 q_ic_rcv.ic_pac_period,
 q_ic_rcv.ic_cost_type,
 q_ic_rcv.ic_start_date,
 q_ic_rcv.ic_end_date,
 q_ic_rcv.ic_item_num,
 q_ic_rcv.ic_category,
 q_ic_rcv.ic_uom_code,
 q_ic_rcv.ic_revision,
 q_ic_rcv.ic_description,
 q_ic_rcv.ic_acquisition_cost,
 q_ic_rcv.ic_value
from
q_ic_main,
q_ic_rcv
where
q_ic_main.ic_item_id_p = q_ic_rcv.ic_item_id_c (+) and
q_ic_main.cost_group_id = q_ic_rcv.cost_group_id (+) and
q_ic_main.period_id = q_ic_rcv.period_id (+)
) x
order by
x.ic_order,
x.item,
x.row_num,
x.ic_txn_id