[Home] [Help]
278:
279: l_from_str := ' FROM rcv_supply rs,';
280: l_from_str := l_from_str || ' rcv_transactions rt,';
281: l_from_str := l_from_str || ' rcv_shipment_lines rsl,';
282: l_from_str := l_from_str || ' rcv_shipment_headers rsh';
283:
284: l_where_str := ' WHERE rs.supply_source_id = rt.transaction_id';
285: l_where_str := l_where_str || ' AND rs.to_organization_id = :org_id';
286: l_where_str := l_where_str || ' AND rs.supply_type_code = ''RECEIVING''';
864: IF (p_receipt_number IS NOT NULL) THEN
865: l_receipt_used := 1;
866: l_where_str := l_where_str||' AND mol.inventory_item_id';
867: l_where_str := l_where_str||' IN (SELECT rsl.item_id';
868: l_where_str := l_where_str||' FROM rcv_shipment_lines rsl,rcv_shipment_headers rsh';
869: l_where_str := l_where_str||' WHERE rsh.receipt_num = :receipt_num';
870: l_where_str := l_where_str||' AND rsh.shipment_header_id = rsl.shipment_header_id)';
871: ELSIF (p_document_type = 'PO') THEN
872: IF (p_document_id IS NOT NULL AND p_document_line_id IS NOT NULL) THEN
936: ELSE
937: IF (p_document_type = 'ASN') THEN
938: l_where_str := l_where_str||' AND mol.inventory_item_id';
939: l_where_str := l_where_str||' IN (SELECT rs.item_id';
940: l_where_str := l_where_str||' FROM rcv_supply rs,rcv_shipment_headers rsh';
941: l_where_str := l_where_str||' WHERE rsh.asn_type IN (''ASN'',''ASBN'')';
942: l_where_str := l_where_str||' AND rsh.shipment_num is not null';
943: l_where_str := l_where_str||' AND rs.shipment_header_id = rsh.shipment_header_id';
944: l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
944: l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
945: ELSE
946: l_where_str := l_where_str||' AND mol.inventory_item_id';
947: l_where_str := l_where_str||' IN (SELECT rs.item_id';
948: l_where_str := l_where_str||' FROM rcv_supply rs,rcv_shipment_headers rsh';
949: l_where_str := l_where_str||' WHERE rsh.asn_type NOT IN (''ASN'',''ASBN'')';
950: l_where_str := l_where_str||' AND rsh.shipment_num is not null';
951: l_where_str := l_where_str||' AND rs.to_organization_id = :org_id';
952: l_where_str := l_where_str||' AND rs.shipment_header_id = rsh.shipment_header_id';
1312: IF l_material_rec.reference_id IS NOT NULL THEN
1313: BEGIN
1314: SELECT rsh.shipment_header_id, rsl.shipment_line_id, rsl.requisition_line_id, rsh.shipment_num, rsh.asn_type, rsh.receipt_source_code, rsh.ship_to_org_id, rsl.line_num, rsh.receipt_num, rsh.vendor_id, rsl.from_organization_id
1315: INTO l_shipment_header_id, l_shipment_line_id, l_req_line_id, l_shipment_num, l_asn_type, l_receipt_source_code, l_ship_to_org_id, l_line_num, l_receipt_num, l_vendor_id, l_src_org_id
1316: FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
1317: WHERE rsh.shipment_header_id = rsl.shipment_header_id
1318: AND rsl.shipment_line_id = l_material_rec.reference_id;
1319: EXCEPTION
1320: WHEN no_data_found THEN
1375: BEGIN
1376: SELECT rsh.shipment_header_id, rsh.shipment_num, rsh.receipt_num,rsh.vendor_id
1377: ,rsl.shipment_line_id, rsl.line_num,rsl.from_organization_id
1378: INTO l_material_rec.document_id, l_material_rec.document_number, l_receipt_num, l_vendor_id,l_shipment_line_id, l_line_num, l_src_org_id
1379: FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
1380: WHERE rsh.shipment_header_id = rsl.shipment_header_id
1381: AND rsl.shipment_line_id = l_material_rec.reference_id;
1382: EXCEPTION
1383: WHEN no_data_found THEN
1431: ELSE
1432: IF (l_material_rec.reference_id IS NOT NULL) THEN
1433: BEGIN
1434: SELECT rsh.vendor_id INTO l_material_rec.vendor_id
1435: FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
1436: WHERE rsh.shipment_header_id = rsl.shipment_header_id
1437: AND rsl.shipment_line_id = l_material_rec.reference_id;
1438: EXCEPTION
1439: WHEN no_data_found THEN