APPS.FV_GEN_ARTRX_REIMB_PROC SQL Statements

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

Line: 154

    SELECT period_year, period_num
    INTO g_period_year, g_period_num
    FROM gl_period_statuses
    WHERE application_id = 101
    AND set_of_books_id = g_ledger_id
    AND period_name = g_period_name;

Line: 167

   select CHART_OF_ACCOUNTS_ID
   into g_coa_id
   from gl_ledgers
   where  ledger_id = g_ledger_id;

Line: 176

    SELECT REC_DUE_TRANSACTION_TYPE_ID,
    LIQ_ADV_TRANSACTION_TYPE_ID,
    REIM_TRANSACTION_SOURCE_ID,
    REC_DUE_PREFIX,
    LIQ_ADV_PREFIX
    INTO
    g_rec_due_trx_type_id,
    g_liq_adv_trx_type_id,
    g_trx_source_id,
    g_rec_due_prefix,
    g_liq_adv_prefix
    From FV_OPERATING_UNITS_ALL
    where set_of_books_id = g_ledger_id
    and org_id = g_org_id;

Line: 202

      SELECT application_column_name
      INTO g_bfy_segment
      FROM fv_pya_fiscalyear_segment
     WHERE set_of_books_id = g_ledger_id;

Line: 225

         SELECT  flex_value_set_id into g_ussgl_flex_value_set_id
         FROM    fnd_id_flex_segments
         WHERE   application_column_name = g_gl_nat_acc_segment
        AND     application_id      = 101
        AND     id_flex_code        = 'GL#'
        AND     id_flex_num         = g_coa_id;

Line: 242

      SELECT application_column_name
      INTO   g_reimb_agreement_segment
      FROM   FND_ID_FLEX_SEGMENTS_VL
      WHERE  application_id         = 101
      AND    id_flex_code           = 'GL#'
      AND    id_flex_num            = g_coa_id
      AND    enabled_flag           = 'Y'
      AND    segment_name like
        (Select REIMB_AGREEMENT_SEGMENT_VALUE
         from fv_reimb_segment
         where set_of_books_id = g_ledger_id);

Line: 265

Select
ADVANCE_SEGMENT_VALUE,
REVENUE_SEGMENT_VALUE,
EXPENDITURE_SEGMENT_VALUE
INTO
g_advance_acc,
g_revenue_acc,
g_expenditure_acc
from fv_reimb_segment
where set_of_books_id = g_ledger_id;

Line: 288

        select count(distinct(FLEX_VALUE)) into l_exp_child
        from FND_FLEX_VALUE_CHILDREN_V
        where FLEX_VALUE_SET_ID = g_ussgl_flex_value_set_id
        start with PARENT_FLEX_VALUE = g_expenditure_acc
        connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE;

Line: 296

        l_sql_exp_child := 'select distinct(FLEX_VALUE)
        from FND_FLEX_VALUE_CHILDREN_V
        where FLEX_VALUE_SET_ID = '||g_ussgl_flex_value_set_id||'
        start with PARENT_FLEX_VALUE = '||''''||g_expenditure_acc||''''||'
        connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE';

Line: 316

        select count(distinct(FLEX_VALUE)) into l_rev_child
        from FND_FLEX_VALUE_CHILDREN_V
        where FLEX_VALUE_SET_ID = g_ussgl_flex_value_set_id
        start with PARENT_FLEX_VALUE = g_revenue_acc
        connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE;

Line: 325

        l_sql_rev_child := 'select distinct(FLEX_VALUE)
        from FND_FLEX_VALUE_CHILDREN_V
        where FLEX_VALUE_SET_ID = '||g_ussgl_flex_value_set_id||'
        start with PARENT_FLEX_VALUE = '||''''||g_revenue_acc||''''||'
        connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE';

Line: 344

        select count(distinct(FLEX_VALUE)) into l_adv_child
        from FND_FLEX_VALUE_CHILDREN_V
        where FLEX_VALUE_SET_ID = g_ussgl_flex_value_set_id
        start with PARENT_FLEX_VALUE = g_advance_acc
        connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE;

Line: 352

        l_sql_adv_child := 'select distinct(FLEX_VALUE)
        from FND_FLEX_VALUE_CHILDREN_V
        where FLEX_VALUE_SET_ID = '||g_ussgl_flex_value_set_id||'
        start with PARENT_FLEX_VALUE = '||''''||g_advance_acc||''''||'
        connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE';

Line: 371

                'SELECT f.flex_value
                 FROM  fnd_flex_values_vl f, fnd_id_flex_segments segs, ra_customer_trx_all r,
                      ra_cust_trx_types_all t
                WHERE f.flex_value_set_id =segs.flex_value_set_id AND
                    segs.application_column_name = :g_reimb_agreement_segment AND
                    segs.application_id      = 101 AND
                    segs.id_flex_code        = ''GL#'' AND
                    segs.id_flex_num         = :g_coa_id AND
                    f.flex_value = r.trx_number AND
                    r.set_of_books_id = :g_ledger_id AND
                    r.invoice_currency_code = :g_currency AND
                    r.cust_trx_type_id = t.cust_trx_type_id AND
                    t.type = ''GUAR''';

Line: 388

l_sql_glbal := '(SELECT
sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_exp_child ||') then (nvl(period_net_dr,0) - nvl(period_net_cr,0)) else 0 end) expenses,
sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_rev_child ||') then (nvl(period_net_dr,0) - nvl(period_net_cr,0)) else 0 end) revenues,
sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_adv_child ||') then (nvl(period_net_dr,0) - nvl(period_net_cr,0)) else 0 end) advances
       FROM gl_balances glb,
       gl_code_combinations glc
  WHERE glb.actual_flag = ''A''
    and glb.ledger_id = :g_ledger_id
    AND glb.template_id is NULL
    AND glb.currency_code = ''USD''
    AND glb.code_combination_id = glc.code_combination_id
    and glc.chart_of_accounts_id = :g_coa_id
    and glc.'||g_gl_balancing_segment||' <> ''0''
    and glb.period_year = :g_period_year
    and glc.'||g_reimb_agreement_segment||' = :g_agreement)';

Line: 408

l_sql_glbc := '(SELECT
sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_exp_child ||') then (nvl(accounted_dr,0) - nvl(accounted_cr,0)) else 0 end) expenses,
sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_rev_child ||') then (nvl(accounted_dr,0) - nvl(accounted_cr,0)) else 0 end) revenues,
sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_adv_child ||') then (nvl(accounted_dr,0) - nvl(accounted_cr,0)) else 0 end) advances
       FROM gl_bc_packets glbc,
       gl_code_combinations glc
  WHERE glbc.actual_flag = ''A''
    and glbc.ledger_id = :g_ledger_id
    AND glbc.template_id is NULL
    AND glbc.status_code = ''A''
    AND glbc.currency_code = ''USD''
    AND glbc.code_combination_id = glc.code_combination_id
    and glc.chart_of_accounts_id = :g_coa_id
    and glc.'||g_gl_balancing_segment||' <> ''0''
    and glbc.period_year = :g_period_year
    and glc.'||g_reimb_agreement_segment||' = :g_agreement)';

Line: 429

l_sql_balances:='select sum(expenses) tot_exp, sum(revenues) tot_rev,
                  sum(advances) tot_adv from
                  ('||l_sql_glbal||' UNION ALL '||l_sql_glbc||')';

Line: 449

        g_coll_hdr_tbl.DELETE;

Line: 450

        g_rec_hdr_tbl.DELETE;

Line: 451

        g_coll_lines_tbl.DELETE;

Line: 452

        g_rec_lines_tbl.DELETE;

Line: 453

        g_coll_dist_tbl.DELETE;

Line: 454

        g_rec_dist_tbl.DELETE;

Line: 461

               SELECT bill_to_customer_id
               INTO   g_customer_id
               FROM   ra_customer_trx_all
               WHERE  trx_number = g_agreement_num
               AND invoice_currency_code = g_currency;

Line: 630

    SELECT * FROM ar_trx_errors_gt;

Line: 687

           'SELECT ' || g_reimb_agreement_segment || '
            FROM gl_code_combinations
           WHERE code_combination_id = ' ||p_trx_dist_tbl(i).code_combination_id;

Line: 700

          Select rtt.name into l_terms
          from ra_customer_trx_all rct,
          ra_terms rtt
          where rct.term_id=rtt.term_id
          and rct.trx_number = l_reimb_agree_num
          and rct.set_of_books_id = g_ledger_id;

Line: 713

          select hzp.party_name into l_customer_name
          from hz_parties hzp ,
          HZ_CUST_ACCounts  hza
          where hzp.party_id = hza.party_id
          and hza.cust_account_id = p_trx_header_tbl(1).bill_to_customer_id;

Line: 723

          Select name into l_trx_type
          from ra_cust_trx_types_all
          where cust_trx_type_id = p_trx_header_tbl(1).cust_trx_type_id;

Line: 762

            SELECT count(*)
            Into l_cnt
            From ar_trx_errors_gt;

Line: 851

               SELECT rctd.code_combination_id
               into l_agreement_rev_ccid
               FROM   ra_customer_trx_all rct,
                      RA_CUST_TRX_LINE_GL_DIST_ALL   rctd
               WHERE  rct.trx_number = p_agreement
               AND rct.customer_trx_id = rctd.customer_trx_id
               AND rctd.account_class = 'REV'
               AND rctd.set_of_books_id = g_ledger_id;

Line: 864

    'SELECT g.'||g_gl_nat_acc_segment||'
    FROM   ra_cust_trx_types_all t,
           gl_code_combinations g
    WHERE  t.cust_trx_type_id = :p_trx_type_id
    AND    t.gl_id_rev = g.code_combination_id
    and    g.chart_of_accounts_id = :g_coa_id';

Line: 900

    SELECT code_combination_id
    INTO   l_ccid
    FROM   gl_code_combinations_kfv
    WHERE  chart_of_accounts_id = g_coa_id
    AND concatenated_segments = l_concat_segs;

Line: 942

     SELECT fv_gen_ar_trx_s.nextval
     INTO   g_trx_coll_hdr_id
     FROM DUAL;

Line: 950

     SELECT g_liq_adv_prefix||fv_gen_coll_ar_trx_s.nextval
     INTO   g_coll_hdr_tbl(1).trx_number
     FROM DUAL;

Line: 969

     SELECT fv_gen_ar_trx_s.nextval
     INTO   g_trx_rec_hdr_id
     FROM DUAL;

Line: 978

     SELECT g_rec_due_prefix||fv_gen_rec_ar_trx_s.nextval
     INTO   g_rec_hdr_tbl(1).trx_number
     FROM DUAL;

Line: 1013

    SELECT ra_customer_trx_lines_s.nextval
    INTO   g_coll_lines_tbl(i).trx_line_id
    FROM DUAL;

Line: 1029

    SELECT ra_cust_trx_line_gl_dist_s.nextval
    INTO   g_coll_dist_tbl(i).trx_dist_id
    FROM DUAL;

Line: 1050

    SELECT ra_customer_trx_lines_s.nextval
    INTO   g_rec_lines_tbl(j).trx_line_id
    FROM DUAL;

Line: 1071

    SELECT ra_cust_trx_line_gl_dist_s.nextval
    INTO   g_rec_dist_tbl(j).trx_dist_id
    FROM DUAL;