Hi All,
Have a good day.
Please a help required in using CURRENT_DATE. I'm using a stored function inside a package which will take two parameters in DATE format. The parameters are start date and end date. The function will return a CURSOR as a result which has all the data fetched through select statement. When I use only the below query to test on the console then the result is getting very quickly but when I try to get through via cursor the process time is very high !. Any ideas will be much appreciated.
OPEN retval FOR
SELECT
SUBSTR(ogr.order_number,2,7) || ogr.order_line || ogr.batch_number_or_approved_date record_id
FROM
works_order_issues_and_returns woir
LEFT JOIN works_orders wo
ON woir.works_order = wo.works_order
LEFT JOIN part_master pm
ON woir.part_number = pm.part_number
LEFT JOIN batch_record_1 br1
ON woir.part_number = br1.part_number
AND (pm.part_type = '1' AND woir.batch_or_serial_number = br1.serial_number
AND woir.rotable_batch_number = br1.batch_number
OR (pm.part_type = '0' AND woir.batch_or_serial_number = br1.batch_number )
)
LEFT JOIN order_goods_received ogr
ON ogr.part_number = br1.part_number
AND (pm.part_type = '1'
AND ogr.quantity_or_serial_number = br1.serial_number
AND ogr.batch_number_or_approved_date = br1.batch_number
OR
(pm.part_type = '0'
AND ogr.batch_number_or_approved_date = br1.batch_number
AND ogr.goods_received_number = br1.goods_received_number
)
)
LEFT JOIN stock_groups sg
ON pm.stock_group = sg.stock_group
WHERE woir.transaction_date
between trunc (start_date)
AND sg.tooling_stock_group != 'Y'
and woir.quantity !=0 ;