This discussion is archived
3 Replies Latest reply: Oct 10, 2013 1:47 AM by Zafar Iqbal RSS

Fetch data from table and generate attachment than mail it.

Zafar Iqbal Newbie
Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points