Standard LPN check Sql

SELECT * FROM apps.wms_license_plate_numbers 
WHERE lpn_id IN(&lpn_id); 

2. 

SELECT * FROM apps.wms_lpn_contents 
WHERE parent_lpn_id IN(&lpn_id); 

3. 

SELECT * FROM apps.mtl_txn_request_lines 
WHERE lpn_id IN(&lpn_id); 


4. 

SELECT * FROM apps.rcv_transactions 
WHERE lpn_id IN(&lpn_id) OR transfer_lpn_id IN(&lpn_id); 

5. 

SELECT * FROM apps.rcv_transactions_interface 
WHERE lpn_id IN(&lpn_id) OR transfer_lpn_id IN(&lpn_id); 

6. 

SELECT * FROM apps.rcv_supply 
WHERE lpn_id IN(&lpn_id); 

7. 

SELECT * FROM apps.mtl_material_transactions 
WHERE lpn_id IN(&lpn_id) OR transfer_lpn_id IN(&lpn_id) OR content_lpn_id IN(&lpn_id); 

8. 

SELECT * FROM apps.mtl_material_transactions_temp 
WHERE lpn_id IN(&lpn_id); 

9. 

SELECT * FROM apps.rcv_shipment_lines 
WHERE shipment_line_id IN 
(SELECT shipment_line_id FROM rcv_transactions 
WHERE lpn_id IN (&lpn_id) OR transfer_lpn_id IN (&lpn_id)); 

10. 

SELECT * FROM apps.mtl_supply 
WHERE shipment_line_id IN 
(SELECT shipment_line_id FROM rcv_transactions 
WHERE lpn_id IN (&lpn_id) OR transfer_lpn_id IN (&lpn_id)); 

11. 

SELECT * FROM apps.mtl_onhand_quantities_detail 
WHERE lpn_id IN (&lpn_id) ; 

12. 

SELECT * FROM apps.mtl_serial_numbers 
WHERE lpn_id IN (&lpn_id) ; 

13. 

SELECT * FROM apps.wip_entities 
WHERE wip_entity_id IN(SELECT txn_source_id FROM apps.mtl_txn_request_lines 
WHERE lpn_id IN(&lpn_id)); 

14. 

SELECT * FROM apps.wip_discrete_jobs 
WHERE wip_entity_id IN(SELECT txn_source_id FROM apps.mtl_txn_request_lines 
WHERE lpn_id IN(&lpn_id)); 

15. 

SELECT * FROM apps.wip_lpn_completions 
WHERE lpn_id IN(&lpn_id); 

16. 

SELECT * FROM apps.rcv_lots_supply 
WHERE lot_num='&lot_number'; 


17. 

SELECT * FROM apps.mtl_transaction_lots_temp 
WHERE lot_number='&lot_number'; 

18 

SELECT * FROM apps.wms_op_plan_instances 
WHERE source_task_id IN ( 
SELECT parent_line_id FROM apps.mtl_material_transactions_temp 
WHERE move_order_line_id IN ( 
SELECT line_id FROM apps.mtl_txn_request_lines 
WHERE lpn_id IN (SELECT lpn_id 
FROM apps.wms_license_plate_numbers 
START WITH lpn_id = &lpn_id 
CONNECT BY PRIOR lpn_id = parent_lpn_id) 
AND organization_id = &organization_id 
AND line_status = 7 
AND quantity-NVL(quantity_delivered,0) > 0)) 


19. 

SELECT * FROM apps.wms_op_operation_instances 
WHERE source_task_id IN ( 
SELECT transaction_temp_id FROM apps.mtl_material_transactions_temp 
WHERE move_order_line_id IN ( 
SELECT line_id FROM apps.mtl_txn_request_lines 
WHERE lpn_id IN (SELECT lpn_id 
FROM apps.wms_license_plate_numbers 
START WITH lpn_id = &lpn_id 
CONNECT BY PRIOR lpn_id = parent_lpn_id) 
AND organization_id = &organization_id 
AND line_status = 7 
AND quantity-NVL(quantity_delivered,0) > 0)) ; 

20 

SELECT * FROM apps.wms_dispatched_tasks 
WHERE transaction_temp_id IN ( 
SELECT transaction_temp_id FROM apps.mtl_material_transactions_temp 
WHERE move_order_line_id IN ( 
SELECT line_id FROM apps.mtl_txn_request_lines 
WHERE lpn_id IN (SELECT lpn_id 
FROM apps.wms_license_plate_numbers 
START WITH lpn_id = &lpn_id 
CONNECT BY PRIOR lpn_id = parent_lpn_id) 
AND organization_id = &organization_id 
AND line_status = 7 
AND quantity-NVL(quantity_delivered,0) > 0)) ; 


21. 

SELECT * FROM apps.rcv_shipment_headers 
WHERE SHIPMENT_HEADER_ID IN 
(SELECT SHIPMENT_HEADER_ID FROM apps.rcv_transactions 

WHERE lpn_id IN (&lpn_id) OR transfer_lpn_id IN (&lpn_id)); 

1 SELECT * FROM mtl_txn_request_lines 
WHERE lpn_id =10140859 
OR txn_source_id in(SELECT transaction_id FROM rcv_transactions 
WHERE lpn_id =10140859 OR transfer_lpn_id =10140859); 

2 SELECT * FROM rcv_transactions 
WHERE lpn_id =10140859 OR transfer_lpn_id =10140859; 

3 SELECT * FROM rcv_transactions_interface 
WHERE lpn_id =10140859 OR transfer_lpn_id =10140859; 

4 SELECT * FROM rcv_supply 
WHERE lpn_id =10140859; 

5 SELECT * FROM mtl_material_transactions 
WHERE lpn_id =10140859 OR transfer_lpn_id =10140859 or 
content_lpn_id =10140859; 

6 SELECT * FROM mtl_material_transactions_temp 
WHERE lpn_id =10140859; 

7 SELECT * FROM rcv_shipment_lines 
WHERE shipment_line_id IN 
(SELECT shipment_line_id FROM rcv_transactions 
WHERE lpn_id =10140859 OR transfer_lpn_id =10140859); 

8 SELECT * FROM mtl_supply 
WHERE shipment_line_id IN 
(SELECT shipment_line_id FROM rcv_transactions 
WHERE lpn_id =10140859 OR transfer_lpn_id =10140859); 

9 SELECT * FROM mtl_onhand_quantities_detail 
WHERE lpn_id =10140859 ;