Advertisement
dicaribapak11

vendor_has_tendering

Jun 27th, 2025
1,357
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 2.94 KB | Writing | 0 0
  1. -- tendering.vendor_has_tendering source
  2.  
  3. CREATE OR REPLACE VIEW tendering.vendor_has_tendering
  4. AS SELECT vs.vendor_id,
  5.     vs.proposal_tender_id,
  6.     vs.registration_status,
  7.     vs.registration_quotation,
  8.     vs.registration_aanwijzing,
  9.         CASE
  10.             WHEN vs.evaluasi_teknis::text = 'lolos'::text THEN 2
  11.             ELSE 1
  12.         END AS current_step,
  13.     ( SELECT count(qi.id) AS count
  14.            FROM tendering.quotation_items qi
  15.           WHERE (qi.quotation_id IN ( SELECT q.id
  16.                    FROM tendering.quotations q
  17.                   WHERE vs.vendor_id = q.vendor_id AND q.proposal_tender_id = vs.proposal_tender_id))) AS quotation_items,
  18.     pt.id,
  19.     pt.uuid,
  20.     pt.number,
  21.     pt.purchasing_org_id,
  22.     pt.metode_pengadaan_id,
  23.     pt.metode_penyampaian_id,
  24.     pt.title,
  25.     pt.jadwal_tender_code,
  26.     pt.code,
  27.     pt.name,
  28.     pt."order",
  29.     pt.start_date,
  30.     pt.start_time,
  31.     pt.end_date,
  32.     pt.end_time,
  33.     pt.approved_at,
  34.     pt.status,
  35.     pt.awarding_status,
  36.     ( SELECT concat(ptj2.end_date, ' ', ptj2.end_time) AS concat
  37.            FROM tendering.proposal_tender_jadwal ptj2
  38.           WHERE ptj2.proposal_tender_id = pt.id AND ptj2.jadwal_tender_code::text = 'JT004'::text) AS bo_start_date,
  39.     pt.is_reject
  40.    FROM ( SELECT DISTINCT vendor_selections.vendor_id,
  41.             vendor_selections.proposal_tender_id,
  42.             vendor_selections.registration_status,
  43.             vendor_selections.registration_quotation,
  44.             vendor_selections.registration_aanwijzing,
  45.             vendor_selections.evaluasi_teknis
  46.            FROM tendering.vendor_selections) vs
  47.      LEFT JOIN ( SELECT pt_1.id,
  48.             pt_1.uuid,
  49.             pt_1.metode_pengadaan_id,
  50.             pt_1.metode_penyampaian_id,
  51.             pt_1.title,
  52.             pt_1.purchasing_org_id,
  53.             pt_1.approved_at,
  54.             pt_1.status,
  55.             ptj.jadwal_tender_code,
  56.             tj.code,
  57.             pt_1.number,
  58.             tj.name,
  59.             tj."order",
  60.             ptj.start_date,
  61.             ptj.start_time,
  62.             ptj.end_date,
  63.             ptj.end_time,
  64.             awarding.status_text AS awarding_status,
  65.             pt_1.is_reject
  66.            FROM tendering.proposal_tender pt_1
  67.              LEFT JOIN tendering.proposal_tender_jadwal ptj ON pt_1.id = ptj.proposal_tender_id
  68.              LEFT JOIN tendering.jadwal_tender tj ON tj.id = ptj.jadwal_tender_id AND tj.metode_penyampaian_id::text = pt_1.metode_penyampaian_id::text
  69.              LEFT JOIN tendering.awarding ON pt_1.id = awarding.proposal_tender_id
  70.           WHERE now() >= to_timestamp(concat(ptj.start_date, ' ', ptj.start_time), 'YYYY-MM-DD HH24:MI:SS'::text) AND now() <= to_timestamp(concat(ptj.end_date, ' ', ptj.end_time), 'YYYY-MM-DD HH24:MI:SS'::text)) pt ON vs.proposal_tender_id = pt.id
  71.   WHERE pt.start_date IS NOT NULL AND pt.approved_at IS NOT NULL AND pt.status = 'H8'::bpchar
  72.   ORDER BY vs.vendor_id, pt.id, pt."order";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement