1 PACKAGE BODY PA_ORG_CLIENT_EXTN AS
2 -- $Header: PAXORCEB.pls 120.1 2005/08/19 17:15:30 mwasowic noship $
3
4 PROCEDURE verify_org_change(X_insert_update_mode IN VARCHAR2
5 , X_calling_module IN VARCHAR2
6 , X_project_id IN NUMBER
7 , X_task_id IN NUMBER
8 , X_old_organization_id IN NUMBER
9 , X_new_organization_id IN NUMBER
10 , X_project_type IN VARCHAR2
11 , X_project_start_date IN DATE
12 , X_project_end_date IN DATE
13 , X_public_sector_flag IN VARCHAR2
14 , X_task_manager_person_id IN NUMBER
15 , X_Service_type IN VARCHAR2
16 , X_task_start_date IN DATE
17 , X_task_end_date IN DATE
18 , X_entered_by_user_id IN NUMBER
19 , X_attribute_category IN VARCHAR2
20 , X_attribute1 IN VARCHAR2
21 , X_attribute2 IN VARCHAR2
22 , X_attribute3 IN VARCHAR2
23 , X_attribute4 IN VARCHAR2
24 , X_attribute5 IN VARCHAR2
25 , X_attribute6 IN VARCHAR2
26 , X_attribute7 IN VARCHAR2
27 , X_attribute8 IN VARCHAR2
28 , X_attribute9 IN VARCHAR2
29 , X_attribute10 IN VARCHAR2
30 , X_pm_product_code IN VARCHAR2
31 , X_pm_project_reference IN VARCHAR2
32 , X_pm_task_reference IN VARCHAR2
33 , X_functional_security_flag IN VARCHAR2
34 , X_outcome OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
35 IS
36
37 -- ====================================================================
38 -- Default logic of client extension is as follows
39 -- ====================================================================
40 -- The default client extension does not do any validation for organization change.
41 -- User needs to add code or uncomment the commented code to perform the validations.
42 -- The validations inside the client extension will not be performed if your
43 -- responsibility has the Function 'Projects:Org:Update:Override Standard Checks'
44 -- assigned through Functional Security.
45
46 -- Define local variables
47
48 X_cdl_check NUMBER;
49 X_dil_check NUMBER;
50 X_rdl_check NUMBER;
51
52 BEGIN
53
54 -- Initialize the X_outcome parameter to NULL. If all the checks
55 -- go through then the X_outcome parameter is returned as NULL .
56 -- If a check fails validation, the value of the
57 -- X_outcome variable should be set to an error code .
58
59 X_outcome := NULL;
60
61 -- If the user responsibility has the access to
62 -- Function 'Projects:Org:Update:Override Standard Checks'
63 -- org without performing any checks.
64
65 if X_functional_security_flag = 'Y' then
66 null;
67 else
68 null; /* Added for bug 2981386 */
69
70 /* USER SHOULD ADD CODE HERE OR UNCOMMENT THE BELOW CODE
71 TO CUSTOMIZE THE CLIENT EXTENSION */
72
73 /* Commented for bug 2981386
74 --
75 -- Check If CDLs exist for the project or the task.
76 --
77 X_cdl_check := 0;
78 --
79 if X_task_id is null then -- Project Org change check
80 select count(*)
81 into X_cdl_check
82 from sys.dual
83 where exists (select null
84 from pa_expenditure_items_ALL pai,
85 pa_tasks t, pa_cost_distribution_lines_ALL pcd
86 where pai.task_id = t.task_id
87 and pai.expenditure_item_id = pcd.expenditure_item_id
88 and t.project_id =X_project_id);
89 --
90 else -- Task Org Change check
91 select count(*)
92 into X_cdl_check
93 from sys.dual
94 where exists (select null
95 from pa_expenditure_items_all pai,
96 pa_cost_distribution_lines_aLL pcd
97 where pai.expenditure_item_id
98 = pcd.expenditure_item_id
99 and pai.task_id = X_task_id);
100 end if;
101
102 if X_cdl_check <> 0 then
103 x_outcome := 'PA_PR_CANT_CHG_PROJ_ORG';
104 return;
105 end if;
106
107 --
108 -- Check if any Revenue has been generated
109 --
110 X_rdl_check := 0;
111 --
112 if X_task_id is null then -- Project Org change check
113 select count(*)
114 into X_rdl_check
115 from sys.dual
116 where exists (select null
117 FROM pa_draft_revenue_items
118 where project_id = x_project_id);
119 --
120 else -- Task Org Change check
121 select count(*)
122 into X_rdl_check
123 from sys.dual
124 where exists (select null
125 from pa_draft_revenue_items
126 where project_id = x_project_id
127 and task_id in
128 (select task_id
129 from pa_tasks
130 connect by prior task_id = parent_task_id
131 start with task_id = x_task_id));
132 end if;
133
134 if X_rdl_check <> 0 then
135 x_outcome := 'PA_PR_CANT_CHG_PROJ_ORG';
136 return;
137 end if;
138
139 --
140 -- Check if any Draft Invoice Items exist
141 --
142 X_dil_check := 0;
143 --
144 if X_task_id is null then -- Project Org change check
145 select count(*)
146 into X_dil_check
147 from sys.dual
148 where exists (select null
149 from pa_draft_invoice_items
150 where project_id = x_project_id);
151 --
152 else -- Task Org Change check
153 select count(*)
154 into X_dil_check
155 from sys.dual
156 where exists (select null
157 from pa_draft_invoice_items
158 where project_id = x_project_id
159 and task_id in
160 (select task_iD
161 from pa_tasks
162 connect by prior task_id = parent_task_id
163 start with task_id = X_task_id));
164 end if;
165
166 if X_dil_check <> 0 then
167 x_outcome := 'PA_PR_CANT_CHG_PROJ_ORG';
168 return;
169 end if;
170 --
171 */
172 end if;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 -- Add your exception handling logic here
177 NULL;
178 /* x_outcome := sqlcode; commented for bug 2981386 */
179 x_outcome := 'PA_AL_CE_FAILED'; /* Added for bug 2981386 */
180
181 END;
182
183 END PA_ORG_CLIENT_EXTN ;