查询某个数据块的基础写法

来源：互联网发布：博森瑞大数据dba培训编辑：程序博客网时间：2024/06/06 10:57

--FIND 按钮
:parameter.G_query_find := 'TRUE';
:control.chk := 'N';
app_find.find('WSH_DELIVERY_RTN');
:parameter.G_query_find := 'FALSE';

--PRE-QUERY
app_folder.event('PRE-QUERY');
CUXFORM.PRE_QUERY('WSH_DELIVERY_RTN');

--CUXFORM.PRE_QUERY
PROCEDURE PRE_QUERY(p_block varchar2)
IS
BEGIN
    IF p_block = 'WSH_DELIVERY_RTN' THEN
      APP_QUERY.RESET('WSH_DELIVERY_RTN');
      SET_BLOCK_PROPERTY('WSH_DELIVERY_RTN',DEFAULT_WHERE,'');
      --fnd_message.debug('1 where: '||get_block_property('WSH_DELIVERY_RTN',default_where));
      IF :QUERY_FIND.SOURCE_CODE IS NOT NULL THEN
        COPY(:QUERY_FIND.SOURCE_CODE,'WSH_DELIVERY_RTN.ORDER_TYPE');
      END IF;
      IF :QUERY_FIND.SALES_REGION1 IS NOT NULL THEN
        COPY(:QUERY_FIND.SALES_REGION1||'.%','WSH_DELIVERY_RTN.SALES_REGION');
      END IF;
      IF :QUERY_FIND.SALES_REGION2 IS NOT NULL THEN
        COPY('%.'||:QUERY_FIND.SALES_REGION2,'WSH_DELIVERY_RTN.SALES_REGION');
      END IF;

      IF :QUERY_FIND.ORDER_NUMBER_B IS NOT NULL THEN
        APP_QUERY.APPEND('WSH_DELIVERY_RTN'
                        ,'order_number >= '''||:QUERY_FIND.ORDER_NUMBER_B||'''');
      END IF;
      IF :QUERY_FIND.ORDER_NUMBER_E IS NOT NULL THEN
        APP_QUERY.APPEND('WSH_DELIVERY_RTN'
                        ,'order_number <= '''||:QUERY_FIND.ORDER_NUMBER_E||'''');
      END IF;

      IF :QUERY_FIND.ORDER_DATE_B IS NOT NULL THEN
        APP_QUERY.APPEND('WSH_DELIVERY_RTN','trunc(ORDERED_DATE) >= TO_DATE('''||TO_CHAR(:QUERY_FIND.ORDER_DATE_B,'YYYYMMDD')||''',''YYYYMMDD'')');
      END IF;
      IF :QUERY_FIND.ORDER_DATE_E IS NOT NULL THEN
        APP_QUERY.APPEND('WSH_DELIVERY_RTN','trunc(ORDERED_DATE) <= TO_DATE('''||TO_CHAR(:QUERY_FIND.ORDER_DATE_E,'YYYYMMDD')||''',''YYYYMMDD'')');
      END IF;

      IF :QUERY_FIND.SHIP_DATE_B IS NOT NULL THEN
        APP_QUERY.APPEND('WSH_DELIVERY_RTN','trunc(CONFIRM_DATE) >= TO_DATE('''||TO_CHAR(:QUERY_FIND.SHIP_DATE_B,'YYYYMMDD')||''',''YYYYMMDD'')');
      END IF;
      IF :QUERY_FIND.SHIP_DATE_E IS NOT NULL THEN
        APP_QUERY.APPEND('WSH_DELIVERY_RTN','trunc(CONFIRM_DATE) <= TO_DATE('''||TO_CHAR(:QUERY_FIND.SHIP_DATE_E,'YYYYMMDD')||''',''YYYYMMDD'')');
      END IF;

      IF NVL(:QUERY_FIND.RETURNED_FLAG,'Y') = 'Y' THEN
        APP_QUERY.APPEND('WSH_DELIVERY_RTN','DELIVERY_STATUS IN( ''已发货'',''已收货'')');
      ELSE
        APP_QUERY.APPEND('WSH_DELIVERY_RTN','DELIVERY_STATUS IN( ''已发货'',''已收货'',''已回单'',''已结算'')');
      END IF;

END IF;
END PRE_QUERY;