APPS.OE_CREDIT_INTERFACE_UTIL SQL Statements

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

Line: 92

  SELECT SUM (
                 ( NVL( rl.amount, 0 ) )
               +   DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
                   NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ), 0 )  --TaxER
              )
    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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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  --commented for bug#8879693
     AND h.transactional_curr_code        = p_curr_code
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
  --bug 11662722   AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
  --bug 11662722   AND rl.interface_line_attribute6     = To_Char(l.line_id) -- To_Char added for Bug 10008003
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722

AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id  --bug 11662722
     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: 128

  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)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0), 0 )  --TaxER
           )
    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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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   --commented for bug#8879693
     AND h.transactional_curr_code        = p_curr_code
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
    --bug 11662722 AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
    --bug 11662722 AND rl.interface_line_attribute6     = To_Char(l.line_id) -- To_Char added for Bug 10008003
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722

AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id  --bug 11662722
     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: 164

  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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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   --commented for bug#8879693
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
--bug 11662722     AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
 --bug 11662722    AND rl.interface_line_attribute6     = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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))
--TaxER Start
UNION ALL
  SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
    FROM oe_price_adjustments p,oe_price_adjustments p1
       , 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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
     AND p1.applied_flag                   = 'N'  --12895421
     AND p.price_adjustment_id = p1.parent_adjustment_id
     AND p.line_id                        = p1.line_id
     AND p.header_id                      = p1.header_id
     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 nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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: 242

  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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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   --commented for bug#8879693
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
--bug 11662722     AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
--bug 11662722     AND rl.interface_line_attribute6     = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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))
--TaxER Start
UNION ALL
  SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
    FROM oe_price_adjustments p,oe_price_adjustments p1
       , 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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
     AND p1.applied_flag                   = 'N'  --12895421
     AND p.price_adjustment_id = p1.parent_adjustment_id
     AND p.line_id                        = p1.line_id
     AND p.header_id                      = p1.header_id
     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 nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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: 323

    SELECT SUM (
                 ( NVL( rl.amount, 0 ) )
               +   DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
                    NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ), 0 )  --TaxER
               )
      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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
       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   --commented for bug#8879693
       AND h.transactional_curr_code        = p_curr_code
       AND nvl(rl.interface_status, '~')    <> 'P'
       AND rl.interface_line_context        = 'ORDER ENTRY'
  --bug 11662722     AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
  --bug 11662722     AND rl.interface_line_attribute6     = To_Char(l.line_id) -- To_Char added for Bug 10008003)
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722

AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id  --bug 11662722
       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: 358

  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)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0), 0 )  --TaxER
           )
    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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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   --commented for bug#8879693
     AND h.transactional_curr_code        = p_curr_code
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
--bug 11662722     AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
 --bug 11662722    AND rl.interface_line_attribute6     = To_Char(l.line_id) -- To_Char added for Bug 10008003
  AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722

AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id  --bug 11662722

     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: 394

  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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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   --commented for bug#8879693
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
---bug 11662722     AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
---bug 11662722     AND rl.interface_line_attribute6     = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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))
--TaxER Start
UNION ALL
  SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
    FROM oe_price_adjustments p,oe_price_adjustments p1
       , 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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
     AND p1.applied_flag                   = 'N'  --12895421
     AND p.price_adjustment_id = p1.parent_adjustment_id
     AND p.line_id                        = p1.line_id
     AND p.header_id                      = p1.header_id
     AND NVL(p.invoiced_flag, 'N')        = 'Y'
     AND h.transactional_curr_code        = p_curr_code
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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: 468

  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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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   --commented for bug#8879693
     AND nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
   --bug 11662722  AND rl.interface_line_attribute1     = To_Char(h.order_number) -- To_Char added for Bug 10008003
   --bug 11662722  AND rl.interface_line_attribute6     = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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))
--TaxER Start
UNION ALL
  SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
    FROM oe_price_adjustments p
       ,oe_price_adjustments p1
       , 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(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID)  --6896462 bug 8744491
     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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
     AND p1.applied_flag                   = 'N'  --12895421
     AND p.price_adjustment_id = p1.parent_adjustment_id
     AND p.line_id                        = p1.line_id
     AND p.header_id                      = p1.header_id
     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 nvl(rl.interface_status, '~')    <> 'P'
     AND rl.interface_line_context        = 'ORDER ENTRY'
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number  -- bug 11662722
     AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id  --bug 11662722
     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: 578

       SELECT cas.cust_account_id,su.cust_acct_site_id
         INTO l_customer_id,l_cust_acct_site_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: 637

             OE_DEBUG_PUB.Add('Select cust_glb_orders  ');

Line: 660

             OE_DEBUG_PUB.Add('Select cust_glb_orders_return ');

Line: 684

             OE_DEBUG_PUB.Add('Select cust_glb_orders_freight1  ');

Line: 713

             OE_DEBUG_PUB.Add('Select cust_glb_orders_freight2  ');

Line: 745

             OE_DEBUG_PUB.Add('Select cust_reg_orders  ');

Line: 769

             OE_DEBUG_PUB.Add('Select cust_reg_orders_return  ');

Line: 793

             OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight1  ');

Line: 822

             OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight2  ');