Query to Derive Customer Number, Site Use ID from Customer Bank Account Number

SELECT hca.account_number, hcsu.site_use_id, hca.cust_account_id INTO l_customer_number, l_cust_site_use_id, l_cust_account_id FROM hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, IBY_ACCOUNT_OWNERS IAO, IBY_EXT_BANK_ACCOUNTS IEBA WHERE hca.cust_account_id = hcas.cust_account_id AND hca.party_id = iao.account_owner_party_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.org_id = AND hca.status = 'A' AND hcas.status = 'A' AND hcsu.status = 'A' AND hcas.bill_to_flag = 'P' AND NVL (IAO.end_date, SYSDATE + 1) > SYSDATE AND ieba.bank_account_num = AND iao.ext_bank_account_id = ieba.ext_bank_account_id;

Read More

Oracle Apps : Steps to Implement Employee/Supervisor Approval Hierarchy

Purchasing-> Setup-> Organization-> Financial Options

Uncheck “Use Approval Hierarchies”

Purchasing-> Setup-> Purchasing-> Document Types

Click the update icon for specific document as in our case “Standard Purchase Order”

“Owner Can Approve” should be Unchecked.
“Default Hierarchy” should be left blank.

HRMS Manager-> People-> Enter and Maintain-> Find->Assignments

Select Supervisor Tab and select Supervisor

Purchasing-> View-> Request-> ”Fill Employee Hierarchy”

Oracle Apps : Steps to Implement Employee/Supervisor Approval Hierarchy
Reviewed by Anurag Jainon Jul 07 1995
Rating: Your-Rating

Read More

Oracle Apps : Steps to implement Position Hierarchies

Create a User

System Administrator -> Security -> User

Assign Purchasing and HRMS Responsibility

Create Employee

HRMS Manager-> People-> Enter and Maintain ->New

Click Assignment Button to Assign Job and Position

Assign Job and Position

Assign Employee to User

Go to System Administrator -> Security - > User

If Employee is not a buyer, We will get a pop-up as below

Press “Add Buyer” Button to make an employee a buyer

Go to Purchasing->Setup->Personnel-> Buyer

To remove an employee as buyer Query the employee name and End Date it

Now Create a Purchase Order Purchasing- > Purchase Orders -> Purchase Orders

Enter the PO Details

Assigned User’s employee will be defaulted as Buyer

Press “Approve” Button to approve the PO

Document Types in Purchasing

Purchasing-> Setup-> Purchasing-> Document Types

“Owner Can Approve” Option if checked and employee is assigned to a Approval Group which has no limits to approve the PO created then created PO will get approved.

Setup Steps

Uncheck the “Owner Can Approve” box in Document Types under Standard Purchase Order
Purchasing-> Setup-> Purchasing -> Document Types

Assign the Default Hierarchy that needs to be followed in Document Types under Standard Purchase Order
Purchasing-> Setup-> Purchasing-> Document Types

Go to Financial Option and check “Use Approval Hierarchy”
Purchasing-> Setup-> Organization-> Financial Options

Position Hierarchy in HRMS Manager should have a correct assignments as required.
HRMS Manager ->Work Structures ->Position ->Hierarchy

Superiors approval Limit should be good enough to approve the Purchase Order created.

Defining Position Hierarchy

• Purchasing-> Setup-> Purchasing->Document Types
• “Owner Can Approve” Option Uncheck “Default Hierarchy” should be assigned
• “Forward Method” if it should directly go to the next level employee who has the authority to approve the select “Direct” else select “Hierarchy”

Purchasing-> Setup-> Organization-> Financial Options

“Use Approval Hierarchy” box should be checked

Read More

Oracle Apps : Apply AP Invoice Hold using API

The hold codes can be extracted from query below.

SELECT *
  FROM ap_hold_codes
 WHERE hold_type = 'INVOICE HOLD REASON';

Below is the API to apply hold using Standard Oracle API :


PROCEDURE inv_hold (p_invoice_id        IN     NUMBER,
                    p_reject_code       IN     VARCHAR2,
                    p_hold_val_status   IN     VARCHAR2,
                    x_status               OUT VARCHAR2)
IS
   l_hold_invoice_id   NUMBER := NULL;
   l_hold_err_msg      VARCHAR2 (2000) := NULL;
BEGIN
   l_hold_invoice_id := p_invoice_id;

   IF     l_hold_invoice_id IS NOT NULL
      AND p_reject_code IS NOT NULL
      AND p_hold_val_status = 'Y'
   THEN
      BEGIN
         ap_holds_pkg.insert_single_hold (
            X_invoice_id         => l_hold_invoice_id,
            X_hold_lookup_code   => p_reject_code);

         x_status := 'S';
      EXCEPTION
         WHEN OTHERS
         THEN
            l_hold_err_msg :=
                  'Unknow Exception Occured in Hold API call.. Exception Code : '
               || SQLCODE
               || ' Exception Details : '
               || SQLERRM;
            DBMS_OUTPUT.put_line (l_hold_err_msg);
      END;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END inv_hold;

Read More

Query to Find Attached Template Name from Template Code

SELECT template_code, file_name
  FROM xdo_templates_b a, xdo_lobs b
 WHERE     b.lob_code = a.template_code
       AND b.xdo_file_type = 'RTF'
       AND a.template_code = 'XXXBITEMPLATE';

Read More

Query to drill down from PO header to shipment

SELECT poll.*
  FROM po_headers_all poh,
       po_releases_all pora,
       po_lines_all pla,
       po_line_locations_all poll
 WHERE     poh.po_header_id = pora.po_header_id
       AND pla.po_line_id = poll.po_line_id
       AND poll.po_release_id = pora.po_release_id
       AND poh.segment1 = :po_number
       AND pora.release_num = :po_release_num
       AND pla.line_num = :po_line_num
       AND poh.org_id = :org_id;

Read More

How To Disable/End Date/ Delete Responsibilities of a User from Backend/API

Below is the query.
Please not Responsibilities cannot be deleted in Oracle Apps from a User. It can only be Disabled or End Dated.


BEGIN
   FOR I
      IN (SELECT fu.user_id,
                 fu.user_name,
                 furg.RESPONSIBILITY_APPLICATION_ID,
                 frv.RESPONSIBILITY_NAME,
                 frv.RESPONSIBILITY_KEY,
                 fa.application_short_name,
                 frg.security_group_key
            FROM FND_USER fu,
                 FND_USER_RESP_GROUPS furg,
                 FND_RESPONSIBILITY_VL frv,
                 fnd_application fa,
                 fnd_security_groups frg
           WHERE     fu.user_id = furg.user_id
                 AND furg.responsibility_id = frv.responsibility_id
                 AND frv.application_id = fa.application_id
                 AND frv.data_group_id = frg.security_group_id
                 AND fu.user_name = 'ABC'
                 AND frv.RESPONSIBILITY_NAME IN ('Receivables Support'))
   LOOP
      BEGIN
         DBMS_OUTPUT.put_line (
               i.user_name
            || i.application_short_name
            || i.responsibility_key
            || i.security_group_key);
         fnd_user_pkg.delresp (username         => i.user_name,
                               resp_app         => i.application_short_name,
                               resp_key         => i.responsibility_key,
                               security_group   => i.security_group_key);
         DBMS_OUTPUT.put_line (
            i.RESPONSIBILITY_NAME || ' End Dated Successfully ');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error encountered while deleting responsibilty from the user and the error is '
         || SQLERRM);
END;
/

Read More

Query to Find Database Version in Oracle

select * from v$version;

Output:

Read More

FND DEBUG / FND LOG MESSAGES

What is FND Debug Log ?

1. It helps you pinpoint the cause of error in standard Oracle Code, by making debug messages to appear in a centralized table named FND_LOG_MESSAGES. 
2. You can design and build your custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code. 

 A program written in any technology, either form, report, PL/SQL, java concurrent program, OAF has their debug message stored in FND_LOG_MESSAGES.

How does this work? 

How to use FND debug Log to pinpoint the error?

Step 1: Set up profiles for the User / Responsibility to be used to reproduce the issue

FND: Debug Log Enabled -> YES This turns the debugging feature on
FND: Debug Log Filename -> NULL Use when you want debug messages to get stored to a file FND: Debug Log Level -> STATEMENT
FND: Debug log module -> %
If the error pertains to a specific module use the application short name.
Ex: For receivable use FND: Debug log module -> ar%

Log into the Oracle application

Step 2: Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows :

SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGE 

Step 3: Run the test case, avoiding extraneous steps to avoid stacking the table.

Step 4:

a) For a concurrent process:

  SELECT LOG.module, LOG.MESSAGE_TEXT MESSAGE
    FROM fnd_log_messages LOG, fnd_log_transaction_context con
   WHERE     request_id = <Conc Req ID>
         AND con.transaction_type = 'REQUEST'
         AND con.transaction_context_id = LOG.transaction_context_id
ORDER BY LOG.log_sequence;


b) For any other debugging:

SELECT module, MESSAGE_TEXT
  FROM fnd_log_messages
 WHERE log_sequence = (SELECT MAX (LOG_SEQUENCE) FROM FND_LOG_MESSAGE);


 Obtaining the log messages via Oracle Application:

1) Query Profile: %FND%DEBUG%

2) Set the FND: Debug Log Enabled: Yes

3) System Administrator > Help > Debug Logging > View

4) set the values and run any concurrent program
5) Then navigate to the above screen and download the complete fnd log messages report by exporting as a CSV.

Read More

Debug : javax.servlet.http.httpservletrequest cannot be resolved

You can do either of the following to resolve this error.

• Right-click the project tab, click Properties.
• Choose Build Path -> Java Build Path
• Choose Add External JARs
• Browse to find and select servlet-api.jar which should be present in ...\apache-tomcat-7.0.61\lib\
• Click OK to update the build path.

OR
you can simply copy the servlet-api.jar present in  ...\apache-tomcat-7.0.61\lib\ and copy that jar files into lib folder, which is in WEB-INF. then just clean and built your project, your errors will be solved.

Read More