Solved

Oracle 10G -- PL/SQL INSERT STATEMENT (Loop) ?

Posted on 2011-03-04
6
589 Views
Last Modified: 2013-12-19
Below works good for part_no = '305'

How can i get it working for all 800+ parts ?
================================================================================
--truncate table condition_code_sale_price_tab;

insert into condition_code_sale_price_tab
select 'N' CONDITION_CODE, 'PVL' CONTRACT, ipis.part_no CATALOG_NO, ROUND(ipis.total_COST/ipis.total_qty*scmt.markup,2) PRICE, 'USD' CURRENCY_CODE, SYSDATE ROWVERSION
from
(
select part_no, SUM(qty_onhand) total_qty,
sum(qty_onhand * decode(inventory_part_stock_owner_api.get_part_ownership_db(contract, part_no, location_no), 'COMPANY OWNED',
inventory_part_unit_cost_api.get_inventory_value_by_method(contract, part_no, configuration_id, lot_batch_no, serial_no), 0)) TOTAL_COST
from inventory_part_in_stock
where
part_no = '305' and
CONTRACT = 'PVL' AND
condition_code_manager_api.get_condition_code (part_no,serial_no,lot_batch_no )  = 'N' and
qty_onhand - qty_reserved > 0 and
decode(inventory_part_stock_owner_api.get_part_ownership_db(contract, part_no, location_no), 'COMPANY OWNED',
inventory_part_unit_cost_api.get_inventory_value_by_method(contract, part_no, configuration_id, lot_batch_no, serial_no), 0) > 0.01
group by part_no
) ipis
inner join inventory_part_tab ipt
on ipis.part_no = ipt.part_no
inner join sab_commodity_markup_tab scmt
on scmt.commodity = ipt.PRIME_COMMODITY

--SELECT * from condition_code_sale_price_tab
0
Comment
Question by:finance_teacher
6 Comments
 
LVL 2

Assisted Solution

by:choukssa
choukssa earned 167 total points
ID: 35040526
Try this (I just made up part_table as the table from whre you get part info)


    FOR rec IN  
    (
      select part_no from part_table
    )
    LOOP
    
    INSERT
    INTO   condition_code_sale_price_tab
    SELECT 'N'                                                 CONDITION_CODE,
           'PVL'                                               CONTRACT      ,
           ipis.part_no                                        CATALOG_NO    ,
           ROUND(ipis.total_COST/ipis.total_qty*scmt.markup,2) PRICE         ,
           'USD'                                               CURRENCY_CODE ,
           SYSDATE                                             ROWVERSION
    FROM   ( SELECT  part_no                 ,
                    SUM(qty_onhand)                                                                                                                                                                                                        total_qty,
                    SUM(qty_onhand * DECODE(inventory_part_stock_owner_api.get_part_ownership_db(contract, part_no, location_no), 'COMPANY OWNED', inventory_part_unit_cost_api.get_inventory_value_by_method(contract, part_no, configuration_id, lot_batch_no, serial_no), 0)) TOTAL_COST
           FROM     inventory_part_in_stock
           WHERE    part_no                                                                                                                                                                    = [b]rec.part_no
[/b]           AND      CONTRACT                                                                                                                                                                                                        = 'PVL'
           AND      condition_code_manager_api.get_condition_code (part_no,serial_no,lot_batch_no )                                                                                                                                                            = 'N'
           AND      qty_onhand - qty_reserved                                                                                                                                                                                                        > 0
           AND      DECODE(inventory_part_stock_owner_api.get_part_ownership_db(contract, part_no, location_no), 'COMPANY OWNED', inventory_part_unit_cost_api.get_inventory_value_by_method(contract, part_no, configuration_id, lot_batch_no, serial_no), 0) > 0.01
           GROUP BY part_no
           )
           ipis
           INNER JOIN inventory_part_tab ipt
           ON     ipis.part_no = ipt.part_no
           INNER JOIN sab_commodity_markup_tab scmt
           ON     scmt.commodity = ipt.PRIME_COMMODITY;
           
    END LOOP;

Open in new window

0
 
LVL 2

Expert Comment

by:choukssa
ID: 35040537
I tried to put rec.part_no in bold face but the editor made it rec.part_no so ignore those. rest is fine.

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 35040591
You might be able to change the sums to the analytic versions and get away without using pl/sql.

So: SUM(qty_onhand) total_qty

Becomes: SUM(qty_onhand) over(partition by part_id order by part_id) total_qty

Then either do away with: part_no = '305' and

Or just add all of them:
part_no in ( '305', '306','307', ...) and
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:awking00
ID: 35070527
Where are the 800+ part_no's? If they are all of the part_no's in the inventory_part_in_stock, then just remove the condition part_no = '305'.
0
 
LVL 3

Accepted Solution

by:
gajmp earned 167 total points
ID: 35076270
Remove predicate "part_no = '305'" and convert that DECODE to CASE
SUM
(qty_onhand *
CASE
    WHEN inventory_part_stock_owner_api.get_part_ownership_db(contract,part_no, location_no) = 'COMPANY OWNED' THEN inventory_part_unit_cost_api.get_inventory_value_by_method(contract,part_no,configuration_id,lot_batch_no,serial_no)
ELSE 0
END
) AS total_cost


If you wants to do this in pl/sql then use BULK COLLECT
0
 

Author Comment

by:finance_teacher
ID: 35124652
Ok, I will test.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question