APPS.ARP_STAGED_DUNNING dependencies on DBMS_SQL

Line 252: ps_cursor := dbms_sql.open_cursor;

248: ' ORDER BY ps.invoice_currency_code';
249: -- bug #436336 : changed from the below:
250: -- ' ORDER BY ps.staged_dunning_level';
251:
252: ps_cursor := dbms_sql.open_cursor;
253:
254: dbms_sql.parse(ps_cursor, sql_statement, dbms_sql.v7 );
255:
256: -- bind variables into placeholder

Line 254: dbms_sql.parse(ps_cursor, sql_statement, dbms_sql.v7 );

250: -- ' ORDER BY ps.staged_dunning_level';
251:
252: ps_cursor := dbms_sql.open_cursor;
253:
254: dbms_sql.parse(ps_cursor, sql_statement, dbms_sql.v7 );
255:
256: -- bind variables into placeholder
257: dbms_sql.bind_variable(ps_cursor,':b_dun_date',parameter.dun_date);
258: if site.dunning_level = 'S' then

Line 257: dbms_sql.bind_variable(ps_cursor,':b_dun_date',parameter.dun_date);

253:
254: dbms_sql.parse(ps_cursor, sql_statement, dbms_sql.v7 );
255:
256: -- bind variables into placeholder
257: dbms_sql.bind_variable(ps_cursor,':b_dun_date',parameter.dun_date);
258: if site.dunning_level = 'S' then
259: dbms_sql.bind_variable(ps_cursor,':b_site_use_id',site.site_use_id);
260: end if;
261: dbms_sql.bind_variable(ps_cursor,':b_customer_id',site.customer_id);

Line 259: dbms_sql.bind_variable(ps_cursor,':b_site_use_id',site.site_use_id);

255:
256: -- bind variables into placeholder
257: dbms_sql.bind_variable(ps_cursor,':b_dun_date',parameter.dun_date);
258: if site.dunning_level = 'S' then
259: dbms_sql.bind_variable(ps_cursor,':b_site_use_id',site.site_use_id);
260: end if;
261: dbms_sql.bind_variable(ps_cursor,':b_customer_id',site.customer_id);
262: if site.grace_days = 'Y' then
263: dbms_sql.bind_variable(ps_cursor,':b_payment_grace_days',

Line 261: dbms_sql.bind_variable(ps_cursor,':b_customer_id',site.customer_id);

257: dbms_sql.bind_variable(ps_cursor,':b_dun_date',parameter.dun_date);
258: if site.dunning_level = 'S' then
259: dbms_sql.bind_variable(ps_cursor,':b_site_use_id',site.site_use_id);
260: end if;
261: dbms_sql.bind_variable(ps_cursor,':b_customer_id',site.customer_id);
262: if site.grace_days = 'Y' then
263: dbms_sql.bind_variable(ps_cursor,':b_payment_grace_days',
264: site.payment_grace_days);
265: end if;

Line 263: dbms_sql.bind_variable(ps_cursor,':b_payment_grace_days',

259: dbms_sql.bind_variable(ps_cursor,':b_site_use_id',site.site_use_id);
260: end if;
261: dbms_sql.bind_variable(ps_cursor,':b_customer_id',site.customer_id);
262: if site.grace_days = 'Y' then
263: dbms_sql.bind_variable(ps_cursor,':b_payment_grace_days',
264: site.payment_grace_days);
265: end if;
266:
267: if parameter.transaction_type_from is not null

Line 273: dbms_sql.bind_variable(ps_cursor,':b_transaction_type_from',

269: parameter.transaction_type_to is not null
270: AND
271: parameter.transaction_type_from = parameter.transaction_type_to
272: then
273: dbms_sql.bind_variable(ps_cursor,':b_transaction_type_from',
274: parameter.transaction_type_from );
275: else
276: if parameter.transaction_type_from is not null
277: then

Line 278: dbms_sql.bind_variable(ps_cursor,':b_transaction_type_from',

274: parameter.transaction_type_from );
275: else
276: if parameter.transaction_type_from is not null
277: then
278: dbms_sql.bind_variable(ps_cursor,':b_transaction_type_from',
279: parameter.transaction_type_from );
280: end if;
281: if parameter.transaction_type_to is not null
282: then

Line 283: dbms_sql.bind_variable(ps_cursor,':b_transaction_type_to',

279: parameter.transaction_type_from );
280: end if;
281: if parameter.transaction_type_to is not null
282: then
283: dbms_sql.bind_variable(ps_cursor,':b_transaction_type_to',
284: parameter.transaction_type_to );
285: end if;
286: end if;
287:

Line 289: dbms_sql.define_column(ps_cursor,1,ps_id);

285: end if;
286: end if;
287:
288: -- specify columns to be selected in
289: dbms_sql.define_column(ps_cursor,1,ps_id);
290: dbms_sql.define_column(ps_cursor,2,curr_code,15);
291: dbms_sql.define_column(ps_cursor,3,adr);
292: dbms_sql.define_column(ps_cursor,4,days_late);
293: dbms_sql.define_column(ps_cursor,5,st_dunning_level);

Line 290: dbms_sql.define_column(ps_cursor,2,curr_code,15);

286: end if;
287:
288: -- specify columns to be selected in
289: dbms_sql.define_column(ps_cursor,1,ps_id);
290: dbms_sql.define_column(ps_cursor,2,curr_code,15);
291: dbms_sql.define_column(ps_cursor,3,adr);
292: dbms_sql.define_column(ps_cursor,4,days_late);
293: dbms_sql.define_column(ps_cursor,5,st_dunning_level);
294: dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);

Line 291: dbms_sql.define_column(ps_cursor,3,adr);

287:
288: -- specify columns to be selected in
289: dbms_sql.define_column(ps_cursor,1,ps_id);
290: dbms_sql.define_column(ps_cursor,2,curr_code,15);
291: dbms_sql.define_column(ps_cursor,3,adr);
292: dbms_sql.define_column(ps_cursor,4,days_late);
293: dbms_sql.define_column(ps_cursor,5,st_dunning_level);
294: dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);
295:

Line 292: dbms_sql.define_column(ps_cursor,4,days_late);

288: -- specify columns to be selected in
289: dbms_sql.define_column(ps_cursor,1,ps_id);
290: dbms_sql.define_column(ps_cursor,2,curr_code,15);
291: dbms_sql.define_column(ps_cursor,3,adr);
292: dbms_sql.define_column(ps_cursor,4,days_late);
293: dbms_sql.define_column(ps_cursor,5,st_dunning_level);
294: dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);
295:
296: sum_adr := 0;

Line 293: dbms_sql.define_column(ps_cursor,5,st_dunning_level);

289: dbms_sql.define_column(ps_cursor,1,ps_id);
290: dbms_sql.define_column(ps_cursor,2,curr_code,15);
291: dbms_sql.define_column(ps_cursor,3,adr);
292: dbms_sql.define_column(ps_cursor,4,days_late);
293: dbms_sql.define_column(ps_cursor,5,st_dunning_level);
294: dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);
295:
296: sum_adr := 0;
297: dun_ok := FALSE;

Line 294: dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);

290: dbms_sql.define_column(ps_cursor,2,curr_code,15);
291: dbms_sql.define_column(ps_cursor,3,adr);
292: dbms_sql.define_column(ps_cursor,4,days_late);
293: dbms_sql.define_column(ps_cursor,5,st_dunning_level);
294: dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);
295:
296: sum_adr := 0;
297: dun_ok := FALSE;
298: ignore := dbms_sql.execute(ps_cursor);

Line 298: ignore := dbms_sql.execute(ps_cursor);

294: dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);
295:
296: sum_adr := 0;
297: dun_ok := FALSE;
298: ignore := dbms_sql.execute(ps_cursor);
299: prev_currency := '0';
300: current_dun_date := fnd_date.canonical_to_date(parameter.dun_date);
301:
302: <>

Line 304: if dbms_sql.fetch_rows(ps_cursor) <= 0 then

300: current_dun_date := fnd_date.canonical_to_date(parameter.dun_date);
301:
302: <>
303: LOOP
304: if dbms_sql.fetch_rows(ps_cursor) <= 0 then
305: exit Open_Payment_Loop;
306: end if;
307:
308: -- get fetched values from the variables

Line 309: dbms_sql.column_value(ps_cursor,1,ps_id);

305: exit Open_Payment_Loop;
306: end if;
307:
308: -- get fetched values from the variables
309: dbms_sql.column_value(ps_cursor,1,ps_id);
310: dbms_sql.column_value(ps_cursor,2,curr_code);
311: dbms_sql.column_value(ps_cursor,3,adr);
312: dbms_sql.column_value(ps_cursor,4,days_late);
313: dbms_sql.column_value(ps_cursor,5,st_dunning_level);

Line 310: dbms_sql.column_value(ps_cursor,2,curr_code);

306: end if;
307:
308: -- get fetched values from the variables
309: dbms_sql.column_value(ps_cursor,1,ps_id);
310: dbms_sql.column_value(ps_cursor,2,curr_code);
311: dbms_sql.column_value(ps_cursor,3,adr);
312: dbms_sql.column_value(ps_cursor,4,days_late);
313: dbms_sql.column_value(ps_cursor,5,st_dunning_level);
314: dbms_sql.column_value(ps_cursor,6,dunning_level_override_date);

Line 311: dbms_sql.column_value(ps_cursor,3,adr);

307:
308: -- get fetched values from the variables
309: dbms_sql.column_value(ps_cursor,1,ps_id);
310: dbms_sql.column_value(ps_cursor,2,curr_code);
311: dbms_sql.column_value(ps_cursor,3,adr);
312: dbms_sql.column_value(ps_cursor,4,days_late);
313: dbms_sql.column_value(ps_cursor,5,st_dunning_level);
314: dbms_sql.column_value(ps_cursor,6,dunning_level_override_date);
315:

Line 312: dbms_sql.column_value(ps_cursor,4,days_late);

308: -- get fetched values from the variables
309: dbms_sql.column_value(ps_cursor,1,ps_id);
310: dbms_sql.column_value(ps_cursor,2,curr_code);
311: dbms_sql.column_value(ps_cursor,3,adr);
312: dbms_sql.column_value(ps_cursor,4,days_late);
313: dbms_sql.column_value(ps_cursor,5,st_dunning_level);
314: dbms_sql.column_value(ps_cursor,6,dunning_level_override_date);
315:
316: if curr_code <> prev_currency then

Line 313: dbms_sql.column_value(ps_cursor,5,st_dunning_level);

309: dbms_sql.column_value(ps_cursor,1,ps_id);
310: dbms_sql.column_value(ps_cursor,2,curr_code);
311: dbms_sql.column_value(ps_cursor,3,adr);
312: dbms_sql.column_value(ps_cursor,4,days_late);
313: dbms_sql.column_value(ps_cursor,5,st_dunning_level);
314: dbms_sql.column_value(ps_cursor,6,dunning_level_override_date);
315:
316: if curr_code <> prev_currency then
317: ok_flag := ARP_STAGED_DUNNING.get_cpa(site,curr_code,min_dun_amount,

Line 314: dbms_sql.column_value(ps_cursor,6,dunning_level_override_date);

310: dbms_sql.column_value(ps_cursor,2,curr_code);
311: dbms_sql.column_value(ps_cursor,3,adr);
312: dbms_sql.column_value(ps_cursor,4,days_late);
313: dbms_sql.column_value(ps_cursor,5,st_dunning_level);
314: dbms_sql.column_value(ps_cursor,6,dunning_level_override_date);
315:
316: if curr_code <> prev_currency then
317: ok_flag := ARP_STAGED_DUNNING.get_cpa(site,curr_code,min_dun_amount,
318: min_dun_inv_amount);

Line 349: sum_cursor := dbms_sql.open_cursor;

345:
346: if (dun_flag = TRUE) then
347:
348: -- Open cursor
349: sum_cursor := dbms_sql.open_cursor;
350: -- Parse cursor
351: dbms_sql.parse(sum_cursor, sql_statement_s, dbms_sql.v7 );
352: -- Bind variables
353:

Line 351: dbms_sql.parse(sum_cursor, sql_statement_s, dbms_sql.v7 );

347:
348: -- Open cursor
349: sum_cursor := dbms_sql.open_cursor;
350: -- Parse cursor
351: dbms_sql.parse(sum_cursor, sql_statement_s, dbms_sql.v7 );
352: -- Bind variables
353:
354: dbms_sql.bind_variable(sum_cursor,':b_dun_date',parameter.dun_date);
355: if site.dunning_level = 'S' then

Line 354: dbms_sql.bind_variable(sum_cursor,':b_dun_date',parameter.dun_date);

350: -- Parse cursor
351: dbms_sql.parse(sum_cursor, sql_statement_s, dbms_sql.v7 );
352: -- Bind variables
353:
354: dbms_sql.bind_variable(sum_cursor,':b_dun_date',parameter.dun_date);
355: if site.dunning_level = 'S' then
356: dbms_sql.bind_variable(sum_cursor,':b_site_use_id',site.site_use_id);
357: end if;
358: dbms_sql.bind_variable(sum_cursor,':b_customer_id',site.customer_id);

Line 356: dbms_sql.bind_variable(sum_cursor,':b_site_use_id',site.site_use_id);

352: -- Bind variables
353:
354: dbms_sql.bind_variable(sum_cursor,':b_dun_date',parameter.dun_date);
355: if site.dunning_level = 'S' then
356: dbms_sql.bind_variable(sum_cursor,':b_site_use_id',site.site_use_id);
357: end if;
358: dbms_sql.bind_variable(sum_cursor,':b_customer_id',site.customer_id);
359: if site.grace_days = 'Y' then
360: dbms_sql.bind_variable(sum_cursor,':b_payment_grace_days',

Line 358: dbms_sql.bind_variable(sum_cursor,':b_customer_id',site.customer_id);

354: dbms_sql.bind_variable(sum_cursor,':b_dun_date',parameter.dun_date);
355: if site.dunning_level = 'S' then
356: dbms_sql.bind_variable(sum_cursor,':b_site_use_id',site.site_use_id);
357: end if;
358: dbms_sql.bind_variable(sum_cursor,':b_customer_id',site.customer_id);
359: if site.grace_days = 'Y' then
360: dbms_sql.bind_variable(sum_cursor,':b_payment_grace_days',
361: site.payment_grace_days);
362: end if;

Line 360: dbms_sql.bind_variable(sum_cursor,':b_payment_grace_days',

356: dbms_sql.bind_variable(sum_cursor,':b_site_use_id',site.site_use_id);
357: end if;
358: dbms_sql.bind_variable(sum_cursor,':b_customer_id',site.customer_id);
359: if site.grace_days = 'Y' then
360: dbms_sql.bind_variable(sum_cursor,':b_payment_grace_days',
361: site.payment_grace_days);
362: end if;
363:
364: if parameter.transaction_type_from is not null

Line 370: dbms_sql.bind_variable(sum_cursor,':b_transaction_type_from',

366: parameter.transaction_type_to is not null
367: AND
368: parameter.transaction_type_from = parameter.transaction_type_to
369: then
370: dbms_sql.bind_variable(sum_cursor,':b_transaction_type_from',
371: parameter.transaction_type_from );
372: else
373: if parameter.transaction_type_from is not null
374: then

Line 375: dbms_sql.bind_variable(sum_cursor,':b_transaction_type_from',

371: parameter.transaction_type_from );
372: else
373: if parameter.transaction_type_from is not null
374: then
375: dbms_sql.bind_variable(sum_cursor,':b_transaction_type_from',
376: parameter.transaction_type_from );
377: end if;
378: if parameter.transaction_type_to is not null
379: then

Line 380: dbms_sql.bind_variable(sum_cursor,':b_transaction_type_to',

376: parameter.transaction_type_from );
377: end if;
378: if parameter.transaction_type_to is not null
379: then
380: dbms_sql.bind_variable(sum_cursor,':b_transaction_type_to',
381: parameter.transaction_type_to );
382: end if;
383: end if;
384: dbms_sql.bind_variable(sum_cursor,':curr',

Line 384: dbms_sql.bind_variable(sum_cursor,':curr',

380: dbms_sql.bind_variable(sum_cursor,':b_transaction_type_to',
381: parameter.transaction_type_to );
382: end if;
383: end if;
384: dbms_sql.bind_variable(sum_cursor,':curr',
385: curr_code );
386:
387: -- Define the output column
388: dbms_sql.define_column(sum_cursor, 1, inv_code, 15);

Line 388: dbms_sql.define_column(sum_cursor, 1, inv_code, 15);

384: dbms_sql.bind_variable(sum_cursor,':curr',
385: curr_code );
386:
387: -- Define the output column
388: dbms_sql.define_column(sum_cursor, 1, inv_code, 15);
389: dbms_sql.define_column(sum_cursor, 2, sum_adr);
390:
391: -- Execute
392: ignore := dbms_sql.execute(sum_cursor);

Line 389: dbms_sql.define_column(sum_cursor, 2, sum_adr);

385: curr_code );
386:
387: -- Define the output column
388: dbms_sql.define_column(sum_cursor, 1, inv_code, 15);
389: dbms_sql.define_column(sum_cursor, 2, sum_adr);
390:
391: -- Execute
392: ignore := dbms_sql.execute(sum_cursor);
393:

Line 392: ignore := dbms_sql.execute(sum_cursor);

388: dbms_sql.define_column(sum_cursor, 1, inv_code, 15);
389: dbms_sql.define_column(sum_cursor, 2, sum_adr);
390:
391: -- Execute
392: ignore := dbms_sql.execute(sum_cursor);
393:
394: -- Fetch a row
395: if dbms_sql.fetch_rows(sum_cursor) <= 0 then
396: exit Open_Payment_Loop;

Line 395: if dbms_sql.fetch_rows(sum_cursor) <= 0 then

391: -- Execute
392: ignore := dbms_sql.execute(sum_cursor);
393:
394: -- Fetch a row
395: if dbms_sql.fetch_rows(sum_cursor) <= 0 then
396: exit Open_Payment_Loop;
397: end if;
398:
399: -- Get column's value

Line 400: dbms_sql.column_value(sum_cursor, 1, inv_code);

396: exit Open_Payment_Loop;
397: end if;
398:
399: -- Get column's value
400: dbms_sql.column_value(sum_cursor, 1, inv_code);
401: dbms_sql.column_value(sum_cursor, 2, sum_adr);
402:
403: -- Close cursor
404: dbms_sql.close_cursor(sum_cursor);

Line 401: dbms_sql.column_value(sum_cursor, 2, sum_adr);

397: end if;
398:
399: -- Get column's value
400: dbms_sql.column_value(sum_cursor, 1, inv_code);
401: dbms_sql.column_value(sum_cursor, 2, sum_adr);
402:
403: -- Close cursor
404: dbms_sql.close_cursor(sum_cursor);
405: end if;

Line 404: dbms_sql.close_cursor(sum_cursor);

400: dbms_sql.column_value(sum_cursor, 1, inv_code);
401: dbms_sql.column_value(sum_cursor, 2, sum_adr);
402:
403: -- Close cursor
404: dbms_sql.close_cursor(sum_cursor);
405: end if;
406:
407: if (sum_adr < min_dun_amount) then
408: dun_flag := FALSE;

Line 452: if ok_flag = FALSE OR dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then

448: END LOOP Open_Payment_Loop;
449:
450: -- return FALSE if no open payment schedule found or function get_cpa returned
451: -- FALSE
452: if ok_flag = FALSE OR dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
453: if dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
454: letter_count := 0;
455: dbms_sql.close_cursor(ps_cursor);
456: return(TRUE);

Line 453: if dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then

449:
450: -- return FALSE if no open payment schedule found or function get_cpa returned
451: -- FALSE
452: if ok_flag = FALSE OR dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
453: if dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
454: letter_count := 0;
455: dbms_sql.close_cursor(ps_cursor);
456: return(TRUE);
457: else

Line 455: dbms_sql.close_cursor(ps_cursor);

451: -- FALSE
452: if ok_flag = FALSE OR dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
453: if dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
454: letter_count := 0;
455: dbms_sql.close_cursor(ps_cursor);
456: return(TRUE);
457: else
458: dbms_sql.close_cursor(ps_cursor);
459: return( FALSE );

Line 458: dbms_sql.close_cursor(ps_cursor);

454: letter_count := 0;
455: dbms_sql.close_cursor(ps_cursor);
456: return(TRUE);
457: else
458: dbms_sql.close_cursor(ps_cursor);
459: return( FALSE );
460: end if;
461: end if;
462: dbms_sql.close_cursor(ps_cursor);

Line 462: dbms_sql.close_cursor(ps_cursor);

458: dbms_sql.close_cursor(ps_cursor);
459: return( FALSE );
460: end if;
461: end if;
462: dbms_sql.close_cursor(ps_cursor);
463:
464:
465: -- sort the array of dunning levels
466: change_flag := TRUE;