Execute Immediate in Oracle Reports

Execute Immediate in Oracle Reports

Using this EXECUTE IMMEDIATE command directy in reports is not possible, if you tried to use, i.e. EXECUTE IMMEDIATE ‘drop TABLE TEST’,
this error message will occured,  “this feature is not supported in client-side programs”.
EXECUTE IMMEDIATE is only supported on the server side.,but you can
work around this by creating astored procedure in the database and then using this
procedure in reports.

The database stored procedure can be created as follows:

CREATE OR REPLACE PROCEDURE DYNAMIC_SQL(STMNT CHAR) IS
BEGIN
   BEGIN
     EXECUTE IMMEDIATE STMNT;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error while executing dynamic sql');
   END;
END;

Now, this procedure can be called in Reports.
For Example:
In the BeforeParam Trigger,the procedure can be called as:
DYNAMIC_SQL('DROP TABLE TEST');
This will drop the table 'TEST' from the database.

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts Details

R12 Supplier Bank Accounts
In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. The supplier (or External) bank account information are stored in the table called IBY_EXT_BANK_ACCOUNTS. The bank and bank branches information are stored in the table HZ_PARTIES. They are linked together through Relationships (in HZ_RELATIONSHIP).

There is a separate link for both Bank to Branch and also from Branch to Bank. Bank sites and Location information are stored in tables: HZ_PARTY_SITES and HZ_LOCATIONS. The bank_id and branch_id fields of IBY_EXT_BANK_ACCOUNTS table link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table (IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id).

Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. Click on Banking Details and then choose Create. After you have created the bank account, you can assign the bank account to the supplier site.

When the Bank is assigned to Vendors then it will be updated in a table called HZ_CODE_ASSIGNMENTS. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. IBY_EXTERNAL_PAYEES_ALL stores payment-related attributes for the funds disbursement payment process for external party payees.

Queries:


SELECT  aps.vendor_name "VERDOR NAME",
        apss.vendor_site_code "VENDOR SITE CODE",
        ieb.bank_name "BANK NAME",
        iebb.bank_branch_name "BANK BRANCH NAME",
        iebb.branch_number "BRANCH NUMBER",
        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM    ap.ap_suppliers aps,
        ap.ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts ieba,
        apps.iby_account_owners iao,
        apps.iby_ext_banks_v ieb,
        apps.iby_ext_bank_branches_v iebb
WHERE   aps.vendor_id = apss.vendor_id
        and iao.account_owner_party_id = aps.party_id
        and ieba.ext_bank_account_id = iao.ext_bank_account_id
        and ieb.bank_party_id = iebb.bank_party_id
        and ieba.branch_id = iebb.branch_party_id
        and ieba.bank_id = ieb.bank_party_id;
Oracle PL/SQL


SELECT party_supp.party_name supplier_name
,      aps.segment1          supplier_number
,      ass.vendor_site_code  supplier_site
,      ieb.bank_account_num
,      ieb.bank_account_name
,      party_bank.party_name bank_name
,      branch_prof.bank_or_branch_number bank_number
,      party_branch.party_name branch_name
,      branch_prof.bank_or_branch_number branch_number
FROM   hz_parties party_supp
,      ap_suppliers aps
,      hz_party_sites site_supp
,      ap_supplier_sites_all ass
,      iby_external_payees_all iep
,      iby_pmt_instr_uses_all ipi
,      iby_ext_bank_accounts ieb
,      hz_parties party_bank
,      hz_parties party_branch
,      hz_organization_profiles bank_prof
,      hz_organization_profiles branch_prof
WHERE  party_supp.party_id = aps.party_id
AND    party_supp.party_id = site_supp.party_id
AND    site_supp.party_site_id = ass.party_site_id
AND    ass.vendor_id = aps.vendor_id
AND    iep.payee_party_id = party_supp.party_id
AND    iep.party_site_id = site_supp.party_site_id
AND    iep.supplier_site_id = ass.vendor_site_id
AND    iep.ext_payee_id = ipi.ext_pmt_party_id
AND    ipi.instrument_id = ieb.ext_bank_account_id
AND    ieb.bank_id = party_bank.party_id
AND    ieb.bank_id = party_branch.party_id
AND    party_branch.party_id = branch_prof.party_id
AND    party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
,        ass.vendor_site_code;

Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program

Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program

Here we can segregate this issue into 2 Parts.

1. Invoices in status of Selected for validation when cancelled Invoice Validation Program it will be stamped with Validation_request_id.
2. Invoice Validation program keep on running from hours together and want to cancel the Request

When ever we cancel the invoice validation program when its running, it should roolback all the invoices back to its normal status but some/many times it will not rollback the invoices and it will stamp the cancelled request id on all the invoices under column VALIDATION_REQUEST_ID of AP_INVOICE_HEADERS_ALL.

What ever the invoices stamped with request_id can be get with this query to know the count or list of invoices.
SQL:
select * from ap_invoices_all where validation_request_id = 'XXXXX';-- or where validation_request_id is not null;

Once we extract these invoices we need to fix the invoices with the help of data-fix to rollback stamped invoices.

Solution: (Reference Patch#17428522)

1. Cancel the  request (If the request is not yet cancel the request)

A.  Cancel present child request #46981209

B.  Wait for few minutes to let parent request complete, it might initiate other new child requests in couple of minutes.

C.  If parent request do not initiate other child requests and parent request not completes in few minutes, cancel parent request as well.

2. Apply patch#17428522 and Run script: ap_inv_val_prb_sel.sql

Path: $AP_TOP/patch/115/sql/ ap_inv_val_prb_sel.sql

Temp Driver Table:
AP_TEMP_DATA_DRIVER_9327208

If you want to extract the invoices from the driver table use this query

select * from AP_TEMP_DATA_DRIVER_9327208

3. Run update statement:

update AP_TEMP_DATA_DRIVER_9327208
set process_flag=’N’
where validation_request_id is null;

commit;
/
4. Run fix script: ap_inv_val_prb_fix.sql

Path:$AP_TOP/patch/115/sql/ ap_inv_val_prb_fix.sql

Once the fix completed successfully, run the below again here you should get '0' records

Query:
select count(invoice_id) from ap_invoices_all where validation_request_id is not null;

Submit a Concurrent Request from backend

/*********************************************************
*PURPOSE: To Submit a Concurrent Request from backend    *
*AUTHOR: Dilli Subramani                              *
*Date : 24/02/2015                        *
**********************************************************/
--
DECLARE
l_responsibility_id     NUMBER;
l_application_id        NUMBER;
l_user_id               NUMBER;
l_request_id            NUMBER;
BEGIN
  --
  SELECT DISTINCT fr.responsibility_id,
    frx.application_id
     INTO l_responsibility_id,
    l_application_id
     FROM apps.fnd_responsibility frx,
    apps.fnd_responsibility_tl fr
    WHERE fr.responsibility_id = frx.responsibility_id
  AND LOWER (fr.responsibility_name) LIKE LOWER('XXTest Resp');
  --
   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'STHALLAM';
  --
  --To set environment context.
  --
  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);
  --
  --Submitting Concurrent Request
  --
  l_request_id := fnd_request.submit_request (
                            application   => 'XXCUST',
                            program       => 'XXEMP',
                            description   => 'XXTest Employee Details',
                            start_time    => sysdate,
                            sub_request   => FALSE,
                argument1     => 'Smith'
  );
  --
  COMMIT;
  --
  IF l_request_id = 0
  THEN
     dbms_output.put_line ('Concurrent request failed to submit');
  ELSE
     dbms_output.put_line('Successfully Submitted the Concurrent Request');
  END IF;
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;
/









========================================



Pre-requisites :
Step1: Data Definition and Template to be created
Step2: Concurrent program needs to be created

Steps To Create the PL/SQL package:

1. Initialize the  Session Specific variable using fnd_global.APPS_INITIALIZE
2. Set The BI publisher report layout Before submitting the concurrent program
3. Submit the Concurrent Program

Code: (Tested in R12.1.1 )

DECLARE
   l_user_id              fnd_user.user_id%TYPE;
   l_resp_id              fnd_responsibility.responsibility_id%TYPE;
   l_resp_appl_id         fnd_application.application_id%TYPE;
   l_set_layout           boolean;
   l_request_id           NUMBER;
   l_phase                VARCHAR2 (100);
   l_status               VARCHAR2 (100);
   l_dev_phase            VARCHAR2 (100);
   l_dev_status           VARCHAR2 (100);
   l_wait_for_request     boolean := FALSE;
   l_get_request_status   boolean := FALSE;
   Output_layout_failed EXCEPTION;
   request_submission_failed EXCEPTION;
   request_completion_abnormal EXCEPTION;
BEGIN
   l_request_id := NULL;

   --
   -- Get the Apps Intilization Variables
   --
   SELECT   fnd.user_id, fresp.responsibility_id, fresp.application_id
     INTO   l_user_id, l_resp_id, l_resp_appl_id
     FROM   fnd_user fnd, fnd_responsibility_tl fresp
    WHERE   fnd.user_name = 'OEAG'
            AND fresp.responsibility_name = 'Custom XML Reports';

   --
   --Initialize the Apps Variables
   --
   fnd_global.APPS_INITIALIZE (user_id        => l_user_id,
                               resp_id        => l_resp_id,
                               resp_appl_id   => l_resp_appl_id);

   COMMIT;

   --
   -- Set the Layout  for BI Publisher Report
   --

   l_set_layout :=
      fnd_request.add_layout (template_appl_name   => 'XXERP',
                              template_code        => 'XXORACLEERPAPPSGUIDE',
                              --Data Template Code
                              template_language    => 'en',
                              template_territory   => 'US',
                              output_format        => 'PDF');

   IF l_set_layout
   THEN
      -- Submit the Request

      l_request_id :=
         fnd_request.submit_request (application   => 'XXERP',
                                     program       => 'XXOEAG_PG',
                                     description   => '',
                                     start_time    => SYSDATE,
                                     sub_request   => FALSE,
                                     argument1     => l_person_id);

      COMMIT;

      IF l_request_id > 0
      THEN
         --
         --waits for the request completion
         --

         l_wait_for_request :=
            fnd_concurrent.wait_for_request (request_id   => l_request_id,
                                             interval     => 60,
                                             max_wait     => 0,
                                             phase        => l_phase,
                                             status       => l_status,
                                             dev_phase    => l_dev_phase,
                                             dev_status   => l_dev_status,
                                             MESSAGE      => l_messase);

         COMMIT;

         --
         -- Get the Request Completion Status.
         --
         l_get_request_status :=
            fnd_concurrent.get_request_status (
               request_id       => l_request_id,
               appl_shortname   => NULL,
               program          => NULL,
               phase            => l_phase,
               status           => l_status,
               dev_phase        => l_dev_phase,
               dev_status       => l_dev_status,
               MESSAGE          => l_messase
            );

         --
         --Check the status if It IS completed Normal Or Not
         --
         IF UPPER (l_dev_phase) != 'COMPLETED'
            AND UPPER (l_dev_status) != 'NORMAL'
         THEN
            RAISE request_completion_abnormal;
         END IF;
      ELSE
         RAISE request_submission_failed;
      END IF;
   ELSE
      RAISE Output_layout_failed;
   END IF;

   p_request_id := l_request_id;
EXCEPTION
   WHEN Output_layout_failed
   THEN
      DBMS_OUTPUT.put_line ('Out put Layout failed');
   WHEN request_submission_failed
   THEN
      DBMS_OUTPUT.put_line ('Concurrent request submission failed');
   WHEN request_completion_abnormal
   THEN
      DBMS_OUTPUT.put_line (
         'Submitted request completed with error' || l_request_id
      );
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR:' || SUBSTR (SQLERRM, 0, 240));
END;

/

You can Create this as PL/SQL Procedure and register into Concurrent Program also.

Concat rows values into single column

Concat rows values into single column

SQL>  select rtrim(xmlagg(xmlelement(e, empno || ',')).extract('//text()').extract('//text()') ,',') empnos from emp
/
EMPNOS                                                                        
--------------------------------------------------------------------------------
7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934  

Oracle Apps: Check file version

Oracle Apps: Check file version

Check version of the File
Two ways to check version of file.
1. Adident
2. Strings
ADIDENT Utility in ORACLE application is used to find version of any file.
1st Method
Syntax : adident Header filename.
e.g If you want to find out the version of appvndrb.pls
$ cd AP_TOP/patch/115/sql
$ adident Header appvndrb.pls
o/p :
appvndrb.pls:
$Header appvndrb.pls 120.78.12010000.83 2010/04/27 21:00:55 vinaik ship
2nd Method :
Using Strings:
Syntax : strings -a Top_name/location of file/filename | grep ‘$Header’
e.g $ strings -a $AP_TOP/patch/115/sql/appvndrb.pls | grep ‘$Header’
o/p : /* $Header: appvndrb.pls 120.78.12010000.83 2010/04/27 21:00:55 vinaik ship $ */
OR
$ cd $AP_TOP/patch/115/sql
$ strings -a appvndrb.pls | grep ‘$Header’

REP-3000: Internal error starting Oracle Toolkit

No reports (PDF/XML) were running and it gave the below error in the log file.

 REP-3000: Internal error starting Oracle Toolkit

To resolve this issue Follow the below steps and it worked-out well.
1. Open the custom report in the Report Builder
2. Go to the property pallette of the Report
3. Remove the xml prologue value
4. Save the changes and move the report to server
5. Retest the issue

Status code and Phase code from fnd_concurrent_requests FND_CONCURRENT_REQUESTS

Status code and Phase code from fnd_concurrent_requests
FND_CONCURRENT_REQUESTS

STATUS_CODE Column:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting.

PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running