with(
  local!param_any: a!fromJson(
    ri!input
  ),
  local!mCategory: local!param_any.mainCategory,
  local!sMonths: local!param_any.selectedMonths,
  local!rIds:local!param_any.requestIds,
  local!pInfo:local!param_any.pagingInfo,
  local!datasubsetDeficiency: a!queryEntity(
    entity: cons!BWIS_WF_REP_AUDIT_LOG_ENTITY,
    query: a!query(
      selection: a!querySelection(
        columns: {
          a!queryColumn(
            field: "requestId"
          )
        }
      ),
      logicalExpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: "applicationName",
            operator: "=",
            value: cons!BWIS_UTIL_BUSINESS_UNIT
          ),
          a!queryFilter(
            field: "reasonCode",
            operator: "not null"
          )
        }
      ),
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: - 1
      )
    )
  ),
  local!datasubsetNew: a!queryEntity(
    entity: cons!BWIS_WF_REP_REQUEST_DETAILS_ENTITY,
    query: a!query(
      selection: a!querySelection(
        columns: {
          a!queryColumn(
            field: "id",
            visible: true
          ),
          a!queryColumn(
            field: "requestId",
            visible: true
          ),
          a!queryColumn(
            field: "mainCategory",
            visible: true
          ),
          a!queryColumn(
            field: "serviceRequestType",
            visible: true
          ),
          a!queryColumn(
            field: "workflowStatus",
            visible: true
          ),
          a!queryColumn(
            field: "accountNumber",
            visible: true
          ),
          a!queryColumn(
            field: "createdDate",
            visible: true
          ),
          a!queryColumn(
            field: "lastModifiedUser",
            visible: true
          ),
          a!queryColumn(
            field: "createdBy",
            visible: true
          ),
          a!queryColumn(
            field: "requestClosedDate",
            visible: true
          ),
          a!queryColumn(
            field: "signature",
            visible: true
          ),
          a!queryColumn(
            field: "boUser",
            visible: true
          )
        }
      ),
      logicalExpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: "requestId",
            operator: "in",
            value: {
              split(
                local!datasubsetDeficiency.data.requestId,
                ";"
              )
            }
          ),
          if(
            isnull(
              local!mCategory
            ),
            {},
            a!queryFilter(
              field: "mainCategory",
              operator: "=",
              value: local!mCategory
            )
          )
          ,
         a!queryFilter(
          field: "workflowStatus",
          operator: "not in",
          value:{cons!BWIS_SER_REQ_STATUS_COMPLETE_ACCEPTED,cons!BWIS_SER_REQ_STATUS_CANCELLED,cons!BWIS_SER_REQ_STATUS_COMPLETED,cons!BWIS_SER_REQ_STATUS_COMPLETE_DENY}
         ),
          if(
          isnull(
            local!sMonths
          ),
          a!queryFilter(
            field: "createdDate",
            operator: "between",
            value: {
              now() - 30,
              now()
            }
          ),
          if(
            local!sMonths = "2",
            a!queryFilter(
              field: "createdDate",
              operator: "between",
              value: {
                now() - 60,
                now()
              }
            ),
            a!queryFilter(
              field: "createdDate",
              operator: "between",
              value: {
                now() - 90,
                now()
              }
            )
          )
        )
        }
      ),
      pagingInfo:local!pInfo
    )
  ),
  local!modifiedDatasubset: updatecdt(
    cdt: {
      local!datasubsetNew.data
    },
    fieldsAndValues: {
      signature: apply(
        rule!BWIS_WF_REP_getManagerId,
        local!datasubsetNew.data.createdBy
      ),
      boUser: apply(
        rule!BWIS_WF_REP_getDeficiencyCountForRequestId,
        local!datasubsetNew.data.requestId
      )
    }
  ),
  local!finaldatasubset: todatasubset(
    local!modifiedDatasubset,
    a!pagingInfo(
      startIndex: 1,
      batchSize: - 1,
      sort: a!sortInfo(
        field: "createdDate",
        ascending: false
      )
    )
  ),
  'type!{urn:appian:plugin:datasubsetdownload:types}ExportableDataSubset'(
    datasubset: local!datasubsetNew,
    fieldNames: {
      "requestId",
      "mainCategory",
      "serviceRequestType",
      "workflowStatus",
      "accountNumber",
      "createdDate",
      "lastModifiedUser",
      "createdBy",
      "signature",
      "boUser"
    },
    fieldLabels: {
      "Request Id"&local!datasubsetNew,
      "Main Category"&local!finaldatasubset,
      "Service Request Type",      
      "Workflow Status",
	    "Account Number",
      "Creation Date",
      "Last Modified User",
      "Created By",
	    "Manager",
      "Deficiency Count"
    },
    filename: "Deficiency Details"
  )
)