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

Source


1 package body aprx_py as
2 /* $Header: aprxpyb.pls 120.8.12020000.3 2012/08/08 21:33:45 lnilacan ship $ */
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 	ledger_id            		number,     /* bug8760710 */
15       org_id				number, 	/* bug 13941628 */
16 	payment_actual_date		varchar2(20)
17 );
18 param param_t;
19 
20 --
21 -- Main AP Payment RX Report function
22 --
23 procedure payment_register_run (
24 	p_payment_date_start in date,
25 	p_payment_date_end in date,
26 	p_payment_currency_code in varchar2,
27 	p_payment_bank_account_name in varchar2,
28 	p_payment_method in varchar2,
29 	p_payment_type_flag in varchar2,
30 	p_ledger_id     in      number,         /* bug8760710 */
31       p_org_id	in number, /* bug 13941628 */
32 	request_id	in	number,
33 	retcode	out NOCOPY	number,
34 	errbuf	out NOCOPY	varchar2
35 )
36 is
37 begin
38   fa_rx_util_pkg.debug('aprx_py.payment_register_run()+');
39 
40   --
41   -- Assign parameters to global variable
42   -- These values will be used within the before_report trigger
43   param.payment_date_start := Trunc(p_payment_date_start);
44   param.payment_date_end := Trunc(p_payment_date_end)+1-1/24/60/60;
45   param.payment_currency_code := p_payment_currency_code;
46   param.payment_bank_account_name := p_payment_bank_account_name;
47   param.payment_method := p_payment_method;
48   param.payment_type_flag := p_payment_type_flag;
49   param.ledger_id := p_ledger_id;           /* bug8760710 */
50   param.org_id := p_org_id;			/* bug 13941628 */
51 
52 
53   --
54   -- Initialize request
55   fa_rx_util_pkg.init_request('aprx_py.payment_run', request_id);
56 
57   --
58   -- Assign report triggers for this report.
59   -- This report has one section called PAYMENT
60   -- NOTE:
61   --    before_report is assigned 'aprx_py.register_before_report;'
62   --    bind is assigned 'aprx_py.register_bind(:CURSOR_SELECT);'
63   --    There is no trigger assigned for after_fetch or after_report
64   --  Each trigger event is assigned with the full procedure name (including package name).
65   --  They end with a ';'.
66   --  The bind trigger requires one host variable ':CURSOR_SELECT'.
67   fa_rx_util_pkg.assign_report('PAYMENT',
68 		true,
69 		'aprx_py.register_before_report;',
70 		'aprx_py.register_bind(:CURSOR_SELECT);',
71 		null, null);
72 
73   --
74   -- Run the report. Make sure to pass as parameter the same
75   -- value passed to p_calling_proc in init_request().
76   fa_rx_util_pkg.run_report('aprx_py.payment_run', retcode, errbuf);
77 
81 
78   fa_rx_util_pkg.debug('aprx_py.payment_register_run()-');
79 end payment_register_run;
80 
82 --
83 -- This procedure is a plug-in for the Thailand Payment Actual Report
84 --
85 procedure payment_actual_run (
86 	p_payment_date_start 	in date,
87 	p_payment_date_end 	in date,
88 	p_payment_currency_code in varchar2,
89 	p_payment_bank_account_name in varchar2,
90 	p_payment_method 	in varchar2,
91 	p_payment_type_flag 	in varchar2,
92 	p_ledger_id             in number,         /* bug8760710 */
93 	request_id		in number,
94 	retcode	out NOCOPY	number,
95 	errbuf	out NOCOPY	varchar2
96 )
97 is
98 begin
99   fa_rx_util_pkg.debug('aprx_py.payment_actual_run()+');
100 
101   --
102   -- Initialize the request
103   fa_rx_util_pkg.init_request('aprx_py.payment_actual_run', request_id);
104 
105 
106   --
107   -- Call the main payment report
108   payment_register_run(	p_payment_date_start,
109 		p_payment_date_end,
110 		p_payment_currency_code,
111 		p_payment_bank_account_name,
112 		p_payment_method,
113 		p_payment_type_flag,
114 		p_ledger_id,                   /* bug8760710 */
115             null, 				/* bug 13941628 */
116 		request_id,
117 		retcode,
118 		errbuf);
119 
120   --
121   -- Assign triggers specific to this report
122   -- Make sure that you make your assignment to the correct section ('PAYMENT')
123   fa_rx_util_pkg.assign_report('PAYMENT',
124 		true,
125 		'aprx_py.actual_before_report;',
126 		null, null, null);
127 
128   --
129   -- Run the report.
130   -- Make sure to pass the p_calling_proc assigned from within this procedure ('aprx_py.payment_actual_run')
131   fa_rx_util_pkg.run_report('aprx_py.payment_actual_run', retcode, errbuf);
132 
133   fa_rx_util_pkg.debug('aprx_py.payment_actual_run()-');
134 end payment_actual_run;
135 
136 
137 --
138 -- This is the before report trigger for the main payment_run report.
139 procedure register_before_report
140 is
141   l_param_where varchar2(2000);
142 begin
143   fa_rx_util_pkg.debug('aprx_py.register_before_report()+');
144 
145   --
146   -- Figure out NOCOPY the where clause for the parameters
147   --
148   l_param_where := null;
149 
150   if param.payment_date_start = param.payment_date_end then
151 	l_param_where := l_param_where || '
152 and	ch.check_date = :b_payment_date_start';
153   elsif param.payment_date_start is not null and param.payment_date_end is not null then
154 	l_param_where := l_param_where || '
155 and	ch.check_date between :b_payment_date_start and :b_payment_date_end';
156   elsif param.payment_date_start is not null then
157 	l_param_where := l_param_where || '
158 and	ch.check_date >= :b_payment_date_start';
159   elsif param.payment_date_end is not null then
160 	l_param_where := l_param_where || '
161 and	ch.check_date <= :b_payment_date_end';
162   end if;
163 
164   if param.payment_currency_code is not null then
165 	l_param_where := l_param_where || '
166 and	ch.currency_code = :b_payment_currency_code';
167   end if;
168 
169   if param.payment_bank_account_name is not null then
170 	l_param_where := l_param_where || '
171 and	ch.bank_account_name = :b_payment_bank_account_name';
172   end if;
173 
174   if param.payment_method is not null then
175 	l_param_where := l_param_where ||
176 /* Commented and added for bug 12432604 */
177 /*and	ch.payment_method_lookup_code = :b_payment_method'; */
178 ' and   ch.payment_method_code = :b_payment_method';
179   end if;
180 
181   if param.payment_type_flag is not null then
182 	l_param_where := l_param_where || '
183 and	ch.payment_type_flag = :b_payment_type_flag';
184   end if;
185 
186  /* Start of bug8760710 */
190       l_param_where:=l_param_where||'
187     /* bug13941628, ensure it is not -9999 either*/
188 
189   if  ( nvl(param.ledger_id,-9999) <> -9999) then
191        and sys.set_of_books_id =:b_ledger_id ';
192   end if;
193   /* bug13941628 */
194   /* End of bug8760710 */
195 
196   /* bug13941628, if org_id is not null or -9999, then report is running for OU level*/
197 
198   if  (nvl(param.org_id,-9999) <> -9999) then
199       l_param_where:=l_param_where||'
200        and sys.org_id = :b_org_id ';
201   end if;
202 
203   /* bug13941628 */
204 
205 
206 
207 
208   --
209   -- Assign SELECT list
210   --
211   -- fa_rx_util_pkg.assign_column(#, select, insert, place, type, len);
212 -->>SELECT_START<<--
213 	fa_rx_util_pkg.assign_column('ORGANIZATION_NAME', null, 'ORGANIZATION_NAME', 'aprx_py.var.ORGANIZATION_NAME', '');
217 	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);
214 	fa_rx_util_pkg.assign_column('FUNCTIONAL_CURRENCY_CODE', null, 'FUNCTIONAL_CURRENCY_CODE', 'aprx_py.var.FUNCTIONAL_CURRENCY_CODE', 'VARCHAR2', 15);
215 	fa_rx_util_pkg.assign_column('PAYMENT_NUMBER', 'CH.CHECK_NUMBER', 'PAYMENT_NUMBER', 'aprx_py.var.PAYMENT_NUMBER', 'NUMBER');
216 	fa_rx_util_pkg.assign_column('PAYMENT_TYPE', 'CHTYPLKP.DISPLAYED_FIELD', 'PAYMENT_TYPE', 'aprx_py.var.PAYMENT_TYPE', 'VARCHAR2', 20);
218 	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');
219 	fa_rx_util_pkg.assign_column('PAYMENT_DATE', 'CH.CHECK_DATE', 'PAYMENT_DATE', 'aprx_py.var.PAYMENT_DATE', 'DATE');
220 	fa_rx_util_pkg.assign_column('PAYMENT_CURRENCY_CODE', 'CH.CURRENCY_CODE', 'PAYMENT_CURRENCY_CODE', 'aprx_py.var.PAYMENT_CURRENCY_CODE', 'VARCHAR2', 15);
221 	fa_rx_util_pkg.assign_column('ORIG_PAYMENT_AMOUNT', 'CH.AMOUNT', 'ORIG_PAYMENT_AMOUNT', 'aprx_py.var.ORIG_PAYMENT_AMOUNT', 'NUMBER');
222 	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');
223 	fa_rx_util_pkg.assign_column('PAYMENT_AMOUNT', 'DECODE(CH.VOID_DATE, NULL, CH.AMOUNT, 0)', 'PAYMENT_AMOUNT', 'aprx_py.var.PAYMENT_AMOUNT', 'NUMBER');
224 	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');
225 	fa_rx_util_pkg.assign_column('PAYMENT_EXCHANGE_RATE', 'CH.EXCHANGE_RATE', 'PAYMENT_EXCHANGE_RATE', 'aprx_py.var.PAYMENT_EXCHANGE_RATE', 'NUMBER');
226 	fa_rx_util_pkg.assign_column('PAYMENT_EXCHANGE_DATE', 'CH.EXCHANGE_DATE', 'PAYMENT_EXCHANGE_DATE', 'aprx_py.var.PAYMENT_EXCHANGE_DATE', 'DATE');
227 	fa_rx_util_pkg.assign_column('PAYMENT_EXCHANGE_TYPE', 'CH.EXCHANGE_RATE_TYPE', 'PAYMENT_EXCHANGE_TYPE', 'aprx_py.var.PAYMENT_EXCHANGE_TYPE', 'VARCHAR2', 30);
228 	fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_DATE', 'CH.CLEARED_DATE', 'PAYMENT_CLEARED_DATE', 'aprx_py.var.PAYMENT_CLEARED_DATE', 'DATE');
229 	fa_rx_util_pkg.assign_column('PAYMENT_CLEARED_AMOUNT', 'CH.CLEARED_AMOUNT', 'PAYMENT_CLEARED_AMOUNT', 'aprx_py.var.PAYMENT_CLEARED_AMOUNT', 'NUMBER');
230 	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');
231 	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');
232 	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');
233 	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);
234 	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');
235 	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);
236 	fa_rx_util_pkg.assign_column('PAYMENT_PAY_METHOD', 'PMLKP.PAYMENT_METHOD_NAME', 'PAYMENT_PAY_METHOD', 'aprx_py.var.PAYMENT_PAY_METHOD', 'VARCHAR2', 25);
237 	fa_rx_util_pkg.assign_column('PAYMENT_STATUS', 'PSLKP.DISPLAYED_FIELD', 'PAYMENT_STATUS', 'aprx_py.var.PAYMENT_STATUS', 'VARCHAR2', 50);
238 -- Bug 6967238
239 	fa_rx_util_pkg.assign_column('PAYMENT_DOC_NAME', 'CS1.PAYMENT_DOCUMENT_NAME', 'PAYMENT_DOC_NAME', 'aprx_py.var.PAYMENT_DOC_NAME', 'VARCHAR2', 20);
240 	fa_rx_util_pkg.assign_column('PAYMENT_DISBURSEMENT_TYPE', 'CSLKP.DISPLAYED_FIELD', 'PAYMENT_DISBURSEMENT_TYPE', 'aprx_py.var.PAYMENT_DISBURSEMENT_TYPE', 'VARCHAR2', 25);
241 	fa_rx_util_pkg.assign_column('SUPPLIER_NAME', 'CH.VENDOR_NAME', 'SUPPLIER_NAME', 'aprx_py.var.SUPPLIER_NAME', 'VARCHAR2', 240);
242 	Fa_Rx_Util_Pkg.Assign_Column('SUPPLIER_NAME_ALT', 'V.VENDOR_NAME_ALT', 'SUPPLIER_NAME_ALT', 'aprx_py.var.SUPPLIER_NAME_ALT', 'VARCHAR2', 320);
243 	fa_rx_util_pkg.assign_column('SUPPLIER_SITE_CODE', 'VS.VENDOR_SITE_CODE', 'SUPPLIER_SITE_CODE', 'aprx_py.var.SUPPLIER_SITE_CODE', 'VARCHAR2', 15);
244 	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);
245 	fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_LINE1', 'CH.ADDRESS_LINE1', 'SUPPLIER_ADDRESS_LINE1', 'aprx_py.var.SUPPLIER_ADDRESS_LINE1', 'VARCHAR2', 240);
246 	fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_LINE2', 'CH.ADDRESS_LINE2', 'SUPPLIER_ADDRESS_LINE2', 'aprx_py.var.SUPPLIER_ADDRESS_LINE2', 'VARCHAR2', 240);
247 	fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_LINE3', 'CH.ADDRESS_LINE3', 'SUPPLIER_ADDRESS_LINE3', 'aprx_py.var.SUPPLIER_ADDRESS_LINE3', 'VARCHAR2', 240);
248 	fa_rx_util_pkg.assign_column('SUPPLIER_ADDRESS_ALT', 'VS.ADDRESS_LINES_ALT', 'SUPPLIER_ADDRESS_ALT', 'aprx_py.var.SUPPLIER_ADDRESS_ALT', 'VARCHAR2', 560);
249 	fa_rx_util_pkg.assign_column('SUPPLIER_CITY', 'CH.CITY', 'SUPPLIER_CITY', 'aprx_py.var.SUPPLIER_CITY', 'VARCHAR2', 25);
250 	fa_rx_util_pkg.assign_column('SUPPLIER_STATE', 'CH.STATE', 'SUPPLIER_STATE', 'aprx_py.var.SUPPLIER_STATE', 'VARCHAR2', 150);
251 	fa_rx_util_pkg.assign_column('SUPPLIER_PROVINCE', 'CH.PROVINCE', 'SUPPLIER_PROVINCE', 'aprx_py.var.SUPPLIER_PROVINCE', 'VARCHAR2', 150);
252 	fa_rx_util_pkg.assign_column('SUPPLIER_POSTAL_CODE', 'CH.ZIP', 'SUPPLIER_POSTAL_CODE', 'aprx_py.var.SUPPLIER_POSTAL_CODE', 'VARCHAR2', 20);
253 	fa_rx_util_pkg.assign_column('SUPPLIER_COUNTRY', 'CH.COUNTRY', 'SUPPLIER_COUNTRY', 'aprx_py.var.SUPPLIER_COUNTRY', 'VARCHAR2', 25);
254 	fa_rx_util_pkg.assign_column('SUPPLIER_TERRITORY', 'VSTERR.TERRITORY_SHORT_NAME', 'SUPPLIER_TERRITORY', 'aprx_py.var.SUPPLIER_TERRITORY', 'VARCHAR2', 80);
255 	fa_rx_util_pkg.assign_column('INT_BANK_NAME', 'B.BANK_NAME', 'INT_BANK_NAME', 'aprx_py.var.INT_BANK_NAME', 'VARCHAR2', 60);
256 	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);
260 	fa_rx_util_pkg.assign_column('INT_BANK_NUM', 'CH.BANK_NUM', 'INT_BANK_NUM', 'aprx_py.var.INT_BANK_NUM', 'VARCHAR2', 30);
257 	fa_rx_util_pkg.assign_column('INT_BANK_NUMBER', 'B.BANK_NUMBER', 'INT_BANK_NUMBER', 'aprx_py.var.INT_BANK_NUMBER', 'VARCHAR2', 30);
258 	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);
259 	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);
261 	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);
262 	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);
263 	fa_rx_util_pkg.assign_column('INT_BANK_ACCOUNT_NUM', 'BA.BANK_ACCOUNT_NUM', 'INT_BANK_ACCOUNT_NUM', 'aprx_py.var.INT_BANK_ACCOUNT_NUM', 'VARCHAR2', 30); --11831834
264 	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);
265 -->>SELECT_END<<--
266 
267 
268   --
269   -- Assign From Clause
270   --
271   fa_rx_util_pkg.From_Clause :=
272 	'AP_CHECKS CH,
273 	FND_DOCUMENT_SEQUENCES CHDOC,
274 	IBY_PAYMENT_METHODS_VL PMLKP,
275 	AP_LOOKUP_CODES PSLKP,
276 	AP_LOOKUP_CODES CSLKP,
277 	AP_LOOKUP_CODES CHTYPLKP,
278 	PO_VENDORS V,
279 	PO_VENDOR_SITES VS,
280 	FND_TERRITORIES_VL VSTERR,
281 	CE_PAYMENT_DOCUMENTS CS1,
282 	CE_BANK_ACCT_USES_ALL CBAU,
283 	CE_BANK_BRANCHES_V B,
284 	CE_BANK_ACCOUNTS ba,
285 	ap_system_parameters sys';           /* bug8760710 */
286 	/*AP_BANK_BRANCHES B,
287 	AP_BANK_ACCOUNTS ba'; Bug 6967238
288 */
289   --
290   -- Assign Where Clause (including the where clause from the parameters)
291   --
292   fa_rx_util_pkg.Where_Clause :=
293 	'CH.DOC_SEQUENCE_ID = CHDOC.DOC_SEQUENCE_ID(+)
294 AND
295 	CH.PAYMENT_DOCUMENT_ID = CS1.PAYMENT_DOCUMENT_ID(+) AND
296 	DECODE(CS1.MANUAL_PAYMENTS_ONLY_FLAG,''Y'', ''RECORDED'', ''N'', ''COMBINED'') = CSLKP.lookup_code(+) AND
297 	CSLKP.lookup_type(+) = ''DISBURSEMENT TYPE'' AND
298 	CH.PAYMENT_METHOD_CODE = PMLKP.PAYMENT_METHOD_CODE AND
299 	CH.STATUS_LOOKUP_CODE = PSLKP.LOOKUP_CODE AND
300 	PSLKP.LOOKUP_TYPE = ''CHECK STATE'' AND
301 	CH.PAYMENT_TYPE_FLAG = CHTYPLKP.LOOKUP_CODE AND
302 	-- bug14406332 VS.org_id =sys.org_id AND       /* 8760710 */
303       CH.org_id = sys.org_id AND                   --added for bug14406332
304 	CHTYPLKP.LOOKUP_TYPE = ''PAYMENT TYPE''
305 AND
306 	CH.VENDOR_ID = V.vendor_id(+) AND
307 	CH.VENDOR_SITE_ID = VS.vendor_site_id(+) AND
308 	CH.COUNTRY = VSTERR.TERRITORY_CODE(+)
309 AND
310 	BA.BANK_BRANCH_ID = B.BRANCH_PARTY_ID AND
311   	CH.CE_BANK_ACCT_USE_ID = cbau.BANK_ACCT_USE_ID AND
312 	cbau.bank_account_id = BA.bank_account_id
313     '|| l_param_where; -- Bug 6967238
314 
315 
316   --
317   -- Initialize some variables
318   -- Commenting for bug8760710
319 /*
320   SELECT sob.name, sob.currency_code
321   INTO aprx_py.var.ORGANIZATION_NAME, aprx_py.var.FUNCTIONAL_CURRENCY_CODE
322   FROM ap_system_parameters sys, gl_sets_of_books sob
323   WHERE sys.set_of_books_id = sob.set_of_books_id;
324 */
325   --Commenting for bug8760710
326 
327 /* Start of bug8760710 */
328   /* bug13941628 Use this sql only if report runs by ledger context */
329   if (nvl(param.ledger_id,-9999) <> -9999) then
330     SELECT sob.name, sob.currency_code
331     INTO aprx_py.var.ORGANIZATION_NAME, aprx_py.var.FUNCTIONAL_CURRENCY_CODE
332     FROM  gl_sets_of_books sob
333     WHERE  sob.set_of_books_id=param.ledger_id ;
334   END IF;
335  /* bug13941628 */
336 /* End of bug8760710 */
337 
338 
339 /* bug13941628 Use this sql only if report runs by OU context */
340   if (nvl(param.org_id,-9999) <> -9999) then
341    SELECT sob.name, sob.currency_code
342     INTO aprx_py.var.ORGANIZATION_NAME, aprx_py.var.FUNCTIONAL_CURRENCY_CODE
343     FROM ap_system_parameters sys, gl_sets_of_books sob
344     WHERE sys.set_of_books_id = sob.set_of_books_id
345     and sys.org_id = param.org_id;
346   END IF;
347  /* bug13941628 */
348 
349   fa_rx_util_pkg.debug('aprx_py.register_before_report()-');
350 end register_before_report;
351 
352 
353 --
354 -- This is the bind trigger for the main payment_run report
355 procedure register_bind(c in integer)
356 is
357   l_nls_yes varchar2(10);
358   l_nls_no varchar2(10);
359 begin
360   fa_rx_util_pkg.debug('aprx_py.register_bind()+');
361 
362   --
363   -- These bind variables were included in the WHERE clause.
364   --
365   if param.payment_date_start is not null then
366 	fa_rx_util_pkg.debug('Binding b_payment_date_start.');
367 	dbms_sql.bind_variable(c, 'b_payment_date_start', param.payment_date_start);
368   end if;
369   if param.payment_date_end is not null and param.payment_date_end <> param.payment_date_start then
370 	fa_rx_util_pkg.debug('Binding b_payment_date_end.');
371 	dbms_sql.bind_variable(c, 'b_payment_date_end', param.payment_date_end);
372   end if;
373 
374   if param.payment_currency_code is not null then
375 	fa_rx_util_pkg.debug('Binding b_payment_currency_code.');
376 	dbms_sql.bind_variable(c, 'b_payment_currency_code', param.payment_currency_code);
377   end if;
378 
379   if param.payment_bank_account_name is not null then
380 	fa_rx_util_pkg.debug('Binding b_payment_bank_account_name.');
381 	dbms_sql.bind_variable(c, 'b_payment_bank_account_name', param.payment_bank_account_name);
382   end if;
383 
384   if param.payment_method is not null then
385 	fa_rx_util_pkg.debug('Binding b_payment_method');
386 	dbms_sql.bind_variable(c, 'b_payment_method', param.payment_method);
390 	fa_rx_util_pkg.debug('Binding b_payment_type_flag.');
387   end if;
388 
389   if param.payment_type_flag is not null then
391 	dbms_sql.bind_variable(c, 'b_payment_type_flag', param.payment_type_flag);
392   end if;
393 
394   /* Start of bug8760710 */
395    /* bug13941628 Also ensure ledger_id is not -9999*/
396     if (nvl(param.ledger_id,-9999) <> -9999) then
397 	fa_rx_util_pkg.debug('Binding b_ledger_id');
398 	dbms_sql.bind_variable(c, 'b_ledger_id', param.ledger_id);
399     end if;
400     /* bug13941628 */
401   /* End of bug8760710 */
402 
403    /* bug13941628 If report is run by OU context*/
404    if (nvl(param.org_id,-9999) <> -9999) then
405 	fa_rx_util_pkg.debug('Binding b_org_id');
406 	dbms_sql.bind_variable(c, 'b_org_id', param.org_id);
407     end if;
408    /* bug13941628 */
409 
410   --
411   -- This bind variable was included in the select list.
412   --
413   select substrb(meaning,1,10) into l_nls_yes from fnd_lookups
414 	where lookup_type='YES_NO' and lookup_code='Y';
415   select substrb(meaning,1,10) into l_nls_no from fnd_lookups
416 	where lookup_type='YES_NO' and lookup_code='N';
417   fa_rx_util_pkg.debug('Binding b_nls_yes and b_nls_no.');
418   dbms_sql.bind_variable(c, 'b_nls_yes', l_nls_yes);
419   dbms_sql.bind_variable(c, 'b_nls_no', l_nls_no);
420 
421   fa_rx_util_pkg.debug('aprx_py.register_bind()-');
422 end register_bind;
423 
424 
425 
426 --
427 -- This is the before report trigger for the payment_actual_run report
428 procedure actual_before_report
429 is
430 begin
431   fa_rx_util_pkg.debug('aprx_py.actual_before_report()+');
432 
433   --
434   -- Assign another column specific to this report
435 	fa_rx_util_pkg.assign_column('INV_PAY_AMOUNT', 'INVPAY.AMOUNT', 'INV_PAY_AMOUNT', 'aprx_py.var.INV_PAY_AMOUNT', 'NUMBER');
436 	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');
437 	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');
438 	fa_rx_util_pkg.assign_column('INVOICE_NUM', 'INV1.INVOICE_NUM', 'INVOICE_NUM', 'aprx_py.var.INVOICE_NUM', 'VARCHAR2', 50);
439 	fa_rx_util_pkg.assign_column('INVOICE_DATE', 'INV1.INVOICE_DATE', 'INVOICE_DATE', 'aprx_py.var.INVOICE_DATE', 'DATE');
440 	fa_rx_util_pkg.assign_column('INVOICE_CURRENCY_CODE', 'INV1.INVOICE_CURRENCY_CODE', 'INVOICE_CURRENCY_CODE', 'aprx_py.var.INVOICE_CURRENCY_CODE', 'VARCHAR2', 15);
441 	fa_rx_util_pkg.assign_column('INVOICE_AMOUNT', 'INV1.INVOICE_AMOUNT', 'INVOICE_AMOUNT', 'aprx_py.var.INVOICE_AMOUNT', 'NUMBER');
442 	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');
443 	fa_rx_util_pkg.assign_column('INVOICE_DESCRIPTION', 'INV1.DESCRIPTION', 'INVOICE_DESCRIPTION', 'aprx_py.var.INVOICE_DESCRIPTION', 'VARCHAR2', 240);
444 
445 
446 
447   -- Add to the  WHERE clause specific to this report
448   fa_rx_util_pkg.From_Clause := fa_rx_util_pkg.From_Clause ||',
449 	AP_INVOICE_PAYMENTS INVPAY,
450 	AP_INVOICES inv1';
451 
452   fa_rx_util_pkg.Where_Clause := fa_rx_util_pkg.Where_Clause || '
453 	and CH.STATUS_LOOKUP_CODE NOT IN (''VOIDED'', ''SET UP'')
454 	and CH.CHECK_ID = INVPAY.CHECK_ID
455 	and INVPAY.INVOICE_ID = INV1.invoice_id';
456 
457     --
458     -- NOTE : Differences compared to original Japanese Actual Payment Report
459     -- The original used the set_of_books_id and included in the where clause a comparison with
460     -- ap_invoices.set_of_books_id
461     -- ap_bank_accounts.set_of_books_id
462     -- po_vendors.set_of_books_id
463     -- While the first two (invoices, bank accounts) do nothing for the select statement either
464     -- way, the last one (vendors) causes a problem in the original. Vendors is a shared entity
465     -- and you should not be looking at the set_of_books_id. I may be missing something, but
466     -- the column seems unnecessary.
467     --
468     -- Additionally, the original compared ap_checks.vendor_name to po_vendors.vendor_name.
469     -- This also is incorrect since users can change the vendor name when entering in a payment.
470     -- You should always compare ap_check.vendor_id to po_vendors.vendor_id when joining these
471     -- two tables.
472     --
473 
474   fa_rx_util_pkg.debug('aprx_py.actual_before_report()-');
475 end actual_before_report;
476 
477 
478 end aprx_py;