取投料数量

select sum(decode(mmt.transaction_action_id,
                   40,
                   mta.primary_quantity,
                   41,
                   mta.primary_quantity,
                   43,
                   mta.primary_quantity,
                   -mmt.transaction_quantity))
  --into x_comp_quantity
  from /*apps.wip_repetitive_items wri,*/ apps.wip_discrete_jobs wdj, /*apps.wip_lines wl,*/
       apps.bom_departments bd, apps.bom_resources br, apps.mtl_transaction_reasons mtr,
       /*apps.mtl_transaction_types mtt,*/ /*apps.mtl_system_items msi, apps.mfg_lookups lu2,*/
       apps.mtl_transaction_accounts mta, apps.mtl_material_transactions mmt,
       apps.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_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 mta.cost_element_id = 1
   and mtt.transaction_type_id in (35, 43)
   and exists
 (select 1
          from apps.mtl_material_transactions mmts, apps.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 mmt.inventory_item_id = 79;