Solved

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

Posted on 2011-03-04
6
591 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 77

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

679 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