1 PACKAGE PA_DCTN_APRV_NOTIFICATION AUTHID CURRENT_USER as
2 /* $Header: PADTNWFS.pls 120.0.12010000.1 2009/07/21 10:59:41 sosharma noship $ */
3
4
5 CURSOR c_proj_info(p_project_id VARCHAR2) IS
6 SELECT project_id project_id
7 ,segment1 project_number
8 ,name project_name
9 ,start_date start_date
10 ,completion_date end_date
11 ,project_type project_type
12 ,carrying_out_organization_id organization_id
13 ,project_status_code project_status
14 FROM PA_PROJECTS
15 WHERE project_id = p_project_id;
16
17 CURSOR c_dctn_hdr(p_dctn_req_id NUMBER) IS
18 SELECT *
19 FROM PA_DEDUCTIONS_ALL
20 WHERE deduction_req_id = p_dctn_req_id;
21
22 CURSOR c_manager( p_manager_id NUMBER ) IS
23 SELECT f.user_id user_id
24 ,f.user_name user_name
25 ,e.first_name||' '||e.last_name full_name
26 FROM FND_USER f
27 ,PA_EMPLOYEES e
28 WHERE f.employee_id = p_manager_id
29 AND f.employee_id = e.person_id;
30
31 CURSOR c_proj_manager (l_project_id NUMBER) IS
32 SELECT ppp.resource_source_id manager_employee_id
33 FROM PA_PROJECT_PARTIES ppp
34 ,PER_ALL_PEOPLE_F pe
35 WHERE ppp.project_id = l_project_id
36 AND ppp.project_role_id = 1
37 AND ppp.resource_type_id = 101
38 AND ppp.resource_source_id = pe.person_id
39 AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date AND pe.effective_end_date
40 AND ppp.object_type = 'PA_PROJECTS'
41 AND TRUNC(SYSDATE) BETWEEN ppp.start_date_active
42 AND NVL(ppp.end_date_active,TRUNC(SYSDATE)+1);
43
44 PROCEDURE Start_Dctn_Aprv_Wf (p_dctn_req_id IN NUMBER
45 ,x_err_stack IN OUT NOCOPY VARCHAR2
46 ,x_err_stage IN OUT NOCOPY VARCHAR2
47 ,x_err_code OUT NOCOPY NUMBER);
48
49 PROCEDURE Select_Project_Manager (itemtype IN VARCHAR2
50 ,itemkey IN VARCHAR2
51 ,actid IN NUMBER
52 ,funcmode IN VARCHAR2
53 ,resultout OUT NOCOPY VARCHAR2);
54
55 PROCEDURE Append_Varchar_To_Clob(p_varchar IN VARCHAR2
56 ,p_clob IN OUT NOCOPY CLOB);
57
58 PROCEDURE Show_Pwp_Notify_Preview (document_id IN VARCHAR2
59 ,display_type IN VARCHAR2
60 ,document IN OUT NOCOPY CLOB
61 ,document_type IN OUT NOCOPY VARCHAR2);
62
63 PROCEDURE Generate_Dctn_Aprv_Notify
64 (p_item_type IN VARCHAR2
65 ,p_item_key IN VARCHAR2
66 ,p_dctn_hdr_rec IN c_dctn_hdr%ROWTYPE
67 ,p_proj_info_rec IN c_proj_info%ROWTYPE
68 ,x_content_id OUT NOCOPY NUMBER);
69
70 PROCEDURE Submit (itemtype IN VARCHAR2
71 ,itemkey IN VARCHAR2
72 ,actid IN NUMBER
73 ,funcmode IN VARCHAR2
74 ,resultout OUT NOCOPY VARCHAR2);
75
76 FUNCTION show_error(p_error_stack IN VARCHAR2,
77 p_error_stage IN VARCHAR2,
78 p_error_message IN VARCHAR2,
79 p_arg1 IN VARCHAR2 DEFAULT null,
80 p_arg2 IN VARCHAR2 DEFAULT null) RETURN VARCHAR2;
81
82 END PA_DCTN_APRV_NOTIFICATION;