[Home] [Help]
6:
7: -- Define global cursors
8: CURSOR get_instance_date(c_instance_id NUMBER) IS
9: SELECT active_end_date
10: FROM csi_item_instances
11: WHERE instance_id = c_instance_id;
12:
13: -- Local validation procedures
14: -- Procedure to validate serial numbers
333: /*
334: SELECT o.operating_unit
335: FROM org_organization_definitions o,
336: mtl_system_items_kfv m,
337: csi_item_instances c
338: WHERE c.instance_id = p_instance_id
339: AND c.inventory_item_id = m.inventory_item_id
340: AND c.inv_master_organization_id = m.organization_id
341: AND m.organization_id = o.organization_id;
341: AND m.organization_id = o.organization_id;
342: */
343: SELECT i.operating_unit
344: FROM inv_organization_info_v i, mtl_system_items_kfv m,
345: csi_item_instances c
346: WHERE c.instance_id = p_instance_id AND
347: c.inventory_item_id = m.inventory_item_id AND
348: c.inv_master_organization_id = m.organization_id AND
349: m.organization_id = i.organization_id;
755: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
756: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
757: CURSOR get_csi_obj_ver_num(c_instance_id NUMBER) IS
758: SELECT object_version_number
759: FROM csi_item_instances
760: WHERE instance_id = c_instance_id
761: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
762: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
763:
1376: unit_of_measure,
1377: install_date,
1378: inventory_revision,
1379: object_version_number
1380: FROM csi_item_instances
1381: WHERE instance_id = c_instance_id;
1382: --Removed on 02/26/2004, otherwise for expired instance, the error message displayed makes no sense.
1383: --AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1384: --AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2221: p_data => x_msg_data);
2222: END;
2223:
2224: -- Define procedure install_new_instance
2225: -- This API is used to create a new instance in csi_item_instances and assign it
2226: -- to a UC node.
2227: PROCEDURE install_new_instance(
2228: p_api_version IN NUMBER := 1.0,
2229: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2356: party_id,
2357: party_source_table,
2358: instance_party_id,
2359: csi.wip_job_id
2360: FROM csi_item_instances csi, csi_i_parties p
2361: WHERE csi.instance_id = p.instance_id
2362: AND p.relationship_type_code = 'OWNER'
2363: AND csi.instance_id = c_csi_instance_id
2364: AND trunc(SYSDATE) < trunc(nvl(csi.active_end_date, SYSDATE+1));
2401: -- SATHAPLI::ER 7419780, 24-Aug-2009, fetch the INTERCHANGE_REASON from the table ahl_item_associations_tl too
2402: CURSOR get_interchange_type (c_instance_id NUMBER, c_relationship_id NUMBER) IS
2403: SELECT i.interchange_type_code,
2404: itl.interchange_reason
2405: FROM csi_item_instances c,
2406: ahl_item_associations_b i,
2407: ahl_item_associations_tl itl,
2408: ahl_mc_relationships m
2409: WHERE m.relationship_id = c_relationship_id
3307: x_msg_data OUT NOCOPY VARCHAR2,
3308: p_uc_header_id IN NUMBER,
3309: p_parent_instance_id IN NUMBER,
3310: p_instance_id IN NUMBER,
3311: p_instance_number IN csi_item_instances.instance_number%TYPE := NULL,
3312: p_relationship_id IN NUMBER,
3313: p_csi_ii_ovn IN NUMBER,
3314: p_prod_user_flag IN VARCHAR2,
3315: -- SURRKUMA :: 13694898 :: Flag to bypass the validation of position rules, 29-FEB-2012
3364: l_csi_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
3365: l_csi_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
3366: l_csi_instance_id_lst csi_datastructures_pub.id_tbl;
3367: l_party_account_rec csi_datastructures_pub.party_account_rec;
3368: l_serial_number csi_item_instances.serial_number%TYPE;
3369: l_mfg_serial_number_flag csi_item_instances.mfg_serial_number_flag%TYPE;
3370: l_serial_number_tag csi_iea_values.attribute_value%TYPE;
3371:
3372: l_return_val BOOLEAN;
3365: l_csi_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
3366: l_csi_instance_id_lst csi_datastructures_pub.id_tbl;
3367: l_party_account_rec csi_datastructures_pub.party_account_rec;
3368: l_serial_number csi_item_instances.serial_number%TYPE;
3369: l_mfg_serial_number_flag csi_item_instances.mfg_serial_number_flag%TYPE;
3370: l_serial_number_tag csi_iea_values.attribute_value%TYPE;
3371:
3372: l_return_val BOOLEAN;
3373: l_transaction_type_id NUMBER;
3530: C.instance_usage_code,
3531: C.location_type_code,
3532: C.object_version_number,
3533: U.unit_config_header_id uc_header_id
3534: FROM csi_item_instances C,
3535: ahl_unit_config_headers U
3536: WHERE C.instance_id = c_instance_id
3537: AND C.instance_id = U.csi_item_instance_id (+)
3538: --AND U.parent_uc_header_id (+) IS NULL
3582: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3583:
3584: CURSOR get_serial_number(c_instance_id NUMBER) IS
3585: SELECT serial_number, mfg_serial_number_flag
3586: FROM csi_item_instances
3587: WHERE instance_id = c_instance_id
3588: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3589: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3590:
3602: -- SATHAPLI::ER 7419780, 24-Aug-2009, fetch the INTERCHANGE_REASON from the table ahl_item_associations_tl too
3603: CURSOR get_interchange_type (c_instance_id NUMBER, c_relationship_id NUMBER) IS
3604: SELECT i.interchange_type_code,
3605: itl.interchange_reason
3606: FROM csi_item_instances c,
3607: ahl_item_associations_b i,
3608: ahl_item_associations_tl itl,
3609: ahl_mc_relationships m
3610: WHERE m.relationship_id = c_relationship_id
4448: p_uc_header_id IN NUMBER,
4449: p_parent_instance_id IN NUMBER,
4450: p_old_instance_id IN NUMBER,
4451: p_new_instance_id IN NUMBER,
4452: p_new_instance_number IN csi_item_instances.instance_number%TYPE := NULL,
4453: p_relationship_id IN NUMBER,
4454: p_csi_ii_ovn IN NUMBER,
4455: p_prod_user_flag IN VARCHAR2,
4456: x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type)
4665: C.unit_of_measure uom_code,
4666: C.inv_subinventory_name,
4667: C.inv_locator_id,
4668: to_number(NULL) uc_header_id
4669: FROM csi_item_instances C,
4670: mtl_system_items_kfv M,
4671: ahl_mc_relationships R,
4672: ahl_item_associations_b A
4673: WHERE C.inventory_item_id = M.inventory_item_id
4803: -- this sub-unit should not be installed and should have valid status
4804: WHERE parent_uc_header_id IS NULL
4805: AND unit_config_status_code IN ('COMPLETE', 'INCOMPLETE')
4806: )U,
4807: csi_item_instances C,
4808: mtl_system_items_kfv M
4809: WHERE U.csi_instance_id = C.instance_id
4810: AND C.inventory_item_id = M.inventory_item_id
4811: AND C.inv_master_organization_id = M.organization_id
5444: -- Please refer earlier version for the details
5445:
5446: l_instance_id NUMBER;
5447: l_inventory_item_id NUMBER;
5448: l_instance_number csi_item_instances.instance_number%TYPE := upper(nvl(p_instance_number, '%'));
5449: l_inventory_org_id NUMBER;
5450: l_quantity NUMBER;
5451: l_inventory_revision VARCHAR2(3);
5452: l_uom_code VARCHAR2(3);
5452: l_uom_code VARCHAR2(3);
5453: l_item_assoc_id NUMBER;
5454: l_priority NUMBER;
5455: l_item_number mtl_system_items_kfv.concatenated_segments%TYPE :=upper(nvl(p_item_number, '%'));
5456: l_serial_number csi_item_instances.serial_number%TYPE := upper(nvl(p_serial_number, '%'));
5457: l_uc_header_id NUMBER;
5458: i NUMBER;
5459: j NUMBER;
5460: l_dummy_char VARCHAR2(1);
5492: C.quantity,
5493: C.inventory_revision,
5494: C.unit_of_measure uom_code,
5495: to_number(NULL) uc_header_id
5496: FROM csi_item_instances C,
5497: mtl_system_items_kfv M,
5498: ahl_mc_relationships R,
5499: ahl_item_associations_b A
5500: WHERE C.inventory_item_id = M.inventory_item_id
5608: CR.relationship_type_code (+) = 'COMPONENT-OF' AND
5609: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
5610: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)
5611: ) U,
5612: csi_item_instances C,
5613: mtl_system_items_kfv M
5614: WHERE U.csi_instance_id = C.instance_id
5615: AND C.inventory_item_id = M.inventory_item_id
5616: AND C.inv_master_organization_id = M.organization_id
6189: END get_available_instances;
6190:
6191: -- SATHAPLI::FP ER 6504147, 18-Nov-2008
6192: -- Define procedure create_unassigned_instance.
6193: -- This API is used to create a new instance in csi_item_instances and assign it
6194: -- to the UC root node as extra node.
6195:
6196: PROCEDURE create_unassigned_instance(
6197: p_api_version IN NUMBER := 1.0,
6222: party_id,
6223: party_source_table,
6224: instance_party_id,
6225: csi.wip_job_id
6226: FROM csi_item_instances csi, csi_i_parties p
6227: WHERE csi.instance_id = p.instance_id
6228: AND p.relationship_type_code = 'OWNER'
6229: AND csi.instance_id = p_csi_instance_id
6230: AND trunc(nvl(csi.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
6698: l_relationship_id NUMBER;
6699: l_mc_header_id NUMBER;
6700:
6701: l_instance_id NUMBER;
6702: l_instance_number csi_item_instances.instance_number%TYPE;
6703: l_inventory_item_id NUMBER;
6704: l_inv_master_org_id NUMBER;
6705: l_quantity NUMBER;
6706: l_inventory_revision VARCHAR2(3);
6705: l_quantity NUMBER;
6706: l_inventory_revision VARCHAR2(3);
6707: l_uom_code VARCHAR2(3);
6708: l_item_assoc_id NUMBER;
6709: l_serial_number csi_item_instances.serial_number%TYPE := upper(nvl(p_serial_number, '%'));
6710: l_uc_header_id NUMBER;
6711: iIndex NUMBER;
6712: l_dummy_char VARCHAR2(1);
6713: l_ignore_quant_vald VARCHAR2(1) := 'Y';
6740: CII.inventory_revision,
6741: CII.unit_of_measure uom_code,
6742: to_number(NULL) uc_header_id,
6743: CII.wip_job_id
6744: FROM csi_item_instances CII,
6745: mtl_system_items_kfv MSIK,
6746: ahl_mc_relationships AMR,
6747: ahl_item_associations_b AIA,
6748: hr_all_organization_units_tl HAOUT
6809: CR.relationship_type_code (+) = 'COMPONENT-OF' AND
6810: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
6811: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)
6812: ) U,
6813: csi_item_instances CII,
6814: mtl_system_items_kfv MSIK,
6815: hr_all_organization_units_tl HAOUT
6816: WHERE CII.inventory_item_id = NVL(c_inventory_item_id, CII.inventory_item_id)
6817: AND U.csi_instance_id = CII.instance_id
7114: WHERE config_status_code = 'COMPLETE');
7115:
7116: CURSOR is_instance_valid(c_instance_id IN NUMBER, c_relationship_id IN NUMBER) IS
7117: SELECT CII.inventory_item_id
7118: FROM csi_item_instances CII,
7119: mtl_system_items_kfv MSIK,
7120: ahl_mc_relationships AMR,
7121: ahl_item_associations_b AIA
7122: WHERE CII.instance_id = c_instance_id
7160: CR.relationship_type_code (+) = 'COMPONENT-OF' AND
7161: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
7162: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)
7163: ) U,
7164: csi_item_instances CII,
7165: mtl_system_items_kfv MSIK
7166: WHERE CII.instance_id = c_instance_id
7167: AND U.csi_instance_id = CII.instance_id
7168: AND CII.inventory_item_id = MSIK.inventory_item_id