取投料成本

Select Sum(nvl(mta.base_transaction_value, 0)) base_transaction_value
               From /*wip_repetitive_items wri,*/ bi_ods.ods_ebs_wip_discrete_jobs wdj,
                    bi_ods.ods_ebs_wip_lines wl, bi_ods.ods_ebs_bom_departments bd, bi_ods.ods_ebs_bom_resources br,
                    bi_ods.ods_ebs_mtl_transaction_reasons mtr, bi_ods.ods_ebs_mtl_transaction_types  mtt,
                    bi_ods.ods_ebs_mtl_system_items_b msi, bi_ods.ods_ebs_mfg_lookups lu2,
                    bi_ods.ods_ebs_mtl_transaction_accounts mta,
                    bi_ods.ods_ebs_mtl_material_transactions mmt, bi_ods.ods_ebs_wip_entities we
              Where mta.transaction_source_id = we.wip_entity_id
                And mmt.organization_id = we.organization_id
                And mtt.transaction_type_id = mmt.transaction_type_id
                And mtr.reason_id(+) = mmt.reason_id
                And bd.department_id(+) = mmt.department_id
                   /*AND mta.transaction_source_type_id = 5
                   AND mmt.transaction_source_type_id = 5*/
                And mta.transaction_id = mmt.transaction_id
                And lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
                And lu2.lookup_code = mta.accounting_line_type
                And msi.inventory_item_id = mmt.inventory_item_id
                And msi.organization_id = mta.organization_id
                And br.resource_id(+) = mta.resource_id
                And wl.line_id(+) = mmt.repetitive_line_id
                And wdj.wip_entity_id(+) = mta.transaction_source_id
                --And wri.wip_entity_id(+) = mta.transaction_source_id
                --And wri.line_id(+) = mta.transaction_source_id
                And mta.accounting_line_type = 7
                And mtt.transaction_type_id In (35, 43)
                And Exists
              (Select 1
                       From bi_ods.ods_ebs_mtl_material_transactions mmts, bi_ods.ods_ebs_wip_entities wes
                      Where mmts.transaction_source_id = wes.wip_entity_id
                        And mmts.organization_id = wes.organization_id
                        And mmts.transaction_type_id = 44
                        And mmts.organization_id = 87
                        And wes.primary_item_id = 128015
                        And we.wip_entity_name = wes.wip_entity_name
                        And mmts.transaction_date Between TO_DATE('2019/3/29','yyyy-mm-dd') And
                            to_date('2019/4/28 23:59:59','yyyy-mm-dd hh24:mi:ss'))
                And mmt.organization_id = 87
                And wdj.primary_item_id = 128015
                And wdj.class_code = 'Assembly'
                --And we.wip_entity_name =
                    --nvl(p_wip_entity_name, we.wip_entity_name)
                And mmt.inventory_item_id = 79
                And mta.cost_element_id = 1