IFNULL(PLA.CREATION_DATE,NOW())

添加链接描述

 <select id="queryERPOrderLine" parameterType="long" resultType="com.sinoprof.spm_purchase_order.poInterfaces.domain.PurchaseOrderLineEntity">
      select
          CASE
            WHEN PLA.ORDER_TYPE = '审计费' THEN 'A'
            WHEN PLA.ORDER_TYPE = '施工费1(控制70%接收)' THEN 'B1'
            WHEN PLA.ORDER_TYPE = '施工费2(不控制接收比例)' THEN 'B2'
            WHEN PLA.ORDER_TYPE = '设计费' THEN 'C'
            WHEN PLA.ORDER_TYPE = '监理费' THEN 'D'
            WHEN PLA.ORDER_TYPE = '安全生产费' THEN 'E'
            WHEN PLA.ORDER_TYPE = '其他' THEN 'F'
            WHEN PLA.ORDER_TYPE IS NULL THEN 'F' END AS expenseType,
          CASE
            WHEN POA.SD_PROJECT_FLAG = 'Y' THEN 'STRAIGHT'
            WHEN POA.SD_PROJECT_FLAG = 'N' THEN 'STORAGE' END AS receiveType,
          PPPA.SEGMENT1 as projectCode,
          PLA.PO_LINE_ID as spmPoLineID,
          PLA.LINE_NUM  as spmPoLineNum,
          POA.LINE_LOCATION_ID as spmPoLineLocationID ,
          POA.LINE_LOCATION_ID as spmPoDistributionID,
          PLA.PO_HEADER_ID as spmPoHeaderID,
          PHA.MIS_PO_ID as misPoHeaderID,
          PLA.MIS_PO_LINE_ID as misPoLineID,
          PLA.MIS_PO_LINE_NUM as misPoLineNum,
          POA.MIS_PO_LINE_LOCATION_ID as misPoLineLocationID,
          POA.MIS_PO_LINE_DISTRIBUTION_ID as misPoDistributionID,
          centralize_interfaces.`SCM_ID_CONV.GET_MIS_ORGANIZATION_ID`(POA.SHIP_TO_ORGANIZATION_ID,10027) as organizationID,
          MV.MIS_ITEM_ID as itemID,
		      MV.SEGMENT10 as itemCode,
          PLA.ITEM_DESCRIPTION as itemDesc,
          PLA.UNIT_MEAS_LOOKUP_CODE as uomDesc,
          PLA.UNIT_MEAS_LOOKUP_CODE as uomCode,
          PLA.UNIT_PRICE as unitPriceExTax,
          (PLA.UNIT_PRICE*((convert(substring(PLA.TAX_RATE,4),DECIMAL(18,2))+100)/100)) as unitPrice,
          convert(substring(PLA.TAX_RATE ,4),DECIMAL(18,2))as taxRate,
          PLA.QUANTITY as quantityOrdered,
          SMEV.EXPENDITURE_TYPE_ID as expenditureTypeID,
          case when POA.INVENTORY_ITEM_FLAG_DESC='库存' then 'INVENTORY'else 'COST'end as inventoryType,
          PLA.CHARGE_ACCOUNT as accountCombinationID,
          IFNULL(PLA.CREATION_DATE,NOW()) as creationDate,
          IFNULL(PLA.CREATION_DATE,NOW()) as lastUpdatedDate,
          CASE WHEN
            PHA.AUTHORIZATION_STATUS = 'CANCELLED' THEN 'CANCEL'
            WHEN
            PHA.AUTHORIZATION_STATUS = 'APPROVED' AND PLA.CANCEL_FLAG IS NOT NULL AND PLA.CANCEL_DATE IS NOT NULL THEN 'CANCEL'
            ELSE 'APPROVED' end as poLineStatus,
          FUV.UUID as applicantEmployeeID,
          FUV.LOGIN_NAME as applicantEmployeeUserID,
          FUV.EMPLOYEE_NUMBER as applicantEmployeeNumber,
          FUV.DISPLAY_NAME as applicantEmployeeName,
          FG.ID as applicantEmployeeOrgID,
          FG.NAME as applicantEmployeeOrgName,
          '0' as isNeedCheck,
           MICNV.PRODUCT_CATALOG_CODE_1 materialTypeCode1,
           MICNV.PRODUCT_CATALOG_NAME_1 materialTypeName1,
           MICNV.PRODUCT_CATALOG_CODE_2 materialTypeCode2,
           MICNV.PRODUCT_CATALOG_NAME_2 materialTypeName2,
           MICNV.PRODUCT_CATALOG_CODE_3 materialTypeCode3,
           MICNV.PRODUCT_CATALOG_NAME_3 materialTypeName3,
           MICNV.FLEX_VALUE4 productCategoryID,
           MICNV.PRODUCT_CATALOG_CODE_4 productCategoryCode,
           MICNV.PRODUCT_CATALOG_NAME_4 productCategoryDesc,
           BP.PROJECT_NO budgetProjectCode,
           BP.PROJECT_NAME budgetProjectName
        from spmproduct.PO_LINES_ALL PLA
          left join spmproduct.PO_LINE_LOCATIONS_ALL POA on POA.PO_LINE_ID=PLA.PO_LINE_ID
          left join spmbusinessdata.PA_PROJECTS_ALL PPPA ON PPPA.PROJECT_ID = POA.PROJECT_ID
          left join spmproduct.PO_HEADERS_ALL PHA on PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
          LEFT JOIN spmproduct.PO_REQUISITION_HEADERS_ALL PRHA ON PRHA.PO_NUMBER = PHA.SEGMENT1
          LEFT JOIN sinosystem.FND_GROUP FG ON FG.ID = PRHA.DEPARTMENT_ID
          left join spmproduct.MTL_SYSTEM_ITEMS_A_V MV on MV.INVENTORY_ITEM_ID=PLA.ITEM_ID and MV.TENANT_ID=PLA.TENANT_ID
          LEFT join spmproduct.FND_USER_V FUV on FUV.ID=PRHA.CREATED_BY
          left join spmproduct.SOA_MIS_EXPENTITURE_INFO_V SMEV on SMEV.EXPENDITURE_TYPE = POA.EXPENDITURE_TYPE and PHA.MIS_BODY=SMEV.MIS_TYPE
          left join spmbusinessdata.MTL_ITEM_CATEGORY_NEW_V MICNV ON PLA.CATEGORY_ID = MICNV.FLEX_VALUE4
          left join spmbusinessdata.BDGT_MAPINFO BM ON BM.PRI_KEY = POA.BDGT_MAP_ID
          left join spmbusinessdata.BDGT_PROJECTINFO BP ON BP.PROJECT_NO = BM.PROJECT_NUM
        where PLA.PO_HEADER_ID=#{poHeaderId}
    </select>