3 Replies Latest reply: Oct 10, 2013 3:47 AM by Zafar Iqbal RSS

    Fetch data from table and generate attachment than mail it.

    Zafar Iqbal

      Hello Experts,

       

      From couple of day I am searching on Google for a better database procedure that will help me to get data from tables and generate attachment and mail it but i fail.

      My Scenario is:

      I have a query that will fetch almost 5000 records from database tables. Each record has almost 75 characters

      select a.location_code,
                     a.item_code,
                     b.description item_desc,
                     to_char(a.manufact_date,'ddMonyy')mfg,
                     to_char((a.manufact_date + nvl(b.expiry_period,0)),'ddMonyy')expr,
                     to_char((a.manufact_date + nvl(b.qurantine_period,0)),'ddMonyy')qrtn,
                     round(nvl (b.qurantine_period, 0) - (sysdate - a.manufact_date)) days_elapsed,
                     a.closing_balance_posted quantity
                from wms_stock_current_balance_v a, wms_item_setup_mast b
               where a.closing_balance > 0
                 and a.item_code = b.item_code
                 and a.loc_type in ('RACKING','PICKING','QUICKA','BUNDLED')
                 and nvl(b.qurantine_period,0) > 0
                 and round(nvl (b.qurantine_period, 0) - (sysdate - a.manufact_date)) <= 0
            order by a.item_code, a.location_code;
      

       

      Sample data of above query is

      LOCATION_CODEITEM_CODEITEM_DESC MFG Expiry Quarantine Days Elapse Quantity
      13DL2 000000000000000F0487 CLEAR COOL BLACK 05ML 20-Feb-1031-Mar-144-Jun-13-122160
      14DL0 000000000000000F0487 CLEAR COOL BLACK 05ML 23-Feb-103-Apr-147-Jun-13-119134
      14DL2 000000000000000F0487 CLEAR COOL BLACK 05ML 23-Feb-103-Apr-147-Jun-13-119160
      14DR2 000000000000000F0487 CLEAR COOL BLACK 05ML 23-Feb-103-Apr-147-Jun-13-11920
      14LL2 000000000000000F0487 CLEAR COOL BLACK 05ML 20-Feb-1031-Mar-144-Jun-13-122160
      17ER2 000000000000000F0487 CLEAR COOL BLACK 05ML 20-Feb-1031-Mar-144-Jun-13-122160
      17GL2 000000000000000F0487 CLEAR COOL BLACK 05ML 20-Feb-1031-Mar-144-Jun-13-122160
      17SL0 000000000000000F0487 CLEAR COOL BLACK 05ML 17-Feb-1028-Mar-141-Jun-13-12564
      18QL0 000000000000000F0487 CLEAR COOL BLACK 05ML 23-Feb-103-Apr-147-Jun-13-119160
      19AR5 000000000000000F0487 CLEAR COOL BLACK 05ML 17-Feb-1028-Mar-141-Jun-13-125160
      19DL1 000000000000000F0487 CLEAR COOL BLACK 05ML 20-Feb-1031-Mar-144-Jun-13-122160
      19JR0 000000000000000F0487 CLEAR COOL BLACK 05ML 17-Feb-1028-Mar-141-Jun-13-12560
      19TL1 000000000000000F0487 CLEAR COOL BLACK 05ML 20-Feb-1031-Mar-144-Jun-13-122160
      20GR2 000000000000000F0487 CLEAR COOL BLACK 05ML 20-Feb-1031-Mar-144-Jun-13-12240
      36FL3 000000000000000F0487 CLEAR COOL BLACK 05ML 18-Feb-1029-Mar-142-Jun-13-12465
      19UR0 000000000000000F0591 COMFORT WHITE 24ML*300 28-Oct-0928-Oct-111-May-11-8871
      12SL1 000000000000000F0593 COMFORT PINK 24ML*300 28-Oct-0928-Oct-111-May-11-88742
      12SR1 000000000000000F0593 COMFORT PINK 24ML*300 28-Oct-0928-Oct-111-May-11-88742
      14OR1 000000000000000F0593 COMFORT PINK 24ML*300 28-Oct-0928-Oct-111-May-11-8878
      36EL4 000000000000000F0594 CLEAR HF DECRASE 5M*360 14-Feb-1014-Feb-1112-Oct-10-108814
      13VL1 000000000000000F0595 CLEAR COM SFT CRE 5*360 8-Feb-108-Feb-116-Oct-10-1094160
      14ER0 000000000000000F0595 CLEAR COM SFT CRE 5*360 8-Feb-108-Feb-116-Oct-10-1094105

       

      Database Info

      Oracle 10g

      Version 10.2.0.1.0