DBA Data[Home] [Help]

APPS.JAI_AR_VALIDATE_DATA_PKG dependencies on RA_CUSTOMER_TRX_ALL

Line 446: PROCEDURE pre_validation( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,

442: p_process_message := SUBSTR(SQLERRM,1,300);
443: END rectify_ar_rec_appl;
444:
445:
446: PROCEDURE pre_validation( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
447: p_process_status OUT NOCOPY VARCHAR2,
448: p_process_message OUT NOCOPY VARCHAR2)
449: IS
450: CURSOR cur_chk_non_il_taxes(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

Line 450: CURSOR cur_chk_non_il_taxes(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

446: PROCEDURE pre_validation( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
447: p_process_status OUT NOCOPY VARCHAR2,
448: p_process_message OUT NOCOPY VARCHAR2)
449: IS
450: CURSOR cur_chk_non_il_taxes(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
451: IS
452: SELECT 1
453: FROM ra_customer_trx_lines_all rctl,
454: ar_vat_tax_all avtl

Line 462: CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

458: AND avtl.tax_code <> jai_constants.tax_code_localization--'Localization' --Added by Bgowrava for Bug#5484865
459: AND rctl.org_id = rctl.org_id
460: AND rctl.line_type IN ('TAX','FREIGHT') ;
461:
462: CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
463: IS
464: SELECT 1
465: FROM ra_cust_trx_line_gl_dist_all gl_dist,
466: ra_customer_trx_all rctx

Line 466: ra_customer_trx_all rctx

462: CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
463: IS
464: SELECT 1
465: FROM ra_cust_trx_line_gl_dist_all gl_dist,
466: ra_customer_trx_all rctx
467: WHERE rctx.customer_trx_id = gl_dist.customer_trx_id
468: AND rctx.invoicing_rule_id IS NOT NULL
469: AND gl_dist.account_class = 'REC'
470: AND gl_dist.account_set_flag = 'N'

Line 475: CURSOR cur_chk_revrec_run_cm(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

471: AND gl_dist.latest_rec_flag = 'Y'
472: AND gl_dist.customer_trx_id = cp_customer_trx_id;
473:
474: --added this cursor for bug#7445602
475: CURSOR cur_chk_revrec_run_cm(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
476: IS
477: SELECT count(*)
478: FROM ra_customer_trx_all rcta,
479: ra_customer_trx_lines_all rctla,

Line 478: FROM ra_customer_trx_all rcta,

474: --added this cursor for bug#7445602
475: CURSOR cur_chk_revrec_run_cm(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
476: IS
477: SELECT count(*)
478: FROM ra_customer_trx_all rcta,
479: ra_customer_trx_lines_all rctla,
480: ra_cust_trx_types_all rctta
481: WHERE rcta.customer_trx_id = rctla.customer_trx_id
482: AND rcta.cust_trx_type_id = rctta.cust_trx_type_id

Line 487: CURSOR cur_chk_gl_posting(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

483: AND rctta.type = 'CM'
484: AND rctla. previous_customer_trx_id IS NOT NULL
485: AND rcta.customer_trx_id = cp_customer_trx_id;
486:
487: CURSOR cur_chk_gl_posting(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
488: IS
489: SELECT 1
490: FROM ra_cust_trx_line_gl_dist_all
491: WHERE customer_trx_id = cp_customer_trx_id

Line 575: FROM ra_customer_trx_all rcta ,

571: SELECT rcta.customer_trx_id,
572: rcta.previous_customer_trx_id,
573: rcta.set_of_books_id ,
574: rctta.type
575: FROM ra_customer_trx_all rcta ,
576: JAI_AR_TRXS jrcta ,
577: ra_cust_trx_types_all rctta /* added by aiyer to check that only INV and CM type of transactions are picked up */
578: WHERE rcta.customer_trx_id = jrcta.customer_trx_id
579: AND rcta.cust_trx_type_id = rctta.cust_trx_type_id

Line 587: CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

583: AND nvl(rcta.complete_flag,'N') = 'Y'
584: ORDER BY rcta.customer_trx_id;
585:
586: --check if revenue recognition program has been run
587: CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
588: IS
589: SELECT 1
590: FROM ra_cust_trx_line_gl_dist_all gl_dist,
591: ra_customer_trx_all rctx

Line 591: ra_customer_trx_all rctx

587: CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
588: IS
589: SELECT 1
590: FROM ra_cust_trx_line_gl_dist_all gl_dist,
591: ra_customer_trx_all rctx
592: WHERE rctx.customer_trx_id = gl_dist.customer_trx_id
593: AND rctx.invoicing_rule_id IS NOT NULL
594: AND gl_dist.account_class = 'REC'
595: AND gl_dist.account_set_flag = 'N'

Line 600: CURSOR cur_chk_gl_posting(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

596: AND gl_dist.latest_rec_flag = 'Y'
597: AND gl_dist.customer_trx_id = cp_customer_trx_id;
598:
599: --Get 1 if record has not been posted else null if posted
600: CURSOR cur_chk_gl_posting(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
601: IS
602: SELECT 1
603: FROM ra_cust_trx_line_gl_dist_all
604: WHERE customer_trx_id = cp_customer_trx_id

Line 610: CURSOR cur_tot_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

606: AND account_class = 'REC'
607: AND latest_rec_flag = 'Y'
608: AND posting_control_id = -3;
609:
610: CURSOR cur_tot_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
611: IS
612: SELECT NVL(SUM(amount_due_remaining),0) amount_due_remaining,
613: NVL(SUM(amount_due_original),0) amount_due_original,
614: NVL(SUM(tax_original),0) tax_original,

Line 627: CURSOR cur_payment_schedules(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

623: NVL(SUM( NVL(amount_due_remaining,0) * NVL(exchange_rate,1) ),0) acctd_amount_due_remain_calc
624: FROM ar_payment_schedules_all
625: WHERE customer_trx_id = cp_customer_trx_id;
626:
627: CURSOR cur_payment_schedules(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
628: IS
629: SELECT status,
630: gl_date_closed,
631: NVL(amount_due_remaining,0) amount_due_remaining,

Line 638: CURSOR cur_tot_cust_trx_gl_dist(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,

634: exchange_rate
635: FROM ar_payment_schedules_all
636: WHERE customer_trx_id = cp_customer_trx_id;
637:
638: CURSOR cur_tot_cust_trx_gl_dist(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
639: cp_account_class ra_cust_trx_line_gl_dist_all.account_class%TYPE)
640: IS
641: SELECT NVL(SUM(amount),0) amount,
642: NVL(SUM(acctd_amount),0) acctd_amount

Line 705: CURSOR cur_cm_ar_recv_appl( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,

701: AND ROUND(nvl(gl_dist.amount,0)) <> ROUND(nvl(extended_amount,0))
702: AND gl_dist.customer_trx_id = cp_customer_trx_id ;
703:
704:
705: CURSOR cur_cm_ar_recv_appl( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
706: cp_exchange_rate ra_customer_trx_all.exchange_rate%TYPE,
707: cp_exchange_rate_prev ra_customer_trx_all.exchange_rate%TYPE)
708: IS
709: SELECT NVL(SUM(line_applied),0) line_applied,

Line 706: cp_exchange_rate ra_customer_trx_all.exchange_rate%TYPE,

702: AND gl_dist.customer_trx_id = cp_customer_trx_id ;
703:
704:
705: CURSOR cur_cm_ar_recv_appl( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
706: cp_exchange_rate ra_customer_trx_all.exchange_rate%TYPE,
707: cp_exchange_rate_prev ra_customer_trx_all.exchange_rate%TYPE)
708: IS
709: SELECT NVL(SUM(line_applied),0) line_applied,
710: NVL(SUM(tax_applied),0) tax_applied,

Line 707: cp_exchange_rate_prev ra_customer_trx_all.exchange_rate%TYPE)

703:
704:
705: CURSOR cur_cm_ar_recv_appl( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
706: cp_exchange_rate ra_customer_trx_all.exchange_rate%TYPE,
707: cp_exchange_rate_prev ra_customer_trx_all.exchange_rate%TYPE)
708: IS
709: SELECT NVL(SUM(line_applied),0) line_applied,
710: NVL(SUM(tax_applied),0) tax_applied,
711: NVL(SUM(freight_applied),0) freight_applied,

Line 725: CURSOR cur_chk_cm_exists (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

721: AND status = 'APP';
722:
723: --Validate that a Invoice has a 'CM' type of receivable application.
724: --This would be used for validating correctness of invoices w.r.t their receivable applications
725: CURSOR cur_chk_cm_exists (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
726: IS
727: SELECT application_type
728: FROM ar_receivable_applications_all
729: WHERE applied_customer_trx_id = cp_customer_trx_id

Line 734: CURSOR cur_inv_cash_cm_recv_appl(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

730: AND application_type = 'CM'
731: AND display = 'Y'
732: AND status = 'APP';
733:
734: CURSOR cur_inv_cash_cm_recv_appl(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
735: IS
736: SELECT NVL(SUM(line_applied),0) line_applied,
737: NVL(SUM(tax_applied),0) tax_applied,
738: NVL(SUM(freight_applied),0) freight_applied,

Line 746: CURSOR cur_inv_ar_recv_appl(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

742: AND application_type IN ('CM' ,'CASH')
743: AND display = 'Y'
744: AND status = 'APP';
745:
746: CURSOR cur_inv_ar_recv_appl(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
747: IS
748: SELECT NVL(SUM(line_applied),0) line_applied,
749: NVL(SUM(tax_applied),0) tax_applied,
750: NVL(SUM(freight_applied),0) freight_applied,

Line 758: CURSOR cur_ra_customer_trx(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

754: AND application_type = 'CM'
755: AND display = 'Y'
756: AND status = 'APP';
757:
758: CURSOR cur_ra_customer_trx(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
759: IS
760: SELECT NVL(exchange_rate,1) exchange_rate,
761: set_of_books_id
762: FROM ra_customer_trx_all

Line 762: FROM ra_customer_trx_all

758: CURSOR cur_ra_customer_trx(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
759: IS
760: SELECT NVL(exchange_rate,1) exchange_rate,
761: set_of_books_id
762: FROM ra_customer_trx_all
763: WHERE customer_trx_id = cp_customer_trx_id;
764:
765: CURSOR cur_utl_location
766: IS

Line 771: CURSOR cur_sync_il_line_tax (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

767: SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,Value,SUBSTR (value,1,INSTR(value,',') -1)) utl_location
768: FROM v$parameter
769: WHERE name = 'utl_file_dir';
770:
771: CURSOR cur_sync_il_line_tax (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
772: IS
773: SELECT jrctl.customer_trx_line_id,
774: nvl(sum(jrcttl.tax_amount),0) tax_amount
775: FROM JAI_AR_TRX_LINES jrctl,

Line 784: CURSOR cur_sync_il_hdr_tax (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)

780: HAVING ROUND(nvl(sum(jrcttl.tax_amount),0)) <>( SELECT ROUND(NVL(tax_amount,0))
781: FROM JAI_AR_TRX_LINES a
782: WHERE a.customer_trx_line_id = jrctl.customer_trx_line_id);
783:
784: CURSOR cur_sync_il_hdr_tax (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
785: IS
786: SELECT jtrx.customer_trx_id ,
787: NVL(SUM(jrctl.tax_amount),0) tax_amount
788: FROM JAI_AR_TRX_LINES jrctl,