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】Oracle EBS 之 OM 模組整理(1)Oracle
- 【EBS】主機併發程式開發
- EBS FORM PLL 開發入門ORM
- EBS之JTF_Grid 開發總結
- 網站修改二次開發，網站二次開發流程網站
- 基於Chappie-II的二次開發日誌-1APP
- kubernetes 二次開發
- SOLIDWORKS二次開發Solid
- Dresdon二次開發
- 有關oracle ebs中:Dialog的做法(1)Oracle
- EBS: 發運(WSH)表清單
- SOLIDWORKS二次開發形式Solid
- NX二次開發-使用NXOPEN C++嚮導模板做二次開發C++
- UG二次開發筆記筆記
- 基於 solox 二次開發
- SOLIDWORKS二次開發介紹Solid
- Voyager 的使用及二次開發
- C++大作業二次開發C++
- Scratch3 二次開發系列
- 小說APP原始碼開發，支援二次開發功能APP原始碼
- BIMFACE二次開發SDK 開源C#版C#
- Web快速開發平臺，基於二次開發平臺Web
- 教育APP系統原始碼二次開發，原生開發功能APP原始碼
- 檢視EBS版本
- 簡訊貓二次開發(java版)Java
- 第一週-軟體二次開發
- 造輪子：minicap/minitouch 二次開發
- Superset Windows二次開發環境搭建Windows開發環境
- GISer如何突破二次開發瓶頸
- Jmeter二次開發——自定義函式JMeter函式
- Oracle EBS基礎學習：Oracle EBS啟用診斷功能Oracle
- 致敬社群開源介面平臺並二次開發
- AndroidStudio外掛GsonFormat解析及二次開發AndroidORM
- 微信二次開發（生成二維碼）
- NX二次開發-建模-遍歷所有物件物件
- zabbix二次開發整合拓撲圖功能
- ThinkSNS：軟體二次開發怎麼做？
- CAD二次開發入門：WPF類庫
- Jmeter二次開發函式 - 文字替換JMeter函式