1 Package Body PA_GL_TIEBACK_PKG As
2 /* $Header: PAGLTIEB.pls 115.3 2002/04/15 21:12:10 pkm ship $*/
3
4 Procedure PA_GL_TIEBACK (
5 P_Module In Varchar2,
6 X_COUNT Out Number ,
7 X_ERROR Out VARCHAR2
8 )
9
10 Is
11 L_Interface_Process Varchar2(30) := Null;
12 L_Count Number := 0;
13 L_Interface_Is_Running varchar2(1):= 'N';
14
15 CURSOR interface IS
16 SELECT 'Y'
17 FROM fnd_concurrent_requests req,
18 fnd_concurrent_programs prog,
19 fnd_executables exe
20 WHERE req.program_application_id = prog.application_id
21 AND req.concurrent_program_id = prog.concurrent_program_id
22 AND req.phase_code = 'R'
23 AND prog.executable_application_id = exe.application_id
24 AND prog.executable_id = exe.executable_id
25 AND exe.executable_name = L_Interface_Process;
26
27
28 Cursor Corruption_Labor is
29 SELECT gi.RowId Row_Id from Pa_Gl_Interface gi
30 WHERE Status = 'NEW'
31 AND not exists (Select 0 from gl_Interface_control gc where gc.group_id = gi.group_id)
32 AND user_je_category_name in ( select user_je_category_name
33 from gl_je_Categories where je_category_name='Labor Cost');
34
35 Cursor Corruption_Usage is
36 SELECT gi.RowId Row_Id from Pa_Gl_Interface gi
37 WHERE Status = 'NEW'
38 AND not exists (Select 0 from gl_Interface_control gc where gc.group_id = gi.group_id)
39 AND user_je_category_name in ( select user_je_category_name
40 from gl_je_Categories where je_category_name
41 in ('Budget','Usage Cost'));
42
43
44 Cursor Corruption_Burden is
45 SELECT gi.RowId Row_Id from Pa_Gl_Interface gi
46 WHERE Status = 'NEW'
47 AND not exists (Select 0 from gl_Interface_control gc where gc.group_id = gi.group_id)
48 AND user_je_category_name in ( select user_je_category_name
49 from gl_je_Categories where je_category_name='Total Burdened Cost');
50
51
52 Cursor Corruption_Revenue is
53 SELECT gi.RowId Row_Id from Pa_Gl_Interface gi
54 WHERE Status = 'NEW'
55 AND not exists (Select 0 from gl_Interface_control gc where gc.group_id = gi.group_id)
56 AND user_je_category_name in ( select user_je_category_name
57 from gl_je_Categories where je_category_name='Revenue');
58
59
60
61 Cursor Corruption_CrossCharge is
62 SELECT gi.RowId Row_Id from Pa_Gl_Interface gi
63 WHERE Status = 'NEW'
64 AND not exists (Select 0 from gl_Interface_control gc where gc.group_id = gi.group_id)
65 AND user_je_category_name in ( select user_je_category_name
66 from gl_je_Categories where je_category_name
67 in (Select gjc.je_category_name
68 FROM gl_je_categories gjc,
69 pa_lookups pl
70 WHERE pl.meaning = gjc.JE_CATEGORY_NAME
71 and pl.lookup_type = 'CC_CCD_LINE_TYPE_JE_CATEGORY'));
72
73
74
75 Begin
76
77 /* Identifying whether any relevant Interface process is running If not the corrupted records
78 will be marked as rejected. The cprrupted records means where Status is NEW and No record
79 exists in gl_interface control table for relevant group id */
80
81 If P_Module = 'LABOR' Then
82 L_Interface_Process := 'PAGGLT';
83 Elsif P_Module = 'USAGE' Then
84 L_Interface_Process := 'PASGLT';
85 Elsif P_Module = 'REVENUE' Then
86 L_Interface_Process := 'PATTGL';
87 Elsif P_Module = 'BURDEN' Then
88 L_Interface_Process := 'PACTFTBC';
89 Elsif P_Module = 'BORRLENT' Then
90 L_Interface_Process := 'PACCGLTR';
91 End If;
92
93 OPEN interface; /* Check if Interface is running */
94 FETCH Interface INTO L_Interface_Is_Running;
95 CLOSE Interface;
96
97 IF ( nvl(L_Interface_Is_Running,'N') = 'N' ) THEN /* If No Process is running update
98 corrupted data as rejected */
99
100 IF P_Module = 'LABOR' Then
101 For Rec In Corruption_labor Loop
102
103 Update PA_GL_Interface
104 Set Status = 'PREV_GL_INTERFACE_UNSUCCESSFUL'
105 Where RowId = Rec.Row_Id;
106
107 L_Count := L_Count + Sql%rowcount;
108
109 End Loop;
110 END IF; /* Calling Modulr LABOR */
111
112 IF P_Module = 'USAGE' Then
113 For Rec In Corruption_usage Loop
114
115 Update PA_GL_Interface
116 Set Status = 'PREV_GL_INTERFACE_UNSUCCESSFUL'
117 Where RowId = Rec.Row_Id;
118
119 L_Count := L_Count + Sql%rowcount;
120
121 End Loop;
122 END IF; /* Calling Module USAGE */
123
124 IF P_Module = 'BURDEN' Then
125 For Rec In Corruption_burden Loop
126
127 Update PA_GL_Interface
128 Set Status = 'PREV_GL_INTERFACE_UNSUCCESSFUL'
129 Where RowId = Rec.Row_Id;
130
131 L_Count := L_Count + Sql%rowcount;
132
133 End Loop;
134 END IF; /* Calling Modulr BURDEN */
135
136 IF P_Module = 'REVENUE' Then
137 For Rec In Corruption_revenue Loop
138
139 Update PA_GL_Interface
140 Set Status = 'PREV_GL_INTERFACE_UNSUCCESSFUL'
141 Where RowId = Rec.Row_Id;
142
143 L_Count := L_Count + Sql%rowcount;
144
145 End Loop;
146 END IF; /* Calling Modulr REVENUE */
147
148 IF P_Module = 'BORRLENT' Then
149 For Rec In Corruption_CrossCharge Loop
150
151 Update PA_GL_Interface
152 Set Status = 'PREV_GL_INTERFACE_UNSUCCESSFUL'
153 Where RowId = Rec.Row_Id;
154
155 L_Count := L_Count + Sql%rowcount;
156
157 End Loop;
158 END IF; /* Calling Modulr BORRLENT */
159
160 END IF; /* End Process is not running */
161
162 X_COUNT := L_Count;
163
164 Exception
165 When Others Then
166 X_COUNT := L_Count;
167 X_ERROR := SQLCODE;
168 End PA_GL_TIEBACK;
169 End PA_GL_TIEBACK_PKG;
[Home] [Help]