API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 21st, 2020
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 11.14 KB | None | 0 0
raw download clone embed print report
  1.  
  2. SET serveroutput ON
  3. DECLARE
  4.  l_header_rec OE_ORDER_PUB.Header_Rec_Type;
  5.  l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
  6.  l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
  7.  l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
  8.  l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type;
  9.  l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
  10.  l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
  11.  l_request_rec OE_ORDER_PUB.Request_Rec_Type ;
  12.  l_return_status VARCHAR2(1000);
  13.  l_msg_count NUMBER;
  14.  l_msg_data VARCHAR2(1000);
  15.  p_api_version_number NUMBER :=1.0;
  16.  p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
  17.  p_return_values VARCHAR2(10) := FND_API.G_FALSE;
  18.  p_action_commit VARCHAR2(10) := FND_API.G_FALSE;
  19.  x_return_status VARCHAR2(1);
  20.  x_msg_count NUMBER;
  21.  x_msg_data VARCHAR2(100);
  22.  p_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
  23.  p_old_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
  24.  p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
  25.  p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
  26.  p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
  27.  p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
  28.  p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
  29.  p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
  30.  p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
  31.  p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
  32.  p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
  33.  p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
  34.  p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
  35.  p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
  36.  p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
  37.  p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
  38.  p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
  39.  p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
  40.  p_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
  41.  p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
  42.  p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type :=  OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
  43.  p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
  44.  p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
  45.  p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
  46.  p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
  47.  p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
  48.  p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
  49.  p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
  50.  p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
  51.  p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
  52.  p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
  53.  p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
  54.  p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
  55.  p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
  56.  p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
  57.  p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
  58.  p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
  59.  p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
  60.  p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
  61.  p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
  62.  p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
  63.  
  64.  x_header_rec OE_ORDER_PUB.Header_Rec_Type;
  65.  --x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
  66.  x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
  67.  x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
  68.  x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
  69.  x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
  70.  x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
  71.  x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
  72.  x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
  73.  x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
  74.  x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
  75.  x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
  76.  x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
  77.  x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
  78.  x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
  79.  x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
  80.  x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
  81.  x_line_tbl OE_ORDER_PUB.Line_Tbl_Type ;
  82.  x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
  83.  x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
  84.  x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
  85.  x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
  86.  X_DEBUG_FILE VARCHAR2(100);
  87.  l_line_tbl_index NUMBER;
  88.  l_msg_index_out NUMBER(10);
  89.  
  90. --------Custom variables
  91.  
  92. CURSOR C1 IS
  93.     SELECT ol.line_id , ol.header_id , oh.ordered_date  ,
  94.            ol.promise_date , ol.schedule_ship_date , ol.request_date    --,ol.FLOW_STATUS_CODE ,oH.FLOW_STATUS_CODE
  95.     FROM apps.oe_order_headers_all oh, apps.oe_order_lines_all ol
  96.     WHERE oh.header_id =ol.header_id
  97.     AND ol.FLOW_STATUS_CODE NOT IN ('CANCELLED','CLOSED')
  98.     AND TRUNC(oh.ordered_date) = TRUNC(ol.promise_date) ;  --14588 / 7579 /13771 /7317
  99.  
  100.  CURSOR C2 IS
  101.     SELECT ol.line_id , ol.header_id , oh.ordered_date  ,
  102.            ol.promise_date , ol.schedule_ship_date , ol.request_date    --,ol.FLOW_STATUS_CODE ,oH.FLOW_STATUS_CODE
  103.            , ol.attribute2
  104.     FROM apps.oe_order_headers_all oh, apps.oe_order_lines_all ol
  105.     WHERE oh.header_id =ol.header_id
  106.     AND ol.FLOW_STATUS_CODE NOT IN ('CANCELLED','CLOSED')
  107.     AND (OL.ATTRIBUTE5 IS NULL OR OL.ATTRIBUTE6 IS NULL) ;
  108.  
  109.  
  110. BEGIN
  111.  DBMS_OUTPUT.enable(1000000);
  112. -- fnd_global.apps_initialize(1318,21623,660); -- pass in user_id, responsibility_id, and application_id
  113. XXMLX_FND_TOOLS_PKG.init_apps_session('MLXBATCH', 'MLX_SFM_SUPERUSER');
  114.  
  115. --This is to UPDATE order line
  116.  l_line_tbl_index :=1;
  117. -- Changed attributes
  118. FOR REC IN C1 LOOP
  119.      l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
  120.      l_line_tbl(l_line_tbl_index).promise_date := rec.SCHEDULE_SHIP_DATE;
  121.     -- Primary key of the entity i.e. the order line
  122.      l_line_tbl(l_line_tbl_index).line_id := REC.line_id;
  123.  
  124.      l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;
  125.  
  126.      -- CALL TO PROCESS ORDER
  127.  OE_ORDER_PUB.process_order (
  128.   p_api_version_number => 1.0
  129.   , p_init_msg_list => fnd_api.g_false
  130.   , p_return_values => fnd_api.g_false
  131.   , p_action_commit => fnd_api.g_false
  132.   , x_return_status => l_return_status
  133.   , x_msg_count => l_msg_count
  134.   , x_msg_data => l_msg_data
  135.   , p_header_rec => l_header_rec
  136.   , p_line_tbl => l_line_tbl
  137.   , p_action_request_tbl => l_action_request_tbl
  138. -- OUT PARAMETERS
  139.   , x_header_rec => x_header_rec
  140.   , x_header_val_rec => x_header_val_rec
  141.   , x_Header_Adj_tbl => x_Header_Adj_tbl
  142.   , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
  143.   , x_Header_price_Att_tbl => x_Header_price_Att_tbl
  144.   , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
  145.   , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
  146.   , x_Header_Scredit_tbl => x_Header_Scredit_tbl
  147.   , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
  148.   , x_line_tbl => x_line_tbl
  149.   , x_line_val_tbl => x_line_val_tbl
  150.   , x_Line_Adj_tbl => x_Line_Adj_tbl
  151.   , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
  152.   , x_Line_price_Att_tbl => x_Line_price_Att_tbl
  153.   , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
  154.   , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
  155.   , x_Line_Scredit_tbl => x_Line_Scredit_tbl
  156.   , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
  157.   , x_Lot_Serial_tbl => x_Lot_Serial_tbl
  158.   , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
  159.   , x_action_request_tbl => x_action_request_tbl
  160.  );
  161.  
  162.     -- l_line_tbl_index := l_line_tbl_index+1;
  163.     COMMIT;
  164. END LOOP;
  165.  
  166.   l_line_tbl.DELETE;
  167.   l_line_tbl_index := 1;
  168.  
  169.   FOR REC IN C2 LOOP
  170.      l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
  171.      l_line_tbl(l_line_tbl_index).attribute2 := TO_CHAR(TO_DATE(rec.attribute2, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-YY');
  172.      l_line_tbl(l_line_tbl_index).attribute5 := TO_CHAR(rec.REQUEST_DATE, 'DD-MON-YY');
  173.      l_line_tbl(l_line_tbl_index).attribute6 := TO_CHAR(rec.SCHEDULE_SHIP_DATE , 'DD-MON-YY');
  174.     -- Primary key of the entity i.e. the order line
  175.      l_line_tbl(l_line_tbl_index).line_id := REC.line_id;
  176.  
  177.      l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;
  178.  
  179.       -- CALL TO PROCESS ORDER
  180.  OE_ORDER_PUB.process_order (
  181.   p_api_version_number => 1.0
  182.   , p_init_msg_list => fnd_api.g_false
  183.   , p_return_values => fnd_api.g_false
  184.   , p_action_commit => fnd_api.g_false
  185.   , x_return_status => l_return_status
  186.   , x_msg_count => l_msg_count
  187.   , x_msg_data => l_msg_data
  188.   , p_header_rec => l_header_rec
  189.   , p_line_tbl => l_line_tbl
  190.   , p_action_request_tbl => l_action_request_tbl
  191. -- OUT PARAMETERS
  192.   , x_header_rec => x_header_rec
  193.   , x_header_val_rec => x_header_val_rec
  194.   , x_Header_Adj_tbl => x_Header_Adj_tbl
  195.   , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
  196.   , x_Header_price_Att_tbl => x_Header_price_Att_tbl
  197.   , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
  198.   , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
  199.   , x_Header_Scredit_tbl => x_Header_Scredit_tbl
  200.   , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
  201.   , x_line_tbl => x_line_tbl
  202.   , x_line_val_tbl => x_line_val_tbl
  203.   , x_Line_Adj_tbl => x_Line_Adj_tbl
  204.   , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
  205.   , x_Line_price_Att_tbl => x_Line_price_Att_tbl
  206.   , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
  207.   , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
  208.   , x_Line_Scredit_tbl => x_Line_Scredit_tbl
  209.   , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
  210.   , x_Lot_Serial_tbl => x_Lot_Serial_tbl
  211.   , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
  212.   , x_action_request_tbl => x_action_request_tbl
  213.  );
  214.   --   l_line_tbl_index := l_line_tbl_index+1;
  215.   COMMIT;
  216. END LOOP;
  217. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!