1、代码① （与本内容相关的代码：7~36） 以下类似 $P{P_XXXX} ：均为页面端的传入参数
select so.sale_order_no as sale_order_no,
(SELECT company_name from CUSTOMER where company_code = so.CUSTOMER_NO) as CUSTOMER_NAME,
so.out_order_no as out_order_no,
so.required_date,
(select sysdate from dual) as system_date,
so.quality_note as quality_note,
(select '同意' from dual WHERE substr(2,1,1) < substr(wf_node_code,1,1) AND so.pro_type = '') as audit_opinion1, -- substr（2,1,1），从2开始，而不是从1开始；是因为审核到2节点了，但，还没审核通过。（以下7~36行代码 相同）
(select '同意' from dual WHERE SUBSTR(3,1,1) < SUBSTR(wf_node_code,1,1) AND so.pro_type = '') as audit_opinion2,
(select '同意' from dual WHERE SUBSTR(4,1,1) < SUBSTR(wf_node_code,1,1) AND so.pro_type = '') as audit_opinion3,
(select '同意' from dual WHERE SUBSTR(5,1,1) < SUBSTR(wf_node_code,1,1) AND so.pro_type = '') as audit_opinion4,
(select '同意' from dual WHERE SUBSTR(6,1,1) < SUBSTR(wf_node_code,1,1) AND so.pro_type = '') as audit_opinion5,
(select '同意' from dual WHERE SUBSTR(7,1,1) < SUBSTR(wf_node_code,1,1) AND so.pro_type = '') as audit_opinion6,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(2,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',22,'',2,'',23) AND w.role_code <> 'test2'))) as signature1,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(3,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',3) AND w.role_code <> 'test2'))) as signature2,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(4,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',41) AND w.role_code <> 'test2'))) as signature3,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(5,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',5) AND w.role_code <> 'test2'))) as signature4,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(6,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',6) AND w.role_code <> 'test2'))) as signature5,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(7,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',7) AND w.role_code <> 'test2'))) as signature6 from sale_order so
where so.sale_order_no = $P{P_SALE_ORDER_NO} -- $P{P_SALE_ORDER_NO} ：需要传入的参数（订单号）
2、代码② （与本内容相关的代码：7~46）
select so.sale_order_no as sale_order_no,
(SELECT company_name from CUSTOMER where company_code = so.CUSTOMER_NO) as CUSTOMER_NAME,
so.out_order_no as out_order_no,
so.required_date,
(select sysdate from dual) as system_date,
so.quality_note as quality_note,
(SELECT CASE WHEN audit_opinion = ' ' THEN '同意' ELSE audit_opinion END FROM
(SELECT ocl.audit_opinion FROM Order_Check_List ocl WHERE ocl.order_no = $P{P_SALE_ORDER_NO} AND ocl.wf_node_code = '' AND ocl.wf_code = '' AND
(SELECT '' FROM sale_order so WHERE so.sale_order_no = $P{P_SALE_ORDER_NO} AND so.wf_node_code IN ('','','','','','')) IS NOT NULL ORDER BY check_date desc)
WHERE ROWNUM = 1) as audit_opinion1,
(SELECT CASE WHEN audit_opinion = ' ' THEN '同意' ELSE audit_opinion END FROM
(SELECT ocl.audit_opinion FROM Order_Check_List ocl WHERE ocl.order_no = $P{P_SALE_ORDER_NO} AND ocl.wf_node_code = '' AND ocl.wf_code = '' AND
(SELECT '' FROM sale_order so WHERE so.sale_order_no = $P{P_SALE_ORDER_NO} AND so.wf_node_code IN ('','','','')) IS NOT NULL ORDER BY check_date desc)
WHERE ROWNUM = 1) as audit_opinion2,
(SELECT CASE WHEN audit_opinion = ' ' THEN '同意' ELSE audit_opinion END FROM
(SELECT ocl.audit_opinion FROM Order_Check_List ocl WHERE ocl.order_no = $P{P_SALE_ORDER_NO} AND ocl.wf_node_code = '' AND ocl.wf_code = '' AND
(SELECT '' FROM sale_order so WHERE so.sale_order_no = $P{P_SALE_ORDER_NO} AND so.wf_node_code IN ('','','')) IS NOT NULL ORDER BY check_date desc)
WHERE ROWNUM = 1) as audit_opinion3,
(SELECT CASE WHEN audit_opinion = ' ' THEN '同意' ELSE audit_opinion END FROM
(SELECT ocl.audit_opinion FROM Order_Check_List ocl WHERE ocl.order_no = $P{P_SALE_ORDER_NO} AND ocl.wf_node_code = '' AND ocl.wf_code = '' AND
(SELECT '' FROM sale_order so WHERE so.sale_order_no = $P{P_SALE_ORDER_NO} AND so.wf_node_code IN ('','')) IS NOT NULL ORDER BY check_date desc)
WHERE ROWNUM = 1) as audit_opinion4,
(SELECT CASE WHEN audit_opinion = ' ' THEN '同意' ELSE audit_opinion END FROM
(SELECT ocl.audit_opinion FROM Order_Check_List ocl WHERE ocl.order_no = $P{P_SALE_ORDER_NO} AND ocl.wf_node_code = '' AND ocl.wf_code = '' AND
(SELECT '' FROM sale_order so WHERE so.sale_order_no = $P{P_SALE_ORDER_NO} AND so.wf_node_code = '') IS NOT NULL ORDER BY check_date desc)
WHERE ROWNUM = 1) as audit_opinion5,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(2,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',22) AND w.role_code <> 'test2'))) as signature1,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(4,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',41) AND w.role_code <> 'test2'))) as signature2,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(5,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',5) AND w.role_code <> 'test2'))) as signature3,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(6,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',6) AND w.role_code <> 'test2'))) as signature4,
(SELECT p.person_name FROM personnel p WHERE p.person_code =
(SELECT distinct au.person_code FROM APPLICATION_USERS au WHERE au.username =
(SELECT distinct w.role_code FROM Work_Flow_Role w WHERE w.wf_code = ''
AND SUBSTR(7,1,1) < SUBSTR(so.wf_node_code,1,1) AND w.node_code = DECODE(pro_type,'',7) AND w.role_code <> 'test2'))) as signature5 from sale_order so
where so.sale_order_no = $P{P_SALE_ORDER_NO} -- $P{P_SALE_ORDER_NO} ：需要传入的参数（订单号）
代码②与代码①的区别：
1、代码①的结果如下图所示，只要是审核通过，审核意见处显示“同意”，签名处显示审核人的名字。
2、代码②的结果是：只要是审核通过，如果审核人填了审核意见，那么审核意见处显示“所填的内容”，否则显示“同意”，签名处显示审核人的名字。
3、代码① 图示
代码① 最终效果图
代码② 最终效果图
小结：只要是对应的部门审核通过了，那么“审核意见”和“签名/时间”这两栏 都同时显示；没有审核通过的部门，都是空白。
附录：（11~25行 为重要代码） 类似 $P{P_XXXX} ：均为页面端的传入参数
该代码的作用：按照where（15行）后面的条件，输入那些条件，就打印出 适合哪些条件的内容
时间格式：（大小写不区分，唯一不同，在时分秒的分处），24指：时间为24小时制。
1、MySQL：YYYY-MM-DD HH24:MM:SS 例：2017-12-18 15:10:30
2、Oracle：YYYY-MM-DD HH24:MI:SS
或 (我常用的写法)
3、MySQL：yyyy-MM-dd HH24:mm:ss
4、Oracle： yyyy-MM-dd HH24:mi:ss
SELECT
(select person_name from personnel where person_code = mlm.eq_responser) AS eq_responser,
(SELECT company_name from COMPANY WHERE company_code = mlm.insert_orgid) AS insert_orgid,
mld.finished_roll_qty AS finished_roll_qty,
mld.actual_time AS actual_time,
mld.start_time AS start_date,
mld.end_time AS end_date,
NVL(WORK_DATE,WORK_DATE) AS WORK_DATE,
NVL(manufacture_no,manufacture_no) AS manufacture_no,
(select material_name from material where material_code = mld.product_code) AS material_name,
(SELECT NVL(ARTS_DESC, ARTS_DESC) FROM EQUIPMENT_POWER_CONSUMPTION WHERE ARTS_NO = (SELECT ARTS_NO FROM MANUFACTURE_ROADMAP
WHERE MANUFACTURE_NO = MLD.MANUFACTURE_NO AND SERIAL = MLD.MAN_SERIAL_NO)) AS MAN_SERIAL_NAME FROM manufacture_log_master mlm,manufacture_log_detail mld
WHERE mlm.log_no = mld.log_no
AND mlm.eq_responser = NVL($P{P_EQ_RESPONSER},eq_responser)
AND TO_CHAR(WORK_DATE,'YYYY-MM-DD') >= DECODE($P{P_WORK_DATE1},null, to_char(NVL(WORK_DATE,SYSDATE),'YYYY-MM-DD'),$P{P_WORK_DATE1})
AND TO_CHAR(WORK_DATE,'YYYY-MM-DD') <= DECODE($P{P_WORK_DATE2},null, to_char(NVL(WORK_DATE,SYSDATE),'YYYY-MM-DD'),$P{P_WORK_DATE2})
AND mlm.insert_orgid = NVL($P{P_INSERT_ORGID},mlm.insert_orgid)
AND mld.manufacture_no = NVL($P{P_MANUFACTURE_NO},manufacture_no)
AND mld.product_code = NVL($P{P_PRODUCT_CODE},product_code)
AND (SELECT ARTS_NO FROM MANUFACTURE_ROADMAP WHERE MANUFACTURE_NO = MLD.MANUFACTURE_NO AND SERIAL = MLD.MAN_SERIAL_NO) = NVL($P{P_MAN_SERIAL_NO},(SELECT ARTS_NO
FROM MANUFACTURE_ROADMAP
WHERE MANUFACTURE_NO = MLD.MANUFACTURE_NO
AND SERIAL = MLD.MAN_SERIAL_NO))
原创作者：DSHORE 作者主页：http://www.cnblogs.com/dshore123/ 原文出自：http://www.cnblogs.com/dshore123/p/8057991.html 欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的 推荐，或评论，谢谢！） |
G
M
T
| 检测语言 世界语 中文简体 中文繁体 丹麦语 乌克兰语 乌兹别克语 乌尔都语 亚美尼亚语 伊博语 俄语 保加利亚语 僧伽罗语 克罗地亚语 冰岛语 加利西亚语 加泰罗尼亚语 匈牙利语 南非祖鲁语 卡纳达语 印地语 印尼巽他语 印尼爪哇语 印尼语 古吉拉特语 哈萨克语 土耳其语 塔吉克语 塞尔维亚语 塞索托语 威尔士语 孟加拉语 宿务语 尼泊尔语 巴斯克语 布尔语(南非荷兰语) 希伯来语 希腊语 德语 意大利语 意第绪语 拉丁语 拉脱维亚语 挪威语 捷克语 斯洛伐克语 斯洛文尼亚语 斯瓦希里语 旁遮普语 日语 格鲁吉亚语 毛利语 法语 波兰语 波斯尼亚语 波斯语 泰卢固语 泰米尔语 泰语 海地克里奥尔语 爱尔兰语 爱沙尼亚语 瑞典语 白俄罗斯语 立陶宛语 索马里语 约鲁巴语 缅甸语 罗马尼亚语 老挝语 芬兰语 苗语 英语 荷兰语 菲律宾语 葡萄牙语 蒙古语 西班牙语 豪萨语 越南语 阿塞拜疆语 阿尔巴尼亚语 阿拉伯语 韩语 马其顿语 马尔加什语 马拉地语 马拉雅拉姆语 马来语 马耳他语 高棉语 齐切瓦语 | 世界语 中文简体 中文繁体 丹麦语 乌克兰语 乌兹别克语 乌尔都语 亚美尼亚语 伊博语 俄语 保加利亚语 僧伽罗语 克罗地亚语 冰岛语 加利西亚语 加泰罗尼亚语 匈牙利语 南非祖鲁语 卡纳达语 印地语 印尼巽他语 印尼爪哇语 印尼语 古吉拉特语 哈萨克语 土耳其语 塔吉克语 塞尔维亚语 塞索托语 威尔士语 孟加拉语 宿务语 尼泊尔语 巴斯克语 布尔语(南非荷兰语) 希伯来语 希腊语 德语 意大利语 意第绪语 拉丁语 拉脱维亚语 挪威语 捷克语 斯洛伐克语 斯洛文尼亚语 斯瓦希里语 旁遮普语 日语 格鲁吉亚语 毛利语 法语 波兰语 波斯尼亚语 波斯语 泰卢固语 泰米尔语 泰语 海地克里奥尔语 爱尔兰语 爱沙尼亚语 瑞典语 白俄罗斯语 立陶宛语 索马里语 约鲁巴语 缅甸语 罗马尼亚语 老挝语 芬兰语 苗语 英语 荷兰语 菲律宾语 葡萄牙语 蒙古语 西班牙语 豪萨语 越南语 阿塞拜疆语 阿尔巴尼亚语 阿拉伯语 韩语 马其顿语 马尔加什语 马拉地语 马拉雅拉姆语 马来语 马耳他语 高棉语 齐切瓦语 |
文本转语音功能仅限200个字符