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.MRP_RELEASE_SO

Source


1 PACKAGE BODY mrp_release_so AS
2 /*$Header: MRPRLSOB.pls 120.1.12010000.2 2008/12/12 15:50:00 eychen ship $ */
3 
4 PROCEDURE release_so_program
5 (
6 errbuf                  OUT NOCOPY VARCHAR2
7 ,retcode                 OUT NOCOPY NUMBER,
8 p_batch_id IN NUMBER,
9 p_dblink in varchar2,
10 p_instance_id in number
11 ) IS
12 
13  TYPE type_cursor IS REF CURSOR;
14  so_cursor type_cursor;
15  sql_stmt varchar2(2000);
16  x_return_status varchar2(1) :=FND_API.G_RET_STS_SUCCESS;
17  x_crm_return_status varchar2(1) :=FND_API.G_RET_STS_SUCCESS;
18  a number;
19  p_so_table OE_SCHEDULE_GRP.Sch_Tbl_Type;
20  p_crm_so_table AHL_LTP_ASCP_ORDERS_PVT.Sched_Orders_Tbl;
21 
22  CURSOR header_c(p_line_id number) is
23   select header_id
24     from oe_order_lines
25    where line_id = p_line_id;
26 
27 
28  CURSOR populate_ou(p_line_id number)  is
29   select org_id
30   from oe_order_lines_all
31   where line_id = p_line_id;
32 
33  p_status number;
34  p_user_name varchar2(30) :=FND_PROFILE.VALUE('USERNAME');
35  p_need_notify boolean := false;
36  p_request_id number;
37  l_file_name varchar2(1000);
38  so_count number :=0;
39  crm_so_count number :=0;
40 begin
41 
42 
43 
44   FND_FILE.PUT_LINE(FND_FILE.LOG, 'batch_id='||p_batch_id||', instance_id='||p_instance_id||', dblink='||p_dblink);
45 
46  retcode :=0;
47 
48 -- release so with source_type = null thru oe package
49  sql_stmt:=
50    ' select schedule_ship_date,'||
51           ' schedule_arrival_date,'||
52           ' earliest_ship_date, '||
53           ' header_id,'||
54           ' line_id,'||
55           ' org_id,'||
56           ' operating_unit,'||
57           ' delivery_lead_time,'||
58           ' ship_method, '||
59           ' orig_schedule_ship_date,'||
60           ' orig_schedule_arrival_date,'||
61           ' orig_org_id,'||
62           ' orig_ship_method, '||
63           ' quantity, '||
64           ' decode(firm_flag,1,''Y'',''N''), '||
65           ' orig_item_id, '||
66           ' inventory_item_id '||
67  ' from msc_sales_order_interface'||p_dblink||
68  ' where sr_instance_id = : p_instance_id '||
69    ' and source_type is null '||
70    ' and batch_id = :p_batch_id ';
71 
72    a :=1;
73    OPEN so_cursor FOR sql_stmt using p_instance_id, p_batch_id;
74    LOOP
75       FETCH so_cursor INTO p_so_table(a).schedule_ship_date,
76                         p_so_table(a).schedule_arrival_date,
77                         p_so_table(a).earliest_ship_date,
78                         p_so_table(a).header_id,
79                         p_so_table(a).line_id,
80                         p_so_table(a).Ship_from_org_id,
81                         p_so_table(a).org_id,
82                         p_so_table(a).delivery_lead_time,
83                         p_so_table(a).shipping_method_code,
84                         p_so_table(a).orig_schedule_ship_date,
85                         p_so_table(a).orig_schedule_arrival_date,
86                         p_so_table(a).orig_ship_from_org_id,
87                         p_so_table(a).orig_shipping_method_code,
88                         p_so_table(a).orig_ordered_quantity,
89                         p_so_table(a).firm_demand_flag,
90                         p_so_table(a).orig_inventory_item_id,
91                         p_so_table(a).inventory_item_id;
92        EXIT WHEN so_cursor%NOTFOUND;
93        so_count := so_count +1;
94 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_ship_date='||to_char(p_so_table(a).schedule_ship_date,'MM-DD-RRRR HH24:MI:SS'));
95 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_arrival_date='||to_char(p_so_table(a).schedule_arrival_date,'MM-DD-RRRR HH24:MI:SS'));
96 FND_FILE.PUT_LINE(FND_FILE.LOG,'earliest_ship_date='||to_char(p_so_table(a).earliest_ship_date,'MM-DD-RRRR HH24:MI:SS'));
97 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_id='||p_so_table(a).line_id);
98 FND_FILE.PUT_LINE(FND_FILE.LOG,'Ship_from_org_id='||p_so_table(a).Ship_from_org_id);
99 FND_FILE.PUT_LINE(FND_FILE.LOG,'delivery_lead_time='||p_so_table(a).delivery_lead_time);
100 FND_FILE.PUT_LINE(FND_FILE.LOG,'shipping_method_code='||p_so_table(a).shipping_method_code);
101 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_schedule_ship_date='||to_char(p_so_table(a).orig_schedule_ship_date,'MM-DD-RRRR HH24:MI:SS'));
102 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_schedule_arrival_date='||to_char(p_so_table(a).orig_schedule_arrival_date,'MM-DD-RRRR HH24:MI:SS'));
103 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_ship_from_org_id='||p_so_table(a).orig_ship_from_org_id);
104 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_shipping_method_code='||p_so_table(a).orig_shipping_method_code);
105 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_ordered_quantity='||p_so_table(a).orig_ordered_quantity);
106 FND_FILE.PUT_LINE(FND_FILE.LOG,'firm_demand_flag='||p_so_table(a).firm_demand_flag);
107 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_inventory_item_id='||p_so_table(a).orig_inventory_item_id);
108 FND_FILE.PUT_LINE(FND_FILE.LOG,'inventory_item_id='||p_so_table(a).inventory_item_id);
109        if p_so_table(a).header_id is null then
110           OPEN header_c(p_so_table(a).line_id);
111           FETCH header_c INTO p_so_table(a).header_id;
112           CLOSE header_c;
113 FND_FILE.PUT_LINE(FND_FILE.LOG,'header_id='||p_so_table(a).header_id);
114        end if;
115 
116 
117      -- populating correct OU
118           OPEN populate_ou(p_so_table(a).line_id);
119           FETCH populate_ou INTO p_so_table(a).org_id;
120           CLOSE populate_ou;
121 FND_FILE.PUT_LINE(FND_FILE.LOG,'org_id='||p_so_table(a).org_id);
122 
123        a := a+1;
124    END LOOP;
125    CLOSE so_cursor;
126 
127 -- release so with source_type =100 thru crm package
128  sql_stmt:=
129    ' select schedule_ship_date,'||
130           ' schedule_arrival_date,'||
131           ' earliest_ship_date, '||
132           ' header_id,'||
133           ' line_id,'||
134           ' org_id,'||
135           ' quantity '||
136  ' from msc_sales_order_interface'||p_dblink||
137  ' where sr_instance_id = : p_instance_id '||
138    ' and source_type =100 '||
139    ' and batch_id = :p_batch_id ';
140 
141    a :=1;
142    OPEN so_cursor FOR sql_stmt using p_instance_id, p_batch_id;
143    LOOP
144       FETCH so_cursor INTO p_crm_so_table(a).schedule_ship_date,
145                         p_crm_so_table(a).schedule_arrival_date,
146                         p_crm_so_table(a).earliest_ship_date,
147                         p_crm_so_table(a).header_id,
148                         p_crm_so_table(a).order_line_id,
149                         p_crm_so_table(a).org_id,
150                         p_crm_so_table(a).quantity_by_due_date;
151        EXIT WHEN so_cursor%NOTFOUND;
152        crm_so_count := crm_so_count +1;
153 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_ship_date='||to_char(p_crm_so_table(a).schedule_ship_date,'MM-DD-RRRR HH24:MI:SS'));
154 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_arrival_date='||to_char(p_crm_so_table(a).schedule_arrival_date,'MM-DD-RRRR HH24:MI:SS'));
155 FND_FILE.PUT_LINE(FND_FILE.LOG,'earliest_ship_date='||to_char(p_crm_so_table(a).earliest_ship_date,'MM-DD-RRRR HH24:MI:SS'));
156 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_id='||p_crm_so_table(a).order_line_id);
157 FND_FILE.PUT_LINE(FND_FILE.LOG,'org_id='||p_crm_so_table(a).org_id);
158 FND_FILE.PUT_LINE(FND_FILE.LOG,'qty='||p_crm_so_table(a).quantity_by_due_date);
159        a := a+1;
160    END LOOP;
161    CLOSE so_cursor;
162 
163   if p_dblink is not null and p_dblink <> ' ' then
164             commit;
165             begin
166                sql_stmt:= ' alter session close database link '||
167                                     ltrim(p_dblink,'@');
168                execute immediate sql_stmt;
169             exception when others then
170                  null;
171             end;
172   end if;
173 
174 
175 
176    IF p_so_table.count > 0 then
177          mo_global.init('ONT');
178          OE_SCHEDULE_GRP.Update_Scheduling_Results(
179               p_so_table,
180               p_batch_id,
181               x_return_status);
182          commit;
183    END IF;
184 
185 
186 
187    IF p_crm_so_table.count >0 then
188       AHL_LTP_ASCP_ORDERS_PVT.Update_Scheduling_Results(
189               1.0,
190               FND_API.g_false,
191               FND_API.g_false,
192               FND_API.g_valid_level_full,
193               p_crm_so_table,
194               x_crm_return_status);
195        commit;
196    END IF;
197 
198 
199       -- send workflow notification for the failed so
200    for a in 1..so_count loop
201       if nvl(p_so_table(a).x_return_status, FND_API.G_RET_STS_ERROR) <>
202              FND_API.G_RET_STS_SUCCESS or
203          p_so_table(a).x_override_atp_date_code = 'Y' then
204             sql_stmt:=
205                 ' update msc_sales_order_interface'||p_dblink||
206                  '   set return_status = :p_status '||
207                  ' where sr_instance_id = :p_instance_id '||
208                    ' and batch_id = :p_batch_id '||
209                    ' and line_id = :p_line_id ';
210             if nvl(p_so_table(a).x_return_status, FND_API.G_RET_STS_ERROR) <>
211                FND_API.G_RET_STS_SUCCESS then
212 FND_FILE.PUT_LINE(FND_FILE.LOG,'update fails for line id '||p_so_table(a).line_id||', om return status ='||p_so_table(a).x_return_status);
213                p_status := 2; -- fails
214                retcode :=2;
215             else
216 FND_FILE.PUT_LINE(FND_FILE.LOG,'atp override for line id'||p_so_table(a).line_id);
217                p_status := 1; -- override
218             end if;
219           EXECUTE IMMEDIATE sql_stmt using p_status, p_instance_id, p_batch_id,
220                                     p_so_table(a).line_id;
221           p_need_notify := true;
222       else
223 FND_FILE.PUT_LINE(FND_FILE.LOG,'update scceeds for line id'||p_so_table(a).line_id);
224       end if;
225 
226    end loop;
227 
228    IF nvl(x_crm_return_status, FND_API.G_RET_STS_ERROR) <>
229              FND_API.G_RET_STS_SUCCESS THEN
230       p_status := 2; -- fails
231       retcode :=2;
232       sql_stmt:=
233                 ' update msc_sales_order_interface'||p_dblink||
234                  '   set return_status = :p_status '||
235                  ' where sr_instance_id = :p_instance_id '||
236                    ' and batch_id = :p_batch_id '||
237                    ' and source_type = 100 ';
238       EXECUTE IMMEDIATE sql_stmt using p_status, p_instance_id, p_batch_id;
239       commit;
240       for a in 1..crm_so_count loop
241         FND_FILE.PUT_LINE(FND_FILE.LOG,'update fails for line id '||p_crm_so_table(a).order_line_id);
242       end loop;
243    ELSE
244       for a in 1..crm_so_count loop
245         FND_FILE.PUT_LINE(FND_FILE.LOG,'update successfully for line id '||p_crm_so_table(a).order_line_id);
246       end loop;
247    END IF;
248 
249    sql_stmt:=
250                 ' delete from msc_sales_order_interface'||p_dblink||
251                  ' where sr_instance_id = :p_instance_id '||
252                    ' and batch_id = :p_batch_id '||
253                    ' and return_status is null ';
254    EXECUTE IMMEDIATE sql_stmt using p_instance_id, p_batch_id;
255    commit;
256 
257    if p_need_notify then
258        sql_stmt:=
259            'BEGIN'
260         ||'  msc_rel_wf.so_release_workflow_program'||p_dblink||'('
261                                           ||'   :p_batch_id, '
262                                           ||' :p_instance_id,'
263                                           ||' :p_planner,'
264                                           ||' :p_request_id);'
265         ||' END;';
266        EXECUTE IMMEDIATE sql_stmt using in p_batch_id,in p_instance_id,
267                                         in p_user_name, out p_request_id ;
268        commit;
269 
270 FND_FILE.PUT_LINE(FND_FILE.LOG,'send workflow notification to planner '||p_user_name||', request id='||p_request_id);
271    end if;
272 exception when others then
273  retcode :=2;
274  raise;
275 
276 END release_so_program;
277 
278 end mrp_release_so;