[Home] [Help]
789: Produce error if:
790: 1. approval option says "Don't approve this unless everything below is approved". There's at least one process below that is NOT approved.
791: 2. there's at least one risk associated (to this process / any process in the downward hierarchy, depending on the approval option) that does not have an approved revision.
792: 3. there's at least one control associated (to this process / any process in the downward hierarchy, depending on the approval option) that does not have an approved revision.
793: 4. the process is non-standard, and the standard variation does not have an approved revision.
794: 5. the process is non-standard and the list of children in the latest hierarchy is not the same as the list of children recorded in the variations table.
795: */
796: procedure check_approval_subm_eligib(
797: p_process_id in number,
790: 1. approval option says "Don't approve this unless everything below is approved". There's at least one process below that is NOT approved.
791: 2. there's at least one risk associated (to this process / any process in the downward hierarchy, depending on the approval option) that does not have an approved revision.
792: 3. there's at least one control associated (to this process / any process in the downward hierarchy, depending on the approval option) that does not have an approved revision.
793: 4. the process is non-standard, and the standard variation does not have an approved revision.
794: 5. the process is non-standard and the list of children in the latest hierarchy is not the same as the list of children recorded in the variations table.
795: */
796: procedure check_approval_subm_eligib(
797: p_process_id in number,
798: p_result out nocopy varchar2,
895: st_var_pid number;
896: l_dummy number;
897:
898: begin
899: -- standard variation treatment
900: -- I could probably have made good use for cursor variables here
901:
902: if approve_option = 'B' then
903: for process_list_rec in process_list(p_process_id) loop
903: for process_list_rec in process_list(p_process_id) loop
904:
905: begin
906:
907: select a.standard_process_flag, b.process_id
908: into std_process, st_var_pid
909: from amw_process a, amw_process b
910: where a.process_id = process_list_rec.parent_child_id
911: and a.end_date is null
908: into std_process, st_var_pid
909: from amw_process a, amw_process b
910: where a.process_id = process_list_rec.parent_child_id
911: and a.end_date is null
912: and b.process_rev_id = a.standard_variation;
913:
914: exception
915: when no_data_found then --process is standard/non-std but no var defined
916: std_process := 'Y';
911: and a.end_date is null
912: and b.process_rev_id = a.standard_variation;
913:
914: exception
915: when no_data_found then --process is standard/non-std but no var defined
916: std_process := 'Y';
917: end;
918:
919: if std_process = 'N' then -- if process is non-standard
915: when no_data_found then --process is standard/non-std but no var defined
916: std_process := 'Y';
917: end;
918:
919: if std_process = 'N' then -- if process is non-standard
920: begin
921: select 1
922: into l_dummy
923: from amw_process
933: p_out_mesg := fnd_message.get;
934: p_result := 'N';
935: return;
936: end;
937: end if; -- if process is non-standard
938:
939: end loop;
940: else
941: begin
938:
939: end loop;
940: else
941: begin
942: select a.standard_process_flag, b.process_id
943: into std_process, st_var_pid
944: from amw_process a, amw_process b
945: where a.process_id = p_process_id
946: and a.end_date is null
943: into std_process, st_var_pid
944: from amw_process a, amw_process b
945: where a.process_id = p_process_id
946: and a.end_date is null
947: and b.process_rev_id = a.standard_variation;
948: exception
949: when no_data_found then --process is standard/non-std but no var defined
950: std_process := 'Y';
951: end;
945: where a.process_id = p_process_id
946: and a.end_date is null
947: and b.process_rev_id = a.standard_variation;
948: exception
949: when no_data_found then --process is standard/non-std but no var defined
950: std_process := 'Y';
951: end;
952:
953: if std_process = 'N' then -- if process is non-standard
949: when no_data_found then --process is standard/non-std but no var defined
950: std_process := 'Y';
951: end;
952:
953: if std_process = 'N' then -- if process is non-standard
954:
955: begin
956: select 1
957: into l_dummy
969: p_result := 'N';
970: return;
971: end;
972:
973: end if; -- if process is non-standard
974:
975: end if;
976:
977: end prod_err_unappr_nsvar;
991: union
992: select pid from dual;
993:
994: std_process varchar2(1);
995: l_std_variation amw_process.STANDARD_VARIATION%TYPE;
996: l_dummy number;
997:
998: begin
999: -- standard variation treatment
995: l_std_variation amw_process.STANDARD_VARIATION%TYPE;
996: l_dummy number;
997:
998: begin
999: -- standard variation treatment
1000: -- I could probably have made good use for cursor variables here
1001:
1002: if approve_option = 'B' then
1003: for process_list_rec in process_list(p_process_id) loop
1001:
1002: if approve_option = 'B' then
1003: for process_list_rec in process_list(p_process_id) loop
1004:
1005: select standard_process_flag,standard_variation
1006: into std_process,l_std_variation
1007: from amw_process
1008: where process_id = process_list_rec.parent_child_id
1009: and end_date is null;
1018: and up_down_ind = 'D'
1019: and hierarchy_type = 'L'
1020: and parent_child_id not in
1021: (select NON_STD_CHILD_ID
1022: from AMW_NONSTANDARD_VARIATIONS_B
1023: where NON_STD_PROCESS_ID = process_list_rec.parent_child_id
1024: and NON_STD_PROCESS_REV_NUM = (select revision_number
1025: from amw_process
1026: where process_id = process_list_rec.parent_child_id
1045:
1046: begin
1047: select NON_STD_CHILD_ID
1048: into l_dummy
1049: from AMW_NONSTANDARD_VARIATIONS_B
1050: where NON_STD_PROCESS_ID = process_list_rec.parent_child_id
1051: and NON_STD_PROCESS_REV_NUM = (select revision_number
1052: from amw_process
1053: where process_id = process_list_rec.parent_child_id
1075: p_result := 'N';
1076: return;
1077: end;
1078:
1079: end if; -- if process is non-standard
1080:
1081: end loop;
1082: else
1083: select standard_process_flag,standard_variation
1079: end if; -- if process is non-standard
1080:
1081: end loop;
1082: else
1083: select standard_process_flag,standard_variation
1084: into std_process,l_std_variation
1085: from amw_process
1086: where process_id = p_process_id
1087: and end_date is null;
1096: and up_down_ind = 'D'
1097: and hierarchy_type = 'L'
1098: and parent_child_id not in
1099: (select NON_STD_CHILD_ID
1100: from AMW_NONSTANDARD_VARIATIONS_B
1101: where NON_STD_PROCESS_ID = p_process_id
1102: and NON_STD_PROCESS_REV_NUM = (select revision_number
1103: from amw_process
1104: where process_id = p_process_id
1123:
1124: begin
1125: select NON_STD_CHILD_ID
1126: into l_dummy
1127: from AMW_NONSTANDARD_VARIATIONS_B
1128: where NON_STD_PROCESS_ID = p_process_id
1129: and NON_STD_PROCESS_REV_NUM = (select revision_number
1130: from amw_process
1131: where process_id = p_process_id
1153: p_result := 'N';
1154: return;
1155: end;
1156:
1157: end if; -- if process is non-standard
1158:
1159: end if; -- approval option B
1160:
1161: end prod_err_modified_nschildlist;