AP Payables Tolerance Listing - draft

            select
            DECODE(TOLERANCE_TYPE , 'GOODS' ,
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'GOODS'
                   AND lookup_type = 'TOLERANCE TYPE'),
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'SERVICES'
                   AND lookup_type = 'TOLERANCE TYPE')  ) C_TOLERANCE_TYPE,
               TOLERANCE_ID,
               TOLERANCE_NAME,
               DESCRIPTION,
               PRICE_TOLERANCE,
               QUANTITY_TOLERANCE,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               QTY_RECEIVED_TOLERANCE,
               TAX_TOLERANCE,
               LAST_UPDATE_LOGIN,
               CREATION_DATE,
               CREATED_BY,
               MAX_QTY_ORD_TOLERANCE,
               MAX_QTY_REC_TOLERANCE,
               SHIP_AMT_TOLERANCE,
               RATE_AMT_TOLERANCE,
               TOTAL_AMT_TOLERANCE,
               TAX_TOL_AMT_RANGE,
               DECODE(TOLERANCE_TYPE , 'GOODS' ,
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'QTY ORD'
                   AND lookup_type = 'HOLD CODE'),
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'AMT ORD'
                   AND lookup_type = 'HOLD CODE'))  C_HOLD_QTY_ORD,
               DECODE(TOLERANCE_TYPE , 'GOODS' ,
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'MAX QTY ORD'
                   AND lookup_type = 'HOLD CODE'),
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'MAX AMT ORD'
                   AND lookup_type = 'HOLD CODE')) C_HOLD_MAX_QTY_ORD,
               DECODE(TOLERANCE_TYPE , 'GOODS' ,
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'QTY REC'
                   AND lookup_type = 'HOLD CODE'),
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'AMT REC'
                   AND lookup_type = 'HOLD CODE')) C_HOLD_QTY_REC,
               DECODE(TOLERANCE_TYPE , 'GOODS' ,
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'MAX QTY REC'
                   AND lookup_type = 'HOLD CODE'),
                   (SELECT displayed_field
                   FROM ap_lookup_codes
                   WHERE lookup_code = 'MAX AMT REC'
                   AND lookup_type = 'HOLD CODE')) C_HOLD_MAX_QTY_REC,
              (SELECT displayed_field
                FROM ap_lookup_codes
                WHERE lookup_code = 'PRICE'
                  AND lookup_type = 'HOLD CODE')  C_HOLD_PRICE,
              (SELECT displayed_field
                FROM ap_lookup_codes
                WHERE lookup_code = 'MAX RATE AMOUNT'
                  AND lookup_type = 'HOLD CODE')  C_HOLD_MAX_RATE_AMOUNT,
              (SELECT displayed_field
                FROM ap_lookup_codes
                WHERE lookup_code = 'MAX SHIP AMOUNT'
                  AND lookup_type = 'HOLD CODE')  C_HOLD_MAX_SHIP_AMOUNT,
              (SELECT displayed_field
                FROM ap_lookup_codes
                WHERE lookup_code = 'MAX TOTAL AMOUNT'
                  AND lookup_type = 'HOLD CODE') C_HOLD_MAX_TOTAL_AMOUNT
            from
               AP_TOLERANCE_TEMPLATES
            order by
               TOLERANCE_TYPE Asc,
               TOLERANCE_NAME Asc