1 PACKAGE BODY PA_PJC_CWK_UTILS AS
2 -- $Header: PACWKUTB.pls 120.3 2011/10/31 13:16:18 speddi ship $
3
4 --Function is_rate_based_line
5 --This function is wrapper around the PO API PO_PA_INTEGRATION_GRP.is_rate_based_line
6
7 Function is_rate_based_line (P_Po_Line_Id In Number ,
8 P_Po_Distribution_Id In Number) Return Varchar2
9 Is
10 -- L_PoRateBased BOOLEAN;
11 L_IsRb Varchar2(1) := 'N';
12 l_Found BOOLEAN := FALSE;
13 L_Po_Line_Id Number;
14
15 Begin
16
17 IF p_po_line_id is null and
18 /* Bug 4227213 p_po_distribution_id is null THEN */
19 nvl(p_po_distribution_id ,0) = 0 THEN
20 RETURN 'N';
21 END IF;
22
23 If P_Po_Line_Id is null Then
24 Begin
25 If G_PoLineIdTab.COUNT > 0 Then
26 --Dbms_Output.Put_Line('count > 0');
27 Begin
28 L_Po_Line_Id := G_PoLineIdTab(P_Po_Distribution_Id);
29 l_Found := TRUE;
30 --Dbms_Output.Put_Line('l_found TRUE');
31 Exception
32 When No_Data_Found Then
33 l_Found := FALSE;
34 When Others Then
35 Raise;
36 End;
37
38 End If;
39
40 If Not l_Found Then
41 --Dbms_Output.Put_Line('l_found FALSE');
42
43 If G_PoLineIdTab.COUNT > 999 Then
44 --Dbms_Output.Put_Line('count > 199');
45 G_PoLineIdTab.Delete;
46 End If;
47 Begin
48 --Dbms_Output.Put_Line('select');
49
50 Select Po_Line_Id
51 Into L_Po_Line_Id
52 From Po_Distributions_All
53 Where Po_Distribution_id = P_Po_Distribution_Id;
54
55 G_PoLineIdTab(P_Po_Distribution_Id) := L_Po_Line_Id;
56 --Dbms_Output.Put_Line('after select');
57
58 Exception
59 When No_Data_Found Then
60 --Dbms_Output.Put_Line('wndf ');
61 L_Po_Line_Id := NULL;
62 G_PoLineIdTab(P_Po_Distribution_Id) := NULL;
63 End;
64 End If;
65 End;
66
67 Else
68 L_Po_Line_Id := P_Po_Line_Id;
69 End If;
70
71 /* Added for bug 7331897 */
72 IF l_Po_line_Id is null then
73 RETURN 'N';
74 END IF;
75 /* Ends Added for bug 7331897 */
76
77 Begin
78 If G_IsRbLineTab.COUNT > 0 Then
79 --Dbms_Output.Put_Line('count > 0');
80 Begin
81 L_IsRb := G_IsRbLineTab(l_Po_line_Id);
82 l_Found := TRUE;
83 --Dbms_Output.Put_Line('l_found TRUE');
84 Exception
85 When No_Data_Found Then
86 l_Found := FALSE;
87 When Others Then
88 Raise;
89 End;
90
91 End If;
92
93 If Not l_Found Then
94 --Dbms_Output.Put_Line('l_found FALSE');
95
96 If G_IsRbLineTab.COUNT > 999 Then
97 --Dbms_Output.Put_Line('count > 199');
98 G_IsRbLineTab.Delete;
99 End If;
100 Begin
101 --Dbms_Output.Put_Line('select');
102 If PO_PA_INTEGRATION_GRP.is_rate_based_line(L_PO_Line_Id, P_Po_Distribution_Id) Then
103 L_IsRb := 'Y';
104 Else
105 L_IsRb := 'N';
106 End If;
107
108 G_IsRbLineTab(l_Po_Line_Id) := L_IsRb;
109 --Dbms_Output.Put_Line('after select');
110
111 Exception
112 When No_Data_Found Then
113 --Dbms_Output.Put_Line('wndf ');
114 L_IsRb:= 'N';
115 G_IsRbLineTab(P_Po_Line_Id) := 'N';
116 End;
117 End If;
118 End;
119
120 Return L_IsRb;
121
122 Exception
123 When Others Then
124 Raise;
125 End is_rate_based_line;
126
127
128 --Function Exists_Prj_Cwk_RbTC(P_Org_Id IN NUMBER)
129 --Called from the Project Implementations form when the value for XFACE_CWK_TIMECARDS_FLAG is changed from Y to N or vice versa
130 --Returns 'Y' if there exists project related rate based POs for any of the projects in the given P_Org_Id. Else return 'N'.
131
132 Function Exists_Prj_Cwk_RbTC(P_Org_Id IN NUMBER) RETURN Varchar2
133 Is
134
135 L_Exists Varchar2(1) := 'N';
136 l_Found BOOLEAN := FALSE;
137
138 Begin
139
140 If G_ExCwkRbTCOrgIdTab.COUNT > 0 Then
141 --Dbms_Output.Put_Line('count > 0');
142 Begin
143 L_Exists := G_ExCwkRbTCOrgIdTab(P_Org_Id);
144 l_Found := TRUE;
145 --Dbms_Output.Put_Line('l_found TRUE');
146 Exception
147 When No_Data_Found Then
148 l_Found := FALSE;
149 When Others Then
150 Raise;
151 End;
152
153 End If;
154
155 If Not l_Found Then
156 --Dbms_Output.Put_Line('l_found FALSE');
157
158 If G_ExCwkRbTCOrgIdTab.COUNT > 999 Then
159 --Dbms_Output.Put_Line('count > 199');
160 G_ExCwkRbTCOrgIdTab.Delete;
161 End If;
162 Begin
163 --Dbms_Output.Put_Line('select');
164
165 Select 'Y'
166 Into L_Exists
167 From Dual
168 Where Exists (Select 1
169 From Pa_Projects_All P,
170 Po_Distributions_All Po
171 Where nvl(P.Org_Id, -99) = nvl(P_Org_Id, -99)
172 And P.Project_Id = Po.Project_Id
173 And PA_PJC_CWK_UTILS.is_rate_based_line (
174 Po.Po_Line_Id,
175 Po.Po_Distribution_Id) = 'Y'
176 );
177
178 G_ExCwkRbTCOrgIdTab(P_Org_Id) := L_Exists;
179 --Dbms_Output.Put_Line('after select');
180
181 Exception
182 When No_Data_Found Then
183 --Dbms_Output.Put_Line('wndf ');
184 L_Exists := 'N';
185 G_ExCwkRbTCOrgIdTab(P_Org_Id) := 'N';
186 End;
187
188 End If;
189
190 Return L_Exists;
191
192 Exception
193 When Others Then
194 Raise;
195
196 End Exists_Prj_Cwk_RbTC;
197
198 --Function Is_Cwk_TC_Xface_Allowed(P_Project_Id IN NUMBER)
199 --This function identifies for the given project OU if CWK timecard interface is allowed or not
200 --If enabled then costs must be interfaced as labor costs for CWK
201 --If disabled costs must be interfaced as supplier costs for CWK
202
203 Function Is_Cwk_TC_Xface_Allowed(P_Project_Id IN NUMBER) RETURN Varchar2
204 Is
205
206 L_Allowed Varchar2(1) := 'N';
207 l_Found BOOLEAN := FALSE;
208 L_Project_Id Pa_projects_all.project_id%type; /*Added varaible for bug#12922795 */
209
210 Begin
211
212 L_Project_Id := NVL(P_Project_Id,0); /*Modified for bug#12922795 */
213
214 If G_CwkTCXfaceAllowedTab.COUNT > 0 Then
215 --Dbms_Output.Put_Line('count > 0');
216
217 Begin
218 L_Allowed := G_CwkTCXfaceAllowedTab(L_Project_Id); /*Modified for bug#12922795 */
219 l_Found := TRUE;
220 --Dbms_Output.Put_Line('l_found TRUE');
221
222 Exception
223 When No_Data_Found Then
224 l_Found := FALSE;
225 When Others Then
226 Raise;
227 End;
228
229 End If;
230
231 If Not l_Found Then
232 --Dbms_Output.Put_Line('l_found FALSE');
233
234 If G_CwkTCXfaceAllowedTab.COUNT > 999 Then
235 --Dbms_Output.Put_Line('count > 199');
236
237 G_CwkTCXfaceAllowedTab.Delete;
238 End If;
239 Begin
240 --Dbms_Output.Put_Line('select');
241
242 Select I.Xface_Cwk_Timecards_Flag
243 Into L_Allowed
244 From Pa_Projects_All P,
245 Pa_Implementations_All I
246 Where P.Project_Id = L_Project_Id /*Modified for bug#12922795 */
247 AND p.org_id = i.org_id; -- bug 5365269
248 -- And nvl(P.Org_Id, -99) = nvl(I.Org_Id, -99); -- bug 5365269
249
250 G_CwkTCXfaceAllowedTab(L_Project_Id) := L_Allowed; /*Modified for bug#12922795 */
251 --Dbms_Output.Put_Line('after select');
252
253
254 Exception
255 When No_Data_Found Then
256 --Dbms_Output.Put_Line('wndf ');
257
258 L_Allowed := 'N';
259 G_CwkTCXfaceAllowedTab(L_Project_Id) := 'N'; /*Modified for bug#12922795 */
260 End;
261 End If;
262 Return L_Allowed;
263 Exception
264 When Others Then
265 Raise;
266 End Is_Cwk_TC_Xface_Allowed;
267
268
269 END PA_PJC_CWK_UTILS;