Cacher is the code snippet organizer for pro developers

SET echo OFF
SET feedback OFF
SET verify OFF
SET heading OFF
SET termout OFF
SET linesize 32767
SET trimspool ON
SET trimout ON
SET serveroutput ON

COL filename new_value filename;
SELECT './reports/'||TRANSLATE(C_NAME,' */''','___')
  ||'_'
  ||
  &1
  ||'_bill_report.csv' filename
FROM GMCCMG_CAMPAIGNS
WHERE N_CAMPAIGN_ID=
  &1;
spool &filename;
DECLARE
TYPE Varchar2Tab
IS
  TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE Varchar2List
IS
  TABLE OF VARCHAR2(30);
  billStatuses Varchar2List  :=Varchar2List('NOT_APPLICABLE', 'REQUESTED', 'CONFIRMED', 'REFUSED', 'ERROR');
  billTypes Varchar2List     :=Varchar2List('SMS', 'CUSTOM', 'NONE');
  campaignTypes Varchar2List :=Varchar2List('PULL', 'PUSH', 'QRCODE', 'UNKNOWN');
  offerTypes Varchar2List :=Varchar2List('COUPON', 'DEAL', 'APP');
  CURSOR billCur
  IS
    SELECT consumer.C_MSISDN,
      billing.N_CAMPAIGN_ID,
      campaign.C_CAMPAIGN_REF,
      campaign.C_NAME C_CAMP_NAME,
      campaign.N_TYPE N_CAMP_TYPE,
      issuer.C_TIMEZONE,
      billing.N_OFFER_ID,
      offer.C_OFFER_REF,
      offer.C_NAME C_OFFER_NAME,
      offer.N_OFFER_TYPE,
      billing.N_BILLING_TYPE,
      billing.N_APPLIED_PRICE,
      billing.N_STATUS,
      billing.N_ERROR_CODE,
      TO_CHAR(CAST((FROM_TZ(CAST(billing.D_BILLING_DATE AS TIMESTAMP),'+00:00') AT TIME ZONE issuer.c_timezone) AS DATE), 'YYYY-MM-DD HH24:MI:SS') D_BILLING_DATE,
      billing.C_REASON
    FROM GMCCRP_CONSUMER_BILLINGS billing
    INNER JOIN gmccop_consumers consumer
    ON consumer.N_CONSUMER_ID = billing.N_CONSUMER_ID
    INNER JOIN GMCCOM_CAMPAIGNS campaign
    ON campaign.N_CAMPAIGN_ID = billing.N_CAMPAIGN_ID
    INNER JOIN GMCCOM_OFFERS offer
    ON offer.N_OFFER_ID = billing.N_OFFER_ID
    INNER JOIN GMCCMG_OFFER_ISSUERS issuer
    ON issuer.N_OFFER_ISSUER_ID = offer.N_OFFER_ISSUER_ID
    WHERE billing.N_CAMPAIGN_ID =&1;
BEGIN
  DBMS_OUTPUT.ENABLE (buffer_size => NULL);
  --output headers
  dbms_output.put_line('"MSISDN","Campaign ID","Campaign Reference","Campaign Name","Campaign Type","Campaign TimeZone","Offer ID","Offer Reference","Offer Name","Offer Type","Billing Type","Applied Price","Billing Status","Billing Status Code","Billing Date","Billing Reason"');
  --loop to generate the report content
  FOR line IN billCur
  LOOP
    dbms_output.put_line('"'||line.C_MSISDN||'",'||line.N_CAMPAIGN_ID||',"'||line.C_CAMPAIGN_REF||'","'
      ||line.C_CAMP_NAME||'",'||campaignTypes(line.N_CAMP_TYPE+1)||',"'||line.C_TIMEZONE||'",'||line.N_OFFER_ID||',"'
      ||line.C_OFFER_REF||'","'||line.C_OFFER_NAME||'",'||offerTypes(line.N_OFFER_TYPE+1)||','||billTypes(line.N_BILLING_TYPE+1)||','
      ||line.N_APPLIED_PRICE||','||billStatuses(line.N_STATUS+1)||','||line.N_ERROR_CODE||','||line.D_BILLING_DATE||',"'||line.C_REASON||'"');
  END LOOP;
END;
/
spool OFF