?
Solved

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

Posted on 2011-03-04
6
Medium Priority
?
597 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 77

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

719 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