APPS.CTO_MSUTIL_PUB dependencies on MRP_SR_ASSIGNMENTS

Line 3019: -- a full table scan on MRP_SR_ASSIGNMENTS and consequent

3015: --
3016: -- bug 6617686
3017: -- The MRP API uses a ASSIGNMENT_ID = p_Assignment_Id OR
3018: -- ASSIGNMENT_SET_ID = p_Assignment_Set_Id that leads to
3019: -- a full table scan on MRP_SR_ASSIGNMENTS and consequent
3020: -- performance issues. Since CTO does not pass ASSIGNMENT_SET_ID
3021: -- into the procedure, it is performance effective to directly
3022: -- query the MRP table
3023: -- ntungare

Line 3101: FROM MRP_SR_ASSIGNMENTS

3097: , lAssignmentRec.SECONDARY_INVENTORY
3098: , lAssignmentRec.SHIP_TO_SITE_ID
3099: , lAssignmentRec.SOURCING_RULE_ID
3100: , lAssignmentRec.SOURCING_RULE_TYPE
3101: FROM MRP_SR_ASSIGNMENTS
3102: WHERE ASSIGNMENT_ID = lAssignmentId;
3103:
3104: IF PG_DEBUG <> 0 THEN
3105: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'after query row',2);

Line 3136: from mrp_sr_assignments

3132: -- bug 6617686
3133: IF pConfigId IS NOT NULL THEN
3134: select 1
3135: into lAssignmentExists
3136: from mrp_sr_assignments
3137: where assignment_set_id = lUPGAssignmentSet /* lAssignmentRec.assignment_set_id commented for upgrade logic */
3138: and assignment_type = lAssignmentRec.assignment_type
3139: and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
3140: and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)

Line 3148: from mrp_sr_assignments

3144: and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
3145: ELSE
3146: select 1
3147: into lAssignmentExists
3148: from mrp_sr_assignments
3149: where assignment_set_id = lUPGAssignmentSet /* lAssignmentRec.assignment_set_id commented for upgrade logic */
3150: and assignment_type = lAssignmentRec.assignment_type
3151: and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
3152: and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)

Line 3181: SELECT mrp_sr_assignments_s.nextval

3177:
3178: --
3179: -- get assignment id for config item
3180: --
3181: SELECT mrp_sr_assignments_s.nextval
3182: INTO lConfigAssignmentId
3183: FROM DUAL;
3184:
3185: --

Line 3404: mrp_sr_assignments assg,

3400: assg.assignment_type
3401: from
3402: mrp_sr_receipt_org rcv,
3403: mrp_sr_source_org src,
3404: mrp_sr_assignments assg,
3405: mrp_sourcing_rules rule,
3406: po_vendor_sites_all vend
3407: where
3408: assg.assignment_set_id = c_def_assg_set

Line 3541: -- a full table scan on MRP_SR_ASSIGNMENTS and consequent

3537: --
3538: -- bug 6617686
3539: -- The MRP API uses a ASSIGNMENT_ID = p_Assignment_Id OR
3540: -- ASSIGNMENT_SET_ID = p_Assignment_Set_Id that leads to
3541: -- a full table scan on MRP_SR_ASSIGNMENTS and consequent
3542: -- performance issues. Since CTO does not pass ASSIGNMENT_SET_ID
3543: -- into the procedure, it is performance effective to directly
3544: -- query the MRP table
3545: -- ntungare

Line 3622: FROM MRP_SR_ASSIGNMENTS

3618: , lAssignmentRec.SECONDARY_INVENTORY
3619: , lAssignmentRec.SHIP_TO_SITE_ID
3620: , lAssignmentRec.SOURCING_RULE_ID
3621: , lAssignmentRec.SOURCING_RULE_TYPE
3622: FROM MRP_SR_ASSIGNMENTS
3623: WHERE ASSIGNMENT_ID = lAssignmentId;
3624:
3625: IF PG_DEBUG <> 0 THEN
3626: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after query row',2);

Line 3660: from mrp_sr_assignments

3656: -- bug 6617686
3657: IF pConfigId IS NOT NULL THEN
3658: select 1
3659: into lAssignmentExists
3660: from mrp_sr_assignments
3661: where assignment_set_id = lUPGAssignmentSet /* commented for upgrade issues lAssignmentRec.assignment_set_id */
3662: and assignment_type = lAssignmentRec.assignment_type
3663: and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
3664: and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)

Line 3672: from mrp_sr_assignments

3668: and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
3669: ELSE
3670: select 1
3671: into lAssignmentExists
3672: from mrp_sr_assignments
3673: where assignment_set_id = lUPGAssignmentSet /* commented for upgrade issues lAssignmentRec.assignment_set_id */
3674: and assignment_type = lAssignmentRec.assignment_type
3675: and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
3676: and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)

Line 3714: SELECT mrp_sr_assignments_s.nextval

3710: --
3711:
3712:
3713:
3714: SELECT mrp_sr_assignments_s.nextval
3715: INTO lConfigAssignmentId
3716: FROM DUAL;
3717:
3718: --