Sourcing
Rule:
- Sourcing Rule Provides list of approved suppliers where we can rank the suppliers on a percentage basis. This is useful for the buyers/requestors at the time of making purchase for sourcing rule item.Purchasing picks the name of the supplier on the rank basis for the purchase agreement and quotation to any requisition for this item.
2. Sourcing
Rule Can be Local or Global(All Org).
Replenishment Methods:
1. Make At : It will make any items associated with the rule at receiving
Organization.
2. Buy From : It will buy any items associated
with the rule from the supplier.
3. Transfer From : It specifies the
different Organization with in the enterprise as source
of items associated with this rule.
Possible
Validations:
Allocation Percent must have Value.
Rank must have Value.
Effective Date should be equal to sysdate
or greater than sysdate.
Source Type Must Have Value.
If Source type is “BUY FROM", Supplier
is mandatory .
If Source type is “TRANSFER FROM”, Organization
is must.
Code
for reference:
PROCEDURE create_sourcing_rules
IS
lc_return_status VARCHAR2
(1);
ln_msg_count
NUMBER
:= 0;
lc_msg_data VARCHAR2
(1000);
lc_msg_data_temp VARCHAR2
(1000);
ln_msg_index_out NUMBER;
ln_count NUMBER;
ln_err_count
NUMBER
:= 0;
l_sourcing_rule_rec
mrp_sourcing_rule_pub.sourcing_rule_rec_type;
l_sourcing_rule_val_rec
mrp_sourcing_rule_pub.sourcing_rule_val_rec_type;
l_receiving_org_tbl
mrp_sourcing_rule_pub.receiving_org_tbl_type;
l_receiving_org_val_tbl
mrp_sourcing_rule_pub.receiving_org_val_tbl_type;
l_shipping_org_tbl
mrp_sourcing_rule_pub.shipping_org_tbl_type;
l_shipping_org_val_tbl
mrp_sourcing_rule_pub.shipping_org_val_tbl_type;
o_sourcing_rule_rec
mrp_sourcing_rule_pub.sourcing_rule_rec_type;
o_sourcing_rule_val_rec
mrp_sourcing_rule_pub.sourcing_rule_val_rec_type;
o_receiving_org_tbl
mrp_sourcing_rule_pub.receiving_org_tbl_type;
o_receiving_org_val_tbl
mrp_sourcing_rule_pub.receiving_org_val_tbl_type;
o_shipping_org_tbl mrp_sourcing_rule_pub.shipping_org_tbl_type;
o_shipping_org_val_tbl
mrp_sourcing_rule_pub.shipping_org_val_tbl_type;
ln_org_id NUMBER;
ln_vendor_id NUMBER;
ln_ship_org NUMBER;
ln_source NUMBER;
ln_rule_id NUMBER;
ln_receipt NUMBER;
ln_site_id NUMBER;
BEGIN
fnd_file.put_line (fnd_file.LOG, 'create_sourcing_rules');
fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),
fnd_profile.VALUE ('RESP_ID'),
fnd_profile.VALUE ('RESP_APPL_ID'),
NULL,
NULL
);
FOR i IN (SELECT *
FROM XX_STG_TBL
WHERE process_flag = 'V')
LOOP
ln_org_id := NULL;
ln_vendor_id := NULL;
ln_ship_org := NULL;
ln_source := NULL;
ln_rule_id := NULL;
ln_receipt := NULL;
ln_site_id := NULL;
BEGIN
SELECT organization_id
INTO ln_org_id
FROM org_organization_definitions
WHERE organization_code = i.org_code;
EXCEPTION
WHEN OTHERS
THEN
ln_org_id := NULL;
DBMS_OUTPUT.put_line ('No Org
Exists' || i.org_code);
fnd_file.put_line (fnd_file.LOG,
'No Org Exists' || i.org_code);
END;
BEGIN
SELECT lookup_code
INTO ln_source
FROM mfg_lookups
WHERE lookup_type = 'MRP_SOURCE_TYPE'
AND UPPER (meaning) = UPPER (TRIM
(i.source_type));
EXCEPTION
WHEN OTHERS
THEN
ln_ship_org := NULL;
DBMS_OUTPUT.put_line ('No
shipping Org Exists'
||
i.shipping_org
);
fnd_file.put_line (fnd_file.LOG,
'Error in
Selecting Lookup Code'
||
i.source_type
);
END;
BEGIN
SELECT organization_id
INTO ln_ship_org
FROM org_organization_definitions
WHERE organization_code = i.shipping_org;
EXCEPTION
WHEN OTHERS
THEN
ln_ship_org := NULL;
DBMS_OUTPUT.put_line ('No
shipping Org Exists'
||
i.shipping_org
);
fnd_file.put_line (fnd_file.LOG,
'No shipping
Org Exists' || i.shipping_org
);
END;
BEGIN
SELECT vendor_id
INTO ln_vendor_id
FROM ap_suppliers
WHERE TRIM (vendor_name) = i.supplier_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in
Selecting Vendor id'
||
i.supplier_name
);
END;
BEGIN
SELECT vendor_site_id
INTO ln_site_id
FROM ap_supplier_sites_all
WHERE vendor_id = ln_vendor_id
AND TRIM (vendor_site_code) =
i.vendor_site_code;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in
Selecting Vendor Site'
||
ln_vendor_id
);
END;
l_sourcing_rule_rec := mrp_sourcing_rule_pub.g_miss_sourcing_rule_rec;
l_receiving_org_tbl := mrp_sourcing_rule_pub.g_miss_receiving_org_tbl;
l_shipping_org_tbl := mrp_sourcing_rule_pub.g_miss_shipping_org_tbl;
BEGIN
SELECT sourcing_rule_id
INTO ln_rule_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = i.sourcing_rule_name
AND NVL (organization_id, 0) =
NVL (ln_org_id, 0);
EXCEPTION
WHEN OTHERS
THEN
ln_rule_id := NULL;
END;
IF ln_rule_id IS NULL
THEN
l_sourcing_rule_rec.operation := 'CREATE';
l_receiving_org_tbl (1).operation := 'CREATE';
ELSE
SELECT sr_receipt_id
INTO ln_receipt
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = ln_rule_id;
l_sourcing_rule_rec.sourcing_rule_id := ln_rule_id;
l_receiving_org_tbl (1).operation := 'UPDATE';
l_receiving_org_tbl (1).sr_receipt_id := ln_receipt;
l_shipping_org_tbl (1).sr_receipt_id := ln_receipt;
END IF;
l_sourcing_rule_rec.sourcing_rule_name := i.sourcing_rule_name;
l_sourcing_rule_rec.description :=
i.description;
l_sourcing_rule_rec.organization_id := ln_org_id;
l_sourcing_rule_rec.planning_active := 1;
l_sourcing_rule_rec.status := 1;
l_sourcing_rule_rec.sourcing_rule_type := 1;
--l_sourcing_rule_rec.operation := 'CREATE';
-- l_receiving_org_tbl :=
mrp_sourcing_rule_pub.g_miss_receiving_org_tbl;
-- l_shipping_org_tbl := mrp_sourcing_rule_pub.g_miss_shipping_org_tbl;
l_receiving_org_tbl (1).effective_date := i.from_date;
l_receiving_org_tbl (1).disable_date := i.TO_DATE;
l_receiving_org_tbl (1).receipt_organization_id := ln_org_id;
--l_receiving_org_tbl (1).sr_receipt_id := 21008;
l_shipping_org_tbl (1).RANK := i.RANK;
l_shipping_org_tbl (1).allocation_percent := i.allocation_percent;
l_shipping_org_tbl (1).source_type := ln_source;
l_shipping_org_tbl (1).receiving_org_index := 1;
l_shipping_org_tbl (1).operation := 'CREATE';
IF UPPER (i.source_type) LIKE 'BUY%'
THEN
l_shipping_org_tbl (1).vendor_id := ln_vendor_id;
l_shipping_org_tbl (1).source_organization_id := NULL;
l_shipping_org_tbl (1).vendor_site_id := ln_site_id;
ELSE
l_shipping_org_tbl (1).source_organization_id := ln_ship_org;
mrp_sourcing_rule_pub.process_sourcing_rule
(p_api_version_number
=> 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_sourcing_rule_rec =>
l_sourcing_rule_rec,
p_sourcing_rule_val_rec =>
l_sourcing_rule_val_rec,
p_receiving_org_tbl => l_receiving_org_tbl,
p_receiving_org_val_tbl =>
l_receiving_org_val_tbl,
p_shipping_org_tbl =>
l_shipping_org_tbl,
p_shipping_org_val_tbl => l_shipping_org_val_tbl,
x_sourcing_rule_rec =>
o_sourcing_rule_rec,
x_sourcing_rule_val_rec =>
o_sourcing_rule_val_rec,
x_receiving_org_tbl => o_receiving_org_tbl,
x_receiving_org_val_tbl =>
o_receiving_org_val_tbl,
x_shipping_org_tbl =>
o_shipping_org_tbl,
x_shipping_org_val_tbl => o_shipping_org_val_tbl
);
IF lc_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Success!');
UPDATE XX_STG_TBL
SET process_flag = 'S',
sourcing_rule_id =
o_sourcing_rule_rec.sourcing_rule_id
WHERE sourcing_rule_name = i.sourcing_rule_name
AND record_id = i.record_id;
fnd_file.put_line (fnd_file.LOG, 'Success');
ELSE
DBMS_OUTPUT.put_line ('count:' || ln_msg_count);
IF ln_msg_count > 0
THEN
FOR l_index IN 1 .. ln_msg_count
LOOP
lc_msg_data := lc_msg_data ||
CHR (13) || lc_msg_data_temp;
lc_msg_data_temp :=
fnd_msg_pub.get
(p_msg_index => l_index,
p_encoded =>
fnd_api.g_false
);
DBMS_OUTPUT.put_line (SUBSTR
(lc_msg_data, 1, 250));
BEGIN
UPDATE xx_mrp_stg
SET process_flag =
'IE',
error_message =
lc_msg_data_temp
WHERE sourcing_rule_name
= i.sourcing_rule_name
AND record_id =
i.record_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('Error in Update');
fnd_file.put_line
(fnd_file.LOG,
'Error in Update' || SQLERRM
);
END;
COMMIT;
END LOOP;
DBMS_OUTPUT.put_line ( 'MSG:'
||
o_sourcing_rule_rec.return_status
);
END IF;
DBMS_OUTPUT.put_line ('Failure!');
fnd_file.put_line (fnd_file.LOG, 'Failure');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error in Calling Api ' || SQLERRM);
END;
Base
Tables :
MRP_SOURCING_RULES
MRP_SR_SOURCE_ORG
MRP_SR_RECEIPT_ORG
Can you please provide the columns fro the staging tables
ReplyDelete