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.ARRX_RC_UNAPP

Source


1 PACKAGE BODY ARRX_RC_UNAPP AS
2 /* $Header: ARRXUNAB.pls 120.4.12010000.2 2008/11/03 13:20:48 ankuagar ship $ */
3 
4 
5 /*========================================================================+
6  | PUBLIC PROCEDURE AR_UNAPP_REG                                          |
7  |                                                                        |
8  | DESCRIPTION                                                            |
9  |                                                                        |
10  |    This procedure is the inner procedure for the RXi report. It uses   |
11  |    the appropriate fa_rx_util_pkg routines to bild the report          |
12  |                                                                        |
13  | PSEUDO CODE/LOGIC                                                      |
14  |                                                                        |
15  | PARAMETERS                                                             |
16  |                                                                        |
17  |     request_id      IN       Request id for the concurrent program     |
18  |   and the other input parameters of the report                         |
19  |                                                                        |
20  | KNOWN ISSUES                                                           |
21  |                                                                        |
22  | NOTES                                                                  |
23  |                                                                        |
24  |                                                                        |
25  | MODIFICATION HISTORY                                                   |
26  | Date                  Author            Description of Changes         |
27  | 04-OCT-2004           rkader            Created                        |
28  | 23-Jun-2006           ggadhams          Made changes for Payment uptake|
29  |					   increased col sizes for Bug5244326|
30  *=======================================================================*/
31 
32 PROCEDURE ar_unapp_reg(
33           request_id             IN  NUMBER,
34           p_reporting_level      IN  VARCHAR2,
35           p_reporting_entity_id  IN  NUMBER,
36           p_sob_id               IN  NUMBER,
37           p_coa_id               IN  NUMBER,
38           p_co_seg_low           IN  VARCHAR2,
39           p_co_seg_high          IN  VARCHAR2,
40           p_gl_date_from         IN  DATE,
41           p_gl_date_to           IN  DATE,
42           p_entered_currency     IN  VARCHAR2,
43           p_batch_name_low       IN  VARCHAR2,
44           p_batch_name_high      IN  VARCHAR2,
45           p_batch_src_low        IN  VARCHAR2,
46           p_batch_src_high       IN  VARCHAR2,
47           p_customer_name_low    IN  VARCHAR2,
48           p_customer_name_high   IN  VARCHAR2,
49           p_customer_number_low  IN  VARCHAR2,
50           p_customer_number_high IN  VARCHAR2,
51           p_receipt_number_low   IN  VARCHAR2,
52           p_receipt_number_high  IN  VARCHAR2,
53           retcode                OUT NOCOPY NUMBER,
54           errbuf                 OUT NOCOPY NUMBER) IS
55 
56    l_profile_rsob_id       NUMBER := NULL;
57    l_client_info_rsob_id   NUMBER := NULL;
58 BEGIN
59 
60     fa_rx_util_pkg.debug('arrx_rc_unapp.ar_unapp_reg()+');
61 
62     /* Assign the parameters to the global variables
63     These will be used in the before_report trigger */
64     var.request_id                  :=    request_id ;
65     var.p_reporting_level           :=    p_reporting_level;
66     var.p_reporting_entity_id       :=    p_reporting_entity_id;
67     var.p_sob_id                    :=    p_sob_id ;
68     var.p_coa_id                    :=    p_coa_id ;
69     var.p_co_seg_low                :=    p_co_seg_low ;
70     var.p_co_seg_high               :=    p_co_seg_high ;
71     var.p_gl_date_from              :=    p_gl_date_from ;
72     var.p_gl_date_to                :=    p_gl_date_to ;
73     var.p_entered_currency          :=    p_entered_currency;
74     var.p_batch_name_low            :=    p_batch_name_low ;
75     var.p_batch_name_high           :=    p_batch_name_high ;
76     var.p_batch_src_low             :=    p_batch_src_low ;
77     var.p_batch_src_high            :=    p_batch_src_high ;
78     var.p_customer_name_low         :=    p_customer_name_low ;
79     var.p_customer_name_high        :=    p_customer_name_high ;
80     var.p_customer_number_low       :=    p_customer_number_low ;
81     var.p_customer_number_high      :=    p_customer_number_high ;
82     var.p_receipt_number_low        :=    p_receipt_number_low ;
83     var.p_receipt_number_high       :=    p_receipt_number_high ;
84 
85 
86 
87     fa_rx_util_pkg.debug('p_reporting_level = '||var.p_reporting_level);
88     fa_rx_util_pkg.debug('p_reporting_entity_id = '||var.p_reporting_entity_id);
89     fa_rx_util_pkg.debug('request_id = '||var.request_id);
90     fa_rx_util_pkg.debug('p_sob_id = '||var.p_sob_id);
91     fa_rx_util_pkg.debug('p_coa_id = '||var.p_coa_id);
92     fa_rx_util_pkg.debug('p_co_seg_low = '||var.p_co_seg_low);
93     fa_rx_util_pkg.debug('p_co_seg_high = '||var.p_co_seg_high);
94     fa_rx_util_pkg.debug('p_gl_date_from = '||var.p_gl_date_from);
95     fa_rx_util_pkg.debug('p_gl_date_to = '||var.p_gl_date_to);
96     fa_rx_util_pkg.debug('p_entered_currency = '||var.p_entered_currency);
97     fa_rx_util_pkg.debug('p_batch_name_low = '||var.p_batch_name_low);
98     fa_rx_util_pkg.debug('p_batch_name_high = '||var.p_batch_name_high);
99     fa_rx_util_pkg.debug('p_batch_src_low = '||var.p_batch_src_low);
100     fa_rx_util_pkg.debug('p_batch_src_high = '||var.p_batch_src_high);
101     fa_rx_util_pkg.debug('p_customer_name_low = '||var.p_customer_name_low);
102     fa_rx_util_pkg.debug('p_customer_name_high = '||var.p_customer_name_high);
103     fa_rx_util_pkg.debug('p_customer_number_low = '||var.p_customer_number_low);
104     fa_rx_util_pkg.debug('p_customer_number_high = '||var.p_customer_number_high);
105     fa_rx_util_pkg.debug('p_receipt_number_low  = '||var.p_receipt_number_low);
106     fa_rx_util_pkg.debug('p_receipt_number_high = '||var.p_receipt_number_high);
107 
108     /* Set the appropriate sob type into the global variable var.ca_sob_type */
109     select to_number(nvl(replace(substr(userenv('CLIENT_INFO'),45,10),' '),-99))
110     into  l_client_info_rsob_id
111     from  dual;
112 
113     fnd_profile.get('MRC_REPORTING_SOB_ID', l_profile_rsob_id);
114     IF (l_client_info_rsob_id = NVL(l_profile_rsob_id,-1)) OR
115         (l_client_info_rsob_id = -99)
116     THEN
117         fa_rx_util_pkg.debug('Setting the sob type to P');
118         var.ca_sob_type := 'P';
119     ELSE
120         fa_rx_util_pkg.debug('Setting the sob type to R');
121         var.ca_sob_id   := l_client_info_rsob_id;
122         var.ca_sob_type := 'R';
123     END IF;
124 
125     /* Initialize the request */
126     fa_rx_util_pkg.debug('Initializing the request');
127     fa_rx_util_pkg.init_request('arrx_rc_unapp.ar_unapp_reg',request_id, 'AR_RECEIPTS_REP_ITF');
128 
129     /* Assign the report triggers to this report.
130        NOTE:
131            before_report is assigned 'arrx_rc_unapp.before_report;'
132            bind is assigned 'arrx_rc_unapp.bind(:CURSOR_SELECT);'
133            after_fetch is assigned 'arrx_rc_unapp.after_fetch;'
134            Each trigger event is assigned with the full procedure name (including package name).
135            They end with a ';'.
136            The bind trigger requires one host variable ':CURSOR_SELECT'.
137      */
138      fa_rx_util_pkg.debug('Assigning the report triggers');
139      fa_rx_util_pkg.assign_report('AR UNAPPLIED',
140                                    true,
141                                   'arrx_rc_unapp.before_report;',
142                                   'arrx_rc_unapp.bind(:CURSOR_SELECT);',
143                                   'arrx_rc_unapp.after_fetch;',
144                                   null);
145 
146      /* Run the report */
147      fa_rx_util_pkg.debug('Running the report');
148      fa_rx_util_pkg.run_report('arrx_rc_unapp.ar_unapp_reg',retcode, errbuf);
149 
150      fa_rx_util_pkg.debug('arrx_rc_unapp.ar_unapp_reg()-');
151 
152 
153 EXCEPTION
154     WHEN OTHERS THEN
155       fa_rx_util_pkg.log(sqlcode);
156       fa_rx_util_pkg.log(sqlerrm);
157       fa_rx_util_pkg.debug(sqlcode);
158       fa_rx_util_pkg.debug(sqlerrm);
159       fa_rx_util_pkg.debug('arrx_rc_unapp.ar_unapp_reg(EXCEPTION)-');
160 END ar_unapp_reg;
161 
162 PROCEDURE before_report IS
163       CO_SEG_WHERE                  VARCHAR2(4000);
164       GL_DATE_WHERE                 VARCHAR2(4000);
165       GL_DATE_CLOSED_WHERE          VARCHAr2(4000);
166       CURRENCY_CODE_WHERE           VARCHAR2(4000);
167       BATCH_NAME_WHERE              VARCHAR2(4000);
168       BATCH_SRC_NAME_WHERE          VARCHAR2(4000);
169       CUSTOMER_NAME_WHERE           VARCHAR2(4000);
170       CUSTOMER_NUMBER_WHERE         VARCHAR2(4000);
171       RECEIPT_NUMBER_WHERE          VARCHAR2(4000);
172       CR_STATUS_DECODE              VARCHAR2(4000);
173       CRH_STATUS_DECODE             VARCHAR2(4000);
174       ON_ACC_AMT_DECODE             VARCHAR2(4000);
175       UNAPP_AMT_DECODE              VARCHAR2(4000);
176       CLAIM_AMT_DECODE              VARCHAR2(4000);
177       PREPAY_AMT_DECODE             VARCHAR2(4000);
178       TOTAL_UNRESOLVED_AMT_DECODE   VARCHAR2(4000);
179       FORMAT_CURRENCY_DECODE        VARCHAR2(4000);
180       L_CR_ORG_WHERE                VARCHAR2(4000);
181       L_CRH_ORG_WHERE               VARCHAR2(4000);
182       L_ABA_ORG_WHERE               VARCHAR2(4000);
183       L_CRH_CURR_ORG_WHERE          VARCHAR2(4000);
184       L_BAT_ORG_WHERE               VARCHAR2(4000);
185       L_BS_ORG_WHERE                VARCHAR2(4000);
186       L_RA_ORG_WHERE                VARCHAR2(4000);
187       L_PS_ORG_WHERE                VARCHAR2(4000);
188 BEGIN
189 
190      fa_rx_util_pkg.debug('arrx_rc_unapp.before_report()+');
191 
192      fa_rx_util_pkg.debug('Set of Books ID : '||var.p_sob_id);
193      fa_rx_util_pkg.debug('Get Chart of Accounts ID ');
194 
195      select CHART_OF_ACCOUNTS_ID,CURRENCY_CODE,NAME
196      into var.p_coa_id,var.functional_currency_code,var.organization_name
197      from GL_SETS_OF_BOOKS
198      where SET_OF_BOOKS_ID = var.p_sob_id;
199 
200      fa_rx_util_pkg.debug('Chart of Accounts ID : '||var.p_coa_id);
201      fa_rx_util_pkg.debug('Functional Currency  : '||var.functional_currency_code);
202      fa_rx_util_pkg.debug('Organization Name    : '||var.organization_name);
203 
204      XLA_MO_REPORTING_API.Initialize(var.p_reporting_level, var.p_reporting_entity_id, 'AUTO');
205 
206 
207      L_CR_ORG_WHERE          := XLA_MO_REPORTING_API.Get_Predicate('CR',NULL);
208      L_CRH_ORG_WHERE         := XLA_MO_REPORTING_API.Get_Predicate('CRH',NULL);
209 --     L_ABA_ORG_WHERE         := XLA_MO_REPORTING_API.Get_Predicate('ABA',NULL);
210      L_CRH_CURR_ORG_WHERE    := XLA_MO_REPORTING_API.Get_Predicate('CRH_CURR',NULL);
211      L_BAT_ORG_WHERE         := XLA_MO_REPORTING_API.Get_Predicate('BAT',NULL);
212      L_BS_ORG_WHERE          := XLA_MO_REPORTING_API.Get_Predicate('BS',NULL);
213      L_RA_ORG_WHERE          := XLA_MO_REPORTING_API.Get_Predicate('RA',NULL);
214      L_PS_ORG_WHERE          := XLA_MO_REPORTING_API.Get_Predicate('PS',NULL);
215 
216      fa_rx_util_pkg.debug('L_CR_ORG_WHERE : '||L_CR_ORG_WHERE);
217      fa_rx_util_pkg.debug('L_CRH_ORG_WHERE : '||L_CRH_ORG_WHERE);
218      fa_rx_util_pkg.debug('L_ABA_ORG_WHERE : '||L_ABA_ORG_WHERE);
219      fa_rx_util_pkg.debug('L_CRH_CURR_ORG_WHERE : '||L_CRH_CURR_ORG_WHERE);
220      fa_rx_util_pkg.debug('L_BAT_ORG_WHERE : '||L_BAT_ORG_WHERE);
221      fa_rx_util_pkg.debug('L_BS_ORG_WHERE : '||L_BS_ORG_WHERE);
222      fa_rx_util_pkg.debug('L_RA_ORG_WHERE : '||L_RA_ORG_WHERE);
223      fa_rx_util_pkg.debug('L_PS_ORG_WHERE : '||L_PS_ORG_WHERE);
224 
225      fa_rx_util_pkg.debug('Building Company Segment Where');
226 
227      IF var.p_co_seg_low IS NULL AND var.p_co_seg_high IS NULL THEN
228          CO_SEG_WHERE := NULL;
229      ELSIF var.p_co_seg_low IS NULL THEN
230          CO_SEG_WHERE := ' AND ' ||
231                 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
232                                 p_id_flex_code => 'GL#',
233                                 p_id_flex_num => var.p_coa_id,
234                                 p_table_alias => 'GC',
235                                 p_mode => 'WHERE',
236                                 p_qualifier => 'GL_BALANCING',
237                                 p_function => '<=',
238                                 p_operand1 => var.p_co_seg_high);
239      ELSIF var.p_co_seg_high IS NULL THEN
240          CO_SEG_WHERE := ' AND ' ||
241                 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
242                                 p_id_flex_code => 'GL#',
243                                 p_id_flex_num => var.p_coa_id,
244                                 p_table_alias => 'GC',
245                                 p_mode => 'WHERE',
246                                 p_qualifier => 'GL_BALANCING',
247                                 p_function => '>=',
248                                 p_operand1 => var.p_co_seg_low);
249      ELSE
250          CO_SEG_WHERE := ' AND ' ||
251                 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
252                                 p_id_flex_code => 'GL#',
253                                 p_id_flex_num => var.p_coa_id,
254                                 p_table_alias => 'GC',
255                                 p_mode => 'WHERE',
256                                 p_qualifier => 'GL_BALANCING',
257                                 p_function => 'BETWEEN',
258                                 p_operand1 => var.p_co_seg_low,
259                                 p_operand2 => var.p_co_seg_high);
260      END IF;
261      fa_rx_util_pkg.debug('CO_SEG_WHERE = '||substr(CO_SEG_WHERE,1,100));
262 
263      fa_rx_util_pkg.debug('Building GL Date Where ');
264 
265      IF var.p_gl_date_from IS NULL and var.p_gl_date_to IS NULL THEN
266          GL_DATE_WHERE := NULL;
267      ELSIF var.p_gl_date_from IS NULL THEN
268          GL_DATE_WHERE :=' AND RA.GL_DATE <= :p_gl_date_to';
269      ELSIF var.p_gl_date_to  IS NULL THEN
270          GL_DATE_WHERE :=' AND RA.GL_DATE >= :p_gl_date_from';
271      ELSE
272          GL_DATE_WHERE := ' AND RA.GL_DATE BETWEEN :p_gl_date_from AND :p_gl_date_to';
273      END IF;
274 
275      fa_rx_util_pkg.debug('GL_DATE_WHERE = '||GL_DATE_WHERE);
276 
277      fa_rx_util_pkg.debug('Building GL Date Closed Where ');
278 
279      IF var.p_gl_date_from IS NULL and var.p_gl_date_to IS NULL THEN
280          GL_DATE_CLOSED_WHERE := 'AND PS.GL_DATE_CLOSED = TO_DATE(''31-12-4712'',''DD-MM-YYYY'')';
281      ELSIF var.p_gl_date_from IS NOT NULL THEN
282          GL_DATE_CLOSED_WHERE := 'AND PS.GL_DATE_CLOSED >= :p_gl_date_from ';
283      ELSIF var.p_gl_date_to IS NOT NULL THEN
284          GL_DATE_CLOSED_WHERE := 'AND PS.GL_DATE_CLOSED >= :p_gl_date_to';
285      END IF;
286 
287      fa_rx_util_pkg.debug('GL_DATE_CLOSED_WHERE = '||GL_DATE_CLOSED_WHERE);
288 
289      fa_rx_util_pkg.debug('Building Currency Code Where ');
293          CURRENCY_CODE_WHERE := ' AND CR.CURRENCY_CODE = :p_entered_currency';
290      IF var.p_entered_currency IS NULL THEN
291          CURRENCY_CODE_WHERE := NULL;
292      ELSE
294      END IF;
295 
296      fa_rx_util_pkg.debug('Building Batch Name Where ');
297      IF var.p_batch_name_low IS NULL and var.p_batch_name_high IS NULL THEN
298          BATCH_NAME_WHERE := NULL;
299      ELSIF var.p_batch_name_low IS NULL THEN
300          BATCH_NAME_WHERE := ' AND BAT.NAME <= :p_batch_name_high';
301      ELSIF var.p_batch_name_high IS NULL THEN
302          BATCH_NAME_WHERE := ' AND BAT.NAME >= :p_batch_name_low';
303      ELSE
304          BATCH_NAME_WHERE := ' AND BAT.NAME BETWEEN :p_batch_name_low AND :p_batch_name_high';
305      END IF;
306 
307      fa_rx_util_pkg.debug('Building Batch Source Name Where ');
308      IF var.p_batch_src_low IS NULL and var.p_batch_src_high IS NULL THEN
309          BATCH_SRC_NAME_WHERE := NULL;
310      ELSIF var.p_batch_src_low IS NULL THEN
311          BATCH_SRC_NAME_WHERE := ' AND BS.NAME <= :p_batch_src_high';
312      ELSIF var.p_batch_src_high IS NULL THEN
313          BATCH_SRC_NAME_WHERE := ' AND BS.NAME >= :p_batch_src_low';
314      ELSE
315          BATCH_SRC_NAME_WHERE := ' AND BS.NAME BETWEEN :p_batch_src_low AND :p_batch_src_high';
316      END IF;
317 
318      fa_rx_util_pkg.debug('Building Customer Name Where');
319      IF var.p_customer_name_low IS NULL AND var.p_customer_name_high IS NULL THEN
320          CUSTOMER_NAME_WHERE := NULL;
321      ELSIF var.p_customer_name_low IS NULL THEN
322          CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME <= :p_customer_name_high';
323      ELSIF var.p_customer_name_high IS NULL THEN
324          CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME >= :p_customer_name_low';
325      ELSE
326          CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME BETWEEN :p_customer_name_low AND :p_customer_name_high';
327      END IF;
328 
329      fa_rx_util_pkg.debug('Building Customer Number Where');
330      IF var.p_customer_number_low IS NULL AND var.p_customer_number_high IS NULL THEN
331          CUSTOMER_NUMBER_WHERE := NULL;
332      ELSIF var.p_customer_number_low IS NULL THEN
333          CUSTOMER_NUMBER_WHERE := ' AND CUST.ACCOUNT_NUMBER <= :p_customer_number_high';
334      ELSIF var.p_customer_number_high IS NULL THEN
335          CUSTOMER_NUMBER_WHERE := ' AND CUST.ACCOUNT_NUMBER >= :p_customer_number_low';
336      ELSE
337          CUSTOMER_NUMBER_WHERE := ' AND CUST.ACCOUNT_NUMBER BETWEEN :p_customer_number_low AND :p_customer_number_high';
338      END IF;
339 
340      fa_rx_util_pkg.debug('Building Receipt Number Where');
341      IF var.p_receipt_number_low IS NULL AND var.p_receipt_number_high IS NULL THEN
342          RECEIPT_NUMBER_WHERE := NULL;
343      ELSIF var.p_receipt_number_low IS NULL THEN
344          RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER <= :p_receipt_number_high';
345      ELSIF var.p_receipt_number_high IS NULL THEN
346          RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER >= :p_receipt_number_low';
347      ELSE
348          RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER BETWEEN :p_receipt_number_low AND :p_receipt_number_high';
349      END IF;
350 
351      fa_rx_util_pkg.debug('Building the DECODE statements');
352 
353      CR_STATUS_DECODE := 'DECODE(CR.STATUS,''APP'',:L_APP,''NSF'',:L_NSF,''REV'',:L_REV,''STOP'',:L_STOP,''UNAPP'',:L_UNAPP,''UNID'',:L_UNID)';
354      CRH_STATUS_DECODE := 'DECODE(CRH.STATUS,''APPROVED'',:L_APPROVED,''CLEARED'',:L_CLEARED,''CONFIRMED'',:L_CONFIRMED,''REMITTED'',:L_REMITTED,''REVERSED'',:L_REVERSED)';
355      ON_ACC_AMT_DECODE := 'SUM(DECODE(RA.STATUS,
356             ''ACC'',DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),0))';
357 
358      UNAPP_AMT_DECODE  := 'SUM(DECODE(RA.STATUS,
359             ''UNAPP'',DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),
360             ''UNID'',DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),0))';
361 
362      CLAIM_AMT_DECODE := 'SUM(DECODE(RA.STATUS,
363             ''OTHER ACC'', DECODE(RA.APPLIED_PAYMENT_SCHEDULE_ID,-4,
364                    DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),0),0))';
365 
366      PREPAY_AMT_DECODE:= 'SUM(DECODE(RA.STATUS,
367             ''OTHER ACC'', DECODE(RA.APPLIED_PAYMENT_SCHEDULE_ID,-7,
368                    DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),0),0))';
369 
370      TOTAL_UNRESOLVED_AMT_DECODE := 'SUM(DECODE(RA.STATUS,
371             ''ACC'',DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),
372             ''UNAPP'',DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),
373             ''OTHER ACC'',DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),
374             ''UNID'',DECODE(:P_ENTERED_CURRENCY,NULL,RA.ACCTD_AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED),0))';
375 
376      FORMAT_CURRENCY_DECODE := 'DECODE(:P_ENTERED_CURRENCY,NULL,:P_FUNCTIONAL_CURRENCY,CR.CURRENCY_CODE)';
377 
378      /* Assign the Select List */
379      fa_rx_util_pkg.debug('Assign Select List');
380 
381      fa_rx_util_pkg.assign_column('10',null                        ,'ORGANIZATION_NAME'
382             ,'arrx_rc_unapp.var.organization_name'                 ,'VARCHAR2', 50);
383      fa_rx_util_pkg.assign_column('20',null                        ,'FUNCTIONAL_CURRENCY_CODE'
384             ,'arrx_rc_unapp.var.functional_currency_code'          ,'VARCHAR2', 15);
385      fa_rx_util_pkg.assign_column('30','BAT.BATCH_ID'              ,'BATCH_ID'
386             ,'arrx_rc_unapp.var.batch_id'                          ,'NUMBER');
387      fa_rx_util_pkg.assign_column('40','BAT.NAME'                  ,'BATCH_NAME'
391      fa_rx_util_pkg.assign_column('60','CR.RECEIPT_NUMBER'         ,'RECEIPT_NUMBER'
388            ,'arrx_rc_unapp.var.batch_name'                         ,'VARCHAR2', 20);
389      fa_rx_util_pkg.assign_column('50 ','CR.CASH_RECEIPT_ID'       ,'CASH_RECEIPT_ID'
390            ,'arrx_rc_unapp.var.cash_receipt_id'                    ,'NUMBER');
392            ,'arrx_rc_unapp.var.receipt_number'                     ,'VARCHAR2', 30);
393      fa_rx_util_pkg.assign_column('70','CR.CURRENCY_CODE'          ,'RECEIPT_CURRENCY_CODE'
394            ,'arrx_rc_unapp.var.receipt_currency_code'              ,'VARCHAR2', 15);
395      fa_rx_util_pkg.assign_column('80','CR.EXCHANGE_RATE'          ,'EXCHANGE_RATE'
396            ,'arrx_rc_unapp.var.exchange_rate'                      ,'NUMBER');
397      fa_rx_util_pkg.assign_column('90','CR.EXCHANGE_DATE'          ,'EXCHANGE_DATE'
398            ,'arrx_rc_unapp.var.exchange_date'                      ,'DATE');
399      fa_rx_util_pkg.assign_column('100','CR.EXCHANGE_RATE_TYPE'    ,'EXCHANGE_TYPE'
400            ,'arrx_rc_unapp.var.exchange_type'                      ,'VARCHAR2',30);
401      fa_rx_util_pkg.assign_column('110','DOCSEQ.NAME'              ,'DOC_SEQUENCE_NAME'
402            ,'arrx_rc_unapp.var.doc_sequence_name'                  ,'VARCHAR2',30);
403      fa_rx_util_pkg.assign_column('120','CR.DOC_SEQUENCE_VALUE'    ,'DOC_SEQUENCE_VALUE'
404            ,'arrx_rc_unapp.var.doc_sequence_value'                 ,'NUMBER');
405      fa_rx_util_pkg.assign_column('130','CR.DEPOSIT_DATE'          ,'DEPOSIT_DATE'
406            ,'arrx_rc_unapp.var.deposit_date'                       ,'DATE');
407      fa_rx_util_pkg.assign_column('140','CR.RECEIPT_DATE'          ,'RECEIPT_DATE'
408            ,'arrx_rc_unapp.var.receipt_date'                       ,'DATE');
409      fa_rx_util_pkg.assign_column('150',CR_STATUS_DECODE           ,'RECEIPT_STATUS'
410            ,'arrx_rc_unapp.var.receipt_status'                     ,'VARCHAR2',40);
411      fa_rx_util_pkg.assign_column('160','ABB.BANK_NAME'            ,'BANK_NAME'
412            ,'arrx_rc_unapp.var.bank_name'                          ,'VARCHAR2',60);
413      fa_rx_util_pkg.assign_column('170','ABB.BANK_NAME_ALT'        ,'BANK_NAME_ALT'
414            ,'arrx_rc_unapp.var.bank_name_alt'                      ,'VARCHAR2',320);
415      fa_rx_util_pkg.assign_column('180','ABB.BANK_BRANCH_NAME'     ,'BANK_BRANCH_NAME'
416            ,'arrx_rc_unapp.var.bank_branch_name'                   ,'VARCHAR2',60);
417      fa_rx_util_pkg.assign_column('190','ABB.BANK_BRANCH_NAME_ALT' ,'BANK_BRANCH_NAME_ALT'
418            ,'arrx_rc_unapp.var.bank_name_alt'                      ,'VARCHAR2',320);
419      fa_rx_util_pkg.assign_column('200','ABB.BANK_NUMBER'          ,'BANK_NUMBER'
420            ,'arrx_rc_unapp.var.bank_number'                        ,'VARCHAR2',30);
421      fa_rx_util_pkg.assign_column('210','ABB.BRANCH_NUMBER'        ,'BANK_BRANCH_NUMBER'
422            ,'arrx_rc_unapp.var.bank_branch_number'                 ,'VARCHAR2',25);
423      fa_rx_util_pkg.assign_column('220','ABA.BANK_ACCOUNT_NAME'    ,'BANK_ACCOUNT_NAME'
424            ,'arrx_rc_unapp.var.bank_account_name'                  ,'VARCHAR2', 80);
425      fa_rx_util_pkg.assign_column('230','ABA.BANK_ACCOUNT_NAME_ALT',    'BANK_ACCOUNT_NAME_ALT'
426            ,'arrx_rc_unapp.var.bank_account_name_alt'              ,'VARCHAR2', 320);
427      fa_rx_util_pkg.assign_column('240','ABA.CURRENCY_CODE'        ,'BANK_ACCOUNT_CURRENCY'
428            ,'arrx_rc_unapp.var.bank_account_currency'              ,'VARCHAR2',15);
429      fa_rx_util_pkg.assign_column('250','ARM.NAME'                 ,'RECEIPT_METHOD'
430            ,'arrx_rc_unapp.var.receipt_method'                     ,'VARCHAR2',30);
431      fa_rx_util_pkg.assign_column('260','CRH.CASH_RECEIPT_HISTORY_ID','CASH_RECEIPT_HISTORY_ID'
432            ,'arrx_rc_unapp.var.cash_receipt_history_id'            ,'NUMBER');
433      fa_rx_util_pkg.assign_column('270','MAX(RA.GL_DATE)'              ,'GL_DATE'
434            ,'arrx_rc_unapp.var.gl_date'                            ,'DATE');
435      fa_rx_util_pkg.assign_column('280','CRH_CURR.AMOUNT'          ,'RECEIPT_AMOUNT'
436            ,'arrx_rc_unapp.var.receipt_amount'                     ,'NUMBER');
437      fa_rx_util_pkg.assign_column('290','CRH_CURR.ACCTD_AMOUNT'    ,'ACCTD_RECEIPT_AMOUNT'
438            ,'arrx_rc_unapp.var.acctd_receipt_amount'               ,'NUMBER');
439      fa_rx_util_pkg.assign_column('300','CRH_CURR.FACTOR_DISCOUNT_AMOUNT','FACTOR_DISCOUNT_AMOUNT'
440            ,'arrx_rc_unapp.var.factor_discount_amount'             ,'NUMBER');
441      fa_rx_util_pkg.assign_column('310','CRH_CURR.ACCTD_FACTOR_DISCOUNT_AMOUNT','ACCTD_FACTOR_DISCOUNT_AMOUNT'
442            ,'arrx_rc_unapp.var.acctd_factor_discount_amount'       ,'NUMBER');
443      fa_rx_util_pkg.assign_column('320',CRH_STATUS_DECODE          ,'RECEIPT_HISTORY_STATUS'
444            ,'arrx_rc_unapp.var.receipt_history_status'             ,'VARCHAR2', 40);
445      fa_rx_util_pkg.assign_column('330','CUST.CUST_ACCOUNT_ID'     ,'CUSTOMER_ID'
446            ,'arrx_rc_unapp.var.customer_id'                        ,'NUMBER');
447      fa_rx_util_pkg.assign_column('340','NVL(SUBSTRB(PARTY.PARTY_NAME,1,50),:L_UNID_CUST)', 'CUSTOMER_NAME'
448            ,'arrx_rc_unapp.var.customer_name'                      ,'VARCHAR2', 50);
449      fa_rx_util_pkg.assign_column('350','DECODE(PARTY.PARTY_TYPE, ''ORGANIZATION'',PARTY.ORGANIZATION_NAME_PHONETIC, NULL)'                                                   ,'CUSTOMER_NAME_ALT'
450            ,'arrx_rc_unapp.var.customer_name_alt'                  ,'VARCHAR2',320);
451      fa_rx_util_pkg.assign_column('360','CUST.ACCOUNT_NUMBER'      ,'CUSTOMER_NUMBER'
452            ,'arrx_rc_unapp.var.customer_number'                    ,'VARCHAR2', 30);
453      fa_rx_util_pkg.assign_column('370','BS.NAME'                  ,'BATCH_SOURCE'
454            ,'arrx_rc_unapp.var.batch_source'                       ,'VARCHAR2',30);
458            ,'arrx_rc_unapp.var.unapp_amount'                       ,'NUMBER');
455      fa_rx_util_pkg.assign_column('380',ON_ACC_AMT_DECODE          ,'ON_ACCOUNT_AMOUNT'
456            ,'arrx_rc_unapp.var.on_acc_amount'                      ,'NUMBER');
457      fa_rx_util_pkg.assign_column('390',UNAPP_AMT_DECODE           ,'UNAPP_AMOUNT'
459      fa_rx_util_pkg.assign_column('400',CLAIM_AMT_DECODE           ,'CLAIM_AMOUNT'
460            ,'arrx_rc_unapp.var.claim_amount'                       ,'NUMBER');
461      fa_rx_util_pkg.assign_column('410',PREPAY_AMT_DECODE          ,'PREPAY_AMOUNT'
462            ,'arrx_rc_unapp.var.prepay_amount'                      ,'NUMBER');
463      fa_rx_util_pkg.assign_column('420',TOTAL_UNRESOLVED_AMT_DECODE,'TOTAL_UNRESOLVED_AMOUNT'
464            ,'arrx_rc_unapp.var.total_unresolved_amount'            ,'NUMBER');
465      fa_rx_util_pkg.assign_column('430',FORMAT_CURRENCY_DECODE     ,'FORMAT_CURRENCY_CODE'
466            ,'arrx_rc_unapp.var.format_currency_code'               ,'VARCHAR2',15);
467      fa_rx_util_pkg.assign_column('440','GC.CODE_COMBINATION_ID'   ,'ACCOUNT_CODE_COMBINATION_ID'
468            ,'arrx_rc_unapp.var.account_code_combination_id'        ,'NUMBER');
469      fa_rx_util_pkg.assign_column('450',NULL                       ,'DEBIT_BALANCING'
470            ,'arrx_rc_unapp.var.debit_balancing'                          ,'VARCHAR2',240);
471 
472 
473     /* Assign the From Clause */
474     fa_rx_util_pkg.debug(' Assigning the FROM CLAUSE');
475 
476     IF NVL(var.ca_sob_type,'P') = 'P' THEN
477        fa_rx_util_pkg.debug(' Report is run for Primary Set of Books');
478        fa_rx_util_pkg.From_Clause := '
479                           AR_CASH_RECEIPTS_ALL CR,
480                           FND_DOCUMENT_SEQUENCES DOCSEQ,
481 --                          AP_BANK_ACCOUNTS_ALL ABA,
482 --                          AP_BANK_BRANCHES ABB,
483  		          CE_BANK_ACCT_USES_OU_V USES,
484                           CE_BANK_ACCOUNTS ABA,
485                           CE_BANK_BRANCHES_V ABB,
486                           AR_RECEIPT_METHODS ARM,
487                           AR_CASH_RECEIPT_HISTORY_ALL CRH,
488                           GL_CODE_COMBINATIONS GC,
489                           HZ_CUST_ACCOUNTS CUST,
490                           HZ_PARTIES PARTY,
491                           AR_BATCHES_ALL BAT,
492                           AR_CASH_RECEIPT_HISTORY_ALL CRH_CURR,
493                           AR_BATCH_SOURCES_ALL BS,
494                           AR_RECEIVABLE_APPLICATIONS_ALL RA,
495                           AR_PAYMENT_SCHEDULES_ALL PS';
496     ELSE
497        fa_rx_util_pkg.debug(' Report is run for Reporting Set of Books');
498        fa_rx_util_pkg.From_Clause := '
499                           AR_CASH_RECEIPTS_ALL_MRC_V CR,
500                           FND_DOCUMENT_SEQUENCES DOCSEQ,
501 --                          AP_BANK_ACCOUNTS_ALL ABA,
502 	 		  CE_BANK_ACCT_USES_OU_V USES,
503 			  CE_BANK_ACCOUNTS ABA,
504                           CE_BANK_BRANCHES_V ABB,
505                           AR_RECEIPT_METHODS ARM,
506                           AR_CASH_RECEIPT_HIST_ALL_MRC_V CRH,
507                           GL_CODE_COMBINATIONS GC,
508                           HZ_CUST_ACCOUNTS CUST,
509                           HZ_PARTIES PARTY,
510                           AR_BATCHES_ALL_MRC_V BAT,
511                           AR_CASH_RECEIPT_HIST_ALL_MRC_V CRH_CURR,
512                           AR_BATCH_SOURCES_ALL BS,
513                           AR_RECEIVABLE_APPS_ALL_MRC_V RA,
514                           AR_PAYMENT_SCHEDULES_ALL_MRC_V PS';
515    END IF;
516 
517    fa_rx_util_pkg.debug('Assigning the Where Clause ');
518    fa_rx_util_pkg.Where_Clause := '
519                       NVL(RA.CONFIRMED_FLAG,''Y'') = ''Y''
520                 AND  RA.STATUS IN (''UNAPP'',''ACC'',''UNID'',''OTHER ACC'')
521                 AND  PS.CASH_RECEIPT_ID  = RA.CASH_RECEIPT_ID
522                 AND  PS.CLASS = ''PMT''
523                '|| GL_DATE_CLOSED_WHERE ||'
524                 AND  CR.CASH_RECEIPT_ID = RA.CASH_RECEIPT_ID
525                 AND  NVL(CR.CONFIRMED_FLAG,''Y'') = ''Y''
526                 AND  CR.DOC_SEQUENCE_ID = DOCSEQ.DOC_SEQUENCE_ID(+)
527                 AND  CR.REMIT_BANK_ACCT_USE_ID = USES.BANK_ACCT_USE_ID
528 
529 --Bug6214927, USES.BANK_ACCOUNT_ID should be matched with ABA.BANK_ACCOUNT_ID.
530 	        AND  USES.BANK_ACCOUNT_ID  = ABA.BANK_account_ID
531 --                AND  CR.ORG_ID = ABA.ORG_ID
532 --                AND  ABA.BANK_BRANCH_ID = ABB.BANK_BRANCH_ID
533 		AND  ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
534                 AND  CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
535                 AND  CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
536                 AND  CRH.FIRST_POSTED_RECORD_FLAG = ''Y''
537                 AND  CRH_CURR.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
538                 AND  CRH_CURR.CURRENT_RECORD_FLAG = ''Y''
539                 AND  CRH.BATCH_ID =  BAT.BATCH_ID(+)
540                 AND  GC.CODE_COMBINATION_ID = RA.CODE_COMBINATION_ID
541                 AND  BAT.BATCH_SOURCE_ID  = BS.BATCH_SOURCE_ID(+)
542                 AND  BAT.ORG_ID = BS.ORG_ID(+)
543                 AND  CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+)
544                 AND  CUST.PARTY_ID = PARTY.PARTY_ID(+)
545                '||L_CR_ORG_WHERE||'
546                '||L_CRH_ORG_WHERE||'
547                '||L_ABA_ORG_WHERE||'
548                '||L_CRH_CURR_ORG_WHERE||'
549                '||L_BAT_ORG_WHERE||'
550                '||L_BS_ORG_WHERE||'
551                '||L_RA_ORG_WHERE||'
552                '||L_PS_ORG_WHERE||'
556    fa_rx_util_pkg.Group_By_Clause := '
553                '||CO_SEG_WHERE||' '||GL_DATE_WHERE||' '||CURRENCY_CODE_WHERE||' '||BATCH_NAME_WHERE||' '||BATCH_SRC_NAME_WHERE||' '||CUSTOMER_NAME_WHERE||' '||CUSTOMER_NUMBER_WHERE||' '||RECEIPT_NUMBER_WHERE;
554 
555    fa_rx_util_pkg.debug('Assigning the Group By Clause ');
557                 BAT.BATCH_ID,
558                 BAT.NAME,
559                 CR.CASH_RECEIPT_ID,
560                 CR.RECEIPT_NUMBER,
561                 CR.CURRENCY_CODE,
562                 CR.EXCHANGE_RATE,
563                 CR.EXCHANGE_DATE,
564                 CR.EXCHANGE_RATE_TYPE,
565                 DOCSEQ.NAME,
566                 CR.DOC_SEQUENCE_VALUE,
567                 CR.DEPOSIT_DATE,
568                 CR.RECEIPT_DATE, '||
569                 CR_STATUS_DECODE||','||'
570                 ABB.BANK_NAME,
571                 ABB.BANK_NAME_ALT,
572                 ABB.BANK_BRANCH_NAME,
573                 ABB.BANK_BRANCH_NAME_ALT,
574                 ABB.BANK_NUMBER,
575                 --ABB.BANK_NUM,
576 		ABB.BRANCH_NUMBER,
577                 ABA.BANK_ACCOUNT_NAME,
578                 ABA.BANK_ACCOUNT_NAME_ALT,
579                 ABA.CURRENCY_CODE,
580                 ARM.NAME,
581                 CRH.CASH_RECEIPT_HISTORY_ID,
582                 CRH_CURR.AMOUNT,
583                 CRH_CURR.ACCTD_AMOUNT,
584                 CRH_CURR.FACTOR_DISCOUNT_AMOUNT,
585                 CRH_CURR.ACCTD_FACTOR_DISCOUNT_AMOUNT,'||
586                 CRH_STATUS_DECODE ||','||'
587                 CUST.CUST_ACCOUNT_ID,
588                 NVL(SUBSTRB(PARTY.PARTY_NAME,1,50),:L_UNID_CUST),
589                 DECODE(PARTY.PARTY_TYPE, ''ORGANIZATION'',PARTY.ORGANIZATION_NAME_PHONETIC, NULL),
590                 CUST.ACCOUNT_NUMBER,
591                 BS.NAME,
592                 GC.CODE_COMBINATION_ID , '||
593                 FORMAT_CURRENCY_DECODE;
594 
595    fa_rx_util_pkg.debug('Assigning the Having Clause ');
596    fa_rx_util_pkg.Having_Clause :=
597                  ON_ACC_AMT_DECODE ||' <> 0
598              OR ' || UNAPP_AMT_DECODE ||' <> 0
599              OR ' || CLAIM_AMT_DECODE ||' <> 0
600              OR ' || PREPAY_AMT_DECODE||' <> 0 ';
601 
602 
603 
604    fa_rx_util_pkg.debug('arrx_rc_unapp.before_report()-');
605 
606 
607 END before_report;
608 
609 PROCEDURE bind(c IN INTEGER) IS
610    l_approved                            VARCHAR2(80);
611    l_cleared                             VARCHAR2(80);
612    l_confirmed                           VARCHAR2(80);
613    l_remitted                            VARCHAR2(80);
614    l_reversed                            VARCHAR2(80);
615    l_app                                 VARCHAR2(80);
616    l_nsf                                 VARCHAR2(80);
617    l_rev                                 VARCHAR2(80);
618    l_stop                                VARCHAR2(80);
619    l_unapp                               VARCHAR2(80);
620    l_unid                                VARCHAR2(80);
621    l_unid_cust                           VARCHAR2(80);
622 begin
623       fa_rx_util_pkg.debug('arrx_rc_unapp.bind()+');
624 
625       IF var.p_gl_date_from IS NOT NULL THEN
626          dbms_sql.bind_variable(c, 'p_gl_date_from', var.p_gl_date_from);
627       END IF;
628       IF var.p_gl_date_to IS NOT NULL THEN
629          dbms_sql.bind_variable(c, 'p_gl_date_to', var.p_gl_date_to);
630       END IF;
631 
632       IF var.p_reporting_entity_id IS NOT NULL AND  var.p_reporting_level = '3000' THEN
633          dbms_sql.bind_variable(c, 'p_reporting_entity_id', var.p_reporting_entity_id);
634       END IF;
635       IF var.p_batch_name_low IS NOT NULL THEN
636          dbms_sql.bind_variable(c, 'p_batch_name_low',var.p_batch_name_low);
637       END IF;
638       IF var.p_batch_name_high IS NOT NULL THEN
639          dbms_sql.bind_variable(c, 'p_batch_name_high',var.p_batch_name_high);
640       END IF;
641       IF var.p_batch_src_low IS NOT NULL THEN
642          dbms_sql.bind_variable(c, 'p_batch_src_low',var.p_batch_src_low);
643       END IF;
644       IF var.p_batch_src_high IS NOT NULL THEN
645          dbms_sql.bind_variable(c, 'p_batch_src_high',var.p_batch_src_high);
646       END IF;
647       IF var.p_customer_name_low IS NOT NULL THEN
648          dbms_sql.bind_variable(c, 'p_customer_name_low',var.p_customer_name_low);
649       END IF;
650       IF var.p_customer_name_high IS NOT NULL THEN
651          dbms_sql.bind_variable(c, 'p_customer_name_high',var.p_customer_name_high);
652       END IF;
653       IF var.p_customer_number_low IS NOT NULL THEN
654          dbms_sql.bind_variable(c, 'p_customer_number_low',var.p_customer_number_low);
655       END IF;
656       IF var.p_customer_number_high IS NOT NULL THEN
657          dbms_sql.bind_variable(c, 'p_customer_number_high',var.p_customer_number_high);
658       END IF;
659       IF var.p_receipt_number_low IS NOT NULL THEN
660          dbms_sql.bind_variable(c, 'p_receipt_number_low',var.p_receipt_number_low);
661       END IF;
662       IF var.p_receipt_number_high IS NOT NULL THEN
663          dbms_sql.bind_variable(c, 'p_receipt_number_high',var.p_receipt_number_high);
664       END IF;
665 
666       dbms_sql.bind_variable(c, 'P_ENTERED_CURRENCY',var.p_entered_currency);
667       dbms_sql.bind_variable(c, 'P_FUNCTIONAL_CURRENCY',var.functional_currency_code);
668 
669       select MEANING into l_app from ar_lookups
670        where lookup_type='CHECK_STATUS' and lookup_code='APP';
671       select MEANING into l_nsf from ar_lookups
672        where lookup_type='CHECK_STATUS' and lookup_code='NSF';
673       select MEANING into l_rev from ar_lookups
674        where lookup_type='CHECK_STATUS' and lookup_code='REV';
675       select MEANING into l_stop from ar_lookups
676        where lookup_type='CHECK_STATUS' and lookup_code='STOP';
677       select MEANING into l_unapp from ar_lookups
678        where lookup_type='CHECK_STATUS' and lookup_code='UNAPP';
679       select MEANING into l_unid from ar_lookups
680        where lookup_type='CHECK_STATUS' and lookup_code='UNID';
681       dbms_sql.bind_variable(c, 'L_APP'  , l_app);
682       dbms_sql.bind_variable(c, 'L_NSF'  , l_nsf);
683       dbms_sql.bind_variable(c, 'L_REV'  , l_rev);
684       dbms_sql.bind_variable(c, 'L_STOP' , l_stop);
685       dbms_sql.bind_variable(c, 'L_UNAPP', l_unapp);
686       dbms_sql.bind_variable(c, 'L_UNID' , l_unid);
687 
688       select substr(MEANING,1,44) into l_unid_cust from ar_lookups
689       where lookup_type = 'SPECIAL_TYPES' and lookup_code = 'UNIDENTIFIED';
690       l_unid_cust := ' **** '||l_unid_cust;
691       dbms_sql.bind_variable(c, 'L_UNID_CUST' , l_unid_cust);
692 
693       select MEANING into L_APPROVED from ar_lookups
694        where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='APPROVED';
695       select MEANING into L_CLEARED from ar_lookups
696        where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='CLEARED';
697       select MEANING into L_CONFIRMED from ar_lookups
698        where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='CONFIRMED';
699       select MEANING into L_REMITTED from ar_lookups
700        where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='REMITTED';
701       select MEANING into L_REVERSED from ar_lookups
702        where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='REVERSED';
703       dbms_sql.bind_variable(c, 'L_APPROVED'   , L_APPROVED);
704       dbms_sql.bind_variable(c, 'L_CLEARED'    , L_CLEARED);
705       dbms_sql.bind_variable(c, 'L_CONFIRMED'  , L_CONFIRMED);
706       dbms_sql.bind_variable(c, 'L_REMITTED'   , L_REMITTED);
707       dbms_sql.bind_variable(c, 'L_REVERSED'   , L_REVERSED);
708 
709       fa_rx_util_pkg.debug('arrx_rc_unapp.bind()-');
710 
711 END bind;
712 PROCEDURE after_fetch IS
713 begin
714       fa_rx_util_pkg.debug('arrx_rc_unapp.after_fetch()+');
715 
716       var.debit_balancing := fa_rx_flex_pkg.get_value(
717                               p_application_id => 101,
718                               p_id_flex_code => 'GL#',
719                               p_id_flex_num => var.p_coa_id,
720                               p_qualifier => 'GL_BALANCING',
721                               p_ccid => var.account_code_combination_id);
722 
723       fa_rx_util_pkg.debug('arrx_rc_unapp.after_fetch()-');
724 end after_fetch;
725 
726 END ARRX_RC_UNAPP;