CST Margin Analysis Report with Order Management

➜ Back to Library
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Parameter Name SQL text Validation
Report Option 
 
 LOV Oracle
Configuration Item Details 
 
 LOV Oracle
Sort Option 
 
 LOV Oracle
Order Number 
 
 LOV Oracle
Item From 
 
 Char
Item To 
 
 Char
Category Set 
 
 LOV Oracle
Category From 
 
 Char
Category To 
 
 Char
Margin Percentage From 
 
 Number
Margin Percentage To 
 
 Number
Cost Type 
 
 LOV Oracle
Currency 
 
 LOV Oracle
Exchange Rate 
 
 LOV Oracle
Customer Type 
 
 LOV Oracle
Customer Name 
 
 LOV Oracle
Sales Representative 
 
 LOV Oracle
Sales Channel 
 
 LOV Oracle
Industry 
 
 LOV Oracle
Sales Territory 
 
 LOV Oracle
From Date 
 
 Date
To Date 
 
 Date
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: