[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
WSH_INTERFACE.Update_Shipping_Attributes(
p_source_code => 'OE',
p_changed_attributes => l_changed_attributes,
x_return_status => x_return_status);
oe_debug_pub.add('Adjust_workflow_node: ' || 'CTO:Failed in WSH_INTERFACE.update_shipping_attributes :', 1);
oe_debug_pub.add('Adjust_workflow_node: ' || ' Success in update_shipping attributes..', 2);
SELECT 'Y',
inventory_item_id,
header_id,
ship_from_org_id,
source_type_code,
nvl(shipping_interfaced_flag,'N') -- Bug Fix: 4863275
INTO lato_item_flag,
lConfig_id,
lheader_id,
lorg_id,
l_source_type_code,
l_shipping_xfaced_flag -- Bug Fix: 4863275
FROM OE_ORDER_LINES_ALL
WHERE line_id = ato_line_id
--Adding item_type_code = 'INCLUDED' for Sun ER#9793792.
AND ( item_type_code = 'STANDARD' OR item_type_code = 'OPTION' OR item_type_code = 'INCLUDED')
AND line_id = pLineid;
SELECT line_id,
inventory_item_id,
ship_from_org_id,
header_id,
source_type_code,
nvl(shipping_interfaced_flag,'N') -- Bug Fix: 4863275
INTO Lconfig_line_id,
lconfig_id,
lorg_id,
lheader_id,
l_source_type_code,
l_shipping_xfaced_flag -- Bug Fix: 4863275
FROM oe_order_lines_all
WHERE ato_line_id = plineid
AND item_type_code = 'CONFIG';
select option_specific_sourced
into l_option_specific
from mtl_system_items msi
where msi.inventory_item_id = lconfig_id
and rownum = 1;
select 'Y'
into l_valid_ship_from_org
from mrp_sr_assignments assg,
mrp_sr_receipt_org rcv,
mrp_sr_source_org src
where assg.inventory_item_id = lconfig_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
and rcv.effective_date <= sysdate
and nvl(rcv.disable_date,sysdate+1)>sysdate
and rcv.SR_RECEIPT_ID = src.sr_receipt_id
and ( assg.organization_id = pchgtype(i).new_value
or src.source_organization_id = pchgtype(i).new_value)
and rownum =1;
SELECT order_number
INTO lorder_no
FROM oe_order_headers_all
WHERE header_id = lheader_id;
SELECT reservation_id
INTO l_reservation_id
FROM
mtl_reservations mr,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
WHERE
mr.demand_source_line_id = oel.line_id
and oel.line_id = pconfigLineId --- Configuration item line id
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code= 'ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag ='B')
and mso.sales_order_id = mr.demand_source_header_id
--and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10, INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
and mr.reservation_quantity > 0
and rownum = 1;
SELECT FU.user_name
FROM MTL_SYSTEM_ITEMS MTI,
PO_BUYERS_ALL_V PBAV,
FND_USER FU
WHERE MTI.inventory_item_id = inv_item_id
AND MTI.organization_id = ship_org_id
AND MTI.buyer_id = PBAV.employee_id
AND PBAV.employee_id = FU.employee_id(+) --outer join b'cos employee need not be an fnd user.
ORDER BY FU.user_name asc;
SELECT u.user_name
FROM mtl_system_items_vl item,
mtl_planners p,
fnd_user u
WHERE item.inventory_item_id = inv_item_id
and item.organization_id = ship_org_id
and p.organization_id = item.organization_id
and p.planner_code = item.planner_code
and p.employee_id = u.employee_id(+)
ORDER BY u.user_name asc;
select u.user_name
from mtl_reservations mr,
po_headers_all poh,
oe_order_lines_all oel,
fnd_user u
where oel.line_id = config_line_id and
mr.demand_source_type_id in (8,2) and
mr.demand_source_line_id = oel.line_id and
mr.supply_source_type_id = 1 and
mr.supply_source_header_id = poh.po_header_id and
poh.agent_id = u.employee_id
ORDER BY u.user_name asc;
select u.user_name
from mtl_reservations mr,
po_requisition_headers_all porh,
po_requisition_lines_all porl,
oe_order_lines_all oel,
fnd_user u
where oel.line_id = config_line_id and
mr.demand_source_type_id in (8,2) and
mr.demand_source_line_id = oel.line_id and
mr.supply_source_type_id = 17 and
mr.supply_source_header_id = porh.requisition_header_id and
porh.requisition_header_id = porl.requisition_header_id and
mr.supply_source_line_id = porl.requisition_line_id and
porl.suggested_buyer_id = u.employee_id
ORDER BY u.user_name asc;
select line_id
from oe_order_lines_all
where inventory_item_id = pconfig_id and
header_id = p_header_id and
ato_line_id = l_ato_line_id;
SELECT mlk2.meaning supply_type,
we.wip_entity_name job_number,
NULL line_number
FROM mtl_reservations mr,
wip_discrete_jobs wdj,
wip_entities we,
mfg_lookups mlk,
mfg_lookups mlk2,
oe_order_lines_all oel
WHERE oel.line_id = p_line_id
AND demand_source_type_id = DECODE(CTO_WORKBENCH_UTIL_PK.get_source_document_id(p_line_id), 10,8,2)
AND oel.line_id = mr.demand_source_line_id
AND mr.supply_source_type_id = 5
AND wdj.wip_entity_id = mr.supply_source_header_id
AND wdj.organization_id = mr.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND mlk.lookup_type = 'WIP_JOB_STATUS'
AND mlk.lookup_code = wdj.status_type
AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
AND mlk2.lookup_code = 1
UNION
SELECT mlk2.meaning supply_type,
wfs.schedule_number job_number,
NULL line_number
FROM wip_flow_schedules wfs,
oe_order_lines_all oel,
mtl_system_items msi,
mfg_lookups mlk,
mfg_lookups mlk2
WHERE wfs.demand_source_line = p_line_id
AND oel.line_id = p_line_id
AND mlk.lookup_type = 'WIP_FLOW_SCHEDULE_STATUS'
AND mlk.lookup_code = wfs.status
AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
AND mlk2.lookup_code = 2
AND wfs.status = 1
AND msi.inventory_item_id = oel.inventory_item_id
AND msi.organization_id = oel.ship_from_org_id
UNION
SELECT mlk2.meaning supply_type,
poh.segment1 job_number,
pol.line_num line_number
FROM mtl_reservations mr,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
oe_order_lines_all oel,
mtl_units_of_measure uom,
mfg_lookups mlk2
WHERE mr.demand_source_type_id IN(8,2)
AND mr.demand_source_line_id = oel.line_id
AND mr.supply_source_type_id = 1
AND mr.supply_source_header_id = poh.po_header_id
AND poh.po_header_id = poll.po_header_id
AND mr.supply_source_line_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.unit_meas_lookup_code = uom.unit_of_measure
AND oel.line_id = p_line_id
AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
AND mlk2.lookup_code = 3
UNION
SELECT mlk2.meaning Supply_type,
porh.segment1 job_number,
porl.line_num line_number
FROM mtl_reservations mr,
po_requisition_headers_all porh,
po_requisition_lines_all porl,
oe_order_lines_all oel,
mtl_units_of_measure uom,
mfg_lookups mlk2
WHERE oel.line_id = p_line_id
AND mr.demand_source_type_id IN (8,2)
AND mr.demand_source_line_id = oel.line_id
AND mr.supply_source_type_id IN (7, 17)
AND mr.supply_source_header_id = porh.requisition_header_id
AND porh.requisition_header_id = porl.requisition_header_id
AND mr.supply_source_line_id = porl.requisition_line_id
AND porl.unit_meas_lookup_code = uom.unit_of_measure
AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
AND mlk2.lookup_code = DECODE(mr.supply_source_type_id, 17, 4, 7, 8)
UNION
SELECT mlk2.meaning Supply_type,
poh.segment1 job_number,
pol.line_num line_number
FROM oe_drop_ship_sources ods,
po_headers_all poh,
po_line_locations_all poll,
po_lines_all pol,
oe_order_lines_all oel,
mtl_units_of_measure uom,
mfg_lookups mlk2
WHERE oel.line_id = p_line_id
AND ods.line_id = oel.line_id
AND ods.po_header_id = poh.po_header_id
AND poh.po_header_id = poll.po_header_id
AND ods.line_location_id = poll.line_location_id
AND ods.po_line_id = pol.po_line_id
AND pol.unit_meas_lookup_code = uom.unit_of_measure
AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
AND mlk2.lookup_code = 5
UNION
SELECT mlk2.meaning Supply_Type,
porh.segment1 job_number,
porl.line_num line_number
FROM oe_drop_ship_sources ods,
po_requisition_headers_all porh,
po_requisition_lines_all porl,
oe_order_lines_all oel,
mtl_units_of_measure uom,
mfg_lookups mlk2
WHERE oel.line_id = p_line_id
AND ods.line_id = oel.line_id
AND ods.po_header_id IS NULL
AND ods.requisition_header_id = porh.requisition_header_id
AND porh.requisition_header_id = porl.requisition_header_id
AND ods.requisition_line_id = porl.requisition_line_id
AND porl.unit_meas_lookup_code = uom.unit_of_measure
AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
AND mlk2.lookup_code = 6
UNION
SELECT mlk2.meaning supply_type,
asn_headers.shipment_num job_number,
NULL line_number
FROM mtl_reservations mr,
oe_order_lines_all oel,
mtl_units_of_measure uom,
mfg_lookups mlk2,
rcv_shipment_lines ASN_LINES,
rcv_shipment_headers ASN_HEADERS
WHERE mr.demand_source_type_id IN (8,2)
AND mr.demand_source_line_id = oel.line_id
AND mr.supply_source_type_id = 25
AND mr.supply_source_line_detail = ASN_LINES.shipment_line_id
AND ASN_LINES.unit_of_measure = uom.unit_of_measure
AND ASN_LINES.shipment_header_id = ASN_HEADERS.shipment_header_id
AND oel.line_id = p_line_id
AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
AND mlk2.lookup_code = 9;
SELECT
schedule_ship_date,
schedule_arrival_date,
ordered_quantity,
ordered_quantity2,--secondary qty for OPM 05/27/2004
line_number||decode(shipment_number,NULL,'','.'||shipment_number)||
decode(option_number,NULL,'','.'||option_number),
request_date,
inventory_item_id,
ship_from_org_id,
header_id,
ordered_quantity_uom2, --secondary ordered UOM for OPM proj
order_quantity_uom, --primary ordered UOM
ato_line_id -- bug 14209094
INTO
lssd_date,
lsad_date,
lord_qty,
lord_qty2, --ordered_quantity2 for OPM proj
lcust_line_no,
lreq_date,
linv_item_id,
lship_org_id,
l_header_id,
l_ord_uom2, --ORDERED_QUANTITY_UOM2 for OPM proj
l_ord_uom, --order_quantity_uom for OPM proj
l_ato_line_id -- bug 14209094
FROM
oe_order_lines_all
WHERE
line_id = pline_no;
SELECT concatenated_segments
INTO litem_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = pconfig_id
AND organization_id = lship_org_id;
SELECT
substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME
into lcustomer_name
FROM HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
oe_order_headers_all oeh
WHERE CUST_ACCT.CUST_ACCOUNT_ID = oeh.sold_to_org_id
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND oeh.header_id = l_header_id;
/*SELECT u.user_name
INTO lplanner_code
FROM MTL_SYSTEM_ITEMS A,
PO_BUYERS_ALL_V B,
FND_USER U
WHERE a.inventory_item_id = linv_item_id
AND a.organization_id = lship_org_id
AND a.buyer_id = b.employee_id
AND b.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
/*SELECT u.user_name
INTO lplanner_code
FROM mtl_system_items_vl item
,mtl_planners p
,fnd_user u
WHERE item.inventory_item_id = linv_item_id
and item.organization_id = lship_org_id
and p.organization_id = item.organization_id
and p.planner_code = item.planner_code
and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
select oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' ) , ordered_quantity
into v_model_line_num , v_split_qty
from oe_order_lines_all oel
where line_id = v_split_line_id ;
select ordered_quantity into v_split_qty from bom_cto_order_lines
where line_id = v_split_line_id ;
select organization_name into v_old_org
from inv_organization_name_v
where organization_id = pchgtype(i).old_value ;
select organization_name into v_new_org
from inv_organization_name_v
where organization_id = pchgtype(i).new_value ;
SELECT bp.ENABLE_LOWER_LEVEL_SUPPLY
INTO l_mlsupply_parameter
FROM bom_parameters bp
WHERE bp.organization_id = lship_org_id;
The logic for the followin select is as follows. Scan the bill of material
for the given item in the given org. If we find atleast one config item as its
child then it is a Multi level configuration.
*/
l_stmt_no := 20;
SELECT 'Y'
INTO x_result
FROM BOM_BILL_OF_MATERIALS BOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS MTL
WHERE BOM.Assembly_item_id = pInventory_item_id
AND BOM.Organization_id = pOrg_id
AND BOM.Bill_sequence_id = BIC.Bill_sequence_id
AND BIC.Bom_item_type = 4 --- Standard item
AND BIC.WIP_SUPPLY_TYPE <> 6 --- Non Phantom
AND MTL.Inventory_item_id = BIC.Component_item_id
AND MTL.Organization_id = pOrg_id
AND MTL.Base_item_id Is Not Null -- This condition tells this is a config item.
AND rownum = 1;
SELECT Ato_line_id ,
item_type_code /* BUG#1874380 */
INTO l_ato_line_iD,
l_item_type_code /* BUG#1874380 */
FROM OE_ORDER_LINES_ALL
WHERE line_id = Pchgtype(i).old_value;
Select 'Y' into l_req_exists
From po_requisitions_interface_all
Where interface_source_line_id = p_line_id
and item_id = p_item_id
and process_flag is null;
Procedure delete_from_req_interface(p_line_id IN Number,
p_item_id IN Number,
x_return_status OUT NOCOPY varchar2) is
Begin
x_return_status := FND_API.G_RET_STS_SUCCESS;
Delete from po_requisitions_interface_all
Where interface_source_line_id = p_line_id
and item_id = p_item_id;
oe_debug_pub.add('delete_from_req_interface: ' || 'Processed interface record deletion. exiting...', 5);
oe_debug_pub.add('delete_from_req_interface: ' || 'expected error: ' || sqlerrm, 1);
oe_debug_pub.add('delete_from_req_interface: ' || 'unexpected error: ' || sqlerrm, 1);
oe_debug_pub.add('delete_from_req_interface: ' || 'When others exception ..' || sqlerrm, 1);
End delete_from_req_interface;
Procedure update_req_interface_rec(p_line_id IN Number,
p_item_id IN Number,
p_qty IN Number default null,
p_qty2 IN Number default null,
p_need_by_date IN date default null,
x_return_status OUT NOCOPY varchar2) is
Begin
x_return_status := FND_API.G_RET_STS_SUCCESS;
oe_debug_pub.add('update_req_interface_rec: ' || 'Values passed in are as follows: ', 5);
oe_debug_pub.add('update_req_interface_rec: ' || 'p_line_id = ' || p_line_id, 5);
oe_debug_pub.add('update_req_interface_rec: ' || 'p_qty = ' || p_qty, 5);
oe_debug_pub.add('update_req_interface_rec: ' || 'p_need_by_date = ' || p_need_by_date, 5);
oe_debug_pub.add('update_req_interface_rec: ' || 'Both qty and date are null. Nothing to update. Simply returning ', 5);
Update po_requisitions_interface_all
Set quantity = nvl(p_qty, quantity),
secondary_quantity = nvl(p_qty2, secondary_quantity), --OPM
need_by_date = nvl(p_need_by_date, need_by_date)
Where interface_source_line_id = p_line_id
and item_id = p_item_id;
oe_debug_pub.add('update_req_interface_rec: ' || 'Processed interface record update. Exiting...', 5);
oe_debug_pub.add('update_req_interface_rec: ' || 'When others exception ..' || sqlerrm, 1);
End update_req_interface_rec;
oe_debug_pub.add('change_order_ato_req_item: ' || 'Interface record does not exist. Nothing to update. Simply returning ', 5);
delete_from_req_interface(p_line_id => p_config_line_id,
p_item_id => p_config_id,
x_return_status => x_return_status);
oe_debug_pub.add('change_order_ato_req_item: ' || 'Either cancel or unschedule or config change. Interface record deleted. Returning ', 5);
delete_from_req_interface(p_line_id => p_config_line_id,
p_item_id => p_config_id,
x_return_status => x_return_status);
oe_debug_pub.add('change_order_ato_req_item: ' || 'No Open demand. Interface record deleted. Returning ', 5);
update_req_interface_rec(p_line_id => p_config_line_id,
p_item_id => p_config_id,
p_qty => l_new_qty,
p_qty2 => l_new_qty2,
p_need_by_date => l_new_date,
x_return_status => x_return_status);
select u.user_name
from mtl_reservations mr,
po_headers_all poh,
oe_order_lines_all oel,
fnd_user u
where oel.line_id = config_line_id and
mr.demand_source_type_id in (8,2) and
mr.demand_source_line_id = oel.line_id and
mr.supply_source_type_id = 1 and
mr.supply_source_header_id = poh.po_header_id and
poh.agent_id = u.employee_id
ORDER BY u.user_name asc;
select u.user_name
from mtl_reservations mr,
po_requisition_headers_all porh,
po_requisition_lines_all porl,
oe_order_lines_all oel,
fnd_user u
where oel.line_id = config_line_id and
mr.demand_source_type_id in (8,2) and
mr.demand_source_line_id = oel.line_id and
mr.supply_source_type_id = 17 and
mr.supply_source_header_id = porh.requisition_header_id and
porh.requisition_header_id = porl.requisition_header_id and
mr.supply_source_line_id = porl.requisition_line_id and
porl.suggested_buyer_id = u.employee_id
ORDER BY u.user_name asc;
SELECT FU.user_name
FROM MTL_SYSTEM_ITEMS MTI,
PO_BUYERS_ALL_V PBAV,
FND_USER FU
WHERE MTI.inventory_item_id = inv_item_id
AND MTI.organization_id = ship_org_id
AND MTI.buyer_id = PBAV.employee_id
AND PBAV.employee_id = FU.employee_id(+) --outer join b'cos employee need not be an fnd user.
ORDER BY FU.user_name asc;
SELECT
inventory_item_id,
ship_from_org_id,
header_id
INTO
linv_item_id,
lship_org_id,
l_header_id
FROM
oe_order_lines_all
WHERE
line_id = pline_no;