--ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
DECLARE
v_sql VARCHAR2 (2000);
v_sql2 VARCHAR2 (2000);
v_sql3 VARCHAR2 (2000);
v_sql4 VARCHAR2 (2000);
v_sql5 VARCHAR2 (2000);
v_sql6 VARCHAR2 (2000);
v_sql7 VARCHAR2 (2000);
o_sql VARCHAR2 (1000);
cnt PLS_INTEGER;
v_1 VARCHAR2 (50);
dt_1 DATE;
v_2 VARCHAR2 (50);
v_3 VARCHAR2 (50);
V_JSON JSON_ARRAY_T;
MY_CURSOR SYS_REFCURSOR;
dml_cnt NUMBER;
v_x PLS_INTEGER;
RES VARCHAR2 (500);
BEGIN
v_sql :=
'SELECT /*+dynamic_sampling(A 8)*/ DISTINCT A.E_NAME
FROM R_EQUIPMENT_CHECK_DETAIL_T B,C_EQUIPMENT_DESC_T A,APN_USER C
WHERE B.EMP_NO=C.NAME(+) AND A.EQUIP_ID=B.EQUIP_ID AND B.CONFIRM_NO IS NULL AND B.CHECK_DATE BETWEEN TO_DATE(''2023-10-23 07:30'',''YYYY-MM-DD HH24:MI:SS'') AND
TO_DATE(''2023-10-23 23:59'',''YYYY-MM-DD HH24:MI:SS'') AND A.LINE_NAME = ''4線'' AND A.DEPT = ''HUB1G''';
v_sql2 :=
'SELECT /*+dynamic_sampling(A 8)*/ A.E_NAME, A.E_DESC, A.SECTION
FROM R_EQUIPMENT_CHECK_DETAIL_T B,C_EQUIPMENT_DESC_T A,APN_USER C
WHERE B.EMP_NO=C.NAME(+) AND A.EQUIP_ID=B.EQUIP_ID AND B.CONFIRM_NO IS NULL AND B.CHECK_DATE BETWEEN TO_DATE ( ''2023-04-02 07:30'',''YYYY-MM-DD HH24:MI:SS'' ) AND TO_DATE ( ''2023-08-30 23:59'',''YYYY-MM-DD HH24:MI:SS'' ) AND A.LINE_NAME = ''塗裝週邊'' AND A.DEPT = ''NME-KQ''
; ';
v_sql3 :=
'SELECT /*+dynamic_sampling(A 8)*/ DISTINCT A.E_NAME
FROM R_EQUIPMENT_CHECK_DETAIL_T B,C_EQUIPMENT_DESC_T A,APN_USER C
WHERE B.EMP_NO=C.NAME(+) AND A.EQUIP_ID=B.EQUIP_ID AND B.CONFIRM_NO IS NULL AND B.CHECK_DATE BETWEEN TO_DATE(''2023-10-26 07:30'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-10-26 23:59'',''YYYY-MM-DD HH24:MI:SS'') AND A.LINE_NAME IN(SELECT /*+no_unnest*/ line_name
FROM (SELECT DISTINCT LINE_NAME FROM C_EQUIPMENT_DESC_T WHERE DEPT= ''NME-L5''
MINUS
(SELECT SCAN_DATA FROM TPM.C_ROLE_MANAGE_T WHERE SCAN_DATA IN(SELECT DISTINCT LINE_NAME
FROM C_EQUIPMENT_DESC_T WHERE DEPT= ''NME-L5'')AND ROLE_TYPE=''CONFIRM''
MINUS
(SELECT SCAN_DATA FROM TPM.C_ROLE_MANAGE_T WHERE SCAN_DATA IN(SELECT DISTINCT LINE_NAME
FROM C_EQUIPMENT_DESC_T WHERE DEPT= ''NME-L5'')AND ROLE_TYPE=''CONFIRM''AND EMP_NO= ''F3662002'')))) AND A.DEPT = ''NME-L5''
ORDER BY CHECK_DATE DESC, E_DESC, SECTION, MODEL_NAME;';
--(1)
v_sql4 :=
'SELECT APPLY_TIME FROM (SELECT ROWNUM V_NO,A.* FROM (
SELECT * FROM C_REPAIR_T WHERE APPLY_TIME BETWEEN TO_DATE ( ''20230901080000'' , ''YYYYMMDDHH24MISS'' ) AND TO_DATE ( TO_CHAR ( ADD_MONTHS ( TO_DATE ( ''202309'' , ''YYYYMM'' ) , 1 ), ''YYYYMM'' )|| ''01075959'' , ''YYYYMMDDHH24MISS'') AND ERROR_MEMO = ''機故'' AND MACHINE_CODE IN(SELECT EQUIP_ID FROM C_EQUIPMENT_DESC_T
WHERE GROUP_MARK=''EIC'' AND APPLY_TIME BETWEEN TO_DATE ( ''20230901080000'' , ''YYYYMMDDHH24MISS'' ) AND TO_DATE ( TO_CHAR(SYSDATE,''YYYYMMDDHH24MISS'') , ''YYYYMMDDHH24MISS'' )
AND GROUP_MARK LIKE ''%SKDF%'' AND LINE_NAME IN(SELECT LINE_NAME FROM C_LINE_TYPE_T WHERE LINE_TYPE= ''TOTAL'' )) ORDER BY APPLY_TIME)A) WHERE V_NO=6;';
v_sql6 :=
'SELECT DISTINCT B.E_DESC, B.SECTION, B.LINE_NAME,B.GROUP_NAME,D.USERNAME,A.MODEL_NAME,A.FLAG CYCLE_CODE,A.CHECK_SECTION,
TO_CHAR(A.CHECK_DATE,''YY/MM/DD HH24:MI'') CHECK_DATE, C.CYCLE_DESC CYCLE_DESC
FROM R_EQUIPMENT_CHECK_DETAIL_T A, C_EQUIPMENT_DESC_T B, APN_USER D, C_CHECK_CYCLE_T C
WHERE A.FLAG=C.CYCLE_CODE AND A.EMP_NO=D.NAME AND A.EQUIP_ID=B.EQUIP_ID AND A.CHECK_DATE=TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND
A.CHECK_DATE>=TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE<=TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'')
AND A.CHECK_DATE>TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE<TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'')
AND A.CHECK_DATE between TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-11-08 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND
A.CHECK_DATE between TO_DATE(to_char(sysdate-10),''YYYY-MM-DD HH24:MI:SS'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-11-08 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND
A.CONFIRM_NO IS NULL AND A.FLAG=''C'' AND A.MODEL_NAME<''MODEL ALL'' AND A.EQUIP_ID=''ZDH-AGV09'' AND
A.CONFIRM_NO IS NULL AND A.FLAG>=''C'' AND A.MODEL_NAME<=''MODEL ALL'' AND A.EQUIP_ID>''ZDH-AGV09''
AND A.MODEL_NAME LIKE ''%TTTT%'' and a.flag= 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag>= 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag<= 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag> 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag< 10 AND A.MODEL_NAME<=''MODEL ALL''
';
v_sql5 :=
'UPDATE tpm.t1 A SET A.CONFIRM_NO=''xx'', A.CONFIRM_TIME=SYSDATE
WHERE A.EQUIP_ID IN(SELECT B.EQUIP_ID FROM C_EQUIPMENT_DESC_T B WHERE B.DEPT = ''TE3F'' )
AND TO_DATE(TO_CHAR(A.CHECK_DATE,''HH24:MI:SS''),''HH24:MI:SS'') BETWEEN TO_DATE(''00:30:00'',''HH24:MI:SS'') AND TO_DATE(''23:29:59'',''HH24:MI:SS'') AND
A.CHECK_DATE BETWEEN TO_DATE(''2023-10-13 07:30'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-11-14 07:29'',''YYYY-MM-DD HH24:MI:SS'') ';
-- v_sql7 :=
-- 'SELECT A.DEPT
--
--FROM C_EQUIPMENT_DESC_T A, CHECK_INFO_DETAIL B
--WHERE B.FLAG=''1'' AND B.UPDATE_TIME BETWEEN TO_DATE(''2023-11-15 07:30'',''YYYY-MM-DD HH24:MI'') AND TO_DATE(''2023-11-15 23:59'',''YYYY-MM-DD HH24:MI'') AND
--A.EQUIP_ID=B.EQUIP_ID AND A.STATUS=''稼動'' AND A.DEPT = ''CBE235''
--ORDER BY A.SECTION,A.LINE_NAME,A.E_DESC,A.EQUIP_ID,B.MODEL_NAME,B.FLAG,B.START_TIME';
v_sql7:='SELECT DISTINCT B.E_DESC
FROM R_EQUIPMENT_CHECK_DETAIL_T A, C_EQUIPMENT_DESC_T B, APN_USER D, C_CHECK_CYCLE_T C
WHERE A.FLAG=C.CYCLE_CODE AND A.EMP_NO=D.NAME AND A.EQUIP_ID=B.EQUIP_ID AND A.CHECK_DATE=TO_DATE(''2023-11-16 08:17:20'',''YYYY-MM-DD HH24:MI:SS'') AND
A.CONFIRM_NO IS NULL AND A.FLAG= 167 AND A.MODEL_NAME=''hello'' AND A.EQUIP_ID= 1099 ';
TPM.SQL2BIND_VARIABLE_V2_TEST (v_sql7, MY_CURSOR);
-- DBMS_OUTPUT.put_line('DML_CNT='||dml_cnt);
（1） TPM.SQL2BIND_VARIABLE(v_sql4,MY_CURSOR);
---- DBMS_OUTPUT.put_line ('----------------------------------------');
--LOOP
--FETCH MY_CURSOR INTO v_1;--,v_2,v_3;
-- EXIT WHEN MY_CURSOR%NOTFOUND;
-- DBMS_OUTPUT.put_line (v_1 );--||','||v_2||','||v_3);
--END LOOP;
--close MY_CURSOR;
（2）TPM.SQL2BIND_VARIABLE_FOR_DML(v_sql5,dml_cnt);
commit; //外面提交
END;
