Extract for Payments - Check and EFT

 SELECT     papf.employee_number  employee_number
          , aps.vendor_name        payee_name
          , ieba.bank_account_num  bank_account_number
          , hp_bank.party_name     bank_name
          , hp_bank.organization_name_phonetic     bank_number
          , hps_branch.party_name  branch_name        
          , ipa.org_id             org_id
          , ipa.payment_date       payment_date
          , ieba.bank_account_type bank_account_type
          , ieba.bank_id
          , cba.bank_account_type  int_bank_account_type
          , cba.bank_account_num   int_bank_account_num
          , round(sum(ifdpv.payment_amount  *
                decode(ipa.payment_currency_code,
                     asp.base_currency_code,
                     1,
                     decode(aivsc.exchange_rate_type,
                            'User', --l_exchange_rate_type
                            ap_pmt_callout_pkg.get_user_rate(asp.base_currency_code,
                                                             ipa.payment_currency_code,
                                                             ifdpv.calling_app_doc_unique_ref1),
                            ap_utilities_pkg.get_exchange_rate(ipa.payment_currency_code,
                                                               asp.base_currency_code,
                                                               aivsc.exchange_rate_type,
                                                               aivsc.check_date,
                                                               'CONFIRM')))),0) payment_amount
          , MIN(ifdpv.calling_app_doc_ref_number)  reference
          , MIN(ifdpv.document_description)        document_description
       FROM iby_pay_instructions_all      ipia
          , iby_payments_all              ipa
          , iby_external_payees_all       iepa
          , ap_supplier_sites_all         assa
          , ap_suppliers                  aps
          , iby_ext_bank_accounts         ieba
          , hz_parties                    hp_bank
          , hz_parties                    hps_branch
          , iby_fd_docs_payable_v         ifdpv
          , ap_inv_selection_criteria_all aivsc
          , ap_system_parameters_all      asp
          , ce_bank_accounts              cba
 , per_all_people_f              papf
      WHERE ipia.payment_instruction_id       = p_payment_instruction_id
        AND ipia.payment_instruction_id       = ipa.payment_instruction_id
        AND ipa.ext_payee_id                  = iepa.ext_payee_id
        AND iepa.supplier_site_id             = assa.vendor_site_id
        AND assa.vendor_id                    = aps.vendor_id
        AND ipa.external_bank_account_id      = ieba.ext_bank_account_id (+)
        AND ieba.bank_id                      = hp_bank.party_id (+)
        AND ieba.branch_id                    = hps_branch.party_id (+)
        AND ipa.payment_id                    = ifdpv.payment_id
        AND ifdpv.calling_app_doc_unique_ref1 = aivsc.checkrun_id
        AND ipa.org_id                        = asp.org_id
        AND ipa.internal_bank_account_id      = cba.bank_account_id
AND aps.employee_id                   = papf.person_id (+)
      GROUP BY papf.employee_number
          , aps.vendor_name
          , ieba.bank_account_num
          , hp_bank.party_name
          , hp_bank.organization_name_phonetic
          , hps_branch.party_name        
          , ipa.org_id
          , ipa.payment_date
          , ieba.bank_account_type
          , ieba.bank_id
          , cba.bank_account_type
          , cba.bank_account_num;

Steps to Create Excel for Apps Report

• GL Wand needs no desktop installation and it is very user friendly for creating many financial reports. It is a very good reporting tool for Oracle E-Business suite.

Pre-Requisites : Users should have Excel4apps Wands responsibility to open the template and run the report.. Once the responsibility is assigned, on clicking the Excel4apps Wands responsibility, new add-ins will be added in Excel named “Reports Wand”

Step 1:

•  After framing sql query, we need to go to Excel4apps responsibility and create Report Definition.

•  Click Create as highlighted in the above screenshot for Creating a new Report definition and Edit for  updating the Report definition. While updating the Report definition, click on Import Icon next to Edit button(Refer screen above) and provide the short name of the Program. Modify the SQL Section and              follow the steps as below.

• On clicking create, below screen will appear

• In Program Information, Provide the short name, Report name, Description. Category will be the application to which the report has to be attached

• Clicking on Next will take you to SQL Window where we need to paste the sql query. If the Report has any bind variables, please attach in the Bind Variable section

• Attach the report to the request group then, and complete the Report definition Template.

• Once we click on ‘Finish’ button, Excel will be opened as below

• In the Action column, double click on the LOV (as in the figure above). When we are creating the Report definition, Action Column should be Create. While updating it should be ‘Update’ and while deleting, it should be delete.

• After selecting ‘Create’ from LOV in the ‘Action column, click on Process Icon next to Import button.

• It will have two drill downs ‘Process All’ and ‘Process Single’. Select Process All and the Report definition will be created successfully and displayed in Error Column. If any Errors, it will be displayed as ‘Error’

• On clicking Proceed, will lead updating the Error Column in the Report definition as shown below

Steps on creating/Running the Template 

• Click on Create button as shown below. Before clicking the ‘Create’ button, we should make sure we are in the correct Responsibility as shown in the figure below. We can even change the Responsibility from here (E4A Tool) as in the figure below 

• Click on ‘Create button’ and select the Report definition’s short name as in the figure below and click Select

• Clicking on ‘Select’ will take us to the below screen where we can add drop columns from the Query. Clicking on ‘Cross’ button will remove the columns from the template.

• Now click ‘Create’ as in the above figure. 

• Now the Template is created. Click on ‘Execute’ button to run the template.
•  Click on ‘Execute’ Report and the report will run successfully and the output will be generated.
• The template to be saved and shared to ‘Users’ and check in different Instances.

ERROR ORA -00980 Synonym Translation no longer valid

Error Message :  ORA -00980 Synonym Translation no longer Valid.

Error Area : it is related Database (Table/view)  not related to forms

When it happens :  Synonym is in inconsistent state, in my case  i have modified the view on which synonym was created (dependent) .

How to confirm the issue : when query the data using the synonym  in TOAD/sqldeveloper .....  it shows error message  ORA -00980 Synonym Translation no longer valid.

Action Plan/solution:  drop the synonym and recreate  synonym(after the  base view/table modified)

verify:  Query the data by using the synonym  then it should fetch the data


By
Venkata GANNE

OATS Installation issue - Struck/Hang for Long Time

Installation Issue :  ' Spawning C:\OracleATS\bin\deploy.bat C:\OracleATS\install\oats.ear'   and never completes for Long Time ( even for Days)

When it Happen : While Installing OATS Software

Action Needs to Taken :  STOP/Disable   Anti Virus Software(s)  and/  Disable Firewall.


By
Venkata GANNE.

Create a User in Oracle Load Testing (OLT)

How to Create a User for ORACLE Load Testing LogIn

Clink on Start Button  Oracle Application Testing Suite Administrator

Login with Administrator User as shown below

Note: Ensure DataBase should be OLT Database while login

After logged in appears as below

Click on Add icon

Enter User Details

Select Access type

If you want provide administrator access to the user then check the check box

Click ok

Logout

Now Open Oracle Load Testing in Browser  as shown below

Or you can login with url  like   http:localhost:8088/login.do

Login with recently Created User 

By

Venkata GANNE

please feel free to ask any query on this.