, , ,

Write message in Log or Out file

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. 

/by
, , ,

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
, , ,

Retrieves all PO transaction quantities

Following Query would get all the PO Numbers, Item ID, Quantity Order, Received, Canceled etc. The same information can be found in the PO order form from the Front end Application.

SELECT
POL.ITEM_ID “Inventory Item ID”,
POLL.SHIP_TO_LOCATION_ID “Ship to Location ID”,
poll.quantity “Quantity”,
poll.Quantity_received “Quantity Received”,
poll.QUANTITY_cancelled “Quantity Canceled”,
POH.SEGMENT1 “PO Order Number”
FROM po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id=pol.po_header_id
AND pol.po_line_id=poll.po_line_id
AND poll.po_header_id=pol.po_header_id
AND poh.authorization_status=’APPROVED’
AND poh.type_lookup_code=’STANDARD’
AND NVL(poh.cancel_flag,’N’) =’N’
AND POL.ITEM_ID IS NOT NULL
AND NVL (poll.quantity, 0) > (NVL (Quantity_received,0) + NVL (Quantity_cancelled,0))

/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