Query to write Custom Invoice And Payment Report that matches with GL Liability Account Balance, GL Prepayment Account Balance, Expense Accounts etc.

Query to write Custom Invoice And Payment Report that matches with GL Liability Account Balance, GL Prepayment Account Balance, Expense Accounts etc.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
SELECT
    aipa.check_number,
    aipa.check_id,
    aia.payment_method_code,
    aia.payment_status_flag,
    aipa.status_lookup_code,
    aipa.reversal_flag,
    aia.wfapproval_status,
    haout.name,
    asup.vendor_name,
    asup.vendor_id,
    aia.invoice_num,
    aia.invoice_id,
    aida.invoice_line_number,
    aida.distribution_line_number,
    TO_CHAR(aia.invoice_date,'MM/DD/YYYY') invoice_date,
    TO_CHAR(aia.gl_date,'MM/DD/YYYY') inv_gl_date,
    TO_CHAR(aida.accounting_date,'MM/DD/YYYY') dist_acct_date,
    aida.period_name dist_period_name,
    aida.accrual_posted_flag,
    aida.cash_posted_flag,
    aida.posted_flag,
    aida.reversal_flag,
    adsa.distribution_set_name,
    gcc.concatenated_segments coa_segments,
    gcc.segment1 company,
    cb_company.description company_desc,
    gcc.segment2 account,
    cb_account.description account_desc,
    gcc.segment3 business_line,
    cb_business_line.description business_line_desc,
    gcc.segment4 counterparty,
    cb_counterparty.description counterparty_desc,
    gcc.segment5 deal,
    cb_deal.description deal_desc,
    gcc.segment6 project,
    cb_project.description proj_desc,
    gcc.segment7 partner,
    cb_partner.description partner_desc,
    gcc.segment8 ic_company,
    cb_ic_company.description ic_comp_desc,
    gcc.segment9 qb_legacy,
    cb_qb_legacy.description qb_legacy_desc,
    gcc.segment10 future,
    cb_future.description future_desc,
    aida.attribute1,
    aida.attribute2,
    aida.description,
    aia.invoice_currency_code,
    aia.exchange_rate,
    aia.invoice_amount,
    aida.amount dist_amt,
    nvl(aida.base_amount,aida.amount) dist_accounted_amt,
    aida.attribute4 ar_trx_num,
    assa.vendor_site_code vendor_site_name,
    aia.reference_key1 exp_rpt_id,
    aipa.check_amt,
    TO_CHAR(aipa.check_date,'MM/DD/YYYY') check_date,
    cbb.bank_name bank_name,
    DECODE(aipa.paycard_reference_id,NULL,cba.bank_account_name,aipa.bank_account_name) current_bank_account_name,
    TO_CHAR(aipa.void_date,'MM/DD/YYYY') void_date,
    aida.line_type_lookup_code dist_type
FROM
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE    fvs.flex_value_set_name = 'CB_FUTURE'
            AND  fvs.flex_value_set_id = ffv.flex_value_set_id) cb_future,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE    fvs.flex_value_set_name = 'CB_QB_LEGACY'
            AND  fvs.flex_value_set_id = ffv.flex_value_set_id) cb_qb_legacy,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE fvs.flex_value_set_name = 'CB_COMPANY'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_ic_company,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE fvs.flex_value_set_name = 'CB_PARTNER'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_partner,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE fvs.flex_value_set_name = 'CB_PROJECT'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_project,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE fvs.flex_value_set_name = 'CB_DEAL'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_deal,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE   fvs.flex_value_set_name = 'CB_COUNTERPARTY'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_counterparty,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE   fvs.flex_value_set_name = 'CB_BUSINESS_LINE'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_business_line,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE   fvs.flex_value_set_name = 'CB_ACCOUNT'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_account,
    (SELECT ffv.flex_value seg_value,ffv.description
        FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs
        WHERE   fvs.flex_value_set_name = 'CB_COMPANY'
            AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_company,
   --ap_checks_all aca,
    (SELECT DISTINCT
            aipa1.check_id check_id,
            aipa1.invoice_id invoice_id,
            nvl(aipa1.reversal_flag,'N') reversal_flag,
            aca1.check_number check_number,
            aca1.status_lookup_code status_lookup_code,
            aca1.amount check_amt,
            aca1.check_date check_date,
            aca1.paycard_reference_id paycard_reference_id,
            aca1.bank_account_name bank_account_name,
            aca1.void_date void_date,
            aca1.ce_bank_acct_use_id ce_bank_acct_use_id
        FROM ap_invoice_payments_all aipa1,ap_checks_all aca1
        WHERE aipa1.check_id = aca1.check_id) aipa,
    gl_code_combinations_kfv gcc,
    hr_all_organization_units_tl haout,
    ap_suppliers asup,
    ap_supplier_sites_all assa,
    ap_distribution_sets_all adsa,
    ap_invoice_distributions_all aida,
    ap_invoices_all aia,
    ce_bank_branches_v cbb,
    ce_bank_accounts cba,
    ce_bank_acct_uses_all cbau
WHERE   aia.invoice_id = aida.invoice_id
    AND aia.vendor_id = asup.vendor_id
    AND aia.vendor_site_id = assa.vendor_site_id
    AND aia.org_id = haout.organization_id
    AND aia.distribution_set_id = adsa.distribution_set_id (+)
    AND aida.dist_code_combination_id = gcc.code_combination_id
    AND gcc.segment1 = cb_company.seg_value (+)
    AND gcc.segment2 = cb_account.seg_value (+)
    AND gcc.segment3 = cb_business_line.seg_value (+)
    AND gcc.segment4 = cb_counterparty.seg_value (+)
    AND gcc.segment5 = cb_deal.seg_value (+)
    AND gcc.segment6 = cb_project.seg_value (+)
    AND gcc.segment7 = cb_partner.seg_value (+)
    AND gcc.segment8 = cb_ic_company.seg_value (+)
    AND gcc.segment9 = cb_qb_legacy.seg_value (+)
    AND gcc.segment10 = cb_future.seg_value (+)
    AND aia.invoice_id = aipa.invoice_id (+)
    --AND aipa.check_id = aca.check_id (+)
    AND cbb.branch_party_id (+) = cba.bank_branch_id
    AND aipa.ce_bank_acct_use_id = cbau.bank_acct_use_id (+)
    AND cbau.bank_account_id = cba.bank_account_id (+)
&gc_where_clause
ORDER BY aia.invoice_num

–Supporting Package for report query

— Package Specification

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE OR REPLACE PACKAGE APPS.xxcb_apinv_inquiry_rpt_pkg
AUTHID CURRENT_USER AS
--======================================================================+
-- Report Lexical Parameters
--======================================================================+
 gc_where_clause              VARCHAR2(32000) := ' AND 1 = 1';
--======================================================================+
-- Report Input Parameters                                              |
--======================================================================+
 P_FROM_INV_DATE              DATE         := NULL;
 P_TO_INV_DATE                DATE         := NULL;

 P_FROM_PAYMENT_DATE          DATE         := NULL;
 P_TO_PAYMENT_DATE            DATE         := NULL;

 P_SUPPLIER_ID               NUMBER       := NULL;
 P_FROM_GL_DATE              DATE         := NULL;
 P_TO_GL_DATE                DATE         := NULL;
 P_FROM_DIST_GL_DATE     	 DATE         := NULL;
 P_TO_DIST_GL_DATE           DATE         := NULL;
 P_GL_ACCOUNT                VARCHAR2(10) := NULL;
 P_COMPANY                   VARCHAR2(75) := NULL;
 P_COUNTERPARTY              VARCHAR2(75) := NULL;
 P_DEAL_CODE                 VARCHAR2(75) := NULL;
 P_PROJECT_CODE              VARCHAR2(75) := NULL;
 P_FROM_VOID_DATE          DATE         := NULL;
 P_TO_VOID_DATE            DATE         := NULL;
 P_INCLUDE_VOID_FLAG               VARCHAR2(10) := NULL;
 P_BUSINESS_LINE           VARCHAR2(10) := NULL;
 P_CHECK_NUMBER              NUMBER       := NULL;
 P_CHECK_NUMBER_TO           NUMBER       := NULL;
 P_AR_TRX_NUM                VARCHAR2(15) := NULL;
 P_RPT_OPTION                VARCHAR2(25) := NULL;
  P_BANK_ACCOUNT              NUMBER := NULL;
--======================================================================+
-- Report Functions
--======================================================================+
 FUNCTION before_report RETURN BOOLEAN;

END xxcb_apinv_inquiry_rpt_pkg;
/

— Package¬†Body

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
CREATE OR REPLACE PACKAGE BODY APPS.xxcb_apinv_inquiry_rpt_pkg AS
  C_LEVEL_STATEMENT     CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
  C_LEVEL_PROCEDURE     CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
  C_LEVEL_EVENT         CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
  C_LEVEL_EXCEPTION     CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
  C_LEVEL_ERROR         CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
  C_LEVEL_UNEXPECTED    CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
  C_LEVEL_LOG_DISABLED  CONSTANT NUMBER        := 99;
  C_DEFAULT_MODULE      CONSTANT VARCHAR2(240) := 'plsql.xxcb_apinv_inquiry_rpt_pkg';

  g_log_level           NUMBER;
  g_log_enabled         BOOLEAN;

  /*********************************************************************************************
  **                     PRIVATE PROCEDURES AND FUNCTIONS
  *********************************************************************************************/
 /* ------------------------------------------------------------------------------------------
    **   Procedure: trace
  	** Description:
 --------------------------------------------------------------------------------------------*/
 PROCEDURE trace(p_msg      IN     VARCHAR2,
                 p_level    IN     NUMBER,
                 p_module   IN     VARCHAR2 DEFAULT C_DEFAULT_MODULE)
 IS
 BEGIN
    IF (p_msg IS NULL AND p_level >= g_log_level) THEN
       fnd_log.message(p_level, p_module);
    ELSIF p_level >= g_log_level THEN
       fnd_log.string(p_level, p_module, p_msg);
    END IF;

 EXCEPTION
    WHEN OTHERS THEN
       RAISE;
 END trace;

 /* ------------------------------------------------------------------------------------------
    **   Procedure: print_logfile
  	** Description: Prints concurrent request logs
 --------------------------------------------------------------------------------------------*/
 PROCEDURE print_logfile(p_msg  IN  VARCHAR2) IS
 BEGIN
    fnd_file.put_line(fnd_file.log, p_msg);

 EXCEPTION
    WHEN OTHERS THEN
       RAISE;
 END print_logfile;

  /*********************************************************************************************
  **                     PUBLIC PROCEDURES AND FUNCTIONS
  *********************************************************************************************/


 /* ------------------------------------------------------------------------------------------
    **   Function: before_report
  	** Description: called before the report is run
  --------------------------------------------------------------------------------------------*/
 FUNCTION before_report RETURN BOOLEAN
 IS
   l_log_module               VARCHAR2(240);

 BEGIN
   IF g_log_enabled THEN
      l_log_module := C_DEFAULT_MODULE||'.before_report';
   END IF;

   IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
      trace(p_msg    => 'BEGIN of before_report',
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);
   END IF;

   IF (C_LEVEL_STATEMENT >= g_log_level) THEN
      trace(p_msg    => 'P_FROM_INV_DATE = ' || P_FROM_INV_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_TO_INV_DATE = ' || P_TO_INV_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_SUPPLIER_ID = ' || p_supplier_id,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_FROM_GL_DATE = ' || P_FROM_GL_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_TO_GL_DATE = ' || P_TO_GL_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);


      trace(p_msg    => 'P_FROM_DIST_GL_DATE = ' || P_FROM_DIST_GL_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_TO_DIST_GL_DATE = ' || P_TO_DIST_GL_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_INCLUDE_VOID_FLAG = ' || P_INCLUDE_VOID_FLAG,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_FROM_VOID_DATE = ' || P_FROM_VOID_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_TO_VOID_DATE = ' || P_TO_VOID_DATE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_GL_ACCOUNT = ' || p_gl_account,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_CHECK_NUMBER = ' || p_check_number,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_AR_TRX_NUM = ' || p_ar_trx_num,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_COMPANY = ' || P_COMPANY,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_COUNTERPARTY = ' || P_COUNTERPARTY,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_DEAL_CODE = ' || P_DEAL_CODE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_PROJECT_CODE = ' || P_PROJECT_CODE,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);

      trace(p_msg    => 'P_RPT_OPTION = ' || p_rpt_option,
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);
   END IF;

   print_logfile('>> get_where_clause');

   IF P_FROM_INV_DATE IS NOT NULL THEN
     gc_where_clause := ' AND TRUNC(aia.invoice_date) >= '''||P_FROM_INV_DATE ||''' ';
   END IF;

   IF P_TO_INV_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND TRUNC(aia.invoice_date) <= '''||P_TO_INV_DATE ||''' ';
   END IF;

  --Added by dliu@20150601 adding Parameters from
   IF P_FROM_PAYMENT_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||'  AND TRUNC(aipa.check_date) >= '''||P_FROM_PAYMENT_DATE ||''' ';
   END IF;

   IF P_TO_PAYMENT_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND TRUNC(aipa.check_date) <= '''||P_TO_PAYMENT_DATE ||''' ';
   END IF;

  if P_INCLUDE_VOID_FLAG = 'Only' then
       gc_where_clause := gc_where_clause ||'  AND aipa.status_lookup_code (+)      = '''||'VOIDED'||''' ';
  end if;

  if nvl(P_INCLUDE_VOID_FLAG,'No') = 'No' then
         gc_where_clause := gc_where_clause ||'  AND aipa.status_lookup_code (+)      != '''||'VOIDED'||'''  AND nvl(aipa.reversal_flag(+), '''||'N'||''') != '''||'Y'||''' ';
  end if;

   IF P_FROM_VOID_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||'  AND TRUNC(aipa.void_date) >= '''||P_FROM_VOID_DATE ||''' ';
   END IF;

   IF P_TO_VOID_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND TRUNC(aipa.void_date) <= '''||P_TO_VOID_DATE ||''' ';
   END IF;


   IF P_BUSINESS_LINE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND gcc.segment3 = '''||P_BUSINESS_LINE ||'''  ';
  END IF;
   --Added by dliu@20150601 adding Parameters from

  IF p_supplier_id IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND asup.vendor_id = '||p_supplier_id ||' ';
  END IF;

-- This is to match report with gl liability account activity report and for audit reason
  IF P_FROM_GL_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) >= '''||P_FROM_GL_DATE ||''' ';
  END IF;

  IF P_TO_GL_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) <= '''||P_TO_GL_DATE ||''' ';
  END IF;

  IF P_FROM_DIST_GL_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) >= '''||P_FROM_DIST_GL_DATE ||''' ';
  END IF;

  IF P_TO_DIST_GL_DATE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) <= '''||P_TO_DIST_GL_DATE ||''' ';
  END IF;

  IF p_gl_account IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND gcc.segment2 = '''||p_gl_account ||'''  ';
  END IF;

    IF p_check_number IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND aipa.check_number >= '||p_check_number ||'  ';
  END IF;

   IF p_check_number_to IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND aipa.check_number <= '||p_check_number_to ||'  ';
  END IF;

  IF p_ar_trx_num IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND aida.attribute4 = '''|| p_ar_trx_num ||'''  ';
  END IF;

  IF P_COMPANY IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND gcc.segment1 = '''||substr(P_COMPANY,1,2) ||'''  ';
  END IF;

  IF P_COUNTERPARTY IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND gcc.segment4 = '''||substr(P_COUNTERPARTY,1,3) ||'''  ';
  END IF;

    IF P_DEAL_CODE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND gcc.segment5 = '''||substr(P_DEAL_CODE,1,3) ||'''  ';
  END IF;

    IF P_PROJECT_CODE IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND gcc.segment6 = '''||substr(P_PROJECT_CODE,1,2) ||'''  ';
  END IF;

   IF P_BANK_ACCOUNT IS NOT NULL THEN
     gc_where_clause := gc_where_clause ||' AND aipa.CE_BANK_ACCT_USE_ID = '||P_BANK_ACCOUNT ||' ';
   END IF;

  IF p_rpt_option = 'NO_EXP_RPT' THEN
     gc_where_clause := gc_where_clause ||' AND aia.invoice_num NOT LIKE ''EXP%''  ';
  ELSIF p_rpt_option = 'EXP_RPT' THEN
     gc_where_clause := gc_where_clause ||' AND aia.invoice_num LIKE ''EXP%''  ';
  END IF;

   print_logfile('gc_where_clause: '|| gc_where_clause);
   print_logfile('<< get_where_clause');

   IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
      trace(p_msg    => 'END of before_report ',
            p_level  => C_LEVEL_PROCEDURE,
            p_module => l_log_module);
   END IF;

   RETURN TRUE;

 EXCEPTION
    WHEN OTHERS THEN
       RAISE;
 END before_report;

           /*********** Initialization routine **********/

/* ------------------------------------------------------------------------------------------
 ** Following code is executed when the package body is referenced for the first time
--------------------------------------------------------------------------------------------*/
BEGIN
  g_log_level      := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
  g_log_enabled    := fnd_log.test(log_level => g_log_level,
                                    MODULE    => C_DEFAULT_MODULE);
  IF NOT g_log_enabled  THEN
    g_log_level := C_LEVEL_LOG_DISABLED;
  END IF;

END xxcb_apinv_inquiry_rpt_pkg;
/

Regards Yogesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s