AR Invoice and Tax Information Query

Query: A

   select  rct.trx_number "INVOICE NUMBER"
,      to_char(rct.TRX_DATE,'DD-MON-YYYY') "INVOICE DATE"
,      rbs.name "SOURCE"
,      rctt.name "TYPE"
,      RCTL.LINE_NUMBER
,      RCTL.LINE_TYPE
,      (select distinct segment1 from mtl_system_items_b msib
         where msib.inventory_item_id = rctl.inventory_item_id) "LINE_ITEM"
,     RCTL.DESCRIPTION
,     rctl.QUANTITY_INVOICED  "QUANTITY"
,     rctl.UNIT_SELLING_PRICE "UNIT PRICE"
,     round((rctl.QUANTITY_INVOICED * rctl.UNIT_SELLING_PRICE),2) AMOUNT
,     decode(rctl.UOM_CODE,'EA','Each') UOM
,     ZL.TRX_LINE_NUMBER
,      ZL.TAX_REGIME_CODE
,      ZL.TAX
,      ZL.TAX_CODE
,      ZL.TAX_JURISDICTION_CODE
,      TJ.TAX_JURISDICTION_NAME
,      ZL.TAX_RATE_CODE
,      ZL.TAX_RATE
,      TRT.TAX_RATE_NAME
,      ZL.TAX_STATUS_CODE
,      TS.TAX_STATUS_NAME
,      ZL.TAXABLE_AMT
,      ZL.tax_only_line_flag
 from RA_CUSTOMER_TRX_ALL rct
,     ra_customer_trx_lines_all            rctl
,     RA_BATCH_SOURCES_ALL   rbs
,     ra_cust_trx_types_all                   rctt
,     ZX_LINES                                   zl
,     ZX_REGIMES_TL                      TR
,     ZX_JURISDICTIONS_TL          TJ
,     ZX_TAXES_TL                           TX
,     ZX_RATES_TL                           TRT
,     ZX_STATUS_TL                         TS
where 1=1
  AND rct.BATCH_SOURCE_ID                           =  rbs.BATCH_SOURCE_ID
  AND rct.CUST_TRX_TYPE_ID                          =  rctt.CUST_TRX_TYPE_ID
  AND rct.org_id                                                      =  rctt.org_id
  AND RCT.CUSTOMER_TRX_ID                       =  RCTL.CUSTOMER_TRX_ID
  AND RCT.org_id                                                   =  RCTL.org_id
  AND rctl.TAX_LINE_ID                                      =  zl.TAX_LINE_ID(+)
  AND ZL.TAX_REGIME_ID                                =  TR.TAX_REGIME_ID(+)
  AND NVL(ZL.TAX_JURISDICTION_ID,1)       =  TJ.TAX_JURISDICTION_ID(+)
  AND NVL(ZL.TAX_ID,1)                                    =  TX.TAX_ID(+)
  AND NVL(ZL.TAX_STATUS_ID,1)                    =  TS.TAX_STATUS_ID(+)
  AND NVL(ZL.TAX_RATE_ID,1)                        =  TRT.TAX_RATE_ID(+)
  AND TR.LANGUAGE(+)                                    =  userenv('LANG')
  AND TJ.LANGUAGE(+)                                     = userenv('LANG')
  AND TX.LANGUAGE(+)                                    = userenv('LANG')
  AND TRT.LANGUAGE(+)                                  = userenv('LANG')
  AND TS.LANGUAGE(+)                                    = userenv('LANG')
--  AND RCTL.LINE_TYPE       = 'LINE' --'TAX'  --  if you want tax line info
  AND rct.trx_number                                             =  'p_invoice_number';

Printable Button in OAF

1.       Create a button on Page Layout Region like

2.       Change Properties of Printable Button in Property Inspector

ID: PrintablePageID

Item Style: button

Attribute Set: /oracle/apps/fnd/attributesets/Buttons/PrintablePage

Note: After specifying Attribute set just Tab Out, Comments will add automatically.

Comments: UI Standards 'Printable Page' button as of 12/09/02

BC4J

View Instance:  XXEmpDetails          (select any View Object)

View Attribute: (Optional)

Navigation

Destination URI: OA.jsp?page=/xxvem/oracle/apps/ont/order/webui/XXVEMOrderAttachmentsPG&retainAM=Y&OARF=printable&HeaderId={@XXHeaderId}

Target Frame: _blank

Note: Here  

 /xxvem/oracle/apps/ont/order/webui/XXVEMOrderAttachmentsPG is the Actual Page Path and  {@XXHeaderId} is the View Attribute

VISUAL

Prompt: Printable Page

Add Code in Controller: In Process Form Request

 if (LafUtils.isPrintableFacet(pageContext))
   {
       // Printable page mode processing
        OAButtonBean print =(OAButtonBean)webBean.findIndexedChildRecursive("PrintPageId");
        print.setRendered(Boolean.FALSE);
     

   }

Register IN OUT mode Parameter in OAF

Code : 
 
    OADBTransaction oadbtransaction = getOADBTransaction();
    StringBuffer str = new StringBuffer();
    str.append( " BEGIN ");
    str.append( " APPS.XXVEM_PKGNAME_.XXVEM_PROCNAME( ");
    str.append( " p_line_id    => :1, ");
    str.append( " p_Invoice_id    => :2, ");
    str.append( " p_bill_id => :3, ");
    str.append( " p_prcedure_id     => :4, ");
    str.append( " p_success_flag    => :5, ");
    str.append( " p_message => :6  ");
    str.append( "    ); ");
    str.append( " END; ");

 CallableStatement oraclecallablestatement=                     oadbtransaction.createCallableStatement(str.toString(), 1);
        String msgs[]=new String[3];
        String SuccFlag;
        String ErrMsg;

        oraclecallablestatement.setInt(1,  LineId );
        oraclecallablestatement.setInt(2,  Invoiceid );
        oraclecallablestatement.setString(3,  billid );
        oraclecallablestatement.setInt(4,  prcedureid );
        oraclecallablestatement.registerOutParameter(4, Types.INTEGER);
        oraclecallablestatement.registerOutParameter(5, Types.VARCHAR);
        oraclecallablestatement.registerOutParameter(6, Types.VARCHAR);
        oraclecallablestatement.execute();
        getOADBTransaction().commit();
     
        PrcedureId = oraclecallablestatement.getString(4).toString();
        SuccFlag = oraclecallablestatement.getString(5);
        Msg= oraclecallablestatement.getString(6);
        msgs[0]= PrcedureId;
        msgs[1]=SuccFlag;
        msgs[2]=Msg;

Explanation: 

In the Above Code, I am registering 4th parameter as IN OUT mode parameter.
Register as IN parameter  :  oraclecallablestatement.setInt(4,  prcedureid );
Register as OUT parameter :  oraclecallablestatement.registerOutParameter(4, Types.INTEGER);
I am passing parameter to string array : msgs[0]= PrcedureId;

so I need to type cast the variable at line : PrcedureId = oraclecallablestatement.getString(4).toString();

OAF Page Deployment into Oracle Apps R12

1. In JDeveloper compile the page.
    When page got compiled, it will create .class files for all our java files.
    java files and xml files    ->  Jdeveloper installed place\jdevhome\jdev\myprojects
    class files and xml files   ->  Jdeveloper installed place\jdevhome\jdev\myclasses
 
2. Java Files can also be compiled in UNIX.
    Go to JAVA_TOP(/u01/oracle/DEMO/apps/apps_st/comn/java/classes).

3. In JAVA_TOP copy your package structure of myprojects (eg:      xxvem/oracle/apps/per/selfservice/server,                                                                  xxvem/oracle/apps/per/selfservice/webui)

   Compile each Java file.
   eg. xxvem/oracle/apps/per/selfservice/server/
           xx_paymentsVOImpl.java
     
        javac xx_paymentsVOImpl.java  -> To compile java file.
       Continue this process for all java files.

    OR

   Copy Package structure for all class files from \jdevhome\jdev\myclasses to server
   /oracle/DEMO/apps/apps_st/comn/java/classes/ ( cd $JAVA_TOP)
 

4. Run your import script for pages and regions.
   Note: When you did modifications on the page then you need to move page.xml file and run Import script. If you changed the only code in CO, AM, VO no needs to run the import script every time, bounce the services is enough.

5.Import script should be run from webui path (e.g. xxvem/oracle/apps/per/selfservice/webui)

6.Go to xxvem/oracle/apps/per/selfservice/webui in UNIX
   and run below Import script.
 
java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxvem/oracle/apps/per/selfservice/webui/XXVEMSelfservicePG.xml -rootdir  $JAVA_TOP -username apps -password veMUL76 -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOSTNAMW>)(PORT=<Port Number>))(CONNECT_DATA=(SID= <SID Value>)))"

7. Print Documents to check imported XML page content (Optional)

     Example:

     BEGIN
 jdr_utils.printDocument('/xxvem/oracle/apps/per/selfservice/webui/XXVEMSelfservicePG.xml,1000);
     EXCEPTION
     WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;
   
8. Create a Function(Application Developer->Applicaton->Function)
     In Properties -> select Type      = SSWA JSP Function
     In WEBHTMl    -> select HTML Call =
     OA.jsp?page=/xxvem/oracle/apps/per/selfservice/webui/XXVEMSelfservicePG
   
9. Create a Menu and submenu.Attach this Function to sub menu and submenu to a menu.
     Attach menu to Responsibility and run the page.

How to Create/Delete Implementation user in Oracle Fusion instance

We have two types of users in Oracle fusion.

1. Implementation User
2. Employee User

All implementation users can create by using IDM (Identity Manager). Implementation user cannot act as an employee. If you create any user from IDM there is no possibility to mapping employee to a user. All Employee users can create from HCM (Human Capital Management).

Create User:

Step1: Go to Home page URL and enter username and password.

Here Username: FUFIN_USR

           Password: *********

Click on “Sign In” Button.

Step2: you are navigating to User Home page, please see below Image

Step3: Click on Username (FUFIN_USR) or down arrow mark beside the username. You will “Settings and Actions” window, select “Setup and Maintenance” under Administration.

Step4: Search with “Manage Job Roles” in search place as shown in below image.

Click on Role Name, it will take to you IDM(Identity Manager) page.

Step 5: Creating Implementation User

             Click on “Administration”.

Click on “Create User” hyperlink.

Enter Mandatory Field values and click on SAVE button.

Last Name: FUSFIN_USR

For Organization click on LOV option and search with “%” and select default Organization provided by Oracle.

Organization: Xellerate Users

For User Type select from drop down list

User Type: Contractor

Enter Account Setting Details

User Login : FUSFIN_USR

Password : *********

Conform Password : *********

Click on SAVE Button.

The user has created successfully.

Step 6: Assign Roles to user

Click on Roles Tab

Click on Assign Button and search with Role display name and select the role and press Add Button.

In the same way, you can assign two more roles.

1. IT Security Manager

2. Employee

You have assigned roles to user successfully

How to Run JOB..?

Go to Home page and select navigator

Navigation: Navigator à Tools à Schedule Processes.

Click on Schedule New Process button.

Type some random text and press tab button.

Search with LDAP (case sensitive) and select ‘’ Retrieve Latest LDAP Changes” and press OK.

Parameter window will appear on the screen. Just click on submit button.

You will get confirmation.

Close the window. And press refresh button.

Delete User:

Step 1: Go to IDM (Identity Manager) and search for user name.

Click on user name and select Delete User Button.

Select Yes Button.

The user has deleted successfully.

OAF: Insert Data into Database

1. First, we have to write one new method in AMImpl class fro creating new record

public void InsertRecord()
{
 InsertVOImpl vo= getInsertVO1();
 OADBTransaction trans= getOADBTransaction();
 vo.executeQuery();
 Row v_row;
 v_row = (Row)vo.createRow();
 vo.insertRow(v_row);
}

2. In controller,  we have to initialise the AM in processRequest

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processRequest(pageContext, webBean);
 InsertRecordsAMImpl am=(InsertRecordsAMImpl)pageContext.getApplicationModule(webBean);
 am.InsertRecord();  /* Name of the method which we created in AM */
}

3. In processFormRequest, we have to write code for save the record into database.

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processFormRequest(pageContext, webBean);
 InsertRecordsAMImpl am=(InsertRecordsAMImpl)pageContext.getApplicationModule(webBean);
 if(pageContext.getParameter("item6")!=null)
 {
 am.getOADBTransaction().commit();
 throw new OAException("Employee Created sucsessfully",OAException.CONFIRMATION);
 }
}