1 PACKAGE BODY PA_CC_IDENT
2 -- $Header: PACCINTB.pls 120.7.12020000.6 2013/03/06 09:23:10 admarath ship $
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 G_unmatched_neg_txn_flag pa_transaction_interface_all.unmatched_negative_txn_flag%TYPE default 'N'; -- bug 14173646
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
105 --
102 ELSE
103 --
104 -- Determine Current Operating Unit
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,
215 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_FI: ' || 'P_ProjectIdTab='||P_ProjectIdTab(j)||'P_PrvdrOrganizationId='||X_PrvdrOrganizationIdTab(j)
212 X_Error_Code => X_Error_Code );
213
214 IF P_DEBUG_MODE THEN
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_UnmatchedNegativeTxnFlagTab PA_PLSQL_DATATYPES.Char1TabTyp
323 default PA_PLSQL_DATATYPES.EmptyChar1Tab; -- added bug 14173646
324 l_CrossChargeTypeTab PA_PLSQL_DATATYPES.Char3TabTyp;
328 l_PrvdrOrganizationIdTab PA_PLSQL_DATATYPES.IdTabTyp;
325 l_CrossChargeCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
326 l_CCProcessIOCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
327 l_CCProcessIUCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
329 l_RecvrOrganizationIdTab PA_PLSQL_DATATYPES.IdTabTyp;
330 l_PrvdrOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
331 l_RecvrOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
332 l_Error_Stage VARCHAR2(2000);
333 l_Error_Code NUMBER;
334 l_StageTab PA_PLSQL_DATATYPES.NewAmtTabTyp;
335 l_StatusTab PA_PLSQL_DATATYPES.Char30TabTyp;
336
337 BEGIN
338
339 -- Assign all the input values to the tables. These tables will be passed
340 -- to the the main identification procedure.
341
342 pa_cc_utils.set_curr_function('PA_CC_IDENTIFY_TXN_ADJ');
343 IF P_DEBUG_MODE THEN
344 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_ADJ: ' || '10: Assign all the input values to the tables');
345 END IF;
346
347 l_ExpOrganizationIdTab(1) := P_ExpOrganizationId ;
348 l_ExpOrgIdTab(1) := P_ExpOrgId ;
349 l_ProjectIdTab(1) := P_ProjectId;
350 l_TaskIdTab(1) := P_TaskId;
351 l_ExpItemDateTab(1) := P_ExpItemDate;
352 l_ExpItemIdTab(1) := P_ExpItemId;
353 l_PersonIdTab(1) := P_PersonId;
354 l_ExpTypeTab(1) := P_ExpType;
355 l_SysLinkTab(1) := P_SysLink;
356 l_PrjOrganizationIdTab(1) := P_PrjOrganizationId;
357 l_PrjorgIdTab(1) := P_PrjorgId;
358 l_TransSourceTab(1) := P_TransSource;
359 l_NLROrganizationIdTab(1) := P_NLROrganizationId;
360 l_PrvdrLEIdTab(1) := NULL ;
361 l_RecvrLEIdTab(1) := NULL ;
362 l_StatusTab(1) := NULL ;
363 l_Error_Stage := X_Error_Stage;
364 l_Error_Code := X_Error_Code;
365 G_unmatched_neg_txn_flag := NVL(PA_TRX_IMPORT.G_unmatched_negative_txn_flag,'N'); -- added bug 14173646
366
367 IF P_DEBUG_MODE THEN
368 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_ADJ: ' || '20: Calling procedure PA_CC_IDENT.PA_CC_IDENTIFY_TXN');
369 END IF;
370
371 PA_CC_IDENT.PA_CC_IDENTIFY_TXN(
372 P_ExpOrganizationIdTab => l_ExpOrganizationIdTab,
373 P_ExpOrgidTab => l_ExpOrgidTab,
374 P_ProjectIdTab => l_ProjectIdTab,
375 P_TaskIdTab => l_TaskIdTab,
376 P_ExpItemDateTab => l_ExpItemDateTab,
377 P_ExpItemIdTab => l_ExpItemIdTab,
378 P_PersonIdTab => l_PersonIdTab,
379 P_ExpTypeTab => l_ExpTypeTab,
380 P_SysLinkTab => l_SysLinkTab,
381 P_PrjOrganizationIdTab => l_PrjOrganizationIdTab,
382 P_PrjOrgIdTab => l_PrjOrgIdTab,
383 P_TransSourceTab => l_TransSourceTab,
384 P_NLROrganizationIdTab => l_NLROrganizationIdTab,
385 P_PrvdrLEIdTab => l_PrvdrLEIdTab,
386 P_RecvrLEIdTab => l_RecvrLEIdTab,
387 X_StatusTab => l_StatusTab,
388 X_CrossChargeTypeTab => l_CrossChargeTypeTab,
389 X_CrossChargeCodeTab => l_CrossChargeCodeTab,
390 X_PrvdrOrganizationIdTab => l_PrvdrOrganizationIdTab,
391 X_RecvrOrganizationIdTab => l_RecvrOrganizationIdTab,
392 X_RecvrOrgIdTab => l_RecvrOrgIdTab,
393 X_Error_Stage => l_Error_Stage,
394 X_Error_Code => l_Error_Code,
395 /* Added calling module for 3234973 */
396 X_Calling_Module => X_Calling_Module);
397
398 IF P_DEBUG_MODE THEN
399 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN_ADJ: ' || '30: Assigning the returned values to the output variables');
400 END IF;
401
402 X_status := l_StatusTab(1);
403 X_CrossChargeType := l_CrossChargeTypeTab(1);
404 X_CrossChargeCode := l_CrossChargeCodeTab(1);
405 X_PrvdrOrganizationId := l_PrvdrOrganizationIdTab(1);
406 X_RecvrOrganizationId := l_RecvrOrganizationIdTab(1);
407 X_RecvrOrgId := l_RecvrOrgIdTab(1);
408
409
410 IF P_DEBUG_MODE THEN
411 pa_cc_utils.log_message( 'X_CrossChargeType :' || X_CrossChargeType);
412 pa_cc_utils.log_message( 'X_CrossChargeCode :' || X_CrossChargeCode);
413 pa_cc_utils.log_message( 'X_PrvdrOrganizationId :' || X_PrvdrOrganizationId);
414 pa_cc_utils.log_message( 'X_RecvrOrganizationId :' || X_RecvrOrganizationId);
415 pa_cc_utils.log_message( 'X_RecvrOrgId :' || X_RecvrOrgId);
416 pa_cc_utils.log_message( 'X_status :' || X_status);
417
418 pa_cc_utils.log_message('40: Exiting the procedure PA_CC_IDENT.PA_CC_IDENTIFY_TXN_ADJ');
419 END IF;
420
421 pa_cc_utils.reset_curr_function;
422 EXCEPTION
423
424 WHEN OTHERS THEN
425 x_error_stage := l_error_stage;
426 x_error_code := l_error_code;
427 RAISE ;
428
429 END PA_CC_IDENTIFY_TXN_ADJ;
430
431 PROCEDURE PA_CC_IDENTIFY_TXN(
432 P_ExpOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
433 P_ExpOrgidTab IN PA_PLSQL_DATATYPES.IdTabTyp,
434 P_ProjectIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
435 P_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
436 P_ExpItemDateTab IN PA_PLSQL_DATATYPES.DateTabTyp,
437 P_ExpItemIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
441 P_PrjOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
438 P_PersonIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
439 P_ExpTypeTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
440 P_SysLinkTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
442 P_PrjOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
443 P_TransSourceTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
444 P_NLROrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
445 P_PrvdrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
446 P_RecvrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
447 /* Added nocopy for 2672653 */
448 X_StatusTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
449 X_CrossChargeTypeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char3TabTyp,
450 X_CrossChargeCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
451 X_PrvdrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
452 X_RecvrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
453 X_RecvrOrgIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
454 X_Error_Stage OUT NOCOPY VARCHAR2,
455 X_Error_Code OUT NOCOPY NUMBER,
456 /* Added calling module for 3234973 */
457 X_Calling_Module IN VARCHAR2)
458
459 IS
460
461 l_CCProcessIOCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
462 l_CCProcessIUCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
463 l_CCPrjFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
464
465
466 l_CCProcessIOCode VARCHAR2(1);
467 l_CCProcessIUCode VARCHAR2(1);
468 l_CCPrjFlag VARCHAR2(1);
469
470 BEGIN
471
472 --
473 -- This procedure is called for determining the following attributes of
474 -- a transactions
475 -- 1. Provider Organization
476 -- 2. Receiver Organization
477 -- 3. Provider Operating Unit
478 -- 4. Receiver Operating Unit
479
480 pa_cc_utils.set_curr_function('PA_CC_IDENTIFY_TXN');
481
482 IF P_DEBUG_MODE THEN
483 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN: ' || '20.10: Calling the procedure to determine the orgs');
484 END IF;
485
486 PA_CC_IDENT.PA_CC_GET_PRVDR_RECVR_ORGS (
487 P_ExpOrganizationIdTab => P_ExpOrganizationIdTab,
488 P_ExpOrgidTab => P_ExpOrgidTab,
489 P_TaskIdTab => P_TaskIdTab,
490 P_ExpItemIdTab => P_ExpItemIdTab,
491 P_SysLinkTab => P_SysLinkTab,
492 P_ProjectIdTab => P_ProjectIdTab,
493 P_NLROrganizationIdTab => P_NLROrganizationIdTab,
494 P_ExpItemDateTab => P_ExpItemDateTab,
495 P_ExpTypeTab => P_ExpTypeTab,
496 P_PrjOrganizationIdTab => P_PrjOrganizationIdTab,
497 P_PrjOrgIdTab => P_PrjOrgIdTab,
498 P_TransSourceTab => P_TransSourceTab, -- added bug 14173646
499 X_StatusTab => X_StatusTab,
500 X_PrvdrOrganizationIdTab => X_PrvdrOrganizationIdTab,
501 X_RecvrOrganizationIdTab => X_RecvrOrganizationIdTab,
502 X_RecvrOrgIdTab => X_RecvrOrgIdTab,
503 X_CCProcessIOCodeTab => l_CCProcessIOCodeTab,
504 X_CCProcessIUCodeTab => l_CCProcessIUCodeTab,
505 X_CCPrjFlagTab => l_CCPrjFlagTab,
506 X_Error_Stage => X_Error_Stage,
507 X_Error_Code => X_Error_Code,
508 /* Added calling module for 3234973 */
509 X_Calling_Module => X_Calling_Module);
510
511 --
512 -- This procedure is called for determining the cross charge code.
513 --
514
515 IF P_DEBUG_MODE THEN
516 pa_cc_utils.log_message('PA_CC_IDENTIFY_TXN: ' || '20.20: Calling the procedure to determine the cross charge code and type');
517 END IF;
518
519 PA_CC_IDENT.PA_CC_GET_CROSS_CHARGE_TYPE (
520 P_PrvdrOrganizationIdTab => X_PrvdrOrganizationIdTab,
521 P_RecvrOrganizationIdTab => X_RecvrOrganizationIdTab,
522 P_ProjectIdTab => P_ProjectIdTab,
523 P_TaskIdTab => P_TaskIdTab,
524 P_SysLinkTab => P_SysLinkTab,
525 P_ExpItemIdTab => P_ExpItemIdTab,
526 P_PersonIdTab => P_PersonIdTab,
527 P_ExpItemDateTab => P_ExpItemDateTab,
528 P_PrvdrOrgIdTab => P_ExpOrgidTab,
529 P_RecvrOrgIdTab => X_RecvrOrgIdTab,
530 P_PrvdrLEIdTab => P_PrvdrLEIdTab,
531 P_RecvrLEIdTab => P_RecvrLEIdTab,
532 P_TransSourceTab => P_TransSourceTab,
533 P_CCProcessIOCodeTab => l_CCProcessIOCodeTab,
534 P_CCProcessIUCodeTab => l_CCProcessIUCodeTab,
535 P_CCPrjFlagTab => l_CCPrjFlagTab,
536 /* Passing calling module instead of hard coded 'TRANSACTION' for 3234973 */
537 P_calling_mode => X_Calling_Module,
538 X_StatusTab => X_StatusTab,
539 X_CrossChargeTypeTab => X_CrossChargeTypeTab,
540 X_CrossChargeCodeTab => X_CrossChargeCodeTab,
541 X_Error_Stage => X_Error_Stage,
542 X_Error_Code => X_Error_Code);
543
544 pa_cc_utils.reset_curr_function;
545
546 EXCEPTION
547
548 WHEN OTHERS THEN
549 RAISE ;
550
551 END PA_CC_IDENTIFY_TXN;
552
553 PROCEDURE PA_CC_GET_PRVDR_RECVR_ORGS (
554 P_ExpOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
558 P_SysLinkTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
555 P_ExpOrgidTab IN PA_PLSQL_DATATYPES.IdTabTyp,
556 P_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
557 P_ExpItemIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
559 P_ProjectIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
560 P_NLROrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
561 P_ExpItemDateTab IN PA_PLSQL_DATATYPES.DateTabTyp,
562 P_ExpTypeTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
563 P_PrjOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
564 P_PrjOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
565 /* Added nocopy for 2672653 */
566 P_TransSourceTab IN PA_PLSQL_DATATYPES.Char30TabTyp, -- added bug 14173646
567 X_StatusTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
568 X_PrvdrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
569 X_RecvrOrganizationIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
570 X_RecvrOrgIdTab IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
571 X_CCProcessIOCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
572 X_CCProcessIUCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
573 X_CCPrjFlagTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
574 X_Error_Stage OUT NOCOPY VARCHAR2,
575 X_Error_Code OUT NOCOPY NUMBER,
576 /* Added calling module for 3234973 */
577 X_Calling_Module IN VARCHAR2) IS
578
579 ----------------------------------------------------------------------------------------
580 -- These variables store the values during the previous iteration. These values are
581 -- compared with the values in the current iteration. Only if the values are different
582 -- the required SELECT is performed. Otherwise the previous results are used as the
583 -- current results
584
585 l_PrevTaskId NUMBER;
586 l_PrevRecvrOrganizationId NUMBER;
587 l_PrevRecvrOrgId NUMBER;
588 l_PrevPrvdrOrgId NUMBER;
589 l_PrevProjectId NUMBER;
590 l_PrevCCPrjFlag VARCHAR2(1);
591 ----------------------------------------------------------------------------------------
592 l_MaxRecs NUMBER;
593 l_MinRecs NUMBER;
594 l_Recvr_Organization_Id NUMBER;
595 l_Prvdr_Organization_Id NUMBER;
596 l_project_Org_Id NUMBER;
597 l_current_org_id NUMBER;
598 l_cc_prj_process_flag VARCHAR2(1);
599 l_cc_process_io_code VARCHAR2(1);
600 l_cc_process_iu_code VARCHAR2(1);
601 l_current_process_io_code VARCHAR2(1);
602 l_current_process_iu_code VARCHAR2(1);
603 l_PrevCCProcessIOCode VARCHAR2(1);
604 l_PrevCCProcessIUCode VARCHAR2(1);
605 l_UnmatchedNegativeTxnFlagTab PA_PLSQL_DATATYPES.Char1TabTyp
606 default PA_PLSQL_DATATYPES.EmptyChar1Tab; -- added bug 14173646
607
608
609 /* added the GetAdjExpid cursor as part of Bug 12955494 */
610
611 l_adj_exp_id NUMBER;
612 l_period_accrual_flag varchar2(5);
613
614 /* added as part of bug 12974801 */
615
616 CURSOR GetAdjExpid( c_exp_item_id number, c_transaction_source varchar2) IS
617 SELECT distinct ADJUSTED_EXPENDITURE_ITEM_ID ,period_accrual_flag
618 FROM PA_EXPENDITURE_ITEMS exp_item,
619 pa_expenditure_groups grp ,
620 pa_expenditures exp
621 WHERE exp_item.EXPENDITURE_ITEM_ID =c_exp_item_id
622 and exp_item.expenditure_id =exp.expenditure_id
623 and exp.expenditure_group = grp.expenditure_group
624 and exp_item.transaction_source = NVL(c_transaction_source, exp_item.transaction_source); -- added bug 14173646
625
626 BEGIN
627
628 l_MinRecs := P_SysLinkTab.FIRST;
629 l_MaxRecs := P_SysLinkTab.LAST;
630
631 --commented for bug 14581021 and added it in below loop l_UnmatchedNegativeTxnFlagTab(1) := NVL(G_unmatched_neg_txn_flag, 'N'); -- added bug 14173646
632 FOR j in l_MinRecs..l_MaxRecs LOOP
633 l_UnmatchedNegativeTxnFlagTab(j) := NVL(G_unmatched_neg_txn_flag, 'N');
634 END LOOP;
635
636 pa_cc_utils.set_curr_function('PA_CC_GET_PRVDR_RECVR_ORGS');
637
638 --
639 -- Determine Current Operating Unit
640 --
641 -- Expenditure Operating Unit is the OU in which the expenditure item is created. If
642 -- it is not passed to this procedure then the current operating unit is used as the
643 -- Expenditure Operating Unit. But usually the Expenditure Operating Unit is passed
644 -- to the procedure except Transaction Import.
645 --
646
647 IF P_DEBUG_MODE THEN
648 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.10: Determine the Expenditure OU and its cross charge options');
649 print_message('G_PrevCCProcessIOCode=' ||G_PrevCCProcessIOCode);
650 print_message('l_current_org_id=' ||l_current_org_id);
651 print_message('l_current_process_io_code=' ||l_current_process_io_code);
652 print_message('l_current_process_iu_code=' ||l_current_process_iu_code);
653 END IF;
654
655 If G_PrevCCProcessIOCode is NULL Then
656
657 IF P_DEBUG_MODE THEN
658 print_message('Selecting from pa_implementaions');
659 END IF;
660
661 SELECT imp.org_id,
662 nvl(imp.cc_process_io_code,'N'),
666 l_current_process_iu_code
663 nvl(imp.cc_process_iu_code,'N')
664 INTO l_current_org_id,
665 l_current_process_io_code,
667 FROM pa_implementations imp ;
668
669 G_PrevPrvdrOrgId := l_current_org_id ;
670 G_PrevCCProcessIOCode := l_current_process_io_code ;
671 G_PrevCCProcessIUCode := l_current_process_iu_code ;
672
673 Else
674 l_current_org_id := G_PrevPrvdrOrgId;/* 3933401 */
675 l_current_process_io_code := G_PrevCCProcessIOCode;/* 3933401 */
676 l_current_process_iu_code := G_PrevCCProcessIUCode;/* 3933401 */
677
678 End If;
679
680 l_PrevPrvdrOrgId := G_PrevPrvdrOrgId ;
681 l_PrevCCProcessIOCode := G_PrevCCProcessIOCode ;
682 l_PrevCCProcessIUCode := G_PrevCCProcessIUCode ;
683
684 IF P_DEBUG_MODE THEN
685 print_message('After l_current_org_id=' ||l_current_org_id);
686 print_message('l_current_process_io_code=' ||l_current_process_io_code);
687 print_message('l_current_process_iu_code=' ||l_current_process_iu_code);
688 END IF;
689
690 --
691 -- End get current operating unit
692 --
693
694 --
695 --
696 FOR j IN l_MinRecs..l_MaxRecs
697 LOOP
698
699 /* Start changes bug 12955494 Added checking for adjusted EI */
700
701 IF l_UnmatchedNegativeTxnFlagTab(j) = 'Y'
702 THEN l_adj_exp_id := null;
703 ELSE
704 OPEN GetAdjExpid (P_ExpItemIdTab(j), P_TransSourceTab(j));
705 FETCH GetAdjExpid
706 INTO l_adj_exp_id , l_period_accrual_flag ;
707 CLOSE GetAdjExpid; /* Bug 12955494 */
708 END IF;
709
710 IF P_DEBUG_MODE THEN -- begin bug 14173646
711 pa_cc_utils.log_message('Parameters passed in GetAdjExpid.');
712 pa_cc_utils.log_message('P_ExpItemIdTab : ' || P_ExpItemIdTab(j));
713 pa_cc_utils.log_message('P_TransSourceTab : ' || P_TransSourceTab(j));
714 pa_cc_utils.log_message('G_UnmatchedNegativeTxnFlagTab : ' || G_unmatched_neg_txn_flag);
715 END IF; -- end bug 14173646
716
717 pa_cc_utils.log_message('Adjusted from expenditure item id : ' || l_adj_exp_id);
718 pa_cc_utils.log_message('Skipping re-derivation of provider and receiver org ids');
719
720 IF l_adj_exp_id IS NULL OR (l_adj_exp_id IS NOT NULL AND l_period_accrual_flag='Y')
721 OR X_Calling_Module ='TRANSACTION_IMPORT' /*Bug 14230879*/ THEN /* end of changes bug 12589016 , added l_period_accrual_flag condition : 12974801 */
722
723 IF X_StatusTab(j) IS NOT NULL THEN
724 -- If the expenditure items are already erroneous which will be indicated
725 -- by this PL/SQL able then do not process CC identification for that
726 -- expenditure item
727
728 IF P_DEBUG_MODE THEN
729 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');
730 END IF;
731 NULL ;
732
733 ELSE
734 --
735 -- Initialize local variables
736 --
737 l_project_org_id := NULL ;
738
739 IF P_DEBUG_MODE THEN
740 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.30: Determine Provider Organization');
741 END IF;
742
743 -- Determine Provider Organization
744 --
745 --
746 -- The provider organization is Non-Labor Organization if the system linkage
747 -- is a Usage and Expenditure organization for the others
748 --
749 X_Error_Stage := 'Checking if system linkage function is Usage';
750 IF P_SysLinkTab(j) = 'USG' THEN
751 -- If the system_linkage_function is 'USG' the provider organization
752 -- will be the non-labor resource organization of the resource. So if
753
754 X_Error_Stage := 'Checking if NLR organization id is NULL for usages';
755 IF P_DEBUG_MODE THEN
756 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.40: Checking if NLR organization id is NULL for usages');
757 END IF;
758 IF P_NLROrganizationIdTab(j) IS NULL THEN
759 -- So if non-labor resource organization is not provided then an error
760 -- code is returned.
761
762 IF P_DEBUG_MODE THEN
763 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.50: Setting the error code for null NLR organization Id');
764 END IF;
765 X_Error_Stage := 'Setting the error code for null NLR organization Id';
766 X_StatusTab(j) := 'PA_CC_NO_NL_ORG_FOR_USG';
767 ELSE
768 -- Return non-labor resource organization as the provider organization.
769
770 IF P_DEBUG_MODE THEN
771 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.60: Returning the NLR organization Id');
772 END IF;
773 X_Error_Stage := 'Returning the NLR organization Id';
774 l_Prvdr_Organization_Id := P_NLROrganizationIdTab(j);
775 END IF;
776
777 ELSE
778 -- Else the provider organization is the expenditure organization. Return
779 -- expenditure organization as provider organization.
780
781 IF P_DEBUG_MODE THEN
782 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.70: Returning the expenditure organization id');
783 END IF;
787
784 X_Error_Stage := 'Returning the expenditure organization id';
785 l_Prvdr_Organization_Id := P_ExpOrganizationIdTab(j);
786 END IF;
788 -- Determine Receiver Organization
789 --
790 --
791 -- Receiver Organization is the organization that owns the task
792 -- ( Carrying_Out_Organization_Id). In this select we also
793
794 IF P_DEBUG_MODE THEN
795 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');
796 END IF;
797 X_Error_Stage := 'Checking if project OU is NULL';
798 IF l_PrevTaskId = P_TaskIdTab(j) THEN
799 -- If the current task id is the same as the previous task id then
800 -- the receiver organization and receiver operating unit will be
801 -- the same as previous one
802
803 IF P_DEBUG_MODE THEN
804 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');
805 END IF;
806 l_Recvr_Organization_Id := l_PrevRecvrOrganizationId ;
807 l_project_org_id := l_PrevRecvrOrgId ;
808 l_cc_prj_process_flag := l_PrevCCPrjFlag ;
809
810
811 ELSE
812 -- If the current task id is not the same as the previous task id
813 -- then select the new receiver organization and receiver operating
814 -- unit
815 -- In this select we also get the project operating unit if it is not
816 -- passed to this procedure. This is done so that we can avoid an
817 -- extra SELECT later on while deriving the value of Provider OU.
818
819 X_Error_Stage := 'Checking if current project id is the same as the previous project id' ;
820
821 IF l_PrevProjectId = P_ProjectIdTab(j) THEN
822 --
823 -- If the current project id is the same as the previous project id
824 -- then we do not need to join to the projects table to get the project
825 -- org id which is the receiver OU.
826 --
827 IF P_DEBUG_MODE THEN
828 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');
829 END IF;
830
831 If (G_PrevTaskId = P_TaskIdTab(j) and G_PrevSysLink = P_SysLinkTab(j)) Then
832
833 l_recvr_organization_id := G_PrevRecvrOrgnId;
834 l_cc_prj_process_flag := G_PrevCCPrjProcessFlag;
835
836 Else
837 SELECT t.carrying_out_organization_id,
838 decode(sl.LABOR_NON_LABOR_FLAG,
839 'Y', t.cc_process_labor_flag,
840 t.cc_process_nl_flag)
841 INTO l_recvr_organization_id,
842 l_cc_prj_process_flag
843 FROM pa_system_linkages sl,
844 pa_tasks t
845 WHERE t.task_id = P_TaskIdTab(j)
846 AND sl.function = P_SysLinkTab(j) ;
847
848 G_PrevTaskId := P_TaskIdTab(j);
849 G_PrevSysLink := P_SysLinkTab(j);
850 G_PrevRecvrOrgnId := l_recvr_organization_id;
851 G_PrevCCPrjProcessFlag := l_cc_prj_process_flag;
852
853 End If;
854
855 l_project_org_id := l_PrevRecvrOrgId ;
856
857 ELSE
858 --
859 -- If the current project id is not the same as the previous project
860 -- id the we need to join to the projects table to get the project
861 -- org id also.
862 --
863
864 IF P_DEBUG_MODE THEN
865 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');
866 END IF;
867
868 BEGIN -- 2650361
869
870 SELECT t.carrying_out_organization_id,
871 p.org_id,
872 decode(sl.LABOR_NON_LABOR_FLAG,
873 'Y', t.cc_process_labor_flag,
874 t.cc_process_nl_flag)
875 INTO l_recvr_organization_id,
876 l_project_org_id,
877 l_cc_prj_process_flag
878 FROM pa_system_linkages sl,
879 pa_projects_all p,
880 pa_tasks t
881 WHERE p.project_id = t.project_id
882 AND t.task_id = P_TaskIdTab(j)
883 AND sl.function = P_SysLinkTab(j) ;
884 /* Adding exception handling for 2650361 */
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 IF P_DEBUG_MODE THEN
888 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');
889 END IF;
890 UPDATE pa_expenditure_items_all
891 SET cost_dist_rejection_code = 'INV_DATA'
892 WHERE task_id = P_TaskIdTab(j)
893 AND cost_distributed_flag = 'S'
894 AND cost_dist_rejection_code is null;
895 END;
896 /* 2650361 */
897
901 G_PrevCCPrjProcessFlag := l_cc_prj_process_flag;
898 G_PrevTaskId := P_TaskIdTab(j);
899 G_PrevSysLink := P_SysLinkTab(j);
900 G_PrevRecvrOrgnId := l_recvr_organization_id;
902 G_PrevPrjOrgId := l_project_org_id;
903
904 END IF;
905
906 l_PrevRecvrOrgId := l_project_org_id ;
907 l_PrevRecvrOrganizationId := l_recvr_organization_id ;
908 l_PrevCCPrjFlag := l_cc_prj_process_flag ;
909
910 END IF;
911
912 -- Determine Provider Operating Unit
913 --
914 --
915 -- The Operating Unit in which the Expenditure is charged. In this case it is the
916 -- same as p_expenditure_org_id.
917
918 IF P_DEBUG_MODE THEN
919 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.120: Determine the cross charge process codes in implementations');
920 END IF;
921 X_Error_Stage := 'Determine the cross charge process codes in implementations';
922
923 --
924 -- Also determine the cross charge process codes in implementations
925 --
926
927 IF nvl(l_PrevPrvdrOrgId,-99) = nvl(P_ExpOrgidTab(j),-99) THEN
928 /* bug#3167296 added nvlto handle single org case, please note that single org case
929 was handled before this fix also using l_current_org_id is null condition below
930 first time its fine but second time it fails because l_current_process_io_code
931 being not available, after fix it works first as well as subsequent time */
932
933 --
934 -- If the current operating unit is the same as the previous operating
935 -- unit then we can use the same implementation options as that of the
936 -- previous one
937 --
938 IF P_DEBUG_MODE THEN
939 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.130: If expenditure OUs are not changed use previous values');
940 END IF;
941
942 l_cc_process_io_code := l_PrevCCProcessIOCode ;
943 l_cc_process_iu_code := l_PrevCCProcessIUCode ;
944 IF P_DEBUG_MODE THEN
945 print_message('l_cc_process_io_code=' ||l_cc_process_io_code);
946 print_message('l_cc_process_iu_code=' ||l_cc_process_iu_code);
947 END IF;
948 ELSE
949 IF l_current_org_id IS NULL THEN
950 --
951 -- If the current operating unit is null then it is a single org
952 -- implementation. For the single org implementation we use the
953 -- same implementation options that were used at the start of the
954 -- procedure.
955 --
956
957 IF P_DEBUG_MODE THEN
958 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.140: Expenditure OU is the same as current OU ');
959 END IF;
960 l_cc_process_io_code := l_current_process_io_code ;
961 l_cc_process_iu_code := l_current_process_iu_code ;
962
963 ELSE
964 IF P_DEBUG_MODE THEN
965 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.150: Determine cross charge codes of current Expenditure OU');
966 END IF;
967
968 SELECT nvl(imp.cc_process_io_code,'N'),
969 nvl(imp.cc_process_iu_code,'N')
970 INTO l_cc_process_io_code,
971 l_cc_process_iu_code
972 FROM pa_implementations_all imp
973 WHERE imp.org_id = nvl(P_ExpOrgidTab(j), l_current_org_id) ;
974
975 END IF;
976 IF P_DEBUG_MODE THEN
977 print_message('l_cc_process_io_code=' ||l_cc_process_io_code);
978 print_message('l_cc_process_iu_code=' ||l_cc_process_iu_code);
979 END IF;
980
981 END IF ;
982
983 l_PrevCCProcessIUCode := l_cc_process_iu_code ;
984 l_PrevCCProcessIOCode := l_cc_process_io_code ;
985 X_CCProcessIUCodeTab(j) := l_cc_process_iu_code ;
986 X_CCProcessIOCodeTab(j) := l_cc_process_io_code ;
987
988 --
989 -- Determine Receiver Operating Unit
990 --
991 --
992
993 IF P_DEBUG_MODE THEN
994 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.160: Determine Receiver Operating Unit');
995 END IF;
996
997 X_Error_Stage := 'Assigning the project OU as provider OU';
998 X_PrvdrOrganizationIdTab(j) := l_Prvdr_Organization_Id ;
999 X_RecvrOrganizationIdTab(j) := l_recvr_organization_id ;
1000
1001 l_PrevPrvdrOrgId := P_ExpOrgidTab(j);
1002 X_RecvrOrgIdTab(j) := NVL( P_PrjOrgIdTab(j), l_project_org_id);
1003 X_CCPrjFlagTab(j) := l_cc_prj_process_flag;
1004
1005 IF P_DEBUG_MODE THEN
1006 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');
1007 END IF;
1008
1009 PA_CC_IDENT_CLIENT_EXTN.OVERRIDE_PRVDR_RECVR (
1010 P_PrvdrOrganizationId => X_PrvdrOrganizationIdTab(j),
1011 P_PrvdrOrgId => P_ExpOrgidTab(j),
1012 P_RecvrOrganizationId => X_RecvrOrganizationIdTab(j),
1016 X_Status => X_StatusTab(j),
1013 P_RecvrOrgId => X_RecvrOrgIdTab(j),
1014 P_TransId => P_ExpItemIdTab(j),
1015 P_SysLink => P_SysLinkTab(j),
1017 X_PrvdrOrganizationId => X_PrvdrOrganizationIdTab(j),
1018 X_RecvrOrganizationId => X_RecvrOrganizationIdTab(j),
1019 X_Error_Stage => X_Error_Stage,
1020 X_Error_Code => X_Error_Code,
1021 /* Added calling module for 3234973
1022 P_calling_mode => 'TRANSACTION', */
1023 P_calling_mode => X_Calling_Module);
1024
1025 END IF;
1026
1027 IF P_DEBUG_MODE THEN
1028 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.180: After determining the orgs for this transaction');
1029 print_message('Exiting..l_cc_process_io_code=' ||l_cc_process_io_code);
1030 print_message('l_cc_process_iu_code=' ||l_cc_process_iu_code);
1031 END IF;
1032
1033 END IF; /* Bug 12955494 */
1034 -- CLOSE GetAdjExpid; /* Bug 12955494 */
1035 END LOOP;
1036
1037 IF P_DEBUG_MODE THEN
1038 pa_cc_utils.log_message('PA_CC_GET_PRVDR_RECVR_ORGS: ' || '20.10.180: After determining the orgs for the bulk of transactions');
1039 END IF;
1040
1041 pa_cc_utils.reset_curr_function;
1042
1043 EXCEPTION
1044
1045 WHEN OTHERS THEN
1046 RAISE ;
1047
1048 END PA_CC_GET_PRVDR_RECVR_ORGS ;
1049
1050 PROCEDURE PA_CC_GET_CROSS_CHARGE_TYPE (
1051 P_PrvdrOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1052 P_RecvrOrganizationIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1053 P_ProjectIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1054 P_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1055 P_SysLinkTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
1056 P_ExpItemIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1057 P_PersonIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1058 P_ExpItemDateTab IN PA_PLSQL_DATATYPES.DateTabTyp,
1059 P_PrvdrOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1060 P_RecvrOrgIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1061 P_PrvdrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1062 P_RecvrLEIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
1063 P_TransSourceTab IN PA_PLSQL_DATATYPES.Char30TabTyp,
1064 P_CCProcessIOCodeTab IN PA_PLSQL_DATATYPES.Char1TabTyp,
1065 P_CCProcessIUCodeTab IN PA_PLSQL_DATATYPES.Char1TabTyp,
1066 P_CCPrjFlagTab IN PA_PLSQL_DATATYPES.Char1TabTyp,
1067 P_calling_mode IN VARCHAR2 ,
1068 /* Added nocopy for 2672653 */
1069 X_StatusTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1070 X_CrossChargeTypeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char3TabTyp,
1071 X_CrossChargeCodeTab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
1072 X_Error_Stage OUT NOCOPY VARCHAR2,
1073 X_Error_Code OUT NOCOPY NUMBER) IS
1074
1075 ----------------------------------------------------------------------------------------
1076 -- These variables store the values during the previous iteration. These values are
1077 -- compared with the values in the current iteration. Only if the values are different
1078 -- the required SELECT is performed. Otherwise the previous results are used as the
1079 -- current results
1080
1081 l_PrevCCTrScFlag VARCHAR2(1);
1082 l_PrevRecvrOrgId NUMBER;
1083 l_PrevPrvdrOrgId NUMBER;
1084 l_PrevPrvdrLEId NUMBER;
1085 l_PrevRecvrLEId NUMBER;
1086 l_PrevProjectId NUMBER;
1087 l_PrevCCPrjFlag VARCHAR2(1);
1088 l_PrevSysLink VARCHAR2(30);
1089 l_PrevTransSource VARCHAR2(30);
1090 l_PrevCrossChargeCode VARCHAR2(1);
1091 l_PrevCrossChargeType VARCHAR2(3);
1092
1093 ----------------------------------------------------------------------------------------
1094 l_CallExtnTab PA_PLSQL_DATATYPES.Char1TabTyp;
1095 l_OvrridCrossChargeCodeTab PA_PLSQL_DATATYPES.Char1TabTyp;
1096 l_MaxRecs NUMBER;
1097 l_MinRecs NUMBER;
1098 l_Recvr_Organization_Id NUMBER;
1099 l_Recvr_Org_Id NUMBER;
1100 l_Prvdr_Org_Id NUMBER;
1101 l_Recvr_LE_Id NUMBER;
1102 l_Prvdr_LE_Id NUMBER;
1103 l_Recvr_LE_Id_1 NUMBER;/*4482589*/
1104 l_Prvdr_LE_Id_1 NUMBER;/*4482589*/
1105 l_project_Org_Id NUMBER;
1106 l_prvdr_project_id NUMBER;
1107 l_vendor_site_id NUMBER;
1108 l_cross_charge_code VARCHAR2(1);
1109 l_cross_charge_type VARCHAR2(3);
1110 l_cc_tr_src_process_flag VARCHAR2(1);
1111 l_cc_prj_process_flag VARCHAR2(1);
1112 l_calling_module VARCHAR2(100);
1113 l_UnmatchedNegativeTxnFlagTab PA_PLSQL_DATATYPES.Char1TabTyp
1114 default PA_PLSQL_DATATYPES.EmptyChar1Tab; -- added bug 14173646
1115
1116
1117 CURSOR GetCCOrgRel(c_prvdr_org_id NUMBER,
1118 c_recvr_org_id NUMBER) IS
1119 SELECT DECODE(co.prvdr_allow_cc_flag, 'N','N', co.cross_charge_code),
1120 co.prvdr_project_id, co.vendor_site_id
1121 FROM pa_cc_org_relationships co
1122 WHERE co.prvdr_org_id = c_prvdr_org_id
1123 AND co.recvr_org_id = c_recvr_org_id;
1124
1128
1125 /* added the GetAdjExpid cursor as part of Bug 12955494 */
1126
1127 l_adj_exp_id NUMBER;
1129 l_period_accrual_flag varchar2(5);
1130
1131 CURSOR GetAdjExpid( c_exp_item_id number, c_transaction_source varchar2) IS
1132 SELECT distinct ADJUSTED_EXPENDITURE_ITEM_ID ,period_accrual_flag
1133 FROM PA_EXPENDITURE_ITEMS exp_item,
1134 pa_expenditure_groups grp ,
1135 pa_expenditures exp
1136 WHERE exp_item.EXPENDITURE_ITEM_ID =c_exp_item_id
1137 and exp_item.expenditure_id =exp.expenditure_id
1138 and exp.expenditure_group = grp.expenditure_group
1139 and exp_item.transaction_source = NVL(c_transaction_source, exp_item.transaction_source); -- added bug 14173646;
1140
1141 BEGIN
1142
1143 If P_calling_mode is NULL then
1144 l_calling_module := 'TRANSACTION';
1145 Else
1146 l_calling_module := P_calling_mode;
1147 End if;
1148
1149 pa_cc_utils.set_curr_function('PA_CC_GET_CROSS_CHARGE_TYPE');
1150
1151 l_MinRecs := P_SysLinkTab.FIRST;
1152 l_MaxRecs := P_SysLinkTab.LAST;
1153
1154 --commented for bug 14581021 and added it in below loop l_UnmatchedNegativeTxnFlagTab(1) := NVL(G_unmatched_neg_txn_flag, 'N'); -- added bug 14173646
1155 FOR j in l_MinRecs..l_MaxRecs LOOP
1156 l_UnmatchedNegativeTxnFlagTab(j) := NVL(G_unmatched_neg_txn_flag, 'N');
1157 END LOOP;
1158
1159 IF P_DEBUG_MODE THEN
1160 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.10: Determine cross charge code and type');
1161 END IF;
1162
1163 FOR j IN l_MinRecs..l_MaxRecs
1164 LOOP
1165
1166 /* Start changes bug 12955494 Added checking for adjusted EI */
1167 IF l_UnmatchedNegativeTxnFlagTab(j) = 'Y'
1168 then l_adj_exp_id := null;
1169 ELSE
1170 OPEN GetAdjExpid (P_ExpItemIdTab(j), P_TransSourceTab(j));
1171 FETCH GetAdjExpid
1172 INTO l_adj_exp_id , l_period_accrual_flag;
1173 CLOSE GetAdjExpid; /* Bug 12955494 */
1174 END IF;
1175
1176 IF P_DEBUG_MODE THEN -- begin bug 14173646
1177 pa_cc_utils.log_message('Parameters passed in GetAdjExpid.');
1178 pa_cc_utils.log_message('P_ExpItemIdTab : ' || P_ExpItemIdTab(j));
1179 pa_cc_utils.log_message('P_TransSourceTab : ' || P_TransSourceTab(j));
1180 pa_cc_utils.log_message('G_unmatched_neg_Txn_Flag : ' || G_unmatched_neg_txn_flag);
1181 END IF; -- end bug 14173646
1182
1183 pa_cc_utils.log_message('Adjusted from expenditure item id : ' || l_adj_exp_id);
1184 pa_cc_utils.log_message('Skipping re-derivation of provider and receiver org ids');
1185
1186 IF l_adj_exp_id IS NULL OR (l_adj_exp_id IS NOT NULL AND l_period_accrual_flag='Y')
1187 OR P_calling_mode ='TRANSACTION_IMPORT' /*Bug 14230879*/ THEN /* end of changes bug 12589016 ,added l_period_accrual_flag condition : 12974801 */
1188
1189 IF X_StatusTab(j) IS NOT NULL THEN
1190 -- If the expenditure items are already erroneous which will be indicated
1191 -- by this PL/SQL able then do not process CC identification for that
1192 -- expenditure item
1193
1194 IF P_DEBUG_MODE THEN
1195 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');
1196 END IF;
1197 NULL ;
1198
1199 ELSE
1200
1201 --
1202 -- After we have the provider OU, receiver OU, provider and receiver
1203 -- organizations check if the transaction requires cc processing.
1204 --
1205
1206 IF P_DEBUG_MODE THEN
1207 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.30: Check for never cross charged');
1208 END IF;
1209
1210 IF ( ( P_PrvdrOrganizationIdTab(j) = P_RecvrOrganizationIdTab(j) AND
1211 nvl(P_RecvrOrgIdTab(j), -99) = nvl(P_PrvdrOrgIdTab(j), -99)) OR
1212 P_SysLinkTab(j) = 'BTC' ) THEN
1213
1214 IF P_DEBUG_MODE THEN
1215 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');
1216 END IF;
1217 --
1218 -- If provider organization is the same as the receiver organization
1219 -- and provider OU is the same as the receiver OU then the transaction
1220 -- is never cross charged. Another situation where the transaction is
1221 -- never cross charged is if the transaction is a burden transaction.
1222 --
1223
1224 l_cross_charge_code := 'X' ;
1225 l_cross_charge_type := 'NO' ;
1226 l_PrevRecvrLEId := NULL; /*4482589*/
1227 l_PrevPrvdrLEId := NULL; /*4482589*/
1228 l_CallExtnTab(j) := 'N' ;
1229
1230 ELSIF P_RecvrOrgIdTab(j) IS NULL THEN
1231
1232 IF P_DEBUG_MODE THEN
1233 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 .');
1234 END IF;
1235
1236 l_cross_charge_code := P_CCProcessIOCodeTab(j) ;
1237 l_cross_charge_type := 'NO' ;
1238 l_PrevRecvrLEId := NULL; /*4482589*/
1239 l_PrevPrvdrLEId := NULL; /*4482589*/
1240 l_CallExtnTab(j) := 'Y' ;
1241
1242 ELSE
1243 --
1244 -- Determine the cross charge type
1245 --
1246
1247 IF P_DEBUG_MODE THEN
1248 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.60: Determine the cross charge type');
1249 END IF;
1250
1251 IF P_PrvdrLEIdTab(j) = l_PrevPrvdrLEId AND
1255
1252 P_RecvrLEIdTab(j) = l_PrevRecvrLEId AND
1253 P_RecvrOrgIdTab(j) = l_PrevRecvrOrgId AND
1254 P_PrvdrOrgIdTab(j) = l_PrevPrvdrOrgId THEN
1256 --
1257 -- If the current provider legal entity is the same as the
1258 -- previous provider legal entity and the current receiver legal
1259 -- entity is the same as the previous receiver legal entity then
1260 -- use the same cross charge code as that of the previous one
1261 --
1262 IF P_DEBUG_MODE THEN
1263 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');
1264 END IF;
1265
1266 l_cross_charge_type := l_PrevCrossChargeType;
1267
1268 ELSE
1269 --
1270 -- Get the value of the provider legal entity
1271 --
1272 IF P_DEBUG_MODE THEN
1273 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.80: Determining provider legal entity');
1274 END IF;
1275
1276 IF P_PrvdrLEIdTab(j) IS NULL THEN
1277 --
1278 -- If the provider legal entity parameter passed into the
1279 -- procedure is null then derrive it based on the provider
1280 -- OU.
1281 --
1282
1283 IF nvl(P_PrvdrOrgIdTab(j),-99) = NVL(l_PrevPrvdrOrgId, -99) THEN /*4482589*/
1284
1285 --
1286 -- If the previous provider OU is the same as the current
1287 -- provider OU then use the legal entity value derrived in
1288 -- the previous iteration.
1289 --
1290 IF P_DEBUG_MODE THEN
1291 print_message('Inside P_PrvdrOrgIdTab = NVL(l_PrevPrvdrOrgId, -99)');
1292 END IF;
1293 If l_PrevPrvdrLEId is null Then /*4482589*/
1294 l_PrevPrvdrLEId := GetLegalEntity(P_PrvdrOrgIdTab(j));
1295 End if;
1296
1297 l_Prvdr_LE_Id := l_PrevPrvdrLEId ;
1298 l_prvdr_le_id_1 := GetLegalEntity(P_PrvdrOrgIdTab(j));
1299
1300 ELSE
1301 --
1302 -- Derrive the provider legal entity based on the current
1303 -- provider OU
1304 --
1305 l_Prvdr_LE_Id := GetLegalEntity(P_PrvdrOrgIdTab(j));
1306
1307 l_PrevPrvdrLEId := l_Prvdr_LE_Id ;
1308 IF P_DEBUG_MODE THEN
1309 print_message('After Getlegalentity - '||l_Prvdr_LE_Id);
1310 END IF;
1311 END IF;
1312 ELSE
1313 l_Prvdr_LE_Id := P_PrvdrLEIdTab(j);
1314
1315 l_PrevPrvdrLEId := l_Prvdr_LE_Id ;
1316 END IF;
1317 IF P_DEBUG_MODE THEN
1318 print_message('l_Prvdr_LE_Id ='||l_Prvdr_LE_Id);
1319 print_message('l_PrevPrvdrLEId ='||l_PrevPrvdrLEId);
1320 END IF;
1321
1322 --
1323 -- Get the value of the receiver legal entity
1324 --
1325 IF P_DEBUG_MODE THEN
1326 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.90: Determining receiver legal entity');
1327 END IF;
1328
1329 IF P_RecvrLEIdTab(j) IS NULL THEN
1330 --
1331 -- If the provider legal entity parameter passed into the
1332 -- procedure is null then derrive it based on the provider
1333 -- OU.
1334 --
1335 IF nvl(P_RecvrOrgIdTab(j),-99) = NVL(l_PrevRecvrOrgId, -99) THEN /*4482589*/
1336 --
1337 -- If the previous provider OU is the same as the current
1338 -- provider OU then use the legal entity value derrived in
1339 -- the previous iteration.
1340 --
1341 If l_PrevRecvrLEId is null Then /*4482589*/
1342 l_PrevRecvrLEId := GetLegalEntity(NVL(P_RecvrOrgIdTab(j),l_Recvr_Org_Id));
1343 End if;
1344
1345 l_Recvr_LE_Id := l_PrevRecvrLEId ;
1346 l_recvr_le_id_1:= GetLegalEntity(NVL(P_RecvrOrgIdTab(j),l_Recvr_Org_Id));
1347
1348 ELSE
1349 --
1350 -- Derrive the provider legal entity based on the current
1351 -- provider OU
1352 --
1353 l_Recvr_LE_Id := GetLegalEntity(NVL(P_RecvrOrgIdTab(j),
1354 l_Recvr_Org_Id));
1355 l_PrevRecvrLEId := l_Recvr_LE_Id ;
1356 END IF;
1357 ELSE
1358 l_Recvr_LE_Id := P_RecvrLEIdTab(j);
1359
1360 l_PrevRecvrLEId := l_Recvr_LE_Id ;
1361 END IF;
1362 IF P_DEBUG_MODE THEN
1363 print_message('l_Recvr_LE_Id ='||l_Recvr_LE_Id);
1364 print_message('l_PrevRecvrLEId ='||l_PrevRecvrLEId);
1365 END IF;
1366
1367 --
1368 -- Get the cross charge type
1369 --
1370
1371 IF nvl(l_Recvr_LE_Id,-99) <> nvl(l_Prvdr_LE_Id,-99) THEN
1372 l_cross_charge_type := 'IC' ;
1373 ELSIF nvl(P_RecvrOrgIdTab(j),-99) <> nvl(P_PrvdrOrgIdTab(j),-99) THEN
1374 l_cross_charge_type := 'IU' ;
1375 ELSE
1376 l_cross_charge_type := 'IO' ;
1377 END IF;
1378 IF P_DEBUG_MODE THEN
1379 print_message('l_cross_charge_type ='||l_cross_charge_type);
1383
1380 END IF;
1381
1382 END IF; -- Determine Cross Charge Type
1384 --
1385 -- Check if the project requires cross charge processing to be done
1386 -- for that class(labor or non-labor) of transactions charged to that
1387 -- project as for some cases cross charge processing may be done
1388 -- externally.
1389 --
1390
1391
1392 IF P_DEBUG_MODE THEN
1393 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.100: Check if project is cross chargeable');
1394 END IF;
1395
1396 IF P_CCPrjFlagTab(j) = 'N' THEN
1397 --
1398 -- Section : Check if the project is cross chargeable.
1399 --
1400 -- If the labor non-labor process flag on the project is 'N' then
1401 -- there is no need of processing cross charge for this project.
1402 --
1403
1404 l_cross_charge_code := 'N';
1405 l_CallExtnTab(j) := 'N' ;
1406
1407 ELSE
1408
1409 IF P_DEBUG_MODE THEN
1410 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.110: Check if transaction source is cross chargeable');
1411 END IF;
1412
1413 IF P_TransSourceTab(j) IS NOT NULL THEN
1414 --
1415 -- Section : Not Null transaction source.
1416 --
1417 -- Check if this transaction is from an external transactions source
1418 -- i.e. if the transaction source element is NULL.
1419 --
1420
1421 IF P_DEBUG_MODE THEN
1422 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.120: Check if transaction source is same as previous one');
1423 END IF;
1424
1425 IF P_TransSourceTab(j) = l_PrevTransSource THEN
1426 --
1427 -- Section : Get the cross charge process flag.
1428 --
1429 -- If the current transaction source is the same as the previous one
1430 -- then use the previous value of the cc_process_flag.
1431 --
1432
1433 l_cc_tr_src_process_flag := l_PrevCCTrScFlag;
1434
1435 ELSE
1436 --
1437 -- If the transaction source is different then get the value of
1438 -- cross charge process flag for the current transaction source.
1439 --
1440 IF P_DEBUG_MODE THEN
1441 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.130: Get the cc process flag for current transaction source');
1442 END IF;
1443
1444 SELECT t.cc_process_flag
1445 INTO l_cc_tr_src_process_flag
1446 FROM pa_transaction_sources t
1447 WHERE t.transaction_source = P_TransSourceTab(j) ;
1448
1449 l_PrevCCTrScFlag := l_cc_tr_src_process_flag ;
1450 l_PrevTransSource:= P_TransSourceTab(j); /*Bug#3364107*/
1451
1452 END IF; -- End Section : Get the cross charge process flag.
1453
1454 END IF;
1455
1456 IF (P_TransSourceTab(j) IS NOT NULL ) AND (l_cc_tr_src_process_flag <> 'Y' ) THEN /*Bug#3364107*/
1457 --
1458 -- Section : Check if transaction source is cross chargeable.
1459 --
1460 -- If the transaction source does not require cross charge processing
1461 -- then set the value of cross charge code and skip all the other
1462 -- checks.
1463 --
1464
1465 IF P_DEBUG_MODE THEN
1466 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');
1467 END IF;
1468 l_cross_charge_code := 'N' ;
1469
1470 ELSE
1471
1472 IF P_DEBUG_MODE THEN
1473 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.150: Check if provider OU is same as receiver OU');
1474 END IF;
1475
1476 IF P_PrvdrOrgIdTab(j) = P_RecvrOrgIdTab(j) THEN
1477 --
1478 -- Section : Same provider OU and receiver OU.
1479 --
1480 -- If the provider OU is the same as the receiver OU then get the
1481 -- processing method from the implementation option of the provider
1482 -- OU.
1483 --
1484
1485 IF P_DEBUG_MODE THEN
1486 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');
1487 END IF;
1488
1489 l_cross_charge_code := P_CCProcessIOCodeTab(j) ;
1490
1491 ELSE
1492 -- If the provider OU is different from the receiver OU then check
1493 -- in the cross charge org relationships entity if there any record
1494 -- for that combination of provider ou and receiver OU. If there is
1495 -- any record then the cross charge code on that record is used.
1496
1497 IF P_DEBUG_MODE THEN
1498 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');
1499 END IF;
1500 OPEN GetCCOrgRel(P_PrvdrOrgIdTab(j),
1504 l_prvdr_project_id,
1501 P_RecvrOrgIdTab(j));
1502 FETCH GetCCOrgRel
1503 INTO l_cross_charge_code,
1505 l_vendor_site_id ;
1506
1507 IF GetCCOrgRel%FOUND THEN
1508 --
1509 -- Section : Check for entry in CC Org relationships entity.
1510 --
1511 -- If there is a record in cross charge org relationships entity
1512 -- then the value is assigned to l_cross_charge_code in the fetch.
1513 --
1514
1515 NULL ;
1516
1517 ELSE
1518 --
1519 -- If there is not record in cross charge org relationships entity
1520 -- then the value then check in the implementations option of the
1521 -- provider operating unit.
1522 --
1523 IF P_DEBUG_MODE THEN
1524 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.180: If there are no controls, then check cross charge type');
1525 END IF;
1526
1527 IF l_cross_charge_type = 'IU' THEN
1528 --
1529 -- Section : No entry in CC Org relationships entity.
1530 --
1531 -- If the cross charge type is 'IU' which means the legal entities
1532 -- of the provider and receiver OUs are the same then check the
1533 -- implementation option for the process iu code which is determined
1534 -- in the override orgs procedure and passed into this procedure.
1535 --
1536
1537 l_cross_charge_code := P_CCProcessIUCodeTab(j) ;
1538
1539 IF P_DEBUG_MODE THEN
1540 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');
1541 END IF;
1542
1543 ELSE
1544
1545 --
1546 -- This section indicates that cross charge type is 'IC' and there is
1547 -- no processing method defined in cross charge org relationships entity.
1548 -- So we cannot process cross charge for this item.
1549
1550 IF P_DEBUG_MODE THEN
1551 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');
1552 END IF;
1553 l_cross_charge_code := 'N' ;
1554
1555 END IF; -- End Section : No entry in CC Org relationships entity.
1556 END IF; -- End Section : Check for entry in CC Org relationships entity.
1557
1558 Close GetCCOrgRel ;
1559 END IF; -- End Section : Same provider OU and receiver OU.
1560
1561 END IF; -- Section : Not Null transaction source.
1562
1563 END IF ; -- Section : Check if the project is cross chargeable.
1564 --
1565
1566 END IF;
1567
1568 X_CrossChargeCodeTab(j) := l_cross_charge_code ;
1569 X_CrossChargeTypeTab(j) := l_cross_charge_type ;
1570 l_PrevCrossChargeType := l_cross_charge_type ;
1571 l_PrevRecvrOrgId := P_RecvrOrgIdTab(j) ;
1572 l_PrevPrvdrOrgId := P_PrvdrOrgIdTab(j) ; /*4154761*/
1573 l_PrevCrossChargeCode := l_cross_charge_code ;
1574 l_PrevProjectId := P_ProjectIdTab(j) ;
1575 /* l_PrevTransSource := P_TransSourceTab(j) ; Commented for bug# 3364107 */
1576 l_cc_tr_src_process_flag := NULL ; /*Bug# 3364107*/
1577
1578 IF P_DEBUG_MODE THEN
1579 print_message('l_cross_charge_code=' ||l_cross_charge_code);
1580 print_message('l_cross_charge_type=' ||l_cross_charge_type);
1581 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.210: Calling client extension');
1582 END IF;
1583 --
1584 -- Call client extension
1585 --
1586 PA_CC_IDENT_CLIENT_EXTN.OVERRIDE_CC_PROCESSING_METHOD (
1587 P_PrvdrOrganizationId => P_PrvdrOrganizationIdTab(j),
1588 P_RecvrOrganizationId => P_RecvrOrganizationIdTab(j),
1589 P_PrvdrOrgId => P_PrvdrOrgIdTab(j),
1590 P_RecvrOrgId => P_RecvrOrgIdTab(j),
1591 P_PrvdrLEId => P_PrvdrLEIdTab(j),
1592 P_RecvrLEId => P_RecvrLEIdTab(j),
1593 P_PersonId => P_PersonIdTab(j),
1594 P_ProjectId => P_ProjectIdTab(j),
1595 P_TaskId => P_TaskIdTab(j),
1596 P_SysLink => P_SysLinkTab(j),
1597 P_TransDate => P_ExpItemDateTab(j),
1598 P_TransSource => P_TransSourceTab(j),
1599 P_TransId => P_ExpItemIdTab(j),
1600 P_CrossChargeCode => X_CrossChargeCodeTab(j),
1601 P_CrossChargeType => X_CrossChargeTypeTab(j),
1602 P_calling_mode => l_calling_module,
1603 X_OvrridCrossChargeCode => l_OvrridCrossChargeCodeTab(j),
1604 X_Status => X_StatusTab(j),
1605 X_Error_Stage => X_Error_Stage,
1606 X_Error_Code => X_Error_Code ) ;
1607 --
1608 -- After Client extension
1609 --
1610
1611 IF P_DEBUG_MODE THEN
1615
1612 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.220: Validating the overriden CC code');
1613 END IF;
1614 IF l_OvrridCrossChargeCodeTab(j) = X_CrossChargeCodeTab(j) THEN
1616 NULL ;
1617 ELSE
1618 IF l_OvrridCrossChargeCodeTab(j) = 'I' THEN
1619
1620 IF P_DEBUG_MODE THEN
1621 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');
1622 END IF;
1623 IF X_CrossChargeTypeTab(j) = 'IO' THEN
1624
1625 IF P_DEBUG_MODE THEN
1626 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');
1627 END IF;
1628
1629 X_StatusTab(j) := 'PA_CC_CODE_TYPE_INVALID';
1630 X_CrossChargeCodeTab(j) := NULL;
1631 ELSE
1632 IF P_DEBUG_MODE THEN
1633 pa_cc_utils.log_message('PA_CC_GET_CROSS_CHARGE_TYPE: ' || '20.20.250: If CC type is IU the check for valid controls ');
1634 END IF;
1635 OPEN GetCCOrgRel(P_PrvdrOrgIdTab(j), P_RecvrOrgIdTab(j));
1636 FETCH GetCCOrgRel
1637 INTO l_cross_charge_code,
1638 l_prvdr_project_id,
1639 l_vendor_site_id ;
1640 IF GetCCOrgRel%NOTFOUND OR
1641 l_prvdr_project_id IS NULL OR
1642 l_vendor_site_id IS NULL THEN
1643
1644 X_StatusTab(j) := 'PA_CC_CODE_TYPE_INVALID';
1645 X_CrossChargeCodeTab(j) := NULL;
1646 ELSE
1647 X_CrossChargeCodeTab(j) := l_OvrridCrossChargeCodeTab(j);
1648
1649 END IF;
1650 close GetCCOrgRel ;
1651 END IF ;
1652 ELSIF l_OvrridCrossChargeCodeTab(j) = 'B' THEN
1653
1654 IF P_DEBUG_MODE THEN
1655 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');
1656 END IF;
1657 IF X_CrossChargeTypeTab(j) = 'IC' THEN
1658
1659 IF P_DEBUG_MODE THEN
1660 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');
1661 END IF;
1662 X_StatusTab(j) := 'PA_CC_CODE_TYPE_INVALID';
1663 X_CrossChargeCodeTab(j) := NULL;
1664 ELSE
1665
1666 X_CrossChargeCodeTab(j) := l_OvrridCrossChargeCodeTab(j);
1667
1668 END IF ;
1669 ELSE
1670 X_CrossChargeCodeTab(j) := l_OvrridCrossChargeCodeTab(j);
1671
1672 END IF ;
1673
1674 END IF;
1675 END IF;
1676
1677 IF P_DEBUG_MODE THEN
1678 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');
1679 END IF;
1680
1681 END IF; /* Bug 12955494 */
1682 -- CLOSE GetAdjExpid; /* Bug 12955494 */
1683 END LOOP ;
1684
1685 IF P_DEBUG_MODE THEN
1686 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');
1687 print_message('Exiting afetr checking client extn l_cross_charge_code=' ||l_cross_charge_code);
1688 print_message('l_cross_charge_type=' ||l_cross_charge_type);
1689 END IF;
1690
1691 pa_cc_utils.reset_curr_function;
1692
1693 EXCEPTION
1694 WHEN OTHERS THEN
1695 RAISE;
1696
1697 END PA_CC_GET_CROSS_CHARGE_TYPE;
1698
1699
1700 -- ==========================================================================
1701 -- = FUNCTION GetLegalEntity
1702 -- ==========================================================================
1703
1704 FUNCTION GetLegalEntity( p_org_id IN NUMBER )
1705 RETURN NUMBER
1706 IS
1707
1708 l_legal_entity_id VARCHAR2(150);
1709 BEGIN
1710
1711
1712
1713 /* R12 Legal entity changes - get from HR not PA Imp */
1714 SELECT org_information2
1715 INTO l_legal_entity_id
1716 FROM hr_organization_information
1717 WHERE organization_id = p_org_id
1718 AND org_information_context = 'Operating Unit Information';
1719
1720 RETURN (to_number(l_legal_entity_id));
1721
1722
1723 EXCEPTION
1724 WHEN NO_DATA_FOUND THEN
1725 RETURN (NULL);
1726 WHEN OTHERS THEN
1727 RAISE ;
1728
1729 END GetLegalEntity;
1730
1731 /* Funtion GET_ORG_LEGAL_ENTITY added for Bug 14657202: IC Enhancement*/
1732 -- ==========================================================================
1733 -- = FUNCTION Get_ORG_LEGAL_ENTITY
1734 -- = Functionality : This funciton will return legal entity id
1735 -- = Input parameter:
1736 -- = p_org_id : Operating unit
1737 -- = p_organization_id
1738 -- = Output parameter:
1739 -- = Legal entity id of data type NUMBER
1740 -- ==========================================================================
1741 FUNCTION GET_ORG_LEGAL_ENTITY( p_org_id NUMBER,
1742 p_organization_id PA_EXPENDITURES_ALL.INCURRED_BY_ORGANIZATION_ID%TYPE)
1743 RETURN NUMBER IS
1744
1748 SELECT h.default_legal_context_id
1745 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
1746
1747 BEGIN
1749 INTO l_legal_entity_id
1750 FROM hr_operating_units h,
1751 xle_entity_profiles xep,
1752 pa_implementations_all i
1753 WHERE i.org_id = p_org_id
1754 AND i.org_id = h.organization_id
1755 AND h.default_legal_context_id = xep.legal_entity_id;
1756
1757 RETURN l_legal_entity_id;
1758 END GET_ORG_LEGAL_ENTITY;
1759
1760 END PA_CC_IDENT;
[Home] [Help]