BATCH COMPONENT COST OF ANY PRODUCT OPM

 BATCH COMPONENT COST OF ANY PRODUCT OPM :


SELECT   o.organization_code, o.organization_name, i.segment1, i.description,
         bh.batch_no, line_type, plan_qty, wip_plan_qty, original_qty,
         actual_qty, c.cost_cmpntcls_id, cc.cost_cmpntcls_code,
         c.cost_analysis_code, cost_amt amount, cd.CMPNT_COST acc_cost, p.PERIOD_CODE
    FROM gme_batch_header bh,
         gme_material_details bd,
         mtl_system_items_b i,
         org_organization_definitions o,
         cm_acst_led c,cm_cmpt_dtl cd,
         cm_cmpt_mst_b cc, GMF_PERIOD_STATUSES P
   WHERE o.organization_code = :p_org_code
     AND bh.organization_id = o.organization_id
     AND bh.batch_id = bd.batch_id
     AND bd.inventory_item_id = i.inventory_item_id
     AND bd.organization_id = i.organization_id
     --AND line_type = 1
     AND c.cost_cmpntcls_id = cc.cost_cmpntcls_id
     AND bd.material_detail_id = c.transline_id
     AND bd.inventory_item_id = c.inventory_item_id(+)
     AND bd.organization_id = c.organization_id(+)
     AND c.period_id(+) = :p_period_id
     AND i.inventory_item_id = :p_inv_item_id
     and c.INVENTORY_ITEM_ID=cd.INVENTORY_ITEM_ID and c.ORGANIZATION_ID=cd.ORGANIZATION_ID
     and c.PERIOD_ID=cd.PERIOD_ID and c.CMPNTCOST_ID=cd.CMPNTCOST_ID
     and c.PERIOD_ID=p.PERIOD_ID

Logical Intransit Shipment Accounting Generated when Transfer Item from OPM to ME:

Logical Intransit Shipment Accounting Generated when Transfer Item from OPM to ME:

Solution: 

Reopen Closed Period

/*  opm_undo_create_accounting_by_date_range_and_LE_R12.sql
+=======================================================================+
 *                     Undo/Reset OPM Costed Flags
 * +=======================================================================+
 *
 * IMPORTANT NOTE:
 * ===============
 *
 * 1. This script should be used by authorised Oracle personnel only.
 *    Use of this script must be approved by:
 *        OPM Financial Manager
 *        OPM Level 2 Support Manager
 *        Customer Management
 *
 * 2. Running this script without control/due care may severly damage the
 *    customer's sub-ledger.
 *  a.  Advice the customer to perform a complete backup and
 *        create a test environement.
 *    b.  Make sure that the customer understands that this script will
 *        only resets costed flags for transactions in process enabled
 *        organizations, so that these transactions can be considered
 *        for processing by ACP/Preprocessor during next runs.
 *    c.  Perform reversing/deleting of GL postings based on GL batches posted.
 *    d.  Run the script in the test environment first.
 *    e.  Ask customer to run the process (ACP/Pre-processor) again.
 *        Verify the results.
 *    f.  IF THE RESULTS ARE ACCEPTABLE RUN THE SCRIPT IN PRODUCTION
 *        ENVIRONMENT.
 *
 * 3. This script must be immediately removed from the customer's machine
 *    as soon as possible.  This is to prevent any accidental execution
 *    by the customer's personnel.
 * 4. Please modify start date ,end date and legal entity id
 *
 *
 * How to Use:
 * ===========
 *   - Make sure you backup all the related tables
 *     mtl_material_transactions
 *     gmf_rcv_accounting_txns
 *     gmf_invoice_distributions
 *     gmf_incoming_material_layers
 *     gme_resource_txns
 *     gme_batch_header
 *     gmf_lot_cost_adjustments
 *     gmf_period_balances
 *     cm_adjs_dtl
 *     gl_aloc_dtl
 *     gmf_lc_actual_cost_adjs  -- Applicable for OPM-LCM integration for R12.1 and later
 *     gmf_lc_lot_cost_adjs      -- Applicable for OPM-LCM integration for R12.1 and later
 *     gmf_lc_adj_transactions  -- Applicable for OPM-LCM integration for R12.1 and later
 *     gmf_transaction_valuation
 *     xla_distribution_links
 *     xla_ae_lines
 *     xla_ae_segment_values
 *     xla_ae_headers
 *     xla_events
 *     xla_transaction_entities_upg
 *     gmf_xla_extract_lines
 *     gmf_xla_extract_headers
 *
 *
 * PRE-REQUISITE:
 *
 *     - Perform reversing/deleting of GL postings based on GL batches posted.
 *         After performing the reversals of batches make sure that the reversal/deleting
 *         of GL postings has happened correctly from GL side. Once that is made sure, only then proceed 
 *         Please log a Service Request with Oracle General Ledger Support team For assistance with reversing/deleting GL Batches
 *
 *       Use THIS script, for next step to reset OPM costed flags.
 *
 *   - Script does not commit. Commit or rollback needs to be explicitly issued
 *         based on the results from the above query.
 *  
 *  CHANGE HISTORY:
 *   Version    Date        Name        Comments   
 *   2.00      01-JUL-2015     KGENIKAL     Modified to add additional scripts needed
 *                        to support LCM-OPM integration tables ( Reference bugs 21054001, 20503780)
 *   
 * +=======================================================================+  */


    --
    -- Update mtl_material_transactions table
    --

        UPDATE mtl_material_transactions t
        SET   opm_costed_flag  = 'D'              
        WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = t.organization_id
    );
   
        --
        -- Update gmf_rcv_accounting_txns table
        --      

        UPDATE gmf_rcv_accounting_txns grat
        SET    accounted_flag  = 'D'
        WHERE  transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = grat.organization_id
    );
       
        --
        -- Update gmf_invoice_distributions table
        -- 

        UPDATE gmf_invoice_distributions
        SET  Accounted_flag = 'D', final_posting_date   = NULL              
        WHERE accounted_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   accounted_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   legal_entity_id = '&LE_ID';
               
    --
    --    Update transaction valuation table to set shipment_costed flag
    --    back to NULL for event clases FOB_RCPT_SENDER_RCPT and FOB_SHIP_RECIPIENT_SHIP
    --        
                      
    UPDATE mtl_material_transactions t
    SET    shipment_costed       = NULL               
    WHERE  transaction_source_type_id IN (7, 8, 13)
    AND    transaction_action_id      IN (12, 21)
    AND    transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = t.organization_id
    );
           

    --
        -- Update incoming layers table (BatchesXperiods Enh.)
        --

    UPDATE gmf_incoming_material_layers giml
    SET    accounted_flag        = 'D',
           actual_posting_date   =  NULL
    WHERE  layer_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    layer_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = giml.mmt_organization_id
    );
       
        --
        -- Update resource transaction table
        -- Decide what Flag to use when run in Draft Mode.
        --       

    UPDATE gme_resource_txns
    SET   posted_ind  = 0               
    WHERE trans_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   trans_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
       
    --
    -- Update Batch Header table
    --
                  
    UPDATE gme_batch_header
    SET   gl_posted_ind     = 0                   
    WHERE batch_close_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   batch_close_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND  organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
           

    --
        -- Now for lot cost method, update gmf_lot_cost_adjustmets table
        -- For Actual/Standard methods, update gmf_period_balances table.
        --
       

    UPDATE gmf_lot_cost_adjustments
    SET   gl_posted_ind      = 0               
    WHERE adjustment_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   adjustment_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   legal_entity_id  = '&LE_ID';

    --
    -- Update transaction valuation table
    --

    UPDATE gmf_period_balances
    SET    costed_flag           = 'D'
    WHERE  period_balance_id IN
        (SELECT xte.SOURCE_ID_INT_1
            FROM xla.xla_transaction_entities xte,
                 xla_events xe,
                 gmf_xla_extract_headers geh
            WHERE xte.entity_id       = xe.entity_id
            AND xe.event_id           = geh.event_id
            AND xe.application_id     = 555
            AND geh.transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND geh.transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
            AND geh.event_type_code   = 'COSTREVAL'
                        AND geh.legal_entity_id   = '&LE_ID'
        );

    --
    -- Update Actual Cost Adjustments
    --
               
    UPDATE cm_adjs_dtl
    SET   gl_posted_ind      = 0
    WHERE adjustment_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   adjustment_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
             
         
    --
    -- Update Cost Allocations
    --
          
               
    UPDATE gl_aloc_dtl
    SET   gl_posted_ind      = 0
    WHERE allocdtl_id IN
        (    SELECT xte.SOURCE_ID_INT_1
            FROM xla.xla_transaction_entities xte,
                 xla_events xe,
                 gmf_xla_extract_headers geh
            WHERE xte.entity_id = xe.entity_id
            AND xe.event_id           = geh.event_id
            AND xe.application_id     = 555
            AND geh.transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND geh.transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
            AND geh.event_type_code   = 'GLCOSTALOC'
            and geh.legal_entity_id   = '&LE_ID'
        );


     /* Start changes V2 Dt. 01-Jul-2015 */
     /* Applicable for OPM-LCM integration for release 12.1 and later */
     /* Start Update OPM-LCM integration tables */
                
         UPDATE gmf_lc_actual_cost_adjs
         SET    accounted_flag     = 'N',
                final_posting_date = NULL
         WHERE  adj_transaction_id IN
                  (
                    SELECT adj_transaction_id
                    FROM   gmf_lc_adj_transactions
                    WHERE  legal_entity_id   = '&LE_ID'
                AND    transaction_date >= TO_DATE('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
                AND    transaction_date <= TO_DATE('01/08/09 23:59:59','dd/mm/yy hh24:mi:ss')
                  );
 
         UPDATE gmf_lc_lot_cost_adjs
         SET    accounted_flag     = 'N',
                final_posting_date = NULL
         WHERE  adj_transaction_id IN
                  (
                    SELECT adj_transaction_id
                    FROM   gmf_lc_adj_transactions
                    WHERE  legal_entity_id   = '&LE_ID'
                AND    transaction_date >= TO_DATE('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
                AND    transaction_date <= TO_DATE('01/08/09 23:59:59','dd/mm/yy hh24:mi:ss')
                  );

         UPDATE gmf_lc_adj_transactions
         SET    accounted_flag    = 'D'
         WHERE  legal_entity_id   = '&LE_ID'
     AND    transaction_date >= TO_DATE('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('01/08/09 23:59:59','dd/mm/yy hh24:mi:ss');

     /* End Update OPM-LCM integration tables */

     /* End changes V2 Dt. 01-Jul-2015 */
    
    --
    -- Delete transaction valuation rows
    --
       
    DELETE FROM gmf_transaction_valuation
    WHERE  transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    legal_entity_id   = '&LE_ID';

     create table xla_events_bck as
     select xe.*
     from   xla_events xe,
            gmf_xla_extract_headers gmf
     where  xe.application_id     = 555
     and    xe.event_id           = gmf.event_id
     and    gmf.transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
     and    gmf.transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
         AND    gmf.legal_entity_id   = '&LE_ID';
   
     create table xla_ae_headers_bck as
     select *
     from   xla_ae_headers
     where  application_id = 555
     and    event_id in(select event_id
                        from   xla_events_bck);
   
   
     create table xla_ae_lines_bck as
     select *
     from   xla_ae_lines
     where  application_id = 555
     and    ae_header_id in(select ae_header_id 
                            from   xla_ae_headers_bck);
   
     create table xla_dist_link_bck as
     select *
     from   xla_distribution_links
     where  application_id = 555
     and    ae_header_id in(select ae_header_id 
                            from   xla_ae_headers_bck);
   
   
     create table xla_ae_seg_val_bck as
     select *
     from   xla_ae_segment_values
     where  ae_header_id in(select ae_header_id 
                            from   xla_ae_headers_bck );

    /* Start changes V2 Dt. 01-Jul-2015 */                           
        CREATE TABLE xla_trans_enti_upg_bck AS
        SELECT *
        FROM XLA_TRANSACTION_ENTITIES_UPG
        WHERE application_id = 555
        AND LEGAL_ENTITY_ID  = '&LE_ID'
        AND ENTITY_ID       IN
          ( SELECT ENTITY_ID FROM XLA_AE_HEADERS_BCK
          );                           
    /* End changes V2 Dt. 01-Jul-2015 */

     -- delete data from xla tables
     delete from xla_distribution_links
     where  application_id = 555
     and    ae_header_id in(select distinct ae_header_id
                            from   xla_dist_link_bck);
   
   
     delete from xla_ae_lines
     where  application_id = 555
     and    ae_header_id in(select distinct ae_header_id
                            from   xla_ae_lines_bck);
   
   
     --This one may take sometime.
     delete from xla_ae_segment_values
     where  ae_header_id in(select distinct ae_header_id
                            from   xla_ae_seg_val_bck);
   
   
     delete from xla_ae_headers
     where  application_id = 555
     and    ae_header_id in (select ae_header_id
                             from   xla_ae_headers_bck);
                            
     delete from xla_events
     where  application_id = 555
         and    event_id in(select event_id
                    from   xla_events_bck);
        
         /* Start changes V2 Dt. 01-Jul-2015 */
        DELETE
        FROM XLA_TRANSACTION_ENTITIES_UPG
        WHERE APPLICATION_ID=555
        AND LEGAL_ENTITY_ID = '&LE_ID'
        AND ENTITY_ID      IN
          ( SELECT ENTITY_ID FROM XLA_AE_HEADERS_BCK
          );                
     /* End changes V2 Dt. 01-Jul-2015 */
   
        --
        -- Delete extract lines
        --
        DELETE FROM gmf_xla_extract_lines
        WHERE header_id IN (
            SELECT header_id
            FROM  gmf_xla_extract_headers
            WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND   transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
                        AND   legal_entity_id   = '&LE_ID'
                );
       
        --
        -- Delete extract headers
        --
   
        DELETE FROM gmf_xla_extract_headers
        WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND   transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
                AND   legal_entity_id   = '&LE_ID';

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 

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

Seeded Procedure and Programs for Landed Cost Adjustments Import Process :

Landed Cost Adjustments Import Process  Program Procedure : 

 

 GMF_LC_ADJ_TRANSACTIONS_PKG.Process_Lc_Adjustments

Also, Find out if the Charges cost will double for Landed Cost Adjustments Import Process
 
1) Select
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE, count(*)
From gmf_lc_adj_transactions
Group by
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
having count(*) > 1;




2) select distinct i(INVENTORY_ITEM_ID) From gmf_lc_adj_transactions where
(RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE) IN
(Select
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
From gmf_lc_adj_transactions
Group by
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
having count(*) > 1);

OU wise Orgs Lists

select  u.SHORT_CODE, u.NAME, count(o.ORGANIZATION_CODE) --over (partition by u.SHORT_CODE)
orgs_per_ou
from org_organization_definitions o, hr_operating_units u
where o.OPERATING_UNIT=u.ORGANIZATION_ID and o.ORGANIZATION_CODE not in ('FMO')
group by  u.SHORT_CODE, u.NAME
order by u.SHORT_CODE

OU wise Orgs

select o.ORGANIZATION_CODE, o.ORGANIZATION_ID, o.ORGANIZATION_NAME, u.SHORT_CODE, u.NAME, count(o.ORGANIZATION_CODE) over (partition by u.SHORT_CODE) orgs_per_ou
from org_organization_definitions o, hr_operating_units u
where o.OPERATING_UNIT=u.ORGANIZATION_ID and o.ORGANIZATION_CODE not in ('FMO')
order by o.ORGANIZATION_CODE

Time stemp

select fcr.request_id request_id, fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog, fcr.argument_text,
fcr.actual_start_date start_date, fcr.actual_completion_date completion_date,
round (fcr.actual_completion_date - fcr.actual_start_date, 5) aa,
substr (numtodsinterval (( fcr.actual_completion_date
- fcr.actual_start_date
), 'day' ), 12, 8 ) DURATION,
fcr.status_code, fcr.completion_text
from fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcpt.application_id
--and fcp.concurrent_program_name = 'GMFACOST'-- '&EnterShortname'   --For example 'GMFACOST'
and rownum < 100
order by trunc ( ( (fcr.actual_completion_date - fcr.actual_start_date) / (1 / 24) ) * 60 ) desc

RA Data Fix

RA Data Fix

SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY');
 
 
  SELECT *--event_id
       FROM ar_receivable_applications_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3  -- 3 22 652 rows data returns
    
    
      SELECT event_id
       FROM ar_cash_receipt_history_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;  -- No data return
   
 
  --3rd
  
  SELECT event_id
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3  -- 9 rows return
    
    
      UPDATE ar_receivable_applications_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
  WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
    
     select * from  ra_cust_trx_line_gl_dist_all
     --SET posting_control_id = -9999,
         --gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-1816','DD-MON-YYYY') TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
     SELECT event_id,posting_control_id,gl_posted_date
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
     SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY');
 
 
 
     SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
  -- No data
 

 SELECT event_id
       FROM ar_receivable_applications_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
  -- 38 rows
 
 
  SELECT event_id,gl_posted_date
       FROM ar_cash_receipt_history_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
     -- no data
    
    
     SELECT event_id,posting_control_id,gl_posted_date
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
     -- 7 rows
    
    
     ---- update
    
      UPDATE ar_receivable_applications_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
  WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
    
     UPDATE ra_cust_trx_line_gl_dist_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;

INV - New Item Creation

CREATE TABLE APPS.XX_ITEM_INTERFACE_STG_DEBUG ( SEGMENT1 VARCHAR2(40 BYTE), DESCRIPTION VARCHAR2(240 BYTE), PRIMARY_UOM_CODE VARCHAR2(3 BYTE), PROCESS_QUALITY_ENABLED_FLAG VARCHAR2(1 BYTE), PROCESS_COSTING_ENABLED_FLAG VARCHAR2(1 BYTE), RECIPE_ENABLED_FLAG VARCHAR2(1 BYTE), PROCESS_EXECUTION_ENABLED_FLAG VARCHAR2(1 BYTE), I_STSTAUS VARCHAR2(500 BYTE), LONG_DESC VARCHAR2(4000 BYTE) ) ------------------------------------------------------------

 

 

 Insert into XX_ITEM_INTERFACE_STG_DEBUG select item_id, item_name, 'DZ' unit , 'Y','Y','Y','Y', null,null from item_master@LSALES a, rfl_fg_item_list b, ITEM_DIST_UNITS@LSALES ut where b.ITEM_CODE=a.item_id and a.ITEM_D_UNITS_ID=ut.ITEM_D_UNITS_ID and upper(ITEM_D_UNITS_NAME)='DOZEN' --872038 and not exists (select * from mtl_system_items_b itm where a.item_id= itm.segment1 and itm.ORGANIZATION_ID=102) ------------------------------------------------------------- -- /* Cursor Declaration ***/ declare cursor c1 is --select * from XX_ITEM_INTERFACE_STG_DEBUG where SEGMENT1 in ('911461','911462'); --xx_item_interface_stg1; select a.* from XX_ITEM_INTERFACE_STG_DEBUG a where -- a.SEGMENT1 in ('911461','911462') and a.SEGMENT1 not in (select segment1 from mtl_system_items_b i where organization_id=102 and a.SEGMENT1= i.SEGMENT1 ) -- and a.SEGMENT1 in ('911465','911466','911467') ; --select ATTRIBUTE1, I.* from mtl_system_items_b i where organization_id=102 and i.SEGMENT1 in ('32144') /* Variable Declaration **/ l_org_id number(3); l_uom varchar2(15); l_suom varchar2(15); l_item_type varchar2(60); l_desc varchar2(60); l_temp_id varchar2(3); l_sale_ac number(16); l_exp_ac number(16); l_cost_sale_ac number(16); l_item_number varchar2(100); l_flag varchar2(4) default'A'; l_msg varchar2(200); begin --delete from MTL_SYSTEM_ITEMS_INTERFACE; --commit; for x1 in c1 loop /* Validation For Organization */ -- begin -- select organization_id into l_org_id -- from ORG_ORGANIZATION_DEFINITIONS -- where organization_code = x1.organization_code; -- exception -- when others then -- l_flag :='E'; -- l_msg :='Item Interface'||'Organization Id'||l_org_id||' not in system'; -- fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); -- end; /* Validation for Item Number */ if x1.SEGMENT1 is null then l_flag :='E'; l_msg :='Item number should not be NULL'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured--'||l_msg); end if; /* Validation for Description */ if x1.DESCRIPTION is null then l_flag :='E'; l_msg :='Item description should not be NULL'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg|| x1.SEGMENT1); end if; /* Validation for Unit Of Measure */ if x1.PRIMARY_UOM_CODE is not null then begin select UOM_CODE into l_uom from mtl_units_of_measure where UOM_CODE =x1.PRIMARY_UOM_CODE; exception when others then l_flag :='E'; l_msg := x1.SEGMENT1||'---'||l_uom ||'UOM is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; else l_msg :='Primary UOM should not be NULL '; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end if; begin select COST_OF_SALES_ACCOUNT into l_cost_sale_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Cost of Sales Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Cost of Sales Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; begin select SALES_ACCOUNT into l_sale_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Sales Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Sales Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; begin select EXPENSE_ACCOUNT into l_exp_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Expence Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Expence Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; if l_flag!='E' then insert into MTL_SYSTEM_ITEMS_INTERFACE ( ORGANIZATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION, SEGMENT1, PRIMARY_UOM_CODE, TEMPLATE_ID, SUMMARY_FLAG, ENABLED_FLAG, SALES_ACCOUNT, COST_OF_SALES_ACCOUNT, EXPENSE_ACCOUNT, PROCESS_QUALITY_ENABLED_FLAG, PROCESS_COSTING_ENABLED_FLAG, RECIPE_ENABLED_FLAG, PROCESS_EXECUTION_ENABLED_FLAG, RESTRICT_SUBINVENTORIES_CODE, RESTRICT_LOCATORS_CODE , LOCATION_CONTROL_CODE , SET_PROCESS_ID, PROCESS_FLAG, TRANSACTION_TYPE/*, ATTRIBUTE1, -- ou ATTRIBUTE25, -- TF ATTRIBUTE26, -- unload ATTRIBUTE27, -- load ATTRIBUTE28, -- DP ATTRIBUTE29, -- TP ATTRIBUTE30 -- MRP*/ ) values( 102, sysdate, fnd_global.USER_ID, sysdate, fnd_global.USER_ID, fnd_global.LOGIN_ID, x1.DESCRIPTION, x1.SEGMENT1, l_uom, -- l_item_type, -- 1, -- 1 for FG item, 2 for Purchase 1, 'Y', 'Y', l_sale_ac, l_cost_sale_ac, l_exp_ac, x1.PROCESS_QUALITY_ENABLED_FLAG, x1.PROCESS_COSTING_ENABLED_FLAG, x1.RECIPE_ENABLED_FLAG, x1.PROCESS_EXECUTION_ENABLED_FLAG, 1, 1, 2, 2, 1, 'CREATE'/*, x1.ATTRIBUTE1, x1.ATTRIBUTE25, -- TF x1.ATTRIBUTE29, -- unload x1.ATTRIBUTE30, -- load x1.ATTRIBUTE27, -- DP x1.ATTRIBUTE28, -- TP x1.ATTRIBUTE26 -- MRP*/ ); -- MTL_INTERFACE_ERRORS -- update XX_ITEM_INTERFACE_STG_DEBUG set I_STATUSES='OK' where SEGMENT1= x1.SEGMENT1; -- commit; else update XX_ITEM_INTERFACE_STG_DEBUG set I_STSTAUS=l_msg where SEGMENT1= x1.SEGMENT1; commit; end if; end loop; commit; end; / ------------------------------------------------------------

Seeded Procedure for GMF Recreate Batch / Period Layers

 

GMF Recreate Batch / Period Layers:  GMF_LAYERS.RELAYER

URL From Toad

URL From Toad

    
    SELECT home_url FROM icx_parameters;

Errors: EAM

Errors: Create Accounting

Error : Intercompany balancing was unable to generate the intercompany account.  Please check the intercompany balancing rules setup.

Solution :
select  distinct count(ORGANIZATION_CODE) times,ORGANIZATION_id, ORGANIZATION_CODE, o.ORGANIZATION_NAME, er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o
where er.APPLICATION_ID=555 and
er.APPLICATION_ID=xah.APPLICATION_ID
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID=:P_REQUEST_ID --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID
and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE)
and xah.REQUEST_ID=xal.REQUEST_ID and xah.REQUEST_ID=:P_REQUEST_ID
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID and o.ORGANIZATION_CODE='478'
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID
GROUP BY ORGANIZATION_CODE, ORGANIZATION_id, o.ORGANIZATION_NAME , er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1


SOURCE_ID_INT_1=TRANSACTION_ID

select DISTRIBUTION_ACCOUNT_ID from mtl_material_transactions t where t.TRANSACTION_ID=120481385

select * from gl_code_combinations c where c.CODE_COMBINATION_ID=825229 

Check the Account Code is Active or Not. If inactive Active it then run Create Accounting program.


SLA Error check with Transaction  :

select distinct --DISTRIBUTION_ACCOUNT_ID,
c.SEGMENT1 ||' - '|| fv1.DESCRIPTION segment1,
 c.SEGMENT6||' - '|| fv.DESCRIPTION segment6 from mtl_material_transactions t, gl_code_combinations c, FND_FLEX_VALUES_VL FV, FND_FLEX_VALUES_VL FV1  where t.DISTRIBUTION_ACCOUNT_ID=c.CODE_COMBINATION_ID
 and c.SEGMENT6=fv.FLEX_VALUE and c.SEGMENT1=fv1.FLEX_VALUE
 and t.TRANSACTION_ID in (
select  distinct
--count(ORGANIZATION_CODE) times,ORGANIZATION_id, ORGANIZATION_CODE, o.ORGANIZATION_NAME, er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID,
 SOURCE_ID_INT_1
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o
where er.APPLICATION_ID=555 and
er.APPLICATION_ID=xah.APPLICATION_ID
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID=:P_REQUEST_ID --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID
and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE)
and xah.REQUEST_ID=xal.REQUEST_ID and xah.REQUEST_ID=:P_REQUEST_ID
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID-- and o.ORGANIZATION_CODE='478'
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID
GROUP BY ORGANIZATION_CODE, ORGANIZATION_id, o.ORGANIZATION_NAME , er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1
)

Errors: Accounting Preprocessor

Error Check after run pre-processor:

 
Select * from GL_MESG_TBL pe where pe.request_id= :request_id

Errors: Actual Cost Process

Errors: XLA

Errors: SLA

Errors: Internal location is Already assigned to another Ship-to Address

 If it returns any row, it means that, indeed, an Internal location is Already assigned to another Ship-to Address 

SELECT * FROM PO_LOCATION_ASSOCIATIONS_ALL WHERE LOCATION_ID = &location_id AND ORG_ID = &enter org_id;

 

For deletion of internal location association

Please run following datafix:

Take a backup of table :- FROM PO_LOCATION_ASSOCIATIONS_ALL

 DELETE FROM PO_LOCATION_ASSOCIATIONS_ALL  WHERE LOCATION_ID = &location_id AND ORG_ID = &enter org_id;