bom临时

bom

SELECT
    trim( c.JIBU ) || '-' || trim( c.PARTNO ) || '-' || trim( c.KOTEI ) || '-' || trim( c.DATAKU ) || '-' || trim( c.KAISEQ ) || trim( c.KSNBIT ) || trim( to_char( a.seqno, '000000' ) ) || '-' || trim( c.DATAKU ) || '-' || trim( a.KAISEQ ) || '-' || a.endymn || trim( b.DATAKU ) || trim( b.KAISEQ ) || trim( b.KSNBIT ) bom_id,
    trim( b.JIBU ) || trim( b.PARTNO ) || trim( b.KOTEI ) || trim( b.DATAKU ) || trim( b.KAISEQ ) || trim( b.KSNBIT ) node_id,
    trim( c.JIBU ) || trim( c.PARTNO ) || trim( c.KOTEI ) || trim( c.DATAKU ) || trim( c.KAISEQ ) || trim( c.KSNBIT ) parent_id,
    a.CDATE version,
    trim( to_char( a.seqno, '000000' ) ) display_sequence,
    2 bom_level,
    a.asuryo consuming_quantity,
    a.tenku unfold_category,
    to_date( a.strymn, 'yyyymmdd' ) compose_start_time,
    to_date( CASE a.endymn WHEN '99999999' THEN '99991231' ELSE a.endymn END, 'yyyymmdd' ) compose_end_time,
    SYSDATE last_receive_time,
    a.nteban production_leadtime,
    a.jibu company_code,
    'ALPHA' creator,
    'ALPHA' creator_name,
    SYSDATE create_time,
    NULL modifier,
    NULL modifier_name,
    NULL modify_time,
    'YES' valid 
FROM
    ND_EPS_TBL_V1 a
    INNER JOIN nd_zub_tbl b ON b.partno = a.bpartno 
    AND b.kotei = a.bkotei 
    AND b.endymn >= to_char( trunc( add_months( last_day( SYSDATE ), - 1 ) + 1 ), 'yyyymmdd' ) 
    AND b.jibu = a.jibu 
    AND substr( b.partno, 1, 1 ) <> ' ' 
    AND b.KSNBIT < 2
    INNER JOIN nd_zub_tbl c ON c.partno = a.apartno 
    AND c.kotei = a.akotei 
    AND c.endymn >= to_char( trunc( add_months( last_day( SYSDATE ), - 1 ) + 1 ), 'yyyymmdd' ) 
    AND c.jibu = a.jibu 
    AND substr( c.partno, 1, 1 ) <> ' ' 
    AND c.KSNBIT < 2 
WHERE
    a.jibu IN ( '42', '41', '70' ) 
    AND trim( a.strymn ) IS NOT NULL 
    AND trim( a.endymn ) IS NOT NULL 
    AND trim( a.apartno ) NOT IN ( SELECT trim( model ) FROM gst_prdmdl WHERE div = a.jibu ) UNION ALL
SELECT
    trim( c.div ) || '-' || trim( c.model ) || '-' || trim( to_char( a.seqno, '000000' ) ) || '-' || trim( a.KAISEQ ) || '-' || a.endymn || '-' || trim( b.KAISEQ ) || trim( b.KSNBIT ) bom_id,
    trim( b.JIBU ) || trim( b.PARTNO ) || trim( b.KOTEI ) || trim( b.DATAKU ) || trim( b.KAISEQ ) || trim( b.KSNBIT ) node_id,
    trim( DIV ) || trim( MODEL ) || trim( PP ) || trim( LREV ) parent_id,
    a.CDATE version,
    trim( to_char( a.seqno, '000000' ) ) display_sequence,
    1 bom_level,
    a.asuryo consuming_quantity,
    a.tenku unfold_category,
    to_date( a.strymn, 'yyyymmdd' ) compose_start_time,
    to_date( CASE a.endymn WHEN '99999999' THEN '99991231' ELSE a.endymn END, 'yyyymmdd' ) compose_end_time,
    SYSDATE last_receive_time,
    a.nteban production_leadtime,
    a.jibu company_code,
    'ALPHA' creator,
    'ALPHA' creator_name,
    SYSDATE create_time,
    NULL modifier,
    NULL modifier_name,
    NULL modify_time,
    'YES' valid 
FROM
    ND_EPS_TBL_V1 a
    INNER JOIN nd_zub_tbl b ON b.partno = a.bpartno 
    AND b.kotei = a.bkotei 
    AND b.endymn >= to_char( trunc( add_months( last_day( SYSDATE ), - 1 ) + 1 ), 'yyyymmdd' ) 
    AND b.jibu = a.jibu 
    AND substr( b.partno, 1, 1 ) <> ' ' 
    AND b.KSNBIT < 2
    INNER JOIN GST_PRDMDL c ON trim( c.model ) = trim( a.apartno ) 
    AND c.lrev = '999' 
    AND c.div = a.jibu 
    AND substr( c.model, 1, 1 ) <> ' ' 
WHERE
    a.jibu IN ( '42', '41', '70' ) 
    AND trim( a.strymn ) IS NOT NULL 
    AND trim( a.endymn ) IS NOT NULL 
    AND trim( a.apartno ) IN ( SELECT trim( model ) FROM gst_prdmdl WHERE div = a.jibu ) 
    AND a.ksnbit < 2 
    AND a.strymn <> a.endymn 
    AND substr( a.apartno, 1, 1 ) <> ' ' 
    AND a.eps = 'EPK' UNION ALL
SELECT
    trim( c.DIV ) || '-' || 'ROOT' || '-' || trim( c.model ) || '-' || '000010' || '-' || trim( c.LREV ) bom_id,
    trim( c.DIV ) || trim( c.MODEL ) || trim( c.PP ) || trim( c.LREV ) node_id,
    NULL parent_id,
    c.CDATE version,
    '000010' display_sequence,
    0 bom_level,
    1 consuming_quantity,
    '4' unfold_category,
    to_date( c.strym, 'yyyymm' ) compose_start_time,
    to_date( CASE c.endym WHEN '999999' THEN '999912' ELSE c.endym END, 'yyyymm' ) compose_end_time,
    SYSDATE last_receive_time,
    0 production_leadtime,
    c.Div company_code,
    'ALPHA' creator,
    'ALPHA' creator_name,
    SYSDATE create_time,
    NULL modifier,
    NULL modifier_name,
    NULL modify_time,
    'YES' valid 
FROM
    GST_PRDMDL c 
WHERE
    c.lrev = '999' 
    AND c.div IN ( '42', '70', '41' ) 
    AND substr( c.MODEL, 1, 1 ) <> ' '

material

select trim(DIV) || trim(MODEL) || trim(PP) || trim(LREV) material_id,
       trim(a.model) material_name,
       'FINISHED_GOODS' material_type,
       trim(a.model) drawing_no,
       ' ' project_no,
       null safety_stock_volume,
       null safety_stock_period,
       null stock_lower_bound,
       null stock_upper_bound,
       0 freezing_period,
       to_date(a.strym, 'yyyymm') effective_start_time,
       to_date(case a.endym
                 when '999999' then
                  '999912'
                 else
                  a.endym
               end,
               'yyyymm') effective_end_time,
       null work_area,
       null purchase_category,
       null unit,
       null centralized_purchase,
       null trade_drawing_no,
       case a.lrev when '999' then 'YES' else 'NO' end product_valid,
             null material_type_no,
       a.div company_code,
       'ALPHA' creator,
       'ALPHA' creator_name,
       sysdate create_time,
       null modifier,
       null modifier_name,
       null modify_time,
       'YES' valid
  from gst_prdmdl a
 where a.div in ('42','70','41') and a.lrev = '999' and substr(a.MODEL,1,1)<>' '
union all
select trim(JIBU) || trim(PARTNO) || trim(KOTEI) || trim(DATAKU) || trim(KAISEQ) || trim(KSNBIT) material_id,
       ' ' material_name,
       'RAW_MATERIAL' material_type,
       trim(a.partno) drawing_no,
       a.kotei project_no,
       null safety_stock_volume,
       null safety_stock_period,
       null stock_lower_bound,
       null stock_upper_bound,
       0 freezing_period,
       to_date(a.strymn, 'yyyymmdd') effective_start_time,
       to_date(case a.endymn
                 when '99999999' then
                  '99991231'
                 else
                  a.endymn
               end,
               'yyyymmdd') effective_end_time,
       a.kojiku work_area,
       a.hatku purchase_category,
       a.hatani unit,
       'NO' centralized_purchase,
       null trade_drawing_no,
       null product_valid,
             a.bncd material_type_no,
       a.jibu company_code,
       'ALPHA' creator,
       'ALPHA' creator_name,
       sysdate create_time,
       null modifier,
       null modifier_name,
       null modify_time,
       'YES' valid
  from ND_ZUB_TBL a
 where a.jibu in ('42','70','41') and a.KSNBIT<2 and trim(a.STRYMN) is not null

progress

select trim(b.JIBU) || trim(b.PARTNO) || trim(b.KOTEI) || trim(b.DATAKU) || trim(b.KAISEQ) || trim(b.KSNBIT) || trim(a.brusn) as  material_progress_id,
       trim(b.JIBU) || trim(b.PARTNO) || trim(b.KOTEI) || trim(b.DATAKU) || trim(b.KAISEQ) || trim(b.KSNBIT) material_id,
       a.brusn progress_no,
       a.partno drawing_no,
       a.kotei project_no,
       to_date(a.strymn, 'yyyymmdd')  progress_start_time,
       to_date(case a.endymn
                 when '99999999' then
                  '99991231'
                 else
                  a.endymn
               end,
               'yyyymmdd') progress_end_time,
       case a.endymn when '99999999' then 'YES' else 'NO' end is_latest,
       a.jibu company_code,
       'ALPHA' creator,
       'ALPHA' creator_name,
       sysdate create_time,
       null modifier,
       null modifier_name,
       null modify_time,
       'YES' valid
  from nd_epw_tbl_v1 a
  left join nd_zub_tbl b on a.partno = b.partno and a.kotei = b.kotei and b.endymn = '99999999' and b.jibu=a.jibu
 where a.jibu in ('42','70','41') and b.ksnbit<2 and trim(a.STRYMN) is not null