1: PACKAGE BODY gmdrpgap_wf_pkg AS
2: /* $Header: GMDRPGAB.pls 120.1 2011/03/22 18:30:34 rnalla ship $ */
3: PROCEDURE wf_init (
4: p_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE,
5: p_recipe_no IN GMD_RECIPES_B.recipe_no%TYPE,
6: p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7: p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8: p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
1: PACKAGE BODY gmdrpgap_wf_pkg AS
2: /* $Header: GMDRPGAB.pls 120.1 2011/03/22 18:30:34 rnalla ship $ */
3: PROCEDURE wf_init (
4: p_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE,
5: p_recipe_no IN GMD_RECIPES_B.recipe_no%TYPE,
6: p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7: p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8: p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
9: p_requester IN GMD_RECIPES_B.LAST_UPDATED_BY%TYPE,
2: /* $Header: GMDRPGAB.pls 120.1 2011/03/22 18:30:34 rnalla ship $ */
3: PROCEDURE wf_init (
4: p_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE,
5: p_recipe_no IN GMD_RECIPES_B.recipe_no%TYPE,
6: p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7: p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8: p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
9: p_requester IN GMD_RECIPES_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_RECIPES_B.LAST_UPDATE_DATE%TYPE
3: PROCEDURE wf_init (
4: p_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE,
5: p_recipe_no IN GMD_RECIPES_B.recipe_no%TYPE,
6: p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7: p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8: p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
9: p_requester IN GMD_RECIPES_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_RECIPES_B.LAST_UPDATE_DATE%TYPE
11: )
4: p_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE,
5: p_recipe_no IN GMD_RECIPES_B.recipe_no%TYPE,
6: p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7: p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8: p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
9: p_requester IN GMD_RECIPES_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_RECIPES_B.LAST_UPDATE_DATE%TYPE
11: )
12: IS
5: p_recipe_no IN GMD_RECIPES_B.recipe_no%TYPE,
6: p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7: p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8: p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
9: p_requester IN GMD_RECIPES_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_RECIPES_B.LAST_UPDATE_DATE%TYPE
11: )
12: IS
13: /* procedure to initialize and run Workflow */
6: p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7: p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8: p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
9: p_requester IN GMD_RECIPES_B.LAST_UPDATED_BY%TYPE,
10: p_last_update_date IN GMD_RECIPES_B.LAST_UPDATE_DATE%TYPE
11: )
12: IS
13: /* procedure to initialize and run Workflow */
14:
175: IS
176: /* procedure to Check Approval is required or not if required find the approver and send the notification to
177: approver */
178:
179: l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_RECIPE_ID'));
180: p_data_string VARCHAR2(2000);
181: p_wf_data_string VARCHAR2(2000);
182: p_lab_wf_item_type VARCHAR2(8) := 'GMDRPGAP'; -- Recipe Lab use Approval Workflow Inernal Name
183: P_lab_Process_name VARCHAR2(32) := 'GMDRPGAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
181: p_wf_data_string VARCHAR2(2000);
182: p_lab_wf_item_type VARCHAR2(8) := 'GMDRPGAP'; -- Recipe Lab use Approval Workflow Inernal Name
183: P_lab_Process_name VARCHAR2(32) := 'GMDRPGAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
184: P_lab_activity_name VARCHAR2(80) := 'GMDRPGAP_NOTI_REQUEST';
185: P_table_name VARCHAR2(32) := 'GMD_RECIPES_B'; -- Key Table
186: P_where_clause VARCHAR2(100):= ' GMD_RECIPES_B.RECIPE_ID='||l_RECIPE_ID; -- Where clause to be appended
187: p_role GMA_ACTDATA_WF.ROLE%TYPE;
188: l_data_string VARCHAR2(2000);
189: l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
182: p_lab_wf_item_type VARCHAR2(8) := 'GMDRPGAP'; -- Recipe Lab use Approval Workflow Inernal Name
183: P_lab_Process_name VARCHAR2(32) := 'GMDRPGAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
184: P_lab_activity_name VARCHAR2(80) := 'GMDRPGAP_NOTI_REQUEST';
185: P_table_name VARCHAR2(32) := 'GMD_RECIPES_B'; -- Key Table
186: P_where_clause VARCHAR2(100):= ' GMD_RECIPES_B.RECIPE_ID='||l_RECIPE_ID; -- Where clause to be appended
187: p_role GMA_ACTDATA_WF.ROLE%TYPE;
188: l_data_string VARCHAR2(2000);
189: l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
190: l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
221:
222: p_resultout:='COMPLETE:Y';
223: ELSE
224: l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_TARGET_STATUS');
225: UPDATE GMD_RECIPES_B
226: SET RECIPE_STATUS = l_target_status
227: WHERE RECIPE_ID = l_recipe_id;
228: p_resultout:='COMPLETE:N';
229: END IF;
275: p_actid IN NUMBER,
276: p_funcmode IN VARCHAR2,
277: p_resultout OUT NOCOPY VARCHAR2) IS
278: l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
279: l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_RECIPE_ID'));
280: /* added variables for recipe generation B3604554*/
281: l_return_status VARCHAR2(1);
282: l_formula_id NUMBER(10);
283: l_orgn_id NUMBER;
283: l_orgn_id NUMBER;
284:
285: CURSOR c_get_details IS
286: SELECT r.owner_organization_id, r.formula_id, r.recipe_no, r.recipe_version
287: FROM gmd_recipes_b r, fm_form_mst_b f
288: WHERE r.recipe_id = l_recipe_id
289: AND r.formula_id = f.formula_id;
290: LocalDetailsRecord c_get_details%ROWTYPE;
291:
299: BEGIN
300:
301: IF (p_funcmode = 'RUN') THEN
302: l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_TARGET_STATUS');
303: UPDATE GMD_RECIPES_B
304: SET RECIPE_STATUS = l_target_status
305: WHERE RECIPE_ID = l_recipe_id;
306:
307: OPEN c_get_details;
341: p_resultout OUT NOCOPY VARCHAR2) IS
342: l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
343: l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
344: l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
345: l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_RECIPE_ID'));
346: BEGIN
347: IF (p_funcmode = 'RUN') THEN
348: l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_START_STATUS');
349: l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_TARGET_STATUS');
351: FROM GMD_STATUS_NEXT
352: WHERE current_status = l_start_status
353: AND target_status = l_target_status
354: AND pending_status IS NOT NULL;
355: UPDATE GMD_RECIPES_B
356: SET RECIPE_STATUS = l_rework_status
357: WHERE RECIPE_ID = l_recipe_id;
358: END IF;
359: END REQ_REJECTED;
364: p_itemkey IN VARCHAR2,
365: p_actid IN NUMBER,
366: p_funcmode IN VARCHAR2,
367: p_resultout OUT NOCOPY VARCHAR2) IS
368: l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_RECIPE_ID'));
369: l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
370: BEGIN
371: IF (p_funcmode = 'RUN') THEN
372: l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_START_STATUS');
369: l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
370: BEGIN
371: IF (p_funcmode = 'RUN') THEN
372: l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPGAP_START_STATUS');
373: UPDATE GMD_RECIPES_B
374: SET RECIPE_STATUS = l_start_status
375: WHERE RECIPE_ID = l_recipe_id;
376: END IF;
377: END NO_RESPONSE;