COGS

COGS in Multiple code :

CREATE TABLE APPS.XXPRG_COGS_DATA
(
  PERIOD_SLNO          VARCHAR2(6 BYTE),
  PERIOD_NAME          VARCHAR2(15 BYTE),
  OU                   VARCHAR2(25 BYTE),
  INVENTORY_ITEM_ID    NUMBER                   NOT NULL,
  ITEM                 VARCHAR2(40 BYTE),
  ITEMNAME             VARCHAR2(240 BYTE),
  ACCOUNTED_DR         NUMBER,
  ACCOUNTED_CR         NUMBER,
  CODE_COMBINATION_ID  NUMBER(15)               NOT NULL,
  RCVOU                VARCHAR2(240 BYTE)       NOT NULL,
  RCVORGNM             VARCHAR2(240 BYTE)       NOT NULL,
  RCVDORG              VARCHAR2(3 BYTE)
)



 insert into XXPRG_COGS_DATA ( OU,PERIOD_SLNO, PERIOD_NAME, INVENTORY_ITEM_ID, ITEM,ITEMNAME,ACCOUNTED_DR, ACCOUNTED_CR, CODE_COMBINATION_ID, RCVOU, RCVORGNM, RCVDORG) 
    select    c.segment1 ou, to_char(e.TRANSACTION_DATE, 'YYYYMM') period_SLNO, ah.PERIOD_NAME, i.INVENTORY_ITEM_ID,i.segment1 item, i.description itemname, sum(nvl(b.accounted_dr,0)) accounted_dr, sum(nvl(b.accounted_cr,0)) accounted_cr, b.code_combination_id, u1.NAME rcvou
  , f.organization_name rcvorgnm, f.organization_code rcvdorg 
  from xla_ae_lines b, gl_code_combinations c,  hr_operating_units u1, xla_ae_headers  ah,  xla.xla_transaction_entities d, mtl_material_transactions e
    , mtl_system_items_b i, org_organization_definitions f-- ,--,MTL_ITEM_CATEGORIES_V ct,               
                -- org_acct_periods pd
     where b.application_id=555 and b.ledger_id=2021 and to_date(b.accounting_date,'dd/mm/yy') between to_date('01/12/24','dd/mm/yy') and to_date('31/12/24','dd/mm/yy') 
      AND b.code_combination_id =c.code_combination_id
      AND c.segment6 in ('124818','412100','424818')  
      AND b.ae_header_id  in (     
     select a.ae_header_id from xla_ae_headers a--, gl_code_combinations c 
     where a.application_id=555 and a.ledger_id=2021 and a.PERIOD_NAME='Dec-24-25'
     and a.AE_HEADER_ID=ah.AE_HEADER_ID
     )
     and c.segment1=u1.SHORT_CODE
     and b.ae_header_id=ah.ae_header_id and ah.application_id=555 and ah.ledger_id=2021 and ah.PERIOD_NAME='Dec-24-25'
     and ah.entity_id = d.entity_id
     and ah.application_id = d.application_id
     and d.source_id_int_1 = e.transaction_id
     and e.inventory_item_id = i.inventory_item_id
     and e.organization_id = i.organization_id     
     and e.organization_id = f.organization_id
     group by  c.segment1, to_char(e.TRANSACTION_DATE, 'YYYYMM'), ah.PERIOD_NAME, i.INVENTORY_ITEM_ID,i.segment1 , i.description , b.code_combination_id, u1.NAME 
  , f.organization_name , f.organization_code  
     
     ;
     
     commit;
     
     

SELECT   ou, item, itemname, 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,  PERIOD_NAME
    FROM XXPRG_COGS_DATA where PERIOD_NAME='Dec-24-25'  GROUP BY ou, item, itemname , PERIOD_NAME

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

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