ID; }, $data->data)); $sql = "UPDATE apps.yty_g_prc_modifier_list_tl SET approval_flag = 'P', submitted_date = sysdate, approval_level = 1 WHERE period = :period AND currency_code = :currency AND NVL(approval_flag,'N') IN ('N','R') AND created_by = :userid AND id in (".$id_selected.")"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':period', $data->period->ID); oci_bind_by_name($sqlarr, ':currency', $data->currency->ID); oci_bind_by_name($sqlarr, ':userid', $data->userid); $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr); $errorMsg = json_encode($e['message']); goto finalStep; } //App Trans foreach($data->data as $dt) { //Check If exists or not. If exist, get the max status $sql = "SELECT MAX(NVL(status,0))+1 status FROM YTYCUSTOM.USER_REG_APP_TRANS WHERE doc_no = :doc_no AND doc_type_code = '2003'"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':doc_no', $dt->ID); $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr); $resMsg = json_encode($e['message']); } $max_status = 0; while ($rw = oci_fetch_array($sqlarr)) { $max_status = $rw['STATUS']; } if ($dt->COMPANY == "MST") { $sql = "INSERT INTO YTYCUSTOM.USER_REG_APP_TRANS (emp_id, approver_emp_id, appr_level, doc_type_code, doc_no, action, enter_date, enter_by, status) SELECT '".$data->userid."', '".$data->userid."', 0, '2003', '".$dt->ID."', 'Submit', sysdate, '".$data->userid."', '".$max_status."' FROM dual UNION SELECT '".$data->userid."', approver_emp_id, appr_level, doc_type_code, '".$dt->ID."', NULL, NULL, NULL, '".$max_status."' FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a WHERE a.portal_name = 'PRICE_APPROVAL_MST' AND NVL(a.enabled, 'Y') = 'Y' AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to ORDER BY 3"; } else if ($dt->COMPANY == "YTY") { $sql = "INSERT INTO YTYCUSTOM.USER_REG_APP_TRANS (emp_id, approver_emp_id, appr_level, doc_type_code, doc_no, action, enter_date, enter_by, status) SELECT '".$data->userid."', '".$data->userid."', 0, '2003', '".$dt->ID."', 'Submit', sysdate, '".$data->userid."', '".$max_status."' FROM dual UNION SELECT '".$data->userid."', approver_emp_id, appr_level, doc_type_code, '".$dt->ID."', NULL, NULL, NULL, '".$max_status."' FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a WHERE a.portal_name = 'PRICE_APPROVAL_YTY' AND NVL(a.enabled, 'Y') = 'Y' AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to ORDER BY 3"; } $sqlarr = oci_parse($dbcon , $sql); $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT); if (!$r) { $e = oci_error($sqlarr); $errorMsg = json_encode($e['message']); goto finalStep; } } //Submitter $sql = "SELECT emp_id, employee_name, email_id FROM production.employee_master WHERE emp_id = :userid"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':userid', $data->userid); $r = oci_execute($sqlarr); if (!$r) { $e = oci_error($sqlarr); $resMsg = json_encode($e['message']); } oci_fetch_all($sqlarr, $rows_emp, null, null, OCI_FETCHSTATEMENT_BY_ROW); //Approver Email if ($data->data[0]->COMPANY == "MST") { $sql = "SELECT to_char(wm_concat(DISTINCT email_id)) email_apprv FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a WHERE portal_name = 'PRICE_APPROVAL_MST' AND NVL(a.enabled, 'Y') = 'Y' AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to AND appr_level = 1"; } else if ($data->data[0]->COMPANY == "YTY") { $sql = "SELECT to_char(wm_concat(DISTINCT email_id)) email_apprv FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_APP a WHERE portal_name = 'PRICE_APPROVAL_YTY' AND NVL(a.enabled, 'Y') = 'Y' AND trunc(SYSDATE) BETWEEN a.eff_from AND a.eff_to AND appr_level = 1"; } $sqlarr = oci_parse($dbcon , $sql); $r = oci_execute($sqlarr); if (!$r) { $e = oci_error($sqlarr); $resMsg = json_encode($e['message']); } while ($rw = oci_fetch_array($sqlarr)) { $apprv_mail = $rw['EMAIL_APPRV']; } //$apprv_mail = 'reyndo.frenliedo@ytygroup.com.my'; //$apprv_mail = 'suresh.bhalla@ytygroup.com.my,laura.chong@ytygroup.com.my'; //Approver Email $html='
'; if ($data->data[0]->COMPANY == "MST") { $html.=''; } else if ($data->data[0]->COMPANY == "YTY") { $html.=''; } $html.='

Dear Approver,

Your kind approval is sought on the '.$data->data[0]->COMPANY.'`s quotation price list for '.$data->period->ID.'. Please login to Pricing system to respond.


Sincerely,
YTY Workflow

Note: This message is auto-generated so please do not respond to this email.

'; $mailSubject="Request for ".$data->data[0]->COMPANY."`s quotation price list for ".$data->period->ID; $strInsertEmailQuery = "BEGIN ytycustom.set_notification.generate(ytycustom.set_notification.get_id, --no change
'EMAIL', --no change
'".$apprv_mail."', --to email id
NULL, --cc email id (Null for none)
NULL, --attachment file path, let it be null for the time being
'".$mailSubject."', --Email Subject
'".$html."', --Email body (in html format)
'HTML', --no change
SYSDATE, --no change
'ORACLE PRICING', --Claim system (will change later on)
Null --bcc email id (Null for none)
); END;"; $insertEmailQuery = oci_parse($dbcon, $strInsertEmailQuery); oci_execute($insertEmailQuery); //Submitter Email $html1='

Dear '.$rows_emp[0]["EMPLOYEE_NAME"].',

Your quotation price list for '.$data->period->ID.' is sent for Approval.


Sincerely,
YTY Workflow

Note: This message is auto-generated so please do not respond to this email.

'; $mailSubject="Request for quotation price list for ".$data->period->ID; $strInsertEmailQuery1= "BEGIN ytycustom.set_notification.generate(ytycustom.set_notification.get_id, --no change
'EMAIL', --no change
'".$rows_emp[0]["EMAIL_ID"]."', --to email id
NULL, --cc email id (Null for none)
NULL, --attachment file path, let it be null for the time being
'".$mailSubject."', --Email Subject
'".$html1."', --Email body (in html format)
'HTML', --no change
SYSDATE, --no change
'ORACLE PRICING', --Claim system (will change later on)
Null --bcc email id (Null for none)
); END;"; $insertEmailQuery1 = oci_parse($dbcon, $strInsertEmailQuery1); oci_execute($insertEmailQuery1); finalStep: $result = []; if ($errorMsg == "") { oci_commit($dbcon); $result[] = [ "message" => "Success", "id" => $newid ]; } else { oci_rollback($dbcon); $result[] = [ "message" => $errorMsg, "id" => "" ]; } print json_encode($result); ?>