QA Quality Specifications

select  qsv.spec_name "Characteristic Group",
        qsv.EFFECTIVE_FROM "Effective From",
        qsv.EFFECTIVE_TO "Effective To",
        qsv.ASSIGNMENT_TYPE "Assignment Type",
        qsv.ASSIGNMENT_TYPE_MEANING "Spec Type",
        null c_flexdata,
        qsv.ITEM_REVISION "Item Revision",
        qsv.ITEM_DESCRIPTION "Item Description",
        qsv.VENDOR_NUMBER "Supplier Number",
        qsv.VENDOR_NAME "Supplier Name",
        qsv.CUSTOMER_NUMBER "Customer Number",
        qsv.CUSTOMER_NAME "Customer Name",
        null c_category_flexdata,
        mc.DESCRIPTION "Category Description",
        qsv.category_set_name "Category Set",
        qsv.COMMON_SPEC_NAME "Common Spec Name",
        qscv.CHAR_NAME "Char Name",
        qscv.Datatype "DATATYPE",
        qscv.uom_code "UOM",
        decode(qscv.datatype,
            3, qltdate.any_to_user(qscv.target_value),
            qscv.target_value)
            "Target Value",
        decode(qscv.datatype,
            3, qltdate.any_to_user(qscv.upper_spec_limit),
            qscv.upper_spec_limit)
            "Upper Spec Limit" ,
        decode(qscv.datatype,
            3, qltdate.any_to_user(qscv.lower_spec_limit),
            qscv.lower_spec_limit)
            "Lower Spec Limit",
        decode(qscv.datatype,
            3, qltdate.any_to_user(qscv.upper_reasonable_limit),
            qscv.upper_reasonable_limit)
            "Upper Reasonable Limit",
        decode(qscv.datatype,
            3, qltdate.any_to_user(qscv.lower_reasonable_limit),
            qscv.lower_reasonable_limit)
            "Lower Reasonable Limit",
        decode(qscv.datatype,
            3, qltdate.any_to_user(qscv.upper_user_defined_limit),
            qscv.upper_user_defined_limit)
            "Upper User Defined Limit",
        decode(qscv.datatype,
            3, qltdate.any_to_user(qscv.lower_user_defined_limit),
            qscv.lower_user_defined_limit)
            "Lower User Defined Limit",
        qscv.DECIMAL_PRECISION, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_desc', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'DESCRIPTION') C_Item_Desc, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_flex', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_category_desc', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'DESCRIPTION') C_CATEGORY_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_category_flex', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_CATEGORY_FLEX, 
	QA_QLTSPECR_XMLP_PKG.c_target_valueformula(qscv.Datatype, decode ( qscv.datatype , 3 , qltdate.any_to_user ( qscv.target_value ) , qscv.target_value )) C_target_value, 
	QA_QLTSPECR_XMLP_PKG.c_upper_spec_numberformula(qscv.Datatype, decode ( qscv.datatype , 3 , qltdate.any_to_user ( qscv.upper_spec_limit ) , qscv.upper_spec_limit )) C_upper_spec_Number, 
	QA_QLTSPECR_XMLP_PKG.c_lower_spec_numberformula(qscv.Datatype, decode ( qscv.datatype , 3 , qltdate.any_to_user ( qscv.lower_spec_limit ) , qscv.lower_spec_limit )) C_lower_spec_Number, 
	QA_QLTSPECR_XMLP_PKG.c_upper_user_numberformula(qscv.Datatype, decode ( qscv.datatype , 3 , qltdate.any_to_user ( qscv.upper_user_defined_limit ) , qscv.upper_user_defined_limit )) C_Upper_User_Number, 
	QA_QLTSPECR_XMLP_PKG.c_lower_user_numberformula(qscv.Datatype, decode ( qscv.datatype , 3 , qltdate.any_to_user ( qscv.lower_user_defined_limit ) , qscv.lower_user_defined_limit )) C_Lower_User_Number, 
	QA_QLTSPECR_XMLP_PKG.c_upper_reason_numberformula(qscv.Datatype, decode ( qscv.datatype , 3 , qltdate.any_to_user ( qscv.upper_reasonable_limit ) , qscv.upper_reasonable_limit )) C_Upper_Reason_Number, 
	QA_QLTSPECR_XMLP_PKG.c_lower_reason_numberformula(qscv.Datatype, decode ( qscv.datatype , 3 , qltdate.any_to_user ( qscv.lower_reasonable_limit ) , qscv.lower_reasonable_limit )) C_Lower_Reason_Number,
	qa_common_xmlp_pkg.get_precision(qscv.DECIMAL_PRECISION) qty_precision
from    mtl_categories mc,
        mtl_system_items msi2,
        qa_spec_chars_v qscv,
        qa_specs_v qsv
where   qscv.spec_id = qsv.common_spec_id
and     mc.category_id (+) = qsv.category_id
and     msi2.Inventory_Item_id (+) = qsv.item_id
and     msi2.Organization_id (+) = qsv.organization_id
&P_Category_Set_limiter
&P_Spec_Name_limiter
and &P_Item_limiter
&P_Spec_Type_limiter
&P_Vendor_Number_limiter
&P_Customer_Number_limiter
&P_org_limiter
and &P_Category_limiter
ORDER BY 5 ASC,15 ASC,7 
  ASC,11 ASC,13 ASC,1 ASC,8 ASC,9 ASC,10 ASC,12 ASC,14 ASC,4 ASC,16 ASC,2 ASC,
  3 ASC