[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT site_use_code
INTO l_site_use_code
FROM hz_cust_site_uses -- Bug 2138398
WHERE site_use_id = p_hold_entity_id;
PROCEDURE UPDATE_HOLD_COMMENTS (
p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HOLD_COMMENTS';
oe_debug_pub.add( 'IN UPDATE_HOLD_COMMENTS' ) ;
UPDATE OE_HOLD_SOURCES
SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id
WHERE HOLD_SOURCE_ID = p_hold_source_rec.HOLD_SOURCE_ID;
UPDATE OE_HOLD_SOURCES
SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id
WHERE HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
AND HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
AND HOLD_ENTITY_CODE2 = p_hold_source_rec.HOLD_ENTITY_CODE2
AND HOLD_ENTITY_ID2 = p_hold_source_rec.HOLD_ENTITY_ID2
AND HOLD_ID = p_hold_source_rec.hold_id
AND RELEASED_FLAG = 'N'
AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
UPDATE OE_HOLD_SOURCES HS
SET HS.HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
HS.LAST_UPDATE_DATE = SYSDATE,
HS.LAST_UPDATED_BY = FND_GLOBAL.user_id
WHERE HS.HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
AND HS.HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
AND HS.HOLD_ENTITY_CODE2 is null
AND HS.HOLD_ENTITY_ID2 is null
AND HS.HOLD_ID = p_hold_source_rec.hold_id
AND HS.RELEASED_FLAG = 'N'
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND exists (SELECT 'x'
FROM OE_ORDER_HOLDS OH
WHERE OH.LINE_ID = p_hold_source_rec.line_id
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID);
UPDATE OE_HOLD_SOURCES
SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id
WHERE HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
AND HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
AND HOLD_ENTITY_CODE2 is null
AND HOLD_ENTITY_ID2 is null
AND HOLD_ID = p_hold_source_rec.hold_id
AND RELEASED_FLAG = 'N'
AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
oe_debug_pub.add( 'OE_HOLDS_PUB.UPDATE_HOLD_COMMENTS:' || 'EITHER PASS HOLD_SOURCE_ID OR HOLD_ENTITY_CODE/HOLD_ENTITY_ID' ) ;
oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
END UPDATE_HOLD_COMMENTS;
select hold_source_id, ORG_ID
from oe_hold_sources
where HOLD_UNTIL_DATE <= sysdate
and released_flag = 'N';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id
AND HEADER_ID = p_hold_entity_id
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id
AND INVENTORY_ITEM_ID = p_hold_entity_id
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES l,
mtl_item_categories ic
WHERE l.LINE_ID = p_line_id
AND ic.category_id = p_hold_entity_id
AND l.INVENTORY_ITEM_ID = ic.inventory_item_id
AND ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES l
WHERE l.LINE_ID = p_line_id
AND l.end_customer_id =p_hold_entity_id
AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
-- validation data based on bill-to or ship-to site to be inserted here.
null;
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id
AND HEADER_ID = p_hold_entity_id2
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id
AND INVENTORY_ITEM_ID = p_hold_entity_id2
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES l
WHERE l.LINE_ID = p_line_id
AND l.end_customer_id =p_hold_entity_id2
AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_LINES l
WHERE l.LINE_ID = p_line_id
AND l.end_customer_id =p_hold_entity_id
AND l.end_customer_site_use_id =p_hold_entity_id2
AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id
AND SOLD_TO_ORG_ID = p_hold_entity_id
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id
AND SHIP_TO_ORG_ID = p_hold_entity_id
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_hold_entity_id2
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id
AND SOLD_TO_ORG_ID = p_hold_entity_id2
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id
AND SHIP_TO_ORG_ID = p_hold_entity_id2
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT header_id
INTO l_header_id
FROM OE_ORDER_LINES
WHERE LINE_ID = p_order_tbl(j).line_id;
SELECT HOLD_ENTITY_CODE, HOLD_ENTITY_ID,
HOLD_ENTITY_CODE2, HOLD_ENTITY_ID2
INTO l_entity_code, l_entity_id,
l_entity_code2, l_entity_id2
FROM OE_HOLD_SOURCES
WHERE HOLD_SOURCE_ID = p_hold_source_id
AND RELEASED_FLAG = 'N'
AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
SELECT header_id
INTO l_header_id
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id;
SELECT 'Duplicate Hold'
INTO l_dummy
FROM OE_ORDER_HOLDS
WHERE hold_source_id = l_hold_source_id
AND HEADER_ID = l_header_id
AND NVL(LINE_ID, NVL(p_line_id,0)) = NVL(p_line_id, 0)
AND HOLD_RELEASE_ID IS NULL
AND ROWNUM = 1;
SELECT 'ANY_LINE_HOLD'
INTO l_dummy
FROM oe_order_holds_all oh
WHERE oh.header_id = p_hdr_id
and oh.line_id = p_line_id
and oh.hold_release_id is null
AND EXISTS
(SELECT 1
FROM oe_hold_sources_all hs,
oe_hold_definitions h
WHERE oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = h.hold_id
AND NVL(h.item_type,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_item, 'NO ITEM')) ) =
NVL(p_wf_item, 'NO ITEM')
AND NVL(H.ACTIVITY_NAME,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_activity, 'NO ACT')) ) =
NVL(p_wf_activity,'NO ACT')
AND DECODE(p_ii_parent_flag, 'Y',
nvl(h.hold_included_items_flag, 'N'), 'XXXXX') =
DECODE(p_ii_parent_flag, 'Y', 'Y', 'XXXXX')
AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
AND NVL( H.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
AND NVL(hs.hold_entity_id2, -99) =
nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
SELECT /* MOAC_SQL_CHANGE */ 'ANY_ATO_LINE_HOLD'
INTO l_dummy
FROM oe_order_holds_all oh
WHERE oh.header_id = p_hdr_id
and oh.line_id in (select ol.line_id from oe_order_lines_all ol
where ol.header_id = oh.header_id
and ol.ato_line_id = p_ato_line_id
and ol.top_model_line_id = p_top_model_line_id)
and oh.hold_release_id is null
AND EXISTS
(SELECT 1
FROM oe_hold_sources_all hs,
oe_hold_definitions h
WHERE oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = h.hold_id
AND NVL(h.item_type,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_item, 'NO ITEM')) ) =
NVL(p_wf_item, 'NO ITEM')
AND NVL(H.ACTIVITY_NAME,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_activity, 'NO ACT')) ) =
NVL(p_wf_activity,'NO ACT')
AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
AND NVL( H.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
AND NVL(hs.hold_entity_id2, -99) =
nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
SELECT /* MOAC_SQL_CHANGE */ 'ANY_SMC_LINE_HOLD'
INTO l_dummy
FROM oe_order_holds_all oh
WHERE oh.header_id = p_hdr_id
and oh.line_id in (select ol.line_id from oe_order_lines_all ol
where ol.header_id = oh.header_id
and ol.top_model_line_id = p_top_model_line_id)
and oh.hold_release_id is null
AND EXISTS
(SELECT 1
FROM oe_hold_sources_all hs,
oe_hold_definitions h
WHERE oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = h.hold_id
AND NVL(h.item_type,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_item, 'NO ITEM')) ) =
NVL(p_wf_item, 'NO ITEM')
AND NVL(H.ACTIVITY_NAME,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_activity, 'NO ACT')) ) =
NVL(p_wf_activity,'NO ACT')
AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
AND NVL( H.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
AND NVL(hs.hold_entity_id2, -99) =
nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
SELECT 'ANY_LINE_HOLD'
INTO l_dummy
FROM oe_order_holds_all oh
WHERE oh.header_id = x_hold_rec.header_id
and OH.LINE_ID is not null
and OH.HOLD_RELEASE_ID IS NULL
and ROWNUM = 1
AND EXISTS
(SELECT 1
FROM oe_hold_sources_all hs,
oe_hold_definitions h
WHERE oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = h.hold_id
AND NVL(h.item_type,
DECODE(x_hold_rec.p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(x_hold_rec.wf_item_type, 'NO ITEM')) ) =
NVL(x_hold_rec.wf_item_type, 'NO ITEM')
AND NVL(H.ACTIVITY_NAME,
DECODE(x_hold_rec.p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(x_hold_rec.wf_activity_name, 'NO ACT')) ) =
NVL(x_hold_rec.wf_activity_name,'NO ACT')
AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
AND NVL( H.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ID = NVL(x_hold_rec.hold_id,HS.HOLD_ID)
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND hs.hold_entity_code = NVL(x_hold_rec.hold_entity_code, hs.hold_entity_code)
AND hs.hold_entity_id = NVL(x_hold_rec.hold_entity_id, hs.hold_entity_id)
AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
NVL(x_hold_rec.hold_entity_code2, NVL(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
AND NVL(hs.hold_entity_id2, -99) =
NVL(x_hold_rec.hold_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
select line_id
from oe_order_lines_all
where ato_line_id = l_ato_line_id
and top_model_line_id = l_top_model_line_id;
select line_id
from oe_order_lines_all
where top_model_line_id = l_top_model_line_id;
** The following 'if' condition was added to select the header_id
** into a local variable 'p_hdr_id'. This variable is passed as a
** parameter to the procedure CHECK_HOLDS_LINE. This ensures that
** header_id is always passed as a not null parameter.
*/
IF p_header_id IS NULL THEN
Begin
SELECT header_id
INTO p_hdr_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT org_id
INTO l_org_id
FROM oe_order_headers_all
WHERE header_id=p_hdr_id;
SELECT 'ANY_HEADER_HOLD'
INTO l_dummy
FROM oe_order_holds_all oh
WHERE oh.header_id = p_hdr_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL
AND EXISTS
(SELECT 1
FROM oe_hold_sources_all hs,
oe_hold_definitions h
WHERE oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = h.hold_id
AND NVL(h.item_type,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_item, 'NO ITEM')) ) =
NVL(p_wf_item, 'NO ITEM')
AND NVL(h.activity_name,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_activity, 'NO ACT')) ) =
NVL(p_wf_activity, 'NO ACT')
AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
AND NVL( H.END_DATE_ACTIVE, SYSDATE )
AND hs.hold_id = NVL(p_hold_id, hs.hold_id)
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
AND NVL(hs.hold_entity_id2, -99) =
nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
SELECT ATO_LINE_ID, TOP_MODEL_LINE_ID,
SHIP_MODEL_COMPLETE_FLAG, ITEM_TYPE_CODE,
LINK_TO_LINE_ID
INTO l_ato_line_id, l_top_model_line_id,
l_smc_flag, l_item_type_code, l_link_to_line_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT 'ANY_HOLD_SOURCE'
INTO l_dummy
FROM oe_hold_sources_all hs,
oe_hold_definitions h
WHERE hs.hold_id = h.hold_id
AND NVL(h.item_type,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_item, 'NO ITEM')) ) =
NVL(p_wf_item, 'NO ITEM')
AND NVL(h.activity_name,
DECODE(p_chk_act_hold_only,
'Y', 'XXXXX',
NVL(p_wf_activity, 'NO ACT')) ) =
NVL(p_wf_activity, 'NO ACT')
AND hs.hold_id = NVL(p_hold_id, hs.hold_id)
AND hs.RELEASED_FLAG = 'N'
AND hs.hold_entity_code = NVL(p_hold_entity_code, hs.hold_entity_code)
AND hs.hold_entity_id = NVL(p_hold_entity_id, hs.hold_entity_id)
AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
NVL(p_hold_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
AND NVL(hs.hold_entity_id2, -99) =
nvl(p_hold_entity_id2, NVL(hs.hold_entity_id2, -99 ) );
SELECT HS.HOLD_SOURCE_ID,OH.ORDER_HOLD_ID
FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
WHERE HS.HOLD_ID = p_hold_id
AND HS.RELEASED_FLAG = 'N'
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND HS.HOLD_ENTITY_CODE = p_entity_code
AND HS.HOLD_ENTITY_ID = p_entity_id
AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
nvl(p_entity_code2, 'NO_ENTITY_CODE2')
AND nvl(HS.HOLD_ENTITY_ID2, -99) =
nvl(p_entity_id2, -99)
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND NVL(OH.HEADER_ID, 0) = NVL(NVL(p_header_id, OH.HEADER_ID), 0)
AND NVL(OH.LINE_ID, 0) = NVL(NVL(p_line_id, OH.LINE_ID), 0)
AND OH.HOLD_RELEASE_ID IS NULL;
SELECT OH.ORDER_HOLD_ID
FROM OE_ORDER_HOLDS OH
WHERE OH.HOLD_SOURCE_ID = l_hold_source_id
AND NVL(OH.HEADER_ID, 0) = NVL(NVL(p_header_id, OH.HEADER_ID), 0)
AND NVL(OH.LINE_ID, 0) = NVL(NVL(p_line_id, OH.LINE_ID), 0)
AND OH.HOLD_RELEASE_ID IS NULL;
SELECT hold_entity_code
INTO l_entity_code
FROM OE_HOLD_SOURCES
WHERE hold_source_id = l_hold_source_id;
PROCEDURE Delete_Holds
( p_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HOLDS';
SELECT order_hold_id, NVL(hold_release_id,0)
FROM OE_ORDER_HOLDS
WHERE HEADER_ID = p_header_id;
SELECT hold_source_id, NVL(hold_release_id,0)
FROM OE_HOLD_SOURCES
WHERE HOLD_ENTITY_CODE = 'O'
AND HOLD_ENTITY_ID = p_header_id;
SELECT order_hold_id, NVL(hold_release_id,0)
FROM OE_ORDER_HOLDS
WHERE LINE_ID = p_line_id;
SELECT OH.order_hold_id, NVL(OH.hold_release_id,0)
FROM OE_ORDER_HOLDS OH,OE_HOLD_SOURCES HS,OE_ORDER_LINES OL
WHERE OH.LINE_ID = p_top_model_line_id
AND OH.LINE_ID = OL.LINE_ID
AND OH.hold_source_id=HS.hold_source_id
AND HS.hold_entity_id=OL.inventory_item_id
AND HS.hold_entity_id2=p_inventory_item_id;
DELETE FROM OE_ORDER_HOLDS
WHERE order_hold_id = l_order_hold_id;
DELETE FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id
AND ORDER_HOLD_ID = l_order_hold_id;
/* DELETE FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id
AND HOLD_RELEASE_ID NOT IN (SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_ORDER_HOLDS
UNION
SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_HOLD_SOURCES
); */
DELETE FROM OE_HOLD_SOURCES
WHERE HOLD_SOURCE_ID = l_hold_source_id;
DELETE FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id;
DELETE FROM OE_ORDER_HOLDS
WHERE order_hold_id = l_order_hold_id;
DELETE FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id
AND ORDER_HOLD_ID = l_order_hold_id;
(SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_ORDER_HOLDS
UNION
SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_HOLD_SOURCES
); */
select top_model_line_id,inventory_item_id
into l_top_model_line_id,l_inventory_item_id
from oe_order_lines
where line_id=p_line_id
and item_type_code in ('OPTION','CLASS','INCLUDED');
oe_debug_pub.add( 'DELETING LINE HOLD RECORD FOR TOP MODEL WHEN OPTION ITEM LINE IS DELETED' ) ;
DELETE FROM OE_ORDER_HOLDS
WHERE order_hold_id = l_order_hold_id;
DELETE FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id
AND ORDER_HOLD_ID = l_order_hold_id;
--ROLLBACK TO DELETE_HOLDS_PUB;
--ROLLBACK TO DELETE_HOLDS_PUB;
END Delete_Holds;
SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code --ER#7479609
FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = l_header_id
AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
NVL(l_line_id,FND_API.G_MISS_NUM)
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND HS.HOLD_ENTITY_ID = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID) --ER#7479609
--AND HS.HOLD_ENTITY_CODE = p_hold_entity_code -- ER#3667551
AND HS.HOLD_ENTITY_CODE = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
AND HS.RELEASED_FLAG = 'N';
SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code2 --ER#7479609
FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
WHERE OH.HEADER_ID = l_header_id
AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
NVL(l_line_id,FND_API.G_MISS_NUM)
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
--AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code -- ER#3667551
AND HS.HOLD_ENTITY_CODE2 = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
AND HS.HOLD_ENTITY_ID2 = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID2) --ER#7479609
AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
AND HS.HOLD_ENTITY_CODE <> 'O'
AND HS.RELEASED_FLAG = 'N';
SELECT HS.hold_source_id, hs.hold_id,
hs.hold_entity_code, hs.hold_entity_id,
hs.hold_entity_code2,hs.hold_entity_id2
FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
--WHERE HS.HOLD_ENTITY_CODE = p_hold_entity_code -- ER#3667551
WHERE HS.HOLD_ENTITY_CODE = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
AND HS.HOLD_ENTITY_ID = p_hold_entity_id
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND HLD.HOLD_ID = HS.HOLD_ID
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE );
SELECT HS.hold_source_id, hs.hold_id,
hs.hold_entity_code, hs.hold_entity_id,
hs.hold_entity_code2,hs.hold_entity_id2
FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
--WHERE HS.HOLD_ENTITY_CODE2 = p_hold_entity_code -- ER#3667551
WHERE HS.HOLD_ENTITY_CODE2 = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id
AND HS.HOLD_ENTITY_CODE <> 'O'
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND HLD.HOLD_ID = HS.HOLD_ID
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE );
-- When a delayed request is logged for update of Bill To Customer Of Order header level
-- the code is passed as 'BTH' instead of 'C'. This is done because both Custom hold for Sold to Customer
-- and Credit Hold for Bill To Customer are created with hold_entity_code as 'C'.
-- For update of Bill To Customer we only need to re-evaluate Credit Hold and not Custom Holds.
If p_hold_entity_code = 'BTH' AND p_entity_code = OE_Globals.G_ENTITY_HEADER then
l_bth_entity_code := 'C';
SELECT * INTO l_header_rec
FROM oe_order_headers
WHERE header_id=l_header_id;
SELECT OH.* INTO l_header_rec
FROM oe_order_headers OH,oe_order_lines OL
WHERE OH.header_id=OL.header_id
AND OL.line_id=l_line_id;
SELECT sold_to_org_id,
invoice_to_org_id,
ship_to_org_id,
ship_from_org_id,
inventory_item_id,
line_number,
Blanket_number,
Blanket_line_number,
header_id
INTO l_sold_to_org_id,
l_invoice_to_org_id,
l_ship_to_org_id,
l_ship_from_org_id,
l_inventory_item_id,
l_line_number,
l_blanket_number,
l_blanket_line_number,
l_header_id
FROM oe_order_lines
WHERE line_id = l_line_id;
SELECT * INTO l_line_rec
FROM oe_order_lines
WHERE line_id = l_line_id;
l_payment_type_tab.delete;
Select payment_type_code payment_type
BULK COLLECT INTO l_payment_type_tab
FROM OE_PAYMENTS
WHERE header_id=l_header_rec.header_id
AND line_id IS NULL;
l_payment_type_tab.delete;
DELETE FROM OE_ORDER_HOLDS
WHERE ORDER_HOLD_ID = l_order_hold_id;
DELETE FROM OE_ORDER_HOLDS
WHERE ORDER_HOLD_ID = l_order_hold_id;
l_payment_type_tab.delete;
SELECT V.payment_type
BULK COLLECT INTO l_payment_type_tab
FROM
(Select payment_type_code payment_type
FROM OE_PAYMENTS
WHERE header_id=l_header_rec.header_id
AND line_id IS NULL
UNION
SELECT payment_type_code payment_type
FROM OE_ORDER_HEADERS_ALL
WHERE header_id=l_header_rec.header_id) V;
l_payment_type_tab.delete;
Select payment_type_code payment_type
BULK COLLECT INTO l_payment_type_tab
FROM OE_PAYMENTS
WHERE header_id=l_header_rec.header_id
AND line_id IS NOT NULL;
--SELECT line_number
-- INTO l_line_number
-- FROM OE_ORDER_LINES
-- WHERE LINE_ID = p_entity_id;
select 'Y' into l_valid_itemcat
from mtl_item_categories mic,
mtl_default_category_sets mdc
where mic.inventory_item_id = l_line_rec.inventory_item_id
and mic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
and mic.category_id = l_hold_entity_id
AND mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id;
--SELECT line_number
-- INTO l_line_number
-- FROM OE_ORDER_LINES
-- WHERE LINE_ID = p_entity_id;
SELECT HS.hold_source_id, hs.hold_id,
hs.hold_entity_code, hs.hold_entity_id,
hs.hold_entity_code2,hs.hold_entity_id2
FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
WHERE HLD.HOLD_ID = HS.HOLD_ID
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
AND HS.HOLD_ENTITY_ID = p_hold_entity_id;
SELECT HS.hold_source_id, hs.hold_id,
hs.hold_entity_code, hs.hold_entity_id,
hs.hold_entity_code2,hs.hold_entity_id2
FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
WHERE HLD.HOLD_ID = HS.HOLD_ID
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
--ER#7479609 start
AND DECODE(p_hold_entity_code,'OI',HS.HOLD_ENTITY_CODE2,HS.HOLD_ENTITY_CODE) = p_hold_entity_code
AND DECODE(p_hold_entity_code,'OI',HS.HOLD_ENTITY_ID2,HS.HOLD_ENTITY_ID) = p_hold_entity_id;
SELECT HS.hold_source_id, hs.hold_id,
hs.hold_entity_code, hs.hold_entity_id,
hs.hold_entity_code2,hs.hold_entity_id2
FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
WHERE HLD.HOLD_ID = HS.HOLD_ID
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
AND HS.HOLD_ENTITY_ID = p_hold_entity_id;
SELECT HS.hold_source_id, hs.hold_id,
hs.hold_entity_code, hs.hold_entity_id,
hs.hold_entity_code2,hs.hold_entity_id2
FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
WHERE HLD.HOLD_ID = HS.HOLD_ID
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code
AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id;
SELECT HS.hold_source_id, hs.hold_id,
hs.hold_entity_code, hs.hold_entity_id,
hs.hold_entity_code2,hs.hold_entity_id2
FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
WHERE HLD.HOLD_ID = HS.HOLD_ID
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code2
AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id2
AND HS.HOLD_ENTITY_CODE <> 'O';
SELECT * INTO l_header_rec
FROM oe_order_headers
WHERE header_id=l_header_id;
SELECT OH.* INTO l_header_rec
FROM oe_order_headers OH,oe_order_lines OL
WHERE OH.header_id=OL.header_id
AND OL.line_id=l_line_id;
SELECT sold_to_org_id,
invoice_to_org_id,
ship_to_org_id,
ship_from_org_id,
inventory_item_id,
line_number,
blanket_number,
blanket_line_number,
header_id
INTO l_sold_to_org_id,
l_invoice_to_org_id,
l_ship_to_org_id,
l_ship_from_org_id,
l_inventory_item_id,
l_line_number,
l_blanket_number,
l_blanket_line_number,
l_header_id
FROM oe_order_lines
WHERE line_id = l_line_id;
SELECT * INTO l_line_rec
FROM oe_order_lines
WHERE line_id = l_line_id;
l_payment_type_tab.delete;
SELECT V.payment_type
BULK COLLECT INTO l_payment_type_tab
FROM
(Select payment_type_code payment_type
FROM OE_PAYMENTS
WHERE header_id=l_header_rec.header_id
AND line_id IS NULL
UNION
SELECT payment_type_code payment_type
FROM OE_ORDER_HEADERS_ALL
WHERE header_id=l_header_rec.header_id) V;
l_payment_type_tab.delete;
Select payment_type_code payment_type
BULK COLLECT INTO l_payment_type_tab
FROM OE_PAYMENTS
WHERE header_id=l_header_rec.header_id
AND line_id IS NOT NULL;
select mic.category_id
into l_entity_tab(m_counter).entity_id
from mtl_item_categories mic,
mtl_default_category_sets mdc
where mic.inventory_item_id = l_line_rec.inventory_item_id
AND mic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
AND mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id;
-- If it is a Bill to Header Level Credit Hold update the level so that
-- appropriate message can be displayed
elsif (l_credithold_cust='BTH' and l_hold_entity_id = l_bill_to_orgid) then
l_ch_level := 'BTH';
SELECT 'Y'
INTO l_hold_exists
FROM OE_HOLD_SOURCES_ALL
WHERE hold_entity_code = p_hold_entity_code
AND HOLD_ENTITY_ID = p_hold_entity_id
AND hold_id = p_hold_id
AND nvl(RELEASED_FLAG, 'N') = 'N'
AND ORG_ID is null;
SELECT 'Y'
INTO l_hold_exists
FROM OE_HOLD_SOURCES_ALL
WHERE hold_entity_code = p_hold_entity_code
AND HOLD_ENTITY_ID = p_hold_entity_id
AND hold_id = p_hold_id
AND nvl(RELEASED_FLAG, 'N') = 'N'
AND ORG_ID = p_org_id;
SELECT DISTINCT org_id
FROM HZ_CUST_ACCT_SITES_ALL acct
WHERE acct.cust_account_id = p_hold_entity_id
AND NOT EXISTS (SELECT 1
FROM OE_HOLD_SOURCES_ALL src
WHERE acct.cust_account_id = src.hold_entity_id
AND src.hold_entity_code = 'C'
AND acct.org_id = src.org_id
AND src.hold_id = p_hold_id
AND nvl(src.released_flag, 'N') = 'N')
UNION
SELECT DISTINCT org_id
FROM OE_ORDER_HEADERS_ALL hdr
WHERE sold_to_org_id = p_hold_entity_id
AND NOT EXISTS (select 1
from oe_hold_sources_all
where hold_entity_id = p_hold_entity_id
and hold_entity_code = 'C'
and hold_id = p_hold_id
and nvl(RELEASED_FLAG, 'N') = 'N')
AND NOT EXISTS (select 1
from HZ_CUST_ACCT_SITES_ALL hzcas
where hzcas.cust_account_id = p_hold_entity_id
and hzcas.cust_account_id = hdr.sold_to_org_id);
SELECT org_id, hold_source_id
FROM OE_HOLD_SOURCES_ALL
WHERE hold_entity_id = p_hold_entity_id
AND hold_entity_code = 'C'
AND nvl(released_flag,'N') = 'N';
SELECT org_id into l_org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_hold_entity_id;
SELECT 'Y'
FROM HZ_CUST_ACCOUNTS_ALL
WHERE cust_account_id = p_hold_entity_id;