[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inv_project.get_locsegs(locator_id, org_id)
INTO locator_name
FROM mtl_item_locations
WHERE inventory_location_id = locator_id
AND organization_id = org_id;
SELECT organization_code
INTO org_code
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT vendor_site_code
INTO supplier_site_name
FROM po_vendor_sites_all
WHERE vendor_site_id = supplier_site_id;
SELECT h.segment1
INTO v_document_header
FROM po_distributions_all d, po_headers_all h
WHERE d.po_distribution_id = v_document_detail_id
AND h.po_header_id = d.po_header_id;
SELECT h.segment1
INTO v_document_header
FROM po_distributions_all d, po_headers_all h
WHERE d.po_distribution_id = v_document_detail_id
AND h.po_header_id = d.po_header_id;
SELECT h.segment1
INTO v_document_header
FROM po_requisition_headers_all h, po_requisition_lines_all l
WHERE l.requisition_line_id = v_document_detail_id
AND h.requisition_header_id = l.requisition_header_id;
SELECT h.request_number
INTO v_document_header
FROM mtl_txn_request_headers h, mtl_txn_request_lines l
WHERE l.line_id = v_document_detail_id
AND h.header_id = l.header_id;
SELECT h.wip_entity_name
INTO v_document_header
FROM wip_entities h
WHERE h.wip_entity_id = v_document_header_id;
SELECT h.wip_entity_name
INTO v_document_header
FROM wip_entities h
WHERE h.wip_entity_id = v_document_header_id;
SELECT h.wip_entity_name
INTO v_document_header
FROM wip_entities h
WHERE h.wip_entity_id = v_document_header_id;
SELECT h.wip_entity_name
INTO v_document_header
FROM wip_entities h
WHERE h.wip_entity_id = v_document_header_id;
SELECT *
INTO recinfo
FROM mtl_kanban_cards
WHERE kanban_card_number = p_kanban_card_number
AND organization_id = p_org_id;
inv_kanbancard_pkg.update_row(
x_return_status => l_return_status
, p_kanban_card_id => recinfo.kanban_card_id
, p_kanban_card_number => recinfo.kanban_card_number
, p_pull_sequence_id => recinfo.pull_sequence_id
, p_inventory_item_id => recinfo.inventory_item_id
, p_organization_id => recinfo.organization_id
, p_subinventory_name => recinfo.subinventory_name
, p_supply_status => recinfo.supply_status
, p_card_status => recinfo.card_status
, p_kanban_card_type => recinfo.kanban_card_type
, p_source_type => recinfo.source_type
, p_kanban_size => recinfo.kanban_size
, p_last_update_date => SYSDATE
, p_last_updated_by => fnd_global.user_id
, p_creation_date => recinfo.creation_date
, p_created_by => recinfo.created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => recinfo.last_print_date
, p_locator_id => recinfo.locator_id
, p_supplier_id => recinfo.supplier_id
, p_supplier_site_id => recinfo.supplier_site_id
, p_source_organization_id => recinfo.source_organization_id
, p_source_subinventory => recinfo.source_subinventory
, p_source_locator_id => recinfo.source_locator_id
, p_wip_line_id => recinfo.wip_line_id
, p_current_replnsh_cycle_id => recinfo.current_replnsh_cycle_id
, p_error_code => recinfo.error_code
, p_attribute_category => recinfo.attribute_category
, p_attribute1 => recinfo.attribute1
, p_attribute2 => recinfo.attribute2
, p_attribute3 => recinfo.attribute3
, p_attribute4 => recinfo.attribute4
, p_attribute5 => recinfo.attribute5
, p_attribute6 => recinfo.attribute6
, p_attribute7 => recinfo.attribute7
, p_attribute8 => recinfo.attribute8
, p_attribute9 => recinfo.attribute9
, p_attribute10 => recinfo.attribute10
, p_attribute11 => recinfo.attribute11
, p_attribute12 => recinfo.attribute12
, p_attribute13 => recinfo.attribute13
, p_attribute14 => recinfo.attribute14
, p_attribute15 => recinfo.attribute15
, p_document_type => NULL
, p_document_header_id => NULL
, p_document_detail_id => NULL
, p_lot_item_id => p_lot_item_id
, p_lot_number => p_lot_number
, p_lot_item_revision => p_lot_item_revision
, p_lot_subinventory_code => p_lot_subinventory_code
, p_lot_location_id => p_lot_location_id
, p_lot_quantity => p_lot_quantity
, p_replenish_quantity => p_replenish_quantity);
SELECT msiv.concatenated_segments
, msiv.description
, mkcv.kanban_size
, mkcv.subinventory_name
, mkcv.locator_id
, mkcv.supply_status_name
, mkcv.source_type
, mkcv.source_type_meaning
, mkcv.source_organization_id
, mkcv.source_org_code
, mkcv.source_subinventory
, mkcv.source_locator_id
, mkcv.wip_line_code
, mkcv.supplier_name
, mkcv.supplier_site_id
, msiv.inventory_item_id
INTO x_item
, x_item_description
, x_quantity
, x_zone
, locator_id
, x_supply_status
, x_source_type_id
, x_source_type
, source_org_id
, x_source_org
, x_source_zone
, source_locator_id
, x_wip_line
, x_supplier_name
, supplier_site_id
, x_item_id
FROM mtl_kanban_cards_v mkcv, mtl_system_items_vl msiv
WHERE mkcv.kanban_card_number = p_kanban_card_number
AND mkcv.organization_id = p_org_id
AND mkcv.inventory_item_id = msiv.inventory_item_id
AND mkcv.organization_id = msiv.organization_id;
SELECT DISTINCT meaning
, lookup_code
FROM mfg_lookups
WHERE lookup_code IN (1, 2, 3, 4)
AND meaning LIKE (p_source_type)
AND lookup_type = 'MTL_KANBAN_SOURCE_TYPE';
SELECT vendor_name
, vendor_id
FROM po_vendors
WHERE vendor_name LIKE (p_supplier_name);
SELECT vendor_site_code
, vendor_site_code_alt
FROM po_vendor_sites_all
WHERE vendor_site_code LIKE (p_supplier_site)
AND vendor_id = p_vendor_id;
SELECT line_code
, line_id
FROM wip_lines
WHERE p_organization_id = organization_id
AND line_code LIKE (p_wip_line);
SELECT mkc.kanban_card_number
, mfg1.meaning kanban_card_type
, msiv.concatenated_segments item
, msiv.description
, mkc.kanban_size
, mkc.subinventory_name
, inv_kanban_pkg.getlocatorname(p_org_id, mkc.locator_id) loc_name
, inv_project.get_project_number project_num
, inv_project.get_task_number task_num
, mfg2.meaning card_status_name
, mfg3.meaning supply_status_name
, mfg4.meaning source_type
, inv_kanban_pkg.getorgcode(mkc.source_organization_id)
, mkc.source_subinventory
, inv_kanban_pkg.getlocatorname(mkc.source_organization_id, mkc.source_locator_id) source_loc_name
, inv_project.get_project_number src_project_num
, inv_project.get_task_number src_task_num
, mka.last_update_date
, mfg5.meaning act_supp_status_name
, mfg6.meaning doc_type
, inv_kanban_pkg.getdocmentnumber(mka.document_header_id, mka.document_type, mka.document_detail_id) doc_num
, inv_kanban_pkg.getorgcode(mka.source_organization_id)
, mka.source_subinventory act_sub
, inv_kanban_pkg.getlocatorname(mka.source_organization_id, mka.source_locator_id) act_loc_name
, inv_project.get_project_number act_project_num
, inv_project.get_task_number act_task_num
, pv.vendor_name vendor
, pvsa.vendor_site_code vendor_site
, wl.line_code
, pv_act.vendor_name vendor_last_activity
, pvsa_act.vendor_site_code vendor_site_last_activity
, wl_act.line_code
FROM mtl_kanban_cards mkc
, mtl_system_items_vl msiv
, mfg_lookups mfg1
, mfg_lookups mfg2
, mfg_lookups mfg3
, mfg_lookups mfg4
, mfg_lookups mfg5
, mfg_lookups mfg6
, po_vendors pv
, mtl_kanban_card_activity mka
, po_vendor_sites_all pvsa
, mtl_kanban_pull_sequences mkps
, wip_lines wl
, wip_lines wl_act
, po_vendors pv_act
, mtl_item_locations mil
, po_vendor_sites_all pvsa_act
WHERE mkc.kanban_card_number LIKE (p_kanban_card_number || '%') -- 3231139
AND mkc.organization_id = p_org_id
AND mkc.inventory_item_id = NVL(p_item_id, mkc.inventory_item_id)
AND mkc.source_type = NVL(p_source_type_id, mkc.source_type)
AND NVL(mkc.source_organization_id, 0) = NVL(p_source_organization_id, NVL(mkc.source_organization_id, 0))
AND NVL(mkc.source_subinventory, '@@@') LIKE NVL(p_source_sub, NVL(mkc.source_subinventory, '@@@'))
AND NVL(mkc.source_locator_id, 0) = NVL(p_source_loc, NVL(mkc.source_locator_id, 0))
--Bug 3622464 Start
AND mil.inventory_location_id(+) = mkc.source_locator_id
--AND (mil.inventory_location_id(+) = NVL(p_source_loc, -1)
--Bug 3882518 fix. commenting the below line.don't need this
--AND (nvl(mil.inventory_location_id,-1) = nvl(p_source_loc,-1)
--Bug 3622464 End
AND NVL(mil.project_id, -1) = NVL(p_project_id, NVL(mil.project_id, -1))
AND NVL(mil.task_id, -1) = NVL(p_task_id, NVL(mil.task_id, -1))
-- )
AND mkc.pull_sequence_id = mkps.pull_sequence_id(+)
AND NVL(mkps.wip_line_id, 0) = NVL(p_wip_line_id, NVL(mkps.wip_line_id, 0))
AND wl.line_id(+) = mkps.wip_line_id
AND wl_act.line_id(+) = mka.wip_line_id
AND pv.vendor_name(+) LIKE (p_supplier || '%')
AND pvsa.vendor_site_code(+) LIKE (p_supplier_site || '%')
AND mkc.inventory_item_id = msiv.inventory_item_id
AND mkc.organization_id = msiv.organization_id
AND mkc.supplier_id = pv.vendor_id(+)
AND mkc.supplier_site_id = pvsa.vendor_site_id(+)
AND mka.supplier_id = pv_act.vendor_id(+)
AND mka.supplier_site_id = pvsa_act.vendor_site_id(+)
AND mkc.inventory_item_id = mka.inventory_item_id(+)
AND mkc.organization_id = mka.organization_id(+)
AND mkc.kanban_card_id = mka.kanban_card_id(+)
AND NVL(TO_CHAR(mka.last_update_date, 'DD-MON-YYYY HH24:MI:SS'), '01/01/1111 00:00:00') =
(SELECT NVL(TO_CHAR(MAX(last_update_date), 'DD-MON-YYYY HH24:MI:SS'), '01/01/1111 00:00:00')
FROM mtl_kanban_card_activity mkca
WHERE NVL(mkca.kanban_card_id, mkc.kanban_card_id) = mkc.kanban_card_id)
AND mfg1.lookup_type = 'MTL_KANBAN_CARD_TYPE'
AND mfg1.lookup_code = mkc.kanban_card_type
AND mfg2.lookup_type = 'MTL_KANBAN_CARD_STATUS'
AND mfg2.lookup_code = mkc.card_status
AND mfg3.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
AND mfg3.lookup_code = mkc.supply_status AND mfg4.lookup_type = 'MTL_KANBAN_SOURCE_TYPE'
AND mfg4.lookup_code = mkc.source_type
AND mfg5.lookup_type(+) = 'MTL_KANBAN_SUPPLY_STATUS'
AND mfg5.lookup_code(+) = NVL(mka.supply_status, 0)
AND mfg6.lookup_type(+) = 'MTL_KANBAN_DOCUMENT_TYPE'
AND mfg6.lookup_code(+) = NVL(mka.document_type, 0);
SELECT vendor_site_code
, vendor_site_id
FROM po_vendor_sites povs
WHERE povs.vendor_id = p_vendor_id
AND povs.vendor_site_code LIKE (p_vendor_site_code);
/* Bug#2810335. Added ood.organization_code to the select clause*/
/* Bug 2834753, continue bug 2810335, show party name, opertion unit second */
PROCEDURE get_vendor_lov(x_ref OUT NOCOPY t_genref, p_vendor VARCHAR2,p_vendor_site_id VARCHAR2) IS
BEGIN
OPEN x_ref FOR
SELECT
pv.vendor_name || '-' || pvs.vendor_site_code owning_planning_party
, ood.organization_code
, pv.vendor_id
, pvs.vendor_site_id
, 1 tp_type
, 'Supplier' party_type
FROM po_vendors pv
, po_vendor_sites_all pvs
, org_organization_definitions ood
WHERE pv.vendor_id = pvs.vendor_id
AND pvs.org_id = ood.organization_id (+)
AND pv.vendor_name || '-' || pvs.vendor_site_code LIKE p_vendor
AND (p_vendor_site_id IS NULL OR pvs.vendor_site_id = p_vendor_site_id)
-- bug# 2880891
order by owning_planning_party;
select lot_number, item, quantity, revision, wslv.subinventory_code,
milk.concatenated_segments, wslv.inventory_item_id, locator_id
from
wsm_source_lots_v wslv,
bom_inventory_components bic,
mtl_item_locations_kfv milk
where
wslv.organization_id = p_organization_id
and wslv.inventory_item_id = bic.component_item_id
and bic.bill_sequence_id = p_bom_sequence_id
and(bic.operation_seq_num = p_start_sequence_num or bic.operation_seq_num = 1 )
and bic.effectivity_date <= sysdate
and nvl(bic.disable_date, sysdate + 1) > Sysdate
AND wslv.locator_id = milk.inventory_location_id(+)
AND wslv.subinventory_code = milk.subinventory_code(+)
AND wslv.organization_id = milk.organization_id(+);
SELECT mkc.kanban_card_type
, mkc.card_status
, mkc.supply_status
, status_check(mkc.supply_status, 4) status_check
, ml.meaning supply_status_meaning
FROM mtl_kanban_cards mkc
, mfg_lookups ml
WHERE mkc.organization_id = p_organization_id
AND mkc.kanban_card_number = p_kanban_number
AND ml.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
AND mkc.supply_status = ml.lookup_code
AND ROWNUM = 1;
SELECT * FROM (SELECT m.line_id
, m.reference_type_code
, m.line_status
, (NVL(quantity_delivered, 0)- m.quantity) qty_diff
FROM mtl_txn_request_lines m
, mtl_kanban_cards k
WHERE m.reference_id = k.kanban_card_id
AND m.organization_id = p_organization_id
AND k.kanban_card_number = p_kanban_number
ORDER BY m.line_id desc)
WHERE ROWNUM = 1;