Reports2017-11-18T12:27:27+00:00

CST Margin Analysis Report with Order Management

Description
Categories: BI Publisher, Financials, Manufacturing
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
Parameter Name SQL text Validation
Quantity Precision
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
CST_RPT_DETAILS_DUMMY
 
Operating Unit
 
LOV Oracle
To Date
 
Date
From Date
 
Date
Sales Territory
 
LOV Oracle
Industry
 
LOV Oracle
Sales Channel
 
LOV Oracle
Sales Representative
 
LOV Oracle
Customer Name
 
LOV Oracle
Customer Type
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Cost Type
 
LOV Oracle
Margin Percentage To
 
Number
Margin Percentage From
 
Number
Category To
 
Category From
 
Category Set
 
LOV Oracle
Item To
 
Item From
 
Order Number
 
LOV Oracle
Sort Option
 
LOV Oracle
Configuration Item Details
 
LOV Oracle
Report Option
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept