Master - Child Conflict in one of these Attributes - Inventory while doing Master Org To Child Org Transfer Error

Count the Invalid  Attributes Default.

SELECT COUNT(*) CATALOG_STATUS_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE CATALOG_STATUS_FLAG IS NULL;
SELECT COUNT(*) COLLATERAL_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE COLLATERAL_FLAG IS NULL;
SELECT COUNT(*) CONTAINER_ITEM_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE CONTAINER_ITEM_FLAG IS NULL;
SELECT COUNT(*) DOWNLOADABLE_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE DOWNLOADABLE_FLAG IS NULL;
SELECT COUNT(*) DRP_PLANNED_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE DRP_PLANNED_FLAG IS NULL;
SELECT COUNT(*) ELECTRONIC_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE ELECTRONIC_FLAG IS NULL;
SELECT COUNT(*) EQUIPMENT_TYPE FROM MTL_SYSTEM_ITEMS_B WHERE EQUIPMENT_TYPE IS NULL;
SELECT COUNT(*) EVENT_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE EVENT_FLAG IS NULL;
SELECT COUNT(*) INDIVISIBLE_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE INDIVISIBLE_FLAG IS NULL;
SELECT COUNT(*) LOT_DIVISIBLE_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE LOT_DIVISIBLE_FLAG IS NULL;
SELECT COUNT(*) LOT_MERGE_ENABLED FROM MTL_SYSTEM_ITEMS_B WHERE LOT_MERGE_ENABLED IS NULL;
SELECT COUNT(*) LOT_SPLIT_ENABLED FROM MTL_SYSTEM_ITEMS_B WHERE LOT_SPLIT_ENABLED IS NULL;
SELECT COUNT(*) LOT_STATUS_ENABLED FROM MTL_SYSTEM_ITEMS_B WHERE LOT_STATUS_ENABLED IS NULL;
SELECT COUNT(*) LOT_TRANSLATE_ENABLED FROM MTL_SYSTEM_ITEMS_B WHERE LOT_TRANSLATE_ENABLED IS NULL;
SELECT COUNT(*) MRP_CALCULATE_ATP_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE MRP_CALCULATE_ATP_FLAG IS NULL;
SELECT COUNT(*) RECIPE_ENABLED_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE RECIPE_ENABLED_FLAG IS NULL;
SELECT COUNT(*) SERIAL_STATUS_ENABLED FROM MTL_SYSTEM_ITEMS_B WHERE SERIAL_STATUS_ENABLED IS NULL;
SELECT COUNT(*) SERIAL_TAGGING_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE SERIAL_TAGGING_FLAG IS NULL;
SELECT COUNT(*) VEHICLE_ITEM_FLAG FROM MTL_SYSTEM_ITEMS_B WHERE VEHICLE_ITEM_FLAG IS NULL;
_________________________________________________________________________________
_________________________________________________________________________________

Master - Child Conflict in one of these Attributes: Type(WIP_SUPPLY_TYPE), Subinventory(WIP_SUPPLY_SUBINVENTORY), Primary UOM Code(PRIMARY_UOM_CODE), Primary Unit Of Measure(PRIMARY_UNIT_OF_MEASURE), Conversions(ALLOWED_UNITS_LOOKUP_CODE), Cost Of Goods Sold Account(COST_OF_SALES_ACCOUNT), Sales Account(SALES_ACCOUNT).

check the we have to assign in master Level Or Org Level

Please check the Inventory - Setup - Item - Attributes control -- check in master org

For this above issue , Initially (PRIMARY_UNIT_OF_MEASURE) it was  master Level I changed to Org level


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

Master - Child Conflict in one of these Attributes: Demant Time Fence(DEMAND_TIME_FENCE_CODE), Lead Time Lot Size(LEAD_TIME_LOT_SIZE), Standard Lot Size(STD_LOT_SIZE), Cumulative Manufacturing(CUM_MANUFACTURING_LEAD_TIME), Overrun Percentage(OVERRUN_PERCENTAGE), Calculation ATP(MRP_CALCULATE_ATP_FLAG), Positive Acceptable Rate(ACCEPTABLE_RATE_INCREASE), Negative Acceptable Rate(ACCEPTABLE_RATE_DECREASE).


For this issue I used below query to update mtl_system_items_b . Before that take back up file and check. It will get resloved.

========================================================================

set verify off
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;


set timing on;
set serveroutput on;
DECLARE
  l_task     VARCHAR2(30) := 'update_bigtable';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,
                                                table_owner => 'INV',
                                                table_name => 'MTL_SYSTEM_ITEMS_B',
                                                by_row => TRUE,
                                                chunk_size => 10000);
  l_sql_stmt := 'UPDATE MTL_SYSTEM_ITEMS_B SET
                      CATALOG_STATUS_FLAG = CASE WHEN CATALOG_STATUS_FLAG IS NULL THEN ''N'' ELSE CATALOG_STATUS_FLAG END,
                      COLLATERAL_FLAG = CASE WHEN COLLATERAL_FLAG IS NULL THEN ''N'' ELSE COLLATERAL_FLAG END,
                      CONTAINER_ITEM_FLAG = CASE WHEN CONTAINER_ITEM_FLAG IS NULL THEN ''N'' ELSE CONTAINER_ITEM_FLAG END,
                      DOWNLOADABLE_FLAG = CASE WHEN DOWNLOADABLE_FLAG IS NULL THEN ''N'' ELSE DOWNLOADABLE_FLAG END,
                      ELECTRONIC_FLAG = CASE WHEN ELECTRONIC_FLAG IS NULL THEN ''N'' ELSE ELECTRONIC_FLAG END,
                      EVENT_FLAG = CASE WHEN EVENT_FLAG IS NULL THEN ''N'' ELSE EVENT_FLAG END,
                      INDIVISIBLE_FLAG = CASE WHEN INDIVISIBLE_FLAG IS NULL THEN ''N'' ELSE INDIVISIBLE_FLAG END,
                      MRP_CALCULATE_ATP_FLAG = CASE WHEN MRP_CALCULATE_ATP_FLAG IS NULL THEN ''N'' ELSE MRP_CALCULATE_ATP_FLAG END,
                      VEHICLE_ITEM_FLAG = CASE WHEN VEHICLE_ITEM_FLAG IS NULL THEN ''N'' ELSE VEHICLE_ITEM_FLAG END,
                      RECIPE_ENABLED_FLAG = CASE WHEN RECIPE_ENABLED_FLAG IS NULL THEN ''N'' ELSE RECIPE_ENABLED_FLAG END,
                      BOM_ENABLED_FLAG = CASE WHEN BOM_ENABLED_FLAG IS NULL THEN ''N'' ELSE BOM_ENABLED_FLAG END,
                      LOT_STATUS_ENABLED = CASE WHEN LOT_STATUS_ENABLED IS NULL THEN ''N'' ELSE LOT_STATUS_ENABLED END,
                      SERIAL_STATUS_ENABLED = CASE WHEN SERIAL_STATUS_ENABLED IS NULL THEN ''N'' ELSE SERIAL_STATUS_ENABLED END,
                      LOT_DIVISIBLE_FLAG = CASE WHEN LOT_DIVISIBLE_FLAG IS NULL THEN ''N'' ELSE LOT_DIVISIBLE_FLAG END,
                      LOT_SPLIT_ENABLED = CASE WHEN LOT_SPLIT_ENABLED IS NULL THEN ''N'' ELSE LOT_SPLIT_ENABLED END,
                      LOT_MERGE_ENABLED = CASE WHEN LOT_MERGE_ENABLED IS NULL THEN ''N'' ELSE LOT_MERGE_ENABLED END,
                      LOT_TRANSLATE_ENABLED = CASE WHEN LOT_TRANSLATE_ENABLED IS NULL THEN ''N'' ELSE LOT_TRANSLATE_ENABLED END,
                      LOT_SUBSTITUTION_ENABLED = CASE WHEN LOT_SUBSTITUTION_ENABLED IS NULL THEN ''N'' ELSE LOT_SUBSTITUTION_ENABLED END,
                      BULK_PICKED_FLAG = CASE WHEN BULK_PICKED_FLAG IS NULL THEN ''N'' ELSE BULK_PICKED_FLAG END,
                      SERIAL_TAGGING_FLAG = CASE WHEN SERIAL_TAGGING_FLAG IS NULL THEN ''N'' ELSE SERIAL_TAGGING_FLAG END,
                      EQUIPMENT_TYPE = CASE WHEN EQUIPMENT_TYPE IS NULL THEN 2 ELSE EQUIPMENT_TYPE END,
  DRP_PLANNED_FLAG = CASE WHEN DRP_PLANNED_FLAG IS NULL THEN 2 ELSE DRP_PLANNED_FLAG END
                WHERE rowid BETWEEN :start_id AND :end_id';
             
  DBMS_PARALLEL_EXECUTE.run_task(task_name => l_task,
                                sql_stmt => l_sql_stmt,
                                language_flag => DBMS_SQL.NATIVE, parallel_level => 10);

  -- If there is error, RESUME it for at most 2 times.
    l_try := 0;
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
    WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
    Loop
      l_try := l_try + 1;
      DBMS_PARALLEL_EXECUTE.resume_task(l_task);
      l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
    END LOOP;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('error occured during batch  UPDATE    ::'||SQLERRM||'--'||SQLCODE);
  dbms_output.put_line(dbms_utility.format_error_backtrace);
  DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;

/

EXIT;

set serveroutput on;

declare

        l_api_version                   NUMBER := 1.0;

        l_init_msg_list                 VARCHAR2(2) := FND_API.G_TRUE;

        l_commit             VARCHAR2(2) := FND_API.G_FALSE;

        x_message_list               Error_Handler.Error_Tbl_Type;

        x_return_status               VARCHAR2(2);

        x_msg_count                  NUMBER := 0;

   

        l_user_id             NUMBER := -1;

        l_resp_id             NUMBER := -1;

        l_application_id   NUMBER := -1;

       

        l_org_code           VARCHAR2(3)  := 'MTP' ;

        l_item_number    VARCHAR2(25) := 'AVS-000341';

        l_org_id         NUMBER ;

        l_item_id        NUMBER ;

        l_user_name                   VARCHAR2(30) := 'SYSADMIN';

        l_resp_name                   VARCHAR2(30) := 'Inventory - AVH';   

BEGIN

      -- Get the user_id

      SELECT user_id

      INTO l_user_id

      FROM fnd_user

      WHERE user_name = l_user_name;

   

      -- Get the application_id and responsibility_id

      SELECT application_id, responsibility_id

      INTO l_application_id, l_resp_id

      FROM fnd_responsibility_vl

      WHERE responsibility_name = l_resp_name;

   

      FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);  -- MGRPLM / Development Manager / EGO

      dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );

     

     select organization_id

     into l_org_id

     from mtl_parameters where organization_code = l_org_code ;

    

     select inventory_item_id

     into l_item_id

     from mtl_system_items_b where segment1 = l_item_number and rownum < 2;

     

     DBMS_OUTPUT.PUT_LINE('Inputs: Organization ID: '||l_org_id ||' Inventory Item ID: '|| l_item_id);

        

     -- call API to assign Items

     DBMS_OUTPUT.PUT_LINE('===========================================');

    DBMS_OUTPUT.PUT_LINE('Calling EGO_ITEM_PUB.Assign_Item_To_Org API');       

  

      EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(

             P_API_VERSION          => l_api_version

          ,  P_INIT_MSG_LIST        => l_init_msg_list

          ,  P_COMMIT               => l_commit

          ,  P_INVENTORY_ITEM_ID    => l_item_id   -- Use INVENTORY_ITEM_ID, ORGANIZATION_ID instead of ITEM_NUMBER, ORGANIZATION_CODE

          ,  P_ITEM_NUMBER          => NULL 

          ,  P_ORGANIZATION_ID      => l_org_id

          ,  P_ORGANIZATION_CODE    => 'MTP'

          ,  P_PRIMARY_UOM_CODE     => 'EA'

          ,  X_RETURN_STATUS        => x_return_status

          ,  X_MSG_COUNT            => x_msg_count

        );

      

       DBMS_OUTPUT.PUT_LINE('=========================================');

       DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);

       IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

          DBMS_OUTPUT.PUT_LINE('Error Messages :');

          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);

          FOR i IN 1..x_message_list.COUNT LOOP

             DBMS_OUTPUT.PUT_LINE(x_message_list(i).message_text);

          END LOOP;

       END IF;

       DBMS_OUTPUT.PUT_LINE('=========================================');      

        

EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE('Exception Occured :');

          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);

          DBMS_OUTPUT.PUT_LINE('========================================');

END;

Journal Entry Reserve Ledger Report Normal Query - 12.2.7 Fixed Asset DATA WILL MATCH STANDARD REPORT

First run this below script and Pass the parameter Period and Book Type

SELECT bc.distribution_source_book dbk,
           NVL (dp.period_close_date, SYSDATE) ucd,
           dp.period_counter upc,
           MIN (dp_fy.period_open_date) tod,
           MIN (dp_fy.period_counter) tpc,
           dp.period_num
      FROM fa_deprn_periods dp, fa_deprn_periods dp_fy, fa_book_controls bc
    WHERE dp.book_type_code = :book_type_code_
         AND dp.period_name = :period_name_
         AND dp_fy.book_type_code =  :book_type_code_
        AND dp_fy.fiscal_year = dp.fiscal_year
        AND bc.book_type_code =  :book_type_code_
   GROUP BY bc.distribution_source_book, dp.period_close_date, dp.period_counter, dp.period_num;

Once the above script is executed you will see the column pass the value in the below query. 

SELECT dh.asset_id asset_id,

       dh.code_combination_id dh_ccid,

     --  APPS_GL_CODE_COMB_UTIL_API.Get_Segment2(dh.code_combination_id) segment2,

       cb.deprn_reserve_acct rsv_account,

       books.date_placed_in_service start_date,

       books.deprn_method_code method,

       books.life_in_months life,

       books.adjusted_rate rate,

       books.production_capacity capacity,

       dd_bonus.cost cost,

       DECODE (dd_bonus.period_counter, :upc_, dd_bonus.deprn_amount - dd_bonus.bonus_deprn_amount, 0) deprn_amount,

       DECODE (SIGN (:tpc_ - dd_bonus.period_counter), 1, 0, dd_bonus.ytd_deprn - dd_bonus.bonus_ytd_deprn) ytd_deprn,

       dd_bonus.deprn_reserve - dd_bonus.bonus_deprn_reserve deprn_reserve,

       DECODE (th.transaction_type_code, NULL, dh.units_assigned / ah.units * 100) percent,

       DECODE (th.transaction_type_code,

               NULL, DECODE (th_rt.transaction_type_code, 'FULL RETIREMENT', 'F', DECODE (books.depreciate_flag, 'NO', 'N')),

               'TRANSFER', 'T',

               'TRANSFER OUT', 'P',

               'RECLASS', 'R')

          t_type,

       dd_bonus.period_counter,

       NVL (th.date_effective, :ucd_),

       ''

  FROM fa_deprn_detail dd_bonus,

       fa_asset_history ah,

       fa_transaction_headers th,

       fa_transaction_headers th_rt,

       fa_books books,

       fa_distribution_history dh,

       fa_category_books cb

 WHERE cb.book_type_code = :book_

   AND cb.category_id = ah.category_id

   AND ah.asset_id = dh.asset_id

   AND ah.date_effective < NVL (th.date_effective, :ucd_)

   AND NVL (ah.date_ineffective, SYSDATE) >= NVL (th.date_effective, :ucd_)

   AND --  AH.ASSET_TYPE                   = 'CAPITALIZED'

       --AND

       dd_bonus.book_type_code = :book_

   AND dd_bonus.distribution_id = dh.distribution_id

   AND dd_bonus.period_counter =

          (SELECT MAX (dd_sub.period_counter)

             FROM fa_deprn_detail dd_sub

            WHERE dd_sub.book_type_code = :book_

              AND dd_sub.asset_id = dh.asset_id

              AND dd_sub.distribution_id = dh.distribution_id

              AND dd_sub.period_counter <= :upc_)

   AND th_rt.book_type_code = :book_

   AND th_rt.transaction_header_id = books.transaction_header_id_in

   AND books.book_type_code = :book_

   AND books.asset_id = dh.asset_id

   AND NVL (books.period_counter_fully_retired, :upc_) >= :tpc_

   AND books.date_effective <= NVL (th.date_effective, :ucd_)

   AND NVL (books.date_ineffective, SYSDATE + 1) > NVL (th.date_effective, :ucd_)

   AND th.book_type_code(+) = :book_

   AND th.transaction_header_id(+) = dh.transaction_header_id_out

   AND th.date_effective(+) BETWEEN :tod_ AND :ucd_

   AND dh.book_type_code = :book_

Journal Entry Reserve Ledger Report Query - 12.2.7 Fixed Asset Based on GTT

First run this Declaration and check the GTT Table  Data will Populate


DECLARE
  BOOK VARCHAR2(200);
  PERIOD VARCHAR2(200);
  ERRBUF VARCHAR2(200);
  RETCODE NUMBER;
  OPERATION VARCHAR2(200);
  REQUEST_ID NUMBER;
BEGIN
  BOOK := NULL;
  PERIOD := NULL;
  REQUEST_ID := NULL;

  FA_RSVLDG_REP_INS_PKG.RSVLDG(
    BOOK => 'AWM ASSET BOOK',     ---------------------parameter Name
    PERIOD => 'Jan-19',                        -------------- Period Name
    ERRBUF => ERRBUF,
    RETCODE => RETCODE,
    OPERATION => OPERATION,   
REQUEST_ID => '108336103' ---- standard concurrent request id  (Journal Entry Reserve Ledger Report)
  );
  /* Legacy output:
DBMS_OUTPUT.PUT_LINE('ERRBUF = ' || ERRBUF);
*/
  :ERRBUF := ERRBUF;
  /* Legacy output:
DBMS_OUTPUT.PUT_LINE('RETCODE = ' || RETCODE);
*/
  :RETCODE := RETCODE;
  /* Legacy output:
DBMS_OUTPUT.PUT_LINE('OPERATION = ' || OPERATION);
*/
  :OPERATION := OPERATION;
--rollback;
END;

select * from FA_RESERVE_LEDGER_GT




SELECT  --AD.DESCRIPTION  ASSET_DESCRIPTION,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE,
decode(TRANSACTION_TYPE,'B',RSV.RESERVE_ACCT, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acct_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') ) GL_ACCOUNT,
RSV.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') COST_CENTER,
AD.ASSET_NUMBER ,
  AD.DESCRIPTION Asset_DESCRIPTION,
  AD.ATTRIBUTE_CATEGORY_CODE CATEGORY ,
DATE_PLACED_IN_SERVICE START_DATE,
METHOD_CODE METHOD,
RSV.LIFE LIFE,
RSV.RATE ADJ_RATE,
DS.BONUS_RATE BONUS_RATE,
RSV.CAPACITY PROD,
SUM(decode(transaction_type,'B',NULL,COST)) COST,
SUM(RSV.DEPRN_AMOUNT)     DEPRN_AMOUNT,
SUM(RSV.YTD_DEPRN) YTD_DEPRN,
SUM(RSV.DEPRN_RESERVE)          DEPRN_RESERVE,
sum(decode(transaction_type,'B',NULL,nvl(PERCENT,0))) PERCENT,
TRANSACTION_TYPE T_TYPE,
FA_FAS400_XMLP_PKG.d_lifeformula(RSV.LIFE, RSV.RATE, DS.BONUS_RATE, RSV.CAPACITY) D_LIFE
FROM
fa_reserve_ledger_gt RSV,
fa_additions AD,
gl_code_combinations CC,
&lp_fa_deprn_summary DS --- use this this table in the parameter - table name fa_deprn_summary
WHERE
RSV.ASSET_ID = AD.ASSET_ID
AND
RSV.DH_CCID = CC.CODE_COMBINATION_ID
AND
DS.PERIOD_COUNTER (+) = RSV.PERIOD_COUNTER AND
DS.BOOK_TYPE_CODE (+) = :P_Book  AND
DS.ASSET_ID (+) = RSV.ASSET_ID
GROUP BY
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
decode(transaction_type,'B', RSV.RESERVE_ACCT,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acct_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')),
RSV.DEPRN_RESERVE_ACCT,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
AD.ASSET_NUMBER,
AD.DESCRIPTION,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
RSV.LIFE,
  RSV.RATE,
RSV.CAPACITY,
DS.BONUS_RATE,
TRANSACTION_TYPE,
  AD.ATTRIBUTE_CATEGORY_CODE
ORDER BY
-- 1, 2, 3, 4, 5
1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 8, 12, 13, 14, 15, 16, 17