[Home] [Help]
PACKAGE BODY: APPS.AR_ARZACS_XMLP_PKG
Source
1 PACKAGE BODY AR_ARZACS_XMLP_PKG AS
2 /* $Header: ARZACSB.pls 120.0 2007/12/27 14:12:53 abraghun noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
7 CHECK_GL_DATE;
8 GET_SET_OF_BOOKS;
9 GET_PAYMENT_METHOD;
10 GET_REMITTANCE_BANK_ACCOUNT;
11 GET_BATCH_DETAILS;
12 GET_MAX_CHR;
13 POPULATE_YES_FIELD;
14 POPULATE_NO_FIELD;
15 POPULATE_VARIOUS_YES_NO_FIELDS;
16 IF P_BR_REPORT <> 'Y' THEN
17 CHECK_AUTOMATIC_CLEARING;
18 ELSE
19 BR_POPUL_VARIOUS_YES_NO_FIELDS;
20 BR_HOUSEKEEPER_PROGRAM;
21 END IF;
22 LP_GL_DATE:=to_char(P_GL_DATE,'DD-MON-YYYY');
23 LP_CLEAR_DATE:=to_char(P_CLEAR_DATE,'DD-MON-YYYY');
24
25 RETURN (TRUE);
26 END BEFOREREPORT;
27
28 FUNCTION AFTERREPORT RETURN BOOLEAN IS
29 BEGIN
30 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
31 RETURN (TRUE);
32 END AFTERREPORT;
33
34 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
35 BEGIN
36 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
37 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
38 RETURN (TRUE);
39 END BEFOREPFORM;
40
41 FUNCTION CF_TRANS_TYPEFORMULA RETURN CHAR IS
42 OUT_TRANS_TYPE_NAME VARCHAR2(20);
43 BEGIN
44 IF P_BR_REPORT <> 'Y' THEN
45 RETURN ' ';
46 END IF;
47 OUT_TRANS_TYPE_NAME := ' ';
48 IF P_BR_TRANSACTION_TYPE IS NOT NULL THEN
49 SELECT
50 NAME
51 INTO OUT_TRANS_TYPE_NAME
52 FROM
53 RA_CUST_TRX_TYPES
54 WHERE CUST_TRX_TYPE_ID = P_BR_TRANSACTION_TYPE;
55 END IF;
56 RETURN OUT_TRANS_TYPE_NAME;
57 END CF_TRANS_TYPEFORMULA;
58
59 FUNCTION CF_BR_DATE_DISPFORMULA(BR_DATE IN DATE) RETURN CHAR IS
60 BEGIN
61 RETURN (FND_DATE.DATE_TO_CHARDATE(BR_DATE));
62 END CF_BR_DATE_DISPFORMULA;
63
64 FUNCTION AFTERPFORM RETURN BOOLEAN IS
65 BEGIN
66 P_AUTO_CLEAR_RECEIPTS := NULL;
67 IF P_BR_REPORT = 'Y' THEN
68 P_AUTO_CLEAR_RECEIPTS := ' (crh.cash_receipt_id in ' || ' (select rap.cash_receipt_id from ' ||
69 ' ar_transaction_history trh,ar_receivable_applications rap ' || ' where trh.request_id = :p_conc_request_id and ' ||
70 ' trh.current_record_flag = ''Y'' and ' || ' rap.applied_customer_trx_id = trh.customer_trx_id) and ' || ' crh.current_record_flag = ''Y'')
71 and ';
72 ELSE
73 P_AUTO_CLEAR_RECEIPTS := ' crh.request_id = :p_conc_request_id and ';
74 END IF;
75 RETURN (TRUE);
76 END AFTERPFORM;
77
78 FUNCTION C_REC1_P RETURN NUMBER IS
79 BEGIN
80 RETURN C_REC1;
81 END C_REC1_P;
82
83 FUNCTION FUNC_CURRENCY_CODE_P RETURN VARCHAR2 IS
84 BEGIN
85 RETURN FUNC_CURRENCY_CODE;
86 END FUNC_CURRENCY_CODE_P;
87
88 FUNCTION COMPANY_NAME_P RETURN VARCHAR2 IS
89 BEGIN
90 RETURN COMPANY_NAME;
91 END COMPANY_NAME_P;
92
93 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
94 BEGIN
95 RETURN C_NLS_YES;
96 END C_NLS_YES_P;
97
98 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
99 BEGIN
100 RETURN C_NLS_NO;
101 END C_NLS_NO_P;
102
103 FUNCTION C_PAYMENT_METHOD_NAME_P RETURN VARCHAR2 IS
104 BEGIN
105 RETURN C_PAYMENT_METHOD_NAME;
106 END C_PAYMENT_METHOD_NAME_P;
107
108 FUNCTION C_REMITTANCE_BANK_ACC_NAME_P RETURN VARCHAR2 IS
109 BEGIN
110 RETURN C_REMITTANCE_BANK_ACC_NAME;
111 END C_REMITTANCE_BANK_ACC_NAME_P;
112
113 FUNCTION C_REMITTANCE_BATCH_NAME_P RETURN VARCHAR2 IS
114 BEGIN
115 RETURN C_REMITTANCE_BATCH_NAME;
116 END C_REMITTANCE_BATCH_NAME_P;
117
118 FUNCTION MAX_CRH_ID_P RETURN NUMBER IS
119 BEGIN
120 RETURN MAX_CRH_ID;
121 END MAX_CRH_ID_P;
122
123 PROCEDURE GET_SET_OF_BOOKS IS
124 BEGIN
125 /*SRW.MESSAGE(1000
126 ,'DEBUG: Before_Report_Procs.Get_Set_Of_Books.')*/NULL;
127 SELECT
128 SOB.NAME,
129 SOB.CURRENCY_CODE
130 INTO COMPANY_NAME,FUNC_CURRENCY_CODE
131 FROM
132 GL_SETS_OF_BOOKS SOB,
133 AR_SYSTEM_PARAMETERS AR
134 WHERE SOB.SET_OF_BOOKS_ID = AR.SET_OF_BOOKS_ID;
135 EXCEPTION
136 WHEN OTHERS THEN
137 /*SRW.MESSAGE(5000
138 ,'DEBUG: Get_Batch_Details.Get_Set_Of_Books.')*/NULL;
139 RAISE;
140 END GET_SET_OF_BOOKS;
141
142 PROCEDURE GET_PAYMENT_METHOD IS
143 BEGIN
144 /*SRW.MESSAGE(1000
145 ,'DEBUG: Before_Report_Procs.Get_Payment_Method.')*/NULL;
146 IF (P_PAYMENT_METHOD_ID IS NOT NULL) THEN
147 SELECT
148 NAME
149 INTO C_PAYMENT_METHOD_NAME
150 FROM
151 AR_RECEIPT_METHODS
152 WHERE RECEIPT_METHOD_ID = P_PAYMENT_METHOD_ID;
153 END IF;
154 EXCEPTION
155 WHEN OTHERS THEN
156 /*SRW.MESSAGE(5000
157 ,'DEBUG: Get_Payment_Method.Unable to retrieve Payment Method.')*/NULL;
158 RAISE;
159 END GET_PAYMENT_METHOD;
160
161 PROCEDURE GET_REMITTANCE_BANK_ACCOUNT IS
162 BEGIN
163 /*SRW.MESSAGE(1000
164 ,'DEBUG: Before_Report_Procs.Get_Remittance_Bank_Account.')*/NULL;
165 IF (P_REMITTANCE_BANK_ACCOUNT_ID IS NOT NULL) THEN
166 SELECT
167 BANK_ACCOUNT_NAME
168 INTO C_REMITTANCE_BANK_ACC_NAME
169 FROM
170 CE_BANK_ACCOUNTS CBA,
171 CE_BANK_ACCT_USES BA
172 WHERE BANK_ACCT_USE_ID = P_REMITTANCE_BANK_ACCOUNT_ID
173 AND CBA.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID;
174 END IF;
175 EXCEPTION
176 WHEN OTHERS THEN
177 /*SRW.MESSAGE(5000
178 ,'DEBUG: Get_Remittance_Bank_Account.Unable to retrieve Remittance Bank Account.')*/NULL;
179 RAISE;
180 END GET_REMITTANCE_BANK_ACCOUNT;
181
182 PROCEDURE GET_BATCH_DETAILS IS
183 BEGIN
184 /*SRW.MESSAGE(1000
185 ,'DEBUG: Before_Report_Procs.Get_Batch_Details.')*/NULL;
186 IF (P_BATCH_NAME IS NOT NULL) THEN
187 SELECT
188 NAME,
189 BATCH_ID
190 INTO C_REMITTANCE_BATCH_NAME,P_BATCH_ID
191 FROM
192 AR_BATCHES
193 WHERE NAME = P_BATCH_NAME;
194 END IF;
195 EXCEPTION
196 WHEN OTHERS THEN
197 /*SRW.MESSAGE(5000
198 ,'DEBUG: Get_Batch_Details.Unable to retrieve Batch Details.')*/NULL;
199 RAISE;
200 END GET_BATCH_DETAILS;
201
202 PROCEDURE GET_MAX_CHR IS
203 BEGIN
204 /*SRW.MESSAGE(1000
205 ,'DEBUG: Before_Report_Procs.Get_Max_CHR.')*/NULL;
206 SELECT
207 MAX(CRH.CASH_RECEIPT_HISTORY_ID)
208 INTO MAX_CRH_ID
209 FROM
210 AR_CASH_RECEIPT_HISTORY CRH;
211 EXCEPTION
212 WHEN OTHERS THEN
213 /*SRW.MESSAGE(5000
214 ,'DEBUG: Get_Batch_Details.Get_Mac_CHR.')*/NULL;
215 RAISE;
216 END GET_MAX_CHR;
217
218 PROCEDURE POPULATE_YES_FIELD IS
219 NLS_YES FND_LOOKUPS.MEANING%TYPE;
220 BEGIN
221 /*SRW.MESSAGE(1000
222 ,'DEBUG: Before_Report_Procs.Populate_Yes_Field.')*/NULL;
223 SELECT
224 MEANING
225 INTO NLS_YES
226 FROM
227 AR_LOOKUPS
228 WHERE LOOKUP_TYPE = 'YES/NO'
229 AND LOOKUP_CODE = 'Y';
230 C_NLS_YES := NLS_YES;
231 EXCEPTION
232 WHEN OTHERS THEN
233 /*SRW.MESSAGE(5000
234 ,'DEBUG: Get_Batch_Details.Populate_Yes_Field.')*/NULL;
235 RAISE;
236 END POPULATE_YES_FIELD;
237
238 PROCEDURE POPULATE_NO_FIELD IS
239 NLS_NO FND_LOOKUPS.MEANING%TYPE;
240 BEGIN
241 /*SRW.MESSAGE(1000
242 ,'DEBUG: Before_Report_Procs.Populate_No_Field.')*/NULL;
243 SELECT
244 MEANING
245 INTO NLS_NO
246 FROM
247 AR_LOOKUPS LN
248 WHERE LOOKUP_TYPE = 'YES/NO'
249 AND LN.LOOKUP_CODE = 'N';
250 C_NLS_NO := NLS_NO;
251 EXCEPTION
252 WHEN OTHERS THEN
253 /*SRW.MESSAGE(5000
254 ,'DEBUG: Get_Batch_Details.Populate_No_Field.')*/NULL;
255 RAISE;
256 END POPULATE_NO_FIELD;
257
258 PROCEDURE POPULATE_VARIOUS_YES_NO_FIELDS IS
259 BEGIN
260 /*SRW.MESSAGE(1000
261 ,'DEBUG: Before_Report_Procs.Populate_Various_Yes_No_Fields.')*/NULL;
262 IF (P_CLR_REMITTED_RECEIPTS = 'Y') THEN
263 P_NLS_CLR_REM_REC := C_NLS_YES;
264 ELSE
265 P_NLS_CLR_REM_REC := C_NLS_NO;
266 END IF;
267 IF (P_CLR_DISC_RECEIPTS = 'Y') THEN
268 P_NLS_CLR_DISC_REC := C_NLS_YES;
269 ELSE
270 P_NLS_CLR_DISC_REC := C_NLS_NO;
271 END IF;
272 IF (P_ELIMINATE_BANK_RISK = 'Y') THEN
273 P_NLS_ELI_BANK_RISK := C_NLS_YES;
274 ELSE
275 P_NLS_ELI_BANK_RISK := C_NLS_NO;
276 END IF;
277 END POPULATE_VARIOUS_YES_NO_FIELDS;
278
279 PROCEDURE CHECK_AUTOMATIC_CLEARING IS
280 CHECK_CLEARING BOOLEAN;
281 BEGIN
282 /*SRW.MESSAGE(1000
283 ,'DEBUG: Before_Report_Procs.check_automatic_clearing.')*/NULL;
284 CHECK_CLEARING := ARP_AUTOMATIC_CLEARING_PKG.AR_AUTOMATIC_CLEARING(P_CLR_REMITTED_RECEIPTS
285 ,P_CLR_DISC_RECEIPTS
286 ,P_ELIMINATE_BANK_RISK
287 ,P_CLEAR_DATE
288 ,P_GL_DATE
289 ,P_CUSTOMER_NAME_LOW
290 ,P_CUSTOMER_NAME_HIGH
291 ,P_CUSTOMER_NUMBER_LOW
292 ,P_CUSTOMER_NUMBER_HIGH
293 ,P_RECEIPT_NUMBER_LOW
294 ,P_RECEIPT_NUMBER_HIGH
295 ,P_REMITTANCE_BANK_ACCOUNT_ID
296 ,P_PAYMENT_METHOD_ID
297 ,P_EXCHANGE_RATE_TYPE
298 ,P_BATCH_ID
299 ,P_UNDO_CLEARING);
300 IF (NOT CHECK_CLEARING) THEN
301 /*SRW.MESSAGE(5000
302 ,'DEBUG: Get_Batch_Details.Error in arp_automatic_clearing_pkg')*/NULL;
303 END IF;
304 EXCEPTION
305 WHEN OTHERS THEN
306 /*SRW.MESSAGE(5000
307 ,'DEBUG: Get_Batch_Details.Check_Automatic_Clearing.')*/NULL;
308 RAISE;
309 END CHECK_AUTOMATIC_CLEARING;
310
311 PROCEDURE BR_POPUL_VARIOUS_YES_NO_FIELDS IS
312 BEGIN
313 /*SRW.MESSAGE(1000
314 ,'DEBUG: Before_Report_Procs.Br_Populate_Various_Yes_No_Fields.')*/NULL;
315 IF (P_BR_INCLUDE_ENDORSED = 'Y') THEN
316 P_NLS_BR_INCLUDE_ENDORSED := C_NLS_YES;
317 ELSE
318 P_NLS_BR_INCLUDE_ENDORSED := C_NLS_NO;
319 END IF;
320 IF (P_BR_INCLUDE_FACTORED = 'Y') THEN
321 P_NLS_BR_INCLUDE_FACTORED := C_NLS_YES;
322 ELSE
323 P_NLS_BR_INCLUDE_FACTORED := C_NLS_NO;
324 END IF;
325 IF (P_BR_INCLUDE_REMITTED = 'Y') THEN
326 P_NLS_BR_INCLUDE_REMITTED := C_NLS_YES;
327 ELSE
328 P_NLS_BR_INCLUDE_REMITTED := C_NLS_NO;
329 END IF;
330 IF (P_BR_REPORT = 'Y') THEN
331 P_NLS_BR_REPORT := C_NLS_YES;
332 ELSE
333 P_NLS_BR_REPORT := C_NLS_NO;
334 END IF;
335 EXCEPTION
336 WHEN OTHERS THEN
337 /*SRW.MESSAGE(5001
338 ,'DEBUG: Br_Popul_Various_Yes_No_Fields')*/NULL;
339 RAISE;
340 END BR_POPUL_VARIOUS_YES_NO_FIELDS;
341
342 PROCEDURE BR_HOUSEKEEPER_PROGRAM IS
343 CHECK_HOUSEKEEPER BOOLEAN;
344 BEGIN
345 /*SRW.MESSAGE(8000
346 ,'DEBUG: Before_Report_Procs.BR_Housekeeper.')*/NULL;
347 CHECK_HOUSEKEEPER := ARP_BR_HOUSEKEEPER_PKG.AR_BR_HOUSEKEEPER(P_BR_EFFECTIVE_DATE
348 ,P_GL_DATE
349 ,P_BR_MATURITY_DATE_FROM
350 ,P_BR_MATURITY_DATE_TO
351 ,P_BR_GL_DATE_FROM
352 ,P_BR_GL_DATE_TO
353 ,P_BR_TRANSACTION_TYPE
354 ,P_BR_INCLUDE_FACTORED
355 ,P_BR_INCLUDE_REMITTED
356 ,P_BR_INCLUDE_ENDORSED);
357 IF (NOT CHECK_HOUSEKEEPER) THEN
358 /*SRW.MESSAGE(8100
359 ,'DEBUG: Check Housekeeper.')*/NULL;
360 END IF;
361 EXCEPTION
362 WHEN OTHERS THEN
363 /*SRW.MESSAGE(8200
364 ,'DEBUG: Check Housekeeper - others.')*/NULL;
365 RAISE;
366 END BR_HOUSEKEEPER_PROGRAM;
367
368 PROCEDURE CHECK_GL_DATE IS
369 OPEN_PERIODS NUMBER;
370 NOT_OPEN EXCEPTION;
371 BEGIN
372 /*SRW.MESSAGE(1000
373 ,'DEBUG: Before_Report_Procs.Check_GL_Date.')*/NULL;
374 SELECT
375 count(*)
376 INTO OPEN_PERIODS
377 FROM
378 GL_PERIOD_STATUSES GPS,
379 AR_SYSTEM_PARAMETERS ASP
380 WHERE GPS.SET_OF_BOOKS_ID = ASP.SET_OF_BOOKS_ID
381 AND GPS.APPLICATION_ID = 222
382 AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
383 AND P_GL_DATE BETWEEN GPS.START_DATE
384 AND GPS.END_DATE
385 AND GPS.CLOSING_STATUS in ( 'O' , 'F' );
386 IF (OPEN_PERIODS = 0) THEN
387 RAISE NOT_OPEN;
388 END IF;
389 EXCEPTION
390 WHEN NOT_OPEN THEN
391 /*SRW.MESSAGE(5000
392 ,'DEBUG: GL_Date not within open period.')*/NULL;
393 RAISE;
394 WHEN OTHERS THEN
395 /*SRW.MESSAGE(5000
396 ,'DEBUG: Get_Batch_Details.Check_GL_Date.')*/NULL;
397 RAISE;
398 END CHECK_GL_DATE;
399
400 END AR_ARZACS_XMLP_PKG;
401