[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Modified the signature which takes l_last_update_date as IN parameter
and returns x_error as OUT parameter. l_last_update_date is used to
check the concurrency i.e., to check whether multiple supplier users
are acting on the same PO simutaneously. If the supplier try to modify
the PO which has already been modified by other user x_error returns false.
*/
PROCEDURE ACKNOWLEDGE_PO (
l_po_header_id IN VARCHAR2,
l_po_release_id IN VARCHAR2 default null,
l_po_buyer_id IN VARCHAR2,
l_po_accept_reject IN VARCHAR2,
l_po_acc_type_code IN VARCHAR2,
l_po_ack_comments IN VARCHAR2 ,
l_user_id IN VARCHAR2,
l_last_update_date IN DATE DEFAULT fnd_api.G_NULL_DATE,
x_error OUT NOCOPY VARCHAR2)
IS
l_acceptance_id NUMBER;
x_Last_Update_Date date ;
x_Last_Updated_By number;
l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
Added the cursors to get the latest last_update_date which is there in the database.
*/
l_last_upd_date po_headers_all.last_update_date%type;
SELECT last_update_date
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = p_po_header_id
FOR UPDATE of last_update_date NOWAIT;
SELECT last_update_date
FROM PO_RELEASES_ALL
WHERE PO_RELEASE_ID = p_po_release_id
FOR UPDATE of last_update_date NOWAIT;
select notification_id
from wf_notifications
where notification_id = p_nid
FOR UPDATE of notification_id NOWAIT;
select a.notification_id,poh.wf_item_type,poh.wf_item_key, a.message_name
INTO l_nid,l_po_item_type,l_po_item_key, l_message_name
from wf_notifications a,po_headers_all poh,
wf_item_activity_statuses wa
where poh.po_header_id=l_po_header_id
and poh.wf_item_key=wa.item_key
and poh.wf_item_type=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'OPEN'
and wa.activity_status = 'NOTIFIED';
select a.notification_id,por.wf_item_type,por.wf_item_key
INTO l_nid,l_po_item_type,l_po_item_key
from wf_notifications a,po_releases_all por,
wf_item_activity_statuses wa
where por.po_release_id=l_po_release_id
and por.wf_item_key=wa.item_key
and por.wf_item_type=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'OPEN'
and wa.activity_status = 'NOTIFIED';
select description into l_accp_type from POS_ACK_ACC_TYPE_LOV_V
where LOOKUP_CODE = l_po_acc_type_code; */
select pov.vendor_name
into x_vendor
from po_vendors pov,po_headers_all poh
where pov.vendor_id = poh.vendor_id
and poh.po_header_id=l_po_header_id;
select pov.vendor_name
into x_vendor
from po_releases_all por,po_headers_all poh,po_vendors pov
where por.po_release_id = l_po_release_id
and por.po_header_id = poh.po_header_id
and poh.vendor_id = pov.vendor_id;
select fnd_message.get_string('POS','POS_PO_ACCEPTED')
into l_accp_res from dual;
select fnd_message.get_string('POS','POS_PO_REJECTED')
into l_accp_res from dual;
Locking the Notification id to allow only one user to update when more than
one supplier user tries to acknowledge the PO.*/
IF (l_last_update_date <> fnd_api.G_NULL_DATE) THEN
BEGIN
OPEN NID_CSR(l_nid);
l_last_update_date contains the last update date which is currently seen by supplier user.
l_last_upd_date contains the last update date which is currently there is database.
If there is any mismatch in the above dates that means the PO data whatever supplier user
seeing currently is not the latest.
*/
-- Lock the PO Header Row for update of Last Update Date
IF (l_last_update_date <> fnd_api.G_NULL_DATE) THEN
if (l_po_release_id is not null ) then
BEGIN
OPEN REL_CSR(l_po_release_id);
l_last_upd_date := relRec.last_update_date;
l_last_upd_date := poRec.last_update_date;
if (l_last_update_date <> l_last_upd_date) THEN
x_error := 'true';
select po_acceptances_s.nextval into l_acceptance_id from dual;
select revision_num ,nvl(pending_signature_flag,'N'),type_lookup_code
into l_revision_num,l_pending_signature_flag,l_doc_subtype
from po_headers_all
where po_header_id = to_number(l_po_header_id);
select revision_num into l_revision_num
from po_releases_all
where po_release_id = to_number(l_po_release_id);
select pos_party_management_pkg.get_job_title_for_user( l_user_id)
into l_role
from dual;
PO_ACCEPTANCES_INS_PVT.insert_row(
x_rowid => x_row_id,
x_acceptance_id => l_acceptance_id,
x_Last_Update_Date => x_Last_Update_Date,
x_Last_Updated_By => x_Last_Updated_By,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => x_Creation_Date,
p_created_by => x_Created_by,
p_po_header_id => x_po_header_id,
p_po_release_id => l_po_release_id,
p_action => fnd_message.get_string('ICX','ICX_POS_ACK_WEB'),
p_action_date => x_Action_Date,
p_employee_id => null,
p_revision_num => l_revision_num,
p_accepted_flag => l_po_accept_reject,
p_acceptance_lookup_code=> l_po_acc_type_code,
p_note => l_po_ack_comments,
p_accepting_party => l_accepting_party,
p_signature_flag => l_signature_flag,
p_role => l_role);
/* insert into po_acceptances (
acceptance_id,
last_update_Date,
last_updated_by,
last_update_login,
creation_date,
created_by,
po_header_id,
po_release_id,
action,
action_date,
--employee_id,
revision_num,
accepted_flag,
acceptance_lookup_code,
note
)
values (
l_acceptance_id,
sysdate,
l_user_id,
l_user_id,
sysdate,
l_user_id,
decode(l_po_release_id, null, l_po_header_id, null),
l_po_release_id,
fnd_message.get_string('ICX','ICX_POS_ACK_WEB'),
sysdate,
--l_po_buyer_id,
l_revision_num,
l_po_accept_reject,
l_po_acc_type_code,
l_po_ack_comments
);
PO_SIGNATURE_PVT.Update_Po_Details(
p_po_header_id => l_doc,
p_status => 'REJECTED',
p_action_code => 'SUPPLIER REJECTED',
p_object_type_code => l_doc_type,
p_object_subtype_code => l_doc_subtype,
p_employee_id => null,
p_revision_num => l_revision_num);
update po_releases_all
set acceptance_required_flag = 'N',
LAST_UPDATE_DATE = SYSDATE,
acceptance_due_date=''
where po_release_id = l_po_release_id;
update po_headers_all
set acceptance_required_flag = 'N',
LAST_UPDATE_DATE = SYSDATE,
acceptance_due_date=''
where po_header_id = l_po_header_id;
select po_wf_itemkey_s.nextval into l_seq_val from dual;
select po_wf_itemkey_s.nextval into l_seq_val
from dual;
update po_headers_all
set authorization_status = 'IN PROCESS'
where po_header_id = to_number(pos_po_header_id);
update po_releases_all
set authorization_status = 'IN PROCESS'
where po_release_id = to_number(pos_po_release_id);
UPDATE PO_LINE_LOCATIONS_ALL PLL
SET pll.promised_date = pll.need_by_date,
pll.last_update_date = sysdate,
pll.last_updated_by = p_user_id
WHERE pll.po_header_id= p_po_header_id
AND pll.po_release_id is null
AND pll.line_location_id= p_line_location_id
AND pll.promised_date is null;
UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
SET plla.promised_date = plla.need_by_date,
plla.last_update_date = sysdate,
plla.last_updated_by = p_user_id
WHERE plla.po_header_id = p_po_header_id
AND plla.po_release_id is null
AND plla.line_location_id = p_line_location_id
AND plla.promised_date is null
AND plla.revision_num = (SELECT max(plla2.revision_num)
FROM po_line_locations_archive_all plla2
WHERE plla2.line_location_id = plla.line_location_id
AND plla.revision_num <= p_revision_num);
UPDATE PO_LINE_LOCATIONS_ALL PLL
SET pll.promised_date = pll.need_by_date,
pll.last_update_date = sysdate,
pll.last_updated_by = p_user_id
WHERE pll.po_header_id = p_po_header_id
AND pll.promised_date is null
/* AND 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.closed_code, 'OPEN') = 'OPEN' and
nvl(pll.consigned_flag, 'N') = 'N') OR
(pll.closed_code = 'CLOSED FOR INVOICE' and pll.consigned_flag = 'Y'));
UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
SET plla.promised_date = plla.need_by_date,
plla.last_update_date = sysdate,
plla.last_updated_by = p_user_id
WHERE plla.po_header_id = p_po_header_id
AND plla.promised_date is null
AND plla.revision_num = (SELECT max(plla2.revision_num)
FROM po_line_locations_archive_all plla2
WHERE plla2.line_location_id = plla.line_location_id
AND plla.revision_num <= p_revision_num)
/* AND 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 = plla.line_location_id ) */
AND nvl(plla.cancel_flag, 'N') = 'N'
AND ((nvl(plla.closed_code, 'OPEN') = 'OPEN' and
nvl(plla.consigned_flag, 'N') = 'N') OR
(plla.closed_code = 'CLOSED FOR INVOICE' and plla.consigned_flag = 'Y'));
UPDATE po_line_locations_all pll
SET pll.promised_date =need_by_date,
pll.last_update_date = sysdate,
pll.last_updated_by = p_user_id
WHERE pll.po_header_id= p_po_header_id
AND pll.line_location_id= p_line_location_id
AND pll.po_release_id = p_po_release_id
AND pll. promised_date is null;
UPDATE po_line_locations_archive_all plla
SET plla.promised_date = plla.need_by_date,
plla.last_update_date = sysdate,
plla.last_updated_by = p_user_id
WHERE plla.po_header_id= p_po_header_id
AND plla.line_location_id= p_line_location_id
AND plla.po_release_id = p_po_release_id
AND plla. promised_date is null
AND plla.revision_num = (SELECT max(plla2.revision_num)
FROM po_line_locations_archive_all plla2
WHERE plla2.line_location_id = plla.line_location_id
AND plla.revision_num <= p_revision_num);
UPDATE PO_LINE_LOCATIONS_ALL PLL
SET pll.promised_date =need_by_date,
pll.last_update_date = sysdate,
pll.last_updated_by = p_user_id
WHERE pll.po_header_id = p_po_header_id
AND pll.po_release_id = p_po_release_id
AND pll.promised_date is null
/* AND exists (
SELECT 1
FROM PO_ACCEPTANCES PA
WHERE pa.po_release_id = p_po_release_id
AND 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.closed_code, 'OPEN') = 'OPEN' AND (nvl(pll.consigned_flag, 'N') = 'N')) OR (pll.closed_code = 'CLOSED FOR INVOICE' AND pll.consigned_flag = 'Y'));
UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
SET plla.promised_date =need_by_date,
plla.last_update_date = sysdate,
plla.last_updated_by = p_user_id
WHERE plla.po_header_id = p_po_header_id
AND plla.po_release_id = p_po_release_id
AND plla.promised_date is null
AND plla.revision_num = (SELECT max(plla2.revision_num)
FROM po_line_locations_archive_all plla2
WHERE plla2.line_location_id = plla.line_location_id
AND plla.revision_num <= p_revision_num)
/* AND exists (
SELECT 1
FROM PO_ACCEPTANCES PA
WHERE pa.po_release_id = p_po_release_id
AND pa.po_header_id = p_po_header_id
AND pa.revision_num = p_revision_num
AND pa.po_line_location_id = PLLA.line_location_id ) */
AND nvl(plla.cancel_flag, 'N') = 'N'
AND ((nvl(plla.closed_code, 'OPEN') = 'OPEN' AND (nvl(plla.consigned_flag, 'N') = 'N')) OR (plla.closed_code = 'CLOSED FOR INVOICE' AND plla.consigned_flag = 'Y'));