[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT haou.organization_id
FROM mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou,
HR_ALL_ORGANIZATION_UNITS_TL haoutl
WHERE mp.organization_id = hoi.organization_id
AND haou.organization_id = hoi.organization_id
AND haou.organization_id = haoutl.organization_id
AND mp.trading_partner_org_flag = 'Y'
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information3 = MO_GLOBAL.get_current_org_id
AND haoutl.LANGUAGE = USERENV('LANG');
SELECT h.header_id
FROM oe_order_headers_all h
WHERE
h.header_id IN (SELECT DISTINCT r.replenishment_so_header_id FROM jmf_shikyu_replenishments r)
AND h.org_Id = p_org_id
AND h.sold_to_org_id = customer_id
-- AND h.header_id = r.replenishment_so_header_id
-- AND h.sold_to_site_use_id = customer_site
AND (h.flow_status_code = 'BOOKED'
OR h.flow_status_code = 'INVOICED'
OR h.flow_status_code = 'INVOICE_HOLD'
OR h.flow_status_code = 'INVOICE_INCOMPLETE'
OR h.flow_status_code = 'ACTIVE'
OR h.flow_status_code = 'CUSTOMER_ACCEPTED'
OR h.flow_status_code = 'INTERNAL_APPROVED'
OR h.flow_status_code = 'PENDING_CUSTOMER_ACCEPTANCE'
OR h.flow_status_code = 'PENDING_INTERNAL_APPROVAL'
OR h.flow_status_code = 'SUBMITTED'
OR h.flow_status_code = 'WORKING'
OR h.flow_status_code = 'CLOSED');
SELECT r.replenishment_so_line_id
FROM jmf_shikyu_replenishments r
WHERE r.replenishment_so_header_id = p_header_id;
SELECT
r.replenishment_po_header_id
, r.replenishment_po_line_id
, r.replenishment_po_shipment_id
INTO
l_po_header_id
, l_po_line_id
, l_po_shipment_id
FROM jmf_shikyu_replenishments r
WHERE r.replenishment_so_line_id = l_line_id;
,p_message => 'no data find when select PO info by so_line_id in jmf_shikyu_replenishments'
);
SELECT l.actual_shipment_date --actual_shipment_date
, l.ship_from_org_id
, l.ship_to_org_id
, l.sold_to_org_id
, l.shipping_method_code
INTO l_ship_date
, l_ship_from_org_id
, l_ship_to_org_id
, l_sold_to_org_id
, l_ship_method
FROM oe_order_lines_all l
WHERE l.line_id = l_line_id;
,p_message => 'no data find when select ship org id or other info by line_id in oe_order_lines_all'
);
SELECT
shipping_method_code
INTO
l_ship_method
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT h.ship_from_org_id
, h.ship_to_org_id
INTO l_ship_from_org_id_h
, l_ship_to_org_id_h
FROM oe_order_headers_all h
WHERE h.header_id = p_header_id;
SELECT hoi.Organization_Id
INTO l_tp_org_id
FROM HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.org_information1 = l_customer_information;
SELECT ship_net.INTRANSIT_TYPE -- 1 is direct and 2 is instrant
INTO l_intransit_type
FROM MTL_SHIPPING_NETWORK_VIEW ship_net
WHERE ship_net.FROM_ORGANIZATION_ID = p_line_ship_from_org_id
AND ship_net.TO_ORGANIZATION_ID = l_tp_org_id;
SELECT NVL(l.shipped_quantity,0)
, l.inventory_item_id
--, l.shipping_quantity_uom
, l.order_quantity_uom -- Bug#5647346: changed to order_quantity_uom from shipping_quantity_uom since the shipped_quantity is order_quantity_uom
INTO l_line_quantity
, l_item_id
, l_uom_code
FROM oe_order_lines_all l
WHERE l.line_id = p_lines_id(l_index);
SELECT locate.quantity_received
, locate.unit_meas_lookup_code
INTO l_received_quantity
, l_po_uom
FROM po_line_locations_all locate
WHERE locate.line_location_id = p_po_shipment_id;
SELECT DISTINCT uom.uom_code
INTO l_po_uom_code
FROM mtl_units_of_measure_tl uom
WHERE uom.unit_of_measure = l_po_uom
AND uom.LANGUAGE = userenv('LANG');
SELECT NVL(l.shipped_quantity,0)
, l.inventory_item_id
--, l.shipping_quantity_uom
, l.order_quantity_uom -- Bug#5647346: changed to order_quantity_uom from shipping_quantity_uom since the shipped_quantity is order_quantity_uom
FROM oe_order_lines_all l
WHERE l.split_from_line_id = p_line_id
AND l.header_id = p_so_header_id;
SELECT
COUNT(*)
INTO
l_line_count
FROM
oe_order_lines_all
WHERE header_id = p_so_header_id;
SELECT
ship_methods.Intransit_Time
, ship_methods.default_flag
, ship_methods.ship_method
FROM
MTL_INTERORG_SHIP_METHODS ship_methods
WHERE
ship_methods.from_organization_id = p_ship_from_org_id
AND ship_methods.To_Organization_Id = p_ship_to_org_id
AND ship_methods.default_flag = 1;
SELECT
hoi.org_information1 Customer_id
,hoi.org_information2 Customer_site_id
--,hoi.org_information3 Supplier_id
--,hoi.org_information4 Supplier_site_id
INTO
l_customer_id
, l_customer_site_id
FROM
HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.org_information_context = 'Customer/Supplier Association'
AND hoi.organization_id = p_org_inventory_id;
SELECT
hoi.org_information3 Supplier_id
, hoi.org_information4 Supplier_site_id
INTO
l_supplier_id
, l_supplier_site_id
FROM
HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.org_information_context = 'Customer/Supplier Association'
AND hoi.organization_id = p_sold_from_org_id;
SELECT po_vendors.vendor_name
INTO l_supplier_name
FROM po_vendors
WHERE po_vendors.vendor_id = l_supplier_id;
SELECT d.po_distribution_id,
d.distribution_num,
d.quantity_ordered,
d.quantity_delivered,
l.unit_meas_lookup_code,
oola.shipped_quantity,
uom.unit_of_measure,
oola.inventory_item_id
FROM po_distributions_all d,
po_line_locations_all l,
MTL_UNITS_OF_MEASURE uom,
jmf_shikyu_replenishments jsr,
oe_order_lines_all oola
WHERE d.line_location_id = p_location_id
AND d.LINE_LOCATION_ID = jsr.replenishment_PO_shipment_ID
and oola.line_id = jsr.replenishment_so_line_id
and nvl(oola.shipped_quantity, 0) > 0
AND l.line_location_id = d.line_location_id
AND oola.order_quantity_uom = uom.uom_code(+)
and oola.line_id = p_line_id
AND not exists (select 1
from rcv_transactions rt
where REPLENISH_ORDER_LINE_ID = oola.line_id)
AND not exists (select 1
from rcv_transactions_interface rti
where REPLENISH_ORDER_LINE_ID = oola.line_id
and TRANSACTION_STATUS_CODE <> 'ERROR'
AND PROCESSING_STATUS_CODE <> 'ERROR')
ORDER BY d.distribution_num; --If multiple Replenishment PO Distributions exist then handle in their order.
l_insert_quantity NUMBER;
SELECT
uom.unit_of_measure
INTO
l_primary_uom
FROM
mtl_units_of_measure_vl uom
WHERE uom.uom_code = p_primary_uom_code;
,p_message => 'no data found when select receiving_routing_id by line_location_id'
);
SELECT
location.RECEIVING_ROUTING_ID -- 1 is standard and 2 is inspection, 3 is direct
INTO
l_routing_header_id
FROM
PO_LINE_LOCATIONS_ALL location
WHERE location.line_location_id = p_po_shipment_id;
,p_message => 'no data found when select receiving_routing_id by line_location_id'
);
l_insert_quantity := 0;
l_insert_quantity := l_allocated_quantity;
l_insert_quantity := l_allocated_quantity;
SELECT wip_para.default_pull_supply_subinv
INTO l_dest_subinventory
FROM wip_parameters wip_para
WHERE wip_para.Organization_Id = p_inventory_org_id;
SELECT wip_para.default_pull_supply_locator_id
INTO l_default_locator_id
FROM wip_parameters wip_para
WHERE wip_para.Organization_Id = p_inventory_org_id;
SELECT msi.locator_type
INTO l_locator_type
FROM MTL_SECONDARY_INVENTORIES msi
WHERE msi.Organization_Id = p_inventory_org_id
AND msi.SECONDARY_INVENTORY_NAME = l_dest_subinventory;
SELECT oola.project_id, oola.task_id
INTO l_project_id, l_task_id
FROM OE_ORDER_LINES_ALL oola
WHERE oola.line_id = p_lines_id(l_index);
,p_message => 'l_insert_quantity'||l_insert_quantity
);
, p_quantity => l_insert_quantity
, p_unit_of_measure => l_so_uom
, p_po_header_id => p_po_header_id
, p_po_line_id => p_po_line_id
, p_subinventory => NULL
, p_transaction_type => l_transaction_type
, p_auto_transact_code => 'RECEIVE'
, p_parent_transaction_id => NULL
, p_po_line_location_id => p_po_shipment_id
, P_locator_id => l_dest_locator_id
, p_replenish_order_line_id => p_lines_id(l_index)
);
, p_quantity => l_insert_quantity
, p_unit_of_measure => l_so_uom
, p_po_header_id => p_po_header_id
, p_po_line_id => p_po_line_id
, p_subinventory => l_dest_subinventory
, p_transaction_type => l_transaction_type
, p_auto_transact_code => 'DELIVER'
, p_parent_transaction_id => NULL
, p_po_line_location_id => p_po_shipment_id
, P_locator_id => l_dest_locator_id
, p_replenish_order_line_id => p_lines_id(l_index)
);