Oracle EBS 销售时物料保留出错相关症状和处理方法

之前,我在Oracle EBS R12版本中遇到过销售订单发运时"物料保留出错",伴随着物料事处处理接口错误和发运事务处事处理不了
背景:
销售订单发运事务处理或销售订发放,子库存的保留和物料事务处理出错.
主要症状:
1.INV:物料保留窗体中的题头的值为空
2.INV:物料事务处理接口错误解释"在释放保留时出现错误."
3.OM:发运事务处理下一步为"运行接口"
4.AR:应收事务处理已经正常产生
若以上没有第1个症状的情况,还是比较简单方法去"系统管理员"提交"工作流后台流程"(Workflow Background Process)就可以处理;当第1个症状时,若保留中的数量与发运事务处理发运数一致比较好办,也可以提交一下"工作流后台流程"(Workflow Background Process)试一试.但是,若保留中的数量与发运事务处理发运数不一致,则麻烦太了.这个错误是Oracle EBS的BUG,公司提交SR,半个月Oracle的也没有回复这事处理.做事效率也太低了.真是"店大欺负客,客大欺店"呀!
原因:.
我们公司用户太NB了,一个订单行拆分成70多行致导保留出错.
解决方法:只能通过保留API来改修或新增销售订单的保留.
1.找到SO发运事务处理订单行要保留的数量,找出哪一些保留是要修改或新增.

SELECT oeh.org_id ou_id,
       hou.NAME ou_name,
       mso.concatenated_segments header_number,
       oel.line_number || '.' || oel.shipment_number ||
       decode(oel.option_number, NULL, NULL, '.' || oel.option_number) ||
       decode(oel.component_number, NULL, NULL, '.' || oel.component_number) ||
       decode(oel.service_number, NULL, NULL, '.' || oel.service_number) line,
       mst.segment1 item_code,
       oel.flow_status_code status_code,
       lov.meaning status_name,
       rsv.organization_id,
       ood.organization_code,
       ood.organization_name,
       rsv.subinventory_code,
       rsv.requirement_date,
       rsv.reservation_quantity,
       rsv.reservation_uom_code,
       rsv.staged_flag,
       oel.ordered_quantity,
       oel.shipped_quantity,
       oel.shipping_quantity
FROM inv.mtl_reservations              rsv,
       inv.mtl_system_items_b            mst,
       apps.mtl_sales_orders_kfv         mso,
       ont.oe_order_lines_all            oel,
       ont.oe_order_headers_all          oeh,
       apps.fnd_lookup_values_vl         lov,
       apps.org_organization_definitions ood,
       apps.hr_operating_units           hou
WHERE rsv.organization_id = mst.organization_id
   AND rsv.inventory_item_id = mst.inventory_item_id
   AND rsv.demand_source_header_id = mso.sales_order_id
   AND rsv.demand_source_line_id = oel.line_id
   AND oel.header_id = oeh.header_id
   AND lov.lookup_type = 'LINE_FLOW_STATUS'
   AND oel.flow_status_code = lov.lookup_code
   AND rsv.organization_id = ood.organization_id
   AND oeh.org_id = hou.organization_id
   AND rsv.demand_source_type_id = 2 --需求2是销售定单，具体看inv.mtl_txn_source_types
   AND rsv.supply_source_type_id = 13 --供应13是库存，具体看inv.mtl_txn_source_types
   AND rsv.reservation_quantity = 0 --数量为0
   AND nvl(rsv.staged_flag, 'N') <> 'Y' --未到待发库
   AND oel.flow_status_code = 'AWAITING_SHIPPING' --状态是等待发运
   AND oeh.org_id = 88
ORDER BY hou.NAME, ood.organization_code

2.对于一些发运事务处理订单行没有产生保留,要用保留API添加销售订单的保留量,处理SQL

DECLARE
p_rsv_rec           inv_reservation_global.mtl_reservation_rec_type;
p_serial_number     inv_reservation_global.serial_number_tbl_type;
x_return_status     VARCHAR2(1);
x_msg_count         NUMBER;
x_msg_data          VARCHAR2(2000);
x_serial_number     inv_reservation_global.serial_number_tbl_type;
x_quantity_reserved NUMBER;
x_reservation_id    NUMBER;
Cursor Cr is
    Select MMT.Creation_Date - + (-15 / 24 / 60) REQUIREMENT_DATE,
           MMT.ORGANIZATION_ID,
           MMT.INVENTORY_ITEM_ID,
           2 DEMAND_SOURCE_TYPE_ID,
           MMT.SOURCE_LINE_ID,
           MMT.TRANSACTION_UOM,
           -1 * Sum(MMT.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,
           13 SUPPLY_SOURCE_TYPE_ID,
           MMT.SUBINVENTORY_CODE,
           MMT.TRANSACTION_SOURCE_ID
      From MTL_TRANSACTIONS_INTERFACE MMT, MTL_RESERVATIONS_ALL_V MRA
     Where MMT.ORGANIZATION_ID = 429
       And MMT.SOURCE_CODE = 'ORDER ENTRY'
       And MMT.ORGANIZATION_ID = MRA.ORGANIZATION_ID(+)
       And MMT.SOURCE_LINE_ID = MRA.DEMAND_SOURCE_LINE_ID(+)
       And MRA.RESERVATION_ID Is Null
       --And MMT.SOURCE_LINE_ID = 664042
     Group By MMT.Creation_Date - + (-15 / 24 / 60),
              MMT.ORGANIZATION_ID,
              MMT.INVENTORY_ITEM_ID,
              MMT.SOURCE_LINE_ID,
              MMT.TRANSACTION_UOM,
              MMT.SUBINVENTORY_CODE,
              MMT.TRANSACTION_SOURCE_ID; ---221111040022
BEGIN
For Rs In Cr Loop
    p_rsv_rec.reservation_id               := NULL;
    p_rsv_rec.requirement_date             := Rs.REQUIREMENT_DATE;
    p_rsv_rec.organization_id              := Rs.ORGANIZATION_ID;
    p_rsv_rec.inventory_item_id            := Rs.INVENTORY_ITEM_ID;
    p_rsv_rec.demand_source_type_id        := Rs.DEMAND_SOURCE_TYPE_ID;
    p_rsv_rec.demand_source_name           := NULL;
    p_rsv_rec.demand_source_header_id      := Rs.TRANSACTION_SOURCE_ID;
    p_rsv_rec.demand_source_line_id        := Rs.SOURCE_LINE_ID;
    p_rsv_rec.demand_source_delivery       := NULL;
    p_rsv_rec.primary_uom_code             := NULL;
    p_rsv_rec.primary_uom_id               := NULL;
    p_rsv_rec.reservation_uom_code         := Rs.TRANSACTION_UOM;
    p_rsv_rec.reservation_uom_id           := NULL;
    p_rsv_rec.reservation_quantity         := Rs.TRANSACTION_QUANTITY;
    p_rsv_rec.primary_reservation_quantity := NULL;
    p_rsv_rec.detailed_quantity            := NULL;
    p_rsv_rec.autodetail_group_id          := NULL;
    p_rsv_rec.external_source_code         := NULL;
    p_rsv_rec.external_source_line_id      := NULL;
    p_rsv_rec.supply_source_type_id        := Rs.SUPPLY_SOURCE_TYPE_ID;
    p_rsv_rec.supply_source_header_id      := NULL;
    p_rsv_rec.supply_source_line_id        := NULL;
    p_rsv_rec.supply_source_name           := NULL;
    p_rsv_rec.supply_source_line_detail    := NULL;
    p_rsv_rec.revision                     := NULL;
    p_rsv_rec.subinventory_code            := Rs.SUBINVENTORY_CODE;
    p_rsv_rec.subinventory_id              := NULL;
    p_rsv_rec.locator_id                   := NULL;
    p_rsv_rec.lot_number                   := NULL;
    p_rsv_rec.lot_number_id                := NULL;
    p_rsv_rec.pick_slip_number             := NULL;
    p_rsv_rec.lpn_id                       := NULL;
    p_rsv_rec.ship_ready_flag              := NULL;
    p_rsv_rec.attribute_category           := NULL;
    p_rsv_rec.attribute1                   := NULL;
    p_rsv_rec.attribute2                   := NULL;
    p_rsv_rec.attribute3                   := NULL;
    p_rsv_rec.attribute4                   := NULL;
    p_rsv_rec.attribute5                   := NULL;
    p_rsv_rec.attribute6                   := NULL;
    p_rsv_rec.attribute7                   := NULL;
    p_rsv_rec.attribute8                   := NULL;
    p_rsv_rec.attribute9                   := NULL;
    p_rsv_rec.attribute10                  := NULL;
    p_rsv_rec.attribute11                  := NULL;
    p_rsv_rec.attribute12                  := NULL;
    p_rsv_rec.attribute13                  := NULL;
    p_rsv_rec.attribute14                  := NULL;
    p_rsv_rec.attribute15                  := NULL;

    Update OE_ORDER_LINES_ALL
       Set open_flag = 'Y'
     Where LINE_ID = Rs.SOURCE_LINE_ID;
    inv_reservation_pub.create_reservation(p_api_version_number       => '1.0',
                                           p_init_msg_lst             => fnd_api.g_true,
                                           x_return_status            => x_return_status,
                                           x_msg_count                => x_msg_count,
                                           x_msg_data                 => x_msg_data,
                                           p_rsv_rec                  => p_rsv_rec,
                                           p_serial_number            => p_serial_number,
                                           x_serial_number            => x_serial_number,
                                           p_partial_reservation_flag => fnd_api.g_true,
                                           p_force_reservation_flag   => fnd_api.g_false,
                                           p_validation_flag          => fnd_api.g_true,
                                           x_quantity_reserved        => x_quantity_reserved,
                                           x_reservation_id           => x_reservation_id);
    Update OE_ORDER_LINES_ALL
       Set open_flag = 'N'
     Where LINE_ID = Rs.SOURCE_LINE_ID;

    IF x_return_status = fnd_api.g_ret_sts_success THEN
      dbms_output.put_line('Reservation Created Successfully! Reservation ID: ' ||
                           x_reservation_id);
    ELSE
      ROLLBACK;
      dbms_output.put_line('Return Status: ' || x_return_status);
      fnd_msg_pub.count_and_get(p_count => x_msg_count,
                                p_data => x_msg_data);
      dbms_output.put_line('Error count : ' || to_char(x_msg_count));
      dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));
      FOR i IN 2 .. x_msg_count LOOP
        x_msg_data := fnd_msg_pub.get;
        dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));
      END LOOP;

    END IF;
End Loop;
END;

注意:以上红色SQL是将订单关闭状态改为登记.记得在物料事处理接口运行完以后要修改成关闭,其OPEN_FLAG为Y.
3.对一些发运事务处理的订单行保留数量与发运数量不一致的,要用保留的API修改订单的保留量,处理的SQL

DECLARE
p_original_rsv_rec       inv_reservation_global.mtl_reservation_rec_type;
p_to_rsv_rec             inv_reservation_global.mtl_reservation_rec_type;
p_original_serial_number inv_reservation_global.serial_number_tbl_type;
p_to_serial_number       inv_reservation_global.serial_number_tbl_type;
x_return_status          VARCHAR2(1);
x_msg_count              NUMBER;
x_msg_data               VARCHAR2(2000);
x_quantity_reserved      NUMBER;
x_secondary_quantity_reserved Number;
Cursor Cr is
    Select MRA.RESERVATION_ID,
           MMT.TRANSACTION_UOM,
           -1 * Sum(MMT.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,
           MMT.TRANSACTION_SOURCE_ID
      From MTL_TRANSACTIONS_INTERFACE MMT, MTL_RESERVATIONS_ALL_V MRA
     Where MMT.ORGANIZATION_ID = 429
       And MMT.SOURCE_CODE = 'ORDER ENTRY'
       And MMT.ORGANIZATION_ID = MRA.ORGANIZATION_ID(+)
       And MMT.SOURCE_LINE_ID = MRA.DEMAND_SOURCE_LINE_ID(+)
       And MRA.RESERVATION_ID Is Not Null

       ---And MMT.SOURCE_LINE_ID = 662459
     Group By MRA.RESERVATION_ID, MMT.TRANSACTION_UOM,MMT.TRANSACTION_SOURCE_ID;
BEGIN
For Rs In Cr Loop
    p_original_rsv_rec.reservation_id := Rs.RESERVATION_ID;
    p_to_rsv_rec.reservation_uom_code := Rs.TRANSACTION_UOM;
    p_to_rsv_rec.reservation_quantity := Rs.TRANSACTION_QUANTITY;
    p_to_rsv_rec.demand_source_header_id :=Rs.TRANSACTION_SOURCE_ID;

    inv_reservation_pub.update_reservation(p_api_version_number       => '1.0',
                                           p_init_msg_lst             => fnd_api.g_true,
                                           x_return_status            => x_return_status,
                                           x_msg_count                => x_msg_count,
                                           x_msg_data                 => x_msg_data,
                                           x_quantity_reserved        => x_quantity_reserved,
                                           x_secondary_quantity_reserved=>x_secondary_quantity_reserved,
                                           p_original_rsv_rec         => p_original_rsv_rec,
                                           p_to_rsv_rec               => p_to_rsv_rec,
                                           p_original_serial_number   => p_original_serial_number,
                                           p_to_serial_number         => p_to_serial_number,
                                           p_validation_flag          => fnd_api.g_true,
                                           p_partial_reservation_flag => fnd_api.g_false,
                                           p_check_availability       => fnd_api.g_false);

    IF x_return_status = fnd_api.g_ret_sts_success THEN
      dbms_output.put_line('Reservation Updated Successfully! New Quantity: ' ||
                           x_quantity_reserved);
    ELSE
      ROLLBACK;
      dbms_output.put_line('Return Status: ' || x_return_status);
      fnd_msg_pub.count_and_get(p_count => x_msg_count,
                                p_data => x_msg_data);
      dbms_output.put_line('Error count : ' || to_char(x_msg_count));
      dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));
      FOR i IN 2 .. x_msg_count LOOP
        x_msg_data := fnd_msg_pub.get;
        dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));
      END LOOP;

    END IF;
end loop;
END;

目前,我们公司没有出现过有保留量是要删除的,也可以通过保留API删除.
注意:以上方法仅供参考,Oracle EBS有一个错误,可能有好多原因导致.请大家慎重使用,一定要先在UAT环境上测试OK,才能在PROD环境上使用.

Oracle EBS 销售时物料保留出错相关症状和处理方法

相关推荐