APPS.JA_CN_PS_FA_MI_EXP_PKG SQL Statements

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

Line: 87

SELECT
  DISTINCT fth.transaction_header_id
, greatest(greatest( fdp.calendar_period_open_date
                   , least(sysdate, fdp.calendar_period_close_date)
                   )
                   , fdp.calendar_period_open_date
           ) transaction_date
, fth.transaction_type_code
, fa.asset_id
, fa.asset_number
, fa.description
, fa.tag_number
, fth.transaction_name
, fth.transaction_key
, fth.book_type_code
FROM
  fa_transaction_headers  fth
, fa_additions            fa
, fa_books                fb
, fa_book_controls_sec    fbcs
, fa_distribution_history fdh
, fa_deprn_periods        fdp
WHERE fth.transaction_type_code IN
      ('ADDITION', 'ADJUSTMENT', 'RECLASS', 'REINSTATEMENT',
       'REVALUATION', 'TRANSFER', 'UNIT ADJUSTMENT',
       'RESERVE ADJUSTMENT')
  AND fth.asset_id = fa.asset_id
  AND fdh.asset_id = fa.asset_id
  AND fb.asset_id = fa.asset_id
  AND fa.asset_type = 'CAPITALIZED'
  AND fth.book_type_code = fbcs.book_type_code
  AND fth.book_type_code = fdp.book_type_code
  AND fbcs.book_class IN  ('CORPORATE', 'TAX')
  AND fbcs.gl_posting_allowed_flag = 'YES'
  AND fth.date_effective BETWEEN fdp.period_open_date AND
           nvl(fdp.period_close_date, SYSDATE)
  AND (fdh.date_ineffective > fdp.period_open_date OR
           fdh.date_ineffective IS NULL)
  AND  EXISTS
        (SELECT jclllbg.bal_seg_value
           FROM   ja_cn_ledger_le_bsv_gt jclllbg
          WHERE   Ja_Cn_Utility.get_balancing_segment
                  (fdh.code_combination_id) = jclllbg.bal_seg_value
            AND   jclllbg.Ledger_Id = ln_ledger_id
            AND   jclllbg.Legal_Entity_Id = ln_legal_entity_id)
  AND fbcs.set_of_books_id = ln_ledger_id
  AND greatest(greatest(fdp.calendar_period_open_date,
                             least(SYSDATE, fdp.calendar_period_close_date)))
             BETWEEN ld_date_from AND ld_date_to
ORDER BY transaction_date, fth.transaction_header_id;

Line: 168

  SELECT
    start_date
  INTO
    ld_start_date
  FROM
    gl_period_statuses
  WHERE ledger_id = pn_ledger_id
    AND application_id = 101
    AND period_name = pv_period_from
    AND period_year = pv_accounting_year;

Line: 179

  SELECT
    end_date
  INTO
    ld_end_date
  FROM
    gl_period_statuses
  WHERE ledger_id = pn_ledger_id
    AND application_id = 101
    AND period_name = pv_period_to
    AND period_year = pv_accounting_year;

Line: 212

      SELECT
        period_num
      INTO
        lv_period_name
      FROM
        gl_period_statuses gp
      WHERE period_year = pv_accounting_year
        AND application_id = 101
        AND ledger_id = pn_ledger_id
        AND gp.adjustment_period_flag = 'N'
        AND v_row.Transaction_Date BETWEEN gp.start_date AND
             gp.end_date;

Line: 225

      SELECT
        MAX(fdp.period_close_date)
      INTO
        ld_dp_close_date
      FROM
        fa_deprn_periods fdp
      WHERE greatest(greatest(fdp.calendar_period_open_date,
                           least(SYSDATE, fdp.calendar_period_close_date)),
                  fdp.calendar_period_open_date) <= v_row.transaction_date;

Line: 235

      SELECT
        SUM(fdh.units_assigned)
      INTO
        ln_total_unit
      FROM
        fa_distribution_history fdh
      WHERE   fdh.asset_id = v_row.asset_id
        AND   fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
        AND   (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
               fdh.date_ineffective IS NULL);

Line: 246

      SELECT
        SUM(fdh.units_assigned)
      INTO
        ln_assigned_unit
      FROM
        fa_distribution_history fdh
      WHERE   fdh.asset_id = v_row.asset_id
        AND   fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
        AND   (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
               fdh.date_ineffective IS NULL)
        AND   EXISTS
          (SELECT jclllbg.bal_seg_value
           FROM   ja_cn_ledger_le_bsv_gt jclllbg
           WHERE  JA_CN_CFS_DATA_CLT_PKG.
                     get_balancing_segment(fdh.code_combination_id) =
                                                 jclllbg.bal_seg_value
           AND    jclllbg.Ledger_Id = pn_ledger_id
           AND    jclllbg.Legal_Entity_Id = pn_legal_entity_id);

Line: 310

        SELECT current_cost
          INTO lv_after(1)
          FROM fa_transaction_history_books_v
        WHERE (key = v_row.transaction_header_id)
          AND transaction_type = 'Addition';

Line: 322

      SELECT  SUM(fdh.units_assigned)
      INTO    ln_total_unit_before
      FROM    fa_distribution_history fdh
      WHERE   fdh.asset_id = v_row.asset_id
      AND     fdh.transaction_header_id_in < v_row.transaction_header_id
      AND     fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
      AND     (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
              fdh.date_ineffective IS NULL);

Line: 332

      SELECT  SUM(fdh.units_assigned)
      INTO    ln_assigned_unit_before
      FROM    fa_distribution_history fdh
      WHERE   fdh.asset_id = v_row.asset_id
      AND     fdh.transaction_header_id_in < v_row.transaction_header_id
      AND     fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
      AND     (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
              fdh.date_ineffective IS NULL)
      AND EXISTS
          (SELECT jclllbg.bal_seg_value
           FROM   ja_cn_ledger_le_bsv_gt jclllbg
           WHERE  JA_CN_UTILITY.get_balancing_segment
                  (fdh.code_combination_id)= jclllbg.bal_seg_value
           AND    jclllbg.Ledger_Id = pn_ledger_id
           AND    jclllbg.Legal_Entity_Id = pn_legal_entity_id);

Line: 356

        SELECT units
          INTO lv_before(1)
          FROM (
        SELECT fahv.units units
          FROM fa_asset_history_v fahv, fa_categories_b fcb
         WHERE (fahv.key = v_row.transaction_header_id)
           AND fahv.category_id = fcb.category_id
      ORDER BY fahv.transaction_header_id_in)
         WHERE ROWNUM = 1;

Line: 366

        SELECT units
          INTO lv_after(1)
          FROM (
        SELECT fahv.units units
          FROM fa_asset_history_v fahv, fa_categories_b fcb
         WHERE (fahv.key = v_row.transaction_header_id)
           AND fahv.category_id = fcb.category_id
      ORDER BY fahv.transaction_header_id_in DESC)
         WHERE ROWNUM = 1;

Line: 385

         SELECT category
           INTO lv_before(1)
           FROM (
         SELECT fcb.concatenated_segments category
           FROM fa_asset_history_v fahv, fa_categories_b_kfv fcb
          WHERE (fahv.key = v_row.transaction_header_id)
            AND fahv.category_id = fcb.category_id
       ORDER BY fahv.transaction_header_id_in)
          WHERE ROWNUM = 1;

Line: 395

         SELECT category
           INTO lv_after(1)
           FROM (
         SELECT fcb.concatenated_segments  category
           FROM fa_asset_history_v fahv, fa_categories_b_kfv fcb
          WHERE (fahv.key = v_row.transaction_header_id)
            AND fahv.category_id = fcb.category_id
       ORDER BY fahv.transaction_header_id_in DESC)
          WHERE ROWNUM = 1;

Line: 405

       SELECT  concatenated_segment_delimiter
         INTO  lv_delimiter
         FROM  fnd_id_flex_structures
        WHERE  id_flex_code = 'CAT#';

Line: 416

        SELECT cost_retired
          INTO lv_after(1)
          FROM fa_retirements_v
         WHERE 'REINSTATEMENT' = 'REINSTATEMENT'
           AND transaction_header_id_out = v_row.transaction_header_id;

Line: 428

        SELECT current_cost
          INTO lv_before(1)
         FROM (
        SELECT current_cost
          FROM fa_transaction_history_books_v
         WHERE key = v_row.transaction_header_id
      ORDER BY transaction_header_id_in)
         WHERE ROWNUM = 1;

Line: 437

        SELECT current_cost
          INTO lv_after(1)
          FROM (
        SELECT current_cost
          FROM fa_transaction_history_books_v
         WHERE key = v_row.transaction_header_id
      ORDER BY transaction_header_id_in DESC)
         WHERE ROWNUM = 1;

Line: 458

         SELECT trans_units,
                full_name,
                location,
                ccid
           INTO lv_before(1), lv_before(2), lv_before(3), lv_before(4)
           FROM (
         SELECT fdi.trans_units,
                pp.full_name,
                fl.concatenated_segments location,
                gcc.concatenated_segments ccid
           FROM fa_distribution_inquiry_v fdi,
                gl_code_combinations_kfv  gcc,
                per_people_x              pp,
                fa_locations_kfv          fl
          WHERE (transaction_header_id_key = v_row.transaction_header_id)
            AND fdi.assigned_to = pp.person_id(+)
            AND fdi.location_id = fl.location_id(+)
            AND fdi.code_combination_id = gcc.code_combination_id(+)
       ORDER BY transaction_header_id)
          WHERE ROWNUM = 1;

Line: 479

         SELECT trans_units,
                full_name,
                location,
                ccid
           INTO lv_after(1), lv_after(2), lv_after(3), lv_after(4)
           FROM (
         SELECT fdi.trans_units,
                pp.full_name,
                fl.concatenated_segments location,
                gcc.concatenated_segments ccid
           FROM fa_distribution_inquiry_v fdi,
                gl_code_combinations_kfv  gcc,
                per_people_x              pp,
                fa_locations_kfv          fl
          WHERE (transaction_header_id_key = v_row.transaction_header_id)
            AND fdi.assigned_to = pp.person_id(+)
            AND fdi.location_id = fl.location_id(+)
            AND fdi.code_combination_id = gcc.code_combination_id(+)
       ORDER BY transaction_header_id DESC)
          WHERE ROWNUM = 1;

Line: 519

         SELECT  current_cost,
                 salvage_value,
                 revaluation_ceiling,
                 deprn_method_code,
                 life,
                 adjusted_rate,
                 production_capacity,
                 unit_of_measure,
                 prorate_convention_code,
                 date_placed_in_service,
                 depreciate_flag,
                 bonus_rule,
                 ceiling_name
          INTO lv_before(1), lv_before(2), lv_before(3), lv_before(4),
               lv_before(5), lv_before(6), lv_before(7), lv_before(8),
               lv_before(9), lv_before(10), lv_before(11), lv_before(12),
               lv_before(13)
          FROM (
         SELECT  current_cost,
                 salvage_value,
                 revaluation_ceiling,
                 deprn_method_code,
                 life,
                 adjusted_rate,
                 production_capacity,
                 unit_of_measure,
                 prorate_convention_code,
                 date_placed_in_service,
                 depreciate_flag,
                 bonus_rule,
                 ceiling_name
           FROM  fa_transaction_history_books_v
          WHERE  (key = v_row.transaction_header_id)
       ORDER BY  transaction_header_id_in)
          WHERE ROWNUM = 1;

Line: 555

         SELECT  current_cost,
                 salvage_value,
                 revaluation_ceiling,
                 deprn_method_code,
                 life,
                 adjusted_rate,
                 production_capacity,
                 unit_of_measure,
                 prorate_convention_code,
                 date_placed_in_service,
                 depreciate_flag,
                 bonus_rule,
                 ceiling_name
          INTO lv_after(1), lv_after(2), lv_after(3), lv_after(4),
               lv_after(5), lv_after(6), lv_after(7), lv_after(8),
               lv_after(9), lv_after(10), lv_after(11), lv_after(12),
               lv_after(13)
          FROM (
         SELECT  current_cost,
                 salvage_value,
                 revaluation_ceiling,
                 deprn_method_code,
                 life,
                 adjusted_rate,
                 production_capacity,
                 unit_of_measure,
                 prorate_convention_code,
                 date_placed_in_service,
                 depreciate_flag,
                 bonus_rule,
                 ceiling_name
           FROM  fa_transaction_history_books_v
          WHERE  (key = v_row.transaction_header_id)
       ORDER BY  transaction_header_id_in DESC)
          WHERE ROWNUM = 1;

Line: 612

         SELECT fds1.impairment_reserve,
                fds.impairment_reserve
           INTO lv_before(1), lv_after(1)
           FROM fa_deprn_summary fds,
                fa_deprn_summary fds1,
                fa_deprn_periods fdp
          WHERE fds.asset_id = v_row.asset_id
            AND fds1.asset_id = v_row.asset_id
            AND fdp.book_type_code = v_row.book_type_code
            AND fdp.book_type_code = fds.book_type_code
            AND fdp.book_type_code = fds1.book_type_code
            AND v_row.transaction_date
                BETWEEN fdp.calendar_period_open_date AND
                   fdp.calendar_period_close_date
            AND fds.period_counter = fdp.period_counter
            AND fds1.period_counter =
                (SELECT MAX(period_counter)
                   FROM fa_deprn_summary
                  WHERE asset_id = v_row.asset_id
                    AND book_type_code = v_row.book_type_code
                    AND period_counter < fdp.period_counter);

Line: 649

         SELECT adjustment_amount
           INTO lv_after(1)
           FROM fa_financial_inquiry_upl_v
          WHERE (transaction_header_id = v_row.transaction_header_id)
            AND ROWNUM = 1;