GL Balance Query in Oracle Apps

GL Balance Query in Oracle Apps

SELECT   gb.period_name "Period Name", gb.period_year "Period Year",gb.period_Num "Period Num",
         gb.set_of_books_id "SOB ID",
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4 "Account",
         gb.currency_code "Currency", SUM (gb.period_net_dr) "Period Net Dr",
         SUM (gb.period_net_cr) "Period Net Cr",
         SUM ((gb.period_net_dr - gb.period_net_cr)) "PTD"
    FROM gl_balances gb, gl_code_combinations gcc
   WHERE gb.set_of_books_id = 10
--     AND gb.period_name = 'FEB-10-11'
     AND gb.currency_code = 'INR'
     AND gcc.segment4 = '13141'
     AND gb.code_combination_id = gcc.code_combination_id
GROUP BY gb.period_name,
         gb.period_year,
         gb.PERIOD_NUM,
         gb.set_of_books_id,
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4,
         gb.currency_code
Order by gb.PERIOD_YEAR, gb.PERIOD_NUM

To get Ap Invoice Status in Oracle Apps

To get Ap Invoice Status in Oracle Apps

AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id )

India Local Payables Tax type

Query to find out the localization Tax Type for Purchasing Module


SELECT typ.lookup_type tax_lookup_type, typ.meaning tax_lookup_type_meaning,
       typ.description tax_lookup_type_desc, val.lookup_code tax_type,
       val.meaning tax_type_meaning, val.description tax_type_desc,
       val.enabled_flag, val.start_date_active, val.end_date_active
  FROM fnd_lookup_types_vl typ, fnd_lookup_values_vl val
 WHERE typ.lookup_type = val.lookup_type
   AND val.lookup_type = 'JAI_TAX_TYPE'
   AND val.end_date_active IS NULL
   AND val.enabled_flag = 'Y'

Item Classification Query in Oracle Apps

SELECT   *
    FROM (SELECT 'q1' query1, msi.organization_id, msi.inventory_item_id,
                 msi.segment1 item_code, msi.description item_desc,
                 tmpl.template_name, tmpl.description, tmpl.regime_code,
                 att.attribute_code, att.attribute_value
            FROM jai_rgm_tmpl_itm_regns tmp_itm,
                 jai_rgm_tmpl_org_regns tmp_org,
                 jai_rgm_itm_templates tmpl,
                 jai_rgm_itm_tmpl_attrs att,
                 mtl_system_items_b msi
           WHERE tmp_itm.templ_org_regns_id = tmp_org.templ_org_regns_id
             AND tmpl.template_id = tmp_org.template_id
             AND tmpl.template_id = att.template_id
             AND msi.inventory_item_id = tmp_itm.inventory_item_id
             AND msi.organization_id = tmp_org.organization_id
             AND tmp_org.organization_id = :P_ORGN_ID
            AND tmp_itm.inventory_item_id = :P_ITEM_ID
          UNION ALL
          SELECT 'q2' query2, msi.organization_id, msi.inventory_item_id,
                 msi.segment1 item_code, msi.description item_desc,
                 'tmpl' template_name, NULL description, rgm.regime_code,
                 tmpl.attribute_code, tmpl.attribute_value
            FROM jai_rgm_itm_regns rgm,
                 jai_rgm_itm_tmpl_attrs tmpl,
                 mtl_system_items_b msi
           WHERE rgm.rgm_item_regns_id = tmpl.rgm_item_regns_id
             AND rgm.organization_id = :P_ORGN_ID
             --AND rgm.inventory_item_id =  :P_ITEM_ID
             AND msi.inventory_item_id = rgm.inventory_item_id
             AND rgm.organization_id = msi.organization_id)
ORDER BY inventory_item_id

How to create a table inside a stored procedure

Create Table Script Inside the Procedure in Oracle 


Sample Script 1 : 


CREATE OR REPLACE PROCEDURE prc_create_table
AS
v_ddl 
VARCHAR2 (100); 
BEGIN
v_ddl := 'CREATE TABLE temp_test(a number, b varchar2(10))'; 
EXECUTE IMMEDIATE v_ddl; 
END


for Test Case...........


Begin
    prc_create_table ();
End;


Sample Script 2 :  
  
Sample 2 :

create or replace procedure create_table as
begin
       execute immediate 'create table mytable (id integer )';
end ;


for Test Case........... 




Begin
create_table ();
End;

When we are running Concurrent Programs  Executable Type is PL/Sql Procedure with Date Parameter using FND_STANDARD_DATE as a Value set , That time might be we will get the following error.

ORA-01861: literal does not match format string

To avoid this error , Convert the date format as per the below format.


to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS')


Note: Define Datatype of Parameter is Varchar2.

Amount in Words Conversion in Oracle

Convert Amount into Words in Oracle 

CREATE OR REPLACE FUNCTION f_amount_to_words (p_amt IN NUMBER)

   RETURN VARCHAR2
IS
   m_main_amt_text     VARCHAR2 (2000);
   m_top_amt_text      VARCHAR2 (2000);
   m_bottom_amt_text   VARCHAR2 (2000);
   m_decimal_text      VARCHAR2 (2000);
   m_top               NUMBER (20, 5);
   m_main_amt          NUMBER (20, 5);
   m_top_amt           NUMBER (20, 5);
   m_bottom_amt        NUMBER (20, 5);
   m_decimal           NUMBER (20, 5);
   m_amt               NUMBER (20, 5);
   m_text              VARCHAR2 (2000);
BEGIN
   m_main_amt := NULL;
   m_top_amt_text := NULL;
   m_bottom_amt_text := NULL;
   m_decimal_text := NULL;
   -- To get paise part
   m_decimal := p_amt - TRUNC (p_amt);


   IF m_decimal > 0
   THEN
      m_decimal := m_decimal * 100;
   END IF;


   m_amt := TRUNC (p_amt);
   m_top := TRUNC (m_amt / 100000);
   m_main_amt := TRUNC (m_top / 100);
   m_top_amt := m_top - m_main_amt * 100;
   m_bottom_amt := m_amt - (m_top * 100000);


   IF m_main_amt > 0
   THEN
      m_main_amt_text := TO_CHAR (TO_DATE (m_main_amt, 'J'), 'JSP');


      IF m_main_amt = 1
      THEN
         m_main_amt_text := m_main_amt_text || ' CRORE ';
      ELSE
         m_main_amt_text := m_main_amt_text || ' CRORES ';
      END IF;
   END IF;


   IF m_top_amt > 0
   THEN
      m_top_amt_text := TO_CHAR (TO_DATE (m_top_amt, 'J'), 'JSP');


      IF m_top_amt = 1
      THEN
         m_top_amt_text := m_top_amt_text || ' LAKH ';
      ELSE
         m_top_amt_text := m_top_amt_text || ' LAKHS ';
      END IF;
   END IF;


   IF m_bottom_amt > 0
   THEN
      m_bottom_amt_text := TO_CHAR (TO_DATE (m_bottom_amt, 'J'), 'JSP');
   END IF;


   IF m_decimal > 0
   THEN
      IF NVL (m_bottom_amt, 0) + NVL (m_top_amt, 0) > 0
      THEN
         m_decimal_text :=
             ' AND ' || TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP')
             || ' Paise ';
      ELSE
         m_decimal_text :=
                        TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP')
                        || ' Paise ';
      END IF;
   END IF;


   m_text :=
      INITCAP (   m_main_amt_text
             || m_top_amt_text
             || m_bottom_amt_text
             || ' Rupees'
             || m_decimal_text
             || ' ONLY'
            );
   m_text := UPPER (SUBSTR (m_text, 1, 1)) || SUBSTR (m_text, 2);
   m_text := ' ' || m_text;
   RETURN (m_text);
END f_amount_to_words;


i.e
SELECT F_AMOUNT_TO_WORDS(155221.25) FROM DUAL; F_AMOUNT_TO_WORDS(155221.25)
Output is
 One Lakh Fifty-Five Thousand Two Hundred Twenty-One Rupees And Twenty-Five Paise  Only