[Home] [Help]
PACKAGE BODY: APPS.JTM_COMMON_CON_PROG_PUB
Source
1 PACKAGE BODY JTM_COMMON_CON_PROG_PUB AS
2 /* $Header: jtmpconb.pls 115.10 2002/12/06 23:38:57 pwu ship $ */
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- JTM_COMMON_CON_PROG_PUB
8 --
9 -- PURPOSE
10 -- Central Entry for Mobile Concurrent Programs.
11 --
12 -- PROCEDURES:
13 --
14 --
15 -- NOTES
16 --
17 --
18 -- HISTORY
19 -- 04-09-2002 YOHUANG Created.
20 -- 07-29-2002 PWU
21 -- Make change to catch "run away" interval program.
22 -- And add log message.
23 -- 09-21 DLIAO
24 -- populate data in txc_start, txc_end, completion_context
25 -- Notes
26 -- Do_Refresh_Mobile_Data will call each procedure according to EXECUTE_FLAG, EXECUTION_ORDER,
27 -- and FREQUENCY. Do_Refresh_Mobile_Data itself it runs every hour.
28 -- Each Procedure must make use of SAVEPOINT, because, at the end, Concurrent Manager will commit everything.
29
30 -- End of Comments
31 --
32 --
33 --
34
35 PROCEDURE Do_Refresh_Mobile_Data
36 (
37 errbuf OUT NOCOPY VARCHAR2,
38 retcode OUT NOCOPY NUMBER
39 ) IS
40
41 -- Reason to Use two cursor is for EXECUTION_ORDER.
42 CURSOR X_INTERVAL IS
43 SELECT INTERVAL_ID FROM JTM_CON_REQUEST_INTERVALS
44 WHERE INTERVAL <= CURRENT_ROUND;
45
46 CURSOR X_TO_BE_RUN_REQUEST(x_interval_id NUMBER, x_interval NUMBER) IS
47 SELECT PACKAGE_NAME, PROCEDURE_NAME
48 FROM JTM_CON_REQUEST_DATA req
49 WHERE (req.INTERVAL_ID = x_interval_id
50 AND req.EXECUTE_FLAG = 'Y')
51 OR
52 (req.INTERVAL_ID = x_interval_id
53 AND SYSDATE - req.LAST_RUN_DATE > x_interval/1440)
54 ORDER BY PRODUCT_CODE, EXECUTION_ORDER;
55
56
57 dynamic_stmt VARCHAR2(2000);
58 l_api_start DATE;
59 l_api_end DATE;
60 l_api_status varchar2(30);
61
62 x_return_status VARCHAR2(1);
63 -- For each procedure, it should init the FND_MESSAGE Stack.
64
65 p_api_version_number NUMBER := 1.0;
66 p_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
67 p_validation_level NUMBER :=FND_API.G_VALID_LEVEL_FULL;
68
69 x_msg_data VARCHAR2(2000);
70 x_msg_index_out NUMBER;
71 x_msg_count NUMBER;
72
73 l_interval_id NUMBER;
74 l_interval NUMBER := 0;
75
76 BEGIN
77
78 -- Update JTM_CON_REQUEST_FREQUENCY , BOUNCE CURRENT_ROUND with 1
79 -- And Lock the whole JTM_CON_REQUEST Table.
80 -- When Apply patch to add new FREQUENCY, it shouldn't do any UPDATE.
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 -- Initalize the Apps Context
84
85 UPDATE JTM_CON_REQUEST_INTERVALS
86 SET CURRENT_ROUND = CURRENT_ROUND + 1 ;
87
88 -- Careful, For Dynamic SQL, VARCHAR2 has to be initialized first.
89 -- remove parameters from dynamic sql.
90 FOR r_interval IN X_INTERVAL LOOP
91 l_interval_id := r_interval.INTERVAL_ID;
92 SELECT INTERVAL INTO l_interval FROM jtm_con_request_intervals
93 where INTERVAL_ID = r_interval.INTERVAL_ID;
94 FOR x_request IN X_TO_BE_RUN_REQUEST(r_interval.INTERVAL_ID, l_interval) LOOP
95
96 l_api_start := sysdate;
97 dynamic_stmt := 'BEGIN ' || x_request.PACKAGE_NAME || '.' ||
98 x_request.PROCEDURE_NAME || ' ; END; ';
99
100 JTM_MESSAGE_LOG_PKG.log_msg(
101 v_object_id => 'Do_Refresh_Mobile_Data',
102 v_object_name => 'JTM_COMMON_CON_PROG_PUB',
103 v_message => 'Execute ' || dynamic_stmt,
104 v_level_id => JTM_HOOK_UTIL_PKG.g_debug_level_sql,
105 v_module => 'JTM_COMMON_CON_PROG_PUB');
106
107
108 BEGIN
109 EXECUTE IMMEDIATE dynamic_stmt;
110
111 l_api_end := sysdate;
112 l_api_status := 'COMPLETED';
113
114 UPDATE JTM_CON_REQUEST_INTERVALS
115 SET CURRENT_ROUND = 0
116 WHERE INTERVAL_ID = r_interval.INTERVAL_ID;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 JTM_MESSAGE_LOG_PKG.log_msg(
121 v_object_id => x_request.PROCEDURE_NAME,
122 v_object_name => x_request.PACKAGE_NAME,
123 v_message => 'Error ocurrs on exection',
124 v_level_id => JTM_HOOK_UTIL_PKG.g_debug_level_error,
125 v_module => 'JTM_COMMON_CON_PROG_PUB');
126
127 l_api_end := sysdate;
128 l_api_status := 'FAILED';
129
130
131 END;
132
133 UPDATE JTM_CON_REQUEST_DATA
134 SET LAST_TXC_START = l_api_start,
135 LAST_TXC_END = l_api_end,
136 COMPLETION_TEXT = l_api_status
137 WHERE PACKAGE_NAME = x_request.PACKAGE_NAME
138 AND PROCEDURE_NAME = x_request.PROCEDURE_NAME;
139
140 COMMIT;
141 END LOOP;
142 END LOOP;
143
144 UPDATE JTM_CON_REQUEST_INTERVALS
145 SET CURRENT_ROUND = 0
146 WHERE CURRENT_ROUND >= INTERVAL;
147 Commit;
148
149
150 retcode := 0;
151 Exception
152 When others then
153
154 UPDATE JTM_CON_REQUEST_INTERVALS
155 SET CURRENT_ROUND = 0
156 WHERE CURRENT_ROUND >= INTERVAL;
157
158 JTM_MESSAGE_LOG_PKG.log_msg(
159 v_object_id => 'Do_Refresh_Mobile_Data',
160 v_object_name => 'JTM_COMMON_CON_PROG_PUB',
161 v_message => 'Exection occurs.',
162 v_level_id => JTM_HOOK_UTIL_PKG.g_debug_level_error,
163 v_module => 'JTM_COMMON_CON_PROG_PUB');
164
165
166
167 END Do_Refresh_Mobile_Data;
168
169 END JTM_COMMON_CON_PROG_PUB;