[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning
from oe_lookups
where LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
and LOOKUP_CODE = p_hold_entity_code
and rownum = 1;
select order_number
from oe_order_headers
where header_id = p_hold_entity_id
and rownum = 1;
select concatenated_segments
from mtl_system_items_kfv
where inventory_item_id = p_hold_entity_id;
select customer_name
from ra_customers
where customer_id = p_hold_entity_id;
select substrb(party.party_name,1,50) customer_name
from hz_parties party, hz_cust_accounts cust_acct
where party.party_id = cust_acct.party_id
AND cust_acct.cust_account_id = p_hold_entity_id;
select name
from oe_ship_to_orgs_v
where ORGANIZATION_ID = p_hold_entity_id;
select name
from oe_invoice_to_orgs_v
where ORGANIZATION_ID = p_hold_entity_id;
select name
from oe_ship_from_orgs_v
where ORGANIZATION_ID = p_hold_entity_id;
select name
from oe_deliver_to_orgs_v
where ORGANIZATION_ID = p_hold_entity_id;
SELECT name
FROM oe_payment_types_vl
WHERE payment_type_code = p_hold_entity_id;
select name
from ra_terms
WHERE term_id = p_hold_entity_id;
select name
from qp_list_headers_vl
WHERE list_header_id = p_hold_entity_id;
select name
from oe_transaction_types
WHERE transaction_type_id = p_hold_entity_id;
select meaning
from oe_lookups
WHERE lookup_code= p_hold_entity_id
AND lookup_type = 'SOURCE_TYPE';
select meaning
from oe_ship_methods_v
WHERE lookup_code= p_hold_entity_id
AND lookup_type = 'SHIP_METHOD';
select name
from fnd_currencies_vl
WHERE currency_code = p_hold_entity_id;
select name
from ra_salesreps
WHERE salesrep_id = p_hold_entity_id;
select meaning
from oe_lookups
WHERE lookup_code= p_hold_entity_id
AND lookup_type = 'SALES_CHANNEL';
select PROJECT_NAME
from PJM_PROJECTS_ORG_OU_SECURE_V
WHERE PROJECT_ID = p_hold_entity_id;
select TASK_NAME
from PJM_TASKS_OU_V
WHERE TASK_ID = p_hold_entity_id;
select user_name
from fnd_user
WHERE user_id = p_hold_entity_id;
SELECT CONCATENATED_SEGMENTS
FROM MTL_CATEGORIES_KFV
WHERE category_id = p_hold_entity_id;
select substrb(location,1,50) location
FROM HZ_CUST_SITE_USES_ALL
WHERE status='A'
AND SITE_USE_CODE in ('SHIP_TO','SOLD_TO','DELIVER_TO','BILL_TO')
AND SITE_USE_ID = p_hold_entity_id;
select hd.name
into l_hold_name
from oe_hold_sources hs,
oe_hold_definitions hd
where hs.HOLD_SOURCE_ID = p_hold_source_id
and hs.hold_id = hd.hold_id;
select USER_NAME
into l_user_name
from fnd_user
where USER_ID = p_user_id;
SELECT wpa_to.process_name || ':' || wpa_to.activity_name full_activity_name,
wias_to.item_type,
wias_to.item_key
FROM wf_item_activity_statuses wias_to,
wf_process_activities wpa_to,
wf_activities wa,
wf_item_activity_statuses wias_from,
wf_process_activities wpa_from,
wf_activity_transitions wat,
wf_items wi
WHERE wpa_to.instance_id= wias_to.process_activity
AND wat.to_process_activity = wpa_to.instance_id
AND wat.result_code = 'ON_HOLD'
AND wias_from.process_activity = wat.from_process_activity
AND wpa_from.instance_id = wias_from.process_activity
AND wpa_from.activity_name = p_activity_name -- 8284926
AND wias_from.activity_result_code = 'ON_HOLD'
AND wias_from.end_date IS NOT NULL
AND wias_from.item_type = 'OEOL'
AND wi.parent_item_key = To_Char(p_header_id)
AND wa.item_type = wias_to.item_type
AND wa.NAME = wpa_to.activity_name
AND wa.FUNCTION = 'OE_STANDARD_WF.STANDARD_BLOCK'
AND wa.end_date IS NULL
AND wias_to.end_date IS NULL
AND wias_to.activity_status = 'NOTIFIED'
AND wias_to.item_type = wias_from.item_type
AND wias_to.item_key = wias_from.item_key
AND wi.item_type = wias_to.item_type
AND wias_to.item_key = wi.item_key;
SELECT activity_name
INTO l_hold_activity
FROM oe_hold_definitions
WHERE hold_id = p_hold_id;
SELECT 'Y'
INTO l_release_children
FROM oe_order_holds oh,
oe_hold_sources hs,
oe_hold_definitions hd
WHERE hs.hold_source_id = oh.hold_source_id
AND hs.hold_id = hd.hold_id
AND hd.hold_id = p_hold_id
AND oh.header_id = p_order_tbl(1).header_id
AND oh.line_id IS NULL
AND hs.hold_entity_code = 'O'
AND hs.hold_entity_id = p_order_tbl(1).header_id
AND NVL(hd.item_type, 'INVALID') = 'OEOL'
AND hd.activity_name IS NOT NULL
AND NVL(hd.apply_to_order_and_line_flag,'N') = 'Y';
/* This select statement will pick up the activity which fulfills
the following criteria :
1. Activity is based on a OE_STANDARD_WF.STANDARD_BLOCK function
2. Activity is in a 'NOTIFIED' status
3. Activity has been reached via a transition of 'ON_HOLD'
4. For a given item_type and item_key
*/
l_found := 'T';
SELECT wpa_to.process_name || ':' || wpa_to.activity_name,
wias_to.item_type
INTO l_activity, l_item_type
FROM wf_item_activity_statuses wias_to,
wf_process_activities wpa_to,
wf_activities wa,
wf_item_activity_statuses wias_from,
wf_process_activities wpa_from,
wf_activity_transitions wat
WHERE wpa_to.instance_id= wias_to.process_activity
AND wat.to_process_activity = wpa_to.instance_id
AND wat.result_code = 'ON_HOLD'
AND wias_from.process_activity = wat.from_process_activity
AND wpa_from.instance_id = wias_from.process_activity
AND wpa_from.activity_name in ('REPRICE', 'REPRICE_LINE') -- 16626753
AND wias_from.activity_result_code = 'ON_HOLD'
AND wias_from.end_date IS NOT NULL
AND wias_from.item_type = DECODE(p_order_tbl(i).line_id, NULL, 'OEOH', 'OEOL')
AND wias_from.item_key = To_Char(NVL(p_order_tbl(i).line_id,p_order_tbl(i).header_id))
AND wa.item_type = wias_to.item_type
AND wa.NAME = wpa_to.activity_name
AND wa.FUNCTION = 'OE_STANDARD_WF.STANDARD_BLOCK'
AND wa.end_date IS NULL
AND wias_to.end_date IS NULL
AND wias_to.activity_status = 'NOTIFIED'
AND wias_to.item_type = wias_from.item_type
AND wias_to.item_key = wias_from.item_key;
SELECT wpa_to.process_name || ':' || wpa_to.activity_name,
wias_to.item_type
INTO l_activity, l_item_type
FROM wf_item_activity_statuses wias_to,
wf_process_activities wpa_to,
wf_activities wa,
wf_item_activity_statuses wias_from,
wf_process_activities wpa_from,
wf_activity_transitions wat
WHERE wpa_to.instance_id= wias_to.process_activity
AND wat.to_process_activity = wpa_to.instance_id
AND wat.result_code = 'ON_HOLD'
AND wias_from.process_activity = wat.from_process_activity
AND wpa_from.instance_id = wias_from.process_activity
AND wpa_from.activity_name = l_hold_activity -- 8284926
AND wias_from.activity_result_code = 'ON_HOLD'
AND wias_from.end_date IS NOT NULL
AND wias_from.item_type = DECODE(p_order_tbl(i).line_id, NULL, 'OEOH', 'OEOL')
AND wias_from.item_key = To_Char(NVL(p_order_tbl(i).line_id,p_order_tbl(i).header_id))
AND wa.item_type = wias_to.item_type
AND wa.NAME = wpa_to.activity_name
AND wa.FUNCTION = 'OE_STANDARD_WF.STANDARD_BLOCK'
AND wa.end_date IS NULL
AND wias_to.end_date IS NULL
AND wias_to.activity_status = 'NOTIFIED'
AND wias_to.item_type = wias_from.item_type
AND wias_to.item_key = wias_from.item_key;
SELECT wpa_to.process_name || ':' || wpa_to.activity_name,
wias_to.item_type
INTO l_activity, l_item_type
FROM wf_item_activity_statuses wias_to,
wf_process_activities wpa_to,
wf_activities wa,
wf_item_activity_statuses_h wias_from,
wf_process_activities wpa_from,
wf_activity_transitions wat
WHERE wpa_to.instance_id= wias_to.process_activity
AND wat.to_process_activity = wpa_to.instance_id
AND wat.result_code = 'ON_HOLD'
AND wias_from.process_activity = wat.from_process_activity
AND wpa_from.instance_id = wias_from.process_activity
AND wpa_from.activity_name = l_hold_activity -- 8284926
AND wias_from.activity_result_code = 'ON_HOLD'
AND wias_from.end_date = wias_to.begin_date
AND wias_from.item_type = DECODE(p_order_tbl(i).line_id, NULL, 'OEOH', 'OEOL')
AND wias_from.item_key = To_Char(NVL(p_order_tbl(i).line_id,p_order_tbl(i).header_id))
AND wa.item_type = wias_to.item_type
AND wa.NAME = wpa_to.activity_name
AND wa.FUNCTION = 'OE_STANDARD_WF.STANDARD_BLOCK'
AND wa.end_date IS NULL
AND wias_to.end_date IS NULL
AND wias_to.activity_status = 'NOTIFIED'
AND wias_to.item_type = wias_from.item_type
AND wias_to.item_key = wias_from.item_key;
SELECT NVL(line_id, header_id) entity_id,
DECODE(line_id , NULL, 'OEOH', 'OEOL') entity_type
FROM oe_order_holds oh, oe_hold_sources hs
WHERE hs.hold_source_id = p_hold_source_id
AND oh.hold_release_id = hs.hold_release_id
AND oh.released_flag = 'Y';
SELECT hd.activity_name
INTO l_hold_activity
FROM oe_hold_definitions hd, oe_hold_sources hs
WHERE hd.hold_id = hs.hold_id
AND hs.hold_source_id = p_hold_source_id;
SELECT hold_entity_code
INTO l_hold_entity_code
FROM oe_hold_sources
WHERE hold_source_id = p_hold_source_id;
/* This select statement will pick up the activity which fulfills
the following criteria :
1. Activity is based on a OE_STANDARD_WF.STANDARD_BLOCK function
2. Activity is in a 'NOTIFIED' status
3. Activity has been reached via a transition of 'ON_HOLD'
4. For a given item_type and item_key
*/
l_found := 'T';
SELECT wpa_to.process_name || ':' || wpa_to.activity_name,
wias_to.item_type
INTO l_activity, l_item_type
FROM wf_item_activity_statuses wias_to,
wf_process_activities wpa_to,
wf_activities wa,
wf_item_activity_statuses wias_from,
wf_process_activities wpa_from,
wf_activity_transitions wat
WHERE wpa_to.instance_id= wias_to.process_activity
AND wat.to_process_activity = wpa_to.instance_id
AND wat.result_code = 'ON_HOLD'
AND wias_from.process_activity = wat.from_process_activity
AND wpa_from.instance_id = wias_from.process_activity
AND wpa_from.activity_name = l_hold_activity -- 8284926
AND wias_from.activity_result_code = 'ON_HOLD'
AND wias_from.end_date IS NOT NULL
AND wias_from.item_type = x.entity_type
AND wias_from.item_key = To_Char(x.entity_id)
AND wa.item_type = wias_to.item_type
AND wa.NAME = wpa_to.activity_name
AND wa.FUNCTION = 'OE_STANDARD_WF.STANDARD_BLOCK'
AND wa.end_date IS NULL
AND wias_to.end_date IS NULL
AND wias_to.activity_status = 'NOTIFIED'
AND wias_to.item_type = wias_from.item_type
AND wias_to.item_key = wias_from.item_key;
SELECT wpa_to.process_name || ':' || wpa_to.activity_name,
wias_to.item_type
INTO l_activity, l_item_type
FROM wf_item_activity_statuses wias_to,
wf_process_activities wpa_to,
wf_activities wa,
wf_item_activity_statuses_h wias_from,
wf_process_activities wpa_from,
wf_activity_transitions wat
WHERE wpa_to.instance_id= wias_to.process_activity
AND wat.to_process_activity = wpa_to.instance_id
AND wat.result_code = 'ON_HOLD'
AND wias_from.process_activity = wat.from_process_activity
AND wpa_from.instance_id = wias_from.process_activity
AND wpa_from.activity_name = l_hold_activity -- 8284926
AND wias_from.activity_result_code = 'ON_HOLD'
AND wias_from.end_date = wias_to.begin_date
AND wias_from.item_type = x.entity_type
AND wias_from.item_key = To_Char(x.entity_id)
AND wa.item_type = wias_to.item_type
AND wa.NAME = wpa_to.activity_name
AND wa.FUNCTION = 'OE_STANDARD_WF.STANDARD_BLOCK'
AND wa.end_date IS NULL
AND wias_to.end_date IS NULL
AND wias_to.activity_status = 'NOTIFIED'
AND wias_to.item_type = wias_from.item_type
AND wias_to.item_key = wias_from.item_key;
select 'Y'
Into l_auth_exists
from oe_hold_authorizations
where hold_id = p_hold_id
and authorized_action_code = p_authorized_action_code
and rownum = 1;
select 'x'
into l_dummy
from oe_hold_authorizations
where sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and authorized_action_code = p_authorized_action_code
and responsibility_id = p_responsibility_id
and application_id = p_application_id
and hold_id = p_hold_id
and rownum = 1;
Procedure InsertTable_OOH_Header (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type
,p_header_id OE_ORDER_HEADERS_ALL.header_id%type
,p_org_id OE_ORDER_HEADERS_ALL.org_id%type
,p_hold_entity_where_clause VARCHAR2
,p_item_type VARCHAR2
,p_activity_name VARCHAR2
,p_activity_status VARCHAR2
,p_additional_where_clause VARCHAR2
,x_is_hold_applied OUT NOCOPY BOOLEAN)
IS
l_user_id NUMBER := OE_HOLDS_PVT.get_user_id;
OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Header');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, NULL
, ''N''
, h.org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id
and h.header_id = :header_id
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id IS NULL
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(h.header_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status
and nvl(activity_result_code, :l_activity_result)
NOT IN (:l_result_1, :l_result_2))'; --9538334
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, NULL
, ''N''
, h.org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id IS NULL
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(h.header_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status
and nvl(activity_result_code, :l_activity_result)
NOT IN (:l_result_1, :l_result_2))'; --9538334
SELECT meaning
INTO l_user_activity_name
FROM oe_lookups
WHERE lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
AND lookup_code = p_activity_name;
l_wf_sqlmt := 'SELECT count(*)
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = ''Y''
AND nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
AND NOT EXISTS ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id = '||p_hold_source_id||' )';
SELECT meaning
INTO l_user_activity_name
FROM oe_lookups
WHERE lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
AND lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Header Successfully');
OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Header with Error:'||SQLCODE);
END InsertTable_OOH_Header;
Procedure InsertTable_OOH_Line (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type,
p_line_id OE_ORDER_LINES_ALL.line_id%type,
p_org_id OE_ORDER_HEADERS_ALL.org_id%type,
p_hold_entity_where_clause VARCHAR2,
p_item_type VARCHAR2,
p_activity_name VARCHAR2,
p_activity_status VARCHAR2,
p_additional_where_clause VARCHAR2,
x_is_hold_applied OUT NOCOPY BOOLEAN)
IS
l_user_id NUMBER := OE_HOLDS_PVT.get_user_id;
OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Line');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id
and ol.line_id = :line_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id = :hold_source_id )';
l_sqlmt :=l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status
and nvl(activity_result_code, :l_activity_result)
NOT IN (:l_result_1, :l_result_2))'; --9538334
l_sqlmt :=l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt :=l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt :=l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status
and nvl(activity_result_code, :l_activity_result)
NOT IN (:l_result_1, :l_result_2))'; --9538334
l_sqlmt :=l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt :=l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
SELECT meaning
INTO l_user_activity_name
FROM oe_lookups
WHERE lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
AND lookup_code = p_activity_name;
l_wf_sqlmt := 'SELECT count(*)
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
AND h.header_id = ol.header_id
AND ol.OPEN_FLAG = ''Y''
AND NVL(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
AND NOT EXISTS ( SELECT ''x''
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id = '||p_hold_source_id||' )';
SELECT meaning
INTO l_user_activity_name
FROM oe_lookups
WHERE lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
AND lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Line Successfully');
OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Line with Error:'||SQLCODE);
END InsertTable_OOH_Line;
l_eligible_record_tab.delete;
l_sqlmt := 'Select line_id
FROM OE_PAYMENTS OP
WHERE line_id IS NOT NULL
AND PAYMENT_TYPE_CODE =:hold_entity_id';
l_sqlmt := 'Select OP.line_id
FROM OE_PAYMENTS OP,OE_ORDER_LINES_ALL OL
WHERE OP.line_id IS NOT NULL
AND OP.line_id= OL.line_id
AND OL.sold_to_org_id= :hold_entity_id
AND OP.PAYMENT_TYPE_CODE = :hold_entity_id2';
l_sqlmt := 'Select OP.line_id
FROM OE_PAYMENTS OP,OE_ORDER_LINES_ALL OL
WHERE OP.line_id IS NOT NULL
AND OP.line_id= OL.line_id
AND OL.end_customer_id= :hold_entity_id
AND OP.PAYMENT_TYPE_CODE = :hold_entity_id2';
oe_debug_pub.add('Calling InsertTable_OOH_Line for P for line_id:'||l_eligible_record_tab(i));
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => l_eligible_record_tab(i)
,p_org_id => p_org_id
,p_hold_entity_where_clause => NULL
,p_item_type => p_item_type
,p_activity_name => p_activity_name
,p_activity_status => p_activity_status
,p_additional_where_clause => p_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
oe_debug_pub.add('After Calling InsertTable_OOH_Line for P');
l_eligible_record_tab.delete;
l_sqlmt := 'Select header_id
FROM OE_PAYMENTS OP
WHERE line_id IS NULL
AND PAYMENT_TYPE_CODE = :hold_entity_id';
l_sqlmt1 := ' UNION Select header_id
FROM OE_ORDER_HEADERS_ALL OH
WHERE PAYMENT_TYPE_CODE = :hold_entity_id
AND header_id = :header_id';
l_sqlmt1 := ' UNION Select header_id
FROM OE_ORDER_HEADERS_ALL OH
WHERE PAYMENT_TYPE_CODE = :hold_entity_id';
l_sqlmt := 'Select OP.header_id
FROM OE_PAYMENTS OP,OE_ORDER_HEADERS_ALL OH
WHERE OP.line_id IS NULL
AND OP.header_id= OH.header_id
AND OH.sold_to_org_id= :hold_entity_id
AND OP.PAYMENT_TYPE_CODE = :hold_entity_id2';
l_sqlmt1 := ' UNION Select header_id
FROM OE_ORDER_HEADERS_ALL OH
WHERE sold_to_org_id= :hold_entity_id
AND PAYMENT_TYPE_CODE = :hold_entity_id2
AND header_id = :header_id';
l_sqlmt1 := ' UNION Select header_id
FROM OE_ORDER_HEADERS_ALL OH
WHERE sold_to_org_id= :hold_entity_id
AND PAYMENT_TYPE_CODE = :hold_entity_id2';
oe_debug_pub.add('Calling InsertTable_OOH_Header for P for header_id:'||l_eligible_record_tab(i));
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>l_eligible_record_tab(i)
,p_org_id => p_org_id
,p_hold_entity_where_clause => NULL
,p_item_type => p_item_type
,p_activity_name => p_activity_name
,p_activity_status => p_activity_status
,p_additional_where_clause => p_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
oe_debug_pub.add('After Calling InsertTable_OOH_Header for P');
SELECT HS.HOLD_SOURCE_ID,
HS.HOLD_ENTITY_CODE,
hs.hold_id,
oh.order_hold_id
FROM OE_HOLD_SOURCES HS,
OE_ORDER_HOLDS oh
WHERE HS.HOLD_ID = nvl(p_hold_source_rec.hold_id, HS.HOLD_ID)
and hs.hold_entity_code = nvl(p_hold_source_rec.hold_entity_code, hs.hold_entity_code)
and hs.hold_entity_id = nvl(p_hold_source_rec.hold_entity_id, hs.hold_entity_id)
and hs.HOLD_SOURCE_ID = oh.HOLD_SOURCE_ID
and oh.HEADER_ID = p_order_rec.header_id
and nvl(oh.LINE_ID, -99 ) = nvl(p_order_rec.line_id, -99)
AND OH.RELEASED_FLAG = 'N';
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_order_holds ooh,
oe_hold_sources ohs,
oe_hold_definitions ohd,
oe_order_headers_all h,
oe_order_sources oos
WHERE ooh.header_id = h.header_id
-- AND ohd.activity_name IS NULL Bug 6791587
AND oos.aia_enabled_flag = 'Y'
AND ohd.hold_id = ohs.hold_id
AND ohs.hold_source_id = ooh.hold_source_id
AND ooh.order_hold_id = p_ord_hld_id);
select ohld.header_id,
ohld.line_id
from oe_order_holds ohld,
oe_order_headers ooh,
oe_order_sources src
where ohld.order_hold_id = p_ord_hld_id
and ohld.header_id = ooh.header_id
and src.order_source_id = ooh.order_source_id
and src.aia_enabled_flag = 'Y';
-- Releasing only orders from hold. Insert a record in the
-- OE_HOLD_RELEASES with hold_source_id as null.
OE_Debug_PUB.Add('Releasing Orders from Hold',1);
SELECT OE_HOLD_RELEASES_S.NEXTVAL
INTO x_hold_release_id
FROM DUAL;
/*Bug3042838 Added nvl condition for insertion into CREATED_BY column */
INSERT INTO OE_HOLD_RELEASES
( HOLD_RELEASE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_SOURCE_ID
, RELEASE_REASON_CODE
, RELEASE_COMMENT
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORDER_HOLD_ID
)
VALUES
( x_hold_release_id
, sysdate
, nvl(p_hold_release_rec.CREATED_BY,l_user_id)
, sysdate
, l_user_id
, p_hold_release_rec.LAST_UPDATE_LOGIN
, p_hold_release_rec.PROGRAM_APPLICATION_ID
, p_hold_release_rec.PROGRAM_ID
, p_hold_release_rec.PROGRAM_UPDATE_DATE
, p_hold_release_rec.REQUEST_ID
, NULL -- HOLD_SOURCE_ID
, p_hold_release_rec.RELEASE_REASON_CODE
, p_hold_release_rec.RELEASE_COMMENT
, p_hold_release_rec.CONTEXT
, p_hold_release_rec.ATTRIBUTE1
, p_hold_release_rec.ATTRIBUTE2
, p_hold_release_rec.ATTRIBUTE3
, p_hold_release_rec.ATTRIBUTE4
, p_hold_release_rec.ATTRIBUTE5
, p_hold_release_rec.ATTRIBUTE6
, p_hold_release_rec.ATTRIBUTE7
, p_hold_release_rec.ATTRIBUTE8
, p_hold_release_rec.ATTRIBUTE9
, p_hold_release_rec.ATTRIBUTE10
, p_hold_release_rec.ATTRIBUTE11
, p_hold_release_rec.ATTRIBUTE12
, p_hold_release_rec.ATTRIBUTE13
, p_hold_release_rec.ATTRIBUTE14
, p_hold_release_rec.ATTRIBUTE15
, l_order_hold_id
);
UPDATE oe_order_holds
SET hold_release_id = x_hold_release_id,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = sysdate,
RELEASED_FLAG = 'Y'
WHERE ORDER_HOLD_ID = l_order_hold_id;
SELECT ohld.header_id, ohld.line_id
INTO l_header_id, l_line_id
FROM oe_order_holds ohld,
oe_order_headers ooh
WHERE ohld.order_hold_id = l_order_hold_id
AND ohld.header_id = ooh.header_id;
select
item_type
, nvl(apply_to_order_and_line_flag, 'N')
from oe_hold_definitions
where hold_id = l_hold_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_hold_source_rec.hold_entity_id
-- QUOTING change
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_sold_to_orgs_v
WHERE ORGANIZATION_ID = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM RA_SITE_USES
WHERE site_use_id = p_hold_source_rec.hold_entity_id
AND site_use_code = 'SHIP_TO'
AND STATUS='A';*/
SELECT 'Valid Entity'
INTO l_dummy
FROM hz_cust_site_uses -- Bug 2138398
WHERE site_use_id = p_hold_source_rec.hold_entity_id
AND site_use_code = 'SHIP_TO'
AND STATUS='A';
SELECT 'Valid Entity'
INTO l_dummy
FROM RA_SITE_USES
WHERE site_use_id = p_hold_source_rec.hold_entity_id
AND site_use_code = 'BILL_TO'
AND STATUS='A';*/
SELECT 'Valid Entity'
INTO l_dummy
FROM hz_cust_site_uses -- Bug 2138398
WHERE site_use_id = p_hold_source_rec.hold_entity_id
AND site_use_code = 'BILL_TO'
AND STATUS='A';
SELECT 'Valid Entity'
INTO l_dummy
FROM hz_cust_site_uses
WHERE site_use_id = p_hold_source_rec.hold_entity_id
AND site_use_code = 'DELIVER_TO'
AND STATUS='A';
SELECT 'Valid Entity'
INTO l_dummy
from mtl_system_items_kfv
where inventory_item_id = p_hold_source_rec.hold_entity_id;
select 'Valid Entity'
into l_dummy
from mtl_categories
where category_id = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
from oe_ship_from_orgs_v
where ORGANIZATION_id = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_blanket_headers
WHERE ORDER_NUMBER = p_hold_source_rec.hold_entity_id
AND SALES_DOCUMENT_TYPE_CODE = 'B';
SELECT 'Valid Entity'
INTO l_dummy
from mtl_system_items_kfv
where inventory_item_id = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM PJM_PROJECTS_ORG_OU_SECURE_V
WHERE PROJECT_ID = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM qp_list_headers_vl
WHERE list_header_id = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_transaction_types
WHERE transaction_type_id = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_payment_types_vl
WHERE payment_type_code = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_lookups
WHERE lookup_code = p_hold_source_rec.hold_entity_id
AND lookup_type = 'SALES_CHANNEL';
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_payment_types_vl
WHERE payment_type_code = p_hold_source_rec.hold_entity_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_ship_methods_v
WHERE lookup_code= p_hold_source_rec.hold_entity_id
AND lookup_type = 'SHIP_METHOD';
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_hold_source_rec.hold_entity_id2
-- QUOTING change
AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_sold_to_orgs_v
WHERE ORGANIZATION_ID = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM RA_SITE_USES
WHERE SITE_USE_ID = p_hold_source_rec.hold_entity_id2
AND site_use_code = 'SHIP_TO';*/
SELECT 'Valid Entity'
INTO l_dummy
FROM HZ_CUST_SITE_USES_ALL
WHERE status='A'
AND SITE_USE_CODE in ('SHIP_TO','SOLD_TO','DELIVER_TO','BILL_TO')
AND SITE_USE_ID = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM hz_cust_site_uses -- Bug 2138398
WHERE SITE_USE_ID = p_hold_source_rec.hold_entity_id2
AND site_use_code = 'SHIP_TO';
SELECT 'Valid Entity'
INTO l_dummy
FROM RA_SITE_USES
WHERE SITE_USE_ID = p_hold_source_rec.hold_entity_id2
AND site_use_code = 'BILL_TO';*/
SELECT 'Valid Entity'
INTO l_dummy
FROM hz_cust_site_uses -- Bug 2138398
WHERE SITE_USE_ID = p_hold_source_rec.hold_entity_id2
AND site_use_code = 'BILL_TO';
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_blanket_headers
WHERE ORDER_NUMBER = p_hold_source_rec.hold_entity_id2
AND SALES_DOCUMENT_TYPE_CODE = 'B';
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_blanket_lines_ext
WHERE ORDER_NUMBER = p_hold_source_rec.hold_entity_id
AND LINE_NUMBER = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
from oe_ship_from_orgs_v
where ORGANIZATION_id = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM fnd_user
WHERE user_id = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM hz_cust_site_uses
WHERE SITE_USE_ID = p_hold_source_rec.hold_entity_id2
AND site_use_code = 'DELIVER_TO';
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_transaction_types
WHERE transaction_type_id = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
from mtl_system_items_kfv
where inventory_item_id = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM ra_terms
WHERE term_id = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_payment_types_vl
WHERE payment_type_code = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
from qp_list_headers_vl
where list_header_id = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM PJM_PROJECTS_ORG_OU_SECURE_V
WHERE PROJECT_ID = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_lookups
WHERE lookup_code = p_hold_source_rec.hold_entity_id2
AND lookup_type = 'SALES_CHANNEL';
SELECT 'Valid Entity'
INTO l_dummy
from oe_ship_methods_v
where lookup_code = p_hold_source_rec.hold_entity_id2
AND lookup_type = 'SHIP_METHOD';
SELECT 'Valid Entity'
INTO l_dummy
FROM oe_lookups
WHERE lookup_code = p_hold_source_rec.hold_entity_id2
AND lookup_type = 'SOURCE_TYPE';
SELECT 'Valid Entity'
INTO l_dummy
FROM PJM_TASKS_OU_V
WHERE TASK_ID = p_hold_source_rec.hold_entity_id2;
SELECT 'Valid Entity'
INTO l_dummy
from fnd_currencies_vl
where currency_code = p_hold_source_rec.hold_entity_id2;
SELECT 'x'
INTO l_dummy
FROM OE_HOLD_DEFINITIONS
WHERE HOLD_ID = p_hold_id
AND SYSDATE
BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
AND NVL(END_DATE_ACTIVE, SYSDATE );
select count(*)
into l_count
--ER#7479609 FROM OE_HOLD_SOURCES HS
FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND 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.RELEASED_FLAG = 'N'
AND HS.org_id= p_org_id --ER#7479609
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND EXISTS ( select 'x'
--ER#7479609 from oe_order_holds OH
from oe_order_holds_all OH --ER#7479609
where OH.line_id = p_hold_source_rec.line_id
and OH.org_id= p_org_id --ER#7479609
and OH.hold_source_id = HS.hold_source_id);
select count(*)
into l_count
--ER#7479609 FROM OE_HOLD_SOURCES HS
FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND 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.RELEASED_FLAG = 'N'
AND HS.org_id= p_org_id --ER#7479609
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND EXISTS ( select 'x'
--ER#7479609 from oe_order_holds OH
from oe_order_holds_all OH --ER#7479609
where OH.line_id is null
and OH.org_id= p_org_id --ER#7479609
and OH.hold_source_id = HS.hold_source_id);
select count(*)
into l_count
--ER#7479609 FROM OE_HOLD_SOURCES HS
FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND 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.org_id= p_org_id --ER#7479609
AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
AND nvl(HS.HOLD_ENTITY_ID2, -99) =
nvl(p_hold_source_rec.hold_entity_id2,-99 )
AND HS.RELEASED_FLAG = 'N'
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
SELECT OE_HOLD_SOURCES_S.NEXTVAL
INTO x_hold_source_id
FROM DUAL;
INSERT INTO OE_HOLD_SOURCES_ALL
( HOLD_SOURCE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_ID
, HOLD_ENTITY_CODE
, HOLD_ENTITY_ID
, HOLD_UNTIL_DATE
, RELEASED_FLAG
, HOLD_COMMENT
, ORG_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, HOLD_RELEASE_ID
,HOLD_ENTITY_CODE2
,HOLD_ENTITY_ID2
)
VALUES
( x_hold_source_id
, sysdate
, l_user_id
, sysdate
, l_user_id
, p_hold_source_rec.LAST_UPDATE_LOGIN
, p_hold_source_rec.PROGRAM_APPLICATION_ID
, p_hold_source_rec.PROGRAM_ID
, p_hold_source_rec.PROGRAM_UPDATE_DATE
, p_hold_source_rec.REQUEST_ID
, p_hold_source_rec.HOLD_ID
, p_hold_source_rec.HOLD_ENTITY_CODE
, p_hold_source_rec.HOLD_ENTITY_ID
, p_hold_source_rec.HOLD_UNTIL_DATE
, 'N'
, p_hold_source_rec.HOLD_COMMENT
, p_org_id --ER#7479609 l_org_id
, p_hold_source_rec.CONTEXT
, p_hold_source_rec.ATTRIBUTE1
, p_hold_source_rec.ATTRIBUTE2
, p_hold_source_rec.ATTRIBUTE3
, p_hold_source_rec.ATTRIBUTE4
, p_hold_source_rec.ATTRIBUTE5
, p_hold_source_rec.ATTRIBUTE6
, p_hold_source_rec.ATTRIBUTE7
, p_hold_source_rec.ATTRIBUTE8
, p_hold_source_rec.ATTRIBUTE9
, p_hold_source_rec.ATTRIBUTE10
, p_hold_source_rec.ATTRIBUTE11
, p_hold_source_rec.ATTRIBUTE12
, p_hold_source_rec.ATTRIBUTE13
, p_hold_source_rec.ATTRIBUTE14
, p_hold_source_rec.ATTRIBUTE15
, p_hold_source_rec.HOLD_RELEASE_ID
, p_hold_source_rec.HOLD_ENTITY_CODE2
, p_hold_source_rec.HOLD_ENTITY_ID2
);
select count(*)
into l_count
--ER#7479609 FROM OE_HOLD_SOURCES HS
FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND 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.RELEASED_FLAG = 'N'
AND HS.ORG_ID = p_org_id --ER#7479609
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND EXISTS ( select 'x'
--ER#7479609 from oe_order_holds OH
from oe_order_holds_all OH --ER#7479609
where OH.line_id = p_hold_source_rec.line_id
and OH.hold_source_id = HS.hold_source_id);
select count(*)
into l_count
--ER#7479609 FROM OE_HOLD_SOURCES HS
FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND 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.RELEASED_FLAG = 'N'
AND HS.ORG_ID = p_org_id --ER#7479609
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND EXISTS ( select 'x'
--ER#7479609 from oe_order_holds OH
from oe_order_holds_all OH --ER#7479609
where OH.line_id is null
and OH.hold_source_id = HS.hold_source_id);
select count(*)
into l_count
--ER#7479609 FROM OE_HOLD_SOURCES HS
FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
AND nvl(HS.HOLD_ENTITY_ID2, -99) =
nvl(p_hold_source_rec.hold_entity_id2,-99 )
AND HS.RELEASED_FLAG = 'N'
AND HS.ORG_ID = p_org_id --ER#7479609
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
SELECT OE_HOLD_SOURCES_S.NEXTVAL
INTO x_hold_source_id
FROM DUAL;
INSERT INTO OE_HOLD_SOURCES_ALL
( HOLD_SOURCE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_ID
, HOLD_ENTITY_CODE
, HOLD_ENTITY_ID
, HOLD_UNTIL_DATE
, RELEASED_FLAG
, HOLD_COMMENT
, ORG_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, HOLD_RELEASE_ID
,HOLD_ENTITY_CODE2
,HOLD_ENTITY_ID2
)
VALUES
( x_hold_source_id
, sysdate
, l_user_id
, sysdate
, l_user_id
, p_hold_source_rec.LAST_UPDATE_LOGIN
, p_hold_source_rec.PROGRAM_APPLICATION_ID
, p_hold_source_rec.PROGRAM_ID
, p_hold_source_rec.PROGRAM_UPDATE_DATE
, p_hold_source_rec.REQUEST_ID
, p_hold_source_rec.HOLD_ID
, p_hold_source_rec.HOLD_ENTITY_CODE
, p_hold_source_rec.HOLD_ENTITY_ID
, p_hold_source_rec.HOLD_UNTIL_DATE
, 'N'
, l_hold_comment --ER#7479609 p_hold_source_rec.HOLD_COMMENT
, p_org_id --ER#7479609 l_org_id
, p_hold_source_rec.CONTEXT
, p_hold_source_rec.ATTRIBUTE1
, p_hold_source_rec.ATTRIBUTE2
, p_hold_source_rec.ATTRIBUTE3
, p_hold_source_rec.ATTRIBUTE4
, p_hold_source_rec.ATTRIBUTE5
, p_hold_source_rec.ATTRIBUTE6
, p_hold_source_rec.ATTRIBUTE7
, p_hold_source_rec.ATTRIBUTE8
, p_hold_source_rec.ATTRIBUTE9
, p_hold_source_rec.ATTRIBUTE10
, p_hold_source_rec.ATTRIBUTE11
, p_hold_source_rec.ATTRIBUTE12
, p_hold_source_rec.ATTRIBUTE13
, p_hold_source_rec.ATTRIBUTE14
, p_hold_source_rec.ATTRIBUTE15
, p_hold_source_rec.HOLD_RELEASE_ID
, p_hold_source_rec.HOLD_ENTITY_CODE2
, p_hold_source_rec.HOLD_ENTITY_ID2
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_order_holds ooh,
oe_hold_sources ohs,
oe_hold_definitions ohd,
oe_order_headers_all h,
oe_order_sources oos
WHERE ohd.activity_name IS NULL
AND ohd.hold_id = ohs.hold_id
AND ooh.header_id = h.header_id
AND h.order_source_id = oos.order_source_id
AND oos.aia_enabled_flag = 'Y'
AND ohs.hold_source_id = ooh.hold_source_id
AND ooh.line_id = p_line_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_order_holds ooh,
oe_hold_sources ohs,
oe_hold_definitions ohd,
oe_order_headers_all h,
oe_order_sources oos
WHERE ohd.activity_name IS NULL
AND ohd.hold_id = ohs.hold_id
AND h.order_source_id = oos.order_source_id
AND oos.aia_enabled_flag = 'Y'
AND ooh.header_id = h.header_id
AND ohs.hold_source_id = ooh.hold_source_id
AND ooh.header_id = p_hdr_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_hold_sources ohs,
oe_hold_definitions ohd
WHERE ohd.activity_name IS NULL
AND ohd.hold_id = ohs.hold_id
AND ohs.hold_source_id = p_hld_src_id);
select item_type, activity_name
into l_wf_item_type, l_wf_activity_name
from oe_hold_definitions
where hold_id = p_hold_source_rec.hold_id;
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and h.header_id = ol.header_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
ra_terms_b rt
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = p_hold_source_rec.hold_entity_id2
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.line_id = p_hold_source_rec.line_id
and ol.OPEN_FLAG = 'Y'
and ol.PAYMENT_TERM_ID = rt.TERM_ID
and rt.CREDIT_CHECK_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
ra_terms_b rt
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = p_hold_source_rec.hold_entity_id2
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and ol.PAYMENT_TERM_ID = rt.TERM_ID
and rt.CREDIT_CHECK_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.line_id = p_hold_source_rec.line_id
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
AND h.header_id = ol.header_id
AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.line_id = p_hold_source_rec.line_id
AND ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
AND NOT EXISTS ( SELECT 'x'
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
AND h.header_id = ol.header_id
AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
AND ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
AND NOT EXISTS ( SELECT 'x'
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.line_id = p_hold_source_rec.line_id
AND h.header_id = ol.header_id
AND ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
AND NOT EXISTS ( SELECT 'x'
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
AND h.header_id = ol.header_id
AND ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
AND NOT EXISTS ( SELECT 'x'
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID= p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.BLANKET_LINE_NUMBER = p_hold_source_rec.hold_entity_id2
and ol.line_id = p_hold_source_rec.line_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.BLANKET_LINE_NUMBER = p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, NULL
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = p_hold_source_rec.hold_entity_id
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, p_hold_source_rec.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = p_hold_source_rec.hold_entity_id
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.open_flag = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, NULL
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = p_hold_source_rec.header_id
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, NULL
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, p_hold_source_rec.hold_source_id
, h.HEADER_ID
, ol.line_id
, 'N'
, l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
--and nvl(h.CANCELLED_FLAG, 'N') = 'N'
and h.header_id = ol.header_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_hold_sources ohs,
oe_hold_definitions ohd
WHERE ohd.hold_id = ohs.hold_id
-- AND ohd.activity_name IS NULL Bug 6791587
AND ohs.hold_source_id = p_hld_src_id
AND ohs.hold_entity_code <> 'O');
select ohld.header_id,
ohld.line_id
from oe_order_holds ohld,
oe_order_headers ooh,
oe_order_sources src
where ohld.order_hold_id = p_ord_hld_id
and ohld.header_id = ooh.header_id
and src.order_source_id = ooh.order_source_id
and src.aia_enabled_flag = 'Y';
UPDATE oe_order_holds
SET hold_release_id = p_hold_release_rec.hold_release_id,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = sysdate,
RELEASED_FLAG = 'Y'
WHERE hold_source_id = p_hold_release_rec.hold_source_id
AND hold_release_id IS NULL;
SELECT ohld.header_id,
ohld.line_id
INTO l_header_id,
l_line_id
FROM oe_order_holds ohld,
oe_order_headers ooh
WHERE ohld.order_hold_id = p_hold_release_rec.order_hold_id
AND ohld.header_id = ooh.header_id;
SAVEPOINT insert_hold_release;
SELECT 'x'
INTO l_dummy
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = 'RELEASE_REASON'
AND LOOKUP_CODE = p_hold_release_rec.release_reason_code;
SELECT OE_HOLD_RELEASES_S.NEXTVAL
INTO x_hold_release_id
FROM DUAL;
/*Bug3042838 Added nvl condition for insertion into CREATED_BY column */
INSERT INTO OE_HOLD_RELEASES
( HOLD_RELEASE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_SOURCE_ID
, RELEASE_REASON_CODE
, RELEASE_COMMENT
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
)
VALUES
( x_hold_release_id
, sysdate
, nvl(p_hold_release_rec.CREATED_BY,l_user_id)
, sysdate
, l_user_id
, p_hold_release_rec.LAST_UPDATE_LOGIN
, p_hold_release_rec.PROGRAM_APPLICATION_ID
, p_hold_release_rec.PROGRAM_ID
, p_hold_release_rec.PROGRAM_UPDATE_DATE
, p_hold_release_rec.REQUEST_ID
, p_hold_release_rec.HOLD_SOURCE_ID
, p_hold_release_rec.RELEASE_REASON_CODE
, p_hold_release_rec.RELEASE_COMMENT
, p_hold_release_rec.CONTEXT
, p_hold_release_rec.ATTRIBUTE1
, p_hold_release_rec.ATTRIBUTE2
, p_hold_release_rec.ATTRIBUTE3
, p_hold_release_rec.ATTRIBUTE4
, p_hold_release_rec.ATTRIBUTE5
, p_hold_release_rec.ATTRIBUTE6
, p_hold_release_rec.ATTRIBUTE7
, p_hold_release_rec.ATTRIBUTE8
, p_hold_release_rec.ATTRIBUTE9
, p_hold_release_rec.ATTRIBUTE10
, p_hold_release_rec.ATTRIBUTE11
, p_hold_release_rec.ATTRIBUTE12
, p_hold_release_rec.ATTRIBUTE13
, p_hold_release_rec.ATTRIBUTE14
, p_hold_release_rec.ATTRIBUTE15
);
UPDATE oe_hold_sources_all --ER#7479609
SET hold_release_id = x_hold_release_id
, released_flag = 'Y'
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_DATE = sysdate
WHERE hold_source_id = p_hold_release_rec.HOLD_SOURCE_ID;
ROLLBACK TO insert_hold_release;
ROLLBACK TO insert_hold_release;
,'Insert_Hold_Release');
ROLLBACK TO insert_hold_release;
SELECT HS.HOLD_SOURCE_ID
FROM OE_HOLD_SOURCES HS
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND HS.RELEASED_FLAG = 'N'
--AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
AND nvl(HS.HOLD_ENTITY_ID2, -99) =
nvl(p_hold_source_rec.hold_entity_id2, -99);
SELECT HS.HOLD_SOURCE_ID
FROM OE_HOLD_SOURCES HS
WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
AND HS.RELEASED_FLAG = 'N'
--AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND 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 nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
-- nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
-- AND nvl(HS.HOLD_ENTITY_ID2, -99) =
-- nvl(p_hold_source_rec.hold_entity_id2, -99)
AND EXISTS (select 1
from oe_order_holds oh
where oh.hold_source_id = hs.hold_source_id
and oh.header_id =
nvl(p_hold_source_rec.header_id, oh.header_id)
and nvl(oh.line_id, -99) =
nvl(p_hold_source_rec.line_id, -99));
OE_CREDIT_CHECK_UTIL.UPDATE_RELEASED_AMOUNT(p_hold_source_rec.hold_entity_id,l_hold_release_id);
,'Insert_Hold_Release');
SELECT header_id
INTO l_header_id
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id;
SELECT 'Valid Entity'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id;
PROCEDURE Delete_Holds (
p_order_rec IN OE_HOLDS_PVT.order_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) := 'DELETE_HOLDS';
SELECT order_hold_id, NVL(hold_release_id,0)
FROM OE_ORDER_HOLDS_all
WHERE HEADER_ID = p_order_rec.header_id;
SELECT hold_source_id, NVL(hold_release_id,0)
FROM OE_HOLD_SOURCES_all
WHERE HOLD_ENTITY_CODE = 'O'
AND HOLD_ENTITY_ID = p_order_rec.header_id;
SELECT order_hold_id, NVL(hold_release_id,0)
FROM OE_ORDER_HOLDS_all
WHERE LINE_ID = p_order_rec.line_id;
SAVEPOINT DELETE_HOLDS_PUB;
-- Delete the hold records corr. to this order or line in OE_ORDER_HOLDS
IF p_order_rec.line_id = FND_API.G_MISS_NUM THEN
-- Delete order hold records
OPEN order_hold;
DELETE FROM OE_ORDER_HOLDS_all
WHERE 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_all
UNION
SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_HOLD_SOURCES_all
);
DELETE FROM OE_HOLD_SOURCES_all
WHERE HOLD_SOURCE_ID = l_hold_source_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_all
UNION
SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_HOLD_SOURCES_all
);
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 HOLD_RELEASE_ID NOT IN
(SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_ORDER_HOLDS_all
UNION
SELECT NVL(HOLD_RELEASE_ID,0)
FROM OE_HOLD_SOURCES_all
);
ROLLBACK TO DELETE_HOLDS_PUB;
ROLLBACK TO DELETE_HOLDS_PUB;
END Delete_Holds;
/*Added the Select query and IF Condition below and calling overloaded create_order_holds procedure
for WF_HOLDS ER (bug 6449458)*/
IF l_wf_item_type IS NULL AND l_wf_activity_name IS NULL THEN
select item_type, activity_name
into l_wf_item_type, l_wf_activity_name
from oe_hold_definitions
where hold_id = l_hold_source_rec.hold_id;
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(l_wf_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = l_wf_activity_name;
select item_type, activity_name
into l_wf_item_type, l_wf_activity_name
from oe_hold_definitions
where hold_id = p_hold_source_rec.hold_id;
select header_id
into l_hold_source_rec.header_id
from oe_order_lines_all
where line_id = l_hold_source_rec.line_id;
SELECT 'NO HOLDS'
INTO l_dummy
FROM OE_ORDER_HOLDS
WHERE HOLD_SOURCE_ID = l_hold_source_id
AND HOLD_RELEASE_ID IS NULL
AND ROWNUM = 1;
UPDATE OE_HOLD_SOURCES
SET RELEASED_FLAG = 'Y'
, HOLD_RELEASE_ID = l_hold_release_id
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_DATE = SYSDATE
WHERE HOLD_SOURCE_ID = l_hold_source_id;
, p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
, p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
, p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
, p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%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_header_rec OE_ORDER_PUB.Header_Rec_Type;
select item_type, activity_name
into l_item_type, l_activity_name
from oe_hold_definitions
where hold_id = l_hold_source_rec.hold_id;
/*Added the Select query and IF Condition below for WF_HOLDS ER (bug 6449458)*/
IF l_item_type IS NOT NULL AND l_activity_name IS NOT NULL THEN
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(l_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES',
'-XX')
and lookup_code = l_activity_name;
, p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
, p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
, p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
, p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%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_header_rec OE_ORDER_PUB.Header_Rec_Type;
select item_type, activity_name
into l_item_type, l_activity_name
from oe_hold_definitions
where hold_id = l_hold_source_rec.hold_id;
/*Added the Select query and IF Condition below for WF_HOLDS ER (bug 6449458)*/
IF l_item_type IS NOT NULL AND l_activity_name IS NOT NULL THEN
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(l_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES',
'-XX')
and lookup_code = l_activity_name;
, p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
, p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
, p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
, p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%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_header_rec OE_ORDER_PUB.Header_Rec_Type;
, p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
, p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
, p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
, p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%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_line_rec OE_ORDER_PUB.line_rec_type;
/* This procedure gets called when Create Holds Source is selected
from the special menu.
*/
procedure process_create_source(
p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
,p_hold_existing_flg IN varchar2
,p_hold_future_flg IN varchar2
,p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id --ER#7479609
,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_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
/*added the select statement for WF_HOLDS ER (bug 6449458)*/
select item_type, activity_name
into l_item_type, l_activity_name
from oe_hold_definitions
where hold_id = l_hold_source_rec.hold_id;
select hold_id, hold_entity_code
into l_hold_id, l_hold_entity_code
from OE_HOLD_SOURCES_all
where hold_source_id = p_hold_source_id;
, p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
, p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
, p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
, p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
, p_wf_release_action IN VARCHAR2
, 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_header_rec OE_ORDER_PUB.Header_Rec_Type;
, p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
, p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
, p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
, p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
, p_wf_release_action IN VARCHAR2
, 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_line_rec OE_ORDER_PUB.line_rec_type;
select hold_id, hold_entity_code
into l_hold_id, l_hold_entity_code
from OE_HOLD_SOURCES_all
where hold_source_id = p_hold_source_id;
SELECT oh.HOLD_SOURCE_ID
, oh.HEADER_ID
, hs.hold_entity_code
, hs.hold_id
, hs.hold_until_date
, hs.hold_comment
, hs.CONTEXT
, hs.ATTRIBUTE1
, hs.ATTRIBUTE2
, hs.ATTRIBUTE3
, hs.ATTRIBUTE4
, hs.ATTRIBUTE5
, hs.ATTRIBUTE6
, hs.ATTRIBUTE7
, hs.ATTRIBUTE8
, hs.ATTRIBUTE9
, hs.ATTRIBUTE10
, hs.ATTRIBUTE11
, hs.ATTRIBUTE12
, hs.ATTRIBUTE13
, hs.ATTRIBUTE14
, hs.ATTRIBUTE15
, oh.org_id
FROM OE_ORDER_HOLDS_all oh,
OE_HOLD_SOURCES_all hs
WHERE oh.line_id = p_split_from_line_id
AND oh.RELEASED_FLAG = 'N'
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID;
INSERT INTO OE_ORDER_HOLDS_all
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
VALUES (
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, NULL
, l_HOLD_SOURCE_ID
, c_rec.HEADER_ID
, p_line_id
, 'N'
, c_rec.org_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_order_holds ooh,
oe_hold_sources ohs,
oe_hold_definitions ohd,
oe_order_headers_all h,
oe_order_sources oos
WHERE ohd.hold_id = ohs.hold_id
-- AND ohd.activity_name IS NULL Bug 6791587
AND ooh.header_id = h.header_id
AND h.order_source_id = oos.order_source_id
AND oos.aia_enabled_flag = 'Y'
AND ohs.hold_source_id = ooh.hold_source_id
AND ooh.line_id = p_line_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_order_holds ooh,
oe_hold_sources ohs,
oe_hold_definitions ohd,
oe_order_headers_all h,
oe_order_sources oos
WHERE ohd.hold_id = ohs.hold_id
-- AND ohd.activity_name IS NULL Bug 6791587
AND h.order_source_id = oos.order_source_id
AND oos.aia_enabled_flag = 'Y'
AND ooh.header_id = h.header_id
AND ohs.hold_source_id = ooh.hold_source_id
AND ooh.header_id = p_hdr_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_hold_sources ohs,
oe_hold_definitions ohd
WHERE ohd.hold_id = ohs.hold_id
--AND ohd.activity_name IS NULL Bug 6791587
AND ohs.hold_source_id = p_hld_src_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_order_holds_all ooh,
oe_hold_sources_all ohs,
oe_hold_definitions ohd,
oe_order_headers_all h,
oe_order_sources oos
WHERE ohd.hold_id = ohs.hold_id
AND ooh.header_id = h.header_id
AND ooh.org_id = p_org_id
AND ooh.org_id = ohs.org_id
AND ooh.org_id = h.org_id
AND h.order_source_id = oos.order_source_id
AND oos.aia_enabled_flag = 'Y'
AND ohs.hold_source_id = ooh.hold_source_id
AND ooh.line_id = p_line_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_order_holds_all ooh,
oe_hold_sources_all ohs,
oe_hold_definitions ohd,
oe_order_headers_all h,
oe_order_sources oos
WHERE ohd.hold_id = ohs.hold_id
AND ooh.org_id = p_org_id
AND ooh.org_id = ohs.org_id
AND ooh.org_id = h.org_id
AND h.order_source_id = oos.order_source_id
AND oos.aia_enabled_flag = 'Y'
AND ooh.header_id = h.header_id
AND ohs.hold_source_id = ooh.hold_source_id
AND ooh.header_id = p_hdr_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM oe_hold_sources_all ohs,
oe_hold_definitions ohd
WHERE ohd.hold_id = ohs.hold_id
AND ohs.org_id = p_org_id
AND ohs.hold_source_id = p_hld_src_id);
oe_debug_pub.add('Calling InsertTable_OOH_Line for I and C/CN');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
oe_debug_pub.add('After Calling InsertTable_OOH_Line for I and C/CN');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
--and nvl(h.CANCELLED_FLAG, ''N'') = ''N''
and h.SOLD_TO_ORG_ID = :hold_entity_id2
and h.header_id = ol.header_id
and h.header_id = :l_org_id --ER#7479609
and ol.line_id = :line_id
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id = :hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
--and nvl(h.CANCELLED_FLAG, ''N'') = ''N''
and h.SOLD_TO_ORG_ID = :hold_entity_id2
and h.header_id = ol.header_id
and h.org_ud = :l_org_id --ER#7479609
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and h.header_id = ol.header_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and SM');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and SM');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and D');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and D');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and PL');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and PL');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and LT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and LT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and PR');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and PR');
oe_debug_pub.add('Calling InsertTable_OOH_Line for B and O');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
oe_debug_pub.add('After Calling InsertTable_OOH_Line for B and O');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
ra_terms_b rt
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = :hold_entity_id2
and h.header_id = ol.header_id
and h.org_id =:l_org_id --ER#7479609
and ol.INVOICE_TO_ORG_ID = :hold_entity_id
and ol.line_id = :line_id
and ol.OPEN_FLAG = ''Y''
and ol.PAYMENT_TERM_ID = rt.TERM_ID
and rt.CREDIT_CHECK_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w where w.source_line_id = ol.line_id and w.source_code = ''OE'' and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
ra_terms_b rt
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = :hold_entity_id2
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.INVOICE_TO_ORG_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and ol.PAYMENT_TERM_ID = rt.TERM_ID
and rt.CREDIT_CHECK_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol , ra_terms_b rt
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = p_hold_source_rec.hold_entity_id2
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and ol.PAYMENT_TERM_ID = rt.TERM_ID
and rt.CREDIT_CHECK_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and B');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and B');
l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.INVOICE_TO_ORG_ID = :hold_entity_id2
and ol.line_id = :line_id
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id = :hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'Insert into oe_order_holds_all
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.INVOICE_TO_ORG_ID = :hold_entity_id2
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id = :hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and S');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.SHIP_TO_ORG_ID = :hold_entity_id2
and ol.line_id = :line_id
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.SHIP_TO_ORG_ID = :hold_entity_id2
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y' and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and W');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and W');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.SHIP_FROM_ORG_ID = :hold_entity_id2
and ol.line_id = :line_id
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.SHIP_FROM_ORG_ID = :hold_entity_id2
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and H');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and H');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and S');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.BLANKET_NUMBER = :hold_entity_id2
and ol.line_id = :line_id
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.BLANKET_NUMBER = :hold_entity_id2
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id2
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
oe_debug_pub.add('Calling InsertTable_OOH_Line for IC and C/CN');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
oe_debug_pub.add('After Calling InsertTable_OOH_Line for IC and C/CN');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and SM');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and SM');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and D');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and D');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and PL');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and PL');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and LT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and LT');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and PR');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and PR');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and B');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and B');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and W');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and W');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and H');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and H');
l_hold_entity_where_clause := ' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for IC and S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for IC and S');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and C/CN');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and C/CN');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id
and ol.SHIP_FROM_ORG_ID = :hold_entity_id
and ol.line_id = :line_id
and h.SOLD_TO_ORG_ID = :hold_entity_id2
and ol.OPEN_FLAG = ''Y''
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, p_org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
and ol.SHIP_FROM_ORG_ID = :hold_entity_id
and h.SOLD_TO_ORG_ID = :hold_entity_id2
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and SM');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and SM');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and ST');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and ST');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and LT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and LT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and D');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and D');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and B');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and B');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
AND h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
AND ol.SHIP_FROM_ORG_ID = :hold_entity_id
and ol.line_id = :line_id
AND ol.INVOICE_TO_ORG_ID = :hold_entity_id2
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
AND NOT EXISTS ( select ''x''
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, p_org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
AND h.header_id = ol.header_id
and h.org_id = :l_org_id --ER#7479609
AND ol.SHIP_FROM_ORG_ID = :hold_entity_id
AND ol.INVOICE_TO_ORG_ID = :hold_entity_id2
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
AND NOT EXISTS ( select ''x''
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
AND h.header_id = ol.header_id
AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
AND ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and S');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
AND h.org_id = :l_org_id --ER#7479609
AND ol.SHIP_FROM_ORG_ID = :hold_entity_id
and ol.line_id = :line_id
AND h.header_id = ol.header_id
AND ol.SHIP_TO_ORG_ID = :hold_entity_id2
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
AND NOT EXISTS ( select ''x''
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
AND h.org_id = :l_org_id --ER#7479609
AND ol.SHIP_FROM_ORG_ID = :hold_entity_id
AND h.header_id = ol.header_id
AND ol.SHIP_TO_ORG_ID = :hold_entity_id2
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
AND NOT EXISTS ( select ''x''
FROM oe_order_holds_ALL oh
WHERE oh.header_id = h.header_id
AND oh.line_id = ol.line_id
AND oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
AND h.header_id = ol.header_id
AND ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and B');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and B');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = :hold_entity_id2
and ol.line_id = :line_id
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID= :hold_entity_id2
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and PT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and PT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and PL');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and PL');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and D');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and D');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and LT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and LT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and SM');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and SM');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and S');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = :hold_entity_id2
and ol.line_id = :line_id
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID= :hold_entity_id2
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and W');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and W');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = :hold_entity_id2
and ol.line_id = :line_id
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID= :hold_entity_id2
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID= p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and L');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and L');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.BLANKET_LINE_NUMBER = :hold_entity_id2
and ol.line_id = :line_id
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.BLANKET_LINE_NUMBER = :hold_entity_id2
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.BLANKET_LINE_NUMBER = p_hold_source_rec.hold_entity_id2
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.line_id = :line_id
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.BLANKET_NUMBER = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, NULL
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = :hold_entity_id
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id =:hold_source_id2 )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(h.header_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status
and nvl(was.activity_result_code, :l_activity_result)
NOT IN (:l_result_1, :l_result_2))'; --9538334
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, :line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = :hold_entity_id
and h.header_id = ol.header_id
and ol.line_id = :line_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status
and nvl(activity_result_code, :l_activity_result)
NOT IN (:l_result_1, :l_result_2))'; --9538334
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = p_hold_source_rec.hold_entity_id
and h.header_id = ol.header_id
and ol.line_id = p_hold_source_rec.line_id
and ol.open_flag = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('slagiset: calling Update_Credit_Profile_Level');
OE_CREDIT_CHECK_UTIL.Update_Credit_Profile_Level(p_hold_source_rec);
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C, BillTOLine');
l_hold_entity_where_clause := ' and exists (Select 1 FROM HZ_CUST_ACCOUNTS_ALL cust ,HZ_CUST_ACCT_SITES_ALL site,hz_cust_site_uses_all siteuse WHERE cust.cust_account_id = '
||''''||p_hold_source_rec.hold_entity_id||''''||'and ol.INVOICE_TO_ORG_ID = siteuse.site_use_id'
||' AND cust.cust_account_id =site.cust_account_id AND site.cust_acct_site_id = siteuse.cust_acct_site_id AND siteuse.site_use_code ='||'''BILL_TO'''||' AND siteuse.status = '||'''A'''||')';
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C/CN');
OE_DEBUG_PUB.ADD('Not calling InsertTable_OOH_Header as Credit Hold System Paramter at BillTOLine');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header as Credit Hold System Paramter at BillTOCustHeader');
l_hold_entity_where_clause := ' and exists (Select 1 FROM HZ_CUST_ACCOUNTS_ALL cust ,HZ_CUST_ACCT_SITES_ALL site,hz_cust_site_uses_all siteuse WHERE cust.cust_account_id = '
||''''||p_hold_source_rec.hold_entity_id||''''||'and h.INVOICE_TO_ORG_ID = siteuse.site_use_id'
||' AND cust.cust_account_id =site.cust_account_id AND site.cust_acct_site_id = siteuse.cust_acct_site_id AND siteuse.site_use_code ='||'''BILL_TO'''||' AND siteuse.status = '||'''A'''||')';
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C/CN');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C/CN');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, NULL
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = :header_id
and h.SOLD_TO_ORG_ID = :hold_entity_id
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(h.header_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, NULL
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = ''Y''
and h.SOLD_TO_ORG_ID = :hold_entity_id
and h.org_id = :l_org_id --ER#7479609
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(h.header_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h
WHERE h.OPEN_FLAG = 'Y'
and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C/CN and B');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN and B');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C/CN and S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN and S');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C/CN and D');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN and D');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C/CN and PL');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN and PL');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C/CN and LT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN and LT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C/CN and PT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN and PT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C/CN and OT');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C/CN and OT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C/CN and TC');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C/CN and TC');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C/CN and ST');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C/CN and ST');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C/CN and SC');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C/CN and SC');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for B');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for B');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id
and h.header_id = ol.header_id
and ol.line_id = :line_id
and ol.INVOICE_TO_ORG_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for S');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.line_id = :line_id
and ol.SHIP_TO_ORG_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.line_id = :line_id
and ol.SHIP_FROM_ORG_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I');
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.line_id = :line_id
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
SELECT
OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, :l_user_id
, SYSDATE
, :l_user_id
, NULL
, :hold_source_id
, h.HEADER_ID
, ol.line_id
, ''N''
, h.org_id --ER#7479609 :l_org_id
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = ''Y''
and h.org_id = :l_org_id --ER#7479609
and h.header_id = ol.header_id
and ol.INVENTORY_ITEM_ID = :hold_entity_id
and ol.OPEN_FLAG = ''Y''
-- QUOTING change
and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
and not exists ( select ''x''
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =:hold_source_id )';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
, wf_process_activities wpa
where was.process_activity = wpa.instance_id
and item_type = :p_item_type
and item_key = to_char(ol.line_id)
and activity_name = :l_activity_name
and activity_status = :l_activity_status)';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details w
where w.source_line_id = ol.line_id
and w.source_code = ''OE''
and w.released_status in (''Y'', ''C''))';
l_sqlmt := l_sqlmt||' and not exists (select 1 from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.source_line_id = ol.LINE_ID
and wdd.source_code = ''OE''
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.parent_delivery_detail_id is not null)';
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
SELECT count(*)
into l_parent_count
FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
WHERE h.OPEN_FLAG = 'Y'
and h.header_id = ol.header_id
and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
and ol.OPEN_FLAG = 'Y'
and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
and not exists ( select 'x'
from oe_order_holds_ALL oh
where oh.header_id = h.header_id
and oh.line_id = ol.line_id
and oh.hold_source_id =
p_hold_source_rec.hold_source_id );
select meaning into l_user_activity_name
from oe_lookups
where lookup_type = DECODE(p_item_type,
OE_GLOBALS.G_WFI_HDR, 'HOLDABLE_HEADER_ACTIVITIES',
OE_GLOBALS.G_WFI_LIN, 'HOLDABLE_LINE_ACTIVITIES', '-XX')
and lookup_code = p_activity_name;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PR and T');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PR and T');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PL and TC');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PL and TC');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for OT and LT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for OT and LT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for OT and TC');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for OT and TC');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for OT and TC');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for OT and TC');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for CD and CB');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for CD and CB');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for CD');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for CD');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for SC');
InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_header_id =>p_hold_source_rec.header_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for SC');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for SM');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for SM');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for D');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for D');
l_line_id_tab.delete;
select line_id
BULK COLLECT INTO l_line_id_tab
from oe_order_lines_all
where inventory_item_id=p_hold_source_rec.hold_entity_id
and line_id=top_model_line_id
and top_model_line_id IS NOT NULL;
select line_id
BULK COLLECT INTO l_line_id_tab
from oe_order_lines_all
where inventory_item_id=p_hold_source_rec.hold_entity_id
and line_id=top_model_line_id
and line_id=p_hold_source_rec.line_id
and top_model_line_id IS NOT NULL;
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for TM:'||l_line_id_tab.count);
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => l_line_id_tab(i)
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for TM');
l_line_id_tab.delete;
select top_model_line_id
BULK COLLECT INTO l_line_id_tab
from oe_order_lines_all line_opt
where line_opt.inventory_item_id=p_hold_source_rec.hold_entity_id2
and line_opt.item_type_code in ('OPTION','CLASS','INCLUDED')
and EXISTS (select 1 from oe_order_lines_all line_mod
where line_mod.inventory_item_id=p_hold_source_rec.hold_entity_id
and line_mod.line_id=line_opt.top_model_line_id);
select top_model_line_id
BULK COLLECT INTO l_line_id_tab
from oe_order_lines_all line_opt
where line_opt.inventory_item_id=p_hold_source_rec.hold_entity_id2
and line_opt.line_id=p_hold_source_rec.line_id
and line_opt.item_type_code in ('OPTION','CLASS','INCLUDED')
and EXISTS (select 1 from oe_order_lines_all line_mod
where line_mod.inventory_item_id=p_hold_source_rec.hold_entity_id
and line_mod.line_id=line_opt.top_model_line_id);
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for TM and OI:'||l_line_id_tab.count);
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => l_line_id_tab(i)
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for TM and OI');
oe_debug_pub.add('Calling InsertTable_OOH_Line for I and E/EN');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
oe_debug_pub.add('After Calling InsertTable_OOH_Line for I and E/EN');
||' and exists (Select 1
from mtl_item_categories mic ,mtl_default_category_sets mdc
where mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id
AND ol.INVENTORY_ITEM_ID = mic.inventory_item_id
and mic.organization_id= '||''''||l_master_orgid||''''
||' and mic.category_id = '||''''||p_hold_source_rec.hold_entity_id||''''||')';
oe_debug_pub.add('Calling InsertTable_OOH_Line for IC and EC/EN');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
oe_debug_pub.add('After Calling InsertTable_OOH_Line for IC and EC/EN');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and E/EN');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and E/EN');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and B');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and B');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and S');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and S');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and D');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and D');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and PL');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and PL');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and LT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and LT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and PT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and PT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and OT');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and OT');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and TC');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and TC');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and ST');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and ST');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and SC');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and SC');
OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for EC/EN and EL');
InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
,p_line_id => p_hold_source_rec.line_id
,p_org_id => p_org_id
,p_hold_entity_where_clause => l_hold_entity_where_clause
,p_item_type => p_item_type
,p_activity_name => l_activity_name
,p_activity_status => l_act_status
,p_additional_where_clause => l_additional_where_clause
,x_is_hold_applied => x_is_hold_applied);
OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for EC/EN and EL');