-- ✅ 项目招投标申请查询
select rownum as rn, t.*, rpd.name_ as proc_def_name_
from ( select *
from ( select t1.*
from ( select hpi.*, pf.*
from ( select *
from ( select * from tpl_wf_hi_procform_t pf ) pf
where pf.bs_app_name_ = 'omp' ) pf
join act_hi_procinst hpi on hpi.proc_inst_id_ = pf.bs_proc_inst_id_
where 1 = 1 and (hpi.proc_def_id_ like '%BidNewProjectProcess%' or
hpi.proc_def_id_ like '%BidExtProjectProcess%') ) t1 ) k
order by k.bs_last_update_date_ desc ) t
join act_re_procdef rpd on t.proc_def_id_ = rpd.id_;
-- ✅ 项目招投标申请查询 主表
select t.competive_bidding_id as competivebiddingid, t.rpojectid as rpojectid,
( select p.project_mp_requ_number from omp_project_t p where p.project_no = t.rpojectid ) as projectmprequnumber,
t.caption as caption, t.project_type as projecttype,
case when t.project_source = 'newomp'
then nvl(( select org.org_name_zh from omp_local_org_t org where to_char(org.id) = t.prodline ),
t.prodline)
else t.prodline
end as prodline,
case when t.project_source = 'newomp'
then nvl(( select org.org_name_zh from omp_local_org_t org where to_char(org.id) = t.subprodline ),
t.subprodline)
else t.subprodline
end as subprodline,
case when t.project_source = 'newomp'
then nvl(( select org.org_name_zh from omp_local_org_t org where to_char(org.id) = t.dept ), t.dept)
else t.dept
end as dept,
case when t.project_source = 'newomp'
then nvl(( select org.org_name_zh from omp_local_org_t org where to_char(org.id) = t.develop ), t.develop)
else t.develop
end as developname, t.business_model as businessmodel, t.site_status as sitestatus, t.datatype as datatype,
t.workaddress as workaddress, t.team_operation_model as teamoperationmodel,
to_char(t.planbegindate, 'yyyy-mm-dd') as planbegindate, to_char(t.planenddate, 'yyyy-mm-dd') as planenddate,
to_char(opt.begin_date, 'yyyy-mm-dd') as realstartdate, to_char(opt.end_date, 'yyyy-mm-dd') as realenddate,
t.tender_type as tendertype, t.rank_type as ranktype, t.type as type,
to_char(t.tender_end_date, 'yyyy-mm-dd') as tenderenddate,
to_char(t.tender_confirm_date, 'yyyy-mm-dd') as tenderconfirmdate,
case t.node when '1'
then '招投标申请'
when '2'
then '采购发标'
when '3'
then '供方回标'
when '4'
then '技术开标'
when '5'
then '确认回标'
when '7'
then '关闭'
end as node, t.work_no as workno, t.work_name as workname, t.remark as remark,
t.winbidding_supply_name as winbiddingsupplyname, t.winbidding_supply_num as winbiddingsupplynum,
to_char(t.creation_date, 'yyyy-mm-dd') as createdate,
to_char(t.last_update_date, 'yyyy-mm-dd') as lastupdatetime, t.requiredyear as requiredyear, t.bg as bg,
t.isinherit as isinherit, t.inheritsupply as inheritsupply, t.budgetprice_notax as budgetpricenotax,
t.isbacktostart as isbacktostart, t.isfailedtoego as isfailedtoego, t.packsendmode as packsendmode,
t.fm_type as fmtype, t.project_source as projectsource, t.keyrole_num as keyrolenum,
t.project_budgetprice as projectbudgetprice, t.bidded_from_rsm as biddedfromrsm, t.target_price as targetprice,
t.business_remark as businessremark, t.first_date as firstdate, t.second_date as seconddate,
t.has_share_control as hassharecontrol, t.purchase_confirm_role as purchaseconfirmrole,
( select count(1) from omp_project_emp_plan_t ep where ep.project_no = t.rpojectid ) as empplancount,
negotia_tion_mode as negotiationmode, org_v.l1_org_name as bg, org_v.l2_org_name as bu, org_v.l3_org_name as pdu,
org_v.l4_org_name as develop
from omp_bid_project_t t
left join omp_project_prpo_info_v opt
on t.rpojectid = opt.project_no and opt.project_status not in ('CLOSE', 'DRAFT')
left join omp_local_org_t org_v on org_v.id = opt.local_org_id
left join omp_frame_t ft
on t.rpojectid = ft.frame_no and ft.frame_no not in ('CLOSE', 'DRAFT')
left join omp_bid_supply_detail_t sutt
on sutt.competive_bidding_id = t.competive_bidding_id and sutt.supply_num = t.winbidding_supply_num
left join ( select p.business_key_, p.proc_inst_id_, wpf.bs_created_by_
from act_hi_procinst p
join act_re_procdef rpd on p.proc_def_id_ = rpd.id_
join tpl_wf_hi_procform_t wpf on p.proc_inst_id_ = wpf.bs_proc_inst_id_
where rpd.key_ in ('BidNewProjectProcess', 'BidExtProjectProcess')
and p.business_key_ like 'R%' ) p1 on p1.business_key_ = t.competive_bidding_id
where t.competive_bidding_id = 'R202509110001';
-- ✅ 项目招投标申请查询 附件
select t.competive_bidding_id as competivebiddingid, t.supply_num as supplynum, t.supply_nme as supplynme,
t.attachmentid as attachmentid, t.file_title as filetitle, t.type as type, tat.filename as filename,
tat.filepath as filepath, tat.batch_id as batchid, tat.download_type as downloadtype
from omp_bid_fiels_t t
left join omp_bid_file_v tat on t.attachmentid = tat.attachmentid
where 1 = 1 and t.competive_bidding_id = 'R202509110001'
and lower(t.supply_num) like lower('%' || trim('10000') || '%');
-- ✅ 项目招投标申请查询 供应商详情表
select t.supply_nme as supplynme, t.supply_num as supplynum, t.competive_bidding_id as competivebiddingid,
t.is_give_up as isgiveup, t.counts as counts, t.price as price, t.view_man_score as viewmanscore,
t.project_involves_score as projectinvolvesscore, t.score as score,
to_char(t.back_bidding_date, 'yyyy-mm-dd') as backbiddingdate, t.remark as remark,
t.evaluation_remark as evaluationremark, to_char(t.creation_date, 'yyyy-mm-dd') as createdate,
bp.contact_w3 as contactid, u.lname as contactname, pj.winbidding_supply_name as winbiddingsupplyname,
t.technicalweight as technicalweight, t.commercialweight as commercialweight,
t.commercial_score as commercialscore, t.supply_userid as contactuserid, t.record_num as recordnum
from omp_bid_supply_detail_t t, omp_coop_t bp, omp_bid_project_t pj, tpl_user_t u
where t.supply_num = bp.code and t.competive_bidding_id = pj.competive_bidding_id
and lower(bp.contact_w3) = lower(u.w3_account) and bp.status = 'ACTIVE' and t.competive_bidding_id = 'R202509110001'
order by t.supply_nme asc;
帮我把这个SQL中的表，提取为下面的这种格式 select * from 表名; – 表中文说明
最新发布
本文提供了Oracle数据库的连接配置示例，包括驱动、URL、用户名及密码等关键信息，适用于两个不同的Oracle实例。
扫一扫
1903
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
