Custom Web ADI in Oracle

Depending on business needs there are instances when data from outside needs to be imported into the system , When we have 2 IT systems working then we can make us of standard inbound interface methods to perform data import into Oracle.
However there can be instances where end user want to load bulk data into oracle for Example business might want to create Journals for Misc Expenses  for this we have inbuilt upload functionality in form of Web ADI.
However this is not available for all the functionalities , Eg : If business wants to update cost of Items , Upload data to perform misc transactions for some purposes etc. In order to facilitate these functions we can create custom Web ADI which can be used by business .


Below steps can be followed to create a custom Web ADI , we are taking an example of ADI  for Uploading item Cost

Step	Action to Perform
1	Create a custom Table to load cost
2	Create a Custom Package to process the loaded item cost
3	Register the Table in Oracle
4	Register Concurrent program for Package in Step2
5	Create a Custom Integrator
6	Create a Layout for the Integrator
7	Perform ADI Test
8	Component Migration via LDT

We will cover the details in the upcoming post

Delete Item Cost

Item cost can be Deleted using 2 options

• Item cost Screen --> the user needs to query the item cost from the item master screen and delete the cost details (cost elements) or complete item cost
• Perform clean up from item cost tables --> In this scenario when we have to perform bulk data cleanup we can create script to do bulk updates. Data a given item , warehouse and cost type needs to be deleted from the table: CST_ITEM_COSTS and CST_ITEM_COST_DETAILS , The data need to be deleted if complete cost need to be deleted. If we need to do bulk updates or delete for some cost element then we need to delete data from CST_ITEM_COST_DETAILS table and perform updates in the columns of table CST_ITEM_COSTS to ensure the item cost is consistent , as if a sub element cost is updated then the total cost should reflect the change
  

API top Update Price list

Many times for bulk processing there will be a need to update price lists for Eg , when we want to end date lines in bulk. In such a scenario we can use the API.


gpr_price_list_line_tbl (l_num_count).list_line_id :=
            list_line_rec.list_line_id;
         gpr_price_list_line_tbl (l_num_count).operation :=
            QP_GLOBALS.G_OPR_UPDATE;
         gpr_price_list_line_tbl (l_num_count).start_date_active :=
            list_line_rec.START_DATE_ACTIVE;
         gpr_price_list_line_tbl (l_num_count).end_date_active :=
            '21-SEP-2016';
         -- call to API
         QP_PRICE_LIST_PUB.Process_Price_List (
            p_api_version_number        => 1,
            p_init_msg_list             => FND_API.G_FALSE,
            p_return_values             => FND_API.G_FALSE,
            p_commit                    => FND_API.G_FALSE,
            x_return_status             => gpr_return_status,
            x_msg_count                 => gpr_msg_count,
            x_msg_data                  => gpr_msg_data,
            p_PRICE_LIST_LINE_tbl       => gpr_price_list_line_tbl,
            x_PRICE_LIST_rec            => ppr_price_list_rec,
            x_PRICE_LIST_val_rec        => ppr_price_list_val_rec,
            x_PRICE_LIST_LINE_tbl       => ppr_price_list_line_tbl,
            x_PRICE_LIST_LINE_val_tbl   => ppr_price_list_line_val_tbl,
            x_QUALIFIERS_tbl            => ppr_qualifiers_tbl,
            x_QUALIFIERS_val_tbl        => ppr_qualifiers_val_tbl,
            x_PRICING_ATTR_tbl          => ppr_pricing_attr_tbl,
            x_PRICING_ATTR_val_tbl      => ppr_pricing_attr_val_tbl);


Some of these are record type variables and some table type variables


      gpr_price_list_line_tbl       QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
      gpr_price_list_line_val_tbl   QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
      ppr_price_list_rec            QP_PRICE_LIST_PUB.Price_List_Rec_Type;
      ppr_price_list_val_rec        QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
      ppr_price_list_line_tbl       QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
      ppr_price_list_line_val_tbl   QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
      ppr_qualifiers_tbl            QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
      ppr_qualifiers_val_tbl        QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
      ppr_pricing_attr_tbl          QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
      ppr_pricing_attr_val_tbl      QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
      p_api_version_number          NUMBER := 1;
      p_init_msg_list               VARCHAR2 (1) := Fnd_Api.G_FALSE;
      p_return_values               VARCHAR2 (1) := Fnd_Api.G_FALSE;
      p_commit                      VARCHAR2 (1) := Fnd_Api.G_TRUE;


If the script is run as an anonymous block then note that API will not do an implicit commit and user needs to do a COMMIT explicitly to ensure changes are reflected in the system