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.HZ_IMP_LOAD_STAGE2 SQL Statements

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

Line: 17


  update hz_imp_batch_summary
  set IMPORT_STATUS = 'COMPL_ERROR_LIMIT'
  where BATCH_ID = P_BATCH_ID;

Line: 21


  UPDATE hz_imp_batch_details
  SET import_status = 'COMPL_ERROR_LIMIT'
  WHERE batch_id = P_BATCH_ID
  AND run_number = (SELECT max(run_number)
    		      FROM hz_imp_batch_details
    		      WHERE batch_id = P_BATCH_ID);

Line: 48


  update hz_imp_batch_summary
  set IMPORT_STATUS = 'ACTION_REQUIRED'
  where BATCH_ID = P_BATCH_ID;

Line: 52


  UPDATE hz_imp_batch_details
  SET import_status = 'ACTION_REQUIRED'
  WHERE batch_id = P_BATCH_ID
  AND run_number = (SELECT max(run_number)
    		      FROM hz_imp_batch_details
    		      WHERE batch_id = P_BATCH_ID);

Line: 83


 *     P_LAST_UPDATE_LOGIN            IN         NUMBER(15,0),
 *     P_PROGRAM_ID                   IN         NUMBER(15,0),
 *     P_PROGRAM_APPLICATION_ID       IN         NUMBER(15,0),
 *     P_REQUEST_ID                   IN         NUMBER(15,0),
 *     P_APPLICATION_ID               IN         NUMBER,
 *     P_GMISS_CHAR                   IN         VARCHAR2(1),
 *     P_GMISS_NUM                    IN         NUMBER,
 *     P_GMISS_DATE                   IN         DATE,
 *     P_FLEX_VALIDATION              IN         VARCHAR2(1),
 *     P_DSS_SECURITY                 IN         VARCHAR2(1),
 *     P_ALLOW_DISABLED_LOOKUP        IN         VARCHAR2(1),
 *     P_PROFILE_VERSION              IN         VARCHAR2(30)
 *     P_WHAT_IF_ANALYSIS             IN         VARCHAR2,
 *     P_REGISTRY_DEDUP               IN         VARCHAR2,
 *     P_REGISTRY_DEDUP_MATCH_RULE_ID IN         VARCHAR2,
 *   OUT:
 *     X_RETURN_STATUS                OUT NOCOPY VARCHAR2,
 *     X_MSG_COUNT                    OUT NOCOPY NUMBER,
 *     X_MSG_DATA                     OUT NOCOPY VARCHAR2
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   08-10-03   Kate Shan    o Created
 *
**********************************************/

PROCEDURE WORKER_PROCESS(
    Errbuf                         OUT NOCOPY VARCHAR2,
    Retcode                        OUT NOCOPY VARCHAR2,
    P_BATCH_ID                     IN         NUMBER,
    P_ACTUAL_CONTENT_SRC           IN         VARCHAR2,
    P_RERUN                        IN         VARCHAR2,
    P_ERROR_LIMIT                  IN         NUMBER,
    P_BATCH_MODE_FLAG              IN         VARCHAR2,
    P_USER_ID                      IN         NUMBER,
    --bug 3932987
    --P_SYSDATE                      IN         DATE,
    P_SYSDATE                      IN         VARCHAR2,
    P_LAST_UPDATE_LOGIN            IN         NUMBER,
    P_PROGRAM_ID                   IN         NUMBER,
    P_PROGRAM_APPLICATION_ID       IN         NUMBER,
    P_REQUEST_ID                   IN         NUMBER,
    P_APPLICATION_ID               IN         NUMBER,
    P_GMISS_CHAR                   IN         VARCHAR2,
    P_GMISS_NUM	                   IN         NUMBER,
    P_GMISS_DATE                   IN         DATE,
    P_FLEX_VALIDATION              IN         VARCHAR2,
    P_DSS_SECURITY                 IN         VARCHAR2,
    P_ALLOW_DISABLED_LOOKUP        IN         VARCHAR2,
    P_PROFILE_VERSION              IN         VARCHAR2,
    P_WHAT_IF_ANALYSIS             IN         VARCHAR2,
    P_REGISTRY_DEDUP               IN         VARCHAR2,
    P_REGISTRY_DEDUP_MATCH_RULE_ID IN         VARCHAR2
) IS

  l_dml_record       HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE;

Line: 169


  l_dml_record.LAST_UPDATE_LOGIN      := P_LAST_UPDATE_LOGIN;

Line: 182


  SELECT hz_imp_errors_s.NEXTVAL INTO l_start_error_id FROM dual;

Line: 193


    SELECT hz_imp_errors_s.CURRVAL INTO l_current_error_id FROM dual;

Line: 200


      SELECT count(rowid) INTO l_real_error_count
      FROM HZ_IMP_TMP_ERRORS
      WHERE BATCH_ID = l_dml_record.BATCH_ID and
            REQUEST_ID = l_dml_record.REQUEST_ID ;

Line: 211


        UPDATE HZ_IMP_WORK_UNITS
        SET STATUS = 'C', STAGE = STAGE-1
        WHERE BATCH_ID = l_dml_record.BATCH_ID
        AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;

Line: 258


    SELECT HWM_STAGE INTO l_hwm_stage
    FROM HZ_IMP_WORK_UNITS
    WHERE BATCH_ID = l_dml_record.BATCH_ID
    AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;

Line: 273


        select 'Y' into l_batch_run_before
        from hz_imp_batch_details
        where batch_id =  P_BATCH_ID
          AND ( import_status = 'COMPL_ERRORS' OR  import_status = 'COMPLETED')
          AND rownum = 1;

Line: 450


          FND_FILE.PUT_LINE(FND_FILE.LOG, 'update dup party id ');

Line: 452


	  -- update dup party id
          UPDATE HZ_IMP_DUP_PARTIES idp
          SET PARTY_ID =
             ( SELECT PARTY_ID FROM HZ_IMP_PARTIES_SG ips
               WHERE ips.PARTY_ORIG_SYSTEM = idp.PARTY_OS
                 and ips.PARTY_ORIG_SYSTEM_REFERENCE = idp.PARTY_OSR
                 and ips.BATCH_ID = P_BATCH_ID)
          WHERE idp.BATCH_ID = P_BATCH_ID
            AND idp.PARTY_OS = l_dml_record.OS
            AND idp.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
             AND idp.PARTY_OSR IN (SELECT PARTY_ORIG_SYSTEM_REFERENCE
                                  FROM HZ_IMP_PARTIES_SG
                                  WHERE BATCH_ID = P_BATCH_ID
                                  AND PARTY_ORIG_SYSTEM = idp.PARTY_OS
                                  AND PARTY_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);

Line: 469


          UPDATE HZ_IMP_DUP_DETAILS idd
          SET (PARTY_ID, RECORD_ID)=
              ( SELECT PARTY_ID, PARTY_SITE_ID FROM HZ_IMP_ADDRESSES_SG ias
                WHERE ias.PARTY_ORIG_SYSTEM = idd.PARTY_OS
                  and ias.PARTY_ORIG_SYSTEM_REFERENCE = idd.PARTY_OSR
                  and ias.SITE_ORIG_SYSTEM = idd.RECORD_OS
                  and ias.SITE_ORIG_SYSTEM_REFERENCE = idd.RECORD_OSR
                  and ias.BATCH_ID = P_BATCH_ID)
          WHERE ENTITY = 'PARTY_SITES'
            and idd.BATCH_ID = P_BATCH_ID
            AND idd.PARTY_OS = l_dml_record.OS
            AND idd.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
            AND idd.PARTY_OSR IN (SELECT PARTY_ORIG_SYSTEM_REFERENCE
                                  FROM HZ_IMP_ADDRESSES_SG
                                  WHERE BATCH_ID = P_BATCH_ID
                                  AND PARTY_ORIG_SYSTEM = idd.PARTY_OS
                                  AND PARTY_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);

Line: 488


	  -- update dup contact

          UPDATE HZ_IMP_DUP_DETAILS idd
          SET (PARTY_ID, RECORD_ID)=
              ( SELECT PARTY_ID, CONTACT_ID FROM HZ_IMP_CONTACTS_SG ics
                WHERE ics.CONTACT_ORIG_SYSTEM = idd.RECORD_OS
                  and ics.CONTACT_ORIG_SYSTEM_REFERENCE = idd.RECORD_OSR
                  and ics.BATCH_ID = P_BATCH_ID)
          WHERE ENTITY = 'CONTACTS'
            and idd.BATCH_ID = P_BATCH_ID
            AND idd.PARTY_OS = l_dml_record.OS
            AND idd.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
            AND idd.RECORD_OSR IN (SELECT CONTACT_ORIG_SYSTEM_REFERENCE
                                  FROM HZ_IMP_CONTACTS_SG
                                  WHERE BATCH_ID = P_BATCH_ID
                                  AND CONTACT_ORIG_SYSTEM = idd.RECORD_OS
                                  AND SUB_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);

Line: 507


	  -- update dup contact point

          UPDATE HZ_IMP_DUP_DETAILS idd
          SET (PARTY_ID, RECORD_ID)=
              ( SELECT PARTY_ID, CONTACT_POINT_ID FROM HZ_IMP_CONTACTPTS_SG ics
                WHERE ics.PARTY_ORIG_SYSTEM = idd.PARTY_OS
                  and ics.PARTY_ORIG_SYSTEM_REFERENCE = idd.PARTY_OSR
                  and ics.BATCH_ID = P_BATCH_ID)
          WHERE ENTITY = 'CONTACT_POINTS'
            and idd.BATCH_ID = P_BATCH_ID
            AND idd.PARTY_OS = l_dml_record.OS
            AND idd.PARTY_OSR BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR
            AND idd.PARTY_OSR IN (SELECT PARTY_ORIG_SYSTEM_REFERENCE
                                  FROM HZ_IMP_CONTACTPTS_SG
                                  WHERE BATCH_ID = P_BATCH_ID
                                  AND PARTY_ORIG_SYSTEM = idd.PARTY_OS
                                  AND PARTY_ORIG_SYSTEM_REFERENCE BETWEEN l_dml_record.FROM_OSR AND l_dml_record.TO_OSR);

Line: 596


    /* Update status to Complete for the work unit that just finished */
    UPDATE HZ_IMP_WORK_UNITS
      SET STATUS = 'C'
    WHERE BATCH_ID = l_dml_record.BATCH_ID
      AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;

Line: 641


    UPDATE hz_imp_batch_summary
    SET import_status = 'ERROR'
    WHERE batch_id = P_BATCH_ID;

Line: 645


    UPDATE hz_imp_batch_details
    SET import_status = 'ERROR'
    WHERE batch_id = P_BATCH_ID
    AND run_number = (SELECT max(run_number)
    		      FROM hz_imp_batch_details
    		      WHERE batch_id = P_BATCH_ID);