- 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;
来源: http://www.bubuko.com/infodetail-3319444.html