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
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 |