[Home] [Help]
3052: -- This API is used to validate the Item Request Inputs
3053: --
3054: ************************************************************************************/
3055: PROCEDURE validate_get_item_request(
3056: p_get_opr_attrs_rec IN inv_ebi_get_operational_attrs,
3057: x_status OUT NOCOPY VARCHAR2,
3058: x_msg_count OUT NOCOPY NUMBER,
3059: x_msg_data OUT NOCOPY VARCHAR2
3060: )
4735: l_entity_count NUMBER :=0;
4736: l_org_id NUMBER;
4737: l_item_id NUMBER;
4738: l_item_output_tbl inv_ebi_get_opr_attrs_tbl;
4739: l_item_obj inv_ebi_get_operational_attrs;
4740: l_item_tbl FND_TABLE_OF_VARCHAR2_255;
4741: l_org_tbl FND_TABLE_OF_VARCHAR2_255;
4742: l_valid_item_tbl FND_TABLE_OF_VARCHAR2_255;
4743: l_valid_org_tbl FND_TABLE_OF_VARCHAR2_255;
4850: l_pk_col_name_val_pairs.TRIM(2);
4851:
4852: IF l_item_id IS NOT NULL THEN
4853: l_counter := l_counter + 1;
4854: l_item_obj := inv_ebi_get_operational_attrs( l_item_id, l_valid_item_tbl(i) , l_org_id, l_valid_org_tbl(j),NULL,NULL);
4855: l_item_output_tbl.EXTEND(1);
4856: l_item_output_tbl(l_counter) := l_item_obj;
4857: l_count := 1;
4858: END IF;
4860: END LOOP;
4861: ELSE
4862: FOR cur IN c_get_all_orgs(l_valid_item_tbl(i)) LOOP
4863: l_counter := l_counter + 1;
4864: l_item_obj := inv_ebi_get_operational_attrs( cur.inventory_item_id, l_valid_item_tbl(i) , cur.organization_id, cur.organization_code,NULL,NULL);
4865: l_item_output_tbl.EXTEND(1);
4866: l_item_output_tbl(l_counter) := l_item_obj;
4867: l_count := 1;
4868: END LOOP;
4925: l_item_tbl_flst inv_ebi_get_opr_attrs_tbl;
4926: l_msg_data VARCHAR2(2000);
4927: l_msg_count NUMBER;
4928: CURSOR c_get_item_list_pim IS
4929: SELECT inv_ebi_get_operational_attrs(item_pim.inventory_item_id,item_pim.concatenated_segments,item_pim.organization_id,item_pim.organization_code,NULL,NULL)
4930: FROM
4931: (SELECT mcr.inventory_item_id, msik.concatenated_segments, mcr.organization_id, mp.organization_code
4932: FROM mtl_cross_references_b mcr, mtl_system_items_b msi, mtl_parameters mp, mtl_system_items_kfv msik
4933: WHERE mcr.inventory_item_id = msi.inventory_item_id
4963: AND cql.last_update_date >= l_from_date
4964: AND cql.last_update_date <= l_to_date) item_pim;
4965:
4966: CURSOR c_get_item_list IS
4967: SELECT inv_ebi_get_operational_attrs(item_npim.inventory_item_id,item_npim.concatenated_segments,item_npim.organization_id,item_npim.organization_code,NULL,NULL)
4968: FROM
4969: (SELECT msi.inventory_item_id, msik.concatenated_segments, msi.organization_id, mp.organization_code
4970: FROM mtl_system_items_b msi, mtl_parameters mp, mtl_system_items_kfv msik
4971: WHERE msi.organization_id = mp.organization_id
4992: AND cql.last_update_date <= l_to_date
4993: ) item_npim;
4994:
4995: CURSOR c_get_item_flist IS
4996: SELECT inv_ebi_get_operational_attrs(item_flst.item_id,item_flst.item_name,item_flst.organization_id,item_flst.organization_code,NULL,NULL)
4997: FROM (
4998: SELECT a.item_id,a.item_name, a.organization_id,a.organization_code
4999: FROM THE (SELECT CAST( l_item_output_tbl as inv_ebi_get_opr_attrs_tbl)
5000: FROM dual ) a
5152: l_item_tbl_flst inv_ebi_get_opr_attrs_tbl;
5153: l_msg_data VARCHAR2(2000);
5154: l_msg_count NUMBER;
5155: CURSOR c_get_bal_item_lst_pim IS
5156: SELECT inv_ebi_get_operational_attrs(gibp.inventory_item_id,gibp.concatenated_segments, gibp.organization_id,gibp.organization_code,NULL,NULL)
5157: FROM (
5158: SELECT DISTINCT mcr.inventory_item_id,msik.concatenated_segments,mcr.organization_id,mp.organization_code
5159: FROM mtl_onhand_quantities_detail moq,mtl_cross_references_b mcr, mtl_parameters mp, mtl_system_items_kfv msik
5160: WHERE mcr.inventory_item_id = moq.inventory_item_id
5166: AND moq.last_update_date >= l_from_date
5167: AND moq.last_update_date <= l_to_date) gibp;
5168:
5169: CURSOR c_get_bal_item_lst IS
5170: SELECT inv_ebi_get_operational_attrs(gib.inventory_item_id,gib.concatenated_segments, gib.organization_id,gib.organization_code,NULL,NULL)
5171: FROM (SELECT DISTINCT moq.inventory_item_id,msik.concatenated_segments,moq.organization_id,mp.organization_code
5172: FROM mtl_onhand_quantities_detail moq, mtl_parameters mp, mtl_system_items_kfv msik
5173: WHERE moq.organization_id = mp.organization_id
5174: AND msik.organization_id = moq.organization_id
5176: AND moq.last_update_date >= l_from_date
5177: AND moq.last_update_date <= l_to_date) gib;
5178:
5179: CURSOR c_get_bal_item_flst IS
5180: SELECT inv_ebi_get_operational_attrs(gibf.item_id,gibf.item_name, gibf.organization_id,gibf.organization_code,NULL,NULL)
5181: FROM (
5182: SELECT a.item_id,a.item_name, a.organization_id,a.organization_code
5183: FROM THE (SELECT cast( l_item_output_tbl as inv_ebi_get_opr_attrs_tbl)
5184: FROM dual ) a