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


Wednesday, January 28, 2015

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;

Wednesday, November 19, 2014

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;



Tuesday, November 18, 2014

Query to find Credit Card Type in EBS R12

To Check the Valid Credit Card Type in R12. Execute the query below.


SELECT lookup_code,
      meaning,
      enabled_flag,
      start_date_active,
      end_date_active
FROM oe_lookups
WHERE lookup_type = 'CREDIT_CARD';


Sunday, November 9, 2014

Types of Invoice Class in AR Transaction

Invoice : A document that you create that lists amounts owed for the purchases of goods or services, any tax, freight charges and payment terms.

Credit Memo : A document that partially or fully reverses an original invoice.

Debit Memo : Debits that you assign to a customer to collect additional charges .

ChargeBack : A new debit item that you assign to your customer when closing an existing, outstanding debit item.

Deposit : A type of commitment whereby a customer agrees to deposit or prepay a sum of money for the future purchase of goods and services.

Guarantee : A contractual obligation to purchase a specified amount of goods or services over a predefined period of time.

Monday, October 27, 2014

Cash Flow Statements : Accounting Basics Day 9

Introduction

Cash flow statement provides considerable information about what is really happening in a business beyond that contained in either the income statement or the balance sheet. Analyzing this statement will quickly become obvious that the benefits of understanding the sources and uses of a company’s cash far outweigh the costs of undertaking some very straightforward analyses. In this chapter we are going to learn about the preparation of the cash flow statements.

What are Cash Flow Statements?

Cash flow statement, which shows inflows and outflows of the cash and cash equivalents. This statement is usually prepared by a company as a tool to know about the sources and uses of cash and cash equivalents of an enterprise over a period of time from various activities of an enterprise. It has gained substantial importance in the last decade because of its practical utility to the users of financial information.

Who Requires a Cash Flow Statement?


  • Executives require knowing if the cash generated by the company will be sufficient to fund their expansion strategy
  • Stockholders will be able to know if the firm is generating enough cash to pay dividends
  • Suppliers would be interested to know if their customers will be able to pay, on offering credit

Division of Cash Flow Statements

The cash flow statement is divided into three sections

  1. Operating activities - Converts the items reported on the income statement from the accrual basis of accounting to cash. Shows the results of cash inflows and outflows related to the fundamental operations of the basic line or lines of business in which the company engages. (Example: cash receipts from the sale of goods or services and cash outflows for purchasing inventory and paying rent and taxes.)
  2. Investing activities - Reports the purchase and sale of long-term investments and property, plant and equipment.
  3. Financing activities - Reports the issuance and repurchase of the company's own bonds and stock and the payment of dividends.

Detecting changes in cash with change in various accounts


  • When an asset (other than cash) increases, the Cash account decreases.
  • When an asset (other than cash) decreases, the Cash account increases.
  • When a liability increases, the Cash account increases.
  • When a liability decreases, the Cash account decreases.
  • When owner's equity increases, the Cash account increases.
  • When owner's equity decreases, the Cash account decreases.

The differences in a company's balance sheet accounts will provide much of the needed information. For example, if the statement of cash flows is for the year 2009, the balance sheet accounts at December 31, 2009 will be compared to the balance sheet accounts at December 31, 2008. The changes—or differences—in these account balances will likely be entered in one of the sections of the statement of cash flows.

Cash Provided From or Used By Operating Activities

Operating activities are the activities that constitute the primary or main activities of an enterprise, for example, for a company manufacturing garments, procurement of raw material, incurrence of manufacturing expenses, sale of garments, etc. These are the principal revenue producing activities (or the main activities) of the enterprise and other activities that are not investing or financing activities. By Operating Activities This section of the cash flow statement reports the company's net income and then converts it from the accrual basis to the cash basis by using the changes in the balances of current asset and current liability accounts, such as:

Accounts Receivable
Inventory
Supplies
Prepaid Insurance
Other Current Assets
Notes Payable (generally due within one year)
Accounts Payable
Wages Payable
Payroll Taxes Payable
Interest Payable
Income Taxes Payable
Unearned Revenues
Other Current Liabilities

In addition to using the changes in current assets and current liabilities, the operating activities section has adjustments for depreciation expense and for the gains and losses on the sale of long-term assets.

Cash Provided From or Used By Investing Activities

Investing activities relate to purchase and sale of long-term assets or fixed assets such as machinery, furniture, land and building, etc. Transactions related to long-term investment are also investing activities.
This section of the cash flow statement reports changes in the balances of long-term asset accounts, such as:

Long-term Investments
Land
Buildings
Equipment
Furniture & Fixtures
Vehicles
In short, investing activities involve the purchase and/or sale of long-term investments and property, plant, and equipment.

Cash provided from or used by financing activities

Financing activities relate to long-term funds or capital of an enterprise, e.g. cash proceeds from issue of equity shares, debentures, raising long-term bank loans, redemption of bank loan, etc
This section of the cash flow statement reports changes in balances of the long-term liability and stockholders' equity accounts, such as:

Notes Payable (generally due after one year) Bonds Payable
Deferred Income Taxes
Preferred Stock
Paid-in Capital in Excess of Par-Preferred Stock
Common Stock
Paid-in Capital in Excess of Par-Common Stock
Paid-in Capital from Treasury Stock
Retained Earnings
Treasury Stock

In short, financing activities involve the issuance and/or the repurchase of a company's own bonds or stock. Dividend payments are also reported in this section.
Where to enter the balance sheet changes
Below are the changes where balance sheet accounts should be entered on Statement of cash flows:


A change in this...is reported in this section
balance sheet categoryof the cash flow statement
Current Assets Operating Activities
Current Liabilities Operating Activities
Long-term Assets Investing Activities
Long-term Liabilities Financing Activities
Stockholders' EquityFinancing Activities

*This refers to current assets other than Cash.


--Coming Soon -- Preparation of Cash Flow Statement  --

Friday, October 17, 2014

Query to derive Qualifier Attribute and Context from Modifier Name

Query :

SELECT 
  c.list_line_no "Modifier Line No",
  b.name "Modifier Name",
  b.description  "Modifier Description",
  a.qualifier_context,
  a.qualifier_attribute,
  a.qualifier_attr_value
FROM qp_qualifiers_v a,
  qp_list_headers b,
  qp_list_lines c
WHERE a.list_header_id=b.list_header_id
and c.list_header_id=b.list_header_id
and c.list_line_id = a.list_line_id
and b.name = &Modifier_Name
ORDER BY a.creation_date DESC;