Link to home
Start Free TrialLog in
Avatar of pardeshirahul
pardeshirahul

asked on

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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Why not just leave it as is and still use the IN for a single id?

AND poh.org_id IN (161)
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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:
https://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
Avatar of pardeshirahul
pardeshirahul

ASKER

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

by mistake i wrote poh.org_id =161
Avatar of pardeshirahul
pardeshirahul

ASKER

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
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?
Avatar of pardeshirahul
pardeshirahul

ASKER

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)
Avatar of pardeshirahul
pardeshirahul

ASKER

so the sql stement will have a case statemnt
and the parameter passed will be l;ike :p_org_id
Avatar of pardeshirahul
pardeshirahul

ASKER

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
>>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.
Avatar of pardeshirahul
pardeshirahul

ASKER

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
Avatar of pardeshirahul
pardeshirahul

ASKER

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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
>> --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.
Avatar of pardeshirahul
pardeshirahul

ASKER

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)
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
>>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)
)
Avatar of pardeshirahul
pardeshirahul

ASKER

i quite didn't understand the logic of this case statement which you provided
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
>>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.
Avatar of pardeshirahul
pardeshirahul

ASKER

will there be another way, just curious
i will be using this in my code
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
>>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?
Avatar of pardeshirahul
pardeshirahul

ASKER

No i am teeting the data right now by running it for individual orgs
the report is taking long time to run
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
>>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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pardeshirahul
pardeshirahul

ASKER

Thanks,
i wil test the output, and update the results