details[0]->INSPECTION_ID; //Get the header data $sql = "SELECT id, customer, ref_no, irrd, serial_no, brand, total_ctn, sample_data, application_status FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl WHERE id = :ID"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':ID', $data->details[0]->INSPECTION_ID); $r = oci_execute($sqlarr); if (!$r) { $e = oci_error($sqlarr); $errorMsg = json_encode($e['message']); goto finalStep; } oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW); $sql_det = " UPDATE ytycustom.gth_rack_master SET occupied = NULL, ref_no = NULL, customer = NULL, brand = NULL, size_code = NULL, quantity = NULL, irrd = NULL, serial_no = NULL, inspection_id = NULL, line_id = NULL WHERE rack_code IN (SELECT racking_no FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl WHERE inspection_id = :INSPECTION_ID)"; $sqlarr_det = oci_parse($dbcon , $sql_det); oci_bind_by_name($sqlarr_det, ':INSPECTION_ID', $data->details[0]->INSPECTION_ID); $r = oci_execute($sqlarr_det, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr_det); $errorMsg = json_encode($e['message']); goto finalStep; } if (empty($data->details[0]->ID)) { foreach ($data->details as $det) { $sql_det = "INSERT INTO YTYCUSTOM.XX_YTY_INCOMING_OUTGOING_INSPECTION_DETAIL_STL (INSPECTION_ID, PALLET_NO, QUANTITY_OF_CARTONS, SIZE_CODE, RACKING_NO, PICKED_BY_DATE, PICKED_BY, LOT_NO) VALUES (:INSPECTION_ID, :PALLET_NO, :QUANTITY_OF_CARTONS, :SIZE_CODE, :RACKING_NO, TO_DATE(:PICKED_BY_DATE,'YYYY-MM-DD'), :PICKED_BY, :LOT_NO) RETURNING ID INTO :NEWID"; $sqlarr_det = oci_parse($dbcon , $sql_det); oci_bind_by_name($sqlarr_det, ':INSPECTION_ID', $det->INSPECTION_ID); oci_bind_by_name($sqlarr_det, ':PALLET_NO', $det->PALLET_NO); oci_bind_by_name($sqlarr_det, ':QUANTITY_OF_CARTONS', $det->QUANTITY_OF_CARTONS); oci_bind_by_name($sqlarr_det, ':SIZE_CODE', $det->SIZE_CODE->ID); oci_bind_by_name($sqlarr_det, ':RACKING_NO', $det->RACKING_NO->ID); oci_bind_by_name($sqlarr_det, ':PICKED_BY', $det->PICKED_BY->ID); oci_bind_by_name($sqlarr_det, ':PICKED_BY_DATE', $det->PICKED_BY_DATE); oci_bind_by_name($sqlarr_det, ':LOT_NO', $det->LOT_NO); oci_bind_by_name($sqlarr_det, ':NEWID', $line_id, 20); $r = oci_execute($sqlarr_det, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr_det); $errorMsg = json_encode($e['message']); goto finalStep; } //Racking $sql_rak = "UPDATE YTYCUSTOM.GTH_RACK_MASTER SET OCCUPIED = 'Y', REF_NO = :REF_NO, CUSTOMER = :CUSTOMER, BRAND = :BRAND, SIZE_CODE = :SIZE_CODE, QUANTITY = :QUANTITY, IRRD = :IRRD, SERIAL_NO = :SERIAL_NO, INSPECTION_ID = :INSPECTION_ID, LINE_ID = :LINE_ID WHERE RACK_CODE = :RACK_CODE"; $sqlarr_rak = oci_parse($dbcon , $sql_rak); oci_bind_by_name($sqlarr_rak, ':REF_NO', $rows_main[0]["REF_NO"]); oci_bind_by_name($sqlarr_rak, ':CUSTOMER', $rows_main[0]["CUSTOMER"]); oci_bind_by_name($sqlarr_rak, ':BRAND', $rows_main[0]["BRAND"]); oci_bind_by_name($sqlarr_rak, ':SIZE_CODE', $det->SIZE_CODE->ID); oci_bind_by_name($sqlarr_rak, ':QUANTITY', $det->QUANTITY_OF_CARTONS); oci_bind_by_name($sqlarr_rak, ':RACK_CODE', $det->RACKING_NO->ID); oci_bind_by_name($sqlarr_rak, ':IRRD', $rows_main[0]["IRRD"]); oci_bind_by_name($sqlarr_rak, ':SERIAL_NO', $rows_main[0]["SERIAL_NO"]); oci_bind_by_name($sqlarr_rak, ':INSPECTION_ID', $det->INSPECTION_ID); oci_bind_by_name($sqlarr_rak, ':LINE_ID', $line_id); $r = oci_execute($sqlarr_rak, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr_det); $errorMsg = json_encode($e['message']); goto finalStep; } } } else { foreach ($data->details as $det) { $sql_det = "UPDATE YTYCUSTOM.XX_YTY_INCOMING_OUTGOING_INSPECTION_DETAIL_STL SET RACKING_NO = :RACKING_NO, PICKED_BY = :PICKED_BY, PICKED_BY_DATE = TO_DATE(:PICKED_BY_DATE,'YYYY-MM-DD') WHERE INSPECTION_ID = :INSPECTION_ID AND ID = :ID"; $sqlarr_det = oci_parse($dbcon , $sql_det); oci_bind_by_name($sqlarr_det, ':RACKING_NO', $det->RACKING_NO->ID); oci_bind_by_name($sqlarr_det, ':PICKED_BY', $det->PICKED_BY->ID); oci_bind_by_name($sqlarr_det, ':PICKED_BY_DATE', $det->PICKED_BY_DATE); oci_bind_by_name($sqlarr_det, ':INSPECTION_ID', $det->INSPECTION_ID); oci_bind_by_name($sqlarr_det, ':ID', $det->ID); $r = oci_execute($sqlarr_det, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr_det); $errorMsg = json_encode($e['message']); goto finalStep; } //Racking $sql_rak = " UPDATE YTYCUSTOM.GTH_RACK_MASTER SET OCCUPIED = 'Y', REF_NO = :REF_NO, CUSTOMER = :CUSTOMER, BRAND = :BRAND, SIZE_CODE = :SIZE_CODE, QUANTITY = :QUANTITY, IRRD = :IRRD, SERIAL_NO = :SERIAL_NO, INSPECTION_ID = :INSPECTION_ID, LINE_ID = :LINE_ID WHERE RACK_CODE = :RACK_CODE"; $sqlarr_rak = oci_parse($dbcon , $sql_rak); oci_bind_by_name($sqlarr_rak, ':REF_NO', $rows_main[0]["REF_NO"]); oci_bind_by_name($sqlarr_rak, ':CUSTOMER', $rows_main[0]["CUSTOMER"]); oci_bind_by_name($sqlarr_rak, ':BRAND', $rows_main[0]["BRAND"]); oci_bind_by_name($sqlarr_rak, ':SIZE_CODE', $det->SIZE_CODE->ID); oci_bind_by_name($sqlarr_rak, ':QUANTITY', $det->QUANTITY_OF_CARTONS); oci_bind_by_name($sqlarr_rak, ':RACK_CODE', $det->RACKING_NO->ID); oci_bind_by_name($sqlarr_rak, ':IRRD', $rows_main[0]["IRRD"]); oci_bind_by_name($sqlarr_rak, ':SERIAL_NO', $rows_main[0]["SERIAL_NO"]); oci_bind_by_name($sqlarr_rak, ':INSPECTION_ID', $det->INSPECTION_ID); oci_bind_by_name($sqlarr_rak, ':LINE_ID', $det->ID); $r = oci_execute($sqlarr_rak, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr_det); $errorMsg = json_encode($e['message']); goto finalStep; } } } $sql_det = " UPDATE ytycustom.xx_yty_incoming_outgoing_inspection_tbl SET application_status = '3', irradiation_lock = 'Y' WHERE id = :INSPECTION_ID"; $sqlarr_det = oci_parse($dbcon , $sql_det); oci_bind_by_name($sqlarr_det, ':INSPECTION_ID', $data->details[0]->INSPECTION_ID); $r = oci_execute($sqlarr_det, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr_det); $errorMsg = json_encode($e['message']); goto finalStep; } finalStep: $result = []; if ($errorMsg == "") { oci_commit($dbcon); $result[] = [ "message" => "Success", "id" => $newid ]; } else { oci_rollback($dbcon); $result[] = [ "message" => $errorMsg, "id" => "" ]; } print json_encode($result); ?>