List all Razorpay Payments in Oracle APEX

    

Razorpay Integration with Oracle APEX

The objective of this blog post is to show you how to list all Razorpay Payments (Specific to particular account) in Oracle APEX using APEX_WEB_SERVICE API.

Before we get into the topic, let's take a look at the following link, which will help you to understand how Razorpay payment gateway can be done in Oracle APEX.

1) Razorpay Payment Gateway Integration with Oracle APEX

2) Invoking a RESTful Style Web Service

List all Razorpay Payments

Below are steps involved to get this process done.

1) Get Razorpay API Keys

2) Get Razorpay Fetch Multiple Payments API End Point

3) Invoke Razorpay Fetch Multiple Payments API with Postman

4) Create a Sample Application

5) List all Razorpay Payments in the form of Report using APEX_WEB_SERVICE API

I) Get Razorpay API Keys

API key is a combination of the key_id and key_secret and is required to make any API request to Razorpay. You also have to implement the API key in your code as part of your integration process.

Step 1: Log into your Dashboard with appropriate credentials.

Step 2: Select the mode (Test or Live) which you want to generate the API Key.

 

Step 3: Navigate to Settings --> API Keys --> Generate Key to generate key for the selected mode.

The key_id and key_secret appear on a pop-up page.

II) Get Razorpay Fetch Multiple Payments API End Point

Step 1: You can get the end point from this link (Payments API)

Step 2: Search text "Fetch Multiple Payments", then you can find the fetch payments end point.

curl -u [YOUR_KEY_ID]:[YOUR_KEY_SECRET]-X 
GET https://api.razorpay.com/v1/payments

III) Invoke Razorpay Fetch Multiple Payments API with Postman

Step 1: Setup Authorization (Basic Authentication) and click "Send" button to call the API.

Username: API Key Id 

Password: API Key Secrete

Note: When status code is between 200 and 299, then API is working fine.

IV) Create a Sample Application

V) List all Razorpay Payments in the form of Report using APEX_WEB_SERVICE API

Step 1: Create a new blank page.

Step 2: Create a new region on the page (Position: Content Body). In the Property Editor, apply the following changes:

Under Identification:

     For Title - enter List Razorpay Payments

     For Type- select Classical Report/Interactive Report

Under Source:

     For Location - Local Database

     For Type - select SQL Query

     For SQL Query - Copy and paste below query.

SQL Query:

SELECT

          *

        FROM dual, 

             json_table (apex_web_service.make_rest_request(p_url => 'https://api.razorpay.com/v1/payments', 

                                                           p_http_method => 'GET',

                                                           p_username => <<Your Razorpay API Key Id>>,

                                                           p_password => <<Your Razorpay API Key Secret>>),

                        '$.items[*]' COLUMNS(id VARCHAR2(4000) path '$.id',

                                             entity VARCHAR2(4000) path '$.entity',

                                             amount NUMBER path '$.amount',

                                             amount_paid NUMBER path '$.amount_paid',

                                             currency VARCHAR2(4000) path '$.currency',

                                             order_id VARCHAR2(4000) path '$.order_id',

                                             status VARCHAR2(4000) path '$.status',

                                             invoice_id VARCHAR2(4000) path '$.invoice_id',

                                             method VARCHAR2(4000) path '$.method',

                                             amount_refunded NUMBER path '$.amount_refunded',

                                             refund_status VARCHAR2(4000) path '$.refund_status',

                                             captured VARCHAR2(4000) path '$.captured',

                                             description VARCHAR2(4000) path '$.description',

                                             card_id VARCHAR2(4000) path '$.card_id',

                                             bank VARCHAR2(4000) path '$.bank',

                                             wallet VARCHAR2(4000) path '$.wallet',

                                             vpa VARCHAR2(4000) path '$.vpa',

                                             email VARCHAR2(4000) path '$.email',

                                             contact VARCHAR2(4000) path '$.contact',

                                             fee NUMBER path '$.fee',

                                             tax NUMBER path '$.tax',

                                             error_code VARCHAR2(4000) path '$.error_code',

                                             error_description VARCHAR2(4000) path '$.error_description',

                                             error_source VARCHAR2(4000) path '$.error_source',

                                             error_step VARCHAR2(4000) path '$.error_step',

                                             error_reason VARCHAR2(4000) path '$.error_reason'))

WHERE 1=1

ORDER BY id ASC NULLS LAST;

Step 3: Report created and it will list all Razorpay Payments.

API Success Response:

{
   "entity":"collection",
   "count":2,
   "items":[
      {
         "id":"pay_G8VaL2Z68LRtDs",
         "entity":"payment",
         "amount":900,
         "currency":"INR",
         "status":"captured",
         "order_id":"order_G8VXfKDWDEOHHd",
         "invoice_id":null,
         "international":false,
         "method":"netbanking",
         "amount_refunded":0,
         "refund_status":null,
         "captured":true,
         "description":"Purchase Shoes",
         "card_id":null,
         "bank":"KKBK",
         "wallet":null,
         "vpa":null,
         "email":"gaurav.kumar@example.com",
         "contact":"+919999999999",
         "customer_id":"cust_DitrYCFtCIokBO",
         "notes":[
            
         ],
         "fee":22,
         "tax":4,
         "error_code":null,
         "error_description":null,
         "error_source":null,
         "error_step":null,
         "error_reason":null,
         "acquirer_data":{
            "bank_transaction_id":"0125836177"
         },
         "created_at":1606985740
      },
      {
         "id":"pay_G8VQzjPLoAvm6D",
         "entity":"payment",
         "amount":1000,
         "currency":"INR",
         "status":"captured",
         "order_id":"order_G8VPOayFxWEU28",
         "invoice_id":null,
         "international":false,
         "method":"upi",
         "amount_refunded":0,
         "refund_status":null,
         "captured":true,
         "description":"#G8VPNzYJsQWMvY",
         "card_id":null,
         "bank":null,
         "wallet":null,
         "vpa":"gaurav.kumar@exampleupi",
         "email":"gaurav.kumar@example.com",
         "contact":"+919999999999",
         "customer_id":"cust_DitrYCFtCIokBO",
         "notes":[
            
         ],
         "fee":24,
         "tax":4,
         "error_code":null,
         "error_description":null,
         "error_source":null,
         "error_step":null,
         "error_reason":null,
         "acquirer_data":{
            "rrn":"033814379298"
         },
         "created_at":1606985209
      }
   ]
}

Output:

The demo is here.

That's it. Happy APEXing!

Related Posts:

• Razorpay Payment Gateway Integration in Oracle APEX

References/Credits:

• Using JSON_TABLE with JSON Arrays
• Invoking a RESTful Style Web Service
• Razorpay Payments API