[Home] [Help]
29: createdByValue integer;
30: begin
31: select created_by
32: into createdByValue
33: from ame_approval_groups
34: where
35: approval_group_id = approvalGroupIdIn and
36: sysdate between start_date and
37: nvl(end_date - ame_util.oneSecond, sysdate) ;
122: raise;
123: return(null);
124: end getApprovalGroupOrderNumber;
125: function getDescription(approvalGroupIdIn in integer) return varchar2 as
126: description ame_approval_groups.description%type;
127: begin
128: select description
129: into description
130: from ame_approval_groups
126: description ame_approval_groups.description%type;
127: begin
128: select description
129: into description
130: from ame_approval_groups
131: where
132: approval_group_id = approvalGroupIdIn and
133: sysdate between start_date and
134: nvl(end_date - ame_util.oneSecond, sysdate) ;
151: approvalGroupId integer;
152: begin
153: select approval_group_id
154: into approvalGroupId
155: from ame_approval_groups
156: where
157: upper(name) = upper(nameIn) and
158: sysdate between start_date and
159: nvl(end_date - ame_util.oneSecond, sysdate) ;
276: return(null);
277: end getItemParameterName;
278: function getName(approvalGroupIdIn in integer,
279: effectiveDateIn in date default sysdate) return varchar2 as
280: name ame_approval_groups.name%type;
281: begin
282: if(approvalGroupIdIn = ame_util.nullInsertionGroupOrChainId) then
283: return('''no approval group'' and ''no chain of authority''');
284: end if;
283: return('''no approval group'' and ''no chain of authority''');
284: end if;
285: select name
286: into name
287: from ame_approval_groups
288: where
289: approval_group_id = approvalGroupIdIn and
290: nvl(effectiveDateIn, sysdate) between start_date and
291: nvl(end_date - ame_util.oneSecond, sysdate) ;
307: function getQueryString(approvalGroupIdIn in integer,
308: effectiveDateIn in date default sysdate) return varchar2 as
309: errorCode integer;
310: errorMessage ame_util.longestStringType;
311: queryString ame_approval_groups.query_string%type;
312: begin
313: select query_string
314: into queryString
315: from ame_approval_groups
311: queryString ame_approval_groups.query_string%type;
312: begin
313: select query_string
314: into queryString
315: from ame_approval_groups
316: where
317: approval_group_id = approvalGroupIdIn and
318: nvl(effectiveDateIn, sysdate) between start_date and
319: nvl(end_date - ame_util.oneSecond, sysdate) ;
349: raise;
350: return(null);
351: end getQueryString;
352: function getIsStatic(approvalGroupIdIn in integer) return varchar2 as
353: isStatic ame_approval_groups.is_static%type;
354: begin
355: select is_static
356: into isStatic
357: from ame_approval_groups
353: isStatic ame_approval_groups.is_static%type;
354: begin
355: select is_static
356: into isStatic
357: from ame_approval_groups
358: where approval_group_id = approvalGroupIdIn and
359: sysdate between start_date and
360: nvl(end_date - ame_util.oneSecond, sysdate) ;
361: return(isStatic);
407: stringStartDate varchar2(50);
408: begin
409: select start_date
410: into startDate
411: from ame_approval_groups
412: where
413: approval_group_id = approvalGroupIdIn and
414: sysdate between start_date and
415: nvl(end_date - ame_util.oneSecond, sysdate) ;
557: tempCount integer;
558: begin
559: select count(*)
560: into tempCount
561: from ame_approval_groups
562: where ame_approval_groups.approval_group_id = approvalGroupIdIn
563: and (nameIn is null or upper(name) = upper(nameIn))
564: and (descriptionIn is null or upper(description) = upper(descriptionIn))
565: and is_static = isStaticIn
558: begin
559: select count(*)
560: into tempCount
561: from ame_approval_groups
562: where ame_approval_groups.approval_group_id = approvalGroupIdIn
563: and (nameIn is null or upper(name) = upper(nameIn))
564: and (descriptionIn is null or upper(description) = upper(descriptionIn))
565: and is_static = isStaticIn
566: and (queryStringIn is null or upper(query_string) = upper(queryStringIn))
563: and (nameIn is null or upper(name) = upper(nameIn))
564: and (descriptionIn is null or upper(description) = upper(descriptionIn))
565: and is_static = isStaticIn
566: and (queryStringIn is null or upper(query_string) = upper(queryStringIn))
567: and sysdate between ame_approval_groups.start_date
568: and nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate);
569: if(tempCount = 0) then
570: return(true);
571: else
564: and (descriptionIn is null or upper(description) = upper(descriptionIn))
565: and is_static = isStaticIn
566: and (queryStringIn is null or upper(query_string) = upper(queryStringIn))
567: and sysdate between ame_approval_groups.start_date
568: and nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate);
569: if(tempCount = 0) then
570: return(true);
571: else
572: return(false);
607: end if;
608: select count(*)
609: into tempCount
610: from
611: ame_approval_groups,
612: ame_approval_group_config
613: where
614: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
615: ame_approval_groups.approval_group_id = approvalGroupIdIn and
610: from
611: ame_approval_groups,
612: ame_approval_group_config
613: where
614: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
615: ame_approval_groups.approval_group_id = approvalGroupIdIn and
616: ame_approval_group_config.application_id = applicationIdIn and
617: ame_approval_group_config.voting_regime = votingRegimeIn and
618: ame_approval_group_config.order_number = orderNumberIn and
611: ame_approval_groups,
612: ame_approval_group_config
613: where
614: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
615: ame_approval_groups.approval_group_id = approvalGroupIdIn and
616: ame_approval_group_config.application_id = applicationIdIn and
617: ame_approval_group_config.voting_regime = votingRegimeIn and
618: ame_approval_group_config.order_number = orderNumberIn and
619: (nameIn is null or upper(name) = upper(nameIn)) and
619: (nameIn is null or upper(name) = upper(nameIn)) and
620: (descriptionIn is null or upper(description) = upper(descriptionIn)) and
621: is_static = isStaticIn and
622: (queryStringIn is null or upper(query_string) = upper(queryStringIn)) and
623: sysdate between ame_approval_groups.start_date and
624: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
625: sysdate between ame_approval_group_config.start_date and
626: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate);
627: if(tempCount = 0 or groupHasBeenUpdated) then
620: (descriptionIn is null or upper(description) = upper(descriptionIn)) and
621: is_static = isStaticIn and
622: (queryStringIn is null or upper(query_string) = upper(queryStringIn)) and
623: sysdate between ame_approval_groups.start_date and
624: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
625: sysdate between ame_approval_group_config.start_date and
626: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate);
627: if(tempCount = 0 or groupHasBeenUpdated) then
628: return(true);
683: return(true);
684: end isInUse;
685: function isStatic(approvalGroupIdIn in integer,
686: effectiveDateIn in date default sysdate) return boolean as
687: isStatic ame_approval_groups.is_static%type;
688: begin
689: select is_static
690: into isStatic
691: from ame_approval_groups
687: isStatic ame_approval_groups.is_static%type;
688: begin
689: select is_static
690: into isStatic
691: from ame_approval_groups
692: where approval_group_id = approvalGroupIdIn and
693: nvl(effectiveDateIn, sysdate) between start_date and
694: nvl(end_date - ame_util.oneSecond, sysdate);
695: if(isStatic = ame_util.booleanTrue) then
768: processingDate := sysdate;
769: begin
770: select approval_group_id
771: into approvalGroupId
772: from ame_approval_groups
773: where
774: (approvalGroupIdIn is null or approval_group_id <> approvalGroupIdIn) and
775: upper(name) = upper(nameIn) and
776: sysdate between start_date and
782: end if;
783: exception
784: when no_data_found then null;
785: end;
786: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_groups',
787: columnNameIn => 'name',
788: argumentIn => nameIn)) then
789: raise nameLengthException;
790: end if;
787: columnNameIn => 'name',
788: argumentIn => nameIn)) then
789: raise nameLengthException;
790: end if;
791: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_approval_groups',
792: columnNameIn => 'description',
793: argumentIn => descriptionIn)) then
794: raise descriptionLengthException;
795: end if;
801: */
802: currentUserId := ame_util.getCurrentUserId;
803: if(approvalGroupIdIn is null) then
804: createdBy := currentUserId;
805: select ame_approval_groups_s.nextval into approvalGroupId from dual;
806: else
807: approvalGroupId := approvalGroupIdIn;
808: select count(*)
809: into tempCount
806: else
807: approvalGroupId := approvalGroupIdIn;
808: select count(*)
809: into tempCount
810: from ame_approval_groups
811: where
812: approval_group_id = approvalGroupIdIn and
813: created_by = ame_util.seededDataCreatedById;
814: if(tempCount > 0) then
876: finalizeIn => true);
877: end if;
878: end loop;
879: end if;
880: insert into ame_approval_groups(approval_group_id,
881: name,
882: created_by,
883: creation_date,
884: last_updated_by,
912: errorCode := -20001;
913: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
914: messageNameIn => 'AME_400196_APG_NAME_LONG',
915: tokenNameOneIn => 'COLUMN_LENGTH',
916: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
917: columnNameIn => 'name'));
918: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
919: routineNameIn => 'new',
920: exceptionNumberIn => errorCode,
927: errorCode := -20001;
928: errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
929: messageNameIn => 'AME_400197_APG_DESC_LONG',
930: tokenNameOneIn => 'COLUMN_LENGTH',
931: tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_approval_groups',
932: columnNameIn => 'description'));
933: ame_util.runtimeException(packageNameIn => 'ame_approval_group_pkg',
934: routineNameIn => 'new',
935: exceptionNumberIn => errorCode,
975: newEndDateIn in date default null,
976: parentVersionStartDateIn in date) return integer as
977: cursor startDateCursor is
978: select start_date
979: from ame_approval_groups
980: where
981: approval_group_id = approvalGroupIdIn and
982: sysdate between start_date and
983: nvl(end_date - ame_util.oneSecond, sysdate)
1399: begin
1400: currentUserId := ame_util.getCurrentUserId;
1401: endDate := newVersionStartDateIn - ame_util.oneSecond;
1402: newStartDate := newVersionStartDateIn;
1403: update ame_approval_groups
1404: set
1405: last_updated_by = currentUserId,
1406: last_update_date = endDate,
1407: last_update_login = currentUserId,
1449: applicationIdIn in integer,
1450: finalizeIn in boolean default false) as
1451: cursor startDateCursor is
1452: select start_date
1453: from ame_approval_groups
1454: where
1455: approval_group_id = approvalGroupIdIn and
1456: sysdate between start_date and
1457: nvl(end_date - ame_util.oneSecond, sysdate)
1465: sysdate between start_date and
1466: nvl(end_date - ame_util.oneSecond, sysdate)
1467: for update;
1468: currentUserId integer;
1469: description ame_approval_groups.description%type;
1470: errorCode integer;
1471: errorMessage ame_util.longestStringType;
1472: name ame_approval_groups.name%type;
1473: objectVersionNoDataException exception;
1468: currentUserId integer;
1469: description ame_approval_groups.description%type;
1470: errorCode integer;
1471: errorMessage ame_util.longestStringType;
1472: name ame_approval_groups.name%type;
1473: objectVersionNoDataException exception;
1474: startDate date;
1475: startDate2 date;
1476: tempCount integer;
1768: orderNumberUniqueIn in varchar2 default null,
1769: parentVersionStartDateIn in date) as
1770: cursor startDateCursor is
1771: select start_date
1772: from ame_approval_groups
1773: where
1774: approval_group_id = approvalGroupIdIn and
1775: sysdate between start_date and
1776: nvl(end_date - ame_util.oneSecond, sysdate)
1780: currentUserId integer;
1781: endDate date;
1782: errorCode integer;
1783: errorMessage ame_util.longestStringType;
1784: groupDescription ame_approval_groups.description%type;
1785: groupIsStatic ame_approval_groups.is_static%type;
1786: groupName ame_approval_groups.name%type;
1787: groupQueryString ame_approval_groups.query_string%type;
1788: newStartDate date;
1781: endDate date;
1782: errorCode integer;
1783: errorMessage ame_util.longestStringType;
1784: groupDescription ame_approval_groups.description%type;
1785: groupIsStatic ame_approval_groups.is_static%type;
1786: groupName ame_approval_groups.name%type;
1787: groupQueryString ame_approval_groups.query_string%type;
1788: newStartDate date;
1789: objectVersionNoDataException exception;
1782: errorCode integer;
1783: errorMessage ame_util.longestStringType;
1784: groupDescription ame_approval_groups.description%type;
1785: groupIsStatic ame_approval_groups.is_static%type;
1786: groupName ame_approval_groups.name%type;
1787: groupQueryString ame_approval_groups.query_string%type;
1788: newStartDate date;
1789: objectVersionNoDataException exception;
1790: oldOrderNumber integer;
1783: errorMessage ame_util.longestStringType;
1784: groupDescription ame_approval_groups.description%type;
1785: groupIsStatic ame_approval_groups.is_static%type;
1786: groupName ame_approval_groups.name%type;
1787: groupQueryString ame_approval_groups.query_string%type;
1788: newStartDate date;
1789: objectVersionNoDataException exception;
1790: oldOrderNumber integer;
1791: parameter ame_approval_group_items.parameter%type;
1829: groupName := getName(approvalGroupIdIn => approvalGroupIdIn);
1830: groupDescription := getDescription(approvalGroupIdIn => approvalGroupIdIn);
1831: groupIsStatic := getIsStatic(approvalGroupIdIn => approvalGroupIdIn);
1832: groupQueryString := getQueryString(approvalGroupIdIn => approvalGroupIdIn);
1833: update ame_approval_groups
1834: set
1835: last_updated_by = currentUserId,
1836: last_update_date = endDate,
1837: last_update_login = currentUserId,
2041: cursor groupCursor is
2042: select
2043: approval_group_id,
2044: name
2045: from ame_approval_groups
2046: where
2047: sysdate between start_date and
2048: nvl(end_date - ame_util.oneSecond, sysdate) ;
2049: tempIndex integer;
2094: sysdate between start_date and
2095: nvl(end_date - ame_util.oneSecond, sysdate)
2096: order by order_number;
2097: tempindex integer;
2098: tempDescription ame_approval_groups.description%type;
2099: tempFirstName per_all_people_f.first_name%type;
2100: tempItemId integer;
2101: tempLastName per_all_people_f.last_name%type;
2102: tempName ame_approval_groups.name%type;
2098: tempDescription ame_approval_groups.description%type;
2099: tempFirstName per_all_people_f.first_name%type;
2100: tempItemId integer;
2101: tempLastName per_all_people_f.last_name%type;
2102: tempName ame_approval_groups.name%type;
2103: tempParameter ame_approval_group_items.parameter%type;
2104: tempParameterName ame_approval_group_items.parameter_name%type;
2105: tempUserName fnd_user.user_name%type;
2106: tempRowNumber integer;
2150: end getApprovalGroupItemList;
2151: procedure getApprovalGroupList(groupListOut out nocopy ame_util.idList) as
2152: cursor groupCursor is
2153: select approval_group_id
2154: from ame_approval_groups
2155: where
2156: sysdate between start_date and
2157: nvl(end_date - ame_util.oneSecond, sysdate);
2158: tempIndex integer;
2174: end getApprovalGroupList;
2175: procedure getApprovalGroupList2(applicationIdIn in integer,
2176: groupListOut out nocopy ame_util.idList) as
2177: cursor groupCursor is
2178: select ame_approval_groups.approval_group_id
2179: from ame_approval_groups,
2180: ame_approval_group_config
2181: where
2182: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
2175: procedure getApprovalGroupList2(applicationIdIn in integer,
2176: groupListOut out nocopy ame_util.idList) as
2177: cursor groupCursor is
2178: select ame_approval_groups.approval_group_id
2179: from ame_approval_groups,
2180: ame_approval_group_config
2181: where
2182: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
2183: ame_approval_group_config.application_id = applicationIdIn and
2178: select ame_approval_groups.approval_group_id
2179: from ame_approval_groups,
2180: ame_approval_group_config
2181: where
2182: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
2183: ame_approval_group_config.application_id = applicationIdIn and
2184: sysdate between ame_approval_groups.start_date and
2185: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
2186: sysdate between ame_approval_group_config.start_date and
2180: ame_approval_group_config
2181: where
2182: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
2183: ame_approval_group_config.application_id = applicationIdIn and
2184: sysdate between ame_approval_groups.start_date and
2185: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
2186: sysdate between ame_approval_group_config.start_date and
2187: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate)
2188: order by ame_approval_group_config.order_number;
2181: where
2182: ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
2183: ame_approval_group_config.application_id = applicationIdIn and
2184: sysdate between ame_approval_groups.start_date and
2185: nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
2186: sysdate between ame_approval_group_config.start_date and
2187: nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate)
2188: order by ame_approval_group_config.order_number;
2189: tempIndex integer;
2215: approval_group_id = approvalGroupIdIn and
2216: sysdate between start_date and
2217: nvl(end_date - ame_util.oneSecond, sysdate)
2218: order by order_number;
2219: isStatic ame_approval_groups.is_static%type;
2220: queryString ame_util.longestStringType;
2221: recursionParameterNames ame_util.stringList;
2222: recursionParameters ame_util.longStringList;
2223: recursionUpperLimit integer;
2228: query_string
2229: into
2230: isStatic,
2231: queryString
2232: from ame_approval_groups
2233: where
2234: approval_group_id = approvalGroupIdIn and
2235: sysdate between start_date and
2236: nvl(end_date - ame_util.oneSecond, sysdate) ;
2692: procedure remove(approvalGroupIdIn in integer,
2693: parentVersionStartDateIn in date) as
2694: cursor startDateCursor is
2695: select start_date
2696: from ame_approval_groups
2697: where
2698: approval_group_id = approvalGroupIdIn and
2699: sysdate between start_date and
2700: nvl(end_date - ame_util.oneSecond, sysdate)
2748: finalizeIn => false);
2749: end if;
2750: end loop;
2751: /* End-date approval group itself.*/
2752: update ame_approval_groups
2753: set
2754: last_updated_by = currentUserId,
2755: last_update_date = processingDate,
2756: last_update_login = currentUserId,
2866: approvalGroupItemsIn in ame_util.idList,
2867: parentVersionStartDateIn in date) as
2868: cursor startDateCursor is
2869: select start_date
2870: from ame_approval_groups
2871: where
2872: approval_group_id = approvalGroupIdIn and
2873: sysdate between start_date and
2874: nvl(end_date - ame_util.oneSecond, sysdate)
2880: sysdate between start_date and
2881: nvl(end_date - ame_util.oneSecond, sysdate)
2882: for update of order_number
2883: order by order_number;
2884: approvalGroupId ame_approval_groups.approval_group_id%type;
2885: approvalGroupItemCount integer;
2886: approvalGroupItemList ame_util.idList;
2887: currentUserId integer;
2888: errorCode integer;
2886: approvalGroupItemList ame_util.idList;
2887: currentUserId integer;
2888: errorCode integer;
2889: errorMessage ame_util.longestStringType;
2890: groupDescription ame_approval_groups.description%type;
2891: groupName ame_approval_groups.name%type;
2892: isStatic ame_approval_groups.is_static%type;
2893: itemOrderNumber integer;
2894: objectVersionNoDataException exception;
2887: currentUserId integer;
2888: errorCode integer;
2889: errorMessage ame_util.longestStringType;
2890: groupDescription ame_approval_groups.description%type;
2891: groupName ame_approval_groups.name%type;
2892: isStatic ame_approval_groups.is_static%type;
2893: itemOrderNumber integer;
2894: objectVersionNoDataException exception;
2895: queryString ame_approval_groups.query_string%type;
2888: errorCode integer;
2889: errorMessage ame_util.longestStringType;
2890: groupDescription ame_approval_groups.description%type;
2891: groupName ame_approval_groups.name%type;
2892: isStatic ame_approval_groups.is_static%type;
2893: itemOrderNumber integer;
2894: objectVersionNoDataException exception;
2895: queryString ame_approval_groups.query_string%type;
2896: startDate date;
2891: groupName ame_approval_groups.name%type;
2892: isStatic ame_approval_groups.is_static%type;
2893: itemOrderNumber integer;
2894: objectVersionNoDataException exception;
2895: queryString ame_approval_groups.query_string%type;
2896: startDate date;
2897: tempIndex integer;
2898: processingDate date;
2899: begin
2937: groupName := getName(approvalGroupIdIn => approvalGroupIdIn);
2938: groupDescription := getDescription(approvalGroupIdIn => approvalGroupIdIn);
2939: isStatic := getIsStatic(approvalGroupIdIn => approvalGroupIdIn);
2940: queryString := getQueryString(approvalGroupIdIn => approvalGroupIdIn);
2941: update ame_approval_groups
2942: set
2943: last_updated_by = currentUserId,
2944: last_update_date = processingDate,
2945: last_update_login = currentUserId,