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