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

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;

Errors: GL

Errors: AP

Errors: OPM

Item Reserved till now and on-hand quantity allocated but no record found from Batch 

select *
FROM apps.mtl_reservations a, apps.gme_batch_header b, apps.mtl_parameters c, apps.gme_material_details d
WHERE --demand_source_type_id=5 AND supply_source_type_id=13
 a.demand_source_header_id=b.batch_id
AND a.organization_id=c.organization_id
AND a.organization_id=b.organization_id
AND b.batch_id=d.batch_id
AND a.demand_source_line_id=d.material_detail_id
--and  b.ORGANIZATION_ID=204
and a.INVENTORY_ITEM_ID=69106

Errors : Landed Cost Adjustments Import Process eBS R12

To run : Landed Cost Adjustments Import Process

Error : 

01. ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Action :

Check data by SQL: 

select * from PON_PRICE_ELEMENT_TYPES_TL where length(name) > 25;

select price_element_code from PON_PRICE_ELEMENT_TYPES where PRICE_ELEMENT_TYPE_ID in
(select PRICE_ELEMENT_TYPE_ID from PON_PRICE_ELEMENT_TYPES_TL where length(name) > 25); 

if exists then follow the solution:

Solution :

Please run the following script on the TEST instance:

update PON_PRICE_ELEMENT_TYPES_TL set name = substr(name, 0, 30) where length(name) > 30;
commit;

After this verify the outcome of the update using the following query:

select * from PON_PRICE_ELEMENT_TYPES_TL where length(name) > 30;

After this run the Landed Cost Adjustments Import Process request and see if it still completes in error.

Error :
02. Input parameters Legal entity / Id PFL_LE/23275 Cost component class ID null for Charge type component The line was not processed Rcv Ttransaction ID: 817450 Ship Num: 0000020015020210 Ship Line Num: 1 Item ID: 9781 Organization ID: 263 Adjustment Num: 0

Action :

Check data by SQL:  

 select PRICE_ELEMENT_TYPE_ID, PRICE_ELEMENT_CODE,  COST_COMPONENT_CLASS_ID,COST_ANALYSIS_CODE  from PON_PRICE_ELEMENT_TYPES t
 where COST_COMPONENT_CLASS_ID is null or COST_ANALYSIS_CODE is null

Find the Last one which you have updated or included or last time you have used in your application.

Solution :

Add cost component class and analyses code on all cost factors that are used for LCM charges.
Navigation Path Purchasing Super User (process Operations) responsibility, Setup – Purchasing – Cost Factors

2. Re-run the Landed Cost Adjustments Import Process