[Home] [Help]
181:
182:
183: -- added for the bug 5464761
184: PROCEDURE check_and_explode_bom
185: ( p_order_line_rec IN oe_order_lines_all%rowtype,
186: l_u_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec,
187: px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
188: x_return_status OUT NOCOPY varchar2)
189:
208: l_bom_explode_flag BOOLEAN := FALSE;
209: l_split_flag varchar2(10);
210: l_auto_split_instances csi_datastructures_pub.instance_tbl;
211: l_ship_order_line_rec csi_order_ship_pub.order_line_rec;
212: l_order_line_rec oe_order_lines_all%rowtype;
213:
214:
215: begin
216:
394: END check_and_explode_bom;
395:
396:
397: PROCEDURE create_dflt_txn_dtls(
398: p_order_line_rec IN oe_order_lines_all%rowtype,
399: px_default_info_rec IN OUT nocopy default_info_rec,
400: x_txn_line_rec OUT nocopy csi_t_datastructures_grp.txn_line_rec,
401: x_tld_tbl OUT nocopy csi_t_datastructures_grp.txn_line_detail_tbl,
402: x_tiir_tbl OUT nocopy csi_t_datastructures_grp.txn_ii_rltns_tbl,
424: x_return_status := fnd_api.g_ret_sts_success;
425: api_log('create_dflt_txn_dtls');
426:
427: c_tl_rec.source_transaction_type_id := 51;
428: c_tl_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
429: c_tl_rec.source_transaction_id := p_order_line_rec.line_id;
430:
431: l_loop_count := 1;
432: l_quantity := p_order_line_rec.ordered_quantity;
537:
538: SELECT processing_status
539: INTO l_processing_status
540: FROM csi_t_transaction_lines
541: WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
542: AND source_transaction_id = p_parent_line_id;
543:
544: EXCEPTION
545: WHEN no_data_found THEN
1269: l_line_rec oe_order_pub.line_rec_type := oe_order_pub.g_miss_line_rec;
1270:
1271: CURSOR op_cur is
1272: SELECT line_id
1273: FROM oe_order_lines_all
1274: WHERE link_to_line_id = p_parent_line_id
1275: and nvl(cancelled_flag, 'N') <> 'Y' -- added for Bug 2946778. shegde
1276: ORDER BY line_number, shipment_number, option_number;
1277:
1892: IF p_ato_line_id = p_line_id THEN
1893:
1894: SELECT ato_line_id
1895: INTO l_ato_line_id
1896: FROM oe_order_lines_all
1897: WHERE line_id=p_top_model_line_id;
1898:
1899: IF l_ato_line_id IS NULL THEN
1900: x_item_type := 'ATO_MODEL';
1947: SELECT link_to_line_id ,
1948: org_id
1949: INTO l_parent_line_id,
1950: l_org_id
1951: FROM oe_order_lines_all
1952: WHERE line_id = p_current_line_id;
1953:
1954: <
1955:
1958: SELECT inventory_item_id ,
1959: link_to_line_id
1960: INTO l_inventory_item_id ,
1961: l_next_parent_line_id
1962: FROM oe_order_lines_all
1963: WHERE line_id = l_parent_line_id;
1964:
1965: SELECT nvl(msi.comms_nl_trackable_flag, 'N')
1966: INTO l_ib_trackable_flag
2272:
2273: END get_parent_instances;
2274:
2275: PROCEDURE get_partner_order_info(
2276: p_order_line_rec IN oe_order_lines_all%rowtype,
2277: x_end_customer_id OUT nocopy number,
2278: x_current_site_use_id OUT nocopy number,
2279: x_install_site_use_id OUT nocopy number,
2280: x_return_status OUT nocopy varchar2)
2396:
2397: PROCEDURE convert_wip_instance_to_cp(
2398: p_instance_id IN number,
2399: p_order_hdr_rec IN oe_order_headers_all%rowtype,
2400: p_order_line_rec IN oe_order_lines_all%rowtype,
2401: p_tld_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
2402: px_default_info_rec IN OUT nocopy default_info_rec,
2403: px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
2404: x_return_status OUT NOCOPY varchar2)
2407: l_party_site_id number;
2408: l_owner_party_id number;
2409: l_owner_account_id number;
2410:
2411: l_order_line_rec oe_order_lines_all%rowtype;
2412: l_order_header_rec oe_order_headers_all%rowtype;
2413:
2414: l_location_type_code varchar2(80);
2415: l_inst_object_ver_num number;
3028: x_return_status := fnd_api.g_ret_sts_error;
3029: END get_wip_instances;
3030:
3031: PROCEDURE get_wip_instances_for_line(
3032: p_option_line_rec IN oe_order_lines_all%rowtype,
3033: p_parent_line_rec IN oe_order_pub.line_rec_type,
3034: p_option_serial_code IN number,
3035: p_class_option_ratio IN number,
3036: p_config_rec IN config_rec,
3804: l_config_rec.item_id,
3805: l_config_rec.ship_organization_id,
3806: l_config_rec.order_quantity,
3807: l_config_rec.split_from_line_id
3808: FROM oe_order_lines_all oel
3809: WHERE oel.header_id = p_ato_header_id
3810: AND oel.link_to_line_id = p_ato_line_id
3811: AND oel.item_type_code = 'CONFIG';
3812: EXCEPTION
3822: l_config_rec.item_id,
3823: l_config_rec.ship_organization_id,
3824: l_config_rec.order_quantity,
3825: l_config_rec.split_from_line_id
3826: FROM oe_order_lines_all oel
3827: WHERE oel.header_id = p_ato_header_id
3828: AND oel.link_to_line_id = p_ato_line_id
3829: AND oel.item_type_code = 'CONFIG'
3830: AND oel.split_from_line_id is null;
4059: l_c_ind binary_integer := 0;
4060: l_config_instances config_serial_inst_tbl;
4061:
4062: l_order_hdr_rec oe_order_headers_all%rowtype;
4063: l_order_line_rec oe_order_lines_all%rowtype;
4064: l_tld_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
4065:
4066: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
4067:
4167: END IF;
4168: ELSE -- sub models
4169:
4170: SELECT * INTO l_order_line_rec
4171: FROM oe_order_lines_all
4172: WHERE line_id = p_config_rec.sub_model_line_id;
4173:
4174: SELECT * INTO l_order_hdr_rec
4175: FROM oe_order_headers_all
4477: END stamp_om_line;
4478:
4479: PROCEDURE stamp_om_line_for_options(
4480: p_order_hdr_rec IN oe_order_headers_all%rowtype,
4481: p_order_line_rec IN oe_order_lines_all%rowtype,
4482: p_wip_instances IN wip_instances,
4483: p_tld_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
4484: px_default_info_rec IN OUT nocopy default_info_rec,
4485: px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
4538: IS
4539: CURSOR config_cur IS
4540: SELECT msi.serial_number_control_code
4541: FROM mtl_system_items msi,
4542: oe_order_lines_all oel
4543: WHERE oel.link_to_line_id = p_ato_line_id
4544: AND oel.item_type_code = 'CONFIG'
4545: AND msi.organization_id = oel.ship_from_org_id
4546: AND msi.inventory_item_id = oel.inventory_item_id;
5026: END check_wip_requirements;
5027:
5028: /* routine to plug in the option classes in between the build option relation at WIP */
5029: PROCEDURE rebuild_relation_for_ato(
5030: p_order_line_rec IN oe_order_lines_all%rowtype,
5031: p_config_rec IN config_rec,
5032: p_config_instances IN config_serial_inst_tbl,
5033: px_default_info_rec IN OUT NOCOPY default_info_rec,
5034: px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
5164: api_log('rebuild_relation_for_ato');
5165:
5166: IF p_order_line_rec.item_type_code = 'CLASS' THEN
5167: get_tld(
5168: p_source_table => 'OE_ORDER_LINES_ALL',
5169: p_source_id => p_order_line_rec.line_id,
5170: p_source_flag => 'Y',
5171: p_processing_status => 'IN_PROCESS',
5172: x_line_dtl_tbl => l_src_tld_tbl,
5704: END rebuild_relation_for_ato;
5705:
5706: -- child order line instances should get hooked in to the ib trackable parent order line
5707: PROCEDURE build_parent_relation(
5708: p_order_line_rec IN oe_order_lines_all%rowtype,
5709: p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
5710: p_split_flag IN varchar2,
5711: p_identified_item_type IN varchar2,
5712: p_config_rec IN config_rec,
5771:
5772: IF nvl(l_parent_line_rec.line_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
5773:
5774: get_tld(
5775: p_source_table => 'OE_ORDER_LINES_ALL',
5776: p_source_id => p_order_line_rec.line_id,
5777: p_source_flag => 'Y',
5778: p_processing_status => 'IN_PROCESS',
5779: x_line_dtl_tbl => l_line_dtl_tbl,
6093: END mark_and_get_instances;
6094:
6095: -- builds relation to the next level
6096: PROCEDURE build_child_relation(
6097: p_order_line_rec IN oe_order_lines_all%ROWTYPE,
6098: p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
6099: p_identified_item_type IN varchar2,
6100: px_default_info_rec IN OUT NOCOPY default_info_rec,
6101: px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
6178:
6179: IF l_child_line_tbl.COUNT > 0 THEN
6180:
6181: get_tld(
6182: p_source_table => 'OE_ORDER_LINES_ALL',
6183: p_source_id => p_order_line_rec.line_id,
6184: p_source_flag => 'Y',
6185: p_processing_status => 'IN_PROCESS',
6186: x_line_dtl_tbl => l_tld_tbl,
6205: IF l_child_line_tbl(l_ind).inventory_item_id <> l_next_item_id THEN
6206: BEGIN
6207: select sum(ordered_quantity)
6208: into l_order_line_qty
6209: from oe_order_lines_all
6210: where link_to_line_id = l_child_line_tbl(l_ind).link_to_line_id
6211: and inventory_item_id = l_child_line_tbl(l_ind).inventory_item_id
6212: and model_remnant_flag = 'Y';
6213: EXCEPTION
6377: /* component of relationship with the parent (ato oc or the config item) */
6378:
6379: PROCEDURE process_ato_option_nsrl(
6380: p_order_hdr_rec IN oe_order_headers_all%rowtype,
6381: p_order_line_rec IN oe_order_lines_all%rowtype,
6382: p_config_rec IN config_rec,
6383: p_tld_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
6384: px_default_info_rec IN OUT nocopy default_info_rec,
6385: x_return_status OUT nocopy Varchar2)
6757: /* serialized config and re-builds the relation with the OC if OC is IB */
6758: /* trackable. Assy Component Relations are build at the WIP completion event*/
6759: PROCEDURE process_ato_option_srl(
6760: p_order_hdr_rec IN oe_order_headers_all%rowtype,
6761: p_order_line_rec IN oe_order_lines_all%rowtype,
6762: p_config_rec IN config_rec,
6763: p_config_instances IN config_serial_inst_tbl,
6764: p_tld_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
6765: px_default_info_rec IN OUT nocopy default_info_rec,
7082:
7083: -- for ato options that are processed from wip
7084: PROCEDURE process_ato_option_from_wip(
7085: p_order_header_rec IN oe_order_headers_all%rowtype,
7086: p_order_line_rec IN oe_order_lines_all%rowtype,
7087: p_config_rec IN config_rec,
7088: p_config_instances IN config_serial_inst_tbl,
7089: px_default_info_rec IN OUT nocopy default_info_rec,
7090: x_wip_processing OUT NOCOPY boolean,
7133: BEGIN
7134: SELECT transaction_line_id
7135: INTO l_transaction_line_id
7136: FROM csi_t_transaction_lines
7137: WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
7138: AND source_transaction_id = p_order_line_rec.line_id;
7139: EXCEPTION
7140: WHEN no_data_found THEN
7141: BEGIN
7141: BEGIN
7142: SELECT transaction_line_id
7143: INTO l_transaction_line_id
7144: FROM csi_t_transaction_lines
7145: WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
7146: AND source_transaction_id = p_order_line_rec.top_model_line_id;
7147:
7148: SELECT ordered_quantity
7149: INTO l_mdl_ordered_qty
7146: AND source_transaction_id = p_order_line_rec.top_model_line_id;
7147:
7148: SELECT ordered_quantity
7149: INTO l_mdl_ordered_qty
7150: FROM oe_order_lines_all
7151: WHERE line_id = p_order_line_rec.top_model_line_id;
7152:
7153: --Fix for bug5096435
7154: SELECT sum(ordered_quantity)/l_mdl_ordered_qty
7152:
7153: --Fix for bug5096435
7154: SELECT sum(ordered_quantity)/l_mdl_ordered_qty
7155: INTO l_qty_ratio
7156: FROM oe_order_lines_all
7157: WHERE link_to_line_id = p_order_line_rec.link_to_line_id
7158: AND inventory_item_id = p_order_line_rec.inventory_item_id;
7159:
7160: SELECT to_char(p_order_line_rec.top_model_line_id)||':'||
7184: END;
7185:
7186: IF l_transaction_line_id is not null THEN
7187: get_tld(
7188: p_source_table => 'OE_ORDER_LINES_ALL',
7189: p_source_id => p_order_line_rec.line_id,
7190: p_source_flag => 'Y',
7191: p_processing_status => 'UNPROCESSED',
7192: x_line_dtl_tbl => l_src_tld_tbl,
7260: -- bug 4966316 - item instance ownership conversion...
7261:
7262: PROCEDURE demo_fulfillment(
7263: p_txn_type_id IN number,
7264: p_order_line_rec IN oe_order_lines_all%rowtype,
7265: p_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
7266: px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
7267: x_return_status OUT nocopy varchar2)
7268: IS
7931:
7932: api_log('query_tld_and_update_ib');
7933:
7934: l_p_tl_rec.transaction_line_id := px_default_info_rec.transaction_line_id;
7935: l_p_tl_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
7936: l_p_tl_rec.source_transaction_id := p_order_line_rec.order_line_id;
7937: l_p_tl_rec.source_transaction_type_id := 51;
7938:
7939: debug('get transaction details for the final process. IN_PROCESS Details');
7938:
7939: debug('get transaction details for the final process. IN_PROCESS Details');
7940:
7941: l_tl_query_rec.transaction_line_id := px_default_info_rec.transaction_line_id;
7942: l_tl_query_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
7943: l_tl_query_rec.source_transaction_id := p_order_line_rec.order_line_id;
7944:
7945: l_tld_query_rec.processing_status := 'IN_PROCESS';
7946: l_tld_query_rec.source_transaction_flag := fnd_api.g_miss_char;
8134: l_txn_line_id number;
8135: l_txn_type_id number := 51;
8136: l_txn_sub_type_id number ;
8137: l_sub_type_rec sub_type_rec;
8138: l_src_txn_table varchar2(30) := 'OE_ORDER_LINES_ALL';
8139: l_transaction_line_id number;
8140:
8141: l_cascade_eligible varchar2(1) := 'N';
8142: l_model_hierarchy varchar2(300);
8209:
8210: l_cascaded_flag varchar2(1) := 'N';
8211: l_shippable_item_flag varchar2(1) := 'N';
8212:
8213: l_order_line_rec oe_order_lines_all%rowtype;
8214: l_order_header_rec oe_order_headers_all%rowtype;
8215:
8216: l_p_order_line_rec csi_order_ship_pub.order_line_rec;
8217: l_csi_txn_rec csi_datastructures_pub.transaction_rec;
8328: BEGIN
8329:
8330: SELECT *
8331: INTO l_order_line_rec
8332: FROM oe_order_lines_all
8333: WHERE line_id = p_order_line_id;
8334:
8335: SELECT *
8336: INTO l_order_header_rec
8761: IF l_order_line_rec.ato_line_id=l_order_line_rec.line_id THEN
8762:
8763: SELECT ato_line_id
8764: INTO l_ato_line_id
8765: FROM oe_order_lines_all
8766: WHERE line_id=l_order_line_rec.link_to_line_id;
8767:
8768: IF l_ato_line_id IS NULL THEN
8769: l_ato_rebuild_flag :='N';
8963: IF l_order_line_rec.model_remnant_flag = 'Y' THEN
8964: BEGIN
8965: select sum(ordered_quantity)
8966: into l_order_line_qty
8967: from oe_order_lines_all
8968: where link_to_line_id = l_order_line_rec.link_to_line_id
8969: and inventory_item_id = l_order_line_rec.inventory_item_id
8970: and model_remnant_flag = 'Y';
8971: EXCEPTION
9048: debug('top model line ('||l_order_line_rec.top_model_line_id||') has transaction details.');
9049:
9050: SELECT ordered_quantity
9051: INTO l_mdl_ordered_qty
9052: FROM oe_order_lines_all
9053: WHERE line_id = l_order_line_rec.top_model_line_id;
9054:
9055: --fix for bug5096435
9056: SELECT sum(ordered_quantity)/l_mdl_ordered_qty
9054:
9055: --fix for bug5096435
9056: SELECT sum(ordered_quantity)/l_mdl_ordered_qty
9057: INTO l_qty_ratio
9058: FROM oe_order_lines_all
9059: WHERE link_to_line_id = l_order_line_rec.link_to_line_id
9060: AND inventory_item_id = l_order_line_rec.inventory_item_id;
9061:
9062:
9402:
9403: UPDATE csi_t_transaction_lines
9404: SET processing_status = 'ERROR'
9405: WHERE source_transaction_id = p_order_line_id
9406: AND source_transaction_table = 'OE_ORDER_LINES_ALL';
9407:
9408: csi_utl_pkg.update_txn_line_dtl (
9409: p_source_trx_id => p_order_line_id,
9410: p_source_trx_table => 'OE_ORDER_LINES_ALL',
9406: AND source_transaction_table = 'OE_ORDER_LINES_ALL';
9407:
9408: csi_utl_pkg.update_txn_line_dtl (
9409: p_source_trx_id => p_order_line_id,
9410: p_source_trx_table => 'OE_ORDER_LINES_ALL',
9411: p_api_name => l_api_name,
9412: p_error_message => l_error_message );
9413:
9414: WHEN others THEN
9425:
9426: UPDATE csi_t_transaction_lines
9427: SET processing_status = 'ERROR'
9428: WHERE source_transaction_id = p_order_line_id
9429: AND source_transaction_table = 'OE_ORDER_LINES_ALL';
9430:
9431: csi_utl_pkg.update_txn_line_dtl (
9432: p_source_trx_id => p_order_line_id,
9433: p_source_trx_table => 'OE_ORDER_LINES_ALL',
9429: AND source_transaction_table = 'OE_ORDER_LINES_ALL';
9430:
9431: csi_utl_pkg.update_txn_line_dtl (
9432: p_source_trx_id => p_order_line_id,
9433: p_source_trx_table => 'OE_ORDER_LINES_ALL',
9434: p_api_name => l_api_name,
9435: p_error_message => l_error_message );
9436:
9437: END order_fulfillment;
9482: api_log('construct_txn_dtls');
9483:
9484: /* assign values for the columns in Txn_line_rec */
9485: l_c_tl_rec.source_transaction_id := p_order_line_rec.order_line_id ;
9486: l_c_tl_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
9487: l_c_tl_rec.source_transaction_type_id := 51;
9488: l_c_tl_rec.processing_status := 'IN_PROCESS';
9489: l_c_tl_rec.object_version_number := 1;
9490:
9813: l_api_name varchar2(30) := 'logical_drop_ship';
9814: l_txn_line_id number;
9815: l_txn_type_id number := 51;
9816: l_txn_sub_type_id number ;
9817: l_src_txn_table varchar2(30) := 'OE_ORDER_LINES_ALL';
9818: p_order_line_id number;
9819:
9820: l_debug_level number;
9821: l_error_message varchar2(2000);
9912: to_number(null) ib_install_loc_id,
9913: to_char(null) ib_current_loc,
9914: to_number(null) ib_current_loc_id
9915: FROM oe_order_headers_all ooh,
9916: oe_order_lines_all ool,
9917: mtl_system_items msi,
9918: mtl_unit_transactions mut,
9919: mtl_material_transactions mmt,
9920: mtl_secondary_inventories msei,
9970: to_number(null) ib_install_loc_id,
9971: to_char(null) ib_current_loc,
9972: to_number(null) ib_current_loc_id
9973: FROM oe_order_headers_all ooh,
9974: oe_order_lines_all ool,
9975: mtl_system_items msi,
9976: mtl_unit_transactions mut,
9977: mtl_transaction_lot_numbers mtln,
9978: mtl_material_transactions mmt,
10035: dbms_application_info.set_client_info(l_order_line_rec.org_id);
10036:
10037: SELECT ship_from_org_id
10038: INTO l_ship_from_org_id
10039: FROM oe_order_lines_all
10040: WHERE line_id = p_order_line_id;
10041:
10042: SELECT nvl(shippable_item_flag ,'N'),
10043: nvl(mtl_transactions_enabled_flag, 'N'),
10077: l_error_rec.inventory_item_id := l_order_line_rec.inv_item_id;
10078:
10079: -- Assign parameters for txn_line_rec
10080: l_txn_line_rec.source_transaction_id := l_order_line_rec.order_line_id;
10081: l_txn_line_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
10082:
10083: -- Check If user entered Transaction Details Exists
10084: l_trx_detail_exist := csi_t_txn_details_grp.check_txn_details_exist(
10085: p_txn_line_rec => l_txn_line_rec );
11240: retcode OUT NOCOPY NUMBER)
11241: IS
11242: CURSOR OE_LINE_CUR(p_freeze_date DATE) IS
11243: SELECT line_id,inventory_item_id,org_id,shippable_flag
11244: FROM OE_ORDER_LINES_ALL
11245: WHERE creation_date <= p_freeze_date
11246: AND nvl(fulfilled_flag,'N') <> 'Y'
11247: AND open_flag = 'Y';
11248: --