• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6288
  • Last Modified:

leading zeros getting truncated

These are the 2 functions that I use in the rdf file , to run a sql and then include some placeholder columns, whose values are set using pl/sql in the program units section in the rdf file, and when I run the rdf file , giving the parameter , it creates a csv file for mw. Now , my problem is that : I have a field called '"'|| 'Payor1 Group Number'        ||'",'|| , which is the group number and it is stored as a varchar2 in the oracle database, but when I see the output in the csv file , fields like   -----‘ 00098767’   , appears as  98767. In other words , the leading zeros are cut off for some reason. I have declared it as a character in the property palette. But still I get this problem. How can I get this to print as it is in the database like --- -----‘ 00098767’   ????? Please help



FUNCTION1:

function CF_exp_detailsFormula return Number is
  v_pos             INTEGER;
  fp                Text_Io.File_Type;

  v_report          VARCHAR2(300);
  txt_line          VARCHAR2(4000);

-- ====================================================================

BEGIN  

   IF (SUBSTR(:P_PATH,LENGTH(:P_PATH),1) <> '\') THEN
       :P_PATH := :P_PATH || '\';
   END IF;

   SELECT :P_PATH || 'unity_svcbr'||:P_SVCBR_ID ||'_'||TO_CHAR(SYSDATE,'MMDDYYYY')||'.csv'
     INTO v_report
     FROM dual;  
     
   IF (:CS_record_cnt = 1) THEN
       fp := Text_IO.Fopen(v_report, 'w');  

       txt_line :=  '"'|| 'Patient ID'               ||'",'||
                    '"'|| 'Last Name'                ||'",'||
                    '"'|| 'Fist Name'                ||'",'||
                    '"'|| 'State'                    ||'",'||
                    '"'|| 'Primary Therapy'          ||'",'||
                    '"'|| 'Pharmacy Status'          ||'",'||
                    '"'|| 'Sales/Mkg Status'         ||'",'||
                    '"'|| 'Team'                     ||'",'||
                    '"'|| 'Invoicer Code'            ||'",'||
                    '"'|| 'Invoicer Name'            ||'",'||
                    '"'|| 'Collector Code'           ||'",'||
                    '"'|| 'Collector Name'           ||'",'||
                    '"'|| 'Regimen'                  ||'",'||
--                    '"'|| 'Severity'                 ||'",'||
                    '"'|| 'Claim Type'               ||'",'||
                    '"'|| 'Rx Number'                ||'",'||
                    '"'|| 'Max Refills'              ||'",'||
                    '"'|| 'Rx Therapy Type'          ||'",'||
                    '"'|| 'Report Branch'            ||'",'||
                    '"'|| 'Clinical Branch'          ||'",'||
                    '"'|| 'Disp. Branch'             ||'",'||
                    '"'|| 'MD Name'                  ||'",'||
--                    '"'|| 'Refill Rule'              ||'",'||
                    '"'|| 'Drug Abbrev'              ||'",'||
--                    '"'|| 'Drug Dosage'              ||'",'||
--                    '"'|| 'Drug Unit'                ||'",'||
--                    '"'|| 'Frequency'                ||'",'||
--                    '"'|| 'No. Doses'                ||'",'||
--                    '"'|| 'Sale Unit'                ||'",'||
--                    '"'|| 'Rx Start Date'            ||'",'||
--                    '"'|| 'Rx Stop Date'             ||'",'||
                    '"'|| 'HID Date'                 ||'",'||
                    '"'|| 'Last Delivery Date'       ||'",'||
                    '"'|| 'Payor1 ID'                ||'",'||
                    '"'|| 'Payor1 Name'              ||'",'||
                    '"'|| 'Payor1 Pricing ID'        ||'",'||
                    '"'|| 'Payor1 Eff Date'          ||'",'||
                    '"'|| 'Payor1 Exp Date'          ||'",'||
                    '"'|| 'Payor1 MBC'               ||'",'||
                    '"'|| 'Payor1 Prior Auth'        ||'",'||
                    '"'|| 'Payor1 Policy Number'        ||'",'||
                    '"'|| 'Payor1 Group Number'        ||'",'||
                    '"'|| 'Payor1 Person Code'        ||'",'||
                    '"'|| 'Payor1 Auth Code1'        ||'",'||
                    '"'|| 'Payor1 Auth Eff Date1'    ||'",'||
                    '"'|| 'Payor1 Auth Exp Date1'    ||'",'||
                    '"'|| 'Payor1 Auth Code2'        ||'",'||
                    '"'|| 'Payor1 Auth Eff Date2'    ||'",'||
                    '"'|| 'Payor1 Auth Exp Date2'    ||'",'||;
       Text_IO.Put_Line(fp,txt_line);
   ELSE
       fp := Text_IO.Fopen(v_report, 'a');
   END IF;

   
   txt_line := '"'|| :patient_id                  ||'",'||
               '"'|| :last_name                   ||'",'||
               '"'|| :first_name                  ||'",'||
               '"'|| :state                       ||'",'||
               '"'|| :primary_therapy_type        ||'",'||
               '"'|| :pharmacy_status             ||'",'||
               '"'|| :sales_marketing_status      ||'",'||
               '"'|| :team                        ||'",'||
               '"'|| :invoicer_code            ||'",'||
               '"'||  :invoicer_name            ||'",'||
               '"'|| :collector_code           ||'",'||
               '"'|| :collector_name           ||'",'||
               '"'|| :CP_regimen                  ||'",'||
--               '"'|| :CP_severity                 ||'",'||
               '"'|| :CP_claim_type               ||'",'||
               '"'|| :CP_rx_number                ||'",'||
               '"'|| :CP_max_refills              ||'",'||
               '"'|| :CP_rx_therapy_type          ||'",'||
               '"'|| :P_SVCBR_ID                  ||'",'||
               '"'|| :clinical_branch             ||'",'||
               '"'|| :CP_dispensing_branch        ||'",'||
               '"'|| :CP_md_name                  ||'",'||
--               '"'|| :CP_refill_rule              ||'",'||
               '"'|| :CP_drug_abbrev              ||'",'||
--               '"'|| :CP_drug_dosage              ||'",'||
--               '"'|| :CP_drug_unit                ||'",'||
--               '"'|| :CP_frequency                ||'",'||
--               '"'|| :CP_no_doses                 ||'",'||
--               '"'|| :CP_sale_unit                ||'",'||
--               '"'|| TO_CHAR(:CP_rx_start_date,'MM/DD/YYYY')      ||'",'||
--               '"'|| TO_CHAR(:CP_rx_stop_date,'MM/DD/YYYY')       ||'",'||
               '"'|| TO_CHAR(:CP_next_delivery_date,'MM/DD/YYYY') ||'",'||
               '"'|| TO_CHAR(:CP_last_delivery_date,'MM/DD/YYYY') ||'",'||
               '"'|| :CP_payor1_id                ||'",'||
               '"'|| :CP_payor1_name              ||'",'||
               '"'|| :CP_payor1_pricing_id        ||'",'||
               '"'|| TO_CHAR(:CP_payor1_effective_date,'MM/DD/YYYY') ||'",'||
               '"'|| TO_CHAR(:CP_payor1_expiration_date,'MM/DD/YYYY')||'",'||
               '"'|| :CP_payor1_ccgroup           ||'",'||
               '"'|| :CP_payor1_prior_auth        ||'",'||
               '"'|| :CP_payor1_policy_no        ||'",'||
               '"'|| :CP_payor1_group_no        ||'",'||
               '"'|| :CP_payor1_person_code        ||'",'||
               '"'|| :CP_payor1_auth_code1        ||'",'||
               '"'|| TO_CHAR(:CP_payor1_auth_eff_date1,'MM/DD/YYYY') ||'",'||
               '"'|| TO_CHAR(:CP_payor1_auth_exp_date1,'MM/DD/YYYY') ||'",'||
               '"'|| :CP_payor1_auth_code2        ||'",'||
               '"'|| TO_CHAR(:CP_payor1_auth_eff_date2,'MM/DD/YYYY') ||'",'||
               '"'|| TO_CHAR(:CP_payor1_auth_exp_date2,'MM/DD/YYYY') ||'",'|| ;
   Text_IO.Put_Line(fp,txt_line);  
   Text_IO.Fclose(fp);

   RETURN(NULL);
EXCEPTION
   WHEN Others THEN
      srw.message(21,'Patient: ' || :patient_id || ' Rx: ' || :CP_rx_number ||'  ' || SQLERRM);
END;              





FUNCTION 2:

function CF_set_payor_valuesFormula return Number is

    v_cc_no               claim_centers.no%TYPE;
    v_name                claim_centers.name%TYPE;

    v_cc_seq              patient_insurance.claim_center_seq%TYPE;
    v_pricing_id          patient_insurance.pricing_id%TYPE;
    v_pi_eff_date         patient_insurance.effective_date%TYPE;
    v_pi_exp_date         patient_insurance.expiration_date%TYPE;
    v_pi_ccgroup          patient_insurance.ccgroup%TYPE;
    v_pi_prior_auth_type  patient_insurance.prior_auth_type%TYPE;
    v_pi_policy_no         patient_insurance.policy_no%TYPE;
    v_pi_group_no         patient_insurance.group_no%TYPE;
    v_pi_person_code         patient_insurance.person_code%TYPE;


    v_auth_code           auth_detail.auth_code%TYPE;
    v_auth_eff_date       auth_detail.effective_date%TYPE;
    v_auth_exp_date       auth_detail.expiration_date%TYPE;

    v_auth_cnt            NUMBER;
    v_billing_terms       claim_Centers.billing_terms%TYPE;

    CURSOR c_claim_center IS
       SELECT cc.no,
              cc.name,
              pi.claim_center_seq,
              pi.pricing_id,
              pi.effective_date,
              pi.expiration_date,
              pi.ccgroup,
              pi.prior_auth_type,
              pi.policy_no,
              pi.group_no,
              pi.person_code,
              cc.billing_terms
         FROM claim_centers cc,
              patient_insurance pi
        WHERE pi.patient_id = :patient_id
          AND cc.no = pi.claim_center_no
          AND pi.claim_center_seq <= 3;

    CURSOR c_auth IS
       SELECT ad.auth_code,
              ad.effective_date,
              ad.expiration_date
         FROM auth_detail ad
        WHERE ad.patient_id = :patient_id
          AND ad.cc_no = v_cc_no
          AND ad.therapy_type = :CP_rx_therapy_type
          AND NVL(ad.effective_date, :CP_prescr_ship_date) <= :CP_prescr_ship_date
          AND NVL(ad.expiration_date,:CP_prescr_ship_date) >= :CP_prescr_ship_date
    ORDER BY ad.effective_date DESC, ad.expiration_date DESC, ad.auth_seq DESC;

-- =====================================================================================

BEGIN
   -- ==================================
   -- Initialize place holders
   -- ==================================
   :CP_payor1_id              := NULL;
   :CP_payor1_name            := NULL;
   :CP_payor1_pricing_id      := NULL;
   :CP_payor1_effective_date  := NULL;
   :CP_payor1_expiration_date := NULL;
   :CP_payor1_ccgroup         := NULL;
   :CP_payor1_prior_auth      := NULL;
   :CP_payor1_policy_no       := NULL;
   :CP_payor1_group_no        := NULL;
   :CP_payor1_person_code     := NULL;  

   :CP_billing_terms1         := NULL;
   :CP_payor1_auth_code1      := NULL;
   :CP_payor1_auth_eff_date1  := NULL;
   :CP_payor1_auth_exp_date1  := NULL;
   :CP_payor1_auth_code2      := NULL;
   :CP_payor1_auth_eff_date2  := NULL;
   :CP_payor1_auth_exp_date2  := NULL;

   -- =============================
   -- Return the first five payors
   -- =============================
   OPEN c_claim_center;
   LOOP
      FETCH c_claim_center
       INTO v_cc_no,
            v_name,
            v_cc_seq,
            v_pricing_id,
            v_pi_eff_date,
            v_pi_exp_date,
            v_pi_ccgroup,
            v_pi_prior_auth_type,
            v_pi_policy_no,
            v_pi_group_no,
            v_pi_person_code,
            v_billing_terms;

      EXIT WHEN c_claim_center%NOTFOUND;

      IF (v_cc_seq = 1) THEN
          :CP_payor1_id              := v_cc_no;
          :CP_payor1_name            := v_name;
          :CP_payor1_pricing_id      := v_pricing_id;
          :CP_payor1_effective_date  := v_pi_eff_date;
          :CP_payor1_expiration_date := v_pi_exp_date;
          :CP_payor1_ccgroup         := v_pi_ccgroup;
          :CP_payor1_prior_auth      := v_pi_prior_auth_type;
          :CP_payor1_policy_no       := v_pi_policy_no;
          :CP_payor1_group_no       := v_pi_group_no;
          :CP_payor1_person_code    := v_pi_person_code;
          :CP_billing_terms1         := v_billing_terms;
      ELSIF (v_cc_seq = 2) THEN
          :CP_payor2_id              := v_cc_no;
          :CP_payor2_name            := v_name;
          :CP_payor2_pricing_id      := v_pricing_id;
          :CP_payor2_effective_date  := v_pi_eff_date;
          :CP_payor2_expiration_date := v_pi_exp_date;
          :CP_payor2_ccgroup         := v_pi_ccgroup;
          :CP_payor2_prior_auth      := v_pi_prior_auth_type;
          :CP_payor2_policy_no       := v_pi_policy_no;
          :CP_payor2_group_no       := v_pi_group_no;
          :CP_payor2_person_code    := v_pi_person_code;
          :cp_billing_terms2         := v_billing_terms;
      ELSIF (v_cc_seq = 3) THEN
          :CP_payor3_id              := v_cc_no;
          :CP_payor3_name            := v_name;
          :CP_payor3_pricing_id      := v_pricing_id;
          :CP_payor3_effective_date  := v_pi_eff_date;
          :CP_payor3_expiration_date := v_pi_exp_date;
          :CP_payor3_ccgroup         := v_pi_ccgroup;
          :CP_payor3_prior_auth      := v_pi_prior_auth_type;
          :CP_payor3_policy_no       := v_pi_policy_no;
          :CP_payor3_group_no       := v_pi_group_no;
          :CP_payor3_person_code    := v_pi_person_code;
          :cp_billing_terms3         := v_billing_terms;
      ELSIF (v_cc_seq = 4) THEN
          :CP_payor4_id              := v_cc_no;
          :CP_payor4_name            := v_name;
          :CP_payor4_pricing_id      := v_pricing_id;
          :CP_payor4_effective_date  := v_pi_eff_date;
          :CP_payor4_expiration_date := v_pi_exp_date;
          :CP_payor4_prior_auth      := v_pi_prior_auth_type;
      ELSIF (v_cc_seq = 5) THEN
          :CP_payor5_id              := v_cc_no;
          :CP_payor5_name            := v_name;
          :CP_payor5_pricing_id      := v_pricing_id;
          :CP_payor5_effective_date  := v_pi_eff_date;
          :CP_payor5_expiration_date := v_pi_exp_date;
          :CP_payor5_prior_auth      := v_pi_prior_auth_type;
      END IF;

      -- =================================
      -- Auth (Two most recent)
      -- =================================
      v_auth_cnt := 0;

      OPEN c_auth;
      LOOP
         FETCH c_auth
          INTO v_auth_code,
               v_auth_eff_date,
               v_auth_exp_date;

         EXIT WHEN c_auth%NOTFOUND;
   
         v_auth_cnt := v_auth_cnt + 1;
 
         IF (v_auth_cnt = 1) THEN
             IF (v_cc_seq = 1) THEN
                 :CP_payor1_auth_code1     := v_auth_code;
                 :CP_payor1_auth_eff_date1 := v_auth_eff_date;
                 :CP_payor1_auth_exp_date1 := v_auth_exp_date;
           --  ELSIF (v_cc_seq = 2) THEN
           --      :CP_payor2_auth_code1     := v_auth_code;
            --     :CP_payor2_auth_eff_date1 := v_auth_eff_date;
            --     :CP_payor2_auth_exp_date1 := v_auth_exp_date;
         --    ELSIF (v_cc_seq = 3) THEN
          --       :CP_payor3_auth_code1     := v_auth_code;
          --       :CP_payor3_auth_eff_date1 := v_auth_eff_date;
              --   :CP_payor3_auth_exp_date1 := v_auth_exp_date;
             END IF;
         ELSIF (v_auth_cnt = 2) THEN
             IF (v_cc_seq = 1) THEN
                 :CP_payor1_auth_code1     := v_auth_code;
                 :CP_payor1_auth_eff_date1 := v_auth_eff_date;
                 :CP_payor1_auth_exp_date1 := v_auth_exp_date;
             ELSIF (v_cc_seq = 2) THEN
                 :CP_payor2_auth_code1     := v_auth_code;
                 :CP_payor2_auth_eff_date1 := v_auth_eff_date;
                 :CP_payor2_auth_exp_date1 := v_auth_exp_date;
             ELSIF (v_cc_seq = 3) THEN
                 :CP_payor3_auth_code1     := v_auth_code;
                 :CP_payor3_auth_eff_date1 := v_auth_eff_date;
                 :CP_payor3_auth_exp_date1 := v_auth_exp_date;
             ELSIF (v_cc_seq = 4) THEN
                 :CP_payor4_auth_code1     := v_auth_code;
                 :CP_payor4_auth_eff_date1 := v_auth_eff_date;
                 :CP_payor4_auth_exp_date1 := v_auth_exp_date;
             ELSIF (v_cc_seq = 5) THEN
                 :CP_payor5_auth_code1     := v_auth_code;
                 :CP_payor5_auth_eff_date1 := v_auth_eff_date;
                 :CP_payor5_auth_exp_date1 := v_auth_exp_date;
             END IF;
             -- =============================
             -- Only return two rows and exit
             -- =============================
             EXIT;
         END IF;
         
      END LOOP; -- c_auth
      CLOSE c_auth;

   END LOOP; -- c_claim_center;

   CLOSE c_claim_center;

   RETURN(1);

END;
-- =========================================================================================
/*   -- ============================
   -- Payor 1
   -- ============================
   BEGIN
      SELECT cc.no,
             cc.name,
             pi.pricing_id,
             pi.effective_date,
             pi.expiration_date,
             pi.policy_no,
             pi.group_no,
             pi.person_code
        INTO :CP_payor1_id,
             :CP_payor1_name,
             :CP_payor1_pricing_id,
             :CP_payor1_effective_date,
             :CP_payor1_expiration_date,
             :CP_payor1_policy_no,
             :CP_payor1_group_no,
             :CP_payor1_person_code
 
        FROM claim_centers cc,
             patient_insurance pi
       WHERE pi.patient_id = :patient_id
         AND cc.no = pi.claim_center_no
         AND pi.claim_center_seq = 1;

      -- ==============================
      -- Get auth codes for payor
      -- ==============================  
      v_rec_cnt := 0;
      v_cc_no   := :CP_payor1_id;

      OPEN c_auth;
      LOOP
         FETCH c_auth
          INTO :CP_payor1_auth_code,
               :CP_payor1_auth_eff_date1,
               :CP_payor1_auth_exp_date1;

         EXIT WHEN c_auth%NOTFOUND;

         v_rec_cnt := v_rec_cnt + 1;

         IF (v_rec_cnt = 1) THEN
             EXIT;
         END IF;
      END LOOP;

      CLOSE c_auth;

   EXCEPTION
      WHEN No_Data_Found THEN
         :CP_payor1_id              := NULL;
         :CP_payor1_name            := NULL;
         :CP_payor1_pricing_id      := NULL;
         :CP_payor1_effective_date  := NULL;
         :CP_payor1_expiration_date := NULL;
         :CP_payor1_policy_no       := NULL;
         :CP_payor1_group_no    := NULL;
         :CP_payor1_person_code  := NULL;
 
   END;

   -- ============================
   -- Payor 2
   -- ============================
   BEGIN
      SELECT cc.no,
             cc.name,
             pi.pricing_id,
             pi.effective_date,
             pi.expiration_date,
             pi.policy_no,
             pi.group_no,
             pi.person_code
           
        INTO :CP_payor2_id,
             :CP_payor2_name,
             :CP_payor2_pricing_id,
             :CP_payor2_effective_date,
             :CP_payor2_expiration_date,
             :CP_payor2_policy_no,
             :CP_payor2_group_no,
             :CP_payor2_person_code
             
        FROM claim_centers cc,
             patient_insurance pi
       WHERE pi.patient_id = :patient_id
         AND cc.no = pi.claim_center_no
         AND pi.claim_center_seq = 2;

      -- ==============================
      -- Get auth codes for payor
      -- ==============================  
      v_rec_cnt := 0;
      v_cc_no   := :CP_payor2_id;

      OPEN c_auth;
      LOOP
         FETCH c_auth
          INTO :CP_payor2_auth_code,
               :CP_payor2_auth_eff_date1,
               :CP_payor2_auth_exp_date1;

         EXIT WHEN c_auth%NOTFOUND;

         v_rec_cnt := v_rec_cnt + 1;

         IF (v_rec_cnt = 1) THEN
             EXIT;
         END IF;
      END LOOP;

      CLOSE c_auth;

   EXCEPTION
      WHEN No_Data_Found THEN
         :CP_payor2_id              := NULL;
         :CP_payor2_name            := NULL;
         :CP_payor2_pricing_id      := NULL;
         :CP_payor2_effective_date  := NULL;
         :CP_payor2_expiration_date := NULL;
         :CP_payor2_policy_no       := NULL;
         :CP_payor2_group_no    := NULL;
         :CP_payor2_person_code  := NULL;
   END;

   -- ============================
   -- Payor 3
   -- ============================
   BEGIN
      SELECT cc.no,
             cc.name,
             pi.pricing_id,
             pi.effective_date,
             pi.expiration_date,
             pi.policy_no,
             pi.group_no,
             pi.person_code
        INTO :CP_payor3_id,
             :CP_payor3_name,
             :CP_payor3_pricing_id,
             :CP_payor3_effective_date,
             :CP_payor3_expiration_date,
             :CP_payor3_policy_no,
             :CP_payor3_group_no,
             :CP_payor3_person_code
        FROM claim_centers cc,
             patient_insurance pi
       WHERE pi.patient_id = :patient_id
         AND cc.no = pi.claim_center_no
         AND pi.claim_center_seq = 3;

      -- ==============================
      -- Get auth codes for payor
      -- ==============================  
      v_rec_cnt := 0;
      v_cc_no   := :CP_payor3_id;

      OPEN c_auth;
      LOOP
         FETCH c_auth
          INTO :CP_payor3_auth_code,
               :CP_payor3_auth_eff_date1,
               :CP_payor3_auth_exp_date1;

         EXIT WHEN c_auth%NOTFOUND;

         v_rec_cnt := v_rec_cnt + 1;

         IF (v_rec_cnt = 1) THEN
             EXIT;
         END IF;
      END LOOP;

      CLOSE c_auth;

   EXCEPTION
      WHEN No_Data_Found THEN
         :CP_payor3_id              := NULL;
         :CP_payor3_name            := NULL;
         :CP_payor3_pricing_id      := NULL;
         :CP_payor3_effective_date  := NULL;
         :CP_payor3_expiration_date := NULL;
         :CP_payor3_policy_no       := NULL;
         :CP_payor3_group_no    := NULL;
         :CP_payor3_person_code  := NULL;
   END;

   -- ============================
   -- Payor 4
   -- ============================
   BEGIN
      SELECT cc.no,
             cc.name,
             pi.pricing_id,
             pi.effective_date,
             pi.expiration_date
        INTO :CP_payor4_id,
             :CP_payor4_name,
             :CP_payor4_pricing_id,
             :CP_payor4_effective_date,
             :CP_payor4_expiration_date
        FROM claim_centers cc,
             patient_insurance pi
       WHERE pi.patient_id = :patient_id
         AND cc.no = pi.claim_center_no
         AND pi.claim_center_seq = 4;

      -- ==============================
      -- Get auth codes for payor
      -- ==============================  
      v_rec_cnt := 0;
      v_cc_no   := :CP_payor4_id;

      OPEN c_auth;
      LOOP
         FETCH c_auth
          INTO :CP_payor4_auth_code,
               :CP_payor4_auth_eff_date1,
               :CP_payor4_auth_exp_date1;

         EXIT WHEN c_auth%NOTFOUND;

         v_rec_cnt := v_rec_cnt + 1;

         IF (v_rec_cnt = 1) THEN
             EXIT;
         END IF;
      END LOOP;

      CLOSE c_auth;

   EXCEPTION
      WHEN No_Data_Found THEN
         :CP_payor4_id              := NULL;
         :CP_payor4_name            := NULL;
         :CP_payor4_pricing_id      := NULL;
         :CP_payor4_effective_date  := NULL;
         :CP_payor4_expiration_date := NULL;
   END;

   -- ============================
   -- Payor 5
   -- ============================
   BEGIN
      SELECT cc.no,
             cc.name,
             pi.pricing_id,
             pi.effective_date,
             pi.expiration_date
        INTO :CP_payor5_id,
             :CP_payor5_name,
             :CP_payor5_pricing_id,
             :CP_payor5_effective_date,
             :CP_payor5_expiration_date
        FROM claim_centers cc,
             patient_insurance pi
       WHERE pi.patient_id = :patient_id
         AND cc.no = pi.claim_center_no
         AND pi.claim_center_seq = 5;

      -- ==============================
      -- Get auth codes for payor
      -- ==============================  
      v_rec_cnt := 0;
      v_cc_no   := :CP_payor5_id;

      OPEN c_auth;
      LOOP
         FETCH c_auth
          INTO :CP_payor5_auth_code,
               :CP_payor5_auth_eff_date1,
               :CP_payor5_auth_exp_date1;

         EXIT WHEN c_auth%NOTFOUND;

         v_rec_cnt := v_rec_cnt + 1;

         IF (v_rec_cnt = 1) THEN
             EXIT;
         END IF;
      END LOOP;

      CLOSE c_auth;

   EXCEPTION
      WHEN No_Data_Found THEN
         :CP_payor5_id              := NULL;
         :CP_payor5_name            := NULL;
         :CP_payor5_pricing_id      := NULL;
         :CP_payor5_effective_date  := NULL;
         :CP_payor5_expiration_date := NULL;
   END;  */
0
Sara_j_11
Asked:
Sara_j_11
  • 8
  • 4
  • 4
  • +3
4 Solutions
 
pennnnCommented:
Are you trying to open the resulting CSV file in Excel? If that's the case, try to open it in Notepad or another plain text editor to check if the leading zeros are in the file. I suspect that the output file is correct and the problem is with Excel, which automatically converts the datatype of the column to Number (thus truncating the leading zeroes).
If the output file is without the leading zeroes, then we can look at the code to see why, but let's first try to find out where the problem is - Excel or Oracle...
Hope that helps!
0
 
jrb1Commented:
If the problem is Excel, you can change what you are writing to the file from 000123 to ="000123", and then Excel won't bother it.
0
 
pennnnCommented:
Ah, I've been trying to remember how this was done since I saw the question... I tried with just =000123, I tried with "000123", but didn't try both... :)
Thanks, jrb1!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sharmanarendraCommented:
I have used lpad function in past.
LPAD(<your_column_name, <max_legnth_your_formatted_field_should_be' ,'0')
0
 
Sara_j_11Author Commented:
I tried opening the file in notepad and it still shows up without the neccessary zeros , which is a part of the field.   00098767 is showing up as 98767. How do I correct this??? The output file itself is wrong..

expert jrb1 says : If the problem is Excel, you can change what you are writing to the file from 000123 to ="000123", and then Excel won't bother it.

I thought the line in the code:
'"'|| :CP_payor1_policy_no        ||'",'||   already does that. Does this not take care of that issue?

Please let me know your comments.
0
 
Sara_j_11Author Commented:
Am I doing something differnet in my code?
0
 
sharmanarendraCommented:
try
"'|| lpad(:CP_payor1_policy_no,6,'0')        ||'",'||  
0
 
pennnnCommented:
Check the properties of the :CP_payor1_policy_no placeholder. Maybe you set its datatype to number, or you've put some format mask on it. Other than that I think your code should be OK.
Hope that helps!
0
 
Sara_j_11Author Commented:
Sorry but lpad may not work in this context since there may be a number like 0002
and 000002. In that case we dont want any confusion...

 I also checked the property palette for the column and it is set as character...
0
 
Sara_j_11Author Commented:
Sorry experts , I just discovered that the problem is with EXCEL. I opened the file in notepad and the zeros are showing up..
But when I just double click and open the csv file, the zeros are missing... How do I solve this problem???
0
 
sharmanarendraCommented:
Does your source data is really 00098767? If it is Oracle won't convert character for Oracle 00098767 will make up entire string.
0
 
jrb1Commented:
My post above solves the Excel problem.

Change what you are writing to the file from 000123 to ="000123", and then Excel won't bother it.
0
 
Sara_j_11Author Commented:
dear expert jrb1:
Does this line in my code do what you ahve suggested?
'"'|| :CP_payor1_policy_no        ||'",'||  
What do you mean by Change what you are writing to the file from 000123 to ="000123", "
Please clarify with code.
0
 
Mark GeerlingsDatabase AdministratorCommented:
I think the basic problem is the function definition:
"function CF_exp_detailsFormula return Number is"

Note the "retun number" portion.  If that was "return varchar2", the leading zeroes would be preserved.

By the way, I noticed these lines near the top of what you posted:
" SELECT :P_PATH || 'unity_svcbr'||:P_SVCBR_ID ||'_'||TO_CHAR(SYSDATE,'MMDDYYYY')||'.csv'
     INTO v_report
     FROM dual;"

That is an innefficient way to get the job done, since it forces a database call and a network round trip or two that are not required.  Here is the better way to do that:

v_report := :P_PATH || 'unity_svcbr'||:P_SVCBR_ID ||'_'||TO_CHAR(SYSDATE,'MMDDYYYY')||'.csv';
0
 
Sara_j_11Author Commented:
pardon me , but I am not a reports expert, so I just tried to change the datatype from number to varchar2 as you suggested and I am getting the following error:
Error at line 0 , column 0
REP-0737  :  Should be a function with return type Number.
0
 
Sara_j_11Author Commented:
Also I am not sure if the function return type is a problem- because when I open the file in notepad , I see the zero's , but when i open it in excel I dont see them... How do I correct this issue??
0
 
Mark GeerlingsDatabase AdministratorCommented:
"Should be a function with return type Number" just means that wherever this function is being called expects a number.  That could likely be changed, but if you see the zeroes in Notepad, then the problem is not in Oracle Reports.

This is a "feature" (not a bug) in Excel that suppresses leading zeroes in what it thinks are numeric values.  If you are opening the file manually in Excel, you can tell Excel to treat the column as "Text" instead of the default "General". (This is in step 3 of the Text Import Wizard).  This should force Excel to leave the leading zeroes.
0
 
pennnnCommented:
What jrb1 is suggesting (and it should solve your problem) is:
...
'="'|| :CP_payor1_policy_no        ||'",'||
...
I.e. just adding an equal sign (=) before the double quotes, so your column will show in the output enclosed in double quotes (which you already had) but also having the = before the quotes:
..."<other columns>",="00098767","<other columns>"...

Hope that helps!
0
 
jrb1Commented:
Sorry...make this line

'"'|| :CP_payor1_policy_no        ||'",'||  

look like this

'="'|| :CP_payor1_policy_no        ||'",'||  
0
 
anand_2000vCommented:
I hope you saw what Markgeer wrote:
This is a "feature" (not a bug) in Excel that suppresses leading zeroes in what it thinks are numeric values.  If you are opening the file manually in Excel, you can tell Excel to treat the column as "Text" instead of the default "General". (This is in step 3 of the Text Import Wizard).  This should force Excel to leave the leading zeroes.

do not open the file as Excel. Rather you should import the file from excel.
Menu:
Data->Import External Data ->Import Data
When asked for a data source, specify your CSV file. While formating the columns make sure that the column with leading zeros is identified as a text column.
HTH
0
 
anand_2000vCommented:
The answer is Markgeer's I am just expanding on it.
0
 
anand_2000vCommented:
why me...give it to markgeer
0
 
Sara_j_11Author Commented:
Thanks experts.. I ended up using jrb's solution. But can someone tell me how jrb could give this solution...I mean is there a web site or something where you can find this information ..           ="21345"
I am really eager to know if this is in a oracle web site or something... Please let me know if you know...or maybe if jrb knows...
0
 
jrb1Commented:
I'll certainly answer.  I learned this by trial and error several years ago.  I was automating a process that pulled data from a DB into excel and finally into a word merge.  I'm sure it's been mentioned on some web sites too, but I don't know any offhand.  It's one of those things I spent a lot of time to get around once, so I won't forget the solution.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now