[Home] [Help]
161: SUBTYPE WDR_ROW IS WMS_DEVICE_REQUESTS%ROWTYPE;
162:
163: SUBTYPE MMTT_ROW_TYPE IS MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE;
164:
165: SUBTYPE WCT_ROW_TYPE IS WMS_CARTONIZATION_TEMP%ROWTYPE;
166:
167: TYPE lpn_alloc_flag_rec IS RECORD
168: ( transaction_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE ,
169: lpn_alloc_flag VARCHAR2(1)
869: ---- end of patchset J bulk picking -------------------
870: if (g_trace_on = 1) then log_event('Insert transaction temp id:'||mmtt_rec.transaction_temp_id); end if;
871: if (g_trace_on = 1) then log_event('transaction quantity:'||mmtt_rec.transaction_quantity); end if;
872: if (g_trace_on = 1) then log_event('secondary transaction quantity:'||mmtt_rec.secondary_transaction_quantity); end if; --invconv kkillams
873: INSERT INTO wms_cartonization_temp
874: (transaction_header_id,
875: transaction_temp_id,
876: source_code,
877: source_line_id,
2060: l_clpn_id,
2061: l_cartonization_id,
2062: l_citem_id
2063: FROM
2064: wms_cartonization_temp
2065: WHERE
2066: transaction_temp_id = p_transaction_temp_id;
2067:
2068: IF( p_transaction_temp_id < 0 ) THEN
2699:
2700: SELECT cartonization_id, container_item_id
2701: INTO
2702: x_par_cart_id, x_par_cont_id
2703: FROM wms_cartonization_temp
2704: WHERE
2705: content_lpn_id = p_child AND
2706: transaction_header_id < 0;
2707:
2746: transaction_temp_id,
2747: cartonization_id,
2748: CONTAINER_ITEM_ID
2749: FROM
2750: wms_cartonization_temp
2751: WHERE
2752: transaction_header_id = p_header_id
2753: --Bug 2864774 fix
2754: ORDER BY cartonization_id;
2757: wct.transaction_temp_id,
2758: wct.cartonization_id,
2759: wct.CONTAINER_ITEM_ID
2760: FROM
2761: wms_cartonization_temp wct,
2762: mtl_txn_request_lines mtrl
2763: WHERE
2764: wct.move_order_line_id = mtrl.line_id AND
2765: mtrl.header_id = p_header_id AND
2862: if (g_trace_on = 1) then log_event('ERROR generating the lpns '||l_return_status); END IF;
2863: RAISE fnd_api.g_exc_unexpected_error;
2864:
2865: ELSE
2866: UPDATE wms_cartonization_temp
2867: SET
2868: cartonization_id = v_lpn_id_out,
2869: container_item_id = cont_id
2870: WHERE
2869: container_item_id = cont_id
2870: WHERE
2871: transaction_temp_id = orig_temp_id;
2872:
2873: if (g_trace_on = 1) then log_event('updated wms_cartonization_temp for temp id '||orig_temp_id); END IF;
2874:
2875:
2876: UPDATE WMS_PACKAGING_HIST
2877: SET parent_lpn_id = v_lpn_id_out
2969: from mtl_system_items msi where
2970: (msi.inventory_item_id, msi.organization_id) =
2971: (
2972: select mmtt.inventory_item_id, mmtt.organization_id from
2973: wms_cartonization_temp mmtt WHERE mmtt.transaction_temp_id = curr_temp_id
2974: );
2975:
2976: IF( (v_allocate_serial_flag IS NULL) OR
2977: (v_allocate_serial_flag = 'N') ) THEN
2992: SELECT primary_quantity ,secondary_transaction_quantity INTO
2993: api_table(api_table_index).primary_quantity,
2994: api_table(api_table_index).secondary_quantity
2995: FROM
2996: wms_cartonization_temp
2997: WHERE
2998: transaction_temp_id = curr_temp_id;
2999:
3000: l_temporary_temp_id := api_table(api_table_index).transaction_id;
3127:
3128:
3129: CURSOR c_tasks IS
3130: SELECT transaction_temp_id
3131: FROM wms_cartonization_temp wct
3132: WHERE
3133: parent_line_id is null or -- non bulked records
3134: parent_line_id = transaction_temp_id; -- parents only
3135:
3134: parent_line_id = transaction_temp_id; -- parents only
3135:
3136: Cursor c_tasks_con is
3137: Select transaction_temp_id
3138: From wms_cartonization_temp
3139: Where parent_line_id = transaction_temp_id;
3140:
3141:
3142: l_task_id NUMBER;
3288: l_api_return_status VARCHAR2(1);
3289: l_count1 NUMBER;
3290:
3291: CURSOR wct_rows IS
3292: SELECT wct.* FROM wms_cartonization_temp wct,
3293: mtl_txn_request_lines mtrl,
3294: mtl_secondary_inventories sub,
3295: mtl_parameters mtlp
3296: WHERE
3312:
3313: CURSOR OB_LBPRT IS
3314: SELECT DISTINCT mmtt.cartonization_id FROM
3315: --2513907 fix
3316: wms_cartonization_temp mmtt,
3317: --mtl_material_transactions_temp mmtt,
3318: mtl_txn_request_lines mtrl WHERE
3319: mmtt.move_order_line_id = mtrl.line_id
3320: AND mtrl.header_id = p_move_order_header_id
3323:
3324: CURSOR IB_LBPRT IS
3325: SELECT DISTINCT mmtt.cartonization_id FROM
3326: --2513907 fix
3327: wms_cartonization_temp mmtt
3328: --mtl_material_transactions_temp mmtt
3329: WHERE
3330: mmtt.transaction_header_id = p_transaction_header_id
3331: AND mmtt.cartonization_id IS NOT NULL
3333:
3334: /*
3335: CURSOR bpack_rows(p_hdr_id NUMBER) IS
3336: SELECT * FROM
3337: wms_cartonization_temp
3338: WHERE
3339: transaction_header_id = p_hdr_id
3340: AND cartonization_id IS NULL
3341: AND transfer_lpn_id IS NULL
3349:
3350:
3351: CURSOR bpack_rows(p_hdr_id NUMBER) IS
3352: SELECT * FROM
3353: wms_cartonization_temp
3354: WHERE
3355: transaction_header_id = p_hdr_id
3356: AND cartonization_id IS NULL
3357: AND transfer_lpn_id IS NULL
3374: content_lpn_id,
3375: container_item_id,
3376: cartonization_id
3377: FROM
3378: wms_cartonization_temp
3379: WHERE
3380: transaction_header_id = p_hdr_id
3381: order by cartonization_id;
3382:
3390: wct.content_lpn_id,
3391: wct.container_item_id,
3392: wct.cartonization_id
3393: FROM
3394: wms_cartonization_temp wct,
3395: mtl_txn_request_lines mtrl
3396: WHERE
3397: wct.move_order_line_id = mtrl.line_id AND
3398: mtrl.header_id = p_hdr_id
3423:
3424: if (g_trace_on = 1) then
3425: log_event('Table_name is '||table_name);
3426:
3427: log_event('Deleting rows in wms_cartonization_temp');
3428: END IF;
3429:
3430: --making sure that we are starting with an empty table
3431: DELETE wms_cartonization_temp;
3427: log_event('Deleting rows in wms_cartonization_temp');
3428: END IF;
3429:
3430: --making sure that we are starting with an empty table
3431: DELETE wms_cartonization_temp;
3432:
3433: pkg_attr_table.DELETE;
3434: lpn_attr_table.DELETE;
3435: lpns_generated_tb.DELETE;
3599: IF(g_trace_on = 1) THEN
3600: log_event('Rules setup exists');
3601: END IF;
3602:
3603: table_name := 'wms_cartonization_temp';
3604:
3605: IF wms_cartnzn_pub.g_org_cartonization_value IN (1,3) THEN
3606: log_event('Calling rulebased cartonization ');
3607: WMS_CARTNZN_PUB.rulebased_cartonization
3624: END IF; -- Release level check
3625: ------------------------ ER : 6682436 END ---------------------------------
3626:
3627: -- This setting ensures that all operations we do are on
3628: -- wms_cartonization_temp until its changed
3629: table_name := 'wms_cartonization_temp';
3630:
3631: IF (g_trace_on = 1) THEN log_event(' Inserting mmtt rows to wct'); END IF;
3632:
3625: ------------------------ ER : 6682436 END ---------------------------------
3626:
3627: -- This setting ensures that all operations we do are on
3628: -- wms_cartonization_temp until its changed
3629: table_name := 'wms_cartonization_temp';
3630:
3631: IF (g_trace_on = 1) THEN log_event(' Inserting mmtt rows to wct'); END IF;
3632:
3633: IF ( outbound = 'Y') THEN
3702:
3703: END IF;
3704:
3705:
3706: 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;
3707:
3708: error_code := 'CARTONIZE 50';
3709:
3710: -- Bug 2457428 fix
3712: create_wct(p_move_order_header_id => p_move_order_header_id,
3713: p_transaction_header_id => NULL,
3714: p_input_for_bulk =>p_input_for_bulk);
3715:
3716: --INSERT INTO wms_cartonization_temp SELECT mmtt.* FROM
3717: --mtl_material_transactions_temp mmtt, mtl_txn_request_lines
3718: --mtrl WHERE mtrl.header_id = p_move_order_header_id AND
3719: --mmtt.move_order_line_id = mtrl.line_id;
3720:
3720:
3721: ELSE --outbound = 'N'
3722:
3723: error_code := 'CARTONIZE 60';
3724: if (g_trace_on = 1) then log_event(' inserting mmtt rows of this header into wms_cartonization_temp '|| p_transaction_header_id ); END IF;
3725:
3726: -- Bug 2457428 fix
3727:
3728: create_wct(p_move_order_header_id => NULL,
3728: create_wct(p_move_order_header_id => NULL,
3729: p_transaction_header_id => p_transaction_header_id
3730: );
3731:
3732: --INSERT INTO wms_cartonization_temp
3733: --SELECT mmtt.* FROM
3734: --mtl_material_transactions_temp mmtt
3735: --WHERE
3736: --mmtt.transaction_header_id = p_transaction_header_id;
4686: END IF;
4687: END ;
4688:
4689:
4690: DELETE wms_cartonization_temp
4691: WHERE
4692: transaction_header_id < 0;
4693:
4694: ELSE
5094:
5095:
5096: ELSE
5097:
5098: if (g_trace_on = 1) then log_event(' table_name = wms_cartonization_temp'); END IF;
5099:
5100: IF p_parent_line_id = -99999 THEN
5101:
5102: SELECT parent_line_id INTO par_line_id FROM
5099:
5100: IF p_parent_line_id = -99999 THEN
5101:
5102: SELECT parent_line_id INTO par_line_id FROM
5103: wms_cartonization_temp WHERE transaction_temp_id =
5104: p_transaction_temp_id;
5105: ELSE
5106: par_line_id := p_parent_line_id;
5107: END IF;
5109: if (g_trace_on = 1) then log_event(' upd qty flag is '||p_upd_qty_flag ); END IF;
5110:
5111: IF( p_upd_qty_flag = 'Y') THEN
5112:
5113: UPDATE wms_cartonization_temp SET
5114: primary_quantity = p_primary_quantity,
5115: transaction_quantity = p_transaction_quantity,
5116: secondary_transaction_quantity = p_secondary_quantity, --invconv kkillams
5117: cartonization_id = l_LPN,
5121: last_updated_by = fnd_global.user_id
5122: WHERE
5123: transaction_temp_id = p_transaction_temp_id;
5124: ELSE
5125: UPDATE wms_cartonization_temp SET
5126: cartonization_id = l_LPN,
5127: parent_line_id = par_line_id,
5128: container_item_id = p_container_item_id,
5129: last_update_date = Sysdate,
5185: log_event(' lpn id IS '||l_lpn);
5186: log_event(' table name is '||table_name);
5187: END IF;
5188:
5189: IF( table_name = 'wms_cartonization_temp' ) THEN
5190:
5191:
5192: SELECT * INTO v1 FROM wms_cartonization_temp WHERE
5193: transaction_temp_id = p_transaction_temp_id AND ROWNUM < 2;
5188:
5189: IF( table_name = 'wms_cartonization_temp' ) THEN
5190:
5191:
5192: SELECT * INTO v1 FROM wms_cartonization_temp WHERE
5193: transaction_temp_id = p_transaction_temp_id AND ROWNUM < 2;
5194:
5195: ELSE
5196: --Bug 3296177 code should never come here
5315: v_return_status VARCHAR2(300);
5316: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5317: BEGIN
5318: /*mlid := 1;
5319: table_name := 'WMS_CARTONIZATION_TEMP';
5320: mlid := do_cartonization(mohdrid =>5388, trxhdrid =>3243813, outbound => 'N', sublvlctrl =>'1');
5321:
5322: dbms_output.put_line(mlid);*/
5323:
5628: x_msg_count OUT NOCOPY NUMBER,
5629: x_msg_data OUT NOCOPY VARCHAR2)
5630: is
5631: CURSOR wct_rows IS
5632: SELECT wct.* FROM wms_cartonization_temp wct,
5633: mtl_txn_request_lines mtrl WHERE wct.move_order_line_id =
5634: mtrl.line_id AND mtrl.header_id = p_m_o_h_id;
5635:
5636: -- patchset J bulk picking ---
5637: -- following cursor will be used for calling from concurrent program --
5638: -- also after patchset J, this cursor will replace wct_row since no need to
5639: -- query by move order header and also to query parent lines ---------------------
5640: CURSOR wct_rows_bulk IS
5641: SELECT wct.* FROM wms_cartonization_temp wct;
5642:
5643: -- end of patchset J bulk picking --------------
5644:
5645: CURSOR bpack_rows IS
5642:
5643: -- end of patchset J bulk picking --------------
5644:
5645: CURSOR bpack_rows IS
5646: SELECT * FROM wms_cartonization_temp
5647: WHERE
5648: transaction_header_id = p_m_o_h_id;
5649:
5650: CURSOR lot_ser_cursor(p_transaction_temp_id NUMBER) IS
5960:
5961: l_uom_rate number;
5962:
5963: CURSOR wct_rows IS
5964: SELECT wct.* FROM wms_cartonization_temp wct,
5965: mtl_txn_request_lines mtrl,
5966: mtl_secondary_inventories sub,
5967: mtl_parameters mtlp
5968: WHERE
5979: wct.move_order_line_id,
5980: Abs(wct.transaction_temp_id);
5981:
5982: CURSOR wct_sub_rows IS
5983: SELECT wct.* FROM wms_cartonization_temp wct,
5984: mtl_txn_request_lines mtrl,
5985: mtl_secondary_inventories sub,
5986: mtl_parameters mtlp
5987: WHERE
6001:
6002: CURSOR OB_LBPRT IS
6003: SELECT DISTINCT mmtt.cartonization_id FROM
6004: --2513907 fix
6005: wms_cartonization_temp mmtt,
6006: --mtl_material_transactions_temp mmtt,
6007: mtl_txn_request_lines mtrl WHERE
6008: mmtt.move_order_line_id = mtrl.line_id
6009: AND mtrl.header_id = p_move_order_header_id
6011: ORDER BY mmtt.cartonization_id;
6012:
6013: CURSOR bpack_rows(p_hdr_id NUMBER) IS
6014: SELECT * FROM
6015: wms_cartonization_temp
6016: WHERE
6017: transaction_header_id = p_hdr_id
6018: AND cartonization_id IS NULL
6019: AND transfer_lpn_id IS NULL
6035: content_lpn_id,
6036: container_item_id,
6037: cartonization_id
6038: FROM
6039: wms_cartonization_temp
6040: WHERE
6041: transaction_header_id = p_hdr_id
6042: order by cartonization_id;
6043:
6051: wct.content_lpn_id,
6052: wct.container_item_id,
6053: wct.cartonization_id
6054: FROM
6055: wms_cartonization_temp wct,
6056: mtl_txn_request_lines mtrl
6057: WHERE
6058: wct.move_order_line_id = mtrl.line_id AND
6059: mtrl.header_id = p_hdr_id
6717: TYPE rules_table_type IS TABLE OF wms_selection_criteria_txn%rowtype;
6718: rules_table rules_table_type;
6719:
6720: CURSOR wct_rows IS
6721: SELECT wct.* FROM wms_cartonization_temp wct,
6722: mtl_txn_request_lines mtrl,
6723: mtl_secondary_inventories sub,
6724: mtl_parameters mtlp
6725: WHERE
6740:
6741: CURSOR OB_LBPRT IS
6742: SELECT DISTINCT mmtt.cartonization_id FROM
6743: --2513907 fix
6744: wms_cartonization_temp mmtt,
6745: --mtl_material_transactions_temp mmtt,
6746: mtl_txn_request_lines mtrl WHERE
6747: mmtt.move_order_line_id = mtrl.line_id
6748: AND mtrl.header_id = p_move_order_header_id
6760: content_lpn_id,
6761: container_item_id,
6762: cartonization_id
6763: FROM
6764: wms_cartonization_temp
6765: WHERE
6766: transaction_header_id = p_hdr_id
6767: order by cartonization_id;
6768:
6776: wct.content_lpn_id,
6777: wct.container_item_id,
6778: wct.cartonization_id
6779: FROM
6780: wms_cartonization_temp wct,
6781: mtl_txn_request_lines mtrl
6782: WHERE
6783: wct.move_order_line_id = mtrl.line_id AND
6784: mtrl.header_id = p_hdr_id
6791: BEGIN
6792:
6793: IF (g_trace_on = 1) THEN
6794: log_event('In RULEBASED_CARTONIZATION()');
6795: log_event('Populating wms_cartonization_temp from MMTT rows');
6796: END IF;
6797: g_cartonize_pick_slip := 'N';
6798:
6799: IF (g_trace_on = 1) THEN
6867:
6868: END IF; -- l_auto_pick_confirm_flag = 'N'
6869:
6870: IF (g_trace_on = 1) THEN
6871: log_event('Populating wms_cartonization_temp from MMTT rows');
6872: END IF;
6873:
6874: create_wct(
6875: p_move_order_header_id => p_move_order_header_id
6879:
6880: IF (g_trace_on = 1) THEN
6881: SELECT count(*)
6882: INTO l_count
6883: FROM wms_cartonization_temp;
6884: log_event('NUMBER OF ROWS INSERTED IN WCT : '|| l_count);
6885: END IF;
6886:
6887: IF (g_trace_on = 1) THEN
6954:
6955: -- Do not consider WCT rows used for multi-level packaging history
6956: SELECT count(1)
6957: INTO l_count
6958: FROM wms_cartonization_temp
6959: WHERE cartonization_id IS NULL
6960: AND transaction_header_id >= 0;
6961:
6962: IF l_count = 0 THEN
7030: END IF;
7031: RAISE fnd_api.g_exc_error;
7032: END;
7033:
7034: DELETE wms_cartonization_temp
7035: WHERE transaction_header_id < 0;
7036:
7037: log_event('Out of cartonization logic going to print labels');
7038:
7252: l_move_order_type NUMBER;
7253: l_pack_level NUMBER := 0;
7254:
7255: CURSOR wct_rows IS
7256: SELECT wct.* FROM wms_cartonization_temp wct,
7257: mtl_txn_request_lines mtrl,
7258: mtl_secondary_inventories sub,
7259: mtl_parameters mtlp
7260: WHERE
7275:
7276: CURSOR OB_LBPRT IS
7277: SELECT DISTINCT mmtt.cartonization_id FROM
7278: --2513907 fix
7279: wms_cartonization_temp mmtt,
7280: --mtl_material_transactions_temp mmtt,
7281: mtl_txn_request_lines mtrl WHERE
7282: mmtt.move_order_line_id = mtrl.line_id
7283: AND mtrl.header_id = p_move_order_header_id
7285: ORDER BY mmtt.cartonization_id;
7286:
7287: CURSOR bpack_rows(p_hdr_id NUMBER) IS
7288: SELECT * FROM
7289: wms_cartonization_temp
7290: WHERE
7291: transaction_header_id = p_hdr_id
7292: AND cartonization_id IS NULL
7293: AND transfer_lpn_id IS NULL
7310: content_lpn_id,
7311: container_item_id,
7312: cartonization_id
7313: FROM
7314: wms_cartonization_temp
7315: WHERE
7316: transaction_header_id = p_hdr_id
7317: order by cartonization_id;
7318:
7326: wct.content_lpn_id,
7327: wct.container_item_id,
7328: wct.cartonization_id
7329: FROM
7330: wms_cartonization_temp wct,
7331: mtl_txn_request_lines mtrl
7332: WHERE
7333: wct.move_order_line_id = mtrl.line_id AND
7334: mtrl.header_id = p_hdr_id
7353: IF p_subinventory_name IS NOT NULL THEN
7354:
7355: IF (g_trace_on = 1) THEN log_event(' Updating wct with dummy cartonization id for sub other than : ' || p_subinventory_name); END IF;
7356:
7357: UPDATE wms_cartonization_temp
7358: SET cartonization_id = -99999
7359: , container_item_id = -99999
7360: WHERE subinventory_code <> p_subinventory_name
7361: AND organization_id = p_org_id
7588:
7589: IF p_subinventory_name IS NOT NULL THEN
7590: 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;
7591:
7592: UPDATE wms_cartonization_temp
7593: SET cartonization_id = NULL
7594: , container_item_id = NULL
7595: WHERE subinventory_code <> p_subinventory_name
7596: AND organization_id = p_org_id
7678: IS
7679: CURSOR Pick_slip_cur
7680: IS
7681: SELECT DISTINCT wct.pick_slip_number
7682: FROM wms_cartonization_temp wct,
7683: mtl_txn_request_lines mtrl,
7684: mtl_parameters mtlp,
7685: mtl_secondary_inventories sub
7686: WHERE mtrl.header_id = p_move_order_header_id
7697:
7698: CURSOR Pick_slip_sub_cur
7699: IS
7700: SELECT DISTINCT wct.pick_slip_number
7701: FROM wms_cartonization_temp wct,
7702: mtl_txn_request_lines mtrl,
7703: mtl_parameters mtlp,
7704: mtl_secondary_inventories sub
7705: WHERE mtrl.header_id = p_move_order_header_id
7718: CURSOR Delivery_cur (p_pick_slip_number NUMBER)
7719: IS
7720: SELECT DISTINCT wda.delivery_id
7721: FROM mtl_txn_request_lines mtrl,
7722: wms_cartonization_temp wct,
7723: wsh_delivery_details wdd ,
7724: wsh_delivery_assignments wda
7725: WHERE mtrl.organization_id = p_org_id
7726: AND mtrl.header_id = p_move_order_header_id
7737: CURSOR Delivery_sub_cur (p_pick_slip_number NUMBER)
7738: IS
7739: SELECT DISTINCT wda.delivery_id
7740: FROM mtl_txn_request_lines mtrl,
7741: wms_cartonization_temp wct,
7742: wsh_delivery_details wdd ,
7743: wsh_delivery_assignments wda
7744: WHERE mtrl.organization_id = p_org_id
7745: AND mtrl.header_id = p_move_order_header_id
7828: END IF;
7829:
7830: l_carton_lpn_id := get_next_package_id;
7831:
7832: UPDATE wms_cartonization_temp
7833: SET cartonization_id = l_carton_lpn_id
7834: WHERE transaction_temp_id IN
7835: (SELECT wct.transaction_temp_id
7836: FROM wsh_delivery_details wdd ,
7833: SET cartonization_id = l_carton_lpn_id
7834: WHERE transaction_temp_id IN
7835: (SELECT wct.transaction_temp_id
7836: FROM wsh_delivery_details wdd ,
7837: wms_cartonization_temp wct ,
7838: wsh_delivery_assignments wda,
7839: mtl_txn_request_lines mtrl
7840: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
7841: AND wdd.source_line_id = wct.demand_source_line
7867: IF p_subinventory_name IS NULL THEN
7868: SELECT wdd.delivery_detail_id BULK COLLECT
7869: INTO l_del_det_id_tab
7870: FROM mtl_txn_request_lines mtrl,
7871: wms_cartonization_temp wct,
7872: wsh_delivery_details wdd ,
7873: wsh_delivery_assignments wda
7874: WHERE mtrl.organization_id = p_org_id
7875: AND mtrl.header_id = p_move_order_header_id
7884: ELSE
7885: SELECT wdd.delivery_detail_id BULK COLLECT
7886: INTO l_del_det_id_tab
7887: FROM mtl_txn_request_lines mtrl,
7888: wms_cartonization_temp wct,
7889: wsh_delivery_details wdd ,
7890: wsh_delivery_assignments wda
7891: WHERE mtrl.organization_id = p_org_id
7892: AND mtrl.header_id = p_move_order_header_id
7940: IF (g_trace_on = 1) THEN
7941: log_event('Got carton_lpn_id :'||l_carton_lpn_id );
7942: END IF;
7943:
7944: UPDATE wms_cartonization_temp
7945: SET cartonization_id = l_carton_lpn_id
7946: WHERE transaction_temp_id IN
7947: (SELECT wct.transaction_temp_id
7948: FROM wsh_delivery_details wdd ,
7945: SET cartonization_id = l_carton_lpn_id
7946: WHERE transaction_temp_id IN
7947: (SELECT wct.transaction_temp_id
7948: FROM wsh_delivery_details wdd ,
7949: wms_cartonization_temp wct,
7950: mtl_txn_request_lines mtrl
7951: WHERE wdd.delivery_detail_id = l_del_det_id_tab (i)
7952: AND wct.pick_slip_number = l_pick_slip
7953: AND wdd.source_line_id = wct.demand_source_line
8020: CURSOR Delivery_cur (p_pick_slip_number NUMBER)
8021: IS
8022: SELECT DISTINCT wda.delivery_id
8023: FROM mtl_txn_request_lines mtrl,
8024: wms_cartonization_temp wct,
8025: wsh_delivery_details wdd ,
8026: wsh_delivery_assignments wda
8027: WHERE mtrl.organization_id = p_org_id
8028: AND mtrl.header_id = p_move_order_header_id
8039: CURSOR Delivery_sub_cur (p_pick_slip_number NUMBER)
8040: IS
8041: SELECT DISTINCT wda.delivery_id
8042: FROM mtl_txn_request_lines mtrl,
8043: wms_cartonization_temp wct,
8044: wsh_delivery_details wdd ,
8045: wsh_delivery_assignments wda
8046: WHERE mtrl.organization_id = p_org_id
8047: AND mtrl.header_id = p_move_order_header_id
8099:
8100: IF p_subinventory_name IS NOT NULL THEN
8101:
8102: SELECT wct.* BULK COLLECT INTO l_mmtt_table
8103: FROM wms_cartonization_temp wct,
8104: mtl_txn_request_lines mtrl,
8105: mtl_secondary_inventories sub,
8106: mtl_parameters mtlp
8107: WHERE wct.move_order_line_id =mtrl.line_id
8120: Abs(wct.transaction_temp_id);
8121: ELSE
8122:
8123: SELECT wct.* BULK COLLECT INTO l_mmtt_table
8124: FROM wms_cartonization_temp wct,
8125: mtl_txn_request_lines mtrl,
8126: mtl_secondary_inventories sub,
8127: mtl_parameters mtlp
8128: WHERE wct.move_order_line_id =mtrl.line_id
8187: END IF;
8188:
8189: l_cartons_not_stamped := 'N';
8190:
8191: UPDATE wms_cartonization_temp
8192: SET cartonization_id = l_out_mmtt_table(i).cartonization_id
8193: , container_item_id = l_out_mmtt_table(i).container_item_id
8194: WHERE transaction_temp_id = l_out_mmtt_table(i).transaction_temp_id;
8195:
8229:
8230: SELECT DISTINCT wdd.delivery_detail_id BULK COLLECT
8231: INTO l_del_det_id_tab
8232: FROM wsh_delivery_details wdd,
8233: wms_cartonization_temp wct,
8234: mtl_txn_request_lines mtrl
8235: WHERE wct.move_order_line_id = mtrl.line_id
8236: AND mtrl.header_id = p_move_order_header_id
8237: AND wct.organization_id = mtrl.organization_id
8398: wct.content_lpn_id,
8399: wct.container_item_id,
8400: wct.cartonization_id
8401: FROM
8402: wms_cartonization_temp wct,
8403: mtl_txn_request_lines mtrl
8404: WHERE
8405: wct.move_order_line_id = mtrl.line_id AND
8406: mtrl.header_id = p_move_order_header_id