[Home] [Help]
PACKAGE BODY: APPS.AR_CASH_RECEIPT_PRINT_PKG
Source
1 PACKAGE BODY AR_CASH_RECEIPT_PRINT_PKG AS
2 -- $Header: arcrprptb.pls 120.3.12010000.2 2008/11/12 12:59:54 ankuagar ship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation BelmFont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | arcrpb.pls |
9 | |
10 | PACKAGE NAME |
11 | ar_cash_receipt_print_pkg |
12 | |
13 | DESCRIPTION |
14 | PACKAGE BODY. This provides XML extract for Receipt Print report for |
15 | Israel |
16 | HISTORY |
17 | 12/19/2006 sgautam Created |
18 | 06/28/2007 Ravikiran Incorporated Review comments |
19 | 14/07/2008 Rakesh Pulla Made changes as per the SR # 7024551.992
20 | 23/07/2008 Rakesh Pulla Made changes as per the SR # 6999382.993 |
21 +===========================================================================*/
22
23
24 --=============================================================================
25 -- *********** public procedures and functions **********
26 --=============================================================================
27 --=============================================================================
28
29 -- Following are public routines
30 --
31 -- 1. beforeReport
32 --
33 --=============================================================================
34 p_program_short_name VARCHAR2(30) := 'ARCRPRRPT';
35 /* This function provides all the processing that needs to be done before the
36 XML publisher engine starts to run the XML query */
37 FUNCTION beforeReport RETURN BOOLEAN IS
38 l_stmt VARCHAR2(32000);
39 l_receipt_filter VARCHAR2(120);
40 l_doc_filter VARCHAR2(120);
41 l_cust_filter VARCHAR2(120);
42 l_cp_orig_select VARCHAR2(120);
43 l_cp_orig_filter VARCHAR2(120);
44 l_date_filter VARCHAR2(240);
45 l_copy_string VARCHAR2(10);
46 l_orig_string VARCHAR2(10);
47 l_nls_date_format VARCHAR2(30);
48 l_user VARCHAR2(100);
49 /* Added for bug 7278191 */
50 ln_conc_program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
51 ln_request_id fnd_concurrent_requests.request_id%TYPE;
52 BEGIN
53 /* Added for bug 7278191 */
54 DELETE FROM AR_IL_CASH_RECEIPTS_GT;
55
56 COMMIT;
57
58 MO_GLOBAL.init('AR');
59 -- to check if the output directory exists
60 -- read the variable request_data to check if it is reentering the program
61 ln_request_id := FND_GLOBAL.conc_request_id;
62
63
64
65
66 /* Find out the user who issued the conc. prog */
67 SELECT user_name
68 INTO l_user
69 FROM fnd_user
70 WHERE user_id=fnd_global.user_id;
71
72 l_user:=','''||l_user||''' ';
73 /* Added for bug 7278191 */
74 IF p_copy_or_original='Original' THEN
75
76 SELECT concurrent_program_id
77 INTO ln_conc_program_id
78 FROM fnd_concurrent_programs
79 WHERE concurrent_program_name=p_program_short_name;
80
81 UPDATE fnd_concurrent_requests
82 SET save_output_flag='N'
83 WHERE request_id = ln_request_id
84 AND concurrent_program_id = ln_conc_program_id;
85 COMMIT;
86 END IF;
87
88 --Check for the Star Date and End Date parameters.
89 IF p_start_date IS NOT NULL THEN
90 l_date_filter := ' AND acr.receipt_date >= '''||p_start_date||'''';
91 ELSE
92 l_date_filter := ' AND 1=1';
93 END IF;
94
95 l_date_filter := l_date_filter||' AND acr.receipt_date <= NVL('''||p_end_date||''',SYSDATE) ';
96
97 -- Check for Receipt Number Parameters
98 IF p_receipt_from IS NOT NULL THEN
99 l_receipt_filter := ' AND acr.receipt_number >= '''||p_receipt_from||'''';
100 END IF;
101
102 IF p_receipt_to IS NOT NULL THEN
103 l_receipt_filter := l_receipt_filter|| ' AND acr.receipt_number <= '''||p_receipt_to||'''';
104 END IF;
105
106 -- Check for doc no parameters
107 IF p_doc_seq_value_from IS NOT NULL THEN
108 l_doc_filter := ' AND acr.doc_sequence_value >= '||p_doc_seq_value_from;
109 END IF;
110
111 IF p_doc_seq_value_to IS NOT NULL THEN
112 l_doc_filter := l_doc_filter|| ' AND acr.doc_sequence_value <= '||p_doc_seq_value_to;
113 END IF;
114
115 -- check for ct. parameter
116 IF p_customer_id IS NOT NULL THEN
117 l_cust_filter := ' AND acr.pay_from_customer = '||p_customer_id;
118 END IF;
119
120 -- Set the string copy or original
121 -- read from message dictionary
122
123 l_copy_string := FND_MESSAGE.GET_STRING('AR','AR_IL_COPY');
124
125 l_orig_string := FND_MESSAGE.GET_STRING('AR','AR_IL_ORIGINAL');
126
127 -- Check for Print Copy Or Original Parameter
128 IF p_copy_or_original='Original' THEN
129 l_cp_orig_select := ''''||l_orig_string||''' ORIG_COPY';
130 l_cp_orig_filter := ' AND NVL(acr.global_attribute20,''Copy'') <> ''Printed''';
131 ELSE
132 l_cp_orig_select := ''''||l_copy_string||''' ORIG_COPY';
133 END IF;
134
135 /* populate gt table */
136 l_stmt := 'INSERT INTO AR_IL_CASH_RECEIPTS_GT(
137 CASH_RECEIPT_ID
138 ,RECEIPT_NUMBER
139 ,RECEIPT_DATE
140 ,RECEIPT_STATUS
141 ,DOCUMENT_NUMBER
142 ,RECEIPT_AMOUNT
143 ,CURRENCY
144 ,CREDIT_CARD_NO
145 ,CREDIT_CARD_TYPE
146 ,MATURITY_DATE
147 ,METHOD_NAME
148 ,BANK_ACCOUNT
149 ,BANK_NAME
150 ,BANK_BRANCH_NAME
151 ,CUSTOMER_NAME
152 ,CUSTOMER_NUMBER
153 ,TAX_REGISTRATION_NUMBER
154 ,CUST_ACCOUNT_ID
155 ,CUST_ACCT_SITE_ID
156 ,ADDRESS_LINE1
157 ,ADDRESS_LINE2
158 ,CITY
159 ,POSTAL_CODE
160 ,COPY_OR_ORIGINAL
161 ,USER_NAME)
162 SELECT /* Receipt Information */
163 acr.cash_receipt_id CASH_RECEIPT_ID
164 ,acr.receipt_number RECEIPT_NUMBER
165 ,acr.receipt_date RECEIPT_DATE
166 ,acr.status RECEIPT_STATUS
167 ,acr.doc_sequence_value DOCUMENT_NUMBER
168 ,acr.amount RECEIPT_AMOUNT
169 ,acr.currency_code CURRENCY
170 ,acr.attribute13 CREDIT_CARD_NO
171 ,acr.attribute14 CREDIT_CARD_TYPE
172 ,aps.due_date MATURITY_DATE
173 ,arm.name METHOD_NAME
174 /* Bank Information */
175 ,iebav.bank_account_number BANK_ACCOUNT
176 ,NVL(cbbv1.bank_name,cbbv2.bank_name) BANK_NAME
177 ,NVL(cbbv1.bank_branch_name,cbbv2.bank_branch_name) BANK_BRANCH_NAME
178 /* Customer Information */
179 ,hp.party_name CUSTOMER_NAME
180 ,hca.account_number CUSTOMER_NUMBER
181 ,(select registration_number
182 from xle_firstparty_information_v
183 where legal_entity_id = acr.legal_entity_id) TAX_REGISTRATION_NUMBER
184 /* The next two columns would be used to automatically
185 join to Q_INVOICES */
186 ,hca.cust_account_id CUST_ACCOUNT_ID
187 ,hcas.cust_acct_site_id CUST_ACCT_SITE_ID
188 /* Customer Address */
189 ,hl.address1 ADDRESS_LINE1
190 ,hl.address2 ADDRESS_LINE2
191 ,hl.city CITY
192 ,hl.postal_code POSTAL_CODE,'||l_cp_orig_select||l_user||'
193 FROM ar_cash_receipts acr,
194 ar_receipt_methods arm,
195 ar_payment_schedules aps,
196 iby_ext_bank_accounts_v iebav,
197 ce_bank_branches_v cbbv1,
198 ce_bank_branches_v cbbv2,
199 hz_parties hp,
200 hz_party_sites hps,
201 hz_cust_accounts hca,
202 hz_cust_acct_sites hcas,
203 hz_cust_site_uses hcsu,
204 hz_locations hl
205 WHERE aps.cash_receipt_id = acr.cash_receipt_id
206 AND acr.status IN (''APP'',''REV'',''NSF'',''STOP'')
207 AND acr.confirmed_flag=''Y''
208 AND arm.receipt_method_id = acr.receipt_method_id
209 AND iebav.ext_bank_account_id (+) = acr.customer_bank_account_id
210 AND iebav.branch_party_id = cbbv1.branch_party_id(+)
211 AND acr.customer_bank_branch_id = cbbv2.branch_party_id (+)
212 AND acr.pay_from_customer = hca.cust_account_id
213 AND acr.customer_site_use_id = hcsu.site_use_id (+)
214 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
215 AND hp.party_id = hca.party_id
216 AND hca.cust_account_id = hcas.cust_account_id
217 AND hcas.party_site_id = hps.party_site_id
218 AND hps.location_id = hl.location_id'
219 ||l_date_filter||l_receipt_filter||l_doc_filter||l_cust_filter||l_cp_orig_filter;
220
221
222
223 EXECUTE IMMEDIATE l_stmt; --using p_start_date,p_end_date;
224
225 return TRUE;
226
227 END;
228
229 /* This procedure marks global_attrbute20 of table ar_cash_receipts_all
230 for its print status */
231 PROCEDURE update_ar_cash_receipts
232 IS
233
234 BEGIN
235
236 IF p_copy_or_original='Original' THEN
237
238 UPDATE ar_cash_receipts_all
239 SET global_attribute20 = 'Printed'
240 WHERE cash_receipt_id in (SELECT cash_receipt_id
241 FROM AR_IL_CASH_RECEIPTS_GT);
242
243
244
245
246 COMMIT;
247
248 END IF;
249
250
251 END;
252
253 /* This function provides all the processing that needs to be done before the
254 XML publisher engine finished running the XML query */
255 FUNCTION afterReport RETURN BOOLEAN IS
256 BEGIN
257
258 update_ar_cash_receipts;
259
260 return TRUE;
261
262 END;
263
264 END AR_CASH_RECEIPT_PRINT_PKG;