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.

APPS.AHL_OSP_SHIPMENT_PUB dependencies on AHL_OSP_ORDER_LINES

Line 23: G_CSI_T_SOURCE_LINE_REF CONSTANT VARCHAR2(50) := 'AHL_OSP_ORDER_LINES';

19: -- Transaction sub type for Exchange order's Return lines
20: --G_SUBTXN_EXC_RETURN CONSTANT VARCHAR2(30) := 'Return for Replacement';
21: -- Source transaction table
22: G_TRANSACTION_TABLE CONSTANT VARCHAR2(30) := 'OE_ORDER_LINES_ALL';
23: G_CSI_T_SOURCE_LINE_REF CONSTANT VARCHAR2(50) := 'AHL_OSP_ORDER_LINES';
24:
25:
26: x_header_rec OE_ORDER_PUB.Header_Rec_Type;
27: x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;

Line 302: from AHL_OSP_ORDER_LINES a

298: OSP_LINE_NUMBER,
299: inventory_item_id,
300: inventory_org_id,
301: serial_number
302: from AHL_OSP_ORDER_LINES a
303: where OSP_ORDER_ID = p_osp_order_id
304: /* Modified by jaramana on January 11, 2008 to fix the Bug 5688387/5842229
305: changed the "and" operation in the line below to "Or" */
306: and (oe_ship_line_id is null or oe_return_line_id is null)

Line 309: from ahl_osp_order_lines

305: changed the "and" operation in the line below to "Or" */
306: and (oe_ship_line_id is null or oe_return_line_id is null)
307: and serial_number is not null
308: and exists (select 1
309: from ahl_osp_order_lines
310: where osp_order_id = p_osp_order_id
311: and inventory_item_id = a.inventory_item_id
312: and inventory_org_id = a.inventory_org_id
313: and serial_number = a.serial_number

Line 327: from AHL_OSP_ORDER_LINES a

323: p_serial_number IN VARCHAR2) IS
324:
325: --Modified by jaramana on January 11, 2008 to fix the new issue raised by AE in the Bug 5688387/5842229
326: Select distinct nvl(oe_ship_line_id,-1) oe_ship_line_id, nvl(oe_return_line_id,-1) oe_return_line_id
327: from AHL_OSP_ORDER_LINES a
328: where OSP_ORDER_ID = p_osp_order_id
329: and (oe_ship_line_id is not null
330: or oe_return_line_id is not null)
331: and inventory_item_id = p_inventory_item_id

Line 1624: FROM ahl_osp_order_lines

1620: where organization_id = p_customer_site_id;
1621:
1622: CURSOR ahl_get_ship_from_org_csr(p_osp_order_id IN NUMBER) IS
1623: SELECT inventory_org_id
1624: FROM ahl_osp_order_lines
1625: WHERE osp_order_id = p_osp_order_id
1626: ORDER BY osp_order_line_id;
1627:
1628: l_cust_id NUMBER;

Line 1990: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl

1986: ospl.osp_order_id,
1987: ospl.osp_order_line_id,
1988: ospl.osp_line_number,
1989: ospl.exchange_instance_id
1990: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl
1991: WHERE ospl.workorder_id = wo.workorder_id
1992: AND wo.job_number = p_wo_name
1993: AND ospl.osp_order_id = p_osp_id
1994: AND ospl.status_code IS NULL;

Line 2016: from ahl_osp_order_lines

2012: and (active_end_date is null or active_end_date > sysdate)
2013: and inv_service_item_rel_id in (select inv_service_item_rel_id
2014: from ahl_inv_service_item_rels
2015: where service_item_id in (select service_item_id
2016: from ahl_osp_order_lines
2017: where osp_order_id = p_osp_order_id
2018: and inventory_item_id = p_inventory_item_id
2019: and inventory_org_id = p_inventory_org_id
2020: and serial_number = p_serial_number

Line 2042: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl

2038: ospl.osp_order_id,
2039: ospl.osp_order_line_id,
2040: ospl.osp_line_number,
2041: ospl.exchange_instance_id
2042: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl
2043: WHERE ospl.workorder_id = wo.workorder_id
2044: AND wo.item_instance_id = p_instance_id
2045: AND ospl.osp_order_id = p_osp_id
2046: AND ospl.status_code IS NULL;

Line 2064: FROM AHL_OSP_ORDER_LINES

2060: --For a given osp line id, check if shipment line exists
2061: CURSOR ahl_oe_ship_id_csr(p_osp_id IN NUMBER,
2062: p_osp_line_id IN NUMBER)IS
2063: SELECT 1
2064: FROM AHL_OSP_ORDER_LINES
2065: WHERE osp_order_id = p_osp_id
2066: AND osp_order_line_id = p_osp_line_id
2067: AND oe_ship_line_id IS NOT NULL;
2068:

Line 2073: FROM AHL_OSP_ORDER_LINES

2069: --For a given osp line id, check if return line exists
2070: CURSOR ahl_oe_return_id_csr(p_osp_id IN NUMBER,
2071: p_osp_line_id IN NUMBER) IS
2072: SELECT 1
2073: FROM AHL_OSP_ORDER_LINES
2074: WHERE osp_order_id = p_osp_id
2075: AND osp_order_line_id = p_osp_line_id
2076: AND oe_return_line_id IS NOT NULL;
2077:

Line 2084: FROM AHL_OSP_ORDER_LINES ospl,

2080: p_csi_ii_id IN NUMBER) IS
2081: --Modified by mpothuku on 21-Feb-06 to fix the Perf Bug #4919255
2082:
2083: SELECT 1
2084: FROM AHL_OSP_ORDER_LINES ospl,
2085: AHL_WORKORDERS wo,
2086: ahl_visit_tasks_b vts
2087: WHERE ospl.workorder_id = wo.workorder_id
2088: AND ospl.osp_order_id = p_osp_id

Line 2102: FROM AHL_OSP_ORDER_LINES a

2098: p_serial_number IN VARCHAR2,
2099: p_lot_number IN VARCHAR2,
2100: p_inv_item_uom IN VARCHAR2) IS
2101: SELECT 1
2102: FROM AHL_OSP_ORDER_LINES a
2103: WHERE a.osp_order_id = p_osp_id
2104: AND a.inventory_item_id = p_inv_item_id
2105: AND a.inventory_org_id= p_inv_org_id
2106: AND NVL(a.sub_inventory, 'X') = NVL(p_sub_inventory, 'X')

Line 2116: FROM AHL_OSP_ORDER_LINES ospl,

2112: p_csi_ii_id IN NUMBER) IS
2113:
2114: --Modified by mpothuku on 21-Feb-06 to fix the Perf Bug #4919255
2115: SELECT 1
2116: FROM AHL_OSP_ORDER_LINES ospl,
2117: AHL_WORKORDERS wo,
2118: ahl_visit_tasks_b vts
2119: WHERE ospl.workorder_id = wo.workorder_id
2120: AND ospl.osp_order_id = p_osp_id

Line 2133: FROM AHL_OSP_ORDER_LINES a

2129: p_serial_number IN VARCHAR2,
2130: p_lot_number IN VARCHAR2,
2131: p_inv_item_uom IN VARCHAR2) IS
2132: SELECT 1
2133: FROM AHL_OSP_ORDER_LINES a
2134: WHERE a.osp_order_id = p_osp_id
2135: AND a.inventory_item_id = p_inv_item_id
2136: AND a.inventory_org_id= p_inv_org_id
2137: AND NVL(a.sub_inventory, 'X') = NVL(p_sub_inventory, 'X')

Line 2163: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,

2159: --Modified by mpothuku on 21-Feb-06 to fix the Perf Bug #4919255
2160: /*
2161: SELECT distinct oe1.line_id, oe1.schedule_ship_date,
2162: oe2.line_id, oe2.schedule_ship_date
2163: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,
2164: OE_ORDER_LINES_ALL oe2, AHL_WORKORDERS_OSP_V b
2165: WHERE a.oe_ship_line_id = oe1.line_id (+)
2166: AND a.oe_return_line_id = oe2.line_id (+)
2167: AND a.workorder_id = b.workorder_id

Line 2173: FROM AHL_OSP_ORDER_LINES ospl,

2169: AND b.item_instance_id = p_csi_ii_id;
2170: */
2171: SELECT distinct oe1.line_id, oe1.schedule_ship_date,
2172: oe2.line_id, oe2.schedule_ship_date
2173: FROM AHL_OSP_ORDER_LINES ospl,
2174: OE_ORDER_LINES_ALL oe1,
2175: OE_ORDER_LINES_ALL oe2,
2176: AHL_WORKORDERS wo,
2177: ahl_visit_tasks_b vts

Line 2194: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,

2190: p_lot_number IN VARCHAR2,
2191: p_inv_item_uom IN VARCHAR2) IS
2192: SELECT oe1.line_id, oe1.schedule_ship_date,
2193: oe2.line_id, oe2.schedule_ship_date
2194: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,
2195: OE_ORDER_LINES_ALL oe2
2196: WHERE a.oe_ship_line_id = oe1.line_id (+)
2197: AND a.oe_return_line_id = oe2.line_id (+)
2198: AND a.osp_order_id = p_osp_id

Line 2243: from ahl_osp_order_lines

2239: serial_number,
2240: lot_number,
2241: inventory_item_uom,
2242: inventory_item_quantity
2243: from ahl_osp_order_lines
2244: where osp_order_id = p_osp_order_id
2245: and osp_order_line_id = p_osp_line_id;
2246:
2247: --yazhou 26-Jul-2006 starts

Line 2277: from ahl_osp_order_lines a

2273: serial_number ,
2274: lot_number ,
2275: inventory_item_uom ,
2276: inventory_item_quantity
2277: from ahl_osp_order_lines a
2278: where osp_order_id = p_osp_order_id
2279: AND ((osp_line_number = (select min(osp_line_number)
2280: from ahl_osp_order_lines
2281: where osp_order_id = p_osp_order_id

Line 2280: from ahl_osp_order_lines

2276: inventory_item_quantity
2277: from ahl_osp_order_lines a
2278: where osp_order_id = p_osp_order_id
2279: AND ((osp_line_number = (select min(osp_line_number)
2280: from ahl_osp_order_lines
2281: where osp_order_id = p_osp_order_id
2282: and inventory_item_id = a.inventory_item_id
2283: and inventory_org_id = a.inventory_org_id
2284: and serial_number = a.serial_number))

Line 2321: from ahl_osp_order_lines

2317: serial_number ,
2318: lot_number ,
2319: inventory_item_uom ,
2320: inventory_item_quantity
2321: from ahl_osp_order_lines
2322: where osp_order_id = p_osp_order_id
2323: AND (NVL(oe_ship_line_id, -9) = NVL(p_line_id, -8)
2324: OR NVL(oe_return_line_id, -9) = NVL(p_line_id, -8));
2325: */

Line 2364: from ahl_osp_order_lines ospl,

2360: decode(csi.instance_id, null, ospl.serial_number, csi.serial_number) serial_number,
2361: decode(csi.instance_id, null, ospl.lot_number, csi.lot_number) lot_number,
2362: ospl.inventory_item_uom ,
2363: ospl.inventory_item_quantity
2364: from ahl_osp_order_lines ospl,
2365: oe_order_lines_all oel,
2366: csi_t_transaction_lines tl,
2367: csi_t_txn_line_details tld,
2368: csi_item_instances csi

Line 2471: FROM ahl_osp_order_lines ospl, csi_item_instances csi

2467: csi.lot_number,
2468: csi.quantity,
2469: csi.unit_of_measure,
2470: csi.last_vld_organization_id
2471: FROM ahl_osp_order_lines ospl, csi_item_instances csi
2472: WHERE ospl.osp_order_line_id = p_osp_order_line_id and
2473: csi.instance_id = ospl.exchange_instance_id;
2474:
2475: l_exchange_inst_dtls exchange_instance_dtls_csr%rowtype;

Line 4156: FROM AHL_OSP_ORDER_LINES

4152:
4153: --
4154: CURSOR ahl_osp_order_line_csr(p_order_id IN NUMBER) IS
4155: SELECT *
4156: FROM AHL_OSP_ORDER_LINES
4157: WHERE osp_order_id = p_order_id;
4158: --
4159: l_osp_order_id NUMBER;
4160: l_osp_line_rec ahl_osp_order_line_csr%ROWTYPE;

Line 4172: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4168: p_oe_header_id => NULL );
4169:
4170: --Update the lines
4171: FOR l_osp_line_rec IN ahl_osp_order_line_csr(l_osp_order_id) LOOP
4172: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4173: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4174: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4175: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4176: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4317: --mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06

4313: --Since one item instance can not be in multiple ship lines for given
4314: --osp order, fetch all the ship/return lines for item instance
4315: --
4316: CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_osp_line_id IN NUMBER) IS
4317: --mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06
4318: SELECT a.OSP_ORDER_LINE_ID,
4319: a.OBJECT_VERSION_NUMBER,
4320: a.LAST_UPDATE_DATE,
4321: a.LAST_UPDATED_BY,

Line 4367: FROM AHL_OSP_ORDER_LINES a

4363: a.ATTRIBUTE15,
4364: -- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4365: a.PO_REQ_LINE_ID
4366: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4367: FROM AHL_OSP_ORDER_LINES a
4368: WHERE a.osp_order_id = p_osp_id
4369: AND a.osp_order_line_id = p_osp_line_id;
4370: --
4371: l_oe_ship_line_id NUMBER;

Line 4394: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4390: ELSE
4391: l_oe_return_line_id := l_osp_line_rec.oe_return_line_id;
4392: END IF;
4393:
4394: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4395: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4396: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4397: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4398: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4464: --mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06

4460: --Since one item instance can not be in multiple ship lines for given
4461: --osp order, fetch all the ship/return lines for item instance
4462: --
4463: CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_csi_ii_id IN NUMBER) IS
4464: --mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06
4465: /*
4466: SELECT a.OSP_ORDER_LINE_ID,
4467: a.OBJECT_VERSION_NUMBER,
4468: a.LAST_UPDATE_DATE,

Line 4513: -- FROM AHL_OSP_ORDER_LINES a, AHL_WORKORDERS_V b

4509: a.ATTRIBUTE13,
4510: a.ATTRIBUTE14,
4511: a.ATTRIBUTE15
4512:
4513: -- FROM AHL_OSP_ORDER_LINES a, AHL_WORKORDERS_V b
4514: -- WHERE a.workorder_id = b.workorder_id
4515: -- AND a.osp_order_id = p_osp_id
4516: -- AND b.item_instance_id = p_csi_ii_id;
4517:

Line 4518: FROM AHL_OSP_ORDER_LINES_V a

4514: -- WHERE a.workorder_id = b.workorder_id
4515: -- AND a.osp_order_id = p_osp_id
4516: -- AND b.item_instance_id = p_csi_ii_id;
4517:
4518: FROM AHL_OSP_ORDER_LINES_V a
4519: WHERE a.osp_order_id = p_osp_id
4520: AND (a.item_instance_id = p_csi_ii_id OR a.exchange_instance_id = p_csi_ii_id);
4521: */
4522:

Line 4570: FROM AHL_OSP_ORDER_LINES a,

4566: a.ATTRIBUTE15,
4567: -- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4568: a.PO_REQ_LINE_ID
4569: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4570: FROM AHL_OSP_ORDER_LINES a,
4571: ahl_workorders wo,
4572: csi_item_instances csii,
4573: ahl_visit_tasks_b vts
4574: WHERE a.osp_order_id = p_osp_id

Line 4606: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4602: ELSE
4603: l_oe_return_line_id := l_osp_line_rec.oe_return_line_id;
4604: END IF;
4605:
4606: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4607: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4608: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4609: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4610: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4741: FROM AHL_OSP_ORDER_LINES a

4737: -- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4738: a.PO_REQ_LINE_ID
4739: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4740:
4741: FROM AHL_OSP_ORDER_LINES a
4742: WHERE a.osp_order_id = p_osp_id
4743: AND a.osp_order_line_id = p_osp_line_id;
4744:
4745: --

Line 4765: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4761: FOR l_osp_line_rec IN ahl_osp_lines_csr(p_osp_order_id, p_osp_line_id)
4762: LOOP
4763: l_row_check := 'Y';
4764:
4765: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4766: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4767: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4768: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4769: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4833: FROM AHL_OSP_ORDER_LINES

4829: --
4830: --Fetch all osp lines with reference to p_oe_line_id
4831: CURSOR ahl_osp_lines_csr(p_line_id IN NUMBER) IS
4832: SELECT *
4833: FROM AHL_OSP_ORDER_LINES
4834: WHERE oe_ship_line_id = p_line_id
4835: OR oe_return_line_id = p_line_id;
4836:
4837: --

Line 4857: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4853: l_oe_return_line_id := NULL;
4854: ELSE
4855: l_oe_return_line_id := l_osp_line_rec.oe_return_line_id;
4856: END IF;
4857: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4858: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4859: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4860: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4861: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4953: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS

4949: FROM csi_item_instances
4950: WHERE instance_id = p_instance_id;
4951:
4952: -- get value for return by date.
4953: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS
4954: SELECT nvl(need_by_date, ship_by_date)
4955: FROM ahl_osp_order_lines
4956: WHERE oe_ship_line_id = p_oe_line_id;
4957:

Line 4955: FROM ahl_osp_order_lines

4951:
4952: -- get value for return by date.
4953: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS
4954: SELECT nvl(need_by_date, ship_by_date)
4955: FROM ahl_osp_order_lines
4956: WHERE oe_ship_line_id = p_oe_line_id;
4957:
4958: CURSOR get_internal_party_csr IS
4959: SELECT internal_party_id from csi_install_parameters;

Line 5311: OPEN ahl_osp_order_lines_csr(p_oe_line_id);

5307: l_txn_line_dtl_tbl(1).PRESERVE_DETAIL_FLAG := 'Y';
5308:
5309: -- get return by date.
5310: IF (p_oe_line_type = 'ORDER') THEN
5311: OPEN ahl_osp_order_lines_csr(p_oe_line_id);
5312: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;
5313: CLOSE ahl_osp_order_lines_csr;
5314:
5315: -- if null then initialise to system date.

Line 5312: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;

5308:
5309: -- get return by date.
5310: IF (p_oe_line_type = 'ORDER') THEN
5311: OPEN ahl_osp_order_lines_csr(p_oe_line_id);
5312: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;
5313: CLOSE ahl_osp_order_lines_csr;
5314:
5315: -- if null then initialise to system date.
5316: IF (l_return_by_date IS NULL) THEN

Line 5313: CLOSE ahl_osp_order_lines_csr;

5309: -- get return by date.
5310: IF (p_oe_line_type = 'ORDER') THEN
5311: OPEN ahl_osp_order_lines_csr(p_oe_line_id);
5312: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;
5313: CLOSE ahl_osp_order_lines_csr;
5314:
5315: -- if null then initialise to system date.
5316: IF (l_return_by_date IS NULL) THEN
5317: l_return_by_date := SYSDATE;

Line 5507: from ahl_osp_order_lines OSPL, oe_order_lines_all OE

5503: --mpothuku added distinct on 13-Feb-2007 for implementing the Osp Receiving feature.
5504: --If there are multiple services for the same physical item, the following query will return the same return line id multiple
5505: --times, and this leads to calls to Delete_Cancel_Order multiple times leading to a run-time error.
5506: SELECT DISTINCT OSPL.OE_RETURN_LINE_ID, OE.shipped_quantity, OE.booked_flag
5507: from ahl_osp_order_lines OSPL, oe_order_lines_all OE
5508: where OSPL.osp_order_id = p_osp_order_id AND
5509: OSPL.OE_RETURN_LINE_ID IS NOT NULL AND
5510: OE.line_id = OSPL.OE_RETURN_LINE_ID;
5511:

Line 5708: from ahl_osp_order_lines_v OSPL, oe_order_lines_all OE --, AHL_WORKORDERS_OSP_V wo

5704: SELECT OSPL.OE_SHIP_LINE_ID,
5705: ospl.item_instance_id,
5706: --wo.item_instance_id, --Jeli on 01/24/2006 for ER 4746426
5707: NVL(OE.shipped_quantity, 0)
5708: from ahl_osp_order_lines_v OSPL, oe_order_lines_all OE --, AHL_WORKORDERS_OSP_V wo
5709: where OSPL.osp_order_id = p_osp_order_id AND
5710: --ospl.workorder_id = wo.workorder_id AND
5711: OSPL.OE_SHIP_LINE_ID IS NOT NULL AND
5712: OE.line_id = OSPL.OE_SHIP_LINE_ID;

Line 5717: from ahl_osp_order_lines OSPL,

5713: */
5714: SELECT OSPL.OE_SHIP_LINE_ID,
5715: decode(wo.workorder_id, null, csii.instance_id, vts.instance_id) item_instance_id,
5716: NVL(OE.shipped_quantity, 0)
5717: from ahl_osp_order_lines OSPL,
5718: oe_order_lines_all OE,
5719: ahl_workorders wo,
5720: ahl_visit_tasks_b vts,
5721: csi_item_instances csii

Line 6375: ahl_osp_order_lines ospl,

6371: csi.serial_number,
6372: csi.last_vld_organization_id
6373: FROM csi_t_transaction_lines tl,
6374: csi_t_txn_line_details tld,
6375: ahl_osp_order_lines ospl,
6376: csi_item_instances csi
6377: WHERE tl.source_transaction_id = ospl.oe_ship_line_id
6378: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6379: AND tl.transaction_line_id = tld.transaction_line_id

Line 6394: FROM ahl_osp_order_lines ospl,

6390: nvl(oert.shipped_quantity,0) returned_quantity,
6391: ospl.status_code osp_line_status_code,
6392: osph.status_code osp_header_status_code,
6393: osph.order_type_code
6394: FROM ahl_osp_order_lines ospl,
6395: ahl_osp_orders_b osph,
6396: oe_order_lines_all oesh,
6397: oe_order_lines_all oert
6398: WHERE ospl.osp_order_line_id = c_osp_order_line_id

Line 6494: the history/consider storing the instance_id in the ahl_osp_order_lines table.

6490: details as well. Since the item and serial on the osp order line can undergo multiple part number/serial number changes,
6491: and we are not storing the instance information we are retrieving it from IB transactions.
6492: We need this instance to be present, otherwise we will not be able to pass the same to the PartNumber/Serial Number
6493: change UI. If there is an issue with this approach, we may need to retrieve the instance details from
6494: the history/consider storing the instance_id in the ahl_osp_order_lines table.
6495: */
6496: OPEN get_ship_line_instance(p_osp_order_line_id);
6497: FETCH get_ship_line_instance INTO l_ship_line_instance;
6498: IF (get_ship_line_instance%NOTFOUND) THEN

Line 6607: FROM ahl_osp_order_lines ospl,

6603: ospl.oe_ship_line_id,
6604: ospl.oe_return_line_id,
6605: retl.ship_from_org_id receiving_org_id,
6606: ospl.osp_order_id
6607: FROM ahl_osp_order_lines ospl,
6608: oe_order_lines_all retl
6609: WHERE osp_order_line_id = c_osp_order_line_id
6610: AND ospl.oe_return_line_id = retl.line_id(+);
6611:

Line 6616: ahl_osp_order_lines ospl

6612: CURSOR get_ship_line_instance(c_osp_order_line_id IN NUMBER) IS
6613: SELECT tld.instance_id
6614: FROM csi_t_transaction_lines tl,
6615: csi_t_txn_line_details tld,
6616: ahl_osp_order_lines ospl
6617: WHERE tl.source_transaction_id = ospl.oe_ship_line_id
6618: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6619: AND tl.transaction_line_id = tld.transaction_line_id
6620: AND ospl.osp_order_line_id = c_osp_order_line_id;

Line 6648: FROM ahl_osp_order_lines matched_ol,

6644: WHERE line_id = p_oe_line_id;
6645:
6646: CURSOR get_same_phyitem_order_lines(c_osp_order_line_id IN NUMBER) IS
6647: SELECT matched_ol.osp_order_line_id
6648: FROM ahl_osp_order_lines matched_ol,
6649: ahl_osp_order_lines passed_ol
6650: WHERE passed_ol.osp_order_line_id = c_osp_order_line_id
6651: AND passed_ol.inventory_item_id = matched_ol.inventory_item_id
6652: AND passed_ol.serial_number = matched_ol.serial_number;

Line 6649: ahl_osp_order_lines passed_ol

6645:
6646: CURSOR get_same_phyitem_order_lines(c_osp_order_line_id IN NUMBER) IS
6647: SELECT matched_ol.osp_order_line_id
6648: FROM ahl_osp_order_lines matched_ol,
6649: ahl_osp_order_lines passed_ol
6650: WHERE passed_ol.osp_order_line_id = c_osp_order_line_id
6651: AND passed_ol.inventory_item_id = matched_ol.inventory_item_id
6652: AND passed_ol.serial_number = matched_ol.serial_number;
6653:

Line 6811: the history/consider storing the instance_id in the ahl_osp_order_lines table.

6807: details as well. Since the item and serial on the osp order line can undergo multiple part number/serial number changes,
6808: and we are not storing the instance information we are retrieving it from IB transactions.
6809: We need this instance to be present, otherwise we will not be able to pass the same to the PartNumber/Serial Number
6810: change UI. If there is an issue with this approach, we may need to retrieve the instance details from
6811: the history/consider storing the instance_id in the ahl_osp_order_lines table.
6812: */
6813:
6814: --If passed instance_id is null, derive it from the osp line, else check that its the one present on the osp line.
6815: OPEN get_ship_line_instance(l_serialnum_change_rec.osp_line_id);