APPS.OE_CREDIT_INTERFACE_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 88

  SELECT SUM (
                 ( NVL( rl.amount, 0 ) )
               +   DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
                   NVL(l.tax_value,0), 0 ), 0 )
              )
    FROM oe_order_lines l
       , oe_order_headers_all h
       , ra_interface_lines_all rl
   WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
     AND h.header_id                      = l.header_id
     AND h.org_id                         = l.org_id
     AND NVL( l.invoiced_quantity, 0 )    <> 0
     AND l.line_category_code             = 'ORDER'
     AND h.booked_flag                    = 'Y'
     AND h.header_id                      <> l_header_id
     AND h.transactional_curr_code        = p_curr_code
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND rl.interface_line_attribute1     = h.order_number
     AND rl.interface_line_attribute6     = l.line_id
     AND (EXISTS
             (SELECT NULL
                FROM oe_payment_types_all pt
               WHERE pt.payment_type_code = NVL(l.payment_type_code,
                                            NVL(h.payment_type_code, 'BME'))
                 AND pt.credit_check_flag = 'Y'
                 AND NVL(pt.org_id, -99)  = l_org_id)
           OR
           (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));

Line: 121

  SELECT SUM (
          ( DECODE( SIGN (NVL( rl.quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( rl.amount, 0 ) )
         +  DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
            NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0), 0 )
           )
    FROM oe_order_lines l
       , oe_order_headers_all h
       , ra_interface_lines_all rl
   WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
     AND h.header_id                      = l.header_id
     AND h.org_id                         = l.org_id
     AND NVL( l.invoiced_quantity, 0 )    <> 0
     AND l.line_category_code             = 'RETURN'
     AND h.booked_flag                    = 'Y'
     AND h.header_id                      <> l_header_id
     AND h.transactional_curr_code        = p_curr_code
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND rl.interface_line_attribute1     = h.order_number
     AND rl.interface_line_attribute6     = l.line_id
     AND (EXISTS
             (SELECT NULL
                FROM oe_payment_types_all pt
               WHERE pt.payment_type_code = NVL(l.payment_type_code,
                                            NVL(h.payment_type_code, 'BME'))
                 AND pt.credit_check_flag = 'Y'
                 AND NVL(pt.org_id, -99)  = l_org_id)
           OR
           (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));

Line: 154

  SELECT SUM( NVL( rl.amount, 0 ))
    FROM oe_price_adjustments p
       , oe_order_lines   l
       , oe_order_headers_all h
       , ra_interface_lines_all rl
   WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
     AND p.line_id                        = l.line_id
     AND NVL( l.invoiced_quantity, 0 )    <> 0
     AND p.header_id                      = l.header_id
     AND p.header_id                      = h.header_id
     AND h.header_id                      = l.header_id
     AND h.org_id                         = l.org_id
     AND h.booked_flag                    = 'Y'
     AND p.applied_flag                   = 'Y'
     AND p.list_line_type_code            = 'FREIGHT_CHARGE'
     AND h.order_category_code IN ('ORDER','MIXED','RETURN')
     AND NVL(p.invoiced_flag, 'N')        = 'Y'
     AND h.transactional_curr_code        = p_curr_code
     AND h.header_id                      <> l_header_id
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND rl.interface_line_attribute1     = h.order_number
     AND rl.interface_line_attribute6     = p.price_adjustment_id
     AND (EXISTS
           (SELECT NULL
              FROM oe_payment_types_all pt
             WHERE pt.payment_type_code = NVL(l.payment_type_code,
                                          NVL(h.payment_type_code, 'BME'))
               AND pt.credit_check_flag = 'Y'
               AND NVL(pt.org_id, -99)  = l_org_id)
            OR
           (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));

Line: 189

  SELECT SUM( NVL( rl.amount, 0 ))
    FROM oe_price_adjustments p
       , oe_order_headers_all h
       , ra_interface_lines_all rl
   WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
     AND p.line_id IS NULL
     AND p.header_id                      = h.header_id
     AND h.booked_flag                    = 'Y'
     AND p.applied_flag                   = 'Y'
     AND p.list_line_type_code            = 'FREIGHT_CHARGE'
     AND h.order_category_code IN ('ORDER','MIXED','RETURN')
     AND NVL(p.invoiced_flag, 'N')        = 'Y'
     AND h.transactional_curr_code        = p_curr_code
     AND h.header_id                      <> l_header_id
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND rl.interface_line_attribute1     = h.order_number
     AND rl.interface_line_attribute6     = p.price_adjustment_id
     AND EXISTS
           (SELECT NULL
              FROM oe_payment_types_all pt,
                   oe_order_lines l
             WHERE pt.credit_check_flag = 'Y'
               AND l.header_id = h.header_id
               AND l.org_id    = pt.org_id
               AND NVL(pt.org_id, -99) = l_org_id
               AND pt.payment_type_code =
                   DECODE(l.payment_type_code, NULL,
                     DECODE(h.payment_type_code, NULL, pt.payment_type_code,
                            h.payment_type_code),
                            l.payment_type_code));

Line: 228

    SELECT SUM (
                 ( NVL( rl.amount, 0 ) )
               +   DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
                    NVL(l.tax_value,0), 0 ), 0 )
               )
      FROM oe_order_lines_all l
         , oe_order_headers_all h
         , ra_interface_lines_all rl
     WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
       AND h.header_id                      = l.header_id
       AND NVL( l.invoiced_quantity, 0 )    <> 0
       AND l.line_category_code             = 'ORDER'
       AND h.booked_flag                    = 'Y'
       AND h.header_id                      <> l_header_id
       AND h.transactional_curr_code        = p_curr_code
       AND nvl(rl.interface_status, '~')    <> 'P'
       AND rl.interface_line_context        = 'ORDER ENTRY'
       AND rl.interface_line_attribute1     = h.order_number
       AND rl.interface_line_attribute6     = l.line_id
       AND (EXISTS
             (SELECT NULL
                FROM oe_payment_types_all pt
               WHERE pt.payment_type_code = NVL(l.payment_type_code,
                                            NVL(h.payment_type_code, 'BME'))
                 AND pt.credit_check_flag = 'Y'
                 AND NVL(pt.org_id, -99)  = NVL(h.org_id, -99))
           OR
           (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));

Line: 260

  SELECT SUM (
          ( DECODE( SIGN (NVL( rl.quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( rl.amount, 0 ) )
         +  DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
            NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0), 0 )
           )
    FROM oe_order_lines_all l
       , oe_order_headers_all h
       , ra_interface_lines_all rl
   WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
     AND h.header_id                      = l.header_id
     AND NVL( l.invoiced_quantity, 0 )    <> 0
     AND l.line_category_code             = 'RETURN'
     AND h.booked_flag                    = 'Y'
     AND h.header_id                      <> l_header_id
     AND h.transactional_curr_code        = p_curr_code
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND rl.interface_line_attribute1     = h.order_number
     AND rl.interface_line_attribute6     = l.line_id
     AND (EXISTS
             (SELECT NULL
                FROM oe_payment_types_all pt
               WHERE pt.payment_type_code = NVL(l.payment_type_code,
                                            NVL(h.payment_type_code, 'BME'))
                 AND pt.credit_check_flag = 'Y'
                 AND NVL(pt.org_id, -99)  = NVL(h.org_id, -99))
           OR
           (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));

Line: 292

  SELECT SUM( NVL( rl.amount, 0 ))
    FROM oe_price_adjustments p
       , oe_order_lines_all l
       , oe_order_headers_all h
       , ra_interface_lines_all rl
   WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
     AND p.line_id                        = l.line_id
     AND NVL( l.invoiced_quantity, 0 )    <> 0
     AND p.header_id                      = l.header_id
     AND p.header_id                      = h.header_id
     AND h.header_id                      = l.header_id
     AND h.booked_flag                    = 'Y'
     AND p.applied_flag                   = 'Y'
     AND p.list_line_type_code            = 'FREIGHT_CHARGE'
     AND NVL(p.invoiced_flag, 'N')        = 'Y'
     AND h.transactional_curr_code        = p_curr_code
     AND h.header_id                      <> l_header_id
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND rl.interface_line_attribute1     = h.order_number
     AND rl.interface_line_attribute6     = p.price_adjustment_id
     AND (EXISTS
           (SELECT NULL
              FROM oe_payment_types_all pt
             WHERE pt.payment_type_code = NVL(l.payment_type_code,
                                          NVL(h.payment_type_code, 'BME'))
               AND pt.credit_check_flag = 'Y'
               AND NVL(pt.org_id, -99)  = NVL(h.org_id, -99))
            OR
           (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));

Line: 325

  SELECT SUM( NVL( rl.amount, 0 ))
    FROM oe_price_adjustments p
       , oe_order_headers_all h
       , ra_interface_lines_all rl
   WHERE rl.orig_system_bill_customer_id  = l_customer_id
     AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462
     AND p.line_id IS NULL
     AND p.header_id                      = h.header_id
     AND h.booked_flag                    = 'Y'
     AND p.applied_flag                   = 'Y'
     AND p.list_line_type_code            = 'FREIGHT_CHARGE'
     AND h.order_category_code IN ('ORDER','MIXED','RETURN')
     AND NVL(p.invoiced_flag, 'N')        = 'Y'
     AND h.transactional_curr_code        = p_curr_code
     AND h.header_id                      <> l_header_id
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND rl.interface_line_attribute1     = h.order_number
     AND rl.interface_line_attribute6     = p.price_adjustment_id
     AND EXISTS
           (SELECT NULL
              FROM oe_payment_types_all pt,
                   oe_order_lines l
             WHERE pt.credit_check_flag = 'Y'
               AND l.header_id = h.header_id
               AND l.org_id    = pt.org_id
               AND NVL(pt.org_id, -99) = NVL(h.org_id, -99)
               AND pt.payment_type_code =
                   DECODE(l.payment_type_code, NULL,
                     DECODE(h.payment_type_code, NULL, pt.payment_type_code,
                            h.payment_type_code),
                            l.payment_type_code));

Line: 392

       SELECT cas.cust_account_id
         INTO l_customer_id
         FROM hz_cust_site_uses su
            , hz_cust_acct_sites_all cas
        WHERE su.site_use_id       = l_site_use_id
          AND su.cust_acct_site_id = cas.cust_acct_site_id ;

Line: 450

             OE_DEBUG_PUB.Add('Select cust_glb_orders  ');

Line: 473

             OE_DEBUG_PUB.Add('Select cust_glb_orders_return ');

Line: 497

             OE_DEBUG_PUB.Add('Select cust_glb_orders_freight1  ');

Line: 518

             OE_DEBUG_PUB.Add('Select cust_glb_orders_freight2  ');

Line: 542

             OE_DEBUG_PUB.Add('Select cust_reg_orders  ');

Line: 566

             OE_DEBUG_PUB.Add('Select cust_reg_orders_return  ');

Line: 590

             OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight1  ');

Line: 611

             OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight2  ');