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

 

 ------MMT Tran to XLA Accounting for each trans type of 61 or 3 

 ----------------------------------------------------

select tv.ENTITY_CODE, xe.ENTITY_ID, mtl.TRANSACTION_ID, tv.VALUATION_ID,
xl.AE_HEADER_ID, gcc.CONCATENATED_SEGMENTS,tl.flex_value_meaning AS value_name,
        tv.JOURNAL_LINE_TYPE,tl.description, ACCTG_COST item_cost, TRANSACTION_QUANTITY,TRANSACTION_UOM,TXN_BASE_VALUE, ACCOUNTED_DR, ACCOUNTED_CR,(nvl(ACCOUNTED_DR,0)-nvl(ACCOUNTED_CR,0)) balance, transfer_price, itm.segment1 item_code, 
SOURCE_CODE,TRANSFER_SUBINVENTORY, xl.*, xe.*, mtl.* , tv.*, xh.*
from mtl_material_transactions mtl, mtl_system_items_b itm, org_organization_definitions org, hr_operating_units hu, gl_item_cst cst
, gmf_transaction_valuation tv, xla.xla_events xe ,  xla.xla_transaction_entities d, xla.xla_ae_headers xh, xla.xla_ae_lines xl, gl_code_combinations_kfv gcc
, fnd_flex_values v, fnd_flex_values_tl tl
 where mtl.transaction_date>=TO_DATE('01-10-25 00:00:00', 'DD-MM-RR HH24:MI:SS')
and mtl.transaction_date<=TO_DATE('05-10-25 23:59:59', 'DD-MM-RR HH24:MI:SS')  and mtl.TRANSACTION_TYPE_ID=3
and mtl.ORGANIZATION_ID=org.ORGANIZATION_ID and org.operating_unit=hu.ORGANIZATION_ID 
and mtl.ORGANIZATION_ID=itm.ORGANIZATION_ID and mtl.INVENTORY_ITEM_ID = itm.INVENTORY_ITEM_ID
--and itm.INVENTORY_ITEM_ID=5076651 --5203444 
--and mtl.ORGANIZATION_ID in  (7163,5323) 
and mtl.ORGANIZATION_ID=cst.ORGANIZATION_ID and mtl.INVENTORY_ITEM_ID = cst.INVENTORY_ITEM_ID and cst.PERIOD_ID=1886
and mtl.TRANSACTION_TYPE_ID = tv.TRANSACTION_TYPE_ID and mtl.TRANSACTION_ID = tv.TRANSACTION_ID 
and tv.EVENT_ID=xe.EVENT_ID  and tv.ENTITY_CODE=d.ENTITY_CODE
--and xe.APPLICATION_ID 
--and (mtl.TRANSACTION_ID = 1844139066 or TRANSFER_TRANSACTION_ID = 1844139066) -- 1844139067
and xe.APPLICATION_ID=d.APPLICATION_ID and xe.ENTITY_ID=d.ENTITY_ID
and SOURCE_ID_INT_1=mtl.TRANSACTION_ID --and SOURCE_ID_INT_1=TRANSACTION_NUMBER
and xe.APPLICATION_ID=xh.APPLICATION_ID
and xe.ENTITY_ID=xh.ENTITY_ID 
and xh.AE_HEADER_ID=xl.AE_HEADER_ID  and xh.APPLICATION_ID=xl.APPLICATION_ID
and xl.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and v.flex_value_id = tl.flex_value_id
and tl.language = 'US'
AND v.flex_value = TRIM(gcc.segment6) --AND gcc.language = 'US'
AND     v.flex_value_set_id = 1014876
--and xe.event_id = d.source_event_id
and xh.event_id = xe.event_id
and nvl(ACCOUNTED_DR,0) - nvl(ACCOUNTED_CR,0)<>0
;


select sender_ou,rcv_ou,CONCATENATED_SEGMENTS,sum(nvl(ACCOUNTED_DR,0)) ACCOUNTED_DR,sum(nvl(ACCOUNTED_CR,0)) ACCOUNTED_CR,sum(nvl(BALANCE,0)) BALANCE from(
select fou.SHORT_CODE sender_ou,tou.SHORT_CODE rcv_ou, --  mtl.*, -- xe.ENTITY_ID, xe.EVENT_ID, xe.EVENT_NUMBER, xe.EVENT_STATUS_CODE,
 mtl.transaction_id,mtl.transfer_transaction_id, mtl.SUBINVENTORY_CODE, mtl.TRANSFER_SUBINVENTORY, gcc.CONCATENATED_SEGMENTS,tl.flex_value_meaning AS value_name,AE_LINE_NUM,ACCOUNTING_CLASS_CODE, ACCOUNTED_DR, ACCOUNTED_CR,(nvl(ACCOUNTED_DR,0)-nvl(ACCOUNTED_CR,0)) balance
 --, xl.* , xh.* 
from xla.xla_events xe , xla.xla_ae_headers xh, xla.xla_ae_lines xl, gl_code_combinations_kfv gcc , fnd_flex_values v, fnd_flex_values_tl tl
, xla.xla_transaction_entities xte, mtl_material_transactions mtl, org_organization_definitions forg, org_organization_definitions torg, hr_operating_units fou, hr_operating_units tou
 where xh.PERIOD_NAME='Oct-25-26' 
 and  xh.AE_HEADER_ID= xl.AE_HEADER_ID 
-- and xh.AE_HEADER_ID in (550645260, 547211932)
and xl.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and v.flex_value_id = tl.flex_value_id
and tl.language = 'US'
AND v.flex_value = TRIM(gcc.segment6) --AND gcc.language = 'US' xe.EVENT_TYPE_CODE='P' -- for Post to GL
AND     v.flex_value_set_id = 1014876
and nvl(ACCOUNTED_DR,0) - nvl(ACCOUNTED_CR,0)<>0
and xe.APPLICATION_ID=xh.APPLICATION_ID
and xe.ENTITY_ID=xh.ENTITY_ID 
and xe.APPLICATION_ID=xte.APPLICATION_ID
and xe.ENTITY_ID=xte.ENTITY_ID 
and xte.APPLICATION_ID =555 and xte.SOURCE_ID_INT_1 =mtl.TRANSACTION_ID 
--and xte.SOURCE_ID_INT_1 in( 1844139067 ,1844139066)
and xte.ENTITY_CODE='INVENTORY'
and mtl.transaction_date>=TO_DATE('01-10-25 00:00:00', 'DD-MM-RR HH24:MI:SS')
and mtl.transaction_date<=TO_DATE('31-10-25 23:59:59', 'DD-MM-RR HH24:MI:SS')  and mtl.TRANSACTION_TYPE_ID=3
and mtl.ORGANIZATION_ID= forg.ORGANIZATION_ID and forg.OPERATING_UNIT=fou.ORGANIZATION_ID
and mtl.TRANSFER_ORGANIZATION_ID= torg.ORGANIZATION_ID and torg.OPERATING_UNIT=tou.ORGANIZATION_ID
and fou.SHORT_CODE <>tou.SHORT_CODE
order by mtl.transaction_id, mtl.TRANSFER_TRANSACTION_ID
) where  SENDER_OU<> RCV_OU
group by sender_ou,rcv_ou,CONCATENATED_SEGMENTS;


select distinct job_no from tasks where assignee='47262'


select * from fnd_user where USER_NAME='345064' --390905' 


select * from gmd_results 

edit  tasks where JOB_NO='SLHP-25-60' and task_dept='RTR' --TASK_NAME like '%Mil%'

select * from XXPRG_BU_PERMISSION 



--and mtl.TRANSACTION_ID in (1828948731,1828950586,1828950587,1828953197)

select * from xla.xla_transaction_entities xte where APPLICATION_ID=555 and xte.SOURCE_ID_INT_1 in( 1844139067 ,1844139066)

--and mtl.transaction_date>=TO_DATE('01-10-25 00:00:00', 'DD-MM-RR HH24:MI:SS')
--and mtl.transaction_date<=TO_DATE('10-10-25 23:59:59', 'DD-MM-RR HH24:MI:SS')  and mtl.TRANSACTION_TYPE_ID=3
--and xe.event_id = xte.event_id  APPLICATION_ID, SOURCE_ID_INT_1


SELECT * --tablespace_name, SUM(used_blocks)*8/1024 MB_used,
      -- SUM(free_blocks)*8/1024 MB_free
FROM v$sort_segment

GROUP BY tablespace_name;


select * from t_prod_info where trunc(CREATED_DATE)='17-NOV-25' -- and CREATED_DATE<

select * from gl_code_combinations_kfv gcc where CONCATENATED_SEGMENTS='551-151-127-0000-00000-111361-000-000-00000-00000'

 select * from xla.xla_transaction_entities d where   APPLICATION_ID = 555  and SOURCE_ID_INT_1 in (1844139066,1844139067) 
--and TRANSACTION_NUMBER =1844139066
 
 ENTITY_ID in (550645260,546097421)

SELECT 
    xl.ae_line_num,
    xl.ae_header_id,
    gcc.concatenated_segments,
    tl.flex_value_meaning AS value_name,
    tl.description,
    xl.accounted_dr,
    xl.accounted_cr,
    mtl.transaction_id,
    itm.segment1 item_code,
    cst.acctg_cost item_cost,
    mtl.transaction_quantity,
    mtl.transaction_uom,
    mtl.source_code
FROM mtl_material_transactions mtl
JOIN gmf_transaction_valuation tv
       ON tv.transaction_id = mtl.transaction_id
JOIN xla.xla_events xe
       ON xe.event_id = tv.event_id
JOIN xla.xla_ae_headers xh
       ON xh.event_id = xe.event_id
JOIN xla.xla_ae_lines xl
       ON xl.ae_header_id = xh.ae_header_id
JOIN gl_code_combinations_kfv gcc
       ON gcc.code_combination_id = xl.code_combination_id
--      AND gcc.language = 'US'
LEFT JOIN fnd_flex_values v
       ON TRIM(gcc.segment6) = v.flex_value
      AND v.flex_value_set_id = 1014876
LEFT JOIN fnd_flex_values_tl tl
       ON tl.flex_value_id = v.flex_value_id
      AND tl.language = 'US'
JOIN mtl_system_items_b itm
       ON mtl.inventory_item_id = itm.inventory_item_id
      AND mtl.organization_id = itm.organization_id
JOIN gl_item_cst cst
       ON mtl.inventory_item_id = cst.inventory_item_id
      AND mtl.organization_id = cst.organization_id
      AND cst.period_id = 1886
WHERE mtl.transaction_type_id = 3
AND (mtl.transaction_id = 1844139066 OR mtl.transfer_transaction_id = 1844139066)
AND mtl.transaction_date BETWEEN
      TO_DATE('01-10-25 00:00:00','DD-MM-RR HH24:MI:SS')
  AND TO_DATE('31-10-25 23:59:59','DD-MM-RR HH24:MI:SS');



select * from xla.xla_transaction_entities 

--and TRANSACTION_QUANTITY


SELECT  v.flex_value,
        tl.flex_value_meaning AS value_name,
        tl.description
FROM    fnd_flex_values v
JOIN    fnd_flex_values_tl tl
       ON v.flex_value_id = tl.flex_value_id
WHERE   tl.language = 'US'
AND     v.flex_value IN ('318201','126502','121001','216200','121002')
AND     v.flex_value_set_id = 1014876;


select * from xla.xla_transaction_entities d

select * from xla_ae_headers where ENTITY_ID=546097421 and APPLICATION_ID=555

select * from xla_ae_lines where AE_HEADER_ID=550645260 and APPLICATION_ID=555


select d.*, xe.* from  xla.xla_events xe, xla.xla_transaction_entities d-- ,xla_ae_headers xh
where xe.APPLICATION_ID=d.APPLICATION_ID and xe.ENTITY_ID=d.ENTITY_ID and xe.ENTITY_ID=546097421
--and d.AE_HEADER_ID = xh.AE_HEADER_ID
EVENT_ID
--and SOURCE_ID_INT_1=TRANSACTION_NUMBER
TRANSACTION_NUMBER
546118276

select * from xla_ae_lines

-- select * from XXCUST.T_DEPOT_DASHBOARD 

 

 

---------------MMT Tran to XLA Accounting for each trans type of 61 or 3  ---- --------------- 

/* Formatted on 22/Nov/25 9:52:35 AM (QP5 v5.371) */
  SELECT SEGMENT1,
         rcvOU,
         SENDOU,
         concatenated_segments,
         value_name,
         description,
         SUM (ACCOUNTED_DR)                                              ACCOUNTED_DR,
         SUM (ACCOUNTED_CR)                                              ACCOUNTED_CR,
         (SUM (NVL (ACCOUNTED_DR, 0)) - SUM (NVL (ACCOUNTED_CR, 0)))     total
    FROM (SELECT c.SEGMENT1,
                 a.AE_HEADER_ID,
                 a.ENTITY_ID,
                 b.CODE_COMBINATION_ID,
                 concatenated_segments,
                 tl.flex_value_meaning
                     AS value_name,
                 tl.description,
                 b.ACCOUNTED_DR,
                 b.ACCOUNTED_CR,
                 (NVL (b.ACCOUNTED_DR, 0) - NVL (b.ACCOUNTED_CR, 0))
                     amnt,
                 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_kfv    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,
                 fnd_flex_values             v,
                 fnd_flex_values_tl          tl
           WHERE     a.PERIOD_NAME = 'Jul-25-26'
                 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 = '561'
                 --and c.segment6 = '318201'
                 AND e.TRANSACTION_TYPE_ID = 61     -- ir/iso, 3 Direct transf
                 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 (NVL (b.ACCOUNTED_DR, 0) - NVL (b.ACCOUNTED_CR, 0)) <> 0
                 AND v.flex_value = TRIM (c.segment6)
                 AND v.flex_value_set_id = 1014876
                 AND v.flex_value_id = tl.flex_value_id
                 AND tl.language = 'US'--and u2.NAME='PFL-HIP OU'
                                       )
GROUP BY SEGMENT1,
         rcvOU,
         SENDOU,
         concatenated_segments,
         value_name,
         description
ORDER BY segment1 

 

-------------------------------------------------

select SEGMENT1, rcvOU,SENDOU,concatenated_segments,value_name, description, sum(ACCOUNTED_DR) ACCOUNTED_DR, sum(ACCOUNTED_CR) ACCOUNTED_CR, (sum(nvl(ACCOUNTED_DR,0)) - sum(nvl(ACCOUNTED_CR,0))) total from
(
select c.SEGMENT1, a.AE_HEADER_ID,a.ENTITY_ID,b.CODE_COMBINATION_ID,concatenated_segments,tl.flex_value_meaning AS value_name,
        tl.description,  b.ACCOUNTED_DR,b.ACCOUNTED_CR,(nvl(b.ACCOUNTED_DR,0) - nvl(b.ACCOUNTED_CR,0)) amnt, 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_kfv 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
, fnd_flex_values v, fnd_flex_values_tl tl      
where a.PERIOD_NAME = 'Jul-25-26' 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 = '561' 
--and c.segment6 = '318201'
 AND e.TRANSACTION_TYPE_ID =61 -- ir/iso, 3 Direct transf
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 (nvl(b.ACCOUNTED_DR,0) - nvl(b.ACCOUNTED_CR,0))<>0
and v.flex_value = TRIM(c.segment6)
      AND v.flex_value_set_id = 1014876
      and v.flex_value_id = tl.flex_value_id
      AND tl.language = 'US'
--and u2.NAME='PFL-HIP OU'
)
group by SEGMENT1, rcvOU,SENDOU,concatenated_segments,value_name, description
order by segment1;


select * from tasks where  ASSIGNEE='423491'--192968'

----- at Sub Ledger Level-------Int org profit -------
/* Formatted on 02/Jun/25 7:43:19 PM (QP5 v5.371) */
  SELECT                               --e.transaction_id, d.transaction_date,
         fou.SHORT_CODE    from_ou,
         tou.SHORT_CODE    to_ou,
         ROUND (
             SUM (
                 (  NVL (e.TRANSACTION_QUANTITY, 0) * NVL (e.TRANSFER_PRICE, 0)
                  - NVL (e.TRANSACTION_QUANTITY, 0) * NVL (ACCTG_COST, 0))),
             2)            int_org_profit
    --, TRANSACTION_QUANTITY*TRANSFER_PRICE, TRANSACTION_QUANTITY*ACCTG_COST trnsf ,  e.*
    FROM mtl_material_transactions   d,
         mtl_material_transactions   e,
         gl_item_cst                 cst,
         org_organization_definitions forg,
         hr_operating_units          fou,
         org_organization_definitions torg,
         hr_operating_units          tou
   WHERE     e.TRANSFER_ORGANIZATION_ID = cst.ORGANIZATION_ID
         AND e.INVENTORY_ITEM_ID = cst.INVENTORY_ITEM_ID
         AND period_id =
             (--181
              SELECT period_id
                FROM gmf_period_statuses p
               WHERE d.transaction_date BETWEEN p.start_date AND p.END_DATE)
         AND e.TRANSACTION_TYPE_ID = 61
         AND e.TRANSFER_TRANSACTION_ID = d.TRANSACTION_ID --and d.transaction_date
         AND e.TRANSACTION_DATE >=
             TO_DATE ('01-05-25 00:00:00', 'dd-mm-yy ss:mi:hh24') --hh24:mi:ss')
         AND e.TRANSACTION_DATE <=
             TO_DATE ('31-05-25 59:59:23', 'dd-mm-yy ss:mi:hh24')
         AND e.TRANSFER_ORGANIZATION_ID = forg.ORGANIZATION_ID
         AND forg.operating_unit = fou.ORGANIZATION_ID --and fou.SHORT_CODE='102'
         AND e.SOURCE_CODE = 'RCV'
         AND e.ORGANIZATION_ID = torg.ORGANIZATION_ID
         AND torg.operating_unit = tou.ORGANIZATION_ID
         AND fou.SHORT_CODE <> tou.SHORT_CODE
  HAVING ROUND (
             SUM (
                 (  NVL (e.TRANSACTION_QUANTITY, 0) * NVL (e.TRANSFER_PRICE, 0)
                  - NVL (e.TRANSACTION_QUANTITY, 0) * NVL (ACCTG_COST, 0))),
             2) <>
         0
--  ((nvl(e.TRANSACTION_QUANTITY,0)*nvl(e.TRANSFER_PRICE,0)- nvl(e.TRANSACTION_QUANTITY,0)*nvl(ACCTG_COST,0)))<>0
GROUP BY fou.SHORT_CODE, tou.SHORT_CODE