APPS.IGS_PE_WF_GEN SQL Statements

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

Line: 13

                      address_create,address_update,primary_address_ind_update
                      old function change_address stubbed.
 asbala   01-09-2003  Reference: Build SWCR01,02,04
                      Business Event triggered
 gmaheswa 3-Nov-2004  Created a procedure change_housing_status for raising an event in case of insert/update of housing status
 pkpatel  9-Nov-2004  Bug 3993967 (Modified signature of procedure CHANGE_RESIDENCE. Modified process_residency as per new
                      Notification message. Stubbed the procedure get_res_details.)
 pkpatel  19=Sep-2005 Bug 4618459 (Removed the reference of HZ_PARAM_TAB. Commented the procedure get_address_dtls.
                      stubbed the procedures address_update and primary_address_ind_update)
 pkpatel  21-Feb-2006 Bug 4938278 (Added the cursor in the create_address to retrieve the Dates from igs_pe_hz_pty_sites)
  vskumar   24-May-2006 Bug 5211157 Added two procdeures specs raise_acad_intent_event and process_acad_intent
******************************************************************/


  PROCEDURE change_residence( p_resident_details_id IN NUMBER,
							  p_old_res_status IN VARCHAR2,
							  p_old_evaluator IN VARCHAR2,
							  p_old_evaluation_date IN VARCHAR2,
   							  p_old_comment IN VARCHAR2,
							  p_action IN VARCHAR2) AS
  /******************************************************************
   Created By         : Vinay Chappidi
   Date Created By    : 20-Sep-2001
   Purpose            : Procedure for sending workflow mail notification when the
                        residency status or class is changed, to inform Fee Asess user(STUDENT_FIN User)
   remarks            :
   Change History
   Who      When        What
   asbala   01-09-2003  Reference: Build SWCR01,02,04
  ******************************************************************/


    CURSOR c_seq_num IS
    SELECT IGS_PE_PE003_WF_S.nextval
    FROM DUAL;

Line: 107

     SELECT full_name person_name,person_number
     FROM igs_pe_person_base_v
     WHERE person_id = cp_person_id;

Line: 112

   SELECT person_id, cal_type, sequence_number, residency_class, residency_class_desc, residency_status_desc,
          calendar_desc, evaluation_date, evaluator, comments, last_updated_by
   FROM igs_pe_res_dtls_v
   WHERE resident_details_id = cp_resident_details_id;

Line: 118

   SELECT meaning
   FROM igs_lookup_values
   WHERE lookup_type = cp_lookup_type AND
         lookup_code = cp_lookup_code;

Line: 123

   CURSOR updated_by_cur(cp_user_id NUMBER) IS
   SELECT user_name
   FROM   fnd_user
   WHERE user_id = cp_user_id;

Line: 235

   OPEN updated_by_cur(res_dtl_rec.last_updated_by);

Line: 236

   FETCH updated_by_cur INTO l_user_name;

Line: 237

   CLOSE updated_by_cur;

Line: 242

		       aname     =>  'UPDATED_BY',
		       avalue    =>  l_user_name
		    );

Line: 305

SELECT party_number, party_name, location_id,hp.party_type,hp.party_id
FROM    hz_parties hp, hz_party_sites hps
WHERE hp.party_id = hps.party_id AND
                (hp.party_type = 'PERSON' OR hp.party_type = 'ORGANIZATION') AND
                hp.status = 'A'  AND
                hps.party_site_id = cp_party_site_id;

Line: 313

SELECT hzl.address1, hzl.address2, hzl.address3, hzl.address4, hzl.city, hzl.province, hzl.state ,
       hzl.county, hzl.postal_code, hzl.country, hzl.delivery_point_code ,hzp.party_number,
       hzp.party_name,hps.identifying_address_flag
FROM
hz_party_sites hps,
hz_locations hzl,
hz_parties hzp
WHERE
hps.party_site_id=cp_party_site_id AND
hps.location_id=hzl.location_id AND
hzp.party_id=hps.party_id;

Line: 327

SELECT inst_org_ind
FROM
       igs_pe_hz_parties
WHERE
       party_id = cp_party_id;

Line: 335

SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = cp_country;

Line: 340

SELECT meaning
FROM igs_lookup_values
WHERE
lookup_type='YES_NO' AND
lookup_code=cp_lk_code;

Line: 347

SELECT start_date, end_date
FROM igs_pe_hz_pty_sites
WHERE party_site_id = cp_party_site_id;

Line: 552

PROCEDURE address_update(itemtype IN VARCHAR2,
                         itemkey IN VARCHAR2,
                         actid IN NUMBER,
                         funcmode IN VARCHAR2,
                         resultout OUT NOCOPY VARCHAR2)
/******************************************************************
 Created By         : Navin Sidana.
 Date Created By    : 9/9/2003
 Purpose            : Function to process location update event
                      raised from HZ.
 remarks            :
 Change History
 Who      When        What
 gmaheswa 17-Jan-1006 4938278: Modified complete logic as per R12 Business Events Mandate Build.
******************************************************************/
IS

CURSOR chk_oss_party(cp_party_site_id NUMBER) IS
SELECT hp.party_number, hp.party_name, hps.location_id,
       php.inst_org_ind, hps.identifying_address_flag, php.oss_org_unit_cd
FROM    hz_parties hp, hz_party_sites hps, igs_pe_hz_parties php
WHERE hp.party_id = hps.party_id
AND hp.party_id = php.party_id
AND hp.status = 'A'
AND hps.status = 'A'
AND hps.party_site_id = cp_party_site_id;

Line: 580

SELECT address1, address2, address3, address4, city, province, state ,
       county, postal_code, country, delivery_point_code
FROM hz_locations
WHERE location_id = cp_location_id;

Line: 586

SELECT start_date, end_date
FROM igs_pe_hz_pty_sites
WHERE party_site_id = cp_party_site_id;

Line: 591

SELECT address1, address2, address3, address4, city,
       prov_state_admin_code, county, postal_code, country
FROM hz_location_profiles
WHERE location_id = cp_location_id
AND SYSDATE NOT BETWEEN effective_start_date AND NVL(effective_end_date,SYSDATE)
ORDER BY location_profile_id DESC;

Line: 599

SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = cp_country;

Line: 875

END address_update;

Line: 878

PROCEDURE primary_address_ind_update(itemtype IN VARCHAR2,
                                     itemkey IN VARCHAR2,
                                     actid IN NUMBER,
                                     funcmode IN VARCHAR2,
                                     resultout OUT NOCOPY VARCHAR2)
/******************************************************************
 Created By         : Navin Sidana.
 Date Created By    : 9/9/2003
 Purpose            : Function to process party site update event
                      raised from HZ.
 remarks            :
 Change History
 Who      When        What
 gmaheswa 17-Jan-06   4938278: Stubbed.
******************************************************************/
IS

BEGIN
NULL;

Line: 897

END primary_address_ind_update;

Line: 917

    SELECT IGS_PE_CHG_HOUSING_STAT_S.nextval
    FROM DUAL;

Line: 968

     SELECT meaning
     FROM   igs_lookup_values
     WHERE  lookup_type=cp_lookup_type
     AND    lookup_code=cp_lookup_code;

Line: 975

     SELECT message_text
     FROM   fnd_new_messages
     WHERE  message_name=cp_message_name
     AND    application_id = 8405
     AND    LANGUAGE_CODE = USERENV('LANG');

Line: 1059

    SELECT ''||party_number||'
'|| person_last_name||', '||person_first_name|| '
' person_record
    FROM hz_parties
    WHERE party_id = cp_person_id;

Line: 1065

    SELECT ''||party_number||'
'|| party_name|| '
' person_record
    FROM  igs_or_inst_org_base_v
    WHERE party_id = cp_party_id
    AND   inst_org_ind='I';

Line: 1192

     person_id_tbl.DELETE;

Line: 1201

      SELECT IGS_PE_PE002_WF_S.NEXTVAL
      FROM DUAL;

Line: 1211

     SELECT inst_org_ind
     FROM   igs_pe_hz_parties
     WHERE  party_id=cp_party_id;

Line: 1279

      p_persons_processes.DELETE;

Line: 1320

        l_parameter_list_t.DELETE;

Line: 1337

       SELECT person_number, full_name
       FROM igs_pe_person_base_v
       WHERE person_id = cp_person_id;

Line: 1342

       SELECT meaning
       FROM igs_lookup_values
       WHERE lookup_type = cp_lookup_type
       AND lookup_code = cp_lookup_code;

Line: 1348

       SELECT description
       FROM igs_ca_inst_all
       WHERE cal_type = cp_cal_type
       AND sequence_number = cp_sequence_number;