Memory Based Snapshot 64 bit Linux is stuck at BOM_CUR (Doc ID 2820688.1)

Last updated on JUNE 15, 2022

Applies to:

Symptoms

On versions: 12.2.6 and above , Memory Based Snapshot 64 bit Linux

When attempting to run the plan, Memory Based Snapshot 64 bit Linux is stuck with the below step

ERROR
-----------------------
Opening BOM_CUR:
BIND cutoff_date: 2459540
BIND plan_type: 2
BIND plan_id: 26
BIND item_simulation_set: 4294943843
BIND mps_recal_flag: 2
BIND bor_id: 4294943843
Opening BOM_CUR

The following SQL is hanging:

SELECT
  /*+ ORDERED */
  mbc.inventory_item_id,
  mbc.organization_id,
  mbc.sr_instance_id,
  boms.assembly_item_id,
  mbc.organization_id,
  mbc.sr_instance_id ,
  items_comp.mrp_planning_code ,
  items.mrp_planning_code ,
  items_comp.drp_planned ,
  items.drp_planned ,
  -23453,
  TO_CHAR(mbc.effectivity_date, 'J') ,
  NVL(TO_CHAR(mbc.disable_date, 'J'), -23453),
  NVL(mbc.use_up_code,2),
  mbc.usage_quantity,
  NVL(mbc.driving_item_id, -23453) ,
  NVL(boms.alternate_bom_designator, '-23453'),
  items.effectivity_control,
  NVL(mbc.component_type, -23453),
  items.critical_component_flag ,
  items_comp.critical_component_flag
FROM msc_plan_organizations_v mpo,
  msc_system_items items,
  msc_bomsboms ,
  msc_bom_components mbc,
  msc_trading_partners mtp,
  msc_system_items items_comp
WHERE NVL(mbc.disable_date, TRUNC(SYSDATE)                        + 1) >
TRUNC(SYSDATE)
AND mbc.effectivity_date                        < to_date(2459540 + 1,'J')
AND 1                                           =1
AND items_comp.plan_id                          = mbc.plan_id
AND (items_comp.inventory_item_flag             = 1
OR items_comp.engineering_item_flag             = 1
OR items_comp.eam_item_type                    IS NOT NULL )
AND (items_comp.bom_item_type                   > 3
OR items_comp.bom_item_type                     < 3)
AND items_comp.planning_make_buy_code          IN(1, 2)
AND ((:plan_type                                = 5
AND ( items.drp_planned                         = 1
OR items_comp.drp_planned                       = 1)
AND mtp.organization_type                      != 2)
OR ((:plan_type                                != 5)
AND ( items_comp.mrp_planning_code             <> 6
OR (items.bom_item_type                        IN (1,2)))))
AND items_comp.uom_code                        IS NOT NULL
AND items_comp.sr_instance_id                   = mbc.sr_instance_id
AND items_comp.organization_id                  = mbc.organization_id
AND mbc.inventory_item_id                       =
items_comp.inventory_item_id
AND mbc.plad
AND mbc.bill_sequence_id           = boms.bill_sequence_id
AND boms.plan_id                   = items.plan_id
AND boms.sr_instance_id            = items.sr_instance_id
AND boms.organization_id           = items.organization_id
AND boms.assembly_item_id          = items.inventory_item_id
AND items.mrp_planning_code       IS NOT NULL
AND (items.inventory_item_flag     = 1
OR items.engineering_item_flag     = 1
OR items_comp.eam_item_type       IS NOT NULL )
AND ( items.planning_make_buy_code = 1
OR items.eam_item_type            IS NOT NULL
OR (:mps_bor_plan                  = 2
AND EXISTS
  (SELECT NULL
  FROM msc_sources_v mis
  WHERE items.inventory_item_id = mis.inventory_item_id
  AND items.organization_id     = mis.organization_id
  AND items.sr_instance_id      = mis.sr_instance_id
  AND items.organization_id     =
NVL(mis.source_organization_id,DECODE(mis.source_type,2,mis.organization_id,NU
LL))
  AND items.sr_instance_id      =
NVL(mis.source_org_instance_id,DECODE(mis.source_type,2,mis.sr_instance_id,NUL
L))
  AND mpo.assignment_set_id     = mis.assignment_set_id
  AND :plan_type               <>7
  AND :plan_type               <> 6
  )) )
AND ((:plan_type               =5
AND ( items.drp_planned        = 1
OR items_comp.drp_planned      = 1))
OR ( (:plan_type              != 5)
AND ( items.mrp_planning_code <> 6
OR (items.bom_item_type       IN (1,2)))))
AND items.uom_code            IS NOT NULL
AND items.plan_id              = -1
AND items.sr_instance_id       = mpo.sr_instance_id
AND items.organization_id      = mpo.planned_organization
AND mpo.plan_id                = :plan_id
AND mpo.planned_organization   = mtp.sr_tp_id
AND mpo.sr_instance_id         = mtp.sr_instance_id
AND mtp.partner_type           = 3
AND 1                          =1
ORDER BY boms.assembly_item_id,
  mbc.inventory_item_id,
  mbc.effectivity_date DESC,
  boms.alternate_bom_designator

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Log into ASCP Responsibility
2. Run the ASCP Plan
3. Review the log file


 

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.