料号转换程序终于开发完成

ENG05_Update 06 Item Attributes

写了一个form , 2个package ,代码量5000左右，小程序，呵呵，但是该用的都用了，五脏俱全

通过这次开发，熟悉了以下：

1． 简单Form 二次开发（应用template 模版进行二次开发， 写常见的触发，调用自己/系统的request 等）

2． ERP接口开发能力

3． 熟练运用Cursor ，写package 等PL/SQL 编程能力，应该说是有一个大的飞跃。（写了两个package ,其中一个后台copy mtl_system_items_b 表里的数据，另外一个是并发程序，通过form 来调用，以报表的形式显示更新结果）

4． 对ERP里的form, function, menu , responsibility 设置的熟悉

5．熟练SecureCRT ， FTP（form server）的上传编译form

一些代码具有可重复使用的特性，以后可重用！

尽管开发过程很艰难，压力很大，但是现在很高兴。

[@more@]

temp 代码：

-- for loop and cursor

--basic
declare
cursor occupancy_cur is
select pet_id, room_number from occupancy wher eoccupied_dt = sysdate;
occupancy_rec occupancy%rowtype;
begin
open occupancy_cur;
loop
fetch occupancy_cur
into occupancy_rec;
exit when occupancy_cur%notfound;
update_bill(occupancy_rec.pet_id, occupanchy_rec.room_number);
end loop;
end;
--better

declare
cursor occupancy_cur is
select pet_id, room_number from occupancy wher eoccupied_dt = sysdate;
begin
for occupancy_rec in occupancy_cur loop
update_bill(occupancy_rec.pet_id, occupanchy_rec.room_number);
end loop;
end;

-- 不适用于根据条件取得记录的 cursor

function 返回多个参数 用procedure 返回OUT/全局变量

insert_into_interface.sql :

mtl_system_items_interface
xxuts_ebiz_con_interface

-- get segment1 and inventory_item_id
select msi.segment1 , msi.inventory_item_id from mtl_system_items_b msi

-- get segment1 and inventory_item_id from interface

select msif.segment1, substr(msif.segment1, 1, 8) segment1s, msif.inventory_item_id from mtl_system_items_interface msif

mtl_system_items_interface

--UPDATE ITEM script
/* Formatted on 2006/08/11 17:40 (Formatter Plus v4.8.7) */
/* Formatted on 2006/08/24 09:49 (Formatter Plus v4.8.7) */
INSERT INTO mtl_system_items_interface
(inventory_item_id, segment1, organization_id, process_flag,
transaction_type, item_type, revision_qty_control_code,
must_use_approved_vendor_flag, inventory_planning_code,
planner_code, planning_make_buy_code, fixed_days_supply,
mrp_planning_code, planning_exception_set, shrinkage_rate,
preprocessing_lead_time, fixed_lead_time, attribute2,
attribute4, attribute1, postprocessing_lead_time, buyer_id,
attribute15, description
)
VALUES (175395, '003000002AZ06', 117, 1,
'UPDATE', 'P_D', 1,
'N', 6,
'GW', 2, NULL,
6, 200, 0,
2, 'F0120', 'F0120',
NULL, 0, 15945, NULL,
'new06'
)

SELECT segment1,inventory_item_id
organization_id, item_type, revision_qty_control_code,
must_use_approved_vendor_flag, inventory_planning_code, planner_code,
planning_make_buy_code, fixed_days_supply, mrp_planning_code,
planning_exception_set, shrinkage_rate, preprocessing_lead_time,
fixed_lead_time, attribute2, attribute4, attribute1,
postprocessing_lead_time, buyer_id, attribute15 --175393
FROM mtl_system_items_b msi
WHERE msi.segment1 IN
('03000002AZ06',
SUBSTR ('03000002AZ06', 1, LENGTH ('03000002AZ06') - 2) || '00')
AND msi.organization_id = 117

--CREATE ITEM script
INSERT INTO mtl_system_items_interface(organization_id, process_flag , transaction_type, segment1, description)
values(102, 1, 'CREATE', 'TESTINSERT04', 'tewsst') ;

commit;
rollback;

-- find insert result
select msif.*
from mtl_system_items_interface msif
where msif.segment1='TESTINSERT04'

select msi.item_type, msi.*
from mtl_system_items_b msi
where msi.segment1='TESTINSERT04'

delete from mtl_system_items_interface where segment1 ='000214-06'
delete from mtl_system_items_interface where segment1 ='000214-06'
--find
--12230006B06 OEEC 480B 1:1.5
-- 17531 and 102
select msi.inventory_item_id , msi.organization_id, msi.* --, msi.transaction_type
from mtl_system_items_b msi
where msi.segment1='TESTUPDATE01'

-- find transaction_type :UPDATE
select transaction_type
from mtl_system_items_interface

/* Formatted on 2006/08/10 11:14 (Formatter Plus v4.8.7) */
-- ITEM 00 FOR 12230006B06
SELECT organization_id , item_type, revision_qty_control_code, must_use_approved_vendor_flag,
inventory_planning_code, planner_code, planning_make_buy_code,
fixed_days_supply, mrp_planning_code, planning_exception_set,
shrinkage_rate, preprocessing_lead_time, fixed_lead_time, attribute2,
attribute4, attribute1, postprocessing_lead_time, buyer_id,
attribute15
FROM mtl_system_items_b msi
WHERE msi.segment1 = '000-00600-00'
and organization_id=117

-- import items
select msi.segment1, msi.*
from mtl_system_items_b msi
where msi.segment1 in ('12230006B00' , '12230006B06')
and msi.organization_id=117

select msi.*
from mtl_system_items_b msi
where msi.segment1='000-00600-06'
and msi.organization_id3=117

select msif.process_flag, msif.item_number, msif.segment1 ,msif.RESERVABLE_TYPE-- , msif.*
from mtl_system_items_interface msif
where msif.segment1='000-00600-06'

-- problem :
-- completed successfully ,
-- process flag 没有改变！！！
-- 查询导入的料号不存在

--ORGANIZATION_ID

select msi.segment1 , msi.*
from mtl_system_items_b msi
where msi. organization_id =117
and msi.segment1 like '%00'
1000001300

-- import success !!!!

select msi.*
from mtl_system_items msi
where msi.segment1='TESTINSERT03'

--find process_flag
select msif.process_flag, msif.item_number, msif.segment1 , msif.*
from mtl_system_items_interface msif
where msif.segment1='000214-06'

--
select msif.process_flag, msif.item_number, msif.segment1 , msif.*
from mtl_system_items_interface msif
where msif.segment1 IN ('000-00600-06', 'TESTUPDATE01')

-- find problem in mtl_interface_errors
select *
from mtl_interface_errors mie
where last_update_date >=to_date('2006-08-10', 'yyyy-mm-dd')
ORDER BY last_update_date desc

-- transaction_id 6749601
select *
from mtl_material_transactions
where transaction_id=6749601

-- find organization

Select organization_code , organization_id from org_organization_definitions

order by organization_code;

-- find master organization

select a.organization_id ,a.organization_code,a.master_organization_id, b.organization_code

from mtl_parameters a, org_organization_definitions b

where a.master_organization_id = b.organization_id

order by a.organization_code;

-- update 的时候不用考虑　OU

--mtl_planners

select *
from mtl_planners
where planner_code='B-1AN2K'

select organization_id , item_type, revision_qty_control_code, must_use_approved_vendor_flag,
inventory_planning_code, planner_code, planning_make_buy_code,
fixed_days_supply, mrp_planning_code, planning_exception_set,
shrinkage_rate, preprocessing_lead_time, fixed_lead_time, attribute2,
attribute4, attribute1, postprocessing_lead_time, buyer_id,
attribute15
from mtl_system_items_b msi, xxuts_rohs_userstemp xru
where msi.segment1= xru.substr(msif.segment1, 1, 8)
and xru.organization_id= msi.organization_id


select segment1 ,organization_id , item_type, revision_qty_control_code, must_use_approved_vendor_flag,
inventory_planning_code, planner_code, planning_make_buy_code,
fixed_days_supply, mrp_planning_code, planning_exception_set,
shrinkage_rate, preprocessing_lead_time, fixed_lead_time, attribute2,
attribute4, attribute1, postprocessing_lead_time, buyer_id,
attribute15
from mtl_system_items_b msi
where msi.segment1 = substr('1000000106', 1, 8)||'00' and organization_id=117

/* Formatted on 2006/08/23 19:38 (Formatter Plus v4.8.7) */
SELECT -- receipt_days_exception_code, receiving_routing_id,
--invoice_close_tolerance, receive_close_tolerance,
--auto_lot_alpha_prefix,
segment1, description, process_flag, transaction_type,
set_process_id, organization_id, item_type,
revision_qty_control_code, must_use_approved_vendor_flag,
inventory_planning_code, planner_code, planning_make_buy_code,
fixed_days_supply, mrp_planning_code, planning_exception_set,
shrinkage_rate, preprocessing_lead_time, fixed_lead_time, attribute2,
attribute4, attribute1, postprocessing_lead_time, buyer_id,
attribute15, inventory_item_id
FROM mtl_system_items_interface

commit;




delete from mtl_system_items_interface
commit;

dev_form.sql

-- choose org_id in from
SELECT msi.segment1
FROM mtl_system_items_b msi
WHERE msi.segment1 LIKE '%06'
and msi.organization_id = FND_ORG.CHOOSE_ORG(true);

/* Formatted on 2006/08/17 15:15 (Formatter Plus v4.8.7) */
BEGIN
DECLARE
-- temprecord NUMBER;
v_process_flag number ;
v_transaction_type varchar2(10);
v_item_type varchar2(10);
v_revision_qty_control_code number;
v_must_use_approved_vendor_flag number;
v_inventory_planning_code varchar2(10);
v_planner_code number;
v_planning_make_buy_code number;
v_fixed_days_supply number;
v_mrp_planning_code number;
v_planning_exception_set varchar2(10);
v_shrinkage_rate number;
v_preprocessing_lead_time number;
v_fixed_lead_time number;
v_attribute1 varchar2(10);
v_attribute2 varchar2(10);
v_attribute4 varchar2(10);
v_attribute15 varchar2(10);
v_postprocessing_lead_time number;
v_buyer_id number;
BEGIN
--temprecord := :SYSTEM.cursor_record;
FIRST_RECORD;

LOOP
-- insert into interface


IF :SYSTEM.LAST_RECORD <> 'TRUE'
THEN
NEXT_RECORD;
ELSE
EXIT;
END IF;
END LOOP;
END;
END;

/* Formatted on 2006/08/24 13:44 (Formatter Plus v4.8.7) */
-- create temp table
CREATE GLOBAL TEMPORARY TABLE xxuts_rohs_userstemp
(
segment1 VARCHAR2(40),
description VARCHAR2(240),
inventory_item_id NUMBER,
organization_id NUMBER,

item_type VARCHAR2(10),
revision_qty_control_code NUMBER,
must_use_approved_vendor_flag VARCHAR2(10),

inventory_planning_code NUMBER,
planner_code VARCHAR2(10),
planning_make_buy_code NUMBER,

fixed_days_supply NUMBER,
mrp_planning_code NUMBER,
planning_exception_set VARCHAR2(10),

shrinkage_rate NUMBER,
preprocessing_lead_time NUMBER,
fixed_lead_time NUMBER,
attribute2 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute1 VARCHAR2(240),
postprocessing_lead_time NUMBER,
buyer_id NUMBER,
attribute15 VARCHAR2(240),
insert_flag VARCHAR2(2),
update_flag VARCHAR2(2),
error VARCHAR2(240),
curr_record VARCHAR2(10)

) ON COMMIT PRESERVE ROWS

CREATE TABLE xxuts_rohs_userstemp
(
segment1 VARCHAR2(40),
description VARCHAR2(240),
inventory_item_id NUMBER,
organization_id NUMBER,

item_type VARCHAR2(10),
revision_qty_control_code NUMBER,
must_use_approved_vendor_flag VARCHAR2(10),

inventory_planning_code NUMBER,
planner_code VARCHAR2(10),
planning_make_buy_code NUMBER,

fixed_days_supply NUMBER,
mrp_planning_code NUMBER,
planning_exception_set VARCHAR2(10),

shrinkage_rate NUMBER,
preprocessing_lead_time NUMBER,
fixed_lead_time NUMBER,
attribute2 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute1 VARCHAR2(240),
postprocessing_lead_time NUMBER,
buyer_id NUMBER,
attribute15 VARCHAR2(240),
insert_flag VARCHAR2(2),
update_flag VARCHAR2(2),
error VARCHAR2(240),
curr_record VARCHAR2(10)

)

/* Formatted on 2006/08/23 19:25 (Formatter Plus v4.8.7) */
CREATE TABLE xxuts_update_usercomm
(
segment1 VARCHAR2(40),
description VARCHAR2(240),
inventory_item_id NUMBER,
organization_id NUMBER,
error VARCHAR2(240),
item_type VARCHAR2(10),
revision_qty_control_code NUMBER,
must_use_approved_vendor_flag VARCHAR2(10),

inventory_planning_code NUMBER,
planner_code VARCHAR2(10),
planning_make_buy_code NUMBER,

fixed_days_supply NUMBER,
mrp_planning_code NUMBER,
planning_exception_set VARCHAR2(10),

shrinkage_rate NUMBER,
preprocessing_lead_time NUMBER,
fixed_lead_time NUMBER,
attribute2 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute1 VARCHAR2(240),
postprocessing_lead_time NUMBER,
buyer_id NUMBER,
attribute15 VARCHAR2(240),
insert_flag VARCHAR2(2),
update_flag VARCHAR2(2),
curr_record VARCHAR2(2)

) --on commit preserve rows

drop table xxuts_update_601
drop table xxuts_update_usercomm
drop table xxuts_rohs_userstemp

*******************************
select * from xxuts_rohs_test_final
select * from xxuts_update_601

select * from xxuts_update_usercomm
select * from xxuts_rohs_userstemp

select * from xxuts_rohs_userstemp
***********************************

DELETE FROM xxuts_update_601
delete from xxuts_update_usercomm
delete from xxuts_rohs_userstemp

commit;

rollback;

****************

xxuts_rohs_item60
/* Formatted on 2006/08/23 16:39 (Formatter Plus v4.8.7) */
SELECT segment1,inventory_item_id
organization_id, item_type, revision_qty_control_code,
must_use_approved_vendor_flag, inventory_planning_code, planner_code,
planning_make_buy_code, fixed_days_supply, mrp_planning_code,
planning_exception_set, shrinkage_rate, preprocessing_lead_time,
fixed_lead_time, attribute2, attribute4, attribute1,
postprocessing_lead_time, buyer_id, attribute15 --175393
FROM mtl_system_items_b msi
WHERE msi.segment1 IN
('0300000206',
SUBSTR ('0300000206', 1, LENGTH ('0300000206') - 2) || '00')
AND msi.organization_id = 117

select *
from mtl_system_items_b msi
where msi.organization_id=117
and msi.segment1='0300000200'

select segment1, organization_id , item_type, revision_qty_control_code, must_use_approved_vendor_flag,
inventory_planning_code, planner_code, planning_make_buy_code,
fixed_days_supply, mrp_planning_code, planning_exception_set,
shrinkage_rate, preprocessing_lead_time, fixed_lead_time, attribute2,
attribute4, attribute1, postprocessing_lead_time, buyer_id,
attribute15 from xxuts_update_usercomm



SELECT msi.item_type, msi.revision_qty_control_code,
msi.must_use_approved_vendor_flag,
msi.inventory_planning_code, msi.planner_code,
msi.planning_make_buy_code, msi.fixed_days_supply,
msi.mrp_planning_code, msi.planning_exception_set,
msi.shrinkage_rate, msi.preprocessing_lead_time,
msi.fixed_lead_time, msi.attribute2, msi.attribute4,
msi.attribute1, msi.postprocessing_lead_time, msi.buyer_id,
msi.attribute15
/* INTO t_item_type, t_revision_qty_control_code,
t_approved_vendor_flag,
t_inventory_planning_code, t_planner_code,
t_planning_make_buy_code, t_fixed_days_supply,
t_mrp_planning_code, t_planning_exception_set,
t_shrinkage_rate, t_preprocessing_lead_time,
t_fixed_lead_time, t_attribute2, t_attribute4,
t_attribute1, t_postprocessing_lead_time, t_buyer_id,
t_attribute15 */
FROM mtl_system_items_b msi
WHERE msi.segment1 =
SUBSTR (1000000106, 1, 8)
|| '00' --substr(msif.segment1, 1, 8)
AND msi.organization_id = 117;



select substr(msi.segment1,1, length(msi.segment1)-2), length(msi.segment1)
from mtl_system_items_b msi
where msi.segment1 ='000944-00'

select msi.segment1
from
mtl_system_items_b msi
where msi.segment1 like '%00' and msi.ORGANIZATION_ID=117
*****************



create table xxuts_update_601
(
segment1 varchar2(20),
description varchar2(50),
organization_id number,
curr_record varchar2(2),
inventory_item_id number
)

rollback;
--select items info

SELECT msi.segment1, msi.organization_id , msi.INVENTORY_ITEM_ID
FROM mtl_system_items_b msi
WHERE msi.segment1 LIKE '%00' and organization_id=117

select msi.*
from mtl_system_items_b msi
where msi.inventory_item_id =5356

*********************************

BEGIN
SELECT requested_start_date, actual_completion_date,phase_code, status_code
INTO v_requested_start_date, v_actual_completion_date,v_phase_code, v_status_code
FROM fnd_conc_req_summary_v

WHERE request_id=:parameter.req_id;
EXCEPTION WHEN OTHERS THEN
FND_MESSAGE.DEBUG('error!');
END;
IF V_PHASE_CODE='C' AND V_STATUS_CODE='C' THEN
SET_ITEM_PROPERTY('ADV_EXCHANGE.PROC',LABEL,'显示数据');
v_command := fnd_profile.VALUE ('APPS_WEB_AGENT');
v_command := v_command || '/Xxuts_Ommpr_All_Pkg.xxuts_ommpr_main_disp?p_seq_no='||:PARAMETER.SEQ_NO;
web.show_document (v_command, '_blank');
ELSE
FND_MESSAGE.DEBUG('数据正在处理中，请稍候!');
END IF;
******************************************************************************************

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/226700/viewspace-860578/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/226700/viewspace-860578/