CST Inventory Master Book
Description
- Categories
- BI Publisher
- Application
- Bills of Material
- Source
- Inventory Master Book Report (XML)
- Short Name
- CSTGMSBK_XML
- DB package
- CST_MGD_MSTR_BOOK_RPT
Imported from BI Publisher
Inventory master Book Report
Inventory master Book Report
SQL 151 lines
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 |
Parameters 21
| Parameter | Type | List of values SQL |
|---|---|---|
| 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 |