-- Date Format -----------------------------------------------------------------
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD hh24:mi:ss'

-- Select rowid using like with wild-cards -------------------------------------
select rowid, dnl_t.* from dnl_t where xml_doc like '%SECONDTRY1%'

-- Select with not exists ------------------------------------------------------
select distinct iv_f.tag
from iv_f
where iv_f.sku = 'TF5125'
and iv_f.pkg = 'TRCD'
and not exists
(
 	select 1
	from recvunit_t
	where recvunit_t.tag = iv_f.tag
)

-- Select with union -----------------------------------------------------------
SELECT '1' err, cm_f.cm_rid 
from cm_f 
WHERE ob_oid = 'BAT1055' 
and ob_type = 'LABL' 
and ob_lno = 1 
UNION 
SELECT '2' err, cp_f.cp_rid 
from cp_f 
WHERE ob_oid = 'BAT1055' 
and ob_type = 'LABL' 
and ob_lno = 1 
and cp_f.cmp_stt not like 'ERR%'
and cp_f.cp_rid != 478796 
UNION 
SELECT '3' err, ps_f.ps_rid 
from ps_f 
WHERE ob_oid  = 'BAT1055' 
and ob_type = 'LABL' 
and ob_lno = 1 
and ps_f.pick_short_stt not in ('ERR','CMP') 
ORDER BY 1 ASC

-- Select with outer join using the 'in' key word ------------------------------
select recvunit_t.ib_oid, iv_f.sku, iv_f.pkg, count(*)
from recvunit_t, iv_f
where ib_oid in ('10485', '10482')
and ib_type = 'REG'
and iv_f.tag (+) = recvunit_t.tag
group by recvunit_t.ib_oid, iv_f.sku, iv_f.pkg

-- Aggregate functions ---------------------------------------------------------
SELECT  SUM( qty ) a, SUM( alloc_qty ) b
FROM iv_f
WHERE iv_f.loc = 'WMSFP1'
AND iv_f.sku = 'PART1'
AND iv_f.pkg = ' '
AND iv_f.kit_code = ' '

-- Trigger Example -------------------------------------------------------------
CREATE OR REPLACE TRIGGER "VIAWARE".upd_plan_trailer_for_shipment
 before update of shipment on om_f
 for each row
begin
	if :new.shipment <> :old.shipment and :new.frlogic_plan_flg = 'Y'
	then
		UPDATE od_f 
		SET plan_trailer = SUBTRING( :new.shipment, 1, 10 ),
			modcnt = od_f,modcnt + 1, 
			pgmmod = 'upd_plan_trailer_for',
			usrmod = 'trigger',
			dtimemod = sysdate			
		WHERE od_f.OB_OID = :new.ob_oid
		AND od_f.OB_TYPE = :new.ob_type
	end if;
end;
/