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

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

Line: 4


procedure INSERT_ROW (
                p_action                        IN      VARCHAR2,
                p_rowid                         IN OUT NOCOPY  VARCHAR2,
                p_location_id                   IN OUT NOCOPY  NUMBER,
                p_start_dt                      IN      igs_pe_hz_pty_sites.start_date%TYPE,
                p_end_dt                        IN      igs_pe_hz_pty_sites.end_date%TYPE,
                p_country                       IN      VARCHAR2,
                p_address_style                 IN      VARCHAR2,
                p_addr_line_1                   IN      VARCHAR2,
                p_addr_line_2                   IN      VARCHAR2,
                p_addr_line_3                   IN      VARCHAR2,
                p_addr_line_4                   IN      VARCHAR2,
                p_date_last_verified            IN      DATE,
                p_correspondence                IN      VARCHAR2,
                p_city                          IN      VARCHAR2,
                p_state                         IN      VARCHAR2,
                p_province                      IN      VARCHAR2,
                p_county                        IN      VARCHAR2,
                p_postal_code                   IN      VARCHAR2,
                p_address_lines_phonetic        IN      VARCHAR2,
                p_delivery_point_code           IN      VARCHAR2,
                p_other_details_1               IN      VARCHAR2,
                p_other_details_2               IN      VARCHAR2,
                p_other_details_3               IN      VARCHAR2,
                l_return_status         OUT NOCOPY     VARCHAR2,
                l_msg_data              OUT NOCOPY      VARCHAR2,
                p_party_id                      IN      NUMBER,
                p_party_site_id       IN OUT NOCOPY     NUMBER,
                p_party_type            IN      VARCHAR2,
                p_last_update_date      IN OUT NOCOPY  DATE,
                p_party_site_ovn                IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
                p_location_ovn                  IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
                p_status                        IN hz_party_sites.status%TYPE
   ) AS

        p_init_msg_list                 VARCHAR2(30) := FND_API.G_TRUE;

Line: 47


        p_location_rec_insert    HZ_LOCATION_V2PUB.location_rec_type;

Line: 48


        p_party_site_rec_insert   HZ_PARTY_SITE_V2PUB.party_site_rec_type;

Line: 52


         CURSOR c_birth_date_val IS SELECT date_of_birth FROM HZ_PERSON_PROFILES
         WHERE party_id = p_party_id AND effective_end_Date IS NULL;

Line: 57


        SELECT hz_party_site_number_s.NEXTVAL
        FROM dual;

Line: 90


        p_location_rec_insert.country                   := p_country;

Line: 91


        p_location_rec_insert.address_style             := p_address_style;

Line: 92


        p_location_rec_insert.address1                  := p_addr_line_1;

Line: 93


        p_location_rec_insert.address2                  := p_addr_line_2;

Line: 94


        p_location_rec_insert.address3                  := p_addr_line_3;

Line: 95


        p_location_rec_insert.address4                  := p_addr_line_4;

Line: 96


        p_location_rec_insert.city                      := p_city;

Line: 97


        p_location_rec_insert.state                     := p_state;

Line: 98


        p_location_rec_insert.province                  := p_province;

Line: 99


        p_location_rec_insert.county                    := p_county;

Line: 100


        p_location_rec_insert.postal_code               := p_postal_code;

Line: 101


        p_location_rec_insert.address_lines_phonetic    := p_address_lines_phonetic;

Line: 102


        p_location_rec_insert.address_effective_date    := NULL;

Line: 103


        p_location_rec_insert.address_expiration_date   := NULL;

Line: 104


        p_location_rec_insert.created_by_module         := 'IGS';

Line: 105


        p_location_rec_insert.application_id            := 8405;

Line: 106


        p_location_rec_insert.delivery_point_code       := p_delivery_point_code;

Line: 110


                 P_LOCATION_REC                         => p_location_rec_insert,
		 P_DO_ADDR_VAL				=> 'Y',
		 X_LOCATION_ID                          => p_location_id,
		 x_addr_val_status			=> l_addr_val_status,
		 x_addr_warn_msg			=> l_addr_warn_msg,
                 X_RETURN_STATUS                        => l_return_status,
                 X_MSG_COUNT                            => l_msg_count,
                 X_MSG_DATA                             => l_msg_data
                );

Line: 137


                P_PARTY_SITE_REC_INSERT.PARTY_ID          :=  p_party_id;

Line: 138


                P_PARTY_SITE_REC_INSERT.LOCATION_ID       :=  p_location_id;

Line: 139


                P_PARTY_SITE_REC_INSERT.IDENTIFYING_ADDRESS_FLAG :=  p_correspondence ;

Line: 140


                P_PARTY_SITE_REC_INSERT.created_by_module := 'IGS';

Line: 141


                P_PARTY_SITE_REC_INSERT.application_id    := 8405;

Line: 142


                P_PARTY_SITE_REC_INSERT.status            := p_status;

Line: 151


                   P_PARTY_SITE_REC_INSERT.PARTY_SITE_NUMBER := 'IGS-'||l_party_site_number;

Line: 158


		       P_PARTY_SITE_REC       =>  p_party_site_rec_insert,
		       X_RETURN_STATUS        => l_return_status,
		       X_MSG_COUNT              => l_msg_count,
		       X_MSG_DATA               => l_msg_data,
		       X_PARTY_SITE_ID        => p_party_site_id,
		       X_PARTY_SITE_NUMBER    => l_party_site_number /* not passed to form */
		);

Line: 182


                         igs_pe_hz_pty_sites_pkg.insert_row(x_rowid          => lv_rowid,
                                                     x_party_site_id  => p_party_site_id,
                                                     x_start_date     => p_start_dt,
                                                     x_end_date       => p_end_dt );

Line: 195


END INSERT_ROW;

Line: 197


procedure UPDATE_ROW (
                p_action                        IN      VARCHAR2,
                p_rowid                         IN OUT NOCOPY  VARCHAR2,
                p_location_id                   IN OUT NOCOPY  NUMBER,
                p_start_dt                      IN      igs_pe_hz_pty_sites.start_date%TYPE,
                p_end_dt                        IN      igs_pe_hz_pty_sites.end_date%TYPE,
                p_country                       IN      VARCHAR2,
                p_address_style                 IN      VARCHAR2,
                p_addr_line_1                   IN      VARCHAR2,
                p_addr_line_2                   IN      VARCHAR2,
                p_addr_line_3                   IN      VARCHAR2,
                p_addr_line_4                   IN      VARCHAR2,
                p_date_last_verified            IN      DATE,
                p_correspondence                IN      VARCHAR2,
                p_city                          IN      VARCHAR2,
                p_state                         IN      VARCHAR2,
                p_province                      IN      VARCHAR2,
                p_county                        IN      VARCHAR2,
                p_postal_code                   IN      VARCHAR2,
                p_address_lines_phonetic        IN      VARCHAR2,
                p_delivery_point_code           IN      VARCHAR2,
                p_other_details_1               IN      VARCHAR2,
                p_other_details_2               IN      VARCHAR2,
                p_other_details_3               IN      VARCHAR2,
                l_return_status                 OUT NOCOPY     VARCHAR2,
                l_msg_data                      OUT NOCOPY      VARCHAR2,
                p_party_id                      IN      NUMBER,
                p_party_site_id                 IN OUT NOCOPY   NUMBER,
                p_party_type                    IN      VARCHAR2,
                p_last_update_date              IN  OUT NOCOPY DATE,
                p_party_site_ovn                IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
                p_location_ovn                  IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
                p_status                        IN hz_party_sites.status%TYPE
  )
  AS
        p_init_msg_list                 VARCHAR2(30) := FND_API.G_TRUE;

Line: 239


        p_location_rec_update   HZ_LOCATION_V2PUB.location_rec_type;

Line: 240


        p_party_site_rec_update HZ_PARTY_SITE_V2PUB.party_site_rec_type;

Line: 247


        CURSOR c_birth_date_val IS SELECT date_of_birth FROM HZ_PERSON_PROFILES
        WHERE party_id = p_party_id AND effective_end_Date IS NULL;

Line: 253


        SELECT ROWID,start_date
        FROM igs_pe_hz_pty_sites WHERE party_site_id = p_party_site_id;

Line: 286


        p_location_rec_update.location_id               := p_location_id;

Line: 287


        p_location_rec_update.country                   := p_country;

Line: 288


        p_location_rec_update.address_style             := NVL(p_address_style,FND_API.G_MISS_CHAR);

Line: 289


        p_location_rec_update.address1                  := NVL(p_addr_line_1,FND_API.G_MISS_CHAR);

Line: 290


        p_location_rec_update.address2                  := NVL(p_addr_line_2,FND_API.G_MISS_CHAR);

Line: 291


        p_location_rec_update.address3                  := NVL(p_addr_line_3,FND_API.G_MISS_CHAR);

Line: 292


        p_location_rec_update.address4                  := NVL(p_addr_line_4,FND_API.G_MISS_CHAR);

Line: 293


        p_location_rec_update.city                      := NVL(p_city,FND_API.G_MISS_CHAR);

Line: 294


        p_location_rec_update.state                     := NVL(p_state,FND_API.G_MISS_CHAR);

Line: 295


        p_location_rec_update.province                  := NVL(p_province,FND_API.G_MISS_CHAR);

Line: 296


        p_location_rec_update.county                    := NVL(p_county,FND_API.G_MISS_CHAR);

Line: 297


        p_location_rec_update.postal_code               := NVL(p_postal_code,FND_API.G_MISS_CHAR);

Line: 298


        p_location_rec_update.address_lines_phonetic    := NVL(p_address_lines_phonetic,FND_API.G_MISS_CHAR);

Line: 299


        p_location_rec_update.delivery_point_code       := NVL(p_delivery_point_code,FND_API.G_MISS_CHAR);

Line: 300


        P_PARTY_SITE_REC_update.PARTY_ID                 :=     p_party_id;

Line: 301


        P_PARTY_SITE_REC_update.PARTY_SITE_ID            :=     p_party_site_id;

Line: 302


        P_PARTY_SITE_REC_update.LOCATION_ID              :=     p_location_id;

Line: 303


        P_PARTY_SITE_REC_update.IDENTIFYING_ADDRESS_FLAG :=     p_correspondence;

Line: 304


        P_PARTY_SITE_REC_update.status                   :=     p_status;

Line: 306


    HZ_LOCATION_V2PUB.update_location(
         P_INIT_MSG_LIST                        => p_init_msg_list,
         P_LOCATION_REC                         => p_location_rec_update,
	 p_do_addr_val				=> 'Y',
         p_object_version_number                => p_location_ovn,
	 x_addr_val_status			=> l_addr_val_status,
	 x_addr_warn_msg			=> l_addr_warn_msg,
	 X_RETURN_STATUS                        => l_return_status,
         X_MSG_COUNT                            => l_msg_count,
         X_MSG_DATA                             => l_msg_data
        );

Line: 329


            HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE(
                                  p_init_msg_list => p_init_msg_list,
                                  p_party_site_rec =>  p_party_site_rec_update,
                                  p_object_version_number => p_party_site_ovn,
                                  x_return_status => l_return_status,
                                  x_msg_count => l_msg_count,
                                  x_msg_data  => l_msg_data
                           );

Line: 363


    END IF;   -- success of update location

Line: 369


END update_row;