Inter-Org Profit

select SEGMENT1, rcvOU,SENDOU, sum(ACCOUNTED_DR) ACCOUNTED_DR, sum(ACCOUNTED_CR) ACCOUNTED_CR, (sum(ACCOUNTED_DR) - sum(ACCOUNTED_CR)) total
from
(
select c.SEGMENT1, 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,
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 = '102'
and c.segment6 = '318201'
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 e.TRANSFER_ORGANIZATION_ID = g.ORGANIZATION_ID
and f.OPERATING_UNIT=u1.ORGANIZATION_ID
and g.OPERATING_UNIT=u2.ORGANIZATION_ID
--and u2.NAME='PFL-HIP OU'
)
group by SEGMENT1, rcvOU,SENDOU
order by segment1

 

--------------------SQL for  Inter Org Profit ------------------------------------------ 

select from_ou, to_ou, round(sum(deff_amount),0) intr_org_profit from (
select from_ou,  to_ou,  from_org, to_org, sum(nvl(amount,0)) TRANSFER_AMOUNT, sum(nvl(TRANSFER_PRICE_AMOUNT,0))  TRANSFER_PRICE_AMOUNT
, sum(nvl(amount,0)) - sum(nvl(TRANSFER_PRICE_AMOUNT,0)) deff_amount  from (              
 select fou.short_code from_ou, tou.short_code to_ou, forg.ORGANIZATION_CODE from_org, torg.ORGANIZATION_CODE to_org,  mtl.TRANSACTION_ID sndr_TRANSACTION_ID, rmtl.TRANSACTION_ID rev_TRANSACTION_ID, mtl.ORGANIZATION_ID sender_org,mtl.SUBINVENTORY_CODE sender_subinv, rmtl.ORGANIZATION_ID rev_org, rmtl.SUBINVENTORY_CODE rev_subinv,
mtl.TRANSACTION_TYPE_ID send_TRANSACTION_TYPE_ID, rmtl.TRANSACTION_TYPE_ID rev_TRANSACTION_TYPE_ID, mtl.TRANSACTION_QUANTITY sndr_TRANSACTION_QUANTITY, rmtl.TRANSACTION_QUANTITY rev_TRANSACTION_QUANTITY, mtl.TRANSACTION_DATE sndr_TRANSACTION_DATE, rmtl.TRANSACTION_DATE rev_TRANSACTION_DATE,
  mtl.INTRANSIT_ACCOUNT, rmtl.INTRANSIT_ACCOUNT, mtl.DISTRIBUTION_ACCOUNT_ID, rmtl.DISTRIBUTION_ACCOUNT_ID,sndrgcc.CONCATENATED_SEGMENTS , rcvgcc.CONCATENATED_SEGMENTS --  mtl.*
 , mtl.transfer_price , rmtl.transfer_price , nvl(mtl.TRANSACTION_QUANTITY,0) * nvl(ACCTG_COST,0) amount, nvl(mtl.TRANSACTION_QUANTITY,0) * nvl(rmtl.transfer_price,0) transfer_price_amount
 from mtl_material_transactions mtl, mtl_material_transactions rmtl , gl_code_combinations_kfv sndrgcc, gl_code_combinations_kfv rcvgcc, gl_item_cst cst
 ,org_organization_definitions forg,  org_organization_definitions torg, hr_operating_units fou, hr_operating_units tou
 where mtl.TRANSACTION_TYPE_ID=62
 and to_char(mtl.TRANSACTION_DATE,'YYMM')='2505' --and mtl.ORGANIZATION_ID =3950-- 3987
 and mtl.TRANSACTION_ID = rmtl.TRANSFER_TRANSACTION_ID(+) --and rmtl.transfer_TRANSACTION_ID is  null
-- and mtl.INTRANSIT_ACCOUNT <> rmtl.INTRANSIT_ACCOUNT
and mtl.INTRANSIT_ACCOUNT =sndrgcc.code_combination_id --segment6
and rmtl.DISTRIBUTION_ACCOUNT_ID =rcvgcc.code_combination_id
and mtl.ORGANIZATION_ID= cst.ORGANIZATION_ID and mtl.INVENTORY_ITEM_ID=cst.INVENTORY_ITEM_ID
and cst.PERIOD_ID=(select PERIOD_ID from gmf_period_statuses where upper(PERIOD_CODE) =upper('May-24-25'))
and mtl.ORGANIZATION_ID = forg.ORGANIZATION_ID  and mtl.TRANSFER_organization_id = torg.ORGANIZATION_ID
and forg.operating_unit= fou.ORGANIZATION_ID and torg.operating_unit= tou.ORGANIZATION_ID
and forg.operating_unit<> torg.operating_unit
) group by from_ou,  to_ou, from_org, to_org
) group by from_ou, to_ou
having round(sum(deff_amount),0)<>0