APPS.OE_CREDIT_CHECK_ORDER_PVT SQL Statements

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

Line: 132

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

         SELECT
           'Y',CREATION_DATE
         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: 578

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

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

Line: 591

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

Line: 594

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

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

Line: 614

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

Line: 627

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

Line: 633

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

Line: 637

      ,   'Update_Comments_And_Commit'
      );

Line: 642

END Update_Comments_And_Commit ;

Line: 652

** 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_Order_CC_Hold
 (  p_header_id            IN NUMBER
  , p_order_number         IN NUMBER
  , p_calling_action       IN VARCHAR2   DEFAULT 'BOOKING'
  , p_cc_limit_used        IN VARCHAR2
  , p_cc_profile_used      IN VARCHAR2
  , p_item_category_id     IN NUMBER
  , p_system_parameter_rec IN OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
  , p_credit_check_rule_rec IN
                  OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
  , x_cc_hold_comment      OUT NOCOPY VARCHAR2
  , x_cc_result_out        OUT NOCOPY VARCHAR2
  )
IS

  -- Cursor to select the category description
  CURSOR item_category_csr IS
    SELECT description
    FROM   mtl_categories
    WHERE  category_id = p_item_category_id;

Line: 734

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

Line: 737

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

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

Line: 749

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

Line: 756

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

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

Line: 776

        OE_DEBUG_PUB.ADD('OEXVCRHB: Updated Comments on Header ID:'
                     ||p_header_id, 1);

Line: 828

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

Line: 1319

    SELECT /* MOAC_SQL_NO_CHANGE */ COUNT(line_id)
    INTO   l_count
    FROM   OE_ORDER_LINES_ALL L
    ,      OE_ORDER_HEADERS_ALL H
    ,      RA_TERMS_B T
    WHERE  h.HEADER_ID = p_header_id
    AND    L.HEADER_ID = H.HEADER_ID
    AND    T.TERM_ID   = L.PAYMENT_TERM_ID
    AND    NVL(T.CREDIT_CHECK_FLAG,'N') = 'Y'  --bug4888346
    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(l.org_id,-99))
            OR
            (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));

Line: 1651

  l_conversion_status.DELETE ;

Line: 2936

    SELECT  /* MOAC_SQL_CHANGE */ cas.cust_account_id
            , su.site_use_id
            , ca.party_id      --------------------new (FPI)
    INTO    l_own_customer_id
          , l_order_site_use_id
          , l_party_id            -------------------new (FPI)
    FROM    HZ_CUST_SITE_USES_ALL su
          , HZ_CUST_ACCT_SITES_all cas
          , hz_cust_accounts_all ca    --------------new (FPI)
    WHERE   su.site_use_id = p_header_rec.invoice_to_org_id
           AND cas.CUST_ACCT_SITE_ID  = su.CUST_ACCT_SITE_ID
           AND cas.cust_account_id=ca.cust_account_id; ---------new (FPI)