问甲骨文11x新手-简单，但真诚.

EN

CREATE OR REPLACE PROCEDURE ORACLE_PROD.RPT_LIKE_PARTS_TEST (
    p_Pos1 IN varchar2 
    , p_Description IN varchar2 
    , p_StartPos IN varchar2 
    , p_StartPosValue IN varchar2
    , p_ViewAll IN varchar2  --ADDED to allow filtered return for data
    , p_recordset OUT SYS_REFCURSOR)
AS
BEGIN

OPEN p_recordset FOR
  SELECT T1.ITEM 
    , T1.REVISION
    , T1.DESCRIPTION
    , T2.CCN
    , T2.DELETED
    , T2.OBSOLETED
    , T2.FINGOOD
    , T2.ABC
    , T3.MAS_LOC
    , T3.LOCATION
    , (T3.OH_QTY - T3.COM_QTY) - T3.RESV_QTY as Avail_QTY
    , T1.USER_NUM1
    , case  when T2.HALT<>' ' then 'Y' else NULL end as Halt
    , T4.DESCRIPTION as T4_Description
    , det.OH_QTY as Det_OH_QTY
    , det.COM_QTY as Det_COM_QTY
    , DECODE(det.INSP_STAT,'3','Passed Inpection' ,DECODE det.INSP_STAT,'1','Waiting Inspection' ,' ')) as Inspect_Descr
    , T3.RESV_QTY

     from ITEM T1
    LEFT OUTER JOIN ITEM_CCN T2
        ON T1.ITEM=T2.ITEM 
        and T1.REVISION=T2.REVISION
    LEFT OUTER JOIN ITEM_LOC T3
        on T2.CCN=T3.CCN 
        and T2.ITEM=T3.ITEM 
        and T2.REVISION=T3.REVISION
    LEFT OUTER JOIN HALT T4
        on T2.HALT=T4.HALT 
        and T2.CCN=T4.CCN
    LEFT OUTER JOIN ITEM_DET det
        on T3.CCN=det.CCN 
        and T3.ITEM=det.ITEM 
        and T3.REVISION=det.REVISION 
        and T3.MAS_LOC=det.MAS_LOC 
        and T3.LOCATION=det.LOCATION
 where  T2.OBSOLETED is null 
    --**  all commented parts are attempted adds
    --&&&and CASE (p_ViewAll)
    --&&& when 'N' 
   --&&& THEN --T2.HALT = DECODE(T2.HALT,'DSGN', 'XXX',' ','XXX',T2.HALT)
    -- and 
   --&&& (T2.HALT != 'DSGN' and (((T3.OH_QTY - T3.COM_QTY) - T3.RESV_QTY))> 0 
    --and  T2.HALT <> 'DSGN' and T3.mas_loc <>'99' and T3.mas_loc <>' ' 

   --&&&WHEN 'Y'
  --&&&  THEN
   and  T2.HALT <>'DSGN'
    --&&&ELSE  NULL-- or T3.mas_loc <> '')
    --&&&END
    --**
      /*and T2.HALT = decode(p_ViewAll,'Y', 
                            DECODE(T2.HALT,'DSGN', T2.HALT),
                            DECODE(T2.HALT,'DSGN', T2.HALT)          

                            --DECODE(T2.HALT,'DSGN', 'XXX',' ','XXX',T2.HALT),
                            --DECODE(T2.HALT,'DSGN', 'XXX', T2.HALT)                            
                   )
       and (p_ViewAll != 'Y' or T3.mas_loc not in ('99',' '))*/
--**



    AND UPPER(TRIM(T1.ITEM)) LIKE (CASE WHEN LENGTH(TRIM(p_pos1)) > 0 THEN UPPER(TRIM(p_pos1) || '%') ELSE UPPER(TRIM(T1.ITEM))END)
    AND UPPER(T1.DESCRIPTION) LIKE (CASE WHEN LENGTH(p_Description) > 0 THEN UPPER(('%' || p_Description || '%')) ELSE UPPER(UPPER(T1.DESCRIPTION))END)
    AND 
            (CASE    WHEN TO_NUMBER(NVL(TRIM(p_StartPos),'0')) > 0 THEN SUBSTR(TRIM(T1.ITEM),TO_NUMBER(TRIM(p_StartPos)),NVL(LENGTH(UPPER(TRIM(p_StartPosValue))),'0'))
                    ELSE 'False'
            END)

            =

             (CASE    WHEN TO_NUMBER(NVL(TRIM(p_StartPos),'0')) > 0  THEN NVL(UPPER(TRIM(p_StartPosValue)),'')
                    ELSE 'False'
            END)
ORDER BY T1.ITEM 
, T1.revision desc 
;
END RPT_LIKE_PARTS_TEST;
/