[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
l_Last_Updated_By PO_ACCEPTANCES.last_updated_by%TYPE;
select PO_HEADER_ID
from PO_HEADERS
where segment1 = X_PO_NUMBER
and type_lookup_code in ('STANDARD', 'BLANKET', 'CONTRACT', 'PLANNED');
select po_release_id
from po_releases
where po_header_id = l_po_header_id
and release_num = X_RELEASE_NUMBER;
select PO_INTERFACE_ERRORS_S.nextval
into l_TRANSACTION_ID from sys.dual;
PO_CHANGE_API1_S.insert_error(l_interface_type,
l_transaction_id,
'X_ACCEPTED_FLAG',
NULL,
'PO_CHNG_INVALID_ACCEPTED_FLAG',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(l_interface_type,
l_transaction_id,
'X_ACCEPTANCE_LOOKUP_CODE',
NULL,
'PO_ALL_CNL_PARAM_NULL',
NULL,
NULL);
select count(*)
into l_count
from PO_LOOKUP_CODES
where lookup_type = 'ACCEPTANCE TYPE'
and lookup_code = X_ACCEPTANCE_LOOKUP_CODE;
PO_CHANGE_API1_S.insert_error(l_interface_type,
l_transaction_id,
'X_ACCEPTANCE_LOOKUP_CODE',
NULL,
'PO_CHNG_INVALID_ACC_LK_CODE',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(l_interface_type,
l_transaction_id,
'PO_HEADER_ID',
'PO_HEADERS',
'PO_NOPOFOUND',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(l_interface_type,
l_transaction_id,
'PO_RELEASE_ID',
'PO_RELEASES',
'PO_CHNG_INVALID_RELEASE_NUM',
NULL,
NULL);
PO_ACCEPTANCES_INS_PVT.insert_row(
x_rowid => l_rowid,
x_acceptance_id => l_acceptance_id,
x_Last_Update_Date => l_Last_Update_Date,
x_Last_Updated_By => l_Last_Updated_By,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => g_sysdate,
p_created_by => g_user_id,
p_po_header_id => l_acc_po_header_id,
p_po_release_id => l_Po_Release_Id,
p_action => nvl(X_ACTION, 'ACCPO'),
p_action_date => nvl(X_ACTION_DATE, g_sysdate),
p_employee_id => l_employee_id,
p_revision_num => X_REVISION_NUMBER,
p_accepted_flag => X_ACCEPTED_FLAG,
p_acceptance_lookup_code => X_ACCEPTANCE_LOOKUP_CODE,
p_note => X_NOTE);
/*Bug # 5597797 The below code is modified to update the acceptance required flag to 'N' only if the a_accepted_flag is 'Y'.
This is in sync with the functionality of the Enter PO --> Tools --> Acceptances form.*/
IF (X_REVISION_NUMBER = l_current_revision) then
IF x_accepted_flag = 'Y' THEN
IF (l_po_release_id is null) then
update PO_HEADERS
/* Changed ACCEPTANCE_REQUIRED_FLAG value from null to N*/
set ACCEPTANCE_REQUIRED_FLAG = 'N', -- bug 4721255
ACCEPTANCE_DUE_DATE = null,
last_update_date = g_sysdate,
last_updated_by = g_user_id
where PO_HEADER_ID = l_po_header_id;
update PO_RELEASES
set ACCEPTANCE_REQUIRED_FLAG = 'N', -- bug 4721255
ACCEPTANCE_DUE_DATE = null,
last_update_date = g_sysdate,
last_updated_by = g_user_id
where PO_RELEASE_ID = l_po_release_id;
select nvl(closed_code, 'OPEN'),
nvl(authorization_status, 'INCOMPLETE'),
nvl(cancel_flag, 'N'),
revision_num,
nvl(X_employee_id, agent_id)
into l_closed_code,
l_status,
l_cancel_flag,
X_current_revision,
X_employee_id
from po_releases
where po_release_id = X_po_release_id;
select nvl(closed_code, 'OPEN'),
nvl(authorization_status, 'INCOMPLETE'),
nvl(cancel_flag, 'N'),
revision_num,
nvl(X_employee_id, agent_id)
into l_closed_code,
l_status,
l_cancel_flag,
X_current_revision,
X_employee_id
from po_headers
where po_header_id = X_po_header_id;
PO_CHANGE_API1_S.insert_error(X_interface_type,
X_transaction_id,
'CLOSED_CODE',
'PO_HEADERS',
'PO_ALL_DOC_CANNOT_BE_OPENED',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(X_interface_type,
X_transaction_id,
'AUTHORIZATION_STATUS',
'PO_HEADERS',
'PO_ALL_DOC_CANNOT_BE_OPENED',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(X_interface_type,
X_transaction_id,
'CANCEL_FLAG',
'PO_HEADERS',
'PO_ALL_DOC_CANNOT_BE_OPENED',
NULL,
NULL);
current_revision, then insert error. */
if (X_revision_num <> X_current_revision) then
l_result := 0;
PO_CHANGE_API1_S.insert_error(X_interface_type,
X_transaction_id,
'X_REVISION_NUMBER',
NULL,
'PO_CHNG_REVISION_NOT_MATCH',
NULL,
NULL);
select count(*)
into l_count
from per_people_f
where person_id = X_employee_id
and trunc(g_sysdate) between effective_start_date
and nvl(effective_end_date, g_sysdate+1);
PO_CHANGE_API1_S.insert_error( X_interface_type,
X_transaction_id,
'X_EMPLOYEE_ID',
NULL,
'PO_CHNG_NOT_VALID_EMPLOYEE',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
X_TRANSACTION_ID,
'PO_NUMBER',
NULL,
'PO_ALL_CNL_PARAM_NULL',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
X_TRANSACTION_ID,
'REVISION_NUM',
NULL,
'PO_ALL_CNL_PARAM_NULL',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
X_TRANSACTION_ID,
'VERSION',
NULL,
'PO_ALL_CNL_PARAM_NULL',
NULL,
NULL);
PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
X_TRANSACTION_ID,
'VERSION',
NULL,
'PO_CHNG_INVALID_VERSION',
NULL,
NULL);
PROCEDURE insert_error
( X_INTERFACE_TYPE IN VARCHAR2,
X_transaction_id IN NUMBER,
X_column_name IN VARCHAR2,
X_TABLE_NAME IN VARCHAR2,
X_MESSAGE_NAME IN VARCHAR2,
X_token_name IN VARCHAR2,
X_token_value IN VARCHAR2
) IS
pragma AUTONOMOUS_TRANSACTION;
insert into PO_INTERFACE_ERRORS (
INTERFACE_TYPE,
INTERFACE_TRANSACTION_ID,
COLUMN_NAME,
ERROR_MESSAGE,
PROCESSING_DATE,
ERROR_MESSAGE_NAME,
TABLE_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
values (
X_INTERFACE_TYPE,
X_transaction_id,
X_column_name,
l_error_message,
g_sysdate,
X_MESSAGE_NAME,
X_TABLE_NAME,
g_sysdate,
g_user_id,
g_sysdate,
g_user_id,
g_login_id,
NULL,
201,
NULL,
NULL );
END insert_error;
FUNCTION update_po
(
X_PO_NUMBER VARCHAR2,
X_RELEASE_NUMBER NUMBER,
X_REVISION_NUMBER NUMBER,
X_LINE_NUMBER NUMBER,
X_SHIPMENT_NUMBER NUMBER,
NEW_QUANTITY NUMBER,
NEW_PRICE NUMBER,
NEW_PROMISED_DATE DATE,
NEW_NEED_BY_DATE DATE,
LAUNCH_APPROVALS_FLAG VARCHAR2,
UPDATE_SOURCE VARCHAR2,
VERSION VARCHAR2,
X_OVERRIDE_DATE DATE := NULL,
X_API_ERRORS OUT NOCOPY PO_API_ERRORS_REC_TYPE,
p_BUYER_NAME VARCHAR2 default NULL, /* Bug:2986718 */
--
p_secondary_quantity NUMBER ,
p_preferred_grade VARCHAR2,
--
p_org_id IN NUMBER
) RETURN NUMBER IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_PO';
PO_DOCUMENT_UPDATE_GRP.update_document(
X_PO_NUMBER,
X_RELEASE_NUMBER,
X_REVISION_NUMBER,
X_LINE_NUMBER,
X_SHIPMENT_NUMBER,
NEW_QUANTITY,
NEW_PRICE,
NEW_PROMISED_DATE,
NEW_NEED_BY_DATE,
LAUNCH_APPROVALS_FLAG,
UPDATE_SOURCE,
X_OVERRIDE_DATE,
2.0, -- Version
l_result,
x_api_errors, --
p_BUYER_NAME, /* Bug:2986718 */
p_secondary_quantity, --
p_preferred_grade --
);
PO_DOCUMENT_UPDATE_PVT.add_message_list_errors (
p_api_errors => x_api_errors,
x_return_status => l_return_status
);
PO_DOCUMENT_UPDATE_PVT.add_message_list_errors (
p_api_errors => x_api_errors,
x_return_status => l_return_status
);
END update_po;