[Home] [Help]
774: ---- end of patchset J bulk picking -------------------
775: if (g_trace_on = 1) then log_event('Insert transaction temp id:'||mmtt_rec.transaction_temp_id); end if;
776: if (g_trace_on = 1) then log_event('transaction quantity:'||mmtt_rec.transaction_quantity); end if;
777: if (g_trace_on = 1) then log_event('secondary transaction quantity:'||mmtt_rec.secondary_transaction_quantity); end if; --invconv kkillams
778: INSERT INTO wms_cartonization_temp
779: (transaction_header_id,
780: transaction_temp_id,
781: source_code,
782: source_line_id,
1974: l_clpn_id,
1975: l_cartonization_id,
1976: l_citem_id
1977: FROM
1978: wms_cartonization_temp
1979: WHERE
1980: transaction_temp_id = p_transaction_temp_id;
1981:
1982: if (g_trace_on = 1) then
2672: error_code := 'GET_PAR';
2673: if (g_trace_on = 1) then log_event(' Enter get_parent for child '||p_child); END IF;
2674:
2675: --Bug9830363(Base Bug : 9749394) added hint
2676: SELECT /*+ index(WMS_CARTONIZATION_TEMP WMS_CARTONIZATION_TEMP_N1) */
2677: cartonization_id, container_item_id
2678: INTO
2679: x_par_cart_id, x_par_cont_id
2680: FROM wms_cartonization_temp
2676: SELECT /*+ index(WMS_CARTONIZATION_TEMP WMS_CARTONIZATION_TEMP_N1) */
2677: cartonization_id, container_item_id
2678: INTO
2679: x_par_cart_id, x_par_cont_id
2680: FROM wms_cartonization_temp
2681: WHERE
2682: content_lpn_id = p_child AND
2683: transaction_header_id < 0;
2684:
2724: transaction_temp_id,
2725: cartonization_id,
2726: CONTAINER_ITEM_ID
2727: FROM
2728: wms_cartonization_temp
2729: WHERE
2730: transaction_header_id = p_header_id
2731: --Bug 2864774 fix
2732: ORDER BY cartonization_id;
2736: wct.cartonization_id,
2737: wct.CONTAINER_ITEM_ID,
2738: wct.inventory_item_id -- Added for LSP, bug 9087971
2739: FROM
2740: wms_cartonization_temp wct,
2741: mtl_txn_request_lines mtrl
2742: WHERE
2743: wct.move_order_line_id = mtrl.line_id AND
2744: mtrl.header_id = p_header_id AND
2842: if (g_trace_on = 1) then log_event('ERROR generating the lpns '||l_return_status); END IF;
2843: RAISE fnd_api.g_exc_unexpected_error;
2844:
2845: ELSE
2846: UPDATE wms_cartonization_temp
2847: SET
2848: cartonization_id = v_lpn_id_out,
2849: container_item_id = cont_id
2850: WHERE
2849: container_item_id = cont_id
2850: WHERE
2851: transaction_temp_id = orig_temp_id;
2852:
2853: if (g_trace_on = 1) then log_event('updated wms_cartonization_temp for temp id '||orig_temp_id); END IF;
2854:
2855:
2856: UPDATE WMS_PACKAGING_HIST
2857: SET parent_lpn_id = v_lpn_id_out
2949: from mtl_system_items msi where
2950: (msi.inventory_item_id, msi.organization_id) =
2951: (
2952: select mmtt.inventory_item_id, mmtt.organization_id from
2953: wms_cartonization_temp mmtt WHERE mmtt.transaction_temp_id = curr_temp_id
2954: );
2955:
2956: IF( (v_allocate_serial_flag IS NULL) OR
2957: (v_allocate_serial_flag = 'N') ) THEN
2972: SELECT primary_quantity ,secondary_transaction_quantity INTO
2973: api_table(api_table_index).primary_quantity,
2974: api_table(api_table_index).secondary_quantity
2975: FROM
2976: wms_cartonization_temp
2977: WHERE
2978: transaction_temp_id = curr_temp_id;
2979:
2980: l_temporary_temp_id := api_table(api_table_index).transaction_id;
3107:
3108:
3109: CURSOR c_tasks IS
3110: SELECT transaction_temp_id
3111: FROM wms_cartonization_temp wct
3112: WHERE
3113: parent_line_id is null or -- non bulked records
3114: parent_line_id = transaction_temp_id; -- parents only
3115:
3114: parent_line_id = transaction_temp_id; -- parents only
3115:
3116: Cursor c_tasks_con is
3117: Select transaction_temp_id
3118: From wms_cartonization_temp
3119: Where parent_line_id = transaction_temp_id;
3120:
3121:
3122: l_task_id NUMBER;
3268: l_api_return_status VARCHAR2(1);
3269: l_count1 NUMBER;
3270:
3271: CURSOR wct_rows IS
3272: SELECT wct.* FROM wms_cartonization_temp wct,
3273: mtl_txn_request_lines mtrl,
3274: mtl_secondary_inventories sub,
3275: mtl_parameters mtlp
3276: WHERE
3294:
3295: CURSOR OB_LBPRT IS
3296: SELECT DISTINCT mmtt.cartonization_id FROM
3297: --2513907 fix
3298: wms_cartonization_temp mmtt,
3299: --mtl_material_transactions_temp mmtt,
3300: mtl_txn_request_lines mtrl WHERE
3301: mmtt.move_order_line_id = mtrl.line_id
3302: AND mtrl.header_id = p_move_order_header_id
3305:
3306: CURSOR IB_LBPRT IS
3307: SELECT DISTINCT mmtt.cartonization_id FROM
3308: --2513907 fix
3309: wms_cartonization_temp mmtt
3310: --mtl_material_transactions_temp mmtt
3311: WHERE
3312: mmtt.transaction_header_id = p_transaction_header_id
3313: AND mmtt.cartonization_id IS NOT NULL
3315:
3316: /*
3317: CURSOR bpack_rows(p_hdr_id NUMBER) IS
3318: SELECT * FROM
3319: wms_cartonization_temp
3320: WHERE
3321: transaction_header_id = p_hdr_id
3322: AND cartonization_id IS NULL
3323: AND transfer_lpn_id IS NULL
3331:
3332:
3333: CURSOR bpack_rows(p_hdr_id NUMBER) IS
3334: SELECT * FROM
3335: wms_cartonization_temp
3336: WHERE
3337: transaction_header_id = p_hdr_id
3338: AND cartonization_id IS NULL
3339: AND transfer_lpn_id IS NULL
3356: content_lpn_id,
3357: container_item_id,
3358: cartonization_id
3359: FROM
3360: wms_cartonization_temp
3361: WHERE
3362: transaction_header_id = p_hdr_id
3363: order by cartonization_id;
3364:
3372: wct.content_lpn_id,
3373: wct.container_item_id,
3374: wct.cartonization_id
3375: FROM
3376: wms_cartonization_temp wct,
3377: mtl_txn_request_lines mtrl
3378: WHERE
3379: wct.move_order_line_id = mtrl.line_id AND
3380: mtrl.header_id = p_hdr_id
3405:
3406: if (g_trace_on = 1) then
3407: log_event('Table_name is '||table_name);
3408:
3409: log_event('Deleting rows in wms_cartonization_temp');
3410: END IF;
3411:
3412: --making sure that we are starting with an empty table
3413: DELETE wms_cartonization_temp;
3409: log_event('Deleting rows in wms_cartonization_temp');
3410: END IF;
3411:
3412: --making sure that we are starting with an empty table
3413: DELETE wms_cartonization_temp;
3414:
3415: pkg_attr_table.DELETE;
3416: lpn_attr_table.DELETE;
3417: lpns_generated_tb.DELETE;
3581: IF(g_trace_on = 1) THEN
3582: log_event('Rules setup exists');
3583: END IF;
3584:
3585: table_name := 'wms_cartonization_temp';
3586:
3587: IF wms_cartnzn_pub.g_org_cartonization_value IN (1,3) THEN
3588: log_event('Calling rulebased cartonization ');
3589: WMS_CARTNZN_PUB.rulebased_cartonization
3606: END IF; -- Release level check
3607: ------------------------ ER : 6682436 END ---------------------------------
3608:
3609: -- This setting ensures that all operations we do are on
3610: -- wms_cartonization_temp until its changed
3611: table_name := 'wms_cartonization_temp';
3612:
3613: IF (g_trace_on = 1) THEN log_event(' Inserting mmtt rows to wct'); END IF;
3614:
3607: ------------------------ ER : 6682436 END ---------------------------------
3608:
3609: -- This setting ensures that all operations we do are on
3610: -- wms_cartonization_temp until its changed
3611: table_name := 'wms_cartonization_temp';
3612:
3613: IF (g_trace_on = 1) THEN log_event(' Inserting mmtt rows to wct'); END IF;
3614:
3615: IF ( outbound = 'Y') THEN
3684:
3685: END IF;
3686:
3687:
3688: IF (g_trace_on = 1) THEN log_event(' inserting mmtt rows of this header into wms_cartonization_temp '|| p_move_order_header_id ); END IF;
3689:
3690: error_code := 'CARTONIZE 50';
3691:
3692: -- Bug 2457428 fix
3694: create_wct(p_move_order_header_id => p_move_order_header_id,
3695: p_transaction_header_id => NULL,
3696: p_input_for_bulk =>p_input_for_bulk);
3697:
3698: --INSERT INTO wms_cartonization_temp SELECT mmtt.* FROM
3699: --mtl_material_transactions_temp mmtt, mtl_txn_request_lines
3700: --mtrl WHERE mtrl.header_id = p_move_order_header_id AND
3701: --mmtt.move_order_line_id = mtrl.line_id;
3702:
3702:
3703: ELSE --outbound = 'N'
3704:
3705: error_code := 'CARTONIZE 60';
3706: if (g_trace_on = 1) then log_event(' inserting mmtt rows of this header into wms_cartonization_temp '|| p_transaction_header_id ); END IF;
3707:
3708: -- Bug 2457428 fix
3709:
3710: create_wct(p_move_order_header_id => NULL,
3710: create_wct(p_move_order_header_id => NULL,
3711: p_transaction_header_id => p_transaction_header_id
3712: );
3713:
3714: --INSERT INTO wms_cartonization_temp
3715: --SELECT mmtt.* FROM
3716: --mtl_material_transactions_temp mmtt
3717: --WHERE
3718: --mmtt.transaction_header_id = p_transaction_header_id;
4679: END IF;
4680: END ;
4681:
4682:
4683: DELETE wms_cartonization_temp
4684: WHERE
4685: transaction_header_id < 0;
4686:
4687: ELSE
5087:
5088:
5089: ELSE
5090:
5091: if (g_trace_on = 1) then log_event(' table_name = wms_cartonization_temp'); END IF;
5092:
5093: IF p_parent_line_id = -99999 THEN
5094:
5095: SELECT parent_line_id INTO par_line_id FROM
5092:
5093: IF p_parent_line_id = -99999 THEN
5094:
5095: SELECT parent_line_id INTO par_line_id FROM
5096: wms_cartonization_temp WHERE transaction_temp_id =
5097: p_transaction_temp_id;
5098: ELSE
5099: par_line_id := p_parent_line_id;
5100: END IF;
5102: if (g_trace_on = 1) then log_event(' upd qty flag is '||p_upd_qty_flag ); END IF;
5103:
5104: IF( p_upd_qty_flag = 'Y') THEN
5105:
5106: UPDATE wms_cartonization_temp SET
5107: primary_quantity = p_primary_quantity,
5108: transaction_quantity = p_transaction_quantity,
5109: secondary_transaction_quantity = p_secondary_quantity, --invconv kkillams
5110: cartonization_id = l_LPN,
5114: last_updated_by = fnd_global.user_id
5115: WHERE
5116: transaction_temp_id = p_transaction_temp_id;
5117: ELSE
5118: UPDATE wms_cartonization_temp SET
5119: cartonization_id = l_LPN,
5120: parent_line_id = par_line_id,
5121: container_item_id = p_container_item_id,
5122: last_update_date = Sysdate,
5147: BEGIN
5148:
5149: error_code := 'UPD_MMTT_QTY';
5150:
5151: IF table_name = 'wms_cartonization_temp' THEN
5152:
5153: if (g_trace_on = 1) then log_event(' table_name = wms_cartonization_temp'); END IF;
5154:
5155: UPDATE wms_cartonization_temp SET
5149: error_code := 'UPD_MMTT_QTY';
5150:
5151: IF table_name = 'wms_cartonization_temp' THEN
5152:
5153: if (g_trace_on = 1) then log_event(' table_name = wms_cartonization_temp'); END IF;
5154:
5155: UPDATE wms_cartonization_temp SET
5156: primary_quantity = primary_quantity - NVL(p_primary_quantity,0),
5157: transaction_quantity = transaction_quantity - NVL(p_transaction_quantity,0),
5151: IF table_name = 'wms_cartonization_temp' THEN
5152:
5153: if (g_trace_on = 1) then log_event(' table_name = wms_cartonization_temp'); END IF;
5154:
5155: UPDATE wms_cartonization_temp SET
5156: primary_quantity = primary_quantity - NVL(p_primary_quantity,0),
5157: transaction_quantity = transaction_quantity - NVL(p_transaction_quantity,0),
5158: last_update_date = Sysdate,
5159: last_updated_by = fnd_global.user_id
5211: log_event(' lpn id IS '||l_lpn);
5212: log_event(' table name is '||table_name);
5213: END IF;
5214:
5215: IF( table_name = 'wms_cartonization_temp' ) THEN
5216:
5217:
5218: SELECT * INTO v1 FROM wms_cartonization_temp WHERE
5219: transaction_temp_id = p_transaction_temp_id AND ROWNUM < 2;
5214:
5215: IF( table_name = 'wms_cartonization_temp' ) THEN
5216:
5217:
5218: SELECT * INTO v1 FROM wms_cartonization_temp WHERE
5219: transaction_temp_id = p_transaction_temp_id AND ROWNUM < 2;
5220:
5221: ELSE
5222: --Bug 3296177 code should never come here
5341: v_return_status VARCHAR2(300);
5342: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5343: BEGIN
5344: /*mlid := 1;
5345: table_name := 'WMS_CARTONIZATION_TEMP';
5346: mlid := do_cartonization(mohdrid =>5388, trxhdrid =>3243813, outbound => 'N', sublvlctrl =>'1');
5347:
5348: dbms_output.put_line(mlid);*/
5349:
5664: x_msg_count OUT NOCOPY NUMBER,
5665: x_msg_data OUT NOCOPY VARCHAR2)
5666: is
5667: CURSOR wct_rows IS
5668: SELECT wct.* FROM wms_cartonization_temp wct,
5669: mtl_txn_request_lines mtrl WHERE wct.move_order_line_id =
5670: mtrl.line_id AND mtrl.header_id = p_m_o_h_id;
5671:
5672: -- patchset J bulk picking ---
5673: -- following cursor will be used for calling from concurrent program --
5674: -- also after patchset J, this cursor will replace wct_row since no need to
5675: -- query by move order header and also to query parent lines ---------------------
5676: CURSOR wct_rows_bulk IS
5677: SELECT wct.* FROM wms_cartonization_temp wct;
5678:
5679: -- end of patchset J bulk picking --------------
5680:
5681: CURSOR bpack_rows IS
5678:
5679: -- end of patchset J bulk picking --------------
5680:
5681: CURSOR bpack_rows IS
5682: SELECT * FROM wms_cartonization_temp
5683: WHERE
5684: transaction_header_id = p_m_o_h_id;
5685:
5686: CURSOR lot_ser_cursor(p_transaction_temp_id NUMBER) IS
5999:
6000: l_uom_rate number;
6001:
6002: CURSOR wct_rows IS
6003: SELECT wct.* FROM wms_cartonization_temp wct,
6004: mtl_txn_request_lines mtrl,
6005: mtl_secondary_inventories sub,
6006: mtl_parameters mtlp
6007: WHERE
6021: wct.move_order_line_id,
6022: Abs(wct.transaction_temp_id);
6023:
6024: CURSOR wct_sub_rows IS
6025: SELECT wct.* FROM wms_cartonization_temp wct,
6026: mtl_txn_request_lines mtrl,
6027: mtl_secondary_inventories sub,
6028: mtl_parameters mtlp
6029: WHERE
6046:
6047: CURSOR OB_LBPRT IS
6048: SELECT DISTINCT mmtt.cartonization_id FROM
6049: --2513907 fix
6050: wms_cartonization_temp mmtt,
6051: --mtl_material_transactions_temp mmtt,
6052: mtl_txn_request_lines mtrl WHERE
6053: mmtt.move_order_line_id = mtrl.line_id
6054: AND mtrl.header_id = p_move_order_header_id
6056: ORDER BY mmtt.cartonization_id;
6057:
6058: CURSOR bpack_rows(p_hdr_id NUMBER) IS
6059: SELECT * FROM
6060: wms_cartonization_temp
6061: WHERE
6062: transaction_header_id = p_hdr_id
6063: AND cartonization_id IS NULL
6064: AND transfer_lpn_id IS NULL
6080: content_lpn_id,
6081: container_item_id,
6082: cartonization_id
6083: FROM
6084: wms_cartonization_temp
6085: WHERE
6086: transaction_header_id = p_hdr_id
6087: order by cartonization_id;
6088:
6096: wct.content_lpn_id,
6097: wct.container_item_id,
6098: wct.cartonization_id
6099: FROM
6100: wms_cartonization_temp wct,
6101: mtl_txn_request_lines mtrl
6102: WHERE
6103: wct.move_order_line_id = mtrl.line_id AND
6104: mtrl.header_id = p_hdr_id
6776: TYPE rules_table_type IS TABLE OF wms_selection_criteria_txn%rowtype;
6777: rules_table rules_table_type;
6778:
6779: CURSOR wct_rows IS
6780: SELECT wct.* FROM wms_cartonization_temp wct,
6781: mtl_txn_request_lines mtrl,
6782: mtl_secondary_inventories sub,
6783: mtl_parameters mtlp
6784: WHERE
6801:
6802: CURSOR OB_LBPRT IS
6803: SELECT DISTINCT mmtt.cartonization_id FROM
6804: --2513907 fix
6805: wms_cartonization_temp mmtt,
6806: --mtl_material_transactions_temp mmtt,
6807: mtl_txn_request_lines mtrl WHERE
6808: mmtt.move_order_line_id = mtrl.line_id
6809: AND mtrl.header_id = p_move_order_header_id
6821: content_lpn_id,
6822: container_item_id,
6823: cartonization_id
6824: FROM
6825: wms_cartonization_temp
6826: WHERE
6827: transaction_header_id = p_hdr_id
6828: order by cartonization_id;
6829:
6837: wct.content_lpn_id,
6838: wct.container_item_id,
6839: wct.cartonization_id
6840: FROM
6841: wms_cartonization_temp wct,
6842: mtl_txn_request_lines mtrl
6843: WHERE
6844: wct.move_order_line_id = mtrl.line_id AND
6845: mtrl.header_id = p_hdr_id
6852: BEGIN
6853:
6854: IF (g_trace_on = 1) THEN
6855: log_event('In RULEBASED_CARTONIZATION()');
6856: log_event('Populating wms_cartonization_temp from MMTT rows');
6857: END IF;
6858: g_cartonize_pick_slip := 'N';
6859:
6860: IF (g_trace_on = 1) THEN
6930:
6931: END IF; -- l_auto_pick_confirm_flag = 'N'
6932:
6933: IF (g_trace_on = 1) THEN
6934: log_event('Populating wms_cartonization_temp from MMTT rows');
6935: END IF;
6936:
6937: create_wct(
6938: p_move_order_header_id => p_move_order_header_id
6942:
6943: IF (g_trace_on = 1) THEN
6944: SELECT count(*)
6945: INTO l_count
6946: FROM wms_cartonization_temp;
6947: log_event('NUMBER OF ROWS INSERTED IN WCT : '|| l_count);
6948: END IF;
6949:
6950: IF (g_trace_on = 1) THEN
7017:
7018: -- Do not consider WCT rows used for multi-level packaging history
7019: SELECT count(1)
7020: INTO l_count
7021: FROM wms_cartonization_temp
7022: WHERE cartonization_id IS NULL
7023: AND transaction_header_id >= 0;
7024:
7025: IF l_count = 0 THEN
7093: END IF;
7094: RAISE fnd_api.g_exc_error;
7095: END;
7096:
7097: DELETE wms_cartonization_temp
7098: WHERE transaction_header_id < 0;
7099:
7100: log_event('Out of cartonization logic going to print labels');
7101:
7332: l_move_order_type NUMBER;
7333: l_pack_level NUMBER := 0;
7334:
7335: CURSOR wct_rows IS
7336: SELECT wct.* FROM wms_cartonization_temp wct,
7337: mtl_txn_request_lines mtrl,
7338: mtl_secondary_inventories sub,
7339: mtl_parameters mtlp
7340: WHERE
7358:
7359: CURSOR OB_LBPRT IS
7360: SELECT DISTINCT mmtt.cartonization_id FROM
7361: --2513907 fix
7362: wms_cartonization_temp mmtt,
7363: --mtl_material_transactions_temp mmtt,
7364: mtl_txn_request_lines mtrl WHERE
7365: mmtt.move_order_line_id = mtrl.line_id
7366: AND mtrl.header_id = p_move_order_header_id
7368: ORDER BY mmtt.cartonization_id;
7369:
7370: CURSOR bpack_rows(p_hdr_id NUMBER) IS
7371: SELECT * FROM
7372: wms_cartonization_temp
7373: WHERE
7374: transaction_header_id = p_hdr_id
7375: AND cartonization_id IS NULL
7376: AND transfer_lpn_id IS NULL
7393: content_lpn_id,
7394: container_item_id,
7395: cartonization_id
7396: FROM
7397: wms_cartonization_temp
7398: WHERE
7399: transaction_header_id = p_hdr_id
7400: order by cartonization_id;
7401:
7409: wct.content_lpn_id,
7410: wct.container_item_id,
7411: wct.cartonization_id
7412: FROM
7413: wms_cartonization_temp wct,
7414: mtl_txn_request_lines mtrl
7415: WHERE
7416: wct.move_order_line_id = mtrl.line_id AND
7417: mtrl.header_id = p_hdr_id
7436: IF p_subinventory_name IS NOT NULL THEN
7437:
7438: IF (g_trace_on = 1) THEN log_event(' Updating wct with dummy cartonization id for sub other than : ' || p_subinventory_name); END IF;
7439:
7440: UPDATE wms_cartonization_temp
7441: SET cartonization_id = -99999
7442: , container_item_id = -99999
7443: WHERE subinventory_code <> p_subinventory_name
7444: AND organization_id = p_org_id
7671:
7672: IF p_subinventory_name IS NOT NULL THEN
7673: IF (g_trace_on = 1) THEN log_event(' Updating wct with back to null where the dummy cartonization id was populated for sub restrictions : '); END IF;
7674:
7675: UPDATE wms_cartonization_temp
7676: SET cartonization_id = NULL
7677: , container_item_id = NULL
7678: WHERE subinventory_code <> p_subinventory_name
7679: AND organization_id = p_org_id
7761: IS
7762: CURSOR Pick_slip_cur
7763: IS
7764: SELECT DISTINCT wct.pick_slip_number
7765: FROM wms_cartonization_temp wct,
7766: mtl_txn_request_lines mtrl,
7767: mtl_parameters mtlp,
7768: mtl_secondary_inventories sub
7769: WHERE mtrl.header_id = p_move_order_header_id
7783:
7784: CURSOR Pick_slip_sub_cur
7785: IS
7786: SELECT DISTINCT wct.pick_slip_number
7787: FROM wms_cartonization_temp wct,
7788: mtl_txn_request_lines mtrl,
7789: mtl_parameters mtlp,
7790: mtl_secondary_inventories sub
7791: WHERE mtrl.header_id = p_move_order_header_id
7807: CURSOR Delivery_cur (p_pick_slip_number NUMBER)
7808: IS
7809: SELECT DISTINCT wda.delivery_id
7810: FROM mtl_txn_request_lines mtrl,
7811: wms_cartonization_temp wct,
7812: wsh_delivery_details wdd ,
7813: wsh_delivery_assignments wda
7814: WHERE mtrl.organization_id = p_org_id
7815: AND mtrl.header_id = p_move_order_header_id
7826: CURSOR Delivery_sub_cur (p_pick_slip_number NUMBER)
7827: IS
7828: SELECT DISTINCT wda.delivery_id
7829: FROM mtl_txn_request_lines mtrl,
7830: wms_cartonization_temp wct,
7831: wsh_delivery_details wdd ,
7832: wsh_delivery_assignments wda
7833: WHERE mtrl.organization_id = p_org_id
7834: AND mtrl.header_id = p_move_order_header_id
7917: END IF;
7918:
7919: l_carton_lpn_id := get_next_package_id;
7920:
7921: UPDATE wms_cartonization_temp
7922: SET cartonization_id = l_carton_lpn_id
7923: WHERE transaction_temp_id IN
7924: (SELECT wct.transaction_temp_id
7925: FROM wsh_delivery_details wdd ,
7922: SET cartonization_id = l_carton_lpn_id
7923: WHERE transaction_temp_id IN
7924: (SELECT wct.transaction_temp_id
7925: FROM wsh_delivery_details wdd ,
7926: wms_cartonization_temp wct ,
7927: wsh_delivery_assignments wda,
7928: mtl_txn_request_lines mtrl
7929: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
7930: AND wdd.source_line_id = wct.demand_source_line
7956: IF p_subinventory_name IS NULL THEN
7957: SELECT wdd.delivery_detail_id BULK COLLECT
7958: INTO l_del_det_id_tab
7959: FROM mtl_txn_request_lines mtrl,
7960: wms_cartonization_temp wct,
7961: wsh_delivery_details wdd ,
7962: wsh_delivery_assignments wda
7963: WHERE mtrl.organization_id = p_org_id
7964: AND mtrl.header_id = p_move_order_header_id
7973: ELSE
7974: SELECT wdd.delivery_detail_id BULK COLLECT
7975: INTO l_del_det_id_tab
7976: FROM mtl_txn_request_lines mtrl,
7977: wms_cartonization_temp wct,
7978: wsh_delivery_details wdd ,
7979: wsh_delivery_assignments wda
7980: WHERE mtrl.organization_id = p_org_id
7981: AND mtrl.header_id = p_move_order_header_id
8029: IF (g_trace_on = 1) THEN
8030: log_event('Got carton_lpn_id :'||l_carton_lpn_id );
8031: END IF;
8032:
8033: UPDATE wms_cartonization_temp
8034: SET cartonization_id = l_carton_lpn_id
8035: WHERE transaction_temp_id IN
8036: (SELECT wct.transaction_temp_id
8037: FROM wsh_delivery_details wdd ,
8034: SET cartonization_id = l_carton_lpn_id
8035: WHERE transaction_temp_id IN
8036: (SELECT wct.transaction_temp_id
8037: FROM wsh_delivery_details wdd ,
8038: wms_cartonization_temp wct,
8039: mtl_txn_request_lines mtrl
8040: WHERE wdd.delivery_detail_id = l_del_det_id_tab (i)
8041: AND wct.pick_slip_number = l_pick_slip
8042: AND wdd.source_line_id = wct.demand_source_line
8109: CURSOR Delivery_cur (p_pick_slip_number NUMBER)
8110: IS
8111: SELECT DISTINCT wda.delivery_id
8112: FROM mtl_txn_request_lines mtrl,
8113: wms_cartonization_temp wct,
8114: wsh_delivery_details wdd ,
8115: wsh_delivery_assignments wda
8116: WHERE mtrl.organization_id = p_org_id
8117: AND mtrl.header_id = p_move_order_header_id
8128: CURSOR Delivery_sub_cur (p_pick_slip_number NUMBER)
8129: IS
8130: SELECT DISTINCT wda.delivery_id
8131: FROM mtl_txn_request_lines mtrl,
8132: wms_cartonization_temp wct,
8133: wsh_delivery_details wdd ,
8134: wsh_delivery_assignments wda
8135: WHERE mtrl.organization_id = p_org_id
8136: AND mtrl.header_id = p_move_order_header_id
8188:
8189: IF p_subinventory_name IS NOT NULL THEN
8190:
8191: SELECT wct.* BULK COLLECT INTO l_mmtt_table
8192: FROM wms_cartonization_temp wct,
8193: mtl_txn_request_lines mtrl,
8194: mtl_secondary_inventories sub,
8195: mtl_parameters mtlp
8196: WHERE wct.move_order_line_id =mtrl.line_id
8212: Abs(wct.transaction_temp_id);
8213: ELSE
8214:
8215: SELECT wct.* BULK COLLECT INTO l_mmtt_table
8216: FROM wms_cartonization_temp wct,
8217: mtl_txn_request_lines mtrl,
8218: mtl_secondary_inventories sub,
8219: mtl_parameters mtlp
8220: WHERE wct.move_order_line_id =mtrl.line_id
8282: END IF;
8283:
8284: l_cartons_not_stamped := 'N';
8285:
8286: UPDATE wms_cartonization_temp
8287: SET cartonization_id = l_out_mmtt_table(i).cartonization_id
8288: , container_item_id = l_out_mmtt_table(i).container_item_id
8289: WHERE transaction_temp_id = l_out_mmtt_table(i).transaction_temp_id;
8290:
8324:
8325: SELECT DISTINCT wdd.delivery_detail_id BULK COLLECT
8326: INTO l_del_det_id_tab
8327: FROM wsh_delivery_details wdd,
8328: wms_cartonization_temp wct,
8329: mtl_txn_request_lines mtrl
8330: WHERE wct.move_order_line_id = mtrl.line_id
8331: AND mtrl.header_id = p_move_order_header_id
8332: AND wct.organization_id = mtrl.organization_id
8493: wct.content_lpn_id,
8494: wct.container_item_id,
8495: wct.cartonization_id
8496: FROM
8497: wms_cartonization_temp wct,
8498: mtl_txn_request_lines mtrl
8499: WHERE
8500: wct.move_order_line_id = mtrl.line_id AND
8501: mtrl.header_id = p_move_order_header_id