1 PACKAGE PA_ASSIGNMENT_APPROVAL_PVT AS
2 /*$Header: PARAAPVS.pls 120.1 2005/08/19 16:47:15 mwasowic noship $*/
3
4
5 --
6 --Organize the approvers in the table in sequential order.
7 --Validate that no duplicate order exists between the approvers.
8 --The pl/sql table should have at least one record.
9 --
10 PROCEDURE Validate_approver_orders
11 ( x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
12
13
14
15 --
16 --Determine if the specified assignment is a new assignment.
17 --A new assignment is one that has not been previously approved.
18 --
19 FUNCTION Is_New_Assignment
20 (
21 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
22 )
23 RETURN VARCHAR2;
24
25
26
27 --
28 --Get the change_id from the pa_assignments_history table.
29 --
30 FUNCTION Get_Change_Id
31 (
32 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
33 )
34 RETURN NUMBER;
35
36 --
37 -- Get lookup_meaning from pa_lookups
38 --
39 FUNCTION get_lookup_meaning ( p_lookup_type IN VARCHAR2
40 ,p_lookup_code IN VARCHAR2)
41 RETURN VARCHAR2;
42
43
44 --
45 --Determine if the specified assignment requires approval.
46 --
47 PROCEDURE Check_Approval_Required
48 (
49 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
50 ,p_new_assignment_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
51 -- ,p_resource_authority_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
52 ,x_approval_required_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
53 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
54 );
55
56
57
58 --
59 --Update the Assignment Approval Status depending on the action performed, and calls schedule API to
60 --update schedule statuses if needed. Any time the Assignment Approval Status need to be updated, this API will
61 --be called. This including when workflow is successful or failure.
62 --
63 --The allowed actions are: 'APPROVE', 'REJECT', 'SUBMIT', 'UPDATE', and 'REVERT'.
64 --
65 PROCEDURE Update_Approval_Status
66 (
67 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
68 ,p_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
69 ,p_record_version_number IN pa_project_assignments.record_version_number%TYPE
70 ,p_note_to_approver IN VARCHAR2 := FND_API.G_MISS_CHAR
71 ,x_apprvl_status_code OUT NOCOPY pa_project_statuses.project_status_code%TYPE --File.Sql.39 bug 4440895
72 ,x_change_id OUT NOCOPY pa_assignments_history.change_id%TYPE --File.Sql.39 bug 4440895
73 ,x_record_version_number OUT NOCOPY pa_project_assignments.record_version_number%TYPE --File.Sql.39 bug 4440895
74 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
75 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
76 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
77 );
78
79
80 --
81 --Get the new assignment approval status after the specified action is performed
82 --
83 --The allowed actions are: 'APPROVE', 'REJECT', 'SUBMIT', 'UPDATE', and 'REVERT'.
84 --
85 PROCEDURE Get_Next_Status_After_Action
86 (
87 p_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
88 ,p_status_code IN pa_project_statuses.project_status_code%TYPE:= FND_API.G_MISS_CHAR
89 ,x_status_code OUT NOCOPY pa_project_statuses.project_status_code%TYPE --File.Sql.39 bug 4440895
90 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
91 );
92
93
94
95 --
96 --This procedure inserts current record in the PA_PROJECT_ASSIGNMENTS into the PA_ASSIGNMENTS_HISTORY table when the
97 -- record's Assignment Approval Status changes from 'APPROVED' to 'WORKING'.
98 --
99 PROCEDURE Insert_Into_Assignment_History
100 (
101 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
102 ,x_change_id OUT NOCOPY pa_assignments_history.change_id%TYPE --File.Sql.39 bug 4440895
103 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
104 );
105
106 --
107 --This procedure abort the workflow approval outstanding for the specific assignment
108 --and update the pending_approval_flag to 'N'
109 --
110 PROCEDURE Abort_Assignment_Approval
111 (
112 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
113 ,p_project_id IN pa_project_assignments.project_id%TYPE
114 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
115 );
116
117
118 --
119 -- Return following record based on p_assignment_is
120 -- x_saved_asmt_rec : assignment record having values in pa_project_assignments
121 -- x_asmt_history_rec : assignment record having values in pa_assignments_history
122 --
123 PROCEDURE get_asmt_and_asmt_history_rec (p_assignment_id IN NUMBER
124 ,x_saved_asmt_rec OUT NOCOPY PA_ASSIGNMENTS_PUB.assignment_rec_type --File.Sql.39 bug 4440895
125 ,x_asmt_history_rec OUT NOCOPY pa_assignments_pub.assignment_rec_type --File.Sql.39 bug 4440895
126 ,x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
127
128
129 END PA_ASSIGNMENT_APPROVAL_PVT ;