Friday, August 7, 2015

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;

Tuesday, June 23, 2015

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”







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

Monday, June 22, 2015

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

Tuesday, June 9, 2015

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;



Thursday, June 4, 2015

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';


Tuesday, May 26, 2015

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;


Thursday, May 21, 2015

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;
/


Tuesday, May 19, 2015

Query to Find Database Version in Oracle



select * from v$version;

Output:


Wednesday, May 13, 2015

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.


Tuesday, April 28, 2015

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.

Monday, April 13, 2015

How to use plsql collections in select statment

Step 1 : Declare Collection type Globally

For Example:


 Test_Coll can be declared globally as "TYPE  Test_Coll IS TABLE OF INTEGER"

Step 2 :

DECLARE

 test_coll_tab  Test_Coll;
 MyName         VARCHAR2(100);

BEGIN

test_coll_tab =  Test_Coll();
test_coll_tab.extend;
test_coll_tab(1):=1;
test_coll_tab.extend;
test_coll_tab(2):=2;
test_coll_tab.extend;
test_coll_tab(3):=3; 
test_coll_tab.extend;
test_coll_tab(4):=4;
test_coll_tab.extend;
test_coll_tab(5):=5; 

 SELECT Name
      INTO MyName
    FROM Item
 WHERE ItemId NOT IN (select * from table(test_coll_tab));

END;



Friday, April 3, 2015

How to send Concurrent Program output as an Email

There are two Methods to send Concurrent Program Output as Email

Method 1:

  • The printer styles have to be configured to execute a shell script instead of sending the output to a physical printer. 
  • The shell script then emails the concurrent program output to the intended recipient. 

Method 2: (Applicable R12.1.3 onwards)

With the R12.1.3 release, a hook to the BI Publisher Delivery Manager is available on the SRS window to send the output to IPP Printers, Email, Fax and FTP destinations.


Steps for Method 1
Step 1: Define/Query Printer Type: Define a new Printer Type or Query for an existing one. Responsibility: System Administrator Navigation: Install->Printer->Types

Step 2: Define Printer Style: Click on the ‘Style’ button in the Printer Types form (as seen in the previous screenshot). Create a new style and save it.

Step 3: Define Printer Driver: Click on ‘ Driver’ button in the Printer Types form. Specify Driver method as command. Specify the following in the arguments section: $XXCUST_TOP/bin/EMAIL.sh $PROFILES$.CONC_REQUEST_ID 30


Step 4: Mapping: Map the Printer Style and Printer Driver to the Printer Type.

Step 5: Install the Printer and map the Printer Type : Create a new printer and map the Printer Type created in the previous step.

Step 6: Attach the defined printer to the concurrent program: Navigation: Concurrent > Program > Define .Specify printer in Output section

Step 7: Running the Concurrent Program: Before clicking on the Submit button in the SRS window, specify non-zero value of no. of copies by clicking on the Options button.
This will invoke Printer.

Step 8: The sample shell script (email_conc_prog_output.sh) that is executed after the completion of the above steps is attached with the article.


Steps for Method 2

This feature is available in the new button ‘Delivery Opts’ as seen in the screenshot below.
On clicking the ‘Delivery Opts’ Button, a new form will pop up which will allow users to specific email addresses of the sender, recipient and carbon copy recipient (multiple emails can be specified separated by commas), subject of the email. This can be seen in the screenshot below.

The Email tab (as seen in the screenshot above) utilizes SMTP to communicate to a mail host which in turn sends the email to the recipients. The hostname and port of the mail host is configured via the profile values:
FND: SMTP Host and FND: SMTP Port


Friday, March 27, 2015

Oracle Apps R12 Query to Find Payment against Invoice



SELECT b.segment1 vendor_number,
       b.vendor_name vendor_name,
       c.vendor_site_code,
       c.pay_group_lookup_code,
       a.invoice_num invoice_number,
       a.invoice_date,
       a.gl_date,
       d.due_date,
       a.invoice_currency_code,
       a.invoice_amount,
       a.amount_paid,
       a.pay_group_lookup_code,
       d.payment_priority,
       (SELECT MAX (check_date)
          FROM ap_checks_all aca, ap_invoice_payments_all aip
         WHERE aca.CHECK_ID = aip.CHECK_ID AND aip.invoice_id = a.invoice_id)
          "Last Payment Made on",
          a.cancelled_date
  FROM apps.ap_invoices_all a,
       apps.ap_suppliers b,
       apps.ap_supplier_sites_all c,
       apps.ap_payment_schedules_all d,
       apps.ap_invoice_payments_all ap,
       ap_checks_all ac
WHERE     a.vendor_id = b.vendor_id
       AND a.vendor_site_id = c.vendor_site_id
       AND b.vendor_id = c.vendor_id
       AND a.invoice_id = d.invoice_id
       AND ap.invoice_id = a.invoice_id
       AND ac.CHECK_ID = ap.CHECK_ID
       and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
       AND a.org_id = 89
       and a.invoice_id= 1234
       AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
       AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')