CST Inventory Master Book
Description
Categories: BI Publisher
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
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
Run
CST Inventory Master Book and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
To |
|
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 |