[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT adjustment_id
FROM jmf_shikyu_adjustments --JMF_SUBCONTRACT_ORDERS
WHERE request_id IS NULL
AND batch_id IS NULL
AND adjustment < 0
--AND group_id = NVL(p_group_id,group_id) --group_id is for future use
ORDER BY adjustment_id;
SELECT DISTINCT adjustment_id
FROM jmf_shikyu_adjustments --JMF_SUBCONTRACT_ORDERS
WHERE request_id IS NULL
AND batch_id IS NULL
AND adjustment > 0
--AND group_id = NVL(p_group_id,group_id) --group_id is for future use
ORDER BY adjustment_id;
SELECT COUNT(adjustment_id)
INTO l_adjust_rows
FROM jmf_shikyu_adjustments
WHERE request_id IS NULL
AND batch_id IS NULL
AND adjustment <> 0
--AND group_id = NVL(p_group_id,group_id) --group_id is for future use
;
SELECT jmf_shikyu_adj_batch_s.NEXTVAL
INTO l_batch_id
FROM dual;
UPDATE jmf_shikyu_adjustments
SET batch_id = l_batch_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE adjustment_id >= l_cur_batch_min_adj_id
AND adjustment_id <= l_cur_batch_max_adj_id
AND request_id IS NULL
AND batch_id IS NULL
AND adjustment < 0
--AND group_id = NVL(p_group_id,group_id) --for group--group_id is for future use
;
/*UPDATE jmf_shikyu_adjustments
SET request_id = l_request_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = l_batch_id
--AND request_id IS NULL
;*/
UPDATE jmf_shikyu_adjustments
SET batch_id = NULL,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = l_batch_id
--AND request_id IS NULL
;
SELECT jmf_shikyu_adj_batch_s.NEXTVAL
INTO l_batch_id
FROM dual;
UPDATE jmf_shikyu_adjustments
SET batch_id = l_batch_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE adjustment_id >= l_cur_batch_min_adj_id
AND adjustment_id <= l_cur_batch_max_adj_id
AND request_id IS NULL
AND batch_id IS NULL
AND adjustment > 0
--AND group_id = NVL(p_group_id,group_id) --for group --group_id is for future use
;
/*UPDATE jmf_shikyu_adjustments
SET request_id = l_request_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = l_batch_id*/
--AND request_id IS NULL
l_batch_request_id_tbl(l_batch_id) := l_request_id
;
UPDATE jmf_shikyu_adjustments
SET batch_id = NULL,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = l_batch_id
--AND request_id IS NULL
;
,p_message => 'Update jmf_shikyu_adjustments'
);
UPDATE jmf_shikyu_adjustments
SET request_id = l_batch_request_id_tbl(l_cur_batch_id_index),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = l_cur_batch_id_index
AND request_id IS NULL
;
UPDATE jmf_shikyu_adjustments
SET request_id = NULL,
batch_id = NULL,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = l_cur_batch_id_index
AND request_id = -1
;
UPDATE jmf_shikyu_adjustments
SET batch_id = NULL,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE request_id IS NULL
AND batch_id IS NOT NULL;
SELECT request_id
FROM fnd_concurrent_requests
WHERE parent_request_id = lp_parent_request_id
ORDER BY request_id;
SELECT parent_request_id
INTO l_parent_request_id
FROM fnd_concurrent_requests
WHERE request_id = l_request_id;
SELECT adjustment_id
,subcontract_po_shipment_id
,shikyu_component_id
,adjustment
,uom
FROM jmf_shikyu_adjustments
WHERE batch_id = p_batch_id
ORDER BY adjustment;
UPDATE jmf_shikyu_adjustments
SET request_id = -1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE adjustment_id = l_adjustment_id;
SELECT ph.segment1 po_number
,pl.line_num po_line_num
,pll.shipment_num po_shipment_num
,item_kfv.concatenated_segments Item_num
INTO l_po_num
,l_po_line_num
,l_shipment_num
,l_component
FROM po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
,mtl_system_items_b_kfv item_kfv
,jmf_shikyu_components jsc
WHERE pl.po_line_id = pll.po_line_id
AND ph.po_header_id = pll.po_header_id
AND pll.ship_to_organization_id = item_kfv.organization_id
AND jsc.shikyu_component_id = item_kfv.inventory_item_id
AND jsc.subcontract_po_shipment_id = pll.line_location_id
AND pll.line_location_id = p_subcontract_po_shipment_id
AND item_kfv.inventory_item_id = p_component_id;
SELECT wro.quantity_issued
,jsc.primary_uom
INTO l_wip_consumed_qty
,l_wip_consumed_uom
FROM wip_requirement_operations wro
,jmf_subcontract_orders jso
,jmf_shikyu_components jsc
WHERE wro.wip_entity_id = jso.wip_entity_id
AND wro.organization_id = jso.tp_organization_id
AND wro.inventory_item_id = jsc.shikyu_component_id
AND wro.repetitive_schedule_id IS NULL
AND wro.operation_seq_num = 1
AND jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
AND jsc.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jsc.shikyu_component_id = p_component_id;
SELECT SUM(jsa.allocated_quantity) --this meam the jsa.allocated_quantity is under Primary UOM
,MAX(jsa.uom)
INTO l_total_allocated_qty
,l_total_allocated_uom
FROM jmf_shikyu_allocations jsa
WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND jsa.shikyu_component_id = p_component_id;
SELECT nvl(SUM(adjustment), 0)
INTO l_adjustment_total
FROM jmf_shikyu_adjustments
WHERE subcontract_po_shipment_id = p_po_shipment_id
AND shikyu_component_id = p_component_id
AND request_id IS NOT NULL;