[Home] [Help]
SELECT msik.concatenated_segments||':'|| wip_sfcb_utilities.date_to_displaydate_tz(wfs.scheduled_completion_date)||':'|| wsg.schedule_group_name||':'||wfs.build_sequence "NODE_LABEL", wfs.organization_id, /* fk */ wfs.line_id, /* fk */ wfs.wip_entity_id, /* pk */ bos.operation_sequence_id, /* pk */ bos.standard_operation_id, /* fk */ wfs.schedule_number, wfs.scheduled_start_date, wfs.scheduled_completion_date, bor.common_routing_sequence_id as routing_sequence_id, bos.operation_seq_num, wsg.schedule_group_name, wfs.build_sequence, wfs.primary_item_id, msik.concatenated_segments "ASSEMBLY_NAME", wl.line_code, wfs.end_item_unit_number, wfs.planned_quantity, wfs.quantity_completed, wfs.alternate_bom_designator, wfs.alternate_routing_designator, wfs.bom_revision, wfs.bom_revision_date, wfs.routing_revision, wfs.routing_revision_date, mso.concatenated_segments "SALES_ORDER_NUMBER", pjm_project.all_proj_idtoname(wfs.project_id), pjm_project.all_task_idtoname(wfs.task_id), mkc.kanban_card_number, wfs.current_line_operation, 1 "NODE_TYPE", bos.standard_operation_code, msik.description "ASSEMBLY_DESCRIPTION", fnd_date.date_to_displaydate(wfs.scheduled_completion_date) "SCHEDULED_COMPL_DISPLAYDATE" from mtl_kanban_cards mkc, mtl_sales_orders_kfv mso, wip_lines wl, mtl_system_items_kfv msik, wip_schedule_groups wsg, bom_operation_sequences_v bos, bom_operational_routings bor, wip_flow_schedules wfs WHERE wfs.scheduled_flag = 1 and bor.organization_id = wfs.organization_id and bor.assembly_item_id = wfs.primary_item_id and bor.line_id = wfs.line_id and bor.cfm_routing_flag = 1 and decode(bor.alternate_routing_designator, null,'@@@@@@@',bor.alternate_routing_designator) = decode(wfs.alternate_routing_designator, null, '@@@@@@@', wfs.alternate_routing_designator) and bos.operation_type = 3 /* line operation */ and bos.routing_sequence_id = bor.common_routing_sequence_id and wsg.organization_id (+) = wfs.organization_id and wsg.schedule_group_id (+) = wfs.schedule_group_id and wl.line_id = wfs.line_id and wl.organization_id = wfs.organization_id and msik.organization_id = wfs.organization_id and msik.inventory_item_id = wfs.primary_item_id and mso.sales_order_id (+) = wfs.demand_source_header_id and mkc.kanban_card_id (+) = wfs.kanban_card_id and wfs.status <> 2 and WIP_SFCB_Utilities.line_op_is_pending ( BOS.operation_seq_num, BOR.common_routing_sequence_id, WFS.primary_item_id, WFS.organization_id, WFS.alternate_routing_designator, WFS.current_line_operation /* will change to current line operation */ ) = 1
SELECT MSIK.CONCATENATED_SEGMENTS||':'|| WIP_SFCB_UTILITIES.DATE_TO_DISPLAYDATE_TZ(WFS.SCHEDULED_COMPLETION_DATE)||':'|| WSG.SCHEDULE_GROUP_NAME||':'||WFS.BUILD_SEQUENCE "NODE_LABEL"
, WFS.ORGANIZATION_ID
, /* FK */ WFS.LINE_ID
, /* FK */ WFS.WIP_ENTITY_ID
, /* PK */ BOS.OPERATION_SEQUENCE_ID
, /* PK */ BOS.STANDARD_OPERATION_ID
, /* FK */ WFS.SCHEDULE_NUMBER
, WFS.SCHEDULED_START_DATE
, WFS.SCHEDULED_COMPLETION_DATE
, BOR.COMMON_ROUTING_SEQUENCE_ID AS ROUTING_SEQUENCE_ID
, BOS.OPERATION_SEQ_NUM
, WSG.SCHEDULE_GROUP_NAME
, WFS.BUILD_SEQUENCE
, WFS.PRIMARY_ITEM_ID
, MSIK.CONCATENATED_SEGMENTS "ASSEMBLY_NAME"
, WL.LINE_CODE
, WFS.END_ITEM_UNIT_NUMBER
, WFS.PLANNED_QUANTITY
, WFS.QUANTITY_COMPLETED
, WFS.ALTERNATE_BOM_DESIGNATOR
, WFS.ALTERNATE_ROUTING_DESIGNATOR
, WFS.BOM_REVISION
, WFS.BOM_REVISION_DATE
, WFS.ROUTING_REVISION
, WFS.ROUTING_REVISION_DATE
, MSO.CONCATENATED_SEGMENTS "SALES_ORDER_NUMBER"
, PJM_PROJECT.ALL_PROJ_IDTONAME(WFS.PROJECT_ID)
, PJM_PROJECT.ALL_TASK_IDTONAME(WFS.TASK_ID)
, MKC.KANBAN_CARD_NUMBER
, WFS.CURRENT_LINE_OPERATION
, 1 "NODE_TYPE"
, BOS.STANDARD_OPERATION_CODE
, MSIK.DESCRIPTION "ASSEMBLY_DESCRIPTION"
, FND_DATE.DATE_TO_DISPLAYDATE(WFS.SCHEDULED_COMPLETION_DATE) "SCHEDULED_COMPL_DISPLAYDATE"
FROM MTL_KANBAN_CARDS MKC
, MTL_SALES_ORDERS_KFV MSO
, WIP_LINES WL
, MTL_SYSTEM_ITEMS_KFV MSIK
, WIP_SCHEDULE_GROUPS WSG
, BOM_OPERATION_SEQUENCES_V BOS
, BOM_OPERATIONAL_ROUTINGS BOR
, WIP_FLOW_SCHEDULES WFS
WHERE WFS.SCHEDULED_FLAG = 1
AND BOR.ORGANIZATION_ID = WFS.ORGANIZATION_ID
AND BOR.ASSEMBLY_ITEM_ID = WFS.PRIMARY_ITEM_ID
AND BOR.LINE_ID = WFS.LINE_ID
AND BOR.CFM_ROUTING_FLAG = 1
AND DECODE(BOR.ALTERNATE_ROUTING_DESIGNATOR
, NULL
, '@@@@@@@'
, BOR.ALTERNATE_ROUTING_DESIGNATOR) = DECODE(WFS.ALTERNATE_ROUTING_DESIGNATOR
, NULL
, '@@@@@@@'
, WFS.ALTERNATE_ROUTING_DESIGNATOR)
AND BOS.OPERATION_TYPE = 3 /* LINE OPERATION */
AND BOS.ROUTING_SEQUENCE_ID = BOR.COMMON_ROUTING_SEQUENCE_ID
AND WSG.ORGANIZATION_ID (+) = WFS.ORGANIZATION_ID
AND WSG.SCHEDULE_GROUP_ID (+) = WFS.SCHEDULE_GROUP_ID
AND WL.LINE_ID = WFS.LINE_ID
AND WL.ORGANIZATION_ID = WFS.ORGANIZATION_ID
AND MSIK.ORGANIZATION_ID = WFS.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = WFS.PRIMARY_ITEM_ID
AND MSO.SALES_ORDER_ID (+) = WFS.DEMAND_SOURCE_HEADER_ID
AND MKC.KANBAN_CARD_ID (+) = WFS.KANBAN_CARD_ID
AND WFS.STATUS <> 2
AND WIP_SFCB_UTILITIES.LINE_OP_IS_PENDING ( BOS.OPERATION_SEQ_NUM
, BOR.COMMON_ROUTING_SEQUENCE_ID
, WFS.PRIMARY_ITEM_ID
, WFS.ORGANIZATION_ID
, WFS.ALTERNATE_ROUTING_DESIGNATOR
, WFS.CURRENT_LINE_OPERATION /* WILL CHANGE TO CURRENT LINE OPERATION */ ) = 1
|
|
|
|