CST Inventory Master Book

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Inventory Master Book Report (XML)
Short Name: CSTGMSBK_XML
DB package: CST_MGD_MSTR_BOOK_RPT
      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
Category Structure
 
Number
Dummy
 
LOV Oracle
Fiscal Year
 
Page Numbering
 
Break By
 
LOV Oracle
Display Cost
 
LOV Oracle
Level of Detail
 
LOV Oracle
ABC Class
 
LOV Oracle
ABC Assignment Group
 
LOV Oracle
To
 
Items From
 
To
 
Categories From
 
Category Set To
 
LOV Oracle
Category Set From
 
LOV Oracle
Date To
 
Date
Date From
 
Date
Subinventory To
 
LOV Oracle
Subinventory From
 
LOV Oracle
Inventory Organization
 
LOV Oracle
All or Single Inventory Organization
 
LOV Oracle
Ledger
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ask a question