[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT mp.organization_code ORG_CODE
, sysdate REP_DATE, ''' || nvl(p_from_item, ' ') || ''' p_from_item,''' || nvl(p_to_item, ' ') || ''' p_to_item,
''' || nvl(p_from_subinv, ' ') || ''' p_from_subinv,''' || nvl(p_to_subinv, ' ') || ''' p_to_subinv
, cursor(select mss.secondary_inventory_name SUBINV
,cursor(select msi.concatenated_segments ITEM_NO
, msi.description ITEM_DESC
,cursor(select mln.lot_number LOT
, fnd_date.date_to_displayDT(mln.origination_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE ) ORIG_DATE
, fnd_date.date_to_displayDT(mln.expiration_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) EXP_DATE
, mil.concatenated_segments LOCATOR
, sum(ohd.primary_transaction_quantity) QTY
, msi.primary_uom_code UOM
, sum(nvl(ohd.secondary_transaction_quantity,0)) SEC_QTY
, msi.secondary_uom_code SEC_UOM
, mms.status_code STATUS
, mln.grade_code GRADE
from mtl_lot_numbers mln,
mtl_item_locations_kfv mil,
mtl_onhand_quantities_detail ohd,
mtl_material_statuses_vl mms
WHERE msi.inventory_item_id = mln.inventory_item_id
AND msi.organization_id = mln.organization_id
-- for the bug 10236246
AND ohd.status_id is null
-- end of the bug 10236246
AND mln.status_id = mms.status_id(+)
AND ohd.organization_id = mln.organization_id
AND ohd.inventory_item_id = mln.inventory_item_id
AND ohd.lot_number = mln.lot_number
AND ohd.locator_id = mil.inventory_location_id(+)
and ohd.organization_id = mp.organization_id
and ohd.subinventory_code = mss.secondary_inventory_name
and ohd.organization_id = msi.organization_id
and ohd.inventory_item_id = msi.inventory_item_id
group by mln.lot_number , mln.origination_date,mln.expiration_date
, mil.concatenated_segments
, msi.primary_uom_code
, msi.secondary_uom_code
, mms.status_code
, mln.grade_code
-- For the bug 10236246 query the on hand material status tracked org.
union
select mln.lot_number LOT
, fnd_date.date_to_displayDT(mln.origination_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) ORIG_DATE
, fnd_date.date_to_displayDT(mln.expiration_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) EXP_DATE
, mil.concatenated_segments LOCATOR
, sum(ohd.primary_transaction_quantity) QTY
, msi.primary_uom_code UOM
, sum(nvl(ohd.secondary_transaction_quantity,0)) SEC_QTY
, msi.secondary_uom_code SEC_UOM
, mms.status_code STATUS
, mln.grade_code GRADE
from mtl_lot_numbers mln,
mtl_item_locations_kfv mil,
mtl_onhand_quantities_detail ohd,
mtl_material_statuses_vl mms
WHERE msi.inventory_item_id = mln.inventory_item_id
AND msi.organization_id = mln.organization_id
AND ohd.status_id is not null
AND ohd.status_id = mms.status_id(+)
AND ohd.organization_id = mln.organization_id
AND ohd.inventory_item_id = mln.inventory_item_id
AND ohd.lot_number = mln.lot_number
AND ohd.locator_id = mil.inventory_location_id(+)
and ohd.organization_id = mp.organization_id
and ohd.subinventory_code = mss.secondary_inventory_name
and ohd.organization_id = msi.organization_id
and ohd.inventory_item_id = msi.inventory_item_id
group by mln.lot_number , mln.origination_date,mln.expiration_date
, mil.concatenated_segments
, msi.primary_uom_code
, msi.secondary_uom_code
, mms.status_code
, mln.grade_code
) as LOT_DETAILS
FROM mtl_system_items_kfv msi
WHERE msi.organization_id = mss.organization_id
and msi.organization_id = mp.organization_id
and msi.lot_control_code = 2';
|| ' and exists (select '' x '' from mtl_onhand_quantities_detail ohd1
where ohd1.organization_id = mp.organization_id
and ohd1.subinventory_code = mss.secondary_inventory_name
and ohd1.organization_id = msi.organization_id
and ohd1.inventory_item_id = msi.inventory_item_id) ) AS ITEM_DETAILS
FROM mtl_secondary_inventories mss
WHERE mss.organization_id = mp.organization_id';
|| ' AND EXISTS ( SELECT ''x''
FROM mtl_onhand_quantities_detail d
WHERE d.organization_id = mss.organization_id
AND d.subinventory_code = mss.secondary_inventory_name
and d.organization_id = mp.organization_id )) AS SUBINV_DETAILS
FROM mtl_parameters mp
WHERE mp.organization_id = '
|| p_organization_id;
' SELECT mp.organization_code org,
sysdate rep_date,''' || nvl(p_from_item, ' ') || ''' p_from_item,''' || nvl(p_to_item, ' ') || ''' p_to_item ,
cursor(select msi.concatenated_segments Item_number
, msi.description item_desc
, cursor(select mln.lot_number
, mln.description
, mln.grade_code
, mln.expiration_action_code
, fnd_date.date_to_displayDT(mln.expiration_action_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) expiration_action_date
, mln.origination_type
, mfgl.meaning lot_origination
, fnd_date.date_to_displayDT(mln.origination_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) origination_date
, fnd_date.date_to_displayDT(mln.expiration_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) expiration_date
, fnd_date.date_to_displayDT(mln.retest_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) retest_date
, fnd_date.date_to_displayDT(mln.maturity_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) maturity_date
, fnd_date.date_to_displayDT(mln.hold_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) hold_date
, mln.parent_lot_number
, mln.vendor_name
, mln.supplier_lot_number
FROM mtl_lot_numbers mln
, mfg_lookups mfgl
WHERE msi.organization_id = mln.organization_id
AND msi.inventory_item_id = mln.inventory_item_id
AND mln.origination_type = mfgl.LOOKUP_CODE(+)
AND mfgl.lookup_type(+) = ''MTL_LOT_ORIGINATION_TYPE'') as LOT_DETAILS
FROM mtl_system_items_kfv msi
WHERE msi.organization_id = mp.organization_id ';
|| ' and msi.lot_control_code = 2 and exists ( select mln1.inventory_item_id from mtl_lot_numbers mln1 where msi.organization_id = mln1.organization_id
AND msi.inventory_item_id = mln1.inventory_item_id and mln1.organization_id = mp.organization_id)
ORDER BY msi.concatenated_segments) as ITEM_DETAILS
from mtl_parameters mp
where mp.organization_id = '
|| p_organization_id;
SELECT meaning
INTO l_order_display
FROM mfg_lookups
WHERE lookup_type = 'INV_SRS_ASC_DESC'
AND enabled_flag = 'Y'
AND lookup_code = NVL(p_sort_order, 1);
'SELECT sysdate rep_date,''' || l_order_display || ''' ORDER_BY,''' ||
nvl(p_from_status, ' ') || ''' p_from_status,''' ||
nvl(p_to_status, ' ') || ''' p_to_status
, mv.status_code
, mv.description
, ml1.meaning enabled_flag_value
, ml2.meaning allow_reservations_flag_value
, ml3.meaning include_in_atp_flag_value
, ml4.meaning nettable_flag_value
, ml5.meaning subinventory_usage_flag_value
, ml6.meaning locator_usage_flag_value
, ml7.meaning lot_usage_flag_value
, ml8.meaning serial_usage_flag_value
, m19.meaning onhand_usage_flag_value
, cursor (select tx.transaction_description
from mtl_status_control_v tx
where tx.status_id = mv.status_id and tx.is_allowed = 1
order by tx.transaction_description) as allowed_transactions
, cursor (select tx.transaction_description
from mtl_status_control_v tx
where tx.status_id = mv.status_id and tx.is_allowed = 2
order by tx.transaction_description) as disallowed_transactions
FROM mtl_material_statuses_vl mv
, mfg_lookups ml1
, mfg_lookups ml2
, mfg_lookups ml3
, mfg_lookups ml4
, mfg_lookups ml5
, mfg_lookups ml6
, mfg_lookups ml7
, mfg_lookups ml8
, mfg_lookups m19
WHERE ml1.lookup_code = mv.enabled_flag
and ml1.lookup_type = ''SYS_YES_NO''
and ml2.lookup_code = mv.reservable_type
and ml2.lookup_type = ''SYS_YES_NO''
and ml3.lookup_code = mv.inventory_atp_code
and ml3.lookup_type = ''SYS_YES_NO''
and ml4.lookup_code = mv.availability_type
and ml4.lookup_type = ''SYS_YES_NO''
and ml5.lookup_code = mv.zone_control
and ml5.lookup_type = ''SYS_YES_NO''
and ml6.lookup_code = mv.locator_control
and ml6.lookup_type = ''SYS_YES_NO''
and ml7.lookup_code = mv.lot_control
and ml7.lookup_type = ''SYS_YES_NO''
and ml8.lookup_code = mv.serial_control
and ml8.lookup_type = ''SYS_YES_NO''
and m19.lookup_code = mv.onhand_control
and m19.lookup_type = ''SYS_YES_NO''';
get the meaning of update_method and not the code */
--#11703502 Character date international calendar support Added calendar_aware in date function arguments
QUERY :=
'SELECT sysdate rep_date
, ood.organization_code org_code
, ood.organization_name org_name
, msi.concatenated_segments Item_No
, msi.primary_uom_code pri_uom
, DECODE(msi.tracking_quantity_ind,''PS'',msi.secondary_uom_code) sec_uom /*Bug#5436402*/
, cursor ( select mlgh.lot_number lot_no
, msi.default_grade def_grade
, mlgh.old_grade_code old_grade
, mlgh.new_grade_code new_grade
, DECODE (mlgh.from_mobile_apps_flag, ''Y'', ''Mobile'', ''N'', ''Desktop'') updated_from
, fnd_date.date_to_displayDT(mlgh.grade_update_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) upd_date
, fnd.user_name user_name
, ml.meaning upd_method
, mlgh.primary_quantity pri_qty
, mlgh.secondary_quantity sec_qty
, mg1.description from_desc
, mg2.description to_desc
, mtr.reason_name reason
from mtl_lot_grade_history mlgh
, mtl_grades_vl mg1
, mtl_grades_vl mg2
, mtl_transaction_reasons mtr
, fnd_user fnd
, mfg_lookups ml
where msi.organization_id = mlgh.organization_id
AND msi.inventory_item_id = mlgh.inventory_item_id
AND mlgh.old_grade_code = mg1.grade_code
AND mlgh.new_grade_code = mg2.grade_code
AND mlgh.created_by = fnd.user_id
AND mlgh.update_reason_id = mtr.reason_id(+)
AND ml.lookup_type = ''MTL_STATUS_UPDATE_METHOD''
AND ml.lookup_code = mlgh.update_method ';
|| 'AND (mlgh.grade_update_date >= '''
|| p_from_dt
|| ''' AND mlgh.grade_update_date <= '''
|| p_to_dt
|| ''')';
QUERY := QUERY || 'AND mlgh.grade_update_date >= ''' || p_from_date || '''';
QUERY := QUERY || 'AND mlgh.grade_update_date <= ''' || p_to_date || '''';
|| ' order by mlgh.lot_number, mlgh.grade_update_date) as Lot_details
FROM org_organization_definitions ood,
mtl_system_items_kfv msi
WHERE ood.organization_id = msi.organization_id
AND ood.organization_id = '''
|| p_organization_id
|| ''''
|| '
AND msi.inventory_item_id = '''
|| p_item_id
|| ''''
|| '
ORDER BY msi.concatenated_segments';