INV*账户别名接收发放
DECLARE
--p_old_new_flag OLD 为导出 NEW 为导入
l_iface_rec inv.mtl_transactions_interface%ROWTYPE;
l_iface_lot_rec inv.mtl_transaction_lots_interface%ROWTYPE;
l_timeout NUMBER;
l_outcome BOOLEAN;
l_error_code VARCHAR2(100);
l_error_explanation VARCHAR2(100);
l_request_id NUMBER;
l_sqlerrm VARCHAR2(1000);
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
l_uom VARCHAR2(240);
l_disposition_id NUMBER;
l_subinventory_code VARCHAR2(240);
l_count NUMBER;
l_locator_id NUMBER;
l_error_msg VARCHAR2(32767);
CURSOR cur IS
SELECT t.import_id,
t.organization_code,
t.item_number,
t.subinventory_code,
t.transaction_type transaction_type1,
decode(t.transaction_type,
'帐户别名接收',
41,
'帐户别名发放',
31,
NULL) transaction_type,
t.account_alias,
t.transaction_uom,
t.transaction_date,
t.lot_number,
decode(t.transaction_type,
'帐户别名接收',
t.transaction_quantity,
'帐户别名发放',
t.transaction_quantity * (-1),
NULL) transaction_quantity,
t.attribute1,
t.attribute2,
t.attribute3,
t.attribute4,
t.attribute5
FROM cux_inv_mmt_account_temp t
WHERE 1 = 1
--AND t.item_number = '1515453000051'
;
BEGIN
mo_global.init('INV');
fnd_global.apps_initialize(user_id => 1318,
resp_id => 50559,
resp_appl_id => 222);
mo_global.set_policy_context('S',
83);
l_count := 0;
FOR rec IN cur
LOOP
l_error_msg := NULL;
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := -1;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := -1;
l_iface_rec.last_update_login := -1;
l_iface_lot_rec.last_update_date := SYSDATE;
l_iface_lot_rec.last_updated_by := -1;
l_iface_lot_rec.creation_date := SYSDATE;
l_iface_lot_rec.created_by := -1;
l_iface_lot_rec.last_update_login := -1;
SELECT mtl_material_transactions_s.nextval
INTO l_iface_rec.transaction_interface_id
FROM dual;
BEGIN
SELECT msi.inventory_item_id,
msi.organization_id,
msi.primary_uom_code
INTO l_inventory_item_id, l_organization_id, l_uom
FROM mtl_system_items_b msi, org_organization_definitions ood
WHERE 1 = 1
AND msi.organization_id = ood.organization_id
AND ood.organization_code = rec.organization_code
AND msi.segment1 = rec.item_number;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := l_error_msg || '物料' || rec.item_number || '不存在';
END;
BEGIN
SELECT mgd.disposition_id
INTO l_disposition_id
FROM mtl_generic_dispositions mgd
WHERE 1 = 1
AND mgd.organization_id = l_organization_id
AND mgd.segment1 = rec.account_alias;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := l_error_msg || '账户别名不存在';
END;
BEGIN
SELECT msi.secondary_inventory_name
INTO l_subinventory_code
FROM mtl_secondary_inventories msi
WHERE 1 = 1
AND msi.organization_id = l_organization_id
AND msi.secondary_inventory_name = rec.subinventory_code;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := l_error_msg || '子库存不存在';
END;
IF (rec.attribute1 IS NOT NULL) THEN
BEGIN
SELECT mik.inventory_location_id
INTO l_locator_id
FROM mtl_item_locations_kfv mik
WHERE mik.concatenated_segments = rec.attribute1
AND mik.organization_id = l_organization_id
AND mik.subinventory_code = rec.subinventory_code;
EXCEPTION
WHEN no_data_found THEN
l_error_msg := l_error_msg || '货位不存在';
END;
END IF;
--dbms_output.put_line('1!');
l_iface_rec.transaction_header_id := l_iface_rec.transaction_interface_id;
l_iface_rec.transaction_mode := 3;
l_iface_rec.process_flag := 1;
l_iface_rec.transaction_type_id := rec.transaction_type; --41 帐户别名接收 31 帐户别名发放
l_iface_rec.transaction_source_id := l_disposition_id; --来源
l_iface_rec.organization_id := l_organization_id;
l_iface_rec.inventory_item_id := l_inventory_item_id;
l_iface_rec.subinventory_code := rec.subinventory_code;
l_iface_rec.locator_id := l_locator_id;
l_iface_rec.transaction_quantity := rec.transaction_quantity; --出库数量为负数 入库数量为正数
l_iface_rec.primary_quantity := rec.transaction_quantity;
l_iface_rec.transaction_uom := l_uom;
l_iface_rec.transaction_date := rec.transaction_date;
l_iface_rec.source_code := '账户别名接收/发放';
l_iface_rec.source_header_id := to_number(to_char(SYSDATE,
'YYYYMMDDHH24MISS'));
l_iface_rec.source_line_id := to_number(to_char(SYSDATE,
'YYYYMMDDHH24MISS'));
IF (l_error_msg IS NULL) THEN
INSERT INTO inv.mtl_transactions_interface VALUES l_iface_rec;
END IF;
IF (rec.lot_number IS NOT NULL) THEN
l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;
l_iface_lot_rec.lot_number := rec.lot_number;
l_iface_lot_rec.transaction_quantity := l_iface_rec.transaction_quantity;
l_iface_lot_rec.source_code := l_iface_rec.source_code;
l_iface_lot_rec.source_line_id := l_iface_rec.source_line_id;
IF (l_error_msg IS NULL) THEN
INSERT INTO inv.mtl_transaction_lots_interface
VALUES l_iface_lot_rec;
END IF;
END IF;
IF (l_error_msg IS NULL) THEN
l_timeout := 10;
l_outcome := mtl_online_transaction_pub.process_online(p_transaction_header_id => l_iface_rec.transaction_header_id,
p_timeout => l_timeout,
p_error_code => l_error_code,
p_error_explanation => l_error_explanation);
IF (l_outcome = FALSE) THEN
dbms_output.put_line('Failed*******Import_id:' || rec.import_id);
dbms_output.put_line('Failed!');
dbms_output.put_line('l_error_code:' || l_error_code);
dbms_output.put_line('l_error_explanation:' || l_error_explanation);
ELSIF (l_outcome = TRUE) THEN
l_count := l_count + 1;
END IF;
END IF;
IF (l_error_msg IS NOT NULL) THEN
dbms_output.put_line('l_error_msg:' || l_error_msg);
END IF;
END LOOP;
dbms_output.put_line('SuccessFul:' || l_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception!' || SQLERRM);
END;
--期初数据导入，创建表存储
create table cux_inv_mmt_account_temp( IMPORT_ID NUMBER, ORGANIZATION_CODE VARCHAR2(240), ITEM_NUMBER VARCHAR2(240), SUBINVENTORY_CODE VARCHAR2(240), TRANSACTION_TYPE VARCHAR2(240), ACCOUNT_ALIAS VARCHAR2(240), TRANSACTION_UOM VARCHAR2(240), TRANSACTION_DATE DATE, LOT_NUMBER VARCHAR2(240), TRANSACTION_QUANTITY NUMBER, ATTRIBUTE1 VARCHAR2(240), ATTRIBUTE2 VARCHAR2(240), ATTRIBUTE3 VARCHAR2(240), ATTRIBUTE4 VARCHAR2(240), ATTRIBUTE5 VARCHAR2(240) )
