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