Concurrent Request Details

SELECT  /*+ ORDERED */       
        fcr.request_id request_id,
         NVL (fu.description, fu.user_name) requested_by,
         DECODE (fcp.concurrent_program_name,
                 'FNDRSSUB', 'Request Set - ' || fcr.description,
                 fcpt.user_concurrent_program_name)
            concurrent_program,
         DECODE (fcr.phase_code,
                 'I', 'Inactive',
                 'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', 'Pending'),
                 'R', 'Running',
                 'C', 'Complete',
                 fcr.phase_code)
            phase,
         DECODE (
            fcr.status_code,
            'U', 'Disabled',
            'W', 'Paused',
            'X', 'Terminated',
            'Z', 'Waiting',
            'M', 'No Manager',
            'Q', 'Standby',
            'R', 'Normal',
            'S', 'Suspended',
            'T', 'Terminating',
            'D', 'Cancelled',
            'E', 'Error',
            'F', 'Scheduled',
            'G', 'Warning',
            'H', 'On Hold',
            'I', CASE
                    WHEN fcr.request_date < fcr.requested_start_date
                    THEN
                       'Scheduled'
                    ELSE
                       'Normal'
                 END,
            'A', 'Waiting',
            'B', 'Resuming',
            'C', 'Normal',
            fcr.status_code)
            status,
         fcr.argument_text,
         TO_CHAR (fcr.request_date, 'DD-Mon-RRRR HH12:MI:SS AM') date_requested,
         TO_CHAR (fcr.requested_start_date, 'DD-Mon-RRRR HH12:MI:SS AM')
            requested_start_date,
         TO_CHAR (fcr.actual_start_date, 'DD-Mon-RRRR HH12:MI:SS AM')
            date_started,
         TO_CHAR (fcr.actual_completion_date, 'DD-Mon-RRRR HH12:MI:SS AM')
            date_completed,
         fcr.oracle_process_id,
         fcr.os_process_id,
         fcr.logfile_node_name || ': ' || fcr.logfile_name logfile_name,
         fcr.outfile_node_name || ': ' || fcr.outfile_name output_name
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt,
         fnd_user fu
   WHERE     fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fcp.concurrent_program_id = fcpt.concurrent_program_id
         AND fcr.program_application_id = fcp.application_id
         AND fcp.application_id = fcpt.application_id
         AND fcr.requested_by = fu.user_id
         AND fcpt.language = 'US'
ORDER BY fcr.request_date DESC

To find Responsibilities having a particular Concurrent Program

SELECT frt.responsibility_name, fcpt.user_concurrent_program_name
    FROM fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt,
         fnd_request_group_units frgu,
         fnd_responsibility fr,
         fnd_responsibility_tl frt
   WHERE     fcp.concurrent_program_id = fcpt.concurrent_program_id
         AND fcp.application_id = fcpt.application_id
         AND fcp.concurrent_program_id = frgu.request_unit_id(+)
         AND frgu.unit_application_id(+) = fcp.application_id
         AND frgu.request_group_id = fr.request_group_id(+)
         AND fr.responsibility_id = frt.responsibility_id(+)
         AND fr.application_id = frt.application_id(+)
         AND frgu.request_unit_type(+) = 'P'
         AND frt.language(+) = 'US'
         AND fcpt.language = 'US'
         AND fcpt.user_concurrent_program_name LIKE '%Trial Balance All Entities Mapping Report%'
ORDER BY frt.responsibility_name

Concurrent Programs details

SELECT fcp.concurrent_program_id,

       fcp.concurrent_program_name,

       fcpt.user_concurrent_program_name,

       fcpt.description,

       fe.executable_name,

       fet.user_executable_name,

       fe.execution_file_name

  FROM apps.fnd_concurrent_programs fcp,

       apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_executables fe,

       apps.fnd_executables_tl fet

 WHERE     fe.executable_id = fet.executable_id

       AND fcp.concurrent_program_id = fcpt.concurrent_program_id

       AND fcpt.language = fet.language

       AND fcp.executable_id = fe.executable_id

       AND fcp.executable_application_id = fe.application_id

       AND fcpt.language = 'US'

       AND fcpt.user_concurrent_program_name LIKE

              'Averda Trial Balance All Entities Mapping Report'

Trial Balance All Entities Report Internal 12.2.7

Query As per Standard

For YTD :

:CURRENCY_TYPE = 'T';
:AMOUNT_YPE = 'YTD'
:FIRST_PERIOD = 'Jan-19'
:PERIOD_NAME : = 'Dec-19'


This is for  ACTUAL_FLAG = 'A' - Actual - below coding




select * from (SELECT /*+ ORDERED */
L.LEDGER_ID , L.Name,cc.segment1||'-'||cc.segment2||'-'||cc.SEGMENT3||'-'||SEGMENT4||'-'||cc.segment5||'-'||cc.segment6||'-'||cc.segment7 Code_combinations,
GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(51515,3,cc.SEGMENT3) ACCOUNT_DESCRIPTION,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'S', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0)), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) BEGIN_BALANCE,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_DR, 0), 'S', NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_DR, 0), NVL(PERIOD_NET_DR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'S', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0), 0))))) PERIOD_DR,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_CR, 0), 'S', NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_CR, 0), NVL(PERIOD_NET_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'S', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) PERIOD_CR ,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'S', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0)), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) + DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_DR, 0), 'S', NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_DR, 0), NVL(PERIOD_NET_DR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'S', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR_BEQ, 0)          + NVL(PERIOD_NET_DR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0), 0))))) - DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_CR, 0), 'S', NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_CR, 0), NVL(PERIOD_NET_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'S', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) ENDING_BALANCE
FROM GL_BALANCES BAL,
  GL_CODE_COMBINATIONS CC,
  GL_LEDGERS L,
  GL_LEDGER_SET_ASSIGNMENTS ASG,
  GL_LEDGER_RELATIONSHIPS LR
WHERE BAL.ACTUAL_FLAG = 'A'
AND BAL.CURRENCY_CODE = (select CURRENCY_CODE from gl_ledgers where ledger_id  = l.ledger_id)--nvl(:ENTERED_CURRENCY,BAL.CURRENCY_CODE)
AND BAL.PERIOD_NAME IN (
  :PERIOD_NAME, DECODE(
  :AMOUNT_YPE, 'PTD',
  :PERIOD_NAME, 'PJTD',
  :PERIOD_NAME, 'YTD',
  :FIRST_PERIOD))
AND BAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
  -- AND CC.CHART_OF_ACCOUNTS_ID = &STRUCT_NUM
AND CC.TEMPLATE_ID IS NULL
AND CC.SUMMARY_FLAG = 'N'
AND L.LEDGER_ID     = nvl(:P_LEDGER_ID,L.LEDGER_ID)
AND ASG.LEDGER_SET_ID(+)    = L.LEDGER_ID
AND LR.TARGET_LEDGER_ID     = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.SOURCE_LEDGER_ID     = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.TARGET_CURRENCY_CODE =  (select TARGET_CURRENCY_CODE from GL_LEDGER_RELATIONSHIPS where PRIMARY_LEDGER_ID = l.ledger_id and TARGET_LEDGER_SHORT_NAME = l.SHORT_NAME) --nvl(:LEDGER_CURRENCY,LR.TARGET_CURRENCY_CODE)--nvl(:LEDGER_CURRENCY,LR.TARGET_CURRENCY_CODE)
AND LR.SOURCE_LEDGER_ID = BAL.LEDGER_ID
AND LR.TARGET_LEDGER_ID = BAL.LEDGER_ID
 and  nvl(bal.translated_flag, 'x') != 'R'
 --and BAL.LEDGER_ID IN ( SELECT acc.ledger_id FROM gl_access_set_ledgers acc WHERE acc.access_set_id = 1521 )
 --and CC.SEGMENT1 = :SEGMENT1_LOW--  and cc.segment3 = '1121024'
GROUP BY cc.SEGMENT4 , cc.SEGMENT3 , cc.segment1,L.LEDGER_ID ,cc.segment5,cc.segment6,cc.segment7,cc.segment2,L.Name
ORDER BY L.LEDGER_ID , segment3
) where nvl(BEGIN_BALANCE,0) != 0 or nvl(PERIOD_DR,0) != 0 or nvl(PERIOD_CR,0) != 0 or nvl(ENDING_BALANCE,0) != 0

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