Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql program for table updation (9i)
pl/sql program for table updation [message #303554] Fri, 29 February 2008 14:22 Go to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
Hi,

I need to create new table as follows and update the records from other table using pl/sql .Pl help me in completing pl/sql program as I have written some code like this

1)new table --

CREATE TABLE XXNFMA_PPS_PACKAGING(
  PIM_KEY            INT,
  OCS_PRODUCT_KEY    INT,
  PACKAGE_LEVEL      INT,
  FROM_UOM           VARCHAR2(30),
  CONVERSION_FACTOR  INT,   
  TO_UOM             VARCHAR2(30));


2)Reference table ,from where I need to bring the data to my new table using pl/sql progrm by imposing few rules.

CREATE TABLE XXNFMA.XXNFMA_PPS_PRODUCTS
(
  PIM_KEY                        INTEGER        NOT NULL,
  COMPANY                        VARCHAR2(254 BYTE),
  DESCRIPTION                    VARCHAR2(4000 BYTE),
  PART_NUMBER                    VARCHAR2(50 BYTE),
  CATEGORY                       VARCHAR2(254 BYTE),
  PRODUCT_SPEND_CATEGORY         VARCHAR2(50 BYTE),
  OCS_PRODUCT_KEY                INTEGER,
  LEVEL_OF_CONFIDENCE            VARCHAR2(1 BYTE),
  MFRDIST_ITEM                   VARCHAR2(1 BYTE),
  NATIONAL_DRUG_CODE             VARCHAR2(50 BYTE),
  NDC_FORMAT_INDICATOR           VARCHAR2(1 BYTE),
  NOVAPLUS_FLAG                  VARCHAR2(1 BYTE),
  FUNCTIONAL_EQUIVALENT_CODE     VARCHAR2(50 BYTE),
  OUTER_PACK_UOM                 VARCHAR2(3 BYTE),
  OUTER_PACK_CONVERSION_FACTOR   NUMBER(28,12),
  OUTER_PACK_UNIT_OF_USE_QTY     NUMBER(28,12),
  OUTER_SUBPACK_QUANTITY         NUMBER(28,12),
  INTERMEDIATE_PACK_UOM          VARCHAR2(3 BYTE),
  INTERMEDIATE_PACK_CONV_FACTOR  NUMBER(28,12),
  INTERMEDIATE_SUBPACK_QUANTITY  NUMBER(28,12),
  UNIT_OF_USE                    VARCHAR2(3 BYTE),
  UNIT_OF_USE_CONVERSION_FACTOR  NUMBER(28,12),
  SUBPACK_UOM                    VARCHAR2(3 BYTE),
  SUPPLYLINE_DESCRIPTION         VARCHAR2(600 BYTE),
  PACKAGE_DESCRIPTION            VARCHAR2(150 BYTE),
  BRAND_NAME                     VARCHAR2(30 BYTE),
  LABEL_NAME                     VARCHAR2(80 BYTE),
  PRODUCT_ASSOC_PART_NUMBER      VARCHAR2(50 BYTE),
  PRODUCT_ASSOC_NAME             VARCHAR2(100 BYTE),
  PRODUCT_ASSOC_COMPANY          VARCHAR2(254 BYTE),
  PRODUCT_ASSOC_PIM_KEY          INTEGER
)


3) Rules need to be used while loading the data are
1. Outer_Inter_CF = Outer_UoU_CF / Inter_UoU_CF
2. Outer_Inter_CF = Outer_Subpack_CF / Inter_Subpack_CF

3. Outer_UoU_CF = Outer_Inter_CF * Inter_UoU_CF
4. Outer_UoU_CF = Outer_Subpack_CF / UoU_Subpack_CF

5. Outer_Subpack_CF = Outer_Inter_CF * Inter_Subpack_CF
6. Outer_Subpack_CF = Outer_UoU_CF * UoU_Subpack_CF

7. Inter_UoU_CF = Outer_UoU_CF / Outer_Inter_CF
8. Inter_UoU_CF = Inter_Subpack_CF / UoU_Subpack_CF

9. Inter_Subpack_CF = Outer_Subpack_CF / Outer_Inter_CF
10. Inter_Subpack_CF = Inter_UoU_CF * UoU_Subpack_CF

11. UoU_Subpack_CF = Outer_Subpack_CF / Outer_UoU_CF
12. UoU_Subpack_CF = Inter_Subpack_CF / Inter_UoU_CF


4) My pl/sql program

a) created new table using create statement .

b) for updating table with rules ,the pl/sql is


declare 

cursor c_rec is
select * from xxnfma_pps_packaging;

cursor con_uom(prd_key in  number,sub_prd in varchar2 ) is 
  select 1 from vha_int_price where product_key = prd_key 
                 and uom_key = sub_prd ;

sub_exists number := 0;

begin 

UPDATE XXNFMA_PPS_PACKAGING 
SET(PIM_KEY,OCS_PRODUCT_KEY) = (SELECT PIM_KEY,OCS_PRODUCT_KEY from XXNFMA_PPS_PRODUCTS);


   for c1 in c_rec loop 

       sub_exists := 0;

       open con_uom(c1.ocs_product_key , c1.SUBPACK_UOM);

         fetch con_uom into sub_exists;

        close con_uom;

       if sub_exists = 1 then 



UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=1,FROM_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=1,
TO_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS) WHERE CONVERSION_FACTOR IS NOT NULL;



UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=2,FROM_UOM=(SELECT UNIT_OF_USE FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=(SELECT 
OUTER_PACK_UNIT_OF_USE_QTY FROM XXNFMA_PPS_PRODUCTS),TO_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS)
WHERE CONVERSION_FACTOR IS NOT NULL;

UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=3,FROM_UOM=(SELECT SUBPACK_UOM FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=(SELECT 
UNIT_OF_USE_CONVERSION_FACTOR FROM XXNFMA_PPS_PRODUCTS),TO_UOM=(SELECT UNIT_OF_USE FROM XXNFMA_PPS_PRODUCTS)
WHERE CONVERSION_FACTOR IS NOT NULL;


UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=1,FROM_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=(SELECT ..)
 ,TO_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS) WHERE CONVERSION_FACTOR IS NULL;

UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=2,FROM_UOM=(SELECT UNIT_OF_USE FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=
(SELECT CONVERSION_FACTOR FROM XXNFMA_PPS_PRODUCTS WHERE CONVERSION_FACTOR= 
(OUTER_PACK_CONVERSION_FACTOR*INTERMEDIATE_PACK_CONV_FACTOR) OR 
(OUTER_SUBPACK_CF/UNIT_OF_USE_CONVERSION_FACTOR)),TO_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS)
WHERE CONVERSION_FACTOR IS NULL;


UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=3,FROM_UOM=(SELECT SUBPACK_UOM FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=
(SELECT CONVERSION_FACTOR FROM XXNFMA_PPS_PRODUCTS WHERE CONVERSION_FACTOR= 
(OUTER_SUBPACK_CF/OUTER_PACK_UNIT_OF_USE_QTY) OR (INTER_SUBPACK_CF/INTERMEDIATE_PACK_CONV_FACTOR)), 
TO_UOM=(SELECT UNIT_OF_USE FROM XXNFMA_PPS_PRODUCTS)
WHERE CONVERSION_FACTOR IS NULL;










/*CASE1           
----------------
PACKAGE_Level  from_uom   conversion_factor    to_uom 
      1         outer        1                   outer  
      2         uou         outer_uou            outer
      3         sub        uou_sub               uou */


else

UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=1,FROM_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=1,
TO_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS) WHERE CONVERSION_FACTOR IS NOT NULL;;

UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=2,FROM_UOM=(SELECT INTERMEDIATE_PACK_UOM FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=(SELECT 
OUTER_PACK_CONVERSION_FACTOR FROM XXNFMA_PPS_PRODUCTS),TO_UOM=(SELECT OUTER_PACK_UOM FROM XXNFMA_PPS_PRODUCTS) 
WHERE CONVERSION_FACTOR IS NOT NULL;;

UPDATE XXNFMA_PPS_PACKAGING 
SET PACKAGE_LEVEL=3,FROM_UOM=(SELECT UNIT_OF_USE FROM XXNFMA_PPS_PRODUCTS),CONVERSION_FACTOR=(SELECT 
INTERMEDIATE_PACK_CONV_FACTOR FROM XXNFMA_PPS_PRODUCTS),TO_UOM=(SELECT INTERMEDIATE_PACK_UOM FROM XXNFMA_PPS_PRODUCTS)
WHERE CONVERSION_FACTOR IS NOT NULL;;



/*CASE 2
----------------
Level from_uom     conversion_factor  to_uom
1     outer              1             outer  
2     inter          outer_inter       outer
3      uou            inter_uou        inter */


end if;

   end loop;

end ;


/

commit;

exit;

Re: pl/sql program for table updation [message #303556 is a reply to message #303554] Fri, 29 February 2008 14:28 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Post your Oracle version with at least 3 decimals.

Regards
Michel
Previous Topic: Import CSV without SQL*LOADER
Next Topic: eliminate duplicates in a table
Goto Forum:
  


Current Time: Tue Dec 06 14:11:39 CST 2016

Total time taken to generate the page: 0.09863 seconds