eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTAP_CMERGE SQL Statements

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

Line: 43


        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,BOOKING_DEAL_ID
              ,yt.CUSTOMER_ID
         FROM OTA_BOOKING_DEALS yt, ra_customer_merges m
         WHERE (
            yt.CUSTOMER_ID = m.DUPLICATE_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_num;

Line: 81


         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID,
           NUM_COL1_ORIG,
           NUM_COL1_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,
         'OTA_BOOKING_DEALS',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID_LIST(I),
         NUM_COL1_ORIG_LIST(I),
         NUM_COL1_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: 111


      UPDATE OTA_BOOKING_DEALS yt SET
           CUSTOMER_ID=NUM_COL1_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 BOOKING_DEAL_ID=PRIMARY_KEY_ID_LIST(I)
         ;

Line: 124


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

Line: 184


        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,BOOKING_ID
              ,yt.CUSTOMER_ID
              ,THIRD_PARTY_CUSTOMER_ID
              ,CONTACT_ADDRESS_ID
              ,THIRD_PARTY_ADDRESS_ID
         FROM OTA_DELEGATE_BOOKINGS yt, ra_customer_merges m
         WHERE (
            yt.CUSTOMER_ID = m.DUPLICATE_ID
            OR yt.THIRD_PARTY_CUSTOMER_ID = m.DUPLICATE_ID
            OR yt.CONTACT_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
            OR yt.THIRD_PARTY_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_num;

Line: 237


         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID,
           NUM_COL1_ORIG,
           NUM_COL1_NEW,
           NUM_COL2_ORIG,
           NUM_COL2_NEW,
           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,
         'OTA_DELEGATE_BOOKINGS',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID_LIST(I),
         NUM_COL1_ORIG_LIST(I),
         NUM_COL1_NEW_LIST(I),
         NUM_COL2_ORIG_LIST(I),
         NUM_COL2_NEW_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: 279


      UPDATE OTA_DELEGATE_BOOKINGS yt SET
           CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
          ,THIRD_PARTY_CUSTOMER_ID=NUM_COL2_NEW_LIST(I)
          ,CONTACT_ADDRESS_ID=NUM_COL3_NEW_LIST(I)
          ,THIRD_PARTY_ADDRESS_ID=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 BOOKING_ID=PRIMARY_KEY_ID_LIST(I)
         ;

Line: 295


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

Line: 350


        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,FINANCE_HEADER_ID
              ,yt.CUSTOMER_ID
              ,yt.ADDRESS_ID
              ,yt.INVOICE_ADDRESS
         FROM OTA_FINANCE_HEADERS yt, ra_customer_merges m
         WHERE (
            yt.CUSTOMER_ID = m.DUPLICATE_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_num;

Line: 364


  SELECT DISTINCT LOC.ADDRESS1||DECODE(LOC.ADDRESS1,NULL,'',', ')||
      LOC.ADDRESS2||DECODE(LOC.ADDRESS2,NULL,'',', ')||
      LOC.ADDRESS3|| DECODE(LOC.ADDRESS3,NULL,'',', ')||
      LOC.ADDRESS4||DECODE(LOC.ADDRESS4,NULL,'',', ')||
      LOC.CITY||DECODE(LOC.CITY,NULL, '',', ')||
      LOC.STATE||DECODE(LOC.STATE,NULL,'',', ')||
      LOC.PROVINCE||DECODE(LOC.PROVINCE,NULL,'',', ')||
      LOC.COUNTY||DECODE(LOC.COUNTY,NULL,'',', ')||
      LOC.POSTAL_CODE||DECODE(LOC.POSTAL_CODE,NULL,'',', ')||
      LOC.COUNTRY ADDRESS
FROM
      HZ_PARTY_SITES PARTY_SITE,
      HZ_LOCATIONS LOC,
      HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE
      LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
  AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
  AND ACCT_SITE.STATUS = 'A'
  AND ACCT_SITE.CUST_ACCT_SITE_ID = NEW_CUST_ACCT_SITE_ID;

Line: 427


         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID,
           NUM_COL1_ORIG,
           NUM_COL1_NEW,
           --address bug
           NUM_COL2_ORIG,
           NUM_COL2_NEW,
           VCHAR_COL1_ORIG,
           VCHAR_COL1_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,
         'OTA_FINANCE_HEADERS',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID_LIST(I),
         NUM_COL1_ORIG_LIST(I),
         NUM_COL1_NEW_LIST(I),
         -- Bug 3590109 Address Merge
         NUM_COL2_ORIG_LIST(I),
         NUM_COL2_NEW_LIST(I),
         VCHAR_COL1_ORIG_LIST(I),
         VCHAR_COL1_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: 467


      UPDATE OTA_FINANCE_HEADERS yt SET
           CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
          -- Bug 3590109 Address Merge
          , ADDRESS_ID=NUM_COL2_NEW_LIST(I)
          , INVOICE_ADDRESS=VCHAR_COL1_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 FINANCE_HEADER_ID=PRIMARY_KEY_ID_LIST(I)
         ;

Line: 483


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

Line: 531


        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,EVENT_ASSOCIATION_ID
              ,yt.CUSTOMER_ID
              ,EVENT_ID
         FROM OTA_EVENT_ASSOCIATIONS yt, ra_customer_merges m
         WHERE (
            yt.CUSTOMER_ID = m.DUPLICATE_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_num;

Line: 544


        SELECT NULL
         FROM OTA_EVENT_ASSOCIATIONS tea
         WHERE    tea.event_id = event_id
              AND tea.CUSTOMER_ID = TO_CUSTOMER;

Line: 550


    select *
    from ota_event_associations
    where EVENT_ASSOCIATION_ID = P_EVENT_ASSOCIATION_ID;

Line: 599


            INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID1,
           NUM_COL1_ORIG,
           NUM_COL1_NEW,
           ACTION_FLAG,
           REQUEST_ID,
           -- tea cols
           DEL_COL1,
           DEL_COL2,
           DEL_COL3,
           DEL_COL4,
           DEL_COL5,
           DEL_COL6,
           DEL_COL7,
           DEL_COL8,
           DEL_COL9,
           DEL_COL10,
           DEL_COL11,
           DEL_COL12,
           DEL_COL13,
           DEL_COL14,
           DEL_COL15,
           DEL_COL16,
           DEL_COL17,
           DEL_COL18,
           DEL_COL19,
           DEL_COL20,
           DEL_COL21,
           DEL_COL22,
           DEL_COL23,
           DEL_COL24,
           DEL_COL25,
           DEL_COL26,
           DEL_COL27,
           DEL_COL28,
           DEL_COL29,
           DEL_COL30,
           DEL_COL31,
           DEL_COL32,
           DEL_COL33,
           DEL_COL34,
           DEL_COL35,
           DEL_COL36,
           DEL_COL37,
           DEL_COL38,
           DEL_COL39,
           DEL_COL40,
           DEL_COL41,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATE_LOGIN,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY
          ) VALUES (         HZ_CUSTOMER_MERGE_LOG_s.nextval
         , 'OTA_EVENT_ASSOCIATIONS'
         , MERGE_HEADER_ID_LIST(I)
         , PRIMARY_KEY_ID1_LIST(I)
         , NUM_COL1_ORIG_LIST(I)
         , NUM_COL1_NEW_LIST(I)
         , 'D'
         , req_id
         --tea cols
        , l_evt_assoc_rec.EVENT_ASSOCIATION_ID
        , l_evt_assoc_rec.event_id
        , l_evt_assoc_rec.JOB_ID
        , l_evt_assoc_rec.POSITION_ID
        , l_evt_assoc_rec.CUSTOMER_ID
        , l_evt_assoc_rec.COMMENTS
        , l_evt_assoc_rec.LAST_UPDATE_DATE
        , l_evt_assoc_rec.LAST_UPDATED_BY
        , l_evt_assoc_rec.LAST_UPDATE_LOGIN
        , l_evt_assoc_rec.CREATED_BY
        , l_evt_assoc_rec.CREATION_DATE
        , l_evt_assoc_rec.TEA_INFORMATION_CATEGORY
        , l_evt_assoc_rec.TEA_INFORMATION1
        , l_evt_assoc_rec.TEA_INFORMATION2
        , l_evt_assoc_rec.TEA_INFORMATION3
        , l_evt_assoc_rec.TEA_INFORMATION4
        , l_evt_assoc_rec.TEA_INFORMATION5
        , l_evt_assoc_rec.TEA_INFORMATION6
        , l_evt_assoc_rec.TEA_INFORMATION7
        , l_evt_assoc_rec.TEA_INFORMATION8
        , l_evt_assoc_rec.TEA_INFORMATION9
        , l_evt_assoc_rec.TEA_INFORMATION10
        , l_evt_assoc_rec.TEA_INFORMATION11
        , l_evt_assoc_rec.TEA_INFORMATION12
        , l_evt_assoc_rec.TEA_INFORMATION13
        , l_evt_assoc_rec.TEA_INFORMATION14
        , l_evt_assoc_rec.TEA_INFORMATION15
        , l_evt_assoc_rec.TEA_INFORMATION16
        , l_evt_assoc_rec.TEA_INFORMATION17
        , l_evt_assoc_rec.TEA_INFORMATION18
        , l_evt_assoc_rec.TEA_INFORMATION19
        , l_evt_assoc_rec.TEA_INFORMATION20
        , l_evt_assoc_rec.CATEGORY_USAGE_ID
        , l_evt_assoc_rec.ACTIVITY_VERSION_ID
        , l_evt_assoc_rec.OFFERING_ID
        , l_evt_assoc_rec.SELF_ENROLLMENT_FLAG
        , l_evt_assoc_rec.MATCH_TYPE
        , l_evt_assoc_rec.PERSON_ID
        , l_evt_assoc_rec.PARTY_ID
        , l_evt_assoc_rec.LEARNING_PATH_ID
        , l_evt_assoc_rec.ORGANIZATION_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: 712


           DELETE OTA_EVENT_ASSOCIATIONS
           WHERE EVENT_ASSOCIATION_ID=PRIMARY_KEY_ID1_LIST(I)
         ;

Line: 717


           NUM_COL1_ORIG_LIST.DELETE(I);

Line: 718


           NUM_COL1_NEW_LIST.DELETE(I);

Line: 719


           PRIMARY_KEY_ID1_LIST.DELETE(I);

Line: 720


           MERGE_HEADER_ID_LIST.DELETE(I);

Line: 725


         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID1,
           NUM_COL1_ORIG,
           NUM_COL1_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,
         'OTA_EVENT_ASSOCIATIONS',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID1_LIST(I),
         NUM_COL1_ORIG_LIST(I),
         NUM_COL1_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: 764


           DELETE OTA_EVENT_ASSOCIATIONS
           WHERE EVENT_ASSOCIATION_ID=PRIMARY_KEY_ID1_LIST(I)
             ;

Line: 769


           NUM_COL1_ORIG_LIST.DELETE(I);

Line: 770


           NUM_COL1_NEW_LIST.DELETE(I);

Line: 771


           PRIMARY_KEY_ID1_LIST.DELETE(I);

Line: 772


           MERGE_HEADER_ID_LIST.DELETE(I);

Line: 779


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

Line: 786


      UPDATE OTA_EVENT_ASSOCIATIONS yt SET
           CUSTOMER_ID=NUM_COL1_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 EVENT_ASSOCIATION_ID=PRIMARY_KEY_ID1_LIST(I)
         ;

Line: 801


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

Line: 843


        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,NOTA_HISTORY_ID
              ,yt.CUSTOMER_ID
         FROM OTA_NOTRNG_HISTORIES yt, ra_customer_merges m
         WHERE (
            yt.CUSTOMER_ID = m.DUPLICATE_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_num;

Line: 881


         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID1,
           NUM_COL1_ORIG,
           NUM_COL1_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,
         'OTA_NOTRNG_HISTORIES',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID1_LIST(I),
         NUM_COL1_ORIG_LIST(I),
         NUM_COL1_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: 911


      UPDATE OTA_NOTRNG_HISTORIES yt SET
           CUSTOMER_ID=NUM_COL1_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 NOTA_HISTORY_ID=PRIMARY_KEY_ID1_LIST(I)
         ;

Line: 924


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