Thursday 28 July 2011

purchase Requisition Query

SELECT prha.segment1 "REQ NO",
                prha.creation_date "REQ DATE",
                prla.quantity "REQ QTY",
                pha.segment1 "PO NO",
                pha.creation_date "PO DATE",
                sum(pla.quantity) "PO QTY",
                pla.unit_price "PRICE",
                rsh.receipt_num ,
                rt.transaction_date "RECEIPT DATE",
                rt.quantity "RECEIPT QTY",
                rt.po_unit_price "PRICE",
                aia.invoice_num,
                aia.invoice_date,
                apc.check_date,
                apc.check_number
FROM    po_requisition_headers_all prha,
                po_requisition_lines_all prla,
                po_req_distributions_all prda,
                po_headers_all pha,
                po_lines_all pla,
                po_distributions_all pda,
                rcv_transactions rt,
                rcv_shipment_lines rsl,
                rcv_shipment_headers rsh,
                ap_invoices_all aia,
                ap_invoice_lines_all aila,
                ap_invoice_distributions_all aid,
                ap_invoice_payments_all aip,
                ap_payment_schedules_all apsa,
                ap_checks_all apc
WHERE  prha.requisition_header_id=prla.requisition_header_id
AND       prla.requisition_line_id=prda.requisition_line_id
AND       pda.req_distribution_id=prda.distribution_id
AND       pla.po_line_id=pda.po_line_id
AND       pha.po_header_id=pla.po_header_id
AND       pda.po_distribution_id=rt.po_distribution_id
AND       rsh.shipment_header_id=rt.shipment_header_id
AND       rt.transaction_id=aid.rcv_transaction_id
AND       aila.invoice_id=aid.invoice_id
AND       aia.invoice_id=aila.invoice_id
AND      aia.invoice_id=aip.invoice_id
AND      aia.invoice_id=apsa.invoice_id
AND      apc.check_id=aip.check_id
AND      prha.segment1='14663'
--and      pha.SEGMENT1='7162'


GROUP BY  prha.segment1,
                       prha.creation_date,
                       prla.quantity,
                       pha.segment1,
                       pha.creation_date,
                       pla.quantity,
                       pla.unit_price,
                       rsh.receipt_num ,
                       rt.transaction_date,
                       rt.quantity,
                       rt.po_unit_price,
                       aia.invoice_num,
                       aia.invoice_date,
                       apc.check_date,
                       apc.check_number

Thursday 21 July 2011

How to Identify the On-Hand Quantity

select  ITEM_ID,
        sum(TARGET_QTY) quantity  
FROM(SELECT moqd.inventory_item_id item_id,           
     SUM (primary_transaction_quantity) target_qty
     FROM   mtl_onhand_quantities_detail moqd
     WHERE  moqd.organization_id = 204
     AND    moqd.owning_tp_type = DECODE (2, 2, 2, moqd.owning_tp_type)
     GROUP BY  moqd.inventory_item_id
  
UNION
    
SELECT mmt.inventory_item_id item_id,
       SUM (primary_quantity) target_qty        
FROM   mtl_material_transactions mmt,
       mtl_txn_source_types mtst,
       mtl_parameters mp
       WHERE   mmt.organization_id = 204 AND mp.organization_id = 204
                                         AND transaction_date >= (sysdate + 1)
       AND NVL (mmt.owning_tp_type, 2) =
                 DECODE (2, 2, 2, NVL (mmt.owning_tp_type, 2))
       AND mmt.transaction_source_type_id =
                    mtst.transaction_source_type_id
       AND NVL (mmt.logical_transaction, 2) <> 1
       GROUP BY  mmt.inventory_item_id)
       GROUP BY   ITEM_ID

Wednesday 20 July 2011

PRAGMA(Pragma Autonomous_transaction)

PRAGMASignifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

1) Autonomous Transaction

Autonomous Transactions is the child transaction, which are Independent of Parent transactions. In Our Example, p1 is child transaction, which is used in the Parent transaction. 


Example
: -
CREATE or REPLACE Procedure p1 IS
Pragma Autonomous_transaction;
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;
In the Declaration section, you will declare this Transaction as the Autonomous Transaction.
DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1;
ROLLBACK;
END;
NOW Table has (1111,’PHANI’) Record. COMMIT in the PROCEDURE P1 have not commit the Outside (p1) DML operations. It will just commit p1 transactions.
The ROLLBACK will not rollback PHANI record, it will just rollback the JACK record.
CREATE or REPLACE Procedure p1 IS
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;

If I remove the Pragma Autonomous_transaction From the declaration sectionthen this transaction will become the normal transaction. Now if you try to use the same parent transaction as given below.
>> delete from TEST_T;
DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1; -- This transaction has ended with the COMMIT;
ROLLBACK;
END;

After executing the above transaction, you can see BOTH records got Inserted (PHANI and JACK records). Here COMMIT in P1 will commit both transactions (PHANI and JACK Records Insert) And then Rollback. Since, there are no transactions happening between COMMIT and ROLLBACK. Our ROLLBACK is not doing any ROLLBACK.
Note: - IF COMMIT is not given in P1 then, the ROLLBACK will do the ROLLBACK both the INSERT transaction (PHANI Record which is in p1 procedure and JACK Record).
 
2) Pragma Restrict_references
It gives the Purity Level of the Function in the package.
CREATE OR REPLACE PACKAGE PKG12 AS FUNCTION F1 RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (F1, WNDS,
RNDS,
WNPS,
RNPS);
END PKG12;
CREATE OR REPLACE PACKAGE BODY PKG12 AS
FUNCTION F1 RETURN NUMBER IS
X NUMBER;
BEGIN
SELECT EMPNO INTO X FROM SCOTT.EMP
WHERE ENAME LIKE ‘SCOTT’;
DBMS_OUTPUT.PUT_LINE (X);
RETURN (X);
END F1;
END PKG12;
You will get the Violate It’s Associated Pragma Error. This in Purity Level, we said
It cannot read from the database. RNDS (In Our Function F1, we have SELECT STATEMENT which is reading the data from the database).
 
3) Pragma SERIALLY_REUSABLE
In my 5 Years of Experience in the Oracle Applications, I have never found the requirement to use this feature :). But, I found this feature is used in some standard Oracle Packages. We may use this feature for improving the performance or to meet certain requirements.
This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.
You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.
The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.

Examples
WITH PRAGMA SERIALLY_REUSABLE
The following example creates a serially reusable package:
CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line('Num: ' || pkg1.num);
END;
END pkg1;
/
begin
pkg1.init_pkg_state(10);
pkg1.PRINT_PKG_STATE;
end;

Num: 10

begin
pkg1.PRINT_PKG_STATE;
end;

Num: 0

Note: - The first block is changing the value of the variable (num) to 10 and if I check the value in same block then it is showing the changed value that is 10. But, if I try to check the value of the (num) variable then it should the default value given to it (i.e.) “0”
WITHOUT PRAGMA SERIALLY_REUSABLE
CREATE OR REPLACE PACKAGE pkg1 IS
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
CREATE PACKAGE BODY pkg1 IS
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line('Num: ' || pkg1.num);
END;
END pkg1;
begin
pkg1.init_pkg_state(10);
pkg1.PRINT_PKG_STATE;
end;
>>Num: 10
begin
pkg1.PRINT_PKG_STATE;
end;
>>Num: 10
Note: - Now, you may noticed the difference. The second block is giving us the changed value.
DROP PACKAGE pkg1;

Concurrent Request Status

SELECT   fcr.phase_code,
         DECODE (fcr.phase_code,'C', 'Completed', 'P', 'Pending', 'R', 'Running',
         'I',  'Inactive', fcr.phase_code) phase,
         fcr.status_code,
         DECODE (fcr.status_code,'A', 'Waiting',
                                 'B', 'Resuming',
                                 'C', 'Normal',
                                 'D', 'Cancelled',
                                 'E', 'Error',
                                 'F', 'Scheduled',
                                 'G', 'Warning',
                                 'H', 'On Hold',
                                 'I', 'Normal',
                                 'M', 'No Manager',
                                 'Q', 'Standby',
                                 'R', 'Normal',
                                 'S', 'Suspended',
                                 'T', 'Terminating',
                                 'U', 'Disabled',
                                 'W', 'Paused',
                                 'X', 'Terminated',
                                 'Z', 'Waiting',
                                 fcr.status_code) status,
         request_date,
         fat.description, frt.responsibility_name, fu.user_name,
         fu.description, fcpt.user_concurrent_program_name, fcpt.description,
         fcr.request_id, fcr.request_date, fcr.priority, fcr.requested_start_date, fcr.hold_flag,
         fcr.number_of_arguments, fcr.number_of_copies, fcr.save_output_flag,
         fcr.printer, fcr.parent_request_id, fcr.description,
         fcr.resubmit_time, fcr.resubmit_end_date, fcr.argument_text,
         fcr.argument1, fcr.argument2, fcr.argument3, fcr.argument4,
         fcr.argument5, fcr.argument6, fcr.argument7, fcr.argument8,
         fcr.argument9 org, fcr.argument10, fcr.argument11, fcr.argument12,
         fcr.argument13, fcr.argument14, fcr.argument15, fcr.argument16,
         fcr.argument17, fcr.argument18, fcr.argument19, fcr.argument20,
         fcr.argument21, fcr.argument22, fcr.argument23, fcr.argument24,
         fcr.argument25, fcr.output_file_type, fcr.cancel_or_hold,
         fcr.completion_code, fcr.ofile_size, fcr.lfile_size,
         fcr.logfile_name, fcr.logfile_node_name, fcr.outfile_name,
         fcr.outfile_node_name
    FROM fnd_concurrent_requests fcr,
         fnd_user fu,
         fnd_responsibility_tl frt,
         fnd_application_tl fat,
         fnd_concurrent_programs_tl fcpt
   WHERE (fu.user_id = fcr.requested_by)
     AND (fat.application_id = fcr.program_application_id)
     AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)
     AND (fcr.responsibility_id = frt.responsibility_id)
     AND fat.LANGUAGE = 'US'
     AND frt.LANGUAGE = 'US'
     AND fcpt.LANGUAGE = 'US'
     AND fcr.request_id = NVL (:request_id, fcr.request_id)
ORDER BY fcr.request_date DESC

Query to Extract Employee Contact Information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type

Script to Print the Oracle Apps Version Number

SELECT substr(a.application_short_name, 1, 5) code,
       substr(t.application_name, 1, 50) application_name,
       p.product_version version
  FROM fnd_application a,
       fnd_application_tl t,
       fnd_product_installations p
 WHERE a.application_id = p.application_id
   AND a.application_id = t.application_id
   AND t.language = USERENV('LANG')

Query to Print Calendar for the year

SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH,
"Sun", "Mon","Tue", "Wed", "Thu", "Fri", "Sat"

FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,

MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Sun",

MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Mon",

MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Tue",

MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Wed",

MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Thu",

MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Fri",

MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Sat"

FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt

FROM all_objects

WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y'))
GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))

ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

Link between purchase Order and Requisition

SELECT prh.segment1 req_number,
       prh.authorization_status,
       prl.line_num req_line_num,
       prl.item_description req_item_description,
       prl.unit_price req_unit_price,
       prl.quantity req_quantity,
       pd.req_header_reference_num,
       pd.req_line_reference_num,
       pl.line_num,
       pl.item_description,
       pl.quantity,
       pl.amount,
       ph.segment1 po_number,
       prd.distribution_id,
       pd.req_distribution_id
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all   prl,
       po_req_distributions_all   prd,
       po_distributions_all       pd,
       po_line_locations_all      pll,
       po_lines_all               pl,
       po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)

Database and Application Information

1) Get Product Version

SELECT produc ,version, status
FROM   product_component_version

The other way to get this information is by using following query


select * from v$version;


2)Check if the application is setup for Multi-Org

SELECT multi_org_flag
FROM   fnd_product_groups






Query for Customer Address Details

SELECT DISTINCT hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
           FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
          WHERE hp.party_id = hps.party_id(+)
            AND hp.party_id = hca.party_id(+)
            AND hcasa1.party_site_id(+) = hps.party_site_id
            AND hcasa2.party_site_id(+) = hps.party_site_id
            AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
            AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
            AND hcsua1.site_use_code(+) = 'bill_to'
            AND hcsua2.site_use_code(+) = 'ship_to'
            AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
            AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
            AND hps.location_id = hl.location_id
            AND hl.country = ftt.territory_code
            AND ftt.LANGUAGE = USERENV ('lang')
       ORDER BY customer_number;           

Query for Supplier Bank Details

SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,
                pvs.vendor_site_code vendor_site_code,
                aba.bank_account_name bank_account_name,
                aba.bank_account_num bank_account_num,
                aba.currency_code currency_code,
                abau.primary_flag primary_flag, abb.bank_name bank_name,
                abb.bank_number bank_number,
                abb.bank_branch_name bank_branch_name, abb.bank_num bank_num
           FROM ap_bank_account_uses_all abau,
                ap_bank_accounts_all aba,
                ap_bank_branches abb,
                po_vendors pv,
                po_vendor_sites_all pvs
          WHERE abau.external_bank_account_id = aba.bank_account_id
            AND aba.bank_branch_id = abb.bank_branch_id
            AND abau.vendor_id = pv.vendor_id
            AND abau.vendor_id = pvs.vendor_id(+)
            AND abau.vendor_site_id = pvs.vendor_site_id(+)

Query for Customer Receipt Details

SELECT   acra.cash_receipt_id,
         DECODE (acra.TYPE,
                 'cash', 'cash receipt receipt',
                 'misc', 'miscellaneous',
                 acra.TYPE
                ) receipt_type,
         acra.currency_code, acra.doc_sequence_value receipt_number,
         acra.receipt_number reference_number,
         TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
         acra.misc_payment_source, hca.account_number customer_no,
         NVL (acra.amount, 0) entered_amount,
         NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
         arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
         abb.bank_name, abb.bank_branch_name, acra.comments description
    FROM ar_cash_receipts_all acra,
         ar_receipt_methods arm,
         ap_bank_accounts_all abaa,
         ap_bank_branches abb,
         hz_cust_accounts hca,
         hz_parties hp
   WHERE acra.pay_from_customer = hca.cust_account_id(+)
     AND acra.org_id = abaa.org_id(+)
     AND hca.party_id = hp.party_id(+)
     AND acra.receipt_method_id = arm.receipt_method_id
     AND acra.remittance_bank_account_id = abaa.bank_account_id
     AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;

Value Sets in Oracle Apps

Value Set is a collection of values. It validates the values entered by the user for a flex-field, report parameters or a concurrent.
The navigation path: Application :- Validation :- Sets
Value Set is associated with the following validations which holds the key to the value sets.
Types of Validations:
  1. None :- Validation is minimal.
  1. Independent :- The data input is based on previously defined list of values.
  1. Dependent :- The data input is checked against a defined set of values.
  1. Table :- The data input is checked against values in a table.

  1. Special :- The data input is checked against a flex-field.
  1. Pair :- The data input is checked against two flex-field specified range of values.
  1. Translatable independent :- The data input is checked against previously defined list of values.
  1. Translatable dependent :- The data input is checked against defined set of values.
Significance of $FLEX$
$FLEX$: enables to match the prior segment with either value set name or segment name.
Let v2 be the value set definition of 2nd parameter and v1 be the value set definition for the first parameter then
In the value set definition of v2 = value $FLEX$.v1
Note:
Changes You Should Never Make
You should never make these types of changes (old value set to new value set) because you will corrupt your existing key flex-field combinations data:
. Independent to Dependent
. Dependent to Independent
. None to Dependent
. Dependent to Table
. Table to Dependent
. Translatable Independent to Translatable Dependent
. Translatable Dependent to Translatable Independent
. None to Translatable Dependent
. Translatable Dependent to Table
. Table to Translatable Dependent

Monday 18 July 2011

LAG and LEAD FUNCTIONS IN SQL

LAG:

   
 SELECT empno,
         ename,
         job,
         sal,
         LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
         sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff 
 FROM   emp;
 
 
LEAD:
 
 SELECT empno,
         ename,
         job,
         sal,
         LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
         LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
 FROM   emp;  
 
  

Finding Host_Name and Host_Address and Profile Option Using Query

select utl_inaddr.get_host_name FROM dual;

select utl_inaddr.get_host_address FROM dual;


select * from icx_parameters

 SELECT PROFILE_OPTION_VALUE
      FROM FND_PROFILE_OPTION_VALUES
          WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
              FROM FND_PROFILE_OPTIONS
                  WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
                      AND LEVEL_VALUE=0

Friday 15 July 2011

Convert Amount into Words

create or replace FUNCTION   spell_money (p_number IN NUMBER)
   RETURN VARCHAR2
AS
   TYPE myarray IS TABLE OF VARCHAR2 (255);

   l_str      myarray
      := myarray (' Thousand ',
                  ' Lakh ',
                  ' Crore ',
                  ' Arab ',
                  ' Kharab ',
                  ' Shankh '
                 );
   l_num      VARCHAR2 (50)   DEFAULT TRUNC (p_number);
   l_return   VARCHAR2 (4000);
BEGIN
   IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
   THEN
      l_return :=
         TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
                  'Jsp');
   END IF;

   l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);

   FOR i IN 1 .. l_str.COUNT
   LOOP
      EXIT WHEN l_num IS NULL;

      IF (SUBSTR (l_num, LENGTH (l_num) - 1, 2) <> 0)
      THEN
         l_return :=
               TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 1, 2), 'J'),
                        'Jsp'
                       )
            || l_str (i)
            || l_return;
      END IF;

      l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 2);
   END LOOP;

   IF TO_CHAR (p_number) LIKE '%.%'
   THEN
      l_num := SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1);

      IF (LENGTH (SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1))) =
                            
                                              1
      THEN
         l_num := TO_NUMBER (TO_CHAR (l_num) || '0');
      END IF;

      IF l_num > 0
      THEN
         l_return :=
               l_return
            || ' And '
            || TO_CHAR (TO_DATE (l_num, 'J'), 'Jsp')
            || ' Paise';
      END IF;
   END IF;

   RETURN (l_return||' only');
END spell_money;

select spell_money(2000) from dual;

SPELL_MONEY(2000)
Two Thousand only

Views:Basic Concepts

Views
1.A view is a predefined query on one or more tables.
2.Retrieving information from a view is done in the same manner as retrieving from a table.
3.With some views you can also perform DML operations (delete, insert, update) on the base tables.
4.Views don’t store data, they only access rows in the base tables.
5.user_tables, user_sequences, and user_indexes are all views.
6.View Only allows a user to retrieve data.
7.view can hide the underlying base tables.
8.By writing complex queries as a view, we can hide complexity from an end user.
9.View only allows a user to access certain rows in the base tables.

Advantages of Views

• To restrict data access
• To make complex queries easy
• To provide data independence
• To present different views of the same data

Creating and Using a View

You create a view using CREATE VIEW , which has the following simplified syntax:

    CREATE [OR REPLACE][{FORCE | NOFORCE}] VIEW view_name
    [(alias_name[, alias_name...])] AS subquery
    [WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];

where

1.OR REPLACE specifies the view is to replace an existing view if present.
2.FORCE specifies the view is to be created even if the base tables don’t exist.
3.NOFORCE specifies the view is not to be created if the base tables don’t exist; NOFORCE is the default.
4.alias_name specifies the name of an alias for an expression in the subquery.
5.There must be the same number of aliases as there are expressions in the subquery.
6.subquery specifies the subquery that retrieves from the base tables.
7.If you’ve supplied aliases, you can use those aliases in the list after the SELECT clause.
8.WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted.
9.By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
10.constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
11.WITH READ ONLY specifies that rows may only read from the base tables.

There are two basic types of views:

Simple views:

*)Derives data from only one table
*)Contains no functions or groups of data
*)Can perform DML operations through the view

Complex views:
*)Derives data from many tables
*)Contains functions or groups of data
*)Does not always allow DML operations through the view

Creating a View with a CHECK OPTION Constraint

You can specify that DML operations on a view must satisfy the subquery by adding a CHECK OPTION constraint to the view.
    CREATE VIEW myView AS
       SELECT *
       FROM employee
       WHERE id  INSERT INTO myView (id) VALUES (0);
   
    1 row created.
   
    INSERT INTO myView (id) VALUES (7);
    INSERT INTO myView (id) VALUES (7)
                *
    ERROR at line 1:
    ORA-01402: view WITH CHECK OPTION where-clause violation

Creating a View with a READ ONLY Constraint

You can make a view read only by adding a READ ONLY constraint to the view.

    CREATE VIEW myView AS
      SELECT *
      FROM employee
      WITH READ ONLY CONSTRAINT my_view_read_only;
   
    View created.
   
    INSERT INTO myView (id) VALUES (1);
    INSERT INTO myView (id) VALUES (1)
                        *
    ERROR at line 1:
    ORA-01733: virtual column not allowed here

Performing an INSERT Using a View

You can only perform DML operations with simple views.Complex views don’t support DML.

    CREATE VIEW employee_view AS
      SELECT id, first_name, last_name
      FROM employee;
   
    View created.
   
    INSERT INTO employee_view (id, first_name, last_name) VALUES (
                                 13, 'New','Western');
   
    1 row created.
   
    1 row is created in the employee table.

Modifying a View

You can completely replace a view using CREATE OR REPLACE. You can alter the constraints on a view using ALTER VIEW.

    CREATE VIEW myView AS
      SELECT *
      FROM employee
      WITH READ ONLY CONSTRAINT my_view_read_only;
   
    View created.
   
    ALTER VIEW myview
    DROP CONSTRAINT my_view_read_only;
   
    View altered.

Create a complex view with group by and having clause

    CREATE VIEW myview AS
      SELECT city, AVG(salary) average_salary
      FROM employee
      GROUP BY city
      HAVING AVG(salary)>50000;

Removing a View

You can remove a view without losing data because a view is based on underlying tables in the database.

DROP VIEW myview;

Inline Views

• An inline view is a subquery with an alias (or correlation name) that you can use within a SQL statement.
• A named subquery in the FROM clause of the main query is an example of an inline view.

Materialized Views

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

   




   

Cursors in Oracle

CURSORS:

A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement. Cursor is a pointer, which works on active set, i.e. which points to only one row at a time in the context area’s ACTIVE SET. A cursor is a construct of pl/sql, used to process multiple rows using a pl/sql block.

Implicit and Explicit cursors:

Oracle automatically declares an implicit cursor every time a SQL statement is executed. The user is unaware of this and cannot control or process the information in an implicit cursor.
We define an explicit cursor for any query that returns more than one row of data. This means that the programmer has declared the cursor within the PL/SQL code block. This declaration allows the application to sequentially process each row of data as the cursor returns it.

Difference between implicit and explicit cursors:
An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.

How an Implicit cursor works? 
  1. Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.
  2. A cursor is automatically associated with every DML statement (UPDATE, DELETE, INSERT).
  3. All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
  4. An INSERT statement needs a place to receive the data that is to be inserted into the database; the implicit cursor fulfills this need.
  5. The most recently opened cursor is called the SQL cursor.
How an Explicit cursor works?

The process of working with an explicit cursor consists of the following steps:                                                                                                                                                        
  1. Declaring the cursor: This initializes the cursor into memory.
  2. Opening the cursor: The declared cursor is opened, and memory is allotted.
  3. Fetching the cursor: The declared and opened cursor can now retrieve data.
  4. Closing the cursor: The declared, opened, and fetched cursor must be closed to release the memory allocation.
 Declaring a Cursor:
  • Cursor without parameters (simplest)
                          CURSOR cursor_name
                          IS  
SELECT_statement;
  • Cursor with parameters
                          CURSOR cursor_name (parameter_list)
                          IS
                          SELECT_statement;
  • Cursor with return clause
                          CURSOR cursor_name
                          RETURN field%ROWTYPE
                          IS
SELECT_statement;

 Opening the Cursor:
When a cursor is opened the active set is determined, the rows satisfying the where clause in the select statement are added to the active set. A pointer is established and points to the first row in the active set.
                          OPEN cursor_name;
Fetching the Data:
The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.
                          FETCH cursor_name INTO <list of variables>;

 Closing the Cursor:
The final step of working with cursors is to close the cursor once you have finished using it.
                         CLOSE cursor_name;
Example:
Declare
Vno emp.empno%type;
            Vname emp.ename %type;
            Cursor emp_cursor is Select empno,ename From emp;
Begin
            Open cursor;
            For I in 1..10 loop
            Fetch emp_cursor into vno,vname;
           dbms_output.put_line(to_char(vno) ||’ ‘||vname);
            End loop;
End;

Implicit & explicit cursor attributes:

Both Implicit and Explicit cursor have 4 attributes:

Implicit Cursor:

1. %ISOPEN: The Oracle engine automatically opens and closed the SQL cursor after executing its associated select, insert, update or delete SQL statement has been processed in case of implicit cursor. Thus the SQL%ISOPEN attribute of an implicit cursor cannot be referenced outside of its SQL statement. As a result, SQL%ISOPEN always evaluates to FALSE.
2. %FOUND: Evaluates to TRUE, if an insert, update or delete affected one or more rows, or a single-row select returned one or more rows. Otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%FOUND.
3. %NOTFOUND: It is logical opposite of %FOUND. It evaluates to TRUE, if an insert , update or delete affected no rows, or otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%NOTFOUND.
4. %ROWCOUNT: Returns the numbers of rows affected by an insert , update or delete, or select into statement. The syntax for accessing this attribute is SQL%ROWCOUNT.
Explicit Cursor:
1. %ISOPEN: Return TRUE if cursor is open, FALSE otherwise.
2. %FOUND: Return TRUE if record was fetched successfully, FALSE otherwise.
3. %NOTFOUND: Return TRUE if record was not fetched successfully, FALSE otherwise.
4. %ROWCOUNT: Returns number of records processed from the cursor.
Example:

Begin
         Open emp_cursor;
         Loop
         Fetch when emp_cursor % rowcount &gt;10 or Emp_curor % not found;
         dbms_output.put_line(to_char(vno)||’ ‘|| vname);
         End loop;
         Close emp_cursor;
End;
Select For Update statement:

The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

                 CURSOR cursor_name
                 IS
                 select_statement
                 FOR UPDATE [of column_list] [NOWAIT];

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

Where Current Of statement:

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

The syntax for the Where Current Of statement is either:

                  UPDATE table_name
                  SET set_clause
                  WHERE CURRENT OF cursor_name;
        OR
                  DELETE FROM table_name
                  WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.
Few PL/SQL Cursor Exceptions:
  • Cursor_Already_Open
  • Invalid_Cursor
What is REF Cursor?

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.
To execute a multi-row query, oracle opens an unnamed work area that stores processing information. To access the information, an explicit cursor, which names the work area or, a cursor variable, which points to the work area can be used.
Whereas a cursor always refers to the same query work area,  a cursor variable can refer to a different work areas. Cursor variables are like ‘c’ pointers, which hold the memory location(address) of some object instead of the object itself.

Describe a cursor for loop

You would use a CURSOR FOR Loop when you want to fetch and process every record in a cursor. The CURSOR FOR Loop will terminate when all of the records in the cursor have been fetched.
The syntax for the CURSOR FOR Loop is:
                         FOR record_index in cursor_name
                         LOOP
                         {.statements.}
                         END LOOP;
Example:
Declare
         Cursor emp_cursor is
         Select empno,ename From emp;
         Begin
            For emp_record in emp_cursor loop
                  Dbms_output.put_line(emp_record.empno);
                  Dbms_output.put_line(emp_record.ename) 
         End loop;       
   End;