[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ELSIF p_attr_id = OE_Line_Util.G_AUTO_SELECTED_QUANTITY THEN
x_line_tbl(1).auto_selected_quantity := TO_NUMBER(p_attr_value);
x_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
ELSIF p_attr_id = OE_Line_Util.G_AUTO_SELECTED_QUANTITY THEN
x_line_tbl(1).auto_selected_quantity := TO_NUMBER(p_attr_value);
x_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
l_x_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
(p_request_type =>OE_GLOBALS.G_UPDATE_COMMITMENT_APPLIED
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
x_line_val_rec.x_last_update_date := l_x_line_rec.last_update_date;
x_line_val_rec.x_last_updated_by := l_x_line_rec.last_updated_by;
x_line_val_rec.x_last_update_login := l_x_line_rec.last_update_login;
OE_GLOBALS.G_START_LINE_FLOWS_TBL.delete(l_last_index);
OE_GLOBALS.G_START_LINE_FLOWS_TBL.delete(l_last_index);
OE_GLOBALS.G_START_LINE_FLOWS_TBL.delete(l_last_index);
PROCEDURE Delete_Row
( x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_line_id IN NUMBER
, p_change_reason_code IN VARCHAR2 Default Null
, p_change_comments IN VARCHAR2 Default Null
)
IS
l_x_line_rec OE_Order_PUB.Line_Rec_Type;
SAVEPOINT LINE_DELETE;
oe_debug_pub.add( 'ENTERING OE_OE_FORM_LINE.DELETE_ROW' , 1 ) ;
l_x_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add( 'EXITING OE_OE_FORM_LINE.DELETE_ROW' , 1 ) ;
ROLLBACK TO SAVEPOINT Line_Delete;
ROLLBACK TO SAVEPOINT Line_Delete;
ROLLBACK TO SAVEPOINT Line_Delete;
, 'Delete_Row'
);
END Delete_Row;
SELECT meaning,lookup_type
FROM AR_LOOKUPS
WHERE (lookup_code=tax_exempt_reason_code
AND lookup_type='TAX_REASON')
OR (lookup_code=tax_exempt_flag
AND lookup_type='TAX_CONTROL_FLAG')
OR (lookup_code=fob_point_code
AND lookup_type='FOB')
OR (lookup_code=return_reason_code
AND lookup_type='CREDIT_MEMO_REASON');
SELECT meaning,lookup_type
FROM FND_LOOKUP_VALUES LV
WHERE LANGUAGE = userenv('LANG')
and VIEW_APPLICATION_ID = 660
and((lookup_code= shipment_priority_code
and lookup_type='SHIPMENT_PRIORITY')
or (lookup_code=freight_terms_code
and lookup_type='FREIGHT_TERMS'))
and SECURITY_GROUP_ID =fnd_global.Lookup_Security_Group(lv.lookup_type,lv.view_application_id);
SELECT 'Y' hold_exists
FROM oe_order_holds
WHERE line_id = c_line_id
AND released_flag = 'N';
SELECT
ato_line_id
, top_model_line_id
, nvl(ship_model_complete_flag, 'N') smc_flag
, item_type_code
, link_to_line_id
FROM oe_order_lines
WHERE line_id = c_line_id;
SELECT 'Y' hold_exists
FROM oe_order_holds ooh, oe_order_lines ool
where ool.ato_line_id = c_ato_line_id
and ool.top_model_line_id = c_top_model_line_id
and ooh.line_id = ool.line_id
and ooh.released_flag = 'N';
SELECT 'Y' hold_exists
FROM oe_order_holds ooh, oe_order_lines ool
where ool.top_model_line_id = c_top_model_line_id
and ooh.line_id = ool.line_id
and ooh.released_flag = 'N';
SELECT 'Y' hold_exists
FROM oe_order_holds ooh, oe_hold_definitions ohd, oe_hold_sources ohs
where ooh.line_id = c_link_to_line_id
and ooh.released_flag = 'N'
and ohs.hold_source_id = ooh.hold_source_id
and ohs.hold_id = ohd.hold_id
and nvl(ohd.hold_included_items_flag, 'N') = 'Y';
/* Select project_number
into x_line_val_rec.project_number
from pjm_projects_org_v
Where project_id=p_line_rec.project_id;*/
/* Select task_number
Into x_line_val_rec.task_number
From pjm_tasks_v
Where task_id=p_line_rec.task_id;*/
select transactional_curr_code
into x_line_val_rec.transactional_curr_code
from oe_order_headers
where header_id=p_line_rec.header_id;
SELECT 'Y'
INTO l_hold_exists
FROM OE_ORDER_HOLDS
WHERE HEADER_ID = p_line_rec.header_id
AND RELEASED_FLAG = 'N';
SELECT 'Y' hold_exists
INTO l_hold_exists
FROM oe_order_holds ooh, oe_hold_definitions ohd, oe_hold_sources ohs
where ooh.line_id = l_top_model_line_id
and ooh.released_flag = 'N'
and ohs.hold_source_id = ooh.hold_source_id
and ohs.hold_id = ohd.hold_id
and ohd.hold_id = 3;
SELECT 'Y' hold_exists
INTO l_hold_exists
FROM oe_order_holds ooh, oe_hold_definitions ohd, oe_hold_sources ohs
where ooh.line_id = l_top_model_line_id
and ooh.released_flag = 'N'
and ohs.hold_source_id = ooh.hold_source_id
and ohs.hold_id = ohd.hold_id;
SELECT 'Y'
INTO x_line_val_rec.message_exists_flag
FROM OE_PROCESSING_MSGS
WHERE header_id = p_line_rec.header_id
AND line_id = p_line_rec.line_id
AND NVL(message_status_code, '0') <> 'CLOSED' --datafix_begin_end
AND rownum < 2;
select /* MOAC_SQL_CHANGE */ H.order_number,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number
into x_line_val_rec.ref_order_number,
x_line_val_rec.ref_line_number,
x_line_val_rec.ref_shipment_number,
x_line_val_rec.ref_option_number,
x_line_val_rec.ref_component_number
from oe_order_headers_all h,
oe_order_lines_all l
where l.line_id=p_line_rec.reference_line_id
and h.header_id=l.header_id;
select /* MOAC_SQL_CHANGE */ rct.trx_number,
rctl.line_number
into x_line_val_rec.ref_invoice_number,
x_line_val_rec.ref_invoice_line_number
from ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl
where rctl.customer_trx_line_id = p_line_rec.reference_customer_trx_line_id
and rctl.customer_trx_id = rct.customer_trx_id;
select /* MOAC_SQL_CHANGE */ rct.trx_number,
rctl.line_number
into x_line_val_rec.ref_invoice_number,
x_line_val_rec.ref_invoice_line_number
from ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl
where rctl.customer_trx_line_id = p_line_rec.reference_customer_trx_line_id
and rctl.customer_trx_id = rct.customer_trx_id;
select /* MOAC_SQL_CHANGE */ rct.trx_number
into x_line_val_rec.credit_invoice_number
from ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl
where rctl.customer_trx_line_id = p_line_rec.credit_invoice_line_id
and rctl.customer_trx_id = rct.customer_trx_id;
Select Name
INTO x_line_val_rec.salesrep
FROM RA_SALESREPS
WHERE Salesrep_id=p_line_rec.salesrep_id
AND org_id=p_line_rec.org_id;
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = p_line_rec.flow_status_code
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
INTO released_count, total_count
FROM wsh_delivery_details
WHERE source_line_id = p_line_rec.line_id
AND source_code = 'OE'
AND released_status <> 'D';
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED'
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED_PARTIAL'
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = l_status
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT description
--,concatenated_segments
--,concatenated_segments /*Bug 1766327 chhung*/
INTO x_line_val_rec.item_description
--,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item /*bug 1766327 chhung*/
FROM mtl_system_items_vl
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = l_organization_id;
SELECT nvl(citems.customer_item_desc, sitems.description)
,citems.customer_item_number
--,sitems.concatenated_segments /*Bug 1766327 chhung*/
INTO x_line_val_rec.item_description
,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item /*Bug 1766327 chhung*/
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
,mtl_parameters mp -- bug 3918771
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_line_rec.inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_line_rec.ordered_item_id
AND citems.customer_id = p_line_rec.sold_to_org_id
AND cxref.master_organization_id = mp.master_organization_id
AND mp.organization_id = sitems.organization_id ; -- bug 3918771
SELECT description
,p_line_rec.ordered_item
--,concatenated_segments
INTO x_line_val_rec.item_description
,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item
FROM mtl_system_items_vl
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = l_organization_id;
SELECT nvl(items.description, sitems.description)
,items.cross_reference
--,sitems.concatenated_segments /*Bug 1766327 chhung*/
INTO x_line_val_rec.item_description
,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item /*Bug 1766327 chhung*/
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.organization_id = l_organization_id
AND sitems.inventory_item_id = p_line_rec.inventory_item_id
AND items.cross_reference_type = p_line_rec.item_identifier_type
AND items.cross_reference = p_line_rec.ordered_item;
SELECT description
,p_line_rec.ordered_item
--,concatenated_segments
INTO x_line_val_rec.item_description
,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item
FROM mtl_system_items_vl
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = l_organization_id;
SELECT nvl(items.description, sitems.description)
,items.cross_reference
--,sitems.concatenated_segments
INTO x_line_val_rec.item_description
,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item
FROM mtl_cross_reference_types types
,mtl_cross_references items
,mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.organization_id = l_organization_id
AND sitems.inventory_item_id = p_line_rec.inventory_item_id
AND items.cross_reference_type = p_line_rec.item_identifier_type
AND items.cross_reference = p_line_rec.ordered_item
AND items.org_independent_flag = 'Y' ;
SELECT nvl(items.description, sitems.description)
,items.cross_reference
--,sitems.concatenated_segments
INTO x_line_val_rec.item_description
,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item
FROM mtl_cross_reference_types types
,mtl_cross_references items
,mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.organization_id = l_organization_id
AND sitems.inventory_item_id = p_line_rec.inventory_item_id
AND items.cross_reference_type = p_line_rec.item_identifier_type
AND items.cross_reference = p_line_rec.ordered_item
AND items.organization_id = l_organization_id ;
SELECT description
,p_line_rec.ordered_item
--,concatenated_segments
INTO x_line_val_rec.item_description
,x_line_val_rec.ordered_item_dsp
--,x_line_val_rec.inventory_item
FROM mtl_system_items_vl
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = l_organization_id;
Select meaning
INTO x_line_val_rec.shipping_method
FROM oe_ship_methods_v
WHERE lookup_code=p_line_rec.shipping_method_code;
Select meaning
INTO x_line_val_rec.service_reference_type
FROM oe_lookups
WHERE lookup_code=p_line_rec.service_Reference_type_code
and lookup_type = 'SERVICE_REFERENCE_TYPE_CODE';
SELECT unit_of_measure_tl description
INTO x_line_val_rec.service_period_dsp
FROM OKX_UNITS_OF_MEASURE_V
WHERE uom_code = p_line_rec.service_period;
Select description
INTO x_line_val_rec.service_period_dsp
FROM mtl_item_uoms_view
WHERE uom_code =p_line_rec.service_period
and inventory_item_id = p_line_rec.inventory_item_id
and organization_id = l_organization_id;
Select description
INTO x_line_val_rec.freight_carrier
FROM org_freight
WHERE freight_code=p_line_rec.freight_carrier_code
and organization_id = p_line_rec.ship_from_org_id;
select meaning
into x_line_val_rec.source_type
from oe_lookups
where lookup_code=p_line_rec.source_type_code
AND lookup_type='SOURCE_TYPE';
select meaning
into x_line_val_rec.demand_class
from oe_fnd_common_lookups_v
where lookup_code=p_line_rec.demand_class_code
and lookup_type='DEMAND_CLASS';
select location,cust_acct_site_id
into x_line_val_rec.intmed_ship_to,
l_address_id
from hz_cust_site_uses_all
where site_use_id=p_line_rec.intermed_ship_to_org_id;
select loc.address1,loc.address2,loc.address3,loc.address4,
DECODE(loc.city, NULL, NULL,loc.city || ', ') ||
DECODE(loc.state, NULL, loc.province || ', ', loc.state || ', ') || -- 3603600
DECODE(loc.postal_code, NULL, NULL,loc.postal_code || ', ') ||
DECODE(loc.country, NULL, NULL,loc.country)
into x_line_val_rec.intmed_ship_to_address1,
x_line_val_rec.intmed_ship_to_address2,
x_line_val_rec.intmed_ship_to_address3,
x_line_val_rec.intmed_ship_to_address4,
x_line_val_rec.intmed_ship_to_address5
from hz_locations loc,
hz_party_sites ps,
hz_cust_acct_sites cas
where cas.cust_acct_site_id = l_address_id
and cas.party_site_id = ps.party_site_id
and ps.location_id = loc.location_id;
select name
into x_line_val_rec.intmed_ship_to_contact
from oe_contacts_v
where contact_id=p_line_rec.intermed_ship_to_contact_id;
SELECT /* MOAC_SQL_CHANGE */
cust_acct.cust_account_id,
party.party_name,
cust_acct.account_number,
cust_site.location,
location.address1,
location.address2,
location.address3,
location.address4,
DECODE(location.city, NULL, NULL,location.city || ', ')
|| DECODE(location.state, NULL, location.province || ', ', location.state || ', ') --3603600
|| DECODE(location.postal_code, NULL, NULL,location.postal_code || ', ')
|| DECODE(location.country, NULL, NULL,location.country)
INTO
x_line_val_rec.deliver_to_customer_id,
x_line_val_rec.deliver_to_customer_name,
x_line_val_rec.deliver_to_customer_number,
x_line_val_rec.deliver_to,
x_line_val_rec.deliver_to_address1,
x_line_val_rec.deliver_to_address2,
x_line_val_rec.deliver_to_address3,
x_line_val_rec.deliver_to_address4,
x_line_val_rec.deliver_to_address5
FROM
hz_cust_site_uses_all cust_site,
hz_cust_acct_sites_all cust_acct_site,
hz_party_sites party_site,
hz_parties party,
hz_cust_accounts cust_acct,
hz_locations location
WHERE
cust_site.site_use_id=p_line_rec.deliver_to_org_id
and cust_site.site_use_code = 'DELIVER_TO'
and cust_site.cust_acct_site_id = cust_acct_site.cust_acct_site_id
and cust_acct_site.party_site_id = party_site.party_site_id
and party_site.party_id = party.party_id
and cust_acct.cust_account_id = cust_acct_site.cust_account_id
and party_site.location_id = location.location_id;
select name
into x_line_val_rec.deliver_to_contact
from oe_contacts_v
where contact_id=p_line_rec.deliver_to_contact_id;
select name||' : '||revision
into x_line_val_rec.agreement
from oe_agreements
where agreement_id=p_line_rec.agreement_id;
select name
into x_line_val_rec.source_document_type
from oe_order_sources
where order_source_id=p_line_rec.source_document_type_id;
select set_name
into x_line_val_rec.arrival_set
from oe_sets
where set_id=p_line_rec.arrival_set_id;
select set_name
into x_line_val_rec.ship_set
from oe_sets
where set_id=p_line_rec.ship_set_id;
select trx_number
into x_line_val_rec.commitment
from ra_customer_trx
where customer_trx_id=p_line_rec.commitment_id;
select commitment_applied_amount
into x_line_val_rec.commitment_applied_amount
from oe_payments
where payment_trx_id = p_line_rec.payment_commitment_id
and ((line_id = p_line_rec.line_id
and payment_level_code = 'LINE')
OR (header_id = p_line_rec.header_id
and payment_level_code = 'ORDER'));
select tdb.booking_id, bst.name
into x_line_val_rec.booking_id,x_line_val_rec.ota_name
from ota_delegate_bookings tdb,
ota_booking_status_types bst
where tdb.line_id = p_line_rec.line_id
and bst.booking_status_type_id = tdb.booking_status_type_id;
SELECT MEANING
INTO x_line_val_rec.calculate_price_descr
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = p_line_rec.calculate_price_flag
AND LOOKUP_TYPE = 'CALCULATE_PRICE_FLAG';
select /* MOAC_SQL_CHANGE */ header_id into x_line_val_rec.svc_header_id
from oe_order_headers_all oh,oe_order_types_v ot
where order_number=p_line_rec.svc_ref_order_number
and oh.order_type_id=ot.order_type_id
and ot.name=p_line_rec.svc_ref_order_type;
Select name into x_line_val_rec.order_source
from oe_order_sources
where order_source_id=p_line_rec.order_source_id;
Select name into x_line_val_rec.order_source
from oe_order_sources
where order_source_id=2;
Select order_number into x_line_val_rec.order_source_ref
from oe_order_headers
where header_id=p_line_rec.source_document_id;
Select line_number into x_line_val_rec.order_source_line_ref
from oe_order_lines
where line_id=p_line_rec.source_document_line_id;
Select name into x_line_val_rec.order_source
from oe_order_sources
where order_source_id=10;
Select name into x_line_val_rec.order_source
from oe_order_sources
where order_source_id=p_line_rec.source_document_type_id;
SELECT unit_of_measure
INTO l_charge_periodicity
FROM MTL_UNITS_OF_MEASURE_VL
WHERE uom_code = p_line_rec.charge_periodicity_code;
SELECT sum(decode(line_category_code,'RETURN',-1*nvl(unit_selling_price,0),nvl(unit_selling_price,0)))
INTO l_retrobilled_price_diff
FROM oe_order_lines
WHERE order_source_id=27 AND
orig_sys_document_ref=to_char(p_line_rec.header_id) AND
orig_sys_line_ref = to_char(p_line_rec.line_id) AND
retrobill_request_id IS NOT NULL;
SELECT unit_selling_price
INTO l_unit_selling_price
FROM oe_order_lines_all
WHERE line_id=p_line_rec.line_id;
SELECT orig_head.order_number,
orig_lin.line_number,
orig_lin.shipment_number,
orig_lin.option_number,
orig_lin.component_number,
orig_lin.service_number
INTO x_line_val_rec.Retro_Order_Number,
x_line_val_rec.Retro_Line_Number,
x_line_val_rec.Retro_Shipment_Number,
x_line_val_rec.Retro_Option_Number,
x_line_val_rec.Retro_Component_Number,
x_line_val_rec.Retro_Service_Number
FROM oe_order_headers_all orig_head,
oe_order_lines_all orig_lin
WHERE line_id =
(
SELECT orig_sys_line_ref
FROM oe_order_lines_all
WHERE line_id=p_line_rec.line_id
and order_source_id=27) AND
orig_head.header_id=orig_lin.header_id;
select cz.name
into x_line_val_rec.instance_name
from cz_config_details_v cz
where cz.config_hdr_id = p_line_rec.config_header_id
and cz.config_rev_nbr = p_line_rec.config_rev_nbr
and cz.config_item_id = p_line_rec.configuration_id;
select meaning into x_line_val_rec.ib_owner_dsp
from oe_lookups
where
( lookup_type='ITEM_OWNER' OR lookup_type='ONT_INSTALL_BASE') and lookup_code=p_line_rec.ib_owner;
select meaning into x_line_val_rec.ib_current_location_dsp
from oe_lookups
where
( lookup_type='ITEM_CURRENT_LOCATION' OR lookup_type='ONT_INSTALL_BASE')and lookup_code=p_line_rec.ib_current_location;
select meaning into x_line_val_rec.ib_installed_at_location_dsp
from oe_lookups
where
(lookup_type='ITEM_INSTALL_LOCATION' OR lookup_type='ONT_INSTALL_BASE')and lookup_code=p_line_rec.ib_installed_at_location;
SELECT count(*)
INTO l_count
FROM mtl_system_items_vl
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_organization_id
AND description = p_description;
SELECT count(*)
INTO l_count
FROM mtl_system_items_tl t --3751209
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_organization_id
AND description like p_description
AND language= userenv('LANG');
SELECT count(*)
INTO l_count
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND nvl(citems.customer_item_desc, sitems.description) = p_description;
SELECT count(*)
INTO l_count
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND nvl(citems.customer_item_desc, sitems.description) like p_description;
SELECT count(*)
INTO l_count
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item
AND nvl(items.description, sitems.description) = p_description;
SELECT count(*)
INTO l_count
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item
AND nvl(items.description, sitems.description) like p_description;
SELECT count(*)
INTO l_count
FROM mtl_system_items_vl
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_organization_id
AND concatenated_segments = p_inventory_item;
SELECT count(*)
INTO l_count
FROM mtl_system_items_vl
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_organization_id
AND concatenated_segments like p_inventory_item;
SELECT count(*)
INTO l_count
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND sitems.concatenated_segments = p_inventory_item;
SELECT count(*)
INTO l_count
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND sitems.concatenated_segments like p_inventory_item;
SELECT count(*)
INTO l_count
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item
AND sitems.concatenated_segments = p_inventory_item;
SELECT count(*)
INTO l_count
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item
AND sitems.concatenated_segments like p_inventory_item;
SELECT count(*)
INTO l_count
FROM mtl_system_items_vl
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_organization_id
AND concatenated_segments = p_item;
SELECT count(*)
INTO l_count
FROM mtl_system_items_vl
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_organization_id
AND concatenated_segments like p_item;
SELECT count(*)
INTO l_count
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND nvl(citems.customer_item_number, sitems.concatenated_segments) =p_item;
SELECT count(*)
INTO l_count
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND nvl(citems.customer_item_number, sitems.concatenated_segments) like p_item;
SELECT count(*)
INTO l_count
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item
AND nvl(items.cross_reference, sitems.concatenated_segments) = p_item;
SELECT count(*)
INTO l_count
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item
AND nvl(items.cross_reference, sitems.concatenated_segments) like p_item;
l_x_line_tbl(i).operation:= OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl.delete;
/* -- 8706868: No need to call again. First call has updated this.
FOR i in x_line_tbl_type.first .. x_line_tbl_type.last LOOP
l_line_tbl(i):=OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(i).operation:= OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(l_rec_count).operation := OE_GLOBALS.G_OPR_UPDATE;
l_x_Header_Adj_tbl.delete;
l_x_header_price_att_tbl.delete;
l_x_Header_Adj_att_tbl.delete;
l_x_Header_Adj_Assoc_tbl.delete;
l_x_Header_Scredit_tbl.delete;
l_x_Line_Adj_tbl.delete;
l_x_Line_Price_att_tbl.delete;
l_x_Line_Adj_att_tbl.delete;
l_x_Line_Adj_Assoc_tbl.delete;
l_x_Line_Scredit_tbl.delete;
l_x_lot_serial_tbl.delete;
OE_DELAYED_REQUESTS_PVT.Delete_Reqs_for_Deleted_Entity(
p_entity_code => OE_GLOBALS.G_ENTITY_LINE
,p_entity_id => p_line_id
-- Bug 3800577
-- Also delete requests logged by this entity
,p_delete_against => FALSE
,x_return_status => l_return_status);
l_new_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_line_rec => l_new_line_rec,
p_line_id => p_line_id,
p_old_line_rec => l_old_line_rec,
x_index => l_index,
x_return_status => l_return_status);
select count(1) into l_count_to_keep
from oe_order_lines_all
where line_id=p_line_id;
SELECT order_date_type_code
INTO l_order_date_type_code
FROM oe_order_headers
WHERE header_id = p_header_id;
SELECT return_context, return_attribute1, return_attribute2
INTO x_return_context, x_return_attribute1, x_return_attribute2
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT /* MOAC_SQL_CHANGE */ cas.cust_account_id
INTO x_ship_to_customer_id
FROM HZ_CUST_SITE_USES_ALL site,
HZ_CUST_ACCT_SITES_ALL cas
WHERE site.cust_acct_site_id = cas.cust_acct_site_id
AND site.site_use_code=l_site_use_code
AND site.site_use_id=p_ship_to_org_id;
SELECT /* MOAC_SQL_CHANGE */ cas.cust_account_id
INTO x_invoice_to_customer_id
FROM HZ_CUST_SITE_USES_ALL site,
HZ_CUST_ACCT_SITES_ALL cas
WHERE site.cust_acct_site_id = cas.cust_acct_site_id
AND site.site_use_code=l_site_use_code
AND site.site_use_id=p_invoice_to_org_id;
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO x_line_id
FROM DUAL;
SELECT NVL(MAX(LINE_NUMBER)+1,1)
INTO x_line_number
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = p_header_id;
SELECT NVL(MAX(SHIPMENT_NUMBER)+1,1)
INTO x_shipment_number
FROM OE_ORDER_LINES
WHERE HEADER_ID = p_header_id
AND LINE_NUMBER = l_line_number;
SELECT concatenated_segments
INTO x_ordered_item
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_organization_id;
SELECT citems.customer_item_number
INTO x_ordered_item
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id;
PROCEDURE Delete_Adjustments(x_line_id IN NUMBER) IS
l_return_status Varchar2(1);
OE_LINE_ADJ_UTIL.Delete_Row(p_line_id=>x_line_id);
oe_debug_pub.add('delete the request logged for this line too');
oe_delayed_requests_pvt.delete_request(
p_entity_code =>OE_GLOBALS.G_ENTITY_LINE_ADJ,
p_entity_id => x_line_id,
p_request_type => OE_GLOBALS.G_PRICE_ADJ,
x_return_status => l_return_status);
END Delete_Adjustments;
select /* MOAC_SQL_CHANGE */ cust.cust_account_id,
party.party_name,
cust.account_number
INTO x_customer_id,
x_customer_name,
x_customer_number
from
hz_cust_site_uses_all site,
hz_cust_acct_sites_all cas,
hz_cust_accounts cust,
hz_parties party
where site.site_use_code = p_site_use_code
and site_use_id = p_site_use_id
and site.cust_acct_site_id = cas.cust_acct_site_id
and cas.cust_account_id = cust.cust_account_id
and cust.party_id=party.party_id;