[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mip.from_organization_id oem_organization_id
, hoi.organization_id tp_organization_id
, p_vendor_id vendor_id
, p_vendor_site_id vendor_site_id
, 'Y' status
FROM HR_ORGANIZATION_INFORMATION hoi
, mtl_interorg_parameters mip
, mtl_parameters mp
WHERE mip.to_organization_id = mp.organization_id
AND mp.organization_id = hoi.organization_id
AND mp.trading_partner_org_flag = 'Y'
AND mip.from_organization_id = p_organization_id
AND hoi.org_information_context = 'Customer/Supplier Association'
AND hoi.org_information3 = to_char(p_vendor_id) --Bugfix 9315131
AND hoi.org_information4 = to_char(p_vendor_site_id); --Bugfix 9315131
SELECT lot_control_code,
serial_number_control_code
INTO l_lot_control_code,
l_serial_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT NVL(eam_enabled_flag,'N')
, wms_enabled_Flag
, process_enabled_flag
INTO x_eam_enabled
, x_wms_enabled
, x_process_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT
subcontract_po_shipment_id
, subcontract_po_header_id
, subcontract_po_line_id
, oem_organization_id
, tp_organization_id
, need_by_date
, vendor_id
, vendor_site_id
, uom
, currency
, quantity
, osa_item_id
, osa_item_price
, project_id
, task_id
FROM JMF_SUBCONTRACT_ORDERS_TEMP;
SELECT distinct project_id
, task_id
, line_location_id
FROM po_distributions_all
WHERE line_location_id = l_shipment_id
AND project_id IS NOT NULL;
INSERT INTO JMF_SUBCONTRACT_ORDERS_TEMP
( subcontract_po_shipment_id
, subcontract_po_header_id
, subcontract_po_line_id
, oem_organization_id
, tp_organization_id
, need_by_date
, vendor_id
, vendor_site_id
, uom
, currency
, quantity
, osa_item_id
, osa_item_price
, project_id
, task_id
)
SELECT /*+ PARALLEL(poll) */
poll.line_location_id
, poll.po_header_id
, poll.po_line_id
, poll.ship_to_organization_id
, null
, nvl(poll.need_by_date, poll.promised_date)
, poh.vendor_id
, poh.vendor_site_id
, muom.uom_code
, poh.currency_code
, poll.quantity
, pol.item_id
, poll.price_override
, pol.project_id
, pol.task_id
FROM
po_line_locations_all poll
, po_headers_all poh
, po_lines_all pol
, mtl_units_of_measure muom
, po_releases_all por
WHERE poll.po_header_id = poh.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poh.po_header_id = pol.po_header_id
AND poll.po_release_id = por.po_release_id (+)
AND NVL(poll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = muom.unit_of_measure
AND ((pol.closed_code = 'OPEN') OR (pol.closed_code IS NULL))
AND poh.approved_flag = 'Y'
AND nvl(poh.cancel_flag, 'N') = 'N'
AND nvl(pol.cancel_flag, 'N') = 'N'
AND nvl(poll.cancel_flag, 'N') = 'N'
AND poll.outsourced_assembly = 1
AND poll.org_id = p_operating_unit
AND DECODE(poll.po_release_id,
NULL, 'Y',
por.approved_flag) = 'Y'
AND poll.ship_to_organization_id
BETWEEN
(NVL(p_from_organization,poll.ship_to_organization_id))
AND
(NVL(p_to_organization,poll.ship_to_organization_id)
)
AND NOT EXISTS
( SELECT subcontract_po_shipment_id
FROM JMF_SUBCONTRACT_ORDERS jso
WHERE poll.line_location_id = jso.subcontract_po_shipment_id
)
--Begin ER#9775673
AND poh.segment1
BETWEEN
(NVL(p_from_po_number, poh.segment1))
AND
(NVL(p_to_po_number, poh.segment1))
AND ( (p_days_in_advance is null) OR
(NVL(poll.promised_date, poll.need_by_date) <= sysdate + p_days_in_advance)
);
UPDATE jmf_subcontract_orders_temp
SET project_id = l_project_tbl(l_curr_index).project_id
, task_id = l_project_tbl(l_curr_index).task_id
WHERE subcontract_po_shipment_id = l_shipment_id;
DELETE FROM JMF_SUBCONTRACT_ORDERS_TEMP
WHERE subcontract_po_shipment_id = l_subcontract_rec(i).subcontract_po_shipment_id;
UPDATE JMF_SUBCONTRACT_ORDERS_TEMP
SET tp_organization_id = l_subcontract_rec(i).tp_organization_id
WHERE subcontract_po_shipment_id = l_subcontract_rec(i).subcontract_po_shipment_id;
USING (SELECT subcontract_po_shipment_id
, subcontract_po_header_id
, subcontract_po_line_id
, oem_organization_id
, tp_organization_id
, osa_item_id
, osa_item_price
, need_by_date
, uom
, currency
, quantity
, project_id
, task_id
FROM JMF_SUBCONTRACT_ORDERS_TEMP ) jsot
ON ( jso.subcontract_po_shipment_id = jsot.subcontract_po_shipment_id)
WHEN NOT MATCHED THEN
INSERT
( jso.subcontract_po_shipment_id
, jso.subcontract_po_header_id
, jso.subcontract_po_line_id
, jso.oem_organization_id
, jso.tp_organization_id
, jso.osa_item_id
, jso.osa_item_price
, jso.need_by_date
, jso.uom
, jso.currency
, jso.quantity
, jso.batch_id
, jso.project_id
, jso.task_id
, jso.last_update_date
, jso.last_updated_by
, jso.creation_date
, jso.created_by
, jso.last_update_login
, jso.interlock_status
)
VALUES
( jsot.subcontract_po_shipment_id
, jsot.subcontract_po_header_id
, jsot.subcontract_po_line_id
, jsot.oem_organization_id
, jsot.tp_organization_id
, jsot.osa_item_id
, jsot.osa_item_price
, jsot.need_by_date
, jsot.uom
, jsot.currency
, jsot.quantity
, -1
, jsot.project_id
, jsot.task_id
, sysdate
, FND_GLOBAL.user_id
, sysdate
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, 'N'
);
UPDATE jmf_subcontract_orders
SET interlock_status ='N'
, batch_id = -1
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE interlock_status = 'E'
AND EXISTS
( SELECT 'X'
FROM jmf_subcontract_orders
WHERE interlock_status = 'E');
SELECT DISTINCT plla.line_location_id,
hoi.organization_id as oem_organization_id,
plla.ship_to_organization_id as tp_organization_id,
pla.item_id as shikyu_component_id,
msib.subcontracting_component,
plla.quantity,
plla.need_by_date,
NVL(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
pha.reference_num,
pha.segment1,
pla.line_num,
plla.shipment_num
FROM hr_organization_information hoi,
po_line_locations_all plla,
po_lines_all pla,
po_headers_all pha,
mtl_interorg_parameters mip,
mtl_system_items_b msib
WHERE 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 mip.to_organization_id = plla.ship_to_organization_id
AND mip.from_organization_id = hoi.organization_id
--AND mip.SHIKYU_ENABLED_FLAG = 'Y' /* SHIKYU_ENABLED_FLAG is no longer used*/
AND mip.subcontracting_type in ('B','C') /* 12.1 Buy/Sell Subcontracting Changes */
AND plla.po_line_id = pla.po_line_id
AND plla.po_header_id = pha.po_header_id
AND plla.org_id = p_operating_unit
AND pla.item_id = msib.inventory_item_id
AND hoi.organization_id = msib.organization_id
AND msib.subcontracting_component in (1, 2)
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 hoi.organization_id
BETWEEN
(NVL(p_from_organization, hoi.organization_id))
AND
(NVL(p_to_organization, hoi.organization_id)
)
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 plla.need_by_date,
pha.segment1,
pla.line_num,
plla.shipment_num;
SELECT jsc.subcontract_po_shipment_id
, jsc.shikyu_component_id
, sum(nvl(jsa.allocated_quantity,0))
, max(nvl(wro.required_quantity,0))
FROM jmf_shikyu_allocations jsa
, jmf_shikyu_components jsc
, jmf_subcontract_orders jso
, wip_requirement_operations wro
WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
AND jsc.shikyu_component_id=jsa.shikyu_component_id(+)
AND jso.interlock_status = 'P'
AND wro.wip_entity_id = jso.wip_entity_id
AND wro.inventory_item_id = jsc.shikyu_component_id
AND wro.organization_id = jso.tp_organization_id
GROUP BY jsc.shikyu_component_id
, jsc.subcontract_po_shipment_id
HAVING sum(nvl(jsa.allocated_quantity,0)) <
avg(nvl(wro.required_quantity,0));
SELECT jsc.subcontract_po_shipment_id
, jsc.shikyu_component_id
--Bugfix 9651506: segment1 is a varchar variable.
--to_number causes ORA-01722: invalid number.
--, max(TO_NUMBER(pha.segment1))
, max(TO_NUMBER(pla.line_num))
, max(TO_NUMBER(plla.shipment_num))
, max(plla.need_by_date)
, sum(nvl(jsa.allocated_quantity,0))
, max(nvl(wro.required_quantity,0))
, max(jsc.replen_so_creation_failed)
FROM jmf_shikyu_allocations jsa
, jmf_shikyu_components jsc
, jmf_subcontract_orders jso
, wip_requirement_operations wro
, po_line_locations_all plla
, po_lines_all pla
, po_headers_all pha
WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
AND jsc.shikyu_component_id = jsa.shikyu_component_id(+)
AND jso.interlock_status = 'P'
AND wro.wip_entity_id = jso.wip_entity_id
AND wro.inventory_item_id = jsc.shikyu_component_id
AND wro.organization_id = jso.tp_organization_id
AND plla.line_location_id = jso.subcontract_po_shipment_id
AND plla.po_line_id = pla.po_line_id
AND plla.po_header_id = pha.po_header_id
AND plla.org_id = p_operating_unit
AND nvl(pha.cancel_flag, 'N') = 'N'
AND nvl(pla.cancel_flag, 'N') = 'N'
AND nvl(plla.cancel_flag, 'N') = 'N'
AND jso.oem_organization_id
BETWEEN
NVL(p_from_organization, jso.oem_organization_id)
AND
NVL(p_to_organization, jso.oem_organization_id)
GROUP BY jsc.shikyu_component_id
, jsc.subcontract_po_shipment_id
HAVING sum(nvl(jsa.allocated_quantity,0)) <
avg(nvl(wro.required_quantity,0))
ORDER BY max(plla.need_by_date),
--Bugfix 9651506: segment1 is a varchar variable.
--to_number causes ORA-01722: invalid number.
--max(TO_NUMBER(pha.segment1)),
max(TO_NUMBER(pla.line_num)),
max(TO_NUMBER(plla.shipment_num));
SELECT header_id
, schedule_ship_date
INTO l_order_header_id
, l_ship_date
FROM oe_order_lines_all
WHERE line_id = l_order_line_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = l_component_id
AND organization_id = l_tp_organization_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 = l_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
, ordered_primary_uom_quantity
, uom
, primary_uom
, org_id
, additional_supply
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, allocable_quantity
, allocable_primary_uom_quantity
, allocated_quantity
, allocated_primary_uom_quantity
)
SELECT
l_order_line_id
, l_order_header_id
, l_ship_date
, poll.po_header_id
, poll.po_line_id
, poll.line_location_id
, l_oem_organization_id
, l_tp_organization_id
, l_tp_supplier_id
, l_tp_supplier_site_id
, l_component_id
, poll.quantity
, l_primary_uom_qty
, l_ordered_uom
, l_primary_uom
, poll.org_id
, l_additional_supply
, sysdate
, FND_GLOBAL.user_id
, sysdate
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, poll.quantity
, l_primary_uom_qty
, 0
, 0
FROM po_line_locations_all poll
WHERE poll.line_location_id = l_line_location_id;
UPDATE jmf_shikyu_components
SET replen_so_creation_failed = 'Y'
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = l_osa_shipment_id
AND shikyu_component_id = l_component_id;
UPDATE jmf_shikyu_components
SET replen_so_creation_failed = NULL
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = l_osa_shipment_id
AND shikyu_component_id = l_osa_component_id;
SELECT
jso.subcontract_po_shipment_id
, jso.osa_item_id
, jso.oem_organization_id
, jso.tp_organization_id
, jso.need_by_date
, poll.quantity
, NVL(poll.unit_meas_lookup_code, pla.unit_meas_lookup_code)
, NULL
, NULL
, NULL
, jso.project_id
, jso.task_id
, 'V'
FROM
jmf_subcontract_orders jso
, po_line_locations_all poll
, po_lines_all pla
WHERE poll.line_location_id = jso.subcontract_po_shipment_id
AND pla.po_line_id = poll.po_line_id
AND jso.interlock_status = 'N'
AND poll.org_id = p_operating_unit
AND NOT EXISTS
(SELECT shikyu_component_id
FROM jmf_shikyu_components
WHERE subcontract_po_shipment_id = jso.subcontract_po_shipment_id);
SELECT
component_item_id shikyu_component_id
, primary_uom_code primary_uom
, sum(component_quantity) quantity
, count(component_item_id) count_seq
FROM
bom_explosion_temp
WHERE group_id = l_group_id
AND assembly_item_id = l_parent_id
AND l_start_date BETWEEN
(effectivity_date) and NVL(disable_date,l_start_date+1)
GROUP BY component_item_id,primary_uom_code;
SELECT count(bor.routing_sequence_id)
INTO l_routing_count
FROM bom_operational_routings bor
WHERE bor.organization_id = l_osa_tbl(i).tp_organization_id
AND bor.assembly_item_id = l_osa_tbl(i).osa_item_id;
SELECT uom_code
INTO l_osa_tbl(i).uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure = l_osa_tbl(i).unit_of_measure;
UPDATE JMF_SUBCONTRACT_ORDERS
SET interlock_status = 'E'
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
SELECT COUNT(*)
INTO l_count_seq
FROM jmf_shikyu_components
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
INSERT INTO JMF_SHIKYU_COMPONENTS
( subcontract_po_shipment_id
, shikyu_component_id
, oem_organization_id
, primary_uom
, quantity
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
)
VALUES
( l_osa_tbl(i).subcontract_po_shipment_id
, l_comp_tbl(l_curr_index).inventory_item_id
, l_osa_tbl(i).oem_organization_id
, l_comp_tbl(l_curr_index).primary_uom_code
, l_comp_tbl(l_curr_index).primary_quantity
, sysdate
, FND_GLOBAL.user_id
, sysdate
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, null
, null
, null
, null
);
|| ': After insert into JMF_SHIKYU_COMPONENTS');
|| ' already inserted');
UPDATE JMF_SHIKYU_COMPONENTS
SET quantity = quantity + l_comp_tbl(l_curr_index).primary_quantity
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
UPDATE JMF_SUBCONTRACT_ORDERS
SET interlock_status = 'E'
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id =
l_osa_tbl(i).subcontract_po_shipment_id;
UPDATE JMF_SUBCONTRACT_ORDERS
SET interlock_status = 'E'
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
DELETE FROM jmf_shikyu_components
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
UPDATE JMF_SUBCONTRACT_ORDERS
SET interlock_status = 'E'
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
SELECT
jsc.subcontract_po_shipment_id
, jsc.shikyu_component_id
, jsc.quantity
, jso.oem_organization_id
, jso.tp_organization_id
FROM
jmf_subcontract_orders jso,
jmf_shikyu_components jsc,
po_line_locations_all plla
WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
AND shikyu_component_price IS NULL
AND plla.line_location_id = jso.subcontract_po_shipment_id
AND plla.org_id = p_operating_unit;
SELECT jmf_shikyu_batch_s.NEXTVAL
INTO l_batch_id
FROM dual;
SELECT *
FROM jmf_subcontract_orders
WHERE batch_id = p_batch_id
AND interlock_status in ('N','U');
SELECT *
FROM jmf_shikyu_components
WHERE subcontract_po_shipment_id = l_shipment_id;
SELECT plla.quantity
, NVL(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code)
INTO l_po_qty
, l_po_uom
FROM po_line_locations_all plla
, po_lines_all pla
WHERE plla.line_location_id = l_osa_tbl(i).subcontract_po_shipment_id
AND plla.po_line_id = pla.po_line_id;
SELECT wip_entity_id
INTO l_wip_entity_id
FROM JMF_SUBCONTRACT_ORDERS
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
UPDATE jmf_subcontract_orders
SET interlock_status ='P'
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
UPDATE jmf_subcontract_orders
SET interlock_status ='U'
, batch_id = -1
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
SELECT segment1 INTO l_osa_item
FROM mtl_system_items_b
WHERE inventory_item_id = l_osa_tbl(i).osa_item_id
AND organization_id = l_osa_tbl(i).tp_organization_id ;
SELECT organization_code INTO l_tp_organization
FROM mtl_parameters
WHERE organization_id =l_osa_tbl(i).tp_organization_id ;
SELECT jso.subcontract_po_shipment_id
FROM jmf_subcontract_orders jso
WHERE jso.interlock_status IN ('N', 'U');
SELECT jso.subcontract_po_shipment_id
FROM jmf_subcontract_orders jso
, po_line_locations_all plla
, po_lines_all pla
, po_headers_all pha
WHERE jso.interlock_status IN ('N', 'U')
AND plla.line_location_id = jso.subcontract_po_shipment_id
AND plla.po_line_id = pla.po_line_id
AND plla.po_header_id = pha.po_header_id
AND plla.org_id = p_operating_unit
AND nvl(pha.cancel_flag, 'N') = 'N'
AND nvl(pla.cancel_flag, 'N') = 'N'
AND nvl(plla.cancel_flag, 'N') = 'N'
AND jso.oem_organization_id
BETWEEN
NVL(p_from_organization, jso.oem_organization_id)
AND
NVL(p_to_organization, jso.oem_organization_id)
ORDER BY jso.subcontract_po_shipment_id;
UPDATE jmf_subcontract_orders
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 subcontract_po_shipment_id = l_osa_tbl(i);