SELECT APE.DAT_INSERT_DATE as "Payment Entry Date", Case when APE.TXT_INTERMEDIARY_CD is null or APE.TXT_PAYER_CUSTOMER_ID <> APE.TXT_INTERMEDIARY_CD then (Select TXT_CUSTOMER_NAME from GENMST_CUSTOMER where TXT_CUSTOMER_CD = APE.TXT_PAYER_CUSTOMER_ID) else(Select TXT_INTERMEDIARY_NAME from GENMST_INTERMEDIARY where TXT_INTERMEDIARY_CD = APE.TXT_PAYER_CUSTOMER_ID) End as "Payer Name" , APE.TXT_PAYER_CUSTOMER_ID as "CustomerID/Payer ID", (Select TXT_CUSTOMER_NAME from GENMST_CUSTOMER where TXT_CUSTOMER_CD = APE.TXT_PAYER_CUSTOMER_ID) as "Customer Name",APE.TXT_INTERMEDIARY_CD AS "Intermediary ID", GI.txt_intermediary_name as "Intermediary Name", GPI.TXT_DEAL_ID as "Deal ID", APE.Num_instrument_id as "Payment ID",APE.txt_instrument_no as "Payment No", APE.txt_alt_instrument_no as "Alternate Payment No", APE.TXT_DRAWEE_BANK_NAME as "Bank Name",APE.TXT_DRAWEE_BANK_BRANCH as "Branch Name", APE.dat_instrument_date as "Payment Date",APE.num_instrument_amt as "Payment Amount", APE.num_balance_amt as "Balance Amount", decode(APE.TXT_PAYMENT_MODE_CD,'C','Cheque','S','Cash','DA','DA','D','DD') as "Payment Mode", decode(APE.NUM_INSTRUMENT_TYPE_CD,0,'Not Valid',1,'Local',2,'Upcountry Cheque',3,'High Value Cheque',4, 'Local ICICI Bank Cheques',5,'Foreign Cheques',6,'UCC ICICI cheques') as "Payment Type", GOF.TXT_OFFICE as "Office Name",AF.TXT_FINANCIER_NAME as "House Bank", case when APE.TXT_PAYER_CUSTOMER_ID=APE.txt_intermediary_cd then 'Intermediary' Else 'Customer' end as "Payer(Customer/Intermediary)", APE.DAT_ACCEPTANCE_DATE as "Acceptance Date", DCT.TXT_PRIMARY_VERTICAL_NAME as "Primary Vertical", DCT.TXT_PRIMARY_MO_NAME as "Primary RM ", DCT.TXT_SECONDARY_VERTICAL_NAME as "Secondary Vertical", DCT.TXT_SECONDARY_MO_NAME as "Secondary RM", DCT.TXT_TERTIARY_VERTICAL_NAME as "Tertiary Vertical", DCT.TXT_TERTIARY_MO_NAME as "Tertiary RM", AMP.NUM_PROPOSAL_NO as "Proposal No" FROM ACC_PAYMENT_ENTRY APE,ACC_MAP_INSTRPROPOSAL AMP,GENMST_INTERMEDIARY GI, ACCMST_FINANCIER AF,GENMST_office GOF, GEN_PROP_INFORMATION_TAB GPI,DISTRIBUTION_CHANNEL_TAB DCT WHERE APE.NUM_INSTRUMENT_ID=AMP.NUM_INSTRUMENT_ID(+) and APE.TXT_INTERMEDIARY_CD=GI.TXT_INTERMEDIARY_CD(+) and nvl(APE.NUM_HOUSE_BANK_BRANCH_CD,1001)=AF.NUM_FINANCIER_CD and upper(APE.TXT_STATUS) = 'CANCELLED' and GPI.NUM_REFERENCE_NUMBER(+) = AMP.NUM_PROPOSAL_NO and APE.NUM_OFFICE_CD = GOF.NUM_OFFICE_CD(+) and GPI.NUM_REFERENCE_NUMBER=DCT.NUM_REFERENCE_NUMBER(+) and GPI.DAT_REFERENCE_DATE= DCT.DAT_REFERENCE_DATE(+) and GOF.NUM_OFFICE_CD in (select distinct NUM_OFFICE_CD from genmst_office where NUM_OFFICE_CD=2) ORDER BY APE.NUM_INSTRUMENT_ID DESC
% TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view
Latest Answer : %TYPE is used to declare a variable with the same type as that of a database table column.%ROWTYPE is used to declare a record as same type found in database table.These two provides data independence and allows you to adopt database changes due to new ...
% ROWTYPE is to be used whenever query returns a entire row of a table or view. TYPE rec RECORD is to be used whenever query returns columns of differenttable or views
Latest Answer : %ROWTYPE is used when you need to work with complete record.TYPE RECORD is used to create your own data type with specificed number of values to hold.Suppose If a table has 20 columns and you need to work with only seven columns . If I use %ROWTYPE, ...
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
Latest Answer : Cursor is an small memory allocation or workstation where operation or processing is done on the data.Types of attribute of cursor are-:1)ROWCOUNT2)ISOPEN3)FOUND4)NOTFOUND ...
There are two types of cursors, Implicit Cursor and Explicit Cursor.PL/SQL uses Implicit Cursors for queries.User defined cursors are called Explicit Cursors. They can be declared and
Latest Answer : Cursors are of two types1. Implicit Cursors: - Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed infomation. The most recently used work are can be accessed using SQL%. In implicit cursors ...
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
Latest Answer : There are two ways of processing a cursor output. These mainly depend on the type of cursor used. Cursors can be1) Static : Declared in declarations section with a definate command2) Dynamic : Usually passed a string, which ...
%ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows fetched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True
Latest Answer : %ISOPEN - To check whether cursor is open or not.Returns true if cursor or cursor variable is open or not otherwise false.%FOUND - To check wheteher cursor is found or not. Returns true if fetch returns a row otherwise false.%NOTFOUND - To check whether ...
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closeswhen all the records have been processed.
Latest Answer : Cursor for loop implicitly declares a loop index as %rowtype. It uses result of query to determine dynamically no of times the loop is to be repeated. It performs open, fectch , close operations implicitly. ...
Latest Answer : Commit in this context will not do anything except the commiting the changes into database, done using DML statements. However, if the cursor is created with FOR UPDATE clause, it will raise runtime exception as commit, in that case, would also ...
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers
Latest Answer : When referencing the current row from an explicit cursor, use the WHERE CURRENT OF clause. This allows you to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the ROWID. You must include the FOR ...
Querying Data with Oracle XQuery Starting with Oracle Database 10g Release 2 you can take advantage of a full featured native XQuery engine integrated with the database With Oracle XQuery you can accomplish various tasks involved in developing PHP Oracle XML applications operating on any kind of dat
Using Oracle XML DB Repository Another variation on accessing and manipulating XML content stored in Oracle database is provided by Oracle XML DB repository which is an essential component of Oracle XML DB mosgoogle NOTE Oracle XML DB repository also known as XML repository is a hierarchically organ
Using Oracle Database for Storing Modifying and Retrieving XML Data With Oracle XML DB you have various XML storage and XML processing options allowing you to achieve the required level of performance and scalability One of the most interesting things about Oracle XML DB is that it allows you to per
Processing XML in PHP Oracle Applications As mentioned there are two alternatives when it comes to performing XML processing in your PHP Oracle application You can perform any required XML processing using either PHP s XML extensions or PEAR XML packages or Oracle s XML features mosgoogle In the fol
PHP Oracle Web Development Data processing Security Caching XML Web Services and Ajax The book is written by Yuli Vaseliev a well known author of different web development and programming books PHP Oracle Web Development Data processing Security Caching XML Web Services and Ajax is a good starting b
ODP NET Developer’ s Guide by Jagadish Chatarji Pulakhandam Sunitha Paruchuri A practical guide for developers working with the Oracle Data Provider for NET and the Oracle Developer Tools for Visual Studio 2005 Application development with ODP NET Dealing with XML DB using ODP NET Oracle
PHP Oracle Web Development Data processing Security Caching XML Web Services and Ajax The book is written by Yuli Vaseliev a well known author of different web development and programming books The author is also an expert in open source technologies and SOA Service Oriented Architecture But besides
This introductory tutorial to PL SQL will help you to understand the basic concepts of PL SQL Please review the following tutorials and practice the sample SQL Statements on your local Oracle Database Please note that you must learn these basic things before we actually start getting in to Advanced
Working with XML in Oracle Introduction to XML Extensive markup language is the language which presents data in a human readable form of text The data can be anything from a purchase order or a stock quote or weather radar or a flight schedule it can be represented using XML XML is very similar to H
Oracle Net Services provides enterprise-wide connectivity solutions in distributed, heterogeneous computing environments. Oracle Net Services eases the complexities of network configuration and management, maximizes performance, and improves network diagnostic capabilities.
Oracle Net, a component
1 
0