Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

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

0
pardeshirahul
Asked:
pardeshirahul
  • 12
  • 9
1 Solution
 
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
 
pardeshirahulAuthor Commented:
yes that is fine with me poh.org_id IN (161)

by mistake i wrote poh.org_id =161
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
pardeshirahulAuthor Commented:
Thanks,
i wil test the output, and update the results
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now