1 PACKAGE BODY PA_CC_IDENT
2 -- $Header: PACCINTB.pls 120.5 2006/06/30 16:00:10 eyefimov noship $
3 AS
4
5 G_PrevPrvdrOrgId NUMBER;
6 G_PrevCCProcessIOCode VARCHAR2(1);
7 G_PrevCCProcessIUCode VARCHAR2(1);
8 G_PrevTaskId NUMBER;
9 G_PrevSysLink VARCHAR2(30);
10 G_PrevRecvrOrgnId NUMBER;
11 G_PrevCCPrjProcessFlag VARCHAR2(1);
12 G_PrevPrjOrgId NUMBER;
13
14
15 P_DEBUG_MODE BOOLEAN := pa_cc_utils.g_debug_mode;
16
17 PROCEDURE PRINT_MESSAGE(p_msg varchar2) IS
18
19 BEGIN
20 If p_msg is NOT NULL then
21 --r_debug.r_msg(p_msg => p_msg);
22 Null;
23 End if;
24 IF P_DEBUG_MODE THEN
25 pa_cc_utils.LOG_MESSAGE('PRINT_MESSAGE: ' || p_msg);
26 END IF;
27
28
29 END PRINT_MESSAGE;
30
31 -- This procedure will be called from Forcast API.
32 -- This wrapper API is created over the main identify procedure.
33 -- This API derives the provider,receiver orgs and assumes that
34 -- to process the records in plsql tables the param x_statusTab should be null
35 -- other wise it will be treated as a error records.
36 PROCEDURE PA_CC_IDENTIFY_TXN_FI(
37 P_ExpOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
38 P_ExpOrgidTab IN PA_PLSQL_DATATYPES.IdTabTyp,
39 P_ProjectIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
40 P_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
41 P_ExpItemDateTab IN PA_PLSQL_DATATYPES.DateTabTyp,
42 P_ExpItemIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
43 P_PersonIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
44 P_ExpTypeTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
45 P_SysLinkTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
46 P_PrjOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
47 P_PrjOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
48 P_TransSourceTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
49 P_NLROrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
50 P_PrvdrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
51 P_RecvrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
52 /* Added nocopy for 2672653 */
53 X_StatusTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
54 X_CrossChargeTypeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char3TabTyp,
55 X_CrossChargeCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
56 X_PrvdrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
57 X_RecvrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
58 X_RecvrOrgIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
59 X_PrvdrOrgIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
60 X_Error_Stage OUT NOCOPY VARCHAR2,
61 X_Error_Code OUT NOCOPY NUMBER) IS
62
63 l_PrevProjectId pa_projects_all.project_id%type := Null;
64 l_project_org_id pa_projects_all.org_id%type := Null;
65 l_recvr_organization_id pa_projects_all.org_id%type := Null;
66 l_prev_recvr_organization_id pa_projects_all.org_id%type := Null;
67 l_prev_cc_prj_process_flag varchar2(10);
68 l_cc_prj_process_flag varchar2(10);
69 l_PrevRecvrOrgId pa_projects_all.org_id%type := Null;
70 l_MinRecs PLS_INTEGER;
71 l_MaxRecs PLS_INTEGER;
72 l_current_process_io_code pa_implementations_all.cc_process_io_code%type;
73 l_current_process_iu_code pa_implementations_all.cc_process_iu_code%type;
74 l_PrevCCProcessIOCode pa_implementations_all.cc_process_io_code%type;
75 l_PrevCCProcessIUCode pa_implementations_all.cc_process_iu_code%type;
76 l_prevexporgid pa_projects_all.org_id%type := Null;
77 l_CCProcessIOCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
78 l_CCProcessIUCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
79 l_CCPrjFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
80 l_current_org_id pa_projects_all.org_id%type := Null;
81 l_PrevPrvdrOrgId pa_projects_all.org_id%type := Null;
82
83
84 BEGIN
85 pa_cc_utils.set_curr_function('PA_CC_IDENTIFY_TXN_FI');
86 l_MinRecs := P_SysLinkTab.FIRST;
87 l_MaxRecs := P_SysLinkTab.LAST;
88
89 FOR j IN l_MinRecs..l_MaxRecs LOOP
90 IF X_StatusTab(j) IS NOT NULL THEN
91 -- If the FIs are already erroneous which will be indicated
92 -- by this PL/SQL able then do not process CC identification for th at
93
94 -- forecast items
95
96 IF P_DEBUG_MODE THEN
97 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.01: This FI is already erroneous and needs no CC identification');
98 END IF;
99
100 NULL ;
101
102 ELSE
103 --
104 -- Determine Current Operating Unit
105 --
106 IF P_DEBUG_MODE THEN
107 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.02: Determine the Expenditure OU and its cross charge options');
108 END IF;
109
110 IF nvl(l_prevexporgid,-99) <> P_ExpOrgidTab(j) Then
111 IF P_DEBUG_MODE THEN
112 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.03: Derive the cross charge options from pa_implementations');
113 END IF;
114
115 SELECT imp.org_id,
116 nvl(imp.cc_process_io_code,'N'),
117 nvl(imp.cc_process_iu_code,'N')
118 INTO l_current_org_id,
119 l_current_process_io_code,
120 l_current_process_iu_code
121 FROM pa_implementations_all imp
122 WHERE imp.org_id = P_ExpOrgidTab(j) ; -- bug 5365276
123 -- WHERE nvl(imp.org_id,-99) = nvl( P_ExpOrgidTab(j), -99) ; -- bug 5365276
124
125
126 l_PrevPrvdrOrgId := l_current_org_id ;
127 l_PrevCCProcessIOCode := l_current_process_io_code ;
128 l_PrevCCProcessIUCode := l_current_process_iu_code ;
129 l_prevexporgid := P_ExpOrgidTab(j);
130
131 ELSE
132
133 IF P_DEBUG_MODE THEN
134 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.04: Retrive cross charge options from cache');
135 END IF;
136 l_current_org_id := l_PrevPrvdrOrgId;
137 l_current_process_io_code := l_PrevCCProcessIOCode;
138 l_current_process_iu_code := l_PrevCCProcessIUCode;
139
140 END IF;
141
142 --
143 -- End get current operating unit
144 --
145 IF nvl(l_PrevProjectId,-99) <> P_ProjectIdTab(j) THEN
146 --
147 -- If the current project id is the same as the previous project id
148
149 -- then we do not need to join to the projects table to get the project
150
151 -- org id which is the receiver OU.
152 --
153 IF P_DEBUG_MODE THEN
154 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.05: Derive receiving org id for the given project');
155 END IF;
156
157
158 SELECT p.carrying_out_organization_id,
159 decode(sl.LABOR_NON_LABOR_FLAG,
160 'Y', p.cc_process_labor_flag,
161 p.cc_process_nl_flag)
162 INTO l_recvr_organization_id,
163 l_cc_prj_process_flag
164 FROM pa_system_linkages sl,
165 pa_projects_all p
166 WHERE p.project_id = P_ProjectIdTab(j)
167 AND sl.function = P_SysLinkTab(j) ;
168
169 l_PrevProjectId := P_ProjectIdTab(j);
170 l_prev_recvr_organization_id := l_recvr_organization_id;
171 l_prev_cc_prj_process_flag := l_cc_prj_process_flag;
172
173
174 ELSE
175 IF P_DEBUG_MODE THEN
176 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.06: Retreive receiving org id cache');
177 END IF;
178
179 l_recvr_organization_id := l_prev_recvr_organization_id;
180 l_cc_prj_process_flag := l_prev_cc_prj_process_flag;
181
182
183 END IF;
184
185 --Assign the out varialbe with the derived values
186 l_CCPrjFlagTab(j) := l_cc_prj_process_flag;
187 l_CCProcessIUCodeTab(j) := l_current_process_iu_code;
188 l_CCProcessIOCodeTab(j) := l_current_process_io_code;
189 X_PrvdrOrganizationIdTab(j) := P_ExpOrganizationIdTab(j);
190 X_RecvrOrganizationIdTab(j) := l_recvr_organization_id ;
191 X_RecvrOrgIdTab(j) := P_PrjOrgIdTab(j);
192 X_PrvdrOrgIdTab(j) := l_current_org_id;
193
194 IF P_DEBUG_MODE THEN
195 pa_cc_utils.log_message
196 ('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.07: Calling Client Extension to override the PRVDR_RECVR orgs');
197 END IF;
198
199 -- Call client extension to override the provider and receiver organizations.
200 PA_CC_IDENT_CLIENT_EXTN.OVERRIDE_PRVDR_RECVR (
201 P_PrvdrOrganizationId => X_PrvdrOrganizationIdTab(j),
202 P_PrvdrOrgId => P_ExpOrgidTab(j),
203 P_RecvrOrganizationId => X_RecvrOrganizationIdTab(j),
204 P_RecvrOrgId => X_RecvrOrgIdTab(j),
205 P_TransId => P_ExpItemIdTab(j),
206 P_SysLink => P_SysLinkTab(j),
207 P_calling_mode => 'FORECAST',
208 X_Status => X_StatusTab(j),
209 X_PrvdrOrganizationId => X_PrvdrOrganizationIdTab(j),
210 X_RecvrOrganizationId => X_RecvrOrganizationIdTab(j),
211 X_Error_Stage => X_Error_Stage,
212 X_Error_Code => X_Error_Code );
213
214 IF P_DEBUG_MODE THEN
215 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || 'P_ProjectIdTab='||P_ProjectIdTab(j)||'P_PrvdrOrganizationId='||X_PrvdrOrganizationIdTab(j)
216 ||'X_RecvrOrganizationIdTab='||X_RecvrOrganizationIdTab(j)||'X_RecvrOrgIdTab='
217 ||X_RecvrOrgIdTab(j)||'P_SysLinkTab='||P_SysLinkTab(j)
218 ||'l_CCPrjFlagTab='||l_CCPrjFlagTab(j)||'l_CCProcessIUCodeTab='
219 ||l_CCProcessIUCodeTab(j)||'l_CCProcessIOCodeTab='||l_CCProcessIOCodeTab(j));
220 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.08: End of Client Extension to override the PRVDR_RECVR orgs');
221 END IF;
222
223 END IF; -- end of status_tab
224
225 END LOOP;
226 IF P_DEBUG_MODE THEN
227 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.09: Calling PA_CC_GET_CROSS_CHARGE_TYPE api');
228 END IF;
229
230
231 PA_CC_IDENT.PA_CC_GET_CROSS_CHARGE_TYPE (
232 P_PrvdrOrganizationIdTab => X_PrvdrOrganizationIdTab
233 ,P_RecvrOrganizationIdTab => X_RecvrOrganizationIdTab
234 ,P_ProjectIdTab => P_ProjectIdTab
235 ,P_TaskIdTab => P_TaskIdTab
236 ,P_SysLinkTab => P_SysLinkTab
237 ,P_ExpItemIdTab => P_ExpItemIdTab
238 ,P_PersonIdTab => P_PersonIdTab
239 ,P_ExpItemDateTab => P_ExpItemDateTab
240 ,P_PrvdrOrgIdTab => P_ExpOrgidTab
241 ,P_RecvrOrgIdTab => X_RecvrOrgIdTab
242 ,P_PrvdrLEIdTab => P_PrvdrLEIdTab
243 ,P_RecvrLEIdTab => P_RecvrLEIdTab
244 ,P_TransSourceTab => P_TransSourceTab
245 ,P_CCProcessIOCodeTab => l_CCProcessIOCodeTab
246 ,P_CCProcessIUCodeTab => l_CCProcessIUCodeTab
247 ,P_CCPrjFlagTab => l_CCPrjFlagTab
248 ,P_calling_mode => 'FORECAST'
249 ,X_StatusTab => X_StatusTab
250 ,X_CrossChargeTypeTab => X_CrossChargeTypeTab
251 ,X_CrossChargeCodeTab => X_CrossChargeCodeTab
252 ,X_Error_Stage => X_Error_Stage
253 ,X_Error_Code => X_Error_Code );
254 IF P_DEBUG_MODE THEN
255 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || '20.05.10: End of PA_CC_GET_CROSS_CHARGE_TYPE api');
256 END IF;
257
258 pa_cc_utils.reset_curr_function;
259
260 EXCEPTION
261
262 WHEN OTHERS THEN
263 IF P_DEBUG_MODE THEN
264 pa_cc_utils.log_message('Failed in PA_CC_IDENTIFY_TXN_FI Error:'||SQLCODE||SQLERRM);
265 END IF;
266 RAISE ;
267
268 END PA_CC_IDENTIFY_TXN_FI;
269
270 PROCEDURE PA_CC_IDENTIFY_TXN_ADJ (
271 P_ExpOrganizationId IN NUMBER,
272 P_ExpOrgid IN NUMBER,
273 P_ProjectId IN NUMBER,
274 P_TaskId IN NUMBER,
275 P_ExpItemDate IN DATE,
276 P_ExpItemId IN NUMBER,
277 P_ExpType IN VARCHAR2,
278 P_PersonId IN NUMBER,
279 P_SysLink IN VARCHAR2,
280 P_PrjOrganizationId IN NUMBER,
281 P_PrjOrgId IN NUMBER,
282 P_TransSource IN VARCHAR2,
283 P_NLROrganizationId IN NUMBER,
284 P_PrvdrLEId IN NUMBER,
285 P_RecvrLEId IN NUMBER,
286 X_Status IN OUT NOCOPY VARCHAR2,
287 X_CrossChargeType IN OUT NOCOPY VARCHAR2,
288 X_CrossChargeCode IN OUT NOCOPY VARCHAR2,
289 X_PrvdrOrganizationId IN OUT NOCOPY NUMBER,
290 X_RecvrOrganizationId IN OUT NOCOPY NUMBER,
291 X_RecvrOrgId IN OUT NOCOPY NUMBER,
292 X_Error_Stage OUT NOCOPY VARCHAR2,
293 X_Error_Code OUT NOCOPY NUMBER,
294 /* Added calling module for 3234973 */
295 X_Calling_Module IN VARCHAR2)
296
297 IS
298
299 -- This procedure will be called by adjustments. Because adjustments will
300 -- not pass the values as tables, this wrapper procedure is created over
301 -- the main identify procedure. This procedure will assign the values passed
302 -- to tables and then call the main identify procedure.
303
304 -- The following PL/SQL tables are created to store the values passed in to
305 -- this procedure.
306
307 l_ExpOrganizationIdTab PA_PLSQL_DATATYPES.IdTabTyp;
308 l_ExpOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
309 l_ProjectIdTab PA_PLSQL_DATATYPES.IdTabTyp;
310 l_TaskIdTab PA_PLSQL_DATATYPES.IdTabTyp;
311 l_ExpItemDateTab PA_PLSQL_DATATYPES.DateTabTyp;
312 l_ExpItemIdTab PA_PLSQL_DATATYPES.IdTabTyp;
313 l_PersonIdTab PA_PLSQL_DATATYPES.IdTabTyp;
314 l_ExpTypeTab PA_PLSQL_DATATYPES.Char30TabTyp;
315 l_SysLinkTab PA_PLSQL_DATATYPES.Char30TabTyp;
316 l_PrjOrganizationIdTab PA_PLSQL_DATATYPES.IdTabTyp;
317 l_PrjorgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
318 l_TransSourceTab PA_PLSQL_DATATYPES.Char30TabTyp;
319 l_NLROrganizationIdTab PA_PLSQL_DATATYPES.IdTabTyp;
320 l_PrvdrLEIdTab PA_PLSQL_DATATYPES.IdTabTyp;
321 l_RecvrLEIdTab PA_PLSQL_DATATYPES.IdTabTyp;
322 l_CrossChargeTypeTab PA_PLSQL_DATATYPES.Char3TabTyp;
323 l_CrossChargeCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
324 l_CCProcessIOCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
328 l_PrvdrOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
325 l_CCProcessIUCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
326 l_PrvdrOrganizationIdTab PA_PLSQL_DATATYPES.IdTabTyp;
327 l_RecvrOrganizationIdTab PA_PLSQL_DATATYPES.IdTabTyp;
329 l_RecvrOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
330 l_Error_Stage VARCHAR2(2000);
331 l_Error_Code NUMBER;
332 l_StageTab PA_PLSQL_DATATYPES.NewAmtTabTyp;
333 l_StatusTab PA_PLSQL_DATATYPES.Char30TabTyp;
334
335 BEGIN
336
337 -- Assign all the input values to the tables. These tables will be passed
338 -- to the the main identification procedure.
339
340 pa_cc_utils.set_curr_function('PA_CC_IDENTIFY_TXN_ADJ');
341 IF P_DEBUG_MODE THEN
342 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_ADJ: ' || '10: Assign all the input values to the tables');
343 END IF;
344
345 l_ExpOrganizationIdTab(1) := P_ExpOrganizationId ;
346 l_ExpOrgIdTab(1) := P_ExpOrgId ;
347 l_ProjectIdTab(1) := P_ProjectId;
348 l_TaskIdTab(1) := P_TaskId;
349 l_ExpItemDateTab(1) := P_ExpItemDate;
350 l_ExpItemIdTab(1) := P_ExpItemId;
351 l_PersonIdTab(1) := P_PersonId;
352 l_ExpTypeTab(1) := P_ExpType;
353 l_SysLinkTab(1) := P_SysLink;
354 l_PrjOrganizationIdTab(1) := P_PrjOrganizationId;
355 l_PrjorgIdTab(1) := P_PrjorgId;
356 l_TransSourceTab(1) := P_TransSource;
357 l_NLROrganizationIdTab(1) := P_NLROrganizationId;
358 l_PrvdrLEIdTab(1) := NULL ;
359 l_RecvrLEIdTab(1) := NULL ;
360 l_StatusTab(1) := NULL ;
361 l_Error_Stage := X_Error_Stage;
362 l_Error_Code := X_Error_Code;
363
364 IF P_DEBUG_MODE THEN
365 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_ADJ: ' || '20: Calling procedure PA_CC_IDENT.PA_CC_IDENTIFY_TXN');
366 END IF;
367
368 PA_CC_IDENT.PA_CC_IDENTIFY_TXN(
369 P_ExpOrganizationIdTab => l_ExpOrganizationIdTab,
370 P_ExpOrgidTab => l_ExpOrgidTab,
371 P_ProjectIdTab => l_ProjectIdTab,
372 P_TaskIdTab => l_TaskIdTab,
373 P_ExpItemDateTab => l_ExpItemDateTab,
374 P_ExpItemIdTab => l_ExpItemIdTab,
375 P_PersonIdTab => l_PersonIdTab,
376 P_ExpTypeTab => l_ExpTypeTab,
377 P_SysLinkTab => l_SysLinkTab,
378 P_PrjOrganizationIdTab => l_PrjOrganizationIdTab,
379 P_PrjOrgIdTab => l_PrjOrgIdTab,
380 P_TransSourceTab => l_TransSourceTab,
381 P_NLROrganizationIdTab => l_NLROrganizationIdTab,
382 P_PrvdrLEIdTab => l_PrvdrLEIdTab,
383 P_RecvrLEIdTab => l_RecvrLEIdTab,
384 X_StatusTab => l_StatusTab,
385 X_CrossChargeTypeTab => l_CrossChargeTypeTab,
386 X_CrossChargeCodeTab => l_CrossChargeCodeTab,
387 X_PrvdrOrganizationIdTab => l_PrvdrOrganizationIdTab,
388 X_RecvrOrganizationIdTab => l_RecvrOrganizationIdTab,
389 X_RecvrOrgIdTab => l_RecvrOrgIdTab,
390 X_Error_Stage => l_Error_Stage,
391 X_Error_Code => l_Error_Code,
392 /* Added calling module for 3234973 */
393 X_Calling_Module => X_Calling_Module);
394
395 IF P_DEBUG_MODE THEN
396 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_ADJ: ' || '30: Assigning the returned values to the output variables');
397 END IF;
398
399 X_status := l_StatusTab(1);
400 X_CrossChargeType := l_CrossChargeTypeTab(1);
401 X_CrossChargeCode := l_CrossChargeCodeTab(1);
402 X_PrvdrOrganizationId := l_PrvdrOrganizationIdTab(1);
403 X_RecvrOrganizationId := l_RecvrOrganizationIdTab(1);
404 X_RecvrOrgId := l_RecvrOrgIdTab(1);
405
406 IF P_DEBUG_MODE THEN
407 pa_cc_utils.log_message('40: Exiting the procedure PA_CC_IDENT.PA_CC_IDENTIFY_TXN_ADJ');
408 END IF;
409
410 pa_cc_utils.reset_curr_function;
411 EXCEPTION
412
413 WHEN OTHERS THEN
414 x_error_stage := l_error_stage;
415 x_error_code := l_error_code;
416 RAISE ;
417
418 END PA_CC_IDENTIFY_TXN_ADJ;
419
420 PROCEDURE PA_CC_IDENTIFY_TXN(
421 P_ExpOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
422 P_ExpOrgidTab IN PA_PLSQL_DATATYPES.IdTabTyp,
423 P_ProjectIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
424 P_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
425 P_ExpItemDateTab IN PA_PLSQL_DATATYPES.DateTabTyp,
426 P_ExpItemIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
427 P_PersonIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
428 P_ExpTypeTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
429 P_SysLinkTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
430 P_PrjOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
431 P_PrjOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
432 P_TransSourceTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
436 /* Added nocopy for 2672653 */
433 P_NLROrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
434 P_PrvdrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
435 P_RecvrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
437 X_StatusTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
438 X_CrossChargeTypeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char3TabTyp,
439 X_CrossChargeCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
440 X_PrvdrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
441 X_RecvrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
442 X_RecvrOrgIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
443 X_Error_Stage OUT NOCOPY VARCHAR2,
444 X_Error_Code OUT NOCOPY NUMBER,
445 /* Added calling module for 3234973 */
446 X_Calling_Module IN VARCHAR2)
447
448 IS
449
450 l_CCProcessIOCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
451 l_CCProcessIUCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
452 l_CCPrjFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
453
454
455 l_CCProcessIOCode VARCHAR2(1);
456 l_CCProcessIUCode VARCHAR2(1);
457 l_CCPrjFlag VARCHAR2(1);
458
459 BEGIN
460
461 --
462 -- This procedure is called for determining the following attributes of
463 -- a transactions
464 -- 1. Provider Organization
465 -- 2. Receiver Organization
466 -- 3. Provider Operating Unit
467 -- 4. Receiver Operating Unit
468
469 pa_cc_utils.set_curr_function('PA_CC_IDENTIFY_TXN');
470
471 IF P_DEBUG_MODE THEN
472 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN: ' || '20.10: Calling the procedure to determine the orgs');
473 END IF;
474
475 PA_CC_IDENT.PA_CC_GET_PRVDR_RECVR_ORGS (
476 P_ExpOrganizationIdTab => P_ExpOrganizationIdTab,
477 P_ExpOrgidTab => P_ExpOrgidTab,
478 P_TaskIdTab => P_TaskIdTab,
479 P_ExpItemIdTab => P_ExpItemIdTab,
480 P_SysLinkTab => P_SysLinkTab,
481 P_ProjectIdTab => P_ProjectIdTab,
482 P_NLROrganizationIdTab => P_NLROrganizationIdTab,
483 P_ExpItemDateTab => P_ExpItemDateTab,
484 P_ExpTypeTab => P_ExpTypeTab,
485 P_PrjOrganizationIdTab => P_PrjOrganizationIdTab,
486 P_PrjOrgIdTab => P_PrjOrgIdTab,
487 X_StatusTab => X_StatusTab,
488 X_PrvdrOrganizationIdTab => X_PrvdrOrganizationIdTab,
489 X_RecvrOrganizationIdTab => X_RecvrOrganizationIdTab,
490 X_RecvrOrgIdTab => X_RecvrOrgIdTab,
491 X_CCProcessIOCodeTab => l_CCProcessIOCodeTab,
492 X_CCProcessIUCodeTab => l_CCProcessIUCodeTab,
493 X_CCPrjFlagTab => l_CCPrjFlagTab,
494 X_Error_Stage => X_Error_Stage,
495 X_Error_Code => X_Error_Code,
496 /* Added calling module for 3234973 */
497 X_Calling_Module => X_Calling_Module);
498
499 --
500 -- This procedure is called for determining the cross charge code.
501 --
502
503 IF P_DEBUG_MODE THEN
504 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN: ' || '20.20: Calling the procedure to determine the cross charge code and type');
505 END IF;
506
507 PA_CC_IDENT.PA_CC_GET_CROSS_CHARGE_TYPE (
508 P_PrvdrOrganizationIdTab => X_PrvdrOrganizationIdTab,
509 P_RecvrOrganizationIdTab => X_RecvrOrganizationIdTab,
510 P_ProjectIdTab => P_ProjectIdTab,
511 P_TaskIdTab => P_TaskIdTab,
512 P_SysLinkTab => P_SysLinkTab,
513 P_ExpItemIdTab => P_ExpItemIdTab,
514 P_PersonIdTab => P_PersonIdTab,
515 P_ExpItemDateTab => P_ExpItemDateTab,
516 P_PrvdrOrgIdTab => P_ExpOrgidTab,
517 P_RecvrOrgIdTab => X_RecvrOrgIdTab,
518 P_PrvdrLEIdTab => P_PrvdrLEIdTab,
519 P_RecvrLEIdTab => P_RecvrLEIdTab,
520 P_TransSourceTab => P_TransSourceTab,
521 P_CCProcessIOCodeTab => l_CCProcessIOCodeTab,
522 P_CCProcessIUCodeTab => l_CCProcessIUCodeTab,
523 P_CCPrjFlagTab => l_CCPrjFlagTab,
524 /* Passing calling module instead of hard coded 'TRANSACTION' for 3234973 */
525 P_calling_mode => X_Calling_Module,
526 X_StatusTab => X_StatusTab,
527 X_CrossChargeTypeTab => X_CrossChargeTypeTab,
528 X_CrossChargeCodeTab => X_CrossChargeCodeTab,
529 X_Error_Stage => X_Error_Stage,
530 X_Error_Code => X_Error_Code);
531
532 pa_cc_utils.reset_curr_function;
533
534 EXCEPTION
535
536 WHEN OTHERS THEN
537 RAISE ;
538
539 END PA_CC_IDENTIFY_TXN;
540
541 PROCEDURE PA_CC_GET_PRVDR_RECVR_ORGS (
542 P_ExpOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
543 P_ExpOrgidTab IN PA_PLSQL_DATATYPES.IdTabTyp,
544 P_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
545 P_ExpItemIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
549 P_ExpItemDateTab IN PA_PLSQL_DATATYPES.DateTabTyp,
546 P_SysLinkTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
547 P_ProjectIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
548 P_NLROrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
550 P_ExpTypeTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
551 P_PrjOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
552 P_PrjOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
553 /* Added nocopy for 2672653 */
554 X_StatusTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
555 X_PrvdrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
556 X_RecvrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
557 X_RecvrOrgIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
558 X_CCProcessIOCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
559 X_CCProcessIUCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
560 X_CCPrjFlagTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
561 X_Error_Stage OUT NOCOPY VARCHAR2,
562 X_Error_Code OUT NOCOPY NUMBER,
563 /* Added calling module for 3234973 */
564 X_Calling_Module IN VARCHAR2) IS
565
566 ----------------------------------------------------------------------------------------
567 -- These variables store the values during the previous iteration. These values are
568 -- compared with the values in the current iteration. Only if the values are different
569 -- the required SELECT is performed. Otherwise the previous results are used as the
570 -- current results
571
572 l_PrevTaskId NUMBER;
573 l_PrevRecvrOrganizationId NUMBER;
574 l_PrevRecvrOrgId NUMBER;
575 l_PrevPrvdrOrgId NUMBER;
576 l_PrevProjectId NUMBER;
577 l_PrevCCPrjFlag VARCHAR2(1);
578 ----------------------------------------------------------------------------------------
579 l_MaxRecs NUMBER;
580 l_MinRecs NUMBER;
581 l_Recvr_Organization_Id NUMBER;
582 l_Prvdr_Organization_Id NUMBER;
583 l_project_Org_Id NUMBER;
584 l_current_org_id NUMBER;
585 l_cc_prj_process_flag VARCHAR2(1);
586 l_cc_process_io_code VARCHAR2(1);
587 l_cc_process_iu_code VARCHAR2(1);
588 l_current_process_io_code VARCHAR2(1);
589 l_current_process_iu_code VARCHAR2(1);
590 l_PrevCCProcessIOCode VARCHAR2(1);
591 l_PrevCCProcessIUCode VARCHAR2(1);
592
593 BEGIN
594
595 l_MinRecs := P_SysLinkTab.FIRST;
596 l_MaxRecs := P_SysLinkTab.LAST;
597
598 pa_cc_utils.set_curr_function('PA_CC_GET_PRVDR_RECVR_ORGS');
599
600 --
601 -- Determine Current Operating Unit
602 --
603 -- Expenditure Operating Unit is the OU in which the expenditure item is created. If
604 -- it is not passed to this procedure then the current operating unit is used as the
605 -- Expenditure Operating Unit. But usually the Expenditure Operating Unit is passed
606 -- to the procedure except Transaction Import.
607 --
608
609 IF P_DEBUG_MODE THEN
610 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.10: Determine the Expenditure OU and its cross charge options');
611 print_message('G_PrevCCProcessIOCode=' ||G_PrevCCProcessIOCode);
612 print_message('l_current_org_id=' ||l_current_org_id);
613 print_message('l_current_process_io_code=' ||l_current_process_io_code);
614 print_message('l_current_process_iu_code=' ||l_current_process_iu_code);
615 END IF;
616
617 If G_PrevCCProcessIOCode is NULL Then
618
619 IF P_DEBUG_MODE THEN
620 print_message('Selecting from pa_implementaions');
621 END IF;
622
623 SELECT imp.org_id,
624 nvl(imp.cc_process_io_code,'N'),
625 nvl(imp.cc_process_iu_code,'N')
626 INTO l_current_org_id,
627 l_current_process_io_code,
628 l_current_process_iu_code
629 FROM pa_implementations imp ;
630
631 G_PrevPrvdrOrgId := l_current_org_id ;
632 G_PrevCCProcessIOCode := l_current_process_io_code ;
633 G_PrevCCProcessIUCode := l_current_process_iu_code ;
634
635 Else
636 l_current_org_id := G_PrevPrvdrOrgId;/* 3933401 */
637 l_current_process_io_code := G_PrevCCProcessIOCode;/* 3933401 */
638 l_current_process_iu_code := G_PrevCCProcessIUCode;/* 3933401 */
639
640 End If;
641
642 l_PrevPrvdrOrgId := G_PrevPrvdrOrgId ;
643 l_PrevCCProcessIOCode := G_PrevCCProcessIOCode ;
644 l_PrevCCProcessIUCode := G_PrevCCProcessIUCode ;
645
646 IF P_DEBUG_MODE THEN
647 print_message('After l_current_org_id=' ||l_current_org_id);
648 print_message('l_current_process_io_code=' ||l_current_process_io_code);
649 print_message('l_current_process_iu_code=' ||l_current_process_iu_code);
650 END IF;
651
652 --
653 -- End get current operating unit
654 --
655
656 --
657 --
661 -- If the expenditure items are already erroneous which will be indicated
658 FOR j IN l_MinRecs..l_MaxRecs
659 LOOP
660 IF X_StatusTab(j) IS NOT NULL THEN
662 -- by this PL/SQL able then do not process CC identification for that
663 -- expenditure item
664
665 IF P_DEBUG_MODE THEN
666 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.20: This expenditure item is already erroneous and needs no CC identification');
667 END IF;
668 NULL ;
669
670 ELSE
671 --
672 -- Initialize local variables
673 --
674 l_project_org_id := NULL ;
675
676 IF P_DEBUG_MODE THEN
677 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.30: Determine Provider Organization');
678 END IF;
679
680 -- Determine Provider Organization
681 --
682 --
683 -- The provider organization is Non-Labor Organization if the system linkage
684 -- is a Usage and Expenditure organization for the others
685 --
686 X_Error_Stage := 'Checking if system linkage function is Usage';
687 IF P_SysLinkTab(j) = 'USG' THEN
688 -- If the system_linkage_function is 'USG' the provider organization
689 -- will be the non-labor resource organization of the resource. So if
690
691 X_Error_Stage := 'Checking if NLR organization id is NULL for usages';
692 IF P_DEBUG_MODE THEN
693 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.40: Checking if NLR organization id is NULL for usages');
694 END IF;
695 IF P_NLROrganizationIdTab(j) IS NULL THEN
696 -- So if non-labor resource organization is not provided then an error
697 -- code is returned.
698
699 IF P_DEBUG_MODE THEN
700 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.50: Setting the error code for null NLR organization Id');
701 END IF;
702 X_Error_Stage := 'Setting the error code for null NLR organization Id';
703 X_StatusTab(j) := 'PA_CC_NO_NL_ORG_FOR_USG';
704 ELSE
705 -- Return non-labor resource organization as the provider organization.
706
707 IF P_DEBUG_MODE THEN
708 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.60: Returning the NLR organization Id');
709 END IF;
710 X_Error_Stage := 'Returning the NLR organization Id';
711 l_Prvdr_Organization_Id := P_NLROrganizationIdTab(j);
712 END IF;
713
714 ELSE
715 -- Else the provider organization is the expenditure organization. Return
716 -- expenditure organization as provider organization.
717
718 IF P_DEBUG_MODE THEN
719 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.70: Returning the expenditure organization id');
720 END IF;
721 X_Error_Stage := 'Returning the expenditure organization id';
722 l_Prvdr_Organization_Id := P_ExpOrganizationIdTab(j);
723 END IF;
724
725 -- Determine Receiver Organization
726 --
727 --
728 -- Receiver Organization is the organization that owns the task
729 -- ( Carrying_Out_Organization_Id). In this select we also
730
731 IF P_DEBUG_MODE THEN
732 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.80: Checking if current task id is the same as the previous task id');
733 END IF;
734 X_Error_Stage := 'Checking if project OU is NULL';
735 IF l_PrevTaskId = P_TaskIdTab(j) THEN
736 -- If the current task id is the same as the previous task id then
737 -- the receiver organization and receiver operating unit will be
738 -- the same as previous one
739
740 IF P_DEBUG_MODE THEN
741 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.90: current task id is the same as the previous task id');
742 END IF;
743 l_Recvr_Organization_Id := l_PrevRecvrOrganizationId ;
744 l_project_org_id := l_PrevRecvrOrgId ;
745 l_cc_prj_process_flag := l_PrevCCPrjFlag ;
746
747
748 ELSE
749 -- If the current task id is not the same as the previous task id
750 -- then select the new receiver organization and receiver operating
751 -- unit
752 -- In this select we also get the project operating unit if it is not
753 -- passed to this procedure. This is done so that we can avoid an
754 -- extra SELECT later on while deriving the value of Provider OU.
755
756 X_Error_Stage := 'Checking if current project id is the same as the previous project id' ;
757
758 IF l_PrevProjectId = P_ProjectIdTab(j) THEN
759 --
760 -- If the current project id is the same as the previous project id
761 -- then we do not need to join to the projects table to get the project
762 -- org id which is the receiver OU.
763 --
767
764 IF P_DEBUG_MODE THEN
765 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.100: If current project id is the same as the previous project id do not join to projects table');
766 END IF;
768 If (G_PrevTaskId = P_TaskIdTab(j) and G_PrevSysLink = P_SysLinkTab(j)) Then
769
770 l_recvr_organization_id := G_PrevRecvrOrgnId;
771 l_cc_prj_process_flag := G_PrevCCPrjProcessFlag;
772
773 Else
774 SELECT t.carrying_out_organization_id,
775 decode(sl.LABOR_NON_LABOR_FLAG,
776 'Y', t.cc_process_labor_flag,
777 t.cc_process_nl_flag)
778 INTO l_recvr_organization_id,
779 l_cc_prj_process_flag
780 FROM pa_system_linkages sl,
781 pa_tasks t
782 WHERE t.task_id = P_TaskIdTab(j)
783 AND sl.function = P_SysLinkTab(j) ;
784
785 G_PrevTaskId := P_TaskIdTab(j);
786 G_PrevSysLink := P_SysLinkTab(j);
787 G_PrevRecvrOrgnId := l_recvr_organization_id;
788 G_PrevCCPrjProcessFlag := l_cc_prj_process_flag;
789
790 End If;
791
792 l_project_org_id := l_PrevRecvrOrgId ;
793
794 ELSE
795 --
796 -- If the current project id is not the same as the previous project
797 -- id the we need to join to the projects table to get the project
798 -- org id also.
799 --
800
801 IF P_DEBUG_MODE THEN
802 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.110: If current project id is not the same as the previous project id join to projects table to get org_id');
803 END IF;
804
805 BEGIN -- 2650361
806
807 SELECT t.carrying_out_organization_id,
808 p.org_id,
809 decode(sl.LABOR_NON_LABOR_FLAG,
810 'Y', t.cc_process_labor_flag,
811 t.cc_process_nl_flag)
812 INTO l_recvr_organization_id,
813 l_project_org_id,
814 l_cc_prj_process_flag
815 FROM pa_system_linkages sl,
816 pa_projects_all p,
817 pa_tasks t
818 WHERE p.project_id = t.project_id
819 AND t.task_id = P_TaskIdTab(j)
820 AND sl.function = P_SysLinkTab(j) ;
821 /* Adding exception handling for 2650361 */
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824 IF P_DEBUG_MODE THEN
825 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.115: Setting the rejection code to INV_DATA when no data found');
826 END IF;
827 UPDATE pa_expenditure_items_all
828 SET cost_dist_rejection_code = 'INV_DATA'
829 WHERE task_id = P_TaskIdTab(j)
830 AND cost_distributed_flag = 'S'
831 AND cost_dist_rejection_code is null;
832 END;
833 /* 2650361 */
834
835 G_PrevTaskId := P_TaskIdTab(j);
836 G_PrevSysLink := P_SysLinkTab(j);
837 G_PrevRecvrOrgnId := l_recvr_organization_id;
838 G_PrevCCPrjProcessFlag := l_cc_prj_process_flag;
839 G_PrevPrjOrgId := l_project_org_id;
840
841 END IF;
842
843 l_PrevRecvrOrgId := l_project_org_id ;
844 l_PrevRecvrOrganizationId := l_recvr_organization_id ;
845 l_PrevCCPrjFlag := l_cc_prj_process_flag ;
846
847 END IF;
848
849 -- Determine Provider Operating Unit
850 --
851 --
852 -- The Operating Unit in which the Expenditure is charged. In this case it is the
853 -- same as p_expenditure_org_id.
854
855 IF P_DEBUG_MODE THEN
856 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.120: Determine the cross charge process codes in implementations');
857 END IF;
858 X_Error_Stage := 'Determine the cross charge process codes in implementations';
859
860 --
861 -- Also determine the cross charge process codes in implementations
862 --
863
864 IF nvl(l_PrevPrvdrOrgId,-99) = nvl(P_ExpOrgidTab(j),-99) THEN
865 /* bug#3167296 added nvlto handle single org case, please note that single org case
866 was handled before this fix also using l_current_org_id is null condition below
867 first time its fine but second time it fails because l_current_process_io_code
868 being not available, after fix it works first as well as subsequent time */
869
870 --
871 -- If the current operating unit is the same as the previous operating
872 -- unit then we can use the same implementation options as that of the
873 -- previous one
874 --
878
875 IF P_DEBUG_MODE THEN
876 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.130: If expenditure OUs are not changed use previous values');
877 END IF;
879 l_cc_process_io_code := l_PrevCCProcessIOCode ;
880 l_cc_process_iu_code := l_PrevCCProcessIUCode ;
881 IF P_DEBUG_MODE THEN
882 print_message('l_cc_process_io_code=' ||l_cc_process_io_code);
883 print_message('l_cc_process_iu_code=' ||l_cc_process_iu_code);
884 END IF;
885 ELSE
886 IF l_current_org_id IS NULL THEN
887 --
888 -- If the current operating unit is null then it is a single org
889 -- implementation. For the single org implementation we use the
890 -- same implementation options that were used at the start of the
891 -- procedure.
892 --
893
894 IF P_DEBUG_MODE THEN
895 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.140: Expenditure OU is the same as current OU ');
896 END IF;
897 l_cc_process_io_code := l_current_process_io_code ;
898 l_cc_process_iu_code := l_current_process_iu_code ;
899
900 ELSE
901 IF P_DEBUG_MODE THEN
902 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.150: Determine cross charge codes of current Expenditure OU');
903 END IF;
904
905 SELECT nvl(imp.cc_process_io_code,'N'),
906 nvl(imp.cc_process_iu_code,'N')
907 INTO l_cc_process_io_code,
908 l_cc_process_iu_code
909 FROM pa_implementations_all imp
910 WHERE imp.org_id = nvl(P_ExpOrgidTab(j), l_current_org_id) ;
911
912 END IF;
913 IF P_DEBUG_MODE THEN
914 print_message('l_cc_process_io_code=' ||l_cc_process_io_code);
915 print_message('l_cc_process_iu_code=' ||l_cc_process_iu_code);
916 END IF;
917
918 END IF ;
919
920 l_PrevCCProcessIUCode := l_cc_process_iu_code ;
921 l_PrevCCProcessIOCode := l_cc_process_io_code ;
922 X_CCProcessIUCodeTab(j) := l_cc_process_iu_code ;
923 X_CCProcessIOCodeTab(j) := l_cc_process_io_code ;
924
925 --
926 -- Determine Receiver Operating Unit
927 --
928 --
929
930 IF P_DEBUG_MODE THEN
931 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.160: Determine Receiver Operating Unit');
932 END IF;
933
934 X_Error_Stage := 'Assigning the project OU as provider OU';
935 X_PrvdrOrganizationIdTab(j) := l_Prvdr_Organization_Id ;
936 X_RecvrOrganizationIdTab(j) := l_recvr_organization_id ;
937
938 l_PrevPrvdrOrgId := P_ExpOrgidTab(j);
939 X_RecvrOrgIdTab(j) := NVL( P_PrjOrgIdTab(j), l_project_org_id);
940 X_CCPrjFlagTab(j) := l_cc_prj_process_flag;
941
942 IF P_DEBUG_MODE THEN
943 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.170: Calling the client extension PA_CC_IDENT_CLIENT_EXTN.OVERRIDE_PRVDR_RECVR');
944 END IF;
945
946 PA_CC_IDENT_CLIENT_EXTN.OVERRIDE_PRVDR_RECVR (
947 P_PrvdrOrganizationId => X_PrvdrOrganizationIdTab(j),
948 P_PrvdrOrgId => P_ExpOrgidTab(j),
949 P_RecvrOrganizationId => X_RecvrOrganizationIdTab(j),
950 P_RecvrOrgId => X_RecvrOrgIdTab(j),
951 P_TransId => P_ExpItemIdTab(j),
952 P_SysLink => P_SysLinkTab(j),
953 X_Status => X_StatusTab(j),
954 X_PrvdrOrganizationId => X_PrvdrOrganizationIdTab(j),
955 X_RecvrOrganizationId => X_RecvrOrganizationIdTab(j),
956 X_Error_Stage => X_Error_Stage,
957 X_Error_Code => X_Error_Code,
958 /* Added calling module for 3234973
959 P_calling_mode => 'TRANSACTION', */
960 P_calling_mode => X_Calling_Module);
961
962 END IF;
963
964 IF P_DEBUG_MODE THEN
965 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.180: After determining the orgs for this transaction');
966 print_message('Exiting..l_cc_process_io_code=' ||l_cc_process_io_code);
967 print_message('l_cc_process_iu_code=' ||l_cc_process_iu_code);
968 END IF;
969 END LOOP;
970
971 IF P_DEBUG_MODE THEN
972 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.180: After determining the orgs for the bulk of transactions');
973 END IF;
974
975 pa_cc_utils.reset_curr_function;
976
977 EXCEPTION
978
979 WHEN OTHERS THEN
980 RAISE ;
981
982 END PA_CC_GET_PRVDR_RECVR_ORGS ;
983
984 PROCEDURE PA_CC_GET_CROSS_CHARGE_TYPE (
985 P_PrvdrOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
986 P_RecvrOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
987 P_ProjectIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
991 P_PersonIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
988 P_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
989 P_SysLinkTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
990 P_ExpItemIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
992 P_ExpItemDateTab IN PA_PLSQL_DATATYPES.DateTabTyp,
993 P_PrvdrOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
994 P_RecvrOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
995 P_PrvdrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
996 P_RecvrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
997 P_TransSourceTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
998 P_CCProcessIOCodeTab IN PA_PLSQL_DATATYPES.Char1TabTyp,
999 P_CCProcessIUCodeTab IN PA_PLSQL_DATATYPES.Char1TabTyp,
1000 P_CCPrjFlagTab IN PA_PLSQL_DATATYPES.Char1TabTyp,
1001 P_calling_mode IN VARCHAR2 ,
1002 /* Added nocopy for 2672653 */
1003 X_StatusTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1004 X_CrossChargeTypeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char3TabTyp,
1005 X_CrossChargeCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
1006 X_Error_Stage OUT NOCOPY VARCHAR2,
1007 X_Error_Code OUT NOCOPY NUMBER) IS
1008
1009 ----------------------------------------------------------------------------------------
1010 -- These variables store the values during the previous iteration. These values are
1011 -- compared with the values in the current iteration. Only if the values are different
1012 -- the required SELECT is performed. Otherwise the previous results are used as the
1013 -- current results
1014
1015 l_PrevCCTrScFlag VARCHAR2(1);
1016 l_PrevRecvrOrgId NUMBER;
1017 l_PrevPrvdrOrgId NUMBER;
1018 l_PrevPrvdrLEId NUMBER;
1019 l_PrevRecvrLEId NUMBER;
1020 l_PrevProjectId NUMBER;
1021 l_PrevCCPrjFlag VARCHAR2(1);
1022 l_PrevSysLink VARCHAR2(30);
1023 l_PrevTransSource VARCHAR2(30);
1024 l_PrevCrossChargeCode VARCHAR2(1);
1025 l_PrevCrossChargeType VARCHAR2(3);
1026
1027 ----------------------------------------------------------------------------------------
1028 l_CallExtnTab PA_PLSQL_DATATYPES.Char1TabTyp;
1029 l_OvrridCrossChargeCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
1030 l_MaxRecs NUMBER;
1031 l_MinRecs NUMBER;
1032 l_Recvr_Organization_Id NUMBER;
1033 l_Recvr_Org_Id NUMBER;
1034 l_Prvdr_Org_Id NUMBER;
1035 l_Recvr_LE_Id NUMBER;
1036 l_Prvdr_LE_Id NUMBER;
1037 l_Recvr_LE_Id_1 NUMBER;/*4482589*/
1038 l_Prvdr_LE_Id_1 NUMBER;/*4482589*/
1039 l_project_Org_Id NUMBER;
1040 l_prvdr_project_id NUMBER;
1041 l_vendor_site_id NUMBER;
1042 l_cross_charge_code VARCHAR2(1);
1043 l_cross_charge_type VARCHAR2(3);
1044 l_cc_tr_src_process_flag VARCHAR2(1);
1045 l_cc_prj_process_flag VARCHAR2(1);
1046 l_calling_module VARCHAR2(100);
1047
1048 CURSOR GetCCOrgRel(c_prvdr_org_id NUMBER,
1049 c_recvr_org_id NUMBER) IS
1050 SELECT DECODE(co.prvdr_allow_cc_flag, 'N','N', co.cross_charge_code),
1051 co.prvdr_project_id, co.vendor_site_id
1052 FROM pa_cc_org_relationships co
1053 WHERE co.prvdr_org_id = c_prvdr_org_id
1054 AND co.recvr_org_id = c_recvr_org_id;
1055
1056 BEGIN
1057
1058 If P_calling_mode is NULL then
1059 l_calling_module := 'TRANSACTION';
1060 Else
1061 l_calling_module := P_calling_mode;
1062 End if;
1063
1064 pa_cc_utils.set_curr_function('PA_CC_GET_CROSS_CHARGE_TYPE');
1065
1066 l_MinRecs := P_SysLinkTab.FIRST;
1067 l_MaxRecs := P_SysLinkTab.LAST;
1068
1069 IF P_DEBUG_MODE THEN
1070 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.10: Determine cross charge code and type');
1071 END IF;
1072
1073 FOR j IN l_MinRecs..l_MaxRecs
1074 LOOP
1075 IF X_StatusTab(j) IS NOT NULL THEN
1076 -- If the expenditure items are already erroneous which will be indicated
1077 -- by this PL/SQL able then do not process CC identification for that
1078 -- expenditure item
1079
1080 IF P_DEBUG_MODE THEN
1081 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.20: Since expenditure item is already erroneous no CC identification is necessary');
1082 END IF;
1083 NULL ;
1084
1085 ELSE
1086
1087 --
1088 -- After we have the provider OU, receiver OU, provider and receiver
1089 -- organizations check if the transaction requires cc processing.
1090 --
1091
1092 IF P_DEBUG_MODE THEN
1093 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.30: Check for never cross charged');
1094 END IF;
1095
1096 IF ( ( P_PrvdrOrganizationIdTab(j) = P_RecvrOrganizationIdTab(j) AND
1097 nvl(P_RecvrOrgIdTab(j), -99) = nvl(P_PrvdrOrgIdTab(j), -99)) OR
1098 P_SysLinkTab(j) = 'BTC' ) THEN
1099
1103 --
1100 IF P_DEBUG_MODE THEN
1101 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.40: Same provider and receiver org and same provider and receiver OU or a BTC');
1102 END IF;
1104 -- If provider organization is the same as the receiver organization
1105 -- and provider OU is the same as the receiver OU then the transaction
1106 -- is never cross charged. Another situation where the transaction is
1107 -- never cross charged is if the transaction is a burden transaction.
1108 --
1109
1110 l_cross_charge_code := 'X' ;
1111 l_cross_charge_type := 'NO' ;
1112 l_PrevRecvrLEId := NULL; /*4482589*/
1113 l_PrevPrvdrLEId := NULL; /*4482589*/
1114 l_CallExtnTab(j) := 'N' ;
1115
1116 ELSIF P_RecvrOrgIdTab(j) IS NULL THEN
1117
1118 IF P_DEBUG_MODE THEN
1119 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.50: Single org implementation, cross charge code is IO code at the implementation .');
1120 END IF;
1121
1122 l_cross_charge_code := P_CCProcessIOCodeTab(j) ;
1123 l_cross_charge_type := 'NO' ;
1124 l_PrevRecvrLEId := NULL; /*4482589*/
1125 l_PrevPrvdrLEId := NULL; /*4482589*/
1126 l_CallExtnTab(j) := 'Y' ;
1127
1128 ELSE
1129 --
1130 -- Determine the cross charge type
1131 --
1132
1133 IF P_DEBUG_MODE THEN
1134 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.60: Determine the cross charge type');
1135 END IF;
1136
1137 IF P_PrvdrLEIdTab(j) = l_PrevPrvdrLEId AND
1138 P_RecvrLEIdTab(j) = l_PrevRecvrLEId AND
1139 P_RecvrOrgIdTab(j) = l_PrevRecvrOrgId AND
1140 P_PrvdrOrgIdTab(j) = l_PrevPrvdrOrgId THEN
1141
1142 --
1143 -- If the current provider legal entity is the same as the
1144 -- previous provider legal entity and the current receiver legal
1145 -- entity is the same as the previous receiver legal entity then
1146 -- use the same cross charge code as that of the previous one
1147 --
1148 IF P_DEBUG_MODE THEN
1149 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.70: If parameters for determining cross charge type is same as previous ones, use the same cross charge type');
1150 END IF;
1151
1152 l_cross_charge_type := l_PrevCrossChargeType;
1153
1154 ELSE
1155 --
1156 -- Get the value of the provider legal entity
1157 --
1158 IF P_DEBUG_MODE THEN
1159 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.80: Determining provider legal entity');
1160 END IF;
1161
1162 IF P_PrvdrLEIdTab(j) IS NULL THEN
1163 --
1164 -- If the provider legal entity parameter passed into the
1165 -- procedure is null then derrive it based on the provider
1166 -- OU.
1167 --
1168
1169 IF nvl(P_PrvdrOrgIdTab(j),-99) = NVL(l_PrevPrvdrOrgId, -99) THEN /*4482589*/
1170
1171 --
1172 -- If the previous provider OU is the same as the current
1173 -- provider OU then use the legal entity value derrived in
1174 -- the previous iteration.
1175 --
1176 IF P_DEBUG_MODE THEN
1177 print_message('Inside P_PrvdrOrgIdTab = NVL(l_PrevPrvdrOrgId, -99)');
1178 END IF;
1179 If l_PrevPrvdrLEId is null Then /*4482589*/
1180 l_PrevPrvdrLEId := GetLegalEntity(P_PrvdrOrgIdTab(j));
1181 End if;
1182
1183 l_Prvdr_LE_Id := l_PrevPrvdrLEId ;
1184 l_prvdr_le_id_1 := GetLegalEntity(P_PrvdrOrgIdTab(j));
1185
1186 ELSE
1187 --
1188 -- Derrive the provider legal entity based on the current
1189 -- provider OU
1190 --
1191 l_Prvdr_LE_Id := GetLegalEntity(P_PrvdrOrgIdTab(j));
1192
1193 l_PrevPrvdrLEId := l_Prvdr_LE_Id ;
1194 IF P_DEBUG_MODE THEN
1195 print_message('After Getlegalentity - '||l_Prvdr_LE_Id);
1196 END IF;
1197 END IF;
1198 ELSE
1199 l_Prvdr_LE_Id := P_PrvdrLEIdTab(j);
1200
1201 l_PrevPrvdrLEId := l_Prvdr_LE_Id ;
1202 END IF;
1203 IF P_DEBUG_MODE THEN
1204 print_message('l_Prvdr_LE_Id ='||l_Prvdr_LE_Id);
1205 print_message('l_PrevPrvdrLEId ='||l_PrevPrvdrLEId);
1206 END IF;
1207
1208 --
1209 -- Get the value of the receiver legal entity
1210 --
1211 IF P_DEBUG_MODE THEN
1212 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.90: Determining receiver legal entity');
1213 END IF;
1214
1215 IF P_RecvrLEIdTab(j) IS NULL THEN
1216 --
1217 -- If the provider legal entity parameter passed into the
1218 -- procedure is null then derrive it based on the provider
1219 -- OU.
1220 --
1224 -- provider OU then use the legal entity value derrived in
1221 IF nvl(P_RecvrOrgIdTab(j),-99) = NVL(l_PrevRecvrOrgId, -99) THEN /*4482589*/
1222 --
1223 -- If the previous provider OU is the same as the current
1225 -- the previous iteration.
1226 --
1227 If l_PrevRecvrLEId is null Then /*4482589*/
1228 l_PrevRecvrLEId := GetLegalEntity(NVL(P_RecvrOrgIdTab(j),l_Recvr_Org_Id));
1229 End if;
1230
1231 l_Recvr_LE_Id := l_PrevRecvrLEId ;
1232 l_recvr_le_id_1:= GetLegalEntity(NVL(P_RecvrOrgIdTab(j),l_Recvr_Org_Id));
1233
1234 ELSE
1235 --
1236 -- Derrive the provider legal entity based on the current
1237 -- provider OU
1238 --
1239 l_Recvr_LE_Id := GetLegalEntity(NVL(P_RecvrOrgIdTab(j),
1240 l_Recvr_Org_Id));
1241 l_PrevRecvrLEId := l_Recvr_LE_Id ;
1242 END IF;
1243 ELSE
1244 l_Recvr_LE_Id := P_RecvrLEIdTab(j);
1245
1246 l_PrevRecvrLEId := l_Recvr_LE_Id ;
1247 END IF;
1248 IF P_DEBUG_MODE THEN
1249 print_message('l_Recvr_LE_Id ='||l_Recvr_LE_Id);
1250 print_message('l_PrevRecvrLEId ='||l_PrevRecvrLEId);
1251 END IF;
1252
1253 --
1254 -- Get the cross charge type
1255 --
1256
1257 IF nvl(l_Recvr_LE_Id,-99) <> nvl(l_Prvdr_LE_Id,-99) THEN
1258 l_cross_charge_type := 'IC' ;
1259 ELSIF nvl(P_RecvrOrgIdTab(j),-99) <> nvl(P_PrvdrOrgIdTab(j),-99) THEN
1260 l_cross_charge_type := 'IU' ;
1261 ELSE
1262 l_cross_charge_type := 'IO' ;
1263 END IF;
1264 IF P_DEBUG_MODE THEN
1265 print_message('l_cross_charge_type ='||l_cross_charge_type);
1266 END IF;
1267
1268 END IF; -- Determine Cross Charge Type
1269
1270 --
1271 -- Check if the project requires cross charge processing to be done
1272 -- for that class(labor or non-labor) of transactions charged to that
1273 -- project as for some cases cross charge processing may be done
1274 -- externally.
1275 --
1276
1277
1278 IF P_DEBUG_MODE THEN
1279 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.100: Check if project is cross chargeable');
1280 END IF;
1281
1282 IF P_CCPrjFlagTab(j) = 'N' THEN
1283 --
1284 -- Section : Check if the project is cross chargeable.
1285 --
1286 -- If the labor non-labor process flag on the project is 'N' then
1287 -- there is no need of processing cross charge for this project.
1288 --
1289
1290 l_cross_charge_code := 'N';
1291 l_CallExtnTab(j) := 'N' ;
1292
1293 ELSE
1294
1295 IF P_DEBUG_MODE THEN
1296 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.110: Check if transaction source is cross chargeable');
1297 END IF;
1298
1299 IF P_TransSourceTab(j) IS NOT NULL THEN
1300 --
1301 -- Section : Not Null transaction source.
1302 --
1303 -- Check if this transaction is from an external transactions source
1304 -- i.e. if the transaction source element is NULL.
1305 --
1306
1307 IF P_DEBUG_MODE THEN
1308 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.120: Check if transaction source is same as previous one');
1309 END IF;
1310
1311 IF P_TransSourceTab(j) = l_PrevTransSource THEN
1312 --
1313 -- Section : Get the cross charge process flag.
1314 --
1315 -- If the current transaction source is the same as the previous one
1316 -- then use the previous value of the cc_process_flag.
1317 --
1318
1319 l_cc_tr_src_process_flag := l_PrevCCTrScFlag;
1320
1321 ELSE
1322 --
1323 -- If the transaction source is different then get the value of
1324 -- cross charge process flag for the current transaction source.
1325 --
1326 IF P_DEBUG_MODE THEN
1327 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.130: Get the cc process flag for current transaction source');
1328 END IF;
1329
1330 SELECT t.cc_process_flag
1331 INTO l_cc_tr_src_process_flag
1332 FROM pa_transaction_sources t
1333 WHERE t.transaction_source = P_TransSourceTab(j) ;
1334
1335 l_PrevCCTrScFlag := l_cc_tr_src_process_flag ;
1336 l_PrevTransSource:= P_TransSourceTab(j); /*Bug#3364107*/
1337
1338 END IF; -- End Section : Get the cross charge process flag.
1339
1340 END IF;
1341
1342 IF (P_TransSourceTab(j) IS NOT NULL ) AND (l_cc_tr_src_process_flag <> 'Y' ) THEN /*Bug#3364107*/
1343 --
1347 -- then set the value of cross charge code and skip all the other
1344 -- Section : Check if transaction source is cross chargeable.
1345 --
1346 -- If the transaction source does not require cross charge processing
1348 -- checks.
1349 --
1350
1351 IF P_DEBUG_MODE THEN
1352 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.140: Setting l_cross_charge_code to N if transaction source is not CC');
1353 END IF;
1354 l_cross_charge_code := 'N' ;
1355
1356 ELSE
1357
1358 IF P_DEBUG_MODE THEN
1359 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.150: Check if provider OU is same as receiver OU');
1360 END IF;
1361
1362 IF P_PrvdrOrgIdTab(j) = P_RecvrOrgIdTab(j) THEN
1363 --
1364 -- Section : Same provider OU and receiver OU.
1365 --
1366 -- If the provider OU is the same as the receiver OU then get the
1367 -- processing method from the implementation option of the provider
1368 -- OU.
1369 --
1370
1371 IF P_DEBUG_MODE THEN
1372 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.160: If provider OU is same as receiver OU assign cc code of implementation option');
1373 END IF;
1374
1375 l_cross_charge_code := P_CCProcessIOCodeTab(j) ;
1376
1377 ELSE
1378 -- If the provider OU is different from the receiver OU then check
1379 -- in the cross charge org relationships entity if there any record
1380 -- for that combination of provider ou and receiver OU. If there is
1381 -- any record then the cross charge code on that record is used.
1382
1383 IF P_DEBUG_MODE THEN
1384 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.170: For different provider and receiver ous check if any controls exist');
1385 END IF;
1386 OPEN GetCCOrgRel(P_PrvdrOrgIdTab(j),
1387 P_RecvrOrgIdTab(j));
1388 FETCH GetCCOrgRel
1389 INTO l_cross_charge_code,
1390 l_prvdr_project_id,
1391 l_vendor_site_id ;
1392
1393 IF GetCCOrgRel%FOUND THEN
1394 --
1395 -- Section : Check for entry in CC Org relationships entity.
1396 --
1397 -- If there is a record in cross charge org relationships entity
1398 -- then the value is assigned to l_cross_charge_code in the fetch.
1399 --
1400
1401 NULL ;
1402
1403 ELSE
1404 --
1405 -- If there is not record in cross charge org relationships entity
1406 -- then the value then check in the implementations option of the
1407 -- provider operating unit.
1408 --
1409 IF P_DEBUG_MODE THEN
1410 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.180: If there are no controls, then check cross charge type');
1411 END IF;
1412
1413 IF l_cross_charge_type = 'IU' THEN
1414 --
1415 -- Section : No entry in CC Org relationships entity.
1416 --
1417 -- If the cross charge type is 'IU' which means the legal entities
1418 -- of the provider and receiver OUs are the same then check the
1419 -- implementation option for the process iu code which is determined
1420 -- in the override orgs procedure and passed into this procedure.
1421 --
1422
1423 l_cross_charge_code := P_CCProcessIUCodeTab(j) ;
1424
1425 IF P_DEBUG_MODE THEN
1426 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.190: For IU CC type set the CC code as the IU code in the implementation options');
1427 END IF;
1428
1429 ELSE
1430
1431 --
1432 -- This section indicates that cross charge type is 'IC' and there is
1433 -- no processing method defined in cross charge org relationships entity.
1434 -- So we cannot process cross charge for this item.
1435
1436 IF P_DEBUG_MODE THEN
1437 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.200: This scenario exists when CC type is IU and not controls exist');
1438 END IF;
1439 l_cross_charge_code := 'N' ;
1440
1441 END IF; -- End Section : No entry in CC Org relationships entity.
1442 END IF; -- End Section : Check for entry in CC Org relationships entity.
1443
1444 Close GetCCOrgRel ;
1445 END IF; -- End Section : Same provider OU and receiver OU.
1446
1447 END IF; -- Section : Not Null transaction source.
1451
1448
1449 END IF ; -- Section : Check if the project is cross chargeable.
1450 --
1452 END IF;
1453
1454 X_CrossChargeCodeTab(j) := l_cross_charge_code ;
1458 l_PrevPrvdrOrgId := P_PrvdrOrgIdTab(j) ; /*4154761*/
1455 X_CrossChargeTypeTab(j) := l_cross_charge_type ;
1456 l_PrevCrossChargeType := l_cross_charge_type ;
1457 l_PrevRecvrOrgId := P_RecvrOrgIdTab(j) ;
1459 l_PrevCrossChargeCode := l_cross_charge_code ;
1460 l_PrevProjectId := P_ProjectIdTab(j) ;
1461 /* l_PrevTransSource := P_TransSourceTab(j) ; Commented for bug# 3364107 */
1462 l_cc_tr_src_process_flag := NULL ; /*Bug# 3364107*/
1463
1464 IF P_DEBUG_MODE THEN
1465 print_message('l_cross_charge_code=' ||l_cross_charge_code);
1466 print_message('l_cross_charge_type=' ||l_cross_charge_type);
1467 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.210: Calling client extension');
1468 END IF;
1469 --
1470 -- Call client extension
1471 --
1472 PA_CC_IDENT_CLIENT_EXTN.OVERRIDE_CC_PROCESSING_METHOD (
1473 P_PrvdrOrganizationId => P_PrvdrOrganizationIdTab(j),
1474 P_RecvrOrganizationId => P_RecvrOrganizationIdTab(j),
1475 P_PrvdrOrgId => P_PrvdrOrgIdTab(j),
1476 P_RecvrOrgId => P_RecvrOrgIdTab(j),
1477 P_PrvdrLEId => P_PrvdrLEIdTab(j),
1478 P_RecvrLEId => P_RecvrLEIdTab(j),
1479 P_PersonId => P_PersonIdTab(j),
1480 P_ProjectId => P_ProjectIdTab(j),
1481 P_TaskId => P_TaskIdTab(j),
1482 P_SysLink => P_SysLinkTab(j),
1483 P_TransDate => P_ExpItemDateTab(j),
1484 P_TransSource => P_TransSourceTab(j),
1485 P_TransId => P_ExpItemIdTab(j),
1486 P_CrossChargeCode => X_CrossChargeCodeTab(j),
1487 P_CrossChargeType => X_CrossChargeTypeTab(j),
1488 P_calling_mode => l_calling_module,
1489 X_OvrridCrossChargeCode => l_OvrridCrossChargeCodeTab(j),
1490 X_Status => X_StatusTab(j),
1491 X_Error_Stage => X_Error_Stage,
1492 X_Error_Code => X_Error_Code ) ;
1493 --
1494 -- After Client extension
1495 --
1496
1497 IF P_DEBUG_MODE THEN
1498 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.220: Validating the overriden CC code');
1499 END IF;
1500 IF l_OvrridCrossChargeCodeTab(j) = X_CrossChargeCodeTab(j) THEN
1501
1502 NULL ;
1503 ELSE
1504 IF l_OvrridCrossChargeCodeTab(j) = 'I' THEN
1505
1506 IF P_DEBUG_MODE THEN
1507 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.230: If the overriden CC code is I the check for validity');
1508 END IF;
1509 IF X_CrossChargeTypeTab(j) = 'IO' THEN
1510
1511 IF P_DEBUG_MODE THEN
1512 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.240: If CC type is IO then the overriden code is invalid');
1513 END IF;
1514
1515 X_StatusTab(j) := 'PA_CC_CODE_TYPE_INVALID';
1516 X_CrossChargeCodeTab(j) := NULL;
1517 ELSE
1518 IF P_DEBUG_MODE THEN
1519 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.250: If CC type is IU the check for valid controls ');
1520 END IF;
1521 OPEN GetCCOrgRel(P_PrvdrOrgIdTab(j), P_RecvrOrgIdTab(j));
1522 FETCH GetCCOrgRel
1523 INTO l_cross_charge_code,
1524 l_prvdr_project_id,
1525 l_vendor_site_id ;
1526 IF GetCCOrgRel%NOTFOUND OR
1527 l_prvdr_project_id IS NULL OR
1528 l_vendor_site_id IS NULL THEN
1529
1530 X_StatusTab(j) := 'PA_CC_CODE_TYPE_INVALID';
1531 X_CrossChargeCodeTab(j) := NULL;
1532 ELSE
1533 X_CrossChargeCodeTab(j) := l_OvrridCrossChargeCodeTab(j);
1534
1535 END IF;
1536 close GetCCOrgRel ;
1537 END IF ;
1538 ELSIF l_OvrridCrossChargeCodeTab(j) = 'B' THEN
1539
1540 IF P_DEBUG_MODE THEN
1541 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.260: If the overriden CC code is B the check for validity');
1542 END IF;
1543 IF X_CrossChargeTypeTab(j) = 'IC' THEN
1544
1545 IF P_DEBUG_MODE THEN
1546 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.240: If CC type is IC then the overriden code is invalid');
1547 END IF;
1548 X_StatusTab(j) := 'PA_CC_CODE_TYPE_INVALID';
1549 X_CrossChargeCodeTab(j) := NULL;
1550 ELSE
1551
1552 X_CrossChargeCodeTab(j) := l_OvrridCrossChargeCodeTab(j);
1553
1554 END IF ;
1555 ELSE
1556 X_CrossChargeCodeTab(j) := l_OvrridCrossChargeCodeTab(j);
1557
1558 END IF ;
1559
1560 END IF;
1561 END IF;
1562
1563 IF P_DEBUG_MODE THEN
1564 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.250: End of determination of CC code and Type for this transaction');
1565 END IF;
1566 END LOOP ;
1567
1568 IF P_DEBUG_MODE THEN
1569 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.260: End of CC code and type determination for the bulk of transactions');
1570 print_message('Exiting afetr checking client extn l_cross_charge_code=' ||l_cross_charge_code);
1574 pa_cc_utils.reset_curr_function;
1571 print_message('l_cross_charge_type=' ||l_cross_charge_type);
1572 END IF;
1573
1575
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 RAISE;
1579
1580 END PA_CC_GET_CROSS_CHARGE_TYPE;
1581
1582
1583 -- ==========================================================================
1584 -- = FUNCTION GetLegalEntity
1585 -- ==========================================================================
1586
1587 FUNCTION GetLegalEntity( p_org_id IN NUMBER )
1588 RETURN NUMBER
1589 IS
1590
1591 l_legal_entity_id VARCHAR2(150);
1592 BEGIN
1593
1594
1595
1596 /* R12 Legal entity changes - get from HR not PA Imp */
1597 SELECT org_information2
1598 INTO l_legal_entity_id
1599 FROM hr_organization_information
1600 WHERE organization_id = p_org_id
1601 AND org_information_context = 'Operating Unit Information';
1602
1603 RETURN (to_number(l_legal_entity_id));
1604
1605
1606 EXCEPTION
1607 WHEN NO_DATA_FOUND THEN
1608 RETURN (NULL);
1609 WHEN OTHERS THEN
1610 RAISE ;
1611
1612 END GetLegalEntity;
1613
1614 END PA_CC_IDENT;
[Home] [Help]