How to add the case statement for the org_id

How to add the logic for this conditions

             AND poh.org_id IN (162, 161, 120, 128, 1077)

i am converting this sql statement into a concurrent program
for the org_id
when ther is now value passed to the parameter for org_id
it should consider
all the orgs 162, 161, 120, 128, 1077

but when passed the specific org_id
the the query should automatically consider

the specific org
like
poh.org_id =161
 SELECT (SELECT segment1
                FROM mtl_system_items_b d
               WHERE organization_id = 103 AND inventory_item_id = pl.item_id)
                item_num,
             pl.item_description item_description,
             v.segment1 vendor_number,
             vendor_name,
             vendor_site_code,
             poh.segment1 po_number,
             rel.release_num,
             NVL (plla.quantity - NVL (plla.quantity_cancelled, 0), 0)
                qty_ordered,
             QUANTITY_RECEIVED,
             plla.need_by_date,
             plla.CREATION_DATE,
             hla.location_code Ship_To_Location,
             DECODE (poh.cancel_flag, 'Y', 'Cancelled') status,
             rel.authorization_status Release_authorization,
             poh.authorization_status PO_authorization,
             agent_name,
             poh.type_lookup_code,
             plla.consigned_flag,
             rel.consigned_consumption_flag,
             currency_code,
             unit_price,
             pl.quantity * pl.unit_price PO_Amount
        FROM po_line_locations_all plla,
             po_lines_all pl,
             po_headers_all poh,
             po_vendors v,
             po_vendor_sites_all vs,
             po_releases_all rel,
             po_agents_v poa,
             apps.hr_locations hla
       WHERE     poh.po_header_id = pl.po_header_id
             AND poh.po_header_id = plla.po_header_id
             AND pl.po_line_id = plla.po_line_id
             AND poh.org_id = plla.org_id
             AND poh.ship_to_location_id = hla.location_id
             AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
             AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
             AND poh.vendor_id = v.vendor_id
             AND poh.vendor_site_id = vs.vendor_site_id
             AND plla.po_header_id = rel.po_header_id(+)
             AND plla.po_release_id = rel.po_release_id(+)
             AND poh.agent_id = poa.agent_id
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
             AND NVL (pl.closed_code, 'OPEN') = 'OPEN'
             AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
             AND poh.org_id IN (162, 161, 120, 128, 1077)
             AND plla.shipment_type IN ('BLANKET', 'STANDARD');

Open in new window

pardeshirahulAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
If I have them right, it should go like this:


...
          AND poh.po_header_id = pl.po_header_id
             and (
                (poh.org_id = p_org_id) or
                (poh.org_id in IN (162, 161, 120, 128, 1077)
                       and 1 = case when p_org_id = 'ALL' then 1 else 0 end)
                )
      UNION ALL
...
0
 
slightwv (䄆 Netminder) Commented:
Why not just leave it as is and still use the IN for a single id?

AND poh.org_id IN (161)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not sure what the problem is because:
poh.org_id =161
and
poh.org_id IN (161)
are equivalent.

I presume the value/list is a actually a parameter, in which case you shall read this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pardeshirahulAuthor Commented:
yes that is fine with me poh.org_id IN (161)

by mistake i wrote poh.org_id =161
0
 
pardeshirahulAuthor Commented:
i mean how to add the case statment in the sql quer

like if the in parameter = 'ALL' the consider all the org
else consider which ever org is passed
0
 
slightwv (䄆 Netminder) Commented:
What does your parameter look like?

Are you passing in a comma seperated string like: '1,2,3' and doing dynamic SQL to execute it or are you just now trying to figure this out?
0
 
pardeshirahulAuthor Commented:
the parameter which is passed is like
poh.org_id = 162
poh.org_id = 161
poh.org_id =120
poh.org_id =128
poh.org_id =1077
if nothing is passed the consider all the org at the same time

poh.org_id in (162, 161, 120, 128, 1077)
0
 
pardeshirahulAuthor Commented:
so the sql stement will have a case statemnt
and the parameter passed will be l;ike :p_org_id
0
 
pardeshirahulAuthor Commented:
i want to add a case statement

just for FYI

   AND (CASE
                       WHEN p_currency = 'FOREIGN CURRENCY'
                            AND glh.currency_code IN ('USD', 'GBP', 'EUR')
                       THEN
                          1
                       WHEN p_currency = 'LOCAL CURRENCY'
                            AND glh.currency_code != 'USD'
                       THEN
                          1
                       ELSE
                          NULL
                    END) = 1

some what like this

like th above query
instead of p_currency  my parameter will be p_org_id
0
 
slightwv (䄆 Netminder) Commented:
>>instead of p_currency  my parameter will be p_org_id

What is the format of p_org_id going to be?  In the above example you just posted, p_currency is a single value.  From what you originaly asked, p_org_id can be a delmited list.
0
 
pardeshirahulAuthor Commented:
p_org_id is a numeris value

when is the org_id
in my query i have considerd all the org_id at one time

what the use needs is

suppose when null  is passed to p-org_id
then the query should consider

poh.org_id in (162, 161, 120, 128, 1077)

or

poh.org_id = 162
poh.org_id = 161
poh.org_id =120
poh.org_id =128
poh.org_id =1077
0
 
pardeshirahulAuthor Commented:
this is my procedure
i am trying to build the case statement
CREATE OR REPLACE PROCEDURE BOLINF.tpco_FLAT_FILE_PO (
   errbuff         OUT VARCHAR2,
   retcode         OUT VARCHAR2,
   p_dir_path   IN     VARCHAR2,
   p_org_id VARCHAR2)
IS
   --  $ HEADER TPCO_BANK_RECON_INTERFACE.PRC V1.7 vpuppala 06-18-10 $
   --
   -- PURPOSE: TO GET THE BANK RECON INFORMATION.
   --
   -- MODIFICATION HISTORY
   -- PERSON              DATE                COMMENTS
   -- -------------------------------------------------------
   -- KIRAN BOUROJU    27-SEP-2008        INITIAL CREATION
   -- GIRISH NARNE     03-APR-2009        INITIAL CREATION
   -- VASU PUPPALA     23-JUN-2009        Added the functionality to get the local currency for all currencies except USD.
   -- VASU PUPPALA     18-JUN-2010        Added the new bank accounts.

   CURSOR c1
   IS
      SELECT pl.item_id Inventory_Item_ID,
             (SELECT segment1
                FROM mtl_system_items_b d
               WHERE organization_id = 103 AND inventory_item_id = pl.item_id)
                item_num,
             pl.item_description item_description,
             DECODE (
                poh.cancel_flag,
                'Y', 'Cancelled',
                DECODE (poh.authorization_status,
                        'APPROVED', 'Approved',
                        'REJECTED', 'Rejected',
                        'IN PROCESS', 'In-Process',
                        'REQUIRES REAPPROVAL', 'Reapproval',
                        'Incomplete'))
                Status,
             --plla.ship_to_location_id "Ship to Location ID",
             hla.location_code Ship_To_Location,
             poh.creation_date,
             plla.need_by_date,
             NVL (plla.quantity, 0) qty_ordered,
             NVL (plla.quantity, 0) - NVL (plla.quantity_received, 0)
                qty_remaining,
             poh.segment1 bpa_number,
             NULL release_number,
             currency_code,
             unit_price,
             --    NVL (plla.quantity_received, 0) * pl.unit_price overall_value
             pl.quantity * pl.unit_price PO_Amount
        FROM po_line_locations_all plla,
             po_lines_all pl,
             po_headers_all poh,
             apps.hr_locations hla
       WHERE     plla.po_line_id = pl.po_line_id
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
             AND NVL (pl.closed_code, 'OPEN') = 'OPEN'
             AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
             AND poh.org_id = plla.org_id
             AND poh.org_id = plla.org_id
             AND poh.po_header_id = pl.po_header_id
             AND (CASE
                       WHEN p_ORg_ID = 'ALL'
                          THEN
                       poh.org_id IN (162, 161, 120, 128, 1077)
                                              ELSE
                          p_org_id=p_org_id
                    END)
             --AND poh.org_id IN (162, 161, 120, 128, 1077)
      UNION ALL
      SELECT pl.item_id Inventory_Item_ID,
             (SELECT segment1
                FROM mtl_system_items_b d
               WHERE organization_id = 103 AND inventory_item_id = pl.item_id)
                item_num,
             pl.item_description item_description,
             DECODE (
                poh.cancel_flag,
                'Y', 'Cancelled',
                DECODE (poh.authorization_status,
                        'APPROVED', 'Approved',
                        'REJECTED', 'Rejected',
                        'IN PROCESS', 'In-Process',
                        'REQUIRES REAPPROVAL', 'Reapproval',
                        'Incomplete'))
                Status,
             --plla.ship_to_location_id "Ship to Location ID",
             hla.location_code Ship_To_Location,
             por.creation_date,
             plla.need_by_date,
             NVL (plla.quantity, 0) qty_ordered,
             NVL (plla.quantity, 0) - NVL (plla.quantity_received, 0)
                qty_remaining,
             poh.segment1 bpa_number,
             por.release_num release_number,
             currency_code,
             unit_price,
             --    NVL (plla.quantity_received, 0) * pl.unit_price overall_value
             pl.quantity * pl.unit_price PO_Amount
        FROM po_line_locations_all plla,
             po_lines_all pl,
             po_headers_all poh,
             po_releases_all por,
             apps.hr_locations hla
       WHERE     plla.po_line_id = pl.po_line_id
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
             AND NVL (pl.closed_code, 'OPEN') = 'OPEN'
             AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
             AND poh.org_id = plla.org_id
             AND poh.org_id = plla.org_id
             AND poh.po_header_id = pl.po_header_id
             AND POH.ORG_ID IN (162, 161, 120, 128, 1077)
             AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
             AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
             AND por.po_release_id = plla.PO_RELEASE_ID
             AND hla.location_id = poh.ship_to_location_id
             AND poh.authorization_status = 'APPROVED';


   v_file       UTL_FILE.file_type;
   c_filename   VARCHAR2 (500)
      :=    TO_CHAR (SYSDATE, 'DDMMYYYYHHMMSS')
         || fnd_global.conc_request_id
         || '.txt';
   l_org_id     NUMBER;
   l_name       VARCHAR2 (50);
   l_source     VARCHAR2 (100);
   l_batch_id   NUMBER;
BEGIN
   IF UTL_FILE.is_open (v_file)
   THEN
      UTL_FILE.fclose (v_file);
   END IF;

   v_file :=
      UTL_FILE.fopen (p_dir_path,
                      c_filename,
                      'W',
                      32767);
   fnd_file.new_line (fnd_file.LOG, 2);
   fnd_file.
    put_line (
      fnd_file.LOG,
      '**********************************************************************');
   fnd_file.
    put_line (fnd_file.LOG,
              'LOAD STATISTICS FOR TECUMSEH BANK RECONCILIATION INTERFACE');
   fnd_file.
    put_line (
      fnd_file.LOG,
      '**********************************************************************');
   fnd_file.
    put_line (fnd_file.LOG, 'PARAMETERS PASSED DURING THE EXECUTION ARE :');
   -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'ORGANIZATION ID : ' || L_ORG_ID );
   fnd_file.put_line (fnd_file.LOG, 'OUTPUT DIRECTORY PATH : ' || p_dir_path);

   fnd_file.
    put_line (
      fnd_file.LOG,
      '**********************************************************************');
   fnd_file.
    put_line (fnd_file.LOG,
              'FOLLOWING ERROR RECORDS WERE FOUND DURING THE EXTRACT :');

   fnd_file.
    put_line (
      fnd_file.output,
         'inventory_item_id'
      || '|'
      || 'item_num'
      || '|'
      || 'item_description'
      || '|'
      || 'status'
      || '|'
      || 'ship_to_location'
      || '|'
      || 'creation_date'
      || '|'
      || 'need_by_date'
      || '|'
      || 'qty_ordered'
      || '|'
      || 'qty_remaining'
      || '|'
      || 'bpa_number'
      || '|'
      || 'currency_code'
      || '|'
      || 'unit_price'
      || '|'
      || 'po_amount');

   FOR c1rec IN c1
   LOOP
      UTL_FILE.
       put_line (
         v_file,
            c1rec.inventory_item_id
         || '|'
         || c1rec.item_num
         || '|'
         || c1rec.item_description
         || '|'
         || c1rec.status
         || '|'
         || c1rec.ship_to_location
         || '|'
         || c1rec.creation_date
         || '|'
         || c1rec.need_by_date
         || '|'
         || c1rec.qty_ordered
         || '|'
         || c1rec.qty_remaining
         || '|'
         || c1rec.bpa_number
         || '|'
         || c1rec.currency_code
         || '|'
         || c1rec.unit_price
         || '|'
         || c1rec.po_amount);
      fnd_file.
       put_line (
         fnd_file.output,
            c1rec.inventory_item_id
         || '|'
         || c1rec.item_num
         || '|'
         || c1rec.item_description
         || '|'
         || c1rec.status
         || '|'
         || c1rec.ship_to_location
         || '|'
         || c1rec.creation_date
         || '|'
         || c1rec.need_by_date
         || '|'
         || c1rec.qty_ordered
         || '|'
         || c1rec.qty_remaining
         || '|'
         || c1rec.bpa_number
         || '|'
         || c1rec.currency_code
         || '|'
         || c1rec.unit_price
         || '|'
         || c1rec.po_amount);
   END LOOP;


   UTL_FILE.fclose (v_file);
/* UTL FILE EXCEPTIONS */
EXCEPTION
   WHEN UTL_FILE.invalid_path
   THEN
      fnd_file.put_line (fnd_file.LOG, 'INVALID PATH');
   WHEN UTL_FILE.invalid_filehandle
   THEN
      fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);
   WHEN UTL_FILE.invalid_operation
   THEN
      fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);
   WHEN UTL_FILE.write_error
   THEN
      fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);
   WHEN UTL_FILE.internal_error
   THEN
      fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);
      RAISE;
END tpco_FLAT_FILE_PO;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>> --AND poh.org_id IN (162, 161, 120, 128, 1077)

Is this actually ALL the org_ids?  If so try this:

...
and  poh.org_id = case when p_org_id ='ALL' then poh_org_id else p_org_id end
...

If not and this is only the ones that 'ALL' represents then we'll need something else.
0
 
pardeshirahulAuthor Commented:
if i did this its fine
and  poh.org_id = case when p_org_id ='ALL' then poh_org_id else p_org_id end

but there are more then 162, 161, 120, 128, 1077
 this many orgs

i have to only consider (162, 161, 120, 128, 1077)
0
 
slightwv (䄆 Netminder) Commented:
>>but there are more then 162, 161, 120, 128, 1077

OK, then try:

and (
(poh.org_id = p_org_id) or
(poh.org_id in IN (162, 161, 120, 128, 1077)
 and 1 = case when p_org_id = 'ALL' then 1 else 0 end)
)
0
 
pardeshirahulAuthor Commented:
i quite didn't understand the logic of this case statement which you provided
0
 
slightwv (䄆 Netminder) Commented:
>>and 1 = case when p_org_id = 'ALL' then 1 else 0 end)

All I did was provide an 'or' condition that would fail if p_org_id = 'ALL'.

Basically this will mane the AND part of that clause either 1=0 (false) or 1=1 (true) based on p_org_id.


The first part  "(poh.org_id = p_org_id) or"  will work for a single org_id being passed in.

If 'ALL' is passed in it will fail unless you have an org_id that = 'ALL'.



The second part "(poh.org_id in IN (162, 161, 120, 128, 1077) and 1 = case when p_org_id = 'ALL' then 1 else 0 end)) "

This will pull back the specific org_ids in the IN list ONLY if p_org_id = 'ALL' since it if isn't the case statement will come out: 1=0.
0
 
pardeshirahulAuthor Commented:
will there be another way, just curious
i will be using this in my code
0
 
slightwv (䄆 Netminder) Commented:
>>will there be another way, just curious

There might be but that is the way I came up with.  I cannot think of any easier way if that is what you are asking.

I mean you can definitly do dynamic SQL and build the query at run time but I really don't think that is 'easier'.

Does what I posted not work or is it just that you aren't understanding what it is doing?
0
 
pardeshirahulAuthor Commented:
No i am teeting the data right now by running it for individual orgs
the report is taking long time to run
0
 
slightwv (䄆 Netminder) Commented:
>>the report is taking long time to run

Can I assume that it is longer than it did before?  If so, make sure you have all the paranthesis in the right places.
0
 
pardeshirahulAuthor Commented:
Thanks,
i wil test the output, and update the results
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.