[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
SELECT criterion_name
INTO l_return_type_name
FROM wms_crossdock_criteria_vl
WHERE criterion_id=p_return_type_id
AND criterion_type = 1 ;
SELECT criterion_name
INTO l_return_type_name
FROM wms_crossdock_criteria_vl
WHERE criterion_id=p_return_type_id
AND criterion_type = 2;
SELECT cost_group
INTO l_return_type_name
FROM CST_COST_GROUPS
WHERE cost_group_id=p_return_type_id
AND organization_id = p_org_id;
SELECT meaning
INTO l_return_type_name
FROM mfg_lookups_v
WHERE lookup_type = 'WMS_CARTONIZATION_ALGORITHMS'
AND lookup_code = p_return_type_id;
select distinct name into l_return_type_name
from wms_strategies_vl
where organization_id in (p_org_id, -1)
and type_code = p_rule_type_code
and strategy_id = p_return_type_id;
select name into l_return_type_name
from wms_rules_vl
where organization_id in (p_org_id, -1)
and rule_id = p_return_type_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
/*select rc.customer_name
into l_customer_name
from ra_customers rc
where rc.customer_id = p_customer_id;*/
/* SELECT distinct substrb ( PARTY.PARTY_NAME, 1, 50 )
INTO l_customer_name
FROM HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PARTY.PARTY_ID = p_customer_id; */
SELECT distinct substrb ( PARTY.PARTY_NAME, 1, 50 )
INTO l_customer_name
FROM HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = p_customer_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select ood.organization_code
into l_organization_code
from org_organization_definitions ood
where sysdate < nvl(ood.disable_date,sysdate+1)
and ood.organization_id = p_organization_id ;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select ofv.freight_code_tl
into l_freight_code_name
from org_freight ofv
where ofv.organization_id = p_org_id
and ofv.freight_code = p_freight_code
and sysdate < nvl(ofv.disable_date,sysdate+1);
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select msik.concatenated_segments into l_item
from mtl_system_items_kfv msik
where msik.organization_id = p_org_id
and msik.inventory_item_id = p_inventory_item_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select maag.assignment_group_name||' / '|| mac.abc_class_name
into l_abc_group_class
from mtl_abc_classes mac,
mtl_abc_assignment_groups maag ,
MTL_ABC_ASSGN_GROUP_CLASSES magc
where maag.organization_id = mac.organization_id
and magc.assignment_group_id = maag.assignment_group_id
and magc.abc_class_id = mac.abc_class_id
and mac.organization_id = p_org_id
and maag.assignment_group_id = p_assignment_group_id
and mac.abc_class_id = p_class_id ;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
Select mcs.category_set_name||' / '||mck.concatenated_segments into l_category_set_name
From mtl_categories_kfv mck
,mtl_category_sets_vl mcs
,mtl_category_set_valid_cats mcsvc
Where mcs.category_set_id = mcsvc.category_set_id
and mck.category_id = mcsvc.category_id
and mcsvc.category_set_id = p_category_set_id
and mcsvc.category_id = p_category_id; */
SELECT mcs.CATEGORY_SET_NAME||' / '|| mck.CONCATENATED_SEGMENTS into l_category_set_name
FROM MTL_CATEGORIES_KFV mck
,MTL_CATEGORIES_VL mc
,MTL_CATEGORY_SETS_VL mcs
,( SELECT mic.ORGANIZATION_ID
,mic.CATEGORY_SET_ID
,mic.CATEGORY_ID
FROM MTL_ITEM_CATEGORIES mic
WHERE mic.ORGANIZATION_ID = p_org_id
GROUP BY mic.ORGANIZATION_ID
,mic.CATEGORY_SET_ID
,mic.CATEGORY_ID ) x
WHERE mcs.CATEGORY_SET_ID = x.CATEGORY_SET_ID
AND mc.CATEGORY_ID = mck.CATEGORY_ID
AND mck.CATEGORY_ID = x.CATEGORY_ID
AND x.CATEGORY_SET_ID = p_category_set_id
AND x.CATEGORY_ID = p_category_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select ottv.name into l_order_type_name
from oe_transaction_types_vl ottv
where ottv.transaction_type_id = p_transaction_type_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select distinct ppov.project_name into l_project_name
from pjm_projects_mtll_v ppov
where ppov.project_id = p_project_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
SELECT nvl(p.project_number, '') || ' / ' || nvl(ptev.TASK_NUMBER, '') into l_task_name
FROM pjm_tasks_mtll_v ptev, pjm_projects_mtll_v p
where p.project_id = ptev.project_id
and ptev.project_id = p_project_id
and ptev.task_id = p_task_id;
/* select ppev.project_name||' / '||ptev.indented_task_name
into l_task_name
from pa_tasks_expend_v ptev
,pa_projects_expend_v ppev
where ptev.project_id = ppev.project_id
and ppev.project_id = p_project_id
and ptev.task_id = p_task_id; */
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
SELECT pv.vendor_name
INTO l_vendor_name
FROM PO_VENDORS pv
,PO_VENDOR_SITES_ALL pvsa
,ORG_ORGANIZATION_DEFINITIONS ood
WHERE ood.ORGANIZATION_ID = p_org_id
AND nvl(pvsa.ORG_ID,-99) = nvl(ood.OPERATING_UNIT,-99)
AND pvsa.PURCHASING_SITE_FLAG = 'Y'
AND sysdate < nvl(pvsa.INACTIVE_DATE, sysdate + 1)
AND pv.VENDOR_ID = pvsa.VENDOR_ID
AND pvsa.vendor_id = p_vendor_id
GROUP BY pv.VENDOR_NAME;
g_pkg_name constant VARCHAR2(50) := 'wms_selection_criteria_pvt';
select fu.user_name
into l_user_name
from fnd_user fu
where sysdate < nvl(fu.end_date,sysdate+1)
and fu.user_id = p_user_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select ml.meaning
into l_transaction_action_name
from mfg_lookups ml
where ml.lookup_type = 'MTL_TRANSACTION_ACTION'
and ml.lookup_code = p_transaction_action_id ;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select mtr.reason_name
into l_reason_name
from mtl_transaction_reasons mtr
where sysdate < nvl(mtr.disable_date,sysdate+1)
and mtr.reason_id = p_reason_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select mtst.transaction_source_type_name
into l_transaction_source_name
from mtl_txn_source_types mtst
where sysdate < nvl(mtst.disable_date,sysdate+1)
and mtst.transaction_source_type_id = p_transaction_source_type_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select mtt.transaction_type_name
into l_transaction_type_name
from mtl_transaction_types mtt
where sysdate < nvl(mtt.disable_date,sysdate+1)
and mtt.transaction_type_id = p_transaction_type_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select muom.unit_of_measure_tl
into l_unit_of_measure
from mtl_units_of_measure muom
where sysdate < nvl(muom.disable_date,sysdate+1)
and muom.uom_code = p_uom_code;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select muc.uom_class_tl
into l_uom_class_name
from mtl_uom_classes muc
where muc.uom_class = p_uom_class;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select ml.meaning into l_item_type
from fnd_common_lookups ml
where ml.lookup_type = 'ITEM_TYPE'
and ml.lookup_code = p_item_type_code;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
l_rec_wsct WMS_SELECTION_CRITERIA_TXN%ROWTYPE;
l_sequence_number wms_selection_criteria_txn.sequence_number%type;
l_return_type_code wms_selection_criteria_txn.return_type_code%type;
l_return_type_id wms_selection_criteria_txn.return_type_id%type;
l_from_organization_id wms_selection_criteria_txn.from_organization_id%type;
l_from_subinventory_name wms_selection_criteria_txn.from_subinventory_name%type default null;
l_to_organization_id wms_selection_criteria_txn.to_organization_id%type;
l_to_subinventory_name wms_selection_criteria_txn.to_subinventory_name%type;
l_customer_id wms_selection_criteria_txn.customer_id%type;
l_freight_code wms_selection_criteria_txn.freight_code%type;
l_inventory_item_id wms_selection_criteria_txn.inventory_item_id%type;
l_item_type wms_selection_criteria_txn.item_type%type;
l_order_type_id wms_selection_criteria_txn.order_type_id%type;
l_vendor_id wms_selection_criteria_txn.vendor_id%type;
l_project_id wms_selection_criteria_txn.project_id%type;
l_task_id wms_selection_criteria_txn.task_id%type;
l_user_id wms_selection_criteria_txn.user_id%type;
l_transaction_action_id wms_selection_criteria_txn.transaction_action_id%type;
l_reason_id wms_selection_criteria_txn.reason_id%type;
l_transaction_source_type_id wms_selection_criteria_txn.transaction_source_type_id%type;
l_transaction_type_id wms_selection_criteria_txn.transaction_type_id%type;
l_uom_code wms_selection_criteria_txn.uom_code%type;
l_uom_class wms_selection_criteria_txn.uom_class%type default null;
l_category_id wms_selection_criteria_txn.category_id%type;
l_category_set_id wms_selection_criteria_txn.category_set_id%type;
l_assignment_group_id wms_selection_criteria_txn.assignment_group_id%type;
l_abc_class_id wms_selection_criteria_txn.abc_class_id%type;
SELECT line_id FROM (
select mtrl.line_id,
mtrl.organization_id,
mtrl.inventory_item_id,
mtrl.transaction_source_type_id,
mtrl.reference_id,
mtrl.reference,
mtrl.uom_code
from mtl_txn_request_lines mtrl
where mtrl.header_id = l_rec_mtrl.header_id
AND mtrl.organization_id = l_from_organization_id
AND mtrl.line_status <> 5
AND NVL(mtrl.from_subinventory_code,'###') = NVL(l_from_subinventory_name,NVL(mtrl.from_subinventory_code,'###'))
AND NVL(mtrl.to_organization_id ,-999) = NVL(l_to_organization_id, NVL(mtrl.to_organization_id,-999))
AND NVL(mtrl.to_subinventory_code, '###') = NVL(l_to_subinventory_name, NVL(mtrl.to_subinventory_code,'###'))
AND mtrl.inventory_item_id = NVL(l_inventory_item_id , mtrl.inventory_item_id )
AND NVL(mtrl.project_id ,-999) = NVL(l_project_id , NVL(mtrl.project_id ,-999) )
AND NVL(mtrl.task_id ,-999) = NVL(l_task_id , NVL(mtrl.task_id ,-999) )
AND NVL(mtrl.reason_id ,-999) = NVL(l_reason_id ,NVL(mtrl.reason_id ,-999) )
AND mtrl.transaction_source_type_id = NVL(l_transaction_source_type_id,mtrl.transaction_source_type_id)
AND mtrl.transaction_type_id = NVL(l_transaction_type_id,mtrl.transaction_type_id)
AND NVL(mtrl.uom_code,'##') = NVL(l_uom_code ,nvl(mtrl.uom_code,'##'))
AND mtrl.last_updated_by = NVL(l_user_id, mtrl.last_updated_by)
AND ( (p_type_code=2 AND mtrl.pick_strategy_id IS NULL) OR
(p_type_code=1 AND mtrl.put_away_strategy_id IS NULL) ) /*10371674*/
) mtrl1
WHERE ( NVL(l_freight_code,'AAA') = 'AAA' OR
EXISTS (SELECT 1
FROM wsh_delivery_details wdd,
wsh_carriers wc,
wsh_carrier_services wcs
WHERE wdd.move_order_line_id = mtrl1.line_id
AND wdd.ship_method_code = wcs.ship_method_code (+)
AND wcs.carrier_id = wc.carrier_id (+)
AND wc.freight_code = l_freight_code
)
)
AND ( Nvl(l_customer_id, -999) = -999 OR
EXISTS (SELECT 1 FROM wsh_delivery_details wdd
WHERE wdd.move_order_line_id = mtrl1.line_id
AND wdd.customer_id = l_customer_id
)
)
AND ( NVL(l_item_type,'1') = '1' OR
WMS_RULES_WORKBENCH_PVT.get_item_type(mtrl1.organization_id,mtrl1.inventory_item_id) = l_item_type
)
AND ( NVL(l_uom_class,'aaa') = 'aaa' OR
WMS_RULES_WORKBENCH_PVT.get_uom_class(mtrl1.uom_code) = l_uom_class
)
AND ( NVL(l_order_type_id,-999)= -999 OR
WMS_RULES_WORKBENCH_PVT.get_order_type_id(mtrl1.line_id,mtrl1.transaction_source_type_id,mtrl1.reference_id) = l_order_type_id
)
AND ( mtrl1.transaction_source_type_id <> 1 OR
NVL(l_vendor_id,-999) = -999 OR
WMS_RULES_WORKBENCH_PVT.get_vendor_id(mtrl1.reference,mtrl1.reference_id) = l_vendor_id
)
AND (l_category_id IS NULL OR WMS_RULES_WORKBENCH_PVT.get_Item_Cat(mtrl1.organization_id,
mtrl1.inventory_item_id,
l_category_set_id,
l_category_id )='Y'
)
AND (l_abc_class_id IS NULL OR WMS_RULES_WORKBENCH_PVT.get_group_class(mtrl1.inventory_item_id,
l_assignment_group_id,
l_abc_class_id)='Y'
);
cursor cur_stg_selection is
select return_type_code
,return_type_id
,sequence_number
,from_subinventory_name
, to_organization_id
, to_subinventory_name
, Nvl(customer_id,l_customer_id) --8809951
, Nvl(freight_code,l_freight_code) --8809951
, inventory_item_id
, Nvl(item_type,l_item_type) --8809951
, Nvl(order_type_id,l_order_type_id) --8809951
, Nvl(vendor_id, l_vendor_id) --8809951
, project_id
, task_id
, user_id
, transaction_action_id
, reason_id
, transaction_source_type_id
, transaction_type_id
, uom_code
, Nvl(uom_class, l_uom_class) --8809951
, category_id
, category_set_id
, assignment_group_id
, abc_class_id
from wms_selection_criteria_txn
where from_organization_id = l_from_organization_id
and rule_type_code = p_type_code
and enabled_flag = 1
and nvl(from_subinventory_name, l_from_subinventory_name) = l_from_subinventory_name
and nvl(to_organization_id, l_to_organization_id) = l_to_organization_id
and nvl(to_subinventory_name, l_to_subinventory_name ) = l_to_subinventory_name
and nvl(customer_id, l_customer_id) = l_customer_id
and nvl(freight_code, l_freight_code) = l_freight_code
and nvl(inventory_item_id, l_inventory_item_id) = l_inventory_item_id
and nvl(item_type, l_item_type) = l_item_type
and nvl(order_type_id, l_order_type_id) = l_order_type_id
and nvl(vendor_id, l_vendor_id) = l_vendor_id
and nvl(project_id, l_project_id) = l_project_id
and nvl(task_id, l_task_id ) = l_task_id
and nvl(user_id, l_user_id ) = l_user_id
and nvl(transaction_action_id, l_transaction_action_id ) = l_transaction_action_id
and nvl(reason_id , l_reason_id ) = l_reason_id
and nvl(transaction_source_type_id, l_transaction_source_type_id) = l_transaction_source_type_id
and nvl(transaction_type_id, l_transaction_type_id) = l_transaction_type_id
and nvl(uom_code, l_uom_code) = l_uom_code
and nvl(uom_class, l_uom_class) = l_uom_class
and nvl(effective_from,to_date('01011900','ddmmyyyy')) <= trunc(sysdate)
and nvl(effective_to,to_date('31124000','ddmmyyyy')) >= trunc(sysdate)
and wms_datecheck_pvt.date_valid(l_from_organization_id,date_type_code,date_type_from,date_type_to,effective_from,effective_to) = 'Y' --Added bug 4081657
and decode(category_id,null,'N', 'Y') = decode(category_id,null,'N', WMS_RULES_WORKBENCH_PVT.get_Item_Cat(l_rec_mtrl.organization_id,
l_rec_mtrl.inventory_item_id,
category_set_id,
category_id )
)
and decode(abc_class_id,null,'N', 'Y') = decode(abc_class_id,null,'N', WMS_RULES_WORKBENCH_PVT.get_group_class(l_rec_mtrl.inventory_item_id,
assignment_group_id,
abc_class_id)
)
order by sequence_number;
log_procedure(l_api_name,'','Start selection Criteria ');
l_user_id := nvl(l_rec_mtrl.last_updated_by, -999);
OPEN cur_stg_selection;
FETCH cur_stg_selection
INTO
l_return_type_code,
l_return_type_id,
l_sequence_number,
l_from_subinventory_name,
l_to_organization_id,
l_to_subinventory_name,
l_customer_id,
l_freight_code,
l_inventory_item_id,
l_item_type,
l_order_type_id,
l_vendor_id,
l_project_id,
l_task_id,
l_user_id,
l_transaction_action_id,
l_reason_id,
l_transaction_source_type_id,
l_transaction_type_id,
l_uom_code,
l_uom_class,
l_category_id,
l_category_set_id,
l_assignment_group_id,
l_abc_class_id;
If (cur_stg_selection%NOTFOUND) Then
--3224420close cur_stg_selection;
log_event(l_api_name,'','stg_selection cursor not found ');
If (cur_stg_selection%FOUND) Then
if l_debug =1 then
log_event(l_api_name,'',' Open/fetching stg_selection cursor');
mtrl_line_tab.delete;
UPDATE mtl_txn_request_lines
SET put_away_strategy_id = l_return_type_id
WHERE line_id = mtrl_line_tab(i);
log_event(l_api_name,'msg:','Updated '||mtrl_line_tab.count||' mtrl lines with strategy_id ' ||l_return_type_id);
UPDATE mtl_txn_request_lines
SET pick_strategy_id = l_return_type_id
WHERE line_id = mtrl_line_tab(i);
log_event(l_api_name,'msg:','Updated '||mtrl_line_tab.count||' mtrl lines with strategy_id ' ||l_return_type_id);
If cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
If cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
If cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
log_error(l_api_name, 'error', 'Error in selection Criteria - ' ||
x_msg_data);
If cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
'in selection Criteria - ' || x_msg_data);
log_event(l_api_name,'',' Exception in selection Criteria');
If cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
l_rec_wsct WMS_SELECTION_CRITERIA_TXN%ROWTYPE;
last_updated_by mtl_material_transactions_temp.last_updated_by%TYPE
);
l_sequence_number wms_selection_criteria_txn.sequence_number%type;
l_return_type_code wms_selection_criteria_txn.return_type_code%type;
l_return_type_id wms_selection_criteria_txn.return_type_id%type;
l_from_organization_id wms_selection_criteria_txn.from_organization_id%type;
l_from_subinventory_name wms_selection_criteria_txn.from_subinventory_name%type default null;
l_to_organization_id wms_selection_criteria_txn.to_organization_id%type;
l_to_subinventory_name wms_selection_criteria_txn.to_subinventory_name%type;
l_customer_id wms_selection_criteria_txn.customer_id%type;
l_freight_code wms_selection_criteria_txn.freight_code%type;
l_inventory_item_id wms_selection_criteria_txn.inventory_item_id%type;
l_item_type wms_selection_criteria_txn.item_type%type;
l_order_type_id wms_selection_criteria_txn.order_type_id%type;
l_vendor_id wms_selection_criteria_txn.vendor_id%type;
l_project_id wms_selection_criteria_txn.project_id%type;
l_task_id wms_selection_criteria_txn.task_id%type;
l_user_id wms_selection_criteria_txn.user_id%type;
l_transaction_action_id wms_selection_criteria_txn.transaction_action_id%type;
l_reason_id wms_selection_criteria_txn.reason_id%type;
l_transaction_source_type_id wms_selection_criteria_txn.transaction_source_type_id%type;
l_transaction_type_id wms_selection_criteria_txn.transaction_type_id%type;
l_uom_code wms_selection_criteria_txn.uom_code%type;
l_uom_class wms_selection_criteria_txn.uom_class%type default null;
cursor cur_stg_selection is
select return_type_code, return_type_id, sequence_number
from wms_selection_criteria_txn
where from_organization_id = l_from_organization_id
and rule_type_code = p_type_code
and enabled_flag = 1
and nvl(from_subinventory_name, l_from_subinventory_name) = l_from_subinventory_name
and nvl(to_organization_id, l_to_organization_id) = l_to_organization_id
and nvl(to_subinventory_name, l_to_subinventory_name ) = l_to_subinventory_name
and nvl(customer_id, l_customer_id) = l_customer_id
and nvl(freight_code, l_freight_code) = l_freight_code
and nvl(inventory_item_id, l_inventory_item_id) = l_inventory_item_id
and nvl(item_type, l_item_type) = l_item_type
and nvl(order_type_id, l_order_type_id) = l_order_type_id
and nvl(vendor_id, l_vendor_id) = l_vendor_id
and nvl(project_id, l_project_id) = l_project_id
and nvl(task_id, l_task_id ) = l_task_id
and nvl(user_id, l_user_id ) = l_user_id
and nvl(transaction_action_id, l_transaction_action_id ) = l_transaction_action_id
and nvl(reason_id , l_reason_id ) = l_reason_id
and nvl(transaction_source_type_id, l_transaction_source_type_id) = l_transaction_source_type_id
and nvl(transaction_type_id, l_transaction_type_id) = l_transaction_type_id
and nvl(uom_code, l_uom_code) = l_uom_code
and nvl(uom_class, l_uom_class) = l_uom_class
and nvl(effective_from,to_date('01011900','ddmmyyyy')) <= trunc(sysdate)
and nvl(effective_to,to_date('31124000','ddmmyyyy')) >= trunc(sysdate)
and wms_datecheck_pvt.date_valid(l_from_organization_id,date_type_code,date_type_from,date_type_to,effective_from,effective_to) = 'Y' --Added bug 4081657
and decode(category_id,null, 'N', 'Y') = decode(category_id, null, 'N', WMS_RULES_WORKBENCH_PVT.get_Item_Cat(l_from_organization_id, -- 14071820
l_rec_mtrl.inventory_item_id ,
category_set_id,
category_id ))
and decode(abc_class_id,null,'N', 'Y') = decode(abc_class_id,null,'N', WMS_RULES_WORKBENCH_PVT.get_group_class(l_rec_mtrl.inventory_item_id,
assignment_group_id,
abc_class_id))
order by sequence_number;
SELECT
mmtt.transaction_temp_id ,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision,
decode( mmtt.transaction_action_id,
1,mmtt.subinventory_code,
2,mmtt.subinventory_code,
3,mmtt.subinventory_code,
21,mmtt.subinventory_code,
28,mmtt.subinventory_code,
29,mmtt.subinventory_code,
32,mmtt.subinventory_code,
34,mmtt.subinventory_code,
NULL),
decode(transaction_action_id, 1,
NULL, 2,
transfer_subinventory, 3,
transfer_subinventory, 21,
NULL, 28,
transfer_subinventory,
29, NULL, 32, NULL,34, NULL, subinventory_code),
mmtt.transaction_uom,
mmtt.reason_id,
mmtt.project_id,
mmtt.task_id,
mmtt.transaction_type_id,
mmtt.transaction_source_type_id,
decode(mmtt.transaction_action_id, 3, mmtt.transfer_organization, 21, mmtt.transfer_organization, mmtt.organization_id),
mmtt.transaction_reference,
decode(mmtt.source_code,'RCV', mmtt.rcv_transaction_id,to_number(NULL)),
mmtt.transaction_action_id,
mmtt.last_updated_by
from mtl_material_transactions_temp mmtt
where mmtt.transaction_temp_id = p_transaction_temp_id;
l_user_id := nvl(l_rec_mtrl.last_updated_by, -999);
OPEN cur_stg_selection;
FETCH cur_stg_selection INTO l_return_type_code, l_return_type_id, l_sequence_number;
If (cur_stg_selection%NOTFOUND) Then
--commenting out for 3224420 close cur_stg_selection;
log_event(l_api_name,'','stg_selection cursor not found ');
If (cur_stg_selection%FOUND) Then
x_return_type := l_return_type_code;
log_event(l_api_name, '',' Open/fetching stg_selection cursor');
IF cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
IF (cur_stg_selection%ISOPEN) then
CLOSE cur_stg_selection;
IF cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
log_error(l_api_name, 'error', 'Error in selection Criteria - ' ||
x_msg_data);
IF cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
IF cur_stg_selection%ISOPEN then
CLOSE cur_stg_selection;
log_event(l_api_name,'',' Exception in selection Criteria');
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select 'Y' INTO l_category_exist
from mtl_item_categories mic
where mic.organization_id = p_org_id
and mic.inventory_item_id = p_inventory_item_id
and mic.category_set_id = p_category_set_id
and mic.category_id = p_category_id
and rownum<2;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select 'Y' INTO l_group_class_exist FROM mtl_abc_assignments
where inventory_item_id = p_inventory_item_id
and assignment_group_id = p_assignment_group_id
and abc_class_id = p_class_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select muom.uom_class into l_uom_class
from MTL_UNITS_OF_MEASURE muom
where muom.uom_code = p_uom_code;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select poh.vendor_id into l_vendor_id
from po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll
where poh.po_header_id = pol.po_header_id
and pll.po_header_id = pol.po_header_id
and pll.po_line_id = pol.po_line_id
and pll.shipment_type = 'STANDARD'
and pll.line_location_id = p_reference_id ;
select poh.vendor_id into l_vendor_id
from po_headers_all poh,
po_lines_all pol,
po_distributions_all pod,
po_line_locations_all pll
where poh.po_header_id = pol.po_header_id
and pll.po_header_id = pol.po_header_id
and pll.po_line_id = pol.po_line_id
and pod.po_header_id = pll.po_header_id
and pod.po_line_id = pll.po_line_id
and pod.line_location_id = pll.line_location_id
and pll.shipment_type = 'STANDARD'
and pod.po_distribution_id = p_reference_id;
select poh.vendor_id into l_vendor_id
from po_headers_all poh,
po_lines_all pol,
rcv_transactions rct,
po_line_locations_all pll
where poh.po_header_id = pol.po_header_id
and pll.po_header_id = pol.po_header_id
and pll.po_line_id = pol.po_line_id
and rct.po_header_id = pll.po_header_id
and rct.po_line_id = pll.po_line_id
and rct.po_line_location_id = pll.line_location_id
and pll.shipment_type = 'STANDARD'
and rct.transaction_id = p_reference_id;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select nvl(mtrl.transaction_source_type_id,0) , nvl(mtrl.reference_id, 0)
into l_transaction_source_type_id, l_reference_id
from mtl_txn_request_lines mtrl
where mtrl.line_id = p_move_order_line_id; */
select /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/ oh.order_type_id into l_order_type_id
from oe_order_headers_all oh,
wsh_delivery_details wdd
where oh.header_id = wdd.source_header_id
and wdd.released_status = 'S'
and wdd.source_code = 'OE'
and wdd.move_order_line_id = p_move_order_line_id;
select oh.order_type_id into l_order_type_id
from oe_order_headers_all oh ,
oe_order_lines_all ol
where oh.header_id = ol.header_id
and ol.line_id = l_reference_id ;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
select /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/ oh.order_type_id into l_order_type_id
from oe_order_headers_all oh,
wsh_delivery_details wdd
where oh.header_id = wdd.source_header_id
and wdd.released_status = 'S'
and wdd.source_code = 'OE'
and wdd.move_order_line_id = p_move_order_line_id;
select oh.order_type_id into l_order_type_id
from oe_order_headers_all oh ,
oe_order_lines_all ol
where oh.header_id = ol.header_id
and ol.line_id = p_reference_id ;
g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
SELECT nvl(trx_source_line_id, -999)
INTO l_trx_source_line_id
FROM mtl_material_Transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND transaction_source_type_id = INV_GLOBALS.G_SOURCETYPE_RMA
AND transaction_action_id = INV_GLOBALS.G_ACTION_RECEIPT;
select customer_id, freight_carrier_code into l_customer_id, l_freight_code
from WMS_TXN_CONTEXT_TEMP
where line_id = p_transaction_temp_id;
SELECT
hz.PARTY_ID,
oola.freight_carrier_code
INTO l_customer_id,
l_freight_code
FROM oe_order_lines_all oola,
HZ_PARTIES hz
WHERE oola.line_id = l_trx_source_line_id
AND hz.party_id = oola.sold_to_org_id;
select location_code into l_location_code
from hr_locations
where location_id=p_location_id;
l_sequence_number wms_selection_criteria_txn.sequence_number%type;
l_return_type_code wms_selection_criteria_txn.return_type_code%type;
l_return_type_id wms_selection_criteria_txn.return_type_id%type;
CURSOR cur_crossdock_value_selection IS
SELECT return_type_code, return_type_id, sequence_number
FROM wms_selection_criteria_txn
WHERE from_organization_id = p_organization_id
AND rule_type_code = p_rule_type_code
AND enabled_flag = 1
AND NVL(customer_id, NVL(p_customer_id, -9)) = NVL(p_customer_id, -9)
AND NVL(inventory_item_id, p_inventory_item_id) = p_inventory_item_id
AND NVL(item_type, NVL(p_item_type, '#')) = NVL(p_item_type, '#')
AND NVL(vendor_id, NVL(p_vendor_id, -9)) = NVL(p_vendor_id, -9)
AND NVL(location_id, NVL(p_location_id, -9)) = NVL(p_location_id, -9)
AND NVL(project_id, NVL(p_project_id, -9)) = NVL(p_project_id, -9)
AND NVL(task_id, NVL(p_task_id, -9)) = NVL(p_task_id, -9)
AND NVL(user_id, NVL(p_user_id, -9)) = NVL(p_user_id, -9)
AND NVL(uom_code, NVL(p_uom_code, '#')) = NVL(p_uom_code, '#')
AND NVL(uom_class, NVL(p_uom_class, '#')) = NVL(p_uom_class, '#')
AND DECODE(abc_class_id, NULL, 'N', 'Y') = DECODE(abc_class_id, NULL,'N',
WMS_RULES_WORKBENCH_PVT.get_group_class(p_inventory_item_id,
assignment_group_id,
abc_class_id))
ORDER BY sequence_number;
OPEN cur_crossdock_value_selection;
FETCH cur_crossdock_value_selection INTO l_return_type_code, l_return_type_id, l_sequence_number;
IF (cur_crossdock_value_selection%NOTFOUND) THEN
l_return_type_code := NULL;
IF cur_crossdock_value_selection%ISOPEN THEN
CLOSE cur_crossdock_value_selection;
If cur_crossdock_value_selection%ISOPEN then
CLOSE cur_crossdock_value_selection;
log_error(l_api_name, 'error', 'Error in Cross selection Criteria - ' ||l_msg_data);
If cur_crossdock_value_selection%ISOPEN then
CLOSE cur_crossdock_value_selection;
log_error(l_api_name, 'unexp_error', 'Unexpected error ' ||' in selection Criteria - ' || l_msg_data);
log_event(l_api_name,'',' Exception in cross selection Criteria');
If cur_crossdock_value_selection%ISOPEN then
CLOSE cur_crossdock_value_selection;