[Home] [Help]
PACKAGE BODY: APPS.CE_PURGE_TRX_CODES_XML
Source
1 PACKAGE BODY CE_PURGE_TRX_CODES_XML AS
2 /* $Header: ceputxcb.pls 120.1 2009/09/15 23:37:14 vnetan noship $ */
3
4 /*========================================================================+
5 | PRIVATE PROCEDURE |
6 | debug_log |
7 | |
8 | DESCRIPTION |
9 | Procedure to print debug messages to logs. |
10 +========================================================================*/
11 PROCEDURE debug_log( p_message IN VARCHAR2 ) IS
12 BEGIN
13 cep_standard.debug(p_message);
14 --vn_debug_proc(p_message);
15 END debug_log;
16
17 /*========================================================================+
18 | PRIVATE PROCEDURE |
19 | debug_log |
20 | |
21 | DESCRIPTION |
22 | Procedure to initialize global variables. |
23 +========================================================================*/
24 PROCEDURE init_variables IS
25 BEGIN
26 debug_log('>>CE_PURGE_TRX_CODES_XML.init_variables');
27 --bank
28 IF p_bank_id IS NOT NULL
29 THEN
30 SELECT SUBSTR(BNK.party_name,0,50) INTO g_bank_name
31 FROM hz_parties BNK WHERE BNK.party_id = p_bank_id;
32 ELSE
33 g_bank_name := NULL;
34 END IF;
35 debug_log('g_bank_name::'||g_bank_name);
36
37 --bank branch
38 IF p_bank_branch_id IS NOT NULL
39 THEN
40 SELECT SUBSTR(BRN.party_name,0,50) INTO g_branch_name
41 FROM hz_parties BRN WHERE BRN.party_id = p_bank_branch_id;
42 ELSE
43 g_branch_name := NULL;
44 END IF;
45 debug_log('g_bank_branch_name::'||g_branch_name);
46
47 --bank account
48 IF p_bank_acct_id IS NOT NULL
49 THEN
50 SELECT CBA.bank_account_num INTO g_account_num
51 FROM ce_bank_accounts CBA
52 WHERE CBA.bank_account_id = p_bank_acct_id;
53 ELSE
54 g_account_num := NULL;
55 END IF;
56 debug_log('g_account_num::'||g_account_num);
57
58 --bank account type
59 IF p_acct_type IS NOT NULL
60 THEN
61 SELECT substr(LKP.meaning,0,50) INTO g_account_type
62 FROM ce_lookups LKP
63 WHERE LKP.lookup_type = 'BANK_ACCOUNT_TYPE'
64 AND LKP.lookup_code = p_acct_type;
65 ElSE
66 g_account_type := NULL;
67 END IF;
68 debug_log('g_account_type::'||g_account_type);
69
70 --Process option
71 IF p_process_option IS NOT NULL
72 THEN
73 SELECT substr(LKP.meaning,0,50) INTO g_process_option
74 FROM ce_lookups LKP
75 WHERE LKP.lookup_type = 'PURGE_TXCD_PROCESS_OPTION'
76 AND LKP.lookup_code = p_process_option;
77 ELSE
78 g_process_option := NULL;
79 END IF;
80
81 debug_log('<<CE_PURGE_TRX_CODES_XML.init_variables');
82 END init_variables;
83
84 /*========================================================================+
85 | PRIVATE PROCEDURE |
86 | purge_trx_codes |
87 | |
88 | DESCRIPTION |
89 | This procedures deletes the bank transaction codes setup for |
90 | a set of bank accounts. |
91 +========================================================================*/
92 PROCEDURE purge_trx_codes
93 IS
94 l_conc_request_id NUMBER(15);
95 BEGIN
96 debug_log('>>CE_PURGE_TRX_CODES_XML.purge_trx_codes');
97
98 --parameter values
99 debug_log('p_process_option::'||p_process_option);
100 debug_log('p_request_id::'||p_request_id);
101 debug_log('p_bank_acct_id::'||p_bank_acct_id);
102 debug_log('p_bank_branch_id::'||p_bank_branch_id);
103 debug_log('p_bank_id::'||p_bank_id);
104 debug_log('p_acct_type::'||p_acct_type);
105
106 l_conc_request_id := FND_GLOBAL.conc_request_id;
107 --fetch transaction codes which can be deleted and insert into tmp table
108 INSERT INTO ce_trx_codes_util_gt (
109 status,
110 request_id,
111 trx_code_id,
112 trx_code,
113 bank_account_id,
114 description,
115 trx_type
116 )
117 SELECT 'DELETED',
118 l_conc_request_id,
119 CTC.transaction_code_id,
120 CTC.trx_code,
121 CTC.bank_account_id,
122 CTC.description,
123 CTC.trx_type
124 FROM CE_TRANSACTION_CODES CTC, CE_BANK_ACCOUNTS_V CBA
125 WHERE -- fetch codes as per input parameters
126 DECODE(p_process_option, 'COPIED', CTC.request_id, 'ALL', 1) =
127 DECODE(p_process_option, 'COPIED', NVL(p_request_id, CTC.request_id), 'ALL', 1)
128 AND CTC.bank_account_id = CBA.bank_account_id
129 AND CBA.bank_account_id = NVL(p_bank_acct_id, CBA.bank_account_id)
130 AND CBA.bank_branch_id = NVL(p_bank_branch_id, CBA.bank_branch_id)
131 AND CBA.bank_id = NVL(p_bank_id, CBA.bank_id)
132 AND ((CBA.bank_account_type IS NULL AND p_acct_type IS NULL)
133 OR CBA.bank_account_type = NVL(p_acct_type, CBA.bank_account_type))
134 -- code should not be used in bank statement lines
135 AND NOT EXISTS (
136 SELECT NULL
137 FROM ce_statement_lines CSL, ce_statement_headers CSH
138 WHERE CSL.statement_header_id = CSH.statement_header_id
139 AND CSH.bank_account_id = CTC.bank_account_id
140 AND CSL.trx_type = CTC.trx_type
141 AND CSL.trx_code = CTC.trx_code)
142 -- code should not be used in archived lines
143 AND NOT EXISTS (
144 SELECT NULL
145 FROM ce_arch_lines CAL
146 WHERE CAL.trx_code_id = CTC.transaction_code_id)
147 -- code should not be used in statment cashflow mappings
148 AND NOT EXISTS (
149 SELECT NULL
150 FROM ce_je_mappings JEM
151 WHERE JEM.TRX_CODE_ID = CTC.TRANSACTION_CODE_ID
152 )
153 -- 8892534: code should not be used in intra-day statements
154 AND NOT EXISTS (
155 SELECT NULL
156 FROM ce_intra_stmt_lines ISL
157 WHERE ISL.trx_code_id = CTC.transaction_code_id
158 )
159 -- 8892534: code should not be used in archived intra-day statements
160 AND NOT EXISTS (
161 SELECT NULL
162 FROM ce_arch_intra_lines ASL
163 WHERE ASL.trx_code_id = CTC.transaction_code_id
164 );
165
166 debug_log('Deleting transaction codes');
167 DELETE ce_transaction_codes CTC
168 WHERE EXISTS(
169 SELECT 1
170 FROM ce_trx_codes_util_gt GT
171 WHERE GT.trx_code_id = CTC.transaction_code_id);
172
173 debug_log('<<CE_PURGE_TRX_CODES_XML.purge_trx_codes');
174
175 END purge_trx_codes;
176
177 /*========================================================================+
178 | PUBLIC FUNCTION |
179 | beforeReport |
180 | |
181 | DESCRIPTION |
182 | Before Report trigger for CEPUTXCD. |
183 | |
184 | CALLS |
185 | CE_PURGE_TRX_CODES_XML.init_variables |
186 | CE_PURGE_TRX_CODES_XML.purge_trx_codes |
187 +========================================================================*/
188 FUNCTION beforeReport RETURN BOOLEAN IS
189 BEGIN
190 debug_log('>>CE_PURGE_TRX_CODES_XML.beforeReport');
191 cep_standard.init_security;
192
193 init_variables;
194
195 purge_trx_codes;
196
197 --store deleted codes
198 SELECT count(*)
199 INTO G_DELETE_COUNT
200 FROM ce_trx_codes_util_gt GT
201 WHERE GT.request_id = FND_GLOBAL.conc_request_id;
202
203 debug_log('g_delete_count::'||g_delete_count);
204
205 debug_log('<<CE_PURGE_TRX_CODES_XML.beforeReport');
206 RETURN (TRUE);
207 EXCEPTION
208 WHEN OTHERS THEN
209 ROLLBACK;
210 debug_log('EXCEPTION: CE_PURGE_TRX_CODES_XML.beforeReport trigger. Error : ' || SUBSTR(SQLERRM, 1, 200));
211 RETURN (FALSE);
212 END beforeReport;
213
214 /*========================================================================+
215 | PUBLIC FUNCTION |
216 | beforeReport |
217 | |
218 | DESCRIPTION |
219 | After Report trigger for CEPUTXCD. Once processing is done |
220 | commit the changes and purge GT table |
221 | |
222 | ARGUMENTS |
223 +========================================================================*/
224 FUNCTION afterReport RETURN BOOLEAN IS
225 BEGIN
226 debug_log('>>CE_PURGE_TRX_CODES_XML.afterReport');
227 DELETE ce_trx_codes_util_gt;
228 COMMIT;
229 debug_log('<<CE_PURGE_TRX_CODES_XML.afterReport');
230 RETURN (TRUE);
231 END afterReport;
232
233 END CE_PURGE_TRX_CODES_XML;