使用API进行发运确认并运行Interface Trip Stop

摘自:https://blog.csdn.net/chosen1brain/article/details/46561451

  1. DECLARE  
  2.   
  3.   p_sales_order            NUMBER := 1506764295;  
  4.   p_line_number            NUMBER := 1.1;  
  5.   p_org_id                 NUMBER := 121;  
  6.   l_shipped_quantity       NUMBER := 1;  
  7.   p_api_version_number     NUMBER := 1.0;  
  8.   init_msg_list            VARCHAR2(200);  
  9.   l_commit                 VARCHAR2(30);  
  10.   x_msg_details            VARCHAR2(3000);  
  11.   x_msg_summary            VARCHAR2(3000);  
  12.   x_return_status          VARCHAR2(3);  
  13.   x_msg_count              NUMBER;  
  14.   x_msg_data               VARCHAR2(3000);  
  15.   p_validation_level       NUMBER;  
  16.   v_errbuf                 VARCHAR2(2000);  
  17.   v_retcode                VARCHAR2(20);  
  18.   v_released_status        wsh_delivery_details.released_status%TYPE;  
  19.   v_inv_interfaced_flag    wsh_delivery_details.inv_interfaced_flag%TYPE;  
  20.   v_oe_interfaced_flag     wsh_delivery_details.oe_interfaced_flag%TYPE;  
  21.   v_source_code            wsh_delivery_details.source_code%TYPE;  
  22.   v_pending_interface_flag wsh_trip_stops.pending_interface_flag%TYPE;  
  23.   l_changed_attributes     wsh_delivery_details_pub.changedattributetabtype;  
  24.   l_source_code            VARCHAR2(30) := 'OE';  
  25.   -- Parameters for WSH_DELIVERIES_PUB  
  26.   p_delivery_name           VARCHAR2(30);  
  27.   p_action_code             VARCHAR2(15);  
  28.   p_asg_trip_id             NUMBER;  
  29.   p_asg_trip_name           VARCHAR2(30);  
  30.   p_asg_pickup_stop_id      NUMBER;  
  31.   p_asg_pickup_loc_id       NUMBER;  
  32.   p_asg_pickup_loc_code     VARCHAR2(30);  
  33.   p_asg_pickup_arr_date     DATE;  
  34.   p_asg_pickup_dep_date     DATE;  
  35.   p_asg_dropoff_stop_id     NUMBER;  
  36.   p_asg_dropoff_loc_id      NUMBER;  
  37.   p_asg_dropoff_loc_code    VARCHAR2(30);  
  38.   p_asg_dropoff_arr_date    DATE;  
  39.   p_asg_dropoff_dep_date    DATE;  
  40.   p_sc_action_flag          VARCHAR2(10);  
  41.   p_sc_intransit_flag       VARCHAR2(10);  
  42.   p_sc_close_trip_flag      VARCHAR2(10);  
  43.   p_sc_create_bol_flag      VARCHAR2(10);  
  44.   p_sc_stage_del_flag       VARCHAR2(10);  
  45.   p_sc_trip_ship_method     VARCHAR2(30);  
  46.   p_sc_actual_dep_date      VARCHAR2(30);  
  47.   p_sc_report_set_id        NUMBER;  
  48.   p_sc_report_set_name      VARCHAR2(60);  
  49.   p_sc_defer_interface_flag VARCHAR2(60);  
  50.   p_sc_send_945_flag        VARCHAR2(60);  
  51.   p_sc_rule_id              NUMBER;  
  52.   p_sc_rule_name            VARCHAR2(60);  
  53.   p_wv_override_flag        VARCHAR2(10);  
  54.   p_asg_pickup_stop_seq     NUMBER;  
  55.   p_asg_dropoff_stop_seq    NUMBER;  
  56.   x_trip_id                 VARCHAR2(30);  
  57.   x_trip_name               VARCHAR2(30);  
  58.   fail_api EXCEPTION;  
  59.   x_debug_file       VARCHAR2(100);  
  60.   l_ship_method_code VARCHAR2(100);  
  61.   l_user_id          NUMBER;  
  62.   l_resp_id          NUMBER;  
  63.   l_appl_id          NUMBER;  
  64.   
  65.   CURSOR c_ord_details IS  
  66.     
  67.     SELECT DISTINCT det.source_header_number sales_order  
  68.                    ,det.org_id  
  69.                    ,det.source_line_number  
  70.                    ,det.source_header_id  
  71.                    ,det.source_line_id  
  72.                    ,det.source_header_type_name  
  73.                    ,det.inventory_item_id  
  74.                    ,det.requested_quantity  
  75.                    ,det.delivery_detail_id  
  76.                    ,(SELECT concatenated_segments  
  77.                        FROM mtl_system_items_kfv  
  78.                       WHERE inventory_item_id = det.inventory_item_id  
  79.                         AND organization_id = det.organization_id) ordered_item  
  80.                    ,det.organization_id  
  81.                    ,det.src_requested_quantity  
  82.                    ,det.shipped_quantity  
  83.                    ,del.delivery_id  
  84.                    ,del.status_code delivery_status_code  
  85.                    ,det.released_status pick_release_status  
  86.                    ,det.oe_interfaced_flag  
  87.                    ,det.inv_interfaced_flag  
  88.       FROM wsh_delivery_details     det  
  89.           ,wsh_delivery_assignments asn  
  90.           ,wsh_new_deliveries       del  
  91.      WHERE 1 = 1  
  92.        AND det.delivery_detail_id = asn.delivery_detail_id  
  93.        AND asn.delivery_id = del.delivery_id(+)  
  94.        AND det.source_header_number = p_sales_order  
  95.        AND det.source_line_number = p_line_number  
  96.        AND det.org_id = p_org_id  
  97.        AND shipped_quantity IS NULL  
  98.        AND nvl(del.status_code, 'OP') <> 'CL'  
  99.        AND det.released_status = 'Y';  
  100.   --  
  101.   v_user_id      NUMBER;  
  102.   v_resp_id      NUMBER;  
  103.   v_resp_appl_id NUMBER;  
  104.   --    
  105.   
  106. BEGIN  
  107.   --  
  108.   /* To get the user id details */  
  109.   SELECT user_id INTO v_user_id FROM fnd_user WHERE user_name = 'SETUP01';  
  110.   
  111.   /* To get the responsibility and responsibility application id */  
  112.   SELECT f.responsibility_id, f.application_id  
  113.     INTO v_resp_id, v_resp_appl_id  
  114.     FROM fnd_responsibility_tl f  
  115.    WHERE f.responsibility_name = '受注管理スーパーユーザー(SC)'  
  116.      AND f.language = 'JA';  
  117.   --  
  118.   fnd_global.apps_initialize(user_id      => v_user_id,  
  119.                              resp_id      => v_resp_id,  
  120.                              resp_appl_id => v_resp_appl_id);  
  121.   
  122.   dbms_output.put_line(v_user_id || ' ' || v_resp_id || ' ' ||  
  123.                        v_resp_appl_id);  
  124.   --  
  125.   
  126.   FOR i IN c_ord_details LOOP  
  127.     dbms_output.put_line('Initializing the Application for Shipping Transactions');  
  128.     -- Mandatory initialization for R12  
  129.     mo_global.set_policy_context('S', i.org_id);  
  130.     mo_global.init('ONT');  
  131.     -- Ship Confirming  
  132.     p_delivery_name := to_char(i.delivery_id);  
  133.     
  134.     dbms_output.put_line('Before Shipping, Calling WSH_DELIVERY_DETAILS_PUB API to Update Shipping Attributes');  
  135.     dbms_output.put_line('=============================================');  
  136.     l_changed_attributes(1).delivery_detail_id := i.delivery_detail_id;  
  137.     l_changed_attributes(1).shipped_quantity := l_shipped_quantity;  
  138.     wsh_delivery_details_pub.update_shipping_attributes(p_api_version_number => 1.0,  
  139.                                                         p_init_msg_list      => init_msg_list,  
  140.                                                         p_commit             => l_commit,  
  141.                                                         x_return_status      => x_return_status,  
  142.                                                         x_msg_count          => x_msg_count,  
  143.                                                         x_msg_data           => x_msg_data,  
  144.                                                         p_changed_attributes => l_changed_attributes,  
  145.                                                         p_source_code        => l_source_code);  
  146.     
  147.     IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN  
  148.       RAISE fail_api;  
  149.       dbms_output.put_line('Failed to Update the Shipping Attributes');  
  150.     ELSE  
  151.       dbms_output.put_line('Successfully Updated the Shipping Attributes');  
  152.     END IF;  
  153.     
  154.     BEGIN  
  155.       SELECT shipping_method_code  
  156.         INTO l_ship_method_code  
  157.         FROM oe_order_headers_all  
  158.        WHERE order_number = i.sales_order  
  159.          AND org_id = i.org_id;  
  160.     EXCEPTION  
  161.       WHEN OTHERS THEN  
  162.         l_ship_method_code := NULL;  
  163.     END;  
  164.     
  165.     p_action_code       := 'CONFIRM'-- The action code for ship confirm  
  166.     p_sc_action_flag    := 'S'-- Ship entered quantity.  
  167.     p_sc_intransit_flag := 'N';  
  168.     --In transit flag is set to 'Y' closes the pickup stop and sets the delivery in transit.  
  169.     p_sc_close_trip_flag      := 'Y'-- Close the trip after ship confirm  
  170.     p_sc_trip_ship_method     := l_ship_method_code; -- The ship method code  
  171.     p_sc_defer_interface_flag := 'Y';  
  172.     p_sc_stage_del_flag       := 'Y';  
  173.     p_sc_create_bol_flag      := 'N';  
  174.     p_wv_override_flag        := 'N';  
  175.     
  176.     -- API Call for Ship Confirmation  
  177.     dbms_output.put_line('Calling WSH_DELIVERIES_PUB to Perform Ship Confirmation');  
  178.     dbms_output.put_line('=============================================');  
  179.     
  180.     wsh_deliveries_pub.delivery_action(p_api_version_number      => 1.0,  
  181.                                        p_init_msg_list           => init_msg_list,  
  182.                                        x_return_status           => x_return_status,  
  183.                                        x_msg_count               => x_msg_count,  
  184.                                        x_msg_data                => x_msg_data,  
  185.                                        p_action_code             => p_action_code,  
  186.                                        p_delivery_id             => i.delivery_id,  
  187.                                        p_delivery_name           => p_delivery_name,  
  188.                                        p_asg_trip_id             => p_asg_trip_id,  
  189.                                        p_asg_trip_name           => p_asg_trip_name,  
  190.                                        p_asg_pickup_stop_id      => p_asg_pickup_stop_id,  
  191.                                        p_asg_pickup_loc_id       => p_asg_pickup_loc_id,  
  192.                                        p_asg_pickup_stop_seq     => p_asg_pickup_stop_seq,  
  193.                                        p_asg_pickup_loc_code     => p_asg_pickup_loc_code,  
  194.                                        p_asg_pickup_arr_date     => p_asg_pickup_arr_date,  
  195.                                        p_asg_pickup_dep_date     => p_asg_pickup_dep_date,  
  196.                                        p_asg_dropoff_stop_id     => p_asg_dropoff_stop_id,  
  197.                                        p_asg_dropoff_loc_id      => p_asg_dropoff_loc_id,  
  198.                                        p_asg_dropoff_stop_seq    => p_asg_dropoff_stop_seq,  
  199.                                        p_asg_dropoff_loc_code    => p_asg_dropoff_loc_code,  
  200.                                        p_asg_dropoff_arr_date    => p_asg_dropoff_arr_date,  
  201.                                        p_asg_dropoff_dep_date    => p_asg_dropoff_dep_date,  
  202.                                        p_sc_action_flag          => p_sc_action_flag,  
  203.                                        p_sc_intransit_flag       => p_sc_intransit_flag,  
  204.                                        p_sc_close_trip_flag      => p_sc_close_trip_flag,  
  205.                                        p_sc_create_bol_flag      => p_sc_create_bol_flag,  
  206.                                        p_sc_stage_del_flag       => p_sc_stage_del_flag,  
  207.                                        p_sc_trip_ship_method     => p_sc_trip_ship_method,  
  208.                                        p_sc_actual_dep_date      => p_sc_actual_dep_date,  
  209.                                        p_sc_report_set_id        => p_sc_report_set_id,  
  210.                                        p_sc_report_set_name      => p_sc_report_set_name,  
  211.                                        p_sc_defer_interface_flag => p_sc_defer_interface_flag,  
  212.                                        p_sc_send_945_flag        => p_sc_send_945_flag,  
  213.                                        p_sc_rule_id              => p_sc_rule_id,  
  214.                                        p_sc_rule_name            => p_sc_rule_name,  
  215.                                        p_wv_override_flag        => p_wv_override_flag,  
  216.                                        x_trip_id                 => x_trip_id,  
  217.                                        x_trip_name               => x_trip_name);  
  218.     
  219.     IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN  
  220.       dbms_output.put_line('Ship confirm has not been Completed For SO => ');  
  221.       --ROLLBACK;  
  222.       RAISE fail_api;  
  223.     ELSE  
  224.       dbms_output.put_line('Ship confirm Successfully Completed For SO => ');  
  225.       --COMMIT;  
  226.       
  227.       dbms_output.put_line('Checking the Delivery Status after delivery action API Call');  
  228.       dbms_output.put_line('==========================================');  
  229.       
  230.       SELECT wdd.source_code  
  231.             ,wdd.released_status  
  232.             ,wdd.inv_interfaced_flag  
  233.             ,wdd.oe_interfaced_flag  
  234.             ,wts.pending_interface_flag  
  235.         INTO v_source_code  
  236.             ,v_released_status  
  237.             ,v_inv_interfaced_flag  
  238.             ,v_oe_interfaced_flag  
  239.             ,v_pending_interface_flag  
  240.         FROM wsh_trips                wtr  
  241.             ,wsh_trip_stops           wts  
  242.             ,wsh_delivery_legs        wlg  
  243.             ,wsh_new_deliveries       wnd  
  244.             ,wsh_delivery_assignments wda  
  245.             ,wsh_delivery_details     wdd  
  246.        WHERE wtr.trip_id = wts.trip_id  
  247.          AND wts.stop_id = wlg.pick_up_stop_id  
  248.          AND wts.pending_interface_flag = 'Y'  
  249.          AND wdd.inv_interfaced_flag <> 'Y'  
  250.          AND wlg.delivery_id = wnd.delivery_id  
  251.          AND wnd.delivery_id = wda.delivery_id  
  252.          AND wda.delivery_detail_id = wdd.delivery_detail_id  
  253.          AND wnd.delivery_id = p_delivery_name  
  254.          AND wdd.source_line_id = i.source_line_id;  
  255.       
  256.       IF (v_source_code = 'OE' AND v_released_status = 'C' AND  
  257.          v_inv_interfaced_flag <> 'Y' AND v_oe_interfaced_flag <> 'Y' AND  
  258.          v_pending_interface_flag = 'Y'THEN  
  259.         
  260.         dbms_output.put_line('The Delivery has been Shipped & the Next Step is - Run Interface');  
  261.         dbms_output.put_line('===========================================');  
  262.         -- API Call for Submitting Interface Trip Stop  
  263.         
  264.         wsh_ship_confirm_actions.interface_all_wrp(errbuf            => v_errbuf,  
  265.                                                    retcode           => v_retcode,  
  266.                                                    p_mode            => 'ALL',  
  267.                                                    p_stop_id         => NULL,  
  268.                                                    p_delivery_id     => p_delivery_name,  
  269.                                                    p_log_level       => 0,  
  270.                                                    p_batch_id        => NULL,  
  271.                                                    p_trip_type       => NULL,  
  272.                                                    p_organization_id => i.organization_id,  
  273.                                                    p_num_requests    => 1,  
  274.                                                    p_stops_per_batch => 1);  
  275.         
  276.       ELSE  
  277.         dbms_output.put_line('The Delivery has not Shipped Properly');  
  278.       END IF;  
  279.     END IF;  
  280.   END LOOP;  
  281.   
  282. EXCEPTION  
  283.   WHEN fail_api THEN  
  284.     
  285.     dbms_output.put_line('==============');  
  286.     dbms_output.put_line('Error Details If Any');  
  287.     dbms_output.put_line('==============');  
  288.     
  289.     wsh_util_core.get_messages(p_init_msg_list => 'Y',  
  290.                                x_summary       => x_msg_summary,  
  291.                                x_details       => x_msg_details,  
  292.                                x_count         => x_msg_count);  
  293.     
  294.     IF x_msg_count > 1 THEN  
  295.       x_msg_data := x_msg_summary || x_msg_details;  
  296.       dbms_output.put_line(x_msg_data);  
  297.     ELSE  
  298.       x_msg_data := x_msg_summary || x_msg_details;  
  299.       dbms_output.put_line(x_msg_data);  
  300.     END IF;  
  301.     --  
  302.   --FOR i IN 1 .. x_msg_count LOOP    
  303.   --  x_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');    
  304.   --  dbms_output.put_line(i || ') ' || x_msg_data);    
  305.   --END LOOP;   
  306.   --  
  307. END;  

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页