To Generate Oracle Report Output in Excel

To Generate Oracle Report Output in Excel

Oracle Reports in csv and tsv . For the output to be displayed in Excel format.

I had to do a report to display the output with Japan Characters.

declare
     demoDocument     xoxo_ExcelDocumentType;
     documentArray    xoxo_ExcelDocumentLine := xoxo_ExcelDocumentLine();
     clobDocument     CLOB;
       v_file        UTL_FILE.FILE_TYPE;
       lc_desc       table.column%TYPE;
       lc_subject        xoxo_email.subject%TYPE;
       ln_batch_id       xoxo_email.batch_id%TYPE;
       lc_email_address  xoxo_email.to_address%TYPE;
       lc_message        xoxo_email.text_body%TYPE;
       lc_appl_name      applsys.fnd_application.application_short_name%TYPE := 'xoxo';
lc_prog_name      applsys.fnd_concurrent_programs.concurrent_program_name%TYPE := 'xoxo_BULK_EMAILER_CP';
       ln_request_id     NUMBER;
       ln_loop_counter   NUMBER := 1;
       lc_instance       VARCHAR2(10);

  BEGIN
    FND_GLOBAL.APPS_INITIALIZE(USER_ID=>1872,RESP_ID=>51097,RESP_APPL_ID=>401);

      BEGIN
                       SELECT substr(table.column,1,50)
                       INTO lc_desc
                       FROM table m
                       WHERE m.column = ...... ;
      EXCEPTION
                       WHEN OTHERS THEN
                       dbms_output.put_line('My Error');
      END;

     demoDocument := xoxo_ExcelDocumentType();

     -- Open the document
     demoDocument.documentOpen;
     -- Define Styles
     demoDocument.stylesOpen;
     -- Include Default Style
     demoDocument.defaultStyle;
     -- Add Custom Styles

     /* Style for Column Header Row */
     demoDocument.createStyle(p_style_id =>'ColumnHeader',
                               p_font     =>'Times New Roman',
                               p_ffamily  =>'Roman',
                               p_fsize    =>'10',
                               p_bold     =>'Y',
                               p_underline =>'Single',
                               p_align_horizontal=>'Center',
                               p_align_vertical=>'Bottom');

    /* Styles for alternating row colors. */
    demoDocument.createStyle(p_style_id=>'NumberStyleBlueCell',
                               p_cell_color=>'Cyan',
                               p_cell_pattern =>'Solid',
                               p_number_format => '###,###,###.00',
                               p_align_horizontal => 'Right');

    demoDocument.createStyle(p_style_id=>'TextStyleBlueCell',
                               p_cell_color=>'Cyan',
                               p_cell_pattern =>'Solid');

    /* Style for numbers */
    demoDocument.createStyle(p_style_id => 'NumberStyle',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right');

   /* Style for Column Sum */
    demoDocument.createStyle(p_style_id => 'ColumnSum',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right',
                              p_text_color => 'Blue');

   /* Style for Column Sum */
    demoDocument.createStyle(p_style_id => 'RowSum',
                              p_number_format => '###,###,###.00',
                              p_align_horizontal => 'Right',
                              p_text_color => 'Red'); 
     -- Close Styles
     demoDocument.stylesClose;
     -- Open Worksheet
     demoDocument.worksheetOpen('Weekly Earnings');

     -- Define Columns
     demoDocument.defineColumn(p_index=>'1',p_width=>100); -- Emp Name
     demoDocument.defineColumn(p_index=>'2',p_width=>16); -- Daily Dollar
     demoDocument.defineColumn(p_index=>'3',p_width=>16);
     demoDocument.defineColumn(p_index=>'4',p_width=>16);
     demoDocument.defineColumn(p_index=>'5',p_width=>16);
     demoDocument.defineColumn(p_index=>'6',p_width=>16);
     demoDocument.defineColumn(p_index=>'7',p_width=>16); -- Sum column

    -- Define Header Row
   demoDocument.rowOpen;

   --Define Header Row Data Cells
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Employee Name');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Monday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Tuesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Wednesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Thursday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Friday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Totals');

   demoDocument.rowClose;

   /*------------------------------------*/
   /* Sheet Data would normally be       */
   /* data driven via cursor loops       */
   /* or other means.                    */
   /* The purpose here is to demonstrate */
   /* the features of the utility.       */
   /*------------------------------------*/

   -- Row 1
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Jason Bennett');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'50000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'14000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 2
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>/*'Joe Smith'*/lc_desc);
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'8000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'35');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'1000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'15');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 3
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Wilma Jones');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'300');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'350');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'2000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'159');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 4
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>'Chris P.');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'45000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Summary Row 5
   demoDocument.rowOpen;
   demoDocument.addCell(p_col_index=>'2',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'3',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'4',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'5',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'6',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'7',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.rowClose;

  -- Close the Worksheet
  demoDocument.worksheetClose;

  -- Open New Worksheet
  demoDocument.worksheetOpen('Weekly Earnings 2');

     -- Define Columns
     demoDocument.defineColumn(p_index=>'1',p_width=>30); -- Emp Name
     demoDocument.defineColumn(p_index=>'2',p_width=>16); -- Daily Dollar
     demoDocument.defineColumn(p_index=>'3',p_width=>16);
     demoDocument.defineColumn(p_index=>'4',p_width=>16);
     demoDocument.defineColumn(p_index=>'5',p_width=>16);
     demoDocument.defineColumn(p_index=>'6',p_width=>16);
     demoDocument.defineColumn(p_index=>'7',p_width=>16); -- Sum column

    -- Define Header Row
   demoDocument.rowOpen;

   --Define Header Row Data Cells
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Employee Name');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Monday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Tuesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Wednesday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Thursday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Friday');
   demoDocument.addCell(p_style=>'ColumnHeader',p_data=>'Totals');

   demoDocument.rowClose;

   /*------------------------------------*/
   /* Sheet Data would normally be       */
   /* data driven via cursor loops       */
   /* or other means.                    */
   /* The purpose here is to demonstrate */
   /* the features of the utility.       */
   /*------------------------------------*/

   -- Row 1
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Jason Bennett');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'80000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'75000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'25000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'94000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'200');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 2
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>'Joe Smith');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'8000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'35');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'1000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'15');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 3
   demoDocument.rowOpen;
   demoDocument.addCell(p_data=>'Wilma Smith');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'77000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'850');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'9000');
   demoDocument.addCell(p_style=>'NumberStyle',p_data_type=>'Number', p_data=>'359');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Row 4
   demoDocument.rowOpen;
   demoDocument.addCell(p_style=>'TextStyleBlueCell',  p_data=>'Jeff F.');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'99000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'67000');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'500');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'650');
   demoDocument.addCell(p_style=>'NumberStyleBlueCell',p_data_type=>'Number', p_data=>'21000');
   demoDocument.addCell(p_style=>'RowSum',p_data_type=>'Number', p_formula=>'SUM(RC[-5]:RC[-1])');
   demoDocument.rowClose;

   -- Summary Row 5
   demoDocument.rowOpen;
   demoDocument.addCell(p_col_index=>'2',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'3',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'4',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'5',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'6',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.addCell(p_col_index=>'7',p_style=>'ColumnSum',p_data_type=>'Number',p_formula=>'SUM(R[-4]C:R[-1]C)');
   demoDocument.rowClose;

  -- Close the Worksheet
  demoDocument.worksheetClose;

  -- Close the document.
  demoDocument.documentClose;

  -- Get CLOB Version
  clobDocument := demoDocument.getDocument;

  -- Display the document to browser.
  demoDocument.displayDocument;

  -- Write document to a file
  -- Assuming UTL file setting are setup in your DB Instance.
  -- 
   documentArray := demoDocument.getDocumentData;

   -- Use command CREATE DIRECTORY FOO as ''
   -- to create a directory for the file.

   v_file := UTL_FILE.fopen('/usr/tmp','ExcelObjectTest.xml','W',4000);

   FOR x IN 1 .. documentArray.COUNT LOOP
                                                      UTL_FILE.put_line(v_file,documentArray(x));
   END LOOP;

   UTL_FILE.fclose(v_file); 

   -- get the next batch id
      SELECT xoxo.xoxo_email_batch_id_s.NEXTVAL
        INTO ln_batch_id
        FROM dual;

      dbms_output.put_line('Insert into xoxo_email table');

      lc_subject := 'Test';

      lc_message := 'Message';
     
      lc_email_address := 'email@address.com';

      INSERT INTO xoxo_email
         (
          email_id
         ,batch_id
         ,creation_date
         ,created_by
         ,from_address
         ,reply_to_address
         ,to_address
         ,cc_address
         ,bcc_address
         ,subject
         ,html_body
         ,text_body
         ,attachments
         ,processed
         )
      VALUES
         (
          xoxo.xoxo_email_email_id_s.NEXTVAL          -- email id
         ,ln_batch_id                                 -- batch id
         ,SYSDATE                                     -- creation date
         ,fnd_global.USER_ID                          -- user_id
         ,'from@address.com'                   -- from address
         ,NULL                                        -- reply to address
         ,lc_email_address                            -- to address
         ,NULL -- 'oracle_archive@kkkk.com'         -- cc address
         ,NULL                                        -- bcc_address
         ,lc_subject                                  -- subject
         ,NULL                                        -- html body
         ,lc_message                                  -- text body
         ,'/usr/tmp/ExcelObjectTest.xml'        -- attchments
         ,'N'                                         -- processed
         );

      COMMIT;

      dbms_output.put_line('Submit Request to send email with batch id: ' || ln_batch_id);

      -- submit the request
      ln_request_id := fnd_request.submit_request(application => lc_appl_name
                                                 ,program     => lc_prog_name
                                                 ,argument1   => ln_batch_id);

      COMMIT;

      dbms_output.put_line('Submit Request id: ' || ln_request_id);

EXCEPTION
  WHEN OTHERS THEN
      /* For displaying web based error.*/
      htp.p(sqlerrm);
      /* For displaying command line error */
      dbms_output.put_line(sqlerrm);

 END;

Link XLA Tables With AR Tables To Get Information From GL

Link XLA Tables With AR Tables To Get Information From GL

There is no one-to-one mapping between AR, XLA and GL.



The data that actually posts to the GL is the XLA table data, not AR. Depending on the AAD rules you have defined, one row that appears in AR could become 10 in XLA_DISTRIBUTION_LINKS, but when the data is posted into the GL, the accounts of the same type and ccid are merged to a single entry.

For example, a transaction could have 34 gl_dist rows, but 201 rows in ar_distribution_links, but when it actually posts, consolidates to 9 rows in gl_import_references.

The main link to bind information together is the gl_sl_link_id. It will show in the gl_je_lines, gl_import_refernces and xla_ae_lines table.


Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction.

ACTION: COMPLETE TRANSACTION: 

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID 

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1 

XLA_TRANSACTION_ENTITIES.ENTITY_ID = XLA_EVENTS.ENTITY_ID 

ACTION: RUN CREATE ACCOUNTING: 

RA_CUST_TRX_LINE_GL_DIST_ALL.CUST_TRX_LINE_GL_DIST_ID = XLA_DISTRIBUTION_LINKS.SOURCE_DISTRIBUTION_ID_NUM_1 

XLA_AE_LINES.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID 

XLA_AE_HEADERS.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID 

ACTION: RUN TRANSFER TO GL: 



XLA_AE_LINES.GL_SL_LINE_ID = GL_JE_LINES.GL_SL_LINK_ID 
XLA_AE_LINES.GL_SL_LINK_ID = GL_IMPORT_REFERENCES.GL_SL_LINK_ID 
GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_LINES.JE_HEADER_ID 
GL_IMPORT_REFERENCES.HE_HEADER_ID = GL_JE_HEADERS.JE_HEADER_ID


Note: If the data is upgraded data, you may find that in the link id is missing in the
gl_je_lines table. IF so, log a Service REquest with Oracle Support to request script upd_gl_sl_link_util_rev.zip to populate the missing data.

Oracle Apps Stuff in Net

 Oracle Apps Stuff in Net

http://wordpress.com/tag/oracle-order-management/
http://georgenet.net/oracle/
http://oracleebusinesssuite.wordpress.com/
http://www.sap-img.com/oracle-database/oracle-application-hints-and-tips.htm
http://garethroberts.blogspot.com/2007/09/standard-report-to-csv-file-via-bi.html
http://oracle-hrms-11i.blogspot.com/
http://www.w3schools.com
http://www.ebusinesslab.cn
http://www.trutek.com/index.php?id=53
http://feeds.feedburner.com/OracleAppsFAQ
http://onlineappsdba.com/index.php/category/identity_manager/
http://www.richardbyrom.com/download.htm
http://www.appsworkshop.com
http://onlineappsdba.blogspot.com/
http://www.admin.ox.ac.uk/financials/helpsheets/
http://oracleqa2.blogspot.com
http://peopleapps.com/
http://aspen.ithaca.edu:7778/portal/page_pageid=133,77462&_dad=portal&_schema=PORTAL
http://www.frp.qut.edu.au/frptoolkit/frptraining/finance/purchasing/enter_req.jsp
http://www.orafaq.com/forum/i/0/
http://www.chain-sys.com/demo_ground.shtml
http://www.ebusinesslab.cn
http://www.sqlmanager.net/en/news/sql/1133
http://blogs.oracle.com/xmlpublisher/2007/08/07
http://advait.wordpress.com/oracle-apps-11i-profiles/
http://www.infocaptor.com/articles/sql.html
http://oracle-applications-rama.blogspot.com/2007_10_01_archive.html
http://www.more4apps.com/?gclid=CKXglPqfhI8CFQh6gwod9zR91w
http://www.appssys.com/
http://oraclecrp.com/
http://www.oracleappsblog.com/index.php/forum
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/ap/invoic13.htm
http://main.uab.edu/show.asp?durki=73096
http://main.uab.edu/show.asp?durki=66289
http://www.learndiscoverer.com/downloads/downloads.htm
https://internet-apps.com/iapps/ic/InvReports.html
https://app.smartturn.com/occam/help/help_en/inv_reports.html
http://www.oracleappshub.com/category/oracle-purchasing/
http://oracle-applications-rama.blogspot.com/
http://www.appsdba.com/
http://www.eas.gwu.edu/home/support/documentation/documentation.cfm
http://www.fin.gov.nl.ca/ComptrollerGeneral/oraclemanuals/purchasing
http://www.fin.gov.nl.ca/ComptrollerGeneral/oraclemanuals/accountspayable/default.htm
http://oraclea2z.blogspot.com
http://www.oraclefans.com/oraclefans/forum/erpfinan/
http://www.erpfans.com/
http://www.teachmeoracle.com/forum/
http://www.esnips.com/_t_/oracle+apps
http://docs.huihoo.com/oracle/docs/B25516_06/current/html/doclist.html
http://www.umec.com.tw/r11i/html/trmdoc.html
http://www.orafaq.com/forum/t/49413/0/
http://erpstuff.com/forum.asp?FORUM_ID=3
http://www.ysn.ru/docs/oracle/workflow.920/
http://www.praetoriate.com/shad_fin9.htm
http://www.praetoriate.com/oracle_tips.htm
http://www.tacticspartners.com
http://www.geocities.com/oracletricks/oraapps/
http://oracle-financials-11i.blogspot.com/
http://www.visualbuilder.com/showCode.php?id=81369&scd_id=2849
http://www.java2s.com/Code/Oracle/PL-SQL/CatalogPL-SQL.htm
http://www.idevelopment.info/data/Programming/plsql/PROGRAMMING_PLSQL_Home.cgi
http://oracleapplications-11i.blogspot.com
http://oracle-hrms-11i.blogspot.com
http://learn-oracle-apps.blogspot.com
http://oracle-aol-11i.blogspot.com
http://oraclehrmsapps.blogspot.com
http://computerebook.blogspot.com/
http://www.oracle.ask2ask.com
http://www.appsdbablog.com
http://blogs.oracle.com/schan/
http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/workflow.102/b15853/T361836T361982.htm
http://www.workflowfaq.com/
http://www.miraclewisdom.com/oracle_applications.htm
http://oracleappstechnology.blogspot.com/2007/07/why-is-it-called-apps-and-not-oracle.html
http://appstechnical.blogspot.com/
http://garethroberts.blogspot.com/2007/08/audit-trail-must-do-bank-accounts.html
http://getappstraining.blogspot.com/2006/10/what-happens-when-you-login-to-apps.html
http://www.coaug.org/presentations.html
http://www.appworx.com/solutions/oracle.cfm
http://www.infocaptor.com/articles/2006/01/view-discoverer-reports-through-oracle.html
http://web.mit.edu/sapr3/windocs/bporb06a.htm
http://www.ctipc.com/Novaware/IC.htm
http://www.virginia.edu/integratedsystem/howdoi/HTML/NAV5537U.htm
http://www.bscaler.com/erm/next_generation_erp.htm
http://www.cougarmtn.com/accounting-software-reports/accounts-payable.asp
http://www.dbtips.com/
http://www.sucharitha.com/
http://www.fors.com/velpuri2/Applications/8.htm
http://bfa.sdsu.edu/~leap/appsupgrade.htm
http://bfa.sdsu.edu/~leap/documentation.htm
http://www.purchasing.upenn.edu/buyinfo/guide/
http://www.acsspr.com/po.html
http://www.frameware2000.com/reports.htm
http://www.virginia.edu/integratedsystem/howdoi/HTML/NAV5515U.htm#ZZZ_TUT_1
http://www.mainstreetasp.com/purchase.htm
http://scripts4oracle.blogspot.com/
http://orafact.com/index_files/Page1023.htm
http://www.erpstuff.com/topic.asp?TOPIC_ID=2353
http://www.eplanetlabs.com/oracle-1Z0-141-Certification.html
http://onlineappsdba.blogspot.com/




http://download-uk.oracle.com/docs/cd/A60725_05/html/comnls/us/ap/index.htm

Oracle Apps Training Videos

 Oracle Apps Training Videos

Easy to Learn Oracle Apps Tutorials
http://www.exforsys.com/content/category/17/260/342/
Oracle Apps Student Guides
http://www.megaupload.com/?d=YTD92KVF
Oracle Student
http://www.megaupload.com/?d=WPXTBMMV
Oracle Financials Open Interface Manual
http://www.megaupload.com/?d=K0QVC3ID
Video Tutorials on Oracle General Ledger
http://www.megaupload.com/?d=JOET6PQS
Oracle ADI and 11i Tip Sheets
http://www.megaupload.com/?d=UYQDBNC9
Oracle Student Guides on HRMS
http://www.megaupload.com/?d=PT3HT7U2
GL Practices
http://www.megaupload.com/?d=7TXUMWZ1
Order Management Demos
http://www.megaupload.com/?d=DVNKZRL7
OPM Setup
http://www.megaupload.com/?d=AOXMEDDC
Oracle Student Guide on Order-To-Cash Life Cycle
http://www.megaupload.com/?d=0TADDC1I
Standard Reports in GL,AR,AP,PO
http://www.megaupload.com/?d=ZT2D0VWX

Good Document on How to Integrate Custom Report with Oracle Apps
http://www.megaupload.com/?d=ABHRCOTK
SQL How-To's for Practice
http://www.megaupload.com/?d=RHYD2D3G
White papers on AP
http://www.megaupload.com/?d=DYQZXAT3http://d.turboupload.com/d/732112/AP.zip.html
White papers on
http://www.megaupload.com/?d=TKV860E3
Oracle Financials Open Interface Manual
http://www.megaupload.com/?d=K0QVC3ID

Base Tables of O2C Process:

Base Tables of O2C Process:
When you entered the Order and Booked the Order following table will store the Information:

SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = 55950
AND HEADER_ID = 82465

SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = 82465
AND LINE_ID IN (161391, 161393)

SELECT * FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID = 82465
AND SOURCE_LINE_ID in (161391, 161393)

Ø Release Status is ‘R’ (Ready to Release)

SELECT * FROM WSH_DELIVERY_ASSIGNMENTS
WHERE DELIVERY_DETAIL_ID IN (179553,179554)

Ø When u Create Delivery Details that time this table will populate the record.
Ø AND DELIVERY_ID COLUMN ALSO WILL UPDATED IN WSH_DELIVERY_ASSIGNMENTS TABLE

SELECT * FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID IN (48152,48153)
After “Launch Pick Release” Following table will populate:

SELECT * FROM WSH_PICKING_BATCHES
WHERE BATCH_ID IN (33867,33868)
Move Order Number = 33868

SELECT * FROM MTL_TXN_REQUEST_HEADERS
WHERE REQUEST_NUMBER = 34003

Ø Here Header_id is Batch Number

SELECT * FROM MTL_TXN_REQUEST_LINES
WHERE TXN_SOURCE_LINE_ID = 161391

Ø Here TXN_SOURCE_LINE_ID is the Line_ID of the OE_ORDER_LINES_TABLES.
After Ship Confirm Following table will populate:

SELECT * FROM MTL_SALES_ORDERS
WHERE SEGMENT1 = 55950
AND SALES_ORDER_ID = 42058

SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE INVENTORY_ITEM_ID = 149
AND TRANSACTION_REFERENCE = '82465'
AND TRANSACTION_SOURCE_ID = 42058

Ø Where TRANSACTION_REFERENCE is storing the HEADER_ID.

SELECT * FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID IN (48152)

select * from MTL_ONHAND_QUANTITIES
where inventory_item_id = 149

After Running the “Workflow Background Process” Programme Invoice will Generate and that time following table will populate:

SELECT * FROM RA_CUSTOMER_TRX_ALL
WHERE INTERFACE_HEADER_ATTRIBUTE1 = '55950'

Ø Here INTERFACE_HEADER_ATTRIBUTE1 is the Order_Number.
Ø And TRX_COLUMN is the Invoice Number.

SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = 118416

Ø Here INTERFACE_LINE_ATTRIBUTE6 Column is the LINE_ID.
Ø SALES_ORDER Column is also there which is storing ORDER_NUMBER.

SELECT * FROM AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_ID = 5093
and CUSTOMER_TRX_ID = 118416

Ø To get the Outstanding of the Customer
After Transfer into the GL tables through “General Ledger Transfer Program” concurrent Programe:
SELECT * FROM GL_JE_BATCHES
WHERE NAME = 'AR 26137 Receivables 2202288: A 26137'

SELECT * FROM GL_JE_HEADERS
WHERE JE_BATCH_ID = '72750'
AND JE_HEADER_ID = 62120

SELECT * FROM GL_JE_LINES
WHERE JE_HEADER_ID = 62120
--AND REFERENCE_5 = 10016902

Here Reference_5 is the Invoice Number.
Receipt Transaction :

SELECT * FROM AR_CASH_RECEIPTS_ALL
WHERE RECEIPT_NUMBER = 'NKREC_240105'

Ø Before Adjustment STATUS is UNAPP Or UNID but Once u Applied with Invoice It will change into “APP”.

SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE CASH_RECEIPT_ID = 21116

Ø In this Table APPLIED_CUSTOMER_TRX_ID store the CUSTOMER_TRX_ID of the RA_CUSTOMER_TRX_ALL table.
Order Type is Return Only:

SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = 55987

SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = 82634

Ø LINE STATUS IS “AWAITING_RETURNS”.
Ø Order Line Type should be “Return (Receipt)”.

SELECT * FROM RCV_SHIPMENT_HEADERS
WHERE RECEIPT_NUM = 7607
AND SHIPMENT_HEADER_ID = 30539

SELECT * FROM RCV_SHIPMENT_LINES
WHERE SHIPMENT_HEADER_ID = 30539

SELECT * FROM RCV_TRANSACTIONS
WHERE SHIPMENT_HEADER_ID = 30539

Ø IN RCV_SHIPMENT_LINES Table OE_ORDER_HEADER_ID AND OE_ORDER_LINE_ID Column is Link with OE_ORDER_HEADER AND OE_ORDER_LINE Tables.
Ø After Receiving the Material at “Receiving Stage” at that time Order Line Status will be “Awaiting Return Disposition”.
Ø After Deliver the Material to “Inventory” at that time Order Line Status will be “Returned” and One More Line will be Created with “Remaining Quantity”
For Example: If your Order line quantity is 3 and you have received 1 quantity than one More line will be created with 2 quantity in OE_OREDER_LINES table with status “AWAITING_RETURNS”
Base Tables of List of LOV in Sales Order Screen:
Customer Name:
PARTY_NAME Column of HZ_PARTIES table.
Customer Number:
ACCOUNT_NUMBER Column of HZ_CUST_ACCOUNTS table.
Ø Where PARTY_ID Column is the Link between HZ_PARTIES and HZ_CUST_ACCOUNTS tables.
Customer Contact:
FIRST_NAMELAST_NAME Column of AR_CONTACTS_V view.
Bill To and Ship to Locations:
CUST_ACCT_SITE_ID Column of HZ_CUST_SITE_USES_ALL table.
Order Type:
Name and Description Column of OE_TRANSACTION_TYPES_V View
To Get the Payment Terms:
SELECT * FROM RA_TERMS
Look Up Values:
Ø REQUEST_DATE_TYPE For Line Set
Ø SALES_CHANNEL
Ø FREIGHT_TERMS
Ø SHIPMENT_PRIORITY
Ø PAYMENT TYPE
To Get the Shipping Method:
SELECT * FROM WSH_CARRIER_SERVICES
SELECT * FROM WSH_ORG_CARRIER_SERVICES
WHERE ORGANIZATION_ID = '207'
To get the FOB:
Value is storing into the AR_LOOKUP where LOOKUP_TYPE = ‘FOB’
Drop Shipment Process:
Ø Once you booked the Order with type “Mixed” and line source type is “External” in “Shipping” tab that time the line status will be “Booked” in both the levels Header as well as line.
Ø After booked the Order we have to do “Purchase Release” or run the “Workflow Background Process”.
Workflow back ground process transfer the Sales Order Data into the PO_REQUISITIONS_INTERFACE_ALL Interface table to create the Purchase Requisition.
Once the Data is populated in Interface table then Run “Requisition Import” program to transfer the data into the base tables.
After that Line Status will be “Awaiting Receipt”.
Purchase Requisition Number can see at Line level in Additional Information option under that “Drop Ship” tab.
Link between Sales Order, Purchase Requisition and Purchase Order we can fine in

SELECT * FROM OE_DROP_SHIP_SOURCES
Here you will get ORDER_HEADER_ID, REQUISITION_HEADER_ID, REQUISITION_LINE_ID, PO_HEADER_ID, and PO_LINE_ID

Ø After Creating the Requisition Run the Auto creates option to create the Purchase Order against that purchases requisition.
Ø Receipt the Material :
If you received partially then Order Line status will be remain same like “Awaiting Receipt” Once you completely receipt the material then only status will changed into “Shipped”.
Ø Then after one Purchase Invoice will be created in Payables Module against the Purchase order and once sales invoice will be created in Receivables module against the sales order.