ASL Setup Validation
ASL (Approved supplier list) setup validation for Local and Global
The local setup we have to pass the parameter location code, item and Vendor code for global we have pass item and Vendor code
BEGIN
— Local Setup validation
SELECT COUNT (*)
INTO v_asl_count
FROM apps.mrp_sr_assignments_v msra,
mrp_assignment_sets mas,
mrp_sourcing_rules msr,
mrp_sr_source_org msso,
apps.hr_locations ood,
po_vendors v,
po_vendor_sites_all vs,
mrp_sr_receipt_org sro,
mtl_interorg_ship_methods sm
WHERE msr.sourcing_rule_id = msra.sourcing_rule_id
AND msra.assignment_set_id = mas.assignment_set_id
AND ood.inventory_organization_id = msra.organization_id
AND mas.assignment_set_name LIKE ‘%Supplier Scheduling%’
AND v.vendor_id(+) = msso.vendor_id
AND vs.vendor_site_id(+) = msso.vendor_site_id
AND msr.sourcing_rule_id = sro.sourcing_rule_id
AND sm.to_organization_id(+) = sro.receipt_organization_id
AND sro.sr_receipt_id = msso.sr_receipt_id
AND msso.ship_method IS NULL
AND v.segment1 = :vendor_code
AND ood.location_code = :destination_organization
AND msra.entity_name = :item;
IF NVL (v_asl_count, 0) > 0
THEN
NULL;
ELSE
— Global setup Validation
SELECT COUNT (*)
INTO v_asl_count
FROM apps.mrp_sr_assignments msra,
mrp_assignment_sets mas,
mrp_sourcing_rules msr,
mrp_sr_source_org msso,
apps.hr_locations ood,
po_vendors v,
po_vendor_sites_all vs,
mrp_sr_receipt_org sro,
mtl_interorg_ship_methods sm
WHERE msr.sourcing_rule_id = msra.sourcing_rule_id
AND msra.assignment_set_id = mas.assignment_set_id
AND ood.inventory_organization_id(+) = msra.organization_id
AND mas.assignment_set_name LIKE ‘%Supplier Scheduling%’
AND v.vendor_id(+) = msso.vendor_id
AND vs.vendor_site_id(+) = msso.vendor_site_id
AND msr.sourcing_rule_id = sro.sourcing_rule_id
AND sm.to_organization_id(+) = sro.receipt_organization_id
AND sro.sr_receipt_id = msso.sr_receipt_id
AND msso.ship_method IS NULL
AND v.segment1 = var.vendor_code
AND msra.inventory_item_id = v_item_id
AND msra.assignment_type = 3;
IF NVL (v_asl_count, 0) > 0
THEN
NULL;
ELSE
fnd_file.put_line (fnd_file.LOG,
‘Sourcing Rule Validation Errorr……’
);
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
‘Sourcing Rule Validation Errorr……’
);
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
‘MULTI ROWS RETURN THIS Sourcing Rule Validation Error……’
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘NOT VALID Sourcing Rule Validation Error……’
);
END;
