how to color cells based on condition in apex
CSS -- INLINE
.email-red-box {
background-color: #ffcdd2;
color: #b71c1c;
font-weight: bold;
padding: 6px 10px;
border-radius: 4px;
text-align: center;
}
.email-green-box {
background-color: #c8e6c9;
color: #1b5e20;
font-weight: bold;
padding: 6px 10px;
border-radius: 4px;
text-align: center;
}
SELECT
hou.name,
cust_mail.party_name,
cust_mail.account_number,
cust_mail.invoice_number,
cust_mail.invoice_date,
cust_mail.amount,
cust_mail.creation_Date,
cust_mail.email_address,
CASE
WHEN cust_mail.EMAIL_STATUS = 'EMAIL DELIVERED'
THEN '<div class="email-green-box">EMAIL DELIVERED</div>'
ELSE '<div class="email-red-box">EMAIL NOT DELIVERED</div>'
END EMAIL_STATUS
FROM
(
SELECT
ract.org_id,
hp.party_name,
hca.account_number,
ract.trx_number invoice_number,
ract.trx_date invoice_date,
SUM(ragdt.amount) amount,
ract.creation_date,
--- to_Date(ract.creation_date , 'DD-MON-YYYY') creation_date,
hcp.email_address,
'EMAIL NOT DELIVERED' "EMAIL_STATUS"
FROM
apps.ra_customer_trx_all ract,
apps.ra_customer_trx_lines_all ratxl,
apps.ra_cust_trx_line_gl_dist_all ragdt,
apps.hz_parties hp,
apps.hz_cust_accounts hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu,
apps.hz_party_sites hps,
apps.hz_contact_points hcp
WHERE
1 = 1
AND hca.party_id = hp.party_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND ract.bill_to_site_use_id = hcsu.site_use_id
AND hcas.attribute18 = 'EMAIL'
AND ract.customer_trx_id = ratxl.customer_trx_id
AND ragdt.customer_trx_line_id = ratxl.customer_trx_line_id
--AND to_date (ract.trx_date,'DD-MON-YYYY') BETWEEN to_date(SYSDATE-45,'DD-MON-YYYY') AND to_Date(sysdate,'DD-MON-YYYY')
AND ract.trx_date BETWEEN SYSDATE-45 AND sysdate
-- and ract.trx_number='2814815'
AND hps.party_site_id = hcas.party_site_id
AND hps.party_site_id = hcp.owner_table_id
AND hcp.contact_point_purpose = 'EMAIL INVOICE'
AND hcas.org_id = hcsu.org_id
AND ract.org_id = hcsu.org_id
-- AND trx_number = '2814815'
AND ( NOT EXISTS (
SELECT
1
FROM
apps.xxar_inv_batch_process_hist
WHERE
trx_number = ract.trx_number
)
AND NOT EXISTS (
SELECT
1
FROM
apps.xxar_inv_batch_process_stg
WHERE
trx_number = ract.trx_number
) )
GROUP BY
ract.org_id,
hp.party_name,
hca.account_number,
ract.trx_number,
ract.trx_date,
ract.creation_date,
hcp.email_address
UNION
SELECT
ract.org_id,
hp.party_name,
hca.account_number,
ract.trx_number invoice_number,
ract.trx_date invoice_date,
SUM(ragdt.amount) amount,
ract.creation_date,
hcp.email_address,
'EMAIL DELIVERED' "EMAIL_STATUS"
FROM
apps.ra_customer_trx_all ract,
apps.ra_customer_trx_lines_all ratxl,
apps.ra_cust_trx_line_gl_dist_all ragdt,
apps.hz_parties hp,
apps.hz_cust_accounts hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu,
apps.hz_party_sites hps,
apps.hz_contact_points hcp
WHERE
1 = 1
AND hca.party_id = hp.party_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND ract.bill_to_site_use_id = hcsu.site_use_id
AND hcas.attribute18 = 'EMAIL'
AND ract.customer_trx_id = ratxl.customer_trx_id
AND ragdt.customer_trx_line_id = ratxl.customer_trx_line_id
--AND to_date (ract.trx_date,'DD-MON-YYYY') BETWEEN to_date(SYSDATE-45,'DD-MON-YYYY') AND to_Date(sysdate,'DD-MON-YYYY')
AND ract.trx_date BETWEEN SYSDATE-45 AND sysdate
-- and ract.trx_number='2814815'
AND hps.party_site_id = hcas.party_site_id
AND hps.party_site_id = hcp.owner_table_id
AND hcp.contact_point_purpose = 'EMAIL INVOICE'
AND hcas.org_id = hcsu.org_id
AND ract.org_id = hcsu.org_id
-- AND trx_number = '2814815'
AND ( EXISTS (
SELECT
1
FROM
apps.xxar_inv_batch_process_hist
WHERE
trx_number = ract.trx_number
)
OR EXISTS (
SELECT
1
FROM
apps.xxar_inv_batch_process_stg
WHERE
trx_number = ract.trx_number
) )
GROUP BY
ract.org_id,
hp.party_name,
hca.account_number,
ract.trx_number,
ract.trx_date,
ract.creation_date,
hcp.email_address
) cust_mail,
apps.hr_operating_units hou
WHERE
cust_mail.org_id = hou.organization_id AND cust_mail.AMOUNT>0
AND (
:P3_OU IS NULL
OR ',' || :P3_OU || ',' LIKE '%,' || hou.organization_id || ',%'
)
Comments
Post a Comment