ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E085552D3FC24D2F9412B97C94E4EEA7Y select mp.organization_code, msiv.concatenated_segments item, msiv.description item_description, mkps.subinventory_name subinventory, (select milk.concatenated_segments from mtl_item_locations_kfv milk where mkps.locator_id=milk.inventory_location_id and mkps.organization_id=milk.organization_id) locator, xxen_util.meaning(mkps.source_type,'MTL_KANBAN_SOURCE_TYPE',700) source_type, aps.vendor_name supplier, assa.vendor_site_code supplier_site, mp_s.organization_code source_organization, mkps.source_subinventory, (select milk.concatenated_segments from mtl_item_locations_kfv milk where mkps.source_locator_id=milk.inventory_location_id and mkps.source_organization_id=milk.organization_id) source_locator, wl.line_code production_line, mkps.replenishment_lead_time, xxen_util.meaning(mkps.calculate_kanban_flag,'MTL_CALCULATE_KANBAN_FLAG',700) calculate, mkps.kanban_size, mkps.number_of_cards, mkps.minimum_order_quantity, mkps.fixed_lot_multiplier, mkps.safety_stock_days, xxen_util.meaning(mkps.aggregation_type,'MTL_KANBAN_AGGREGATION_TYPE',700) aggregation_type, mkps.allocation_percent, msiv.primary_uom_code uom, mkc.kanban_card_number card_number, xxen_util.meaning(mkc.kanban_card_type,'MTL_KANBAN_CARD_TYPE',700) card_type, xxen_util.meaning(mkc.card_status,'MTL_KANBAN_CARD_STATUS',700) card_status, xxen_util.meaning(mkc.supply_status,'MTL_KANBAN_SUPPLY_STATUS',700) supply_status, mkc.kanban_size card_kanban_size, xxen_util.meaning(mkc.error_code,'MTL_KANBAN_ERROR_CODE',700) card_error, (select count(distinct mkca.replenishment_cycle_id) from mtl_kanban_card_activity mkca where mkc.kanban_card_id=mkca.kanban_card_id) replenishment_cycles, (select max(mkca.creation_date) from mtl_kanban_card_activity mkca where mkc.kanban_card_id=mkca.kanban_card_id) last_activity_date, mkc.last_print_date, &activity_columns xxen_util.user_name(mkc.created_by) card_created_by, mkc.creation_date card_creation_date from mtl_parameters mp, mtl_kanban_pull_sequences mkps, mtl_system_items_vl msiv, ap_suppliers aps, ap_supplier_sites_all assa, mtl_parameters mp_s, wip_lines wl, (select mkc.* from mtl_kanban_cards mkc where '&pull_sequences_only' is null) mkc, (select mkca.* from mtl_kanban_card_activity mkca where '&show_activity'='Y' and 2=2) mkca, po_headers_all pha, po_releases_all pra, po_headers_all pha_r, po_requisition_headers_all prha, mtl_txn_request_headers mtrh, wip_entities we where 1=1 and mkps.kanban_plan_id=-1 and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id=mkps.organization_id and mkps.organization_id=msiv.organization_id and mkps.inventory_item_id=msiv.inventory_item_id and mkps.supplier_id=aps.vendor_id(+) and mkps.supplier_site_id=assa.vendor_site_id(+) and mkps.source_organization_id=mp_s.organization_id(+) and mkps.wip_line_id=wl.line_id(+) and mkps.pull_sequence_id=mkc.pull_sequence_id(+) and mkc.kanban_card_id=mkca.kanban_card_id(+) and case when mkca.document_type=1 then mkca.document_header_id end=pha.po_header_id(+) and case when mkca.document_type=2 then mkca.document_header_id end=pra.po_release_id(+) and pra.po_header_id=pha_r.po_header_id(+) and case when mkca.document_type=3 then mkca.document_header_id end=prha.requisition_header_id(+) and case when mkca.document_type=4 then mkca.document_header_id end=mtrh.header_id(+) and case when mkca.document_type in (5,6,7,8) then mkca.document_header_id end=we.wip_entity_id(+) order by mp.organization_code, msiv.concatenated_segments, mkps.subinventory_name, mkc.kanban_card_number, mkca.creation_date desc, mkca.kanban_activity_id desc New report US INV Kanban Cards Kanban cards with their pull sequence, one row per card, including card type, card status, supply status and card kanban size. Replenishment Cycles and Last Activity Date are derived from the card activity history. Only production pull sequences are listed, not those of kanban plans. Pull Sequences Only lists one row per pull sequence without the cards. Show Activity adds one row per card activity (supply status change), with the document that replenished the card. Enginatics &activity_columns &pull_sequences_only &show_activity 1=1 2=2 1 10 1=1 mp.organization_code=:organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 2 20 1=1 msiv.concatenated_segments=:item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 3 30 1=1 mkps.subinventory_name=:subinventory LOV INV Subinventory 8E2FF36EDF2A79D2E0530100007F1FF2 Y select msi.secondary_inventory_name value, msi.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_secondary_inventories msi where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msi.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msi.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' or xxen_util.contains(:$flex$.organization_code,mp.organization_id)='Y' ) and mp.organization_id=msi.organization_id order by mp.organization_code, msi.secondary_inventory_name US Subinventory 4 40 1=1 mkps.source_type=:source_type_id LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_KANBAN_SOURCE_TYPE' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Source Type 5 50 1=1 aps.vendor_name=:supplier LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Supplier 6 60 1=1 mkc.kanban_card_number=:card_number Char US Card Number 7 70 1=1 mkc.card_status=:card_status_id LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_KANBAN_CARD_STATUS' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Card Status 8 80 1=1 mkc.supply_status=:supply_status_id LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_KANBAN_SUPPLY_STATUS' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Supply Status 9 90 &pull_sequences_only Y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Pull Sequences Only One row per pull sequence, without the kanban cards. 10 100 &show_activity Y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Activity One row per card activity (status change and replenishment document), latest first. 11 &activity_columns xxen_util.client_time(mkca.creation_date) activity_date, xxen_util.meaning(mkca.supply_status,'MTL_KANBAN_SUPPLY_STATUS',700) activity_supply_status, xxen_util.meaning(mkca.card_status,'MTL_KANBAN_CARD_STATUS',700) activity_card_status, mkca.kanban_size activity_kanban_size, xxen_util.meaning(mkca.document_type,'MTL_KANBAN_DOCUMENT_TYPE',700) document_type, case when mkca.document_type=1 then pha.segment1 when mkca.document_type=2 then pha_r.segment1||'-'||pra.release_num when mkca.document_type=3 then prha.segment1 when mkca.document_type=4 then mtrh.request_number when mkca.document_type in (5,6,7,8) then we.wip_entity_name end document_number, xxen_util.user_name(mkca.last_updated_by) activity_updated_by, US Show Activity 12 110 2=2 mkca.creation_date>=:activity_date_from Date US Activity Date From Restricts the activity rows shown with Show Activity. 13 120 2=2 mkca.creation_date<:activity_date_to+1 Date :$flex$.activity_date_from US Activity Date To 80D317026C9A4284B32E409119940908 Kanban Card Activity Y Kanban card status and replenishment history: every supply status change of each card with the replenishment document (purchase order, release, move order, job). ENGINATICS 4 ACTIVITY_DATE -4 3 CARD_NUMBER 3 2 ITEM 2 1 ORGANIZATION_CODE 1 Show Activity Y S Site B06FF53109D24F18A537AC9F28BCC2CA Kanban Pull Sequences Kanban pull sequences (production) with source, supplier, lead time, kanban size and number of cards, one row per pull sequence. ENGINATICS 20 AGGREGATION_TYPE 21 ALLOCATION_PERCENT 14 CALCULATE 18 FIXED_LOT_MULTIPLIER 2 ITEM 2 3 ITEM_DESCRIPTION 15 KANBAN_SIZE 5 LOCATOR 17 MINIMUM_ORDER_QUANTITY 16 NUMBER_OF_CARDS 1 ORGANIZATION_CODE 1 12 PRODUCTION_LINE 13 REPLENISHMENT_LEAD_TIME 19 SAFETY_STOCK_DAYS 11 SOURCE_LOCATOR 9 SOURCE_ORGANIZATION 10 SOURCE_SUBINVENTORY 6 SOURCE_TYPE 4 SUBINVENTORY 3 7 SUPPLIER 8 SUPPLIER_SITE 22 UOM Pull Sequences Only Y S Site