BI Report - Bursting - Not generating output file

Question

New to the bursting report world.

I'm trying to send to designated recipients a custom credit memo report I created using bursting. Template, filename and path is derived via bursting.

When I try to schedule the report (starts immediately) it gets completed successfully but no email is being received (even when my email is hardcoded on the bursting sql). In BI - Report job history - Process is completed successfully but no file is generated as well (no output available).

But when the report is ran adhoc/analytics, and same parameters selected, there is an output.

Note that the output format is PDF and bursting under report properties is enabled.

When I pass the parameters to bursting query manually to test, it works and gives parameter details.

Below is my query. note that the for the parameters 1 and 2, I just indicated 'my email address' to not display personal details.

`SELECT 
 DISTINCT HP.PARTY_NAME AS  KEY
,'Layout'   AS TEMPLATE
,'en-US' AS LOCALE
,'PDF' AS   OUTPUT_FORMAT
,'EMAIL' AS DEL_CHANNEL
,'Aggregated_Credit_Memo' OUTPUT_NAME
,'my email address' AS  PARAMETER1
,'my email address' AS PARAMETER2
,'[email protected]' AS PARAMETER3
,'Credit Memo Print' AS PARAMETER4
,'Please see the attached'  AS PARAMETER5
,'true' AS PARAMETER6
,'[email protected]'    AS PARAMETER7
FROM
HZ_PARTIES HP
,HZ_CONTACT_POINTS HCP
,HZ_CUST_ACCOUNTS CUST
,HZ_CUSTOMER_PROFILES_F HCPF
,RA_CUSTOMER_TRX_ALL RCTA
,HR_OPERATING_UNITS HOU
WHERE 1 = 1
AND CUST.ACCOUNT_NUMBER BETWEEN NVL(:p_Account_Num_From,CUST.ACCOUNT_NUMBER) AND NVL(:p_Account_Num_To,CUST.ACCOUNT_NUMBER)
AND HCP.OWNER_TABLE_ID = HP.PREFERRED_CONTACT_PERSON_ID
AND CUST.PARTY_ID = HP.PARTY_ID
AND HCPF.CUST_ACCOUNT_ID = CUST.CUST_ACCOUNT_ID
AND HCPF.STMT_DELIVERY_METHOD = 'EMAIL'
AND :P_PRINT_OPTION = 'EMAIL'
AND RCTA.ORG_ID = HOU.ORGANIZATION_ID
AND RCTA.BILL_TO_CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
AND HOU.ORGANIZATION_ID = :P_ORG_ID

UNION

`SELECT 
 DISTINCT HP.PARTY_NAME AS  KEY
,'Layout'   AS TEMPLATE
,'en-US' AS LOCALE
,'PDF' AS   OUTPUT_FORMAT
,'EMAIL' AS DEL_CHANNEL
,'Aggregated_Credit_Memo' OUTPUT_NAME
,' ' AS PARAMETER1
,' '    AS PARAMETER2
,'[email protected]' AS PARAMETER3
,'Credit Memo Print' AS PARAMETER4
,'Please see the attached'  AS PARAMETER5
,'true' AS PARAMETER6
,'[email protected]'    AS PARAMETER7
FROM
HZ_PARTIES HP
,HZ_CONTACT_POINTS HCP
,HZ_CUST_ACCOUNTS CUST
,HZ_CUSTOMER_PROFILES_F HCPF
,RA_CUSTOMER_TRX_ALL RCTA
,HR_OPERATING_UNITS HOU
WHERE 1 = 1
AND CUST.ACCOUNT_NUMBER BETWEEN NVL(:p_Account_Num_From,CUST.ACCOUNT_NUMBER) AND NVL(:p_Account_Num_To,CUST.ACCOUNT_NUMBER)
AND HCP.OWNER_TABLE_ID = HP.PREFERRED_CONTACT_PERSON_ID
AND CUST.PARTY_ID = HP.PARTY_ID
AND HCPF.CUST_ACCOUNT_ID = CUST.CUST_ACCOUNT_ID
AND HCPF.STMT_DELIVERY_METHOD = 'EMAIL'
AND :P_PRINT_OPTION = 'PRINT OUT'
AND RCTA.ORG_ID = HOU.ORGANIZATION_ID
AND RCTA.BILL_TO_CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
AND HOU.ORGANIZATION_ID = :P_ORG_ID

Email containing the PDF output file will be received.

Answer 1

It looks like the only difference between the two queries you have UNIONed together is the check on the parameter :P_PRINT_OPTION, which leads to PARAMETER1 and PARAMETER2 being either set to an email or to a single space. (I would argue that using a single space for this value is probably not the right answer. I would prefer a NULL.)

If this is indeed how your code works, there is a much simpler way to do it. Check this out:

SELECT DISTINCT       HP.PARTY_NAME             AS KEY,
                      'Layout'                  AS TEMPLATE,
                      'en-US'                   AS LOCALE,
                      'PDF'                     AS OUTPUT_FORMAT,
                      'EMAIL'                   AS DEL_CHANNEL,
                      'Aggregated_Credit_Memo'  AS OUTPUT_NAME,
                      DECODE(:P_PRINT_OPTION,
                        'EMAIL',                  'my email address',
                        'PRINT OUT',              ' '
                      )                         AS PARAMETER1,
                      DECODE(:P_PRINT_OPTION,
                        'EMAIL',                  'my email address',
                        'PRINT OUT',              ' '
                      )                         AS PARAMETER2,
                      '[email protected]'       AS PARAMETER3,
                      'Credit Memo Print'       AS PARAMETER4,
                      'Please see the attached' AS PARAMETER5,
                      'true'                    AS PARAMETER6,
                      '[email protected]'   AS PARAMETER7
FROM                  HZ_PARTIES                HP,
JOIN                  HZ_CONTACT_POINTS         HCP,
ON                    HCP.OWNER_TABLE_ID = HP.PREFERRED_CONTACT_PERSON_ID
JOIN                  HZ_CUST_ACCOUNTS          CUST,
ON                    CUST.PARTY_ID = HP.PARTY_ID
JOIN                  HZ_CUSTOMER_PROFILES_F    HCPF,
ON                    HCPF.CUST_ACCOUNT_ID = CUST.CUST_ACCOUNT_ID
JOIN                  RA_CUSTOMER_TRX_ALL       RCTA,
ON                    RCTA.BILL_TO_CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
JOIN                  HR_OPERATING_UNITS        HOU
ON                    HOU.ORGANIZATION_ID = RCTA.ORG_ID 
WHERE                 1 = 1
AND                   HOU.ORGANIZATION_ID = :P_ORG_ID
AND                   HCPF.STMT_DELIVERY_METHOD = 'EMAIL'
AND                   CUST.ACCOUNT_NUMBER
                        BETWEEN NVL(:p_Account_Num_From, CUST.ACCOUNT_NUMBER)
                        AND     NVL(:p_Account_Num_To, CUST.ACCOUNT_NUMBER)
AND                   :P_PRINT_OPTION = 'EMAIL'
/

Another thing is that, because most of your values are hardcoded, it's probably a better idea not to hardcode them in the SQL query, but instead do it in the program that's calling the query.

Yet another thing is that I suspect that not all of the tables you've joined in there are necessary, but I don't know that for certain.