SAOP API to Update Descriptive Flexfield on Financial entities at Oracle Fusion

Lately, we have been required to update several Financial entities' (namely Receivables and Payables)  DFFs. Our need came from the new requirement from the Israel Tax Authority for the AP and AR Invoices to our client. Every invoice will be reported LIVE to the IRS and a unique authorization code will be provided back to the client. Subsequently, Oracles' localization team provided Global DFF segments to store those codes.

Unfortunately, an EDIT part of those DFF segments was entirely missing from the corresponding entities' REST APIs. (I believe there is an improvement request already at Metalink)

Following a short search on the Web, I came upon a SOAP service Oracle provided for that very purpose:

https://docs.oracle.com/en/cloud/saas/financials/23d/oeswf/erpobjectdescriptiveflexfieldupdateservice-d16476e12.html#u30243261

Plus there is a Note at Metalink that provides more technical insight on the matter:

How to Update DFF - Descriptive Flexfields Within an Invoice using updateDffEntityDetails? (Doc ID 2482375.1)

I would like to share step-by-step implementation inside the OIC (Oracle Integration Cloud) we have been using.

1. Confirm the DFFs and Localizations' Context in your system:

Oracle established that the IRS Authorization code would be stored in GLOBAL_ATTRIBUTE2 under the VAT Reporting for Israel regional context:

2. Create a suitable connection in OIC as per Oracles documentation mentioned above:

3. The following is a basic (working!) Request for the API calls in your integrations:

AP Invoice Payload example:

<updateDffEntityDetails xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" 

......... xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">

  <nstrgmpr:Body>

    <tns:updateDffEntityDetails>

      <tns:operationMode>SINGLE</tns:operationMode>

      <tns:object>

        <ns1:EntityName>Payables Invoice</ns1:EntityName>

        <ns1:ContextValue>JE_IL_VAT_REPORTING</ns1:ContextValue>

        <ns1:UserKeyA>1302</ns1:UserKeyA>

        <ns1:UserKeyD>300000026329373</ns1:UserKeyD>

        <ns1:DFFAttributes>{"GLOBAL_ATTRIBUTE2":"234233333333333"}</ns1:DFFAttributes>

      </tns:object>

    </tns:updateDffEntityDetails>

  </nstrgmpr:Body>

</updateDffEntityDetails>

According to Oracles documentation, currently, only the SINGLE mode is supported. Context is an absolute MUST - provide a correct code, if no context is defined use #NULL. 

***UserKeysA and UserKeysD are invoice_number and invoice_id accordingly. 

Make sure to follow the exact syntax as above for DFFAttributes, should you need to update more than one attribute, list them within the brackets separated by a comma: {"ATTRIBUTE1":"BlahBlah", .., "ATTRIBUTE(N)":"BlahBlah"}.

***Following the issue we have been facing lately - multiple AP Invoices bearing the same Invoice_Number (invoices originated from different vendors). Oracle provided more specific instructions regarding data passed to API as per SR 3-36709718271 I logged at Oracle Support:

Please follow the template below for Invoices with the same number:

====

User Key Details for the Payables Invoice Entity Name.

User Key

Details

User Key A Invoice Number *

User Key B Business Unit Name **

User Key C Supplier Name **

User Key D Supplier Number **

User Key E Supplier Site Name **

User Key F Invoice Identifier (Invoice_Id)

User Key G null

User Key H null

* Mandatory

** Needed if there are multiple invoices with the same number

To stay on the SAFE side - populate the Keys from A to E:

<nstrgmpr:Body>

<tns:updateDffEntityDetails>

<tns:operationMode>SINGLE</tns:operationMode>

<tns:object>

<ns1:EntityName>Payables Invoice</ns1:EntityName>

<ns1:ContextValue>JE_IL_VAT_REPORTING</ns1:ContextValue>

<ns1:UserKeyA>Invoice_Number</ns1:UserKeyA>

<ns1:UserKeyB>Organization BU</ns1:UserKeyB>

<ns1:UserKeyC>Vendor Name</ns1:UserKeyC>

<ns1:UserKeyD>Vendor Number</ns1:UserKeyD>

<ns1:UserKeyE>Vendor Site</ns1:UserKeyE>

<ns1:DFFAttributes>{"GLOBAL_ATTRIBUTE2":"2842888888888888"}</ns1:DFFAttributes>

</tns:object>

</tns:updateDffEntityDetails>

</nstrgmpr:Body>

It generally throws exceptions if something is amiss but the best indication of a successful execution is result =1 in Response:

<ns0:updateDffEntityDetailsResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">

  <result xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">1</result>

</ns0:updateDffEntityDetailsResponse>

Naturally, the same syntax would work for the Receivables Invoice entity as well.

enjoy

Reservations Processing in R12.1.3 from OAF by using INV_RESERVATION_PUB API

hi All,

Lately I been tasked by our client to develop OAF page to manage Inventory Reservations from Delivery Details screen. Delivery Detail Id is passed as parameter to all regions displayed one OAF page.

I made a simple Form Personalization to have the custom page opened from Action Menu:

The only thing worth mentioning regarding the personalization is the syntax for Form Function submit:

















Following OAF form displays various unique information to support reservations processing that is relevant only for this particular client:

Back to our topic: by manipulating values in Qty column on any line at Lots Available for Reservation table user would be able to Create, Update or Delete reservations of the corresponding Lot for Delivery Detail Line passed to this page as a parameter.

I decided to make this post for two reasons:

First: Most of the similar post tend to mention only Reservation creation from Inventory source and rarely from Order Management.

Second: It took me a while to have a CORRECT minimum number of  the parameters in place to them running successfully. (none of the posts I encountered had it right!) 

Bellow are the short scripts for every one of the main operations from INV_RESERVATION_PUB API.

* make sure to provide mtl_sales_orders.sales_order_id as demand_source_header_id to "glue" the reservation to corresponding Sales Order:

select mso.sales_order_id 

into l_order_header_id       

from mtl_sales_orders mso

     ,oe_order_headers_all ooha

     ,oe_transaction_types_tl ttl

where ooha.order_number      = <corresponding sales order number>

and   mso.segment1           = ooha.order_number

and   mso.segment2           = ttl.name

and   ttl.language           = 'US'

and   ooha.order_type_id     = ttl.transaction_type_id;     

1. CREATE_RESERVATION

DECLARE

   p_rsv                     inv_reservation_global.mtl_reservation_rec_type;

   p_dummy_sn                inv_reservation_global.serial_number_tbl_type;

   x_msg_count               NUMBER;

   x_msg_data                VARCHAR2(240);

   x_rsv_id                  NUMBER;

   x_dummy_sn                inv_reservation_global.serial_number_tbl_type;

   x_status                  VARCHAR2(1);

   x_qty                     NUMBER;

BEGIN

   fnd_global.apps_initialize(2116,50641,660);

   p_rsv.requirement_date            := Sysdate;

   p_rsv.organization_id             := 101; --mtl_parameters.organization id

   p_rsv.inventory_item_id           := 258;--mtl_system_items.Inventory_item_id;

   p_rsv.demand_source_type_id       := inv_reservation_global.g_source_type_oe; -- which is 2

   p_rsv.demand_source_name          := NULL;

   p_rsv.demand_source_header_id     := l_order_header_id;--1334166 ; --mtl_sales_orders.sales_order_id

   p_rsv.demand_source_line_id       := 48067;--4912468 ; -- oe_order_lines.line_id

   p_rsv.primary_uom_code            := 'SLB';

   p_rsv.primary_uom_id              := NULL;

   p_rsv.reservation_uom_code        := 'SLB';

   p_rsv.reservation_uom_id          := NULL;

   p_rsv.reservation_quantity        := 1;

   p_rsv.primary_reservation_quantity := 1;

   p_rsv.lot_number                  := '1187341A';--p_lot_number;

   p_rsv.locator_id                  :=   null;

   p_rsv.supply_source_type_id       := inv_reservation_global.g_source_type_inv;

   p_rsv.ship_ready_flag             := NULL;

   p_rsv.primary_uom_id              := NULL;

   p_rsv.reservation_uom_id          := NULL;

   p_rsv.subinventory_code           :=  '102';

   p_rsv.subinventory_id             :=  NULL;

   p_rsv.attribute15                 :=   NULL ;

   p_rsv.attribute14                 :=   NULL ;       

   p_rsv.attribute13                 :=   NULL ;

   p_rsv.attribute12                 :=   NULL ;

   p_rsv.attribute11                 :=   NULL ; 

   p_rsv.attribute10                 :=   NULL ;

   p_rsv.attribute9                  :=   NULL ;     

   p_rsv.attribute8                  :=   NULL ; 

   p_rsv.attribute7                  :=   NULL ;

   p_rsv.attribute6                  :=   NULL ; 

   p_rsv.attribute5                  :=   NULL ;      

   p_rsv.attribute4                  :=   NULL ;  

   p_rsv.attribute3                  :=   NULL ;

   p_rsv.attribute2                  :=   NULL ;  

   p_rsv.attribute1                  :=   NULL ;  

   p_rsv.attribute_category          :=   NULL ;

   p_rsv.lpn_id                      :=   NULL ;

   p_rsv.pick_slip_number            :=   NULL ;

   p_rsv.lot_number_id               :=   NULL ;

   p_rsv.revision                    :=   NULL ;

   p_rsv.external_source_line_id     :=   NULL ;     

   p_rsv.external_source_code        :=   NULL ;     

   p_rsv.autodetail_group_id         :=   NULL ;    

   p_rsv.reservation_uom_id          :=   NULL ;    

   p_rsv.primary_uom_id              :=   NULL ;   

   p_rsv.demand_source_delivery      :=   NULL ;

   p_rsv.supply_source_line_detail   :=   NULL;

   p_rsv.supply_source_name          :=   NULL;

   p_rsv.supply_source_header_id     :=   NULL; 

   p_rsv.supply_source_line_id       :=   NULL;   

     

   inv_reservation_pub.create_reservation

   (

        p_api_version_number       =>       1.0

      , x_return_status            =>       x_status

      , x_msg_count                =>       x_msg_count

      , x_msg_data                 =>       x_msg_data

      , p_rsv_rec                  =>       p_rsv

      , p_serial_number            =>       p_dummy_sn

      , x_serial_number            =>       x_dummy_sn

      , x_quantity_reserved        =>       x_qty

      , x_reservation_id           =>       x_rsv_id

      

   );

   dbms_output.put_line('Return status    = '||x_status);

   dbms_output.put_line('msg count        = '||to_char(x_msg_count));

   dbms_output.put_line('msg data         = '||x_msg_data);

   dbms_output.put_line('Quantity reserved = '||to_char(x_qty));

   dbms_output.put_line('Reservation id   = '||to_char(x_rsv_id));

   IF x_msg_count >=1 THEN

     FOR I IN 1..x_msg_count

     LOOP

       dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));

       fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));

     END LOOP;

     

   END IF;

COMMIT;

END;

/

2. UPDATE_RESERVATION

DECLARE

     l_rsv_old   inv_reservation_global.mtl_reservation_rec_type;

     l_rsv_new   inv_reservation_global.mtl_reservation_rec_type;

      l_msg_count NUMBER;

      l_msg_data  VARCHAR2(240);

      l_rsv_id    NUMBER;

     l_dummy_sn  inv_reservation_global.serial_number_tbl_type;

     l_status    VARCHAR2(1);

      l_quantity_reserved NUMBER;

   BEGIN

      fnd_global.apps_initialize(2116,50641,660);

 -- could be "located" by order_header_id, order_line_id, item and warehouse - in that case several reservations could be updated

      l_rsv_old.reservation_id               := 69956;

      -- specify the new values

      l_rsv_new.reservation_id               := 69956;

      l_rsv_new.primary_reservation_quantity := 10;

      l_rsv_new.requirement_date             := Sysdate+2;

      

      inv_reservation_pub.update_reservation(      

          p_api_version_number        => 1.0

         , p_init_msg_lst              => fnd_api.g_true

         , x_return_status             => l_status

         , x_msg_count                 => l_msg_count

         , x_msg_data                  => l_msg_data

         , p_original_rsv_rec          => l_rsv_old

         , p_to_rsv_rec                => l_rsv_new

         , p_original_serial_number    => l_dummy_sn -- no serial contorl

         , p_to_serial_number         => l_dummy_sn -- no serial control

         , p_validation_flag           => fnd_api.g_true

         , p_check_availability        => fnd_api.g_false

         , p_over_reservation_flag     => 0

         );

      IF l_status = fnd_api.g_ret_sts_success THEN

         dbms_output.put_line('reservation updated');

     ELSE

        IF l_msg_count >=1 THEN

           FOR I IN 1..l_msg_count    LOOP

             dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));

             --fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));

           END LOOP; 

        END IF;

     END IF;

COMMIT;

END;

/

3. DELETE_RESERVATION (decided not to use relieve_reservation since secondary quantity is not used )

DECLARE

      l_rsv       inv_reservation_global.mtl_reservation_rec_type;

      l_msg_count NUMBER;

      l_msg_data  VARCHAR2(240);

      l_rsv_id    NUMBER;

      l_dummy_sn  inv_reservation_global.serial_number_tbl_type;

      l_status    VARCHAR2(1);

BEGIN

      fnd_global.apps_initialize(2116,50641,660);

      l_rsv.reservation_id               := 95377;

      /*l_rsv.organization_id            := 101;

      l_rsv.inventory_item_id            := 258;

      l_rsv.demand_source_type_id        := inv_reservation_global.g_source_type_oe; -- order entry

      l_rsv.demand_source_header_id      := 76069; --oe_order_headers.header_id

      l_rsv.demand_source_line_id        := 48067; --oe_order_lines.line_id

      l_rsv.supply_source_type_id        := inv_reservation_global.g_source_type_inv;

      l_rsv.subinventory_code            := '102';*/

     inv_reservation_pub.delete_reservation

       (

          p_api_version_number        => 1.0

         , p_init_msg_lst              => fnd_api.g_true

         , x_return_status             => l_status

         , x_msg_count                 => l_msg_count

         , x_msg_data                  => l_msg_data

         , p_rsv_rec                   => l_rsv

         , p_serial_number => l_dummy_sn

         );

     IF l_status = fnd_api.g_ret_sts_success THEN

         dbms_output.put_line('reservation deleted');

     ELSE

        IF l_msg_count >=1 THEN

           FOR I IN 1..l_msg_count    LOOP

             dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));

             --fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));

           END LOOP; 

        END IF;

     END IF;

     COMMIT;

END;

/

enjoy,
Felix

WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY() versus Context Sensitive Descriptive Flexfields

hi,
Lately, I been working on a piece that should populate "Additional Delivery Information" DFF for "local" context (88) and was surprised to see that attributes hadn't been updated.

Methodology:  Custom mobile EBS form updates WSH_NEW_DELIVERIES table attributes - corresponding Oracle API is being called by MWA classes via JDBC connection.

Issue: After API call relevant attributes updated ONLY for Global Data Elements context.

Reference Field for this DFF is ORG_ID, so naturally, while calling this API to allocate Attributes 12 & 13, I would expect them to be saved within "local" Context 88.

x_delivery_info         apps.wsh_deliveries_pub.Delivery_Pub_Rec_Type;

      select tt.name into l_delivery_name
      from wsh_new_deliveries tt 
      where tt.delivery_id = to_number(p_delivery_id);
      
      x_delivery_info.attribute12 := VALUE1;
      x_delivery_info.attribute13 := VALUE2;
      x_delivery_info.delivery_id := to_number(p_delivery_id);
      x_delivery_info.NAME := l_delivery_name;
      x_delivery_info.last_update_date := SYSDATE;
      x_delivery_info.last_updated_by := fnd_global.user_id;
      apps.WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY(
                           p_api_version_number => 1.0,
                           p_init_msg_list => l_init_msg_list,
                           x_return_status => xx_return_status,
                           x_msg_count => xx_msg_count,
                           x_msg_data => xx_msg_data,
                           p_action_code => 'UPDATE',
                           p_delivery_info => x_delivery_info,
                           x_delivery_id => x_delivery_number,
                           x_name => x_delivery_name);


I been working with Oracles APIs for quite some time and kinda, got used to the fact that ATTRIBUTE_CATEGORY update is "built in" (I been told its mater of the setup though).

Anyway, in this particular case YOU MUST provide the value to the record to have it working correctly:

x_delivery_info.attribute_category := fnd_profile.value('ORG_ID');


Hopefully 2 minutes of reading   save you some time.

Take care,