Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-04
6
Medium Priority
?
600 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 668 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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 668 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

876 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