Some OPM Routing And Operations Not Collected In ASCP (Doc ID 2497953.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

There is make item I00041-15 in HW3 organization.
That item has formula, recipe, VR, routing in EBS (source) side.
After ASCP collections completed, I can see that in VCP (destination) item I00041-15 has process effectivity collected. But it is missing routing and operations.

They do not use Formula security

We found the following
While resource PLM-DEF is assigned to 6 organizations (21735, 21736, 21737, 21741, 24193 and 25373), the other resource ITEM CONVERSION is assigned only to org_id 21735.

 based on the log file "Planning Data Pull Worker_20sep2018.txt", I see many messages related to "routing_id 1671", like:

validate_routing returned Invalid for Organization_id 21741 routing_id 1671 rtg_hdr_location 2073fmeff_id 129609
or
find_routing_header returned Invalid for Organization_id 21768 routing_id 1671
find_routing_header returned Invalid for Organization_id 511 routing_id 1671
find_routing_header returned Invalid for Organization_id 13729 routing_id 1671
find_routing_header returned Invalid for Organization_id 19839 routing_id 1671
find_routing_header returned Invalid for Organization_id 21741 routing_id 1671
find_routing_header returned Invalid for Organization_id 21736 routing_id 1671

NEXT:
They said they cleaned up all the errors for this item/routing - but still now collected

Got logs
 Planning_Data_Pull_log_files_2nov2018.zip in file Planning Data Pull Worker 1.txt
we see the MSC_CL_BOM_PULL.LOAD_BOM started

Do not see 1671 listed in the log

Do see many other errors and also 25 lines like
Error writing to msc_st_routings: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

So had them run all the sql to check data.
>> they reported For SQL Nr 3, 4, 5, 10, 13, 14, 15, 16 no rows returned.
>> all results in file - SQL_Output_ToOracle_2nov2018.xlsx

#1
select * from gmd_recipes_b where recipe_no = '&RECIPE_NO' and recipe_version = &VERSION;

#2
select * from gmd_recipe_validity_rules where recipe_id =
(select recipe_id from gmd_recipes_b where recipe_no = '&RECIPE_NO' and recipe_version = &VERSION);

#3
select * from fm_form_mst_b where formula_id =
(select formula_id from gmd_recipes_b where recipe_no = '&RECIPE_NO' and recipe_version = &VERSION);

#4
select * from fm_matl_dtl where formula_id =
(select formula_id from gmd_recipes_b where recipe_no = '&RECIPE_NO' and recipe_version = &VERSION);

#5
select * from gmd_routings_b where routing_id =
(select routing_id from gmd_recipes_b where recipe_no = '&RECIPE_NO' and recipe_version = &VERSION);

----- then ROUTING details - use routing_id from #1

#6
select * from fm_rout_dtl where routing_id = &routing_id;

#7
select * from GMD_OPERATION_ACTIVITIES where oprn_id in
(select oprn_id from fm_rout_dtl where routing_id = &routing_id);

#8
select * from GMD_OPERATION_RESOURCES where oprn_line_id in
(select oprn_line_id from GMD_OPERATION_ACTIVITIES where oprn_id in (select oprn_id from fm_rout_dtl where routing_id = &routing_id));

#9
select * from cr_rsrc_dtl where resources in
(select resources from GMD_OPERATION_RESOURCES where oprn_line_id in
(select oprn_line_id from GMD_OPERATION_ACTIVITIES where oprn_id in (select oprn_id from fm_rout_dtl where routing_id = &routing_id)));


#10
routing_hdr_cursor, as per following query:
-- isolated for single recipe ID from single org_id

SELECT UNIQUE frh.routing_id, mp.organization_id
FROM mtl_parameters mp,
fm_rout_hdr frh,
gmd_recipes_b grb,
gmd_recipe_validity_rules ffe,
hr_organization_units hou,
gmd_status_b gs
WHERE grb.recipe_id = ffe.recipe_id
AND ffe.validity_rule_status = gs.status_code
AND gs.status_type IN ('700','900','400')
AND gs.delete_mark = 0
AND NVL(ffe.end_date,(SYSDATE + 1)) > SYSDATE
AND frh.routing_id = grb.routing_id
AND NVL(hou.date_to,SYSDATE) >= SYSDATE
AND hou.organization_id = mp.organization_id
AND NVL(ffe.organization_id, mp.organization_id) = mp.organization_id
AND mp.process_enabled_flag = 'Y'
AND mp.organization_id IN (&org_id)
and grb.recipe_id = &recipe_id
AND ffe.delete_mark = 0
AND frh.delete_mark = 0
AND frh.inactive_ind = 0
ORDER BY frh.routing_id, mp.organization_id;



-- use validity_rule_id from #2 above when prompted

select * from msc_boms@EBSPRD_TO_VCP_VCPPRD.WORLD
where bill_sequence_id in ( select aps_fmeff_id*2 +1
  from GMP_FORM_EFF
  where fmeff_id = &validity_rule_id)
and plan_id = -1;

select * from msc_bom_components@EBSPRD_TO_VCP_VCPPRD.WORLD
where bill_sequence_id in ( select aps_fmeff_id*2 +1
  from GMP_FORM_EFF
  where fmeff_id = &validity_rule_id)
and plan_id = -1;

SELECT *
FROM msc_routings@EBSPRD_TO_VCP_VCPPRD.WORLD
where routing_sequence_id in ( select aps_fmeff_id*2 +1
  from GMP_FORM_EFF
  where fmeff_id = &validity_rule_id)
AND plan_id = -1;

SELECT *
from msc_routing_operations@EBSPRD_TO_VCP_VCPPRD.WORLD
where routing_sequence_id in ( select aps_fmeff_id*2 +1
  from GMP_FORM_EFF
  where fmeff_id = &validity_rule_id)
and plan_id = -1;

SELECT *
from msc_operation_resources@EBSPRD_TO_VCP_VCPPRD.WORLD
where routing_sequence_id in ( select aps_fmeff_id*2 +1
  from GMP_FORM_EFF
  where fmeff_id = &validity_rule_id)
and plan_id = -1;

select * from
msc_operation_resource_seqs@EBSPRD_TO_VCP_VCPPRD.WORLD
where routing_sequence_id in ( select aps_fmeff_id*2 +1
  from GMP_FORM_EFF
  where fmeff_id = &validity_rule_id)
and plan_id = -1;

Cause

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