Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Saturday, April 27, 2019

FA - Query to Fixed Asset Transfer Details

  SELECT book_type_code,
         asset_id,
         asset_number,
         description,
         transnum,
         AVG (to_from) to_from,
         segment1,
         segment2,
         segment3,
         location_segment,
         transaction_date,
         assigned_to,
         ccid,
         SUM (cost) cost,
         SUM (deprn_reserve) deprn_reserve,
         AVG (units) units,
         trans_period_name
    FROM (  SELECT th.book_type_code,
                   ad.asset_id,
                   ad.asset_number,
                   ad.description,
                   th.transaction_header_id transnum,
                   DECODE (th.transaction_header_id,
                           dh.transaction_header_id_in, 1,
                           dh.transaction_header_id_out, 0)
                      to_from,
                   --ACCT_FLEX_ACCT_SEG     GL_ACCOUNT,
                   --ACCT_FLEX_BAL_SEG  COMP_CODE,
                   --ACCT_FLEX_COST_SEG     COST_CENTER,
                   --        LOC_FLEX_ALL_SEG       LOCATION,
                   ascc.segment1,
                   ascc.segment2,
                   ascc.segment3,
                      loc.segment1
                   || '-'
                   || loc.segment2
                   || '-'
                   || loc.segment3
                   || '-'
                   || loc.segment4
                   || '-'
                   || loc.segment5
                   || '-'
                   || loc.segment6
                   || '-'
                   || loc.segment7
                      location_segment,
                   th.transaction_date_entered transaction_date,
                   dh.assigned_to assigned_to,
                   ascc.code_combination_id ccid,
                   SUM (
                      cadj.adjustment_amount
                      * DECODE (cadj.debit_credit_flag,  'CR', -1,  'DR', 1))
                      cost,
                   0 deprn_reserve,
                   SUM (
                      DISTINCT DECODE (th.transaction_header_id,
                                       dh.transaction_header_id_in, 1,
                                       dh.transaction_header_id_out, -1)
                               * dh.units_assigned)
                      units,
                   apps_fa_util_api.
                    get_deprn_period_name_by_date (th.book_type_code,
                                                   th.date_effective)
                      trans_period_name
              FROM fa_locations loc,
                   fa_additions ad,
                   gl_code_combinations ascc,
                   fa_distribution_history dh,
                   fa_transaction_headers th,
                   fa_adjustments cadj
             WHERE                                --th.book_type_code = p_book
                  th.transaction_type_code = 'TRANSFER'
                   ---AND    TH.DATE_EFFECTIVE     >=  :PERIOD1_POD AND    TH.DATE_EFFECTIVE     <=  :PERIOD1_PCD
                   --AND    nvl(TH.MASS_REFERENCE_ID, 0)    =    NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0))
                   AND (th.transaction_header_id = dh.transaction_header_id_in
                        OR th.transaction_header_id =
                              dh.transaction_header_id_out)
                   AND ad.asset_id = th.asset_id
                   AND loc.location_id = dh.location_id
                   AND ascc.code_combination_id = dh.code_combination_id
                   AND cadj.book_type_code = th.book_type_code      ---:p_book
                   AND cadj.asset_id = th.asset_id
                   AND cadj.distribution_id = dh.distribution_id
                   AND cadj.transaction_header_id = th.transaction_header_id
                   AND cadj.source_type_code = 'TRANSFER'
                   AND cadj.adjustment_type IN ('COST', 'CIP COST')
          --LP_WHERE_CLAUSE1
          GROUP BY th.book_type_code,
                   th.transaction_header_id,
                   DECODE (th.transaction_header_id,
                           dh.transaction_header_id_in, 1,
                           dh.transaction_header_id_out, 0),
                   dh.distribution_id,
                   --ACCT_FLEX_ACCT_SEG,
                   --ACCT_FLEX_BAL_SEG,
                   --ACCT_FLEX_COST_SEG,
                   --LOC_FLEX_ALL_SEG,
                   ascc.segment1,
                   ascc.segment2,
                   ascc.segment3,
                      loc.segment1
                   || '-'
                   || loc.segment2
                   || '-'
                   || loc.segment3
                   || '-'
                   || loc.segment4
                   || '-'
                   || loc.segment5
                   || '-'
                   || loc.segment6
                   || '-'
                   || loc.segment7,
                   ad.asset_number,
                   ad.description,
                   th.transaction_date_entered,
                   dh.assigned_to,
                   ascc.code_combination_id,
                   ad.asset_id,
                   apps_fa_util_api.
                    get_deprn_period_name_by_date (th.book_type_code,
                                                   th.date_effective)
          UNION
            SELECT th.book_type_code,
                   ad.asset_id,
                   ad.asset_number,
                   ad.description,
                   th.transaction_header_id transnum,
                   DECODE (th.transaction_header_id,
                           dh.transaction_header_id_in, 1,
                           dh.transaction_header_id_out, 0)
                      to_from,
                   --ACCT_FLEX_ACCT_SEG     GL_ACCOUNT,
                   --ACCT_FLEX_BAL_SEG  COMP_CODE,
                   --ACCT_FLEX_COST_SEG     COST_CENTER,
                   --LOC_FLEX_ALL_SEG       LOCATION,
                   ascc.segment1,
                   ascc.segment2,
                   ascc.segment3,
                      loc.segment1
                   || '-'
                   || loc.segment2
                   || '-'
                   || loc.segment3
                   || '-'
                   || loc.segment4
                   || '-'
                   || loc.segment5
                   || '-'
                   || loc.segment6
                   || '-'
                   || loc.segment7
                      location_segment,
                   th.transaction_date_entered start_date,
                   dh.assigned_to assigned_to,
                   ascc.code_combination_id ccid,
                   0 cost,
                   SUM (
                      NVL (radj.adjustment_amount, 0)
                      * DECODE (NVL (radj.debit_credit_flag, 'CR'),
                                'CR', 1,
                                'DR', -1))
                      deprn_reserve,
                   SUM (
                      DISTINCT DECODE (th.transaction_header_id,
                                       dh.transaction_header_id_in, 1,
                                       dh.transaction_header_id_out, -1)
                               * dh.units_assigned)
                      units,
                   apps_fa_util_api.
                    get_deprn_period_name_by_date (th.book_type_code,
                                                   th.date_effective)
                      trans_period_name
              FROM fa_locations loc,
                   fa_additions ad,
                   gl_code_combinations ascc,
                   fa_distribution_history dh,
                   fa_transaction_headers th,
                   fa_adjustments radj
             WHERE                                --th.book_type_code = p_book
                  th.transaction_type_code = 'TRANSFER'
                   ---    AND    TH.DATE_EFFECTIVE     >=  :PERIOD1_POD AND    TH.DATE_EFFECTIVE     <=  :PERIOD1_PCD
                   --AND    nvl(TH.MASS_REFERENCE_ID, 0)    =   NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0))
                   AND (th.transaction_header_id = dh.transaction_header_id_in
                        OR th.transaction_header_id =
                              dh.transaction_header_id_out)
                   AND ad.asset_id = th.asset_id
                   AND loc.location_id = dh.location_id
                   AND ascc.code_combination_id = dh.code_combination_id
                   AND radj.book_type_code = th.book_type_code      ---:p_book
                   AND radj.asset_id = th.asset_id
                   AND radj.distribution_id = dh.distribution_id
                   AND radj.source_type_code = 'TRANSFER'
                   AND radj.adjustment_type = 'RESERVE'
                   AND radj.transaction_header_id = th.transaction_header_id
          ---LP_WHERE_CLAUSE2
          GROUP BY th.book_type_code,
                   th.transaction_header_id,
                   DECODE (th.transaction_header_id,
                           dh.transaction_header_id_in, 1,
                           dh.transaction_header_id_out, 0),
                   dh.distribution_id,
                   --ACCT_FLEX_ACCT_SEG,
                   --ACCT_FLEX_BAL_SEG,
                   --ACCT_FLEX_COST_SEG,
                   --LOC_FLEX_ALL_SEG,
                   ascc.segment1,
                   ascc.segment2,
                   ascc.segment3,
                      loc.segment1
                   || '-'
                   || loc.segment2
                   || '-'
                   || loc.segment3
                   || '-'
                   || loc.segment4
                   || '-'
                   || loc.segment5
                   || '-'
                   || loc.segment6
                   || '-'
                   || loc.segment7,
                   ad.asset_id,
                   ad.asset_number,
                   ad.description,
                   th.transaction_date_entered,
                   dh.assigned_to,
                   ascc.code_combination_id,
                   apps_fa_util_api.
                    get_deprn_period_name_by_date (th.book_type_code,
                                                   th.date_effective)
          ORDER BY 1,
                   3,
                   5,
                   6,
                   7,
                   8,
                   9,
                   10)
---WHERE book_type_code = :book_type_code_
GROUP BY book_type_code,
         asset_id,
         asset_number,
         description,
         transnum,
         segment1,
         segment2,
         segment3,
         location_segment,
         transaction_date,
         assigned_to,
         ccid,
         trans_period_name
ORDER BY 1,
         3,
         5,
         6,
         7,
         8,
         9,

         10;

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect