1 Package Body Wip_Ato_Utils as
2 /* $Header: wipatoub.pls 120.1 2006/03/28 15:58:33 hshu noship $ */
3
4 /* *********************************************************************
5 Public Procedures
6 ***********************************************************************/
7
8
9 /***************************************************************************
10 * check_wip_supply_type :
11 * Description: This function is used to return the type of wip entity that
12 * is linked to a given sales order.
13 * Input : Sales order header, line and delivery id.
14 * Output : The output of this function is one of the following
15 * 1 = Discrete Job
16 * 4 = Flow schedule
17 * 0 = None
18 * -1 = Error
19 ***************************************************************************/
20
21
22 PROCEDURE check_wip_supply_type(p_so_header_id IN NUMBER,
23 p_so_line IN VARCHAR2,
24 p_so_delivery IN VARCHAR2,
25 p_org_id IN NUMBER,
26 p_wip_entity_type IN OUT NOCOPY NUMBER,
27 p_err_msg IN OUT NOCOPY VARCHAR2) IS
28 BEGIN
29 p_wip_entity_type := check_wip_supply_type(p_so_header_id,p_so_line,p_so_delivery,p_org_id,-1);
30 IF(p_wip_entity_type = -1) THEN
31 p_err_msg := 'wipatoub: check_wip_supply_type : SQLERRM: ' || SUBSTR(SQLERRM,1,200);
32 END IF;
33 END check_wip_supply_type;
34
35
36
37
38 FUNCTION check_wip_supply_type(p_so_header_id NUMBER,
39 p_so_line VARCHAR2,
40 p_so_delivery VARCHAR2,
41 p_org_id NUMBER,
42 p_supply_source_id NUMBER := -1)
43 RETURN NUMBER IS
44 x_wip_entity_id NUMBER;
45 x_wip_entity_type NUMBER;
46 BEGIN
47 x_wip_entity_id := 0;
48 x_wip_entity_type := 0;
49
50 /* Check to see if there is a record in mtl_demand corresponding to this
51 * sales order Optionally the supply source id can be provided so that
52 * we don't have to select from MTL_DEMAND.
53 */
54
55 IF p_supply_source_id = -1 THEN
56 DECLARE
57 BEGIN
58 SELECT supply_source_header_id INTO x_wip_entity_id
59 FROM mtl_demand
60 WHERE organization_id = p_org_id
61 AND supply_source_type = 5
62 AND demand_source_header_id = p_so_header_id
63 AND demand_source_line = p_so_line
64 AND Decode(p_so_delivery, NULL, '@@@', demand_source_delivery) = Nvl(p_so_delivery,'@@@')
65 AND demand_source_type = 2
66 AND reservation_type in (2,3)
67 AND ROWNUM = 1;
68 EXCEPTION
69 WHEN no_data_found THEN
70 x_wip_entity_id := 0;
71 x_wip_entity_type := 0;
72 END;
73 ELSE
74 x_wip_entity_id := Nvl(p_supply_source_id,0);
75 END IF;
76
77 IF(x_wip_entity_id > 0) THEN
78 /* If a row is found in mtl_demand then get its wip entity type. */
79
80 SELECT entity_type INTO x_wip_entity_type
81 FROM wip_entities
82 WHERE wip_entity_id = x_wip_entity_id;
83
84 ELSE
85 /* If no row was found in mtl_demand check interface table for
86 * any flow schedules that haven't been picked up yet
87 */
88
89 DECLARE
90 BEGIN
91 SELECT transaction_source_id INTO x_wip_entity_id
92 FROM mtl_transactions_interface
93 WHERE organization_id = p_org_id
94 /* Bug fix 4889919 */
95 and transaction_source_type_id = 5
96 /* End of bug fix 4889919 */
97 AND demand_source_header_id = p_so_header_id
98 AND demand_source_line = p_so_line
99 AND Decode(p_so_delivery, NULL, '@@@', demand_source_delivery) = Nvl(p_so_delivery,'@@@')
100 AND flow_schedule = 'Y'
101 AND process_flag = 1
102 AND ROWNUM = 1;
103 EXCEPTION
104 WHEN no_data_found THEN
105 x_wip_entity_id := 0;
106 x_wip_entity_type := 0;
107 END;
108
109 IF(x_wip_entity_id <> 0) THEN
110 x_wip_entity_type := 4;
111 END IF;
112
113 END IF;
114
115 RETURN(x_wip_entity_type);
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 RETURN(-1);
120 END check_wip_supply_type;
121
122
123
124 /***************************************************************************
125 * get_so_open_qty :
126 * Description: This procedure is used to return the quantity in a given
127 * sales order that is open for reservation.
128 * Input : Sales order header, line and delivery id.
129 * Output : The output of this function is the quantity open in mtl_demand
130 *
131 ***************************************************************************/
132
133
134
135 PROCEDURE get_so_open_qty(p_so_header_id IN NUMBER,
136 p_so_line IN VARCHAR2,
137 p_so_delivery IN VARCHAR2,
138 p_org_id IN NUMBER,
139 p_qty IN OUT NOCOPY NUMBER,
140 p_err_msg IN OUT NOCOPY VARCHAR2) IS
141 x_qty NUMBER;
142 BEGIN
143
144 /* get the open quantity from mtl_demand */
145
146 DECLARE
147 BEGIN
148 SELECT Nvl(primary_uom_quantity,0) INTO x_qty
149 FROM mtl_demand
150 WHERE organization_id = p_org_id
151 AND demand_source_header_id = p_so_header_id
152 AND demand_source_line = p_so_line
153 AND demand_source_delivery = p_so_delivery
154 AND demand_source_type = 2
155 AND reservation_type = 1
156 AND row_status_flag = 1
157 AND parent_demand_id IS NOT null
158 AND ((config_status = 20 AND demand_type= 4)
159 OR (config_status = 20 AND NVL(demand_type,6) = 6));
160 EXCEPTION
161 WHEN no_data_found THEN
162 x_qty := 0;
163 END;
164
165 p_qty := x_qty;
166
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 p_qty := 0;
171 p_err_msg := 'wipatoub: get_so_open_qty : SQLERRM : ' || SUBSTR(SQLERRM,1,200);
172 END get_so_open_qty;
173
174
175 END Wip_Ato_Utils;
[Home] [Help]