[Home] [Help]
1: package body pa_control_items_workflow as
2: /* $Header: PACIWFPB.pls 120.27.12020000.2 2012/07/19 09:32:32 admarath ship $ */
3: /*============================================================================+
4: | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5: | All rights reserved. |
51: and canel the notification. This is called when an action is cancelled.
52: 08-Sep-05 raluthra Bug 4565156. Added code for Manual NOCOPY Changes
53: for usage of same variable for In and Out parameter.
54: 01-Feb-06 vgottimu Bug 4923945. Changed the cursor cur_ci_status_n_owner query,
55: pa_ci_list_v is replaced with the base table pa_control_items and
56: included table hz_parties to get the owner name.
57: 26-Apr-07 vvjoshi Bug#5962401:Modified set_workflow_attributes procedure to set expiration date for adhoc role.
58: 25-Jun-07 rballamu Bug#6053648:Modified change_status_approved to initialize the application context.
59: 17-8-09 anuragar Bug 8566495 Changes for E&C enchancement.
127:
128: CURSOR get_project_id
129: IS
130: SELECT project_id
131: FROM pa_control_items
132: WHERE ci_id = p_ci_id;
133: BEGIN
134:
135: OPEN get_project_id;
152: , x_item_key
153: , p_Process_Name);
154: --debug_msg ( 'after WF_ENGINE createProcess: key = ' || x_item_key);
155:
156: pa_control_items_wf_client.start_workflow(
157: p_item_type
158: , p_process_name
159: , x_item_key
160: , p_ci_id
187: wf_engine.SetItemAttrText(itemtype => p_item_type,
188: itemkey => x_item_key,
189: aname => 'CI_TASK_INFO',
190: avalue =>
191: 'PLSQL:pa_control_items_workflow.show_task_details/'||
192: p_ci_id);
193: end if;
194: --Changes for bug 8942843 end
195:
337: ( itemtype => itemtype,
338: itemkey => itemkey,
339: aname => 'RECORD_VERSION_NUMBER');
340:
341: pa_control_items_utils.ChangeCIStatus
342: (
343: p_validate_only=> 'F',
344: p_commit=>'T',
345:
385: l_num_of_actions NUMBER;
386: l_status VARCHAR2(30);
387: -- #Bug#13683760 changes start.
388: cursor c_get_working_status_code (p_ci_id NUMBER) is
389: SELECT ps.project_status_code FROM pa_ci_statuses_v pc,pa_project_statuses ps, pa_control_items ci
390: where ci.ci_id = p_ci_id and ci.ci_type_id = pc.ci_type_id
391: and ps.project_status_code=pc.project_status_code
392: and ps.project_system_status_code =pc.project_system_status_code
393: and ps.project_system_status_code = 'CI_WORKING' and rownum = 1;
394: -- #Bug#13683760 changes end.
395: cursor c_status(p_ci_id NUMBER, p_item_type VARCHAR2) is
396: SELECT wf_failure_status_code
397: FROM pa_project_statuses ps,
398: pa_control_items ci
399: WHERE ci.ci_id = p_ci_id
400: and ci.status_code = ps.project_status_code
401: and ps.status_type = 'CONTROL_ITEM'
402: and ps.workflow_item_type = p_item_type
434: l_comment);
435: END IF;
436:
437: -- set notification party based on the notification type
438: pa_control_items_wf_client.set_notification_party
439: (itemtype,
440: itemkey,
441: 'CI_REJECTED',
442: actid,
444: resultout
445: );
446:
447: -- #Bug#13683760 changes start.
448: pa_control_items_wf_client.set_submitted_by
449: (itemtype,
450: itemkey,
451: actid,
452: funcmode,
459: fetch c_status into l_status;
460: close c_status;
461:
462: -- debug_msg_s1 ('b4 reject the ci ' || l_return_status);
463: pa_control_items_utils.ChangeCIStatus
464: (
465: p_validate_only=> 'F',
466: p_commit=>'T',
467: p_ci_id => l_ci_id,
493: open c_get_working_status_code(l_ci_id);
494: fetch c_get_working_status_code into l_status;
495: close c_get_working_status_code;
496:
497: pa_control_items_utils.ChangeCIStatusWorking
498: (
499: p_validate_only=> 'F',
500: p_commit=>'T',
501: p_ci_id => l_ci_id,
538:
539: cursor c_status(p_ci_id NUMBER, p_item_type VARCHAR2) is
540: SELECT wf_success_status_code
541: FROM pa_project_statuses ps,
542: pa_control_items ci
543: WHERE ci.ci_id = p_ci_id
544: and ci.status_code = ps.project_status_code
545: and ps.status_type = 'CONTROL_ITEM'
546: and ps.workflow_item_type = p_item_type
556: l_user_id fnd_user.user_id%type;
557: -- End for Bug 6053648
558: -- #Bug#13683760 changes start.
559: cursor c_get_working_status_code (p_ci_id NUMBER) is
560: SELECT ps.project_status_code FROM pa_ci_statuses_v pc,pa_project_statuses ps, pa_control_items ci
561: where ci.ci_id = p_ci_id and ci.ci_type_id = pc.ci_type_id
562: and ps.project_status_code=pc.project_status_code
563: and ps.project_system_status_code =pc.project_system_status_code
564: and ps.project_system_status_code = 'CI_WORKING' and rownum = 1;
619:
620:
621:
622: -- set notification party based on the notification type
623: pa_control_items_wf_client.set_notification_party
624: (itemtype,
625: itemkey,
626: 'CI_APPROVED',
627: actid,
629: resultout
630: );
631:
632: -- #Bug#13683760 changes start.
633: pa_control_items_wf_client.set_submitted_by
634: (itemtype,
635: itemkey,
636: actid,
637: funcmode,
645: open c_status(l_ci_id, itemtype);
646: fetch c_status into l_status;
647: close c_status;
648:
649: pa_control_items_utils.ChangeCIStatus
650: (
651: p_validate_only=> 'F',
652: p_commit=>'T',
653: p_ci_id => l_ci_id,
677:
678: open c_get_working_status_code(l_ci_id);
679: fetch c_get_working_status_code into l_status;
680: close c_get_working_status_code;
681: pa_control_items_utils.ChangeCIStatusWorking
682: (
683: p_validate_only=> 'F',
684: p_commit=>'T',
685: p_ci_id => l_ci_id,
776: l_ret VARCHAR2(240);
777:
778: CURSOR get_status IS
779: SELECT status_code
780: FROM pa_control_items
781: WHERE ci_id = l_ci_id;
782:
783:
784: BEGIN
808:
809:
810: -- added by syao
811: -- set notification party based on the notification type
812: pa_control_items_wf_client.set_notification_party
813: (itemtype,
814: itemkey,
815: l_status,
816: actid,
1018: where
1019: pprv.version_id = document_id
1020: AND pprv.object_type = 'PA_PROJECTS'*/
1021: select PAGE_CONTENT from PA_PAGE_CONTENTS
1022: where object_Type = 'PA_CONTROL_ITEMS'
1023: and pk1_value = document_id;
1024:
1025: l_size number;
1026:
1123:
1124: PROCEDURE START_NOTIFICATION_WF
1125: ( p_item_type In VARCHAR2
1126: ,p_process_name In VARCHAR2
1127: ,p_ci_id In pa_control_items.ci_id%TYPE
1128: ,p_action_id In pa_ci_actions.ci_action_id%TYPE
1129: ,x_item_key Out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1130: ,x_return_status Out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1131: ,x_msg_count Out NOCOPY NUMBER --File.Sql.39 bug 4440895
1157: Invalid_Arg_Exc_CI Exception;
1158:
1159: l_content_id NUMBER := 0;
1160: --cursor to obtain the project id.
1161: CURSOR get_project_id(c_ci_id pa_control_items.ci_id%TYPE)
1162: IS
1163: SELECT project_id
1164: FROM pa_control_items
1165: WHERE ci_id = c_ci_id;
1160: --cursor to obtain the project id.
1161: CURSOR get_project_id(c_ci_id pa_control_items.ci_id%TYPE)
1162: IS
1163: SELECT project_id
1164: FROM pa_control_items
1165: WHERE ci_id = c_ci_id;
1166:
1167: BEGIN
1168: x_msg_count := 0;
1233:
1234:
1235: -- The following API will set all the required attributes for the workflow to function.
1236:
1237: pa_control_items_workflow.set_workflow_attributes(
1238: p_item_type => p_item_type
1239: ,p_process_name => p_process_name
1240: ,p_ci_id => p_ci_id
1241: ,p_action_id => p_action_id
1257: RAISE Invalid_Arg_Exc_CI;
1258: END IF;
1259:
1260: IF l_debug_mode = 'Y' THEN
1261: pa_debug.g_err_stage:= 'returned from pa_control_items_workflow.set_workflow_attributes';
1262: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1263: END IF;
1264:
1265:
1335: CLOSE get_project_id;
1336: END IF;
1337:
1338: FND_MSG_PUB.add_exc_msg
1339: ( p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW'
1340: ,p_procedure_name => 'START_NOTIFICATION_WF'
1341: ,p_error_text => x_msg_data);
1342:
1343: IF l_debug_mode = 'Y' THEN
1358:
1359: PROCEDURE set_workflow_attributes
1360: ( p_item_type In VARCHAR2
1361: ,p_process_name In VARCHAR2
1362: ,p_ci_id In pa_control_items.ci_id%TYPE
1363: ,p_action_id In pa_ci_actions.ci_action_id%TYPE
1364: ,p_item_key In NUMBER
1365: ,x_return_status Out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1366: ,x_msg_count Out NOCOPY NUMBER --File.Sql.39 bug 4440895
1391: l_project_number pa_projects_all.segment1%TYPE;
1392: l_customer pa_project_lists_v.customer_name%TYPE;
1393: l_project_manager pa_project_lists_v.person_name%TYPE;
1394: l_org pa_project_lists_v.carrying_out_organization_name%TYPE;
1395: l_owner_id pa_control_items.owner_id%TYPE;
1396: l_owner_role wf_items.owner_role%TYPE; /* Bug 9689141 */
1397: l_action_number pa_ci_actions_v.ci_action_number%TYPE;
1398: l_action_request pa_ci_actions_v.comment_text%TYPE;
1399: l_action_requestor pa_ci_actions_v.create_name%TYPE;
1415: l_priority_name pa_lookups.meaning%TYPE;
1416: l_comment_text pa_ci_comments.comment_text%TYPE;
1417: l_loop_var1 NUMBER := 1;
1418: l_record_version_number NUMBER;
1419: l_ci_owner_id pa_control_items.owner_id%TYPE;
1420: l_ci_owner_name VARCHAR2(2000);
1421: l_ci_status_code pa_control_items.status_code%TYPE;
1422: l_ci_status_name VARCHAR2(2000);
1423: l_action_type pa_ci_actions_v.action_type%TYPE;
1417: l_loop_var1 NUMBER := 1;
1418: l_record_version_number NUMBER;
1419: l_ci_owner_id pa_control_items.owner_id%TYPE;
1420: l_ci_owner_name VARCHAR2(2000);
1421: l_ci_status_code pa_control_items.status_code%TYPE;
1422: l_ci_status_name VARCHAR2(2000);
1423: l_action_type pa_ci_actions_v.action_type%TYPE;
1424: l_action_type_code pa_ci_actions_v.action_type_code%TYPE;
1425: l_last_updated pa_ci_actions_v.last_update_date%TYPE;
1452: pcc.class_code classification,
1453: pci.record_version_number record_version_number,
1454: pl.meaning ci_type_class,
1455: pcb.ci_type_class_code
1456: FROM pa_control_items pci,
1457: pa_ci_types_tl pct,
1458: pa_ci_types_b pcb,
1459: pa_lookups pl,
1460: pa_class_codes pcc
1504: select pci.status_code,
1505: pps.project_status_name,
1506: pci.owner_id,
1507: hzp.party_name
1508: from pa_control_items pci,
1509: pa_project_statuses pps,
1510: hz_parties hzp
1511: where pci.ci_id = p_ci_id AND
1512: pci.status_code=pps.project_status_code AND
1517:
1518:
1519:
1520: --This cursor gets the users to whom the notification should be sent to.
1521: CURSOR get_notification_list(c_owner_id pa_control_items.owner_id%TYPE)
1522: IS
1523: --select user_name, party_name, email_address
1524: --from (
1525: select fu.user_name, hp.party_name, hp.email_address
1555: lkp.lookup_type = c_lookup_type
1556: and lkp.lookup_code = c_lookup_code;
1557:
1558: --This cursor gets the change owner comment text.
1559: Cursor get_comment_text(c_ci_id pa_control_items.ci_id%TYPE)
1560: IS
1561: select comment_text
1562: from pa_ci_comments
1563: where ci_comment_id =
1586: Cursor get_task_info is
1587: select ppe.name,
1588: ppe.element_number
1589: from pa_proj_elements ppe,
1590: pa_control_items ci
1591: where
1592: ppe.proj_element_id = ci.object_id
1593: and ppe.project_id = ci.project_id
1594: and ci.object_type = 'PA_TASKS'
1601: where customer_id = p_cus_id;
1602:
1603:
1604: -- This table contains PartyId's of all involved in hirarchy
1605: type l_table is table of pa_control_items.owner_id%TYPE index by binary_integer;
1606: PartyId_Tbl l_table ;
1607:
1608: -- This table checks for duplicates of PartyId's of all involved in hirarchy
1609: type l_table_dupck is table of CHAR index by binary_integer;
2201: CLOSE get_comment_text;
2202: END IF;
2203:
2204: FND_MSG_PUB.add_exc_msg
2205: ( p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW'
2206: ,p_procedure_name => 'set_workflow_attributes'
2207: ,p_error_text => x_msg_data);
2208:
2209: IF l_debug_mode = 'Y' THEN
2266: l_item_key pa_wf_processes.item_key%TYPE;
2267:
2268: Cursor getRecordVersionNumber IS
2269: select record_version_number
2270: from pa_control_items
2271: where ci_id = l_ci_id;
2272:
2273: -- Added the cursor for bug# 3877985 Issue# 2
2274: CURSOR get_fnd_usr( p_party_id NUMBER) IS
2284: where ci_action_id = l_ci_action_id;
2285:
2286: BEGIN
2287: -- Initialize the Error Stack
2288: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_WORKFLOW.CLOSE_CI_ACTION');
2289:
2290:
2291: l_ci_id := wf_engine.GetItemAttrNumber
2292: ( itemtype => itemtype,
2426: CLOSE getRecordVersionNumber;
2427:
2428: If (l_return_status = fnd_api.g_ret_sts_success) then
2429:
2430: PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
2431: p_api_version => 1.0,
2432: p_init_msg_list => fnd_api.g_true,
2433: p_commit => FND_API.g_false,
2434: p_validate_only => FND_API.g_true,
2476: resultout := wf_engine.eng_completed||':'||'F';
2477: WHEN OTHERS THEN
2478: ROLLBACK TO CLOSE_CI_ACTION;
2479: resultout := wf_engine.eng_completed||':'||'F';
2480: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW',
2481: p_procedure_name => 'CLOSE_CI_ACTIONS',
2482: p_error_text => SUBSTRB(SQLERRM,1,240));
2483: RAISE;
2484: END CLOSE_CI_ACTION;
2542: l_item_key pa_wf_processes.item_key%TYPE;
2543:
2544: Cursor getRecordVersionNumber IS
2545: select record_version_number
2546: from pa_control_items
2547: where ci_id = l_ci_id;
2548: BEGIN
2549: -- Initialize the Error Stack
2550: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_WORKFLOW.KEEP_OPEN');
2546: from pa_control_items
2547: where ci_id = l_ci_id;
2548: BEGIN
2549: -- Initialize the Error Stack
2550: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_WORKFLOW.KEEP_OPEN');
2551:
2552:
2553:
2554: l_ci_id := wf_engine.GetItemAttrNumber
2650: resultout := wf_engine.eng_completed||':'||'F';
2651: WHEN OTHERS THEN
2652: ROLLBACK TO KEEP_OPEN;
2653: resultout := wf_engine.eng_completed||':'||'F';
2654: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW',
2655: p_procedure_name => 'KEEP_OPEN',
2656: p_error_text => SUBSTRB(SQLERRM,1,240));
2657: RAISE;
2658: END KEEP_OPEN;
2869: document := 'An Unexpected Error has occured' ;
2870: document_type := 'text/html';
2871: -- RAISE not needed here.
2872: END show_task_details;
2873: END pa_control_items_workflow;
2874: