APPS.AP_TURNOVER_RPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 47

  SELECT name
    INTO gc_ledger_name
	FROM gl_ledgers
   WHERE ledger_id = LEDGER_ID_PARAM;

Line: 72

    gc_prepay_invoice_select := ' ai.gl_date ';

Line: 80

    gc_prepay_invoice_select := ' (CASE WHEN asps.recon_accounting_flag=''Y''';

Line: 81

    gc_prepay_invoice_select := gc_prepay_invoice_select ||' THEN ac.cleared_date';

Line: 82

    gc_prepay_invoice_select := gc_prepay_invoice_select ||' WHEN asps.recon_accounting_flag=''N''';

Line: 83

    gc_prepay_invoice_select := gc_prepay_invoice_select ||' AND asps.when_to_account_pmt=''ALWAYS''';

Line: 84

    gc_prepay_invoice_select := gc_prepay_invoice_select ||' THEN ac.check_date END) ';

Line: 115

    SELECT TRIM ( SUBSTR ( acc_num
                       , INSTR (acc_num, '-', 1, LEVEL) + 1
                       , INSTR (acc_num, '-', 1, LEVEL + 1)
                        -INSTR ( acc_num, '-', 1, LEVEL)- 1
                         )) AS account_segments
         , TRIM ( SUBSTR ( sum_mask
                         , INSTR ( sum_mask, '.', 1, LEVEL) + 1
                         , INSTR ( sum_mask, '.', 1, LEVEL + 1)
                           - INSTR ( sum_mask, '.', 1, LEVEL)- 1
                         )) AS summary_mask
      FROM ( SELECT '-' || Acct_num || '-' acc_num
                  , '.' || SUMMARY_MASK_PARAM || '.' sum_mask
              FROM DUAL )
CONNECT BY LEVEL <= LENGTH ( acc_num ) - LENGTH ( REPLACE ( acc_num, '-', '')) - 1;

Line: 135

                SELECT LENGTH(lcu_Acctnum.account_segments) into ln_length from dual;

Line: 169

    SELECT SUM(NVL(xdl.unrounded_accounted_dr,0)-NVL(xdl.unrounded_accounted_cr,0))
INTO gn_open_payment_balance
  FROM  ap_invoices aia
        ,ap_invoice_payments aip
		,ap_checks ac
		,ap_payment_history aph
		,ap_lookup_codes alc
		,ap_system_parameters asps
		,xla_events xe
		,xla_distribution_links xdl
		,xla_ae_lines xal
		,xla_ae_headers xah
		,gl_code_combinations gcc
 WHERE  aia.vendor_id             = p_in_sup_id
   AND  aia.org_id                = p_in_orgs_id
   AND  aia.vendor_site_id        = p_in_sup_site_id
   AND  aia.invoice_currency_code = p_in_curr
   AND  aia.invoice_id   = aip.invoice_id
   AND  aia.gl_date      < TO_DATE(PERIOD_START_DATE_PARAM)
   AND  aip.check_id     = ac.check_id
   AND  ac.check_id      = aph.check_id
   AND  alc.lookup_type  = 'PAYMENT TYPE'
   AND  asps.org_id      = aia.org_id
   AND  alc.lookup_code  = ac.payment_type_flag
   AND  aph.accounting_event_id = xe.event_id
   AND  xe.application_id = 200
   AND  xe.event_id      = xdl.event_id
   AND  xdl.application_id      = 200
   AND  aph.accounting_event_id = xdl.event_id
   AND  xdl.source_distribution_type = 'AP_PMT_DIST'
   AND  xdl.applied_to_source_id_num_1 = aia.invoice_id
   AND  xdl.ae_header_id       =  xal.ae_header_id
   AND  xal.application_id = 200
   AND  xdl.ae_line_num    = xal.ae_line_num
   AND  xdl.accounting_line_code NOT IN ('AP_LIAB_AWT_PMT')
   AND  xdl.applied_to_entity_code = 'AP_INVOICES'
   AND  xal.ae_header_id   = xah.ae_header_id
   AND  xah.application_id = 200
   AND  xah.ledger_id      = LEDGER_ID_PARAM
   AND  xdl.rounding_class_code = 'LIABILITY'
   AND  gcc.code_combination_id   = xal.code_combination_id
   AND  gcc.code_combination_id   = p_in_code_comb_id
   AND ((PRPMT_PROCESSING_PARAM = 2)
        OR (PRPMT_PROCESSING_PARAM = 1
          AND (aia.invoice_type_lookup_code  IN ('CREDIT','DEBIT','STANDARD','AWT','EXPENSE REPORT','MIXED')
        OR (aia.invoice_type_lookup_code  = 'PREPAYMENT'
       AND (CASE WHEN asps.recon_accounting_flag='Y'
                 THEN ac.cleared_date
                 WHEN asps.recon_accounting_flag='N'
                 THEN ac.check_date END) IS NOT NULL))));

Line: 225

  SELECT SUM(NVL(xal.accounted_cr,0)-NVL(xal.accounted_dr,0))
    INTO gn_open_invoice_balance
    FROM ap_invoices               ai
        ,xla_transaction_entities xte
        ,xla_events               xe
        ,xla_ae_headers           xah
        ,xla_ae_lines             xal
        ,gl_code_combinations     gcc
  WHERE ai.set_of_books_id        = LEDGER_ID_PARAM
    AND ai.vendor_id              = p_in_sup_id
    AND ai.org_id                 = p_in_orgs_id
    AND ai.vendor_site_id         = p_in_sup_site_id
    AND ai.invoice_currency_code  = p_in_curr
    AND ai.gl_date                < PERIOD_START_DATE_PARAM
    AND ai.invoice_type_lookup_code  IN ('CREDIT','DEBIT','STANDARD','AWT','EXPENSE REPORT','MIXED')
    AND xte.source_id_int_1       = ai.invoice_id
    AND xte.application_id        = 200
    AND xe.application_id         = 200
    AND xah.application_id        = 200
    AND xal.application_id        = 200
    AND xte.entity_code           = 'AP_INVOICES'
    AND xe.entity_id              = xte.entity_id
    AND xah.entity_id             = xte.entity_id
	AND xe.event_type_code      NOT IN ('PREPAYMENT UNAPPLIED','PREPAYMENT APPLIED')
    AND xah.event_id              = xe.event_id
    AND xal.ae_header_id          = xah.ae_header_id
    AND ai.invoice_id            = NVL(xal.upg_tax_reference_id1,ai.invoice_id)
    AND xal.accounting_class_code = 'LIABILITY'
    AND xal.ledger_id             = ai.set_of_books_id
    AND gcc.code_combination_id   = xal.code_combination_id
    AND gcc.code_combination_id   = p_in_code_comb_id;