CST Margin Analysis Report with Order Management

Description
Categories: BI Publisher
Application: Bills of Material
Source: Margin Analysis Report with Order Management (XML)
Short Name: CSTROMMA_XML
DB package: BOM_CSTROMMA_XMLP_PKG
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