[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_TBL;
PROCEDURE sort_line_tbl(p_line_id_tbl IN OE_GLOBALS.Selected_Record_Tbl,
p_version_number IN NUMBER,
p_phase_change_flag IN VARCHAR2,
p_num_lines IN NUMBER,
x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type);
SELECT ORDER_CATEGORY_CODE --SELECT ORDER_CATEGORY_CODE
FROM OE_transaction_TYPES_all --FROM OE_ORDER_TYPES_V
WHERE TRANSACTION_TYPE_ID = p_order_type_in; --WHERE ORDER_TYPE_ID = p_order_type_in;
SELECT ORDER_CATEGORY_CODE --SELECT ORDER_CATEGORY_CODE
FROM OE_transaction_TYPES_all --FROM OE_LINE_TYPES_V
WHERE TRANSACTION_TYPE_ID = p_line_type_in; --WHERE LINE_TYPE_ID = p_line_type_in;
SELECT default_inbound_line_type_id
INTO l_line_type_id
FROM oe_transaction_types_v
WHERE transaction_type_id = p_order_type_id;
SELECT default_outbound_line_type_id
INTO l_line_type_id
FROM oe_transaction_types_v
WHERE transaction_type_id = p_order_type_id;
SELECT name
INTO l_line_type
FROM Oe_line_types_v
WHERE line_type_id = l_line_type_id;
SELECT order_number,
version_number,
order_type_id
FROM OE_ORDER_HEADERS
WHERE header_id = p_header_in;
x_header_rec.last_update_date := FND_API.G_MISS_DATE;
x_header_rec.last_updated_by := FND_API.G_MISS_NUM;
x_header_rec.last_update_login := FND_API.G_MISS_NUM;
x_header_rec.program_update_date := FND_API.G_MISS_DATE;
SELECT 'Y'
INTO l_payment_exists
FROM oe_payments
WHERE header_id = p_header_id
AND line_id is null -- bug 5167945
AND PAYMENT_COLLECTION_EVENT = 'INVOICE';
x_Header_Scredit_tbl.delete; -- 1724939
SELECT version_number
INTO l_v_number
FROM oe_order_headers_all /*Modified to base table for bug 9701486 */
WHERE header_id = p_header_id;
x_Header_Payment_tbl.delete;
Select customer_id
Into l_invoice_to_cust_id
From Oe_invoice_to_orgs_v
Where organization_id = oe_order_cache.g_header_rec.invoice_to_org_id;
Select invoice_to_org_id
into l_invoice_to_org_id
from oe_order_headers_all
where header_id = p_header_id;
l_line_tbl.DELETE(k);
G_Canceled_Line_Deleted := TRUE;
l_line_tbl.DELETE(k);
-- passed in. It gets deleted if parent is passed in since
-- Process Order will re-exploded based on Freeze date.
IF l_line_tbl(k).item_type_code IN
(OE_GLOBALS.G_ITEM_CONFIG,
OE_GLOBALS.G_ITEM_INCLUDED,
OE_GLOBALS.G_ITEM_OPTION) THEN
-- Find if Parent Line has been passed In
IF l_line_tbl(k).top_model_line_id IS NOT NULL THEN
l_top_model_index := Find_LineIndex(l_line_tbl,
l_line_tbl(k).top_model_line_id);
l_line_tbl.DELETE(k);
-- Delete class lines.
IF l_line_tbl(k).item_type_code = OE_GLOBALS.G_ITEM_CLASS THEN
l_line_tbl.DELETE(k);
-- Delete service lines.
IF l_line_tbl(k).item_type_code = OE_GLOBALS.G_ITEM_SERVICE THEN
l_line_tbl.DELETE(k);
l_line_tbl.DELETE(k);
l_line_tbl.DELETE(k);
l_line_tbl.DELETE(k);
l_line_tbl.DELETE(k);
l_line_tbl.DELETE(k);
G_Canceled_Line_Deleted := TRUE;
l_line_tbl.DELETE(k);
-- Delete options and class if remnant flag is true.
IF l_line_tbl(k).model_remnant_flag = 'Y' THEN
l_line_tbl.DELETE(k);
-- passed in. It gets deleted if parent is passed in since
-- Process Order will re-exploded based on Freeze date.
IF l_line_tbl(k).item_type_code IN (OE_GLOBALS.G_ITEM_CONFIG,
OE_GLOBALS.G_ITEM_INCLUDED) THEN
-- Find if Link to Line has been passed in.
IF l_line_tbl(k).link_to_line_id IS NOT NULL THEN
l_link_to_index := Find_LineIndex(l_line_tbl,l_line_tbl(k).link_to_line_id);
l_line_tbl.DELETE(k);
-- else it gets deleted(Classes cannot be conveted to STANDARD)
IF l_line_tbl(k).item_type_code = (OE_GLOBALS.G_ITEM_CLASS) THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CLASS 1' || TO_CHAR ( L_LINE_TBL ( K ) .LINE_ID ) , 1 ) ;
-- Delete when top model is not passed in.
l_line_tbl.DELETE(K);
l_line_tbl.DELETE(K);
l_line_tbl.delete(k);
l_line_tbl.DELETE(K);
,p_line_id_tbl IN OE_GLOBALS.Selected_Record_Tbl
,p_all_lines IN VARCHAR2
,p_incl_cancelled IN VARCHAR2
,p_header_id IN NUMBER
,p_hdr_type_id IN NUMBER
,p_line_type_id IN NUMBER
,p_version_number IN NUMBER
,p_phase_change_flag IN VARCHAR2
,x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
/* Added the following line to fix the bug 1923460 */
,x_top_model_tbl IN OUT NOCOPY Top_Model_Tbl_Type
)
IS
l_top_model_index NUMBER;
l_handle_deleted_flag VARCHAR2(1) :=NULL;
:handle_deleted_flag,
:new_name);
IN l_handle_deleted_flag,
IN l_new_name ;
DELETE_TBL;
p_x_line_tbl(k).last_update_date := FND_API.G_MISS_DATE;
p_x_line_tbl(k).last_updated_by := FND_API.G_MISS_NUM;
p_x_line_tbl(k).last_update_login := FND_API.G_MISS_NUM;
p_x_line_tbl(k).program_update_date := FND_API.G_MISS_DATE;
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO p_x_line_tbl(k).line_id
FROM dual;
SELECT OE_SETS_S.NEXTVAL
INTO l_line_set_tbl(x).line_set_id
FROM DUAL;
p_x_line_tbl(k).Auto_Selected_quantity := FND_API.G_MISS_NUM;
l_sub_scredit_tbl.DELETE;
Select oit.customer_id, ooh.invoice_to_org_id
Into l_invoice_to_cust_id, l_invoice_to_org_id
From oe_order_lines_all ooh,Oe_invoice_to_orgs_v oit
Where ooh.line_id = x_line_payment_tbl(I).line_id
And oit.organization_id = ooh.invoice_to_org_id;
l_sub_payment_tbl.DELETE;
Procedure delete_config(
p_top_model_tbl top_model_tbl_type)
IS
k NUMBER; -- Used as Index for loop.
oe_debug_pub.add( 'ENTERING OE_ORDER_COPY_UTIL.DELETE_CONFIG ' , 1 ) ;
oe_config_pvt.delete_config(
p_top_model_tbl(k).config_header_id,
p_top_model_tbl(k).config_rev_nbr,
l_return_status );
oe_debug_pub.add( 'EXITING OE_ORDER_COPY_UTIL.DELETE_CONFIG' , 1 ) ;
END delete_config;
INSERT INTO oe_payments
(trxn_extension_id,
payment_level_code,
header_id,
line_id,
payment_type_code,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(g_trxn_extension_id,
'ORDER',
p_header_id,
null, -- line id
g_payment_type_code,
sysdate,
1,
sysdate,
1);
,p_hdr_id_tbl IN OE_GLOBALS.Selected_Record_Tbl
,p_line_id_tbl IN OE_GLOBALS.Selected_Record_Tbl
,x_header_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_delete_config BOOLEAN;
l_hdr_id_sets_tbl OE_GLOBALS.Selected_Record_Tbl;
G_Canceled_Line_Deleted := FALSE;
Select header_id
Into l_line_header_id
From oe_order_lines
Where line_id = l_line_id;
select payment_type_code into l_payment_type_code
from oe_order_headers_all where header_id = l_header_id;
SELECT OE_ORDER_HEADERS_S.NEXTVAL
INTO l_header_rec.header_id
FROM DUAL;
SELECT 'Y'
INTO l_exists
FROM oe_payments
Where header_id = l_header_id;
Select customer_id
Into l_invoice_to_cust_id
From oe_invoice_to_orgs_v
Where organization_id = l_header_rec.invoice_to_org_id;
l_line_tbl.DELETE;
l_top_model_tbl.DELETE;
select count(*) into l_cc_line_payments
from oe_payments where header_id = l_header_id
and line_id is not null and payment_type_code = 'CREDIT_CARD';
l_delete_config := FALSE;
SELECT header_id
INTO l_dummy_header
FROM OE_ORDER_HEADERS
WHERE header_id = l_header_rec.header_id;
l_delete_config := TRUE;
NOT G_Canceled_Line_Deleted AND
NOT G_Order_Has_Split_Lines) OR
G_REGULAR_TO_RMA OR
OE_CODE_CONTROL.Get_Code_Release_Level < '110509' THEN
-- Do nothing as the Line numbers will match
NULL;
IF( l_delete_config ) THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' DELETE CONFIG : TRUE' , 5 ) ;
oe_debug_pub.add( ' DELETE CONFIG : FALSE' , 5 ) ;
IF ( l_delete_config ) THEN
delete_config(l_top_model_tbl);
l_line_tbl.DELETE(j);
PROCEDURE DELETE_TBL
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
G_Line_Num_Rec.line_id.DELETE;
G_Line_Num_Rec.new_line_id.DELETE;
G_Line_Num_Rec.line_number.DELETE;
G_Line_Num_Rec.shipment_number.DELETE;
G_Line_Num_Rec.option_number.DELETE;
G_Line_Num_Rec.component_number.DELETE;
G_Line_Num_Rec.service_number.DELETE;
G_Line_Num_Rec.split_from_line_id.DELETE;
G_Line_Num_Rec.split_by.DELETE;
END DELETE_TBL;
SELECT line_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
split_from_line_id,
source_document_Line_id,
source_document_type_id,
split_by,
line_set_id,
item_type_code,
service_reference_line_id,
link_to_line_id,
top_model_line_id --9534576
FROM oe_order_lines
WHERE header_id = p_header_id
ORDER BY line_number , shipment_number ,NVL(option_number, -1),
NVL(component_number,-1),NVL(service_number,-1);
SELECT OE_SETS_S.NEXTVAL
INTO l_copied_rec.line_set_id(m)
FROM DUAL;
oe_debug_pub.add( 'UPDATE ORDER LINES IN BULK' , 1 ) ;
UPDATE OE_ORDER_LINES
SET line_number = l_copied_rec.line_number(i)
, shipment_number = l_copied_rec.shipment_number(i)
, option_number = l_copied_rec.option_number(i)
, component_number = l_copied_rec.component_number(i)
, service_number = l_copied_rec.service_number(i)
, split_from_line_id = l_copied_rec.split_from_line_id(i)
, split_by = l_copied_rec.split_by(i)
, line_set_id = l_copied_rec.line_set_id(i)
WHERE line_id = l_copied_rec.line_id(i);
oe_debug_pub.add( ' AFTER UPDATE ORDER LINES IN BULK' , 1 ) ;
l_copied_rec.line_id.delete;
l_copied_rec.line_number.delete;
l_copied_rec.shipment_number.delete;
l_copied_rec.option_number.delete;
l_copied_rec.component_number.delete;
l_copied_rec.service_number.delete;
l_copied_rec.split_from_line_id.delete;
l_copied_rec.source_document_Line_id.delete;
l_copied_rec.source_document_type_id.delete;
l_copied_rec.split_by.delete;
l_copied_rec.line_set_id.delete;
INSERT INTO OE_SETS(
SET_ID
, SET_NAME
, SET_TYPE
, Header_Id
, inventory_item_id
, ordered_quantity_uom
, line_type_id
, Ship_tolerance_above
, ship_tolerance_below
, CREATED_BY
, CREATION_DATE
, UPDATE_DATE
, UPDATED_BY
)
SELECT
p_line_set_id
, to_char(p_line_id)
, 'SPLIT'
, p_header_id
, l.inventory_item_id
, l.order_quantity_uom
, p_line_type_id
, l.Ship_tolerance_above
, l.ship_tolerance_below
, FND_GLOBAL.USER_ID
, sysdate
, sysdate
, 1001
FROM OE_ORDER_LINES l
WHERE line_id = p_src_line_id;
PROCEDURE sort_line_tbl(p_line_id_tbl IN OE_GLOBALS.Selected_Record_Tbl,
p_version_number IN NUMBER,
p_phase_change_flag IN VARCHAR2,
p_num_lines IN NUMBER,
x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type)
IS
i binary_integer := 1;
SELECT line_id
FROM OE_COPY_LINE_SORT_TMP
WHERE top_model_line_id = line_id;
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
phase_change_flag,
top_model_line_id,
item_type_code
)
SELECT
l_line_id(i),
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
'Y',
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines_history l
WHERE l.line_id = l_line_id(i)
--and l.version_number = NVL(p_version_number,l.version_number) -- bug 7443507
and l.version_number = p_version_number -- bug 7443507
and l.phase_change_flag = 'Y';
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
phase_change_flag,
top_model_line_id,
item_type_code
)
SELECT
l_line_id(i),
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
'Y',
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines_history l
WHERE l.line_id = l_line_id(i)
and l.phase_change_flag = 'Y';
DELETE FROM OE_COPY_LINE_SORT_TMP a
WHERE a.top_model_line_id is NOT NULL
AND a.line_id <> a.top_model_line_id
AND EXISTS (select b.line_id
from OE_COPY_LINE_SORT_TMP b
WHERE b.line_id = a.top_model_line_id);
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
phase_change_flag,
top_model_line_id,
item_type_code
)
SELECT
l.line_id,
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
'Y',
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines_history l,
oe_copy_line_sort_tmp c
WHERE c.line_id = c.top_model_line_id
and l.top_model_line_id = c.line_id
--and l.line_id <> l.top_model_line_id -- bug 7443507
and l.item_type_code not in ('CONFIG','INCLUDED','MODEL','KIT') -- bug 7443507
--and l.version_number = NVL(p_version_number,l.version_number) -- bug 7443507
and l.version_number = p_version_number -- bug 7443507
and l.phase_change_flag = 'Y';
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
phase_change_flag,
top_model_line_id,
item_type_code
)
SELECT
l.line_id,
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
'Y',
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines_history l,
oe_copy_line_sort_tmp c
WHERE c.line_id = c.top_model_line_id
and l.top_model_line_id = c.line_id
and l.item_type_code not in ('CONFIG','INCLUDED','MODEL','KIT')
and l.phase_change_flag = 'Y';
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
top_model_line_id,
item_type_code
)
SELECT
l_line_id(i),
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines l,
oe_order_headers h
WHERE l.line_id = l_line_id(i)
and l.header_id = h.header_id
--and h.version_number = NVL(p_version_number,h.version_number) -- bug 7443507
and h.version_number = p_version_number -- bug 7443507
UNION
SELECT
l_line_id(i),
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines_history l
WHERE l.line_id = l_line_id(i)
--and l.version_number = NVL(p_version_number,-1) --bug 7443507
and l.version_number = p_version_number -- bug 7443507
and l.version_flag = 'Y';
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
top_model_line_id,
item_type_code
)
SELECT
l_line_id(i),
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines l,
oe_order_headers h
WHERE l.line_id = l_line_id(i)
and l.header_id = h.header_id;
DELETE FROM OE_COPY_LINE_SORT_TMP a
WHERE a.top_model_line_id is NOT NULL
AND a.line_id <> a.top_model_line_id
AND EXISTS (select b.line_id
from OE_COPY_LINE_SORT_TMP b
WHERE b.line_id = a.top_model_line_id);
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
top_model_line_id,
item_type_code
)
SELECT
l.line_id,
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines l,
oe_order_headers h,
oe_copy_line_sort_tmp c
WHERE c.line_id = c.top_model_line_id
and l.top_model_line_id = c.line_id
--and l.line_id <> l.top_model_line_id --bug 7443507
and l.item_type_code not in ('CONFIG','INCLUDED','MODEL','KIT') --bug 7443507
and l.header_id = h.header_id
--and h.version_number = NVL(p_version_number,h.version_number) --bug 7443507
and h.version_number = p_version_number --bug 7443507
UNION
SELECT
l.line_id,
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines_history l,
oe_copy_line_sort_tmp c
WHERE c.line_id = c.top_model_line_id
and l.top_model_line_id = c.line_id
--and l.line_id <> l.top_model_line_id -- bug 7443507
and l.item_type_code not in ('CONFIG','INCLUDED','MODEL','KIT') -- bug 7443507
--and l.version_number = NVL(p_version_number,-1) -- bug 7443507
and l.version_number = p_version_number -- bug 7443507
and l.version_flag = 'Y';
INSERT INTO OE_COPY_LINE_SORT_TMP(
line_id,
version_number,
header_id,
line_number,
shipment_number,
option_number,
component_number,
service_number,
top_model_line_id,
item_type_code
)
SELECT
l.line_id,
p_version_number,
l.header_id,
l.line_number,
l.shipment_number,
l.option_number,
l.component_number,
l.service_number,
l.top_model_line_id,
l.item_type_code
FROM
oe_order_lines l,
oe_order_headers h,
oe_copy_line_sort_tmp c
WHERE c.line_id = c.top_model_line_id
and l.top_model_line_id = c.line_id
and l.item_type_code not in ('CONFIG','INCLUDED','MODEL','KIT')
and l.header_id = h.header_id;
l_line_id.DELETE;
SELECT line_id
BULK COLLECT INTO
l_line_id
FROM OE_COPY_LINE_SORT_TMP
ORDER BY header_id, line_number, shipment_number, NVL(option_number, -1),
NVL(component_number,-1),NVL(service_number,-1);
l_selected_line_tbl OE_GLOBALS.Selected_Record_Tbl;
SELECT distinct set_id, set_name,set_type
FROM oe_sets
WHERE header_id = p_old_header_id
AND set_type = stype
ORDER BY set_id;
SELECT l.line_id
FROM oe_order_lines_all l
WHERE l.ship_set_id = p_set_id;
SELECT l.line_id
FROM oe_order_lines_all l
WHERE l.arrival_set_id = p_set_id;
SELECT l.line_id
FROM oe_order_lines_all l,
oe_line_sets ols
WHERE l.line_id = ols.line_id
AND l.header_id = p_old_header_id
AND ols.set_id = p_set_id;
l_selected_line_tbl.DELETE;
l_selected_line_tbl(I).id1 := p_line_tbl(lindex).line_id;
IF l_selected_line_tbl.COUNT > 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Line Table Ready. Processing this set:'||set_rec.set_name);
( p_selected_line_tbl => l_selected_line_tbl,
p_record_count => l_selected_line_tbl.COUNT,
p_set_name => set_rec.set_name,
p_set_type => 'FULFILLMENT',
p_operation => 'ADD' ,
p_header_id => p_new_header_id,
x_Set_Id => l_set_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
l_selected_line_tbl.DELETE;
l_selected_line_tbl(I).id1 := p_line_tbl(lindex).line_id;
IF l_selected_line_tbl.COUNT > 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Line Table Ready. Processing this set:'||set_rec.set_name);
( p_selected_line_tbl => l_selected_line_tbl,
p_record_count => l_selected_line_tbl.COUNT,
p_set_name => set_rec.set_name,
p_set_type => 'SHIP',
p_operation => 'ADD' ,
p_header_id => p_new_header_id,
x_Set_Id => l_set_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
l_selected_line_tbl.delete;
l_selected_line_tbl(I).id1 := p_line_tbl(lindex).line_id;
IF l_selected_line_tbl.COUNT > 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Line Table Ready. Processing this set:'||set_rec.set_name);
( p_selected_line_tbl => l_selected_line_tbl,
p_record_count => l_selected_line_tbl.COUNT,
p_set_name => set_rec.set_name,
p_set_type => 'ARRIVAL',
p_operation => 'ADD' ,
p_header_id => p_new_header_id,
x_Set_Id => l_set_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);