CREATE OR REPLACE
PACKAGE XXASCP_ITEM_SOURCING_RULE_PKG
IS
/*---------------------------------------------------------
Current Version : 1.00
Create By :
Create Date : 2009-05-25
Purpose :
Logicality :
---------------------------------------------------------*/
C_USERNAME CONSTANT VARCHAR2 (10) := 'DM';
C_DYNAMIC_VENDOR CONSTANT VARCHAR2 (50) := UPPER('Dynamic_Vendor');
PROCEDURE xxascp_insert_sourcing_rule
(p_site_code IN VARCHAR2,
o_error_code OUT NUMBER,
o_error_msg OUT VARCHAR2,
o_record_count OUT NUMBER
) ;
END; -- Package spec
/
CREATE OR REPLACE
PACKAGE BODY XXASCP_ITEM_SOURCING_RULE_PKG
IS
PROCEDURE xxascp_insert_sourcing_rule (
p_site_code IN VARCHAR2,
o_error_code OUT NUMBER,
o_error_msg OUT VARCHAR2,
o_record_count OUT NUMBER
)
IS
v_default_warehouse VARCHAR2 (200) := '';
exp_not_default_warehouse EXCEPTION;
v_step VARCHAR2 (200);
v_record NUMBER;
BEGIN
--check if default warehouse is set
v_step := 'E0001';
BEGIN
SELECT wh_name
INTO v_default_warehouse
FROM apps.xxascp_di_whcode
WHERE is_default_wh = 'Y'
AND is_active =1; --amy add 20090610 because error;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE exp_not_default_warehouse;
END;
--all items that need insert into sourcing rule
/* SELECT item.*
FROM apps.xxascp_di_item_source_mv item
WHERE item.last_update_date >
NVL( TRUNC( ( SELECT co.last_execute_date
FROM apps.xxascp_di_execute_plan_control co
WHERE co.control_name = 'SOURCING RULE' )),
TO_DATE( '19000101', 'yyyymmdd' ))*/
v_step := 'E0002';
--delete xxascp_di_assignmentset
DELETE FROM apps.xxascp_di_assignmentset;
v_step := 'E0003';
IF p_site_code IS NULL OR UPPER(p_site_code)='ALL' THEN
--insert xxascp_di_assignmentset by items
INSERT INTO apps.xxascp_di_assignmentset
(s_id,item_name, source_partner_name, source_partner_site_code,
sourcing_rule_name, effective_date, allocation_percent,
source_type, assignment_type, organization_code,
sr_instance_code, RANK, ship_method, source_org_code,
source_org_instance_code, assignment_set_name,
planning_active, creation_date, created_by,
lastupdated_date, lastupdated_by, is_active)
SELECT apps.xxascp_di_assignmentset_s.NEXTVAL,item_wh_pl.item_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_name
ELSE TEMPLATE.source_partner_name
END AS vendor_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_site_code
ELSE TEMPLATE.source_partner_site_code
END AS vendor_site_code,
TEMPLATE.sourcing_rule_name, TEMPLATE.effective_date,
TEMPLATE.allocation_percent, TEMPLATE.source_type,
TEMPLATE.assignment_type, TEMPLATE.organization_code,
TEMPLATE.sr_instance_code, TEMPLATE.RANK,
TEMPLATE.ship_method, TEMPLATE.source_org_code,
TEMPLATE.source_org_instance_code,
TEMPLATE.assignment_set_name, 1 AS planning_active,
SYSDATE AS creation_date, 1 AS created_by,
SYSDATE AS lastupdated_date, 1 AS lastupdated_by,
1 AS is_active
FROM (SELECT item.item_name, item.organization_code,
item.source_instance_code,
DECODE (itemwh.item_wh,
NULL, v_default_warehouse,
itemwh.item_wh
) AS item_wh,
itemline.plcode
FROM apps.xxascp_di_item_source_mv item,
apps.xxascp_di_itemwh itemwh,
apps.xxascp_gpcs_ppart itemline,
apps.xxascp_di_org org
WHERE item.item_name = itemwh.item_no(+)
AND item.item_name = itemline.ppart
AND org.org_type = 'RO'
AND item.organization_code = org.org_code
AND ( item.last_update_date >
NVL
(TRUNC
((SELECT co.last_execute_date
FROM apps.xxascp_di_execute_plan_control co
WHERE co.control_name =
'SOURCING RULE'
AND item.source_instance_code = co.attribute1)
),
TO_DATE ('19000101', 'yyyymmdd')
)
OR item.new_flag = 'Y'
)) item_wh_pl,
apps.xxascp_di_sourcing_template TEMPLATE,
apps.xxascp_di_item_source_mv item_mv,
apps.xxascp_di_pl_vendor vendor
WHERE TEMPLATE.is_active = 1
AND item_wh_pl.source_instance_code = TEMPLATE.sr_instance_code
AND item_wh_pl.item_wh = TEMPLATE.wh_name
AND item_wh_pl.item_wh = vendor.wh_name
AND item_wh_pl.plcode = vendor.prod_line
AND item_wh_pl.source_instance_code = vendor.ro_code
AND ( ( TEMPLATE.source_type = 3
AND TEMPLATE.sr_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.organization_code =
item_mv.organization_code
)
OR ( TEMPLATE.source_type = 1
AND TEMPLATE.source_org_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.source_org_code = item_mv.organization_code
)
);
--AND item_wh_pl.item_name='4K.06201.031'
--AND item_wh_pl.source_instance_code = 'BQP'
v_record := SQL%ROWCOUNT;
v_step := 'E0004';
--delete xxascp_di_assignmentset_all by items
DELETE FROM apps.xxascp_di_assignmentset_all
WHERE (item_name || sr_instance_code || organization_code) IN (
SELECT item_name || sr_instance_code || organization_code
FROM apps.xxascp_di_assignmentset);
v_step := 'E0005';
--insert xxascp_di_assignmentset_all by items
INSERT INTO apps.xxascp_di_assignmentset_all
(s_id,item_name, source_partner_name, source_partner_site_code,
sourcing_rule_name, effective_date, allocation_percent,
source_type, assignment_type, organization_code,
sr_instance_code, RANK, ship_method, source_org_code,
source_org_instance_code, assignment_set_name,
planning_active, creation_date, created_by,
lastupdated_date, lastupdated_by, is_active)
SELECT apps.xxascp_di_assignmentset_all_s.NEXTVAL,item_wh_pl.item_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_name
ELSE TEMPLATE.source_partner_name
END AS vendor_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_site_code
ELSE TEMPLATE.source_partner_site_code
END AS vendor_site_code,
TEMPLATE.sourcing_rule_name, TEMPLATE.effective_date,
TEMPLATE.allocation_percent, TEMPLATE.source_type,
TEMPLATE.assignment_type, TEMPLATE.organization_code,
TEMPLATE.sr_instance_code, TEMPLATE.RANK,
TEMPLATE.ship_method, TEMPLATE.source_org_code,
TEMPLATE.source_org_instance_code,
TEMPLATE.assignment_set_name, 1 AS planning_active,
SYSDATE AS creation_date, 1 AS created_by,
SYSDATE AS lastupdated_date, 1 AS lastupdated_by,
1 AS is_active
FROM (SELECT item.item_name, item.organization_code,
item.source_instance_code,
DECODE (itemwh.item_wh,
NULL, v_default_warehouse,
itemwh.item_wh
) AS item_wh,
itemline.plcode
FROM apps.xxascp_di_item_source_mv item,
apps.xxascp_di_itemwh itemwh,
apps.xxascp_gpcs_ppart itemline,
apps.xxascp_di_org org
WHERE item.item_name = itemwh.item_no(+)
AND item.item_name = itemline.ppart
AND org.org_type = 'RO'
AND item.organization_code = org.org_code
AND ( item.last_update_date >
NVL
(TRUNC
((SELECT co.last_execute_date
FROM apps.xxascp_di_execute_plan_control co
WHERE co.control_name =
'SOURCING RULE'
AND item.source_instance_code = co.attribute1)
),
TO_DATE ('19000101', 'yyyymmdd')
)
OR item.new_flag = 'Y'
)) item_wh_pl,
apps.xxascp_di_sourcing_template TEMPLATE,
apps.xxascp_di_item_source_mv item_mv,
apps.xxascp_di_pl_vendor vendor
WHERE TEMPLATE.is_active = 1
AND item_wh_pl.source_instance_code = TEMPLATE.sr_instance_code
AND item_wh_pl.item_wh = TEMPLATE.wh_name
AND item_wh_pl.item_wh = vendor.wh_name
AND item_wh_pl.plcode = vendor.prod_line
AND item_wh_pl.source_instance_code = vendor.ro_code
AND ( ( TEMPLATE.source_type = 3
AND TEMPLATE.sr_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.organization_code =
item_mv.organization_code
)
OR ( TEMPLATE.source_type = 1
AND TEMPLATE.source_org_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.source_org_code = item_mv.organization_code
)
);
--AND item_wh_pl.item_name='4K.06201.031'
--AND item_wh_pl.source_instance_code = 'BQP'
ELSE
--insert xxascp_di_assignmentset by items
INSERT INTO apps.xxascp_di_assignmentset
(s_id,item_name, source_partner_name, source_partner_site_code,
sourcing_rule_name, effective_date, allocation_percent,
source_type, assignment_type, organization_code,
sr_instance_code, RANK, ship_method, source_org_code,
source_org_instance_code, assignment_set_name,
planning_active, creation_date, created_by,
lastupdated_date, lastupdated_by, is_active)
SELECT apps.xxascp_di_assignmentset_s.NEXTVAL,item_wh_pl.item_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_name
ELSE TEMPLATE.source_partner_name
END AS vendor_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_site_code
ELSE TEMPLATE.source_partner_site_code
END AS vendor_site_code,
TEMPLATE.sourcing_rule_name, TEMPLATE.effective_date,
TEMPLATE.allocation_percent, TEMPLATE.source_type,
TEMPLATE.assignment_type, TEMPLATE.organization_code,
TEMPLATE.sr_instance_code, TEMPLATE.RANK,
TEMPLATE.ship_method, TEMPLATE.source_org_code,
TEMPLATE.source_org_instance_code,
TEMPLATE.assignment_set_name, 1 AS planning_active,
SYSDATE AS creation_date, 1 AS created_by,
SYSDATE AS lastupdated_date, 1 AS lastupdated_by,
1 AS is_active
FROM (SELECT item.item_name, item.organization_code,
item.source_instance_code,
DECODE (itemwh.item_wh,
NULL, v_default_warehouse,
itemwh.item_wh
) AS item_wh,
itemline.plcode
FROM apps.xxascp_di_item_source_mv item,
apps.xxascp_di_itemwh itemwh,
apps.xxascp_gpcs_ppart itemline,
apps.xxascp_di_org org
WHERE item.item_name = itemwh.item_no(+)
AND item.item_name = itemline.ppart
AND org.org_type = 'RO'
AND item.source_instance_code = p_site_code
AND item.organization_code = org.org_code
AND ( item.last_update_date >
NVL
(TRUNC
((SELECT co.last_execute_date
FROM apps.xxascp_di_execute_plan_control co
WHERE co.control_name =
'SOURCING RULE'
AND co.attribute1 = p_site_code)
),
TO_DATE ('19000101', 'yyyymmdd')
)
OR item.new_flag = 'Y'
)) item_wh_pl,
apps.xxascp_di_sourcing_template TEMPLATE,
apps.xxascp_di_item_source_mv item_mv,
apps.xxascp_di_pl_vendor vendor
WHERE TEMPLATE.is_active = 1
AND item_wh_pl.source_instance_code = TEMPLATE.sr_instance_code
AND item_wh_pl.item_wh = TEMPLATE.wh_name
AND item_wh_pl.item_wh = vendor.wh_name
AND item_wh_pl.plcode = vendor.prod_line
AND item_wh_pl.source_instance_code = vendor.ro_code
AND ( ( TEMPLATE.source_type = 3
AND TEMPLATE.sr_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.organization_code =
item_mv.organization_code
)
OR ( TEMPLATE.source_type = 1
AND TEMPLATE.source_org_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.source_org_code = item_mv.organization_code
)
);
--AND item_wh_pl.item_name='4K.06201.031'
--AND item_wh_pl.source_instance_code = 'BQP'
v_record := SQL%ROWCOUNT;
v_step := 'E0004';
--delete xxascp_di_assignmentset_all by items
DELETE FROM apps.xxascp_di_assignmentset_all
WHERE (item_name || sr_instance_code || organization_code) IN (
SELECT item_name || sr_instance_code || organization_code
FROM apps.xxascp_di_assignmentset);
v_step := 'E0005';
--insert xxascp_di_assignmentset_all by items
INSERT INTO apps.xxascp_di_assignmentset_all
(s_id,item_name, source_partner_name, source_partner_site_code,
sourcing_rule_name, effective_date, allocation_percent,
source_type, assignment_type, organization_code,
sr_instance_code, RANK, ship_method, source_org_code,
source_org_instance_code, assignment_set_name,
planning_active, creation_date, created_by,
lastupdated_date, lastupdated_by, is_active)
SELECT apps.xxascp_di_assignmentset_all_s.NEXTVAL,item_wh_pl.item_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_name
ELSE TEMPLATE.source_partner_name
END AS vendor_name,
CASE WHEN TEMPLATE.source_type=1 THEN ''
WHEN TEMPLATE.source_type=3 AND UPPER(TEMPLATE.source_partner_name)=C_DYNAMIC_VENDOR THEN vendor.vendor_site_code
ELSE TEMPLATE.source_partner_site_code
END AS vendor_site_code,
TEMPLATE.sourcing_rule_name, TEMPLATE.effective_date,
TEMPLATE.allocation_percent, TEMPLATE.source_type,
TEMPLATE.assignment_type, TEMPLATE.organization_code,
TEMPLATE.sr_instance_code, TEMPLATE.RANK,
TEMPLATE.ship_method, TEMPLATE.source_org_code,
TEMPLATE.source_org_instance_code,
TEMPLATE.assignment_set_name, 1 AS planning_active,
SYSDATE AS creation_date, 1 AS created_by,
SYSDATE AS lastupdated_date, 1 AS lastupdated_by,
1 AS is_active
FROM (SELECT item.item_name, item.organization_code,
item.source_instance_code,
DECODE (itemwh.item_wh,
NULL, v_default_warehouse,
itemwh.item_wh
) AS item_wh,
itemline.plcode
FROM apps.xxascp_di_item_source_mv item,
apps.xxascp_di_itemwh itemwh,
apps.xxascp_gpcs_ppart itemline,
apps.xxascp_di_org org
WHERE item.item_name = itemwh.item_no(+)
AND item.item_name = itemline.ppart
AND org.org_type = 'RO'
AND item.organization_code = org.org_code
AND item.source_instance_code = p_site_code
AND ( item.last_update_date >
NVL
(TRUNC
((SELECT co.last_execute_date
FROM apps.xxascp_di_execute_plan_control co
WHERE co.control_name =
'SOURCING RULE'
AND co.attribute1 = p_site_code)
),
TO_DATE ('19000101', 'yyyymmdd')
)
OR item.new_flag = 'Y'
)) item_wh_pl,
apps.xxascp_di_sourcing_template TEMPLATE,
apps.xxascp_di_item_source_mv item_mv,
apps.xxascp_di_pl_vendor vendor
WHERE TEMPLATE.is_active = 1
AND item_wh_pl.source_instance_code = TEMPLATE.sr_instance_code
AND item_wh_pl.item_wh = TEMPLATE.wh_name
AND item_wh_pl.item_wh = vendor.wh_name
AND item_wh_pl.plcode = vendor.prod_line
AND item_wh_pl.source_instance_code = vendor.ro_code
AND ( ( TEMPLATE.source_type = 3
AND TEMPLATE.sr_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.organization_code =
item_mv.organization_code
)
OR ( TEMPLATE.source_type = 1
AND TEMPLATE.source_org_instance_code =
item_mv.source_instance_code
AND item_wh_pl.item_name = item_mv.item_name
AND TEMPLATE.source_org_code = item_mv.organization_code
)
);
--AND item_wh_pl.item_name='4K.06201.031'
--AND item_wh_pl.source_instance_code = 'BQP'
END IF;
COMMIT;
o_error_code := 0;
o_record_count := v_record;
o_error_msg := NULL;
EXCEPTION
WHEN exp_not_default_warehouse
THEN
ROLLBACK;
o_error_code := 2;
o_record_count := 0;
o_error_msg := 'Default warehouse not exists.';
--raise_application_error(-20101, 'default warehouse not exists.');
WHEN OTHERS
THEN
ROLLBACK;
o_error_code := 2;
o_record_count := 0;
o_error_msg := v_step || ':' || SQLERRM;
--RAISE;
END;
END;
/
Oracle Procedure示例01
最新推荐文章于 2024-07-24 20:04:27 发布
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
