CST Inventory Master Book

Description

Imported from BI Publisher
Description: Inventory master Book Report
Application: Bills of Material
Source: Inventory Master Book Report (XML)
Short Name: CSTGMSBK_XML
DB package: CST_MGD_MSTR_BOOK_RPT

CST Inventory Master Book and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

      SELECT CMIAKD.transaction_id                                              TRANSACTION_ID
            ,CMIAKD.currency_code                                               CURRENCY_CODE
            ,ROUND(CMIAKD.txn_unit_cost, 2)                                     TXN_UNIT_COST
            ,CMIAKD.txn_date                                                    TXN_DATE
            ,CMIAKD.txn_type                                                    TXN_TYPE
            ,CMIAKD.txn_source                                                  TXN_SOURCE
            ,CMIAKD.txn_ini_qty                                                 TXN_INI_QTY
            ,CST_MGD_MSTR_BOOK_RPT.get_shipment_num (CMIAKD.transaction_id)     SHIPMENT_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_waybill (CMIAKD.transaction_id)          WAYBILL_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'PO')  PO_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'SO')  SALESORDER
            ,CST_MGD_MSTR_BOOK_RPT.get_trx_action (CMIAKD.transaction_id)       TRX_ACTION
            ,ROUND(CMIAKD.txn_ini_unit_cost, 2)                                 TXN_INI_UNIT_COST
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_qty                TXN_INI_VALUE
            ,CMIAKD.txn_fnl_qty                                                 TXN_FNL_QTY
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)                                 TXN_FNL_UNIT_COST
            ,CMIAKD.txn_qty                                                     TXN_QTY
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)  *  CMIAKD.txn_fnl_qty          TXN_FNL_VALUE
            ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0)                           TXN_VALUE
            ,CST_MGD_MSTR_BOOK_RPT.get_break_by                                 BREAK_BY
            ,CST_MGD_MSTR_BOOK_RPT.get_detail_param                             DETAIL_PARAM
            ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost                        INCL_COST
        FROM cst_mgd_mstr_book_temp     CMIAKD
       WHERE CMIAKD.inventory_item_id = :inventory_item_id
         AND :p_break_by              = 1
         AND :p_detail                <> 'S'
      UNION ALL
      SELECT CMIAKD.transaction_id                                              TRANSACTION_ID
            ,CMIAKD.currency_code                                               CURRENCY_CODE
            ,ROUND(CMIAKD.txn_unit_cost, 2)                                     TXN_UNIT_COST
            ,CMIAKD.txn_date                                                    TXN_DATE
            ,CMIAKD.txn_type                                                    TXN_TYPE
            ,CMIAKD.txn_source                                                  TXN_SOURCE
            ,CMIAKD.txn_ini_qty                                                 TXN_INI_QTY
            ,CST_MGD_MSTR_BOOK_RPT.get_shipment_num (CMIAKD.transaction_id)     SHIPMENT_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_waybill (CMIAKD.transaction_id)          WAYBILL_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'PO')  PO_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'SO')  SALESORDER
            ,CST_MGD_MSTR_BOOK_RPT.get_trx_action (CMIAKD.transaction_id)       TRX_ACTION
            ,ROUND(CMIAKD.txn_ini_unit_cost, 2)                                 TXN_INI_UNIT_COST
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_qty                TXN_INI_VALUE
            ,CMIAKD.txn_fnl_qty                                                 TXN_FNL_QTY
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)                                 TXN_FNL_UNIT_COST
            ,CMIAKD.txn_qty                                                     TXN_QTY
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)  *  CMIAKD.txn_fnl_qty          TXN_FNL_VALUE
            ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0)                           TXN_VALUE
            ,CST_MGD_MSTR_BOOK_RPT.get_break_by                                 BREAK_BY
            ,CST_MGD_MSTR_BOOK_RPT.get_detail_param                             DETAIL_PARAM
            ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost                        INCL_COST
        FROM cst_mgd_mstr_book_temp     CMIAKD
       WHERE CMIAKD.inventory_item_id = :inventory_item_id
         AND CMIAKD.organization_id   = :organization_id
         AND :p_break_by              IN (2, 4)
         AND :p_detail                <> 'S'
      UNION ALL
      SELECT CMIAKD.transaction_id                                              TRANSACTION_ID
            ,CMIAKD.currency_code                                               CURRENCY_CODE
            ,ROUND(CMIAKD.txn_unit_cost, 2)                                     TXN_UNIT_COST
            ,CMIAKD.txn_date                                                    TXN_DATE
            ,CMIAKD.txn_type                                                    TXN_TYPE
            ,CMIAKD.txn_source                                                  TXN_SOURCE
            ,CMIAKD.txn_ini_qty                                                 TXN_INI_QTY
            ,CST_MGD_MSTR_BOOK_RPT.get_shipment_num (CMIAKD.transaction_id)     SHIPMENT_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_waybill (CMIAKD.transaction_id)          WAYBILL_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'PO')  PO_NUMBER
            ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'SO')  SALESORDER
            ,CST_MGD_MSTR_BOOK_RPT.get_trx_action (CMIAKD.transaction_id)       TRX_ACTION
            ,ROUND(CMIAKD.txn_ini_unit_cost, 2)                                 TXN_INI_UNIT_COST
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_qty                TXN_INI_VALUE
            ,CMIAKD.txn_fnl_qty                                                 TXN_FNL_QTY
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)                                 TXN_FNL_UNIT_COST
            ,CMIAKD.txn_qty                                                     TXN_QTY
            ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)  *  CMIAKD.txn_fnl_qty          TXN_FNL_VALUE
            ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0)                           TXN_VALUE
            ,CST_MGD_MSTR_BOOK_RPT.get_break_by                                 BREAK_BY
            ,CST_MGD_MSTR_BOOK_RPT.get_detail_param                             DETAIL_PARAM
            ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost                        INCL_COST
        FROM cst_mgd_mstr_book_temp           CMIAKD
       WHERE CMIAKD.inventory_item_id       = :inventory_item_id
         AND CMIAKD.organization_id         = :organization_id
         AND CMIAKD.subinventory_code       = :sub_inv_org_name
         AND CMIAKD.sub_inv_organization_id = :sub_inv_org_id
         AND :p_break_by              IN (3, 5)
         AND :p_detail                <> 'S'
      UNION ALL
      SELECT 0                                                                  TRANSACTION_ID
            ,NULL                                                               CURRENCY_CODE
            ,0                                                                  TXN_UNIT_COST
            ,NULL                                                               TXN_DATE
            ,txn_type2                                                          TXN_TYPE
            ,NULL                                                               TXN_SOURCE
            ,0                                                                  TXN_INI_QTY
            ,NULL                                                               SHIPMENT_NUMBER
            ,NULL                                                               WAYBILL_NUMBER
            ,NULL                                                               PO_NUMBER
            ,NULL                                                               SALESORDER
            ,NULL                                                               TRX_ACTION
            ,0                                                                  TXN_INI_UNIT_COST
            ,0                                                                  TXN_INI_VALUE
            ,0                                                                  TXN_FNL_QTY
            ,0                                                                  TXN_FNL_UNIT_COST
            ,SUM(txn_qty2)                                                      TXN_QTY
            ,0                                                                  TXN_FNL_VALUE
            ,NVL(SUM(txn_value2),0)                                             TXN_VALUE
            ,MAX(break_by2)                                                     BREAK_BY
            ,MAX(detail_param2)                                                 DETAIL_PARAM
            ,MAX(incl_cost2)                                                    INCL_COST
      FROM (      
            SELECT CMIAKD.txn_type                                            TXN_TYPE2
                  ,CMIAKD.txn_qty                                             TXN_QTY2
                  ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0)                   TXN_VALUE2
                  ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)  *  CMIAKD.txn_qty      TXN_FNL_VALUE2
                  ,CST_MGD_MSTR_BOOK_RPT.get_break_by                         BREAK_BY2
                  ,CST_MGD_MSTR_BOOK_RPT.get_detail_param                     DETAIL_PARAM2
                  ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost                INCL_COST2
              FROM cst_mgd_mstr_book_temp     CMIAKD
             WHERE CMIAKD.inventory_item_id = :inventory_item_id
               AND :p_break_by              = 1
               AND :p_detail                = 'S'
         UNION ALL
            SELECT CMIAKD.txn_type                                            TXN_TYPE2
                  ,CMIAKD.txn_qty                                             TXN_QTY2
                  ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0)                   TXN_VALUE2
                  ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)  *  CMIAKD.txn_fnl_qty  TXN_FNL_VALUE2
                  ,CST_MGD_MSTR_BOOK_RPT.get_break_by                         BREAK_BY2
                  ,CST_MGD_MSTR_BOOK_RPT.get_detail_param                     DETAIL_PARAM2
                  ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost                INCL_COST2
              FROM cst_mgd_mstr_book_temp     CMIAKD
             WHERE CMIAKD.inventory_item_id = :inventory_item_id
               AND CMIAKD.organization_id   = :organization_id
               AND :p_break_by              IN (2, 4)
               AND :p_detail                = 'S'
         UNION ALL
            SELECT CMIAKD.txn_type                                            TXN_TYPE2
                  ,CMIAKD.txn_qty                                             TXN_QTY2
                  ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0)                   TXN_VALUE2
                  ,ROUND(CMIAKD.txn_fnl_unit_cost, 2)  *  CMIAKD.txn_fnl_qty  TXN_FNL_VALUE2
                  ,CST_MGD_MSTR_BOOK_RPT.get_break_by                         BREAK_BY2
                  ,CST_MGD_MSTR_BOOK_RPT.get_detail_param                     DETAIL_PARAM2
                  ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost                INCL_COST2
              FROM cst_mgd_mstr_book_temp           CMIAKD
             WHERE CMIAKD.inventory_item_id       = :inventory_item_id
               AND CMIAKD.organization_id         = :organization_id
               AND CMIAKD.subinventory_code       = :sub_inv_org_name
               AND CMIAKD.sub_inv_organization_id = :sub_inv_org_id
               AND :p_break_by              IN (3, 5)
               AND :p_detail                = 'S'
            )
          GROUP BY TXN_TYPE2
       ORDER BY 4, 1 ASC

SELECT CMIAKD.transaction_id TRANSACTION_ID ,CMIAKD.currency_code CURRENCY_CODE ,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST ,CMIAKD.txn_date TXN_DATE ,CMIAKD.txn_type TXN_TYPE ,CMIAKD.txn_source TXN_SOURCE ,CMIAKD.txn_ini_qty TXN_INI_QTY ,CST_MGD_MSTR_BOOK_RPT.get_shipment_num (CMIAKD.transaction_id) SHIPMENT_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_waybill (CMIAKD.transaction_id) WAYBILL_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'PO') PO_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'SO') SALESORDER ,CST_MGD_MSTR_BOOK_RPT.get_trx_action (CMIAKD.transaction_id) TRX_ACTION ,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_qty TXN_INI_VALUE ,CMIAKD.txn_fnl_qty TXN_FNL_QTY ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST ,CMIAKD.txn_qty TXN_QTY ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0) TXN_VALUE ,CST_MGD_MSTR_BOOK_RPT.get_break_by BREAK_BY ,CST_MGD_MSTR_BOOK_RPT.get_detail_param DETAIL_PARAM ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost INCL_COST FROM cst_mgd_mstr_book_temp CMIAKD WHERE CMIAKD.inventory_item_id = :inventory_item_id AND :p_break_by = 1 AND :p_detail <> 'S' UNION ALL SELECT CMIAKD.transaction_id TRANSACTION_ID ,CMIAKD.currency_code CURRENCY_CODE ,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST ,CMIAKD.txn_date TXN_DATE ,CMIAKD.txn_type TXN_TYPE ,CMIAKD.txn_source TXN_SOURCE ,CMIAKD.txn_ini_qty TXN_INI_QTY ,CST_MGD_MSTR_BOOK_RPT.get_shipment_num (CMIAKD.transaction_id) SHIPMENT_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_waybill (CMIAKD.transaction_id) WAYBILL_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'PO') PO_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'SO') SALESORDER ,CST_MGD_MSTR_BOOK_RPT.get_trx_action (CMIAKD.transaction_id) TRX_ACTION ,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_qty TXN_INI_VALUE ,CMIAKD.txn_fnl_qty TXN_FNL_QTY ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST ,CMIAKD.txn_qty TXN_QTY ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0) TXN_VALUE ,CST_MGD_MSTR_BOOK_RPT.get_break_by BREAK_BY ,CST_MGD_MSTR_BOOK_RPT.get_detail_param DETAIL_PARAM ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost INCL_COST FROM cst_mgd_mstr_book_temp CMIAKD WHERE CMIAKD.inventory_item_id = :inventory_item_id AND CMIAKD.organization_id = :organization_id AND :p_break_by IN (2, 4) AND :p_detail <> 'S' UNION ALL SELECT CMIAKD.transaction_id TRANSACTION_ID ,CMIAKD.currency_code CURRENCY_CODE ,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST ,CMIAKD.txn_date TXN_DATE ,CMIAKD.txn_type TXN_TYPE ,CMIAKD.txn_source TXN_SOURCE ,CMIAKD.txn_ini_qty TXN_INI_QTY ,CST_MGD_MSTR_BOOK_RPT.get_shipment_num (CMIAKD.transaction_id) SHIPMENT_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_waybill (CMIAKD.transaction_id) WAYBILL_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'PO') PO_NUMBER ,CST_MGD_MSTR_BOOK_RPT.get_po_number (CMIAKD.transaction_id, 'SO') SALESORDER ,CST_MGD_MSTR_BOOK_RPT.get_trx_action (CMIAKD.transaction_id) TRX_ACTION ,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_qty TXN_INI_VALUE ,CMIAKD.txn_fnl_qty TXN_FNL_QTY ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST ,CMIAKD.txn_qty TXN_QTY ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0) TXN_VALUE ,CST_MGD_MSTR_BOOK_RPT.get_break_by BREAK_BY ,CST_MGD_MSTR_BOOK_RPT.get_detail_param DETAIL_PARAM ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost INCL_COST FROM cst_mgd_mstr_book_temp CMIAKD WHERE CMIAKD.inventory_item_id = :inventory_item_id AND CMIAKD.organization_id = :organization_id AND CMIAKD.subinventory_code = :sub_inv_org_name AND CMIAKD.sub_inv_organization_id = :sub_inv_org_id AND :p_break_by IN (3, 5) AND :p_detail <> 'S' UNION ALL SELECT 0 TRANSACTION_ID ,NULL CURRENCY_CODE ,0 TXN_UNIT_COST ,NULL TXN_DATE ,txn_type2 TXN_TYPE ,NULL TXN_SOURCE ,0 TXN_INI_QTY ,NULL SHIPMENT_NUMBER ,NULL WAYBILL_NUMBER ,NULL PO_NUMBER ,NULL SALESORDER ,NULL TRX_ACTION ,0 TXN_INI_UNIT_COST ,0 TXN_INI_VALUE ,0 TXN_FNL_QTY ,0 TXN_FNL_UNIT_COST ,SUM(txn_qty2) TXN_QTY ,0 TXN_FNL_VALUE ,NVL(SUM(txn_value2),0) TXN_VALUE ,MAX(break_by2) BREAK_BY ,MAX(detail_param2) DETAIL_PARAM ,MAX(incl_cost2) INCL_COST FROM ( SELECT CMIAKD.txn_type TXN_TYPE2 ,CMIAKD.txn_qty TXN_QTY2 ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0) TXN_VALUE2 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_qty TXN_FNL_VALUE2 ,CST_MGD_MSTR_BOOK_RPT.get_break_by BREAK_BY2 ,CST_MGD_MSTR_BOOK_RPT.get_detail_param DETAIL_PARAM2 ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost INCL_COST2 FROM cst_mgd_mstr_book_temp CMIAKD WHERE CMIAKD.inventory_item_id = :inventory_item_id AND :p_break_by = 1 AND :p_detail = 'S' UNION ALL SELECT CMIAKD.txn_type TXN_TYPE2 ,CMIAKD.txn_qty TXN_QTY2 ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0) TXN_VALUE2 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE2 ,CST_MGD_MSTR_BOOK_RPT.get_break_by BREAK_BY2 ,CST_MGD_MSTR_BOOK_RPT.get_detail_param DETAIL_PARAM2 ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost INCL_COST2 FROM cst_mgd_mstr_book_temp CMIAKD WHERE CMIAKD.inventory_item_id = :inventory_item_id AND CMIAKD.organization_id = :organization_id AND :p_break_by IN (2, 4) AND :p_detail = 'S' UNION ALL SELECT CMIAKD.txn_type TXN_TYPE2 ,CMIAKD.txn_qty TXN_QTY2 ,NVL(ROUND(CMIAKD.txn_h_total_cost, 2),0) TXN_VALUE2 ,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE2 ,CST_MGD_MSTR_BOOK_RPT.get_break_by BREAK_BY2 ,CST_MGD_MSTR_BOOK_RPT.get_detail_param DETAIL_PARAM2 ,CST_MGD_MSTR_BOOK_RPT.get_include_item_cost INCL_COST2 FROM cst_mgd_mstr_book_temp CMIAKD WHERE CMIAKD.inventory_item_id = :inventory_item_id AND CMIAKD.organization_id = :organization_id AND CMIAKD.subinventory_code = :sub_inv_org_name AND CMIAKD.sub_inv_organization_id = :sub_inv_org_id AND :p_break_by IN (3, 5) AND :p_detail = 'S' ) GROUP BY TXN_TYPE2 ORDER BY 4, 1 ASC

Parameter Name

SQL text

Validation

Legal Entity

LOV Oracle

Ledger

LOV Oracle

All or Single Inventory Organization

LOV Oracle

Inventory Organization

LOV Oracle

Subinventory From

LOV Oracle

Subinventory To

LOV Oracle

Date From

Date

Date To

Date

Category Set From

LOV Oracle

Category Set To

LOV Oracle

Categories From

Char

Items From

Char

To 2

Char

ABC Assignment Group

LOV Oracle

ABC Class

LOV Oracle

Level of Detail

LOV Oracle

Display Cost

LOV Oracle

Break By

LOV Oracle

Page Numbering

Char

Fiscal Year

Char

Download

Related Blitz Reports

GL Account Analysis (Distributions) INV Movement Statistics Exception CAC Deferred COGS Out-of-Balance INV Material Transactions COPY OF: GL Account Analysis GL Account Analysis FA Mass Additions Delete - draft FA Unposted Mass Additions - draft ECC Oracle Asset Tracking, Oracle Asset Tracking: Assets

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption