Przekazać wyjście do kolumny sub-zapytania

głosy
0

Mam ten prcodeure poniżej:

PROCEDURE return_information (i_vcust_cd IN VARCHAR2,
                           i_vplace_of_ship IN VARCHAR2,
                           i_vstatus IN VARCHAR2,
                           i_vdate_to IN VARCHAR2,
                           i_vdate_from IN VARCHAR2,
                           o_Cresultset OUT SYS_REFCURSOR)
                          as

BEGIN 

 OPEN o_Cresultset for 

SELECT 
rownum as seq,
ship_cust_cd as place_of_shipment,
c.FIRM_NM_LOCL as customer_name,
DD.src as source,
DD.part_no as part_number,
p.PART_NM_ENG as part_name,
p.main_mdl as vehicle_type,
sale_qty as sales_qty,
sale_prce as sales_price,
sale_amt as sales_amount,
(select  ROUND(NVL(b.sale_prce,0) *  NVL(a.appl_rate,0),0) as sale_prce FROM 
 SALE_PRICE_APPLY_RATE a, PARTS_SALES_PRICES b,
 (SELECT P.src,
      P.part_no,
      part_src,  
      rcpt_prce,  
      prce_grad_cd, 
      decode(substr(main_mdl,1,1),  'C','C','S','S','P') as flag
FROM parts P
     WHERE P.src            =    'M'              -- SOURCE
  AND P.part_no  =  '90887656') c   -- PART NUMBER. current part no is temp.
WHERE a.CUST_CD = i_vcust_cd                                        --RETRUN                dealer CODE(cust_cd)
 and a.sratm0_flag = c.flag
 and a.PRCE_APPL_CD = 'DW'                                      -- RETRUN     dealer infor(prce_cd)
 and b.src = c.src
 and b.part_no = trim(c.part_no)
 and b.prce_cd = a.PRCE_APPL_CD) as return_price,            --RETURN_PRICE,
 req_remark as remark, 
 status as status,
 DD.reg_dt as registered_date,
 cnfm_dt as completed_date,
PRE_SLIP_YEAR||PRE_SLIP_DEPT_CD||PRE_SLIP_SEQ as return_statement,
NEW_SLIP_YEAR||NEW_SLIP_DEPT_CD||NEW_SLIP_SEQ as  shipment_statement





FROM   DOMESTIC_ORDER_DIRECT_DELIVERY DD,
   CUSTOMERS C,
   PARTS P
 WHERE  RETURN_CUST_CD =  i_vcust_cd   -- Login dealer code
 AND    ship_cust_cd LIKE NVL(i_vplace_of_ship, '%')   -- place of return  
 AND    STATUS LIKE NVL(i_vstatus, '%') -- 100,300,900,ALL  
 AND    DD.RETURN_CUST_CD = C.CUST_CD
 AND    DD.SRC = P.SRC
 AND    DD.PART_NO = trim(P.PART_NO)
 AND    dd.REG_DT >= i_vdate_from -- date from  
 AND    dd.REG_DT <= i_vdate_to-- date to 
 ;
 end return_information;

Problem mam jest to, że trzeba obliczyć „return_price” ale korzysta szereg części, które są identyczne z „” PART_NUMBER kolumnie. Jednakże, bez przechodzenia w numerze katalogowym jestem pewien jak mapować wyjście numer udział w kursora do klauzuli gdzie w podzapytaniu zwracającej „return_price”

Utwórz 14/06/2016 o 17:41
użytkownik
W innych językach...                            

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more