eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GMI_UPDATE_ORDER

Source


1 PACKAGE BODY GMI_UPDATE_ORDER AS
2 /*  $Header: GMIUSITB.pls 115.2 2004/07/21 18:29:40 uphadtar noship $ */
3 /* +=========================================================================+
4  |                Copyright (c) 2000 Oracle Corporation                    |
5  |                        TVP, Reading, England                            |
6  |                         All rights reserved                             |
7  +=========================================================================+
8  | FILENAME                                                                |
9  |    GMIUSITB.pls                                                         |
10  |                                                                         |
11  | DESCRIPTION                                                             |
12  |     This file is introduced for WSH J release.                          |
13  |     The procedure process_order is called from WSHDDSHB.pls to process  |
14  |     OPM Internal orders. The same procedure for earlier releases is     |
15  |     already in use and declared in GMIUSHPB.pls and procedure is called |
16  |     process_opm_orders                                                  |
17  |                                                                         |
18  | HISTORY                                                                 |
19  |     HAW Initianl release 115.0                                          |
20  +=========================================================================+
21 */
22 
23 PROCEDURE process_order(P_stop_tab  IN  wsh_util_core.id_tab_type
24                              ,x_return_status OUT NOCOPY VARCHAR2  ) IS
25 
26 
27 l_completion_status     VARCHAR2(30) := 'NORMAL';
28 
29 l_delivery_id number;
30 request_id number;
31 l_error_code number;
32 l_msg_count number;
33 l_msg_data varchar2(3000);
34 l_error_text varchar2(2000);
35 l_transaction_header_id number ;
36 l_return_status varchar2(30);
37 l_if_internal   number ;
38 l_request_id             NUMBER := NULL;
39 rcv_inter_req_submission exception;
40 
41 CURSOR pickup_deliveries (p_stop_id NUMBER)IS
42 SELECT dg.delivery_id , st.transaction_header_id
43      FROM   wsh_delivery_legs dg,
44          wsh_new_deliveries dl,
45          wsh_trip_stops st
46      WHERE     st.stop_id = dg.pick_up_stop_id AND
47          st.stop_id = p_stop_id AND
48          st.stop_location_id = dl.initial_pickup_location_id AND
49          dg.delivery_id = dl.delivery_id  ;
50 
51 -- Bug 3779795 Added NOT EXISTS to the cursor below so that wdd which is already synchronized to PO
52 -- side is not inserted again in rcv_transactions_interface.
53 CURSOR c_detail_in_delivery is
54    SELECT   dd.delivery_detail_id, dd.source_line_id
55    FROM     wsh_delivery_details dd, wsh_delivery_assignments da
56    WHERE    dd.delivery_detail_id             = da.delivery_detail_id
57    AND      da.delivery_id                    = l_delivery_id
58    AND      NVL(dd.inv_interfaced_flag , 'N') = 'Y'
59    AND      dd.container_flag                 = 'N'
60    AND      dd.source_code                    = 'OE'
61    AND  NOT EXISTS(select 1
62                    from   rcv_shipment_lines rsl, oe_order_lines_all oel
63                    where  oel.line_id             = dd.source_line_id
64                    and    rsl.requisition_line_id = oel.source_document_line_id
65                    and    rsl.comments            = 'OPM WDD:'||to_char(dd.delivery_detail_id));
66 
67 l_detail_in_delivery c_detail_in_delivery%ROWTYPE;
68 
69 CURSOR c_order_line_info(c_order_line_id number) is
70 SELECT source_document_type_id
71      , source_document_id
72      , source_document_line_id
73      , ship_from_org_id
74 from     oe_order_lines_all
75 where  line_id = c_order_line_id;
76 
77 l_order_line_info c_order_line_info%ROWTYPE;
78 
79 l_group_id                 NUMBER := 0;
80 
81 CURSOR c_details_for_interface(p_del_detail_id number)  is
82 SELECT * from wsh_delivery_details
83 where delivery_detail_id = p_del_detail_id
84 and container_flag = 'N';
85 
86 l_detail_rec c_details_for_interface%ROWTYPE;
87 
88 del_rec_stop_id pickup_deliveries%ROWTYPE;
89 
90 
91 CURSOR c_trip_stop (c_trip_stop_id NUMBER ) IS
92    SELECT * FROM WSH_TRIP_STOPS
93    WHERE STOP_ID = c_trip_stop_id;
94 
95 l_trip_stop_rec c_trip_stop%ROWTYPE;
96 
97  BEGIN
98 
99 
100    x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
101    l_if_internal := 0 ;
102 
103    gmi_reservation_util.println('Value of count in OPM_ORDERS for WSH J is '||p_stop_tab.count);
104    for i in 1.. p_stop_tab.count LOOP
105     ---FOR del IN pickup_deliveries LOOP
106     GMI_RESERVATION_UTIL.Println ('Processing Stop: '||p_stop_tab(i) );
107      OPEN pickup_deliveries(p_stop_tab(i));
108      LOOP
109        gmi_reservation_util.println('Fetching pickup_deliveries');
110        FETCH pickup_deliveries into del_rec_stop_id ;
111        l_delivery_id := del_rec_stop_id.delivery_id;
112        EXIT WHEN pickup_deliveries%NOTFOUND;
113        GMI_RESERVATION_UTIL.Println ('Found delivery: '  ||   l_delivery_id);
114        OPEN c_detail_in_delivery ;
115 
116        LOOP
117          gmi_reservation_util.println('Fetching c_detail_in_delivery');
118          FETCH c_detail_in_delivery into l_detail_in_delivery;
119          EXIT WHEN c_detail_in_delivery%NOTFOUND;
120 
121          OPEN c_order_line_info(l_detail_in_delivery.source_line_id);
122          gmi_reservation_util.println('Fetching c_order_line_info for l_detail_in_delivery.source_line_id'||l_detail_in_delivery.source_line_id);
123          FETCH c_order_line_info into l_order_line_info;
124          if (c_order_line_info%NOTFOUND) THEN
125             CLOSE c_order_line_info;
126             x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
127             GMI_RESERVATION_UTIL.Println('Warning, Sales order not valid');
128             CLOSE c_detail_in_delivery;  /* B2886561 close this cursor before return */
129             return;
130          END if;
131          CLOSE c_order_line_info;
132 
133          IF (l_order_line_info.source_document_type_id = 10
134              and INV_GMI_RSV_BRANCH.Process_Branch(l_order_line_info.ship_from_org_id) )
135          THEN /* internal order */
136             l_if_internal := 1;  -- internal order
137             gmi_reservation_util.println('It is an internal Order');
138             IF nvl(l_group_id,0) = 0 THEN
139                /* only do this once */
140                select RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_group_id FROM DUAL;
141             END IF;
142             gmi_reservation_util.println('Going to open c_details_for_interface and value of l_detail_in_delivery.delivery_detail_id is '||l_detail_in_delivery.delivery_detail_id);
143             OPEN c_details_for_interface(l_detail_in_delivery.delivery_detail_id);
144             FETCH c_details_for_interface into l_detail_rec;
145             if c_details_for_interface%NOTFOUND then
146                CLOSE c_details_for_interface;
147                x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
148                WSH_UTIL_CORE.Println('Warning, Delivery Detail ' ||l_detail_in_delivery.delivery_detail_id ||' not found');
149                IF c_detail_in_delivery%ISOPEN THEN  /* B2886561 close this cursor before return */
150                   CLOSE c_detail_in_delivery;
151                END IF;
152                return;
153             end if;
154             CLOSE c_details_for_interface;
155 
156             OPEN c_trip_stop(p_stop_tab(i));
157             gmi_reservation_util.println('Going to fetch  c_trip_stop for  stop  '||p_stop_tab(i));
158             FETCH c_trip_stop into l_trip_stop_rec;
159               if c_trip_stop%NOTFOUND then
160                CLOSE c_trip_stop;
161                x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
162                WSH_UTIL_CORE.Println('Warning, Trip Stop '|| p_stop_tab(i) ||' not found');
163                IF c_detail_in_delivery%ISOPEN THEN  /* B2886561 close this cursor before return */
164                   CLOSE c_detail_in_delivery;
165                END IF;
166                return;
167               end if;
168             oe_debug_pub.add('Found Trip Stop '|| p_stop_tab(i));
169             CLOSE c_trip_stop;
170 
171             GMI_RESERVATION_UTIL.Println ('found internal order line in this delivery  '  ||   l_delivery_id);
172             -- internal orders, insert into rcv_transactions_interface
173             gmi_reservation_util.println('Going to call GMI_SHIPPING_UTIL.create_rcv_transaction');
174             GMI_SHIPPING_UTIL.create_rcv_transaction
175                 ( p_shipping_line   => l_detail_rec
176                 , p_trip_stop_rec   => l_trip_stop_rec
177                 , p_group_id        => l_group_id
178                 , x_return_status    => l_return_status
179                 , x_msg_count        => l_msg_count
180                 , x_msg_data         => l_msg_data
181                 );
182             gmi_reservation_util.println('Finished calling GMI_Shipping_Util.create_rcv_transaction');
183             IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
184                 WSH_UTIL_CORE.printmsg('Warning ...');
185                 WSH_UTIL_CORE.println('Failed GMI_Shipping_Util.create_rcv_transaction for delivery ID '
186                       || l_detail_rec.DELIVERY_DETAIL_ID );
187             END IF;
188 
189          END IF;
190        END LOOP;   -- 2nd inner loop
191 
192        CLOSE c_detail_in_delivery;
193      END LOOP;      -- 1st inner loop
194      CLOSE pickup_deliveries;
195    END LOOP; -- of For loop
196 
197 
198 
199    IF ( l_if_internal = 1 ) THEN
200       l_request_id := fnd_request.submit_request(
201           'PO',
202           'RVCTP',
203           null,
204           null,
205           false,
206           'IMMEDIATE',
207           l_group_id,
208           fnd_global.local_chr(0),
209           NULL, NULL, NULL, NULL, NULL, NULL,
210           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
211           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
212           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
213           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
214           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
215           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
216           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
217           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
218           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
219           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
220           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
221           NULL, NULL, NULL, NULL, NULL, NULL, NULL,
222           NULL, NULL, NULL, NULL, NULL, NULL, NULL);
223    END IF;
224 
225    IF  (l_request_id = 0 ) THEN
226      raise rcv_inter_req_submission;
227    END IF;
228 
229    EXCEPTION
230      WHEN rcv_inter_req_submission THEN
231       x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
232       gmi_reservation_util.println('Submitting Receivable Transaction failed');
233 
234 
235 END process_order;
236 
237 END GMI_UPDATE_ORDER;
238