, , ,

Get the Modifier details of particular item (only at the Line Level)

When we are define Modifier in the OM module. We can define Modifier at the particular Item or all the Items in the Price List/Item Category etc.

Note:- Item Category is the Flex-field in the Inventory Module. In the Master Item define, we choose what item belongs to which Item Category.

The following query will give the Modifier Name and other details of Particular Item:-
————————————————————————————-

Note:- This is applicable only if the Item Number directly given at the Modifier Line Level.

SELECT distinct qlh.comments “Modifier Name”
,qqv.rule_name “Qualifier Group”
,ou.name “Store Id”
–,qlhv.name “Price list name”
,qms.product_attr_value “SKU”
,qms.list_line_no “Modifier Line No”
,qms.start_date_active “Start Date”
,qms.end_date_active “End Date”
,qms.arithmetic_operator_type “Application Method”
,qms.operand “Value”
,qms.product_precedence “Precedence”
,qms.incompatibility_grp “Incompatibility Group”
,qms.pricing_group_sequence “Bucket”
FROM 
qp_modifier_summary_v qms
, qp_list_headers_b qlh
,qp_list_headers_tl qlt
,qp_qualifiers_v qqv 
,mtl_system_items_b msi
,hr_all_organization_units ou
–,qp_list_headers_v qlhv 
WHERE 
qlh.list_header_id = qms.list_header_id 
–and qms.list_header_id=qlhv.list_header_id 
and qlh.list_header_id =qqv.list_header_id 
and to_char(msi.inventory_item_id)=qms.product_attr_val
AND ou.organization_id = msi.organization_id 
and to_char(ou.organization_id)= qqv.qualifier_attr_value 
and sysdate between qms.start_date_active and qms.end_date_active 
and qlt.LIST_HEADER_ID=qlh.LIST_HEADER_ID
AND exists
(select 1
from mtl_system_items_b a
where a.organization_id=(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
and to_char(a.inventory_item_id)=qms.product_attr_val 
and a.segment1 in(‘Your Item Name’))

/by
, , ,

To identify the items at Item Category level against active modifiers

When we are define Modifier in the OM module. We can define Modifier at the particular Item or all the Items in the Price List/Item Category etc.

Note:- Item Category is the Flex-field in the Inventory Module. In the Master Item define, we choose what item belongs to which Item Category.

The following query will give the Modifier Name and other details of Particular Item of Item Category:-
—————————————————————————————-

SELECT DISTINCT — qpa.list_header_id “Modifier Header ID”, 
qpa.list_line_id “Modifier Line Number”,
qlh.COMMENTS “Modifier Name (Description)”,
qll.start_date_active “Modifier Start Date”,
qll.end_date_active “Modifier End Date”,
b.segment1 “SKU”,
b.inventory_item_id “Inventory Item ID”,
qll.arithmetic_operator_type “Application Method”,
qll.operand “Value”, 
qll.product_precedence “Precedence”, 
qll.incompatibility_grp “Incompatibility Group”,
qll.pricing_group_sequence “Bucket”
FROM qp_pricing_attributes qpa,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_modifier_summary_v qll,
mtl_item_catalog_groups a,
mtl_system_items_b b,
mtl_descr_element_values c
–qp_qualifiers_v q
WHERE b.item_catalog_group_id = a.item_catalog_group_id
AND b.inventory_item_id = c.inventory_item_id
AND b.organization_id =
(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
AND c.element_sequence IN (’20’)
AND qq.list_header_id = qpa.list_header_id
AND qq.list_line_id = qpa.list_line_id
AND qq.qualifier_attribute = ‘QUALIFIER_ATTRIBUTE35’
AND qq.qualifier_attr_value =
a.segment1 || ‘-‘ || c.element_value 
AND qpa.product_attribute = ‘PRICING_ATTRIBUTE3’
AND qlh.list_header_id = qq.list_header_id
AND qpa.list_header_id = qll.list_header_id
AND qpa.list_line_id = qll.list_line_id
AND qll.list_header_id = qlh.list_header_id
AND qlh.active_flag = ‘Y’
AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND b.segment1 = ‘Your Item Name’

/by
, , ,

Get all the Assembly completion Lot Numbers

The following query gets the information about the Lot Numbers of Assembly Completion.

Note:- I have commented “mmt.organization_id = your_organization_id” condition. If you want to get the results specific to some inventory organization then, you can use this condition. Organization_id is representing the Inventory Organization.

SELECT mmt.transaction_id
, mmt.transaction_date
, mmt.inventory_item_id
, mmt.organization_id
, mtn.lot_number
, msi.shelf_life_days
, msi.shelf_life_code
, msi.segment1
FROM mtl_material_txns_val_v mmt
,mtl_transaction_lot_numbers mtn
,mtl_lot_numbers mln
, mtl_system_items_b msi
WHERE mmt.transaction_id = mtn.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mtn.lot_number = mln.lot_number
AND mtn.organization_id = mln.organization_id
AND mmt.transaction_type_name = ‘WIP Completion’
AND mmt.transaction_action = ‘Assembly completion’
AND NVL (mln.attribute1, ‘N’) <> ‘Y’
— AND mmt.organization_id = your_organization_id
AND mmt.transaction_date >= mmt.transaction_date
AND mmt.transaction_date <= mmt.transaction_date
AND mmt.inventory_item_id >= mmt.inventory_item_id
AND mmt.inventory_item_id <= mmt.inventory_item_id;

/by
, , ,

To identify the items at Pricelist level against active modifiers

When we are define Modifier in the OM module. We can define Modifier at the particular Item or all the Items in the Price List/Item Category etc.

The following query will give the Modifier Name and other details of Particular Item of Price List:-
————————————————————————————————-

SELECT DISTINCT qpa.list_header_id “Modifier Header ID”,
qlh.COMMENTS “Modifier Name (Description)”, 
qpa.list_line_id “Modifier Line ID”, 
qll.start_date_active “Modifier start date”,
qll.end_date_active “Modifier end date”,
qq.qualifier_attr_value “Price List ID”,
qllv.product_attr_value “Inventory Item ID”,
msi.segment1 “SKU”,
qll.arithmetic_operator_type “Application Method”,
qll.operand “Value”, 
qll.product_precedence “Precedence”, 
qll.incompatibility_grp “Incompatibility Group”,
qll.pricing_group_sequence “Bucket”
FROM qp_pricing_attributes qpa,
mtl_system_items_b msi,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_list_lines_v qllv,
qp_modifier_summary_v qll
WHERE qpa.product_attribute = ‘PRICING_ATTRIBUTE3’
AND qq.qualifier_attribute = ‘QUALIFIER_ATTRIBUTE4’
AND qllv.product_attribute = ‘PRICING_ATTRIBUTE1’
AND qllv.product_attr_value = msi.inventory_item_id
AND qllv.list_header_id = qq.qualifier_attr_value
AND qlh.list_header_id = qq.list_header_id
AND qpa.list_header_id = qll.list_header_id
AND qpa.list_line_id = qll.list_line_id
AND qll.list_header_id = qlh.list_header_id
AND qlh.active_flag = ‘Y’
AND msi.segment1 = ‘Your Item Name’
AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND TRUNC (NVL (qllv.end_date_active, SYSDATE)) <= TRUNC (SYSDATE)
— AND rownum <=10

/by
, , ,

On-hand inventory information

The following select statement would extracts all on-hand inventory information from Oracle
Applications base tables.

Note:- You may need to modify the query to match with your instance conditions and your requirements.

SELECT
NVL(substr(org.organization_code, 1,3), ‘ ‘) orgcode
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ‘ ‘) trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
— AND hou.type = ‘DC’
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

I hope the above information would be helpful to you.

/by