eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_UC_RELEASE_TRANS_PKG

Source


1 PACKAGE BODY igs_uc_release_trans_pkg AS
2 /* $Header: IGSUC69B.pls 120.2 2006/05/04 20:59:02 jchakrab noship $*/
3 
4   l_debug_level NUMBER:= fnd_log.g_current_runtime_level;
5 
6   PROCEDURE release_transactions (
7      errbuf                        OUT NOCOPY VARCHAR2,
8      retcode                       OUT NOCOPY NUMBER,
9      p_org_unit_code               IN  VARCHAR2,
10      p_ucas_system_code            IN  VARCHAR2,
11      p_ucas_program_code           IN  VARCHAR2,
12      p_ucas_campus                 IN  VARCHAR2,
13      p_ucas_entry_point            IN  NUMBER,
14      p_ucas_entry_month            IN  NUMBER,
15      p_ucas_entry_year             IN  NUMBER,
16      p_ucas_trans_type             IN  VARCHAR2,
17      p_ucas_decision_code          IN  VARCHAR2,
18      p_trans_creation_dt_from      IN  VARCHAR2,
19      p_trans_creation_dt_to        IN  VARCHAR2,
20      p_trans_transmit_dt_from      IN  VARCHAR2,
21      p_trans_transmit_dt_to        IN  VARCHAR2
22     ) IS
23 
24   /*------------------------------------------------------------------
25   --Created by  : jchakrab, Oracle Corporation
26   --Date created: 24-Jun-2005
27   --
28   --Purpose: Releases UCAS transactions in bulk.The parameters entered by the user
29   --         when the concurrent process is invoked are used to determine the criteria
30   --         for releasing transactions.
31   --Known limitations/enhancements and/or remarks:
32   --
33   --Change History:
34   WHO       WHEN          WHAT
35   anwest    18-JAN-2006   Bug# 4950285 R12 Disable OSS Mandate
36   jchakrab  04-May-2006   Bug 5203018 - closed cursor created using DBMS_SQL
37   -----------------------------------------------------------------------*/
38 
39   --variables for WHO columns
40   l_last_update_date           DATE ;
41   l_last_updated_by            NUMBER;
42   l_last_update_login          NUMBER;
43 
44   l_cursor_id                  NUMBER;
45   l_update_stmt                VARCHAR2(2500);
46   l_updated_rows               NUMBER;
47 
48   BEGIN
49 
50     --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
51     IGS_GE_GEN_003.SET_ORG_ID;
52 
53     fnd_file.put_line(fnd_file.log, '-------------------------------------------------------');
54     fnd_file.put_line(fnd_file.log, 'P_ORG_UNIT_CODE              = ' || p_org_unit_code);
55     fnd_file.put_line(fnd_file.log, 'P_UCAS_SYSTEM_CODE           = ' || p_ucas_system_code);
56     fnd_file.put_line(fnd_file.log, 'P_UCAS_PROGRAM_CODE          = ' || p_ucas_program_code);
57     fnd_file.put_line(fnd_file.log, 'P_UCAS_CAMPUS                = ' || p_ucas_campus);
58     fnd_file.put_line(fnd_file.log, 'P_UCAS_ENTRY_POINT           = ' || p_ucas_entry_point);
59     fnd_file.put_line(fnd_file.log, 'P_UCAS_ENTRY_MONTH           = ' || p_ucas_entry_month);
60     fnd_file.put_line(fnd_file.log, 'P_UCAS_ENTRY_YEAR            = ' || p_ucas_entry_year);
61     fnd_file.put_line(fnd_file.log, 'P_UCAS_TRANS_TYPE            = ' || p_ucas_trans_type);
62     fnd_file.put_line(fnd_file.log, 'P_UCAS_DECISION_CODE         = ' || p_ucas_decision_code);
63     fnd_file.put_line(fnd_file.log, 'P_TRANS_CREATION_DT_FROM     = ' || p_trans_creation_dt_from);
64     fnd_file.put_line(fnd_file.log, 'P_TRANS_CREATION_DT_TO       = ' || p_trans_creation_dt_to);
65     fnd_file.put_line(fnd_file.log, 'P_TRANS_TRANSMIT_DT_FROM     = ' || p_trans_transmit_dt_from);
66     fnd_file.put_line(fnd_file.log, 'P_TRANS_TRANSMIT_DT_TO       = ' || p_trans_transmit_dt_to);
67     fnd_file.put_line(fnd_file.log, '-------------------------------------------------------');
68 
69 
70     /* Checking whether the UK profile is enabled */
71     IF NOT (igs_uc_utils.is_ucas_hesa_enabled) THEN
72 
73         fnd_message.set_name('IGS','IGS_UC_HE_NOT_ENABLED');
74         fnd_file.put_line(fnd_file.log, fnd_message.get());  -- display to user also
75         -- also log using the fnd logging framework
76         IF (fnd_log.level_statement >= l_debug_level ) THEN
77             fnd_log.string( fnd_log.level_statement, 'igs.plsql.ucas.release_transactions.validation', fnd_message.get());
78         END IF;
79         errbuf  := fnd_message.get_string ('IGS', 'IGS_UC_HE_NOT_ENABLED');
80         retcode := 3 ;
81         RETURN ;
82 
83     END IF;
84 
85     --set values for WHO columns
86     l_last_update_date := SYSDATE;
87     l_last_updated_by := fnd_global.user_id;
88     IF l_last_updated_by IS NULL THEN
89         l_last_updated_by := -1;
90     END IF;
91     l_last_update_login := fnd_global.login_id;
92     IF l_last_update_login IS NULL THEN
93         l_last_update_login := -1;
94     END IF;
95 
96     --========================================================================
97     --                  START WHERE CLAUSE CONSTRUCTION
98     --========================================================================
99     --initialize fnd_dsql data-structures
100     fnd_dsql.init;
101 
102     --set the base UPDATE statement
103     fnd_dsql.add_text(' UPDATE IGS_UC_TRANSACTIONS UCTRANS SET UCTRANS.HOLD_FLAG = ''N'' ');
104     fnd_dsql.add_text(' ,UCTRANS.LAST_UPDATE_DATE = ');
105     fnd_dsql.add_bind(l_last_update_date);
106     fnd_dsql.add_text(' ,UCTRANS.LAST_UPDATED_BY = ');
107     fnd_dsql.add_bind(l_last_updated_by);
108     fnd_dsql.add_text(' ,UCTRANS.LAST_UPDATE_LOGIN = ');
109     fnd_dsql.add_bind(l_last_update_login);
110 
111     --system code is a required parameter and only need to release transactions which
112     --are currently on hold
113     fnd_dsql.add_text(' WHERE HOLD_FLAG = ''Y'' AND UCTRANS.SYSTEM_CODE = ');
114     fnd_dsql.add_bind(p_ucas_system_code);
115 
116     -- org_code
117     IF p_org_unit_code IS NOT NULL THEN
118         fnd_dsql.add_text(' AND EXISTS (SELECT 1 FROM IGS_PS_VER PSVER, IGS_UC_APP_CHOICES APPCH WHERE APPCH.APP_NO = UCTRANS.APP_NO AND ');
119         fnd_dsql.add_text(' UCTRANS.CHOICE_NO = APPCH.CHOICE_NO AND UCTRANS.UCAS_CYCLE = APPCH.UCAS_CYCLE AND PSVER.COURSE_CD = APPCH.OSS_PROGRAM_CODE AND ');
120         fnd_dsql.add_text(' PSVER.VERSION_NUMBER = APPCH.OSS_PROGRAM_VERSION AND PSVER.RESPONSIBLE_ORG_UNIT_CD = ');
121         fnd_dsql.add_bind(p_org_unit_code);
122         fnd_dsql.add_text(' )');
123     END IF;
124 
125     -- Program search
126     IF p_ucas_program_code IS NOT NULL THEN
127         fnd_dsql.add_text(' AND EXISTS (SELECT 1 FROM IGS_UC_APP_CHOICES APPCH WHERE UCTRANS.APP_NO = APPCH.APP_NO AND  UCTRANS.CHOICE_NO = APPCH.CHOICE_NO AND ');
128         fnd_dsql.add_text(' UCTRANS.UCAS_CYCLE = APPCH.UCAS_CYCLE AND APPCH.UCAS_PROGRAM_CODE = ');
129         fnd_dsql.add_bind(p_ucas_program_code);
130         fnd_dsql.add_text(' )');
131     END IF;
132 
133     -- Campus search
134     IF p_ucas_campus IS NOT NULL THEN
135         fnd_dsql.add_text(' AND EXISTS (SELECT 1 FROM IGS_UC_APP_CHOICES APPCH WHERE UCTRANS.APP_NO = APPCH.APP_NO AND  UCTRANS.CHOICE_NO = APPCH.CHOICE_NO AND ');
136         fnd_dsql.add_text(' UCTRANS.UCAS_CYCLE = APPCH.UCAS_CYCLE AND APPCH.CAMPUS = ');
137         fnd_dsql.add_bind(p_ucas_campus);
138         fnd_dsql.add_text(' )');
139     END IF;
140 
141     -- Entry Point search
142     IF p_ucas_entry_point IS NOT NULL THEN
143         fnd_dsql.add_text(' AND EXISTS (SELECT 1 FROM IGS_UC_APP_CHOICES APPCH WHERE UCTRANS.APP_NO = APPCH.APP_NO AND  UCTRANS.CHOICE_NO = APPCH.CHOICE_NO AND ');
144         fnd_dsql.add_text(' UCTRANS.UCAS_CYCLE = APPCH.UCAS_CYCLE AND APPCH.POINT_OF_ENTRY = ');
145         fnd_dsql.add_bind(p_ucas_entry_point);
146         fnd_dsql.add_text(' )');
147     END IF;
148 
149     -- Entry month search
150     IF p_ucas_entry_month IS NOT NULL THEN
151         fnd_dsql.add_text(' AND EXISTS (SELECT 1 FROM IGS_UC_APP_CHOICES APPCH WHERE UCTRANS.APP_NO = APPCH.APP_NO AND  UCTRANS.CHOICE_NO = APPCH.CHOICE_NO AND ');
152         fnd_dsql.add_text(' UCTRANS.UCAS_CYCLE = APPCH.UCAS_CYCLE AND APPCH.ENTRY_MONTH = ');
153         fnd_dsql.add_bind(p_ucas_entry_month);
154         fnd_dsql.add_text(' )');
155     END IF;
156 
157     -- Entry Year search
158     IF p_ucas_entry_year IS NOT NULL THEN
159         fnd_dsql.add_text(' AND EXISTS (SELECT 1 FROM IGS_UC_APP_CHOICES APPCH WHERE UCTRANS.APP_NO = APPCH.APP_NO AND  UCTRANS.CHOICE_NO = APPCH.CHOICE_NO AND ');
160         fnd_dsql.add_text(' UCTRANS.UCAS_CYCLE = APPCH.UCAS_CYCLE AND APPCH.ENTRY_YEAR = ');
161         fnd_dsql.add_bind(p_ucas_entry_year);
162         fnd_dsql.add_text(' )');
163     END IF;
164 
165 
166 
167     -- Transaction type
168     IF p_ucas_trans_type IS NOT NULL THEN
169         fnd_dsql.add_text(' AND UCTRANS.TRANSACTION_TYPE = ');
170         fnd_dsql.add_bind(p_ucas_trans_type);
171     END IF;
172 
173     -- Decision
174     IF p_ucas_decision_code IS NOT NULL THEN
175         fnd_dsql.add_text(' AND UCTRANS.DECISION = ');
176         fnd_dsql.add_bind(p_ucas_decision_code);
177     END IF;
178 
179     -- Creation dates search
180     IF p_trans_creation_dt_from IS NOT NULL THEN
181         fnd_dsql.add_text(' AND UCTRANS.CREATION_DATE >= ');
182         fnd_dsql.add_bind(IGS_GE_DATE.igsdate(p_trans_creation_dt_from));
183     END IF;
184 
185     IF p_trans_creation_dt_to IS NOT NULL THEN
186         fnd_dsql.add_text(' AND UCTRANS.CREATION_DATE <= ');
187         fnd_dsql.add_bind(IGS_GE_DATE.igsdate(p_trans_creation_dt_to));
188     END IF;
189 
190     -- Transmission dates search
191     IF p_trans_transmit_dt_from IS NOT NULL THEN
192         fnd_dsql.add_text(' AND UCTRANS.DATETIMESTAMP >= ');
193         fnd_dsql.add_bind(IGS_GE_DATE.igsdate(p_trans_transmit_dt_from));
194     END IF;
195 
196     IF p_trans_transmit_dt_to IS NOT NULL THEN
197         fnd_dsql.add_text(' AND UCTRANS.DATETIMESTAMP <= ');
198         fnd_dsql.add_bind(IGS_GE_DATE.igsdate(p_trans_transmit_dt_to));
199     END IF;
200 
201 
202     l_update_stmt := fnd_dsql.get_text(FALSE);
203 
204     -- log the UPDATE DML statement using the fnd logging framework
205     IF (fnd_log.level_statement >= l_debug_level ) THEN
206         fnd_log.string( fnd_log.level_statement, 'igs.plsql.ucas.release_transactions.update_dml', l_update_stmt);
207     END IF;
208 
209     l_cursor_id := DBMS_SQL.OPEN_CURSOR;
210     fnd_dsql.set_cursor(l_cursor_id);
211 
212     DBMS_SQL.parse(l_cursor_id, l_update_stmt, dbms_sql.native);
213     fnd_dsql.do_binds;
214 
215     l_updated_rows := dbms_sql.EXECUTE(l_cursor_id);
216 
217     DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
218 
219     COMMIT;
220 
221     IF (l_updated_rows > 0) THEN
222         --show the number of records successfully processed
223         fnd_message.set_name('IGS','IGS_UC_REC_CNT_SUCCESS_PROC');
224         fnd_message.set_token('REC_CNT',l_updated_rows);
225         fnd_file.put_line(fnd_file.log, fnd_message.get());
226     ELSE
227         --report that there were no records processed
228         fnd_message.set_name('IGS','IGS_UC_REC_CNT_PROC');
229         fnd_message.set_token('REC_CNT',l_updated_rows);
230         fnd_file.put_line(fnd_file.log, fnd_message.get());
231     END IF;
232 
233   EXCEPTION
234     WHEN OTHERS THEN
235 
236       IF l_cursor_id IS NOT NULL THEN
237           DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
238       END IF;
239 
240       ROLLBACK;
241 
242       fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
243       fnd_message.set_token('NAME','igs_uc_release_trans_on_hold_pkg.release_transactions - '||SQLERRM);
244       fnd_file.put_line(fnd_file.log,fnd_message.get);
245 
246       IF ( fnd_log.level_unexpected >= l_debug_level ) THEN
247           fnd_log.message(fnd_log.level_unexpected, 'igs.plsql.ucas.release_transactions.exception', FALSE);
248       END IF;
249 
250       fnd_message.retrieve (errbuf);
251       retcode := 2 ;
252       igs_ge_msg_stack.conc_exception_hndl;
253 
254   END release_transactions;
255 
256 END igs_uc_release_trans_pkg;