API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 18th, 2018
101
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 12.63 KB | None | 0 0
raw download clone embed print report
  1.  SELECT iib.item,
  2.           iib2.item AS comp_item,
  3.           iib.item_description,
  4.           ood.organization_name,
  5.           ood.organization_code,    
  6.           iib.planner_code,
  7.           iib.master_scheduler,
  8.           iib.mlx_package,
  9.           iib.mlx_supplier_package,
  10.           iib.mlx_inventory_project,
  11.           dev.element_value AS product_name,
  12.           supply.requested_due_date,                                                                
  13.           supply.completion_date,
  14.           supply.request_date_year,
  15.           supply.completion_date_year,
  16.           supply.qty_out,
  17.           (TO_CHAR(TO_DATE(sic.from_date,'DD-MON-YYYY HH24:MI:SS') ,'WWYYYY')) AS from_date,
  18.           (TO_CHAR(TO_DATE(sic.TO_DATE,'DD-MON-YYYY HH24:MI:SS') ,'WWYYYY')) AS TO_DATE,
  19.           sic.capacity_per_day          
  20.      FROM XXMLX_ITEM_INFORMATION_BASE_MV iib,
  21.           XXMLX_ITEM_INFORMATION_BASE_MV iib2,
  22.           MTL_DESCR_ELEMENT_VALUES dev,
  23.           ORG_ORGANIZATION_DEFINITIONS ood,
  24.           pos_supplier_item_capacity_v sic,
  25.           mtl_system_items msi,
  26.           (
  27.             SELECT 'Inventory'                     AS supply_location
  28.                  , moq.subinventory_code
  29.                  , moq.lot_number
  30.                  , moq.primary_transaction_quantity AS qty_out
  31.                  , NULL                             AS rcv_transaction
  32.                  , moq.date_received
  33.                  , moq.last_update_date
  34.                  , moq.inventory_item_id
  35.                  , moq.organization_id
  36.                  , NULL                             AS gen_object_id
  37.                  , NULL                             AS wip_entity_id
  38.                  , NULL                             AS current_operation_code
  39.                  , NULL                             AS operation_seq_num
  40.                  , NULL                             AS job_operation_seq_num
  41.                  , NULL                             AS department
  42.                  , NULL                             AS step
  43.                  , NULL                             AS start_date
  44.                  , NULL                             AS requested_due_date
  45.                  , NULL                             AS completion_date
  46.                  , NULL                             AS firm_planned_flag
  47.                  , NULL                             AS production_remarks
  48.                  , NULL                             AS error_handling
  49.                  , NULL                             AS hold_reason
  50.                  , NULL                             AS completion_subinventory
  51.                  , NULL                             AS handlerprober
  52.                  , NULL                             AS taper
  53.                  , ' '                              AS demand_class
  54.                  , NULL                             AS completion_date_year
  55.                  , NULL                             AS completion_date_quarter
  56.                  , NULL                             AS completion_date_month
  57.                  , NULL                             AS completion_date_week
  58.                  , NULL                             AS completion_date_day
  59.                  , NULL                             AS request_date_year
  60.                  , NULL                             AS request_date_quarter
  61.                  , NULL                             AS request_date_month
  62.                  , NULL                             AS request_date_week
  63.                  , NULL                             AS request_date_day
  64.                  , NVL( xxmlx_sfm_tools_pkg.get_lot_wafer_qty( moq.lot_number ),moq.primary_transaction_quantity ) AS wafer_qty_out
  65.                  , ''                               AS location_name
  66.                  , ''                               AS shipment_number
  67.                  , TO_DATE( '' )                    AS receipt_date
  68.                  , NULL                             AS planning_remarks
  69.             FROM xxmlx_onhand_qty_prod_overvw_v moq
  70.             WHERE 1 = 1
  71.             AND moq.is_consigned = 2
  72.             UNION ALL
  73.             SELECT 'WIP'                          AS supply_location
  74.                  , DECODE( mlu.meaning,'Released','SFM Released','SFM On Hold' ) AS subinventory_code
  75.                  , we.wip_entity_name             AS lot_number
  76.                  , wo.quantity_in_queue + wo.quantity_running + wo.quantity_waiting_to_move AS qty_out
  77.                  , NULL                           AS rcv_transaction
  78.                  , wdj.creation_date              AS date_received
  79.                  , wdj.last_update_date           AS last_update_date
  80.                  , we.primary_item_id             AS inventory_item_id -- TODO - Check why WIP Part
  81.                  , we.organization_id             AS organization_id
  82.                  , we.gen_object_id               AS gen_object_id
  83.                  , we.wip_entity_id               AS wip_entity_id
  84.                  , wco.operation_description      AS current_operation_code
  85.                  , wco.operation_seq_num          AS operation_seq_num
  86.                  , wo.operation_seq_num           AS job_operation_seq_num
  87.                  , bd.department_code             AS department
  88.                  , ml.meaning                     AS step
  89.                  , wdj.scheduled_start_date       AS start_date
  90.                  , wdj.due_date                   AS requested_due_date
  91.                  , wdj.scheduled_completion_date  AS completion_date
  92.                  , DECODE( wdj.firm_planned_flag,1,'Yes',2,'No' ) AS firm_planned_flag
  93.                  , wdj.attribute1                 AS production_remarks
  94.                  , wdj.attribute2                 AS error_handling
  95.                  , wdj.attribute3                 AS hold_reason
  96.                  , wdj.completion_subinventory    AS completion_subinventory
  97.                  , wdj.attribute4                 AS handlerprober
  98.                  , wdj.attribute5                 AS taper
  99.                  , wdj.demand_class               AS demand_class
  100.                  , TO_CHAR( wdj.scheduled_completion_date,'IYYY' ) AS completion_date_year
  101.                  , TO_CHAR( wdj.scheduled_completion_date,'Q' )    AS completion_date_quarter
  102.                  , TO_CHAR( wdj.scheduled_completion_date,'MM' )   AS completion_date_month
  103.                  , xxmlx_to_number( TO_CHAR( NEXT_DAY( wdj.scheduled_completion_date,'sunday' ),'iw' ) ) AS completion_date_week
  104.                  , TO_CHAR( wdj.scheduled_completion_date,'DD' )   AS completion_date_day
  105.                  , TO_CHAR( wdj.due_date,'IYYY' ) AS request_date_year
  106.                  , TO_CHAR( wdj.due_date,'Q' )    AS request_date_quarter
  107.                  , TO_CHAR( wdj.due_date,'MM' )   AS request_date_month
  108.                  , xxmlx_to_number( TO_CHAR( NEXT_DAY( wdj.due_date,'sunday' ),'iw' ) ) AS request_date_week
  109.                  , TO_CHAR( wdj.due_date,'DD' )   AS request_date_day
  110.                  , NVL(xxmlx_sfm_tools_pkg.get_lot_wafer_qty( we.wip_entity_name )
  111.                        ,wo.quantity_in_queue + wo.quantity_running + wo.quantity_waiting_to_move
  112.                   )                               AS wafer_qty_out
  113.                  , ''                             AS location_name
  114.                  , ''                             AS shipment_number
  115.                  , TO_DATE( '' )                  AS receipt_date
  116.                  , NULL                           AS planning_remarks
  117.             FROM mfg_lookups ml
  118.                  , wip_operations wo
  119.                  , wip_entities we
  120.                  , wip_discrete_jobs wdj
  121.                  , mfg_lookups mlu
  122.                  , bom_standard_operations bso
  123.                  , wsm_copy_operations wco
  124.                  , bom_departments bd
  125.             WHERE 1 = 1
  126.             AND ml.lookup_type = 'WIP_INTRAOPERATION_STEP'
  127.             AND ml.lookup_code = DECODE( wo.quantity_in_queue, 0 ,DECODE( wo.quantity_running,0,3,2 ),1 )
  128.             AND we.wip_entity_id = wo.wip_entity_id
  129.             AND wo.standard_operation_id = bso.standard_operation_id (+)
  130.             AND NVL( bso.operation_type,1 ) = 1
  131.             AND bso.line_id IS NULL
  132.             AND ( wo.quantity_in_queue != 0 OR wo.quantity_running != 0 OR wo.quantity_waiting_to_move != 0)
  133.             AND wo.operation_sequence_id = wco.operation_sequence_id (+)
  134.             AND wo.wip_entity_id = wco.wip_entity_id (+)
  135.             AND wdj.wip_entity_id = we.wip_entity_id
  136.             AND wdj.status_type IN ( 3, 6 )
  137.             AND wdj.organization_id = we.organization_id
  138.             AND mlu.lookup_code = wdj.status_type
  139.             AND mlu.lookup_type = 'WIP_JOB_STATUS'
  140.             AND bd.department_id = bso.department_id
  141.             UNION ALL
  142.             SELECT CASE WHEN ms.supply_type_code = 'RECEIVING'
  143.                         THEN'RECEIVING'
  144.                         WHEN ms.supply_type_code = 'SHIPMENT'
  145.                         THEN'INTRANSIT'
  146.                         END AS supply_location
  147.                  , CASE WHEN ms.supply_type_code = 'RECEIVING'
  148.                         THEN 'RECEIVING'
  149.                         WHEN ms.supply_type_code = 'SHIPMENT'
  150.                         THEN 'INTRANSIT'
  151.                         END AS subinventory_code
  152.                  , rls.lot_num           AS lot_number
  153.                  , NVL( rls.quantity,ms.quantity ) AS qty_out
  154.                  , ms.rcv_transaction_id AS rcv_transaction
  155.                  , ms.creation_date      AS date_received
  156.                  , ms.last_update_date   AS last_update_date
  157.                  , ms.item_id            AS inventory_item_id
  158.                  , ms.to_organization_id AS organization_id
  159.                  , TO_NUMBER( '' )   AS gen_object_id
  160.                  , NULL              AS wip_entity_id
  161.                  , NULL              AS current_operation_code
  162.                  , NULL              AS operation_seq_num
  163.                  , NULL              AS job_operation_seq_num
  164.                  , NULL              AS department
  165.                  , NULL              AS step
  166.                  , NULL              AS start_date
  167.                  , NULL              AS requested_due_date
  168.                  , NULL              AS completion_date
  169.                  , NULL              AS firm_planned_flag
  170.                  , NULL              AS production_remarks
  171.                  , NULL              AS error_handling
  172.                  , NULL              AS hold_reason
  173.                  , NULL              AS completion_subinventory
  174.                  , NULL              AS handlerprober
  175.                  , NULL              AS taper
  176.                  , ' '               AS demand_class
  177.                  , NULL              AS completion_date_year
  178.                  , NULL              AS completion_date_quarter
  179.                  , NULL              AS completion_date_month
  180.                  , NULL              AS completion_date_week
  181.                  , NULL              AS completion_date_day
  182.                  , NULL              AS request_date_year
  183.                  , NULL              AS request_date_quarter
  184.                  , NULL              AS request_date_month
  185.                  , NULL              AS request_date_week
  186.                  , NULL              AS request_date_day
  187.                  , NULL              AS wafer_qty_out
  188.                  , hla.location_code AS location_name
  189.                  , NVL( sh.shipment_num,sh.receipt_num ) AS shipment_number
  190.                  , ms.receipt_date   AS receipt_date
  191.                  , mln_from.c_attribute10 AS planning_remarks
  192.             FROM mtl_supply ms
  193.                , rcv_lots_supply rls
  194.                , hr_locations_all hla
  195.                , rcv_shipment_headers sh
  196.                , mtl_lot_numbers  mln_from
  197.             WHERE 1 = 1
  198.             AND ms.shipment_line_id    = rls.shipment_line_id (+)
  199.             AND ms.supply_type_code    IN ('RECEIVING','SHIPMENT')
  200.             AND ms.location_id         = hla.location_id (+)
  201.             AND ms.shipment_header_id  = sh.shipment_header_id (+)
  202.             AND ms.item_id             = mln_from.inventory_item_id(+)
  203.             AND ms.from_organization_id= mln_from.organization_id(+)
  204.             AND (rls.lot_num = mln_from.lot_number OR rls.lot_num  IS NULL)
  205.       ) supply
  206.      WHERE iib.inventory_item_id = dev.inventory_item_id
  207.        AND iib.organization_id = ood.organization_id
  208.        AND dev.element_name (+)     = 'PRODUCT'
  209.        AND supply.inventory_item_id = iib.inventory_item_id
  210.        AND supply.organization_id   = iib.organization_id
  211.        AND iib2.inventory_item_id = sic.item_id;
  212.        AND iib2.organization_id   = 83
  213.        AND iib2.MLX_OSP_SUPPLIER = iib.MLX_OSP_SUPPLIER
  214.        AND iib2.MLX_PACKAGE = iib.MLX_PACKAGE;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!