1 PACKAGE BODY PA_PJC_CWK_UTILS AS
2 -- $Header: PACWKUTB.pls 120.1.12010000.2 2008/08/21 11:36:28 vbkumar 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
209 Begin
210
211 If G_CwkTCXfaceAllowedTab.COUNT > 0 Then
212 --Dbms_Output.Put_Line('count > 0');
213 Begin
214 L_Allowed := G_CwkTCXfaceAllowedTab(P_Project_Id);
215 l_Found := TRUE;
216 --Dbms_Output.Put_Line('l_found TRUE');
217 Exception
218 When No_Data_Found Then
219 l_Found := FALSE;
220 When Others Then
221 Raise;
222 End;
223
224 End If;
225
226 If Not l_Found Then
227 --Dbms_Output.Put_Line('l_found FALSE');
228
229 If G_CwkTCXfaceAllowedTab.COUNT > 999 Then
230 --Dbms_Output.Put_Line('count > 199');
231 G_CwkTCXfaceAllowedTab.Delete;
232 End If;
233 Begin
234 --Dbms_Output.Put_Line('select');
235
236 Select I.Xface_Cwk_Timecards_Flag
237 Into L_Allowed
238 From Pa_Projects_All P,
239 Pa_Implementations_All I
240 Where P.Project_Id = P_Project_Id
241 AND p.org_id = i.org_id; -- bug 5365269
242 -- And nvl(P.Org_Id, -99) = nvl(I.Org_Id, -99); -- bug 5365269
243
244 G_CwkTCXfaceAllowedTab(P_Project_Id) := L_Allowed;
245 --Dbms_Output.Put_Line('after select');
246
247 Exception
248 When No_Data_Found Then
249 --Dbms_Output.Put_Line('wndf ');
250 L_Allowed := 'N';
251 G_CwkTCXfaceAllowedTab(P_Project_Id) := 'N';
252 End;
253
254 End If;
255
256 Return L_Allowed;
257 Exception
258 When Others Then
259 Raise;
260 End Is_Cwk_TC_Xface_Allowed;
261
262
263 END PA_PJC_CWK_UTILS;