Question on a Conditional Block

[jaymoore1756]

I have work order report that is SQL injected. It contains a conditional block that list costs for the work order report (payroll,inventory, accounts payable, equipment, ETC) In its current condition it works fine. Management now wants to subdivide the payroll expense based on union (union code >0) and non union (union code >0). Additionally they want to list detail cost for union and want to only summarize detail for non union. The variable ( as the block variable)  used currently is RESOURCE_TYPE which is the subsystem from which the cost originate. I have added the union code to the detail query, and I have added it to the conditional block  on the report for the payroll variable. I am struggling on how to do the payroll split inside the conditional block.

Any suggestions would greatly be appreciated.

This is the detail query --

SELECT
   DBO.LTRIM2(A.PREFIX,0) AS PREFIX,
   A.ORDER_NUMBER,
   B.ORDER_LINE_NO,
   A.ORDER_STATUS,
   C.RESOURCE_TYPE,
   PA.UNION_CODE,
   D.ENTRY_DT,
   C.TRANS_DATE,
   A.CLOSE_DT,
   COALESCE((SELECT L.DESCRIPTION
               FROM WP00100M L
              WHERE L.PREFIX = D.PREFIX AND
                    L.PLAN_NUMBER = D.PLAN_NUMBER AND
                    L.LINE_NUMBER = 0),' ') AS DESCRIPTION,
   DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(E.HOUSE_NO)),' '),LTRIM(RTRIM(E.STREET_NM))),' '),LTRIM(RTRIM(E.STREET_NM_SFX))),' '),LTRIM(RTRIM(E.CITY))),' '),LTRIM(RTRIM(E.PROVINCE_CD))),' '),LTRIM(RTRIM(E.POSTAL_CODE))) AS ADDRESS,
   A.FULL_DESC,
   COALESCE((SELECT F.FULL_DESC
               FROM WP00100M F
              WHERE F.PREFIX = D.PREFIX AND
                    F.PLAN_NUMBER = D.PLAN_NUMBER AND
                    F.LINE_NUMBER = 0),' ') AS WR_COMMENT,
   DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(SUBSTRING(C.DAC,1,3))),'-'),DBO.LTRIM2(SUBSTRING(C.DAC,4,21),0)) AS DAC,
   C.RESOURCE_ID,
   C.PREFIX AS WO420T_PREFIX,
   C.ORDER_NUMBER AS WO420T_ORDER_NO,
   C.ORDER_LINE_NO AS WO420T_LINE_NO,
   C.ORIGINAL_SS AS WO420T_ORIGINAL_SS,
   C.BATCH_NO AS WO420T_BATCH_NO,
   C.SHEET_NO AS WO420T_SHEET_NO,
   C.SEQ_NO AS WO420T_SEQ_NO,
   DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(C.JOB_AREA)),':'),DBO.LTRIM2(SUBSTRING(C.JOB_NO,1,12),0)),' '),DBO.LTRIM2(SUBSTRING(C.JOB_NO,13,18),0)),' '),DBO.LTRIM2(SUBSTRING(C.JOB_NO,19,24),0)) AS JOB_NO,
   C.TRX_STATUS,
   C.AMOUNT,
   C.QUANTITY,   
   
   C.RATE,
   PA.EMPL_NAME AS DESCRIPTION_PA,
   LA.DESC_24 AS DESCRIPTION_LA,
   LTRIM(RTRIM(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(EQ.YR)),' '),LTRIM(RTRIM(EQ.MAKE))),' '),LTRIM(RTRIM(EQ.MODEL))))) AS DESCRIPTION_EQ,
   COALESCE(LTRIM(RTRIM(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(IN1.DESCRIPTION)),' '),LTRIM(RTRIM(IN1.DESCRIPTION_EXT))))),' ') AS DESCRIPTION_IN,
   IN1.ITEM,
   COALESCE((SELECT N.SERIAL_NUM   
               FROM IN101T N     
              WHERE N.BATCH = C.BATCH_NO AND   
                    N.SHEET = DBO.TO_NUMBER(C.SHEET_NO) AND   
                    N.ITEM = C.RESOURCE_ID AND   
                    N.SUBSYSTEM = C.RESOURCE_TYPE AND
                    SUBSTRING(DBO.TO_CHAR1(N.TRANS_SEQUENCE),2,5) = SUBSTRING(DBO.TO_CHAR1(C.SEQ_NO),2,5) AND
                    LTRIM(RTRIM(N.CHARGE_CODE)) = DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(C.PREFIX)),LTRIM(RTRIM(C.ORDER_NUMBER))),'-'),LTRIM(RTRIM(C.ORDER_LINE_NO)))),' ') AS SERIAL_NUM,
   COALESCE((SELECT L.UNTS   
               FROM IN101T L     
              WHERE L.BATCH = C.BATCH_NO AND   
                    L.SHEET = DBO.TO_NUMBER(C.SHEET_NO) AND 
                    L.ITEM  = C.RESOURCE_ID AND   
                    L.SUBSYSTEM = C.RESOURCE_TYPE AND
                    SUBSTRING(DBO.TO_CHAR1(L.TRANS_SEQUENCE),2,5) = SUBSTRING(DBO.TO_CHAR1(C.SEQ_NO),2,5) AND 
                    LTRIM(RTRIM(L.CHARGE_CODE)) = DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(C.PREFIX)),LTRIM(RTRIM(C.ORDER_NUMBER))),'-'),LTRIM(RTRIM(C.ORDER_LINE_NO)))),' ') AS UNTS,
   DBO.CONCAT(LTRIM(RTRIM(IN4.IR_PREFIX)),LTRIM(RTRIM(IN4.IR_NUMBER))) AS ISSUE_NO,
   LTRIM(RTRIM(IN4.ISSUEE_RETURNER)) AS ISSUER_RETURNEE,
   COALESCE((SELECT A.INVOICE_NO
               FROM AP010M A
              WHERE A.BATCH_NO = LTRIM(RTRIM(C.BATCH_NO)) AND
                    A.SHEET_NO = LTRIM(RTRIM(C.SHEET_NO))),' ') AS INVOICE_NO10M,
   COALESCE((SELECT B.INVOICE_NO
               FROM AP003M B
              WHERE B.BATCH_NO = LTRIM(RTRIM(C.BATCH_NO)) AND
                    B.SHEET = LTRIM(RTRIM(C.SHEET_NO))),' ') AS INVOICE_NO3M,
   LTRIM(RTRIM(AP.SUPPLIER_NAME)) AS SUPPLIER_NAME,
   COALESCE((SELECT LTRIM(RTRIM(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(A.DESCRIPTION_1)),' '),LTRIM(RTRIM(A.DESCRIPTION_2))),' '),LTRIM(RTRIM(A.DESCRIPTION_3))),' '),LTRIM(RTRIM(A.DESCRIPTION_4))),' '),LTRIM(RTRIM(A.DESCRIPTION_5)))))
               FROM AP010M A
              WHERE A.BATCH_NO = LTRIM(RTRIM(C.BATCH_NO)) AND
                    A.SHEET_NO = LTRIM(RTRIM(C.SHEET_NO))),' ') AS DESC_10M,
   COALESCE((SELECT COALESCE(LTRIM(RTRIM(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(DBO.CONCAT(LTRIM(RTRIM(B.DESCRIPTION_1)),' '),LTRIM(RTRIM(B.DESCRIPTION_2))),' '),LTRIM(RTRIM(B.DESCRIPTION_3))),' '),LTRIM(RTRIM(B.DESCRIPTION_4))),' '),LTRIM(RTRIM(B.DESCRIPTION_5))))),' ')
               FROM AP003M B
              WHERE B.BATCH_NO = C.BATCH_NO AND
                    B.SHEET = LTRIM(RTRIM(C.SHEET_NO))),' ') AS DESC_03M,
   PO3.VENDOR,
   DBO.CONCAT(LTRIM(RTRIM(PO4.DESCRIPTION_1)),LTRIM(RTRIM(PO4.DESCRIPTION_2))) AS DESCRIPTION_PO,
   PO4.ORDER_UNITS,
   PO4.PRICE,
   PO4.QUANTITY AS QUANTITY_PO,
   PO4.EXTENDED_AMOUNT,
   DBO.CONCAT(LTRIM(RTRIM(GL101T.DESCRIPTION)),LTRIM(RTRIM(GL101T.DESC_OVERFLOW))) AS DESCRIPTION_GL,
   COALESCE(LTRIM(RTRIM(ST.DESC_LONG)),' ') AS DESCRIPTION_ST,
   ST.STOCK_IDENT,
   ST.MATERIAL_TYPE,
   ST.MEASURE_CODE
FROM 
   WO00100M A
      INNER JOIN
   WO00150M B
      ON A.PREFIX = B.PREFIX AND
         A.ORDER_NUMBER = B.ORDER_NUMBER
      INNER JOIN
   WO00420T C
      ON B.PREFIX = C.PREFIX AND
         B.ORDER_NUMBER = C.ORDER_NUMBER AND
         B.ORDER_LINE_NO = C.ORDER_LINE_NO
      INNER JOIN
   WP00100M D
      ON B.WP_PREFIX = D.PREFIX AND
         B.WP_PLAN_NO = D.PLAN_NUMBER AND
         B.WP_LINE_NO = D.LINE_NUMBER
      LEFT JOIN
   UM00100M E
      ON D.LOCATION = E.LOCATION_NO
     
      LEFT JOIN
   PA001T PA
      ON C.RESOURCE_ID = PA.EMPLOYEE_NO AND
         C.TRANS_DATE >= PA.EFD_START_DT AND
         C.TRANS_DATE <= PA.EFD_EXPIRY_DT
      LEFT JOIN
   JC524C LA
      ON C.RESOURCE_ID = LA.RESOURCE_CODE_24
      LEFT JOIN
   EQ001M EQ
      ON C.RESOURCE_ID = EQ.EQUIP_NO
      LEFT JOIN
   IN001M IN1
      ON C.RESOURCE_ID = IN1.ITEM
      LEFT JOIN
   IN004M IN4
      ON C.BATCH_NO = IN4.BATCH AND
         C.SHEET_NO = IN4.SHEET AND
         C.RESOURCE_TYPE = IN4.SUBSYSTEM
      LEFT JOIN
   AP001M AP
      ON C.RESOURCE_ID = AP.SUPPLIER_NO
      LEFT JOIN
   PO003M PO3
      ON C.BATCH_NO = PO3.BATCH AND
         C.SHEET_NO = PO3.SHEET
      LEFT JOIN
   PO004M PO4
      ON LTRIM(RTRIM(C.BATCH_NO)) = PO4.BATCH AND
         C.SHEET_NO = PO4.SHEET
      LEFT JOIN
   GL101T#prompt('pGLYear','token','09')# GL101T
      ON C.RESOURCE_TYPE = GL101T.SUBSYSTEM AND
         C.SS_BATCH_NO = GL101T.BATCH AND
         C.SS_SHEET_NO = GL101T.SHEET AND
         C.SS_SEQ_NO = GL101T.SEQUENCE
     LEFT JOIN   
   ST001M ST
      ON C.RESOURCE_ID = ST.STOCK_IDENT
WHERE 
   1=1
         
   #prompt('pResourceTypeClause','token',' ')#
ORDER BY
   JOB_NO,
   PREFIX,
   WO420T_ORDER_NO,
   WO420T_LINE_NO,
   CASE RESOURCE_TYPE WHEN 'PA' THEN 1  WHEN 'PAMG' THEN 2 WHEN 'LA' THEN 3 WHEN 'EQ' THEN 4 WHEN 'IN' THEN 5 WHEN 'ST' THEN 6 WHEN 'AP' THEN 6 WHEN 'PO' THEN 8 WHEN 'GL' THEN 9 END