QA Quality Specifications

Description
Categories: BI Publisher, Manufacturing
Application: Quality
Source: Quality Specifications Report (XML)
Short Name: QLTSPECR_XML
DB package: QA_QLTSPECR_XMLP_PKG
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
Parameter Name SQL text Validation
Organization Id
 
Number
Category Structure
 
LOV Oracle
Supplier
 
LOV Oracle
Customer
 
LOV Oracle
Item
 
Category
 
Category Set
 
LOV Oracle
Specification Name
 
LOV Oracle
Specification Type
 
LOV Oracle