APPS.GHR_MASS_AWARDS_ELIG dependencies on DBMS_SQL

Line 837: l_cursor_id := DBMS_SQL.OPEN_CURSOR;

833: p_rating_exists => l_rating_exists);
834:
835:
836:
837: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
838: DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);

Line 838: DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);

834:
835:
836:
837: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
838: DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);

Line 839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);

835:
836:
837: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
838: DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);

Line 840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);

836:
837: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
838: DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);
844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);

Line 841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);

837: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
838: DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);
844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);
845: l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);

Line 842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);

838: DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);
844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);
845: l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);
846:

Line 843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);

839: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);
844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);
845: l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);
846:
847: Loop

Line 844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);

840: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);
844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);
845: l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);
846:
847: Loop
848: If DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then

Line 845: l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);

841: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);
844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);
845: l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);
846:
847: Loop
848: If DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
849: exit;

Line 848: If DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then

844: DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);
845: l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);
846:
847: Loop
848: If DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
849: exit;
850: Else
851: dbms_sql.column_value(l_cursor_id,1,l_per_id);
852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);

Line 851: dbms_sql.column_value(l_cursor_id,1,l_per_id);

847: Loop
848: If DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
849: exit;
850: Else
851: dbms_sql.column_value(l_cursor_id,1,l_per_id);
852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);
853: dbms_sql.column_value(l_cursor_id,3,l_pos_id);
854: dbms_sql.column_value(l_cursor_id,4,l_loc_id);
855: dbms_sql.column_value(l_cursor_id,5,l_job_id);

Line 852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);

848: If DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
849: exit;
850: Else
851: dbms_sql.column_value(l_cursor_id,1,l_per_id);
852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);
853: dbms_sql.column_value(l_cursor_id,3,l_pos_id);
854: dbms_sql.column_value(l_cursor_id,4,l_loc_id);
855: dbms_sql.column_value(l_cursor_id,5,l_job_id);
856: dbms_sql.column_value(l_cursor_id,6,l_grd_id);

Line 853: dbms_sql.column_value(l_cursor_id,3,l_pos_id);

849: exit;
850: Else
851: dbms_sql.column_value(l_cursor_id,1,l_per_id);
852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);
853: dbms_sql.column_value(l_cursor_id,3,l_pos_id);
854: dbms_sql.column_value(l_cursor_id,4,l_loc_id);
855: dbms_sql.column_value(l_cursor_id,5,l_job_id);
856: dbms_sql.column_value(l_cursor_id,6,l_grd_id);
857: -- Bug#2459352 Checking whether the employee belongs to federal org or not.

Line 854: dbms_sql.column_value(l_cursor_id,4,l_loc_id);

850: Else
851: dbms_sql.column_value(l_cursor_id,1,l_per_id);
852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);
853: dbms_sql.column_value(l_cursor_id,3,l_pos_id);
854: dbms_sql.column_value(l_cursor_id,4,l_loc_id);
855: dbms_sql.column_value(l_cursor_id,5,l_job_id);
856: dbms_sql.column_value(l_cursor_id,6,l_grd_id);
857: -- Bug#2459352 Checking whether the employee belongs to federal org or not.
858:

Line 855: dbms_sql.column_value(l_cursor_id,5,l_job_id);

851: dbms_sql.column_value(l_cursor_id,1,l_per_id);
852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);
853: dbms_sql.column_value(l_cursor_id,3,l_pos_id);
854: dbms_sql.column_value(l_cursor_id,4,l_loc_id);
855: dbms_sql.column_value(l_cursor_id,5,l_job_id);
856: dbms_sql.column_value(l_cursor_id,6,l_grd_id);
857: -- Bug#2459352 Checking whether the employee belongs to federal org or not.
858:
859: open c_fed_employee(l_pos_id);

Line 856: dbms_sql.column_value(l_cursor_id,6,l_grd_id);

852: dbms_sql.column_value(l_cursor_id,2,l_asg_id);
853: dbms_sql.column_value(l_cursor_id,3,l_pos_id);
854: dbms_sql.column_value(l_cursor_id,4,l_loc_id);
855: dbms_sql.column_value(l_cursor_id,5,l_job_id);
856: dbms_sql.column_value(l_cursor_id,6,l_grd_id);
857: -- Bug#2459352 Checking whether the employee belongs to federal org or not.
858:
859: open c_fed_employee(l_pos_id);
860: fetch c_fed_employee into l_pos_grade_ei_id;

Line 863: l_cursor_kff_id := DBMS_SQL.OPEN_CURSOR;

859: open c_fed_employee(l_pos_id);
860: fetch c_fed_employee into l_pos_grade_ei_id;
861: IF c_fed_employee%found then
862:
863: l_cursor_kff_id := DBMS_SQL.OPEN_CURSOR;
864:
865: DBMS_SQL.PARSE(l_cursor_kff_id,l_kff_select,DBMS_SQL.NATIVE);
866: If l_pos_exists then
867: For bus_gp_rec in c_business_group_id loop

Line 865: DBMS_SQL.PARSE(l_cursor_kff_id,l_kff_select,DBMS_SQL.NATIVE);

861: IF c_fed_employee%found then
862:
863: l_cursor_kff_id := DBMS_SQL.OPEN_CURSOR;
864:
865: DBMS_SQL.PARSE(l_cursor_kff_id,l_kff_select,DBMS_SQL.NATIVE);
866: If l_pos_exists then
867: For bus_gp_rec in c_business_group_id loop
868: l_business_group_id := bus_gp_rec.business_group_id;
869: End loop;

Line 871: DBMS_SQL.bind_variable(l_cursor_kff_id,'pos_id',l_pos_id);

867: For bus_gp_rec in c_business_group_id loop
868: l_business_group_id := bus_gp_rec.business_group_id;
869: End loop;
870:
871: DBMS_SQL.bind_variable(l_cursor_kff_id,'pos_id',l_pos_id);
872: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
873: DBMS_SQL.bind_variable(l_cursor_kff_id,'effective_date',l_effective_date);
874: End if;
875: If l_job_exists then

Line 872: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);

868: l_business_group_id := bus_gp_rec.business_group_id;
869: End loop;
870:
871: DBMS_SQL.bind_variable(l_cursor_kff_id,'pos_id',l_pos_id);
872: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
873: DBMS_SQL.bind_variable(l_cursor_kff_id,'effective_date',l_effective_date);
874: End if;
875: If l_job_exists then
876: For bus_gp_rec in c_business_group_id loop

Line 873: DBMS_SQL.bind_variable(l_cursor_kff_id,'effective_date',l_effective_date);

869: End loop;
870:
871: DBMS_SQL.bind_variable(l_cursor_kff_id,'pos_id',l_pos_id);
872: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
873: DBMS_SQL.bind_variable(l_cursor_kff_id,'effective_date',l_effective_date);
874: End if;
875: If l_job_exists then
876: For bus_gp_rec in c_business_group_id loop
877: l_business_group_id := bus_gp_rec.business_group_id;

Line 879: DBMS_SQL.bind_variable(l_cursor_kff_id,'job_id',l_job_id);

875: If l_job_exists then
876: For bus_gp_rec in c_business_group_id loop
877: l_business_group_id := bus_gp_rec.business_group_id;
878: End loop;
879: DBMS_SQL.bind_variable(l_cursor_kff_id,'job_id',l_job_id);
880: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
881: End if;
882: l_numrows_kff := DBMS_SQL.EXECUTE(l_cursor_kff_id);
883: Loop

Line 880: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);

876: For bus_gp_rec in c_business_group_id loop
877: l_business_group_id := bus_gp_rec.business_group_id;
878: End loop;
879: DBMS_SQL.bind_variable(l_cursor_kff_id,'job_id',l_job_id);
880: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
881: End if;
882: l_numrows_kff := DBMS_SQL.EXECUTE(l_cursor_kff_id);
883: Loop
884: If DBMS_SQL.FETCH_ROWS(l_cursor_kff_id) = 0 then

Line 882: l_numrows_kff := DBMS_SQL.EXECUTE(l_cursor_kff_id);

878: End loop;
879: DBMS_SQL.bind_variable(l_cursor_kff_id,'job_id',l_job_id);
880: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
881: End if;
882: l_numrows_kff := DBMS_SQL.EXECUTE(l_cursor_kff_id);
883: Loop
884: If DBMS_SQL.FETCH_ROWS(l_cursor_kff_id) = 0 then
885: exit;
886: Else

Line 884: If DBMS_SQL.FETCH_ROWS(l_cursor_kff_id) = 0 then

880: DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
881: End if;
882: l_numrows_kff := DBMS_SQL.EXECUTE(l_cursor_kff_id);
883: Loop
884: If DBMS_SQL.FETCH_ROWS(l_cursor_kff_id) = 0 then
885: exit;
886: Else
887: l_cursor_pos_grp1_id := DBMS_SQL.OPEN_CURSOR;
888: DBMS_SQL.PARSE(l_cursor_pos_grp1_id,l_pos_grp1_select,DBMS_SQL.NATIVE);

Line 887: l_cursor_pos_grp1_id := DBMS_SQL.OPEN_CURSOR;

883: Loop
884: If DBMS_SQL.FETCH_ROWS(l_cursor_kff_id) = 0 then
885: exit;
886: Else
887: l_cursor_pos_grp1_id := DBMS_SQL.OPEN_CURSOR;
888: DBMS_SQL.PARSE(l_cursor_pos_grp1_id,l_pos_grp1_select,DBMS_SQL.NATIVE);
889:
890: -- Get POS gRP1 from history
891: ghr_history_fetch.fetch_positionei

Line 888: DBMS_SQL.PARSE(l_cursor_pos_grp1_id,l_pos_grp1_select,DBMS_SQL.NATIVE);

884: If DBMS_SQL.FETCH_ROWS(l_cursor_kff_id) = 0 then
885: exit;
886: Else
887: l_cursor_pos_grp1_id := DBMS_SQL.OPEN_CURSOR;
888: DBMS_SQL.PARSE(l_cursor_pos_grp1_id,l_pos_grp1_select,DBMS_SQL.NATIVE);
889:
890: -- Get POS gRP1 from history
891: ghr_history_fetch.fetch_positionei
892: (p_position_id => l_pos_id,

Line 898: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'POI',l_pos_ei_data.poei_information3);

894: p_date_effective => l_effective_date,
895: p_pos_ei_data => l_pos_ei_data
896: );
897: If l_poi_exists then
898: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'POI',l_pos_ei_data.poei_information3);
899: End if;
900: If l_ofs_exists then
901: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'OFS',l_pos_ei_data.poei_information4);
902: End if;

Line 901: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'OFS',l_pos_ei_data.poei_information4);

897: If l_poi_exists then
898: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'POI',l_pos_ei_data.poei_information3);
899: End if;
900: If l_ofs_exists then
901: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'OFS',l_pos_ei_data.poei_information4);
902: End if;
903: If l_ors_exists then
904: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'ORS',l_pos_ei_data.poei_information5);
905: End if;

Line 904: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'ORS',l_pos_ei_data.poei_information5);

900: If l_ofs_exists then
901: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'OFS',l_pos_ei_data.poei_information4);
902: End if;
903: If l_ors_exists then
904: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'ORS',l_pos_ei_data.poei_information5);
905: End if;
906: l_pos_ei_data := Null;
907: l_numrows_pos_grp1 := DBMS_SQL.EXECUTE(l_cursor_pos_grp1_id);
908: Loop

Line 907: l_numrows_pos_grp1 := DBMS_SQL.EXECUTE(l_cursor_pos_grp1_id);

903: If l_ors_exists then
904: DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'ORS',l_pos_ei_data.poei_information5);
905: End if;
906: l_pos_ei_data := Null;
907: l_numrows_pos_grp1 := DBMS_SQL.EXECUTE(l_cursor_pos_grp1_id);
908: Loop
909: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp1_id) = 0 then
910: exit;
911: Else

Line 909: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp1_id) = 0 then

905: End if;
906: l_pos_ei_data := Null;
907: l_numrows_pos_grp1 := DBMS_SQL.EXECUTE(l_cursor_pos_grp1_id);
908: Loop
909: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp1_id) = 0 then
910: exit;
911: Else
912: l_cursor_pos_grp2_id := DBMS_SQL.OPEN_CURSOR;
913: DBMS_SQL.PARSE(l_cursor_pos_grp2_id,l_pos_grp2_select,DBMS_SQL.NATIVE);

Line 912: l_cursor_pos_grp2_id := DBMS_SQL.OPEN_CURSOR;

908: Loop
909: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp1_id) = 0 then
910: exit;
911: Else
912: l_cursor_pos_grp2_id := DBMS_SQL.OPEN_CURSOR;
913: DBMS_SQL.PARSE(l_cursor_pos_grp2_id,l_pos_grp2_select,DBMS_SQL.NATIVE);
914:
915: -- Get POS gRP2 from history
916: ghr_history_fetch.fetch_positionei

Line 913: DBMS_SQL.PARSE(l_cursor_pos_grp2_id,l_pos_grp2_select,DBMS_SQL.NATIVE);

909: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp1_id) = 0 then
910: exit;
911: Else
912: l_cursor_pos_grp2_id := DBMS_SQL.OPEN_CURSOR;
913: DBMS_SQL.PARSE(l_cursor_pos_grp2_id,l_pos_grp2_select,DBMS_SQL.NATIVE);
914:
915: -- Get POS gRP2 from history
916: ghr_history_fetch.fetch_positionei
917: (p_position_id => l_pos_id,

Line 923: DBMS_SQL.bind_variable(l_cursor_pos_grp2_id,'POC',l_pos_ei_data.poei_information3);

919: p_date_effective => l_effective_date,
920: p_pos_ei_data => l_pos_ei_data
921: );
922: If l_poc_exists then
923: DBMS_SQL.bind_variable(l_cursor_pos_grp2_id,'POC',l_pos_ei_data.poei_information3);
924: End if;
925:
926: --l_pos_ei_data := Null;
927: l_numrows_pos_grp2 := DBMS_SQL.EXECUTE(l_cursor_pos_grp2_id);

Line 927: l_numrows_pos_grp2 := DBMS_SQL.EXECUTE(l_cursor_pos_grp2_id);

923: DBMS_SQL.bind_variable(l_cursor_pos_grp2_id,'POC',l_pos_ei_data.poei_information3);
924: End if;
925:
926: --l_pos_ei_data := Null;
927: l_numrows_pos_grp2 := DBMS_SQL.EXECUTE(l_cursor_pos_grp2_id);
928: Loop
929: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp2_id) = 0 then
930: exit;
931: Else

Line 929: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp2_id) = 0 then

925:
926: --l_pos_ei_data := Null;
927: l_numrows_pos_grp2 := DBMS_SQL.EXECUTE(l_cursor_pos_grp2_id);
928: Loop
929: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp2_id) = 0 then
930: exit;
931: Else
932:
933: -- Open cursor

Line 934: l_cursor_pos_grd_id := DBMS_SQL.OPEN_CURSOR;

930: exit;
931: Else
932:
933: -- Open cursor
934: l_cursor_pos_grd_id := DBMS_SQL.OPEN_CURSOR;
935: DBMS_SQL.PARSE(l_cursor_pos_grd_id,l_pos_grd_select,DBMS_SQL.NATIVE);
936:
937: If l_pay_plan_exists or l_grade_exists then
938:

Line 935: DBMS_SQL.PARSE(l_cursor_pos_grd_id,l_pos_grd_select,DBMS_SQL.NATIVE);

931: Else
932:
933: -- Open cursor
934: l_cursor_pos_grd_id := DBMS_SQL.OPEN_CURSOR;
935: DBMS_SQL.PARSE(l_cursor_pos_grd_id,l_pos_grd_select,DBMS_SQL.NATIVE);
936:
937: If l_pay_plan_exists or l_grade_exists then
938:
939: -- Get POSITION VALID GRADE from history

Line 947: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'PP',hr_api.g_varchar2);

943: p_date_effective => l_effective_date,
944: p_pos_ei_data => l_pos_ei_data
945: );
946: If l_pay_plan_exists then
947: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'PP',hr_api.g_varchar2);
948: End if;
949: If l_grade_exists then
950: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'GRD',hr_api.g_varchar2);
951: End if;

Line 950: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'GRD',hr_api.g_varchar2);

946: If l_pay_plan_exists then
947: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'PP',hr_api.g_varchar2);
948: End if;
949: If l_grade_exists then
950: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'GRD',hr_api.g_varchar2);
951: End if;
952:
953: If l_pos_ei_data.poei_information3 is not null then
954: For grade_kff_rec in c_grade_kff(to_number(l_pos_ei_data.poei_information3)) loop

Line 957: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'PP',grade_kff_rec.pay_plan);

953: If l_pos_ei_data.poei_information3 is not null then
954: For grade_kff_rec in c_grade_kff(to_number(l_pos_ei_data.poei_information3)) loop
955: If l_pay_plan_exists then
956: If grade_kff_rec.pay_plan is not null then
957: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'PP',grade_kff_rec.pay_plan);
958: End if;
959: End if;
960:
961: If l_grade_exists then

Line 963: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'GRD',grade_kff_rec.grade_or_level);

959: End if;
960:
961: If l_grade_exists then
962: If grade_kff_rec.grade_or_level is not null then
963: DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'GRD',grade_kff_rec.grade_or_level);
964: End if;
965: End if;
966:
967: End loop;

Line 972: l_numrows_pos_grd := DBMS_SQL.EXECUTE(l_cursor_pos_grd_id);

968:
969: End if;
970: End if;
971:
972: l_numrows_pos_grd := DBMS_SQL.EXECUTE(l_cursor_pos_grd_id);
973: Loop
974: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grd_id) = 0 then
975: exit;
976: Else

Line 974: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grd_id) = 0 then

970: End if;
971:
972: l_numrows_pos_grd := DBMS_SQL.EXECUTE(l_cursor_pos_grd_id);
973: Loop
974: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grd_id) = 0 then
975: exit;
976: Else
977: l_cursor_loc_ddf_id := DBMS_SQL.OPEN_CURSOR;
978: DBMS_SQL.PARSE(l_cursor_loc_ddf_id,l_loc_ddf_select,DBMS_SQL.NATIVE);

Line 977: l_cursor_loc_ddf_id := DBMS_SQL.OPEN_CURSOR;

973: Loop
974: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grd_id) = 0 then
975: exit;
976: Else
977: l_cursor_loc_ddf_id := DBMS_SQL.OPEN_CURSOR;
978: DBMS_SQL.PARSE(l_cursor_loc_ddf_id,l_loc_ddf_select,DBMS_SQL.NATIVE);
979: If l_lei_exists then
980: for duty_station_code_rec in c_duty_station_code loop
981: l_duty_Station_code := duty_station_code_rec.duty_Station_code;

Line 978: DBMS_SQL.PARSE(l_cursor_loc_ddf_id,l_loc_ddf_select,DBMS_SQL.NATIVE);

974: If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grd_id) = 0 then
975: exit;
976: Else
977: l_cursor_loc_ddf_id := DBMS_SQL.OPEN_CURSOR;
978: DBMS_SQL.PARSE(l_cursor_loc_ddf_id,l_loc_ddf_select,DBMS_SQL.NATIVE);
979: If l_lei_exists then
980: for duty_station_code_rec in c_duty_station_code loop
981: l_duty_Station_code := duty_station_code_rec.duty_Station_code;
982: end loop;

Line 983: DBMS_SQL.bind_variable(l_cursor_loc_ddf_id,'DSC',l_duty_station_code);

979: If l_lei_exists then
980: for duty_station_code_rec in c_duty_station_code loop
981: l_duty_Station_code := duty_station_code_rec.duty_Station_code;
982: end loop;
983: DBMS_SQL.bind_variable(l_cursor_loc_ddf_id,'DSC',l_duty_station_code);
984: End if;
985: l_numrows_loc_ddf := DBMS_SQL.EXECUTE(l_cursor_loc_ddf_id);
986: Loop
987: If DBMS_SQL.FETCH_ROWS(l_cursor_loc_ddf_id) = 0 then

Line 985: l_numrows_loc_ddf := DBMS_SQL.EXECUTE(l_cursor_loc_ddf_id);

981: l_duty_Station_code := duty_station_code_rec.duty_Station_code;
982: end loop;
983: DBMS_SQL.bind_variable(l_cursor_loc_ddf_id,'DSC',l_duty_station_code);
984: End if;
985: l_numrows_loc_ddf := DBMS_SQL.EXECUTE(l_cursor_loc_ddf_id);
986: Loop
987: If DBMS_SQL.FETCH_ROWS(l_cursor_loc_ddf_id) = 0 then
988: exit;
989: Else

Line 987: If DBMS_SQL.FETCH_ROWS(l_cursor_loc_ddf_id) = 0 then

983: DBMS_SQL.bind_variable(l_cursor_loc_ddf_id,'DSC',l_duty_station_code);
984: End if;
985: l_numrows_loc_ddf := DBMS_SQL.EXECUTE(l_cursor_loc_ddf_id);
986: Loop
987: If DBMS_SQL.FETCH_ROWS(l_cursor_loc_ddf_id) = 0 then
988: exit;
989: Else
990: l_cursor_rating_id := DBMS_SQL.OPEN_CURSOR;
991: DBMS_SQL.PARSE(l_cursor_rating_id,l_rating_select,DBMS_SQL.NATIVE);

Line 990: l_cursor_rating_id := DBMS_SQL.OPEN_CURSOR;

986: Loop
987: If DBMS_SQL.FETCH_ROWS(l_cursor_loc_ddf_id) = 0 then
988: exit;
989: Else
990: l_cursor_rating_id := DBMS_SQL.OPEN_CURSOR;
991: DBMS_SQL.PARSE(l_cursor_rating_id,l_rating_select,DBMS_SQL.NATIVE);
992: If l_rating_exists then
993: --get rating record
994: ghr_api.return_special_information

Line 991: DBMS_SQL.PARSE(l_cursor_rating_id,l_rating_select,DBMS_SQL.NATIVE);

987: If DBMS_SQL.FETCH_ROWS(l_cursor_loc_ddf_id) = 0 then
988: exit;
989: Else
990: l_cursor_rating_id := DBMS_SQL.OPEN_CURSOR;
991: DBMS_SQL.PARSE(l_cursor_rating_id,l_rating_select,DBMS_SQL.NATIVE);
992: If l_rating_exists then
993: --get rating record
994: ghr_api.return_special_information
995: (p_person_id => l_per_id,

Line 1000: DBMS_SQL.bind_variable(l_cursor_rating_id,'RATING',l_sit.segment2);

996: p_structure_name => 'US Fed Perf Appraisal',
997: p_effective_date => l_effective_date,
998: p_special_info => l_sit
999: );
1000: DBMS_SQL.bind_variable(l_cursor_rating_id,'RATING',l_sit.segment2);
1001: End if;
1002: l_numrows_rating := DBMS_SQL.EXECUTE(l_cursor_rating_id);
1003: Loop
1004: If DBMS_SQL.FETCH_ROWS(l_cursor_rating_id) = 0 then

Line 1002: l_numrows_rating := DBMS_SQL.EXECUTE(l_cursor_rating_id);

998: p_special_info => l_sit
999: );
1000: DBMS_SQL.bind_variable(l_cursor_rating_id,'RATING',l_sit.segment2);
1001: End if;
1002: l_numrows_rating := DBMS_SQL.EXECUTE(l_cursor_rating_id);
1003: Loop
1004: If DBMS_SQL.FETCH_ROWS(l_cursor_rating_id) = 0 then
1005: exit;
1006: Else

Line 1004: If DBMS_SQL.FETCH_ROWS(l_cursor_rating_id) = 0 then

1000: DBMS_SQL.bind_variable(l_cursor_rating_id,'RATING',l_sit.segment2);
1001: End if;
1002: l_numrows_rating := DBMS_SQL.EXECUTE(l_cursor_rating_id);
1003: Loop
1004: If DBMS_SQL.FETCH_ROWS(l_cursor_rating_id) = 0 then
1005: exit;
1006: Else
1007: l_count := l_count + 1;
1008:

Line 1065: DBMS_SQL.CLOSE_CURSOR(l_cursor_rating_id);

1061:
1062: End if;
1063: l_duty_station_code := Null; -- Note : Move with appropriate loop
1064: End loop;
1065: DBMS_SQL.CLOSE_CURSOR(l_cursor_rating_id);
1066: End if;
1067: End loop;
1068: DBMS_SQL.CLOSE_CURSOR(l_cursor_loc_ddf_id);
1069: End if;

Line 1068: DBMS_SQL.CLOSE_CURSOR(l_cursor_loc_ddf_id);

1064: End loop;
1065: DBMS_SQL.CLOSE_CURSOR(l_cursor_rating_id);
1066: End if;
1067: End loop;
1068: DBMS_SQL.CLOSE_CURSOR(l_cursor_loc_ddf_id);
1069: End if;
1070: End loop;
1071: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grd_id);
1072: End if;

Line 1071: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grd_id);

1067: End loop;
1068: DBMS_SQL.CLOSE_CURSOR(l_cursor_loc_ddf_id);
1069: End if;
1070: End loop;
1071: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grd_id);
1072: End if;
1073: End loop;
1074: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp2_id);
1075: End if;

Line 1074: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp2_id);

1070: End loop;
1071: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grd_id);
1072: End if;
1073: End loop;
1074: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp2_id);
1075: End if;
1076: End loop;
1077: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp1_id);
1078: End if;

Line 1077: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp1_id);

1073: End loop;
1074: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp2_id);
1075: End if;
1076: End loop;
1077: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp1_id);
1078: End if;
1079: End loop;
1080: DBMS_SQL.CLOSE_CURSOR(l_cursor_kff_id);
1081: End If;

Line 1080: DBMS_SQL.CLOSE_CURSOR(l_cursor_kff_id);

1076: End loop;
1077: DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp1_id);
1078: End if;
1079: End loop;
1080: DBMS_SQL.CLOSE_CURSOR(l_cursor_kff_id);
1081: End If;
1082: close c_fed_employee;
1083: End if;
1084: End loop;

Line 1085: DBMS_SQL.CLOSE_CURSOR(l_cursor_id);

1081: End If;
1082: close c_fed_employee;
1083: End if;
1084: End loop;
1085: DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1086:
1087: /* Still to do */
1088: -- Set out parameters
1089: -- p_errbuf := ????