SELECT &&1 || '|' || to_char(sysdate, 'YYYYMMDD') || 'T' ||
        to_char(sysdate, 'HH24MM') || '00' || '|' || item.segment1 || '|' || 'IM' || '|' ||
        substr(item.description, 1, 50) || '|' || item.primary_UOM_Code || '|' || case
          when item.unit_weight > 0 then
           item.unit_weight
          when item.unit_weight <= 0 then
           9999.9999
          else
           9999.999
        end || '|' || NVL(item.weight_uom_code, 'KG') || '|' || 0 || '|' ||
        'USD' || '|' || 'N' || '|' || '' || '|' || '' || '|' || 'E' || '|' || '' || '|' || '' || '|' ||
        replace(glo_buyer.agent_name, '''', '') || '|' || '' || '|' || case
          when length(hts.tariff_code) < 10 THEN
           '1111111111'
          when hts.tariff_code Is Null THEN
           '1111111111'
          else
           hts.tariff_code
        END || '|' || 'M' || '|' || 'XX' || '|' || 'MULTIPLE' || '|' || '' || '|' || case
          when cst.item_cost > 0 then
           cst.item_cost
          when lacst.item_cost > 0 then
           lacst.item_cost
        --when pendingcst.item_cost > 0 then pendingcst.item_cost
          else
           0
        end || '|' ||
       --cst.item_cost||'|'||
        0 || '|' || 'USD' || '|' || 0 || '|' || 0 || '|' || '' || '|' || 0 || '|' || 0 || '|' || '' || '|' || '' || '|' || '' || '|' || 0 || '|' || '' || '|' || 0 || '|' || '' || '|' ||
        glo_buyer.agent_name || '|' || '' || '|' || ''
  FROM apps.mtl_system_items_b item