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 355: FROM ahl_osp_order_lines

351:
352: -- salogan added for bug 9722617 Begin
353: CURSOR is_osp_line_id_valid_csr(p_osp_line_id IN NUMBER, p_osp_order_id IN NUMBER) IS
354: SELECT 1
355: FROM ahl_osp_order_lines
356: WHERE osp_order_line_id = p_osp_line_id
357: AND osp_order_id = p_osp_order_id;
358: -- salogan added for bug 9722617 End
359:

Line 1659: FROM ahl_osp_order_lines

1655: where organization_id = p_customer_site_id;
1656:
1657: CURSOR ahl_get_ship_from_org_csr(p_osp_order_id IN NUMBER) IS
1658: SELECT inventory_org_id
1659: FROM ahl_osp_order_lines
1660: WHERE osp_order_id = p_osp_order_id
1661: ORDER BY osp_order_line_id;
1662:
1663: l_cust_id NUMBER;

Line 2025: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl

2021: ospl.osp_order_id,
2022: ospl.osp_order_line_id,
2023: ospl.osp_line_number,
2024: ospl.exchange_instance_id
2025: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl
2026: WHERE ospl.workorder_id = wo.workorder_id
2027: AND wo.job_number = p_wo_name
2028: AND ospl.osp_order_id = p_osp_id
2029: AND ospl.status_code IS NULL;

Line 2051: from ahl_osp_order_lines

2047: and (active_end_date is null or active_end_date > sysdate)
2048: and inv_service_item_rel_id in (select inv_service_item_rel_id
2049: from ahl_inv_service_item_rels
2050: where service_item_id in (select service_item_id
2051: from ahl_osp_order_lines
2052: where osp_order_id = p_osp_order_id
2053: and inventory_item_id = p_inventory_item_id
2054: and inventory_org_id = p_inventory_org_id
2055: and serial_number = p_serial_number

Line 2077: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl

2073: ospl.osp_order_id,
2074: ospl.osp_order_line_id,
2075: ospl.osp_line_number,
2076: ospl.exchange_instance_id
2077: FROM AHL_WORKORDERS_OSP_V wo, AHL_OSP_ORDER_LINES ospl
2078: WHERE ospl.workorder_id = wo.workorder_id
2079: AND wo.item_instance_id = p_instance_id
2080: AND ospl.osp_order_id = p_osp_id
2081: AND ospl.status_code IS NULL;

Line 2099: FROM AHL_OSP_ORDER_LINES

2095: --For a given osp line id, check if shipment line exists
2096: CURSOR ahl_oe_ship_id_csr(p_osp_id IN NUMBER,
2097: p_osp_line_id IN NUMBER)IS
2098: SELECT 1
2099: FROM AHL_OSP_ORDER_LINES
2100: WHERE osp_order_id = p_osp_id
2101: AND osp_order_line_id = p_osp_line_id
2102: AND oe_ship_line_id IS NOT NULL;
2103:

Line 2108: FROM AHL_OSP_ORDER_LINES

2104: --For a given osp line id, check if return line exists
2105: CURSOR ahl_oe_return_id_csr(p_osp_id IN NUMBER,
2106: p_osp_line_id IN NUMBER) IS
2107: SELECT 1
2108: FROM AHL_OSP_ORDER_LINES
2109: WHERE osp_order_id = p_osp_id
2110: AND osp_order_line_id = p_osp_line_id
2111: AND oe_return_line_id IS NOT NULL;
2112:

Line 2119: FROM AHL_OSP_ORDER_LINES ospl,

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

Line 2137: FROM AHL_OSP_ORDER_LINES a

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

Line 2151: FROM AHL_OSP_ORDER_LINES ospl,

2147: p_csi_ii_id IN NUMBER) IS
2148:
2149: --Modified by mpothuku on 21-Feb-06 to fix the Perf Bug #4919255
2150: SELECT 1
2151: FROM AHL_OSP_ORDER_LINES ospl,
2152: AHL_WORKORDERS wo,
2153: ahl_visit_tasks_b vts
2154: WHERE ospl.workorder_id = wo.workorder_id
2155: AND ospl.osp_order_id = p_osp_id

Line 2168: FROM AHL_OSP_ORDER_LINES a

2164: p_serial_number IN VARCHAR2,
2165: p_lot_number IN VARCHAR2,
2166: p_inv_item_uom IN VARCHAR2) IS
2167: SELECT 1
2168: FROM AHL_OSP_ORDER_LINES a
2169: WHERE a.osp_order_id = p_osp_id
2170: AND a.inventory_item_id = p_inv_item_id
2171: AND a.inventory_org_id= p_inv_org_id
2172: AND NVL(a.sub_inventory, 'X') = NVL(p_sub_inventory, 'X')

Line 2198: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,

2194: --Modified by mpothuku on 21-Feb-06 to fix the Perf Bug #4919255
2195: /*
2196: SELECT distinct oe1.line_id, oe1.schedule_ship_date,
2197: oe2.line_id, oe2.schedule_ship_date
2198: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,
2199: OE_ORDER_LINES_ALL oe2, AHL_WORKORDERS_OSP_V b
2200: WHERE a.oe_ship_line_id = oe1.line_id (+)
2201: AND a.oe_return_line_id = oe2.line_id (+)
2202: AND a.workorder_id = b.workorder_id

Line 2208: FROM AHL_OSP_ORDER_LINES ospl,

2204: AND b.item_instance_id = p_csi_ii_id;
2205: */
2206: SELECT distinct oe1.line_id, oe1.schedule_ship_date,
2207: oe2.line_id, oe2.schedule_ship_date
2208: FROM AHL_OSP_ORDER_LINES ospl,
2209: OE_ORDER_LINES_ALL oe1,
2210: OE_ORDER_LINES_ALL oe2,
2211: AHL_WORKORDERS wo,
2212: ahl_visit_tasks_b vts

Line 2229: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,

2225: p_lot_number IN VARCHAR2,
2226: p_inv_item_uom IN VARCHAR2) IS
2227: SELECT oe1.line_id, oe1.schedule_ship_date,
2228: oe2.line_id, oe2.schedule_ship_date
2229: FROM AHL_OSP_ORDER_LINES a, OE_ORDER_LINES_ALL oe1,
2230: OE_ORDER_LINES_ALL oe2
2231: WHERE a.oe_ship_line_id = oe1.line_id (+)
2232: AND a.oe_return_line_id = oe2.line_id (+)
2233: AND a.osp_order_id = p_osp_id

Line 2278: from ahl_osp_order_lines

2274: serial_number,
2275: lot_number,
2276: inventory_item_uom,
2277: inventory_item_quantity
2278: from ahl_osp_order_lines
2279: where osp_order_id = p_osp_order_id
2280: and osp_order_line_id = p_osp_line_id;
2281:
2282: --yazhou 26-Jul-2006 starts

Line 2312: from ahl_osp_order_lines a

2308: serial_number ,
2309: lot_number ,
2310: inventory_item_uom ,
2311: inventory_item_quantity
2312: from ahl_osp_order_lines a
2313: where osp_order_id = p_osp_order_id
2314: AND ((osp_line_number = (select min(osp_line_number)
2315: from ahl_osp_order_lines
2316: where osp_order_id = p_osp_order_id

Line 2315: from ahl_osp_order_lines

2311: inventory_item_quantity
2312: from ahl_osp_order_lines a
2313: where osp_order_id = p_osp_order_id
2314: AND ((osp_line_number = (select min(osp_line_number)
2315: from ahl_osp_order_lines
2316: where osp_order_id = p_osp_order_id
2317: and inventory_item_id = a.inventory_item_id
2318: and inventory_org_id = a.inventory_org_id
2319: and serial_number = a.serial_number))

Line 2356: from ahl_osp_order_lines

2352: serial_number ,
2353: lot_number ,
2354: inventory_item_uom ,
2355: inventory_item_quantity
2356: from ahl_osp_order_lines
2357: where osp_order_id = p_osp_order_id
2358: AND (NVL(oe_ship_line_id, -9) = NVL(p_line_id, -8)
2359: OR NVL(oe_return_line_id, -9) = NVL(p_line_id, -8));
2360: */

Line 2399: from ahl_osp_order_lines ospl,

2395: decode(csi.instance_id, null, ospl.serial_number, csi.serial_number) serial_number,
2396: decode(csi.instance_id, null, ospl.lot_number, csi.lot_number) lot_number,
2397: ospl.inventory_item_uom ,
2398: ospl.inventory_item_quantity
2399: from ahl_osp_order_lines ospl,
2400: oe_order_lines_all oel,
2401: csi_t_transaction_lines tl,
2402: csi_t_txn_line_details tld,
2403: csi_item_instances csi

Line 2506: FROM ahl_osp_order_lines ospl, csi_item_instances csi

2502: csi.lot_number,
2503: csi.quantity,
2504: csi.unit_of_measure,
2505: csi.last_vld_organization_id
2506: FROM ahl_osp_order_lines ospl, csi_item_instances csi
2507: WHERE ospl.osp_order_line_id = p_osp_order_line_id and
2508: csi.instance_id = ospl.exchange_instance_id;
2509:
2510: l_exchange_inst_dtls exchange_instance_dtls_csr%rowtype;

Line 4140: FROM AHL_OSP_ORDER_LINES

4136:
4137: --
4138: CURSOR ahl_osp_order_line_csr(p_order_id IN NUMBER) IS
4139: SELECT *
4140: FROM AHL_OSP_ORDER_LINES
4141: WHERE osp_order_id = p_order_id;
4142: --
4143: l_osp_order_id NUMBER;
4144: l_osp_line_rec ahl_osp_order_line_csr%ROWTYPE;

Line 4156: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4152: p_oe_header_id => NULL );
4153:
4154: --Update the lines
4155: FOR l_osp_line_rec IN ahl_osp_order_line_csr(l_osp_order_id) LOOP
4156: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4157: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4158: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4159: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4160: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

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

4297: --Since one item instance can not be in multiple ship lines for given
4298: --osp order, fetch all the ship/return lines for item instance
4299: --
4300: CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_osp_line_id IN NUMBER) IS
4301: --mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06
4302: SELECT a.OSP_ORDER_LINE_ID,
4303: a.OBJECT_VERSION_NUMBER,
4304: a.LAST_UPDATE_DATE,
4305: a.LAST_UPDATED_BY,

Line 4351: FROM AHL_OSP_ORDER_LINES a

4347: a.ATTRIBUTE15,
4348: -- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4349: a.PO_REQ_LINE_ID
4350: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4351: FROM AHL_OSP_ORDER_LINES a
4352: WHERE a.osp_order_id = p_osp_id
4353: AND a.osp_order_line_id = p_osp_line_id;
4354: --
4355: l_oe_ship_line_id NUMBER;

Line 4378: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4374: ELSE
4375: l_oe_return_line_id := l_osp_line_rec.oe_return_line_id;
4376: END IF;
4377:
4378: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4379: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4380: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4381: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4382: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

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

4444: --Since one item instance can not be in multiple ship lines for given
4445: --osp order, fetch all the ship/return lines for item instance
4446: --
4447: CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_csi_ii_id IN NUMBER) IS
4448: --mpothuku removed the usage of AHL_OSP_ORDER_LINES_V usage for fixing the perf Bug# 4919255 on 21-Feb-06
4449: /*
4450: SELECT a.OSP_ORDER_LINE_ID,
4451: a.OBJECT_VERSION_NUMBER,
4452: a.LAST_UPDATE_DATE,

Line 4497: -- FROM AHL_OSP_ORDER_LINES a, AHL_WORKORDERS_V b

4493: a.ATTRIBUTE13,
4494: a.ATTRIBUTE14,
4495: a.ATTRIBUTE15
4496:
4497: -- FROM AHL_OSP_ORDER_LINES a, AHL_WORKORDERS_V b
4498: -- WHERE a.workorder_id = b.workorder_id
4499: -- AND a.osp_order_id = p_osp_id
4500: -- AND b.item_instance_id = p_csi_ii_id;
4501:

Line 4502: FROM AHL_OSP_ORDER_LINES_V a

4498: -- WHERE a.workorder_id = b.workorder_id
4499: -- AND a.osp_order_id = p_osp_id
4500: -- AND b.item_instance_id = p_csi_ii_id;
4501:
4502: FROM AHL_OSP_ORDER_LINES_V a
4503: WHERE a.osp_order_id = p_osp_id
4504: AND (a.item_instance_id = p_csi_ii_id OR a.exchange_instance_id = p_csi_ii_id);
4505: */
4506:

Line 4554: FROM AHL_OSP_ORDER_LINES a,

4550: a.ATTRIBUTE15,
4551: -- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4552: a.PO_REQ_LINE_ID
4553: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4554: FROM AHL_OSP_ORDER_LINES a,
4555: ahl_workorders wo,
4556: csi_item_instances csii,
4557: ahl_visit_tasks_b vts
4558: WHERE a.osp_order_id = p_osp_id

Line 4614: FROM AHL_OSP_ORDER_LINES a,

4610: a.ATTRIBUTE15,
4611: -- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4612: a.PO_REQ_LINE_ID
4613: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4614: FROM AHL_OSP_ORDER_LINES a,
4615: ahl_workorders wo,
4616: csi_item_instances csii,
4617: ahl_visit_tasks_b vts
4618: WHERE a.osp_order_id = p_osp_id

Line 4651: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4647: ELSE
4648: l_oe_return_line_id := l_osp_line_rec.oe_return_line_id;
4649: END IF;
4650:
4651: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4652: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4653: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4654: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4655: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4786: FROM AHL_OSP_ORDER_LINES a

4782: -- Begin Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4783: a.PO_REQ_LINE_ID
4784: -- End Changes by jaramana on January 14, 2008 for the Requisition ER 6034236
4785:
4786: FROM AHL_OSP_ORDER_LINES a
4787: WHERE a.osp_order_id = p_osp_id
4788: AND a.osp_order_line_id = p_osp_line_id;
4789:
4790: --

Line 4810: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4806: FOR l_osp_line_rec IN ahl_osp_lines_csr(p_osp_order_id, p_osp_line_id)
4807: LOOP
4808: l_row_check := 'Y';
4809:
4810: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4811: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4812: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4813: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4814: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4878: FROM AHL_OSP_ORDER_LINES

4874: --
4875: --Fetch all osp lines with reference to p_oe_line_id
4876: CURSOR ahl_osp_lines_csr(p_line_id IN NUMBER) IS
4877: SELECT *
4878: FROM AHL_OSP_ORDER_LINES
4879: WHERE oe_ship_line_id = p_line_id
4880: OR oe_return_line_id = p_line_id;
4881:
4882: --

Line 4902: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (

4898: l_oe_return_line_id := NULL;
4899: ELSE
4900: l_oe_return_line_id := l_osp_line_rec.oe_return_line_id;
4901: END IF;
4902: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
4903: P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
4904: P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
4905: P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
4906: P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,

Line 4998: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS

4994: FROM csi_item_instances
4995: WHERE instance_id = p_instance_id;
4996:
4997: -- get value for return by date.
4998: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS
4999: SELECT nvl(need_by_date, ship_by_date)
5000: FROM ahl_osp_order_lines
5001: WHERE oe_ship_line_id = p_oe_line_id;
5002:

Line 5000: FROM ahl_osp_order_lines

4996:
4997: -- get value for return by date.
4998: CURSOR ahl_osp_order_lines_csr (p_oe_line_id IN NUMBER) IS
4999: SELECT nvl(need_by_date, ship_by_date)
5000: FROM ahl_osp_order_lines
5001: WHERE oe_ship_line_id = p_oe_line_id;
5002:
5003: CURSOR get_internal_party_csr IS
5004: SELECT internal_party_id from csi_install_parameters;

Line 5356: OPEN ahl_osp_order_lines_csr(p_oe_line_id);

5352: l_txn_line_dtl_tbl(1).PRESERVE_DETAIL_FLAG := 'Y';
5353:
5354: -- get return by date.
5355: IF (p_oe_line_type = 'ORDER') THEN
5356: OPEN ahl_osp_order_lines_csr(p_oe_line_id);
5357: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;
5358: CLOSE ahl_osp_order_lines_csr;
5359:
5360: -- if null then initialise to system date.

Line 5357: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;

5353:
5354: -- get return by date.
5355: IF (p_oe_line_type = 'ORDER') THEN
5356: OPEN ahl_osp_order_lines_csr(p_oe_line_id);
5357: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;
5358: CLOSE ahl_osp_order_lines_csr;
5359:
5360: -- if null then initialise to system date.
5361: IF (l_return_by_date IS NULL) THEN

Line 5358: CLOSE ahl_osp_order_lines_csr;

5354: -- get return by date.
5355: IF (p_oe_line_type = 'ORDER') THEN
5356: OPEN ahl_osp_order_lines_csr(p_oe_line_id);
5357: FETCH ahl_osp_order_lines_csr INTO l_return_by_date;
5358: CLOSE ahl_osp_order_lines_csr;
5359:
5360: -- if null then initialise to system date.
5361: IF (l_return_by_date IS NULL) THEN
5362: l_return_by_date := SYSDATE;

Line 5552: from ahl_osp_order_lines OSPL, oe_order_lines_all OE

5548: --mpothuku added distinct on 13-Feb-2007 for implementing the Osp Receiving feature.
5549: --If there are multiple services for the same physical item, the following query will return the same return line id multiple
5550: --times, and this leads to calls to Delete_Cancel_Order multiple times leading to a run-time error.
5551: SELECT DISTINCT OSPL.OE_RETURN_LINE_ID, OE.shipped_quantity, OE.booked_flag
5552: from ahl_osp_order_lines OSPL, oe_order_lines_all OE
5553: where OSPL.osp_order_id = p_osp_order_id AND
5554: OSPL.OE_RETURN_LINE_ID IS NOT NULL AND
5555: OE.line_id = OSPL.OE_RETURN_LINE_ID;
5556:

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

5749: SELECT OSPL.OE_SHIP_LINE_ID,
5750: ospl.item_instance_id,
5751: --wo.item_instance_id, --Jeli on 01/24/2006 for ER 4746426
5752: NVL(OE.shipped_quantity, 0)
5753: from ahl_osp_order_lines_v OSPL, oe_order_lines_all OE --, AHL_WORKORDERS_OSP_V wo
5754: where OSPL.osp_order_id = p_osp_order_id AND
5755: --ospl.workorder_id = wo.workorder_id AND
5756: OSPL.OE_SHIP_LINE_ID IS NOT NULL AND
5757: OE.line_id = OSPL.OE_SHIP_LINE_ID;

Line 5762: from ahl_osp_order_lines OSPL,

5758: */
5759: SELECT OSPL.OE_SHIP_LINE_ID,
5760: decode(wo.workorder_id, null, csii.instance_id, vts.instance_id) item_instance_id,
5761: NVL(OE.shipped_quantity, 0)
5762: from ahl_osp_order_lines OSPL,
5763: oe_order_lines_all OE,
5764: ahl_workorders wo,
5765: ahl_visit_tasks_b vts,
5766: csi_item_instances csii

Line 6420: ahl_osp_order_lines ospl,

6416: csi.serial_number,
6417: csi.last_vld_organization_id
6418: FROM csi_t_transaction_lines tl,
6419: csi_t_txn_line_details tld,
6420: ahl_osp_order_lines ospl,
6421: csi_item_instances csi
6422: WHERE tl.source_transaction_id = ospl.oe_ship_line_id
6423: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6424: AND tl.transaction_line_id = tld.transaction_line_id

Line 6439: FROM ahl_osp_order_lines ospl,

6435: nvl(oert.shipped_quantity,0) returned_quantity,
6436: ospl.status_code osp_line_status_code,
6437: osph.status_code osp_header_status_code,
6438: osph.order_type_code
6439: FROM ahl_osp_order_lines ospl,
6440: ahl_osp_orders_b osph,
6441: oe_order_lines_all oesh,
6442: oe_order_lines_all oert
6443: WHERE ospl.osp_order_line_id = c_osp_order_line_id

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

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

Line 6652: FROM ahl_osp_order_lines ospl,

6648: ospl.oe_ship_line_id,
6649: ospl.oe_return_line_id,
6650: retl.ship_from_org_id receiving_org_id,
6651: ospl.osp_order_id
6652: FROM ahl_osp_order_lines ospl,
6653: oe_order_lines_all retl
6654: WHERE osp_order_line_id = c_osp_order_line_id
6655: AND ospl.oe_return_line_id = retl.line_id(+);
6656:

Line 6661: ahl_osp_order_lines ospl

6657: CURSOR get_ship_line_instance(c_osp_order_line_id IN NUMBER) IS
6658: SELECT tld.instance_id
6659: FROM csi_t_transaction_lines tl,
6660: csi_t_txn_line_details tld,
6661: ahl_osp_order_lines ospl
6662: WHERE tl.source_transaction_id = ospl.oe_ship_line_id
6663: AND tl.source_transaction_table = G_TRANSACTION_TABLE
6664: AND tl.transaction_line_id = tld.transaction_line_id
6665: AND ospl.osp_order_line_id = c_osp_order_line_id;

Line 6693: FROM ahl_osp_order_lines matched_ol,

6689: WHERE line_id = p_oe_line_id;
6690:
6691: CURSOR get_same_phyitem_order_lines(c_osp_order_line_id IN NUMBER) IS
6692: SELECT matched_ol.osp_order_line_id
6693: FROM ahl_osp_order_lines matched_ol,
6694: ahl_osp_order_lines passed_ol
6695: WHERE passed_ol.osp_order_line_id = c_osp_order_line_id
6696: AND passed_ol.inventory_item_id = matched_ol.inventory_item_id
6697: AND passed_ol.serial_number = matched_ol.serial_number

Line 6694: ahl_osp_order_lines passed_ol

6690:
6691: CURSOR get_same_phyitem_order_lines(c_osp_order_line_id IN NUMBER) IS
6692: SELECT matched_ol.osp_order_line_id
6693: FROM ahl_osp_order_lines matched_ol,
6694: ahl_osp_order_lines passed_ol
6695: WHERE passed_ol.osp_order_line_id = c_osp_order_line_id
6696: AND passed_ol.inventory_item_id = matched_ol.inventory_item_id
6697: AND passed_ol.serial_number = matched_ol.serial_number
6698: -- Added by jaramana on 12-APR-2010 for bug 9229301

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

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