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);
?>