[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Auto_Allocate(p_allow_delete IN NUMBER,
p_mo_line_id IN NUMBER,
p_transaction_header_id IN NUMBER,
p_move_order_type IN NUMBER,
x_number_of_rows OUT NOCOPY NUMBER,
x_qc_grade OUT NOCOPY VARCHAR2,
x_detailed_qty OUT NOCOPY NUMBER,
x_qty_UM OUT NOCOPY VARCHAR2,
x_detailed_qty2 OUT NOCOPY NUMBER,
x_qty_UM2 OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(10);
l_p_allow_delete VARCHAR2(3);
IF (p_allow_delete = 1) THEN
l_p_allow_delete := 'YES';
l_p_allow_delete := 'NO';
, p_allow_delete => l_p_allow_delete
, x_number_of_rows => x_number_of_rows
, x_qc_grade => x_qc_grade
, x_detailed_qty => x_detailed_qty
, x_qty_UM => x_qty_UM
, x_detailed_qty2 => x_detailed_qty2
, x_qty_UM2 => x_qty_UM
, x_return_status => x_return_status
, x_msg_count => l_count
, x_msg_data => l_msg
);
SELECT
oeh.order_number,
oeh.header_id,
moh.request_number mo_number
FROM
ic_txn_request_headers moh,
ic_txn_request_lines mol,
oe_order_lines_all oel,
oe_order_headers_all oeh
WHERE oeh.header_id = oel.header_id
AND oeh.order_number LIKE (p_so_no)
AND oel.line_id = mol.txn_source_line_id
AND moh.header_id = mol.header_id
AND mol.organization_id = p_org_id
AND mol.line_status in (3,7)
GROUP BY oeh.order_number, oeh.header_id, moh.request_number
ORDER BY oeh.order_number;
SELECT DISTINCT opi.item_no, opi.item_desc1, opi.item_id, mti.inventory_item_id
FROM mtl_system_items mti,
ic_item_mst opi,
ic_txn_request_lines l
WHERE opi.item_no = mti.segment1
and opi.item_no LIKE (p_item_no)
and mti.organization_id = p_org_id
and mti.inventory_item_flag = 'Y'
and mti.inventory_item_id = l.inventory_item_id
UNION
SELECT DISTINCT opi.item_no, opi.item_desc1, opi.item_id, mti.inventory_item_id
FROM mtl_system_items mti,
ic_item_mst opi,
mtl_cross_references mcr
WHERE
mti.organization_id = p_org_id
AND opi.item_no = mti.segment1
AND mti.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = mti.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT
loct_ctl
FROM
ic_item_mst
WHERE
item_id = p_item_id;
select distinct inv.location, NVL(loc.loct_desc, inv.location)
from ic_loct_inv inv, ic_loct_mst loc
where inv.whse_code = p_whse_code and inv.delete_mark = 0 and
inv.location like (p_location)
and inv.lot_id = nvl(l_lot_id,inv.lot_id)
and inv.loct_onhand > 0
and inv.item_id = p_item_id
and loc.location = inv.location;
select distinct inv.location, NVL(loc.loct_desc, inv.location)
from ic_loct_inv inv, ic_loct_mst loc
where inv.whse_code = p_whse_code and inv.delete_mark = 0 and
inv.location like (p_location)
and inv.lot_id = nvl(l_lot_id,inv.lot_id)
and inv.loct_onhand > 0
and inv.item_id = p_item_id
and loc.location(+) = inv.location;
select distinct inv.location, NVL(loc.loct_desc, inv.location)
from ic_loct_inv inv, ic_loct_mst loc
where inv.whse_code = p_whse_code and inv.delete_mark = 0 and
inv.location like (p_location)
and inv.lot_id = nvl(l_lot_id,inv.lot_id)
and inv.item_id = p_item_id
and loc.location = inv.location;
select distinct inv.location, NVL(loc.loct_desc, inv.location)
from ic_loct_inv inv, ic_loct_mst loc
where inv.whse_code = p_whse_code and inv.delete_mark = 0 and
inv.location like (p_location)
and inv.lot_id = nvl(l_lot_id,inv.lot_id)
and inv.item_id = p_item_id
and loc.location(+) = inv.location;
select distinct lot_no,sublot_no, a.lot_id,a.qc_grade, b.lot_status
from ic_lots_mst a, ic_loct_inv b
where a.lot_no like (p_lot_no) and a.lot_id <> 0 and a.lot_id =b.lot_id and a.item_id = b.item_id and
a.delete_mark = 0 and b.delete_mark = 0 and a.item_id = p_item_id and
b.whse_code = p_whse_code and b.location = nvl(l_location,b.location)
and b.loct_onhand > 0 and a.expire_date >= sysdate
order by lot_no;
select distinct lot_no,sublot_no, a.lot_id,a.qc_grade, b.lot_status
from ic_lots_mst a, ic_loct_inv b
where a.lot_no like (p_lot_no) and a.lot_id <> 0 and a.lot_id =b.lot_id and a.item_id = b.item_id and
a.delete_mark = 0 and b.delete_mark = 0 and a.item_id = p_item_id and
b.whse_code = p_whse_code and b.location = nvl(l_location,b.location)
and a.qc_grade = l_pref_grade
and b.loct_onhand > 0 and a.expire_date >= sysdate
order by lot_no;
select distinct lot_no,sublot_no, a.lot_id,a.qc_grade,b.lot_status
from ic_lots_mst a, ic_loct_inv b
where a.lot_no like (p_lot_no) and a.lot_id <> 0 and a.lot_id =b.lot_id
and a.item_id = b.item_id and
a.delete_mark = 0 and b.delete_mark = 0 and a.item_id = p_item_id and
b.whse_code = p_whse_code and b.location = nvl(l_location,b.location)
and a.expire_date >= sysdate
order by lot_no;
select distinct lot_no,sublot_no, a.lot_id,a.qc_grade,b.lot_status
from ic_lots_mst a, ic_loct_inv b
where a.lot_no like (p_lot_no) and a.lot_id <> 0 and a.lot_id =b.lot_id
and a.item_id = b.item_id and
a.delete_mark = 0 and b.delete_mark = 0 and a.item_id = p_item_id and
b.whse_code = p_whse_code and b.location = nvl(l_location,b.location)
and a.qc_grade = l_pref_grade
and a.expire_date >= sysdate
order by lot_no;
select distinct sublot_no, a.qc_grade, b.lot_status, a.lot_id
from ic_lots_mst a, ic_loct_inv b
where a.lot_id <> 0 and a.lot_id = b.lot_id and a.item_id = b.item_id and
a.delete_mark = 0 and
b.delete_mark = 0 and b.whse_code = p_whse_code and a.lot_no = p_lot_no and b.loct_onhand > 0
and b.location = nvl(l_location, b.location) and a.expire_date >= sysdate and
a.sublot_no LIKE (p_sublot_no) and a.item_id = p_item_id order by sublot_no;
select distinct sublot_no, a.qc_grade, b.lot_status, a.lot_id
from ic_lots_mst a, ic_loct_inv b
where a.lot_id <> 0 and a.lot_id = b.lot_id and a.item_id = b.item_id and
a.delete_mark = 0 and
b.delete_mark = 0 and b.whse_code = p_whse_code and a.lot_no = p_lot_no
and b.location = nvl(l_location, b.location) and a.expire_date >= sysdate and
a.sublot_no LIKE (p_sublot_no) and a.item_id = p_item_id order by sublot_no;
SELECT MAX(h.request_number)
, MAX(h.description)
, h.header_id
, MAX(h.move_order_type)
, DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
, DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
FROM ic_txn_request_headers h, ic_txn_request_lines l
WHERE h.organization_id = p_organization_id
AND h.request_number LIKE(p_mo_req_number)
AND h.header_status IN(3, 7)
AND l.organization_id = h.organization_id
AND l.line_status IN(3, 7)
AND l.header_id = h.header_id
GROUP BY h.header_id;
SELECT wnd.NAME, wnd.delivery_id
FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments wda,
ic_txn_request_lines ml
WHERE wda.delivery_id = wnd.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = ml.line_id
AND wdd.organization_id = p_organization_id
AND ml.quantity > NVL(ml.quantity_delivered, 0)
AND wnd.NAME LIKE(p_deliv_num || '%');
SELECT UNIQUE t.pick_slip_number
FROM ic_tran_pnd t,
ic_whse_mst i
WHERE
t.whse_code = i.whse_code AND
i.mtl_organization_id = p_organization_id AND
t.pick_slip_number LIKE(p_pickslip_num)
AND t.delete_mark = 0
AND t.completed_ind = 0
AND t.doc_type = 'OMSO';
select
reason_code,reason_desc1 from sy_reas_cds
where
reason_code like (p_reason_code) and
delete_mark = 0
order by 1;
fnd_msg_pub.delete_msg;