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

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
finance_teacherAsked:
Who is Participating?
 
gajmpConnect With a Mentor Commented:
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
 
choukssaConnect With a Mentor Commented:
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
 
choukssaCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
awking00Commented:
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
 
finance_teacherAuthor Commented:
Ok, I will test.
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.