Update Sourcing Rule using API in Oracle
Sourcing rule is used for approved supplier list to determine
how much percentage of order can be shared among the suppliers on priority based
on Allocation percentage and Rank for Supplier.
I have used this blog to update sourcing rule effective date and disable date.
Here, we will update Sourcing Rule using below Base table data:
1) MRP_SOURCING_RULES
2) ORG_ORGANIZATION_DEFINITIONS
3) HR_OPERATING_UNITS
4) MRP_SR_RECEIPT_ORG
5) MRP_SR_SOURCE_ORG
6) AP_SUPPLIERS
7) AP_SUPPLIER_SITES_ALL
For update Sourcing Rule refer and follow below steps:
Step 1) Create Staging table:
CREATE TABLE XXPO_UPDATE_SR_STG
( sourcing_rule_id NUMBER
, sourcing_rule_name VARCHAR2(150 BYTE)
, description VARCHAR2(240 BYTE)
, organization_code VARCHAR2(20)
, status NUMBER
, sourcing_rule_type NUMBER
, planning_active NUMBER
, effective_date DATE
, disable_date DATE
, source_type NUMBER
, source_organization_code VARCHAR2(240)
, vendor_name VARCHAR2(800)
, vendor_id NUMBER
, vendor_site VARCHAR2(50)
, allocation_percent NUMBER
, rank NUMBER
, ship_method NUMBER
, operating_unit VARCHAR2(240 BYTE)
, sr_creation_date DATE
, sr_created_by NUMBER
, record_status VARCHAR2(10)
);
Step 2) Insert data in Staging table:
-- Get data using below Data extract query:
SELECT msr.sourcing_rule_id
,msr.sourcing_rule_name
,msr.description
,ood.organization_code
,msr.status
,msr.sourcing_rule_type
,msr.planning_active
,msrep.effective_date
,msrep.disable_date
,msour.source_type
,(SELECT organization_code FROM org_organization_definitions
WHERE organization_id = msour.source_organization_id) source_organization_code
,asup.vendor_name
,msour.vendor_id
,assa.vendor_site_code vendor_site
,msour.allocation_percent
,msour.rank
,msour.ship_method
,hou.name operating_unit
,msr.creation_date sr_creation_date
,msr.created_by sr_created_by
,'N' record_status
FROM mrp_sourcing_rules msr
, org_organization_definitions ood
, hr_operating_units hou
, mrp_sr_receipt_org msrep
, mrp_sr_source_org msour
, ap_suppliers asup
, ap_supplier_sites_all assa
WHERE msr.organization_id = ood.organization_id
AND ood.operating_unit = hou.organization_id
AND msr.sourcing_rule_id = msrep.sourcing_rule_id
AND msrep.sr_receipt_id = msour.sr_receipt_id
AND msour.vendor_id = asup.vendor_id(+)
AND msour.vendor_site_id = assa.vendor_site_id(+)
AND hou.organization_id = assa.org_id(+);
Step 3) PLSQL Script to update SR:
SET SERVEROUTPUT ON SIZE 999999;
DECLARE
lx_return_status VARCHAR2 (1);
lx_msg_count NUMBER;
lx_msg_data VARCHAR2 (1000);
l_receipt_idx NUMBER := 1;
l_source_idx NUMBER := 1;
l_uncheck_count NUMBER;
l_asl_count NUMBER;
ln_receipt NUMBER;
-- DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_err_count NUMBER := 0;
ln_cnt_rank NUMBER := 0;
j 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;
-- Validation variables
ln_organization_id NUMBER;
lc_rec_status VARCHAR2(10);
ln_sr_count NUMBER;
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
ln_sourcing_rule_id NUMBER;
lc_operation VARCHAR2(30);
ln_source_organization_id NUMBER;
ln_sr_source_id NUMBER;
--
CURSOR lcur_source_rule
IS
SELECT sourcing_rule_name, description, organization_code, status,
sourcing_rule_type, planning_active, effective_date, disable_date
FROM XXPO_UPDATE_SR_STG
WHERE 1 = 1 AND record_status = 'N'
GROUP BY sourcing_rule_name, description, organization_code, status,
sourcing_rule_type, planning_active, effective_date, disable_date;
CURSOR lcur_source_rule_ship(p_sour_rule_name IN VARCHAR2) -- Rank cursor
IS
SELECT * FROM XXPO_UPDATE_SR_STG
WHERE 1 = 1 AND record_status = 'N'
AND sourcing_rule_name = p_sour_rule_name;
BEGIN
FOR lreq_sr IN lcur_source_rule
LOOP -- First Loop
-- Variables initialization
dbms_output.put_line('First Loop.');
ln_organization_id := NULL;
lc_rec_status := 'S';
ln_sr_count := 0;
ln_vendor_id := NULL;
ln_vendor_site_id := NULL;
ln_sourcing_rule_id := NULL;
lc_operation := NULL;
ln_receipt := NULL;
ln_sr_source_id := NULL;
-- Validation Start
--1) Validation of Organization Code
BEGIN
SELECT organization_id INTO ln_organization_id
FROM org_organization_definitions
WHERE UPPER(organization_code) = UPPER(lreq_sr.organization_code);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Organization Code not found.');
lc_rec_status := 'E';
WHEN others THEN
dbms_output.put_line('Error in Organization Code is:'||sqlerrm);
lc_rec_status := 'E';
END;
--
--2) Sourcing Rule
BEGIN
SELECT sourcing_rule_id INTO ln_sourcing_rule_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = lreq_sr.sourcing_rule_name
AND NVL(organization_id, 0) = NVL(ln_organization_id, 0);
EXCEPTION
WHEN others THEN
ln_sourcing_rule_id := NULL;
END;
--
--3) Get receipt ID
BEGIN
SELECT sr_receipt_id
INTO ln_receipt
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = ln_sourcing_rule_id;
EXCEPTION
WHEN OTHERS THEN
ln_receipt := NULL;
dbms_output.put_line('SR Receipt ID not found against SR: '||lreq_sr.sourcing_rule_name);
END;
--
--4) Get SR_srouce_id
BEGIN
SELECT sr_source_id INTO ln_sr_source_id
FROM mrp_sr_source_org_v
WHERE sr_receipt_id = ln_receipt;
EXCEPTION
WHEN others THEN
dbms_output.put_line('SR Source ID not found against SR: '||lreq_sr.sourcing_rule_name);
END;
--
--5) Get Rank staging table
BEGIN
SELECT COUNT (RANK) INTO ln_cnt_rank
FROM xxpo_update_sr_stg
WHERE sourcing_rule_name = lreq_sr.sourcing_rule_name;
EXCEPTION
WHEN others THEN
dbms_output.put_line('Rank not found against SR: '||lreq_sr.sourcing_rule_name);
END;
--
dbms_output.put_line('ln_sourcing_rule_id:'||ln_sourcing_rule_id);
--
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;
l_sourcing_rule_rec.operation := 'UPDATE';--lc_operation;
l_receiving_org_tbl (1).operation := 'UPDATE'; -- Create or Update
l_sourcing_rule_rec.sourcing_rule_name := lreq_sr.sourcing_rule_name;
l_sourcing_rule_rec.sourcing_rule_id := ln_sourcing_rule_id;
l_sourcing_rule_rec.description := lreq_sr.description;
l_sourcing_rule_rec.organization_id := ln_organization_id;
l_sourcing_rule_rec.planning_active := lreq_sr.planning_active; -- Active
l_sourcing_rule_rec.status := lreq_sr.status; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := lreq_sr.sourcing_rule_type;
l_receiving_org_tbl (1).sr_receipt_id := ln_receipt;
l_receiving_org_tbl (1).disable_date := sysdate;
dbms_output.put_line('fnd_api.g_miss_num: '||ln_receipt);
--
l_receiving_org_tbl (1).receipt_organization_id := ln_organization_id;
----------------
IF ln_cnt_rank > 0 AND lc_rec_status = 'S' THEN
j := 0;
--
FOR lreq_sr_ship IN lcur_source_rule_ship (lreq_sr.sourcing_rule_name)
LOOP -- Second Loop
ln_source_organization_id := NULL;
--
--6) Validation of Vendor info
IF lreq_sr_ship.source_type = 3 AND lreq_sr_ship.vendor_name IS NOT NULL THEN
BEGIN
SELECT DISTINCT pv.vendor_id, pvs.vendor_site_id
INTO ln_vendor_id, ln_vendor_site_id
FROM ap_suppliers pv,
ap_supplier_sites_all pvs,
hr_operating_units hou
WHERE pv.vendor_name = lreq_sr_ship.vendor_name
AND pvs.vendor_site_code = lreq_sr_ship.vendor_site
AND hou.name = lreq_sr_ship.operating_unit
AND hou.organization_id = pvs.org_id
AND pvs.vendor_id = pv.vendor_id;
EXCEPTION
WHEN others THEN
lc_rec_status := 'E';
dbms_output.put_line('Error occurred while validating vendor name:'||lreq_sr_ship.vendor_name
||' and vendor site:'|| lreq_sr_ship.vendor_site||' Error Message:'|| SQLERRM);
END;
END IF;
--
j := j + 1;
l_shipping_org_tbl (j).RANK := lreq_sr_ship.RANK;
l_shipping_org_tbl (j).allocation_percent := lreq_sr_ship.allocation_percent; -- Allocation 100
l_shipping_org_tbl (j).operation := 'UPDATE';
l_shipping_org_tbl (j).source_type := lreq_sr_ship.source_type;
l_shipping_org_tbl (j).SR_SOURCE_ID := ln_sr_source_id;
-- BUY FROM
--
IF (lreq_sr_ship.source_type = 3) THEN
l_shipping_org_tbl (j).vendor_id := lreq_sr_ship.vendor_id;
l_shipping_org_tbl (j).vendor_site_id := ln_vendor_site_id;--lreq_sr_ship.vendor_site_id;
ELSE
BEGIN
SELECT organization_id INTO ln_source_organization_id
FROM org_organization_definitions
WHERE organization_code = lreq_sr_ship.source_organization_code;
EXCEPTION
WHEN others THEN
ln_source_organization_id := NULL;
END;
--
l_shipping_org_tbl (j).source_organization_id := ln_source_organization_id;
END IF;
l_shipping_org_tbl (j).receiving_org_index := 1;
END LOOP; -- Second Loop Ended
END IF;
--
-- Call API to Update Sourcing Rule
-- Call API to Update Sourcing Rule
IF (lc_rec_status = 'S') THEN
BEGIN
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_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 l_return_status = fnd_api.g_ret_sts_success
THEN
dbms_output.put_line('Successfully Updated!');
BEGIN
UPDATE XXPO_UPDATE_SR_STG
SET record_status = 'S'
WHERE sourcing_rule_name = lreq_sr.sourcing_rule_name
AND organization_code = lreq_sr.organization_code;
EXCEPTION
WHEN others THEN
NULL;
END;
COMMIT;
ELSE
IF l_msg_count > 0 THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data := fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
END LOOP;
--
END IF;
dbms_output.put_line('Failure:- '||l_msg_data);
ROLLBACK;
END IF;
EXCEPTION
WHEN OTHERS
THEN NULL;
END;
END IF;
END LOOP; -- First Loop Ended
END;
/
No comments:
Post a Comment