[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Select_Purge_Orders
(
p_dummy1 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
p_dummy2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
p_purge_set_id IN NUMBER
)
IS
l_id_list CLOB;
l_Count_Selected NUMBER;
l_selected_ids_tbl SELECTED_IDS_TBL;
Select selected_ids
, Count_Selected
,purge_set_submit_datetime
, orders_per_commit
From OE_PURGE_SETS
Where Purge_set_id = p_purge_set_id;
oe_debug_pub.add('Entering OE_Order_Purge_PVT.Select_Purge_Orders '||p_purge_set_id,1);
,l_Count_Selected
,l_purge_set_submit_datetime
,l_orders_per_commit;
IF nvl(l_count_selected,0) <> 0 THEN
DBMS_LOB.OPEN(l_id_list,DBMS_LOB.LOB_READONLY);
FOR I IN 1..l_count_selected LOOP
l_position := DBMS_LOB.INSTR(l_id_list,l_separator,l_start_from,1);
l_selected_ids_tbl(I) := l_header_id;
oe_debug_pub.add('Selected Ids : '||l_char_id_list);
IF nvl(l_count_selected,0) <> 0 THEN
Select_Ids_Purge
(
p_purge_set_id,
l_selected_ids_tbl,
l_count_selected ,
l_orders_per_commit);
End Select_Purge_Orders;
Procedure Select_Where_Cond_Purge
(
ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, RETCODE OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_organization_id IN NUMBER
, p_purge_set_name IN VARCHAR2
, p_purge_set_description IN VARCHAR2
, p_order_number_low IN NUMBER
, p_order_number_high IN NUMBER
, p_order_type_id IN NUMBER
, p_order_category IN VARCHAR2
, p_customer_id IN NUMBER
, p_ordered_date_low IN VARCHAR2
, p_ordered_date_high IN VARCHAR2
, p_creation_date_low IN VARCHAR2
, p_creation_date_high IN VARCHAR2
, p_dummy IN VARCHAR2 DEFAULT NULL
, p_include_contractual_orders IN VARCHAR2 DEFAULT NULL
)
IS
l_sql_stmt VARCHAR2(4000) := NULL;
l_selected_ids SELECTED_IDS_TBL;
SELECT org_id
FROM OE_ORDER_TYPES_V --MOAC view based on multiple objects
WHERE order_type_id = p_order_type_id;
'SELECT OOH.HEADER_ID,
OOH.ORDER_NUMBER,
OOT.NAME,
OOH.SOLD_TO_ORG_ID,
OOH.PRICE_LIST_ID,
OOH.QUOTE_NUMBER,
OOH.FLOW_STATUS_CODE,
OOH.UPGRADED_FLAG
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_TYPES_V OOT --MOAC view based on multiple obj
WHERE OOH.ORDER_TYPE_ID = OOT.ORDER_TYPE_ID
AND NVL(OOH.TRANSACTION_PHASE_CODE,''F'')<>''N''';
OE_DEBUG_PUB.Add('Inside select_where_cond_quote');
SELECT fnd_date.canonical_to_date(p_ordered_date_low),
fnd_date.canonical_to_date(p_ordered_date_high),
fnd_date.canonical_to_date(p_creation_date_low),
fnd_date.canonical_to_date(p_creation_date_high)
INTO l_ordered_date_low,
l_ordered_date_high,
l_creation_date_low,
l_creation_date_high
FROM DUAL;
SELECT NAME
INTO l_order_type_name
FROM OE_ORDER_TYPES_V --MOAC view based on multiple obj
WHERE ORDER_TYPE_ID = p_order_type_id;
SELECT p.PARTY_NAME
INTO l_customer_name
FROM HZ_CUST_ACCOUNTS c,
HZ_PARTIES p
WHERE c.CUST_ACCOUNT_ID = p_customer_id
AND c.PARTY_ID = p.PARTY_ID;
OE_Order_Purge_PVT.Insert_Purge_Set
(
p_purge_set_name => p_purge_set_name,
p_purge_set_description => p_purge_set_description,
p_purge_set_request_Id => 1,
p_purge_set_submit_datetime => SYSDATE,
p_selected_ids => l_selected_ids,
p_count_selected => 0,
p_where_condition => l_where_condition,
p_created_by => FND_GLOBAL.USER_ID,
p_last_updated_by => FND_GLOBAL.USER_ID,
x_purge_set_id => l_purge_set_id
);
OE_DEBUG_PUB.Add('before update='|| l_purge_set_id);
UPDATE OE_PURGE_SETS
SET purge_processed = 'Y'
WHERE purge_set_id = l_purge_set_id;
OE_DEBUG_PUB.Add('after update='|| to_char(SQL%ROWCOUNT));
End Select_Where_Cond_Purge;
Procedure : Select_Where_Cond_Purge_Quote
Description : Called from Quote Purge Selection concurrent program. Based on
the parameters it will construct the where condition. Call Insert_Purge_Set.
Calls Check_And_Get_Detail for every record satisfying the where condition.
DBMS_SQL Package is being used for building the where condition for optimization.
Change Record : Version 1
-------------------------------------------------------------------------------------------------------------*/
Procedure Select_Where_Cond_Purge_Quote
(
ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,RETCODE OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,p_organization_id IN NUMBER
,p_purge_set_name IN VARCHAR2
,p_purge_set_description IN VARCHAR2
,p_quote_number_low IN NUMBER
,p_quote_number_high IN NUMBER
,p_order_type_id IN NUMBER
,p_customer_id IN NUMBER
,p_quote_date_low IN VARCHAR2
,p_quote_date_high IN VARCHAR2
,p_creation_date_low IN VARCHAR2
,p_creation_date_high IN VARCHAR2
,p_offer_exp_date_low IN VARCHAR2
,p_offer_exp_date_high IN VARCHAR2
,p_purge_exp_quotes IN VARCHAR2
,p_purge_lost_quotes IN VARCHAR2
)
IS
l_sql_stmt VARCHAR2(4000) := NULL;
l_selected_ids SELECTED_IDS_TBL;
SELECT org_id
FROM OE_ORDER_TYPES_V --MOAC view based on multiple objects
WHERE order_type_id = p_order_type_id;
' SELECT OOH.HEADER_ID,
OOH.QUOTE_NUMBER,
OOT.NAME,
OOH.SOLD_TO_ORG_ID,
OOH.PRICE_LIST_ID,
OOH.EXPIRATION_DATE,
OOH.FLOW_STATUS_CODE,
OOH.UPGRADED_FLAG
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_TYPES_V OOT --MOAC view based on multiple objects
WHERE OOH.ORDER_TYPE_ID = OOT.ORDER_TYPE_ID
AND NVL(OOH.TRANSACTION_PHASE_CODE,''F'')=''N''';
OE_DEBUG_PUB.Add('Inside select_where_cond_quote');
SELECT fnd_date.canonical_to_date(p_offer_exp_date_low),
fnd_date.canonical_to_date(p_offer_exp_date_high),
fnd_date.canonical_to_date(p_creation_date_low),
fnd_date.canonical_to_date(p_creation_date_high),
fnd_date.canonical_to_date(p_quote_date_low),
fnd_date.canonical_to_date(p_quote_date_high)
INTO l_offer_exp_date_low,
l_offer_exp_date_high,
l_creation_date_low,
l_creation_date_high,
l_quote_date_low,
l_quote_date_high
FROM DUAL;
SELECT NAME
INTO l_order_type_name
FROM OE_ORDER_TYPES_V --MOAC view based on multiple objects
WHERE ORDER_TYPE_ID = p_order_type_id;
SELECT p.PARTY_NAME
INTO l_customer_name
FROM HZ_CUST_ACCOUNTS c,
HZ_PARTIES p
WHERE c.CUST_ACCOUNT_ID = p_customer_id
AND c.PARTY_ID = p.PARTY_ID;
OE_ORDER_PURGE_PVT.Insert_Purge_Set
(
p_purge_set_name => p_purge_set_name,
p_purge_set_description => p_purge_set_description,
p_purge_set_request_Id => 1,
p_purge_set_submit_datetime => SYSDATE,
p_selected_ids => l_selected_ids,
p_count_selected => 0,
p_where_condition => l_where_condition,
p_created_by => FND_GLOBAL.USER_ID,
p_last_updated_by => FND_GLOBAL.USER_ID,
x_purge_set_id => l_purge_set_id
);
OE_DEBUG_PUB.Add('before update='|| l_purge_set_id);
UPDATE OE_PURGE_SETS
SET purge_processed = 'Y'
WHERE purge_set_id = l_purge_set_id;
OE_DEBUG_PUB.Add('after update='|| to_char(SQL%ROWCOUNT));
End Select_Where_Cond_Purge_Quote;
PROCEDURE Select_Ids_Purge
(
p_purge_set_id IN NUMBER
, p_selected_ids_tbl IN SELECTED_IDS_TBL
, p_count_selected IN NUMBER
, p_orders_per_commit IN NUMBER
)
IS
l_order_number NUMBER := 0;
oe_debug_pub.Add('Entering OE_Order_Purge_PVT.Select_Ids_Purge : '||p_purge_set_id,1);
FOR I IN 1..p_count_selected
LOOP
l_header_id := p_selected_ids_tbl(I);
SELECT ooh.order_number
, oot.name
, ooh.sold_to_org_id
, ooh.price_list_id
, ooh.quote_number
, ooh.expiration_date
, ooh.flow_status_code
, ooh.upgraded_flag
, ooh.transaction_phase_code
INTO
l_order_number
, l_order_type_name
, l_customer_number
, l_price_list_id
, l_quote_number
, l_expiration_date
, l_flow_status_code
, l_upgraded_flag
, l_transaction_phase_code
From oe_order_types_v oot, --MOAC view based on multiple objects
oe_order_headers_all ooh
WHERE
ooh.header_id = l_header_id
AND ooh.order_type_id = oot.order_type_id;
OE_DEBUG_PUB.Add('before update='|| p_purge_set_id);
UPDATE OE_PURGE_SETS
SET purge_processed = 'Y'
WHERE purge_set_id = p_purge_set_id;
OE_DEBUG_PUB.Add('after update='|| to_char(SQL%ROWCOUNT));
oe_debug_pub.Add('Exiting OE_Order_Purge_PVT.Select_Ids_Purge : '||p_purge_set_id,1);
END Select_Ids_Purge;
PROCEDURE Insert_Purge_Set
(
p_purge_set_name IN VARCHAR2
, p_purge_set_description IN VARCHAR2
, p_purge_set_request_Id IN NUMBER
, p_purge_set_submit_datetime IN DATE
, p_selected_ids IN SELECTED_IDS_TBL
, p_count_selected IN NUMBER
, p_where_condition IN VARCHAR2
, p_created_by IN NUMBER
, p_last_updated_by IN NUMBER
, x_purge_set_id OUT NOCOPY /* file.sql.39 change */ NUMBER
)
IS
l_purge_set_id NUMBER;
l_selected_ids CLOB;
oe_debug_pub.add('Entering OE_Order_Purge_PVT.Insert_Purge_Set : '||p_purge_set_name,1);
SELECT OE_PURGE_SETS_S.NEXTVAL
INTO l_purge_set_id
FROM DUAL;
INSERT INTO OE_PURGE_SETS
( PURGE_SET_ID
, PURGE_SET_NAME
, PURGE_SET_DESCRIPTION
, PURGE_SET_REQUEST_ID
, PURGE_SET_SUBMIT_DATETIME
, COUNT_SELECTED
, PURGE_PROCESSED
, PURGE_SET_PURGED
, WHERE_CONDITION
, ORDERS_PER_COMMIT
, SELECTED_IDS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
)
VALUES
( l_purge_set_id
, p_purge_set_name
, p_purge_set_description
, p_purge_set_request_Id
, p_purge_set_submit_datetime
, p_count_selected
, 'N'
, 'N'
, p_where_condition
, l_orders_per_commit
, EMPTY_CLOB()
, sysdate
, p_created_by
, sysdate
, p_last_updated_by);
IF p_count_selected <> 0 THEN
SELECT SELECTED_IDS
INTO l_selected_ids
FROM OE_PURGE_SETS
WHERE PURGE_SET_ID = l_purge_set_id;
DBMS_LOB.OPEN(l_selected_ids,DBMS_LOB.LOB_READWRITE);
FOR I IN 1 .. p_count_selected
LOOP
IF (length(l_buffer) + length(p_selected_ids(I))) > l_amount THEN
oe_debug_pub.add('Reached the limit : '||to_char(length(l_buffer)));
DBMS_LOB.WRITE(l_selected_ids,l_amount,l_position,l_buffer);
l_buffer := l_buffer || to_char(p_selected_ids(I));
l_buffer := l_buffer||l_separator||to_char(p_selected_ids(I));
DBMS_LOB.WRITE(l_selected_ids,l_amount,l_position,l_buffer);
DBMS_LOB.CLOSE(l_selected_ids);
oe_debug_pub.add('Exiting OE_Order_Purge_PVT.Insert_Purge_Set : '||to_char(x_purge_set_id),1);
END Insert_Purge_Set;
SELECT transaction_phase_code
INTO l_transaction_phase_code
FROM oe_order_headers
WHERE header_id=p_header_id;
SELECT meaning
INTO l_flow_status
FROM fnd_lookup_values lv
WHERE lv.lookup_code=p_flow_status_code
AND lookup_type='LINE_FLOW_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 otl.name
INTO l_order_type_name
FROM oe_transaction_types_tl otl,
oe_order_headers ooh
WHERE otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
AND otl.transaction_type_id = ooh.order_type_id
AND ooh.header_id = p_header_id;
select count(*)
into l_cnt
from wsh_delivery_details dd,
oe_order_lines l
where l.header_id = p_header_id
and dd.source_line_id = l.line_id
AND dd.org_id = l.org_id
and dd.source_code = 'OE'
and (nvl(dd.released_status, 'N') not in ('C', 'D') or
( dd.released_status = 'C' and
( nvl(dd.inv_interfaced_flag, 'N') in ( 'N','P') or
nvl(dd.oe_interfaced_flag, 'N') in ( 'N','P')
)
)
);
INSERT INTO OE_PURGE_ORDERS
( PURGE_SET_ID,
HEADER_ID,
ORDER_NUMBER,
QUOTE_NUMBER,
ORDER_TYPE_NAME,
CUSTOMER_NUMBER,
PRICE_LIST_ID,
IS_PURGABLE,
IS_PURGED,
ERROR_TEXT,
FLOW_STATUS,
EXPIRATION_DATE,
UPGRADED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGON,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID)
VALUES ( p_purge_set_id
, p_header_id
, p_order_number
, p_quote_number
, p_order_type_name
, p_customer_number
, p_price_list_id
, l_is_purgable
,'N'
, l_error_message
, l_flow_status
, p_expiration_date
, p_upgraded_flag
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.LOGIN_ID
, NULL
, NULL
, 0
,660);
SELECT header_id
FROM oe_purge_orders
Where purge_set_id = p_purge_set_id
AND NVL(is_purgable,'N') = 'Y'
AND NVL(is_purged,'N') = 'N';
SELECT created_by
FROM oe_purge_orders
WHERE purge_set_id = p_purge_set_id;
SELECT orders_per_commit, purge_set_name
INTO l_orders_per_commit, l_purge_set_name
FROM OE_PURGE_SETS
WHERE purge_set_id = p_purge_set_id
AND created_by = fnd_global.user_id;
SELECT count(*)
INTO l_number_of_orders
FROM oe_purge_orders
where purge_set_id = p_purge_set_id;
UPDATE OE_PURGE_ORDERS
SET IS_PURGED = 'Y'
WHERE purge_set_id = p_purge_set_id
AND header_id = l_header_id
AND created_by = fnd_global.user_id;
UPDATE OE_PURGE_SETS
SET PURGE_SET_PURGED = 'Y',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE PURGE_SET_ID = p_purge_set_id
AND CREATED_BY = fnd_global.user_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of orders/quotes selected for purge : '||l_number_of_orders);
fnd_file.put_line(FND_FILE.LOG,'Orders could not be Purged as the Purge Set selected is Created by another User.');
PROCEDURE Delete_Purge_Set
(
p_purge_set_id IN NUMBER
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
Cursor c_purge_set is
SELECT PURGE_SET_NAME,
PURGE_SET_PURGED
FROM OE_PURGE_SETS
WHERE purge_set_id = p_purge_set_id;
SELECT IS_PURGED
FROM OE_PURGE_ORDERS
WHERE PURGE_SET_ID = p_purge_set_id
AND NVL(IS_PURGED,'N') = 'Y';
oe_debug_pub.add('Entering OE_Order_Purge_PVT.Delete_Purge_Set : '||to_char(p_purge_set_id),1);
DELETE FROM OE_PURGE_ORDERS
WHERE purge_set_id = p_purge_set_id;
DELETE FROM OE_PURGE_SETS
WHERE purge_set_id = p_purge_set_id;
oe_debug_pub.add('Exiting OE_Order_Purge_PVT.Delete_Purge_Set : '||to_char(p_purge_set_id),1);
END Delete_Purge_Set;
SELECT 'Open Quotes'
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id
AND NVL(OPEN_FLAG,'Y') = 'N';
SELECT 'Open Orders'
FROM OE_ORDER_HEADERS
WHERE Header_id=p_header_id --for Bug # 4516769
AND NVL(OPEN_FLAG,'Y') = 'N';
SELECT 'Open invoices for this sales order'
--FROM ra_customer_trx_lines rctl, --MOAC
FROM ra_customer_trx_lines_all rctl, --MOAC
RA_CUSTOMER_TRX rct
WHERE rctl.interface_line_attribute1 = p_order_number
AND rctl.interface_line_attribute2 = p_order_type_name
--bug3389049 start
AND rctl.interface_line_context = 'ORDER ENTRY'
--bug3389049 end
AND rctl.customer_trx_id = rct.customer_trx_id
AND rct.complete_flag = 'N';
SELECT 'Open return for this sales order'
/*MOAC*/
--FROM oe_order_lines sl1,
--oe_order_lines sl2,
FROM oe_order_lines_all sl1,
oe_order_lines_all sl2,
oe_order_headers_all sh,
oe_order_types_v ot --MOAC view based on multiple objects
WHERE sh.order_number = p_order_number
AND sh.order_type_id = ot.order_type_id
AND ot.name = p_order_type_name
AND sl1.header_id = sh.header_id
AND sl2.reference_line_id = sl1.line_id
AND sl2.line_category_code = 'RETURN'
-- AND sl2.reference_type IN ( 'ORDER', 'PO' )
AND sl2.return_context IN ( 'ORDER', 'PO' ) --for bug 2784219
AND nvl(sl2.open_flag,'N') = 'Y';
SELECT line_id
FROM oe_order_lines
WHERE header_id = p_header_id
AND line_category_code = 'RETURN';
SELECT ds.line_location_id line_location_id
FROM oe_drop_ship_sources ds
WHERE ds.header_id = p_header_id;
SELECT header_id -- Lock all rows to be purged
FROM oe_order_headers
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT header_id -- Lock all rows to be purged
FROM oe_order_header_history
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT header_id -- Lock all rows to be purged
FROM oe_price_adjs_history
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT header_id -- Lock all rows to be purged
FROM oe_sales_credit_history
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT entity_id -- Lock all rows to be purged
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN (SELECT 1,header_id FROM oe_order_headers_all WHERE header_id=p_header_id)
FOR UPDATE NOWAIT;
SELECT entity_id -- Lock all rows to be purged
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN (SELECT 2,line_id FROM oe_order_lines_all WHERE header_id=p_header_id)
FOR UPDATE NOWAIT;
SELECT entity_id -- Lock all rows to be purged
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN (SELECT DECODE(line_id,null,6,8),price_adjustment_id FROM oe_price_adjustments WHERE header_id=p_header_id )
FOR UPDATE NOWAIT;
SELECT entity_id -- Lock all rows to be purged
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN ( SELECT DECODE(line_id,null,5,7),sales_credit_id FROM oe_sales_credits WHERE header_id=p_header_id)
FOR UPDATE NOWAIT;
select set_id from oe_sets_history
where header_id= p_header_id
FOR UPDATE NOWAIT;
DELETE
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN (SELECT 1,header_id FROM oe_order_headers_all WHERE header_id=p_header_id);
DELETE
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN (SELECT 2,line_id FROM oe_order_lines_all WHERE header_id=p_header_id);
DELETE
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN (SELECT DECODE(line_id,null,6,8),price_adjustment_id FROM oe_price_adjustments WHERE header_id=p_header_id );
DELETE
FROM OE_AUDIT_ATTR_HISTORY
WHERE (entity_id,entity_number) IN ( SELECT DECODE(line_id,null,5,7),sales_credit_id FROM oe_sales_credits WHERE header_id=p_header_id);
-- Delete the attachments.
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
OE_Atchmt_Util.Delete_Attachments
(
p_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
p_entity_id => p_header_id,
x_return_status => l_return_status
);
oe_debug_pub.add('Attachments delete failed : ');
-- Delete the header work flow.
BEGIN
OE_Order_WF_Util.Delete_Row
(
p_type => 'HEADER',
p_id => p_header_id
);
DELETE FROM oe_order_header_history WHERE header_id = p_header_id;
DELETE FROM oe_price_adjs_history WHERE header_id = p_header_id;
DELETE FROM oe_sales_credit_history WHERE header_id = p_header_id;
DELETE FROM oe_sets_history WHERE header_id = p_header_id;
DELETE FROM oe_order_headers
WHERE header_id = p_header_id;
oe_debug_pub.add('deleted header='|| to_char(p_header_id));
SELECT line_id,order_quantity_uom,
org_id,line_category_code,source_type_code,
top_model_line_id,config_header_id,
config_rev_nbr
FROM oe_order_lines
WHERE header_id = p_header_id;
SELECT line_id
FROM oe_order_lines_history
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT line_id
FROM oe_drop_ship_sources
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT ds.line_location_id
FROM oe_drop_ship_sources ds
WHERE ds.header_id = p_header_id;
SELECT line_id
INTO l_lock_line_id
FROM oe_order_lines
WHERE line_id = l_line_id
FOR UPDATE NOWAIT;
OE_Config_Pvt.Delete_Config
( p_config_hdr_id => l_config_header_id,
p_config_rev_nbr => l_config_rev_nbr,
x_return_status => l_return_status );
OE_DEBUG_PUB.Add('After Calling Delete_Config',1);
-- Delete the attachments.
oe_debug_pub.add('Before before attach : ',1);
OE_Atchmt_Util.Delete_Attachments
(
p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
p_entity_id => l_line_id,
x_return_status => l_return_status
);
oe_debug_pub.add('Attachments delete failed : ');
-- Delete the Line work flow.
oe_debug_pub.add('Before workflow : ',1);
OE_Order_WF_Util.Delete_Row
(
p_type => 'LINE',
p_id => l_line_id
);
DELETE FROM oe_order_lines_history WHERE header_id = p_header_id;
OE_DEBUG_PUB.Add('Before line delete : ',1);
DELETE FROM oe_order_lines
WHERE line_id = l_line_id;
DELETE FROM oe_drop_ship_sources where header_id = p_header_id;
oe_debug_pub.add('Number of lines deleted : '||to_char(c_purge_lines%ROWCOUNT),1);
oe_debug_pub.add('deleted lines for header='|| to_char(p_header_id));
SELECT PRICE_ADJUSTMENT_ID
FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID = p_header_id;
SELECT price_adj_attrib_id
FROM oe_price_adj_attribs
WHERE price_adjustment_id = p_price_adjustment_id;
SELECT price_adj_assoc_id
FROM OE_PRICE_ADJ_ASSOCS OPAA
WHERE opaa.rltd_price_adj_id IN (SELECT TO_NUMBER (p_price_adjustment_id)
FROM DUAL
UNION ALL
SELECT opaa1.rltd_price_adj_id
FROM oe_price_adj_assocs opaa1
WHERE opaa1.price_adjustment_id = p_price_adjustment_id);
OPAA.rltd_price_adj_id in( select opaa1.rltd_price_adj_id from
oe_price_adj_assocs opaa1 where
opaa1.price_adjustment_id = p_price_adjustment_id);
SELECT price_adjustment_id
INTO l_lock_adjustment_id
FROM oe_price_adjustments
WHERE price_adjustment_id = l_price_adjustment_id
FOR UPDATE NOWAIT;
-- Delete from price_adj_attribs.
OPEN c_price_adj(l_price_adjustment_id);
SELECT price_adj_attrib_id
INTO l_lock_price_adj_attrib_id
FROM oe_price_adj_attribs
WHERE price_adj_attrib_id = l_price_adj_attrib_id
FOR UPDATE NOWAIT;
DELETE FROM oe_price_adj_attribs
WHERE price_adj_attrib_id = l_price_adj_attrib_id;
oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
-- Delete from price_adj_assocs.
OPEN c_price_adj_assocs(l_price_adjustment_id);
SELECT price_adj_assoc_id
INTO l_lock_price_adj_assoc_id
FROM oe_price_adj_assocs
WHERE price_adj_assoc_id = l_price_adj_assoc_id
FOR UPDATE NOWAIT;
DELETE FROM oe_price_adj_assocs
WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE price_adjustment_id = l_lock_adjustment_id;
oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_header_adj%ROWCOUNT),1);
SELECT PRICE_ADJUSTMENT_ID
FROM OE_PRICE_ADJUSTMENTS
WHERE LINE_ID = p_line_id;
SELECT price_adj_attrib_id
FROM oe_price_adj_attribs
WHERE price_adjustment_id = p_price_adjustment_id;
SELECT price_adj_assoc_id
FROM OE_PRICE_ADJ_ASSOCS OPAA
WHERE opaa.rltd_price_adj_id IN (
SELECT p_price_adjustment_id
FROM DUAL
UNION ALL
SELECT opaa1.rltd_price_adj_id
FROM oe_price_adj_assocs opaa1
WHERE opaa1.price_adjustment_id = p_price_adjustment_id);
SELECT price_adjustment_id
INTO l_lock_adjustment_id
FROM oe_price_adjustments
WHERE price_adjustment_id = l_price_adjustment_id
FOR UPDATE NOWAIT;
SELECT price_adj_attrib_id
INTO l_lock_price_adj_attrib_id
FROM oe_price_adj_attribs
WHERE price_adj_attrib_id = l_price_adj_attrib_id
FOR UPDATE NOWAIT;
DELETE FROM oe_price_adj_attribs
WHERE price_adj_attrib_id = l_price_adj_attrib_id;
oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
-- Delete from price_adj_attribs.
SELECT price_adjustment_id
INTO l_lock_adjustment_id
FROM oe_price_adj_attribs
WHERE price_adjustment_id = l_price_adjustment_id
FOR UPDATE NOWAIT;
DELETE FROM oe_price_adj_attribs
WHERE price_adjustment_id = l_price_adjustment_id;
-- Delete from price_adj_assocs.
OPEN c_price_adj_assocs(l_price_adjustment_id);
SELECT price_adj_assoc_id
INTO l_lock_price_adj_assoc_id
FROM oe_price_adj_assocs
WHERE price_adj_assoc_id = l_price_adj_assoc_id
FOR UPDATE NOWAIT;
DELETE FROM oe_price_adj_assocs
WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE price_adjustment_id = l_lock_adjustment_id;
oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_line_adj%ROWCOUNT),1);
SELECT ORDER_PRICE_ATTRIB_ID
FROM OE_ORDER_PRICE_ATTRIBS
WHERE HEADER_ID = p_header_id;
SELECT ORDER_PRICE_ATTRIB_ID
INTO l_lock_price_attrib_id
FROM oe_order_price_attribs
WHERE ORDER_PRICE_ATTRIB_ID = l_order_price_attrib_id
FOR UPDATE NOWAIT;
DELETE FROM OE_ORDER_PRICE_ATTRIBS
WHERE ORDER_PRICE_ATTRIB_ID = l_order_price_attrib_id;
oe_debug_pub.add('Number of order price attribs deleted : '||to_char(c_order_price_attribs%ROWCOUNT),1);
SELECT SALES_CREDIT_ID
FROM OE_SALES_CREDITS
WHERE HEADER_ID = p_header_id;
SELECT sales_credit_id
INTO l_lock_sales_credit_id
FROM oe_sales_credits
WHERE sales_credit_id = l_sales_credit_id
FOR UPDATE NOWAIT;
DELETE FROM OE_SALES_CREDITS
WHERE SALES_CREDIT_ID = l_sales_credit_id;
oe_debug_pub.add('Number of order sales credit deleted : '||to_char(c_order_sales_credits%ROWCOUNT),1);
SELECT SALES_CREDIT_ID
FROM OE_SALES_CREDITS
WHERE LINE_ID = p_line_id;
SELECT sales_credit_id
INTO l_lock_sales_credit_id
FROM oe_sales_credits
WHERE sales_credit_id = l_sales_credit_id
FOR UPDATE NOWAIT;
DELETE FROM OE_SALES_CREDITS
WHERE SALES_CREDIT_ID = l_sales_credit_id;
oe_debug_pub.add('Number of line sales credit deleted : '||to_char(c_line_sales_credits%ROWCOUNT),1);
SELECT SET_ID
FROM OE_SETS
WHERE HEADER_ID = p_header_id;
SELECT set_id
INTO l_lock_set_id
FROM oe_sets
WHERE set_id = l_set_id
FOR UPDATE NOWAIT;
DELETE FROM OE_SETS
WHERE SET_ID = l_set_id;
oe_debug_pub.add('Number of order sets deleted : '||to_char(c_order_sets%ROWCOUNT),1);
SELECT LINE_ID,SET_ID
FROM OE_LINE_SETS
WHERE LINE_ID = p_line_id;
SELECT set_id,line_id
INTO l_lock_set_id,l_lock_line_id
FROM oe_line_sets
WHERE set_id = l_set_id
AND line_id = l_line_id
FOR UPDATE NOWAIT;
DELETE FROM OE_LINE_SETS
WHERE SET_ID = l_set_id
AND LINE_ID = l_line_id;
oe_debug_pub.add('Number of Line sets deleted : '||to_char(c_line_sets%ROWCOUNT),1);
SELECT DISTINCT NVL(HOLD_RELEASE_ID,0),
NVL(HOLD_SOURCE_ID,0),
ORDER_HOLD_ID
FROM OE_ORDER_HOLDS
WHERE HEADER_ID = p_header_id;
SELECT hold_source_id
FROM OE_HOLD_SOURCES
WHERE hold_source_id = p_hold_source_id
AND hold_entity_id = p_header_id
AND hold_entity_code = 'O';
SELECT hold_release_id
FROM OE_HOLD_RELEASES
WHERE hold_release_id = p_hold_release_id;
SELECT order_hold_id
INTO l_lock_order_hold_id
FROM oe_order_holds
WHERE order_hold_id = l_order_hold_id
FOR UPDATE NOWAIT;
SELECT hold_source_id
INTO l_lock_hold_source_id
FROM oe_hold_sources
WHERE hold_source_id = l_hold_source_id
FOR UPDATE NOWAIT;
DELETE FROM OE_HOLD_SOURCES
WHERE HOLD_SOURCE_ID = l_hold_source_id;
oe_debug_pub.add('Number of hold sources deleted : '||to_char(c_hold_sources%ROWCOUNT),1);
select count(*) into l_count from OE_ORDER_HOLDS where
hold_release_id = l_hold_release_id and HEADER_ID <> p_header_id;
SELECT hold_release_id
INTO l_lock_hold_release_id
FROM oe_hold_releases
WHERE hold_release_id = l_hold_release_id
FOR UPDATE NOWAIT;
DELETE FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id;
oe_debug_pub.add('Number of hold releases deleted : '||to_char(c_hold_releases%ROWCOUNT),1);
DELETE FROM OE_ORDER_HOLDS
WHERE ORDER_HOLD_ID = l_order_hold_id;
oe_debug_pub.add('Number of order holds deleted : '||to_char(c_order_holds%ROWCOUNT),1);
SELECT lot_serial_id
FROM oe_lot_serial_numbers
WHERE LINE_ID = p_line_id;
SELECT lot_serial_id
INTO l_lock_lot_serial_id
FROM oe_lot_serial_numbers
WHERE lot_serial_id = l_lot_serial_id
FOR UPDATE NOWAIT;
delete from oe_lot_serial_numbers
where lot_serial_id = l_lot_serial_id;
UPDATE oe_purge_orders
SET ERROR_TEXT = p_error_message
,IS_PURGED = 'N'
WHERE purge_set_id = p_purge_set_id
AND header_id = p_header_id;
Select payment_number
From oe_payments
Where header_id = p_header_id
and line_id is null
FOR UPDATE NOWAIT;
Delete from oe_payments
Where header_id = p_header_id
and nvl(payment_number,0) = nvl(l_payment_number,0);
select payment_number
from oe_payments
where line_id = p_line_id
and header_id = p_header_id
FOR UPDATE NOWAIT;
Delete from oe_payments
Where line_id = p_line_id
and nvl(payment_number,0) = nvl(l_payment_number,0);
SELECT transaction_phase_code
FROM oe_order_headers
WHERE header_id=p_header_id;
SELECT otl.name
INTO l_order_type_name
FROM oe_transaction_types_tl otl,
oe_order_headers ooh
WHERE otl.language = (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B')
AND otl.transaction_type_id = ooh.order_type_id
AND ooh.header_id = p_header_id;
SELECT count(*)
INTO l_cnt
FROM wsh_delivery_details dd,
oe_order_lines l
WHERE l.header_id = p_header_id
AND dd.source_line_id = l.line_id
AND dd.org_id = l.org_id
AND dd.source_code = 'OE'
AND (nvl(dd.released_status, 'N') not in ('C', 'D') or
( dd.released_status = 'C' and
( nvl(dd.inv_interfaced_flag, 'N') in ( 'N','P') or
nvl(dd.oe_interfaced_flag, 'N') in ( 'N','P')
)
)
);