ebs二次開發1
CREATE OR REPLACE PACKAGE APPS.cux_bom_pkg
AS
PROCEDURE call_expand_bom_sp (
p_assembly_item IN VARCHAR2 ,
p_assembly_item_id IN VARCHAR2 ,
-- p_org_code IN VARCHAR2 DEFAULT NULL,
p_org_id IN number DEFAULT null,
p_identity IN VARCHAR2,
p_order_by IN VARCHAR2 DEFAULT NULL,
p_expand_level IN VARCHAR2 DEFAULT 'A',
o_succ_flag OUT VARCHAR2,
o_error_message OUT VARCHAR2);
PROCEDURE expand_bom_sp (
p_assembly_item IN VARCHAR2,
p_assembly_item_id IN NUMBER,
p_org_id IN VARCHAR2,
p_identity IN VARCHAR2,
p_order_by IN VARCHAR2 DEFAULT NULL,
p_expand_level IN VARCHAR2 DEFAULT 'A',
o_succ_flag OUT VARCHAR2,
o_error_message OUT VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.cux_bom_pkg
AS
PROCEDURE call_expand_bom_sp (
p_assembly_item IN VARCHAR2 ,
p_assembly_item_id IN VARCHAR2 ,
-- p_org_code IN VARCHAR2 DEFAULT NULL,
p_org_id IN number DEFAULT null,
p_identity IN VARCHAR2,
p_order_by IN VARCHAR2 DEFAULT NULL,
p_expand_level IN VARCHAR2 DEFAULT 'A',
o_succ_flag OUT VARCHAR2,
o_error_message OUT VARCHAR2)
IS
l_organization_id NUMBER;
l_item_id NUMBER;
BEGIN
-- if nvl(p_org_id,0)<=0 then
-- BEGIN
-- SELECT organization_id
-- INTO l_organization_id
-- FROM org_organization_definitions
-- WHERE organization_code = p_org_code;
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- l_organization_id := 0;
-- o_succ_flag := 'N';
-- o_error_message :=
-- p_org_code || ' doesnt exist in system, pls check.';
-- WHEN OTHERS THEN
-- l_organization_id := 0;
-- o_succ_flag := 'N';
-- o_error_message := SQLCODE || ' ' || SQLERRM;
-- END;
-- else
-- l_organization_id:=p_org_id;
-- end if;
-- BEGIN
-- SELECT inventory_item_id
-- INTO l_item_id
-- FROM mtl_system_items_kfv
-- WHERE concatenated_segments = p_assembly_item
-- AND organization_id = l_organization_id;
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- l_item_id := 0;
-- o_succ_flag := 'N';
-- o_error_message :=
-- p_assembly_item || ' doesnt exist in system, pls check.';
-- WHEN OTHERS THEN
-- l_item_id := 0;
-- o_succ_flag := 'N';
-- o_error_message := SQLCODE || ' ' || SQLERRM;
-- END;
expand_bom_sp (p_assembly_item => p_assembly_item,
p_assembly_item_id => p_assembly_item_id,
p_org_id => p_org_id,
p_identity => p_identity,
p_order_by => p_order_by,
p_expand_level => p_expand_level,
o_succ_flag => o_succ_flag,
o_error_message => o_error_message);
END;
---------------------------------------------------------------------------------------
-- p_expand_level parameter define as follow
-- A means expand all components all herichy under this assembly part number
PROCEDURE expand_bom_sp (
p_assembly_item IN VARCHAR2,
p_assembly_item_id IN NUMBER,
p_org_id IN VARCHAR2,
p_identity IN VARCHAR2,
p_order_by IN VARCHAR2 DEFAULT NULL,
p_expand_level IN VARCHAR2 DEFAULT 'A',
o_succ_flag OUT VARCHAR2,
o_error_message OUT VARCHAR2)
IS
v_item_id NUMBER;
l_order_by VARCHAR2 (100);
v_assem_count NUMBER := 0;
v_level VARCHAR2 (50);
CURSOR cur_item_id
IS
SELECT bom.assembly_item_id,
bic.component_item_id,
bic.item_num,
bic.component_quantity,
bic.created_by,
bic.creation_date,
bic.last_updated_by,
bic.last_update_date,
bic.component_remarks,
bic.attribute1,
bic.attribute2,
bom.organization_id,
bic.wip_supply_type
FROM apps.bom_bill_of_materials bom,
apps.bom_inventory_components bic
WHERE bom.common_bill_sequence_id = bic.bill_sequence_id
AND bom.organization_id = p_org_id
AND bic.disable_date IS NULL
AND bic.implementation_date IS NOT NULL
AND bic.effectivity_date IS NOT NULL
AND bom.assembly_item_id = p_assembly_item_id
ORDER BY bic.item_num;
BEGIN
FOR deta_item IN cur_item_id LOOP
v_assem_count := 0;
l_order_by :=
(CASE
WHEN NVL (p_order_by, 'a') = 'a' THEN ''
ELSE TRIM (p_order_by)
END)
|| TRIM (TO_CHAR (cur_item_id%ROWCOUNT, '0999') );
v_item_id := deta_item.component_item_id;
apps.cux_bom_pkg.expand_bom_sp (p_assembly_item,
deta_item.component_item_id,
p_org_id,
p_identity,
l_order_by,
p_expand_level,
o_succ_flag => o_succ_flag,
o_error_message => o_error_message);
IF p_expand_level = 'A' THEN
INSERT INTO apps.cux_bom_expand
(p_item, assembly_item_id,
component_item_id,
item_num,
component_quantity,
created_by, creation_date,
last_updated_by,
last_update_date, identity,
order_by, component_remarks,
organization_id)
VALUES (p_assembly_item, deta_item.assembly_item_id,
deta_item.component_item_id,
deta_item.item_num,
deta_item.component_quantity,
deta_item.created_by, deta_item.creation_date,
deta_item.last_updated_by,
deta_item.last_update_date, p_identity,
l_order_by, deta_item.component_remarks,
deta_item.organization_id);
ELSE
SELECT COUNT (bom.assembly_item_id)
INTO v_assem_count
FROM apps.bom_bill_of_materials bom,
apps.bom_inventory_components bic
WHERE bom.common_bill_sequence_id = bic.bill_sequence_id
AND bom.organization_id = p_org_id
AND bom.implementation_date IS NOT NULL
AND bic.disable_date IS NULL
AND bic.implementation_date IS NOT NULL
AND bom.assembly_item_id = deta_item.component_item_id;
IF p_expand_level = 'M' AND v_assem_count > 0 THEN
INSERT INTO apps.cux_bom_expand
(p_item,
assembly_item_id,
component_item_id,
item_num,
component_quantity,
created_by,
creation_date,
last_updated_by,
last_update_date, identity,
order_by, component_remarks,
organization_id)
VALUES (p_assembly_item,
deta_item.assembly_item_id,
deta_item.component_item_id,
deta_item.item_num,
deta_item.component_quantity,
deta_item.created_by,
deta_item.creation_date,
deta_item.last_updated_by,
deta_item.last_update_date, p_identity,
l_order_by, deta_item.component_remarks,
deta_item.organization_id);
END IF;
IF p_expand_level = 'P' AND v_assem_count = 0 THEN
INSERT INTO apps.cux_bom_expand
(p_item,
assembly_item_id,
component_item_id,
item_num,
component_quantity,
created_by,
creation_date,
last_updated_by,
last_update_date, identity,
order_by, component_remarks,
organization_id)
VALUES (p_assembly_item,
deta_item.assembly_item_id,
deta_item.component_item_id,
deta_item.item_num,
deta_item.component_quantity,
deta_item.created_by,
deta_item.creation_date,
deta_item.last_updated_by,
deta_item.last_update_date, p_identity,
l_order_by, deta_item.component_remarks,
deta_item.organization_id);
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
o_succ_flag := 'N';
o_error_message := SQLCODE || ' ' || SQLERRM;
END;
END;
/*
--DROP TABLE LERADO.BOM_EXPAND CASCADE CONSTRAINTS;
CREATE TABLE apps.CUX_BOM_EXPAND
(
ASSEMBLY_ITEM_ID NUMBER NOT NULL,
COMPONENT_ITEM_ID NUMBER NOT NULL,
ITEM_NUM NUMBER,
COMPONENT_QUANTITY NUMBER NOT NULL,
CREATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
P_ITEM VARCHAR2(40 BYTE),
IDENTITY VARCHAR2(50 BYTE),
ORDER_BY VARCHAR2(100 BYTE),
COMPONENT_REMARKS VARCHAR2(240 BYTE),
ORGANIZATION_ID NUMBER null
)
--ALTER TABLE apps.CUX_BOM_EXPAND add(ORGANIZATION_ID NUMBER null)
*/
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11536986/viewspace-620197/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ebs二次開發2
- ebs二次開發3
- ebs二次開發4
- ebs二次開發5
- ebs二次開發6
- ebs二次開發7
- EBS開發筆記-1筆記
- ECshop 二次開發模板教程1
- EBS開發環境搭建開發環境
- EBS開發筆記-2筆記
- EBS FORM PLL 開發入門ORM
- Application(EBS)開發整理(轉)APP
- Dresdon二次開發
- ThinkS二次開發
- AutoCAD二次開發（.Net）之設定MText字型(1)
- SOLIDWORKS二次開發Solid
- NX二次開發-使用NXOPEN C++嚮導模板做二次開發C++
- Oracle EBS 基於Host併發程式的開發Oracle
- UG二次開發筆記筆記
- SOLIDWORKS二次開發形式Solid
- 小說APP原始碼開發，支援二次開發功能APP原始碼
- openfire 3.8.2 原始碼部署 /開發配置 / 二次開發原始碼
- 一個開源的OJ二次開發
- Scratch3 二次開發系列
- Voyager 的使用及二次開發
- SOLIDWORKS二次開發介紹Solid
- 基於ecshop的二次開發
- 團隊專案：二次開發
- ECshop 二次開發模板教程2
- ECshop 二次開發模板教程3
- ECshop 二次開發模板教程4
- ecshop 二次開發,原始碼分析原始碼
- C++大作業二次開發C++
- BIMFACE二次開發SDK 開源C#版C#
- 我的ECshop二次開發從零開始
- EBS之JTF_Grid 開發總結
- Web快速開發平臺，基於二次開發平臺Web
- 教育APP系統原始碼二次開發，原生開發功能APP原始碼