When ever, I have to create new package, I normally code a procedure to write any information in the log or Out files of the program.

The following is the Procedure which I use it in any package. This is standard in some companies coding. This save a lot of time and this makes easy to other people who goes through your program.

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : Procedure writes to the log file or output file
based on type.O=Output File, L=Log File
*************************************************************************/

BEGIN
IF p_type = ‘L’
THEN

fnd_file.put_line (fnd_file.log, p_message);

ELSIF p_type = ‘O’
THEN

fnd_file.put_line (fnd_file.output, p_message);

END IF;
END write;

The above write procedure can be used in other Procedure/Function in the package to write any information in the Log or Out files. 

PROCEDURE main(errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_par1 IN NUMBER
)
IS
v_errbuf VARCHAR2(1000) := NULL;
v_retcode NUMBER := 0;
v_file_name VARCHAR2(100);
BEGIN

v_retcode := 0;

v_file_name := fnd_profile.value(‘XYZ’);

IF v_file_name IS NULL
THEN
write(‘O’,’Profile XYZ is not defined or the value is not set’);
retcode := 2;
RETURN;
END IF;
END;

Note:- In the above Procedure, I am using the write Procedure and returning 2 for the retcode (0 – Complete, 1- Warning and 2 will be for Error).

Note:- This is one time process and you will realise, how much helpful it will be when ever you have to right something in log or out file.

More then information, this post is more of suggestion. Hope this post will help you make your code easy. 

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’))

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’