DBA Data[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;