<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file"
    xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:spring="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">
<spring:beans>
        <spring:bean id="CSFMDataSource" name="CSFMDataSource" class="oracle.jdbc.pool.OracleDataSource">
            <spring:property name="user" value="XXCCS_OSB_O"/>
            <spring:property name="password" value="OrG2_BtN"/>
            <spring:property name="dataSourceName" value="ds"/>
            <spring:property name="URL" value="jdbc:oracle:thin:@(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=1)(ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=scan-nprd-2023)(PORT=1541)))(CONNECT_DATA=(SERVICE_NAME=DV3CSF_SRVC_OTH.cisco.com)(SERVER=DEDICATED)))"/>
        </spring:bean>
    </spring:beans>
    <db:oracle-config name="Oracle_Configuration" dataSource-ref="CSFMDataSource" doc:name="Oracle Configuration"/>
    <http:request-config name="HTTP_Request_Configuration" host="tools-stage.cisco.com" port="80" doc:name="HTTP Request Configuration"/>

  <flow name="testFlow">
        <poll doc:name="Poll">
            <fixed-frequency-scheduler frequency="1" timeUnit="MINUTES"/>
            <logger message="Log-1" level="INFO" doc:name="Logger"/>
        </poll>
        <db:select config-ref="Oracle_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[SELECT sol.SW_UPG_CONTRACT_NUMBER,
       sol.service_level,
       sol.BILL_TO_SITE_USE_ID,
       sol.SERVICE_BEGIN_DATE,
       sol.SERVICE_END_DATE,
       0 Net_Price,
       sol.PRODUCT_ORDER_NUMBER,
       sol.SERIAL_NUMBER,
       sol.SW_UPG_FULFILLMENT_PID,
       sol.INSTALL_SITE_USE_ID,
       sii.INSTANCE_ID,
       DECODE (sii.INSTANCE_ID, 'Yes', 'No') softline,
       '1232' cs_cse_number,
       lg.error_message
  FROM apps.XXCSS_SFM_ORDER_LINES sol,
       apps.XXCSS_SFM_ERROR_LOG lg,
       apps.XXCSS_SFM_INSTANCE_INTERFACE sii
        WHERE     1 = 1
       AND sol.order_type = 'INNVO_LINE'
       AND sol.entitlement_status IN ('CSFM_ERROR', 'QA_FAILED')
       --AND lg.so_header_id = sol.header_id
       --AND lg.so_line_id = sol.line_id
       AND lg.ERROR_TYPE = 'ERROR'
       AND sol.header_id = sii.so_header_id(+)
       AND sol.line_id = sii.so_line_id(+)
       AND lg.sfdc_case_req = 'Y'
       AND lg.sfdc_case_no IS NULL]]></db:parameterized-query>
        </db:select>
    <dw:transform-message doc:name="Transform Message" metadata:id="38e67a04-7f03-4261-a450-72b69ae0fff1">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/csv
---
payload map ((payload01 , indexOfPayload01) -> {
    BILL_TO_SITE_USE_ID: payload01.BILL_TO_SITE_USE_ID as :number,
    CS_CSE_NUMBER: payload01.CS_CSE_NUMBER,
    ERROR_MESSAGE: payload01.ERROR_MESSAGE,
    INSTALL_SITE_USE_ID: payload01.INSTALL_SITE_USE_ID as :number,
    INSTANCE_ID: payload01.INSTANCE_ID,
    NET_PRICE: payload01.NET_PRICE as :number,
    PRODUCT_ORDER_NUMBER: payload01.PRODUCT_ORDER_NUMBER,
    SERIAL_NUMBER: payload01.SERIAL_NUMBER,
    SERVICE_BEGIN_DATE: payload01.SERVICE_BEGIN_DATE,
    SERVICE_END_DATE: payload01.SERVICE_END_DATE,
    SERVICE_LEVEL: payload01.SERVICE_LEVEL,
    SOFTLINE: payload01.SOFTLINE,
    SW_UPG_CONTRACT_NUMBER: payload01.SW_UPG_CONTRACT_NUMBER,
    SW_UPG_FULFILLMENT_PID: payload01.SW_UPG_FULFILLMENT_PID
})]]></dw:set-payload>
        </dw:transform-message>
        <byte-array-to-object-transformer doc:name="Byte Array to Object"/>
        <set-attachment attachmentName="filedata" value="#[payload]" contentType="application/csv" doc:name="Attachment"/>
        <set-attachment attachmentName="casedata" value="{ "Origin":"Web", "CaseNumber":"33315931584", "SearchCCOID":"vimerugu"}" contentType="text/plain" doc:name="Attachment"/>
        <set-payload value="#[null]" doc:name="Set Payload"/>

        <http:request config-ref="HTTP_Request_Configuration" path="/cvcm/was70/pwc036/caseApi/updateCaseAttach/33315931584" method="POST" doc:name="HTTP" parseResponse="false">
            <http:request-builder>
                <http:header headerName="Authorization" value="Basic Q1BFQ2FzZUFQSS5nZW46Y2FzZTFnZW4="/>

            </http:request-builder>
        </http:request>
        <logger message="#[flowVars.varCaseUpdate]" level="INFO" doc:name="Logger"/>

       <catch-exception-strategy doc:name="Catch Exception Strategy">
            <db:insert config-ref="Oracle_Configuration" doc:name="Log_DB">
                <db:parameterized-query><![CDATA[insert into XXCSS_SFM_OSB_EXECUTION_LOG (PROCESS_STATUS) values ('SFDCCaseCreationService-Exception')]]></db:parameterized-query>
            </db:insert>
            <db:insert config-ref="Oracle_Configuration" doc:name="SFDCCaseCreation- Exception">
                <db:parameterized-query><![CDATA[insert into XXCSS_SFM_CESB_LOG_ERROR ( LOG_ERROR_ID,SERVICE_NAME,SERVICE_TYPE,SERVICE_ACTIVITY,SERVICE_PAYLOAD,LOG_ERROR_STATUS,
LOG_ERROR_MESSAGE,ERROR_CODE,ERROR_MESSAGE,ERROR_DETAILS,LOG_ERROR_SEVERITY,SUGGESTED_ACTION,NOTIFY_MAIL,NOTIFY_COUNT,ATTRIBUTE_1,
ATTRIBUTE_2,ATTRIBUTE_3,ATTRIBUTE_4,ATTRIBUTE_5,ATTRIBUTE_6,ATTRIBUTE_7,ATTRIBUTE_8,ATTRIBUTE_9,ATTRIBUTE_10,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,BATCH_ID)
values ('1000','SFDCCaseCreationService','SFDCCaseCreationServiceType','SFDCCaseCreationServiceActivity','SFDCCaseCreationServicePayload','ERROR','','Error_Code','Error_Message',
#[exception.getSummaryMessage()],'Critical','','NA',3,'','','','','','','','','','','SFDCCaseCreationService',sysdate,'SFDCCaseCreationService',sysdate,'1000')]]></db:parameterized-query>
            </db:insert>
        </catch-exception-strategy>
    </flow>
</mule>