The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
From PO_LINE_LOCATIONS_ALL PLL
Where pll.po_release_id = p_po_release_id
And not exists (
select 1
From PO_ACCEPTANCES PA
Where PA.po_release_id = p_po_release_id
And pa.revision_num = p_revision_num
And pa.po_line_location_id = PLL.line_location_id )
And nvl(pll.cancel_flag, 'N') = 'N'
And nvl(pll.payment_type,'NULL') not in ('ADVANCE','DELIVERY')
And ((nvl(pll.closed_code, 'OPEN') = 'OPEN' and
nvl(pll.consigned_flag, 'N') = 'N') OR
(pll.closed_code = 'CLOSED FOR INVOICE' and
pll.consigned_flag = 'Y'));
select 'Y'
From PO_LINE_LOCATIONS_ALL PLL
Where pll.po_header_id = p_po_header_id
And pll.po_release_id is null
And not exists (
select 1
From PO_ACCEPTANCES PA
Where PA.po_header_id = p_po_header_id
And pa.revision_num = p_revision_num
And pa.po_line_location_id = PLL.line_location_id )
And nvl(pll.cancel_flag, 'N') = 'N'
And nvl(pll.payment_type,'NULL') not in ('ADVANCE','DELIVERY')
And ((nvl(pll.closed_code, 'OPEN') = 'OPEN' and
nvl(pll.consigned_flag, 'N') = 'N') OR
(pll.closed_code = 'CLOSED FOR INVOICE' and
pll.consigned_flag = 'Y'));
select 'Y'
from PO_ACCEPTANCES
where po_header_id = p_po_header_id
and po_release_id is null
and revision_num = p_revision_num
and po_line_location_id is not null;
select 'Y'
from PO_ACCEPTANCES
where po_header_id is null
and po_release_id = p_po_release_id
and revision_num = p_revision_num
and po_line_location_id is not null;
select 'Y'
from PO_ACCEPTANCES
where po_header_id = p_po_header_id
and revision_num = p_revision_num
and po_release_id is null
and po_line_location_id is null
and accepting_party = 'S'
and accepted_flag = 'Y'
and signature_flag = 'Y';
select 'Y'
from PO_ACCEPTANCES
where po_header_id = p_po_header_id
and revision_num = p_revision_num
and po_release_id is null
and po_line_location_id is null
and accepted_flag = 'N'
and signature_flag = 'Y';
select nvl(acceptance_required_flag, 'N'),
nvl(change_requested_by, ' ')
into l_acceptance_required_flag,
l_change_requested_by
from PO_RELEASES_ALL
where po_release_id = p_po_release_id;
select revision_num
into l_arch_revision_num
from po_releases_archive_all
where po_release_id = p_po_release_id
and latest_external_flag = 'Y';
select nvl(acceptance_required_flag, 'N'),
nvl(change_requested_by, ' '),
nvl(pending_signature_flag,'N')
into l_acceptance_required_flag,
l_change_requested_by,
l_sign_flag
from PO_HEADERS_ALL
where po_header_id = p_po_header_id;
select 'Y'
into l_shipment_exist_flag
from sys.dual
where exists (
select 1
from PO_LINE_LOCATIONS_ALL
where po_header_id = p_po_header_id
and po_release_id is NULL );
select revision_num
into l_arch_revision_num
from po_headers_archive_all
where po_header_id = p_po_header_id
and latest_external_flag = 'Y';
select accepted_flag
into l_accepted_flag
from PO_ACCEPTANCES
where acceptance_id = (
select max(acceptance_id)
from PO_ACCEPTANCES
where po_release_id = p_po_release_id
and revision_num = p_revision_num
and po_line_location_id is null );
select accepted_flag
into l_accepted_flag
from PO_ACCEPTANCES
where acceptance_id = (
select max(acceptance_id)
from PO_ACCEPTANCES
where po_header_id = p_po_header_id
and po_release_id is null
and revision_num = p_revision_num
and po_line_location_id is null );
* Effects: Insert header level acknowledgement result into PO_ACCEPTANCES
* table, also update ACCEPTANCE_REQUIRED_FLAG at PO header level.
*/
PROCEDURE Acknowledge_Po (
p_po_header_id IN NUMBER,
p_po_release_id IN NUMBER,
p_revision_num IN NUMBER,
p_accepted_flag IN VARCHAR2,
p_comment IN VARCHAR2 default null,
p_buyer_id IN NUMBER,
p_user_id IN NUMBER )
IS
-- Bug 2850566
l_rowid ROWID;
l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
l_user_id PO_ACCEPTANCES.last_updated_by%TYPE;
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_user_id,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => sysdate,
p_created_by => p_user_id,
p_po_header_id => l_acc_po_header_id,
p_po_release_id => p_po_release_id,
p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
p_action_date => sysdate,
p_revision_num => p_revision_num,
p_accepted_flag => p_accepted_flag,
p_note => p_comment,
p_accepting_party => 'S');
update PO_RELEASES_ALL
set acceptance_required_flag = 'N',
acceptance_due_date = ''
where po_release_id = p_po_release_id;
update PO_HEADERS_ALL
set acceptance_required_flag = 'N',
acceptance_due_date = ''
where po_header_id = p_po_header_id;
select 'Y'
from PO_CHANGE_REQUESTS
where po_release_id = p_po_release_id
and request_status = 'PENDING'
and request_level = 'HEADER'
and action_type = 'CANCELLATION'
and initiator = 'SUPPLIER';
select 'Y'
from PO_CHANGE_REQUESTS
where document_header_id = p_po_header_id
and request_status = 'PENDING'
and request_level = 'HEADER'
and action_type = 'CANCELLATION'
and initiator = 'SUPPLIER';
select nvl(cancel_flag, 'N'),
nvl(frozen_flag, 'N'),
nvl(hold_flag, 'N'),
nvl(closed_code, 'OPEN'),
nvl(acceptance_required_flag, 'N'),
nvl(authorization_status, 'INCOMPLETE'),
revision_num,
nvl(change_requested_by, ' ')
into l_cancel_flag,
l_frozen_flag,
l_on_hold_flag,
l_closed_code,
l_accp_reqd_flag,
l_auth_status,
l_revision_num,
l_changed_by
from PO_RELEASES_all
where po_release_id = p_po_release_id;
select nvl(cancel_flag, 'N'),
nvl(frozen_flag, 'N'),
nvl(user_hold_flag, 'N'),
nvl(closed_code, 'OPEN'),
nvl(acceptance_required_flag, 'N'),
nvl(authorization_status, 'INCOMPLETE'),
revision_num,
nvl(change_requested_by, ' '),
nvl(global_agreement_flag, 'N'),
nvl(supplier_auth_enabled_flag, 'N'),
nvl(lock_owner_role, ' ')
into l_cancel_flag,
l_frozen_flag,
l_on_hold_flag,
l_closed_code,
l_accp_reqd_flag,
l_auth_status,
l_revision_num,
l_changed_by,
l_ga_flag,
l_authoring_flag,
l_lock_owner_role
from PO_HEADERS_all
where po_header_id = p_po_header_id;
SELECT POR.revision_num, authorization_status
INTO l_revision_num, l_authorization_status
FROM PO_RELEASES_ALL POR
WHERE POR.po_release_id = p_po_release_id;
SELECT POH.revision_num, authorization_status
INTO l_revision_num, l_authorization_status
FROM PO_HEADERS_ALL POH
WHERE POH.po_header_id = p_po_header_id;
select PCR.action_type
into l_action_type
from PO_CHANGE_REQUESTS PCR,
PO_RELEASES_ALL POR
where pcr.document_line_location_id = P_line_location_id
and pcr.po_release_id = p_po_release_id
and por.po_release_id = p_po_release_id
and por.change_requested_by = 'SUPPLIER'
and request_level = 'SHIPMENT'
and ((request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
(request_status = 'REJECTED' and change_request_group_id = (
select MAX(change_request_group_id)
from po_change_requests pcr2
where pcr2.po_release_id = p_po_release_id
and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP'))) )
and rownum = 1;
select PCR.action_type
into l_action_type
from PO_CHANGE_REQUESTS PCR,
PO_HEADERS_ALL POH
where pcr.document_line_location_id = P_line_location_id
and pcr.document_header_id = p_po_header_id
and poh.po_header_id = p_po_header_id
and poh.change_requested_by = 'SUPPLIER'
and request_level = 'SHIPMENT'
and ((request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
(request_status = 'REJECTED' and change_request_group_id = (
select MAX(change_request_group_id)
from po_change_requests pcr2
where pcr2.document_header_id = p_po_header_id
and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP'))) )
and rownum = 1;
select accepted_flag
into l_ship_accepted_flag
from PO_ACCEPTANCES
where po_line_location_id = P_line_location_id
and revision_num = l_revision_num
and acceptance_id = (select MAX(acceptance_id)
from PO_ACCEPTANCES PA2
where PA2.po_line_location_id = P_line_location_id
and PA2.revision_num = l_revision_num );
select nvl(acceptance_required_flag, 'N')
into l_acceptance_required_flag
from PO_RELEASES_ALL
where po_release_id = p_po_release_id
and revision_num = l_revision_num;
select accepted_flag
into l_header_accepted_flag
from PO_ACCEPTANCES
where acceptance_id = (
select max(acceptance_id)
from PO_ACCEPTANCES
where po_release_id = p_po_release_id
and revision_num = l_revision_num
and po_line_location_id is null );
select nvl(acceptance_required_flag, 'N')
into l_acceptance_required_flag
from PO_HEADERS_ALL
where po_header_id = p_po_header_id;
select accepted_flag
into l_header_accepted_flag
from PO_ACCEPTANCES
where acceptance_id = (
select max(acceptance_id)
from PO_ACCEPTANCES
where po_header_id = p_po_header_id
and po_release_id is null
and revision_num = l_revision_num
and po_line_location_id is null );
* Effects: Insert shipment level acknowledgement result into PO_ACCEPTANCES
* table. Also checks if all shipments have been acknowledged after
* insertion, if yes then post the header level acknowledge result.
*/
PROCEDURE Acknowledge_Shipment (
p_api_version IN NUMBER,
p_Init_Msg_List IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_line_location_id IN NUMBER,
p_po_header_id IN NUMBER,
p_po_release_id IN NUMBER,
p_revision_num IN NUMBER,
p_accepted_flag IN VARCHAR2,
p_comment IN VARCHAR2 default null,
p_buyer_id IN NUMBER,
p_user_id IN NUMBER )
IS
-- Bug 2850566
l_rowid ROWID;
l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
l_user_id PO_ACCEPTANCES.last_updated_by%TYPE;
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_user_id,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => sysdate,
p_created_by => p_user_id,
p_po_header_id => l_acc_po_header_id,
p_po_release_id => p_po_release_id,
p_po_line_location_id => p_line_location_id,
p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
p_action_date => sysdate,
p_employee_id => to_number(null),
p_revision_num => p_revision_num,
p_accepted_flag => p_accepted_flag,
p_note => p_comment);
l_api_name || '.after inserting shipment acknowledgement',
'Line_location_id: '|| NVL(TO_CHAR(p_line_location_id),'null'));
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;
l_user_id PO_ACCEPTANCES.last_updated_by%TYPE;
Select
PA.created_by,
PA.po_line_location_id,
PA.action,
PA.action_date,
PA.accepted_flag,
PA.Last_Updated_By
from PO_ACCEPTANCES PA
Where PA.po_release_id = p_po_release_id
And PA.revision_num = p_revision_num - 1
And PA.po_line_location_id is not null
AND NOT EXISTS ( select 1
From PO_ACCEPTANCES PA2
Where PA2.po_release_id = p_po_release_id
And PA2.revision_num = p_revision_num
And PA2.po_line_location_id = PA.po_line_location_id);
Select
document_line_location_id
from PO_CHANGE_REQUESTS PCR
Where PCR.po_release_id = p_po_release_id
And PCR.document_revision_num = p_revision_num - 1
And PCR.document_line_location_id is not null
And PCR.request_status = 'ACCEPTED'
and PCR.initiator = 'SUPPLIER'
And PCR.action_type = 'MODIFICATION'
and PCR.REQUEST_LEVEL = 'SHIPMENT'
AND NOT EXISTS ( select 1
From PO_ACCEPTANCES PA2
Where PA2.po_release_id = p_po_release_id
And PA2.revision_num = p_revision_num
And PA2.po_line_location_id = PCR.document_line_location_id);
Select
PA.created_by,
PA.po_line_location_id,
PA.action,
PA.action_date,
PA.accepted_flag,
PA.Last_Updated_By
from PO_ACCEPTANCES PA
Where PA.po_header_id = p_po_header_id
and PA.po_release_id is null
And PA.revision_num = p_revision_num - 1
And PA.po_line_location_id is not null
AND NOT EXISTS ( select 1
From PO_ACCEPTANCES PA2
Where PA2.po_release_id = p_po_release_id
And PA2.revision_num = p_revision_num
And PA2.po_line_location_id = PA.po_line_location_id);
Select
document_line_location_id
from PO_CHANGE_REQUESTS PCR
Where PCR.document_header_id = p_po_header_id
And PCR.po_release_id is null
And PCR.document_revision_num = p_revision_num - 1
And PCR.document_line_location_id is not null
And PCR.request_status = 'ACCEPTED'
and PCR.initiator = 'SUPPLIER'
And PCR.action_type = 'MODIFICATION'
and PCR.REQUEST_LEVEL = 'SHIPMENT'
AND NOT EXISTS ( select 1
From PO_ACCEPTANCES PA2
Where PA2.po_header_id = p_po_header_id
And PA2.po_release_id is null
And PA2.revision_num = p_revision_num
And PA2.po_line_location_id = PCR.document_line_location_id);
select agent_id
into l_buyer_id
from po_releases_all
where po_release_id = p_po_release_id;
l_user_id := c1_rec.Last_Updated_By;
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_user_id,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => sysdate,
p_created_by => c1_rec.created_by,
p_po_header_id => NULL,
p_po_release_id => p_po_release_id,
p_po_line_location_id => c1_rec.po_line_location_id,
p_action => c1_rec.action,
p_action_date => c1_rec.action_date,
p_revision_num => p_revision_num,
p_accepted_flag => c1_rec.accepted_flag);
l_Last_Updated_By := 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 => sysdate,
p_created_by => fnd_global.user_id,
p_po_header_id => NULL,
p_po_release_id => p_po_release_id,
p_po_line_location_id => c2_rec.document_line_location_id,
p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
p_action_date => sysdate,
p_revision_num => p_revision_num,
p_accepted_flag => 'Y');
select agent_id
into l_buyer_id
from po_headers_all
where po_header_id = p_po_header_id;
l_user_id := c3_rec.Last_Updated_By;
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_user_id,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => sysdate,
p_created_by => c3_rec.created_by,
p_po_header_id => p_po_header_id,
p_po_release_id => NULL,
p_po_line_location_id => c3_rec.po_line_location_id,
p_action => c3_rec.action,
p_action_date => c3_rec.action_date,
p_revision_num => p_revision_num,
p_accepted_flag => c3_rec.accepted_flag);
l_Last_Updated_By := 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 => sysdate,
p_created_by => fnd_global.user_id,
p_po_header_id => p_po_header_id,
p_po_release_id => NULL,
p_po_line_location_id => c4_rec.document_line_location_id,
p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
p_action_date => sysdate,
p_revision_num => p_revision_num,
p_accepted_flag => 'Y');
Select 'A'
into l_header_accepted_flag
From sys.dual
Where exists (
select 1
From PO_ACCEPTANCES
Where po_release_id = p_po_release_id
and revision_num = p_revision_num
and po_line_location_id is not null
and accepted_flag <> 'Y' );
Select 'A'
into l_header_accepted_flag
From sys.dual
Where exists (
select 'Y'
From PO_ACCEPTANCES
Where po_header_id = p_po_header_id
and po_release_id is null
and revision_num = p_revision_num
and po_line_location_id is not null
and accepted_flag <> 'Y' );
select 'Y'
From PO_LINE_LOCATIONS_ALL PLL
Where pll.po_release_id = p_po_release_id
And not exists (
select 1
From PO_ACCEPTANCES PA
Where PA.po_release_id = p_po_release_id
And pa.revision_num = p_revision_num
And pa.po_line_location_id = PLL.line_location_id )
And not exists (
select 1
From PO_CHANGE_REQUESTS pcr, po_releases_all por
WHERE por.po_release_id = p_po_release_id
and por.change_requested_by = 'SUPPLIER'
and pcr.po_release_id = p_po_release_id
AND PCR.document_revision_num = p_revision_num
And ((pcr.document_line_location_id = PLL.line_location_id) OR
(pcr.parent_line_location_id = PLL.line_location_id))
and pcr.initiator = 'SUPPLIER'
And ((pcr.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
(pcr.request_status = 'REJECTED'
and pcr.CHANGE_REQUEST_GROUP_ID = (
select MAX(pcr2.CHANGE_REQUEST_GROUP_ID)
from po_change_requests pcr2
where pcr2.po_release_id = p_po_release_id
and pcr2.document_revision_num = p_revision_num
and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP')) ) ) )
And nvl(pll.cancel_flag, 'N') = 'N'
And nvl(pll.payment_type,'NULL') NOT IN ('ADVANCE','DELIVERY')
And nvl(pll.closed_code, 'OPEN') not in ('CLOSED', 'FINALLY CLOSED');
select 'Y'
From PO_LINE_LOCATIONS_ALL PLL
Where pll.po_header_id = p_po_header_id
And pll.po_release_id is null
And not exists (
select 1
From PO_ACCEPTANCES PA
Where PA.po_header_id = p_po_header_id
And pa.revision_num = p_revision_num
And pa.po_line_location_id = PLL.line_location_id )
And not exists (
select 1
From PO_CHANGE_REQUESTS pcr, po_headers_all poh
WHERE poh.po_header_id = p_po_header_id
and poh.change_requested_by = 'SUPPLIER'
and pcr.document_header_id = p_po_header_id
AND PCR.document_revision_num = p_revision_num
And ((pcr.document_line_location_id = PLL.line_location_id) OR
(pcr.parent_line_location_id = PLL.line_location_id))
and pcr.initiator = 'SUPPLIER'
And ((pcr.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
(pcr.request_status = 'REJECTED'
and pcr.CHANGE_REQUEST_GROUP_ID = (
select MAX(pcr2.CHANGE_REQUEST_GROUP_ID)
from po_change_requests pcr2
where pcr2.document_header_id = p_po_header_id
and pcr2.document_revision_num = p_revision_num
and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP'))) ))
And nvl(pll.cancel_flag, 'N') = 'N'
And nvl(pll.payment_type,'NULL') NOT IN ('ADVANCE','DELIVERY')
And nvl(pll.closed_code, 'OPEN') not in ('CLOSED', 'FINALLY CLOSED');
SELECT accepted_flag
FROM po_acceptances
WHERE po_release_id = p_po_release_id
AND revision_num = rev_num
AND po_line_location_id is not null;
SELECT accepted_flag
FROM po_acceptances
WHERE po_header_id = p_po_header_id
AND revision_num = rev_num
AND po_line_location_id is not null;
SELECT agent_id,
revision_num,
acceptance_required_flag,
change_requested_by
INTO l_buyer_id,
l_revision_num,
l_accp_required_flag,
l_change_requested_by
FROM PO_RELEASES_ALL
WHERE po_release_id = p_po_release_id;
SELECT agent_id,
revision_num,
acceptance_required_flag,
change_requested_by
INTO l_buyer_id,
l_revision_num,
l_accp_required_flag,
l_change_requested_by
FROM PO_HEADERS_ALL
WHERE po_header_id = p_po_header_id;
SELECT 'A'
INTO l_header_accepted_flag
FROM sys.dual
WHERE exists (
SELECT 1
FROM PO_ACCEPTANCES
WHERE po_release_id = p_po_release_id
AND revision_num = l_revision_num
AND po_line_location_id is not null
AND accepted_flag <> l_ship_accepted_flag );
SELECT 'A'
INTO l_header_accepted_flag
FROM sys.dual
WHERE exists (
SELECT 'Y'
FROM PO_ACCEPTANCES
WHERE po_header_id = p_po_header_id
AND po_release_id is null
AND revision_num = l_revision_num
AND po_line_location_id is not null
AND accepted_flag <> l_ship_accepted_flag );