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

Group Requistion Lines during Requisition Import

When Importing requisitions from the Requisition Interface : po_requisitions_interface_all via Requisition import . Oracle will group the requisitions into 1 or multiple headers  based on Oracle grouping process Which is provided as an Input parameter in the requisition import process


This solution is desirable for Drop ship Process where business many a times needs separate requisitions for individual sales orders. This solution can be achieved by using one of the below changes , which will need a customization to populate the required fields.

• Requisitions Interface has a GROUP_CODE column for the lines which we want to be populated under the same header , same value for group code can be populated for the requisition lines which are intended to be grouped together
• If you specify the value for : REQ_NUMBER_SEGMENT1 in the PO_REQUISITIONS_INTERFACE table then the records with same value will be grouped under the same requisition header
• there is a column of batch_id in requisition interface , you can specify the batch id for the group of records you want to be grouped under same header and then Run requisition import for individual group Id's --> this will also prevent processing of any new records which might have been populated in the requisition interface between the custom code processing and submission of Requisition Import

Requistion Import

Oracle Provides an Interface through which requisition data can be imported into Oracle. this interface is widely used when implementing Drop Ship functionality but can also be used when businesses integrate different systems where requisition is raised in another system but needs to be imported into Oracle.
Depending on business need the INTERFACE_SOURCE_CODE can be populated for each data source.
Further more to control specific data sets to be processed during any run , users can give a combination of INTERFACE_SOURCE_CODE and batch_id , This will ensure only specifc data is picked up
this is helpful in the below scenario :

• Business provides data in the interface , Preprocessing need to be performed on the data and only the processed data should be imported
• the Pre-processing program should update the batch id for the processed records  and submit requisition import with the specific batch id. this will ensure only relevant records are processed

The solution prevents a scenario where due to timing issue unprocessed data get picked up for processed by standard import program