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

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;

Read More

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

Read More

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

Read More

Query to find the parent request id recursively

SELECT request_id,
  description
FROM fnd_concurrent_requests
WHERE NVL (request_type, 'X')        = 'M'
  CONNECT BY PRIOR parent_request_id = request_id
  START WITH request_id              = &RequestID;

Read More

Credit Card Validation Oracle EBS R12

Introduction

Have you ever encountered the below validation failure for credit card in EBS R12.

• Validation failed for the field - Credit Card
• Unable to set up a Credit Card Bank Account for the Customer. Credit Card information on the order is not valid.

Below is the sample block you can use to validate your credit card in Oracle EBS.


We will use standard Oracle Package and Lookup to validate them

• Package : iby_cc_validate
• Oe Lookup : Credit Card

Sample Block

SET serveroutput ON;

DECLARE
  v_fill_chars VARCHAR (3)  := '*-#';
  v_cc_number  VARCHAR (50) := '1234567812345678';
  --'4111*1111 1111-1111#';
  v_api_version   NUMBER           := 1.0;
  v_init_msg_list VARCHAR2 (20000) := ' ';
  v_return_status VARCHAR2 (20000);
  v_msg_count     NUMBER;
  v_msg_data      VARCHAR2 (20000);
  v_clean_cc      VARCHAR (50);
  v_cc_type iby_cc_validate.cctype;
  v_cc_valid   BOOLEAN;
  v_expr_date  DATE        := SYSDATE ();
  v_error_flag VARCHAR2(1) := 'N';
  
BEGIN
  -- Procedure Call to strip unwanted and special characters
  -- checks that illegal characters were not found
  
  iby_cc_validate.stripcc (v_api_version ,
                           v_init_msg_list,
                           v_cc_number,
                           v_fill_chars,
                           v_return_status,
                           v_msg_count,
                           v_msg_data,
                           v_clean_cc );
  
  
  IF v_return_status = fnd_api.g_ret_sts_unexp_error THEN
    v_error_flag    := 'Y';
  END IF;
  
  
  -- Procedure Call to validate credit Card Type
  iby_cc_validate.getcctype (v_api_version ,
                             v_init_msg_list ,
                             v_clean_cc ,
                             v_return_status ,
                             v_msg_count ,
                             v_msg_data ,
                             v_cc_type );
  
  
  IF v_return_status = fnd_api.g_ret_sts_unexp_error AND v_cc_type = iby_cc_validate.c_invalidcc THEN
    dbms_output.put_line ('Credit card number type is invalid');
    v_error_flag := 'Y';
  ELSE
    dbms_output.put_line ('Credit card Type is Valid.');
  END IF;
  
  
    -- Procedure Call to validate credit card structure
  iby_cc_validate.validatecc (v_api_version ,
                              v_init_msg_list ,
                              v_clean_cc ,
                              v_expr_date ,
                              v_return_status ,
                              v_msg_count ,
                              v_msg_data ,
                              v_cc_valid );
                              
  IF v_cc_valid AND v_return_status != fnd_api.g_ret_sts_unexp_error THEN
    dbms_output.put_line ('Credit card structure is valid.');
  ELSE
    dbms_output.put_line ('Credit card structure is invalid or has expired.');
    v_error_flag := 'Y';
  END IF;
  
 -- Functional Call to returns Credit Card Validation Results using Luhn Algorithm
  IF iby_cc_validate.CheckCCDigits(v_clean_cc) = 0 THEN
    dbms_output.put_line ('Credit card  is valid.');
  ELSE
    dbms_output.put_line ('Credit card  is invalid or has expired.');
    v_error_flag := 'Y';
  END IF;
  
  
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END;

Read More