Skip to main content

To Extract Value in Oracle Report from exiting another Table

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;

Comments

Post a Comment

Popular posts from this blog

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.  In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB. Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import). Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g). You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.  $sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> Select property_...
Post a Comment
Read more

Import Excel Data into Oracle using Oracle form Button

Import Excel Data into Oracle using Oracle form Button 1. Create a button on the form with the name "IMPORT_EXCEL" and write a pl/sql on "WHEN BUTTON PRESSED" trigger. BEGIN IF :System.Cursor_Block<>'GL_DAILY_COMPOUND_HEADER' THEN   Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE'); END IF; IF :System.Mode = 'NORMAL' AND :System.Record_Status IN ('NEW','INSERT') THEN   IMPORT_EXCEL_PROC;   ---Form procedure ELSE  MESSAGE('Import allowed only for new entry!!!');  MESSAGE('Import allowed only for new entry!!!'); END IF; END; 2. Now write the procedure "IMPORT_EXCEL_PROC" into the program unit. Don't forget to create required table and folder for procedure IMPORT_EXCEL_PROC PROCEDURE IMPORT_EXCEL_PROC IS application    OLE2.Obj_Type; workbooks      OLE2.Obj_Type; workbook       OLE2.Obj_Type; worksheets      OLE2.Obj_Type; worksheet       OLE2.Obj_Type; ce...
Post a Comment
Read more

How to Monitor RMAN Backup through SQL Query

To Monitor RMAN you can use OEM or any other tools such as "Quest Backup Report for Oracle". You can also used following Views in oracle 9i to check the RMAN backup status: v$rman_configuration v$backup_set v$backup_piece v$backup_spfile v$session_longops Script to check RMAN Configuration: SELECT  name "PARAMETERS", value "RMAN CONFIGURATION" FROM  v$rman_configuration ORDER BY name; PARAMETERS RMAN CONFIGURATION BACKUP OPTIMIZATION ON CHANNEL DEVICE TYPE DISK FORMAT   'H:\ORABACK\%U' CONTROLFILE AUTOBACKUP ON CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'H:\ORABACK\%F' DEFAULT DEVICE TYPE TO DISK RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS Script to List RMAN Backup Piece: SELECT bs.recid, DECODE(   bp.status, 'A', 'Available', 'D', 'De...
Post a Comment
Read more