[Home] [Help]
87: to_serial_number WSH_DELIVERY_DETAILS.to_serial_number%TYPE,
88: transaction_temp_id WSH_DELIVERY_DETAILS.transaction_temp_id%TYPE,
89: container_flag WSH_DELIVERY_DETAILS.container_flag%TYPE,
90: released_status WSH_DELIVERY_DETAILS.released_status%TYPE,
91: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
92: parent_delivery_detail_id wsh_delivery_assignments_v.parent_delivery_detail_id%TYPE,
93: -- Bug 2419301
94: oe_interfaced_flag WSH_DELIVERY_DETAILS.oe_interfaced_flag%TYPE,
95: -- anxsharm bug 2181132
88: transaction_temp_id WSH_DELIVERY_DETAILS.transaction_temp_id%TYPE,
89: container_flag WSH_DELIVERY_DETAILS.container_flag%TYPE,
90: released_status WSH_DELIVERY_DETAILS.released_status%TYPE,
91: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
92: parent_delivery_detail_id wsh_delivery_assignments_v.parent_delivery_detail_id%TYPE,
93: -- Bug 2419301
94: oe_interfaced_flag WSH_DELIVERY_DETAILS.oe_interfaced_flag%TYPE,
95: -- anxsharm bug 2181132
96: source_line_set_id WSH_DELIVERY_DETAILS.source_line_set_id%TYPE,
101: line_direction WSH_DELIVERY_DETAILS.line_direction%TYPE,-- J-IB-NPARIKH
102: -- J: W/V Changes
103: gross_weight WSH_DELIVERY_DETAILS.gross_weight%TYPE,
104: wv_frozen_flag WSH_DELIVERY_DETAILS.wv_frozen_flag%TYPE,
105: parent_delivery_id WSH_DELIVERY_ASSIGNMENTS_V.parent_delivery_id%TYPE,
106: wda_type WSH_DELIVERY_ASSIGNMENTS_V.type%TYPE,
107: replenishment_status WSH_DELIVERY_DETAILS.replenishment_status%TYPE --bug# 6689448 (replenishment project)
108: );
109:
102: -- J: W/V Changes
103: gross_weight WSH_DELIVERY_DETAILS.gross_weight%TYPE,
104: wv_frozen_flag WSH_DELIVERY_DETAILS.wv_frozen_flag%TYPE,
105: parent_delivery_id WSH_DELIVERY_ASSIGNMENTS_V.parent_delivery_id%TYPE,
106: wda_type WSH_DELIVERY_ASSIGNMENTS_V.type%TYPE,
107: replenishment_status WSH_DELIVERY_DETAILS.replenishment_status%TYPE --bug# 6689448 (replenishment project)
108: );
109:
110: -- anxsharm
123: Instead of Using 2 cursors just use One Cursor with a UNION
124: */
125: --
126: -- LPN CONV. rv
127: --Based on assumption that we are using wsh_delivery_assignments_v,
128: --delivery and its contents will belong to same organization.
129: --Similarly, container and its contents will belong to same organization.
130: --Hence, we are checking for WMS org or non-WMS org. at the parent level (i.e. delivery/container)
131: --rather than at line-level for performance reasons.
131: --rather than at line-level for performance reasons.
132:
133: --If this assumptions were to be violated in anyway
134: -- i.e Query was changed to refer to base table wsh_delivery_assignments instead of
135: -- wsh_delivery_assignments_v
136: -- or
137: -- if existing query were to somehow return/fetch records where
138: -- delivery and its contents may belong to diff. org.
139: -- container and its contents may belong to diff. org.
145: CURSOR C_INSIDE_OUTSIDE_OF_CONTAINER (c_detail_id number) is
146: SELECT delivery_detail_id,
147: parent_delivery_detail_id,
148: delivery_id
149: FROM wsh_delivery_assignments_v
150: START WITH delivery_detail_id = c_detail_id
151: CONNECT BY prior delivery_detail_id = parent_delivery_detail_id
152: UNION
153: SELECT delivery_detail_id,
152: UNION
153: SELECT delivery_detail_id,
154: parent_delivery_detail_id,
155: delivery_id
156: FROM wsh_delivery_assignments_v
157: START WITH delivery_detail_id = c_detail_id
158: CONNECT BY prior parent_delivery_detail_id = delivery_detail_id;
159:
160: /** Variable Declaration for Fetching the Cursor */
185: wdd.weight_uom_code,
186: wdd.volume_uom_code,
187: wdd.inventory_item_id,
188: wda.type wda_type
189: FROM wsh_delivery_assignments_v wda,
190: wsh_delivery_details wdd
191: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
192: AND wda.delivery_detail_id = c_detail_id;
193: