APPS.IGS_PE_GEN_003 SQL Statements

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

Line: 6

  ||  Purpose : Created to process FA todo items in case of insert/update of housing status/residency status
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  (reverse chronological order - newest change first)
  ||  Who             When            What
  ||  skpandey        18-AUG-2005     Bug#: 4378028
  ||                                  Added procedure raise_person_type_event and resp_assignment to handle person type responsibility enhancements
*/
PROCEDURE process_res_dtls(
                           p_action        IN  VARCHAR2 , -- I/U I-INSERT U-UPDATE,
                           p_old_record    IN  igs_pe_res_dtls_all%ROWTYPE,
                           p_new_record    IN  igs_pe_res_dtls_all%ROWTYPE
                          ) AS
l_label             VARCHAR2(100);

Line: 45

                               p_action      IN  VARCHAR2 , -- I/U I-INSERT U-UPDATE,
                               p_old_record  IN  igs_pe_teach_periods_all%ROWTYPE,
                               p_new_record  IN  igs_pe_teach_periods_all%ROWTYPE
                              ) AS
l_label             VARCHAR2(100);

Line: 89

    SELECT ppt.system_type
    FROM igs_pe_person_types ppt
    WHERE ppt.person_type_code = cp_person_type_code;

Line: 95

    SELECT IGS_PE_PER_TYP_WF_S.nextval
    FROM DUAL;

Line: 141

     l_parameter_list_t.DELETE;

Line: 171

  ||  ssawhney        18-AUG-2005     Bug#: 4378028 added end date logic in insert/update.
*/

l_default_date             DATE := TO_DATE('4712/12/31','YYYY/MM/DD');

Line: 177

SELECT user_id
FROM fnd_user
WHERE person_party_id = cp_person_id;

Line: 184

SELECT default_resp.responsibility_key, oss.application_short_name, default_resp.responsibility_id, default_resp.application_id, default_resp.description
FROM igs_pe_typ_rsp_dflt oss , fnd_responsibility_vl default_resp
WHERE oss.s_person_type= cp_system_person_typ
AND oss.responsibility_key = default_resp.responsibility_key
AND NOT EXISTS
(SELECT 1
FROM fnd_user_resp_groups_direct resp_group
WHERE user_id = cp_user_id AND
resp_group.responsibility_id = default_resp.responsibility_id AND
resp_group.responsibility_application_id = default_resp.application_id
);

Line: 200

SELECT  resp.responsibility_key, resp.responsibility_id, resp.application_id, resp_group.start_date, Resp_group.end_date, fnd.application_short_name
FROM fnd_user_resp_groups_direct resp_group, fnd_responsibility resp , igs_pe_typ_rsp_dflt oss, fnd_application fnd
WHERE user_id = cp_user_id AND
Resp.responsibility_id = resp_group.Responsibility_id AND
Resp.application_id = resp_group.responsibility_application_id AND
oss.s_person_type = cp_system_person_type AND
oss.responsibility_key =  resp.responsibility_key AND
resp.application_id = fnd.application_id;

Line: 213

SELECT max(NVL(pti.end_date , l_default_date))
FROM   igs_pe_typ_instances_all pti , igs_pe_person_types typ , igs_pe_typ_rsp_dflt dflt
WHERE  pti.person_id = cp_person_id AND
       pti.person_type_code = typ.person_type_code AND
       typ.system_type = dflt.s_person_type AND
       dflt.responsibility_key = cp_resp_key AND
       dflt.application_short_name = cp_app_short_name;

Line: 226

SELECT  resp.application_id, resp.responsibility_id , resp.responsibility_key, resp_group.start_date, Resp_group.end_date
FROM fnd_user_resp_groups_direct resp_group, fnd_responsibility resp
WHERE user_id = cp_user_id AND
Resp.responsibility_id = resp_group.Responsibility_id AND
Resp.application_id = resp_group.responsibility_application_id
--AND Resp_group.end_date IS NOT NULL
AND EXISTS
   (SELECT 1
    FROM igs_pe_typ_rsp_dflt oss
    WHERE oss.s_person_type= cp_system_person_type
    AND oss.responsibility_key = resp.responsibility_key);

Line: 250

l_update_resp              BOOLEAN := FALSE;

Line: 268

  IF l_action IN ('INSERT', 'UPDATE')  THEN

    FOR inactive_resp_rec IN get_inactive_resp_cur(l_user_id, l_system_person_type) LOOP

             --fnd_logging
        IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
                 l_label := 'igs.plsql.igs_pe_gen_003.resp_assignment.'||l_action;

Line: 279

        IF l_action = 'INSERT' THEN
        -- if any existing resp exists, then update them with the MAX end date between PTI end date and existing resp end date.
             IF  ( NVL(inactive_resp_rec.end_date, l_default_date) < NVL(TRUNC(l_end_date), l_default_date)   )  THEN
		   l_update_resp := TRUE;

Line: 285

	   --l_action is update.
	     OPEN get_max_date (l_person_id,inactive_resp_rec.responsibility_key, inactive_resp_rec.application_short_name);

Line: 292

	         l_update_resp := FALSE;

Line: 294

	         l_update_resp := TRUE;

Line: 313

	       --Call update
	       IF l_update_resp THEN
                  fnd_user_resp_groups_api.update_assignment (
                                                   user_id                         => l_user_id,
                                                   responsibility_id               => inactive_resp_rec.responsibility_id,
                                                   responsibility_application_id   => inactive_resp_rec.application_id,
                                                   security_group_id               => 0,
                                                   start_date                      => inactive_resp_rec.start_date,
                                                   end_date                        => TRUNC(l_end_date),
                                                   description                     => NULL);

Line: 333

       Fnd_User_Resp_Groups_api.insert_assignment (
                                                   user_id                         => l_user_id,
                                                   responsibility_id               => resp_info_rec.responsibility_id,
                                                   responsibility_application_id   => resp_info_rec.application_id,
                                                   security_group_id               => 0,
                                                   start_date                      => SYSDATE,
                                                   end_date                        => TRUNC(l_end_date),
                                                   description                     => resp_info_rec.description);

Line: 344

ELSIF l_action = 'DELETE' THEN

   FOR resp_sys_rec IN get_resp_sys_cur(l_user_id, l_system_person_type) LOOP
      --fnd_logging
             IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
                 l_label := 'igs.plsql.igs_pe_gen_003.resp_assignment.delete';

Line: 357

            fnd_user_resp_groups_api.update_assignment (
                                                   user_id                         => l_user_id,
                                                   responsibility_id               => resp_sys_rec.responsibility_id,
                                                   responsibility_application_id   => resp_sys_rec.application_id,
                                                   security_group_id               => 0,
                                                   start_date                      => resp_sys_rec.start_date,
                                                   end_date                        => TRUNC(SYSDATE),
                                                   description                     => NULL);