COGS

select ou,item, itemname,
-- rcvOU,SENDOU,
sum(nvl(ACCOUNTED_DR,0)) ACCOUNTED_DR, sum(nvl(ACCOUNTED_CR,0)) ACCOUNTED_CR, (sum(nvl(ACCOUNTED_DR,0)) - sum(nvl(ACCOUNTED_CR,0))) total
from
(
select c.SEGMENT1 ou,i.SEGMENT1 item, i.DESCRIPTION itemname, a.AE_HEADER_ID,a.ENTITY_ID,b.CODE_COMBINATION_ID,b.ACCOUNTED_DR,b.ACCOUNTED_CR,d.SOURCE_ID_INT_1, u1.NAME rcvOU,
e.ORGANIZATION_ID,f.ORGANIZATION_NAME RCVORGNM,f.ORGANIZATION_CODE RCVDORG,
--u2.NAME SENDOU,
 e.TRANSFER_ORGANIZATION_ID--,g.ORGANIZATION_NAME SENDRORGNM,g.ORGANIZATION_CODE SENDRORG
--,  c.segment6
from xla_ae_headers a,xla_ae_lines b,gl_code_combinations c,xla.xla_transaction_entities d,mtl_material_transactions e,mtl_system_items_b i
,org_organization_definitions f--,org_organization_definitions g
, hr_operating_units u1--, hr_operating_units u2
where a.PERIOD_NAME = 'Nov-16-17' and a.APPLICATION_ID = 555 and a.AE_HEADER_ID = b.AE_HEADER_ID and a.APPLICATION_ID = b.APPLICATION_ID
and b.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID and c.SEGMENT1 ='105'--:p_ou
and c.segment6 = '124818'  -- COGS Account
and a.ENTITY_ID = d.ENTITY_ID and a.APPLICATION_ID = d.APPLICATION_ID
and d.SOURCE_ID_INT_1 = e.TRANSACTION_ID
and e.ORGANIZATION_ID = f.ORGANIZATION_ID --and i.SEGMENT1='44603'
--and e.TRANSFER_ORGANIZATION_ID = g.ORGANIZATION_ID
and f.OPERATING_UNIT=u1.ORGANIZATION_ID
and e.INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID and e.ORGANIZATION_ID=i.ORGANIZATION_ID
--and g.OPERATING_UNIT=u2.ORGANIZATION_ID
--and u2.NAME='PFL-HIP OU'
)
group by ou,item, itemname--, rcvOU,SENDOU
--order by segment1