CST Margin Analysis Report with Order Management
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Margin Analysis Report
Application: Bills of Material
Source: Margin Analysis Report with Order Management (XML)
Short Name: CSTROMMA_XML
DB package: BOM_CSTROMMA_XMLP_PKG
Description: Margin Analysis Report
Application: Bills of Material
Source: Margin Analysis Report with Order Management (XML)
Short Name: CSTROMMA_XML
DB package: BOM_CSTROMMA_XMLP_PKG
Run
CST Margin Analysis Report with Order Management and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT null Category , null Item_Number , MSI.description Description , MSI.primary_uom_code Primary_Uom_Code , MSI.invoiceable_item_flag Invoiceable_Item_Flag , MSI.shippable_item_flag Shippable_Item_Flag , decode( :P_CUST_OPT, 1, bill_to_customer_name, 2, sold_to_customer_name, 3, ship_to_customer_name, sold_to_customer_name ) Cust_Name , SOT.name Order_Type , DECODE( :P_sort_option, 4, temp.order_number, NULL ) Order_Num_Sort , DECODE( :P_sort_option, 4, decode( :P_CUST_OPT, 1, bill_to_customer_name, 2, sold_to_customer_name, 3, ship_to_customer_name, sold_to_customer_name ), NULL ) Customer_Sort , temp.order_number Order_Num , temp.parent_line_number Line_Num , decode( temp.origin, 1, 'ORDER', 2, 'RMA', 3, 'CR-MEMO' ) Origin , decode( origin, 2, rma_number, 3, temp. cr_trx_id, NULL ) rma_invoice_number , decode( origin, 2, rma_line_number, 3, temp. cr_trx_line_id, NULL ) Rma_Invoice_Line_Number , NVL( SUM( NVL(temp.invoice_quantity,0) ), 0 ) Invoice_Qty , NVL( SUM( NVL(temp.ship_quantity,0) ), 0 ) Ship_Qty , ROUND( SUM( NVL( temp.invoiced_amount, 0 ) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit Sales_Amount , ROUND( SUM( NVL(&p_sql_select_cost_type,0) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit Cogs_Amount , ((ROUND( SUM( NVL( temp.invoiced_amount, 0 ) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit) - (ROUND( SUM( NVL(&p_sql_select_cost_type,0) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit)) Margin_Amount , decode(SUM( NVL( temp.invoiced_amount, 0 ) ),0,0,ROUND((((SUM( NVL( temp.invoiced_amount, 0 ) )) - (SUM( NVL(&p_sql_select_cost_type,0) ))) / SUM( NVL( temp.invoiced_amount, 0 ) ))* :P_exchange_rate / :P_round_unit ) * :P_round_unit ) ORDER_MARGIN_PCT ,msi2.concatenated_segments config_item, --BOM_CSTROMMA_XMLP_PKG.order_margin_pctformula(:ORDER_SALES, :ORDER_MARGIN) ORDER_MARGIN_PCT, --BOM_CSTROMMA_XMLP_PKG.order_sales1formula(:ORDER_SALES, :ORDER_MARGIN_PCT) ORDER_SALES1, --BOM_CSTROMMA_XMLP_PKG.order_cogs1formula(:ORDER_COGS, :ORDER_MARGIN_PCT) ORDER_COGS1, --BOM_CSTROMMA_XMLP_PKG.order_margin1formula(:ORDER_MARGIN, :ORDER_MARGIN_PCT) ORDER_MARGIN1, --BOM_CSTROMMA_XMLP_PKG.itemcatformula(CATEGORY, :CATEGORY_SEGMENT, :CATEGORY_PSEG) ITEMCAT, fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') CATEGORY_PSEG, fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY_SEGMENT, --BOM_CSTROMMA_XMLP_PKG.cat_margin_pctformula(:CAT_SALES, :CAT_MARGIN) CAT_MARGIN_PCT, fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') ITEM_PSEG, fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT --BOM_CSTROMMA_XMLP_PKG.item_sales1formula(:ITEM_SALES, :ITEM_MARGIN_PCT) ITEM_SALES1, --BOM_CSTROMMA_XMLP_PKG.item_cogs1formula(:ITEM_COGS, :ITEM_MARGIN_PCT) ITEM_COGS1, --BOM_CSTROMMA_XMLP_PKG.item_margin1formula(:ITEM_MARGIN, :ITEM_MARGIN_PCT) ITEM_MARGIN1, --BOM_CSTROMMA_XMLP_PKG.item_margin_pctformula(:ITEM_SALES, :ITEM_MARGIN) ITEM_MARGIN_PCT, --BOM_CSTROMMA_XMLP_PKG.avg_unit_priceformula(:ITEM_INVOICE_QTY, :ITEM_SALES) AVG_UNIT_PRICE, --BOM_CSTROMMA_XMLP_PKG.avg_unit_costformula(:ITEM_SHIP_QTY, :ITEM_COGS) AVG_UNIT_COST, --BOM_CSTROMMA_XMLP_PKG.config_item_marginformula(:CONFIG_ITEM_SALES_AMOUNT, :CONFIG_ITEM_COGS_AMOUNT) CONFIG_ITEM_MARGIN, --BOM_CSTROMMA_XMLP_PKG.config_item_margin_pctformula(:CONFIG_ITEM_SALES_AMOUNT, :CONFIG_ITEM_MARGIN) CONFIG_ITEM_MARGIN_PCT, --BOM_CSTROMMA_XMLP_PKG.marginformula(:TOTAL_SALES_AMOUNT, :TOTAL_COGS_AMOUNT) MARGIN, --BOM_CSTROMMA_XMLP_PKG.margin_pctformula(:TOTAL_SALES_AMOUNT, :MARGIN) MARGIN_PCT FROM CST_MARGIN_SUMMARY TEMP &p_sql_from_cost_type , mtl_categories MC , mtl_item_categories MIC , mtl_system_items MSI , mtl_system_items_kfv msi2 , oe_order_lines_all oola , so_order_types_all SOT WHERE oola.item_type_code = 'CONFIG' AND temp.parent_line_id = oola.top_model_line_id AND NVL(temp.parent_ato_flag,'N')='Y' AND :P_CONFIG_ITEM_DETAILS = 1 AND oola.header_id = temp.header_id AND msi2. inventory_item_id = oola.inventory_item_id AND msi2.organization_id = :P_master_org_id AND temp.legal_entity_id >= 0 and &p_sql_where_cost_type and decode(:p_multi_org_flag, 'Y', temp.org_id, 999) = decode(:p_multi_org_flag, 'Y', :p_operating_unit, 999) AND decode(:p_multi_org_flag, 'Y', NVL( SOT.org_id(+), -999 ), 999) = decode(:p_multi_org_flag, 'Y', NVL( :P_Operating_Unit, NVL( sot.org_id(+), -999 ) ), 999) AND SOT.order_type_id( + ) = temp.order_type_id AND MSI.organization_id = :P_master_org_id AND MSI.inventory_item_id = temp.parent_inventory_item_id AND MIC.category_set_id = :P_category_set AND MIC.organization_id = MSI.organization_id AND MIC.inventory_item_id = MSI.inventory_item_id AND MC.category_id = MIC.category_id AND NVL( temp.ORDER_NUMBER, -999 ) = NVL( :P_ORDER_NUMBER, NVL( temp.ORDER_NUMBER, -999 ) ) AND NVL( primary_salesrep_id, -999 ) = NVL( :P_salesrep, NVL( primary_salesrep_id, -999 ) ) AND NVL( temp.customer_id, -999 ) = NVL( :P_customer, NVL( temp.customer_id, -999 ) ) AND NVL( temp.sales_channel_code, -999 ) = NVL( :P_Sales_Channel_Code, NVL( temp.sales_channel_code, -999 ) ) AND NVL( customer_class_code, -999 ) = NVL( :P_Class_Code, NVL( customer_class_code, -999 ) ) AND NVL( temp.territory_id, -999 ) = NVL( :P_Territory_Id, NVL( temp.territory_id, -999 ) ) AND TRUNC(temp.gl_date) between NVL(:lP_date_from ,temp.gl_date-1) and NVL(:lP_date_to,temp.gl_date+1) AND &P_item_where AND &P_cat_where GROUP BY fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') , MSI.description , MSI.primary_uom_code , MSI.invoiceable_item_flag , MSI.shippable_item_flag , decode( :P_CUST_OPT, 1, bill_to_customer_name, 2, sold_to_customer_name, 3, ship_to_customer_name, sold_to_customer_name ) , SOT.name , temp.order_number , msi2.concatenated_segments , temp.parent_line_number , temp.origin , decode( origin, 2, rma_number, 3, temp. cr_trx_id, NULL ) , decode( origin, 2, rma_line_number, 3, temp. cr_trx_line_id, NULL ) having &P_RANGE_WHERE UNION SELECT null Category , null Item_Number , MSI.description Description , MSI.primary_uom_code Primary_Uom_Code , MSI.invoiceable_item_flag Invoiceable_Item_Flag , MSI.shippable_item_flag Shippable_Item_Flag , decode( :P_CUST_OPT, 1, bill_to_customer_name, 2, sold_to_customer_name, 3, ship_to_customer_name, sold_to_customer_name ) Cust_Name , SOT.name Order_Type , DECODE( :P_sort_option, 4, temp.order_number, NULL ) Order_Num_Sort , DECODE( :P_sort_option, 4, decode( :P_CUST_OPT, 1, bill_to_customer_name, 2, sold_to_customer_name, 3, ship_to_customer_name, sold_to_customer_name ), NULL ) Customer_Sort , temp.order_number Order_Num , temp.parent_line_number Line_Num , decode( temp.origin, 1, 'ORDER', 2, 'RMA', 3, 'CR-MEMO' ) Origin , decode( origin, 2, rma_number, 3, temp. cr_trx_id, NULL ) rma_invoice_number , decode( origin, 2, rma_line_number, 3, temp. cr_trx_line_id, NULL ) Rma_Invoice_Line_Number , NVL( SUM( NVL(temp.invoice_quantity,0) ), 0 ) Invoice_Qty , NVL( SUM( NVL(temp.ship_quantity ,0)), 0 ) Ship_Qty , ROUND( SUM( NVL( temp.invoiced_amount, 0 ) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit Sales_Amount , ROUND( SUM( NVL(&p_sql_select_cost_type,0) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit Cogs_Amount , ((ROUND( SUM( NVL( temp.invoiced_amount, 0 ) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit) - (ROUND( SUM( NVL(&p_sql_select_cost_type,0) ) * :P_exchange_rate / :P_round_unit ) * :P_round_unit)) Margin_Amount , decode(SUM( NVL( temp.invoiced_amount, 0 ) ),0,0,ROUND((((SUM( NVL( temp.invoiced_amount, 0 ) )) - (SUM( NVL(&p_sql_select_cost_type,0) ))) / SUM( NVL( temp.invoiced_amount, 0 ) ))* :P_exchange_rate / :P_round_unit ) * :P_round_unit ) ORDER_MARGIN_PCT ,NULL config_item, --BOM_CSTROMMA_XMLP_PKG.order_margin_pctformula(:ORDER_SALES, :ORDER_MARGIN) ORDER_MARGIN_PCT, --BOM_CSTROMMA_XMLP_PKG.order_sales1formula(:ORDER_SALES, :ORDER_MARGIN_PCT) ORDER_SALES1, --BOM_CSTROMMA_XMLP_PKG.order_cogs1formula(:ORDER_COGS, :ORDER_MARGIN_PCT) ORDER_COGS1, --BOM_CSTROMMA_XMLP_PKG.order_margin1formula(:ORDER_MARGIN, :ORDER_MARGIN_PCT) ORDER_MARGIN1, --BOM_CSTROMMA_XMLP_PKG.itemcatformula(CATEGORY, :CATEGORY_SEGMENT, :CATEGORY_PSEG) ITEMCAT, fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') CATEGORY_PSEG, fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY_SEGMENT, --BOM_CSTROMMA_XMLP_PKG.cat_margin_pctformula(:CAT_SALES, :CAT_MARGIN) CAT_MARGIN_PCT, fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') ITEM_PSEG, fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT --BOM_CSTROMMA_XMLP_PKG.item_sales1formula(:ITEM_SALES, :ITEM_MARGIN_PCT) ITEM_SALES1, --BOM_CSTROMMA_XMLP_PKG.item_cogs1formula(:ITEM_COGS, :ITEM_MARGIN_PCT) ITEM_COGS1, --BOM_CSTROMMA_XMLP_PKG.item_margin1formula(:ITEM_MARGIN, :ITEM_MARGIN_PCT) ITEM_MARGIN1, --BOM_CSTROMMA_XMLP_PKG.item_margin_pctformula(:ITEM_SALES, :ITEM_MARGIN) ITEM_MARGIN_PCT, --BOM_CSTROMMA_XMLP_PKG.avg_unit_priceformula(:ITEM_INVOICE_QTY, :ITEM_SALES) AVG_UNIT_PRICE, --BOM_CSTROMMA_XMLP_PKG.avg_unit_costformula(:ITEM_SHIP_QTY, :ITEM_COGS) AVG_UNIT_COST, --BOM_CSTROMMA_XMLP_PKG.config_item_marginformula(:CONFIG_ITEM_SALES_AMOUNT, :CONFIG_ITEM_COGS_AMOUNT) CONFIG_ITEM_MARGIN, --BOM_CSTROMMA_XMLP_PKG.config_item_margin_pctformula(:CONFIG_ITEM_SALES_AMOUNT, :CONFIG_ITEM_MARGIN) CONFIG_ITEM_MARGIN_PCT, --BOM_CSTROMMA_XMLP_PKG.marginformula(:TOTAL_SALES_AMOUNT, :TOTAL_COGS_AMOUNT) MARGIN, --BOM_CSTROMMA_XMLP_PKG.margin_pctformula(:TOTAL_SALES_AMOUNT, :MARGIN) MARGIN_PCT FROM CST_MARGIN_SUMMARY TEMP &p_sql_from_cost_type , mtl_categories MC , mtl_item_categories MIC , mtl_system_items MSI , so_order_types_all SOT WHERE temp.legal_entity_id >= 0 AND ( ( ( NVL(temp.parent_ato_flag,'N') <> 'Y' OR temp.parent_item_type_code <> 'MODEL' ) AND NVL(:P_CONFIG_ITEM_DETAILS,2)=1 ) OR NVL(:P_CONFIG_ITEM_DETAILS,2)=2 ) and &p_sql_where_cost_type and decode(:p_multi_org_flag, 'Y', temp.org_id, 999) = decode(:p_multi_org_flag, 'Y', :p_operating_unit, 999) AND decode(:p_multi_org_flag, 'Y', NVL( SOT.org_id(+), -999 ), 999) = decode(:p_multi_org_flag, 'Y', NVL( :P_Operating_Unit, NVL( sot.org_id(+), -999 ) ), 999) AND SOT.order_type_id( + ) = temp.order_type_id AND MSI.organization_id = :P_master_org_id AND MSI.inventory_item_id = temp.parent_inventory_item_id AND MIC.category_set_id = :P_category_set AND MIC.organization_id = MSI.organization_id AND MIC.inventory_item_id = MSI.inventory_item_id AND MC.category_id = MIC.category_id AND NVL( temp.ORDER_NUMBER, -999 ) = NVL( :P_ORDER_NUMBER, NVL( temp.ORDER_NUMBER, -999 ) ) AND NVL( primary_salesrep_id, -999 ) = NVL( :P_salesrep, NVL( primary_salesrep_id, -999 ) ) AND NVL( temp.customer_id, -999 ) = NVL( :P_customer, NVL( temp.customer_id, -999 ) ) AND NVL( temp.sales_channel_code, -999 ) = NVL( :P_Sales_Channel_Code, NVL( temp.sales_channel_code, -999 ) ) AND NVL( customer_class_code, -999 ) = NVL( :P_Class_Code, NVL( customer_class_code, -999 ) ) AND NVL( temp.territory_id, -999 ) = NVL( :P_Territory_Id, NVL( temp.territory_id, -999 ) ) AND TRUNC(temp.gl_date) between NVL(:lP_date_from,temp.gl_date-1) and NVL(:lP_date_to,temp.gl_date+1) AND &P_item_where AND &P_cat_where GROUP BY fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') , MSI.description , MSI.primary_uom_code , MSI.invoiceable_item_flag , MSI.shippable_item_flag , decode( :P_CUST_OPT, 1, bill_to_customer_name, 2, sold_to_customer_name, 3, ship_to_customer_name, sold_to_customer_name ) , SOT.name , temp.order_number , temp.parent_line_number , temp.origin , decode( origin, 2, rma_number, 3, temp. cr_trx_id, NULL ) , decode( origin, 2, rma_line_number, 3, temp. cr_trx_line_id, NULL ) having &P_RANGE_WHERE |