select 'ou' OU
,A.MACHINE_CODE||'-'||A.MACHINE_NAME MACHINE_NAME
,AC.FAC_NAME
,A.MACHINE_PLACE
,A.DIV_CODE
,A.MACHINE_BRAND
,A.MACHINE_MODEL
,A.MACHINE_SERILS_NO
,A.MACHINE_VENDOR
,A.MACHINE_VENDOR_ADDR
,A.MACHINE_TECH1
,A.MACHINE_TECH2
,A.MACHINE_TECH3
,A.MACHINE_TECH4
,A.MACHINE_TECH5
,A.MACHINE_TECH6
,A.MACHINE_INS_DATE
,AA.ASSET_AMT
,AA.BOOK_VALUE
,S.PROBLEM
,S.CAUSE
,S.EMP_CODE
,S.SUB_ATV_CODE
,S.ATV_SDATE
,S.ATV_EDATE
,S.ATV_STIME
,S.ATV_ETIME
,S.ITEM_CODE
,S.QTY
,S.AMT
FROM MA_MACHINE A
LEFT OUTER JOIN FA_ASSET AA ON
A.ASSET_ID = AA. ASSET_ID
LEFT OUTER JOIN FA_PLACE AB ON
AA.PLACE_CODE = AB.PLACE_CODE
LEFT OUTER JOIN FA_FACTORY AC ON
AB.FAC_CODE = AC. FAC_CODE
,
(select '1' type_report
,A.MACHINE_CODE machine_code
,A.PROBLEM problem
,C.CAUSE cause
,C.EMP_CODE emp_code
,D.SUB_ATV_CODE SUB_ATV_CODE
,D.ATV_SDATE ATV_SDATE
,D.ATV_EDATE ATV_EDATE
,D.ATV_STIME ATV_STIME
,D.ATV_ETIME ATV_ETIME
,F.ITEM_CODE ITEM_CODE
,F.TRAN_QTY QTY
,F.TRAN_AMT AMT
FROM MA_REQUEST A
, MA_REPAIR C
, MA_REPAIR_ATV D
LEFT JOIN IN_TRAN_HEAD E ON
D.OU_CODE = E.OU_CODE AND D.SPO_NO = E.DOC_NO AND E.DOC_TYPE = 'SPO01'
LEFT JOIN IN_TRAN_DETAIL F ON
E.OU_CODE = F.OU_CODE AND E.DOC_NO = F.DOC_NO AND E.DOC_TYPE = F.DOC_TYPE
WHERE A.OU_CODE = :OU_CODE
AND A.OU_CODE = C.OU_CODE
AND A.REQ_NO = C.REQ_NO
AND A.REQ_NO_SEQ = C.REQ_NO_SEQ
AND C.OU_CODE = D.OU_CODE
AND C.REQ_NO = D.REQ_NO
AND C.REQ_NO_SEQ = D.REQ_NO_SEQ
AND C.REPAIR_SEQ = D.REPAIR_SEQ
union
select '2' type_report
,A.MACHINE_CODE machine_code
,'-' problem
,'-' cause
,A.PM_EMP_CODE emp_code
,D.SUB_ATV_CODE SUB_ATV_CODE
,A.PM_ACTUal_DATE ATV_SDATE
,D.ATV_EDATE ATV_EDATE
,0 ATV_STIME
,0 ATV_ETIME
,E.ITEM_CODE ITEM_CODE
,E.QTY QTY
,0 AMT
FROM MA_PM A
, MA_PLAN_ATV D
, MA_PLAN_SP E
WHERE A.plan_seq = D.plan_seq
AND A.atv_SEQ = D.atv_SEQ
AND A.plan_seq = E.plan_seq
AND A.atv_SEQ = E.atv_SEQ
) S
WHERE S.MACHINE_CODE = A.MACHINE_CODE
AND S.type_report like '%'
order by A.MACHINE_CODE,S.ATV_SDATE
ความคิดเห็นนี้ถูกลบโดยผู้ดูแลระบบของบล็อก
ตอบลบ