Friday, 27 April 2018

Punchout in Oracle iProcurement

Punchout in Oracle iProcurement  

Punchout is an Oracle standard functionality to purchase an item directly from supplier’s website by a requester.In this feature, the supplier catalogue contains latest product information, price, picture and other details since it is maintained by the Supplier itself. Hence it will help the requester to find the latest item to fulfill his requirement.
In this document, we are discussing the setup steps required to configure “Punchout from Oracle iProcurement Directly to Supplier-Hosted Catalog (cXML)” type of punch-out only. (Type 2b as per oracle note id : A92190-03)

Benefit of this Feature:

· Helps the user
to identify the appropriate product directly in supplier Site
Latest information of the product
Less maintenance of catalogue for the procurement organization
Alternative options when the required item is not available   

Step1: Set the required profile option

We have to set  below profile options at site level to start the process.  
1. POR : Proxy Server Name
Set the Proxy server name if you are using proxy setup. DBA team will be able to help to provide this information.

2. POR: Proxy Server Port
Set the Proxy port name if you are using proxy setup. DBA team will be able to help to provide this information.

3. POR: CA Certificate file Name:
If your supplier uses secured site to access, get the security certificate from your supplier and store it in the server.  Provide the path to access the security certificate here to access by Oracle. 

4. POR: Transparent Punchout Timeout Limit
Set the time in seconds after which you want transparent punchout catalogs to time out if the search takes longer than this time to complete.

5. POR: Default Currency Conversion Rate Type

Set the Default currency conversion rate type  as Corporate or Spot if the punchout supplier is using different currency other than the functional currency of the organization.

Step2: Setup Punchout item category:

Create PO Item category which can be used for oracle punchout in Oracle Purchasing and add the same in category set.
 Navigation : Inventory > Items > Categories > Category Codes

Structure Name
PO Item Category
Office Supplies
Inactive On

View able by Supplier

 Navigation : Inventory > Items > Categories > Category Sets

Step3: Category Mapping in iProcurement:

Map the PO item category to a Shopping category
 Navigation : iProcurement catalogue Administration > Schema > Category Mapping


Purchasing Category Name
Office Supplies
Mapped to Shopping Category
Office Supplies

Step4: Define UOM and UOM Conversion:

Set the required Unit of Measure and Conversion to the unit of measure required for punchout items.
 Navigation : inventory > Setup > Unit of Measure > Unit of Measure
Base Unit

  Inactive On

Click on Conversion to add conversion.

Step5: Define Punchout Content Zone:

Create Punchout content zone using iProcurement catalogue administration responsibility
 Navigation : iProcurement Catalogue Administration> Stores > Content Zone >


US XYZ Punchout
US XYZ Content Zone
Direct Supplier Punchout (cXML)
XYZ ( Obtain it from the Punch out Supplier)
Key 1
USXYZ ( Obtain it from the Punch out Supplier)
Supplier Site
Supplier Name
Punchout URL

Step5: Define Punchout Store
Setup Punchout Stores and add the Punchout content zone in this store.

Step6: Define Code Conversion mapping

Map  Oracle Item category with the Supplier UNSPSC code ( Universal Item category used for category) to recognize the  supplier’s item category in Oracle.
Also Map the Supplier Unit of measure and our Unit of measure to get the supplier UOM to during punchout process.

Obtain the External code (UNSPSC code) and UOM from punchout supplier. The direction should be “IN” since the requested item is inbound information. Use the same KEY1 information which is used in Punchout content zone definition.
Add All required item categories and UOM mapping and save the setup

Navigation : e-Commerce Gateway: Setup > Code Conversion > Define Code Conversion Values

Step7: Punchout link in iProcurement

Once the above setup is completed, the punchout link will appear in the iProcurement screen.   
When the user  clicks on the link, System redirects the page to supplier website.  Once the user added the required item in the supplier site cart and checks out, system brings the item to Oracle shopping cart page.  Now, user can perform remaining actions in Oracle iProcurement to complete the transaction.

The Punchout supplier page opens in the in the browser as below and user can browse site and select the item.

Thursday, 26 April 2018

Personalization at Adjustment/Sales order/Credit Memo application form

This component can be used, for requirement related to personalization where we want to restrict the user to create RMA/Adjustment/Credit memo if certain(user defined) conditions are not met.

Personalization at Transaction/Adjustment Form

Responsibilty: Receivables

Form       : Transaction

This personalization will restrict the users to create adjustments against the invoices depending on the condition.

Condition:   'N'=(SELECT NVL(ATTRIBUTE15 ,'N')FROM
AND TRX_DATE>'03-APR-2018')

Personalization at sales order form for RMA creation

Responsibility: Order Management

Form       : Sales Order

This personalization will restrict the users to create RMA against the invoices depending on the condition

Condition : 'N'=(Select NVL(b.attribute15,'N')
  from ra_customer_trx_all b
 where b.customer_trx_id=:Line.return_attribute1
and b.trx_date>'03-APR-2018') and (:ORDER.ORDER_TYPE LIKE '%C/n' or :ORDER.ORDER_TYPE LIKE '%C/N')

Personalization at Transaction/Application Form

Responsibilty: Receivables

Form       : Transaction

This personalization will restrict the users to create credit note memo against the invoices depending on the condition

AND TRX_DATE>'16-FEB-2018')

Monday, 23 April 2018

Create AR Transaction/Invoice adjustment(API)

Scope: To apply the adjustment in AR Invoice. (eg. To make AR Invoice amount roundoff).

  • Before RoundOff adjustment API.

Applying Adjustment through API.


up_adj_rec              ar_adjustments%rowtype;
up_api_name             VARCHAR2(20);
up_api_version          NUMBER;
up_called_from          VARCHAR2(10);
up_check_amount         VARCHAR2(1);
up_chk_approval_limits  VARCHAR2(1);
up_commit_flag          VARCHAR2(1);
up_init_msg_list        VARCHAR2(1);
up_move_deferred_tax    VARCHAR2(10);
up_msg_count            NUMBER;
up_msg_data             VARCHAR2(2000);
up_new_adjust_id        ar_adjustments.adjustment_id%type;
up_new_adjust_number    ar_adjustments.adjustment_number%type;
up_old_adjust_id        ar_adjustments.adjustment_id%type;
up_return_status        VARCHAR2(5);
up_validation_level     NUMBER;


 apps.mo_global.init ('AR');
 apps.mo_global.set_policy_context ('S',204);  --Pass valid Org_id 
 apps.fnd_global.apps_initialize(1318,50559,222); -- Initiliaze the application with valid value.

up_adj_rec              := NULL;
up_api_name             := NULL;
up_api_version          := 1.0;
up_called_from          := NULL;
up_check_amount         := NULL;
up_chk_approval_limits  := NULL;
up_commit_flag          := NULL;
up_init_msg_list        := FND_API.G_TRUE;
up_move_deferred_tax    := 'Y';
up_msg_count            := 0;
up_msg_data             := NULL;
up_new_adjust_id        := NULL;
up_new_adjust_number    := NULL;
up_old_adjust_id        := NULL;
up_return_status        := NULL;
up_validation_level     := FND_API.G_VALID_LEVEL_FULL;

/* api- data adjustments mapping record - start */

up_adj_rec.ACCTD_AMOUNT         := 0.09;        
up_adj_rec.ADJUSTMENT_ID        := NULL;
up_adj_rec.ADJUSTMENT_NUMBER    := NULL;
up_adj_rec.ADJUSTMENT_TYPE      := 'M';                 
up_adj_rec.AMOUNT               := 0.09;         
up_adj_rec.CREATED_BY           := -1;        
up_adj_rec.CREATED_FROM         := 'XXTEST_RCVRoundoff';
up_adj_rec.CREATION_DATE        := SYSDATE;
up_adj_rec.GL_DATE              := SYSDATE;
up_adj_rec.LAST_UPDATE_DATE     := SYSDATE;
up_adj_rec.LAST_UPDATED_BY      := -1;
--up_adj_rec.POSTING_CONTROL_ID   := -3;
up_adj_rec.SET_OF_BOOKS_ID      := 1;
up_adj_rec.STATUS               := 'A';
up_adj_rec.TYPE                 := 'CHARGES';              
up_adj_rec.PAYMENT_SCHEDULE_ID  := 561827;               
up_adj_rec.APPLY_DATE           := SYSDATE;
up_adj_rec.RECEIVABLES_TRX_ID   := 13910;        -- this is rec activity
up_adj_rec.CUSTOMER_TRX_ID      := 1263466;       --- Transaction for which adjustment is made

/*  api- data adjustments mapping record - End */

p_api_name              => up_api_name,
p_api_version           => up_api_version,
p_init_msg_list         => up_init_msg_list,
p_commit_flag           => up_commit_flag,
p_validation_level      => up_validation_level,
p_msg_count             => up_msg_count,
p_msg_data              => up_msg_data,
p_return_status         => up_return_status,
p_adj_rec               => up_adj_rec,
p_chk_approval_limits   => up_chk_approval_limits,
p_check_amount          => up_check_amount,
p_move_deferred_tax     => up_move_deferred_tax,
p_new_adjust_number     => up_new_adjust_number,
p_new_adjust_id         => up_new_adjust_id,
p_called_from           => up_called_from,
p_old_adjust_id         => up_old_adjust_id

DBMS_OUTPUT.PUT_LINE('New Adjustment Number: ' || up_new_adjust_number);
DBMS_OUTPUT.PUT_LINE('New Adjustment ID: ' || up_new_adjust_id);

IF up_msg_count >=1 THEN
FOR I IN 1..up_msg_count LOOP
dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));


  • After  Round Off Apply. The Transaction possibly looks like this below.

Here the adjustment applied successfully and transaction amount has been rounded off.