Oracle WMS Checks Query

--Assign printer to documents

select --*
wrp.application_id,
wrp.concurrent_program_id, decode(wrp.concurrent_program_id,'4','LPN Content','3','LPN','1','Material') "Type",
wrp.level_type_id, decode(wrp.level_type_id,'10004','User','10003','Responsibility','10009','Format') "Level",
wrp.level_value_id,decode(wrp.level_type_id,
        '10004', (select user_name from apps.fnd_user where user_id = wrp.level_value_id),
        '10003',(select responsibility_name from apps.fnd_responsibility_tl where responsibility_id =wrp.level_value_id),
        '10009',(select label_format_name from apps.wms_label_formats_v where label_format_id = wrp.level_value_id)
        ) "LevelValue" ,
wrp.printer_name, wrp.enabled_flag, wrp.default_printer_flag
from
apps.WSH_REPORT_PRINTERS wrp
where
1=1
and application_id =  385
--and concurrent_program_id = 4
and wrp.enabled_flag = 'Y'
and level_type_id <> 10004
--and printer_name like 'si%'
and wrp.creation_date > sysdate - 100
;
select * from
apps.fnd_user
where user_id = 61987;

--Assign lable type to business flows
select
wblt.business_flow_code,
fvbf.meaning,
wblt.document_id,
fvlt.meaning,
wblt.level_type_code,
fvpl.meaning,
decode(fvpl.meaning,
        'Responsibility',(select responsibility_name from apps.fnd_responsibility_tl where responsibility_id =wblt.level_value_id)--,
        --'10009',(select label_format_name from apps.wms_label_formats_v where label_format_id = wblt.business_flow_code)
        ) "RespName" ,
wblt.creation_date, wblt.created_by
from
apps.WMS_BFLOW_LABEL_TYPE wblt, apps.fnd_lookup_values fvbf, apps.fnd_lookup_values fvlt, apps.fnd_lookup_values fvpl
where wblt.creation_date > sysdate - 100
--and created_by = 10298
--and wblt.business_flow_code <> 27
and wblt.business_flow_code = fvbf.lookup_code
and fvbf.lookup_type = 'WMS_BUSINESS_FLOW'
and wblt.document_id = fvlt.lookup_code
and fvlt.lookup_type = 'WMS_LABEL_TYPE'
and wblt.level_type_code = fvpl.lookup_code
and fvpl.lookup_type = 'ITA_PROFILE_LEVEL_ID'

;

select label_format_name from apps.wms_label_formats_v where label_format_id =
;

select user_name from apps.fnd_user where user_id = 3265
;
-- 10004 = User
--10009 = Format
-- 10003 = Responsibility

57327
;

select responsibility_name from pps.fnd_responsibility_tl where responsibility_id = 57327
;

select * from
apps.fnd_lookups
where description = '10003'
;
--10003

select * from
apps.wms_rules_v
where organization_id = 1872
and type_code = 4
--and name = 'SGD - Drop Label Format - ARC.ICE V20160923'
;

select * from
apps.wms_label_formats_v
where label_format_name like '%sin%'
;

select
wblt.*
from
apps.WMS_BFLOW_LABEL_TYPE wblt
where
1=1
--creation_date > sysdate - 100
--and created_by = 10298
and wblt.business_flow_code = 19
;


select * from
apps.fnd_lookup_values
where
lookup_type = 'ITA_PROFILE_LEVEL_ID'  --'WMS_LABEL_TYPE'  --'WMS_BUSINESS_FLOW'
--lookup_code = '10009'
order by meaning
;

select organization_id, (select employee_number||', '||full_name from apps.PER_PEOPLE_F where person_id = bre.person_id) nam from
apps.bom_resource_employees  bre
where organization_id = (select organization_id from apps.mtl_parameters where organization_code = 'EYD')
;

alter session set nls_language = 'AMERICAN';

select * from 

apps.fnd_responsibility_tl

where responsibility_name like 'GEAS%PO%HKD%'

;

select * from

apps.MTL_SECONDARY_INVENTORIES

where organization_id in (1531, 1533)

and secondary_inventory_name like '%RCV%'

order by 2

;

select * from

apps.MTL_ITEM_LOCATIONS 

where organization_id in (1531, 1533)

and subinventory_code like 'STG%'

order by 2, segment1, segment2, segment3, segment4

;

select -- * 

decode(business_flow_code,36, 'Packing Workbench',27,'PutAway',19,'Pick Drop') "Flow", 

decode(document_id,8,'Shipping Contents',3,'LPN',4,'LPN Content') "Document", 

decode(level_type_code,10003,'Responsibility') "Level", 

level_value_id, 

(

select responsibility_name from apps.fnd_responsibility_tl where responsibility_id = level_value_id

) "level value",

enabled_flag, creation_date

from apps.WMS_BFLOW_LABEL_TYPE 

--where business_flow_code = 27

order by creation_date

;

select --*

bso.operation_code, bso.standard_operation_id, bso.operation_description,  bso.department_code, bso.wms_task_type , flv.MEANING

from 

apps.BOM_STANDARD_OPERATIONS_V bso,apps.FND_LOOKUP_VALUES_VL flv 

where bso.organization_id = 1872

and flv.lookup_type = 'WMS_TASK_TYPES'

and bso.wms_task_type = flv.LOOKUP_CODE

order by bso.wms_task_type

;

select * from

apps.BOM_STD_OP_RESOURCES_V 

;

select --*

so.organization_id, so.operation_code, so.standard_operation_id, so.operation_description,  so.department_code,

sr.resource_id, sr.resource_code, sr.unit_of_measure, sr.resource_seq_num, sr.usage_rate_or_amount,

sr.available_24_hours_flag, sr.assigned_units, sr.schedule_flag

from 

apps.BOM_STANDARD_OPERATIONS_V so,

apps.BOM_STD_OP_RESOURCES_V sr

where (so.organization_id = 1533 or so.organization_id = 84)

and so.standard_operation_id = sr.standard_operation_id

order by 1

;

select 

c.from_organization_id, c.rule_type, c.sequence_number, c.return_type, c.return_type_id, c.enabled_flag,  

c.transaction_action_name, c.TRANSACTION_SOURCE_TYPE_NAME, c.wms_enabled_flag, c.LAST_UPDATE_DATE, c.LAST_UPDATED_BY, 

s.strategy_id, s.name, s.description, s.user_defined_flag, s.created_by, s.over_allocation_mode, 

m.sequence_number,  m.rule_id, m.rule_name, m.partial_success_allowed_flag, m.LAST_UPDATED_BY, m.LAST_UPDATE_DATE, 

r.sequence_number, r.logical_operator_meaning, r.bracket_open, r.left_object_name,  r.left_parameter_name,  

r.operator_meaning, 

r.operand_type_meaning, r.operand_constant_character, r.operand_expression, r.right_parameter_name, 

r.bracket_close, r.last_updated_by, r.last_update_date 

 

from 

apps.WMS_SELECTION_CRITERIA_TXN_V c, 

apps.WMS_STRATEGIES_V s, 

apps.WMS_STRATEGY_MEMBERS_V m, 

apps.WMS_RESTRICTIONS_V r 

 

where 1=1  

/*and (

c.from_organization_id = 1531 

or c.from_organization_id = 84 

or c.from_organization_id = 1131 

or c.from_organization_id = 1151 

)

*/

and s.strategy_id = c.RETURN_TYPE_ID 

and m.strategy_id = c.RETURN_TYPE_ID 

and m.rule_id = r.rule_id 

order by c.from_organization_id, c.rule_type, c.sequence_number, m.sequence_number, r.SEQUENCE_NUMBER

;

SELECT LOOKUP_CODE, MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_TYPES';

select * from

apps.fnd_lookups

where lookup_type like '%WMS%'

and lookup_code = '1'

;

--transaction queries general

a) Select organization_id,Organization_code,wms_enabled_flag

from mtl_parameters where organization_code = '&org_code';

To Check LPN Details

=======================

b) select count(wlpn.license_plate_number) from wms_license_plate_numbers wlpn, mtl_parameters mp

where mp.organization_id = wlpn.organization_id

and mp.organization_code = '&org_code';

c) select count (wlc.parent_lpn_id) from wms_lpn_contents wlc,   mtl_parameters mp

where mp.organization_id = wlc.organization_id

and mp.organization_code = '&org_code';

d) select count(wlh.lpn_id) from wms_lpn_histories wlh, mtl_parameters mp

Where Mp.Organization_Id = Wlh.Organization_Id

and mp.organization_code = '&org_code';

To Check Pending PO details

==============================

e) Select count(Ms.Supply_Source_Id) From Mtl_Supply Ms, Mtl_Parameters Mp

Where Ms.To_Organization_Id = Mp.Organization_Id

and mp.organization_code ='&org_code';

f) Select count(Ms.Supply_Source_Id) From rcv_supply Ms, Mtl_Parameters Mp

Where Ms.To_Organization_Id = Mp.Organization_Id

and mp.organization_code ='&org_code';

To Check Pending SO details

============================

g) Select Wdd.Source_Code, Wdd.Released_Status, Wdd.Oe_Interfaced_Flag,Wdd.Inv_Interfaced_Flag,Count(Wdd.Delivery_Detail_Id)

From Wsh_Delivery_Details Wdd, Mtl_Parameters Mp

Where Wdd.Organization_Id = Mp.Organization_Id

And Mp.Organization_Code = '&org_code'

group by wdd.source_code, Wdd.Released_Status,Wdd.Oe_Interfaced_Flag,Wdd.Inv_Interfaced_Flag;

To check Pending Transactions and Uncosted Records

===================================================

h) Select Count(Transaction_Temp_Id) From Mtl_Material_Transactions_Temp Mmtt, Mtl_Parameters Mp

Where Mp.Organization_Id = Mmtt.Organization_Id

And Mp.Organization_Code ='&org_code';

i) Select Count(Transaction_Interface_Id) From Mtl_Transactions_Interface Mti, Mtl_Parameters Mp

Where Mp.Organization_Id = Mti.Organization_Id

And Mp.Organization_Code ='&org_code';

j) Select Count(Transaction_id) From Mtl_Material_Transactions Mmt, Mtl_Parameters Mp

Where Mp.Organization_Id = Mmt.Organization_Id

and mmt.costed_flag is not null

And Mp.Organization_Code ='&org_code';

To check transaction history with LPN reference

================================================

k) Select Count(Transaction_Id) From Mtl_Material_Transactions Mmt, Mtl_Parameters Mp

Where Mp.Organization_Id = Mmt.Organization_Id

And Mp.Organization_Code ='&org_code'

And( Mmt.Lpn_Id Is Not Null

Or Mmt.Transfer_Lpn_Id Is Not Null

or mmt.content_lpn_id is not null);

To check the Rule details

============================

l) Select R.Organization_Id, R.Type_Code,R.Wms_Enabled_Flag ,Count(R.Rule_Id)

From Wms_Rules_B R , Mtl_Parameters Mp

Where MP.organization_code = '&org_code'

And (R.Organization_Id = Mp.Organization_Id

Or R.Organization_Id = -1)

Group By R.Organization_Id, R.Type_Code,R.Wms_Enabled_Flag

order by R.Organization_Id, R.Type_Code;