[Home] [Help]
27: raise;
28: return(true); /* conservative: avoids allowing deletion if might still be in use */
29: end actionTypeIsInUse;
30: function getActionTypeDescQuery(actionTypeIdIn in integer) return varchar2 as
31: descriptionQuery ame_action_types.description_query%type;
32: begin
33: select description_query
34: into descriptionQuery
35: from ame_action_types
31: descriptionQuery ame_action_types.description_query%type;
32: begin
33: select description_query
34: into descriptionQuery
35: from ame_action_types
36: where
37: action_type_id = actionTypeIdIn and
38: sysdate between start_date and
39: nvl(end_date - ame_util.oneSecond, sysdate) ;
51: raise;
52: return(null);
53: end getActionTypeDescQuery;
54: function getActionTypeDynamicDesc(actionTypeIdIn in integer) return varchar2 as
55: dynamicDescription ame_action_types.dynamic_description%type;
56: begin
57: select dynamic_description
58: into dynamicDescription
59: from ame_action_types
55: dynamicDescription ame_action_types.dynamic_description%type;
56: begin
57: select dynamic_description
58: into dynamicDescription
59: from ame_action_types
60: where
61: action_type_id = actionTypeIdIn and
62: sysdate between start_date and
63: nvl(end_date - ame_util.oneSecond, sysdate) ;
103: actionTypeId integer;
104: begin
105: select action_type_id
106: into actionTypeId
107: from ame_action_types
108: where
109: upper(name) = upper(actionTypeNameIn) and
110: sysdate between start_date and
111: nvl(end_date - ame_util.oneSecond, sysdate) ;
123: raise;
124: return(null);
125: end getActionTypeIdByName;
126: function getActionTypeDescription(actionTypeIdIn in integer) return varchar2 as
127: description ame_action_types.description%type;
128: begin
129: select description
130: into description
131: from ame_action_types
127: description ame_action_types.description%type;
128: begin
129: select description
130: into description
131: from ame_action_types
132: where
133: action_type_id = actionTypeIdIn and
134: sysdate between start_date and
135: nvl(end_date - ame_util.oneSecond, sysdate) ;
154: select max(ame_action_type_config.order_number)
155: into orderNumber
156: from ame_action_type_config,
157: ame_action_type_usages,
158: ame_action_types
159: where
160: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
161: ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
162: ame_action_type_usages.rule_type = ruleTypeIn and
156: from ame_action_type_config,
157: ame_action_type_usages,
158: ame_action_types
159: where
160: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
161: ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
162: ame_action_type_usages.rule_type = ruleTypeIn and
163: ame_action_type_config.application_id = applicationIdIn and
164: sysdate between ame_action_type_config.start_date and
164: sysdate between ame_action_type_config.start_date and
165: nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
166: sysdate between ame_action_type_usages.start_date and
167: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
168: sysdate between ame_action_types.start_date and
169: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
170: return(orderNumber);
171: exception
172: when others then
165: nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
166: sysdate between ame_action_type_usages.start_date and
167: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
168: sysdate between ame_action_types.start_date and
169: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
170: return(orderNumber);
171: exception
172: when others then
173: rollback;
178: raise;
179: return(null);
180: end getActionTypeMaxOrderNumber;
181: function getActionTypeName(actionTypeIdIn in integer) return varchar2 as
182: name ame_action_types.name%type;
183: begin
184: if(actionTypeIdIn = ame_util.nullInsertionActionTypeId) then
185: return('no action type');
186: end if;
185: return('no action type');
186: end if;
187: select name
188: into name
189: from ame_action_types
190: where
191: action_type_id = actionTypeIdIn and
192: sysdate between start_date and
193: nvl(end_date - ame_util.oneSecond, sysdate) ;
205: raise;
206: return(null);
207: end getActionTypeName;
208: function getActionTypeNameByActionId(actionIdIn in integer) return varchar2 as
209: name ame_action_types.name%type;
210: begin
211: select ame_action_types.name
212: into name
213: from ame_actions,
207: end getActionTypeName;
208: function getActionTypeNameByActionId(actionIdIn in integer) return varchar2 as
209: name ame_action_types.name%type;
210: begin
211: select ame_action_types.name
212: into name
213: from ame_actions,
214: ame_action_types
215: where
210: begin
211: select ame_action_types.name
212: into name
213: from ame_actions,
214: ame_action_types
215: where
216: ame_actions.action_type_id = ame_action_types.action_type_id and
217: action_id = actionIdIn and
218: sysdate between ame_actions.start_date and
212: into name
213: from ame_actions,
214: ame_action_types
215: where
216: ame_actions.action_type_id = ame_action_types.action_type_id and
217: action_id = actionIdIn and
218: sysdate between ame_actions.start_date and
219: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
220: sysdate between ame_action_types.start_date and
216: ame_actions.action_type_id = ame_action_types.action_type_id and
217: action_id = actionIdIn and
218: sysdate between ame_actions.start_date and
219: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
220: sysdate between ame_action_types.start_date and
221: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
222: return(name);
223: exception
224: when others then
217: action_id = actionIdIn and
218: sysdate between ame_actions.start_date and
219: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
220: sysdate between ame_action_types.start_date and
221: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
222: return(name);
223: exception
224: when others then
225: rollback;
233: raise;
234: return(null);
235: end getActionTypeNameByActionId;
236: function getActionTypeProcedureName(actionTypeIdIn in integer) return varchar2 as
237: procedureName ame_action_types.procedure_name%type;
238: begin
239: select procedure_name
240: into procedureName
241: from ame_action_types
237: procedureName ame_action_types.procedure_name%type;
238: begin
239: select procedure_name
240: into procedureName
241: from ame_action_types
242: where
243: action_type_id = actionTypeIdIn and
244: sysdate between start_date and
245: nvl(end_date - ame_util.oneSecond, sysdate) ;
287: createdBy integer;
288: begin
289: select created_by
290: into createdBy
291: from ame_action_types
292: where
293: action_type_id = actionTypeIdIn and
294: sysdate between start_date and
295: nvl(end_date - ame_util.oneSecond, sysdate) ;
449: the action's static or dynamic description. The maximum length of the
450: string returned by getDescription is 500 bytes.
451: */
452: function getDescription(actionIdIn in integer) return varchar2 as
453: actionTypeName ame_action_types.name%type;
454: approverName ame_actions.parameter%type;
455: description ame_actions.description%type;
456: approverDesc ame_util.longStringType;
457: approverValid boolean;
540: return(null);
541: end getDescription2;
542: function getDynamicActionDesc(actionIdIn in integer) return varchar2 as
543: actionDescription ame_util.stringType;
544: actionTypeName ame_action_types.name%type;
545: descriptionQuery ame_action_types.description_query%type;
546: parameterOne ame_actions.parameter%type;
547: parameterTwo ame_actions.parameter_two%type;
548: tempIndex integer;
541: end getDescription2;
542: function getDynamicActionDesc(actionIdIn in integer) return varchar2 as
543: actionDescription ame_util.stringType;
544: actionTypeName ame_action_types.name%type;
545: descriptionQuery ame_action_types.description_query%type;
546: parameterOne ame_actions.parameter%type;
547: parameterTwo ame_actions.parameter_two%type;
548: tempIndex integer;
549: begin
553: into descriptionQuery,
554: parameterOne,
555: parameterTwo
556: from ame_actions,
557: ame_action_types
558: where
559: ame_actions.action_type_id = ame_action_types.action_type_id and
560: action_id = actionIdIn and
561: sysdate between ame_action_types.start_date and
555: parameterTwo
556: from ame_actions,
557: ame_action_types
558: where
559: ame_actions.action_type_id = ame_action_types.action_type_id and
560: action_id = actionIdIn and
561: sysdate between ame_action_types.start_date and
562: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
563: sysdate between ame_actions.start_date and
557: ame_action_types
558: where
559: ame_actions.action_type_id = ame_action_types.action_type_id and
560: action_id = actionIdIn and
561: sysdate between ame_action_types.start_date and
562: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
563: sysdate between ame_actions.start_date and
564: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
565: if(instrb(descriptionQuery, ame_util.actionParameterOne) > 0) then
558: where
559: ame_actions.action_type_id = ame_action_types.action_type_id and
560: action_id = actionIdIn and
561: sysdate between ame_action_types.start_date and
562: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
563: sysdate between ame_actions.start_date and
564: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
565: if(instrb(descriptionQuery, ame_util.actionParameterOne) > 0) then
566: if(instrb(descriptionQuery, ame_util.actionParameterTwo) > 0) then /* both parameters */
598: raise;
599: return(null);
600: end getDynamicActionDesc;
601: function getGroupChainActionTypeId return integer as
602: actionTypeId ame_action_types.action_type_id%type;
603: begin
604: select action_type_id
605: into actionTypeId
606: from ame_action_types
602: actionTypeId ame_action_types.action_type_id%type;
603: begin
604: select action_type_id
605: into actionTypeId
606: from ame_action_types
607: where name = ame_util.groupChainApprovalTypeName and
608: sysdate between start_date and
609: nvl(end_date - ame_util.oneSecond, sysdate) ;
610: return actionTypeId;
697: stringStartDate varchar2(50);
698: begin
699: select start_date
700: into startDate
701: from ame_action_types
702: where
703: action_type_id = actionTypeIdIn and
704: sysdate between start_date and
705: nvl(end_date - ame_util.oneSecond, sysdate) ;
718: raise;
719: return(null);
720: end getParentVersionStartDate;
721: function getPreApprovalActionTypeId return integer as
722: actionTypeId ame_action_types.action_type_id%type;
723: begin
724: select action_type_id
725: into actionTypeId
726: from ame_action_types
722: actionTypeId ame_action_types.action_type_id%type;
723: begin
724: select action_type_id
725: into actionTypeId
726: from ame_action_types
727: where name = ame_util.preApprovalTypeName and
728: sysdate between start_date and
729: nvl(end_date - ame_util.oneSecond, sysdate) ;
730: return actionTypeId;
738: raise;
739: return(null);
740: end getPreApprovalActionTypeId;
741: function getPostApprovalActionTypeId return integer as
742: actionTypeId ame_action_types.action_type_id%type;
743: begin
744: select action_type_id
745: into actionTypeId
746: from ame_action_types
742: actionTypeId ame_action_types.action_type_id%type;
743: begin
744: select action_type_id
745: into actionTypeId
746: from ame_action_types
747: where name = ame_util.postApprovalTypeName and
748: sysdate between start_date and
749: nvl(end_date - ame_util.oneSecond, sysdate) ;
750: return actionTypeId;
857: attributeId integer;
858: begin
859: select created_by
860: into createdByValue
861: from ame_action_types
862: where
863: action_type_id = actionTypeIdIn and
864: sysdate between start_date and
865: nvl(end_date - ame_util.oneSecond, sysdate) ;
917: end if;
918: begin
919: select action_type_id
920: into actionTypeId
921: from ame_action_types
922: where
923: (actionTypeIdIn is null or action_type_id <> actionTypeIdIn) and
924: upper(name) = upper(nameIn) and
925: sysdate between start_date and
955: end if;
956: end if;
957: end if;
958: end if;
959: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_action_types',
960: columnNameIn => 'name',
961: argumentIn => nameIn)) then
962: raise nameLengthException;
963: end if;
960: columnNameIn => 'name',
961: argumentIn => nameIn)) then
962: raise nameLengthException;
963: end if;
964: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_action_types',
965: columnNameIn => 'procedure_name',
966: argumentIn => procedureNameIn)) then
967: raise procedureNameLengthException;
968: end if;
965: columnNameIn => 'procedure_name',
966: argumentIn => procedureNameIn)) then
967: raise procedureNameLengthException;
968: end if;
969: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_action_types',
970: columnNameIn => 'description',
971: argumentIn => descriptionIn)) then
972: raise descriptionLengthException;
973: end if;
979: */
980: currentUserId := ame_util.getCurrentUserId;
981: if(actionTypeIdIn is null) then
982: createdBy := currentUserId;
983: select ame_action_types_s.nextval into actionTypeId from dual;
984: else
985: actionTypeId := actionTypeIdIn;
986: select count(*)
987: into tempCount
984: else
985: actionTypeId := actionTypeIdIn;
986: select count(*)
987: into tempCount
988: from ame_action_types
989: where
990: action_type_id = actionTypeId and
991: created_by = ame_util.seededDataCreatedById;
992: if(tempCount > 0) then
994: else
995: createdBy := currentUserId;
996: end if;
997: end if;
998: insert into ame_action_types(action_type_id,
999: name,
1000: procedure_name,
1001: created_by,
1002: creation_date,
1057: ame_util.getMessage(applicationShortNameIn => 'PER',
1058: messageNameIn => 'AME_400140_ACT_APT_NAME_LONG',
1059: tokenNameOneIn => 'COLUMN_LENGTH',
1060: tokenValueOneIn =>
1061: ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1062: columnNameIn => 'name'));
1063: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1064: routineNameIn => 'new',
1065: exceptionNumberIn => errorCode,
1074: ame_util.getMessage(applicationShortNameIn => 'PER',
1075: messageNameIn => 'AME_400141_ACT_APT_PRC_NAM_LNG',
1076: tokenNameOneIn => 'COLUMN_LENGTH',
1077: tokenValueOneIn =>
1078: ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1079: columnNameIn => 'procedure_name'));
1080: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1081: routineNameIn => 'new',
1082: exceptionNumberIn => errorCode,
1091: ame_util.getMessage(applicationShortNameIn => 'PER',
1092: messageNameIn => 'AME_400142_ACT_APT_DESC_LONG',
1093: tokenNameOneIn => 'COLUMN_LENGTH',
1094: tokenValueOneIn =>
1095: ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1096: columnNameIn => 'description'));
1097: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1098: routineNameIn => 'new',
1099: exceptionNumberIn => errorCode,
1144: ame_util.getMessage(applicationShortNameIn => 'PER',
1145: messageNameIn => 'AME_400142_ACT_APT_DESC_LONG',
1146: tokenNameOneIn => 'COLUMN_LENGTH',
1147: tokenValueOneIn =>
1148: ame_util.getColumnLength(tableNameIn => 'ame_action_types',
1149: columnNameIn => 'description_query'));
1150: ame_util.runtimeException(packageNameIn => 'ame_action_pkg',
1151: routineNameIn => 'new',
1152: exceptionNumberIn => errorCode,
1177: actionIdIn in integer default null,
1178: processingDateIn in date default null) return integer as
1179: cursor startDateCursor is
1180: select start_date
1181: from ame_action_types
1182: where
1183: action_type_id = actionTypeIdIn and
1184: sysdate between start_date and
1185: nvl(end_date - ame_util.oneSecond, sysdate)
1186: for update;
1187: actionCount integer;
1188: actionId integer;
1189: actionTypeId integer;
1190: actionTypeDescription ame_action_types.description%type;
1191: actionTypeDescQuery ame_action_types.description_query%type;
1192: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
1193: actionTypeName ame_action_types.name%type;
1194: actionTypeProcedureName ame_action_types.procedure_name%type;
1187: actionCount integer;
1188: actionId integer;
1189: actionTypeId integer;
1190: actionTypeDescription ame_action_types.description%type;
1191: actionTypeDescQuery ame_action_types.description_query%type;
1192: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
1193: actionTypeName ame_action_types.name%type;
1194: actionTypeProcedureName ame_action_types.procedure_name%type;
1195: attributeId ame_attributes.attribute_id%type;
1188: actionId integer;
1189: actionTypeId integer;
1190: actionTypeDescription ame_action_types.description%type;
1191: actionTypeDescQuery ame_action_types.description_query%type;
1192: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
1193: actionTypeName ame_action_types.name%type;
1194: actionTypeProcedureName ame_action_types.procedure_name%type;
1195: attributeId ame_attributes.attribute_id%type;
1196: createdBy integer;
1189: actionTypeId integer;
1190: actionTypeDescription ame_action_types.description%type;
1191: actionTypeDescQuery ame_action_types.description_query%type;
1192: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
1193: actionTypeName ame_action_types.name%type;
1194: actionTypeProcedureName ame_action_types.procedure_name%type;
1195: attributeId ame_attributes.attribute_id%type;
1196: createdBy integer;
1197: currentUserId integer;
1190: actionTypeDescription ame_action_types.description%type;
1191: actionTypeDescQuery ame_action_types.description_query%type;
1192: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
1193: actionTypeName ame_action_types.name%type;
1194: actionTypeProcedureName ame_action_types.procedure_name%type;
1195: attributeId ame_attributes.attribute_id%type;
1196: createdBy integer;
1197: currentUserId integer;
1198: descriptionLengthException exception;
1310: actionTypeDescription := getActionTypeDescription(actionTypeIdIn => actionTypeIdIn);
1311: actionTypeProcedureName := getActionTypeProcedureName(actionTypeIdIn => actionTypeIdIn);
1312: actionTypeDynamicDesc := getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn);
1313: actionTypeDescQuery := getActionTypeDescQuery(actionTypeIdIn => actionTypeIdIn);
1314: update ame_action_types
1315: set
1316: last_updated_by = currentUserId,
1317: last_update_date = endDate,
1318: last_update_login = currentUserId,
1535: deleteListIn in ame_util.stringList default ame_util.emptyStringList,
1536: finalizeIn in boolean default false) as
1537: cursor startDateCursor is
1538: select start_date
1539: from ame_action_types
1540: where action_type_id = actionTypeIdIn and
1541: sysdate between start_date and
1542: nvl(end_date - ame_util.oneSecond, sysdate)
1543: for update;
1551: endDate date;
1552: errorCode integer;
1553: errorMessage ame_util.longestStringType;
1554: deleteCount integer;
1555: description ame_action_types.description%type;
1556: dynamicDescription ame_action_types.dynamic_description%type;
1557: name ame_action_types.name%type;
1558: newStartDate date;
1559: nullException exception;
1552: errorCode integer;
1553: errorMessage ame_util.longestStringType;
1554: deleteCount integer;
1555: description ame_action_types.description%type;
1556: dynamicDescription ame_action_types.dynamic_description%type;
1557: name ame_action_types.name%type;
1558: newStartDate date;
1559: nullException exception;
1560: objectVersionNoDataException exception;
1553: errorMessage ame_util.longestStringType;
1554: deleteCount integer;
1555: description ame_action_types.description%type;
1556: dynamicDescription ame_action_types.dynamic_description%type;
1557: name ame_action_types.name%type;
1558: newStartDate date;
1559: nullException exception;
1560: objectVersionNoDataException exception;
1561: procedureName ame_action_types.procedure_name%type;
1557: name ame_action_types.name%type;
1558: newStartDate date;
1559: nullException exception;
1560: objectVersionNoDataException exception;
1561: procedureName ame_action_types.procedure_name%type;
1562: seededException exception;
1563: startDate date;
1564: tempIndex integer;
1565: tempIndex2 integer;
1578: dynamicDescription := getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn);
1579: /* make sure the end_date and start_date values do not overlap */
1580: endDate := processingDateIn;
1581: newStartDate := processingDateIn;
1582: update ame_action_types
1583: set
1584: last_updated_by = currentUserId,
1585: last_update_date = endDate,
1586: last_update_login = currentUserId,
1692: parentVersionStartDateIn in date,
1693: processingDateIn in date default null) as
1694: cursor startDateCursor is
1695: select start_date
1696: from ame_action_types
1697: where action_type_id = actionTypeIdIn and
1698: sysdate between start_date and
1699: nvl(end_date - ame_util.oneSecond, sysdate)
1700: for update;
1705: sysdate between start_date and
1706: nvl(end_date - ame_util.oneSecond, sysdate)
1707: for update;
1708: actionId ame_actions.action_id%type;
1709: actionTypeId ame_action_types.action_type_id%type;
1710: currentUserId integer;
1711: description ame_actions.description%type;
1712: endDate date;
1713: errorCode integer;
1711: description ame_actions.description%type;
1712: endDate date;
1713: errorCode integer;
1714: errorMessage ame_util.longestStringType;
1715: newActionTypeId ame_action_types.action_type_id%type;
1716: newStartDate date;
1717: nullDescriptionException exception;
1718: objectVersionNoDataException exception;
1719: startDate date;
1886: deleteListIn in ame_util.stringList default ame_util.emptyStringList,
1887: finalizeIn in boolean default false) as
1888: cursor startDateCursor is
1889: select start_date
1890: from ame_action_types
1891: where action_type_id = actionTypeIdIn and
1892: sysdate between start_date and
1893: nvl(end_date - ame_util.oneSecond, sysdate)
1894: for update;
1902: nvl(end_date - ame_util.oneSecond, sysdate)
1903: for update;
1904: currentUserId integer;
1905: description ame_approval_groups.description%type;
1906: dynamicDescription ame_action_types.dynamic_description%type;
1907: errorCode integer;
1908: errorMessage ame_util.longestStringType;
1909: nullDescriptionQueryException exception;
1910: nullException exception;
2395: actionDescriptionsOut out nocopy ame_util.stringList,
2396: actionCreatedByOut out nocopy ame_util.idList) as
2397: cursor actionCursor(actionTypeIdIn in integer) is
2398: select
2399: ame_action_types.name,
2400: ame_action_types.description_query,
2401: ame_actions.action_id,
2402: ame_actions.description,
2403: ame_actions.parameter,
2396: actionCreatedByOut out nocopy ame_util.idList) as
2397: cursor actionCursor(actionTypeIdIn in integer) is
2398: select
2399: ame_action_types.name,
2400: ame_action_types.description_query,
2401: ame_actions.action_id,
2402: ame_actions.description,
2403: ame_actions.parameter,
2404: ame_actions.parameter_two,
2402: ame_actions.description,
2403: ame_actions.parameter,
2404: ame_actions.parameter_two,
2405: ame_actions.created_by
2406: from ame_action_types,
2407: ame_actions
2408: where
2409: ame_action_types.action_type_id = ame_actions.action_type_id and
2410: ame_action_types.action_type_id = actionTypeIdIn and
2405: ame_actions.created_by
2406: from ame_action_types,
2407: ame_actions
2408: where
2409: ame_action_types.action_type_id = ame_actions.action_type_id and
2410: ame_action_types.action_type_id = actionTypeIdIn and
2411: sysdate between ame_action_types.start_date and
2412: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2413: sysdate between ame_actions.start_date and
2406: from ame_action_types,
2407: ame_actions
2408: where
2409: ame_action_types.action_type_id = ame_actions.action_type_id and
2410: ame_action_types.action_type_id = actionTypeIdIn and
2411: sysdate between ame_action_types.start_date and
2412: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2413: sysdate between ame_actions.start_date and
2414: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate)
2407: ame_actions
2408: where
2409: ame_action_types.action_type_id = ame_actions.action_type_id and
2410: ame_action_types.action_type_id = actionTypeIdIn and
2411: sysdate between ame_action_types.start_date and
2412: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2413: sysdate between ame_actions.start_date and
2414: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate)
2415: order by ame_actions.created_by, ame_actions.description;
2408: where
2409: ame_action_types.action_type_id = ame_actions.action_type_id and
2410: ame_action_types.action_type_id = actionTypeIdIn and
2411: sysdate between ame_action_types.start_date and
2412: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2413: sysdate between ame_actions.start_date and
2414: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate)
2415: order by ame_actions.created_by, ame_actions.description;
2416: parameterOne ame_actions.parameter%type;
2478: select ame_actions.action_id,
2479: ame_actions.parameter,
2480: ame_actions.parameter_two,
2481: ame_actions.description,
2482: ame_action_types.dynamic_description,
2483: ame_action_types.description_query
2484: from ame_actions,
2485: ame_action_types
2486: where
2479: ame_actions.parameter,
2480: ame_actions.parameter_two,
2481: ame_actions.description,
2482: ame_action_types.dynamic_description,
2483: ame_action_types.description_query
2484: from ame_actions,
2485: ame_action_types
2486: where
2487: ame_actions.action_type_id = ame_action_types.action_type_id and
2481: ame_actions.description,
2482: ame_action_types.dynamic_description,
2483: ame_action_types.description_query
2484: from ame_actions,
2485: ame_action_types
2486: where
2487: ame_actions.action_type_id = ame_action_types.action_type_id and
2488: ame_actions.action_type_id = actionTypeIdIn and
2489: sysdate between ame_actions.start_date and
2483: ame_action_types.description_query
2484: from ame_actions,
2485: ame_action_types
2486: where
2487: ame_actions.action_type_id = ame_action_types.action_type_id and
2488: ame_actions.action_type_id = actionTypeIdIn and
2489: sysdate between ame_actions.start_date and
2490: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
2491: sysdate between ame_action_types.start_date and
2487: ame_actions.action_type_id = ame_action_types.action_type_id and
2488: ame_actions.action_type_id = actionTypeIdIn and
2489: sysdate between ame_actions.start_date and
2490: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
2491: sysdate between ame_action_types.start_date and
2492: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate)
2493: order by ame_actions.created_by, ame_actions.description;
2494: actionId integer;
2495: tempIndex integer;
2488: ame_actions.action_type_id = actionTypeIdIn and
2489: sysdate between ame_actions.start_date and
2490: nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
2491: sysdate between ame_action_types.start_date and
2492: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate)
2493: order by ame_actions.created_by, ame_actions.description;
2494: actionId integer;
2495: tempIndex integer;
2496: begin
2543: cursor actionTypeCursor is
2544: select
2545: action_type_id,
2546: name
2547: from ame_action_types
2548: where
2549: sysdate between start_date and
2550: nvl(end_date - ame_util.oneSecond, sysdate)
2551: order by name;
2572: cursor actionTypeCursor is
2573: select
2574: action_type_id,
2575: name
2576: from ame_action_types
2577: where
2578: sysdate between start_date and
2579: nvl(end_date - ame_util.oneSecond, sysdate)
2580: order by name;
2602: actionTypeNamesOut out nocopy ame_util.stringList,
2603: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2604: ruleTypesOut out nocopy ame_util.idList) as
2605: cursor actionTypeCursor is
2606: select ame_action_types.action_type_id,
2607: ame_action_types.name,
2608: ame_action_types.description,
2609: ame_action_type_usages.rule_type,
2610: ame_action_type_config.order_number
2603: actionTypeDescriptionsOut out nocopy ame_util.stringList,
2604: ruleTypesOut out nocopy ame_util.idList) as
2605: cursor actionTypeCursor is
2606: select ame_action_types.action_type_id,
2607: ame_action_types.name,
2608: ame_action_types.description,
2609: ame_action_type_usages.rule_type,
2610: ame_action_type_config.order_number
2611: from ame_action_types,
2604: ruleTypesOut out nocopy ame_util.idList) as
2605: cursor actionTypeCursor is
2606: select ame_action_types.action_type_id,
2607: ame_action_types.name,
2608: ame_action_types.description,
2609: ame_action_type_usages.rule_type,
2610: ame_action_type_config.order_number
2611: from ame_action_types,
2612: ame_action_type_usages,
2607: ame_action_types.name,
2608: ame_action_types.description,
2609: ame_action_type_usages.rule_type,
2610: ame_action_type_config.order_number
2611: from ame_action_types,
2612: ame_action_type_usages,
2613: ame_action_type_config
2614: where
2615: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2611: from ame_action_types,
2612: ame_action_type_usages,
2613: ame_action_type_config
2614: where
2615: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2616: ame_action_types.action_type_id = ame_action_type_config.action_type_id and
2617: ame_action_type_config.application_id = applicationIdIn and
2618: ame_action_type_usages.rule_type <> ame_util.exceptionRuleType and
2619: sysdate between ame_action_types.start_date and
2612: ame_action_type_usages,
2613: ame_action_type_config
2614: where
2615: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2616: ame_action_types.action_type_id = ame_action_type_config.action_type_id and
2617: ame_action_type_config.application_id = applicationIdIn and
2618: ame_action_type_usages.rule_type <> ame_util.exceptionRuleType and
2619: sysdate between ame_action_types.start_date and
2620: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2615: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2616: ame_action_types.action_type_id = ame_action_type_config.action_type_id and
2617: ame_action_type_config.application_id = applicationIdIn and
2618: ame_action_type_usages.rule_type <> ame_util.exceptionRuleType and
2619: sysdate between ame_action_types.start_date and
2620: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2621: sysdate between ame_action_type_usages.start_date and
2622: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
2623: sysdate between ame_action_type_config.start_date and
2616: ame_action_types.action_type_id = ame_action_type_config.action_type_id and
2617: ame_action_type_config.application_id = applicationIdIn and
2618: ame_action_type_usages.rule_type <> ame_util.exceptionRuleType and
2619: sysdate between ame_action_types.start_date and
2620: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2621: sysdate between ame_action_type_usages.start_date and
2622: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
2623: sysdate between ame_action_type_config.start_date and
2624: nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate)
2623: sysdate between ame_action_type_config.start_date and
2624: nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate)
2625: order by ame_action_type_usages.rule_type,
2626: ame_action_type_config.order_number,
2627: ame_action_types.name;
2628: tempIndex integer;
2629: begin
2630: tempIndex := 1;
2631: for tempActionType in actionTypeCursor loop
2653: cursor actionTypeCursor is
2654: select
2655: action_type_id,
2656: description
2657: from ame_action_types
2658: where
2659: sysdate between start_date and
2660: nvl(end_date - ame_util.oneSecond, sysdate)
2661: order by description;
2810: cursor actionTypeCursor(ruleTypeIn in varchar2,
2811: allowAllApproverTypesIn in varchar2,
2812: allowProductionsIn in varchar2) is
2813: select
2814: ame_action_types.action_type_id action_type_id,
2815: ame_action_types.description description
2816: from
2817: ame_action_types,
2818: ame_action_type_usages
2811: allowAllApproverTypesIn in varchar2,
2812: allowProductionsIn in varchar2) is
2813: select
2814: ame_action_types.action_type_id action_type_id,
2815: ame_action_types.description description
2816: from
2817: ame_action_types,
2818: ame_action_type_usages
2819: where
2813: select
2814: ame_action_types.action_type_id action_type_id,
2815: ame_action_types.description description
2816: from
2817: ame_action_types,
2818: ame_action_type_usages
2819: where
2820: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2821: ((allowProductionsIn = ame_util.yes and ame_action_type_usages.rule_type = ame_util.productionRuleType) or
2816: from
2817: ame_action_types,
2818: ame_action_type_usages
2819: where
2820: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2821: ((allowProductionsIn = ame_util.yes and ame_action_type_usages.rule_type = ame_util.productionRuleType) or
2822: ame_action_type_usages.rule_type = ruleTypeIn) and
2823: (allowAllApproverTypesIn = ame_util.yes or
2824: ame_action_types.action_type_id in (
2820: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2821: ((allowProductionsIn = ame_util.yes and ame_action_type_usages.rule_type = ame_util.productionRuleType) or
2822: ame_action_type_usages.rule_type = ruleTypeIn) and
2823: (allowAllApproverTypesIn = ame_util.yes or
2824: ame_action_types.action_type_id in (
2825: select distinct action_type_id
2826: from ame_approver_type_usages
2827: where
2828: (approver_type_id = ame_util.anyApproverType or
2833: orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
2834: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2835: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2836: sysdate between
2837: ame_action_types.start_date and
2838: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2839: sysdate
2840: between ame_action_type_usages.start_date and
2841: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2834: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2835: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2836: sysdate between
2837: ame_action_types.start_date and
2838: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2839: sysdate
2840: between ame_action_type_usages.start_date and
2841: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2842: order by upper(description);
2929: cursor actionTypeCursor(allowAllApproverTypesIn in varchar2,
2930: allowProductionsIn in varchar2,
2931: subOrListModActsForCombRuleIn in varchar2) is
2932: select
2933: ame_action_types.action_type_id action_type_id,
2934: ame_action_types.description description
2935: from
2936: ame_action_types,
2937: ame_action_type_usages
2930: allowProductionsIn in varchar2,
2931: subOrListModActsForCombRuleIn in varchar2) is
2932: select
2933: ame_action_types.action_type_id action_type_id,
2934: ame_action_types.description description
2935: from
2936: ame_action_types,
2937: ame_action_type_usages
2938: where
2932: select
2933: ame_action_types.action_type_id action_type_id,
2934: ame_action_types.description description
2935: from
2936: ame_action_types,
2937: ame_action_type_usages
2938: where
2939: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2940: ((allowProductionsIn = ame_util.yes and
2935: from
2936: ame_action_types,
2937: ame_action_type_usages
2938: where
2939: ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
2940: ((allowProductionsIn = ame_util.yes and
2941: ame_action_type_usages.rule_type = ame_util.productionRuleType) or
2942: (subOrListModActsForCombRuleIn = ame_util.no and
2943: ame_action_type_usages.rule_type in
2948: ame_action_type_usages.rule_type in
2949: (ame_util.listModRuleType,
2950: ame_util.substitutionRuleType))) and
2951: (allowAllApproverTypesIn = ame_util.yes or
2952: ame_action_types.action_type_id in (
2953: select distinct action_type_id
2954: from ame_approver_type_usages
2955: where
2956: (approver_type_id = ame_util.anyApproverType or
2961: orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
2962: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2963: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2964: sysdate between
2965: ame_action_types.start_date and
2966: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2967: sysdate
2968: between ame_action_type_usages.start_date and
2969: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2962: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2963: sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
2964: sysdate between
2965: ame_action_types.start_date and
2966: nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
2967: sysdate
2968: between ame_action_type_usages.start_date and
2969: nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
2970: order by upper(description);
3341: parentVersionStartDateIn in date,
3342: processingDateIn in date default null) as
3343: cursor startDateCursor is
3344: select start_date
3345: from ame_action_types
3346: where
3347: action_type_id = actionTypeIdIn and
3348: sysdate between start_date and
3349: nvl(end_date - ame_util.oneSecond, sysdate)
3407: finalizeIn => false);
3408: end if;
3409: end loop;
3410: currentUserId := ame_util.getCurrentUserId;
3411: update ame_action_types
3412: set
3413: last_updated_by = currentUserId,
3414: last_update_date = processingDate,
3415: last_update_login = currentUserId,
3520: action_id = actionIdIn and
3521: sysdate between start_date and
3522: nvl(end_date - ame_util.oneSecond, sysdate)
3523: for update;
3524: actionTypeDescription ame_action_types.description%type;
3525: actionTypeDescQuery ame_action_types.description_query%type;
3526: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
3527: actionTypeId ame_action_types.action_type_id%type;
3528: actionTypeName ame_action_types.name%type;
3521: sysdate between start_date and
3522: nvl(end_date - ame_util.oneSecond, sysdate)
3523: for update;
3524: actionTypeDescription ame_action_types.description%type;
3525: actionTypeDescQuery ame_action_types.description_query%type;
3526: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
3527: actionTypeId ame_action_types.action_type_id%type;
3528: actionTypeName ame_action_types.name%type;
3529: actionTypeProcedureName ame_action_types.procedure_name%type;
3522: nvl(end_date - ame_util.oneSecond, sysdate)
3523: for update;
3524: actionTypeDescription ame_action_types.description%type;
3525: actionTypeDescQuery ame_action_types.description_query%type;
3526: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
3527: actionTypeId ame_action_types.action_type_id%type;
3528: actionTypeName ame_action_types.name%type;
3529: actionTypeProcedureName ame_action_types.procedure_name%type;
3530: currentUserId integer;
3523: for update;
3524: actionTypeDescription ame_action_types.description%type;
3525: actionTypeDescQuery ame_action_types.description_query%type;
3526: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
3527: actionTypeId ame_action_types.action_type_id%type;
3528: actionTypeName ame_action_types.name%type;
3529: actionTypeProcedureName ame_action_types.procedure_name%type;
3530: currentUserId integer;
3531: deleteCount integer;
3524: actionTypeDescription ame_action_types.description%type;
3525: actionTypeDescQuery ame_action_types.description_query%type;
3526: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
3527: actionTypeId ame_action_types.action_type_id%type;
3528: actionTypeName ame_action_types.name%type;
3529: actionTypeProcedureName ame_action_types.procedure_name%type;
3530: currentUserId integer;
3531: deleteCount integer;
3532: errorCode integer;
3525: actionTypeDescQuery ame_action_types.description_query%type;
3526: actionTypeDynamicDesc ame_action_types.dynamic_description%type;
3527: actionTypeId ame_action_types.action_type_id%type;
3528: actionTypeName ame_action_types.name%type;
3529: actionTypeProcedureName ame_action_types.procedure_name%type;
3530: currentUserId integer;
3531: deleteCount integer;
3532: errorCode integer;
3533: errorMessage ame_util.longestStringType;
3573: actionTypeProcedureName := getActionTypeProcedureName(actionTypeIdIn => actionTypeIdIn);
3574: actionTypeDescription := getActionTypeDescription(actionTypeIdIn => actionTypeIdIn);
3575: actionTypeDescQuery := getActionTypeDescQuery(actionTypeIdIn => actionTypeIdIn);
3576: actionTypeDynamicDesc := getActionTypeDynamicDesc(actionTypeIdIn => actionTypeIdIn);
3577: update ame_action_types
3578: set
3579: last_updated_by = currentUserId,
3580: last_update_date = processingDate,
3581: last_update_login = currentUserId,