Fixed Assets few useful Queries in Oracle APPS

SELECT *

   FROM fa_additions_b

  WHERE asset_number = p_asset_number;

SELECT *

   FROM fa_additions_tl

  WHERE asset_id = p_asset_id

    AND LANGUAGE = USERENV('LANG');

  SELECT *

    FROM fa_transaction_headers

   WHERE asset_id = p_asset_id

     AND book_type_code = p_book_type_code

ORDER BY book_type_code, date_effective;

SELECT *

  FROM fa_asset_history

 WHERE asset_id = p_asset_id;

 SELECT *

   FROM fa_adjustments

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_books

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_deprn_summary

   WHERE asset_id = p_asset_id

     AND book_type_code = p_book_type_code

  ORDER BY deprn_run_date;

SELECT *

    FROM fa_deprn_summary_h

   WHERE asset_id = p_asset_id;

SELECT *

    FROM fa_deprn_detail

   WHERE asset_id = p_asset_id

  ORDER BY book_type_code, deprn_run_date;

SELECT *

    FROM fa_deprn_detail_h

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_deprn_events

   WHERE asset_id = p_asset_id

ORDER BY book_type_code, deprn_run_date;

SELECT *

  FROM fa_asset_invoices

 WHERE asset_id = p_asset_id;

SELECT *

  FROM fa_invoice_transactions

 WHERE invoice_transaction_id IN (SELECT DISTINCT invoice_transaction_id_in

                                    FROM fa_asset_invoices

                                   WHERE asset_id = p_asset_id);

  SELECT *

    FROM fa_books_summary

   WHERE asset_id = p_asset_id

ORDER BY book_type_code, period_counter;

  SELECT *

    FROM fa_deprn_periods

   WHERE book_type_code = (SELECT DISTINCT book_type_code

                             FROM fa_deprn_detail_h

                            WHERE asset_id = p_asset_id)

ORDER BY period_counter;

SELECT *

  FROM fa_mass_additions

 WHERE asset_number = p_asset_number;

SELECT *

  FROM fa_massadd_distributions

 WHERE mass_addition_id IN (SELECT DISTINCT mass_addition_id

                              FROM FA_MASS_ADDITIONS

                             WHERE asset_number = p_asset_number);

SELECT *

  FROM fa_book_controls

 WHERE book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id);

SELECT *

  FROM fa_book_controls_history

 WHERE book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                          );

SELECT *

  FROM fa_categories_b

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number);

SELECT *

  FROM fa_categories_tl

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                        );

SELECT *

  FROM fa_category_books

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                            )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                          );

SELECT *

  FROM fa_category_book_defaults

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                      )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                        );

SELECT *

  FROM fa_calendar_periods

 WHERE calendar_type =

          (SELECT deprn_calendar

             FROM fa_book_controls

            WHERE book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                     )

          );

SELECT *

  FROM fa_conventions

 WHERE prorate_convention_code =

          (SELECT DISTINCT prorate_convention_code

             FROM fa_category_books

            WHERE category_id IN (SELECT DISTINCT asset_category_id

                                    FROM fa_additions_b

                                   WHERE asset_number = p_asset_number

                                     )

              AND book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                    )

          );

SELECT *

  FROM fa_methods

 WHERE     method_code =

              (SELECT DISTINCT deprn_method

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =p_asset_number

                                         )

                  AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

              )

       AND life_in_months =

              (SELECT DISTINCT life_in_months

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =

                                                    p_asset_number)

                      AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

            );

SELECT *

    FROM xla_events

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code)

ORDER BY event_date;

SELECT *

    FROM ( (SELECT *

              FROM xla_transaction_entities

             WHERE source_id_int_1 IN (SELECT DISTINCT

                                                  transaction_header_id

                                         FROM fa_transaction_headers

                                        WHERE asset_id = p_asset_id

                                          AND book_type_code = p_book_type_code

                                          AND event_id IS NOT NULL

                                       )

               AND source_id_char_1 = p_book_type_code

             )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE     source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             AND event_id IS NOT NULL

                                             )

                  AND source_id_char_1 = p_book_type_code

                  AND source_id_int_1 = p_asset_id

           )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary_h

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             )

             AND source_id_char_1 = p_book_type_code

             AND source_id_int_1 = p_asset_id

                  )

       )

ORDER BY creation_date;

SELECT *

    FROM xla_ae_headers

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                       )

ORDER BY accounting_date;

Exchange Rate Handling in Oracle Cloud

 Please find below a summary regarding exchange rate handling in Oracle Cloud:

• The exchange rate values in both RA_CUSTOMER_TRX_ALL and GL_DAILY_RATES are identical.

• However, the Fusion UI typically displays the exchange rate rounded to 6 decimal places. To ensure consistency, it is recommended to use ROUND(exchange_rate, 6) in custom reports or queries.

• The AutoInvoice interface (RA_INTERFACE_LINES_ALL) does not apply rounding by default, which can result in high-precision values being inserted—particularly when CONVERSION_TYPE is set to 'User' or a custom type.

Recommendations to Avoid High-Precision Exchange Rates:

• Round the exchange rate in FBDI templates, APIs, or integration sources before loading data.

• Ensure values in GL_DAILY_RATES are stored with a consistent precision (ideally up to 6 decimal places).

• Use ROUND(exchange_rate, 6) in all custom queries and reports to match how values are displayed in the Fusion UI.

Oracle EBS + Query to Find List of XML Publisher Reports

 SELECT fe.executable_name,'Data Template (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'K'

AND fe.executable_name = 'XDODTEXE'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'PLSQL (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'I'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'RDF (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'P'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%');

Regards,

Amar Alam

Oracle Fusion + Sales Order Audit Report Query + Sales Order Fulfillment Lines EFF Query

 SELECT * FROM 

(SELECT --dla.line_id,

       --dha.header_id,

   dha.order_number "Order Number",

       hp.party_name "customer",

       hp.country "country",

   (NVL(dla.ordered_qty,0) * NVL(dla.unit_selling_price,0)) amount,

  (SELECT attribute_char3

   FROM doo_headers_eff_b dheb

   WHERE dheb.header_id = dha.header_id

     AND CONTEXT_CODE='Subscription')  region,

       Revenue_Management.attribute_char8 "workday contract name",

       Subscription.attribute_char1 "billing_offset",

       ACCOUNTS_RECEIVABLES.attribute_char1 "invoice group by number",

       ACCOUNTS_RECEIVABLES.attribute_char2 "invoice group by description",

       ACCOUNTS_RECEIVABLES.attribute_char3 "management group",

        SF_SUBSCRIPTION.ATTRIBUTE_CHAR10 "align billing from",

            SF_SUBSCRIPTION.attribute_char11 "custom billing",

                 Conversion.ATTRIBUTE_CHAR4 "Misc info",

                 Conversion.attribute_char2 "PS billed amount",

                 Conversion.attribute_char3 "PS unbilled amount",

                 Conversion.attribute_char5 "supplier purchase order",

                 TO_CHAR(SNOW.attribute_timestamp1,'MM/DD/YY HH:MI') "cloud provision date",

                 TO_CHAR(SNOW.attribute_timestamp2,'MM/DD/YY HH:MI') "on-perm provision date",

                 IB.attribute_char2 "asset line identifer",

                 IB.attribute_char3 "life cycle pid ",

                 SFDC.attribute_char1 "PO requied",

 SFDC.attribute_char2 "PO Number",

 SFDC.attribute_char3 "PO Line Number",

                 SFDC.attribute_char4 "invoice trigger event",

                 SFDC.attribute_char5 "site id",

                 SFDC.attribute_char6 "bundle id",

                 SFDC.attribute_char7 "advanced billing flag",

                 SFDC.attribute_char10 "used inventory flag",

                 SFDC.attribute_char11 "GSS validation flag",

                 SFDC.attribute_char16 "suppress fulfillment",

                 Procurement.attribute_char1 "cabinet position",

                 Procurement.attribute_char2 "PO vendor cost/transfer price",

                 Procurement.attribute_char3 "related manual order",

                 Tax.attribute_char2 "0% tax  rate flag",

                 BRAZIL.attribute_char1 "CFOP-line order",

                 CSP.attribute_char1 "CSP flag",

                 CSP.attribute_char3 "private order flag",

     esib.item_number,

 esib.description item_description,

 bill_to_party.party_name bill_to_customer,

 ship_to_party.party_name ship_to_customer,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR5 sf_subscription_number,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR2  "SF Line Identifier (Model)",

 revenue_management.attribute_char6 "Rev Contract Grouping" ,

 (SELECT header_curr_duration_ext_amt

 FROM doo_order_pricing_details_v dopdv

 WHERE dopdv.fulfill_line_id = dfla.fulfill_line_id

                 AND dopdv.PRICE_ELEMENT_CODE='QP_NET_PRICE'

                 AND dopdv.ROLLUP_FLAG ='Y' ) Total_Amount_for_Duration

FROM doo_headers_all dha,

     doo_lines_all dla,

     doo_fulfill_lines_all dfla,

     doo_fulfill_lines_eff_b ACCOUNTS_RECEIVABLES,

     doo_fulfill_lines_eff_b Revenue_Management,

     doo_fulfill_lines_eff_b Subscription,

     doo_fulfill_lines_eff_b CONVERSION,

     doo_fulfill_lines_eff_b SFDC,

 doo_fulfill_lines_eff_b SNOW,

     doo_fulfill_lines_eff_b IB,

     doo_fulfill_lines_eff_b Procurement,

     doo_fulfill_lines_eff_b Tax,

     doo_fulfill_lines_eff_b BRAZIL,

     doo_fulfill_lines_eff_b CSP,

 doo_fulfill_lines_eff_b SF_SUBSCRIPTION,

     hz_parties hp,

 egp_system_items esib,

     doo_order_addresses bill_to,

 doo_order_addresses ship_to,

 hz_cust_accounts bill_to_cust,

 hz_parties bill_to_party,

 hz_parties ship_to_party

WHERE 1=1

  ---AND dha.order_number='00026264'

  AND dha.header_id=dla.header_id

  AND dla.line_id= dfla.line_id

  AND dfla.fulfill_line_id = accounts_receivables.fulfill_line_id (+)

  AND accounts_receivables.context_code (+)='ACCOUNTS RECEIVABLES'

  AND dfla.fulfill_line_id = Revenue_Management.fulfill_line_id (+)

  AND Revenue_Management.context_code (+)='Revenue_Management_Information_Line'

  AND dfla.fulfill_line_id = Subscription.fulfill_line_id (+)

  AND Subscription.context_code (+)='Subscription'

  AND dfla.fulfill_line_id = Conversion.fulfill_line_id (+)

  AND Conversion.context_code (+)='Conversion'

  AND dfla.fulfill_line_id = SFDC.fulfill_line_id (+)

  AND SFDC.context_code (+)='SFDC'

  AND dfla.fulfill_line_id = SNOW.fulfill_line_id (+)

  AND SNOW.context_code (+)='SNOW'

  AND dfla.fulfill_line_id = IB.fulfill_line_id (+)

  AND IB.context_code (+)='IB - Asset Line Identifier Model'

  AND dfla.fulfill_line_id = Procurement.fulfill_line_id (+)

  AND Procurement.context_code (+)='Procurement'

  AND dfla.fulfill_line_id = Tax.fulfill_line_id (+)

  AND Tax.context_code (+)='Tax'

  AND dfla.fulfill_line_id = BRAZIL.fulfill_line_id (+)

  AND BRAZIL.context_code (+)='BRAZIL-Localization'

  AND dfla.fulfill_line_id = CSP.fulfill_line_id (+)

  AND CSP.context_code (+)='CSP Details'

  AND dfla.fulfill_line_id = SF_SUBSCRIPTION.fulfill_line_id (+)

  AND SF_SUBSCRIPTION.context_code (+)='SF Subscription - SF Line Identifier Model'

  AND dha.sold_to_party_id = hp.party_id 

  AND dla.inventory_organization_id =esib.organization_id

  and dla.inventory_item_id = esib.inventory_item_id

  AND dha.header_id = bill_to.header_id (+)

  AND bill_to.address_use_type (+)= 'BILL_TO'

  AND bill_to.cust_acct_id = bill_to_cust.cust_account_id (+)

  AND bill_to_cust.party_id = bill_to_party.party_id

  AND dha.header_id = ship_to.header_id (+)

  AND ship_to.address_use_type (+) = 'SHIP_TO'

  AND ship_to.party_id = ship_to_party.party_id (+)

 ) 

  WHERE region = NVL(:p_region,region);

Regards,

Amar Alam