213: where ppr.proposal_id = p_proposal_id
214: and ppr.proposal_id = ppu.proposal_id
215: and ppr.user_id = ppu.user_id
216: and (ppr.role_id = 0 or ppr.role_id = 2)
217: and ppu.user_id = fnd_global.user_id
218: and sysdate >= ppu.start_date_active
219: and sysdate <= nvl(ppu.end_date_active,sysdate);
220:
221: if l_count = 0 then
520: last_update_login)
521: values(
522: igw_prop_maps_s.nextval,g_proposal_id,l_description,
523: p_rule_type,l_map_seq_number,p_run_id,null,
524: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
525: fnd_global.login_id);
526:
527: --dbms_output.put_line('!!!!!!!!!!!!!!!!Inserted into igw_prop_maps .....'||to_char(l_map_id));
528: for c1 in get_map_details loop
521: values(
522: igw_prop_maps_s.nextval,g_proposal_id,l_description,
523: p_rule_type,l_map_seq_number,p_run_id,null,
524: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
525: fnd_global.login_id);
526:
527: --dbms_output.put_line('!!!!!!!!!!!!!!!!Inserted into igw_prop_maps .....'||to_char(l_map_id));
528: for c1 in get_map_details loop
529:
559: last_update_login)
560: values(
561: igw_prop_maps_s.currval,c1.stop_id,c1.user_name,l_wf_role_name,
562: l_wf_display_role_name,c1.approver_type,null,null,null,null,
563: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
564: fnd_global.login_id);
565:
566: --dbms_output.put_line('################Inserted into igw_prop_map_stops .....'||to_char(l_map_id));
567:
560: values(
561: igw_prop_maps_s.currval,c1.stop_id,c1.user_name,l_wf_role_name,
562: l_wf_display_role_name,c1.approver_type,null,null,null,null,
563: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
564: fnd_global.login_id);
565:
566: --dbms_output.put_line('################Inserted into igw_prop_map_stops .....'||to_char(l_map_id));
567:
568:
576: last_update_date,last_updated_by,creation_date, created_by,
577: last_update_login)
578: values(
579: g_proposal_id,l_user_id,sysdate,null,
580: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
581: fnd_global.login_id);
582:
583: end if;
584: close user_exists;
577: last_update_login)
578: values(
579: g_proposal_id,l_user_id,sysdate,null,
580: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
581: fnd_global.login_id);
582:
583: end if;
584: close user_exists;
585:
593: last_update_date,last_updated_by,creation_date, created_by,
594: last_update_login)
595: values(
596: g_proposal_id,l_user_id,4,
597: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
598: fnd_global.login_id);
599: end if;
600: close role_exists;
601:
594: last_update_login)
595: values(
596: g_proposal_id,l_user_id,4,
597: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
598: fnd_global.login_id);
599: end if;
600: close role_exists;
601:
602: elsif p_rule_type = 'R' then
607: last_update_date,last_updated_by,creation_date, created_by,
608: last_update_login)
609: values(
610: g_proposal_id,l_user_id,1,
611: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
612: fnd_global.login_id);
613: end if;
614: close role_exists;
615: end if;
608: last_update_login)
609: values(
610: g_proposal_id,l_user_id,1,
611: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
612: fnd_global.login_id);
613: end if;
614: close role_exists;
615: end if;
616: end loop;
1124: end_date_active,last_update_date,last_updated_by,creation_date,
1125: created_by,last_update_login)
1126: values(
1127: g_proposal_id,l_sign_off_user_id,sysdate,
1128: null,sysdate,fnd_global.user_id,sysdate,
1129: fnd_global.user_id,fnd_global.login_id);
1130:
1131: end if;
1132: close so_user_exists;
1125: created_by,last_update_login)
1126: values(
1127: g_proposal_id,l_sign_off_user_id,sysdate,
1128: null,sysdate,fnd_global.user_id,sysdate,
1129: fnd_global.user_id,fnd_global.login_id);
1130:
1131: end if;
1132: close so_user_exists;
1133:
1140: end_date_active,last_update_date,last_updated_by,creation_date,
1141: created_by,last_update_login)
1142: values(
1143: g_proposal_id,l_admin_off_user_id,sysdate,
1144: null,sysdate,fnd_global.user_id,sysdate,
1145: fnd_global.user_id,fnd_global.login_id);
1146:
1147: end if;
1148: close so_user_exists;
1141: created_by,last_update_login)
1142: values(
1143: g_proposal_id,l_admin_off_user_id,sysdate,
1144: null,sysdate,fnd_global.user_id,sysdate,
1145: fnd_global.user_id,fnd_global.login_id);
1146:
1147: end if;
1148: close so_user_exists;
1149:
1156: last_update_date,last_updated_by,creation_date, created_by,
1157: last_update_login)
1158: values(
1159: g_proposal_id,l_sign_off_user_id,3,
1160: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
1161: fnd_global.login_id);
1162: end if;
1163: close so_user_role_exists;
1164:
1157: last_update_login)
1158: values(
1159: g_proposal_id,l_sign_off_user_id,3,
1160: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
1161: fnd_global.login_id);
1162: end if;
1163: close so_user_role_exists;
1164:
1165: if l_sign_off_user_id <> l_admin_off_user_id then
1170: last_update_date,last_updated_by,creation_date, created_by,
1171: last_update_login)
1172: values(
1173: g_proposal_id,l_admin_off_user_id,3,
1174: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
1175: fnd_global.login_id);
1176: end if;
1177: close so_user_role_exists;
1178:
1171: last_update_login)
1172: values(
1173: g_proposal_id,l_admin_off_user_id,3,
1174: sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
1175: fnd_global.login_id);
1176: end if;
1177: close so_user_role_exists;
1178:
1179: end if;