Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 3 May 2014

To Extract Value in Oracle Report from exiting another Table

Create one place holder column of the related type value then write the select statement into the formula column.
For Example: Display the Supplier_name from Existing another table: GL_Master
Item Name: CP_SUPPLIER_NAME
Item Type: Place holder
Data type: Char 100

Item Name: CF_CALC
Item Type: Formula Column
Data type: Number – 10
PL/SQL Formula:
function CF_CALCFormula return Number is
begin
SELECT UPPER(M_DESC)
into   :CP_SUPPLIER_NAME
FROM GL_MASTER
WHERE GL_MASTER.VEN_NUMBER = :SUPP_CODE;
RETURN 0;
end;
To Extract Check flag and List item value in Oracle Report
Create a place holder column to store the value and then write the code into formula column.
IF :FMCG_FLAG = 1 THEN
      :CP_FMCG := 'X';
else
      :CP_FMCG := ' ';
END IF;
IF :NON_FOOD = 1 THEN
      :CP_NON_FOOD := 'X';
else
:CP_NON_FOOD := ' ';
END IF;

IF :REBATE_DED_MONTHLY = 1 THEN
      :CP_REBATE_DEDUCTION := 'MONTHLY';
ELSE IF :REBATE_DED_MONTHLY = 2 THEN
                        :CP_REBATE_DEDUCTION := 'QUATERLY';
ELSE IF :REBATE_DED_MONTHLY = 3 THEN                 
:CP_REBATE_DEDUCTION := 'YEARLY';
END IF;
END IF;

END IF;

1 comments: