CREATE OR REPLACE PACKAGE BODY XXC_SOURCING_RULE_PKG
AS
g_error_message VARCHAR2 (4000);
g_status VARCHAR2 (30);
g_debug VARCHAR2 (1) := 'Y';
g_user_id NUMBER := fnd_global.user_id;
g_sysdate DATE := SYSDATE;
PROCEDURE write_debug (p_msg IN VARCHAR2)
IS
BEGIN
IF g_debug = 'Y'
THEN
fnd_file.put_line (fnd_file.LOG, p_msg);
END IF;
END write_debug;
PROCEDURE validate_sr (
p_record_id IN NUMBER,
p_status IN VARCHAR2,
p_param2 IN VARCHAR2 DEFAULT NULL,
p_param3 IN VARCHAR2 DEFAULT NULL,
p_param4 IN VARCHAR2 DEFAULT NULL,
p_param5 IN VARCHAR2 DEFAULT NULL,
p_param6 IN VARCHAR2 DEFAULT NULL,
p_param7 IN VARCHAR2 DEFAULT NULL,
p_param8 IN VARCHAR2 DEFAULT NULL,
p_param9 IN VARCHAR2 DEFAULT NULL,
p_param10 IN VARCHAR2 DEFAULT NULL,
p_param11 IN VARCHAR2 DEFAULT NULL,
p_param12 IN VARCHAR2 DEFAULT NULL,
p_param13 IN VARCHAR2 DEFAULT NULL,
p_param14 IN VARCHAR2 DEFAULT NULL
)
AS
CURSOR cur_sr
IS
SELECT *
FROM STAGING_TABLE
WHERE xxc_record_id = p_record_id
AND NVL (xxc_status, 'N') NOT IN ('V', 'P', 'PE');
l_count NUMBER;
l_error_message VARCHAR2 (2000);
BEGIN
fnd_file.put_line (fnd_file.LOG,
'Inside validate_sr parameters record_id - '
|| p_record_id
);
FOR rec_sr IN cur_sr
LOOP
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Inside validate_sr');
g_error_message := NULL;
g_status := 'V';
SAVEPOINT validate_start;
IF rec_sr.organization_code IS NOT NULL
THEN
BEGIN
SELECT organization_id
INTO rec_sr.organization_id
FROM org_organization_definitions
WHERE organization_code = rec_sr.organization_code;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Unexpected Error occurred while validating Inventory Organization Code - '
|| rec_sr.organization_code
|| ' Error Message - '
|| SQLERRM;
g_error_message :=
g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END;
END IF;
IF rec_sr.source_organization_name IS NOT NULL
THEN
BEGIN
SELECT organization_id
INTO rec_sr.source_organization_id
FROM org_organization_definitions
WHERE organization_code = rec_sr.source_organization_name;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Unexpected Error occurred while validating Inventory Organization Code - '
|| rec_sr.source_organization_name
|| ' Error Message - '
|| SQLERRM;
g_error_message :=
g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END;
END IF;
l_count := 0;
SELECT COUNT (*)
INTO l_count
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = rec_sr.sourcing_rule_name
AND organization_id = rec_sr.organization_id;
IF l_count > 0
THEN
g_status := 'E';
l_error_message :=
'Sourcing Rule Already Exists '
|| rec_sr.sourcing_rule_name;
g_error_message := g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END IF;
IF rec_sr.source_type = 3
THEN
BEGIN
SELECT DISTINCT pv.vendor_id, pvs.vendor_site_id
INTO rec_sr.vendor_id, rec_sr.vendor_site_id
FROM ap_suppliers pv,
ap_supplier_sites_all pvs,
hr_operating_units hou
WHERE pv.vendor_name = rec_sr.vendor_name
AND pvs.vendor_site_code = rec_sr.vendor_site
AND hou.NAME = rec_sr.operating_unit
AND hou.organization_id = pvs.org_id
AND pvs.vendor_id = pv.vendor_id;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Unexpected Error occurred while validating vendor name - '
|| rec_sr.vendor_name
|| ' and vendor site - '
|| rec_sr.vendor_site
|| ' Error Message - '
|| SQLERRM;
g_error_message :=
g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END;
END IF;
UPDATE STAGING_TABLE
SET xxc_status = g_status,
err_msg = g_error_message,
organization_id = rec_sr.organization_id,
source_organization_id = rec_sr.source_organization_id,
vendor_id = rec_sr.vendor_id,
vendor_site_id = rec_sr.vendor_site_id,
last_update_date = g_sysdate,
last_updated_by = g_user_id,
last_update_login = g_user_id
WHERE xxc_record_id = p_record_id;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Unexpected Error occurred while validating record id - '
|| rec_sr.xxc_record_id
|| ' Error Message - '
|| (SUBSTR (SQLERRM, 1, 250));
g_error_message := g_error_message || ' ' || l_error_message;
fnd_file.put_line (fnd_file.LOG, l_error_message);
UPDATE xxc8839_po_app_supp_list_stg2
SET xxc_status = g_status,
err_msg = g_error_message,
last_update_date = g_sysdate,
last_updated_by = g_user_id,
last_update_login = g_user_id
WHERE xxc_record_id = p_record_id;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO validate_asl;
fnd_file.put_line
(fnd_file.LOG,
'Unexpected Error occurred in validate_asl for record id - '
|| p_record_id
|| ' Error Message - '
|| (SUBSTR (SQLERRM, 1, 250))
);
END validate_sr;
PROCEDURE validate_sa (
p_record_id IN NUMBER,
p_status IN VARCHAR2,
p_param2 IN VARCHAR2 DEFAULT NULL,
p_param3 IN VARCHAR2 DEFAULT NULL,
p_param4 IN VARCHAR2 DEFAULT NULL,
p_param5 IN VARCHAR2 DEFAULT NULL,
p_param6 IN VARCHAR2 DEFAULT NULL,
p_param7 IN VARCHAR2 DEFAULT NULL,
p_param8 IN VARCHAR2 DEFAULT NULL,
p_param9 IN VARCHAR2 DEFAULT NULL,
p_param10 IN VARCHAR2 DEFAULT NULL,
p_param11 IN VARCHAR2 DEFAULT NULL,
p_param12 IN VARCHAR2 DEFAULT NULL,
p_param13 IN VARCHAR2 DEFAULT NULL,
p_param14 IN VARCHAR2 DEFAULT NULL
)
AS
CURSOR cur_sa
IS
SELECT *
FROM SR_ASSIGNMENT_STAGING_TABLE xaa
WHERE xxc_record_id = p_record_id AND NVL (xxc_status, 'N') <> 'V';
l_count NUMBER;
l_error_message VARCHAR2 (2000);
BEGIN
fnd_file.put_line (fnd_file.LOG,
'Inside validate_sa parameters record_id - '
|| p_record_id
);
FOR rec_sa IN cur_sa
LOOP
g_error_message := NULL;
g_status := 'V';
SAVEPOINT validate_start;
BEGIN
/* BEGIN
SELECT ASSIGNMENT_SET_ID
INTO rec_sa.ASSIGNMENT_SET_ID
FROM MRP_ASSIGNMENT_SETS
WHERE ASSIGNMENT_SET_NAME = rec_sa.ASSIGNMENT_SET_NAME;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
g_error_message :=
g_error_message
|| ' '
|| 'Unexpected Error occurred while validating ASSIGNMENT_SET_NAME - '
|| rec_sa.ASSIGNMENT_SET_NAME
|| ' Error Message - '
|| SQLERRM;
write_debug (g_error_message);
END; */
BEGIN
SELECT sourcing_rule_id
INTO rec_sa.sourcing_rule_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = rec_sa.sourcing_rule_name
AND organization_id = rec_sa.organization_id;
write_debug ( ' rec_sa.SOURCING_RULE_NAME'
|| rec_sa.sourcing_rule_name
|| rec_sa.sourcing_rule_id
);
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
g_error_message :=
g_error_message
|| ' '
|| 'Unexpected Error occurred while validating SOURCING_RULE_NAME - '
|| rec_sa.sourcing_rule_name
|| ' Error Message - '
|| SQLERRM;
write_debug (g_error_message);
END;
BEGIN
fnd_file.put_line (fnd_file.LOG,
' Validating Assignment Type');
SELECT lookup_code
INTO rec_sa.assignment_type_id
FROM fnd_lookup_values
WHERE lookup_type = 'MRP_ASSIGNMENT_TYPE'
AND LANGUAGE = 'US'
AND meaning = rec_sa.assignment_type;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Error while validating Assignment Type - '
|| rec_sa.assignment_type
|| ' '
|| SQLERRM;
g_error_message := g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END;
BEGIN
fnd_file.put_line (fnd_file.LOG, ' MRP_SOURCING_RULE_TYPE');
SELECT lookup_code
INTO rec_sa.sourcing_rule_type_id
FROM fnd_lookup_values
WHERE lookup_type = 'MRP_SOURCING_RULE_TYPE'
AND LANGUAGE = 'US'
AND meaning = rec_sa.sourcing_rule_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
g_status := 'E';
l_error_message :=
'Error while validating SOURCING_RULE_TYPE - '
|| rec_sa.sourcing_rule_type
|| ' '
|| SQLERRM;
g_error_message := g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END;
------------------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
' Validating ITEM_OR_CATEGORY'
);
SELECT inventory_item_id
INTO rec_sa.inventory_item_id
FROM mtl_system_items_b msi,
org_organization_definitions ood
WHERE msi.organization_id = ood.organization_id
AND msi.segment1 = rec_sa.item_or_category
AND ood.organization_code = rec_sa.organization_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
SELECT category_id
INTO rec_sa.category_id
FROM mtl_categories_kfv
WHERE concatenated_segments = rec_sa.item_or_category;
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Error while validating ITEM_OR_CATEGORY - '
|| rec_sa.item_or_category;
g_error_message := g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END;
BEGIN
SELECT organization_id
INTO rec_sa.organization_id
FROM org_organization_definitions
WHERE organization_code = rec_sa.organization_code;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Unexpected Error occurred while validating Inventory Organization Code - '
|| rec_sa.organization_code
|| ' Error Message - '
|| SQLERRM;
g_error_message := g_error_message || ' ' || l_error_message;
write_debug (l_error_message);
END;
l_count := 0;
SELECT COUNT (assignment_set_id)
INTO l_count
FROM mrp_sr_assignments_v
WHERE assignment_set_id = rec_sa.assignment_set_id
AND assignment_type = rec_sa.assignment_type_id
AND organization_id = rec_sa.organization_id
AND entity_name = rec_sa.item_or_category
AND sourcing_rule_type = rec_sa.sourcing_rule_type_id
AND sourcing_rule_id = rec_sa.sourcing_rule_id;
IF l_count > 0
THEN
g_status := 'E';
g_error_message :=
g_error_message
|| ' '
|| 'Sourcing Rule Assignment Already Exists! ';
write_debug (g_error_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
g_status := 'E';
l_error_message :=
'Unexpected Error occurred while validating record id - '
|| rec_sa.xxc_record_id
|| ' Error Message - '
|| (SUBSTR (SQLERRM, 1, 250));
g_error_message := g_error_message || ' ' || l_error_message;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
write_debug ('updating Records ' || p_record_id);
UPDATE SR_ASSIGNMENT_STAGING_TABLE
SET xxc_status = g_status,
err_msg = g_error_message,
assignment_set_id = rec_sa.assignment_set_id,
assignment_type_id = rec_sa.assignment_type_id,
sourcing_rule_type_id = rec_sa.sourcing_rule_type_id,
organization_id = rec_sa.organization_id,
sourcing_rule_id = rec_sa.sourcing_rule_id,
inventory_item_id = rec_sa.inventory_item_id,
category_id = rec_sa.category_id,
last_update_date = g_sysdate,
last_updated_by = g_user_id,
last_update_login = g_user_id
WHERE xxc_record_id = p_record_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO validate_asl;
fnd_file.put_line
(fnd_file.LOG,
'Unexpected Error occurred in validate_asl for record id - '
|| p_record_id
|| ' Error Message - '
|| (SUBSTR (SQLERRM, 1, 250))
);
END validate_sa;
-------------------------------------------------------------------------------
PROCEDURE create_sr (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
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_organization_id NUMBER;
l_uncheck_count NUMBER;
l_asl_count NUMBER;
ln_rule_id 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;
CURSOR cur_sou_rules_det
IS
SELECT DISTINCT sourcing_rule_id, sourcing_rule_name, description,
organization_code, status, source_type,
sourcing_rule_type, planning_active,
organization_id, effective_date, disable_date
FROM STAGING_TABLE
WHERE 1 = 1 AND UPPER (xxc_status) <> 'E'
-- and SOURCING_RULE_NAME not IN ('A035C047', 'A029L392','A045C225','A055P965')
;
CURSOR cur_sou_rules_ship_det (p_sour_rule_name IN VARCHAR2)
IS
SELECT *
/* distinct sourcing_rule_name, rank,
allocation_percent,
vendor_id,
vendor_site_id,
source_organization_id,
source_type,
organization_id */
FROM STAGING_TABLE
WHERE 1 = 1
--and sourcing_rule_name='04843GW2'
AND UPPER (xxc_status) <> 'E'
AND sourcing_rule_name = p_sour_rule_name
--AND SOURCE_ORGANIZATION_id =p_sou_org_id
;
BEGIN
FOR i IN cur_sou_rules_det
LOOP
--
-- LN_CNT_RANK := 0;
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 (i.organization_id, 0);
-- ln_rule_id := null;
fnd_file.put_line (fnd_file.LOG,
'ln_rule_id swap: ' || ln_rule_id
);
EXCEPTION
WHEN OTHERS
THEN
ln_rule_id := NULL;
END;
IF (ln_rule_id IS NULL)
THEN
BEGIN
SELECT sr_receipt_id
INTO ln_receipt
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = ln_rule_id;
EXCEPTION
WHEN OTHERS
THEN
ln_receipt := NULL;
END;
SELECT COUNT (RANK)
INTO ln_cnt_rank
FROM STAGING_TABLE
WHERE sourcing_rule_name = i.sourcing_rule_name;
fnd_message.CLEAR;
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 := 'CREATE';
l_receiving_org_tbl (1).operation := 'CREATE'; -- Create or Update
-- l_sourcing_rule_rec.operation := 'UPDATE';
l_sourcing_rule_rec.sourcing_rule_name := i.sourcing_rule_name;
--SR Name --
l_sourcing_rule_rec.description := i.description;
l_sourcing_rule_rec.organization_id := i.organization_id;
l_sourcing_rule_rec.planning_active := i.planning_active;
-- Active?
l_sourcing_rule_rec.status := i.status; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := i.sourcing_rule_type;
l_receiving_org_tbl (1).sr_receipt_id := fnd_api.g_miss_num;
-- 5483270;
l_receiving_org_tbl (1).effective_date := TRUNC (SYSDATE + 1);
l_receiving_org_tbl (1).receipt_organization_id :=
i.organization_id;
--586;
fnd_file.put_line (fnd_file.LOG, 'before call');
fnd_file.put_line (fnd_file.LOG,
'Operation before call'
|| l_sourcing_rule_rec.operation
);
IF ln_cnt_rank > 0
THEN
fnd_file.put_line (fnd_file.LOG, 'in rank loop');
j := 0;
FOR rec_sou_rules_det IN
cur_sou_rules_ship_det (i.sourcing_rule_name)
LOOP
j := j + 1;
fnd_file.put_line (fnd_file.LOG,
'rank:=' || rec_sou_rules_det.RANK
);
fnd_file.put_line (fnd_file.LOG,
'allocation percent:='
|| rec_sou_rules_det.allocation_percent
);
l_shipping_org_tbl (j).RANK := rec_sou_rules_det.RANK;
l_shipping_org_tbl (j).allocation_percent :=
rec_sou_rules_det.allocation_percent;
-- Allocation 100
l_shipping_org_tbl (j).operation := 'CREATE';
l_shipping_org_tbl (j).source_type :=
rec_sou_rules_det.source_type;
-- BUY FROM
fnd_file.put_line (fnd_file.LOG,
'In rank loop for sourcing rule '
|| rec_sou_rules_det.sourcing_rule_name
);
IF (rec_sou_rules_det.source_type = 3)
THEN
l_shipping_org_tbl (j).vendor_id :=
rec_sou_rules_det.vendor_id;
l_shipping_org_tbl (j).vendor_site_id :=
rec_sou_rules_det.vendor_site_id;
ELSE
--if (.source_type = 2) then
l_shipping_org_tbl (j).source_organization_id :=
rec_sou_rules_det.source_organization_id;
--FND_API.G_MISS_NUM;
END IF;
-- l_shipping_org_tbl (j).source_organization_id := rec_sou_rules_det.source_organization_id;
l_shipping_org_tbl (j).receiving_org_index := 1;
--l_shipping_org_tbl := mrp_sourcing_rule_pub.g_miss_shipping_org_tbl;
END LOOP;
END IF;
fnd_file.put_line (fnd_file.LOG, 'before call');
fnd_file.put_line (fnd_file.LOG,
'Operation before call'
|| l_sourcing_rule_rec.operation
);
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
fnd_file.put_line (fnd_file.LOG, 'Success!');
fnd_file.put_line (fnd_file.LOG,
'Sourcing rule created: '
|| i.sourcing_rule_name
);
fnd_file.put_line
(fnd_file.LOG,
'----------------------------------------------------------------------------'
);
ELSE
fnd_file.put_line (fnd_file.LOG,
'count:'
|| l_msg_count
|| 'Sourcing rule name having error :'
|| i.sourcing_rule_name
);
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
);
fnd_file.put_line (fnd_file.LOG, l_msg_data);
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'MSG: '
|| o_sourcing_rule_rec.return_status
);
END IF;
fnd_file.put_line (fnd_file.LOG, 'Failure!');
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Sourcing rule name having exception :'
|| i.sourcing_rule_name
);
END;
/* else
fnd_file.put_line (fnd_file.LOG,'Sourcing rule ID is prdesnt for !' || i.SOURCING_RULE_NAME ); */
END IF;
END LOOP;
END;
--xxpo_create_mrp_sourcing;
-----------------------------------------------------------------------------------------------------------------
PROCEDURE create_sr_multiple_rank (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_sou_rule_name IN VARCHAR2
)
IS
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_organization_id NUMBER;
l_uncheck_count NUMBER;
l_asl_count NUMBER;
ln_rule_id NUMBER;
ln_receipt NUMBER;
ln_cnt_rank 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;
j NUMBER;
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;
CURSOR cur_sou_rules_det
IS
SELECT *
FROM STAGING_TABLE
WHERE 1 = 1
-- AND XXC_STATUS='E'
AND sourcing_rule_name = 'p_sou_rule_name'
;
CURSOR cur_sou_rules_ship_det (p_sourcing_rule_name IN VARCHAR2)
IS
SELECT *
FROM STAGING_TABLE
WHERE 1 = 1
-- AND XXC_STATUS='E'
AND sourcing_rule_name = 'p_sou_rule_name'
;
BEGIN
FOR i IN cur_sou_rules_det
LOOP
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 (i.organization_id, 0);
-- ln_rule_id := null;
fnd_file.put_line (fnd_file.LOG,
'ln_rule_id swap ' || ln_rule_id);
EXCEPTION
WHEN OTHERS
THEN
ln_rule_id := NULL;
END;
IF (ln_rule_id IS NULL)
THEN
/* BEGIN
SELECT sr_receipt_id
INTO ln_receipt
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = ln_rule_id;
EXCEPTION
WHEN OTHERS
THEN
ln_receipt := NULL;
END; */
fnd_message.CLEAR;
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.Sourcing_Rule_Id := ln_rule_id; --4833081;
l_sourcing_rule_rec.sourcing_rule_name := i.sourcing_rule_name;
--SR Name --
l_sourcing_rule_rec.description := i.description;
l_sourcing_rule_rec.organization_id := i.organization_id;
l_sourcing_rule_rec.planning_active := i.planning_active;
-- Active?
l_sourcing_rule_rec.status := i.status; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := i.sourcing_rule_type;
-- 1:Sourcing Rule
-- l_sourcing_rule_rec.operation := 'UPDATE';
l_sourcing_rule_rec.operation := 'CREATE';
l_receiving_org_tbl (1).operation := 'CREATE';
-- Create or Update
-- 5483270;
l_receiving_org_tbl (1).effective_date := TRUNC (SYSDATE + 1);
l_receiving_org_tbl (1).disable_date := i.disable_date;
l_receiving_org_tbl (1).receipt_organization_id :=
i.organization_id;
--586;
--l_shipping_org_tbl(1).Sr_Source_Id:=228;
l_shipping_org_tbl (1).RANK := i.RANK;
l_shipping_org_tbl (1).operation := 'CREATE';
l_shipping_org_tbl (1).allocation_percent := i.allocation_percent;
-- Allocation 100
l_shipping_org_tbl (1).source_type := i.source_type; -- BUY FROM
fnd_file.put_line (fnd_file.LOG, 'Success!');
IF (i.source_type = 3)
THEN
l_shipping_org_tbl (1).vendor_id := i.vendor_id;
l_shipping_org_tbl (1).vendor_site_id := i.vendor_site_id;
ELSE
fnd_file.put_line (fnd_file.LOG,
'Source Type = ' || i.source_type
);
--l_shipping_org_tbl (1).source_organization_id := i.organization_id;
END IF;
l_shipping_org_tbl (1).receiving_org_index := 1;
fnd_file.put_line (fnd_file.LOG, 'before call');
fnd_file.put_line (fnd_file.LOG,
'Operation before call'
|| l_sourcing_rule_rec.operation
);
SELECT COUNT (RANK)
INTO ln_cnt_rank
FROM STAGING_TABLE
WHERE sourcing_rule_name = i.sourcing_rule_name;
IF ln_cnt_rank > 1
THEN
j := 0;
FOR rec_sou_rules_det IN
cur_sou_rules_ship_det (i.sourcing_rule_name)
LOOP
rec_sou_rules_det.source_organization_id := NULL;
j := j + 1;
-- l_receiving_org_tbl(j).Sr_Receipt_Id:= FND_API.G_MISS_NUM;
l_shipping_org_tbl (j).RANK := rec_sou_rules_det.RANK;
l_shipping_org_tbl (j).allocation_percent :=
rec_sou_rules_det.allocation_percent;
l_shipping_org_tbl (j).source_type :=
rec_sou_rules_det.source_type;
-- l_shipping_org_tbl (j).receiving_org_index := 1;
l_shipping_org_tbl (j).operation := 'CREATE';
-- IF UPPER (rec_sou_rules.source_type) LIKE 'BUY%'
-- THEN
l_shipping_org_tbl (j).vendor_id :=
rec_sou_rules_det.vendor_id;
l_shipping_org_tbl (j).source_organization_id :=
rec_sou_rules_det.source_organization_id;
l_shipping_org_tbl (j).vendor_site_id :=
rec_sou_rules_det.vendor_site_id;
l_shipping_org_tbl (j).receiving_org_index := 1;
-- ELSE
-- l_shipping_org_tbl (1).source_organization_id := ln_ship_org;
--END IF;
END LOOP;
--else
--l_receiving_org_tbl(1).Sr_Receipt_Id:= FND_API.G_MISS_NUM;
END IF;
fnd_file.put_line (fnd_file.LOG,
'callin API ' || i.sourcing_rule_id
);
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
fnd_file.put_line (fnd_file.LOG, 'Success!');
ELSE
fnd_file.put_line (fnd_file.LOG,
'count:'
|| l_msg_count
|| 'Sourcing rule name having error :'
|| i.sourcing_rule_name
);
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
);
fnd_file.put_line (fnd_file.LOG, l_msg_data);
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'MSG: '
|| o_sourcing_rule_rec.return_status
);
END IF;
fnd_file.put_line (fnd_file.LOG, 'Failure!');
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Sourcing rule name having exception :'
|| i.sourcing_rule_name
);
END;
ELSE
fnd_file.put_line (fnd_file.LOG,
'Sourcing rule name not found !'
|| i.sourcing_rule_name
);
END IF;
END LOOP;
--end loop;
END;
--xxpo_create_mrp_sourcing;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
PROCEDURE create_sou_assign (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
x_msg_count NUMBER;
x_msg_data VARCHAR2 (1000);
l_receipt_idx NUMBER := 1;
l_source_idx NUMBER := 1;
l_organization_id NUMBER;
l_uncheck_count NUMBER;
l_asl_count NUMBER;
-- Cursor for Approved Supplier List
CURSOR cur_sou_rules
IS
SELECT *
FROM SR_ASSIGNMENT_STAGING_TABLE
WHERE xxc_status <> 'E';
x_session_key NUMBER;
x_return_status VARCHAR2 (30);
x_return_msg VARCHAR2 (2000);
l_error_message VARCHAR2 (2000);
l_assin_rec NUMBER := 0;
l_assignment_id NUMBER;
l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'--------------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG,
'Processing Approved Supplier Information.'
);
-- FND_MESSAGE.CLEAR;
l_assin_rec := l_assin_rec + 1;
FOR rec_sou_rules IN cur_sou_rules
LOOP
g_error_message := NULL;
--l_assignment_tbl:= mrp_src_assignment_pub.assignment_tbl_type;
BEGIN
-- Calling API for Approved Supplier
SAVEPOINT discard_record;
BEGIN
SELECT mrp_sr_assignments_s.NEXTVAL
INTO l_assignment_id
FROM DUAL;
END;
fnd_file.put_line (fnd_file.LOG,
'The Sourcing Rule Processed is : '
|| rec_sou_rules.xxc_record_id
|| l_assignment_id
);
l_assignment_tbl (l_assin_rec).assignment_id := l_assignment_id;
l_assignment_tbl (l_assin_rec).assignment_set_id :=
rec_sou_rules.assignment_set_id;
l_assignment_tbl (l_assin_rec).assignment_type :=
rec_sou_rules.assignment_type_id;
l_assignment_tbl (l_assin_rec).operation := 'CREATE';
l_assignment_tbl (l_assin_rec).organization_id :=
rec_sou_rules.organization_id;
l_assignment_tbl (l_assin_rec).inventory_item_id :=
rec_sou_rules.inventory_item_id;
l_assignment_tbl (l_assin_rec).category_id :=
rec_sou_rules.category_id;
l_assignment_tbl (l_assin_rec).sourcing_rule_id :=
rec_sou_rules.sourcing_rule_id;
l_assignment_tbl (l_assin_rec).sourcing_rule_type :=
rec_sou_rules.sourcing_rule_type_id;
mrp_src_assignment_pub.process_assignment
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_assignment_set_rec => l_assignment_set_rec,
p_assignment_set_val_rec => l_assignment_set_val_rec,
p_assignment_tbl => l_assignment_tbl,
p_assignment_val_tbl => l_assignment_val_tbl,
x_assignment_set_rec => o_assignment_set_rec,
x_assignment_set_val_rec => o_assignment_set_val_rec,
x_assignment_tbl => o_assignment_tbl,
x_assignment_val_tbl => o_assignment_val_tbl
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
write_debug ('Success!');
UPDATE xxc.SR_ASSIGNMENT_STAGING_TABLE
SET xxc_status = 'P',
err_msg = NULL,
last_update_date = g_sysdate,
last_updated_by = g_user_id,
last_update_login = g_user_id
WHERE xxc_record_id = rec_sou_rules.xxc_record_id;
COMMIT;
ELSE
write_debug ('count:' || x_msg_count);
IF x_msg_count > 0
THEN
FOR l_index IN 1 .. x_msg_count
LOOP
x_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
write_debug (SUBSTR (x_msg_data, 1, 250));
g_error_message :=
g_error_message || ' ' || SUBSTR (x_msg_data, 1, 250);
END LOOP;
UPDATE xxc.SR_ASSIGNMENT_STAGING_TABLE
SET xxc_status = 'PE',
err_msg = g_error_message,
last_update_date = g_sysdate,
last_updated_by = g_user_id,
last_update_login = g_user_id
WHERE xxc_record_id = rec_sou_rules.xxc_record_id;
END IF;
write_debug ('Failure!');
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'1' || SUBSTR (SQLERRM, 1, 250)
);
UPDATE xxc.SR_ASSIGNMENT_STAGING_TABLE
SET xxc_status = 'PE',
err_msg =
'Unexpected Error Occurred during running API - '
|| SUBSTR (x_return_msg, 1, 100)
|| l_error_message,
last_update_date = g_sysdate,
last_updated_by = g_user_id,
last_update_login = g_user_id
WHERE xxc_record_id = rec_sou_rules.xxc_record_id;
END;
END LOOP;
--END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, SUBSTR (SQLERRM, 1, 250));
END create_sou_assign;
END XXC_SOURCING_RULE_PKG;
/
No comments:
Post a Comment