Inventory 物料库存 mtl_transactions_interface 的开发示例代码
mtl_transactions_interface的开发:
比如杂收，杂发，物料库存数控制等。
注意item的属性控制。
INSERT INTO mtl_transactions_interface
(transaction_interface_id,
transaction_header_id,
source_code,
source_header_id,
source_line_id,
process_flag,
transaction_mode,
inventory_item_id,
-- item_segment1 ,
revision,
organization_id,
transaction_date,
transaction_quantity,
subinventory_code,
locator_id,
transaction_source_id,
transaction_source_name,
transaction_reference,
transaction_type_id,
transaction_uom,
last_update_date,
last_updated_by,
creation_date,
created_by,
distribution_account_id,
wip_entity_type,
flow_schedule,
scheduled_flag,
-- ACCT_PERIOD_ID ,
attribute1,
attribute2)
VALUES
(v_interface_id, --999 , --transaction_interface_id
v_header_id, --transaction_header_id
'ROHS_CVT', --source_code
1, --source_header_id
1, --source_line_id
1, --process_flag
3, --transaction_mode 3-background 2-immediate
to_number(c2.attribute9), --inventory_item_id
-- NULL , --item_segment1
c2.revision, --revision
to_number(c2.attribute8), --organization_id
SYSDATE, --transaction_date
-1 * c2.quantity, --transaction_quantity
c2.attribute4, --subinventory_code
null, --locator_id
v_trans_source_id, -- 999 , --transaction_source_id
'ROHS LOT ADJUSTMENT', --to_char(c2.set_id) , --transaction_source_name
v_set_name || to_char(c2.id), --transaction_reference
v_issue, --transaction_type_id
v_primary_uom_code, --transaction_uom
SYSDATE, --last_update_date
p_userid, --last_updated_by
SYSDATE, --creation_date
p_userid, --created_by
l_inventory_debit_ccid, --distribution_account_id
null, --wip_entity_type
'Y', --flow_schedule
2, --scheduled_flag
-- 1655, --ACCT_PERIOD_ID
null, --'Y' , --attribute1
null --'N15387' --attribute2
);
------------------------------------------------
--mtl_transaction_lots_interface
------------------------------------------------
v_err := 'Inert 00item into mtl_transaction_lots_interface';
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id,
source_code,
source_line_id,
lot_number,
lot_expiration_date,
transaction_quantity,
process_flag,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES
(v_interface_id, --transaction_interface_id
'ROHS_CVT', --source_code
NULL, --source_line_id
c2.attribute3, --lot_number
NULL, --lot_expiration_date
-1 * c2.quantity, --transaction_quantity
1, --process_flag
SYSDATE, --last_update_date
p_userid, --last_updated_by
SYSDATE, --creation_date
p_userid --created_by
);
PO模块，采购订单interface开发的示例代码
INSERT INTO po_headers_interface
(interface_header_id,
action,
batch_id,
po_header_id,
DOCUMENT_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
--LAST_UPDATE_LOGIN,
org_id,
vendor_id,
vendor_site_id,
VENDOR_DOC_NUM,
EFFECTIVE_DATE,
EXPIRATION_DATE)
VALUES
(lcl_interface_header_id,
'CREATE',--or UPDATE
batch_id,
l_po_header.po_header_id,
l_po_header.segment1,
sysdate,
l_user_id,
sysdate,
l_user_id,
--l_po_header.LAST_UPDATE_LOGIN,
l_po_header.org_id,
l_po_header.vendor_id,
l_po_header.vendor_site_id,
l_po_header.quote_vendor_quote_number,
l_po_header.start_date,
l_po_header.end_date);
INSERT INTO PO_LINES_INTERFACE (
interface_line_id,
interface_header_id,
action,
line_type_id,
line_num,
item,
--item_id,
item_revision,
unit_of_measure,
unit_price,
--list_price_per_unit,
--min_order_quantity,
--max_order_quantity,
quantity,
shipment_num
--creation_date,
--last_update_date,
--last_updated_by,
--created_by,
--EFFECTIVE_DATE,
--EXPIRATION_DATE
--SHIP_TO_ORGANIZATION_ID,
--line_ATTRIBUTE1,
--line_ATTRIBUTE2
) VALUES (
lcl_interface_line_id,
lcl_interface_header_id,
'ADD',
lr_po_lines.line_type_id,
l_max_line_num,
lcl_new_item_number,
--lcl_new_item_id,
lcl_item_revision,
lr_po_lines.unit_meas_lookup_code,
lr_po_lines.unit_price,
--lr_po_lines.list_price_per_unit,
--lr_po_lines.min_order_quantity,
--lr_po_lines.max_order_quantity,
lr_po_lines.quantity,
lr_po_lines.shipment_num
--sysdate,
--sysdate,
--l_user_id,
--l_user_id,
--lr_po_lines.start_date,
--lr_po_lines.end_date
--lr_po_lines.SHIP_TO_ORGANIZATION_ID,
--lr_po_lines.ATTRIBUTE1,
--lr_po_lines.ATTRIBUTE2
);
WIP工单interface的开发示例代码
insert into wip_job_schedule_interface
(last_update_date,
last_updated_by,
last_updated_by_name,
creation_date,
created_by,
created_by_name,
last_update_login,
group_id,
source_code,
process_phase,
process_status,
source_line_id,
organization_id,
organization_code,
load_type,
status_type,
first_unit_start_date,
first_unit_completion_date,
last_unit_start_date,
last_unit_completion_date,
line_id,
line_code,
primary_item_id,
bom_revision,
bom_revision_date,
bom_reference_id,
routing_reference_id,
routing_revision_date,
completion_subinventory,
completion_locator_id,
wip_supply_type,
class_code,
lot_number,
job_name,
description,
firm_planned_flag,
alternate_routing_designator,
alternate_bom_designator,
net_quantity,
start_quantity,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
priority,
allow_explosion,
header_id,
date_released)
values
(sysdate --last_update_date ,
,
v_user_id --last_updated_by,
,
null --last_updated_by_name
,
sysdate --creation_date ,
,
v_user_id --created_by ,
,
null --created_by_name
,
fnd_profile.value('LOGIN_ID') --last_update_login ,
,
v_group_id --group_id ,
,
null --r_so_line.order_number || '(' || r_so_line.line_number || ')' --source_code ,
,
2 -- 2,stand for processing --process_phase
,
wip_constants.pending -- 1,stand for pending --process_status
,
null --p_so_line_id --source_line_id ,
,
r_so_line.ship_from_org_id --organization_id ,
,
null --organization_code
,
v_load_type ---load_type 1-create standard work order,4- create non-standard work order
,
wip_constants.unreleased -- 3,--lc.status_type, --status_type ,3-release
,
v_start_date -- lc.scheduled_start_date, --first_unit_start_date ,
,
null -- lc.scheduled_completion_date,--first_unit_completion_date ,
,
null --lc.scheduled_start_date, --last_unit_start_date ,
,
v_schedule_completion_date -- lc.scheduled_completion_date,--last_unit_completion_date ,
,
null ---line_id ,
,
null --line_code
,
r_so_line.inventory_item_id ---primary_item_id ,
,
v_bom_revision ---bom_revision
,
null --sysdate --bom revision date
,
v_bom_reference_id ---bom_refference_id
,
null --routing_reffernce_id
,
null --routing_revision_date
,
r_so_line.subinventory --completion_subinventory ,
,
null --completion_locator_id ,
,
7 --wip_supply_type , 'Based in bill'
,
v_class_code ---class_code ,
,
v_wip_entity_name --lot_number ,
,
v_wip_entity_name --job_name ,
,
r_so_line.user_item_description --description ,
,
null --firm_planned_flag ,
,
null --alternate_routing_designator,
,
null --alternate_bom_designator ,
,
r_so_line.ordered_quantity --net_quantity ,
,
r_so_line.ordered_quantity --start_quantity ,
,
null --attribute_category
,
null --attribute1 ,
,
null --attribute2 ,
,
null --attribute3 ,
,
null --attribute4 ,
,
null --attribute5 ,
,
null --attribute6 ,
,
null --attribute7 ,
,
null --attribute8 ,
,
null --attribute9 ,
,
null --attribute10 ,
,
null --attribute11 ,
,
v_completion_date_dff --attribute12 ,
,
null --attribute13 ,
,
null --attribute14 ,
,
null --attribute15 ,
,
10 --priority ,
,
v_need_explode --allow_explosion ,
,
v_header_id --header_id ,
,
null --date_released
);
insert into wip_job_dtls_interface
(operation_seq_num,
inventory_item_id_old,
inventory_item_id_new,
quantity_per_assembly,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
department_id,
wip_supply_type,
date_required,
required_quantity,
quantity_issued,
supply_subinventory,
supply_locator_id,
mrp_net_flag,
mps_required_quantity,
mps_date_required,
auto_request_material,
comments,
attribute1,
attribute14,
attribute15,
load_type,
substitution_type,
interface_id,
group_id,
parent_header_id,
process_phase,
process_status,
organization_id)
values
(1 --operation_seq_num
,
null --r_so_line.inventory_item_id --inventory_item_id_new
,
r_so_line.inventory_item_id --inventory_item_id_new
,
1 --quantity_per_assembly
,
sysdate --last_update_date
,
v_user_id --last_updated_by
,
sysdate --creation_date
,
v_user_id --created_by
,
fnd_profile.value('LOGIN_ID') --last_update_login ,
,
null --department_id
,
1 --wip_supply_type 'PUSH'
,
r_so_line.schedule_ship_date --date_required
,
r_so_line.ordered_quantity --required_quantity
,
0 --quantity_issued
,
null --r_so_line.subinventory --supply_subinventory
,
null --supply_locator_id
,
1 --mrp_net_flag
,
null --r_so_line.ordered_quantity --mps_required_quantity
,
null --r_so_line.schedule_ship_date --mps_date_required
,
null --auto_request_material
,
null --comments
,
nvl(v_comp_revision, '00') --attribute1
,
null --attribute14
,
null --attribute15
,
wip_job_details.wip_mtl_requirement --load_type
,
wip_job_details.wip_add --substitution_type 'Add'
,
v_header_id --null --interface_id
,
v_group_id --group_id
,
v_header_id --parent_header_id
,
2 --process_phase
,
1 --process_status
,
r_so_line.ship_from_org_id --organization_id
);
PO模块PR的interface开发
INSERT INTO po_requisitions_interface_all
( transaction_id,
interface_source_code,
source_type_code,
requisition_type,
destination_type_code,
authorization_status,
batch_id,
preparer_id,
note_to_approver,
header_description,
header_attribute1,
header_attribute2,
header_attribute3,
header_attribute4,
header_attribute5,
line_type_id,
destination_organization_id,
item_id,
item_description,
unit_of_measure,
quantity,
unit_price,
need_by_date,
deliver_to_location_id,
deliver_to_requestor_id,
charge_account_id,
project_id,
task_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_date,
suggested_buyer_id,
suggested_vendor_name,
suggested_vendor_site,
suggested_vendor_contact,
suggested_vendor_phone,
note_to_buyer,
justification,
line_attribute1,
line_attribute2,
line_attribute3,
line_attribute4,
line_attribute7,
line_attribute8,
line_attribute9,
org_id,
currency_code,
project_accounting_context,
note_to_receiver )
VALUES ( v_transaction_id,
'CMCC_IMPORT',
'VENDOR',
'PURCHASE',
decode(rec_interface_line.inventory_item_flag, 'Y', 'INVENTORY', 'EXPENSE'),
'INCOMPLETE',
p_batch_id,
p_preparer_id,
rec_interface_line.note_to_authorizer,
rec_interface_line.description,
rec_interface_line.department,
rec_interface_line.budget_type,
rec_interface_line.budget_property,
rec_interface_line.urgency,
rec_interface_line.gross_value,
rec_interface_line.line_type_id,
rec_interface_line.organization_id,
rec_interface_line.item_id,
rec_interface_line.item_description,
rec_interface_line.unit_of_measure,
rec_interface_line.quantity,
rec_interface_line.unit_price,
rec_interface_line.need_by_date,
rec_interface_line.deliver_to_location_id,
nvl(rec_interface_line.to_person_id, p_preparer_id),
rec_interface_line.charge_account_id,
rec_interface_line.project_id,
rec_interface_line.task_id,
decode(rec_interface_line.expenditure_organization_id, NULL, NULL,
decode(rec_interface_line.process_by_cadre_flag, 'Y', fnd_global.org_id,
rec_interface_line.expenditure_organization_id)),
decode(rec_interface_line.expenditure_organization_id, NULL, NULL,
rec_interface_line.expenditure_type),
decode(rec_interface_line.expenditure_organization_id, NULL, NULL,
rec_interface_line.expenditure_item_date),
rec_interface_line.suggested_buyer_id,
rec_interface_line.suggested_vendor_name,
decode(rec_interface_line.suggested_vendor_name, NULL, NULL,
rec_interface_line.suggested_vendor_site),
rec_interface_line.suggested_vendor_contact,
rec_interface_line.suggested_vendor_phone,
rec_interface_line.note_to_agent,
rec_interface_line.justification,
rec_interface_line.supply_cycle, -- attribute1
rec_interface_line.charge_cost_center, -- attribute2
rec_interface_line.charge_purpose, -- attribute3
rec_interface_line.charge_service_brand, -- attribute4
v_transaction_id, -- attribtue7
rec_interface_line.import_request_id, -- attribute8
rec_interface_line.line_num_to_sort, -- attribute9
decode(rec_interface_line.process_by_cadre_flag, 'Y', fnd_global.org_id,
rec_interface_line.org_id),
rec_interface_line.currency_code,
decode(rec_interface_line.project_id, NULL, 'N', 'Y'),
-- Concate these three fields into one NOTE_TO_RECEIVER
rec_interface_line.receiver_location || '|' ||
rec_interface_line.receiver_contact || '|' ||
rec_interface_line.receiver_phone );
BOM的interface开发的示例代码
---- **** INSERT INTO BOM INTERFACE *****
insert into bom.bom_bill_of_mtls_interface
(last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_number,
organization_code,
assembly_type,
process_flag,
transaction_type)
values
(g_today,
g_user_id,
g_today,
g_user_id,
g_user_id,
p_assembly_item_number,
p_org_code,
1,
1,
'CREATE');
---- **** INSERT INTO BOM COMP INTERFACE *****
Insert into bom_inventory_comps_interface
(last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
operation_seq_num,
assembly_item_number,
component_item_number, --convert to 00 function,
organization_code,
component_quantity,
wip_supply_type,
COMPONENT_REMARKS,
attribute12,
attribute13,
effectivity_date, -- get effectivity date from Assebly revision effectivey date, from mtl_item_revisions table
process_flag,
transaction_type)
values
(g_today,
g_user_id,
g_today,
g_user_id,
g_user_id,
1,
p_assembly_item_number,
p_component_item_number,
p_org_code,
p_component_quantity,
p_wip_supply_type,
substr(p_bom_notes, 1, 235),
substr(p_bom_notes, 236, 145),
substr(p_bom_notes, 380, 145),
p_effectivity_date,
1,
'CREATE');
---- **** INSERT INTO BOM REF_DESGS INTERFACE *****
INSERT into BOM_REF_DESGS_INTERFACE
(last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
COMPONENT_REFERENCE_DESIGNATOR,
ACD_TYPE, --1
operation_seq_num, --1
component_item_number,
effectivity_date, --assebly effectity date
organization_code,
assembly_item_number,
process_flag, --1
transaction_type)
VALUES
(g_today,
g_User_Id,
g_User_Id,
g_today,
g_User_Id,
substr(p_comp_reference_desg,1,14),
1,
1,
p_component_item_number,
p_effectivity_date,
p_org_code,
p_assembly_item_number,
1,
'CREATE'
);
insert into BOM_DELETE_GROUPS
(
DELETE_GROUP_SEQUENCE_ID ,
DELETE_GROUP_NAME ,
ORGANIZATION_ID ,
DELETE_TYPE ,
ACTION_TYPE ,
DESCRIPTION ,
ENGINEERING_FLAG ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
DELETE_COMMON_BILL_FLAG ,
DELETE_ORG_TYPE
) values (
l_delete_group_id,
'PC'||l_delete_group_id,
g_mst_org_id,
2,
1,
'PNO_CONVERSION',
1,
g_today,
g_user_id,
g_today,
g_user_id,
2,
1
);
insert into BOM_DELETE_ENTITIES
(
DELETE_ENTITY_SEQUENCE_ID ,
DELETE_GROUP_SEQUENCE_ID ,
DELETE_ENTITY_TYPE ,
BILL_SEQUENCE_ID ,
INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
ITEM_DESCRIPTION ,
ITEM_CONCAT_SEGMENTS ,
DELETE_STATUS_TYPE ,
PRIOR_PROCESS_FLAG ,
PRIOR_COMMIT_FLAG ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
) values (
BOM_delete_entities_s.nextval,
l_delete_group_id,
2,
R.X_mst_bill_sequence_id,
R.X_MST_ITEM_ID,
g_mst_org_id,
l_bom_description,
R.X_OO_ITEM_ID,
1,
1,
1,
g_today,
g_user_id,
g_today,
g_user_id
);
Inventory item interface的开发示例代码
INSERT into MTL_SYSTEM_ITEMS_INTERFACE
(SET_PROCESS_ID
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,organization_id
,organization_code
,segment1
--- ,item_number --- disabled on 9/11/03 - cannot handle dots
,description
,revision
,REVISION_QTY_CONTROL_CODE ---7/31
,lot_control_code
,auto_lot_alpha_prefix
,start_auto_lot_number
,template_name
,primary_uom_code
,primary_unit_of_measure
,material_sub_elem
,material_cost
,buyer_id
,planner_code
,process_flag
,transaction_type
,cost_of_sales_account
,sales_account
--- ,inspection_required_flag
,shrinkage_rate
,planning_time_fence_code
,planning_time_fence_days
,fixed_lead_time
,postprocessing_lead_time
,full_lead_time
,wip_supply_type
,inventory_item_status_code
,inventory_planning_code
,fixed_lot_multiplier
,mrp_safety_stock_code
,minimum_order_quantity
,maximum_order_quantity
,min_minmax_quantity
,max_minmax_quantity
,fixed_days_supply
,mrp_planning_code
,attribute1
,attribute2
,attribute4 --- 8/25/3
,attribute14
,LIST_PRICE_PER_UNIT
,SOURCE_TYPE
,SOURCE_ORGANIZATION_ID
)
values
(g_SET_PROCESS_ID
,g_Last_Update_Date
,G_User_Id
,G_User_Id
,G_Creation_Date
,G_User_Id
,G_Organization_id
,v_ITEM_rec.organization_code
,v_ITEM_rec.sep_item_number
---- ,v_ITEM_rec.sep_item_number --- disabled on 9/11/03
,NVL(v_ITEM_rec.Description1,g_dflt_item_description)
,g_starting_revision
,C_REVISION_CONTROL_NO --- 9/12 AS PER DONG -- 7/31
,v_lot_control_code -- 8/25/3
,C_Auto_Lot_Alpha_Prefix -- 7/31
,C_Start_Auto_Lot_Number -- 7/31
,v_ITEM_rec.Template_Name
,v_primary_uom_code
,v_primary_unit_of_measure
,v_material_sub_elem
,v_material_cost
,V_Buyer_ID
,V_Planner_Code
,g_Process_Flag
,g_Transaction_Type
,v_new_COGS_ID -- 7/31
,v_new_Sales_ID -- 7/31
--- ,v_ITEM_rec.inspection_required_flag --- DISABLED AS PER NI's note on 7/14
,v_shrinkage_rate
,v_planning_time_fence_code --- always user defined time fence
,nvl(v_ITEM_rec.planning_time_fence_days,0) --- as per Manju/Ni on 7/14 - zero if null
,V_fixed_lead_time --- 8/25/3
,v_postprocessing_lead_time
,v_full_lead_time --- 8/25/3
,v_wip_supply_type
,v_new_inv_item_status_code --- changed 7/29
,v_inventory_planning_code
,v_ITEM_rec.fixed_lot_multiplier
,v_mrp_safety_stock_code
,v_ITEM_rec.minimum_order_quantity
,v_maximum_order_quantity
,v_min_minmax_quantity
,v_max_minmax_quantity --- 8/25
,v_fixed_days_supply
,v_mrp_planning_code
,v_attribute1 --- 7/31
,v_attribute2 --- CHANGED 7/29
,v_attribute4 --- 8/25/3
,v_category_id --- in attribute14 - temporary
,v_list_price_per_unit --- ADDED 7/29
,v_SOURCE_TYPE --- ADDED 7/29
,v_SOURCE_ORGANIZATION_ID --- ADDED 7/29
);
OM interface开发的示例脚本
示例代码：
--Header
INSERT INTO oe_headers_iface_all
(order_category, operation_code,
order_source_id, orig_sys_document_ref,
order_type_id, created_by, creation_date,
last_update_date, last_updated_by,
customer_number, booked_flag,
payment_term_id, freight_terms, ordered_date,
salesrep_id, ship_to_org_id,
invoice_to_org_id, price_list_id,
ship_from_org_id, attribute1, sold_to_org_id,
customer_po_number
)
VALUES (lc.order_category, lc.operation_code,
lc.order_source_id, lc.orig_sys_document_ref,
lc.order_type_id, lc.created_by, lc.creation_date,
lc.last_update_date, lc.last_updated_by,
lc.customer_number, lc.booked_flag,
lc.payment_term_id, lc.freight_terms, lc.ordered_date,
lc.salesrep_id, lc.ship_to_org_id,
lc.invoice_to_org_id, lc.price_list_id,
lc.ship_from_org_id, lc.attribute1, lc.sold_to_org_id,
lc.customer_po_number
);
--Line:
INSERT INTO oe_lines_iface_all
(order_source_id,
orig_sys_document_ref,
orig_sys_line_ref,
org_id,
line_number,
line_type_id,
inventory_item_id,
ship_from_org_id,
schedule_status_code,
operation_code,
request_date,
ordered_quantity,
order_quantity_uom,
created_by,
creation_date,
last_update_date,
last_updated_by,
unit_list_price,
unit_selling_price,
calculate_price_flag,
invoice_to_org_id,
ship_to_org_id,
sold_from_org_id,
line_category_code,
tax_code,
schedule_ship_date,
override_atp_date_code,
demand_class_code,
inventory_item)
VALUES
(lc.order_source_id,
lc.orig_sys_document_ref,
lc.orig_sys_line_ref,
lc.org_id,
lc.line_number,
lc.line_type_id,
v_inventory_item_id,
lc.ship_from_org_id,
lc.schedule_status_code,
lc.operation_code,
lc.request_date,
lc.ordered_quantity,
v_order_quantity_uom,
lc.created_by,
lc.creation_date,
lc.last_update_date,
lc.last_updated_by,
lc.unit_list_price,
lc.unit_selling_price,
lc.calculate_price_flag,
lc.invoice_to_org_id,
lc.ship_to_org_id,
lc.sold_from_org_id,
lc.line_category_code,
lc.tax_code,
v_schedule_ship_date,
lc.override_atp_date_code,
v_demand_class_code,
lc.inventory_item);
BOM 接口开发
说明
在接口数据导入时，系统中接口表的某些字段如果没有输入值，系统可能会默认一些值。如果
在导入的时候导入一个manufaturing BOM，则该BOM在Oracle Bills of Material and Oracle Engineering
都可以看到。
在导入BOM之前，所有的物料和组件（成品，半成品，原材料）都必须在主组织中正确的设置，并
分配到相应的组织（操作：INV：Items>Master Items设置，保存后 Organization Assignment中，选中需
要分配的库存组织，保存）。
虽然BOM和Routing可以同时导入，但是工需中的工序号必须在导入BOM前已经存在，这样在BOM
可以为物料分配工序，如果没有设置工序，则导入BOM时的工序号OPERATION_SEQ_NUM字段必须设
置为 1 。
一旦将数据插入到接口表，就可以通过提交请求：Bill and Routing Interface，其中的字段：
Process_flag显示当前行的处理状态，可能的状态有：
1 - Pending
2 - Assigned Succeeded
3 - Assign/Validation Failed
4 - Validation Succeeded
7 - Import Succeeded (如导入后删除接口数据，则看不到 7 的状态)
如果导入不成功或导入失败，数据仍然停留在接口表中，则可以通过查看
MTL_INTERFACE_ERRORS表获得程序执行的错误消息：
select table_name, column_name, organization_id, message_name,
error_message
from mtl_interface_errors
where request_id = xxxx; (xxxx是提交的请求ID)
通过接口创建BOM
下面以一个例子来说明创建物料BOM的接口导入过程
一个成品品名 A 由两个物料组成: A1,A2
创建BOM涉及两个表:BOM_BILL_OF_MTLS_INTERFACE和
BOM_INVENTORY_COMPS_INTERFACE，为方便测试，删除两个表中的数据。
delete from bom_bill_of_mtls_interface;
delete from bom_inventory_comps_interface;
commit;
插入头信息
INSERT into bom_bill_of_mtls_interface
(process_flag,
organization_code,
item_number,
assembly_type,
last_updated_by,
created_by,
transaction_type)
VALUES
(1,
'Org_code',
'Item_A',
1,
-1,
-1,
'CREATE');
commit;
插入物料A1的行信息：
INSERT into bom_inventory_comps_interface
(component_item_number,
last_updated_by,
created_by,
operation_seq_num,
effectivity_date,
transaction_type,
process_flag,
component_quantity,
assembly_item_number,
organization_code)
VALUES
('Item_A1',
-1,
-1 ,
'2',
sysdate,
'CREATE',
1,
2,
'Item_A',
'Org_code');
插入物料A2的行信息：
INSERT into bom_inventory_comps_interface
(component_item_number,
last_updated_by,
created_by,
operation_seq_num,
effectivity_date,
transaction_type,
process_flag,
component_quantity,
assembly_item_number,
organization_code)
VALUES
('Item_A2',
-1,
-1 ,
'1',
sysdate,
'CREATE',
1,
3,
'Item_A',
'Org_code');
commit;
接口说明：
BOM_BILL_OF_MTLS_INTERFACE 表
该表存储每个导入到BOM中的头信息，每行代表一个Manufacturing或Engineering物料，通过
BILL_SEQUENCE_ID字段进行区分。创建一个物料料表在导入时，字段的含义：
PROCESS_FLAG=1 未处理事务。
ORGANIZATION_CODE 库存组织ID
ITEM_NUMBER 品名ID
ASSEMBLY_TYPE 1 à manufacturing 2 à engineering
TRANSACTION_TYPE ‘Create’ （该值在11.5.9版本前要求为 ‘Insert’ ）
其中ORGANIZATION_CODE字段可以通过在字段ORGANIZATION_ID插入值来代替，
ITEM_NUMBER字段可以通过插入值到ASSEMBLY_ITEM_ID来代替
如果你在该表的Version字段中插入值，则导入程序会在MTL_ITEM_REVISIONS表中插入一条记
录。
如果需要导入一个替代料单名，则需要在ALTERNATE_BOM_DESIGNATOR字段插入替代料单名。
BOM_INVENTORY_COMPS_INTERFACE表：
该表用来存储料表所包含的组件信息，即组成料表的原材料或半成品的信息，每行表示一个物料。
如果在料表头信息接口表中使用替代料表的，则在该接口表中必须在字段
ALTERNATE_BOM_DESIGNATOR中输入相应的值，并且BILL_SEQUENCE_ID字段留空。
该表中使用的字段的含义：
COMPONENT_ITEM_NUMBER 组件料号
OPERATION_SEQ_NUM 工序号
EFFECTIVITY_DATE 生效日期
TRANSACTION_TYPE 类型 ‘Create’
PROCESS_FLAG 1 未处理事物 pending
COMPONENT_QUANTITY 数量
ASSEMBLY_ITEM_NUMBER 接口表头信息表中的品名
ORGANIZATION_CODE 库存组织代码
其中COMPONENT_ITEM_NUMBER字段可以通过在字段COMPONENT_ITEM_ID中插入物料的ID
来代替，如果没有设置Routing，则OPERATION_SEQ_NUM字段必须插入固定值 1 。
ORGANIZATION_CODE字段可以通过在ORGANIZATION_ID字段中插入值来替代。
ASSEMBLY_ITEM_NUMBER字段可以通过在ASSEMBLY_ITEM_ID字段插入值来替代。
以上可以完成将数据插入到接口表，数据导入到接口表中以后，需要通过提交并发请求 Bill
and Routing Interface ，路径：BOM：Bill>Import，选择Bill and Routing Interface，输入参数提交即可
接收退货接口开发（Receiving Return to Supplier）
create or replace package body xxuts_o2c_receipt_return_pkg As
/*
---------------------------------------------------------------------------------------------
-- File Name : XXUTS_O2C_PO_IMP_PKG.pck
-- Program Name :
-- Purpose :
-- parameters : None
-- Dependencies :
--
-- Modification History
---------------------------------------------------------------------------------------------
-- | WHEN | Ver | WHO | WHAT
---------------------------------------------------------------------------------------------
--
-- 19-Mar-2008 |v1.0 | SIMON |
---------------------------------------------------------------------------------------------
*/
procedure main(errbuf OUT VARCHAR2,
retcode OUT number,
p_organization_id in number,
p_group_id in number)
Is
Cursor Cur_receipt is
select rt.transaction_id,
rt.group_id,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rt.po_distribution_id,
rt.organization_id,
rt.vendor_id,
rt.vendor_site_id,
rt.unit_of_measure,
rt.destination_type_code,
rt.subinventory,
rt.shipment_header_id,
rt.shipment_line_id,
rt.primary_quantity
--,rt.*
from rcv_transactions rt
where rt.transaction_type = 'DELIVER'
and rt.po_header_id in (20630, 20631, 20632)
--and rt.group_id = p_group_id
;
v_user_id number;
v_group_id number;
l_request_id number;
l_request_id2 number;
Begin
v_user_id := fnd_global.USER_ID;
Select RCV_INTERFACE_GROUPS_S.nextval into v_group_id from dual;
For c_receipt in Cur_receipt Loop
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
PRIMARY_QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
ITEM_DESCRIPTION,
EMPLOYEE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
VENDOR_SITE_ID,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY,
FROM_LOCATOR_ID,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DESTINATION_CONTEXT,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
VALIDATION_FLAG)
VALUES
(rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
v_group_id,--rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
5610, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
5610, --CREATED_BY
5610, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'IMMEDIATE', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
c_receipt.primary_quantity, --QUANTITY
c_receipt.primary_quantity, --PRIMARY_QUANTITY
c_receipt.unit_of_measure, --UNIT_OF_MEASURE
Null, --ITEM_ID
Null,--'手写笔',
0, --EMPLOYEE_ID
c_receipt.shipment_header_id, --SHIPMENT_HEADER_ID
c_receipt.shipment_line_id, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
c_receipt.vendor_id, --VENDOR_ID
c_receipt.vendor_site_id, --VENDOR_SITE_ID
c_receipt.organization_id, --FROM_ORGANIZATION_ID
Null, --'NA01', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
c_receipt.transaction_id, --PARENT_TRANSACTION_ID
c_receipt.po_header_id, --PO_HEADER_ID
c_receipt.po_line_id, --PO_LINE_ID
c_receipt.po_line_location_id, --PO_LINE_LOCATION_ID
c_receipt.po_distribution_id, --PO_DISTRIBUTION_ID
c_receipt.destination_type_code, --'RECEIVING', --DESTINATION_TYPE_CODE
c_receipt.destination_type_code, --DESTINATION_CONTEXT
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --VALIDATION_FLAG
);
End Loop;
l_request_id := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
'RVCTP', --PROGRAME SHORT NAME;
'',
'',
FALSE,
'IMMEDIATE',
v_group_id
--104--104,
--CHR(0)
);
l_request_id2 := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
'RCVDLPDT', --PROGRAME SHORT NAME;
'',
'',
FALSE,
'P_group_id=' || v_group_id,
'P_receipt_source_type=Supplier',
'P_qty_precision=2',
'P_org_id=' || p_organization_id);
FND_FILE.put_line(2,
'Receiving Transaction Processor: ' || l_request_id ||
' Receipt Traveller Concurrent program: ' ||
l_request_id2);
FND_FILE.put_line(2, 'Group Id: ' || v_group_id);
commit;
End main;
end xxuts_o2c_receipt_return_pkg;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-492485/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12122734/viewspace-492485/
129
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
