[Home] [Help]
22: | B172755 04/09/01 HW Need to add sublot_no is NULL to sql statement |
23: | when sublot_number is null because sublot_no is |
24: | part of the key |
25: | B1826752 06/08/01 HW Issue with clearing out lot information |
26: | when updating wsh_delivery_details. |
27: | in GMI_APPLY_BACKORDER_UPDATES |
28: | B1854224 06/27/01 HW close the comment for bug 1826752 in |
29: | GMI_APPLY_BACKORDER_UPDATES |
30: | B2547509 12/06/01 Uday Phadtare Adding the message returned by |
56: | by procedure |
57: +=========================================================================+
58: */
59: PROCEDURE GMI_UPDATE_SHIPMENT_TXN_new
60: ( p_shipping_line IN wsh_delivery_details%ROWTYPE
61: , p_actual_ship_date IN DATE
62: , x_return_status OUT NOCOPY VARCHAR2
63: , x_msg_count OUT NOCOPY NUMBER
64: , x_msg_data OUT NOCOPY VARCHAR2
75:
76: INVENTORY_NEG_WARNING EXCEPTION;
77:
78: PROCEDURE GMI_CREATE_BACKORDER_TXN
79: ( p_shipping_line IN wsh_delivery_details%ROWTYPE
80: , x_return_status OUT NOCOPY VARCHAR2
81: , x_msg_count OUT NOCOPY NUMBER
82: , x_msg_data OUT NOCOPY VARCHAR2
83: )
665: END unreserve_inv;
666:
667: -- this is obsoleted once the WSH had made changes for backorder, new int, omchanges
668: PROCEDURE GMI_UPDATE_SHIPMENT_TXN
669: ( p_shipping_line IN wsh_delivery_details%ROWTYPE
670: , p_actual_ship_date IN DATE
671: , x_return_status OUT NOCOPY VARCHAR2
672: , x_msg_count OUT NOCOPY NUMBER
673: , x_msg_data OUT NOCOPY VARCHAR2
766: AND line_id = p_shipping_line.source_line_id
767: AND line_detail_id = p_shipping_line.delivery_detail_id
768: AND delete_mark = 1
769: AND exists (select 1
770: from wsh_delivery_Details
771: where line_id = p_shipping_line.source_line_id
772: and delivery_detail_id = p_shipping_line.delivery_detail_id
773: and shipped_quantity = (-1)*itp.trans_qty);
774:
1544: END GMI_UPDATE_SHIPMENT_TXN;
1545:
1546: /* rewritten after OM changes from wsh */
1547: PROCEDURE GMI_UPDATE_SHIPMENT_TXN_new
1548: ( p_shipping_line IN wsh_delivery_details%ROWTYPE
1549: , p_actual_ship_date IN DATE
1550: , x_return_status OUT NOCOPY VARCHAR2
1551: , x_msg_count OUT NOCOPY NUMBER
1552: , x_msg_data OUT NOCOPY VARCHAR2
1594: AND line_id = p_shipping_line.source_line_id
1595: AND line_detail_id = p_shipping_line.delivery_detail_id
1596: AND delete_mark = 1
1597: AND exists (select 1
1598: from wsh_delivery_Details
1599: where line_id = p_shipping_line.source_line_id
1600: and delivery_detail_id = p_shipping_line.delivery_detail_id
1601: and shipped_quantity = (-1)*itp.trans_qty);
1602:
1836:
1837: Cursor get_bad_delivery IS
1838: Select delivery_detail_id , source_line_id,
1839: released_status,lot_number,sublot_number
1840: From wsh_delivery_details
1841: Where source_line_id = p_source_line_id
1842: AND released_status NOT IN ('C','Y');
1843: -- HW END OF BUG#:1826752
1844:
1845:
1846:
1847: Cursor get_move_order IS
1848: SELECT move_order_line_id, released_status
1849: FROM wsh_delivery_details
1850: WHERE source_line_id = p_source_line_id
1851: AND released_status = 'S';
1852: /* odaboval : Oct-2000, in the where_clause, taken the */
1853: /* p_source_line_id instead of p_original_source_line_id, in order to */
1919:
1920: /* B1504749, 5-Dec-2000 odaboval : added released_status clause */
1921: -- HW BUG#:1854224 closed the comment properly
1922: /* HW BUG#:1826752 exclude shipped (C) and staged (Y) */
1923: UPDATE wsh_delivery_details
1924: SET locator_id = NULL,
1925: lot_number = NULL,
1926: sublot_number = NULL
1927: WHERE source_line_id = p_source_line_id
1930: /* HAM RE_CHECK */
1931: /* and ship_confirm_action_flag = 'B'; */
1932: IF (SQL%NOTFOUND)
1933: THEN
1934: GMI_RESERVATION_UTIL.println('No Update in wsh_delivery_details, line_id='||p_source_line_id);
1935:
1936: -- x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1937: -- RAISE NO_DATA_FOUND;
1938: END IF;
2005: l_line_id ic_tran_pnd.line_id%TYPE;
2006: l_item_id ic_tran_pnd.item_id%TYPE;
2007: l_location ic_tran_pnd.location%TYPE;
2008: l_lot_id ic_tran_pnd.lot_id%TYPE;
2009: l_line_detail_id wsh_delivery_details.delivery_detail_id%TYPE;
2010: l_reduction_factor NUMBER;
2011: l_delta_trans_qty2 NUMBER;
2012: l_trans_id NUMBER;
2013: l_staged_ind NUMBER;
2279: reveiving transaction processer
2280: */
2281:
2282: PROCEDURE create_rcv_transaction
2283: ( p_shipping_line IN wsh_delivery_details%ROWTYPE
2284: , p_trip_stop_rec IN wsh_trip_stops%ROWTYPE
2285: , p_group_id IN NUMBER
2286: , x_return_status OUT NOCOPY VARCHAR2
2287: , x_msg_count OUT NOCOPY NUMBER
2311: rc NUMBER := NULL;
2312: l_transaction_id NUMBER := NULL;
2313: l_charge_account_id NUMBER;
2314: l_accrual_account_id NUMBER;
2315: l_detail_rec wsh_delivery_details%ROWTYPE;
2316: l_trip_stop_rec wsh_trip_stops%ROWTYPE;
2317: l_item_desc VARCHAR2(240) := NULL;
2318: l_unit_of_measure VARCHAR2(25);
2319: l_secondary_unit_of_measure VARCHAR2(25);
2334: SELECT a.delivery_id
2335: , d.waybill
2336: , d.ultimate_dropoff_date
2337: from wsh_delivery_assignments a,
2338: wsh_delivery_details dd,
2339: wsh_new_deliveries d
2340: where a.delivery_detail_id = dd.delivery_detail_id
2341: and d.delivery_id = a.delivery_id
2342: and dd.delivery_detail_id = p_shipping_line.delivery_detail_id
2831: -- Bug 3764091 Added NOT EXISTS to the cursor below so that wdd which is already synchronized to PO
2832: -- side is not inserted again in rcv_transactions_interface.
2833: CURSOR c_detail_in_delivery is
2834: SELECT dd.delivery_detail_id, dd.source_line_id
2835: FROM wsh_delivery_details dd, wsh_delivery_assignments da
2836: WHERE dd.delivery_detail_id = da.delivery_detail_id
2837: AND da.delivery_id = l_delivery_id
2838: AND NVL(dd.inv_interfaced_flag , 'N') = 'Y'
2839: AND dd.container_flag = 'N'
2858: rc NUMBER := NULL;
2859: l_group_id NUMBER := 0;
2860:
2861: CURSOR c_details_for_interface(p_del_detail_id number) is
2862: SELECT * from wsh_delivery_details
2863: where delivery_detail_id = p_del_detail_id
2864: and container_flag = 'N';
2865: l_detail_rec c_details_for_interface%ROWTYPE;
2866:
3004: , wdd.locator_id LOCATOR_ID
3005: , wdd.lot_number
3006: , wdd.sublot_number
3007: , wdd.released_status SHIP_STATUS
3008: FROM wsh_delivery_details wdd
3009: , oe_order_headers_all soh
3010: , oe_order_lines_all sol
3011: , mtl_parameters mp1
3012: , mtl_parameters mp2
3065: , wdd.organization_id SHIP_ORG
3066: , wdd.delivery_detail_id SHIP_ID
3067: , wdd.SHIPPED_QUANTITY SHIP_QTY
3068: FROM ic_txn_request_lines mo
3069: , wsh_delivery_details wdd
3070: , oe_order_headers_all soh
3071: , oe_order_lines_all sol
3072: , mtl_parameters mp1
3073: , mtl_parameters mp2
3101: Cursor count_unstaged_wdd_for_mo (p_line_id IN NUMBER)
3102: IS
3103: Select count(*)
3104: FROM ic_txn_request_lines mo
3105: , wsh_delivery_details wdd
3106: Where mo.txn_source_line_id = p_line_id
3107: and mo.line_status in (3,7)
3108: and mo.line_id = wdd.move_order_line_id
3109: and mo.txn_source_line_id = wdd.source_line_id
3336:
3337: Select wdd.delivery_detail_id
3338: Into l_line_detail_id
3339: FROM ic_txn_request_lines mo
3340: , wsh_delivery_details wdd
3341: Where mo.txn_source_line_id = lines.line_id
3342: and mo.line_status in (3,7)
3343: and mo.line_id = wdd.move_order_line_id
3344: and mo.txn_source_line_id = wdd.source_line_id
3506: l_trans_id ic_tran_pnd.trans_id%TYPE;
3507: l_item_id ic_tran_pnd.item_id%TYPE;
3508: l_location ic_tran_pnd.location%TYPE;
3509: l_lot_id ic_tran_pnd.lot_id%TYPE;
3510: l_line_detail_id wsh_delivery_details.delivery_detail_id%TYPE;
3511: l_new_delivery_detail_id NUMBER;
3512: l_source_line_id NUMBER;
3513: l_fulfilled_qty NUMBER;
3514: l_qty_to_fulfil NUMBER;
3544: , released_status
3545: , source_line_id
3546: , inventory_item_id
3547: , organization_id
3548: From wsh_delivery_details
3549: Where delivery_detail_id = p_old_delivery_detail_id;
3550:
3551: Cursor Get_item_info IS
3552: Select ic.item_id
4003: l_trans_id ic_tran_pnd.trans_id%TYPE;
4004: l_item_id ic_tran_pnd.item_id%TYPE;
4005: l_location ic_tran_pnd.location%TYPE;
4006: l_lot_id ic_tran_pnd.lot_id%TYPE;
4007: l_line_detail_id wsh_delivery_details.delivery_detail_id%TYPE;
4008: l_source_line_id NUMBER;
4009: l_inventory_item_id NUMBER;
4010: l_organization_id NUMBER;
4011: l_whse_code VARCHAR2(5);
4020: Cursor get_wdd_info IS
4021: Select source_line_id
4022: , organization_id
4023: , inventory_item_id
4024: From wsh_delivery_details
4025: Where delivery_detail_id = p_delivery_detail_id;
4026:
4027: cursor c_reservations IS
4028: SELECT trans_id
4392: -- Find the proper source_line_Id from WSH using
4393: -- the consolidated dd_id
4394: CURSOR find_new_source_line (l_dd_id NUMBER) IS
4395: SELECT WDD.source_line_id
4396: FROM WSH_DELIVERY_DETAILS WDD
4397: WHERE WDD.delivery_detail_id = l_dd_id
4398: AND WDD.container_flag = 'N'
4399: AND WDD.source_code = 'OE'
4400: AND WDD.released_status='B';
4419:
4420: FETCH find_new_source_line into l_new_source_line_id;
4421: IF ( find_new_source_line%NOTFOUND) THEN
4422: CLOSE find_new_source_line;
4423: gmi_reservation_util.println('Can not find source_line from WSH_DELIVERY_DETAILS for source_line'||p_cons_dd_id);
4424: x_return_status := FND_API.G_RET_STS_ERROR;
4425: RETURN;
4426: END IF;
4427: CLOSE find_new_source_line;