Varun Blogs

--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_p...

SELECT *   FROM (SELECT wdth.transaction_temp_id,                wdth.task_id,                oeh.order_number,                oel.line_number,                msi.concatenated_segments item_number,                'Pick',                'Completed',                fu1.user_name             assigned_to,                wdth.person_id,                mmtt.transaction_quantity,                mmtt.transaction_uom           FROM apps.oe_order_headers_all         oeh,         ...

/********************* WMS Rules Extract *********************/ select mp.organization_code, 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 , apps.mtl_parameters mp where 1=1  and ...

/*  ************************************************************************************************************          OUTBOUND METRICS           Released to Warehouse --> Staged/Pick Confirmed      ************************************************************************************************************  */  SELECT mp.organization_code "Warehouse", wda.delivery_id "Delivery", wt.name "Trip", (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'PICK_STATUS' AND LOOKUP_CODE = wdd.released_status) "Delivery Status", CASE  WHEN wdt_extra.move_order_line_id IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = wdt_extra.status)  WHEN mmtt.wms_task_status IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = mmtt.wms_task_status) ...

/*  ************************************************************************************************************          INBOUND METRICS           Lines in-transit      ************************************************************************************************************  */  SELECT mp.organization_code "Warehouse", rsh.receipt_num "Receipt Number", rsh.shipment_num "Shipment Number", wt.name "Trip", NULL "Putaway LPN", NULL "Putaway Subinventory", NULL "Putaway Locator", msib.segment1 "Item", (select c_ext_attr11||'/'||c_ext_attr12 from apps.ego_mtl_sy_items_ext_b where organization_id = 89 and inventory_item_id = msib.inventory_item_id and attr_group_id = 42) "Item Temperature", (rsl.quantity_shipped-rsl.quantity_received) "Quantity", decode(msib.serial_number_control_code,1,'No','Yes') "Serial Item", (cic.item_cost * (rsl.q...