Planning Data Pull Concurrent Program Running Long While Pulling Data From MRP_AP_COMPONENT_SUBSTITUTES_V
(Doc ID 3045735.1)
Last updated on SEPTEMBER 04, 2024
Applies to:
Oracle Advanced Supply Chain Planning - Version 12.2.6.3 and laterInformation in this document applies to any platform.
Symptoms
When attempting to run the Data Collections, the the Planning Data Pull/Worker (MSCPDP/MSCPDPW) is taking a long time to complete. Upon analyzing the AWR report, the following problem SQL was found:
ASCP AWR
INSERT INTO MSC_ST_COMPONENT_SUBSTITUTES ( BILL_SEQUENCE_ID, COMPONENT_SEQUENCE_ID, SUBSTITUTE_ITEM_ID, USAGE_QUANTITY, ORGANIZATION_ID, PRIORITY, DELETED_FLAG, ROUNDING_DIRECTION, REFRESH_ID, SR_INSTANCE_ID)
SELECT x.BILL_SEQUENCE_ID, x.COMPONENT_SEQUENCE_ID, x.SUBSTITUTE_ITEM_ID, x.USAGE_QUANTITY, x.ORGANIZATION_ID, NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority, 1, x.Attribute1, 2, x.Attribute2, 3, x.Attribute3, 4, x.Attribute4, 5, x.Attribute5, 6, x.Attribute6, 7, x.Attribute7, 8, x.Attribute8, 9, x.Attribute9, 10, x.Attribute10, 11, x.Attribute11, 12, x.Attribute12, 13, x.Attribute13, 14, x.Attribute14, 15, x.Attribute15)), 2), 2, decode(x.rounding_direction, 0, 3, 1, 2, 2, 1, 3), :v_refresh_id, :v_instance_id FROM MRP_AP_COMPONENT_SU BSTITUTES_V@APS_TO_ERP x WHERE x.ORGANIZATION_ID IN (524, 545, 265, 176, 167, 254, 139, 327, 348, 349, 325, 326, 255, 256, 257, 284, 304, 243, 244, 245, 250, 251, 258, 259, 260, 261, 262, 263, 264, 266, 166, 168, 169, 170, 171, 172, 173, 174, 175, 177, 428, 587, 425, 445, 446, 447, 453, 448, 449, 450, 455, 451, 824, 924, 944, 949, 925, 945, 950, 926, 927, 1095, 1135, 1136, 1255, 585, 624, 625, 386, 387, 706, 704, 684, 705, 724, 725, 188, 184, 505, 506, 507, 508, 509, 510, 511, 512, 181, 975, 344, 345, 346)
SELECT x.BILL_SEQUENCE_ID, x.COMPONENT_SEQUENCE_ID, x.SUBSTITUTE_ITEM_ID, x.USAGE_QUANTITY, x.ORGANIZATION_ID, NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority, 1, x.Attribute1, 2, x.Attribute2, 3, x.Attribute3, 4, x.Attribute4, 5, x.Attribute5, 6, x.Attribute6, 7, x.Attribute7, 8, x.Attribute8, 9, x.Attribute9, 10, x.Attribute10, 11, x.Attribute11, 12, x.Attribute12, 13, x.Attribute13, 14, x.Attribute14, 15, x.Attribute15)), 2), 2, decode(x.rounding_direction, 0, 3, 1, 2, 2, 1, 3), :v_refresh_id, :v_instance_id FROM MRP_AP_COMPONENT_SU BSTITUTES_V@APS_TO_ERP x WHERE x.ORGANIZATION_ID IN (524, 545, 265, 176, 167, 254, 139, 327, 348, 349, 325, 326, 255, 256, 257, 284, 304, 243, 244, 245, 250, 251, 258, 259, 260, 261, 262, 263, 264, 266, 166, 168, 169, 170, 171, 172, 173, 174, 175, 177, 428, 587, 425, 445, 446, 447, 453, 448, 449, 450, 455, 451, 824, 924, 944, 949, 925, 945, 950, 926, 927, 1095, 1135, 1136, 1255, 585, 624, 625, 386, 387, 706, 704, 684, 705, 724, 725, 188, 184, 505, 506, 507, 508, 509, 510, 511, 512, 181, 975, 344, 345, 346)
EBS AWR
SELECT /*+ OPAQUE_TRANSFORM */ "BILL_SEQUENCE_ID", "COMPONENT_SEQUENCE_ID", "SUBSTITUTE_ITEM_ID", "USAGE_QUANTITY", "ORGANIZATION_ID", "ATTRIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3", "ATTRIBUTE4", "ATTRIBUTE5", "ATTRIBUTE6", "ATTRIBUTE7", "ATTRIBUTE8", "ATTRIBUTE9", "ATTRIBUTE10", "ATTRIBUTE11", "ATTRIBUTE12", "ATTRIBUTE13", "ATTRIBUTE14", "ATTRIBUTE15", "ROUNDING_DIRECTION" FROM "MRP_AP_COMPONENT_SUBSTITUTES_V" "X" WHERE "ORGANIZATION_ID"=139 OR "ORGANIZATION_ID"=166 OR "ORGANIZATION_ID"=167 OR "ORGANIZATION_ID"=168 OR "ORGANIZATION_ID"=169 OR "ORGANIZATION_ID"=170 OR "ORGANIZATION_ID"=171 OR "ORGANIZATION_ID"=172 OR "ORGANIZATION_ID"=173 OR "ORGANIZATION_ID"=174 OR "ORGANIZATION_ID"=175 OR "ORGANIZATION_ID"=176 OR "ORGANIZATION_ID"=177 OR "ORGANIZATION_ID"=181 OR "ORGANIZATION_ID"=184 OR "ORGANIZATION_ID"=188 OR "ORGANIZATION_ID"=243 OR "ORGANIZATION_ID"=244 OR "ORGANIZATION_ID"=245 OR "ORGANIZATION_ID"=250 OR "ORGANIZATION_ID"=251 OR "ORGANIZATION_ID"=254 OR "ORGANIZATION_ID"=255 OR "ORGANIZATION_ID"=256 OR "ORGANIZATION_ID"=257 OR "ORGANIZATION_ID"=258 OR "ORGANIZATION_ID"=259 OR "ORGANIZATION_ID"=260 OR "ORGANIZATION_ID"=261 OR "ORGANIZATION_ID"=262 OR "ORGANIZATION_ID"=263 OR "ORGANIZATION_ID"=264 OR "ORGANIZATION_ID"=265 OR "ORGANIZATION_ID"=266 OR "ORGANIZATION_ID"=284 OR "ORGANIZATION_ID"=304 OR "ORGANIZATION_ID"=325 OR "ORGANIZATION_ID"=326 OR "ORGANIZATION_I D"=327 OR "ORGANIZATION_ID"=344 OR "ORGANIZATION_ID"=345 OR "ORGANIZATION_ID"=346 OR "ORGANIZATION_ID"=348 OR "ORGANIZATION_ID"=349 OR "ORGANIZATION_ID"=386 OR "ORGANIZATION_ID"=387 OR "ORGANIZATION_ID"=425 OR "ORGANIZATION_ID"=428 OR "ORGANIZATION_ID"=445 OR "ORGANIZATION_ID"=446 OR "ORGANIZATION_ID"=447 OR "ORGANIZATION_ID"=448 OR "ORGANIZATION_ID"=449 OR "ORGANIZATION_ID"=450 OR "ORGANIZATION_ID"=451 OR "ORGANIZATION_ID"=453 OR "ORGANIZATION_ID"=455 OR "ORGANIZATION_ID"=505 OR "ORGANIZATION_ID"=506 OR "ORGANIZATION_ID"=507 OR "ORGANIZATION_ID"=508 OR "ORGANIZATION_ID"=509 OR "ORGANIZATION_ID"=510 OR "ORGANIZATION_ID"=511 OR "ORGANIZATION_ID"=512 OR "ORGANIZATION_ID"=524 OR "ORGANIZATION_ID"=545 OR "ORGANIZATION_ID"=585 OR "ORGANIZATION_ID"=587 OR "ORGANIZATION_ID"=624 OR "ORGANIZATION_ID"=625 OR "ORGANIZATION_ID"=684 OR "ORGANIZATION_ID"=704 OR "ORGANIZATION_ID"=705 OR "ORGANIZATION_ID"=706 OR "ORGANIZATION_ID"=724 OR "ORGANIZATION_ID"=725 OR "ORGANIZATION_ID"=824 OR "ORGANIZATION_ID"=924 OR "ORGANIZATION_ID"=925 OR "ORGANIZATION_ID"=926 OR "ORGANIZATION_ID"=927 OR "ORGANIZATION_ID"=944 OR "ORGANIZATION_ID"=945 OR "ORGANIZATION_ID"=949 OR "ORGANIZATION_ID"=950 OR "ORGANIZATION_ID"=975 OR "ORGANIZATION_ID"=1095 OR "ORGANIZATION_ID"=1135 OR "ORGANIZATION_ID"=1136 OR "ORGANIZATION_ID"=1255
The issue can be reproduced at will with the following steps:
- Responsibility: Advanced Supply Chain Planner
- Navigation: Collections > Oracle Systems > Standard Collections.
- For Collection Method parameter, enter Targeted Refresh.
- Ensure that the Bills of Materials/Routings/Resources parameter is set to Yes.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |