1: PACKAGE BODY GMDOPLAP_wf_pkg AS
2: /* $Header: GMDOPLAB.pls 120.0 2005/05/30 04:13:56 appldev noship $ */
3: PROCEDURE wf_init (
4: p_operation_id IN GMD_OPERATIONS_B.oprn_id%TYPE,
5: p_operation_no IN GMD_OPERATIONS_B.oprn_no%TYPE,
6: p_operation_vers IN GMD_OPERATIONS_B.oprn_vers%TYPE,
7: p_start_status IN GMD_OPERATIONS_B.operation_status%TYPE,
8: p_target_status IN GMD_OPERATIONS_B.operation_status%TYPE,
1: PACKAGE BODY GMDOPLAP_wf_pkg AS
2: /* $Header: GMDOPLAB.pls 120.0 2005/05/30 04:13:56 appldev noship $ */
3: PROCEDURE wf_init (
4: p_operation_id IN GMD_OPERATIONS_B.oprn_id%TYPE,
5: p_operation_no IN GMD_OPERATIONS_B.oprn_no%TYPE,
6: p_operation_vers IN GMD_OPERATIONS_B.oprn_vers%TYPE,
7: p_start_status IN GMD_OPERATIONS_B.operation_status%TYPE,
8: p_target_status IN GMD_OPERATIONS_B.operation_status%TYPE,
9: p_requester IN GMD_OPERATIONS_B.LAST_UPDATED_BY%TYPE,
2: /* $Header: GMDOPLAB.pls 120.0 2005/05/30 04:13:56 appldev noship $ */
3: PROCEDURE wf_init (
4: p_operation_id IN GMD_OPERATIONS_B.oprn_id%TYPE,
5: p_operation_no IN GMD_OPERATIONS_B.oprn_no%TYPE,
6: p_operation_vers IN GMD_OPERATIONS_B.oprn_vers%TYPE,
7: p_start_status IN GMD_OPERATIONS_B.operation_status%TYPE,
8: p_target_status IN GMD_OPERATIONS_B.operation_status%TYPE,
9: p_requester IN GMD_OPERATIONS_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_OPERATIONS_B.LAST_UPDATE_DATE%TYPE
3: PROCEDURE wf_init (
4: p_operation_id IN GMD_OPERATIONS_B.oprn_id%TYPE,
5: p_operation_no IN GMD_OPERATIONS_B.oprn_no%TYPE,
6: p_operation_vers IN GMD_OPERATIONS_B.oprn_vers%TYPE,
7: p_start_status IN GMD_OPERATIONS_B.operation_status%TYPE,
8: p_target_status IN GMD_OPERATIONS_B.operation_status%TYPE,
9: p_requester IN GMD_OPERATIONS_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_OPERATIONS_B.LAST_UPDATE_DATE%TYPE
11: )
4: p_operation_id IN GMD_OPERATIONS_B.oprn_id%TYPE,
5: p_operation_no IN GMD_OPERATIONS_B.oprn_no%TYPE,
6: p_operation_vers IN GMD_OPERATIONS_B.oprn_vers%TYPE,
7: p_start_status IN GMD_OPERATIONS_B.operation_status%TYPE,
8: p_target_status IN GMD_OPERATIONS_B.operation_status%TYPE,
9: p_requester IN GMD_OPERATIONS_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_OPERATIONS_B.LAST_UPDATE_DATE%TYPE
11: )
12: IS
5: p_operation_no IN GMD_OPERATIONS_B.oprn_no%TYPE,
6: p_operation_vers IN GMD_OPERATIONS_B.oprn_vers%TYPE,
7: p_start_status IN GMD_OPERATIONS_B.operation_status%TYPE,
8: p_target_status IN GMD_OPERATIONS_B.operation_status%TYPE,
9: p_requester IN GMD_OPERATIONS_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_OPERATIONS_B.LAST_UPDATE_DATE%TYPE
11: )
12: IS
13: /* procedure to initialize and run Workflow */
6: p_operation_vers IN GMD_OPERATIONS_B.oprn_vers%TYPE,
7: p_start_status IN GMD_OPERATIONS_B.operation_status%TYPE,
8: p_target_status IN GMD_OPERATIONS_B.operation_status%TYPE,
9: p_requester IN GMD_OPERATIONS_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_OPERATIONS_B.LAST_UPDATE_DATE%TYPE
11: )
12: IS
13: /* procedure to initialize and run Workflow */
14:
19: l_performer_display_name FND_USER.DESCRIPTION%TYPE ;
20: l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
21:
22: /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
23: l_oprn_desc GMD_OPERATIONS.OPRN_DESC%TYPE;
24: l_oprn_class GMD_OPERATIONS.OPRN_CLASS%TYPE;
25: l_item_um GMD_OPERATIONS.PROCESS_QTY_UOM%TYPE;
26: l_effective_start_date GMD_OPERATIONS.EFFECTIVE_START_DATE%TYPE;
27: l_effective_end_date GMD_OPERATIONS.EFFECTIVE_END_DATE%TYPE;
20: l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
21:
22: /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
23: l_oprn_desc GMD_OPERATIONS.OPRN_DESC%TYPE;
24: l_oprn_class GMD_OPERATIONS.OPRN_CLASS%TYPE;
25: l_item_um GMD_OPERATIONS.PROCESS_QTY_UOM%TYPE;
26: l_effective_start_date GMD_OPERATIONS.EFFECTIVE_START_DATE%TYPE;
27: l_effective_end_date GMD_OPERATIONS.EFFECTIVE_END_DATE%TYPE;
28: /* Mercy Thomas Bug 3173515 End of the changes */
21:
22: /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
23: l_oprn_desc GMD_OPERATIONS.OPRN_DESC%TYPE;
24: l_oprn_class GMD_OPERATIONS.OPRN_CLASS%TYPE;
25: l_item_um GMD_OPERATIONS.PROCESS_QTY_UOM%TYPE;
26: l_effective_start_date GMD_OPERATIONS.EFFECTIVE_START_DATE%TYPE;
27: l_effective_end_date GMD_OPERATIONS.EFFECTIVE_END_DATE%TYPE;
28: /* Mercy Thomas Bug 3173515 End of the changes */
29:
22: /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
23: l_oprn_desc GMD_OPERATIONS.OPRN_DESC%TYPE;
24: l_oprn_class GMD_OPERATIONS.OPRN_CLASS%TYPE;
25: l_item_um GMD_OPERATIONS.PROCESS_QTY_UOM%TYPE;
26: l_effective_start_date GMD_OPERATIONS.EFFECTIVE_START_DATE%TYPE;
27: l_effective_end_date GMD_OPERATIONS.EFFECTIVE_END_DATE%TYPE;
28: /* Mercy Thomas Bug 3173515 End of the changes */
29:
30: /* make sure that process runs with background engine
23: l_oprn_desc GMD_OPERATIONS.OPRN_DESC%TYPE;
24: l_oprn_class GMD_OPERATIONS.OPRN_CLASS%TYPE;
25: l_item_um GMD_OPERATIONS.PROCESS_QTY_UOM%TYPE;
26: l_effective_start_date GMD_OPERATIONS.EFFECTIVE_START_DATE%TYPE;
27: l_effective_end_date GMD_OPERATIONS.EFFECTIVE_END_DATE%TYPE;
28: /* Mercy Thomas Bug 3173515 End of the changes */
29:
30: /* make sure that process runs with background engine
31: to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
91: avalue => l_performer_name );
92: /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
93: SELECT OPRN_DESC, OPRN_CLASS, PROCESS_QTY_UOM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
94: INTO l_oprn_desc, l_oprn_class, l_item_um, l_effective_start_date, l_effective_end_date
95: FROM GMD_OPERATIONS
96: WHERE OPRN_ID = P_OPERATION_ID;
97:
98: WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
99: aname => 'GMDOPLAP_OPRN_DESC',
147: IS
148: /* procedure to Check Approval is required or not if required find the approver and send the notification to
149: approver */
150:
151: l_operation_id GMD_OPERATIONS_B.oprn_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_OPERATION_ID'));
152: p_data_string VARCHAR2(2000);
153: p_wf_data_string VARCHAR2(2000);
154: p_lab_wf_item_type VARCHAR2(8) := 'GMDOPLAP'; -- Recipe Lab use Approval Workflow Inernal Name
155: P_lab_Process_name VARCHAR2(32) := 'GMDOPLAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
153: p_wf_data_string VARCHAR2(2000);
154: p_lab_wf_item_type VARCHAR2(8) := 'GMDOPLAP'; -- Recipe Lab use Approval Workflow Inernal Name
155: P_lab_Process_name VARCHAR2(32) := 'GMDOPLAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
156: P_lab_activity_name VARCHAR2(80) := 'GMDOPLAP_NOTI_REQUEST';
157: P_table_name VARCHAR2(32) := 'GMD_OPERATIONS_B'; -- Key Table
158: P_where_clause VARCHAR2(100):= ' GMD_OPERATIONS_B.OPRN_ID='||l_OPERATION_ID; -- Where clause to be appended
159: p_role GMA_ACTDATA_WF.ROLE%TYPE;
160: l_data_string VARCHAR2(2000);
161: l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
154: p_lab_wf_item_type VARCHAR2(8) := 'GMDOPLAP'; -- Recipe Lab use Approval Workflow Inernal Name
155: P_lab_Process_name VARCHAR2(32) := 'GMDOPLAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
156: P_lab_activity_name VARCHAR2(80) := 'GMDOPLAP_NOTI_REQUEST';
157: P_table_name VARCHAR2(32) := 'GMD_OPERATIONS_B'; -- Key Table
158: P_where_clause VARCHAR2(100):= ' GMD_OPERATIONS_B.OPRN_ID='||l_OPERATION_ID; -- Where clause to be appended
159: p_role GMA_ACTDATA_WF.ROLE%TYPE;
160: l_data_string VARCHAR2(2000);
161: l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
162: l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
193:
194: p_resultout:='COMPLETE:Y';
195: ELSE
196: l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_TARGET_STATUS');
197: UPDATE GMD_OPERATIONS_B
198: SET OPERATION_STATUS = l_target_status
199: WHERE OPRN_ID = l_operation_id;
200: p_resultout:='COMPLETE:N';
201: END IF;
239: p_actid IN NUMBER,
240: p_funcmode IN VARCHAR2,
241: p_resultout OUT NOCOPY VARCHAR2) IS
242: l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
243: l_operation_id GMD_OPERATIONS_B.oprn_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_OPERATION_ID'));
244: BEGIN
245: IF (p_funcmode = 'RUN') THEN
246: l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_TARGET_STATUS');
247: UPDATE GMD_OPERATIONS_B
243: l_operation_id GMD_OPERATIONS_B.oprn_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_OPERATION_ID'));
244: BEGIN
245: IF (p_funcmode = 'RUN') THEN
246: l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_TARGET_STATUS');
247: UPDATE GMD_OPERATIONS_B
248: SET OPERATION_STATUS = l_target_status
249: WHERE OPRN_ID = l_operation_id;
250: END IF;
251:
259: p_resultout OUT NOCOPY VARCHAR2) IS
260: l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
261: l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
262: l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
263: l_operation_id GMD_OPERATIONS_B.oprn_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_OPERATION_ID'));
264: BEGIN
265: IF (p_funcmode = 'RUN') THEN
266: l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_START_STATUS');
267: l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_TARGET_STATUS');
269: FROM GMD_STATUS_NEXT
270: WHERE current_status = l_start_status
271: AND target_status = l_target_status
272: AND pending_status IS NOT NULL;
273: UPDATE GMD_OPERATIONS_B
274: SET OPERATION_STATUS = l_rework_status
275: WHERE OPRN_ID = l_operation_id;
276: END IF;
277: END REQ_REJECTED;
281: p_itemkey IN VARCHAR2,
282: p_actid IN NUMBER,
283: p_funcmode IN VARCHAR2,
284: p_resultout OUT NOCOPY VARCHAR2) IS
285: l_operation_id GMD_OPERATIONS_B.oprn_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_OPERATION_ID'));
286: l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
287: BEGIN
288: IF (p_funcmode = 'RUN') THEN
289: l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_START_STATUS');
286: l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
287: BEGIN
288: IF (p_funcmode = 'RUN') THEN
289: l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPLAP_START_STATUS');
290: UPDATE GMD_OPERATIONS_B
291: SET OPERATION_STATUS = l_start_status
292: WHERE OPRN_ID = l_operation_id;
293: END IF;
294: END NO_RESPONSE;