Oracle EBS - OM: 如何对销售定单行界面进行DataLoad，唯一性字段“Line”是非数据库字段，F11不可输入

最新推荐文章于 2024-12-29 17:40:42 发布

原创最新推荐文章于 2024-12-29 17:40:42 发布 · 502 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#销售定单行界面 #唯一性字段Line #非数据库字段 #F11不可输入

SQL & PL/SQL 同时被 2 个专栏收录

12 篇文章

订阅专栏

EBS: OM

3 篇文章

订阅专栏

本文介绍了销售订单行界面中唯一性字段“Line”的构成原理及其在数据库中的实现方式。Line由五个字段拼接而成，并提供了具体的Form源代码示例及查询语句。

销售定单行界面的唯一性字段“Line”是非数据库字段，F11不可输入

假如我们要在销售定单行界面DataLoad（修改字段），那么如何对销售定单行界面把你所需要的订单行找出来呢？

解决方法：

Line字段是有5个字段拼接起来的

Line := line_number || '.' || shipment_number || '.' || option_number || '.' || component_number || '.' ||service_number;

当然上面一些字段默认是Hide的，要把它们Show出来。

Form源代码关于如何拼接Line字段：

（1）
Line块的块级触发器POST-QUERY调用：
OE_LINE.Post_Query;（来自于库OEXOELIN）


（2)
库OEXOELIN里Line_Shipment_Option_Number赋值代码：
    OE_CONCAT_VALUES.LINE_SHIPMENT_OPTION
                                        (
                                         Name_in('Line.Line_Number'),
                                         Name_In('Line.Shipment_Number'),
                                         Name_In('Line.Option_Number'),
                                         x_concat_values,
                                         Name_In('Line.Component_Number'),
                                         Name_In('Line.Service_Number')
                                         );

    COPY(x_concat_values,'Line.Line_Shipment_Option_Number');
	
	
(3)
OE_CONCAT_VALUES包代码：
PACKAGE BODY oe_concat_values IS
  PROCEDURE line_shipment_option(line_number      IN NUMBER,
                                 shipment_number  IN NUMBER,
                                 option_number    IN NUMBER,
                                 p_concat_value   OUT VARCHAR2,
                                 component_number IN NUMBER DEFAULT NULL,
                                 service_number   IN NUMBER DEFAULT NULL) IS
  BEGIN
  
    --=========================================
    -- Added for identifying Service Lines
    --=========================================
    IF service_number IS NOT NULL THEN
      IF option_number IS NOT NULL THEN
        IF component_number IS NOT NULL THEN
          p_concat_value := line_number || '.' || shipment_number || '.' ||
                            option_number || '.' || component_number || '.' ||
                            service_number;
        ELSE
          p_concat_value := line_number || '.' || shipment_number || '.' ||
                            option_number || '..' || service_number;
        END IF;
      
        --- if a option is not attached
      ELSE
        IF component_number IS NOT NULL THEN
          p_concat_value := line_number || '.' || shipment_number || '..' ||
                            component_number || '.' || service_number;
        ELSE
          p_concat_value := line_number || '.' || shipment_number || '...' ||
                            service_number;
        END IF;
      
      END IF; /* if option number is not null */
    
      -- if the service number is null
    ELSE
      IF option_number IS NOT NULL THEN
        IF component_number IS NOT NULL THEN
          p_concat_value := line_number || '.' || shipment_number || '.' ||
                            option_number || '.' || component_number;
        ELSE
          p_concat_value := line_number || '.' || shipment_number || '.' ||
                            option_number;
        END IF;
      
        --- if a option is not attached
      ELSE
        IF component_number IS NOT NULL THEN
          p_concat_value := line_number || '.' || shipment_number || '..' ||
                            component_number;
        ELSE
          /*Bug2848734 - Added IF condition */
          IF (line_number IS NULL AND shipment_number IS NULL) THEN
            p_concat_value := NULL;
          ELSE
            p_concat_value := line_number || '.' || shipment_number;
          END IF;
        END IF;
      
      END IF; /* if option number is not null */
    
    END IF; /* if service number is not null */
  
  END line_shipment_option;

END oe_concat_values;

--查询

SELECT ool.line_id
      ,to_char(ool.line_number) ||
       decode(ool.shipment_number,
              NULL,
              NULL,
              '.' || to_char(ool.shipment_number)) ||
       decode(ool.option_number,
              NULL,
              NULL,
              '.' || to_char(ool.option_number)) ||
       decode(ool.component_number,
              NULL,
              NULL,
              decode(ool.option_number, NULL, '.', NULL) || '.' ||
              to_char(ool.component_number)) ||
       decode(ool.service_number,
              NULL,
              NULL,
              decode(ool.component_number, NULL, '.', NULL) ||
              decode(ool.option_number, NULL, '.', NULL) || '.' ||
              to_char(ool.service_number)) line_num
      ,msi.segment1 item_number
      ,ool.ordered_quantity
  FROM oe_order_lines_all ool, mtl_system_items msi
 WHERE ool.header_id = &header_id
   AND ool.ship_from_org_id = msi.organization_id(+)
   AND ool.inventory_item_id = msi.inventory_item_id(+)
   AND ool.item_type_code <> 'INCLUDED';