Script For Order Management (API)

create or replace package comms_om_new is

  
  -- Purpose : Deal with order management process 
  PROCEDURE main(
    itemtype  in varchar2,
    itemkey   in varchar2,
    actid     in number,
    funcmode  in varchar2,
    resultout in out varchar2);   

  PROCEDURE book(x_return_status   OUT  VARCHAR2,
                 x_msg_count       OUT  NUMBER,
                 x_msg_data        OUT  VARCHAR2);     

  PROCEDURE auto_full(errbuf        OUT  VARCHAR2,
                      retcode       OUT  VARCHAR2,
                      p_line_id      IN  NUMBER,
                      p_user_id      IN  NUMBER                      
                      );
  
  PROCEDURE pick_release(x_return_status   OUT  VARCHAR2,
                         x_msg_count       OUT  NUMBER,
                         x_msg_data        OUT  VARCHAR2);
  
  PROCEDURE ship_confirm(x_return_status   OUT  VARCHAR2,
                         x_msg_count       OUT  NUMBER,
                         x_msg_data        OUT  VARCHAR2,
                         p_delivery_id     IN   NUMBER);
                         
  PROCEDURE submit_req;

  PROCEDURE create_install_base(p_line_id         IN NUMBER, 
                                x_return_status  OUT VARCHAR2, 
                                x_msg_count      OUT VARCHAR2, 
                                x_msg_data       OUT VARCHAR2);
                                
  PROCEDURE create_install_base_rel(p_line_id         IN NUMBER, 
                                    x_return_status  OUT VARCHAR2, 
                                    x_msg_count      OUT VARCHAR2, 
                                    x_msg_data       OUT VARCHAR2);
                                                                  
                                  
  PROCEDURE expire_install_base(x_return_status   OUT  VARCHAR2,
                                x_msg_count       OUT  NUMBER,
                                x_msg_data        OUT  VARCHAR2);

  PROCEDURE deal_install_base(x_return_status   OUT  VARCHAR2,
                              x_msg_count       OUT  NUMBER,
                              x_msg_data        OUT  VARCHAR2);
                                                                
  
end comms_om_new;
/
create or replace package body comms_om_new
is

  g_header_id      NUMBER;
  g_order_number   NUMBER;

  g_line_id        NUMBER;
  g_line_ordered_quantity  NUMBER;
  g_line_schedule_date     DATE;

  g_user_id        NUMBER := fnd_profile.value('USER_ID'); 
  g_delivery_id    NUMBER; 
  g_cur_party_id          NUMBER;  
  g_cur_party_account_id  NUMBER;  
  
  --for WF calling
  PROCEDURE main(
    itemtype  in varchar2,
    itemkey   in varchar2,
    actid     in number,
    funcmode  in varchar2,
    resultout in out varchar2)
  AS
    l_return_status  VARCHAR2(1000);
    l_msg_count      NUMBER;
    l_msg_data       VARCHAR2(1000);
    l_book_flag      VARCHAR2(1);
    l_picked_flag    VARCHAR2(1);
    l_line_type      NUMBER;
    req_id           NUMBER;
    l_link_to_line_id NUMBER;
    
  BEGIN
  
     g_line_id := to_number(itemkey);
     g_user_id := wf_engine.GetItemAttrNumber(itemtype,itemkey, 'USER_ID');
     g_user_id := 1050;
     
     IF (funcmode = 'RUN') THEN
         
         SELECT ooha.header_id,
                ooha.order_number,
                oola.line_type_id,
                oola.link_to_line_id                     
           INTO g_header_id,
                g_order_number,
                l_line_type,
                l_link_to_line_id
           FROM oe_order_headers_all  ooha,
                oe_order_lines_all    oola
          WHERE ooha.header_id = oola.header_id
            AND oola.line_id = g_line_id
            AND ROWNUM = 1;

         --1033  STRORDER, 1034 ChangePlan, 1036 Suspension
         --for child item no need to deal
         IF l_line_type IN (1033, 1034, 1036) AND
            l_link_to_line_id IS NULL
         THEN         

            req_id := fnd_request.submit_request('ONT',      --application 
                                                  'TBFAFL',  --program
                                                  NULL,      --description
                                                  NULL,      --start_time
                                                  FALSE,     --sub_request
                                                  g_line_id,g_user_id,chr(0),'','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','',
                                                  '','','','','','','','','','');                           
         END IF;  --for transation_type 
         resultout := 'COMPELTE:COMPLETE';
         RETURN;
         
     END IF;    
     
     IF (funcmode = 'CANCEL') THEN
         resultout := 'COMPELTE';
         RETURN;
     END IF;    
     
     resultout := 'COMPLETE:COMPLETE';
     OE_STANDARD_WF.Clear_Msg_Context;
     return;     
     
  EXCEPTION
     WHEN OTHERS THEN
          WF_CORE.CONTEXT('comms_om_new', 'main', itemtype, itemkey, to_char(actid), funcmode );       
          RAISE;  
  END;
  
  
  PROCEDURE book(x_return_status   OUT  VARCHAR2,
                 x_msg_count       OUT  NUMBER,
                 x_msg_data        OUT  VARCHAR2)
  AS       

     CURSOR cur_line IS
     SELECT line_id,             
            ordered_quantity,
            schedule_ship_date
       FROM oe_order_lines_all
      WHERE line_id = g_line_id; 
  
     i                       NUMBER:=0;
     X_DEBUG_FILE            VARCHAR2(100); 

     --IN Parameters
     l_header_rec             OE_ORDER_PUB.Header_Rec_Type;
     l_action_request_tbl     OE_ORDER_PUB.Request_Tbl_Type; 
     l_line_tbl               OE_ORDER_PUB.Line_Tbl_Type;
     --OUT Parameters          
     x_header_rec             OE_ORDER_PUB.Header_Rec_Type; 
     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_tbl               OE_ORDER_PUB.Line_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; 
                   
  BEGIN
       
     oe_debug_pub.initialize;       
     X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('TABLE'); 
     oe_debug_pub.SetDebugLevel(1); 
     oe_msg_pub.initialize;  
  
     fnd_file.put_line(fnd_file.log, 'g_header_id!' || g_header_id); 
     
     fnd_global.apps_initialize( g_user_id, 21623, 660,NULL);  --(user_id, resp_id, app_id, NULL)                                                                       
                                    
     l_header_rec           := Oe_Order_Pub.G_Miss_Header_Rec; 
     l_header_rec.header_id := g_header_id; 
     l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE; 
     
     l_action_request_tbl(1).request_type := oe_globals.g_book_order; 
     l_action_request_tbl(1).entity_code  := oe_globals.g_entity_header; 
     l_action_request_tbl(1).entity_id    := g_header_id; 

     i := 0;
     FOR row_line IN cur_line
     LOOP   
        i := i+1;            
        l_line_tbl(i)                    := oe_order_pub.g_miss_line_rec; 
        l_line_tbl(i).line_id            := row_line.line_id;
        l_line_tbl(i).shipped_quantity   := row_line.ordered_quantity; 
        l_line_tbl(i).schedule_ship_date := g_line_schedule_date;
        l_line_tbl(i).operation          := oe_globals.g_opr_update; 
     END LOOP;   

     Oe_Order_Pub.Process_Order 
     ( 1 
     , Fnd_Api.G_FALSE 
     , Fnd_Api.G_FALSE 
     , Fnd_Api.G_FALSE 
     , x_return_status 
     , x_msg_count 
     , x_msg_data 
     --IN PARAMETERS 
     , 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 => 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 => 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 => x_action_request_tbl 
     );   
    fnd_file.put_line(fnd_file.log, 'Success:  Booked');     
    COMMIT;
  EXCEPTION   
     WHEN OTHERS THEN
          ROLLBACK;
          fnd_file.put_line(fnd_file.log, 'Failed:  Booked'); 
          x_return_status := SQLERRM;
  END book;


  PROCEDURE auto_full(errbuf         OUT VARCHAR2,
                      retcode        OUT VARCHAR2,
                      p_line_id      IN NUMBER,
                      p_user_id      IN NUMBER
                      )
  AS
    l_return_status    VARCHAR2(1000);
    l_msg_count        NUMBER;
    l_msg_data         VARCHAR2(1000); 
    l_picked_flag      VARCHAR2(1);
    l_line_type        NUMBER; 
    l_cfg_start_date   DATE;  
    l_bom_item_type_id NUMBER;
    l_child_count      NUMBER; 
  BEGIN
     g_line_id := p_line_id;  
     g_user_id := p_user_id;
     
     BEGIN
        SELECT msib.bom_item_type,
               oola.header_id,
               oola.schedule_ship_date
          INTO l_bom_item_type_id,
               g_header_id,
               g_line_schedule_date
          FROM mtl_system_items_b  msib,
               oe_order_lines_all  oola
         WHERE msib.inventory_item_id = oola.inventory_item_id
           AND oola.line_id = g_line_id
           AND ROWNUM = 1;      
     EXCEPTION
        WHEN OTHERS THEN
             l_bom_item_type_id := 1;             
     END;      
        
     l_cfg_start_date := sysdate;            
     IF l_bom_item_type_id=1 THEN
        <<wait_for_config>>
        SELECT COUNT(*)
          INTO l_child_count
          FROM oe_order_lines_all oola
         WHERE oola.link_to_line_id = g_line_id; 

         IF l_child_count=0 AND (sysdate-l_cfg_start_date)<1/24/6
         THEN
            --DBMS_LOCK.SLEEP(3);
            GOTO wait_for_config;
            INSERT INTO comms_log values('waiting');
            commit;
         END IF;         
     END IF; --for bom module

     book(l_return_status, l_msg_count, l_msg_data );       
                 
     pick_release(l_return_status, l_msg_count, l_msg_data);  

     submit_req;    
       
     deal_install_base(l_return_status, l_msg_count, l_msg_data) ; 

  END;                          
  
  
  PROCEDURE pick_release(x_return_status   OUT  VARCHAR2,
                         x_msg_count       OUT  NUMBER,
                         x_msg_data        OUT  VARCHAR2)
  AS
  
    CURSOR cur_delivery_details IS
    SELECT delivery_detail_id
      FROM wsh_delivery_details
     WHERE source_line_id = g_line_id
        OR top_model_line_id = g_line_id;

    p_api_version_number    NUMBER :=1.0;
    init_msg_list           VARCHAR2(200);
    x_msg_details           VARCHAR2(3000);
    x_msg_summary           VARCHAR2(3000);     

    p_line_rows             WSH_UTIL_CORE.ID_TAB_TYPE;
    x_del_rows              WSH_UTIL_CORE.ID_TAB_TYPE;
    l_ship_method_code      VARCHAR2(100);
    i                       NUMBER; 
    l_commit                VARCHAR2(30);       
    p_delivery_id           NUMBER;
    p_delivery_name         VARCHAR2(30);
    x_trip_id               VARCHAR2(30);
    x_trip_name             VARCHAR2(30); 
    fail_api                EXCEPTION;  
    l_picked_flag           VARCHAR2(10);
    
    l_return_status    VARCHAR2(1000);
    l_msg_count        NUMBER;
    l_msg_data         VARCHAR2(1000); 
            
  BEGIN

    --Initialize (user_id, resp_id, app_id, NULL)     
    fnd_global.apps_initialize( g_user_id, 21623, 660, NULL);  
    -- Initialize return status
    x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;    
    
    i := 0;
    FOR row_delivery_details IN cur_delivery_details
    LOOP
       p_line_rows(1) := row_delivery_details.delivery_detail_id;
        
       WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES(   
               p_api_version_number     => 1.0,              
               p_init_msg_list          => APPS.FND_API.G_TRUE,              
               p_commit                 => l_commit,              
               x_return_status          => x_return_status,              
               x_msg_count              => x_msg_count,              
               x_msg_data               => x_msg_data,              
               p_line_rows              => p_line_rows,              
               x_del_rows               => x_del_rows );
       IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
          RAISE fail_api;
       ELSE
          fnd_file.put_line(fnd_file.log, 'Success:  Auto create delivery');     
       END IF;         

       -- Pick release.
       p_delivery_id := x_del_rows(1);
       p_delivery_name := TO_CHAR( x_del_rows(1) );
       g_delivery_id := p_delivery_id;
        
       wsh_deliveries_pub.delivery_action( 
            p_api_version_number => 1.0 , 
            p_init_msg_list => null, -- IN VARCHAR2, 
            x_return_status => x_return_status, -- OUT VARCHAR2, 
            x_msg_count => x_msg_count, -- OUT NUMBER, 
            x_msg_data => x_msg_data, -- OUT VARCHAR2, 
            p_action_code => 'PICK-RELEASE', -- IN VARCHAR2, 
            p_delivery_id => p_delivery_id, -- IN NUMBER DEFAULT NULL, 
            p_delivery_name => p_delivery_name, -- IN VARCHAR2 DEFAULT NULL, 
            x_trip_id => x_trip_id, -- OUT VARCHAR2, 
            x_trip_name => x_trip_name -- OUT VARCHAR2 
            ); 
            
       IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
          RAISE fail_api;
       ELSE
          fnd_file.put_line(fnd_file.log, 'Success:  Pick release'); 
       END IF;     

       --for pick confirm
       COMMIT; 
              
       <<wait_for_pick2>>    
       BEGIN
          SELECT wdd.released_status
            INTO l_picked_flag
            FROM wsh_delivery_details wdd
           WHERE wdd.delivery_detail_id = row_delivery_details.delivery_detail_id;
       EXCEPTION
          WHEN OTHERS THEN
               l_picked_flag := 'Y';
       END;     
       IF l_picked_flag<>'Y' THEN
          GOTO wait_for_pick2;
       END IF;
     
       ship_confirm(l_return_status, l_msg_count, l_msg_data, p_delivery_id);       
                   
    END LOOP; --row_delivery_details
    
  EXCEPTION
     WHEN fail_api THEN
            WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);
            IF x_msg_count > 1 THEN
               x_msg_data := x_msg_summary || x_msg_details;
            ELSE
               x_msg_data := x_msg_summary || x_msg_details;
            END IF;  
  END pick_release;                          
  

  PROCEDURE ship_confirm(x_return_status   OUT  VARCHAR2,
                         x_msg_count       OUT  NUMBER,
                         x_msg_data        OUT  VARCHAR2,
                         p_delivery_id     IN   NUMBER)
  IS
    -- Standard Parameters.
    p_api_version_number    NUMBER :=1.0;
    init_msg_list           VARCHAR2(200);
    x_msg_details           VARCHAR2(3000);
    x_msg_summary           VARCHAR2(3000);
    p_validation_level      NUMBER;

    -- Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
    p_delivery_name         VARCHAR2(30);
    p_action_code           VARCHAR2(15);
    p_asg_trip_id           NUMBER;
    p_asg_trip_name         VARCHAR2(30);
    p_asg_pickup_stop_id    NUMBER;
    p_asg_pickup_loc_id     NUMBER;
    p_asg_pickup_loc_code   VARCHAR2(30);      
    p_asg_pickup_arr_date   DATE;
    p_asg_pickup_dep_date   DATE;
    p_asg_dropoff_stop_id   NUMBER;
    p_asg_dropoff_loc_id    NUMBER;
    p_asg_dropoff_loc_code  VARCHAR2(30);
    p_asg_dropoff_arr_date  DATE;
    p_asg_dropoff_dep_date  DATE;
    p_sc_action_flag        VARCHAR2(10);
    p_sc_intransit_flag     VARCHAR2(10);
    p_sc_close_trip_flag    VARCHAR2(10);
    p_sc_create_bol_flag    VARCHAR2(10);
    p_sc_stage_del_flag     VARCHAR2(10);
    p_sc_trip_ship_method   VARCHAR2(30);
    p_sc_actual_dep_date    VARCHAR2(30);
    p_sc_report_set_id      NUMBER;
    p_sc_report_set_name    VARCHAR2(60);
    p_wv_override_flag      VARCHAR2(10);
    x_trip_id               VARCHAR2(30);
    x_trip_name             VARCHAR2(30);

    /*Handle exceptions*/
    fail_api                EXCEPTION;

    X_DEBUG_FILE            VARCHAR2(100); 
    l_ship_method_code      VARCHAR2(100);
     
BEGIN

    --Initialize (user_id, resp_id, app_id, NULL)     
    fnd_global.apps_initialize( g_user_id, 21623, 660, NULL);  
    -- Initialize return status
    x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;        
       
    -- Ship Confirming    
    p_delivery_name := TO_CHAR(p_delivery_id);
    BEGIN
      SELECT shipping_method_code
        INTO l_ship_method_code
        FROM oe_order_headers_all
       WHERE header_id = g_header_id;    
    EXCEPTION
      WHEN OTHERS THEN
           l_ship_method_code := NULL;
    END;   
    p_action_code := 'CONFIRM'; -- The action code for ship confirm
    p_sc_action_flag := 'S'; -- Ship entered quantity.
    p_sc_intransit_flag := 'Y'; -- In transit flag is set to 'Y' closes the pickup stop and sets the delivery in transit.
    p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
    p_sc_trip_ship_method := l_ship_method_code;  -- The ship method code
          
    WSH_DELIVERIES_PUB.Delivery_Action( 
          p_api_version_number => 1.0, 
          p_init_msg_list => init_msg_list, 
          x_return_status => x_return_status, 
          x_msg_count => x_msg_count, 
          x_msg_data => x_msg_data, 
          p_action_code => p_action_code, 
          p_delivery_id => p_delivery_id, 
          p_delivery_name => p_delivery_name, 
          p_asg_trip_id => p_asg_trip_id, 
          p_asg_trip_name => p_asg_trip_name, 
          p_asg_pickup_stop_id => p_asg_pickup_stop_id, 
          p_asg_pickup_loc_id => p_asg_pickup_loc_id, 
          p_asg_pickup_loc_code => p_asg_pickup_loc_code, 
          p_asg_pickup_arr_date => p_asg_pickup_arr_date, 
          p_asg_pickup_dep_date => p_asg_pickup_dep_date, 
          p_asg_dropoff_stop_id => p_asg_dropoff_stop_id, 
          p_asg_dropoff_loc_id => p_asg_dropoff_loc_id, 
          p_asg_dropoff_loc_code => p_asg_dropoff_loc_code, 
          p_asg_dropoff_arr_date => p_asg_dropoff_arr_date, 
          p_asg_dropoff_dep_date => p_asg_dropoff_dep_date, 
          p_sc_action_flag => p_sc_action_flag, 
          p_sc_intransit_flag => p_sc_intransit_flag, 
          p_sc_close_trip_flag => p_sc_close_trip_flag, 
          p_sc_create_bol_flag => p_sc_create_bol_flag, 
          p_sc_stage_del_flag => p_sc_stage_del_flag,
          p_sc_trip_ship_method => p_sc_trip_ship_method, 
          p_sc_actual_dep_date => p_sc_actual_dep_date, 
          p_sc_report_set_id => p_sc_report_set_id, 
          p_sc_report_set_name => p_sc_report_set_name, 
          p_wv_override_flag => p_wv_override_flag, 
          x_trip_id => x_trip_id, 
          x_trip_name => x_trip_name); 
    IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
       RAISE fail_api;
    ELSE
       fnd_file.put_line(fnd_file.log, 'Success:  Ship confirm');        
    END IF;         
   
    COMMIT;    
  EXCEPTION
     WHEN fail_api THEN
            WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);
            IF x_msg_count > 1 THEN
               x_msg_data := x_msg_summary || x_msg_details;
            ELSE
               x_msg_data := x_msg_summary || x_msg_details;
            END IF;
  END SHIP_CONFIRM;

   
   
  PROCEDURE submit_req
  AS
    req_id  NUMBER;    
  BEGIN       
     --submit Process transaction interface  
     req_id := fnd_request.submit_request('INV',      --application 
                                          'INCTCM',  --program
                                           NULL,      --description
                                           NULL,      --start_time
                                           FALSE,     --sub_request
                                           chr(0), '','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','');
     fnd_file.put_line(fnd_file.log, 'Submit request: Process transaction interface');
                                                 
     --submit WIP Move Transaction Manager  
     req_id := fnd_request.submit_request('WIP',      --application 
                                          'WICTMS',  --program
                                           NULL,      --description
                                           NULL,      --start_time
                                           FALSE,     --sub_request
                                           chr(0), '','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','',
                                           '','','','','','','','','','');  
     fnd_file.put_line(fnd_file.log, 'Submit request: WIP Move Transaction Manager');  
     
  END submit_req;


  PROCEDURE create_install_base(p_line_id         IN NUMBER, 
                                x_return_status  OUT VARCHAR2, 
                                x_msg_count      OUT VARCHAR2, 
                                x_msg_data       OUT VARCHAR2)
  AS
     l_instance_rec             csi_datastructures_pub.instance_rec;
     l_ext_attrib_values_tbl    csi_datastructures_pub.extend_attrib_values_tbl;
     l_party_tbl                csi_datastructures_pub.party_tbl;
     l_account_tbl              csi_datastructures_pub.party_account_tbl;
     l_pricing_attrib_tbl       csi_datastructures_pub.pricing_attribs_tbl;
     l_org_assignments_tbl      csi_datastructures_pub.organization_units_tbl;
     l_asset_assignment_tbl     csi_datastructures_pub.instance_asset_tbl;
     l_txn_rec                  csi_datastructures_pub.transaction_rec;
     
     l_inventory_item_id        NUMBER;
     l_org_id                   NUMBER;
     l_ordered_quantity         NUMBER;
     l_uom                      VARCHAR2(10);     

     l_instance_id              NUMBER;
     l_instance_party_id        NUMBER;
     l_ip_account_id            NUMBER;
     l_order_number             NUMBER;
     l_sold_to_org_id           NUMBER;
     l_ship_to_org_id           NUMBER;
     l_invoice_to_org_id        NUMBER;
     l_cur_party_id             NUMBER;
     l_cust_account_id          NUMBER;
     l_ship_to_site_id          NUMBER;
     l_invoice_to_site_id       NUMBER;
     l_user_party_id            NUMBER;
     
      cursor cur_user(p_party_id IN NUMBER) is 
      select hr.object_id party_id    
        FROM hz_relationships  hr        
       where hr.subject_id = p_party_id
         AND hr.relationship_code = 'DOCUMENT_USER'
         AND hr.relationship_type = 'USERS';        

     
  BEGIN
  
     SELECT oola.inventory_item_id,
            oola.org_id,
            oola.ordered_quantity,
            oola.order_quantity_uom,
            oola.ship_to_org_id,
            oola.invoice_to_org_id,
            ooha.order_number,
            ooha.sold_to_org_id
       INTO l_inventory_item_id,
            l_org_id,
            l_ordered_quantity,
            l_uom,
            l_ship_to_org_id,
            l_invoice_to_org_id,
            l_order_number,
            l_sold_to_org_id
       FROM oe_order_lines_all oola,
            oe_order_headers_all ooha
      WHERE oola.line_id = p_line_id
        AND oola.header_id = ooha.header_id;         
     
     SELECT hpsu.party_site_id
       INTO l_ship_to_site_id
       FROM hz_party_site_uses hpsu
      WHERE hpsu.party_site_use_id = l_ship_to_org_id;      

     SELECT hpsu.party_site_id
       INTO l_invoice_to_site_id
       FROM hz_party_site_uses hpsu
      WHERE hpsu.party_site_use_id = l_invoice_to_org_id;      
       
     SELECT csi_item_instances_s.nextval
       INTO l_instance_id
       FROM dual;
       
     l_instance_rec.instance_id := l_instance_id;
     l_instance_rec.instance_number := l_instance_id;
     l_instance_rec.external_reference := l_order_number;
     l_instance_rec.inventory_item_id := l_inventory_item_id;
     l_instance_rec.inv_master_organization_id := 86;
     l_instance_rec.mfg_serial_number_flag := 'N';
     l_instance_rec.quantity := l_ordered_quantity;         
     l_instance_rec.unit_of_measure := l_uom;
     l_instance_rec.accounting_class_code := 'CUST_PROD';
     l_instance_rec.instance_status_id := 10000;
     l_instance_rec.customer_view_flag := NULL;
     l_instance_rec.merchant_view_flag := NULL;
     l_instance_rec.sellable_flag      := NULL;
     l_instance_rec.active_start_date  := TRUNC(SYSDATE); 
     l_instance_rec.location_type_code := 'HZ_PARTY_SITES';
     l_instance_rec.location_id        := 249;
     l_instance_rec.install_date       := TRUNC(SYSDATE);
     l_instance_rec.creation_complete_flag := 'Y';
     l_instance_rec.version_label          := 'AS_CREATED';
     l_instance_rec.object_version_number  := 1;


     --get the user account id
     SELECT hca.party_id, 
            hca.cust_account_id
       INTO l_cur_party_id,
            l_cust_account_id
       FROM hz_cust_accounts hca
      WHERE hca.cust_account_id = l_sold_to_org_id;     
     
     insert into comms_log values ('cur_party_id ' || l_cur_party_id);

     
     FOR row_user IN cur_user(l_cur_party_id) LOOP
         --get user's account id
         BEGIN
           SELECT hca.cust_account_id
             INTO l_cust_account_id
             FROM hz_cust_accounts hca
            WHERE hca.party_id = row_user.party_id
              AND ROWNUM=1;
         EXCEPTION
           WHEN OTHERS THEN             
                l_cust_account_id := NULL;
         END;         

         insert into comms_log values ('user_party_id ' || row_user.party_id );         
         insert into comms_log values ('user_party_cunt_id ' || l_cust_account_id);
         
         SELECT csi_i_parties_s.nextval
           INTO l_instance_party_id
           FROM dual;
         l_party_tbl(1).instance_party_id      := l_instance_party_id;
         l_party_tbl(1).instance_id            := l_instance_id;
         l_party_tbl(1).party_source_table     := 'HZ_PARTIES'; 
         l_party_tbl(1).party_id               := row_user.party_id;
         l_party_tbl(1).relationship_type_code := 'DOCUMENT_USER';
         l_party_tbl(1).contact_flag           := 'N';
         l_party_tbl(1).active_start_date      := SYSDATE;
         l_party_tbl(1).object_version_number  := 1;

         SELECT csi_ip_accounts_s.nextval
         INTO l_ip_account_id
         FROM dual;       
         l_account_tbl(1).ip_account_id          := l_ip_account_id;       
         l_account_tbl(1).instance_party_id      := l_instance_party_id;
         l_account_tbl(1).party_account_id       := l_cust_account_id;
         l_account_tbl(1).relationship_type_code := 'DOCUMENT_USER';
         l_account_tbl(1).active_start_date      := sysdate;
         l_account_tbl(1).BILL_TO_ADDRESS       := 1170; 
         l_account_tbl(1).SHIP_TO_ADDRESS       := 1170;          
         l_account_tbl(1).object_version_number  := 1;
         l_account_tbl(1).parent_tbl_index       := 1;
         l_account_tbl(1).call_contracts         := 'Y';
     
         l_txn_rec.transaction_date              := TRUNC(SYSDATE);
         l_txn_rec.SOURCE_TRANSACTION_DATE      := TRUNC(SYSDATE);     
         l_txn_rec.TRANSACTION_TYPE_ID          := 1;      
         l_txn_rec.OBJECT_VERSION_NUMBER        := 1;    
    
         csi_item_instance_pub.create_item_instance(
                  p_api_version           => 1.0   --IN     NUMBER
                  ,p_instance_rec          => l_instance_rec  --   IN OUT NOCOPY csi_datastructures_pub.instance_rec
                  ,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl -- IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl
                  ,p_party_tbl             => l_party_tbl --IN OUT NOCOPY csi_datastructures_pub.party_tbl
                  ,p_account_tbl           => l_account_tbl --IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
                  ,p_pricing_attrib_tbl    => l_pricing_attrib_tbl --IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl
                  ,p_org_assignments_tbl   => l_org_assignments_tbl --IN OUT NOCOPY csi_datastructures_pub.organization_units_tbl
                  ,p_asset_assignment_tbl  => l_asset_assignment_tbl --IN OUT NOCOPY csi_datastructures_pub.instance_asset_tbl
                  ,p_txn_rec               => l_txn_rec --IN OUT NOCOPY csi_datastructures_pub.transaction_rec
                  ,x_return_status         => x_return_status --OUT    NOCOPY VARCHAR2
                  ,x_msg_count             => x_msg_count --OUT    NOCOPY NUMBER
                  ,x_msg_data              => x_msg_data);  --OUT    NOCOPY VARCH              
         
         insert into comms_log values('create install base ');         
     
     END LOOP;
     COMMIT;     
  END;                                
  
  PROCEDURE create_install_base_rel(p_line_id         IN NUMBER, 
                                    x_return_status  OUT VARCHAR2, 
                                    x_msg_count      OUT VARCHAR2, 
                                    x_msg_data       OUT VARCHAR2)
  AS
    l_instance_id        NUMBER;
    p_party_tbl          csi_datastructures_pub.party_tbl;

    p_txn_rec            csi_datastructures_pub.transaction_rec;
    l_i_parties_id       NUMBER;
    l_owner_party_id     NUMBER;                       
    
    cursor cur_user(p_party_id IN NUMBER) is 
    select hr.object_id party_id    
      FROM hz_relationships  hr        
     where hr.subject_id = p_party_id
       AND hr.relationship_code = 'DOCUMENT_USER'
       AND hr.relationship_type = 'USERS';        

  BEGIN
  
      SELECT cii.instance_id, cii.owner_party_id
        INTO l_instance_id, l_owner_party_id
        FROM csi_item_instances cii
       WHERE cii.last_oe_order_line_id = p_line_id;

      
      FOR row_user IN cur_user(l_owner_party_id)
      LOOP

         SELECT csi_i_parties_s.nextval
           INTO l_i_parties_id
           FROM DUAL;
           
         /*p_relationship_tbl(1).RELATIONSHIP_ID  := l_ii_rel_id;        
         p_relationship_tbl(1).RELATIONSHIP_TYPE_CODE := 'USED BY';        
         p_relationship_tbl(1).OBJECT_ID   := row_user.party_id;        
         p_relationship_tbl(1).SUBJECT_ID   := l_instance_id;        
         p_relationship_tbl(1).SUBJECT_HAS_CHILD  := 'N';       
         p_relationship_tbl(1).POSITION_REFERENCE  := NULL;        
         p_relationship_tbl(1).ACTIVE_START_DATE  := SYSDATE;        
         p_relationship_tbl(1).ACTIVE_END_DATE  := SYSDATE;        
         p_relationship_tbl(1).DISPLAY_ORDER  := NULL;        
         p_relationship_tbl(1).MANDATORY_FLAG  := 'N'; 
         p_relationship_tbl(1).OBJECT_VERSION_NUMBER := 1;       
      
         p_txn_rec.TRANSACTION_DATE := TRUNC(SYSDATE);      
         p_txn_rec.SOURCE_TRANSACTION_DATE := TRUNC(SYSDATE);      
         p_txn_rec.TRANSACTION_TYPE_ID :=1;      
         p_txn_rec.OBJECT_VERSION_NUMBER :=1; 
       */
          
        insert into csi_i_parties (
                     instance_party_id, 
                     instance_id,
                     party_source_table,
                     party_id,
                     relationship_type_code,
                     contact_flag,
                     active_start_date,
                     created_by,
                     creation_date,
                     last_updated_by,
                     last_update_date,
                     last_update_login,
                     object_version_number)
               values( l_i_parties_id,
                       l_instance_id,
                       'HZ_PARTIES',
                       row_user.party_id,
                       'USED BY',
                       'N',
                       sysdate,
                       '-1',
                       sysdate,
                       '-1',
                       sysdate,
                       '-1',
                       1
                     );
                     
           /* csi_party_relationships_pub.create_inst_party_relationship
                          ( p_api_version   =>  1.0  ,-- IN     NUMBER
                           p_commit        =>  'Y' ,--      IN     VARCHAR2
                           p_init_msg_list =>  'N' ,--     IN     VARCHAR2
                           p_validation_level => 0, --   IN     NUMBER
                           p_party_tbl        => p_party_tbl   ,--   IN OUT NOCOPY csi_datastructures_pub.party_tbl
                           p_party_account_tbl=> p_party_account_tbl   ,--   IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
                           p_txn_rec          => p_txn_rec ,--   IN OUT NOCOPY csi_datastructures_pub.transaction_rec
                           x_return_status    => x_return_status   ,--   OUT NOCOPY    VARCHAR2
                           x_msg_count        => x_msg_count  ,--   OUT NOCOPY    NUMBER
                           x_msg_data         => x_msg_data  --   OUT NOCOPY    VARCHAR2
                           ) ;*/
      END LOOP;  
      COMMIT;
  END;                                    
  
  --expire earily install base records
  PROCEDURE expire_install_base(x_return_status   OUT  VARCHAR2,
                                x_msg_count       OUT  NUMBER,
                                x_msg_data        OUT  VARCHAR2)
  AS                                 
     l_instance_rec          csi_datastructures_pub.instance_rec;
     l_txn_rec               csi_datastructures_pub.transaction_rec;
     l_instance_id_lst       csi_datastructures_pub.id_tbl;
     
     l_cust_account_id       NUMBER;
     l_party_id              NUMBER;
     l_line_type_id          NUMBER;
     
     --all install base records of earliy subscripiton
     CURSOR cur_instance(p_party_id IN NUMBER, p_party_account_id IN NUMBER) IS
     SELECT cii.instance_id,
            cii.instance_number,
            cii.inventory_item_id,
            cii.inv_master_organization_id,
            cii.active_start_date,
            cii.active_end_date            
       FROM csi_item_instances cii
      WHERE cii.owner_party_id = p_party_id
        AND cii.owner_party_account_id = p_party_account_id
        AND cii.last_oe_order_line_id <> g_line_id;
     
     no_need_expire          EXCEPTION;
     
  BEGIN

     SELECT oola.line_type_id,
            hca.cust_account_id,
            hca.party_id
       INTO l_line_type_id,
            l_cust_account_id,
            l_party_id
       FROM oe_order_lines_all    oola,
            oe_order_headers_all  ooha,
            hz_cust_accounts      hca
      WHERE oola.line_id = g_line_id
        AND oola.header_id = ooha.header_id
        AND hca.cust_account_id = ooha.sold_to_org_id
        AND ROWNUM = 1;

     --for Changeplan, expire existing item install base
     IF l_line_type_id <> 1034 THEN   
        RAISE no_need_expire;
     END IF;
     
     FOR row_instance IN cur_instance(l_party_id, l_cust_account_id)
     LOOP
         l_instance_rec.instance_id         := row_instance.instance_id;
         l_instance_rec.instance_number     := row_instance.instance_number;
         l_instance_rec.external_reference  := NULL;
         l_instance_rec.inventory_item_id   := row_instance.inventory_item_id;
         l_instance_rec.inv_master_organization_id := row_instance.inv_master_organization_id;
         l_instance_rec.active_end_date     := TRUNC(SYSDATE);

         l_txn_rec.transaction_date              := TRUNC(SYSDATE);
         l_txn_rec.SOURCE_TRANSACTION_DATE      := TRUNC(SYSDATE);     
         l_txn_rec.TRANSACTION_TYPE_ID          := 1;      
         l_txn_rec.OBJECT_VERSION_NUMBER        := 1;      
             
         csi_item_instance_pub.expire_item_instance(
            p_api_version        => 1.0 -- IN      NUMBER
           ,p_instance_rec       => l_instance_rec -- IN      csi_datastructures_pub.instance_rec
           ,p_txn_rec            => l_txn_rec -- IN OUT  NOCOPY csi_datastructures_pub.transaction_rec
           ,x_instance_id_lst    => l_instance_id_lst-- OUT     NOCOPY csi_datastructures_pub.id_tbl
           ,x_return_status      => x_return_status-- OUT     NOCOPY VARCHAR2
           ,x_msg_count          => x_msg_count-- OUT     NOCOPY NUMBER
           ,x_msg_data           => x_msg_data-- OUT     NOCOPY VARCHAR2
          );            
     END LOOP;

     COMMIT;               
  EXCEPTION
     WHEN no_need_expire THEN
          NULL;
     WHEN OTHERS THEN
          NULL;  
  END;  


  PROCEDURE deal_install_base(x_return_status   OUT  VARCHAR2,
                              x_msg_count       OUT  NUMBER,
                              x_msg_data        OUT  VARCHAR2)
  AS     
     l_cust_account_id       NUMBER;
     l_party_id              NUMBER;
     l_line_type_id          NUMBER;  
     no_need_expire          EXCEPTION; 
     l_inventory_item_id     NUMBER;   
     l_product_type          VARCHAR2(20);
     l_item_number           mtl_system_items_b.segment1%TYPE; 
     l_count                 NUMBER;
     l_telephone_num         VARCHAR2(20);
     l_sim_num               VARCHAR2(20);   

     l_return_status    VARCHAR2(1000);
     l_msg_count        NUMBER;
     l_msg_data         VARCHAR2(1000);        

     --product subscribled earlier     
     CURSOR cur_prod IS
     SELECT cii.instance_id,
            cii.last_oe_order_line_id line_id
       FROM csi_item_instances cii
      WHERE cii.last_oe_order_line_id <> g_line_id
        AND NVL(cii.attribute10,'E') = l_product_type
        AND cii.owner_party_id = l_party_id
        AND cii.owner_party_account_id = l_cust_account_id;
         
  BEGIN
     
     SELECT oola.line_type_id,
            hca.cust_account_id,
            hca.party_id,
            oola.inventory_item_id
       INTO l_line_type_id,
            l_cust_account_id,
            l_party_id,
            l_inventory_item_id
       FROM oe_order_lines_all    oola,
            oe_order_headers_all  ooha,
            hz_cust_accounts      hca
      WHERE oola.line_id = g_line_id
        AND oola.header_id = ooha.header_id
        AND hca.cust_account_id = ooha.sold_to_org_id
        AND ROWNUM = 1;

     SELECT msib.segment1
       INTO l_item_number
       FROM mtl_system_items_b msib
      WHERE msib.organization_id = 86
        AND msib.inventory_item_id = l_inventory_item_id
        AND ROWNUM = 1;
        
     IF l_item_number IN ('MOBILEPROD','MOBILECHANGEPLAN')   THEN
        l_product_type := 'M';
     ELSIF l_item_number='BRODBAND' THEN
        l_product_type := 'B';       
     ELSIF l_item_number='DIGITAL_LEASED_LINE_NATL' THEN 
        l_product_type := 'D';                   
     END IF;
       

     --get service number
     --Get customer serial number and SIM attribute in quator   
     BEGIN
            SELECT cn.service_num, cn.sim_num
              INTO l_telephone_num, l_sim_num
              FROM comms_numbers          cn,
                   aso_quote_headers_all  aqh,
                   aso_quote_lines_all    aql,
                   oe_order_lines_all     oola
             WHERE oola.line_id = g_line_id
               AND oola.header_id = aqh.order_id
               AND aql.quote_header_id = aqh.quote_header_id
               AND cn.quote_line_id = aql.quote_line_id
               AND cn.app_id        = 521
               AND ROWNUM = 1;
         EXCEPTION
            WHEN OTHERS THEN
                 l_sim_num := NULL;
                 l_telephone_num := NULL;   
     END;
     IF l_sim_num IS NULL THEN
            BEGIN
               SELECT cn.service_num, cn.sim_num
                 INTO l_telephone_num, l_sim_num
                 FROM comms_numbers cn
                WHERE cn.quote_line_id = g_line_id
                  AND cn.app_id        = 660
                  AND ROWNUM = 1;
            EXCEPTION
               WHEN OTHERS THEN
                    l_sim_num := NULL;
                    l_telephone_num := NULL;   
            END;
     END IF;   

     
     --for change plan, get service number from old subscription
     IF l_line_type_id=1034 AND l_sim_num IS NULL THEN        
        SELECT MAX(cii.serial_number), MAX(cii.attribute11)
          INTO l_telephone_num, l_sim_num
          FROM csi_item_instances cii
         WHERE cii.owner_party_id = l_party_id
           AND cii.instance_status_id = 10000;        
     END IF;
     
     --1033, Staprodline2
     --1034, ChangePlan2,
     --1036, Suspension
     --for Changeplan, expire existing item install base
     --attribute10, 'M' Mobile, 'C' Cable, 'D' Degital, 'E' Expire
     IF l_line_type_id IN (1034, 1036) THEN
        
        FOR row_prod IN cur_prod
        LOOP
            --update product subscribled earliy
            UPDATE csi_item_instances cii
               SET cii.active_end_date = TRUNC(sysdate),
                   cii.instance_status_id = 1,
                   cii.attribute10 = 'E'
             WHERE cii.instance_id = row_prod.instance_id;
            
            --update product lines at the same order 
            UPDATE csi_item_instances cii
               SET cii.active_end_date = TRUNC(sysdate),
                   cii.instance_status_id = 1,
                   cii.attribute10 = 'E'
             WHERE cii.last_oe_order_line_id IN (
                     SELECT oola.line_id
                       FROM oe_order_lines_all oola
                      WHERE oola.top_model_line_id = row_prod.line_id
                  );                           
        END LOOP;           
        
     END IF;
  
     --make product type for new install base
     <<wait_for_install_base>>
     SELECT COUNT(*)
       INTO l_count
       FROM csi_item_instances  cii
      WHERE cii.last_oe_order_line_id = g_line_id;
     IF l_count=0 THEN
        GOTO wait_for_install_base;
     END IF;   
    
     UPDATE csi_item_instances cii
        SET cii.attribute10 = l_product_type,
            cii.serial_number = l_telephone_num,
            cii.attribute11 = l_sim_num
      WHERE cii.last_oe_order_line_id = g_line_id;
     
     --update comms_number for the lastest order line_id
     IF l_line_type_id=1034 THEN
        UPDATE comms_numbers cn
           SET cn.quote_line_id = g_line_id,
               cn.app_id = 660
         WHERE cn.service_num = l_telephone_num;               
     END IF;
          
     --for suspension order, update status 
     IF l_line_type_id=1036 THEN
        --update product lines at the same order
        <<wait_for_all_install_base>>
        SELECT COUNT(*)
          INTO l_count
          FROM oe_order_lines_all  oola,
               oe_order_lines_all  oola2
         WHERE oola.header_id = oola2.header_id
           AND oola2.line_id = g_line_id;
        IF l_count<2 THEN
            GOTO wait_for_all_install_base;
        END IF;   
         
        UPDATE csi_item_instances cii
           SET cii.instance_status_id = 10021
         WHERE cii.last_oe_order_line_id IN (
                   SELECT oola.line_id
                     FROM oe_order_lines_all  oola,
                          oe_order_lines_all  oola2
                    WHERE oola.header_id = oola2.header_id
                      AND oola2.line_id = g_line_id
                  );                           
     END IF;
      
     
     --for starprod, create a child intall base for user
     IF l_line_type_id=1033 THEN
        create_install_base_rel(g_line_id, 
                                l_return_status, 
                                l_msg_count, 
                                l_msg_data) ;                                 
     END IF; 
      
     COMMIT;
       
  EXCEPTION
     WHEN no_need_expire THEN
          NULL;
     WHEN OTHERS THEN
          NULL;    
  END;                               

END comms_om_new;
/

Comments

Popular posts from this blog

Queries For Oracle Interface Errors Records.

Customising PO Output For Communication Report in Oracle Purchasing

Oracle APPS Useful Queries