Oracle 11x Newbie - simple, but sincere...

Question

First, thank you for taking the time to read this, I am fairly well experienced with most versions of MSSQL, but not so much with Oracle and PL SQL.

My problem and question are this: I have a SSRS report in MSSQK2k12 that is calling a stored proc on an Oracle 11x db. there are several params passed in that are used in the where clause, I need to add one more param ( a simple Y/N) that will add additional filters to the where clause, effectively saying at the report prompt 'do you want to see all parts? (Y/N). A 'Y' answer will run the proc nearly wide open, including parts that have no inventory, parts on hand, but already sold, parts no longer active. A 'N' response will pass filters that require available inventory, valid current part class, etc. I have attempted a half-dozen seemingly good solutions, to no avail. I have asked an associate (who is well versed in PLSQL) for his advice, his resulting 'DECODE' addition did nothing. I would like to request advice/assistance that does not involve months of study, as I only have a few days to finish. Please note, the original SP was not my work, I have inherited from predecessor would did/could not complete. Stored procedure (Scrubbed) attached

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;
/

Answer 1

so with the union all or union solution probably looks something like this

/* Formatted on 7/5/2016 1:50:34 PM (QP5 v5.256.13226.35510) */
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 and p_ViewAll = 'Y'
 UNION ALL

 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 and p_ViewAll = 'N'
 and myfilterstuff = 'whatever'

Answer 2

You have to use UNION and not decode. In one side of UNION you have to handle parameter Y with outer join and other will be as you have coded already.

OR you need to use if / else of PLSQL since user can pass only one of them as parameter.

To be honest, this question is nothing specific to oracle and mere SQL.