Skip to main content

OracleApps SQL Queries


Entity Structure:

select
LEV.LEDGER_ID LEDG_ID,
LEV.LEDGER_NAME,
LEV.CURRENCY_CODE CCY,
LEV.LEGAL_ENTITY_ID LE_ID,
LEV.LEGAL_ENTITY_NAME,
LAD.ADDRESS_LINE_1,
LAD.ADDRESS_LINE_2,
LAD.ADDRESS_LINE_3,
LAD.TOWN_OR_CITY,
LAD.COUNTRY,
OOD.OPERATING_UNIT OU_ID,
OU.OPERATING_UNIT_NAME,
OU.LOCATION_NAME,
LEV.LOCATION_ID LOC_ID,
LEV.LOCATION_CODE LOC_CODE,
OOD.ORGANIZATION_ID ORG_ID,
OOD.ORGANIZATION_CODE ORG_CODE,
OOD.ORGANIZATION_NAME,
houv.location_code,
mp.attribute7

 FROM
      APPS.GL_LEDGER_LE_V LEV,
      apps.org_organization_definitions OOD,
      apps.HRFV_OPERATING_UNITS OU,
      APPS.HR_LOCATION_LEGAL_ADR_V LAD,
    apps.mtl_parameters mp
,APPS.GL_LEDGERS gl
,apps.HR_ORGANIZATION_UNITS_V houv
WHERE
        LEV.LEGAL_ENTITY_ID=OOD.LEGAL_ENTITY
AND   OOD.OPERATING_UNIT=OU.OPERATING_UNITS_ID
AND   OU.LOCATION_ID=LAD.LOCATION_ID(+)
and ood.organization_code = mp.organization_code

 and gl.ledger_id = lev.ledger_id
and gl.ledger_category_code = 'PRIMARY'

 and houv.organization_id = OOD.ORGANIZATION_ID
and mp.organization_code = 'BRD'
--and LEV.CURRENCY_CODE = 'TWD'
--AND OOD.OPERATING_UNIT = 1851
order by
OOD.OPERATING_UNIT
;

Comments

Post a Comment

Popular posts from this blog

Oracle WMS Outbound Query

/*  ************************************************************************************************************          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) ...
Post a Comment
Read more

Oracle WMS Tasks Query

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,         ...
Post a Comment
Read more

Oracle WMS Rules Query

/********************* 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 ...
Post a Comment
Read more