[Home] [Help]
PACKAGE BODY: APPS.APRX_PY
Source
1 package body aprx_py as
2 /* $Header: aprxpyb.pls 120.2 2005/07/29 11:27:43 mswamina noship $ */
3
4 --
5 -- Structure to hold values of all parameters
6 --
7 type param_t is record (
8 payment_date_start date,
9 payment_date_end date,
10 payment_currency_code varchar2(15),
11 payment_bank_account_name varchar2(80),
12 payment_method varchar2(25),
13 payment_type_flag varchar2(25),
14 payment_actual_date varchar2(20)
15 );
16 param param_t;
17
18 --
19 -- Main AP Payment RX Report function
20 --
21 procedure payment_register_run (
22 p_payment_date_start in date,
23 p_payment_date_end in date,
24 p_payment_currency_code in varchar2,
25 p_payment_bank_account_name in varchar2,
26 p_payment_method in varchar2,
27 p_payment_type_flag in varchar2,
28 request_id in number,
29 retcode out NOCOPY number,
30 errbuf out NOCOPY varchar2
31 )
32 is
33 begin
34 fa_rx_util_pkg.debug('aprx_py.payment_register_run()+');
35
36 --
37 -- Assign parameters to global variable
38 -- These values will be used within the before_report trigger
39 param.payment_date_start := Trunc(p_payment_date_start);
40 param.payment_date_end := Trunc(p_payment_date_end)+1-1/24/60/60;
41 param.payment_currency_code := p_payment_currency_code;
42 param.payment_bank_account_name := p_payment_bank_account_name;
43 param.payment_method := p_payment_method;
44 param.payment_type_flag := p_payment_type_flag;
45
46
47 --
48 -- Initialize request
49 fa_rx_util_pkg.init_request('aprx_py.payment_run', request_id);
50
51 --
52 -- Assign report triggers for this report.
53 -- This report has one section called PAYMENT
54 -- NOTE:
55 -- before_report is assigned 'aprx_py.register_before_report;'
56 -- bind is assigned 'aprx_py.register_bind(:CURSOR_SELECT);'
57 -- There is no trigger assigned for after_fetch or after_report
58 -- Each trigger event is assigned with the full procedure name (including package name).
59 -- They end with a ';'.
60 -- The bind trigger requires one host variable ':CURSOR_SELECT'.
61 fa_rx_util_pkg.assign_report('PAYMENT',
62 true,
63 'aprx_py.register_before_report;',
64 'aprx_py.register_bind(:CURSOR_SELECT);',
65 null, null);
66
67 --
68 -- Run the report. Make sure to pass as parameter the same
69 -- value passed to p_calling_proc in init_request().
70 fa_rx_util_pkg.run_report('aprx_py.payment_run', retcode, errbuf);
71
72 fa_rx_util_pkg.debug('aprx_py.payment_register_run()-');
73 end payment_register_run;
74
75
76 --
77 -- This procedure is a plug-in for the Thailand Payment Actual Report
78 --
79 procedure payment_actual_run (
80 p_payment_date_start in date,
81 p_payment_date_end in date,
82 p_payment_currency_code in varchar2,
83 p_payment_bank_account_name in varchar2,
84 p_payment_method in varchar2,
85 p_payment_type_flag in varchar2,
86 request_id in number,
87 retcode out NOCOPY number,
88 errbuf out NOCOPY varchar2
89 )
90 is
91 begin
92 fa_rx_util_pkg.debug('aprx_py.payment_actual_run()+');
93
94 --
95 -- Initialize the request
96 fa_rx_util_pkg.init_request('aprx_py.payment_actual_run', request_id);
97
98
99 --
100 -- Call the main payment report
101 payment_register_run( p_payment_date_start,
102 p_payment_date_end,
103 p_payment_currency_code,
104 p_payment_bank_account_name,
105 p_payment_method,
106 p_payment_type_flag,
107 request_id,
108 retcode,
109 errbuf);
110
111 --
112 -- Assign triggers specific to this report
113 -- Make sure that you make your assignment to the correct section ('PAYMENT')
114 fa_rx_util_pkg.assign_report('PAYMENT',
115 true,
116 'aprx_py.actual_before_report;',
117 null, null, null);
118
119 --
120 -- Run the report.
121 -- Make sure to pass the p_calling_proc assigned from within this procedure ('aprx_py.payment_actual_run')
122 fa_rx_util_pkg.run_report('aprx_py.payment_actual_run', retcode, errbuf);
123
124 fa_rx_util_pkg.debug('aprx_py.payment_actual_run()-');
125 end payment_actual_run;
126
127
128 --
129 -- This is the before report trigger for the main payment_run report.
130 procedure register_before_report
131 is
132 l_param_where varchar2(2000);
133 begin
134 fa_rx_util_pkg.debug('aprx_py.register_before_report()+');
135
136 --
137 -- Figure out NOCOPY the where clause for the parameters
138 --
139 l_param_where := null;
140
141 if param.payment_date_start = param.payment_date_end then
142 l_param_where := l_param_where || '
143 and ch.check_date = :b_payment_date_start';
144 elsif param.payment_date_start is not null and param.payment_date_end is not null then
145 l_param_where := l_param_where || '
146 and ch.check_date between :b_payment_date_start and :b_payment_date_end';
147 elsif param.payment_date_start is not null then
148 l_param_where := l_param_where || '
149 and ch.check_date >= :b_payment_date_start';
150 elsif param.payment_date_end is not null then
151 l_param_where := l_param_where || '
152 and ch.check_date <= :b_payment_date_end';
153 end if;
154
155 if param.payment_currency_code is not null then
156 l_param_where := l_param_where || '
157 and ch.currency_code = :b_payment_currency_code';
158 end if;
159
160 if param.payment_bank_account_name is not null then
161 l_param_where := l_param_where || '
162 and ch.bank_account_name = :b_payment_bank_account_name';
163 end if;
164
165 if param.payment_method is not null then
166 l_param_where := l_param_where || '
167 and ch.payment_method_lookup_code = :b_payment_method';
168 end if;
169
170 if param.payment_type_flag is not null then
171 l_param_where := l_param_where || '
172 and ch.payment_type_flag = :b_payment_type_flag';
173 end if;
174
175 --
176 -- Assign SELECT list
177 --
178 -- fa_rx_util_pkg.assign_column(#, select, insert, place, type, len);
179 -->>SELECT_START<<--
180 fa_rx_util_pkg.assign_column('ORGANIZATION_NAME', null, 'ORGANIZATION_NAME', 'aprx_py.var.ORGANIZATION_NAME', '');
181 fa_rx_util_pkg.assign_column('FUNCTIONAL_CURRENCY_CODE', null, 'FUNCTIONAL_CURRENCY_CODE', 'aprx_py.var.FUNCTIONAL_CURRENCY_CODE', 'VARCHAR2', 15);
182 fa_rx_util_pkg.assign_column('PAYMENT_NUMBER', 'CH.CHECK_NUMBER', 'PAYMENT_NUMBER', 'aprx_py.var.PAYMENT_NUMBER', 'NUMBER');
183 fa_rx_util_pkg.assign_column('PAYMENT_TYPE', 'CHTYPLKP.DISPLAYED_FIELD', 'PAYMENT_TYPE', 'aprx_py.var.PAYMENT_TYPE', 'VARCHAR2', 20);
184 fa_rx_util_pkg.assign_column('PAYMENT_DOC_SEQ_NAME', 'CHDOC.NAME', 'PAYMENT_DOC_SEQ_NAME', 'aprx_py.var.PAYMENT_DOC_SEQ_NAME', 'VARCHAR2', 30);
185 fa_rx_util_pkg.assign_column('PAYMENT_DOC_SEQ_VALUE', 'CH.DOC_SEQUENCE_VALUE', 'PAYMENT_DOC_SEQ_VALUE', 'aprx_py.var.PAYMENT_DOC_SEQ_VALUE', 'NUMBER');
186 fa_rx_util_pkg.assign_column('PAYMENT_DATE', 'CH.CHECK_DATE', 'PAYMENT_DATE', 'aprx_py.var.PAYMENT_DATE', 'DATE');
187 fa_rx_util_pkg.assign_column('PAYMENT_CURRENCY_CODE', 'CH.CURRENCY_CODE', 'PAYMENT_CURRENCY_CODE', 'aprx_py.var.PAYMENT_CURRENCY_CODE', 'VARCHAR2', 15);
188 fa_rx_util_pkg.assign_column('ORIG_PAYMENT_AMOUNT', 'CH.AMOUNT', 'ORIG_PAYMENT_AMOUNT', 'aprx_py.var.ORIG_PAYMENT_AMOUNT', 'NUMBER');
189 fa_rx_util_pkg.assign_column('ORIG_PAYMENT_BASE_AMOUNT', 'NVL(CH.BASE_AMOUNT, CH.AMOUNT)', 'ORIG_PAYMENT_BASE_AMOUNT', 'aprx_py.var.ORIG_PAYMENT_BASE_AMOUNT', 'NUMBER');
190 fa_rx_util_pkg.assign_column('PAYMENT_AMOUNT', 'DECODE(CH.VOID_DATE, NULL, CH.AMOUNT, 0)', 'PAYMENT_AMOUNT', 'aprx_py.var.PAYMENT_AMOUNT', 'NUMBER');
191 fa_rx_util_pkg.assign_column('PAYMENT_BASE_AMOUNT', 'DECODE(CH.VOID_DATE, NULL, NVL(CH.BASE_AMOUNT, CH.AMOUNT), 0)', 'PAYMENT_BASE_AMOUNT', 'aprx_py.var.PAYMENT_BASE_AMOUNT', 'NUMBER');
192 fa_rx_util_pkg.assign_column('PAYMENT_EXCHANGE_RATE', 'CH.EXCHANGE_RATE', 'PAYMENT_EXCHANGE_RATE', 'aprx_py.var.PAYMENT_EXCHANGE_RATE', 'NUMBER');
193 fa_rx_util_pkg.assign_column('PAYMENT_EXCHANGE_DATE', 'CH.EXCHANGE_DATE', 'PAYMENT_EXCHANGE_DATE', 'aprx_py.var.PAYMENT_EXCHANGE_DATE', 'DATE');
194 fa_rx_util_pkg.assign_column('PAYMENT_EXCHANGE_TYPE', 'CH.EXCHANGE_RATE_TYPE', 'PAYMENT_EXCHANGE_TYPE', 'aprx_py.var.PAYMENT_EXCHANGE_TYPE', 'VARCHAR2', 30);
195 fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_DATE', 'CH.CLEARED_DATE', 'PAYMENT_CLEARED_DATE', 'aprx_py.var.PAYMENT_CLEARED_DATE', 'DATE');
196 fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_AMOUNT', 'CH.CLEARED_AMOUNT', 'PAYMENT_CLEARED_AMOUNT', 'aprx_py.var.PAYMENT_CLEARED_AMOUNT', 'NUMBER');
197 fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_BASE_AMOUNT', 'CH.CLEARED_BASE_AMOUNT', 'PAYMENT_CLEARED_BASE_AMOUNT', 'aprx_py.var.PAYMENT_CLEARED_BASE_AMOUNT', 'NUMBER');
198 fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_EXC_RATE', 'CH.CLEARED_EXCHANGE_RATE', 'PAYMENT_CLEARED_EXC_RATE', 'aprx_py.var.PAYMENT_CLEARED_EXC_RATE', 'NUMBER');
199 fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_EXC_DATE', 'CH.CLEARED_EXCHANGE_DATE', 'PAYMENT_CLEARED_EXC_DATE', 'aprx_py.var.PAYMENT_CLEARED_EXC_DATE', 'DATE');
200 fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_EXC_TYPE', 'CH.CLEARED_EXCHANGE_RATE_TYPE', 'PAYMENT_CLEARED_EXC_TYPE', 'aprx_py.var.PAYMENT_CLEARED_EXC_TYPE', 'VARCHAR2', 30);
201 fa_rx_util_pkg.assign_column('PAYMENT_FUTURE_PAY_DUE_DATE', 'CH.FUTURE_PAY_DUE_DATE', 'PAYMENT_FUTURE_PAY_DUE_DATE', 'aprx_py.var.PAYMENT_FUTURE_PAY_DUE_DATE', 'DATE');
202 fa_rx_util_pkg.assign_column('PAYMENT_VOID_FLAG', 'DECODE(CH.VOID_DATE, NULL, :b_nls_no, :b_nls_yes)', 'PAYMENT_VOID_FLAG', 'aprx_py.var.PAYMENT_VOID_FLAG', 'VARCHAR2', 10);
203 fa_rx_util_pkg.assign_column('PAYMENT_PAY_METHOD', 'PMLKP.DISPLAYED_FIELD', 'PAYMENT_PAY_METHOD', 'aprx_py.var.PAYMENT_PAY_METHOD', 'VARCHAR2', 25);
204 fa_rx_util_pkg.assign_column('PAYMENT_STATUS', 'PSLKP.DISPLAYED_FIELD', 'PAYMENT_STATUS', 'aprx_py.var.PAYMENT_STATUS', 'VARCHAR2', 50);
205 fa_rx_util_pkg.assign_column('PAYMENT_DOC_NAME', 'CS1.NAME', 'PAYMENT_DOC_NAME', 'aprx_py.var.PAYMENT_DOC_NAME', 'VARCHAR2', 20);
206 fa_rx_util_pkg.assign_column('PAYMENT_DISBURSEMENT_TYPE', 'CSLKP.DISPLAYED_FIELD', 'PAYMENT_DISBURSEMENT_TYPE', 'aprx_py.var.PAYMENT_DISBURSEMENT_TYPE', 'VARCHAR2', 25);
207 fa_rx_util_pkg.assign_column('SUPPLIER_NAME', 'CH.VENDOR_NAME', 'SUPPLIER_NAME', 'aprx_py.var.SUPPLIER_NAME', 'VARCHAR2', 240);
208 fa_rx_util_pkg.assign_column('SUPPLIER_NAME_ALT', 'V.VENDOR_NAME_ALT', 'SUPPLIER_NAME_ALT', 'aprx_py.var.SUPPLIER_NAME_ALT', 'VARCHAR2', 320);
209 fa_rx_util_pkg.assign_column('SUPPLIER_SITE_CODE', 'CH.VENDOR_SITE_CODE', 'SUPPLIER_SITE_CODE', 'aprx_py.var.SUPPLIER_SITE_CODE', 'VARCHAR2', 15);
210 fa_rx_util_pkg.assign_column('SUPPLIER_SITE_CODE_ALT', 'VS.VENDOR_SITE_CODE_ALT', 'SUPPLIER_SITE_CODE_ALT', 'aprx_py.var.SUPPLIER_SITE_CODE_ALT', 'VARCHAR2', 320);
211 fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_LINE1', 'CH.ADDRESS_LINE1', 'SUPPLIER_ADDRESS_LINE1', 'aprx_py.var.SUPPLIER_ADDRESS_LINE1', 'VARCHAR2', 240);
212 fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_LINE2', 'CH.ADDRESS_LINE2', 'SUPPLIER_ADDRESS_LINE2', 'aprx_py.var.SUPPLIER_ADDRESS_LINE2', 'VARCHAR2', 240);
213 fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_LINE3', 'CH.ADDRESS_LINE3', 'SUPPLIER_ADDRESS_LINE3', 'aprx_py.var.SUPPLIER_ADDRESS_LINE3', 'VARCHAR2', 240);
214 fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_ALT', 'VS.ADDRESS_LINES_ALT', 'SUPPLIER_ADDRESS_ALT', 'aprx_py.var.SUPPLIER_ADDRESS_ALT', 'VARCHAR2', 560);
215 fa_rx_util_pkg.assign_column('SUPPLIER_CITY', 'CH.CITY', 'SUPPLIER_CITY', 'aprx_py.var.SUPPLIER_CITY', 'VARCHAR2', 25);
216 fa_rx_util_pkg.assign_column('SUPPLIER_STATE', 'CH.STATE', 'SUPPLIER_STATE', 'aprx_py.var.SUPPLIER_STATE', 'VARCHAR2', 150);
217 fa_rx_util_pkg.assign_column('SUPPLIER_PROVINCE', 'CH.PROVINCE', 'SUPPLIER_PROVINCE', 'aprx_py.var.SUPPLIER_PROVINCE', 'VARCHAR2', 150);
218 fa_rx_util_pkg.assign_column('SUPPLIER_POSTAL_CODE', 'CH.ZIP', 'SUPPLIER_POSTAL_CODE', 'aprx_py.var.SUPPLIER_POSTAL_CODE', 'VARCHAR2', 20);
219 fa_rx_util_pkg.assign_column('SUPPLIER_COUNTRY', 'CH.COUNTRY', 'SUPPLIER_COUNTRY', 'aprx_py.var.SUPPLIER_COUNTRY', 'VARCHAR2', 25);
220 fa_rx_util_pkg.assign_column('SUPPLIER_TERRITORY', 'VSTERR.TERRITORY_SHORT_NAME', 'SUPPLIER_TERRITORY', 'aprx_py.var.SUPPLIER_TERRITORY', 'VARCHAR2', 80);
221 fa_rx_util_pkg.assign_column('INT_BANK_NAME', 'B.BANK_NAME', 'INT_BANK_NAME', 'aprx_py.var.INT_BANK_NAME', 'VARCHAR2', 60);
222 fa_rx_util_pkg.assign_column('INT_BANK_NAME_ALT', 'B.BANK_NAME_ALT', 'INT_BANK_NAME_ALT', 'aprx_py.var.INT_BANK_NAME_ALT', 'VARCHAR2', 320);
223 fa_rx_util_pkg.assign_column('INT_BANK_NUMBER', 'B.BANK_NUMBER', 'INT_BANK_NUMBER', 'aprx_py.var.INT_BANK_NUMBER', 'VARCHAR2', 30);
224 fa_rx_util_pkg.assign_column('INT_BANK_BRANCH_NAME', 'B.BANK_BRANCH_NAME', 'INT_BANK_BRANCH_NAME', 'aprx_py.var.INT_BANK_BRANCH_NAME', 'VARCHAR2', 60);
225 fa_rx_util_pkg.assign_column('INT_BANK_BRANCH_NAME_ALT', 'B.BANK_BRANCH_NAME_ALT', 'INT_BANK_BRANCH_NAME_ALT', 'aprx_py.var.INT_BANK_BRANCH_NAME_ALT', 'VARCHAR2', 320);
226 fa_rx_util_pkg.assign_column('INT_BANK_NUM', 'CH.BANK_NUM', 'INT_BANK_NUM', 'aprx_py.var.INT_BANK_NUM', 'VARCHAR2', 30);
227 fa_rx_util_pkg.assign_column('INT_BANK_ACCOUNT_NAME', 'CH.BANK_ACCOUNT_NAME', 'INT_BANK_ACCOUNT_NAME', 'aprx_py.var.INT_BANK_ACCOUNT_NAME', 'VARCHAR2', 80);
228 fa_rx_util_pkg.assign_column('INT_BANK_ACCOUNT_NAME_ALT', 'BA.BANK_ACCOUNT_NAME_ALT', 'INT_BANK_ACCOUNT_NAME_ALT', 'aprx_py.var.INT_BANK_ACCOUNT_NAME_ALT', 'VARCHAR2', 320);
229 fa_rx_util_pkg.assign_column('INT_BANK_ACCOUNT_NUM', 'CH.BANK_ACCOUNT_NUM', 'INT_BANK_ACCOUNT_NUM', 'aprx_py.var.INT_BANK_ACCOUNT_NUM', 'VARCHAR2', 30);
230 fa_rx_util_pkg.assign_column('INT_BANK_CURRENCY_CODE', 'BA.CURRENCY_CODE', 'INT_BANK_CURRENCY_CODE', 'aprx_py.var.INT_BANK_CURRENCY_CODE', 'VARCHAR2', 15);
231 -->>SELECT_END<<--
232
233
234 --
235 -- Assign From Clause
236 --
237 fa_rx_util_pkg.From_Clause :=
238 'AP_CHECKS CH,
239 FND_DOCUMENT_SEQUENCES CHDOC,
240 AP_LOOKUP_CODES PMLKP,
241 AP_LOOKUP_CODES PSLKP,
242 AP_CHECK_STOCKS CS1,
243 AP_LOOKUP_CODES CSLKP,
244 AP_LOOKUP_CODES CHTYPLKP,
245 PO_VENDORS V,
246 PO_VENDOR_SITES VS,
247 FND_TERRITORIES_VL VSTERR,
248 AP_BANK_BRANCHES B,
249 AP_BANK_ACCOUNTS ba';
250
251 --
252 -- Assign Where Clause (including the where clause from the parameters)
253 --
254 fa_rx_util_pkg.Where_Clause :=
255 'CH.DOC_SEQUENCE_ID = CHDOC.DOC_SEQUENCE_ID(+)
256 AND
257 CH.CHECK_STOCK_ID = CS1.check_stock_id(+) AND
258 CS1.DISBURSEMENT_TYPE_LOOKUP_CODE = CSLKP.lookup_code(+) AND
259 CSLKP.lookup_type(+) = ''DISBURSEMENT TYPE'' AND
260 CH.PAYMENT_METHOD_LOOKUP_CODE = PMLKP.LOOKUP_CODE AND
261 PMLKP.LOOKUP_TYPE = ''PAYMENT METHOD'' AND
262 CH.STATUS_LOOKUP_CODE = PSLKP.LOOKUP_CODE AND
263 PSLKP.LOOKUP_TYPE = ''CHECK STATE'' AND
264 CH.PAYMENT_TYPE_FLAG = CHTYPLKP.LOOKUP_CODE AND
265 CHTYPLKP.LOOKUP_TYPE = ''PAYMENT TYPE''
266 AND
267 CH.VENDOR_ID = V.vendor_id(+) AND
268 CH.VENDOR_SITE_ID = VS.vendor_site_id(+) AND
269 CH.COUNTRY = VSTERR.TERRITORY_CODE(+)
270 AND
271 BA.BANK_BRANCH_ID = B.BANK_BRANCH_ID AND
272 CH.BANK_ACCOUNT_ID = BA.bank_account_id
273 '|| l_param_where;
274
275
276 --
277 -- Initialize some variables
278 SELECT sob.name, sob.currency_code
279 INTO aprx_py.var.ORGANIZATION_NAME, aprx_py.var.FUNCTIONAL_CURRENCY_CODE
280 FROM ap_system_parameters sys, gl_sets_of_books sob
281 WHERE sys.set_of_books_id = sob.set_of_books_id;
282
283 fa_rx_util_pkg.debug('aprx_py.register_before_report()-');
284 end register_before_report;
285
286
287 --
288 -- This is the bind trigger for the main payment_run report
289 procedure register_bind(c in integer)
290 is
291 l_nls_yes varchar2(10);
292 l_nls_no varchar2(10);
293 begin
294 fa_rx_util_pkg.debug('aprx_py.register_bind()+');
295
296 --
297 -- These bind variables were included in the WHERE clause.
298 --
299 if param.payment_date_start is not null then
300 fa_rx_util_pkg.debug('Binding b_payment_date_start.');
301 dbms_sql.bind_variable(c, 'b_payment_date_start', param.payment_date_start);
302 end if;
303 if param.payment_date_end is not null and param.payment_date_end <> param.payment_date_start then
304 fa_rx_util_pkg.debug('Binding b_payment_date_end.');
305 dbms_sql.bind_variable(c, 'b_payment_date_end', param.payment_date_end);
306 end if;
307
308 if param.payment_currency_code is not null then
309 fa_rx_util_pkg.debug('Binding b_payment_currency_code.');
310 dbms_sql.bind_variable(c, 'b_payment_currency_code', param.payment_currency_code);
311 end if;
312
313 if param.payment_bank_account_name is not null then
314 fa_rx_util_pkg.debug('Binding b_payment_bank_account_name.');
315 dbms_sql.bind_variable(c, 'b_payment_bank_account_name', param.payment_bank_account_name);
316 end if;
317
318 if param.payment_method is not null then
319 fa_rx_util_pkg.debug('Binding b_payment_method');
320 dbms_sql.bind_variable(c, 'b_payment_method', param.payment_method);
321 end if;
322
323 if param.payment_type_flag is not null then
324 fa_rx_util_pkg.debug('Binding b_payment_type_flag.');
325 dbms_sql.bind_variable(c, 'b_payment_type_flag', param.payment_type_flag);
326 end if;
327
328
329 --
330 -- This bind variable was included in the select list.
331 --
332 select substrb(meaning,1,10) into l_nls_yes from fnd_lookups
333 where lookup_type='YES_NO' and lookup_code='Y';
334 select substrb(meaning,1,10) into l_nls_no from fnd_lookups
335 where lookup_type='YES_NO' and lookup_code='N';
336 fa_rx_util_pkg.debug('Binding b_nls_yes and b_nls_no.');
337 dbms_sql.bind_variable(c, 'b_nls_yes', l_nls_yes);
338 dbms_sql.bind_variable(c, 'b_nls_no', l_nls_no);
339
340 fa_rx_util_pkg.debug('aprx_py.register_bind()-');
341 end register_bind;
342
343
344
345 --
346 -- This is the before report trigger for the payment_actual_run report
347 procedure actual_before_report
348 is
349 begin
350 fa_rx_util_pkg.debug('aprx_py.actual_before_report()+');
351
352 --
353 -- Assign another column specific to this report
354 fa_rx_util_pkg.assign_column('INV_PAY_AMOUNT', 'INVPAY.AMOUNT', 'INV_PAY_AMOUNT', 'aprx_py.var.INV_PAY_AMOUNT', 'NUMBER');
355 fa_rx_util_pkg.assign_column('INV_PAY_BASE_AMOUNT', 'nvl(INVPAY.PAYMENT_BASE_AMOUNT,INVPAY.AMOUNT)', 'INV_PAY_BASE_AMOUNT', 'aprx_py.var.INV_PAY_BASE_AMOUNT', 'NUMBER');
356 fa_rx_util_pkg.assign_column('INV_PAY_DISCOUNT_TAKEN', 'INVPAY.DISCOUNT_TAKEN', 'INV_PAY_DISCOUNT_TAKEN', 'aprx_py.var.INV_PAY_DISCOUNT_TAKEN', 'NUMBER');
357 fa_rx_util_pkg.assign_column('INVOICE_NUM', 'INV1.INVOICE_NUM', 'INVOICE_NUM', 'aprx_py.var.INVOICE_NUM', 'VARCHAR2', 50);
358 fa_rx_util_pkg.assign_column('INVOICE_DATE', 'INV1.INVOICE_DATE', 'INVOICE_DATE', 'aprx_py.var.INVOICE_DATE', 'DATE');
359 fa_rx_util_pkg.assign_column('INVOICE_CURRENCY_CODE', 'INV1.INVOICE_CURRENCY_CODE', 'INVOICE_CURRENCY_CODE', 'aprx_py.var.INVOICE_CURRENCY_CODE', 'VARCHAR2', 15);
360 fa_rx_util_pkg.assign_column('INVOICE_AMOUNT', 'INV1.INVOICE_AMOUNT', 'INVOICE_AMOUNT', 'aprx_py.var.INVOICE_AMOUNT', 'NUMBER');
361 fa_rx_util_pkg.assign_column('INVOICE_BASE_AMOUNT', 'NVL(INV1.BASE_AMOUNT, INV1.INVOICE_AMOUNT)', 'INVOICE_BASE_AMOUNT', 'aprx_py.var.INVOICE_BASE_AMOUNT', 'NUMBER');
362 fa_rx_util_pkg.assign_column('INVOICE_DESCRIPTION', 'INV1.DESCRIPTION', 'INVOICE_DESCRIPTION', 'aprx_py.var.INVOICE_DESCRIPTION', 'VARCHAR2', 240);
363
364
365
366 -- Add to the WHERE clause specific to this report
367 fa_rx_util_pkg.From_Clause := fa_rx_util_pkg.From_Clause ||',
368 AP_INVOICE_PAYMENTS INVPAY,
369 AP_INVOICES inv1';
370
371 fa_rx_util_pkg.Where_Clause := fa_rx_util_pkg.Where_Clause || '
372 and CH.STATUS_LOOKUP_CODE NOT IN (''VOIDED'', ''SET UP'')
373 and CH.CHECK_ID = INVPAY.CHECK_ID
374 and INVPAY.INVOICE_ID = INV1.invoice_id';
375
376 --
377 -- NOTE : Differences compared to original Japanese Actual Payment Report
378 -- The original used the set_of_books_id and included in the where clause a comparison with
379 -- ap_invoices.set_of_books_id
380 -- ap_bank_accounts.set_of_books_id
381 -- po_vendors.set_of_books_id
382 -- While the first two (invoices, bank accounts) do nothing for the select statement either
383 -- way, the last one (vendors) causes a problem in the original. Vendors is a shared entity
384 -- and you should not be looking at the set_of_books_id. I may be missing something, but
385 -- the column seems unnecessary.
386 --
387 -- Additionally, the original compared ap_checks.vendor_name to po_vendors.vendor_name.
388 -- This also is incorrect since users can change the vendor name when entering in a payment.
389 -- You should always compare ap_check.vendor_id to po_vendors.vendor_id when joining these
390 -- two tables.
391 --
392
393 fa_rx_util_pkg.debug('aprx_py.actual_before_report()-');
394 end actual_before_report;
395
396
397 end aprx_py;