APPS.IGS_PE_GEN_004 SQL Statements

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

Line: 36

 SELECT lk.meaning
 FROM igs_lookup_values lk, igs_pe_race race
 WHERE race.person_id = cp_person_id AND
 lk.lookup_type = cp_lookup_type AND
 lk.lookup_code = race.race_cd;

Line: 84

 SELECT PEI_INFORMATION2, START_DATE
 FROM IGS_PE_EIT
 WHERE PERSON_ID = cp_person_id AND
 INFORMATION_TYPE = cp_information_type AND
 PEI_INFORMATION1= cp_country_code AND
 SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);

Line: 93

 SELECT PEI_INFORMATION2, START_DATE
 FROM IGS_PE_EIT
 WHERE PERSON_ID = cp_person_id AND
 INFORMATION_TYPE = cp_information_type;

Line: 101

  SELECT 'Y'
  FROM hz_citizenship
  WHERE country_code <> cp_country_code AND
        party_id = cp_person_id AND
        SYSDATE BETWEEN NVL(DATE_RECOGNIZED,SYSDATE) AND NVL(END_DATE,SYSDATE);

Line: 168

 SELECT PEI_INFORMATION2, START_DATE
 FROM IGS_PE_EIT
 WHERE PERSON_ID = cp_person_id AND
 INFORMATION_TYPE = cp_information_type AND -- 'PE_INT_PERM_RES' AND
 PEI_INFORMATION1= cp_country_code AND
 SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);

Line: 204

  SELECT SUBSTR(meaning,5,2)
  FROM hr_lookups
  WHERE lookup_type = cp_lookup_type
   AND lookup_code= cp_country_code;

Line: 246

  SELECT 1
  FROM igs_pe_typ_instances_all inst,
       igs_pe_person_types typ
  WHERE inst.person_type_code = typ.person_type_code AND
       inst.person_id = cp_person_id AND
       typ.system_type = cp_system_type AND
       SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE);

Line: 257

  SELECT citizenship_id, country_code, object_version_number, date_recognized, end_date, document_reference,
         document_type, birth_or_selected
  FROM hz_citizenship
  WHERE country_code = cp_country_code AND
        party_id = cp_person_id;

Line: 268

  SELECT birth_or_selected, country_code, date_recognized, document_reference, document_type,
        citizenship_id, object_version_number
  FROM hz_citizenship
  WHERE country_code <> cp_country_code AND
        party_id = cp_person_id AND
        SYSDATE BETWEEN NVL(DATE_RECOGNIZED,SYSDATE) AND NVL(END_DATE,SYSDATE);

Line: 278

  SELECT rowid, pe_eit_id, pei_information1, pei_information2, start_date, end_date
  FROM igs_pe_eit
  WHERE information_type = 'PE_INT_PERM_RES' AND
        pei_information1 = cp_country_code AND
		person_id = cp_person_id AND
		SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);

Line: 288

  SELECT rowid, pe_eit_id, pei_information1, pei_information2, start_date, end_date
  FROM igs_pe_eit
  WHERE information_type = 'PE_INT_PERM_RES' AND
        pei_information1 <> cp_country_code AND
		person_id = cp_person_id AND
		SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);

Line: 298

  SELECT rowid, pe_eit_id, pei_information1, pei_information2, start_date, end_date
  FROM igs_pe_eit
  WHERE information_type = 'PE_INT_PERM_RES' AND
        pei_information1 = cp_country_code AND
		person_id = cp_person_id;

Line: 310

  l_last_update_date hz_citizenship.last_update_date%TYPE;

Line: 341

   OSS_COUNTRY_CODE then update the record with end date as Current Date.
  */

  IF p_has_citizen = 'Y' THEN
   OPEN ctzn_country_cur(p_person_id, l_fnd_country_code);

Line: 352

            p_action            => 'INSERT',
            P_birth_or_selected => NULL,
            P_country_code      => l_fnd_country_code,
            p_date_disowned     => NULL,
            p_date_recognized   => TRUNC(SYSDATE),
            p_DOCUMENT_REFERENCE => NULL,
            p_DOCUMENT_TYPE      => NULL,
            p_PARTY_ID           => P_PERSON_ID,
            p_END_DATE           => NULL,
            p_TERRITORY_SHORT_NAME  => NULL,
            p_last_update_date   => l_last_update_date,
            P_citizenship_id     => l_citizenship_id,
            p_return_status      => l_return_status,
            p_msg_count          => l_msg_count,
            p_msg_data           => p_msg_data,
            p_object_version_number => l_object_version_number
      );

Line: 375

                p_action            => 'UPDATE',
                P_birth_or_selected => NULL,
                P_country_code      => l_fnd_country_code,
                p_date_disowned     => NULL,
                p_date_recognized   => ctzn_country_rec.date_recognized,
                p_document_reference => ctzn_country_rec.document_reference,
                p_document_type      => ctzn_country_rec.document_type,
                p_party_id           => p_person_id,
                p_end_date           => NULL,
                p_territory_short_name  => NULL,
                p_last_update_date   => l_last_update_date,
                P_citizenship_id     => ctzn_country_rec.citizenship_id,
                p_return_status      => l_return_status,
                p_msg_count          => l_msg_count,
                p_msg_data           => p_msg_data,
                p_object_version_number => ctzn_country_rec.object_version_number
        );

Line: 400

						p_action            => 'UPDATE',
						P_birth_or_selected => active_ctzn_rec.birth_or_selected,
						P_country_code      => active_ctzn_rec.country_code,
						p_date_disowned     => NULL,
						p_date_recognized   => active_ctzn_rec.date_recognized,
						p_document_reference => active_ctzn_rec.document_reference,
						p_document_type      => active_ctzn_rec.document_type,
						p_party_id           => p_person_id,
						p_end_date           => TRUNC(SYSDATE),
						p_territory_short_name  => NULL,
						p_last_update_date   => l_last_update_date,
						P_citizenship_id     => active_ctzn_rec.citizenship_id,
						p_return_status      => l_return_status,
						p_msg_count          => l_msg_count,
						p_msg_data           => p_msg_data,
						p_object_version_number => active_ctzn_rec.object_version_number
				);

Line: 423

				  igs_pe_eit_pkg.update_row (
					 X_ROWID     => active_permres_rec.rowid,
					 X_PE_EIT_ID => active_permres_rec.pe_eit_id,
					 X_PERSON_ID => p_person_id,
					 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
					 X_PEI_INFORMATION1 => active_permres_rec.pei_information1,
					 X_PEI_INFORMATION2 => active_permres_rec.pei_information2,
					 X_PEI_INFORMATION3 => NULL,
					 X_PEI_INFORMATION4 => NULL,
					 X_PEI_INFORMATION5 => NULL,
					 X_START_DATE => active_permres_rec.start_date,
					 X_END_DATE   => TRUNC(SYSDATE)
				 );

Line: 459

				p_action            => 'INSERT',
				P_birth_or_selected => NULL,
				P_country_code      => p_citizen_country,
				p_date_disowned     => NULL,
				p_date_recognized   => TRUNC(SYSDATE),
				p_DOCUMENT_REFERENCE => NULL,
				p_DOCUMENT_TYPE      => NULL,
				p_PARTY_ID           => P_PERSON_ID,
				p_END_DATE           => NULL,
				p_TERRITORY_SHORT_NAME  => NULL,
				p_last_update_date   => l_last_update_date,
				P_citizenship_id     => l_citizenship_id,
				p_return_status      => l_return_status,
				p_msg_count          => l_msg_count,
				p_msg_data           => p_msg_data,
				p_object_version_number => l_object_version_number
		  );

Line: 481

					p_action            => 'UPDATE',
					P_birth_or_selected => NULL,
					P_country_code      => p_citizen_country,
					p_date_disowned     => NULL,
					p_date_recognized   => ctzn_country_rec.date_recognized,
					p_document_reference => ctzn_country_rec.document_reference,
					p_document_type      => ctzn_country_rec.document_type,
					p_party_id           => p_person_id,
					p_end_date           => NULL,
					p_territory_short_name  => NULL,
					p_last_update_date   => l_last_update_date,
					P_citizenship_id     => ctzn_country_rec.citizenship_id,
					p_return_status      => l_return_status,
					p_msg_count          => l_msg_count,
					p_msg_data           => p_msg_data,
					p_object_version_number => ctzn_country_rec.object_version_number
			);

Line: 508

						p_action            => 'UPDATE',
						P_birth_or_selected => active_ctzn_rec.birth_or_selected,
						P_country_code      => active_ctzn_rec.country_code,
						p_date_disowned     => NULL,
						p_date_recognized   => active_ctzn_rec.date_recognized,
						p_document_reference => active_ctzn_rec.document_reference,
						p_document_type      => active_ctzn_rec.document_type,
						p_party_id           => p_person_id,
						p_end_date           => TRUNC(SYSDATE),
						p_territory_short_name  => NULL,
						p_last_update_date   => l_last_update_date,
						P_citizenship_id     => active_ctzn_rec.citizenship_id,
						p_return_status      => l_return_status,
						p_msg_count          => l_msg_count,
						p_msg_data           => p_msg_data,
						p_object_version_number => active_ctzn_rec.object_version_number
				);

Line: 535

   If there is already a record present then Update the start date with P_REG_DATE and Registration Number as P_REG_NUMBER.
   -- If the existing record's Country Code is different from the profile OSS_COUNTRY_CODE then end date any active record
   with end date = P_REG_DATE - 1 and create a new record as above.
  */

     IF P_PERM_RES = 'Y' THEN
        OPEN permres_cur(p_person_id, l_per_country_code);

Line: 547

		  igs_pe_eit_pkg.insert_row (
			 X_ROWID     => l_rowid,
			 X_PE_EIT_ID => l_pe_eit_id,
			 X_PERSON_ID => p_person_id,
			 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
			 X_PEI_INFORMATION1 => l_per_country_code,
			 X_PEI_INFORMATION2 => p_reg_number,
			 X_PEI_INFORMATION3 => NULL,
			 X_PEI_INFORMATION4 => NULL,
			 X_PEI_INFORMATION5 => NULL,
			 X_START_DATE => p_reg_date,
			 X_END_DATE   => NULL
		 );

Line: 562

		  igs_pe_eit_pkg.update_row (
			 X_ROWID     => permres_rec.rowid,
			 X_PE_EIT_ID => permres_rec.pe_eit_id,
			 X_PERSON_ID => p_person_id,
			 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
			 X_PEI_INFORMATION1 => permres_rec.pei_information1,
			 X_PEI_INFORMATION2 => p_reg_number,
			 X_PEI_INFORMATION3 => NULL,
			 X_PEI_INFORMATION4 => NULL,
			 X_PEI_INFORMATION5 => NULL,
			 X_START_DATE => p_reg_date,
			 X_END_DATE   => NULL
		 );

Line: 580

		  igs_pe_eit_pkg.update_row (
			 X_ROWID     => oth_active_permres_rec.rowid,
			 X_PE_EIT_ID => oth_active_permres_rec.pe_eit_id,
			 X_PERSON_ID => p_person_id,
			 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
			 X_PEI_INFORMATION1 => oth_active_permres_rec.pei_information1,
			 X_PEI_INFORMATION2 => oth_active_permres_rec.pei_information2,
			 X_PEI_INFORMATION3 => NULL,
			 X_PEI_INFORMATION4 => NULL,
			 X_PEI_INFORMATION5 => NULL,
			 X_START_DATE => oth_active_permres_rec.start_date,
			 X_END_DATE   => p_reg_date - 1
		 );

Line: 599

   OSS_COUNTRY_CODE then update the record with end date as Current Date.
  */
	 ELSIF P_PERM_RES = 'N' THEN

	   FOR active_permres_rec IN active_permres_cur(p_person_id, l_per_country_code) LOOP

		  igs_pe_eit_pkg.update_row (
			 X_ROWID     => active_permres_rec.rowid,
			 X_PE_EIT_ID => active_permres_rec.pe_eit_id,
			 X_PERSON_ID => p_person_id,
			 X_INFORMATION_TYPE => 'PE_INT_PERM_RES',
			 X_PEI_INFORMATION1 => active_permres_rec.pei_information1,
			 X_PEI_INFORMATION2 => active_permres_rec.pei_information2,
			 X_PEI_INFORMATION3 => NULL,
			 X_PEI_INFORMATION4 => NULL,
			 X_PEI_INFORMATION5 => NULL,
			 X_START_DATE => active_permres_rec.start_date,
			 X_END_DATE   => TRUNC(SYSDATE)
		 );

Line: 651

		SELECT 'X'
		FROM igs_pe_felony_dtls
		WHERE person_id=cp_person_id;

Line: 656

		SELECT 'X'
		FROM igs_pe_felony_dtls
		WHERE person_id=cp_person_id
		AND convict_ind='Y';

Line: 698

SELECT teach_description ||'/'||load_description
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type =  cp_teach_cal_type
AND teach_ci_sequence_number = cp_teach_seq_no
ORDER BY load_start_dt DESC;

Line: 736

SELECT description
FROM igs_ca_inst_all
WHERE cal_type = cp_load_cal_typ
AND sequence_number = cp_cal_seq_no;

Line: 775

SELECT MEANING||': '||ALIAS.SURNAME||' '||ALIAS.GIVEN_NAMES OTHER_NAMES
FROM IGS_PE_PERSON_ALIAS ALIAS, IGS_LOOKUP_VALUES LKUP
WHERE ALIAS.ALIAS_TYPE = LKUP.LOOKUP_CODE
AND LKUP.LOOKUP_TYPE = 'PE_ALIAS_TYPE'
AND ALIAS.PERSON_ID = cp_person_id;

Line: 823

Purpose: This procedure inserts record in the Credential and Fnd_Lobs table.
After the successful insertion of these records it calls the FND_WEBATTCH API to create the link in the
attachment tables for the Entity IGS_PE_CREDENTIALS

Change History:
Who         When            What

*/
  l_fileid NUMBER;

Line: 837

  SELECT category_id
  FROM fnd_document_categories_tl
  WHERE name = cp_name;

Line: 842

  SELECT FND_LOBS_S.NEXTVAL FROM dual;

Line: 850

  INSERT INTO FND_LOBS(
  FILE_ID,
  FILE_NAME,
  FILE_CONTENT_TYPE,
  UPLOAD_DATE,
  EXPIRATION_DATE,
  PROGRAM_NAME,
  PROGRAM_TAG,
  LANGUAGE,
  ORACLE_CHARSET,
  FILE_FORMAT
  ) VALUES
  (
  l_fileid,
  P_FILE_NAME,
  P_FILE_CONTENT_TYPE,
  SYSDATE,
  NULL,
  NULL,
  NULL,
  USERENV('LANG'),
  NULL,
  P_FILE_FORMAT
  );

Line: 877

    igs_pe_credentials_pkg.insert_row (
      x_mode                              => 'R',
      x_rowid                             => lv_rowid,
      x_credential_id                     => l_credential_id,
      x_person_id                         => P_PERSON_ID,
      x_credential_type_id                => P_CRED_TYPE_ID,
      x_date_received                     => TO_DATE(NULL),
      x_reviewer_id                       => TO_NUMBER(NULL),
      x_reviewer_notes                    => NULL,
      x_recommender_name                  => NULL,
      x_recommender_title                 => NULL,
      x_recommender_organization          => NULL,
      x_rating_code                       => NULL
    );

Line: 920

PROCEDURE delete_attachment(
 P_CREDENTIAL_ID IN NUMBER,
 P_DOCUMENT_ID IN NUMBER,
 P_MSG_DATA OUT NOCOPY VARCHAR2
) IS
/*
Purpose: This procedure first removes the link for the attachment created and then deletes the record
from the IGS_PE_CREDENTIALS table

Change History:
Who         When            What

*/
 l_rowid ROWID;

Line: 936

 FND_DOCUMENTS_PKG.DELETE_ROW(
 x_document_id => P_DOCUMENT_ID,
 x_datatype_id => 6,
 delete_ref_Flag => 'Y'
 );

Line: 942

 SELECT ROWID INTO l_rowid
 FROM igs_pe_credentials
 WHERE credential_id = P_CREDENTIAL_ID;

Line: 946

 igs_pe_credentials_pkg.delete_row(
  x_rowid => l_rowid
  );

Line: 953

END delete_attachment;

Line: 969

 SELECT date_of_death
 FROM igs_pe_person_base_v
 WHERE person_id = cp_person_id;

Line: 974

 SELECT deceased_ind
 FROM igs_pe_hz_parties
 WHERE party_id = cp_person_id;