APPS.OKS_HZ_MERGE_PUB SQL Statements

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

Line: 73

  SELECT 1
  FROM oks_billing_profiles_b bpe
  WHERE bpe.dependent_cust_acct_id1 IN (SELECT cme.duplicate_id
                                        FROM ra_customer_merges cme
                                        WHERE cme.process_flag = 'N'
                                          AND cme.request_id   = req_id
                                          AND cme.set_number   = set_number
                                        )
  FOR UPDATE NOWAIT;

Line: 131

        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,ID
              ,DEPENDENT_CUST_ACCT_ID1
              ,OBJECT_VERSION_NUMBER
         FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
         WHERE (
            yt.DEPENDENT_CUST_ACCT_ID1 = m.DUPLICATE_ID
            OR yt.OBJECT_VERSION_NUMBER = m.DUPLICATE_SITE_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_number;

Line: 172

         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID1,
           NUM_COL1_ORIG,
           NUM_COL1_NEW,
           NUM_COL2_ORIG,
           NUM_COL2_NEW,
           ACTION_FLAG,
           REQUEST_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATE_LOGIN,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY
      ) VALUES (         HZ_CUSTOMER_MERGE_LOG_s.nextval,
         'OKS_BILLING_PROFILES_B',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID1_LIST(I),
         NUM_COL1_ORIG_LIST(I),
         NUM_COL1_NEW_LIST(I),
         NUM_COL2_ORIG_LIST(I),
         NUM_COL2_NEW_LIST(I),
         'U',
         req_id,
         hz_utility_pub.CREATED_BY,
         hz_utility_pub.CREATION_DATE,
         hz_utility_pub.LAST_UPDATE_LOGIN,
         hz_utility_pub.LAST_UPDATE_DATE,
         hz_utility_pub.LAST_UPDATED_BY
      );

Line: 206

      UPDATE OKS_BILLING_PROFILES_B yt SET
           DEPENDENT_CUST_ACCT_ID1=NUM_COL1_NEW_LIST(I)
          ,OBJECT_VERSION_NUMBER=NUM_COL2_NEW_LIST(I)
          , LAST_UPDATE_DATE=SYSDATE
          , last_updated_by=arp_standard.profile.user_id
          , last_update_login=arp_standard.profile.last_update_login
      WHERE ID=PRIMARY_KEY_ID1_LIST(I)
         ;

Line: 220

    arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 226

      INSERT INTO HZ_CUSTOMER_MERGE_LOG (
       MERGE_LOG_ID,
       MERGE_HEADER_ID,
       TABLE_NAME,
       PRIMARY_KEY_ID,
       NUM_COL1_ORIG,
       NUM_COL1_NEW,
       NUM_COL2_ORIG,
       NUM_COL2_NEW,
       REQUEST_ID
    ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
             CUSTOMER_MERGE_HEADER_ID,
             'OKS_BILLING_PROFILES_B',
             ID,
             DEPENDENT_CUST_ACCT_ID1,
             decode(yt.DEPENDENT_CUST_ACCT_ID1,m.DUPLICATE_ID,m.CUSTOMER_ID,yt.DEPENDENT_CUST_ACCT_ID1),
             OBJECT_VERSION_NUMBER,
             OBJECT_VERSION_NUMBER,
             request_id
     FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
     WHERE (
          yt.DEPENDENT_CUST_ACCT_ID1 = m.DUPLICATE_ID
--          OR yt.BILL_TO_ADDRESS_ID1 = m.DUPLICATE_SITE_ID
     ) AND    m.process_flag = 'N'
     AND    m.request_id = req_id
     AND    m.set_number = set_number;

Line: 253

     UPDATE OKS_BILLING_PROFILES_B yt SET (
      DEPENDENT_CUST_ACCT_ID1, OBJECT_VERSION_NUMBER) = (
           SELECT NUM_COL1_NEW, NUM_COL2_NEW
           FROM HZ_CUSTOMER_MERGE_LOG l
           WHERE l.REQUEST_ID = req_id
           AND l.TABLE_NAME = 'OKS_BILLING_PROFILES_B'
           AND l.PRIMARY_KEY_ID = ID
           AND DEPENDENT_CUST_ACCT_ID1 = NUM_COL1_ORIG
           and rownum <2
      )
       , LAST_UPDATE_DATE=SYSDATE
       , last_updated_by=arp_standard.profile.user_id
       , last_update_login=arp_standard.profile.last_update_login
      WHERE (ID) in (
         SELECT PRIMARY_KEY_ID
         FROM HZ_CUSTOMER_MERGE_LOG l1, RA_CUSTOMER_MERGES h
         WHERE h.CUSTOMER_MERGE_HEADER_ID = l1.MERGE_HEADER_ID
         AND l1.TABLE_NAME = 'OKS_BILLING_PROFILES_B'
         AND l1.REQUEST_ID = req_id
         AND h.set_number = set_number);

Line: 275

    arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 328

        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,ID
              ,BILL_TO_ADDRESS_ID1
              ,OBJECT_VERSION_NUMBER
         FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
         WHERE (
            yt.BILL_TO_ADDRESS_ID1 = m.DUPLICATE_SITE_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_number;

Line: 342

        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,ID
              ,QUOTE_TO_SITE_ID
              ,OBJECT_VERSION_NUMBER
         FROM OKS_K_HEADERS_B hdr, ra_customer_merges m
         WHERE (
            hdr.QUOTE_TO_SITE_ID = m.DUPLICATE_ADDRESS_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_number;

Line: 381

         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID1,
           NUM_COL3_ORIG,
           NUM_COL3_NEW,
           NUM_COL4_ORIG,
           NUM_COL4_NEW,
           ACTION_FLAG,
           REQUEST_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATE_LOGIN,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY
      ) VALUES (         HZ_CUSTOMER_MERGE_LOG_s.nextval,
         'OKS_BILLING_PROFILES_B',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID1_LIST(I),
         NUM_COL3_ORIG_LIST(I),
         NUM_COL3_NEW_LIST(I),
         NUM_COL4_ORIG_LIST(I),
         NUM_COL4_NEW_LIST(I),
         'U',
         req_id,
         hz_utility_pub.CREATED_BY,
         hz_utility_pub.CREATION_DATE,
         hz_utility_pub.LAST_UPDATE_LOGIN,
         hz_utility_pub.LAST_UPDATE_DATE,
         hz_utility_pub.LAST_UPDATED_BY
      );

Line: 416

      UPDATE OKS_BILLING_PROFILES_B yt SET
           BILL_TO_ADDRESS_ID1=NUM_COL3_NEW_LIST(I)
          ,OBJECT_VERSION_NUMBER=NUM_COL4_NEW_LIST(I)
          , LAST_UPDATE_DATE=SYSDATE
          , last_updated_by=arp_standard.profile.user_id
          , last_update_login=arp_standard.profile.last_update_login
      WHERE ID=PRIMARY_KEY_ID1_LIST(I)
         ;

Line: 431

    arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 458

         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID1,
           NUM_COL1_ORIG,
           NUM_COL1_NEW,
           NUM_COL2_ORIG,
           NUM_COL2_NEW,
           ACTION_FLAG,
           REQUEST_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATE_LOGIN,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY
      ) VALUES (         HZ_CUSTOMER_MERGE_LOG_s.nextval,
         'OKS_K_HEADERS_B',
         MERGE_HEADER_ID_LIST_QUOTE(I),
         PRIMARY_KEY_ID1_LIST_QUOTE(I),
         NUM_COL1_ORIG_LIST_QUOTE(I),
         NUM_COL1_NEW_LIST_QUOTE(I),
         NUM_COL2_ORIG_LIST_QUOTE(I),
         NUM_COL2_NEW_LIST_QUOTE(I),
         'U',
         req_id,
         hz_utility_pub.CREATED_BY,
         hz_utility_pub.CREATION_DATE,
         hz_utility_pub.LAST_UPDATE_LOGIN,
         hz_utility_pub.LAST_UPDATE_DATE,
         hz_utility_pub.LAST_UPDATED_BY
      );

Line: 494

      UPDATE OKS_K_HEADERS_B yt SET
           QUOTE_TO_SITE_ID=NUM_COL1_NEW_LIST_QUOTE(I)
          ,OBJECT_VERSION_NUMBER=NUM_COL2_NEW_LIST_QUOTE(I)
          , LAST_UPDATE_DATE=SYSDATE
          , last_updated_by=arp_standard.profile.user_id
          , last_update_login=arp_standard.profile.last_update_login
      WHERE ID=PRIMARY_KEY_ID1_LIST_QUOTE(I);

Line: 503

    arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 533

  SELECT cmh.merge_reason_code
  FROM ra_customer_merge_headers cmh
      ,ra_customer_merges cme
  WHERE cmh.customer_merge_header_id = cme.customer_merge_header_id
    AND cme.request_id               = req_id
    AND cme.set_number               = set_number
    AND cme.process_flag             = 'N'
  ;

Line: 547

  SELECT customer_id, duplicate_id
  FROM ra_customer_merges cme
  WHERE cme.request_id   = req_id
    AND cme.set_number   = set_number
    AND cme.process_flag = 'N'
  ;

Line: 558

  SELECT party_id
  FROM hz_cust_accounts
  WHERE cust_account_id = b_account_id
;

Line: 567

  SELECT 1
  FROM oks_billing_profiles_b bpe
  WHERE bpe.owned_party_id1 = b_party_id
  ;