eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GMF_AR_GET_PAYMENT_DETAILS

Source


1 PACKAGE BODY GMF_AR_GET_PAYMENT_DETAILS AS
2 /* $Header: gmfinrpb.pls 115.1 99/07/16 04:19:17 porting shi $ */
3 	CURSOR cur_get_payment_details(
4 		invoice_id NUMBER,
5 		start_date DATE,
6 		end_date DATE) IS
7 SELECT
8 	arc.CASH_RECEIPT_ID,
9 	arc.LAST_UPDATED_BY,
10 	arc.LAST_UPDATE_DATE,
11 	arc.CREATED_BY,
12 	arc.CREATION_DATE,
13 	arc.AMOUNT,
14 	arc.SET_OF_BOOKS_ID,
15 	arc.CURRENCY_CODE,
16 	arc.RECEIVABLES_TRX_ID,
17 	arc.PAY_FROM_CUSTOMER,
18 	arc.STATUS,
19 	arc.TYPE,
20 	arc.RECEIPT_NUMBER,
21 	arc.RECEIPT_DATE,
22 	arc.MISC_PAYMENT_SOURCE,
23 	arc.COMMENTS,
24 	arc.DISTRIBUTION_SET_ID,
25 	arc.REVERSAL_DATE,
26 	arc.REVERSAL_CATEGORY,
27 	arc.REVERSAL_REASON_CODE,
28 	arc.REVERSAL_COMMENTS,
29 	arc.EXCHANGE_RATE_TYPE,
30 	arc.EXCHANGE_RATE,
31 	arc.EXCHANGE_DATE,
32 	arc.REMITTANCE_BANK_ACCOUNT_ID,
33 	arc.CONFIRMED_FLAG,
34 	arc.CUSTOMER_BANK_ACCOUNT_ID,
35 	arc.CUSTOMER_SITE_USE_ID,
36 	arc.DEPOSIT_DATE,
37 	arc.PROGRAM_APPLICATION_ID,
38 	arc.PROGRAM_ID,
39 	arc.PROGRAM_UPDATE_DATE,
40 	arc.RECEIPT_METHOD_ID,
41 	arc.REQUEST_ID,
42 	arc.SELECTED_FOR_FACTORING_FLAG,
43 	arc.SELECTED_REMITTANCE_BATCH_ID,
44 	arc.FACTOR_DISCOUNT_AMOUNT,
45 	arc.VAT_TAX_ID,
46 	arr.RECEIVABLE_APPLICATION_ID,
47 	arr.LAST_UPDATED_BY,
48 	arr.LAST_UPDATE_DATE,
49 	arr.CREATED_BY,
50 	arr.CREATION_DATE,
51 	arr.AMOUNT_APPLIED,
52 	arr.GL_DATE,
53 	arr.CODE_COMBINATION_ID,
54 	arr.SET_OF_BOOKS_ID,
55 	arr.DISPLAY,
56 	arr.APPLY_DATE,
57 	arr.APPLICATION_TYPE,
58 	arr.STATUS,
59 	arr.PAYMENT_SCHEDULE_ID,
60 	arr.LAST_UPDATE_LOGIN,
61 	arr.CASH_RECEIPT_ID,
62 	arr.APPLIED_CUSTOMER_TRX_ID,
63 	arr.APPLIED_CUSTOMER_TRX_LINE_ID,
64 	arr.APPLIED_PAYMENT_SCHEDULE_ID,
65 	arr.CUSTOMER_TRX_ID,
66 	arr.LINE_APPLIED,
67 	arr.TAX_APPLIED,
68 	arr.FREIGHT_APPLIED,
69 	arr.RECEIVABLES_CHARGES_APPLIED,
70 	arr.ON_ACCOUNT_CUSTOMER,
71 	arr.RECEIVABLES_TRX_ID,
72 	arr.EARNED_DISCOUNT_TAKEN,
73 	arr.UNEARNED_DISCOUNT_TAKEN,
74 	arr.DAYS_LATE,
75 	arr.APPLICATION_RULE,
76 	arr.GL_POSTED_DATE,
77 	arr.COMMENTS,
78 	arr.POSTABLE,
79 	arr.POSTING_CONTROL_ID,
80 	arr.ACCTD_AMOUNT_APPLIED_FROM,
81 	arr.ACCTD_AMOUNT_APPLIED_TO,
82 	arr.ACCTD_EARNED_DISCOUNT_TAKEN,
83 	arr.CONFIRMED_FLAG,
84 	arr.PROGRAM_APPLICATION_ID,
85 	arr.PROGRAM_ID,
86 	arr.PROGRAM_UPDATE_DATE,
87 	arr.REQUEST_ID,
88 	arr.EARNED_DISCOUNT_CCID,
89 	arr.UNEARNED_DISCOUNT_CCID,
90 	arr.ACCTD_UNEARNED_DISCOUNT_TAKEN,
91 	arr.REVERSAL_GL_DATE,
92 	' ',
93 	arr.CASH_RECEIPT_HISTORY_ID
94 from    AR_CASH_RECEIPTS_ALL arc,
95 	AR_RECEIVABLE_APPLICATIONS_ALL arr
96 where   (arr.APPLIED_CUSTOMER_TRX_ID = invoice_id)
97 	AND (arr.last_update_date between nvl(start_date, arr.last_update_date)
98 	AND nvl(end_date, arr.last_update_date))
99 	AND (arc.CASH_RECEIPT_ID = arr.CASH_RECEIPT_ID);
100 
101 PROCEDURE get_payment_details
102 	(invoice_id                     IN      OUT     NUMBER,
103 	start_date                      IN      OUT     DATE,
104 	end_date                        IN      OUT     DATE,
105 	arc_CASH_RECEIPT_ID                     OUT     NUMBER,
106 	arc_LAST_UPDATED_BY                     OUT     NUMBER,
107 	arc_LAST_UPDATE_DATE                    OUT     DATE,
108 	arc_CREATED_BY                          OUT     NUMBER,
109 	arc_CREATION_DATE                       OUT     DATE,
110 	arc_AMOUNT                              OUT     NUMBER,
111 	arc_SET_OF_BOOKS_ID                     OUT     NUMBER,
112 	arc_CURRENCY_CODE                       OUT     VARCHAR2,
113 	arc_RECEIVABLES_TRX_ID                  OUT     NUMBER,
114 	arc_PAY_FROM_CUSTOMER                   OUT     NUMBER,
115 	arc_STATUS                              OUT     VARCHAR2,
116 	arc_TYPE                                OUT     VARCHAR2,
117 	arc_RECEIPT_NUMBER                      OUT     VARCHAR2,
118 	arc_RECEIPT_DATE                        OUT     DATE,
119 	arc_MISC_PAYMENT_SOURCE                 OUT     VARCHAR2,
120 	arc_COMMENTS                            OUT     VARCHAR2,
121 	arc_DISTRIBUTION_SET_ID                 OUT     NUMBER,
122 	arc_REVERSAL_DATE                       OUT     DATE,
123 	arc_REVERSAL_CATEGORY                   OUT     VARCHAR2,
124 	arc_REVERSAL_REASON_CODE                OUT     VARCHAR2,
125 	arc_REVERSAL_COMMENTS                   OUT     VARCHAR2,
126 	arc_EXCHANGE_RATE_TYPE                  OUT     VARCHAR2,
127 	arc_EXCHANGE_RATE                       OUT     NUMBER,
128 	arc_EXCHANGE_DATE                       OUT     DATE,
129 	arc_REMITTANCE_BANK_ACC_ID          	OUT     NUMBER,
130 	arc_CONFIRMED_FLAG                      OUT     VARCHAR2,
131 	arc_CUSTOMER_BANK_ACC_ID            	OUT     NUMBER,
132 	arc_CUSTOMER_SITE_USE_ID                OUT     NUMBER,
133 	arc_DEPOSIT_DATE                        OUT     DATE,
134 	arc_PROGRAM_APPLICATION_ID              OUT     NUMBER,
135 	arc_PROGRAM_ID                          OUT     NUMBER,
136 	arc_PROGRAM_UPDATE_DATE                 OUT     DATE,
137 	arc_RECEIPT_METHOD_ID                   OUT     NUMBER,
138 	arc_REQUEST_ID                          OUT     NUMBER,
139 	arc_SELECTED_FOR_FACT_FLAG         	OUT     VARCHAR2,
140 	arc_SELECTED_REMIT_BATCH_ID        	OUT     NUMBER,
141 	arc_FACTOR_DISCOUNT_AMOUNT              OUT     NUMBER,
142 	arc_VAT_TAX_ID                          OUT     NUMBER,
143 	arr_RECEIVABLE_APPLICATION_ID           OUT     NUMBER,
144 	arr_LAST_UPDATED_BY                     OUT     NUMBER,
145 	arr_LAST_UPDATE_DATE                    OUT     DATE,
146 	arr_CREATED_BY                          OUT     NUMBER,
147 	arr_CREATION_DATE                       OUT     DATE,
148 	arr_AMOUNT_APPLIED                      OUT     NUMBER,
149 	arr_GL_DATE                             OUT     DATE,
150 	arr_CODE_COMBINATION_ID                 OUT     NUMBER,
151 	arr_SET_OF_BOOKS_ID                     OUT     NUMBER,
152 	arr_DISPLAY                             OUT     VARCHAR2,
153 	arr_APPLY_DATE                          OUT     DATE,
154 	arr_APPLICATION_TYPE                    OUT     VARCHAR2,
155 	arr_STATUS                              OUT     VARCHAR2,
156 	arr_PAYMENT_SCHEDULE_ID                 OUT     NUMBER,
157 	arr_LAST_UPDATE_LOGIN                   OUT     NUMBER,
158 	arr_CASH_RECEIPT_ID                     OUT     NUMBER,
159 	arr_APPLIED_CUSTOMER_TRX_ID             OUT     NUMBER,
160 	arr_APPLIED_CUST_TRX_LINE_ID    	OUT     NUMBER,
161 	arr_APPLIED_PAYMENT_SCH_ID         	OUT     NUMBER,
162 	arr_CUSTOMER_TRX_ID                     OUT     NUMBER,
163 	arr_LINE_APPLIED                        OUT     NUMBER,
164 	arr_TAX_APPLIED                         OUT     NUMBER,
165 	arr_FREIGHT_APPLIED                     OUT     NUMBER,
166 	arr_RECEIVABLES_CHARGES_APPD         	OUT     NUMBER,
167 	arr_ON_ACCOUNT_CUSTOMER                 OUT     NUMBER,
168 	arr_RECEIVABLES_TRX_ID                  OUT     NUMBER,
169 	arr_EARNED_DISCOUNT_TAKEN               OUT     NUMBER,
170 	arr_UNEARNED_DISCOUNT_TAKEN             OUT     NUMBER,
171 	arr_DAYS_LATE                           OUT     NUMBER,
172 	arr_APPLICATION_RULE                    OUT     VARCHAR2,
173 	arr_GL_POSTED_DATE                      OUT     DATE,
174 	arr_COMMENTS                            OUT     VARCHAR2,
175 	arr_POSTABLE                            OUT     VARCHAR2,
176 	arr_POSTING_CONTROL_ID                  OUT     NUMBER,
177 	arr_ACCTD_AMOUNT_APPLIED_FROM           OUT     NUMBER,
178 	arr_ACCTD_AMOUNT_APPLIED_TO             OUT     NUMBER,
179 	arr_ACCTD_EARNED_DISC_TAKEN         	OUT     NUMBER,
180 	arr_CONFIRMED_FLAG                      OUT     VARCHAR2,
181 	arr_PROGRAM_APPLICATION_ID              OUT     NUMBER,
182 	arr_PROGRAM_ID                          OUT     NUMBER,
183 	arr_PROGRAM_UPDATE_DATE                 OUT     DATE,
184 	arr_REQUEST_ID                          OUT     NUMBER,
185 	arr_EARNED_DISCOUNT_CCID                OUT     NUMBER,
186 	arr_UNEARNED_DISCOUNT_CCID              OUT     NUMBER,
187 	arr_ACCTD_UNEARNED_DISC_TAKEN       	OUT     NUMBER,
188 	arr_REVERSAL_GL_DATE                    OUT     DATE,
189 	arr_REVERSAL_GL_DATE_CONTEXT            OUT     VARCHAR2,
190 	arr_CASH_RECEIPT_HISTORY_ID             OUT     NUMBER,
191 	row_to_fetch                    IN      OUT     NUMBER,
192 	error_status                            OUT     NUMBER ) IS
193 BEGIN
194 	if NOT cur_get_payment_details%ISOPEN
195 	then
196 		OPEN cur_get_payment_details( invoice_id, start_date, end_date);
197 	end if;
198 
199 	FETCH cur_get_payment_details INTO
200 	arc_CASH_RECEIPT_ID,
201 	arc_LAST_UPDATED_BY,
202 	arc_LAST_UPDATE_DATE,
203 	arc_CREATED_BY,
204 	arc_CREATION_DATE,
205 	arc_AMOUNT,
206 	arc_SET_OF_BOOKS_ID,
207 	arc_CURRENCY_CODE,
208 	arc_RECEIVABLES_TRX_ID,
209 	arc_PAY_FROM_CUSTOMER,
210 	arc_STATUS,
211 	arc_TYPE,
212 	arc_RECEIPT_NUMBER,
213 	arc_RECEIPT_DATE,
214 	arc_MISC_PAYMENT_SOURCE,
215 	arc_COMMENTS,
216 	arc_DISTRIBUTION_SET_ID,
217 	arc_REVERSAL_DATE,
218 	arc_REVERSAL_CATEGORY,
219 	arc_REVERSAL_REASON_CODE,
220 	arc_REVERSAL_COMMENTS,
221 	arc_EXCHANGE_RATE_TYPE,
222 	arc_EXCHANGE_RATE,
223 	arc_EXCHANGE_DATE,
224 	arc_REMITTANCE_BANK_ACC_ID,
225 	arc_CONFIRMED_FLAG,
226 	arc_CUSTOMER_BANK_ACC_ID,
227 	arc_CUSTOMER_SITE_USE_ID,
228 	arc_DEPOSIT_DATE,
229 	arc_PROGRAM_APPLICATION_ID,
230 	arc_PROGRAM_ID,
231 	arc_PROGRAM_UPDATE_DATE,
232 	arc_RECEIPT_METHOD_ID,
233 	arc_REQUEST_ID,
234 	arc_SELECTED_FOR_FACT_FLAG,
235 	arc_SELECTED_REMIT_BATCH_ID,
236 	arc_FACTOR_DISCOUNT_AMOUNT,
237 	arc_VAT_TAX_ID,
238 	arr_RECEIVABLE_APPLICATION_ID,
239 	arr_LAST_UPDATED_BY,
240 	arr_LAST_UPDATE_DATE,
241 	arr_CREATED_BY,
242 	arr_CREATION_DATE,
243 	arr_AMOUNT_APPLIED,
244 	arr_GL_DATE,
245 	arr_CODE_COMBINATION_ID,
246 	arr_SET_OF_BOOKS_ID,
247 	arr_DISPLAY,
248 	arr_APPLY_DATE,
249 	arr_APPLICATION_TYPE,
250 	arr_STATUS,
251 	arr_PAYMENT_SCHEDULE_ID,
252 	arr_LAST_UPDATE_LOGIN,
253 	arr_CASH_RECEIPT_ID,
254 	arr_APPLIED_CUSTOMER_TRX_ID,
255 	arr_APPLIED_CUST_TRX_LINE_ID,
256 	arr_APPLIED_PAYMENT_SCH_ID,
257 	arr_CUSTOMER_TRX_ID,
258 	arr_LINE_APPLIED,
259 	arr_TAX_APPLIED,
260 	arr_FREIGHT_APPLIED,
261 	arr_RECEIVABLES_CHARGES_APPD,
262 	arr_ON_ACCOUNT_CUSTOMER,
263 	arr_RECEIVABLES_TRX_ID,
264 	arr_EARNED_DISCOUNT_TAKEN,
265 	arr_UNEARNED_DISCOUNT_TAKEN,
266 	arr_DAYS_LATE,
267 	arr_APPLICATION_RULE,
268 	arr_GL_POSTED_DATE,
269 	arr_COMMENTS,
270 	arr_POSTABLE,
271 	arr_POSTING_CONTROL_ID,
272 	arr_ACCTD_AMOUNT_APPLIED_FROM,
273 	arr_ACCTD_AMOUNT_APPLIED_TO,
274 	arr_ACCTD_EARNED_DISC_TAKEN,
275 	arr_CONFIRMED_FLAG,
276 	arr_PROGRAM_APPLICATION_ID,
277 	arr_PROGRAM_ID,
278 	arr_PROGRAM_UPDATE_DATE,
279 	arr_REQUEST_ID,
280 	arr_EARNED_DISCOUNT_CCID,
281 	arr_UNEARNED_DISCOUNT_CCID,
282 	arr_ACCTD_UNEARNED_DISC_TAKEN,
283 	arr_REVERSAL_GL_DATE,
284 	arr_REVERSAL_GL_DATE_CONTEXT,
285 	arr_CASH_RECEIPT_HISTORY_ID;
286 
287 
288 	if cur_get_payment_details%NOTFOUND
289 	then
290 		error_status := 100;
291 		close cur_get_payment_details;
292 	end if;
293 
294 	if row_to_fetch = 1 and cur_get_payment_details%ISOPEN
295 	then
296 		close cur_get_payment_details;
297 	end if;
298 
299 	EXCEPTION
300 
301 		when others then
302 		error_status := SQLCODE;
303 END get_payment_details;
304 END GMF_AR_GET_PAYMENT_DETAILS;