APPS.IGW_PROP SQL Statements

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

Line: 11

      SELECT imp.business_group_id,
             org.name
      INTO   o_business_group_id,
             o_business_group_name
      FROM   igw_implementations   imp,
             hr_organization_units org
      WHERE  org.organization_id = imp.business_group_id;

Line: 34

      SELECT o.signing_official_id,
             p.full_name
      INTO   o_signing_official_id,
             o_signing_official_name
      FROM   igw_org_details o,
             per_people_x    p
      WHERE  o.organization_id = i_organization_id
      AND    p.person_id = o.signing_official_id;

Line: 58

      SELECT admin_official_id
      FROM   igw_org_details
      WHERE  organization_id = i_organization_id;

Line: 80

      SELECT user_name
      FROM   fnd_user
      WHERE  employee_id = p_person_id
      ORDER BY user_id;

Line: 104

      SELECT full_name
      INTO   v_pi_full_name
      FROM   igw_prop_persons pp,
             per_people_x     per
      WHERE  pp.proposal_id = p_proposal_id
      AND    pp.pi_flag     = 'Y'
      AND    per.person_id = pp.person_id;

Line: 129

      SELECT last_name||','||first_name
      INTO   v_pi_full_name
      FROM   igw_prop_persons pp,
             per_people_x     per
      WHERE  pp.proposal_id = p_proposal_id
      AND    pp.pi_flag     = 'Y'
      AND    per.person_id = pp.person_id;

Line: 155

      SELECT meaning
      INTO   v_lookup_meaning
      FROM   fnd_lookups
      WHERE  lookup_type = p_lookup_type
      AND    lookup_code = p_lookup_code;

Line: 182

      SELECT count(*)
      INTO   v_count_incomplete
      FROM   igw_prop_narratives
      WHERE  proposal_id = p_proposal_id
      AND    module_status = 'I';

Line: 188

      SELECT count(*)
      INTO   v_count_complete
      FROM   igw_prop_narratives
      WHERE  proposal_id = p_proposal_id
      AND    module_status = 'C';

Line: 215

      SELECT l.meaning
      INTO   v_major_subdivision
      FROM   fnd_lookups     l,
             igw_org_details o
      WHERE  o.organization_id = p_organization_id
      AND    l.lookup_type = 'IGW_NIH_MAJOR_SUBDIVISION'
      AND    l.lookup_code = o.nih_subdivision_code;

Line: 238

      select 'Y'
      from   igw_prop_user_roles
      where  proposal_id = p_proposal_id
      and    user_id = p_user_id
      and    role_id = 3;

Line: 286

         SELECT name
         INTO   v_top_parent_org_name
         FROM   hr_organization_units
         WHERE  organization_id = v_curr_organization_id;

Line: 308

      INSERT INTO igw_prop_users
      (
         proposal_id,
         user_id,
         start_date_active,
         end_date_active,
         last_update_date,
         last_updated_by,
         creation_date,
         created_by,
         last_update_login
      )
      SELECT
         p_proposal_id,
         p_user_id,
         SYSDATE,
         null,
         SYSDATE,
         FND_GLOBAL.USER_ID,
         SYSDATE,
         FND_GLOBAL.USER_ID,
         FND_GLOBAL.LOGIN_ID
      FROM
         dual
      WHERE not exists
         ( SELECT 'X'
           FROM   igw_prop_users
           WHERE  proposal_id = p_proposal_id
           AND    user_id     = p_user_id );

Line: 339

      INSERT INTO igw_prop_user_roles
      (
         proposal_id,
         user_id,
         role_id,
         last_update_date,
         last_updated_by,
         creation_date,
         created_by,
         last_update_login
      )
      SELECT
         p_proposal_id,
         p_user_id,
         p_role_id,
         SYSDATE,
         FND_GLOBAL.USER_ID,
         SYSDATE,
         FND_GLOBAL.USER_ID,
         FND_GLOBAL.LOGIN_ID
      FROM
         dual
      WHERE not exists
         ( SELECT 'X'
           FROM   igw_prop_user_roles
           WHERE  proposal_id = p_proposal_id
           AND    user_id     = p_user_id
           AND    role_id     = p_role_id );

Line: 378

      DELETE igw_prop_user_roles
      WHERE  proposal_id = p_proposal_id
      AND    user_id     = p_user_id
      AND    role_id     = p_role_id;

Line: 383

      DELETE igw_prop_users
      WHERE  proposal_id = p_proposal_id
      AND    user_id     = p_user_id
      AND    not exists (
               SELECT 'X'
               FROM   igw_prop_user_roles
               WHERE  proposal_id = p_proposal_id
               AND    user_id     = p_user_id );

Line: 410

         INSERT INTO igw_proposals_all
         (
            sponsor_action_code,
            sponsor_action_date,
            award_amount,
            proposal_id,
            proposal_number,
            lead_organization_id,
            org_id,
            proposal_status,
            proposal_start_date,
            proposal_end_date,
            proposal_title,
            proposal_type_code,
            award_number,
            original_proposal_number,
            original_award_number,
            original_proposal_start_date,
            original_proposal_end_date,
            activity_type_code,
            sponsor_id,
            funding_sponsor_unit,
            original_sponsor_id,
            sponsor_proposal_number,
            notice_of_opportunity_code,
            program_number,
            program_title,
            program_url,
            submitting_organization_id,
            signing_official_id,
            admin_official_id,
            deadline_date,
            deadline_type,
            letter_of_intent_due_date,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login,
            attribute_category,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            attribute6,
            attribute7,
            attribute8,
            attribute9,
            attribute10,
            attribute11,
            attribute12,
            attribute13,
            attribute14,
            attribute15
         )
         SELECT
            null, /* sponsor_action_code */
            null, /* sponsor_action_date */
            null, /* award_amount */
            p_new_proposal_id,
            p_new_proposal_number,
            lead_organization_id,
            org_id,
            'P',
            proposal_start_date,
            proposal_end_date,
            proposal_title,
            proposal_type_code,
            null, /* award_number */
            original_proposal_number,
            original_award_number,
            original_proposal_start_date,
            original_proposal_end_date,
            activity_type_code,
            sponsor_id,
            funding_sponsor_unit,
            original_sponsor_id,
            null, /* sponsor_proposal_number */
            notice_of_opportunity_code,
            program_number,
            program_title,
            program_url,
            submitting_organization_id,
            signing_official_id,
            admin_official_id,
            deadline_date,
            deadline_type,
            letter_of_intent_due_date,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID,
            attribute_category,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            attribute6,
            attribute7,
            attribute8,
            attribute9,
            attribute10,
            attribute11,
            attribute12,
            attribute13,
            attribute14,
            attribute15
         FROM
            igw_proposals_all
         WHERE
            proposal_id = p_old_proposal_id;

Line: 536

         INSERT INTO igw_prop_users
         (
            proposal_id,
            user_id,
            start_date_active,
            end_date_active,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            p_new_proposal_id,
            user_id,
            SYSDATE,
            null,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_user_roles
         WHERE
            proposal_id = p_old_proposal_id and
            role_id = 0
         ;

Line: 568

         INSERT INTO igw_prop_user_roles
         (
            proposal_id,
            user_id,
            role_id,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            p_new_proposal_id,
            user_id,
            role_id,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_user_roles
         WHERE
            proposal_id = p_old_proposal_id and
            role_id = 0
         ;

Line: 609

         INSERT INTO igw_prop_program_addresses
         (
            proposal_id,
            address_id,
            number_of_copies,
            mail_description,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            p_new_proposal_id,
            address_id,
            number_of_copies,
            mail_description,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_program_addresses
         WHERE
            proposal_id = p_old_proposal_id;

Line: 648

         INSERT INTO igw_prop_locations
         (
            prop_location_id,
            proposal_id,
            performing_organization_id,
            party_id,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            igw_prop_locations_s.nextval,
            p_new_proposal_id,
            performing_organization_id,
            party_id,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_locations
         WHERE
            proposal_id = p_old_proposal_id;

Line: 687

         INSERT INTO igw_prop_persons
         (
            proposal_id,
            person_id,
            person_party_id,
            person_sequence,
            proposal_role_code,
            pi_flag,
            key_person_flag,
            percent_effort,
            person_organization_id,
            org_party_id,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            p_new_proposal_id,
            person_id,
            person_party_id,
            person_sequence,
            proposal_role_code,
            pi_flag,
            key_person_flag,
            percent_effort,
            person_organization_id,
            org_party_id,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_persons
         WHERE
            proposal_id = p_old_proposal_id;

Line: 737

         INSERT INTO igw_prop_special_reviews
         (
            proposal_id,
            special_review_code,
            special_review_type,
            approval_type_code,
            protocol_number,
            application_date,
            approval_date,
            comments,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            p_new_proposal_id,
            special_review_code,
            special_review_type,
            approval_type_code,
            protocol_number,
            application_date,
            approval_date,
            comments,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_special_reviews
         WHERE
            proposal_id = p_old_proposal_id;

Line: 785

         INSERT INTO igw_prop_science_codes
         (
            proposal_id,
            science_code,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            p_new_proposal_id,
            science_code,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_science_codes
         WHERE
            proposal_id = p_old_proposal_id;

Line: 819

         INSERT INTO igw_prop_abstracts
         (
            proposal_id,
            abstract_type,
            abstract_type_code,
            abstract,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login,
            attribute_category,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            attribute6,
            attribute7,
            attribute8,
            attribute9,
            attribute10,
            attribute11,
            attribute12,
            attribute13,
            attribute14,
            attribute15
         )
         SELECT
            p_new_proposal_id,
            abstract_type,
            abstract_type_code,
            abstract,
            1,
            decode(last_update_date,null,null,SYSDATE),
            decode(last_updated_by,null,null,FND_GLOBAL.USER_ID),
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID,
            attribute_category,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            attribute6,
            attribute7,
            attribute8,
            attribute9,
            attribute10,
            attribute11,
            attribute12,
            attribute13,
            attribute14,
            attribute15
         FROM
            igw_prop_abstracts
         WHERE
            proposal_id = p_old_proposal_id;

Line: 929

         INSERT INTO igw_prop_narratives
         (
            proposal_id,
            module_id,
            comments,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login,
            email_address,
            module_status,
            contact_name,
            phone_number,
            module_title
         )
         SELECT
            p_new_proposal_id,
            module_id,
            comments,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID,
            email_address,
            'I',
            contact_name,
            phone_number,
            module_title
         FROM
            igw_prop_narratives
         WHERE
            proposal_id = p_old_proposal_id;

Line: 966

         UPDATE igw_proposals_all
         SET    (narrative_type_code, narrative_submission_code) =
                ( SELECT narrative_type_code, narrative_submission_code
                  FROM   igw_proposals_all
                  WHERE  proposal_id = p_old_proposal_id )
         WHERE  proposal_id = p_new_proposal_id;

Line: 990

         INSERT INTO igw_prop_checklist
         (
            proposal_id,
            document_type_code,
            checklist_order,
            complete,
            not_applicable,
            record_version_number,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login
         )
         SELECT
            p_new_proposal_id,
            document_type_code,
            checklist_order,
            'N',
            not_applicable,
            1,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID
         FROM
            igw_prop_checklist
         WHERE
            proposal_id = p_old_proposal_id;

Line: 1054

            x_last_update_login        => null,
            x_program_application_id   => null,
            x_program_id               => null,
            x_request_id               => null
         );

Line: 1070

            select distinct pk2_value
            from   fnd_attached_documents
            where  entity_name = 'IGW_PROP_NARRATIVES'
            and    pk1_value   = p_old_proposal_id;

Line: 1094

               x_last_update_login        => null,
               x_program_application_id   => null,
               x_program_id               => null,
               x_request_id               => null
            );

Line: 1168

         update igw_prop_checklist
         set    complete = decode(i_value,'C','Y','N'),
                not_applicable = decode(i_value,'N','Y','N'),
                last_update_date  = sysdate,
                last_updated_by   = fnd_global.user_id,
                last_update_login = fnd_global.login_id
         where  proposal_id = i_proposal_id
         and    document_type_code = 'BUDGETS';

Line: 1179

         update igw_prop_checklist
         set    complete = decode(i_value,'C','Y','N'),
                not_applicable = decode(i_value,'N','Y','N'),
                last_update_date  = sysdate,
                last_updated_by   = fnd_global.user_id,
                last_update_login = fnd_global.login_id
         where  proposal_id = i_proposal_id
         and    document_type_code = 'NARRATIVES';