1 PACKAGE BODY CE_SYSTEM_PARAMETERS1_PKG as
2 /* $Header: cesysp1b.pls 120.8 2008/01/23 13:21:45 kbabu ship $ */
3 FUNCTION body_revision RETURN VARCHAR2 IS
4 BEGIN
5
6 RETURN '$Revision: 120.8 $';
7
8 END body_revision;
9
10 FUNCTION spec_revision RETURN VARCHAR2 IS
11 BEGIN
12
13 RETURN G_spec_revision;
14
15 END spec_revision;
16
17 /* ---------------------------------------------------------------------
18 | CALL BY |
19 | CEXCABMR.fmb AXCABMR.set_sys_par
20 | ceabrdrb.pls,
21 | ceabrmab.pls
22 |
23 | The LEGAL_ENTITY_ID must be provided
24 --------------------------------------------------------------------- */
25
26 PROCEDURE SELECT_COLUMNS
27 (X_ROWID IN OUT NOCOPY VARCHAR2,
28 X_SET_OF_BOOKS_ID IN OUT NOCOPY NUMBER,
29 X_CASHBOOK_BEGIN_DATE IN OUT NOCOPY DATE,
30 X_SHOW_CLEARED_FLAG IN OUT NOCOPY VARCHAR2,
31 X_SHOW_VOID_PAYMENT_FLAG IN OUT NOCOPY VARCHAR2,
32 X_LINE_AUTOCREATION_FLAG IN OUT NOCOPY VARCHAR2,
33 X_INTERFACE_PURGE_FLAG IN OUT NOCOPY VARCHAR2,
34 X_INTERFACE_ARCHIVE_FLAG IN OUT NOCOPY VARCHAR2,
35 X_LINES_PER_COMMIT IN OUT NOCOPY NUMBER,
36 X_FUNCTIONAL_CURRENCY IN OUT NOCOPY VARCHAR2,
37 X_SOB_SHORT_NAME IN OUT NOCOPY VARCHAR2,
38 X_ACCOUNT_PERIOD_TYPE IN OUT NOCOPY VARCHAR2,
39 X_USER_EXCHANGE_RATE_TYPE IN OUT NOCOPY VARCHAR2,
40 X_CHART_OF_ACCOUNTS_ID IN OUT NOCOPY NUMBER,
41 X_CASHFLOW_EXCHANGE_RATE_TYPE IN OUT NOCOPY VARCHAR2,
42 X_AUTHORIZATION_BAT IN OUT NOCOPY VARCHAR2,
43 X_BSC_EXCHANGE_DATE_TYPE IN OUT NOCOPY VARCHAR2,
44 X_BAT_EXCHANGE_DATE_TYPE IN OUT NOCOPY VARCHAR2,
45 X_LEGAL_ENTITY_ID IN OUT NOCOPY NUMBER
46 ) IS
47 BEGIN
48 IF ( X_legal_entity_id is not null) then
49 SELECT s.rowid,
50 s.set_of_books_id,
51 s.cashbook_begin_date,
52 s.show_cleared_flag,
53 NVL(s.show_void_payment_flag, 'N'),
58 NVL(s.interface_archive_flag,'N'),
54 NVL(s.lines_per_commit,1),
55 g.currency_code,
56 s.line_autocreation_flag,
57 NVL(s.interface_purge_flag,'N'),
59 g.short_name,
60 g.accounted_period_type,
61 ct.user_conversion_type,
62 g.chart_of_accounts_id,
63 s.CASHFLOW_EXCHANGE_RATE_TYPE,
64 s.AUTHORIZATION_BAT,
65 s.BSC_EXCHANGE_DATE_TYPE,
66 s.BAT_EXCHANGE_DATE_TYPE,
67 s.legal_entity_id
68 INTO X_Rowid,
69 X_Set_Of_Books_Id,
70 X_Cashbook_Begin_Date,
71 X_Show_Cleared_Flag,
72 X_Show_Void_Payment_Flag,
73 X_Lines_Per_Commit,
74 X_Functional_Currency,
75 X_line_autocreation_flag,
76 X_interface_purge_flag,
77 X_interface_archive_flag,
78 X_sob_short_name,
79 X_account_period_type,
80 X_user_exchange_rate_type,
81 X_chart_of_accounts_id,
82 X_CASHFLOW_EXCHANGE_RATE_TYPE,
83 X_AUTHORIZATION_BAT,
84 X_BSC_EXCHANGE_DATE_TYPE,
85 X_BAT_EXCHANGE_DATE_TYPE,
86 X_legal_entity_id
87 FROM CE_SYSTEM_PARAMETERS s,
88 GL_SETS_OF_BOOKS g,
89 GL_DAILY_CONVERSION_TYPES ct
90 WHERE s.legal_entity_id = X_legal_entity_id
91 and s.set_of_books_id = g.set_of_books_id
92 AND ct.conversion_type = 'User';
93
94
95 END IF;
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.SELECT_COLUMNS NO_DATA_FOUND');
99 null;
100 WHEN TOO_MANY_ROWS THEN
101 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.SELECT_COLUMNS TOO_MANY_ROWS');
102 null;
103 WHEN OTHERS THEN
104 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.SELECT_COLUMNS');
105 RAISE;
106 END SELECT_COLUMNS;
107
108 /* ---------------------------------------------------------------------
109 | CALL BY |
110 | ceabrimb.pls, ceabrmab.pls
111 |
112 | The X_BANK_ACCOUNT_ID must be provided
113 |
114 --------------------------------------------------------------------- */
115
116 PROCEDURE BA_SELECT_COLUMNS
117 (X_ROWID IN OUT NOCOPY VARCHAR2,
118 X_AP_AMOUNT_TOLERANCE IN OUT NOCOPY NUMBER,
119 X_AP_PERCENT_TOLERANCE IN OUT NOCOPY NUMBER,
120 X_AR_AMOUNT_TOLERANCE IN OUT NOCOPY NUMBER,
121 X_AR_PERCENT_TOLERANCE IN OUT NOCOPY NUMBER,
122 X_CE_AMOUNT_TOLERANCE IN OUT NOCOPY NUMBER,
123 X_CE_PERCENT_TOLERANCE IN OUT NOCOPY NUMBER,
124 X_STMT_LN_FLOAT_HANDLING_FLAG IN OUT NOCOPY VARCHAR2,
125 X_AUTORECON_AP_MATCHING_ORDER IN OUT NOCOPY VARCHAR2,
126 X_AUTORECON_AR_MATCHING_ORDER IN OUT NOCOPY VARCHAR2,
127 X_RECON_FX_BANK_XRATE_TYPE IN OUT NOCOPY VARCHAR2,
128 X_RECON_FX_BANK_XDATE_TYPE IN OUT NOCOPY VARCHAR2,
129 X_RECON_ENABLE_OI_FLAG IN OUT NOCOPY VARCHAR2,
130 X_RECON_OI_FLOAT_STATUS IN OUT NOCOPY VARCHAR2,
131 X_RECON_OI_CLEARED_STATUS IN OUT NOCOPY VARCHAR2,
132 X_RECON_OI_MATCHING_CODE IN OUT NOCOPY VARCHAR2,
133 X_RECON_OI_AMOUNT_TOLERANCE IN OUT NOCOPY NUMBER,
134 X_RECON_OI_PERCENT_TOLERANCE IN OUT NOCOPY NUMBER,
135 X_MANUAL_RECON_AMOUNT_TOL IN OUT NOCOPY NUMBER,
136 X_MANUAL_RECON_PERCENT_TOL IN OUT NOCOPY NUMBER,
137 X_RECON_AP_FX_DIFF_HANDLING IN OUT NOCOPY VARCHAR2,
138 X_RECON_AR_FX_DIFF_HANDLING IN OUT NOCOPY VARCHAR2,
139 X_RECON_CE_FX_DIFF_HANDLING IN OUT NOCOPY VARCHAR2,
140 X_RECON_AP_TOL_DIFF_ACCT IN OUT NOCOPY VARCHAR2,
141 X_RECON_CE_TOL_DIFF_ACCT IN OUT NOCOPY VARCHAR2,
142 X_LEGAL_ENTITY_ID IN OUT NOCOPY NUMBER,
143 X_BANK_ACCOUNT_ID IN OUT NOCOPY NUMBER,
144 X_AUTORECON_AP_MATCHING_ORDER2 IN OUT NOCOPY VARCHAR2 -- FOR SEPA ER 6700007
145 ) IS
146 BEGIN
147 IF ( X_legal_entity_id is not null) then
148 SELECT BA.rowid,
149 BA.AP_AMOUNT_TOLERANCE ,
150 BA.AP_PERCENT_TOLERANCE ,
151 BA.AR_AMOUNT_TOLERANCE ,
152 BA.AR_PERCENT_TOLERANCE ,
153 BA.CE_AMOUNT_TOLERANCE ,
154 BA.CE_PERCENT_TOLERANCE ,
155 BA.STMT_LINE_FLOAT_HANDLING_FLAG ,
156 BA.AUTORECON_AP_MATCHING_ORDER ,
157 BA.AUTORECON_AR_MATCHING_ORDER ,
158 BA.RECON_FOREIGN_BANK_XRATE_TYPE,
159 BA.RECON_FOR_BANK_XRATE_DATE_TYPE ,
160 BA.RECON_ENABLE_OI_FLAG ,
161 BA.RECON_OI_FLOAT_STATUS ,
162 BA.RECON_OI_CLEARED_STATUS ,
163 BA.RECON_OI_MATCHING_CODE ,
164 BA.RECON_OI_AMOUNT_TOLERANCE ,
165 BA.RECON_OI_PERCENT_TOLERANCE ,
166 BA.MANUAL_RECON_AMOUNT_TOLERANCE ,
167 BA.MANUAL_RECON_PERCENT_TOLERANCE ,
168 BA.RECON_AP_FOREIGN_DIFF_HANDLING,
169 BA.RECON_AR_FOREIGN_DIFF_HANDLING,
170 BA.RECON_CE_FOREIGN_DIFF_HANDLING,
171 BA.RECON_AP_TOLERANCE_DIFF_ACCT ,
172 BA.RECON_CE_TOLERANCE_DIFF_ACCT ,
173 BA.ACCOUNT_OWNER_ORG_ID,
174 BA.BANK_ACCOUNT_ID ,
175 BA.AUTORECON_AP_MATCHING_ORDER2 -- FOR SEPA ER 6700007
176
177 INTO X_Rowid,
178 X_AP_AMOUNT_TOLERANCE ,
179 X_AP_PERCENT_TOLERANCE ,
180 X_AR_AMOUNT_TOLERANCE ,
181 X_AR_PERCENT_TOLERANCE ,
182 X_CE_AMOUNT_TOLERANCE ,
183 X_CE_PERCENT_TOLERANCE ,
184 X_STMT_LN_FLOAT_HANDLING_FLAG ,
185 X_AUTORECON_AP_MATCHING_ORDER ,
186 X_AUTORECON_AR_MATCHING_ORDER ,
187 X_RECON_FX_BANK_XRATE_TYPE ,
188 X_RECON_FX_BANK_XDATE_TYPE ,
189 X_RECON_ENABLE_OI_FLAG ,
190 X_RECON_OI_FLOAT_STATUS ,
191 X_RECON_OI_CLEARED_STATUS ,
192 X_RECON_OI_MATCHING_CODE ,
193 X_RECON_OI_AMOUNT_TOLERANCE ,
194 X_RECON_OI_PERCENT_TOLERANCE ,
195 X_MANUAL_RECON_AMOUNT_TOL ,
196 X_MANUAL_RECON_PERCENT_TOL ,
197 X_RECON_AP_FX_DIFF_HANDLING ,
198 X_RECON_AR_FX_DIFF_HANDLING ,
199 X_RECON_CE_FX_DIFF_HANDLING ,
200 X_RECON_AP_TOL_DIFF_ACCT ,
201 X_RECON_CE_TOL_DIFF_ACCT ,
202 X_LEGAL_ENTITY_ID ,
203 X_BANK_ACCOUNT_ID ,
204 X_AUTORECON_AP_MATCHING_ORDER2 -- FOR SEPA ER 6700007
205 FROM CE_BANK_ACCOUNTS BA
206 WHERE BA.BANK_ACCOUNT_ID = X_BANK_ACCOUNT_ID;
207 END IF;
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.BA_SELECT_COLUMNS NO_DATA_FOUND');
211 null;
212 WHEN TOO_MANY_ROWS THEN
213 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.BA_SELECT_COLUMNS TOO_MANY_ROWS');
214 null;
215 WHEN OTHERS THEN
216 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.BA_SELECT_COLUMNS');
217 RAISE;
218 END Ba_Select_Columns;
219
220 /* ---------------------------------------------------------------------
221 | CALL BY |
222 |
223 |
224 |
225 --------------------------------------------------------------------- */
226 PROCEDURE BAU_SELECT_COLUMNS
227 (X_ROWID IN OUT NOCOPY VARCHAR2,
228 X_RECEIVABLE_TRX_ID IN OUT NOCOPY NUMBER,
229 X_ORG_ID IN OUT NOCOPY NUMBER,
230 X_BANK_ACCOUNT_ID IN OUT NOCOPY NUMBER,
231 X_BANK_ACCT_USE_ID IN OUT NOCOPY NUMBER)
232 IS
233 BEGIN
234 IF ((X_BANK_ACCT_USE_ID is not null) or
235 (X_BANK_ACCOUNT_ID is not null and X_ORG_ID is not null )) then
236 null;
237 SELECT BAU.rowid,
238 BAU.NEW_AR_RCPTS_RECEIVABLE_TRX_ID ,
239 BAU.ORG_ID,
240 BAU.BANK_ACCOUNT_ID,
241 BAU.BANK_ACCT_USE_ID
242 INTO X_ROWID ,
243 X_RECEIVABLE_TRX_ID,
244 X_ORG_ID,
245 X_BANK_ACCOUNT_ID,
246 X_BANK_ACCT_USE_ID
247 FROM CE_BANK_ACCT_USES_ALL BAU
248 WHERE
249 BAU.BANK_ACCT_USE_ID = NVL(X_BANK_ACCT_USE_ID, BAU.BANK_ACCT_USE_ID)
250 and BAU.ORG_ID = NVL(X_ORG_ID, BAU.ORG_ID)
251 AND BAU.BANK_ACCOUNT_ID = NVL(X_BANK_ACCOUNT_ID, BAU.BANK_ACCOUNT_ID);
252 END IF;
253 EXCEPTION
254 WHEN NO_DATA_FOUND THEN
255 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.BAU_SELECT_COLUMNS NO_DATA_FOUND');
256 null;
257 WHEN TOO_MANY_ROWS THEN
258 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.BAU_SELECT_COLUMNS TOO_MANY_ROWS');
259 null;
260 WHEN OTHERS THEN
261 cep_standard.debug('EXCEPTION: CE_SYSTEM_PARAMETERS1_PKG.BAU_SELECT_COLUMNS');
262 RAISE;
263 END BAU_SELECT_COLUMNS;
264
265 END CE_SYSTEM_PARAMETERS1_PKG;
[Home] [Help]