APPS.OE_CREDIT_CHECK_ORDER_PVT SQL Statements

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

Line: 114

    SELECT  ohr.HOLD_RELEASE_ID
    FROM OE_ORDER_HOLDS h,
	 OE_HOLD_SOURCES_ALL s,
	 oe_hold_releases ohr
     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'
     AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
   ORDER BY ohr.creation_date DESC;

Line: 156

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

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

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

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

Line: 637

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

Line: 640

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

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

Line: 657

  	OE_DEBUG_PUB.ADD('OEXVCRHB: Call OE_CREDIT_CHECK_UTIL.Update_Credit_Profile_Level');

Line: 661

	  OE_DEBUG_PUB.ADD('OEXVCRHB: calling Update_Credit_Profile_Level');

Line: 662

	  OE_CREDIT_CHECK_UTIL.Update_Credit_Profile_Level(p_hold_source_rec);

Line: 667

	OE_DEBUG_PUB.ADD('OEXVCRHB: Out OE_CREDIT_CHECK_UTIL.Update_Credit_Profile_Level');

Line: 679

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

Line: 692

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

Line: 698

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

Line: 702

      ,   'Update_Comments_And_Commit'
      );

Line: 707

END Update_Comments_And_Commit ;

Line: 717

** 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: 801

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

Line: 806

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

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

Line: 820

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

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

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

Line: 842

        OE_DEBUG_PUB.ADD('OEXVCRHB: Call OE_CREDIT_CHECK_UTIL.Update_Credit_Profile_Level');

Line: 848

        OE_DEBUG_PUB.ADD('OEXVCRHB: calling Update_Credit_Profile_Level');

Line: 849

        OE_CREDIT_CHECK_UTIL.Update_Credit_Profile_Level(l_hold_source_rec);

Line: 856

        OE_DEBUG_PUB.ADD('OEXVCRHB: Out OE_CREDIT_CHECK_UTIL.Update_Credit_Profile_Level');

Line: 869

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

Line: 927

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

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

  l_conversion_status.DELETE ;

Line: 2391

    SELECT Credit_Profile_Level
    FROM OE_ORDER_HOLDS_all h,
      OE_HOLD_SOURCES_ALL s,
      oe_hold_releases ohr
    WHERE H.HOLD_SOURCE_ID  = S.HOLD_SOURCE_ID
    AND H.HEADER_ID         = p_header_rec.header_id
    AND H.LINE_ID          IS NULL
    AND H.HOLD_RELEASE_ID  IS NOT NULL
    AND S.HOLD_ID           = 1
    AND S.HOLD_ENTITY_CODE  = 'O'
    AND S.HOLD_ENTITY_ID    = p_header_rec.header_id
    AND S.RELEASED_FLAG     ='Y'
    AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
    ORDER BY ohr.creation_date DESC;

Line: 2490

      SELECT acct_site.cust_account_id
      INTO l_new_cust_account_id
      FROM HZ_CUST_SITE_USES_ALL SITE,
        HZ_PARTY_SITES PARTY_SITE,
        HZ_CUST_ACCT_SITES ACCT_SITE
      WHERE SITE.SITE_USE_ID      = OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).new_invoice_to_org_id
      AND SITE.SITE_USE_CODE      = 'BILL_TO'
      AND SITE.CUST_ACCT_SITE_ID  = ACCT_SITE.CUST_ACCT_SITE_ID
      AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
      AND SITE.ORG_ID             = ACCT_SITE.ORG_ID;

Line: 2501

      SELECT acct_site.cust_account_id
      INTO l_old_cust_account_id
      FROM HZ_CUST_SITE_USES_ALL SITE,
        HZ_PARTY_SITES PARTY_SITE,
        HZ_CUST_ACCT_SITES ACCT_SITE
      WHERE SITE.SITE_USE_ID      = OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).old_invoice_to_org_id
      AND SITE.SITE_USE_CODE      = 'BILL_TO'
      AND SITE.CUST_ACCT_SITE_ID  = ACCT_SITE.CUST_ACCT_SITE_ID
      AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
      AND SITE.ORG_ID             = ACCT_SITE.ORG_ID;

Line: 2536

  OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete;

Line: 3183

    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)