ebs二次開發4
CREATE OR REPLACE PACKAGE APPS.cux_invcc_pkg IS
/**************************************************************************
File name : cux_invcc_pkg.pck
Doc Ref(s) :
Project :
Description : Cycle count interface for Etrace
Change History Information
--------------------------
Version Date Author Change Reference / Description
------- ----------- --------------- ------------------------------------
1.0 2009-08-06 MARK First Version
**************************************************************************/
TYPE cur_reference IS REF CURSOR;
--get count item for etrace
PROCEDURE get_count_items(p_org_code IN VARCHAR2,
p_cc_name IN VARCHAR2,
p_seq IN NUMBER,
o_cursor IN OUT cur_reference,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2);
--process count entry var interface
PROCEDURE process_cc_count(p_cc_name IN VARCHAR2,
p_org_code IN VARCHAR2,
p_sequence IN NUMBER,
p_cc_qty IN NUMBER,
p_uom_code IN VARCHAR2,
p_reason IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2);
END cux_invcc_pkg;
/
CREATE OR REPLACE PACKAGE BODY APPS.cux_invcc_pkg IS
PROCEDURE validate_data(p_cc_name IN VARCHAR2,
p_org_code IN VARCHAR2,
p_seq IN NUMBER,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_reason IN VARCHAR2,
o_iface_rec OUT mtl_cceoi_var_pvt.inv_cceoi_type,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2) IS
--l_subinventory VARCHAR2(30);
l_organization VARCHAR2(30);
--l_locator VARCHAR2(30);
l_cc_name VARCHAR2(30);
--l_item_num VARCHAR2(30);
l_quantity NUMBER;
--l_item_rev VARCHAR2(30);
l_uom VARCHAR2(30);
l_reason VARCHAR2(30);
--Attribute field
l_exists NUMBER;
BEGIN
o_success_flag := 'Y';
--l_subinventory := p_subinventory;
l_organization := p_org_code;
--l_locator := p_locator;
l_cc_name := p_cc_name;
--l_item_num := p_item_num;
--l_item_rev := p_item_rev;
l_uom := p_uom;
l_reason := p_reason;
l_quantity := p_quantity;
--Validate oraganization
BEGIN
SELECT organization_id
INTO o_iface_rec.organization_id
FROM org_organization_definitions o
WHERE organization_code = l_organization;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Organization CODE:' ||
l_organization;
RETURN;
END;
--Validate cc_name
BEGIN
SELECT cycle_count_header_id
INTO o_iface_rec.cycle_count_header_id
FROM mtl_cycle_count_headers
WHERE cycle_count_header_name = l_cc_name
AND organization_id = o_iface_rec.organization_id;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid CC name:' || l_cc_name;
RETURN;
END;
BEGIN
SELECT cce.cycle_count_entry_id,
cce.count_list_sequence seq,
cce.cycle_count_header_id,
cce.inventory_item_id,
cce.cost_group_id
INTO o_iface_rec.cycle_count_entry_id,
o_iface_rec.count_list_sequence,
o_iface_rec.cycle_count_header_id,
o_iface_rec.inventory_item_id,
o_iface_rec.cost_group_id
FROM mtl_cycle_count_entries cce
WHERE cce.organization_id = o_iface_rec.organization_id
AND cce.entry_status_code = '1' --Uncounted
AND cce.count_list_sequence = p_seq
AND cce.cycle_count_header_id =
o_iface_rec.cycle_count_header_id;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid count seq.';
RETURN;
END;
/* --Validate cycle count item
BEGIN
SELECT cci.inventory_item_id
INTO o_iface_rec.inventory_item_id
FROM mtl_cycle_count_items cci,
mtl_system_items_kfv msi,
mtl_abc_classes ab
WHERE msi.inventory_item_id = cci.inventory_item_id
AND msi.organization_id = ab.organization_id
AND ab.abc_class_id = cci.abc_class_id
AND msi.concatenated_segments = l_item_num
AND cci.cycle_count_header_id =
o_iface_rec.cycle_count_header_id;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid count item:' || l_item_num;
RETURN;
END;
--Validate count item version
IF l_item_rev IS NOT NULL THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_item_revisions_vl
WHERE revision = l_item_rev
AND organization_id = o_iface_rec.organization_id;
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Item Rev.:' || l_item_rev;
RETURN;
END IF;
END IF;
--Validate subinventory
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_subinventory
AND organization_id = o_iface_rec.organization_id;
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Subinventory:' || l_subinventory;
RETURN;
ELSE
o_iface_rec.subinventory := l_subinventory;
END IF;
--Validate locator
IF l_locator IS NOT NULL THEN
BEGIN
SELECT inventory_location_id
INTO o_iface_rec.locator_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = l_locator
AND organization_id = o_iface_rec.organization_id;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Locator:' || l_locator;
RETURN;
END;
END IF;
*/
--Validate UOM
SELECT COUNT(1)
INTO l_exists
FROM mtl_units_of_measure_vl
WHERE uom_code = l_uom;
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid UOM:' || l_uom;
RETURN;
ELSE
o_iface_rec.count_uom := l_uom;
END IF;
--Validate Count quantity
IF l_quantity < 0 OR
l_quantity IS NULL THEN
o_success_flag := 'N';
o_error_mssg := 'Count quantity must be >0';
RETURN;
ELSE
o_iface_rec.count_quantity := l_quantity;
END IF;
--validate reason
IF l_reason IS NOT NULL THEN
BEGIN
SELECT r.reason_id
INTO o_iface_rec.transaction_reason_id
FROM mtl_transaction_reasons r
WHERE r.reason_name = l_reason;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Reason:' || l_reason;
RETURN;
END;
END IF;
--get employee full name
BEGIN
SELECT employee_id
INTO o_iface_rec.employee_id
FROM fnd_user u
WHERE user_id = fnd_global.user_id;
IF o_iface_rec.employee_id IS NULL THEN
o_success_flag := 'N';
o_error_mssg := 'User must set employee.';
RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Can not get User ID.';
RETURN;
END;
END validate_data;
PROCEDURE initialize_data(o_iface_rec IN OUT mtl_cceoi_var_pvt.inv_cceoi_type) IS
BEGIN
-- set online processing mode
o_iface_rec.process_mode := 1;
-- import request into the application and do any adjustments necessary
o_iface_rec.action_code := mtl_cceoi_var_pvt.g_process;
-- count list sequence of the enrty (null = don't know/unscheduled)
-- o_iface_rec.count_list_sequence := NULL;
--Date and Counter info
o_iface_rec.count_date := trunc(SYSDATE);
-- this record is not in the interface
o_iface_rec.cc_entry_interface_id := NULL;
END initialize_data;
--process data in the cc interface table
PROCEDURE handle_mcc_iface(p_cc_iface IN mtl_cceoi_var_pvt.inv_cceoi_type,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2) IS
l_status VARCHAR2(1);
l_errorcode VARCHAR2(20);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_interface_id NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
mtl_cceoi_action_pub.import_countrequest(p_api_version => 0.9,
p_commit => fnd_api.g_false,
x_return_status => l_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interface_rec => p_cc_iface,
x_interface_id => l_interface_id);
dbms_output.put_line(l_errorcode);
IF (l_status <> fnd_api.g_ret_sts_success) THEN
o_success_flag := 'N';
BEGIN
SELECT error_message
INTO o_error_mssg
FROM mtl_cc_interface_errors
WHERE cc_entry_interface_id = l_interface_id;
EXCEPTION
WHEN OTHERS THEN
IF l_msg_count = 1 OR
l_msg_count IS NULL THEN
o_error_mssg := l_msg_data;
ELSE
FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
o_error_mssg := o_error_mssg || ';' ||
fnd_msg_pub.get(p_msg_index => i,
p_encoded => 'F');
END LOOP;
END IF;
END;
END IF;
END handle_mcc_iface;
-- get need to count item
PROCEDURE get_count_items(p_org_code IN VARCHAR2,
p_cc_name IN VARCHAR2,
p_seq IN NUMBER,
o_cursor IN OUT cur_reference,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2) IS
l_org_id NUMBER;
l_header_id NUMBER;
l_exists NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--validate org code
IF p_org_code IS NULL THEN
o_success_flag := 'N';
o_error_mssg := 'Org code can not be null';
RETURN;
ELSE
BEGIN
SELECT organization_id
INTO l_org_id
FROM org_organization_definitions
WHERE organization_code = p_org_code;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid org code.';
RETURN;
END;
END IF;
--validate cc name
IF p_cc_name IS NULL THEN
o_success_flag := 'N';
o_error_mssg := 'Cycle count name can not be null';
OPEN o_cursor FOR
SELECT 'Cycle count name can not be null' error_mssg
FROM dual;
RETURN;
ELSE
BEGIN
SELECT cycle_count_header_id
INTO l_header_id
FROM mtl_cycle_count_headers
WHERE cycle_count_header_name = p_cc_name;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid cycle count name.';
OPEN o_cursor FOR
SELECT 'Invalid cycle count name' error_mssg
FROM dual;
RETURN;
END;
END IF;
--validate sequence
IF p_seq IS NULL THEN
o_success_flag := 'N';
o_error_mssg := 'Seq can not be null';
OPEN o_cursor FOR
SELECT 'Seq can not be null' error_mssg FROM dual;
RETURN;
ELSE
SELECT COUNT(1)
INTO l_exists
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = l_header_id
AND count_list_sequence = p_seq
AND entry_status_code = '1';
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid count list sequence.';
OPEN o_cursor FOR
SELECT 'Invalid count list sequence' error_mssg FROM dual;
RETURN;
END IF;
END IF;
--get data and return data set
OPEN o_cursor FOR
SELECT cce.count_list_sequence seq,
cce.subinventory,
cce.revision,
cce.lot_number,
ml.concatenated_segments locator,
it.segment1 item_no,
it.primary_uom_code uom_code
FROM mtl_system_items it,
mtl_cycle_count_entries cce,
mtl_item_locations_kfv ml
WHERE it.inventory_item_id = cce.inventory_item_id
AND it.organization_id = cce.organization_id
AND nvl(cce.locator_id,
-1) = ml.inventory_location_id(+)
AND cce.organization_id = ml.organization_id(+)
AND cce.entry_status_code = '1' --Uncounted
AND cce.organization_id = l_org_id
AND cce.count_list_sequence = p_seq
AND cce.cycle_count_header_id = l_header_id
ORDER BY cce.count_list_sequence;
EXCEPTION
WHEN OTHERS THEN
OPEN o_cursor FOR
SELECT 'Invalid CC name' error_mssg FROM dual;
END get_count_items;
--process count entry
PROCEDURE process_cc_count(p_cc_name IN VARCHAR2,
p_org_code IN VARCHAR2,
p_sequence IN NUMBER,
p_cc_qty IN NUMBER,
p_uom_code IN VARCHAR2,
p_reason IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2) IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_iface_rec mtl_cceoi_var_pvt.inv_cceoi_type;
BEGIN
l_success_flag := 'Y';
--setp 1 : validate data
validate_data(p_cc_name => p_cc_name,
p_org_code => p_org_code,
p_seq => p_sequence,
p_quantity => p_cc_qty,
p_uom => p_uom_code,
p_reason => p_reason,
o_iface_rec => l_iface_rec,
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 2 : initialize data
initialize_data(o_iface_rec => l_iface_rec);
--step 3 : call api process
handle_mcc_iface(p_cc_iface => l_iface_rec,
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--
o_success_flag := 'Y';
o_error_mssg := NULL;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception:' || SQLERRM;
END process_cc_count;
END cux_invcc_pkg;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11536986/viewspace-620201/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ebs二次開發1
- ebs二次開發2
- ebs二次開發3
- ebs二次開發5
- ebs二次開發6
- ebs二次開發7
- ECshop 二次開發模板教程4
- EBS開發筆記-1筆記
- EBS開發環境搭建開發環境
- EBS開發筆記-2筆記
- 【EBS】主機併發程式開發
- EBS FORM PLL 開發入門ORM
- Application(EBS)開發整理(轉)APP
- 網站修改二次開發，網站二次開發流程網站
- Dresdon二次開發
- ThinkS二次開發
- SOLIDWORKS二次開發Solid
- NX二次開發-使用NXOPEN C++嚮導模板做二次開發C++
- Oracle EBS 基於Host併發程式的開發Oracle
- UG二次開發筆記筆記
- SOLIDWORKS二次開發形式Solid
- kubernetes 二次開發
- 基於 solox 二次開發
- 小說APP原始碼開發，支援二次開發功能APP原始碼
- openfire 3.8.2 原始碼部署 /開發配置 / 二次開發原始碼
- 一個開源的OJ二次開發
- Scratch3 二次開發系列
- Voyager 的使用及二次開發
- SOLIDWORKS二次開發介紹Solid
- 基於ecshop的二次開發
- 團隊專案：二次開發
- ECshop 二次開發模板教程1
- ECshop 二次開發模板教程2
- ECshop 二次開發模板教程3
- ecshop 二次開發,原始碼分析原始碼
- C++大作業二次開發C++
- EBS之JTF_Grid 開發總結
- BIMFACE二次開發SDK 開源C#版C#