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

Main Differences Between OTBI and BIP Reports - Oracle Fusion

BIP Reports are less user-friendly as they require technical expertise to develop, whereas OTBI Reports are more user-friendly and do not require technical expertise. OTBI reports can be developed with just drag-and-drop functionality.

A BIP Report is a combination of two objects: Data Model and Report, whereas an OTBI Report consists of only one object: Analysis.

For BIP Reports, we need to write SQL queries to extract data from Oracle Fusion. In contrast, for OTBI Reports, there is no need to write SQL queries. Users can simply select the columns they want to include in the report using drag-and-drop functionality.

BIP Reports are better suited for complex reports where data must be extracted from multiple tables, while OTBI Reports are suitable for simpler data scenarios involving one or two tables.

In BIP Reports, there is greater flexibility in terms of data extraction, as SQL queries can be written to meet specific requirements. In OTBI Reports, there is limited flexibility, as data can only be extracted from predefined OTBI data sources, and no modifications can be made to data extraction.

In OTBI Reports, SQL queries are generated automatically based on column selection for data extraction. In contrast, for BIP Reports, SQL statements must be written manually as per the requirements.

OTBI Reports are more focused on simple data extraction, while BIP Reports are robust tools that allow for flexible report layout design based on user requirements.

In OTBI Reports, complex report layouts cannot be designed; they are limited to table-based layouts. However, BIP Reports allow the design of any report layout as per requirements.

Statuary reports cannot be designed in OTBI Reports. For statuary reports, the BIP Reports tool must be used.

From a user perspective, the GUI of BIP Reports is much better compared to OTBI Reports.

OTBI Reports can be created very quickly, whereas BIP Reports may take more time for development.

BIP Reports support the feature of report bursting, but OTBI Reports do not.

In BIP Reports, multiple templates can be designed, whereas OTBI Reports support only a single template layout.

OTBI Reports are tied to data roles assigned to the user account, meaning access to data is governed by these roles in Oracle Fusion. In BIP Reports, data access is not tied to user-assigned data roles, allowing users to access data irrespective of their assigned roles.

In terms of security, OTBI Reports are more secure compared to BIP Reports.

 

Oracle SQL + How to split a string in Oracle using substr/instr

 Table Name: GLCC

Column Name: criteria

Column Value: ALAM-**-98000-10301990-***-***************-*****-OPR**

SELECT

   criteria,INSTR(criteria,'-',1,1) AS first_comma,

   INSTR(criteria,'-',1,2) AS second_comma,

   INSTR(criteria,'-',1,3) AS third_comma

   ,INSTR(criteria,'-',1,4) AS fourth_comma,

   INSTR(criteria,'-',1,5) AS fifth_comma,

   SUBSTR(criteria,1,INSTR(criteria,'-',1,1)-1) column1

   ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,1) + 1

             ,INSTR(criteria,'-',1,2)

              - INSTR(criteria,'-',1,1)

              - 1)

       AS column2

         ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,2) + 1

             ,INSTR(criteria,'-',1,3)

              - INSTR(criteria,'-',1,2)

              - 1)

       AS column3       

                ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,3) + 1

             ,INSTR(criteria,'-',1,4)

              - INSTR(criteria,'-',1,3)

              - 1)

       AS column4  

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,4) + 1

             ,INSTR(criteria,'-',1,5)

              - INSTR(criteria,'-',1,4)

              - 1)

       AS column5 

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,5) + 1

             ,INSTR(criteria,'-',1,6)

              - INSTR(criteria,'-',1,5)

              - 1)

       AS column6

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,6) + 1

             ,INSTR(criteria,'-',1,7)

              - INSTR(criteria,'-',1,6)

              - 1)

       AS column7

             ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,7) + 1)

       AS column8

              FROM

    glcc;  

Oracle Fusion + Query to fetch Inventory On-hand Available to Reserve quantity

 select 

  NVL(TR_QTY, 0) - (

    NVL(TR_QTY_B, 0) + NVL(TR_QTY_C, 0)

  ) res_qty 

from 

  (

    SELECT 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348 

          AND subinventory_code IN (

            SELECT 

              SECONDARY_INVENTORY_NAME 

            FROM 

              INV_SECONDARY_INVENTORIES 

            WHERE 

              organization_id = 300000002138348 

              AND reservable_type = '2'

          )

      ) AS TR_QTY_C, 

      (

        SELECT 

          SUM(RESERVATION_QUANTITY) 

        FROM 

          INV_RESERVATIONS 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY_B, 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY 

    from 

      egp_system_items_b MB1 

    where 

      1 = 1 

      AND (

        mb1.organization_id = 300000002138348

      ) 

      AND (

        mb1.inventory_item_id = 100000000615408

      )

  );