Function To Convert Amount to Words

Function To Convert Amount to Words

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 := LOWER(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;
/

Link between GL and SLA

Link between GL and SLA


GL_JE_BATCHES (je_batch_id)                                   => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                                  => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)                      => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)                   => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id)                     => XLA_EVENTS (application_id, event_id)  
XLA_EVENTS (application_id, entity_id)                        => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id) 

Link from AR to SLA to GL

Link from AR to SLA to GL
-- Table Link from AR to SLA to GL in Oracle Apps R12
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = ''-- Customer_trx_id

SELECT *
FROM HZ_CUST_ACCOUNTS_ALL
WHERE CUST_ACCOUNT_ID = ''--(BILL_TO_CUSTOMER_ID.RA_CUSTOMER_TRX_ALL)

SELECT *
FROM HZ_PARTIES
WHERE PARTY_ID = ''--(PARTY_ID.HZ_CUST_ACCOUNTS_ALL)

SELECT *
FROM XLA_TRANSACTION_ENTITIES
WHERE SOURCE_ID_INT_1 = ''--(CUSTOMER_TRX_ID.RA_CUSTOMER_TRX_ALL)

SELECT *
FROM XLA_AE_HEADERS
WHERE ENTITY_ID = '' -- Entity_Id from XLA_TRANSACTION_ENTITIES

SELECT *
FROM XLA_AE_LINES
WHERE AE_HEADER_ID = '' -- AE_HEADER_ID FROM XLA_AE_HEADERS

SELECT *
FROM GL_IMPORT_REFERENCES
WHERE GL_SL_LINK_ID = ''--GL_SL_LINK_ID FROM XLA_AE_LINES
AND GL_SL_LINK_TABLE = '' --GL_SL_LINK_TABLE FROM XLA_AE_LINES

SELECT *
FROM GL_JE_LINES
WHERE JE_HEADER_ID = ''--JE_HEADER_ID FROM GL_IMPORT_REFERENCES
AND JE_LINE_NUM = '' --LINE NUMBER FROM GL_JE_LINES

SELECT *
FROM GL_JE_HEADERS
WHERE JE_HEADER_ID = '' --JE_HEADER_ID FROM GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID = '' -- JE_BATCH_ID.GL_JE_HEADERS

Link from AP to SLA to GL

Link from AP to SLA to GL
SELECT INVOICE_ID
FROM AP_INVOICES_ALL

SELECT *
FROM XLA.XLA_TRANSACTION_ENTITIES
WHERE SOURCE_ID_INT_1 = 10000 -- INVOICE_ID.AP_INVOICES_ALL

SELECT *
FROM XLA_EVENTS
WHERE EVENT_ID = 37207 -- EVENT_ID.XLA_TRANSACTION_ENTITIES

SELECT *
FROM XLA_AE_HEADERS
WHERE EVENT_ID = 37207 -- EVENT_ID.XLA_TRANSACTION_ENTITIES

SELECT GL_SL_LINK_ID
FROM XLA_AE_LINES
WHERE AE_HEADER_ID = 28257 --AE_HEADER_ID.XLA_AE_HEADERS

SELECT *
FROM GL_IMPORT_REFERENCES
WHERE GL_SL_LINK_ID = 44986 -- GL_SL_LINK_ID.XLA_AE_LINES

SELECT *
FROM GL_JE_HEADERS
WHERE JE_HEADER_ID = 1137482 -- JE_HEADER_ID.GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_LINES
WHERE JE_HEADER_ID = 1137482 -- JE_HEADER_ID.GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID = 869749 --JE_BATCH_ID.GL_IMPORT_REFERENCES  

List of Operating Units and List of INV Organizations

List of Operating Units

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'OPERATING_UNIT'
       AND org_information2 = 'Y'
     
List of INV Organizations

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'INV'
       AND org_information2 = 'Y'

Xml Publisher Basics

Xml Publisher Basics

1) How wil u set sl.no in xml reports?
  we wil use postion attribute here
 syntax: <?position()?>
2) How wil u give pagebreaks after specific records(rows)?
 we wil use <?split-by-page-break?>
 syntax: <?if:position() mod 5=0 ?><?split-by-page-break:?><?end if?>
    ------  we should not use split-by -page-break in table.
3)  How 2 Avoid the page break inbetween the table
           Navg: In the table properties we wil select row tab
   in that we we wil select check box Allow row 2 break across the page
4)  How wil give Headers to all pages?
 by using start body and end body
 syntax: <?start-body?>
  <?end body?>
5)  Xml publisher can have Only one top level elements
6)  How 2 give count of elemnts in last page of the report?
 syntax: <?start@last-page:body?>
               count attrbute1
               <?end body?>
7)  How to appy colours alternatively?
 syntax: <?if@row:position() mod 2=0?>  <xsl:attribute name="background-color"  xdofo:ctx="incontext">silver</xsl:attribute><?end if?>
8)   How wil  u set Heading per each page?
           Navg: In the table properties we wil select row tab ->
  in that we we wil select check box repeat header for each page
9) Navigation for landscape
           Navg:  pagelayout ->page setup ->landscape(to the text----only 4 tat page)
10) How 2 apply colour in Background
           Navg: borders and shading -> shading -> fill
11) How 2 remove Boarders
           Navg: borders and shading -> borders -> (remove all sides except down)
12) Check box (check box) <?ename>kasi?>
14) Drop dowlist  select drop dronlist give  list of elements then go for add help text
15. If we want to hilight one cell then we wil write syntax like
  <?if:debit>1000?><xsl:
  attribute xdofo:ctx=
  "block" name="backgroundcolor">
  red</xsl:
  attribute><?end if?>
16).we wil  do running totals like this
 <?xdoxslt:set_variable($_
 XDOCTX, ’RTotalVar’, xdoxslt:
 get_variable($_XDOCTX,
 ’RTotalVar’) + INVAMT)?>
17) if i want to calclulate totals --------  <?add-page-total:variablename;'column_name'?>
       to display those columns               <?show-page-total:ct;’$#,##0.00’;’ ($#,##0.00)’?>
18.we can do sorting like this
syn:-<?sort:element name?>
ex:--<?sort:vendor_name?><?sort:vendor_num?>
18. set autotrace traceonly -to trace the query without data
19.select to_char(to_date('10/25/2008','MM/DD/YYYY'),'dd-mon-yyyy') from dual
20. we can execute pl/sql  proceures with EXEC or CALL command;

21) to remove extra 0's in xml
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CP_COST?></fo:bidi-override>
22) sum of sal
<?sum(SAL[text()])?>       

Query to find procesing time of concurrent program

Query to find the concurrent program processing time:

SELECT f.request_id,
         pt.user_concurrent_program_name user_concurrent_program_name,
         f.actual_start_date actual_start_date,
         f.actual_completion_date actual_completion_date,
         FLOOR (
            ( (f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60)
            / 3600)
         || ' HOURS '
         || FLOOR (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600)
               / 60)
         || ' MINUTES '
         || ROUND (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600
                - (FLOOR (
                      ( (  (f.actual_completion_date - f.actual_start_date)
                         * 24
                         * 60
                         * 60)
                       - FLOOR (
                            (  (f.actual_completion_date - f.actual_start_date)
                             * 24
                             * 60
                             * 60)
                            / 3600)
                         * 3600)
                      / 60)
                   * 60)))
         || ' SECS '
            time_difference,
         DECODE (
            p.concurrent_program_name,
            'ALECDC', p.concurrent_program_name || '[' || f.description || ']',
            p.concurrent_program_name)
            concurrent_program_name,
         DECODE (f.phase_code,
                 'R', 'Running',
                 'C', 'Complete',
                 f.phase_code)
            Phase,
         f.status_code
    FROM apps.fnd_concurrent_programs p,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
   WHERE     f.concurrent_program_id = p.concurrent_program_id
         AND f.program_application_id = p.application_id
         AND f.concurrent_program_id = pt.concurrent_program_id
         AND f.program_application_id = pt.application_id
         AND pt.language = USERENV ('Lang')
         AND f.actual_start_date IS NOT NULL
         AND USER_CONCURRENT_PROGRAM_NAME LIKE 'XXXtest%'
ORDER BY f.actual_completion_date - f.actual_start_date DESC;