APPS.OE_CREDIT_CHECK_LINES_PVT SQL Statements

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

Line: 146

PROCEDURE Update_Comments_And_Commit
  ( p_hold_source_rec  IN         OE_HOLDS_PVT.Hold_Source_Rec_Type
  , x_msg_count        OUT NOCOPY NUMBER
  , x_msg_data         OUT NOCOPY VARCHAR2
  , x_return_status    OUT NOCOPY VARCHAR2
  )
IS
  PRAGMA AUTONOMOUS_TRANSACTION;

Line: 158

    OE_DEBUG_PUB.ADD('OEXVCRLB: Entering Update_Comments_And_Commit');

Line: 159

    OE_DEBUG_PUB.ADD('OEXVCRLB: Before OE_Holds_PUB.Update_Hold_Comments');

Line: 162

  OE_Holds_PUB.Update_Hold_comments
      (   p_hold_source_rec   => p_hold_source_rec
      ,   x_msg_count         => x_msg_count
      ,   x_msg_data          => x_msg_data
      ,   x_return_status     => x_return_status
      );

Line: 171

    OE_DEBUG_PUB.ADD('OEXVCRLB: After OE_Holds_PUB.Update_Hold_Comments Status '
                     || x_return_status);

Line: 182

      OE_DEBUG_PUB.ADD('OEXVCRLB: Update Hold Comment Success, Issue COMMIT');

Line: 195

    OE_DEBUG_PUB.ADD(' OEXVCRLB: Exiting Update_Comments_And_Commit');

Line: 201

   OE_DEBUG_PUB.ADD('OEXVCRLB: Error in Update_Comments_And_Commit' );

Line: 205

      ,   'Update_Comments_And_Commit'
      );

Line: 210

END Update_Comments_And_Commit ;

Line: 230

    SELECT /* MOAC_SQL_NO_CHANGE */ l.line_id, l.line_number,0 line_total --ER 6135714
    FROM   oe_order_lines_all l,
           oe_order_headers_all h,
           ra_terms_b t
    WHERE  l.invoice_to_org_id = p_site_use_id
    AND    l.header_id         = p_header_id
    AND    h.header_id         = l.header_id
    AND    l.open_flag         = 'Y'
    AND    l.booked_flag       = 'Y'
    AND    NVL(l.invoiced_quantity,0) = 0
    AND    NVL(l.shipped_quantity,0) = 0
    AND    l.line_category_code  = 'ORDER'
    AND    l.payment_term_id   = t.term_id
    AND    t.credit_check_flag = 'Y'
    AND    (l.ato_line_id IS NULL OR l.ato_line_id = 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)
           )
    ORDER BY l.line_id;

Line: 260

     SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
      + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from  oe_order_lines_all l2 where
                 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
              )line_total
     FROM   oe_order_lines_all l,
            oe_order_headers_all h,
            ra_terms_b t
     WHERE  l.invoice_to_org_id = p_site_use_id
     AND    l.header_id         = p_header_id
     AND    h.header_id         = l.header_id
     AND    l.open_flag         = 'Y'
     AND    l.booked_flag       = 'Y'
     AND    NVL(l.invoiced_quantity,0) = 0
     AND    NVL(l.shipped_quantity,0) = 0
     AND    l.line_category_code  = 'ORDER'
     AND    l.payment_term_id   = t.term_id
     AND    t.credit_check_flag = 'Y'
     AND    (l.ato_line_id IS NULL OR l.ato_line_id = 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)
            )
     ORDER BY nvl(l.SCHEDULE_SHIP_DATE, l.REQUEST_DATE) , l.SHIPMENT_PRIORITY_CODE, l.line_id;

Line: 292

     SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
      + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from  oe_order_lines_all l2 where
                 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
              ) line_total
     FROM   oe_order_lines_all l,
            oe_order_headers_all h,
            ra_terms_b t
     WHERE  l.invoice_to_org_id = p_site_use_id
     AND    l.header_id         = p_header_id
     AND    h.header_id         = l.header_id
     AND    l.open_flag         = 'Y'
     AND    l.booked_flag       = 'Y'
     AND    NVL(l.invoiced_quantity,0) = 0
     AND    NVL(l.shipped_quantity,0) = 0
     AND    l.line_category_code  = 'ORDER'
     AND    l.payment_term_id   = t.term_id
     AND    t.credit_check_flag = 'Y'
     AND    (l.ato_line_id IS NULL OR l.ato_line_id = 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)
            )
     ORDER BY l.SHIPMENT_PRIORITY_CODE,nvl(l.SCHEDULE_SHIP_DATE, l.REQUEST_DATE) , l.line_id;

Line: 323

     SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
      + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from  oe_order_lines_all l2 where
                 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
              ) line_total
     FROM   oe_order_lines_all l,
            oe_order_headers_all h,
            ra_terms_b t
     WHERE  l.invoice_to_org_id = p_site_use_id
     AND    l.header_id         = p_header_id
     AND    h.header_id         = l.header_id
     AND    l.open_flag         = 'Y'
     AND    l.booked_flag       = 'Y'
     AND    NVL(l.invoiced_quantity,0) = 0
     AND    NVL(l.shipped_quantity,0) = 0
     AND    l.line_category_code  = 'ORDER'
     AND    l.payment_term_id   = t.term_id
     AND    t.credit_check_flag = 'Y'
     AND    (l.ato_line_id IS NULL OR l.ato_line_id = 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)
            )
     ORDER BY line_total asc;

Line: 354

     SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
      + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from  oe_order_lines_all l2 where
                 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
              ) line_total
     FROM   oe_order_lines_all l,
            oe_order_headers_all h,
            ra_terms_b t
     WHERE  l.invoice_to_org_id = p_site_use_id
     AND    l.header_id         = p_header_id
     AND    h.header_id         = l.header_id
     AND    l.open_flag         = 'Y'
     AND    l.booked_flag       = 'Y'
     AND    NVL(l.invoiced_quantity,0) = 0
     AND    NVL(l.shipped_quantity,0) = 0
     AND    l.line_category_code  = 'ORDER'
     AND    l.payment_term_id   = t.term_id
     AND    t.credit_check_flag = 'Y'
     AND    (l.ato_line_id IS NULL OR l.ato_line_id = 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)
            )
     ORDER BY line_total desc;

Line: 462

PROCEDURE Update_Holds_Table
  (  p_holds_table      IN OUT NOCOPY Line_Holds_Tbl_Rectype
   , p_line_id          IN NUMBER    DEFAULT NULL
   , p_hold             IN VARCHAR2  DEFAULT NULL
   , p_cc_limit_used    IN VARCHAR2  DEFAULT NULL
   , p_cc_profile_used  IN VARCHAR2  DEFAULT NULL
   , p_customer_id      IN NUMBER    DEFAULT NULL
   , p_site_use_id      IN NUMBER    DEFAULT NULL
   , p_party_id         IN NUMBER    DEFAULT NULL
   , p_item_category_id IN NUMBER    DEFAULT NULL
   , x_return_status   OUT NOCOPY VARCHAR2
  )
IS
BEGIN
  IF G_debug_flag = 'Y'
  THEN
    OE_DEBUG_PUB.Add('OEXVCRLB: In Update_Holds_Table');

Line: 549

    OE_DEBUG_PUB.Add('OEXVCRLB: Out Update_Holds_Table');

Line: 556

        OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Holds_Table');

Line: 559

END Update_Holds_Table;

Line: 568

 PROCEDURE Update_Holds_Table
   (  p_holds_table      IN OUT NOCOPY Line_Holds_Tbl_Rectype
    , p_hold             IN VARCHAR2  DEFAULT NULL
    , p_cc_limit_used    IN VARCHAR2  DEFAULT NULL
    , p_cc_profile_used  IN VARCHAR2  DEFAULT NULL
    , p_customer_id      IN NUMBER    DEFAULT NULL
    , p_site_use_id      IN NUMBER    DEFAULT NULL
    , p_party_id         IN NUMBER    DEFAULT NULL
    , p_exposure         IN NUMBER
    , p_overall_credit_limit         IN NUMBER
   )
 IS
 l_amt_on_hold number := 0;

Line: 586

     OE_DEBUG_PUB.Add('OEXVCRLB: In Update_Holds_Table Overloaded');

Line: 622

     OE_DEBUG_PUB.Add('OEXVCRLB: Out Update_Holds_Table');

Line: 627

         OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Holds_Table overloaded');

Line: 630

 END Update_Holds_Table;

Line: 676

        SELECT /* MOAC_SQL_CHANGE */ NVL(MAX(H.HOLD_RELEASE_ID),0)
        INTO   l_hold_release_id
        FROM OE_ORDER_HOLDS h,
             OE_HOLD_SOURCES_ALL s
         WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
         AND H.HEADER_ID = p_header_id
         AND H.LINE_ID IS NULL
         AND H.HOLD_RELEASE_ID IS NOT NULL
         AND S.HOLD_ID = p_hold_id
         AND S.HOLD_ENTITY_CODE = 'O'
         AND S.HOLD_ENTITY_ID = p_header_id
         AND S.RELEASED_FLAG ='Y';

Line: 689

        SELECT /* MOAC_SQL_CHANGE */ NVL(MAX(H.HOLD_RELEASE_ID),0)
        INTO l_hold_release_id
        FROM OE_ORDER_HOLDS h,
             OE_HOLD_SOURCES_ALL s
        WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
         AND H.HEADER_ID = p_header_id
         AND H.LINE_ID = p_line_id
         AND H.HOLD_RELEASE_ID IS NOT NULL
         AND S.HOLD_ID = p_hold_id
         AND S.HOLD_ENTITY_CODE = 'O'
         AND S.HOLD_ENTITY_ID = p_header_id
         AND S.RELEASED_FLAG ='Y';

Line: 714

         SELECT
           'Y'
         , CREATION_DATE    -----added
         INTO
           l_manual_hold_exists
         , l_release_date
         FROM OE_HOLD_RELEASES
         WHERE HOLD_RELEASE_ID = l_hold_release_id
           AND RELEASE_REASON_CODE <> 'PASS_CREDIT'
           AND CREATED_BY <> 1;

Line: 1295

** Introduced new procedure Update_Comments_And_Commit to
** Update and Commit Hold Comments. Apply_Holds_And_Commit
** And Update_Comments_And_Commit are now called whenever
** Calling Action is Picking, Packing or Shipping ELSE
** Apply_Holds and Update_Hold_Comments are called.
*/

PROCEDURE Apply_Line_CC_Hold
 (  p_header_id            IN NUMBER
  , p_order_number         IN NUMBER
  , p_line_id              IN NUMBER
  , p_line_number          IN NUMBER
  , p_calling_action       IN VARCHAR2   DEFAULT 'BOOKING'
  , p_cc_limit_used        IN VARCHAR2
  , p_cc_profile_used      IN VARCHAR2
  , p_party_id             IN NUMBER     DEFAULT NULL
  , p_customer_id          IN NUMBER     DEFAULT NULL
  , p_site_use_id          IN NUMBER     DEFAULT NULL
  , p_item_category_id     IN NUMBER     DEFAULT NULL
  , p_credit_hold_level    IN VARCHAR2
  , p_credit_check_rule_rec IN
                   OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
  , x_cc_result_out        OUT NOCOPY VARCHAR2
  )
IS

  -- Cursor to select the customer name
  CURSOR customer_name_csr IS
    SELECT name
    FROM   oe_sold_to_orgs_v
    WHERE  customer_id = p_customer_id;

Line: 1328

    SELECT party_name
    FROM   hz_parties
    WHERE  party_id = p_party_id  ;

Line: 1334

    SELECT location
    FROM   hz_cust_site_uses
    WHERE  site_use_id = p_site_use_id;

Line: 1339

    SELECT description
    FROM   mtl_categories
    WHERE  category_id = p_item_category_id;

Line: 1434

        OE_DEBUG_PUB.ADD('OEXVCRLB: Call Update_Comments_And_Commit');

Line: 1439

      Update_Comments_And_Commit
      (   p_hold_source_rec   => l_hold_source_rec
      ,   x_msg_count         => l_msg_count
      ,   x_msg_data          => l_msg_data
      ,   x_return_status     => l_return_status
      );

Line: 1449

        OE_DEBUG_PUB.ADD('OEXVCRLB: Out Update_Comments_And_Commit');

Line: 1452

    ELSIF  NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
    THEN
      IF G_debug_flag = 'Y'
      THEN
        OE_DEBUG_PUB.ADD('OEXVCRLB: Call OE_Holds_PUB.Update_Hold_Comments directly');

Line: 1461

      OE_Holds_PUB.Update_Hold_comments
      (   p_hold_source_rec   => l_hold_source_rec
      ,   x_msg_count         => l_msg_count
      ,   x_msg_data          => l_msg_data
      ,   x_return_status     => l_return_status
      );

Line: 1472

        OE_DEBUG_PUB.ADD('OEXVCRLB: Out OE_Holds_PUB.Update_Hold_Comments directly');

Line: 1483

             ('OEXVCRLB: Updated Comments on Header/Line ID:' ||
                      p_header_id || '/' ||
                      p_line_id, 1);

Line: 1539

       ELSIF  NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
       THEN
         IF G_debug_flag = 'Y'
         THEN
           OE_DEBUG_PUB.ADD('OEXVCRLB: Call OE_Holds_PUB.Apply_Holds directly');

Line: 1622

    SELECT description
    FROM   mtl_categories
    WHERE  category_id = p_item_category_id;

Line: 1892

      Update_Holds_Table
       (  p_holds_table         => p_holds_table
        , p_line_id             => p_lines(i).line_id
        , p_hold                => 'ITEM'
        , p_cc_limit_used       => 'ITEM'
        , p_cc_profile_used     => 'CATEGORY'
        , p_item_category_id    => p_item_category_id
        , x_return_status       => l_return_status
       );

Line: 1942

  Update_Holds_Table
    (  p_holds_table         => p_holds_table
     , p_hold                => 'OTHER'
     , p_cc_limit_used       => p_cc_limit_used
     , p_cc_profile_used     => p_cc_profile_used
     , p_customer_id         => p_customer_id
     , p_site_use_id         => p_site_use_id
     , p_party_id            => p_party_id
     , x_return_status       => l_return_status
    );

Line: 3555

         Update_Holds_Table
         (  p_holds_table         => p_holds_table
         , p_hold                => 'OTHER'
         , p_cc_limit_used       => l_cc_limit_used
         , p_cc_profile_used     => l_cc_profile_used
         , p_customer_id         => p_customer_id
         , p_site_use_id         => p_site_use_id
         , p_party_id            => l_credit_limit_entity_id
         , p_exposure	         =>   l_total_exposure
         , p_overall_credit_limit => l_overall_credit_limit
        );

Line: 3824

  SELECT  DISTINCT
          ool.invoice_to_org_id site_use_id
  FROM    oe_order_lines_all ool
  WHERE    ool.header_id	        = p_header_rec.header_id
  AND      ool.open_flag                = 'Y'
  AND      NVL(ool.invoiced_quantity,0) = 0
  AND      NVL(ool.shipped_quantity,0) = 0
  ORDER BY  1 ;

Line: 3839

  SELECT l.line_id, l.line_number
  FROM   oe_order_headers_all h,
         oe_order_lines_all l,
         ra_terms t
  WHERE  h.header_id = p_header_rec.header_id
  AND    h.header_id = l.header_id
  AND    l.payment_term_id = t.term_id
  AND    nvl(t.credit_check_flag, 'N') = 'N'
  AND    (EXISTS
           (SELECT 'Y'
           FROM   oe_payment_types_all pt
           WHERE  NVL(l.payment_type_code, 'N') = pt.payment_type_code
           AND pt.credit_check_flag = 'N'
           )
          OR l.payment_type_code IS NULL
          )
  AND    (EXISTS
         (SELECT 'Y'
         FROM   oe_order_holds_all oh,
                oe_hold_sources_all hs
         WHERE  oh.header_id = p_header_rec.header_id
         AND    oh.line_id = l.line_id
         AND    oh.hold_release_id IS NULL
         AND    oh.hold_source_id = hs.hold_source_id
         AND    hs.hold_id = 1
         ));

Line: 3961

      SELECT /* MOAC_SQL_CHANGE */ cas.cust_account_id
           , ca.party_id             --------------new (FPI)
      INTO   l_own_customer_id
           , l_party_id              --------------new (FPI)
      FROM   HZ_cust_acct_sites_all cas
             , HZ_cust_site_uses su
             , hz_cust_accounts_all ca    --------------new (FPI)
      WHERE  su.site_use_id = c_site.site_use_id
        AND  cas.cust_acct_site_id = su.cust_acct_site_id
        AND  cas.cust_account_id=ca.cust_account_id; ---------new (FPI)