[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO jmf_shikyu_allocations
( SUBCONTRACT_PO_SHIPMENT_ID
, SHIKYU_COMPONENT_ID
, REPLENISHMENT_SO_LINE_ID
, ALLOCATED_QUANTITY
, UOM
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
)
VALUES
( p_subcontract_po_shipment_id
, p_component_id
, p_replen_so_line_id
, l_qty_to_allocate
, l_primary_uom
, sysdate
, FND_GLOBAL.user_id
, sysdate
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
);
UPDATE jmf_shikyu_allocations
SET allocated_quantity = l_allocation_qty,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND replenishment_so_line_id = p_replen_so_line_id
AND shikyu_component_id = p_component_id;
SELECT segment1
INTO l_order_number
FROM po_headers_all poh
WHERE EXISTS
(SELECT 1 FROM po_line_locations_all poll
WHERE poll.line_location_id = p_subcontract_po_shipment_id
AND poll.po_header_id = poh.po_header_id);
SELECT segment1
INTO l_sub_comp
FROM mtl_system_items_b
WHERE inventory_item_id = p_component_id
AND organization_id = l_tp_organization_id ;
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocated_primary_uom_quantity = l_replen_allocated_primary_qty,
allocated_quantity = l_replen_allocated_qty,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE replenishment_so_line_id = p_replen_so_line_id;
SELECT DISTINCT jsr.replenishment_so_line_id,
jsr.shikyu_component_id,
jsr.allocable_quantity - jsr.allocated_quantity,
jsr.uom,
jsr.allocable_primary_uom_quantity - jsr.allocated_primary_uom_quantity,
jsr.primary_uom,
oola.schedule_ship_date,
--Bugfix 14246759: Adding actual_shipment_date
oola.actual_shipment_date
FROM jmf_shikyu_replenishments jsr,
jmf_subcontract_orders jso,
oe_order_lines_all oola
WHERE jsr.oem_organization_id = jso.oem_organization_id
AND jsr.tp_organization_id = jso.tp_organization_id
AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jsr.shikyu_component_id = p_component_id
AND jsr.replenishment_so_line_id = oola.line_id
--Bugfix 14246759: Adding actual_shipment_date
AND ((TRUNC(nvl(oola.actual_shipment_date, oola.schedule_ship_date)) + l_ship_lead_time <=
NVL(l_threshold_date, TRUNC(nvl(oola.actual_shipment_date, oola.schedule_ship_date)) + l_ship_lead_time))
OR
(p_arrived_so_lines_only = 'Y'
AND
NVL(oola.shipped_quantity, 0) > 0
AND
jsr.allocated_primary_uom_quantity <
INV_CONVERT.inv_um_convert( jsr.shikyu_component_id
, 5
, oola.shipped_quantity
, oola.order_quantity_uom
, JMF_SHIKYU_UTIL.Get_Primary_Uom_Code
( jsr.shikyu_component_id
, jsr.tp_organization_id)
, null
, null)))
AND jsr.allocable_primary_uom_quantity - jsr.allocated_primary_uom_quantity > 0
AND DECODE(p_include_additional_supply,
'Y', NVL(jsr.additional_supply, 'N'),
'N')
= NVL(jsr.additional_supply, 'N')
--Bugfix 14246759: Adding actual_shipment_date
ORDER BY nvl(oola.actual_shipment_date, oola.schedule_ship_date),
jsr.replenishment_so_line_id;
SELECT wdj.scheduled_start_date,
wdj.scheduled_completion_date
FROM WIP_DISCRETE_JOBS wdj,
JMF_SUBCONTRACT_ORDERS jso
WHERE wdj.wip_entity_id = jso.wip_entity_id
AND wdj.organization_id = jso.tp_organization_id
AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
SELECT NVL(mism.intransit_time, 0)
, FROM_organization_id
, to_organization_id
FROM MTL_INTERORG_SHIP_METHODS mism,
JMF_SUBCONTRACT_ORDERS jso
WHERE mism.from_organization_id = jso.oem_organization_id
AND mism.to_organization_id = jso.tp_organization_id
AND mism.default_flag = 1
AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
x_available_replen_tbl.delete;
SELECT organization_code INTO l_oem_organization
FROM mtl_parameters mip
WHERE exists
(SELECT 1 FROM jmf_subcontract_orders jso
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jso.oem_organization_id = mip.organization_id);
SELECT organization_code INTO l_tp_organization
FROM mtl_parameters mip
WHERE exists
(SELECT 1 FROM jmf_subcontract_orders jso
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jso.tp_organization_id = mip.organization_id);
SELECT DISTINCT plla.line_location_id,
pla.item_id,
plla.quantity,
muomv.uom_code,
plla.quantity,
muomv.uom_code,
NVL(plla.need_by_date, plla.promised_date),
pha.segment1,
pla.line_num,
plla.shipment_num
FROM jmf_subcontract_orders jso,
hr_organization_information hoi,
po_line_locations_all plla,
po_lines_all pla,
po_headers_all pha,
mtl_units_of_measure_vl muomv
WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND hoi.organization_id = jso.oem_organization_id
AND hoi.org_information_context = 'Customer/Supplier Association'
AND TO_NUMBER(hoi.org_information3) = pha.vendor_id
AND TO_NUMBER(hoi.org_information4) = pha.vendor_site_id
AND plla.ship_to_organization_id = jso.tp_organization_id
AND plla.po_line_id = pla.po_line_id
AND plla.po_header_id = pha.po_header_id
AND pla.item_id = p_component_id
AND pha.approved_flag = 'Y'
AND nvl(plla.approved_flag, 'N') = 'Y' -- Added for bug 13549961
AND plla.shipment_type in ('STANDARD', 'BLANKET') -- Added for bug 13549961
AND NVL(pha.cancel_flag, 'N') = 'N'
AND NVL(pla.cancel_flag, 'N') = 'N'
AND NVL(plla.cancel_flag, 'N') = 'N'
AND plla.unit_meas_lookup_code = muomv.unit_of_measure
AND NOT EXISTS (SELECT jsr.replenishment_so_line_id
FROM jmf_shikyu_replenishments jsr
WHERE jsr.replenishment_po_shipment_id = plla.line_location_id)
ORDER BY NVL(plla.need_by_date, plla.promised_date),
pha.segment1,
pla.line_num,
plla.shipment_num;
x_available_replen_tbl.DELETE;
x_new_replen_tbl.delete;
SELECT jso.oem_organization_id,
jso.tp_organization_id,
jsc.uom,
jsc.primary_uom
INTO l_oem_organization_id,
l_tp_organization_id,
l_component_uom,
l_primary_uom
FROM jmf_subcontract_orders jso,
jmf_shikyu_components jsc
WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
AND jsc.shikyu_component_id = p_component_id;
SELECT msib.subcontracting_component
INTO l_subcontracting_component
FROM MTL_SYSTEM_ITEMS_B msib,
JMF_SUBCONTRACT_ORDERS jso
WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND msib.inventory_item_id = p_component_id
AND msib.organization_id = jso.tp_organization_id;
SELECT segment1
INTO l_order_number
FROM po_headers_all poh
WHERE EXISTS
(SELECT 1 FROM po_line_locations_all poll
WHERE poll.line_location_id = p_subcontract_po_shipment_id
AND poll.po_header_id = poh.po_header_id);
SELECT segment1
INTO l_sub_comp
FROM mtl_system_items_b msi
WHERE inventory_item_id = p_component_id
AND exists
(SELECT 1
FROM jmf_subcontract_orders jso
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jso.tp_organization_id = msi.organization_id );
SELECT DISTINCT jsa.subcontract_po_shipment_id
, oola.line_id
, jsa.shikyu_component_id
, jsa.allocated_quantity
, jsa.uom
--Bugfix 14246759: Adding actual_shipment_date
, oola.actual_shipment_date
, oola.schedule_ship_date
, ooha.order_number
, oola.line_number
FROM JMF_SHIKYU_ALLOCATIONS jsa,
OE_ORDER_LINES_ALL oola,
OE_ORDER_HEADERS_ALL ooha
WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jsa.shikyu_component_id = p_component_id
AND oola.line_id = jsa.replenishment_so_line_id
AND ooha.header_id = oola.header_id
--Bugfix 14246759: Adding actual_shipment_date
ORDER BY nvl(oola.actual_shipment_date, oola.schedule_ship_date) DESC,
ooha.order_number DESC,
oola.line_number DESC;
SELECT jsa.allocated_quantity, jsa.uom
FROM JMF_SHIKYU_ALLOCATIONS jsa
WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jsa.replenishment_so_line_id = p_replen_so_line_id;
x_reduced_allocations_tbl.DELETE;
PROCEDURE Delete_Allocations
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_subcontract_po_shipment_id IN NUMBER
, p_component_id IN NUMBER
, p_replen_so_line_id IN NUMBER
, x_deleted_allocations_tbl OUT NOCOPY g_allocation_qty_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Allocations';
l_deleted_primary_uom_qty NUMBER;
DELETE FROM jmf_shikyu_allocations
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND shikyu_component_id = p_component_id
RETURNING subcontract_po_shipment_id,
replenishment_so_line_id,
shikyu_component_id,
allocated_quantity,
uom
BULK COLLECT INTO x_deleted_allocations_tbl;
DELETE FROM jmf_shikyu_allocations
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND shikyu_component_id = p_component_id
AND replenishment_so_line_id = p_replen_so_line_id
RETURNING subcontract_po_shipment_id,
replenishment_so_line_id,
shikyu_component_id,
allocated_quantity,
uom
BULK COLLECT INTO x_deleted_allocations_tbl;
DELETE FROM jmf_shikyu_allocations
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
RETURNING subcontract_po_shipment_id,
replenishment_so_line_id,
shikyu_component_id,
allocated_quantity,
uom
BULK COLLECT INTO x_deleted_allocations_tbl;
DELETE FROM jmf_shikyu_allocations
WHERE replenishment_so_line_id = p_replen_so_line_id
RETURNING subcontract_po_shipment_id,
replenishment_so_line_id,
shikyu_component_id,
allocated_quantity,
uom
BULK COLLECT INTO x_deleted_allocations_tbl;
l_tbl_index := x_deleted_allocations_tbl.FIRST;
|| ': x_deleted_allocations_tbl.FIRST = ' || NVL(TO_CHAR(l_tbl_index), 'NULL'));
SELECT jsr.uom
INTO l_replen_uom
FROM JMF_SHIKYU_REPLENISHMENTS jsr
WHERE jsr.replenishment_so_line_id = x_deleted_allocations_tbl(l_tbl_index).replenishment_so_line_id;
IF l_replen_uom <> x_deleted_allocations_tbl(l_tbl_index).qty_uom
THEN
l_deleted_primary_uom_qty := INV_CONVERT.inv_um_convert
( item_id => x_deleted_allocations_tbl(l_tbl_index).component_id
, precision => 5
, from_quantity => x_deleted_allocations_tbl(l_tbl_index).qty
, from_unit => x_deleted_allocations_tbl(l_tbl_index).qty_uom
, to_unit => l_replen_uom
, from_name => null
, to_name => null
);
l_deleted_primary_uom_qty := x_deleted_allocations_tbl(l_tbl_index).qty;
UPDATE jmf_shikyu_replenishments
SET allocated_quantity = allocated_quantity - l_deleted_primary_uom_qty,
allocated_primary_uom_quantity
= allocated_primary_uom_quantity - x_deleted_allocations_tbl(l_tbl_index).qty,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE replenishment_so_line_id = x_deleted_allocations_tbl(l_tbl_index).replenishment_so_line_id;
l_tbl_index := x_deleted_allocations_tbl.next(l_tbl_index);
END Delete_Allocations;
SELECT jsr.replenishment_so_line_id,
jsr.shikyu_component_id,
oola.shipped_quantity,
jsr.uom,
oola.ordered_quantity,
jsr.primary_uom,
oola.schedule_ship_date,
--Bugfix 14246759: Adding this because the record structure g_replen_so_qty_rec_type
--has changed.
oola.actual_shipment_date
FROM jmf_shikyu_replenishments jsr,
oe_order_lines_all oola
WHERE jsr.replenishment_so_line_id = oola.line_id
--Bugfix 14078692: Adding nvl.
AND nvl(jsr.status, 'XXX') <> 'CLOSED'
AND nvl(jsr.status,'XXX') <> 'CANCELLED'
--AND oola.open_flag = 'N' --Bugfix 14078692: The line might not be closed.
AND oola.shipped_quantity <> oola.ordered_quantity
AND oola.shipped_quantity <> jsr.allocable_quantity
--Bugfix 14078692: Additional conditions to pick up over-shipped
--and under-shipped SO lines.
AND oola.ordered_quantity = jsr.allocable_quantity
AND (--Under-shipped SO lines.
(oola.ordered_quantity - oola.shipped_quantity - (oola.ordered_quantity * (oola.ship_tolerance_below/100)) <= 0)
OR
--Over-shipped SO lines.
(oola.shipped_quantity > oola.ordered_quantity)
);
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocable_quantity = l_closed_so_line_rec.qty,
allocable_primary_uom_quantity = l_closed_so_line_rec.primary_uom_qty,
status = 'CLOSED',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE replenishment_so_line_id = l_closed_so_line_rec.replenishment_so_line_id;
SELECT jsa.subcontract_po_shipment_id,
jsa.replenishment_so_line_id,
jsa.shikyu_component_id,
jsa.allocated_quantity,
jsa.uom,
NVL(plla.need_by_date, plla.promised_date),
pha.segment1,
pla.line_num,
plla.shipment_num
FROM JMF_SHIKYU_ALLOCATIONS jsa,
PO_LINE_LOCATIONS_ALL plla,
PO_LINES_ALL pla,
PO_HEADERS_ALL pha
WHERE jsa.replenishment_so_line_id = p_replen_order_line_id
AND jsa.shikyu_component_id = l_component_id
AND jsa.subcontract_po_shipment_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND plla.po_header_id = pha.po_header_id
ORDER BY NVL(plla.need_by_date, plla.promised_date) DESC,
pha.segment1 DESC,
pla.line_num DESC,
plla.shipment_num DESC;
Select jsr.shikyu_component_id,
jsr.primary_uom,
jsr.allocable_primary_uom_quantity,
jsr.allocated_primary_uom_quantity,
oola.shipped_quantity,
oola.order_quantity_uom
INTO l_component_id,
l_primary_uom,
l_allocable_primary_qty,
l_allocated_primary_qty,
l_shipped_primary_qty,
l_ordered_uom
FROM JMF_SHIKYU_REPLENISHMENTS jsr,
OE_ORDER_LINES_ALL oola
WHERE jsr.REPLENISHMENT_SO_LINE_ID = p_replen_order_line_id
AND jsr.replenishment_so_line_id = oola.line_id;
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocable_quantity = l_allocable_qty,
allocable_primary_uom_quantity = l_allocable_primary_qty,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE replenishment_so_line_id = p_replen_order_line_id;
JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
( P_API_VERSION => 1.0
, P_INIT_MSG_LIST => p_init_msg_list
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
, X_MSG_DATA => x_msg_data
, P_SUBCONTRACT_PO_SHIPMENT_ID => NULL
, P_COMPONENT_ID => NULL
, P_REPLEN_SO_LINE_ID => p_replen_order_line_id
, X_DELETED_ALLOCATIONS_TBL => l_reduced_allocations_tbl
);
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocable_quantity = 0,
allocable_primary_uom_quantity = 0,
allocated_quantity = 0,
allocated_primary_uom_quantity = 0,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE replenishment_so_line_id = p_replen_order_line_id;
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocable_quantity = l_allocable_qty,
allocable_primary_uom_quantity = l_allocable_primary_qty,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE replenishment_so_line_id = p_replen_order_line_id;
l_deleted_qty NUMBER;
l_deleted_allocations_tbl
JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type;
SELECT jsr.replenishment_so_line_id replenishment_so_line_id
, jsr.replenishment_so_header_id replenishment_so_header_id
, jsr.schedule_ship_date schedule_ship_date
, jsr.replenishment_po_header_id replenishment_po_header_id
, jsr.replenishment_po_line_id replenishment_po_line_id
, jsr.replenishment_po_shipment_id replenishment_po_shipment_id
, jsr.oem_organization_id oem_organization_id
, jsr.tp_organization_id tp_organization_id
, oeh.cancelled_flag oeh_cancelled_flag
, oel.cancelled_flag oel_cancelled_flag
, oel.shipped_quantity oel_shipped_quantity
, oel.ordered_quantity oel_ordered_quantity
, jsr.shikyu_component_id shikyu_component_id
, jsr.ORDERED_QUANTITY jsr_ordered_quantity
, jsr.ALLOCATED_PRIMARY_UOM_QUANTITY allocated_primary_UOM_quantity
--, oel.closed_flag closed_flag
, jsa.subcontract_po_shipment_id subcontract_po_shipment_id
, jsr.ALLOCABLE_PRIMARY_UOM_QUANTITY allocable_primary_UOM_quantity
, jsr.allocable_quantity allocable_quantity
, jsr.allocated_quantity allocated_quantity
FROM JMF_SHIKYU_REPLENISHMENTS jsr
, OE_ORDER_LINES_ALL oel
, OE_ORDER_HEADERS_ALL oeh
, JMF_SHIKYU_ALLOCATIONS jsa
WHERE oeh.header_id = jsr.REPLENISHMENT_SO_HEADER_ID
AND oel.header_id = oeh.header_id
AND oel.line_id = jsr.replenishment_so_line_id
AND jsa.shikyu_component_id(+) = jsr.shikyu_component_id
AND jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
--Bugfix 14078692: Picking up only positive quantities.
AND jsr.allocable_quantity > 0
AND ( oeh.cancelled_flag = 'Y' OR
oel.cancelled_flag = 'Y' OR
--Bugfix 14078692: Considering quantity modifications also.
--oel.ordered_quantity < jsr.allocable_quantity );
SELECT line_id
, ordered_quantity
, schedule_ship_date
, header_id
, split_from_line_id
, line_number FROM (
SELECT line_id
, ordered_quantity
, schedule_ship_date
, header_id
, split_from_line_id
, line_number
FROM oe_order_lines_all
WHERE header_id = l_header_id )
CONNECT BY PRIOR line_id = split_from_line_id
START WITH line_id = l_parent_so_line_id;
SELECT line_id
, ordered_quantity
, schedule_ship_date
, header_id
, split_from_line_id
FROM oe_order_lines_all
WHERE header_id = l_header_id
and split_from_line_id = l_parent_so_line_id ;
l_deleted_qty := 0 ;
SELECT UOM,
PRIMARY_UOM
INTO l_uom,
l_primary_uom
FROM JMF_SHIKYU_REPLENISHMENTS
WHERE REPLENISHMENT_SO_LINE_ID = C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocable_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
allocable_primary_uom_quantity = l_primary_uom_qty,
ordered_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
ordered_primary_uom_quantity = l_primary_uom_qty,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE REPLENISHMENT_SO_LINE_ID = C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
, 'Updated rows:' || sql%rowcount ||
':for RSO line_id:' || C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
);
, 'JMFVSKAB :Invoke JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations '
, C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id);
JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
( p_api_version => 1.0
, p_init_msg_list => NULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_subcontract_po_shipment_id =>
C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id
, p_component_id =>
C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
, p_replen_so_line_id =>
C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
, x_deleted_allocations_tbl => l_deleted_allocations_tbl
);
, 'Delete allocations returned error. Status:' || l_return_status);
, 'Delete allocations returned success. Status:' || l_return_status);
, 'JMFVSKAB :AFter l_deleted_allocations_tbl.count '
, l_deleted_allocations_tbl.COUNT );
IF l_deleted_allocations_tbl.COUNT > 0
THEN
l_deleted_qty := l_deleted_allocations_tbl(1).qty ;
l_deleted_qty := 0;
END IF; /* IF l_deleted_allocations_tbl.COUNT > 0 */
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocable_quantity = 0,
allocable_primary_uom_quantity = 0,
allocated_quantity = 0,
allocated_primary_uom_quantity = 0,
ordered_quantity = 0,
ordered_primary_uom_quantity = 0,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE replenishment_so_line_id = C_SHIKYU_REPLENISHMENT_REC.replenishment_so_line_id
AND replenishment_so_header_id = C_SHIKYU_REPLENISHMENT_REC.replenishment_so_header_id ;
IF l_deleted_qty > 0
THEN
IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
THEN
FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
, 'JMFVSKAB :1Cal JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations '
, l_deleted_qty );
, p_qty => l_deleted_qty
, p_skip_po_replen_creation => 'N'
);
END IF; /* l_deleted_qty > 0 */
SELECT UOM,
PRIMARY_UOM
INTO l_uom,
l_primary_uom
FROM JMF_SHIKYU_REPLENISHMENTS
WHERE REPLENISHMENT_SO_LINE_ID =
C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocable_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
allocable_primary_uom_quantity = l_primary_uom_qty,
ordered_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
ordered_primary_uom_quantity = l_primary_uom_qty,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE REPLENISHMENT_SO_LINE_ID = C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
, 'After UPDATE JMF_SHIKYU_REPLENISHMENTS for line_id :' ||
C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
);
INSERT INTO JMF_SHIKYU_REPLENISHMENTS
( REPLENISHMENT_SO_LINE_ID
, REPLENISHMENT_SO_HEADER_ID
, SCHEDULE_SHIP_DATE
, REPLENISHMENT_PO_HEADER_ID
, REPLENISHMENT_PO_LINE_ID
, REPLENISHMENT_PO_SHIPMENT_ID
, OEM_ORGANIZATION_ID
, TP_ORGANIZATION_ID
, TP_SUPPLIER_ID
, TP_SUPPLIER_SITE_ID
, SHIKYU_COMPONENT_ID
, ORDERED_QUANTITY
, ALLOCATED_QUANTITY
, ALLOCABLE_QUANTITY
, ORDERED_PRIMARY_UOM_QUANTITY
, ALLOCATED_PRIMARY_UOM_QUANTITY
, ALLOCABLE_PRIMARY_UOM_QUANTITY
, UOM
, PRIMARY_UOM
, ADDITIONAL_SUPPLY
, ORG_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
)
SELECT
C_child_so_lines_rec.line_id
, C_child_so_lines_rec.header_id
, C_child_so_lines_rec.schedule_ship_date
, REPLENISHMENT_PO_HEADER_ID
, REPLENISHMENT_PO_LINE_ID
, REPLENISHMENT_PO_SHIPMENT_ID
, OEM_ORGANIZATION_ID
, TP_ORGANIZATION_ID
, TP_SUPPLIER_ID
, TP_SUPPLIER_SITE_ID
, SHIKYU_COMPONENT_ID
, C_child_so_lines_rec.ordered_quantity -- ordered qty
, 0 -- allocated qty
, C_child_so_lines_rec.ordered_quantity -- allocable qty
, l_primary_uom_qty -- ordered qty in primary UOM
, 0 -- allocated qty in primary UOM
, l_primary_uom_qty -- allocable qty in primary UOM
, UOM
, PRIMARY_UOM
, ADDITIONAL_SUPPLY
, ORG_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
FROM JMF_SHIKYU_REPLENISHMENTS jsr
WHERE REPLENISHMENT_SO_LINE_ID =
C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
AND NOT EXISTS (
SELECT jsr1.REPLENISHMENT_SO_LINE_ID
FROM JMF_SHIKYU_REPLENISHMENTS jsr1
WHERE jsr1.REPLENISHMENT_SO_LINE_ID =
C_child_so_lines_rec.line_id );
, 'JMFVSKAB :after INSERT child = '|| C_child_so_lines_rec.line_id
, C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID );
SELECT oola.header_id,
jsr.allocable_primary_uom_quantity,
jsr.allocated_primary_uom_quantity,
jsr.uom,
jsr.primary_uom,
'Y'
INTO x_header_id,
x_allocable_primary_uom_qty,
x_allocated_primary_uom_qty,
x_uom,
x_primary_uom,
x_replen_so_line_exists
FROM JMF_SHIKYU_REPLENISHMENTS jsr,
OE_ORDER_LINES_ALL oola
WHERE jsr.replenishment_so_line_id = p_replen_so_line_id
AND jsr.replenishment_so_line_id = oola.line_id;
SELECT jsa.allocated_quantity,
jsa.uom,
'Y'
INTO x_allocated_qty,
x_uom,
x_allocation_exists
FROM JMF_SHIKYU_ALLOCATIONS jsa
WHERE jsa.replenishment_so_line_id = p_replen_so_line_id
AND jsa.shikyu_component_id = p_component_id
AND jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
SELECT plla.po_header_id,
plla.po_line_id
INTO l_replen_po_header_id,
l_replen_po_line_id
FROM PO_LINE_LOCATIONS_ALL plla
WHERE plla.line_location_id = p_replen_po_shipment_id;
SELECT oola.header_id,
oola.ordered_quantity,
oola.order_quantity_uom,
oola.schedule_ship_date,
oola.org_id
INTO l_replen_so_header_id,
l_ordered_qty,
l_ordered_uom,
l_schedule_ship_date,
l_org_id
FROM OE_ORDER_LINES_ALL oola
WHERE oola.line_id = p_replen_so_line_id;
SELECT TO_NUMBER(org_information3),
TO_NUMBER(org_information4)
INTO l_tp_supplier_id,
l_tp_supplier_site_id
FROM hr_organization_information
WHERE organization_id = p_tp_organization_id
AND org_information_context = 'Customer/Supplier Association';
INSERT INTO JMF_SHIKYU_REPLENISHMENTS
( replenishment_so_line_id
, replenishment_so_header_id
, schedule_ship_date
, replenishment_po_header_id
, replenishment_po_line_id
, replenishment_po_shipment_id
, oem_organization_id
, tp_organization_id
, tp_supplier_id
, tp_supplier_site_id
, shikyu_component_id
, ordered_quantity
, allocated_quantity
, allocable_quantity
, ordered_primary_uom_quantity
, allocated_primary_uom_quantity
, allocable_primary_uom_quantity
, uom
, primary_uom
, org_id
, additional_supply
, status
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
)
VALUES
( p_replen_so_line_id
, l_replen_so_header_id
, l_schedule_ship_date
, l_replen_po_header_id
, l_replen_po_line_id
, p_replen_po_shipment_id
, p_oem_organization_id
, p_tp_organization_id
, l_tp_supplier_id
, l_tp_supplier_site_id
, p_component_id
, l_ordered_qty
, 0
, l_ordered_qty
, l_primary_uom_qty
, 0
, l_primary_uom_qty
, l_ordered_uom
, l_primary_uom
, l_org_id
, p_additional_supply
, NULL
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
);
SELECT
oem_organization_id, tp_organization_id
INTO
l_oem_org_id, l_mp_org_id
FROM
JMF_SUBCONTRACT_ORDERS
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;
SELECT count(*)
INTO l_count
FROM jmf_shikyu_components jsc
WHERE jsc.shikyu_component_id = p_component_id
AND jsc.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND EXISTS (SELECT 'x'
FROM oe_order_lines_all oola,
oe_order_headers_all ooha
WHERE oola.line_id = p_replen_so_line_id
AND oola.inventory_item_id = p_component_id
AND oola.price_list_id = jsc.price_list_id
AND oola.header_id = ooha.header_id
AND ooha.transactional_curr_code = jsc.currency
AND oola.unit_selling_price
= DECODE(oola.pricing_quantity_uom,
jsc.uom , jsc.shikyu_component_price,
jsc.primary_uom, jsc.primary_uom_price,
-1));
SELECT count(*)
INTO l_count
FROM mtl_parameters mtlp,
jmf_subcontract_orders jso
WHERE mtlp.organization_id = jso.tp_organization_id
AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND ( (mtlp.project_control_level = 1
AND EXISTS (SELECT 'x'
FROM oe_order_lines_all oola
WHERE oola.line_id = p_replen_so_line_id
AND jso.project_id IS NOT NULL
AND jso.project_id = oola.project_id
AND NVL(jso.task_id, -1) = NVL(oola.task_id, -1)))
OR (mtlp.project_control_level = 2
AND EXISTS (SELECT 'x'
FROM oe_order_lines_all oola
WHERE oola.line_id = p_replen_so_line_id
AND jso.project_id IS NOT NULL
AND jso.task_id IS NOT NULL
AND jso.project_id = oola.project_id
AND jso.task_id = oola.task_id))
OR (jso.project_id IS NULL
AND jso.task_id IS NULL
AND EXISTS (SELECT 'x'
FROM oe_order_lines_all oola
WHERE oola.line_id = p_replen_so_line_id
AND oola.project_id IS NULL
AND oola.task_id IS NULL))
);
UPDATE JMF_SHIKYU_ALLOCATIONS
SET allocated_quantity = l_new_allocated_qty
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND replenishment_so_line_id = p_replen_so_line_id
RETURNING subcontract_po_shipment_id,
replenishment_so_line_id,
shikyu_component_id,
allocated_quantity,
uom
INTO x_reduced_allocations_rec;
, l_api_name || ': Updated JMF_SHIKYU_ALLOCATIONS table, '
|| 'x_reduced_allocations_rec.qty = '
|| x_reduced_allocations_rec.qty
);
DELETE FROM JMF_SHIKYU_ALLOCATIONS
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND replenishment_so_line_id = p_replen_so_line_id
RETURNING subcontract_po_shipment_id,
replenishment_so_line_id,
shikyu_component_id,
allocated_quantity,
uom
INTO x_reduced_allocations_rec;
SELECT uom
INTO l_replen_uom
FROM JMF_SHIKYU_REPLENISHMENTS
WHERE replenishment_so_line_id = p_replen_so_line_id;
UPDATE JMF_SHIKYU_REPLENISHMENTS
SET allocated_quantity = allocated_quantity - l_remain_qty_to_reduce,
allocated_primary_uom_quantity = allocated_primary_uom_quantity - l_reduce_replen_uom_qty
WHERE replenishment_so_line_id = p_replen_so_line_id;
l_deleted_qty NUMBER;
l_deleted_allocations_tbl JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type;
select jsr.replenishment_so_line_id line_id,
jsr.schedule_ship_date jmf_schedule_ship_date,
jsr.oem_organization_id oem_organization_id,
jsr.tp_organization_id tp_organization_id,
oel.schedule_ship_date om_schedule_ship_date
from jmf_shikyu_replenishments jsr,
oe_order_lines_all oel
where oel.line_id = jsr.replenishment_so_line_id
and oel.schedule_ship_date <> jsr.schedule_ship_date
and oel.actual_shipment_date is null --pick up only unshipped lines. Rescheduling can only be done if the line is not shipped though.
and oel.open_flag = 'Y';
select jsa.shikyu_component_id shikyu_component_id,
jsa.subcontract_po_shipment_id subcontract_po_shipment_id,
jso.wip_entity_id wip_entity_id
from jmf_shikyu_allocations jsa,
jmf_subcontract_orders jso
where jsa.replenishment_so_line_id = l_line_id
and jsa.subcontract_po_shipment_id = jso.subcontract_po_shipment_id;
update jmf_shikyu_replenishments
set schedule_ship_date = so_date_change_rec.om_schedule_ship_date,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where replenishment_so_line_id = l_line_id;
,'SO is backward scheduled:: Rows Updated in JSR:' || l_cnt
);
JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations(p_api_version => 1.0
, p_init_msg_list => NULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_subcontract_po_shipment_id => c_alloc_rec.subcontract_po_shipment_id
, p_component_id => c_alloc_rec.shikyu_component_id
, p_replen_so_line_id => l_line_id
, x_deleted_allocations_tbl => l_deleted_allocations_tbl
);
, 'Delete allocations returned error. Status:' || l_return_status);
, 'Delete allocations returned success. Status:' || l_return_status ||
':for SCO_shipment_id:' || c_alloc_rec.subcontract_po_shipment_id ||
':component_id:' || c_alloc_rec.shikyu_component_id ||
':line_id:' || l_line_id);
if l_deleted_allocations_tbl.count > 0
then
l_deleted_qty := l_deleted_allocations_tbl(1).qty;
l_deleted_qty := 0;
,'Rows deleted:' || l_deleted_allocations_tbl.count ||
':Deleted Quantity:' || l_deleted_qty);
if l_deleted_qty > 0 then
JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations(p_api_version => 1.0
, p_init_msg_list => NULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_subcontract_po_shipment_id => c_alloc_rec.subcontract_po_shipment_id
, p_component_id => c_alloc_rec.shikyu_component_id
, p_qty => l_deleted_qty --check if this needs to be in puom
, p_skip_po_replen_creation => 'N'
);
end if; --l_deleted_qty > 0
update jmf_shikyu_replenishments
set schedule_ship_date = so_date_change_rec.om_schedule_ship_date,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where replenishment_so_line_id = l_line_id;
, 'l_deletion_failed = 0. Rows updated in JSR::' || l_cnt);