[Home] [Help]
PACKAGE BODY: APPS.JTM_CHECK_ACC_PKG
Source
1 PACKAGE BODY JTM_Check_Acc_PKG as
2 /* $Header: jtmchkab.pls 120.1 2005/08/24 02:07:53 saradhak noship $ */
3
4 FUNCTION check_profile_acc
5 (p_errtable IN OUT NOCOPY errTab) RETURN BOOLEAN
6 IS
7 l_query_string VARCHAR2(2000);
8
9 acctable_list accTab;
10 errtable_list errTab;
11
12 l_status BOOLEAN := TRUE;
13 l_counter NUMBER;
14 j NUMBER := 0;
15
16
17 ll_log_id NUMBER;
18 ll_status VARCHAR2(1);
19 ll_message VARCHAR2(2000);
20
21 BEGIN
22
23 acctable_list(1) := 'JTM_FND_PROF_OPTIONS_ACC';
24 acctable_list(2) := 'JTM_FND_PROF_OPTIONS_VAL_ACC';
25
26 for i IN 1 .. acctable_list.count loop
27
28 l_query_string := 'SELECT COUNT(*) FROM ' || acctable_list(i) ;
29
30 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
31 (v_package_name => NULL
32 ,v_procedure_name => NULL
33 ,v_con_query_id => NULL
34 ,v_query_stmt => l_query_string
35 ,v_start_time => sysdate
36 ,v_end_time => NULL
37 ,v_status => 'START'
38 ,v_message => 'IN PROCESS OF ' || acctable_list(i)
39 ,x_log_id => ll_log_id
40 ,x_status => ll_status
41 ,x_msg_data => ll_message);
42
43 EXECUTE IMMEDIATE l_query_string into l_counter;
44
45 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
46 (v_package_name => NULL
47 ,v_procedure_name => NULL
48 ,v_con_query_id => NULL
49 ,v_query_stmt => l_query_string
50 ,v_start_time => NULL
51 ,v_end_time => sysdate
52 ,v_status => 'COMPLETE'
53 ,v_message => 'FINISHED ' || acctable_list(i)
54 ,x_log_id => ll_log_id
55 ,x_status => ll_status
56 ,x_msg_data => ll_message);
57
58
59 if (l_counter = 0 ) then
60 l_status := FALSE;
61 errtable_list(j) := acctable_list(i);
62 j := j+1;
63 end if;
64
65 END LOOP;
66
67 p_errtable := errtable_list;
68
69 return l_status;
70
71 EXCEPTION WHEN OTHERS THEN
72 ROLLBACK;
73 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
74 (v_package_name => NULL
75 ,v_procedure_name => NULL
76 ,v_con_query_id => NULL
77 ,v_query_stmt => l_query_string
78 ,v_start_time => sysdate
79 ,v_end_time => NULL
80 ,v_status => 'FAILED'
81 ,v_message => sqlerrm
82 ,x_log_id => ll_log_id
83 ,x_status => ll_status
84 ,x_msg_data => ll_message);
85 RAISE FND_API.G_EXC_ERROR;
86
87 END check_profile_acc;
88
89 FUNCTION check_jtf_acc(p_errtable IN OUT NOCOPY errTab) RETURN BOOLEAN
90 AS
91 l_query_string VARCHAR2(2000);
92
93 acctable_list accTab;
94 errtable_list errTab;
95
96 l_status BOOLEAN := TRUE;
97 l_counter NUMBER;
98 j NUMBER := 0;
99
100
101 ll_log_id NUMBER;
102 ll_status VARCHAR2(1);
103 ll_message VARCHAR2(2000);
104
105 BEGIN
106
107 acctable_list(1) := 'JTM_JTF_TASK_PRIORITIES_ACC';
108 acctable_list(2) := 'JTM_JTF_TASK_STATUSES_ACC';
109 acctable_list(3) := 'JTM_JTF_TASK_TYPES_ACC';
110
111 for i IN 1 .. acctable_list.count loop
112
113 l_query_string := 'SELECT COUNT(*) FROM ' || acctable_list(i) ;
114
115 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
116 (v_package_name => NULL
117 ,v_procedure_name => NULL
118 ,v_con_query_id => NULL
119 ,v_query_stmt => l_query_string
120 ,v_start_time => sysdate
121 ,v_end_time => NULL
122 ,v_status => 'START'
123 ,v_message => 'IN PROCESS OF ' || acctable_list(i)
124 ,x_log_id => ll_log_id
125 ,x_status => ll_status
126 ,x_msg_data => ll_message);
127
128 EXECUTE IMMEDIATE l_query_string into l_counter;
129
130 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
131 (v_package_name => NULL
132 ,v_procedure_name => NULL
133 ,v_con_query_id => NULL
134 ,v_query_stmt => l_query_string
135 ,v_start_time => NULL
136 ,v_end_time => sysdate
137 ,v_status => 'COMPLETE'
138 ,v_message => 'FINISHED ' || acctable_list(i)
139 ,x_log_id => ll_log_id
140 ,x_status => ll_status
141 ,x_msg_data => ll_message);
142
143 if (l_counter = 0 ) then
144 l_status := FALSE;
145 errtable_list(j) := acctable_list(i);
146 j := j+1;
147 end if;
148
149 END LOOP;
150
151 p_errtable := errtable_list;
152
153 return l_status;
154
155 EXCEPTION WHEN OTHERS THEN
156 ROLLBACK;
157 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
158 (v_package_name => NULL
159 ,v_procedure_name => NULL
160 ,v_con_query_id => NULL
161 ,v_query_stmt => l_query_string
162 ,v_start_time => sysdate
163 ,v_end_time => NULL
164 ,v_status => 'FAILED'
165 ,v_message => sqlerrm
166 ,x_log_id => ll_log_id
167 ,x_status => ll_status
168 ,x_msg_data => ll_message);
169 RAISE FND_API.G_EXC_ERROR;
170
171 END check_jtf_acc;
172
173 end JTM_Check_Acc_PKG;