API to Delete Descriptive Flex Field Context in Oracle APPS

--------------------------------------------------------------------------------
--API to delete Descriptive Flex Field Context
--------------------------------------------------------------------------------
DECLARE
CURSOR c1
IS
SELECT dff.application_id
  ,dff.descriptive_flexfield_name
              ,dff_context.descriptive_flex_context_code
  FROM fnd_descriptive_flexs_vl dff
              ,fnd_descr_flex_contexts_vl dff_context
         WHERE title = 'Requisition Headers'
   AND dff.descriptive_flexfield_name = dff_context.descriptive_flexfield_name
   AND dff_context.descriptive_flex_context_code IN ('Test','Req Operating Enable')
;
BEGIN
FOR i IN c1
LOOP
BEGIN 
fnd_descr_flex_contexts_pkg.delete_row(x_application_id                => i.application_id
  ,x_descriptive_flexfield_name    => i.descriptive_flexfield_name
  ,x_descriptive_flex_context_cod  => i.descriptive_flex_context_code
  ); 
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error occured while deleting DFF Context. Error Message: '||SQLERRM);
END;

dbms_output.put_line('DFF Conetext Filed: '||i.descriptive_flex_context_code ||' deleted successfully');

END LOOP;


END;

--
--Run below query to validate deletion successfully or not
/*SELECT dff.application_id
  ,dff.descriptive_flexfield_name
              ,dff_context.descriptive_flex_context_code
  FROM fnd_descriptive_flexs_vl dff
              ,fnd_descr_flex_contexts_vl dff_context
         WHERE title = 'Requisition Headers'
   AND dff.descriptive_flexfield_name = dff_context.descriptive_flexfield_name
   AND dff_context.descriptive_flex_context_code IN ('Test','Req Operating Enable')
;
*/

-- Issue commit 

API to create Sales Order Quote in Oracle APPS

To import Quote in Order Management we can use OE_ORDER_PUB.process_order API by setting TRANSACTION_PHASE_CODE to "N".

Below is the sample code:

set serveroutput on
DECLARE
 l_header_rec              OE_ORDER_PUB.Header_Rec_Type;
 l_x_header_rec          OE_ORDER_PUB.Header_Rec_Type;
 l_line_tbl              OE_ORDER_PUB.Line_Tbl_Type;
 l_x_line_tbl              OE_ORDER_PUB.Line_Tbl_Type;
 l_action_request_tbl      OE_ORDER_PUB.Request_Tbl_Type;
 l_x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
 l_header_adj_tbl          OE_ORDER_PUB.Header_Adj_Tbl_Type;
 l_line_adj_tbl          OE_ORDER_PUB.line_adj_tbl_Type;
 l_header_scr_tbl          OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 l_line_scredit_tbl      OE_ORDER_PUB.Line_Scredit_Tbl_Type;
 l_request_rec              OE_ORDER_PUB.Request_Rec_Type ;
 l_return_status          VARCHAR2(1000);
 l_msg_count              NUMBER;
 l_msg_data              VARCHAR2(1000);
 p_api_version_number      NUMBER :=1.0;
 p_init_msg_list          VARCHAR2(10) := FND_API.G_FALSE;
 p_return_values          VARCHAR2(10) := FND_API.G_FALSE;
 p_action_commit          VARCHAR2(10) := FND_API.G_FALSE;
 x_return_status          VARCHAR2(1);
 x_msg_count              NUMBER;
 x_msg_data              VARCHAR2(100);
 p_header_rec              OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
 p_old_header_rec          OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
 p_header_val_rec          OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
 p_old_header_val_rec      OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
 p_Header_Adj_tbl          OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
 p_old_Header_Adj_tbl      OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
 p_Header_Adj_val_tbl      OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
 p_old_Header_Adj_val_tbl   OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
 p_Header_price_Att_tbl     OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
 p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
 p_Header_Adj_Att_tbl       OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
 p_old_Header_Adj_Att_tbl   OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
 p_Header_Adj_Assoc_tbl     OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
 p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
 p_Header_Scredit_tbl        OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
 p_old_Header_Scredit_tbl      OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
 p_Header_Scredit_val_tbl      OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
 p_old_Header_Scredit_val_tbl   OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
 p_line_tbl                     OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
 p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
 p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
 p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
 p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
 p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
 p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
 p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
 p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
 p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
 p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
 p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
 p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
 p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
 p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
 p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
 p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
 p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
 p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
 p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
 p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
 p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
 p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
 x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
 x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
 x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
 x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
 x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
 x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
 x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
 x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
 x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
 x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
 x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
 x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
 x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
 x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
 x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
 x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
 x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
 x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
 X_DEBUG_FILE VARCHAR2(2000);
 l_line_tbl_index NUMBER;
 l_msg_index_out NUMBER(10);
BEGIN
 dbms_output.enable(1000000);

 fnd_global.apps_initialize(0,21623,660); -- 1318 pass in user_id, responsibility_id, and application_id

 MO_GLOBAL.INIT('ONT');
 MO_GLOBAL.SET_POLICY_CONTEXT('S', 204);

 oe_debug_pub.debug_on;
 oe_debug_pub.initialize;

 X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

 oe_Debug_pub.setdebuglevel(5);
 dbms_output.put_line('File : '||X_DEBUG_FILE);
 dbms_output.put_line('START OF NEW DEBUG');

 --This is to CREATE an order header and an order line

 --Create Header record
 --Initialize header record to missing
 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
 l_header_rec.TRANSACTIONAL_CURR_CODE := 'USD';
 l_header_rec.pricing_date := SYSDATE;
 l_header_rec.cust_po_number := 'TestQuote1';
 l_header_rec.sold_to_org_id := 1005;
 l_header_rec.price_list_id := 1000;
 l_header_rec.ordered_date := SYSDATE;
 l_header_rec.shipping_method_code := 'DHL';
 l_header_rec.sold_from_org_id := 204;
 l_header_rec.salesrep_id := -3;
 l_header_rec.order_type_id := 1000;
 l_header_rec.transaction_phase_code := 'N';
 l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
 l_line_tbl_index :=1;

 -- FIRST LINE RECORD
 -- Initialize record to missing
 l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
 -- Line attributes
 l_line_tbl(l_line_tbl_index).inventory_item_id := 149;
 l_line_tbl(l_line_tbl_index).ordered_quantity := 5;
 l_line_tbl(l_line_tbl_index).ship_from_org_id := 207;
 l_line_tbl(l_line_tbl_index).subinventory := 'FGI';
 l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;
 -- CALL TO PROCESS ORDER Check the return status and then commit.

 --"TRANSACTION_PHASE_CODE"should be set to "N" while using Process Order API to import quote.

 OE_ORDER_PUB.process_order(p_api_version_number => 1.0
                           ,p_init_msg_list => fnd_api.g_false
                           ,p_return_values => fnd_api.g_false
                           ,p_action_commit => fnd_api.g_false
                           ,x_return_status => l_return_status
                           ,x_msg_count => l_msg_count
                           ,x_msg_data => l_msg_data
                           ,p_header_rec => l_header_rec
                           ,p_line_tbl => l_line_tbl
                           ,p_action_request_tbl => l_action_request_tbl
   -- OUT PARAMETERS
   ,x_header_rec => l_x_header_rec
   ,x_header_val_rec => x_header_val_rec
   ,x_Header_Adj_tbl => x_Header_Adj_tbl
   ,x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
   ,x_Header_price_Att_tbl => x_Header_price_Att_tbl
   ,x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
   ,x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
   ,x_Header_Scredit_tbl => x_Header_Scredit_tbl
   ,x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
   ,x_line_tbl => l_x_line_tbl
   ,x_line_val_tbl => x_line_val_tbl
   ,x_Line_Adj_tbl => x_Line_Adj_tbl
   ,x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
   ,x_Line_price_Att_tbl => x_Line_price_Att_tbl
   ,x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
   ,x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
   ,x_Line_Scredit_tbl => x_Line_Scredit_tbl
   ,x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
   ,x_Lot_Serial_tbl => x_Lot_Serial_tbl
   ,x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
   ,x_action_request_tbl => l_x_action_request_tbl
);
dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);

-- Retrieve messages
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get( p_msg_index => i
                        ,p_encoded => Fnd_Api.G_FALSE
                        ,p_data => l_msg_data
                        ,p_msg_index_out => l_msg_index_out
);

DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);
END LOOP;
 -- Check the return status
 IF l_return_status = FND_API.G_RET_STS_SUCCESS
 THEN
  dbms_output.put_line('Process Order Sucess');
 ELSE
  dbms_output.put_line('Failed');
 END IF;
END;
/
Commit;


You can see data in ORDER_NUMBER, QUOTE_NUMBER columns in OE_ORDER_HEADERS_ALL table .

Budgetary Control Options in Oracle General Ledger(Lookups: FUNDS_CHECK_LEVEL,PTD_YTD and BOUNDARY_TYPE)

In Budgetory Control Options form we Funds Check Level, Amount Type, Boundary and Funding Budget.

We have Advisory, Absolute and None types of Funds Check Level.

Use Funds Check level when setting budgetary control options for account ranges, for source and category combinations in budgetary control groups, and for summary account templates.

• Advisory: Use Advisory as Fund Check Level option for online notification when transactions fail funds checking. The system still reserves funds for transactions even when no funds are available.

• Absolute: Use Absolute as Fund Check Level option to prohibit you from reserving funds for a transaction unless funds are available.

• None: Use None as Fund Check Level option for no funds checking or funds reservation.

Funds Check Level is coming from Lookup Type "FUNDS_CHECK_LEVEL". Below is the query we can use.

Amount Type is coming from Lookup Type "PTD_YTD". Below is the query we can use.

Boundary is coming from Lookup Type "BOUNDARY_TYPE". Below is the query we can use.