[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* corresponding PO part, and also update the status to 'ACCEPTED'
* of the req change
*
* 'ACCEPTANCE is to process the buyer's acceptance of
* change request. It will call movechangetopo to move the accepted
* change request to PO, and then update the req with the
* new value. also update the corresponding req change status.
*
* the process will commit when it exits.
*
* Returns:
************************************************************************/
procedure ProcessBuyerAction(p_change_request_group_id in number,
p_action in varchar2, p_launch_approvals_flag IN VARCHAR2 default 'N', p_supplier_change IN varchar2 default 'N');
* Private Procedure: InsertActionHist
* Effects: insert into action history table.
*
* It is called when the change request is submitted (by requester
* or buyer) and when buyer responds to the change request.
*
* the action can be 'SUBMIT CHANGE', 'ACCEPTED', 'REJECTED'
* or 'RESPOND'
*
* the process will commit when it exits.
*
* Returns:
************************************************************************/
PROCEDURE InsertActionHist(itemtype varchar2,
itemkey varchar2,
p_doc_id number,
p_doc_type varchar2,
p_doc_subtype varchar2,
p_employee_id number,
p_action varchar2,
p_note varchar2,
p_path_id number);
* call the procedure AutoApprove to update the status of the
* change request and the corresponding requisition.
*
* the process will commit when it exits.
*
* Returns:
************************************************************************/
PROCEDURE CheckPOAutoApproval(p_change_request_group_id in number);
* update the corresponding req change record and the requisition.
*
* called by CheckPOAutoApproval, and will be committed in that
* procedure.
*
* Returns:
************************************************************************/
procedure AutoApprove(p_change_request_id in number);
* Private Procedure: UpdateReqDistribution
* Effects: update the quantity of a requisition distribution.
*
* Returns:
************************************************************************/
procedure UpdateReqDistribution(p_req_line_id in number,
p_req_distribution_id in number,
p_new_quantity in number,
p_old_quantity in number,
p_new_dist_amount number,
p_old_dist_amount number,
p_new_currency_dist_amount number,
p_old_currency_dist_amount number);
* update the corresponding req change record and the requisition.
* call validate api to check if the request is valid or not.
* if yes, save the request to database
*
* Returns:
************************************************************************/
procedure ValidateAndSaveRequest(
p_po_header_id in number,
p_po_release_id in number,
p_revision_num in number,
p_po_change_requests in out nocopy pos_chg_rec_tbl);
* Private Procedure: UpdateReqLine
* Effects: update the need by date and/or price of a requisition line
*
* Returns:
************************************************************************/
procedure UpdateReqLine(p_req_line_id in number,
p_new_need_by_date in DATE,
p_new_unit_price in number,
p_new_currency_unit_price in number,
p_new_start_date date,
p_new_end_date date);
* Private Procedure: UpdatePODocHeaderTables
* Effects: This procedure gets invoked from PO_ReqChangeRequestWF_PVT.New_PO_Change_Exists
*
* When there is a change request,updating of the table po_header_all/po_release_all
* based on the status of the change requests can also be done by an autonomous block
* which there by can create a deadlock.Hence included the updating of tables in
* this separate autonomous transaction procedure to avoid any deadlock error.
********************************************************************************************/
procedure UpdatePODocHeaderTables(p_document_type varchar2, p_document_id number);
SELECT ooh.ORG_ID
INTO l_org_id
from po_requisition_lines_all prl,
po_requisition_headers_all prh,
oe_order_headers_all ooh,
po_system_parameters_all psp
WHERE prl.requisition_header_id = nvl(p_req_hdr_id,prh.requisition_header_id)
AND prl.requisition_line_id = nvl(p_req_line_id,prl.requisition_line_id)
AND prh.requisition_header_id = nvl(p_req_hdr_id,prh.requisition_header_id)
AND prh.requisition_header_id = ooh.source_document_id
AND prh.segment1 = ooh.orig_sys_document_ref
AND psp.org_id = prh.org_id
AND psp.order_source_id = ooh.order_source_id
AND nvl(p_req_hdr_id,p_req_line_id) IS NOT NULL
and rownum =1;
SELECT prh.org_id
INTO l_org_id
from po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prl.requisition_header_id = nvl(p_req_hdr_id,prh.requisition_header_id)
AND prh.requisition_header_id = nvl(p_req_hdr_id,prh.requisition_header_id)
AND prl.requisition_line_id = nvl(p_req_line_id,prl.requisition_line_id)
AND nvl(p_req_hdr_id,p_req_line_id) IS NOT NULL
and rownum =1;
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_approver_user_name;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
* update the corresponding req change record and the requisition.
* call validate api to check if the request is valid or not.
* if yes, save the request to database
*
* Returns:
************************************************************************/
procedure ValidateAndSaveRequest(
p_po_header_id in number,
p_po_release_id in number,
p_revision_num in number,
p_po_change_requests in out nocopy pos_chg_rec_tbl) is
l_doc_check_rec_type Doc_Check_Return_Type;
select nvl(authorization_status, 'IN PROCESS')
into l_doc_status
from po_headers_all
where po_header_id=p_po_header_id;
select nvl(authorization_status, 'IN PROCESS')
into l_doc_status
from po_releases_all
where po_release_id=p_po_release_id;
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_reason=substr(fnd_message.get_string('PO',
'PO_RCO_VALIDATION_ERROR')||':'||
l_error_message1, 1, 2000),
response_date=sysdate,
validation_error=l_error_message
where change_request_id=
p_po_change_requests(i).parent_change_request_id;
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
where change_request_id=
p_po_change_requests(i).parent_change_request_id;
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_SAVING')
where change_request_id=
p_po_change_requests(i).parent_change_request_id;
* Private Procedure: UpdateReqDistribution
* Effects: update the quantity of a requisition distribution.
*
* Returns:
************************************************************************/
procedure UpdateReqDistribution(
p_req_line_id in number,
p_req_distribution_id in number,
p_new_quantity in number,
p_old_quantity in number,
p_new_dist_amount number,
p_old_dist_amount number,
p_new_currency_dist_amount number,
p_old_currency_dist_amount number) is
l_quantity number;
raise g_update_data_exp;
update po_req_distributions_all
set req_line_quantity=p_new_quantity,
req_line_amount = p_new_dist_amount,
req_line_currency_amount = p_new_currency_dist_amount,
recoverable_tax=l_recoverable_tax,
nonrecoverable_tax=l_nonrecoverable_tax
where distribution_id=p_req_distribution_id;
update po_requisition_lines_all
set
quantity = quantity + decode(p_new_quantity,null,0,(p_new_quantity-p_old_quantity)),
amount = amount + decode(p_new_dist_amount,null,0,(p_new_dist_amount - p_old_dist_amount)),
currency_amount = currency_amount + decode(p_new_currency_dist_amount,null,0,
(p_new_currency_dist_amount-p_old_currency_dist_amount))
where requisition_line_id=p_req_line_id;
'UpdateReqDistribution',x_progress||sqlerrm);
end UpdateReqDistribution;
* Private Procedure: UpdateReqLine
* Effects: update the need by date and/or price of a requisition line
*
* Returns:
************************************************************************/
procedure UpdateReqLine(
p_req_line_id in number,
p_new_need_by_date in DATE,
p_new_unit_price in number,
p_new_currency_unit_price in number,
p_new_start_date date,
p_new_end_date date) is
l_quantity number;
select distribution_id
from po_req_distributions_all
where requisition_line_id=p_req_line_id;
raise g_update_data_exp;
update po_req_distributions_all
set recoverable_tax=l_recoverable_tax,
nonrecoverable_tax=l_nonrecoverable_tax
where distribution_id=l_distributions_id;
update po_requisition_lines_all
set need_by_date=nvl(p_new_need_by_date, need_by_date),
unit_price=nvl(p_new_unit_price, unit_price),
currency_unit_price=nvl(p_new_currency_unit_price, currency_unit_price), assignment_start_date = nvl(p_new_start_date, assignment_start_date),
assignment_end_date = nvl(p_new_end_date, assignment_end_date)
where requisition_line_id=p_req_line_id;
wf_core.context('PO_ReqChangeRequestWF_PVT','UpdateReqLine',x_progress||sqlerrm);
end UpdateReqLine;
select parent_change_request_id
into l_req_change_request_id
from po_change_requests
where change_request_id=p_change_request_id;
update po_change_requests
set request_status='ACCEPTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
where change_request_id in (l_req_change_request_id, p_change_request_id);
select document_header_id, document_line_id, document_distribution_id, action_type, request_level
into l_header_id, l_line_id, l_distribution_id, l_action_type, l_request_level
from po_change_requests
where change_request_id=l_req_change_request_id;
update po_requisition_headers_all
set cancel_flag='Y'
where requisition_header_id=l_header_id;
update po_requisition_lines_all
set cancel_flag='Y'
where requisition_line_id=l_line_id;
select new_need_by_date, new_price, new_currency_unit_price,
new_start_date, new_expiration_date
into l_new_need_by_date, l_new_unit_price,
l_new_currency_unit_price, l_new_start_date,
l_new_expiration_date
from po_change_requests
where change_request_id=l_req_change_request_id;
UpdateReqLine(l_line_id, l_new_need_by_date,
l_new_unit_price, l_new_currency_unit_price,
l_new_start_date, l_new_expiration_date);
select new_quantity, old_quantity
into l_new_quantity, l_old_quantity
from po_change_requests
where change_request_id=l_req_change_request_id;
UpdateReqDistribution(l_line_id, l_distribution_id,
l_new_quantity, l_old_quantity);
select pcr1.change_request_id,
pcr1.parent_change_request_id,
pcr2.request_level,
pcr2.new_need_by_date,
pcr2.new_quantity,
pcr2.old_quantity,
pcr2.new_amount,
pcr2.old_amount,
pcr2.new_currency_amount,
pcr2.old_currency_amount,
pcr2.document_line_id,
pcr2.document_distribution_id,
pcr2.change_request_group_id
from po_change_requests pcr1, po_change_requests pcr2
where pcr1.change_request_group_id=p_change_request_group_id
and pcr1.document_line_location_id=p_line_location_id
and pcr1.parent_change_request_id=pcr2.change_request_id;
update po_change_requests
set request_status='ACCEPTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
where change_request_id in (l_request_id, l_parent_request_id);
UpdateReqLine(l_line_id, l_new_need_by_date,
null, null, null, null);
UpdateReqDistribution(l_line_id, l_distribution_id,
l_new_quantity, l_old_quantity, l_new_amount, l_old_amount,
l_new_currency_amount, l_old_currency_amount);
update po_change_requests
set request_status='ACCEPTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
where change_request_group_id=p_change_request_group_id
and document_line_location_id=p_line_location_id
and parent_change_request_id is null;
select new_price, new_currency_unit_price
into l_new_price, l_new_currency_unit_price
from po_change_requests
where change_request_group_id=l_req_change_group_id1
and document_line_id=l_line_id1
and action_type='DERIVED'
and new_price is not null;
UpdateReqLine(l_line_id1, null, l_new_price, l_new_currency_unit_price, null, null);
select pcr.document_type,
pcr.document_header_id,
pcr.document_line_id,
pcr.document_line_location_id,
pcr.document_distribution_id,
pcr.po_release_id,
pcr.change_request_id,
pcr.request_level,
pcr.action_type,
pcr.new_need_by_date,
pcr.new_price,
pcr.new_quantity,
pcr.new_start_date,
pcr.new_expiration_date,
pcr.new_amount,
pol.unit_price line_price,
pll.need_by_date ship_need_by_date,
pod.quantity_ordered dist_quantity,
pol.start_date line_start_date,
pol.expiration_date line_end_date,
pod.amount_ordered dist_amount,
nvl(por.cancel_flag, poh.cancel_flag) header_cancel_flag,
pol.cancel_flag line_cancel_flag,
pll.cancel_flag shipment_cancel_flag,
poh.currency_code,
pol.org_id
from po_change_requests pcr,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod,
po_releases_all por
where pcr.change_request_group_id=p_change_request_group_id
and pcr.request_status='PENDING'
and pcr.parent_change_request_id is not null
and pcr.document_header_id=poh.po_header_id
and pcr.document_line_id=pol.po_line_id(+)
and pcr.document_line_location_id=pll.line_location_id(+)
and pcr.document_distribution_id=pod.po_distribution_id(+)
and pcr.po_release_id=por.po_release_id(+)
order by document_line_id, nvl(document_line_location_id, 0), nvl(document_distribution_id,0);
SELECT sob.currency_code
INTO l_functional_currency_code
FROM gl_sets_of_books sob, financials_system_params_all fsp
WHERE fsp.org_id = l_org_id
AND fsp.set_of_books_id = sob.set_of_books_id;
select document_header_id,
po_release_id,
document_type
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='PENDING';
select
document_header_id,
po_release_id,
document_num,
action_type,
document_type,
request_level,
document_revision_num,
created_by,
document_line_id,
document_line_number,
document_line_location_id,
document_shipment_number,
document_distribution_id,
document_distribution_number,
request_reason,
old_need_by_date,
new_need_by_date,
old_price,
new_price,
old_quantity,
new_quantity,
old_start_date,
new_start_date,
old_expiration_date,
new_expiration_date,
old_amount,
new_amount,
parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='PENDING';
select nvl(authorization_status, 'IN PROCESS'), revision_num
into l_doc_status,ll_document_revision_num
from po_headers_all
where po_header_id=ll_document_header_id;
select nvl(authorization_status, 'IN PROCESS'), revision_num
into l_doc_status, ll_document_revision_num
from po_releases_all
where po_release_id=ll_po_release_id;
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
where change_request_id in (
select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='PENDING');
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
where change_request_group_id=p_change_request_group_id
and request_status='PENDING';
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=substr(fnd_message.get_string('PO',
'PO_RCO_VALIDATION_ERROR')||':'||
l_error_message1, 1, 2000)
where change_request_id in (
select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='PENDING');
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=substr(fnd_message.get_string('PO',
'PO_RCO_VALIDATION_ERROR')||':'||
l_error_message1, 1, 2000)
where change_request_group_id=p_change_request_group_id
and request_status='PENDING';
* corresponding PO part, and also update the status to 'ACCEPTED'
* of the req change
*
* 'ACCEPTANCE is to process the buyer's acceptance of
* change request. It will call movechangetopo to move the accepted
* change request to PO, and then update the req with the
* new value. also update the corresponding req change status.
*
* the process will COMMIT when it exits.
*
* Returns:
************************************************************************/
procedure ProcessBuyerAction(p_change_request_group_id in number, p_action in varchar2, p_launch_approvals_flag IN VARCHAR2 default 'N', p_supplier_change IN varchar2 default 'N') is
pragma AUTONOMOUS_TRANSACTION;
select decode (document_type, 'RELEASE', null, document_line_id), document_line_location_id,
change_request_id, request_reason
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='CANCELLATION';
select request_level, document_type, document_header_id,
document_line_id, document_distribution_id, po_release_id,
change_request_id, old_need_by_date, new_need_by_date,
old_price, new_price, old_quantity, new_quantity,
old_currency_unit_price, new_currency_unit_price,
old_start_date, new_start_date,
old_expiration_date, new_expiration_date,
old_amount, new_amount,
old_currency_amount, new_currency_amount,
change_request_group_id
from po_change_requests
where change_request_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='MODIFICATION')
order by document_line_id, document_distribution_id;
select request_level, document_type, document_header_id,
document_line_id, document_distribution_id, po_release_id,
change_request_id, old_need_by_date, new_need_by_date,
old_price, new_price, old_quantity, new_quantity,
old_currency_unit_price, new_currency_unit_price,
old_start_date, new_start_date,
old_expiration_date, new_expiration_date,
old_amount, new_amount,
old_currency_amount, new_currency_amount,
change_request_group_id
from po_change_requests
where change_request_group_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='MODIFICATION')
order by document_line_id, document_distribution_id;
select document_type, document_header_id, po_release_id, nvl(requester_id, created_by)
from po_change_requests
where change_request_group_id =p_change_request_group_id;
select change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status in ('PENDING', 'BUYER_APP')
and action_type='MODIFICATION';
select document_line_id,change_request_group_id
from po_change_requests
where document_type = 'REQ'
and change_request_id in
(select parent_change_request_id
from po_change_requests pcr2
where pcr2.change_request_group_id=p_change_request_group_id
and pcr2.action_type='MODIFICATION'
and ( pcr2.new_start_date is not null
or pcr2.new_expiration_date is not null ) );
update PO_CHANGE_REQUESTS pcr1
set (pcr1.request_status,
pcr1.change_active_flag,
pcr1.response_date,
pcr1.response_reason,
pcr1.responded_by,
pcr1.last_updated_by,
pcr1.last_update_login,
pcr1.last_update_date) =
(select 'REJECTED',
'N',
pcr2.response_date,
pcr2.response_reason,
pcr2.responded_by,
fnd_global.user_id,
fnd_global.login_id,
sysdate
from po_change_requests pcr2
where pcr2.parent_change_request_id=pcr1.change_request_id
and pcr2.change_request_group_id=p_change_request_group_id
and pcr2.request_status='REJECTED')
where pcr1.change_request_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='REJECTED');
update PO_CHANGE_REQUESTS pcr1
set (pcr1.request_status,
pcr1.change_active_flag,
pcr1.response_date,
pcr1.response_reason,
pcr1.responded_by,
pcr1.last_updated_by,
pcr1.last_update_login,
pcr1.last_update_date) =
(select 'REJECTED',
'N',
pcr2.response_date,
pcr2.response_reason,
pcr2.responded_by,
fnd_global.user_id,
fnd_global.login_id,
sysdate
from po_change_requests pcr2
where pcr2.parent_change_request_id in
( select pcr3.change_request_id
from po_change_requests pcr3
where
pcr3.change_request_group_id=pcr1.change_request_group_id
and pcr3.document_line_id = pcr1.document_line_id
)
and pcr2.change_request_group_id=p_change_request_group_id
and pcr2.request_status='REJECTED' and rownum=1
)
where pcr1.change_request_id in (
select pcr5.change_request_id
from
po_change_requests pcr,
po_change_requests pcr4,
po_change_requests pcr5,
po_requisition_lines_all por
where
pcr.change_request_group_id=p_change_request_group_id
and pcr.parent_change_request_id=pcr4.change_request_id
and pcr4.change_request_group_id=pcr5.change_request_group_id
and pcr4.document_line_id = pcr5.document_line_id
and pcr.request_status='REJECTED'
and por.requisition_line_id = pcr4.document_line_id
and por.purchase_basis='TEMP LABOR' );
UPDATE po_line_locations_all
SET
approved_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE line_location_id IN
(SELECT document_line_location_id
FROM po_change_requests
WHERE
request_level = 'SHIPMENT' AND
change_request_group_id = p_change_request_group_id AND
action_type IN ('MODIFICATION', 'CANCELLATION') AND
initiator = 'REQUESTER') AND
approved_flag = 'R';
update po_headers_all
set AUTHORIZATION_STATUS = 'APPROVED',
approved_flag='Y',
CHANGE_REQUESTED_BY=null,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_header_id = l_document_id;
update po_releases_all
set AUTHORIZATION_STATUS = 'APPROVED',
approved_flag='Y',
CHANGE_REQUESTED_BY=null,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_release_id = l_release_id;
select count(1)
into l_num_of_shipments
from po_line_locations_all
where po_line_id = l_document_line_id
and nvl(cancel_flag, 'N') = 'N';
update PO_CHANGE_REQUESTS pcr1
set (pcr1.request_status,
pcr1.change_active_flag,
pcr1.response_date,
pcr1.response_reason,
pcr1.responded_by,
pcr1.last_updated_by,
pcr1.last_update_login,
pcr1.last_update_date) =
(select 'ACCEPTED',
'N',
pcr2.response_date,
pcr2.response_reason,
pcr2.responded_by,
fnd_global.user_id,
fnd_global.login_id,
sysdate
from po_change_requests pcr2
where pcr2.parent_change_request_id=pcr1.change_request_id
and pcr2.change_request_group_id=p_change_request_group_id
and pcr2.request_status in ('BUYER_APP', 'ACCEPTED')
and pcr2.action_type='CANCELLATION')
where pcr1.change_request_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status in ('BUYER_APP', 'ACCEPTED')
and action_type='CANCELLATION');
update PO_CHANGE_REQUESTS
set request_status='ACCEPTED',
change_active_flag='N'
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='CANCELLATION';
update po_headers_all set
AUTHORIZATION_STATUS = 'IN PROCESS',
-- approved_flag='N',
CHANGE_REQUESTED_BY='REQUESTER',
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_header_id = l_document_id;
update po_releases_all set
AUTHORIZATION_STATUS = 'IN PROCESS',
-- approved_flag='N',
CHANGE_REQUESTED_BY='REQUESTER',
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_release_id = l_release_id;
update PO_CHANGE_REQUESTS
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
validation_error =l_validation_error
where change_request_id in (select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='CANCELLATION');
update PO_CHANGE_REQUESTS
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
validation_error =l_validation_error
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='CANCELLATION';
select new_price, new_currency_unit_price
into l_new_price1, l_new_currency_unit_price1
from po_change_requests
where change_request_group_id=l_group_id
and document_line_id=l_document_line_id
and action_type='DERIVED'
and new_price is not null;
UpdateReqLine(l_document_line_id,
null,
l_new_price1,
l_new_currency_unit_price1, null, null);
select new_start_date
into l_new_start_date
from po_change_requests
where change_request_group_id = l_group_id
and document_type = 'REQ'
and document_line_id = l_document_line_id
and request_level= 'LINE'
and action_type = 'MODIFICATION'
and new_expiration_date is null
and new_start_date is not null;
select new_expiration_date
into l_new_expiration_date
from po_change_requests
where change_request_group_id = l_group_id
and document_type = 'REQ'
and document_line_id = l_document_line_id
and request_level= 'LINE'
and action_type = 'MODIFICATION'
and new_start_date is null
and new_expiration_date is not null;
UpdateReqLine(l_document_line_id,
l_new_need_by_date,
l_new_price,
l_new_currency_unit_price,
l_new_start_date,
l_new_expiration_date);
UpdateReqDistribution(l_document_line_id,
l_document_distribution_id,
l_new_quantity,
l_old_quantity,
l_new_amount,
l_old_amount,
l_new_currency_amount,
l_old_currency_amount);
update PO_CHANGE_REQUESTS pcr1
set (pcr1.request_status,
pcr1.change_active_flag,
pcr1.response_date,
pcr1.response_reason,
pcr1.responded_by,
pcr1.last_updated_by,
pcr1.last_update_login,
pcr1.last_update_date) =
(select 'ACCEPTED',
'N',
pcr2.response_date,
pcr2.response_reason,
pcr2.responded_by,
fnd_global.user_id,
fnd_global.login_id,
sysdate
from po_change_requests pcr2
where pcr2.parent_change_request_id=pcr1.change_request_id
and pcr2.change_request_group_id=p_change_request_group_id
and pcr2.request_status='BUYER_APP'
and pcr2.action_type='MODIFICATION')
where pcr1.change_request_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='MODIFICATION');
update PO_CHANGE_REQUESTS pcr1
set (pcr1.request_status,
pcr1.change_active_flag,
pcr1.response_date,
pcr1.response_reason,
pcr1.responded_by,
pcr1.last_updated_by,
pcr1.last_update_login,
pcr1.last_update_date) =
(select 'ACCEPTED',
'N',
pcr2.response_date,
pcr2.response_reason,
pcr2.responded_by,
fnd_global.user_id,
fnd_global.login_id,
sysdate
from po_change_requests pcr2
where pcr2.document_type= 'REQ'
and pcr2.change_request_group_id=l_req_change_grp_id
and pcr2.document_line_id = l_req_doc_id
and pcr2.request_status= 'ACCEPTED'
and pcr2.action_type ='MODIFICATION'
and pcr2.request_level ='LINE')
where pcr1.change_request_group_id =l_req_change_grp_id
and pcr1.document_line_id = l_req_doc_id
and pcr1.request_status <>'ACCEPTED'
and pcr1.action_type='MODIFICATION'
and pcr1.request_level = 'LINE';
update PO_CHANGE_REQUESTS
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
validation_error=l_err_msg
where change_request_id in (select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='MODIFICATION');
update PO_CHANGE_REQUESTS
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
validation_error=l_err_msg
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='MODIFICATION';
select request_level, document_type, document_header_id,
document_line_id, document_distribution_id, po_release_id,
change_request_id, old_need_by_date, new_need_by_date,
old_price, new_price, old_quantity, new_quantity,
old_currency_unit_price, new_currency_unit_price,
old_start_date, new_start_date,
old_expiration_date, new_expiration_date,
old_amount, new_amount,
old_currency_amount, new_currency_amount,
change_request_group_id
from po_change_requests
where change_request_group_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='MODIFICATION')
order by document_line_id, document_distribution_id;
select document_type, document_header_id, po_release_id, nvl(requester_id, created_by) from po_change_requests
where change_request_group_id =p_change_request_group_id;
select new_price, new_currency_unit_price
into l_new_price1, l_new_currency_unit_price1
from po_change_requests
where change_request_group_id=l_group_id
and document_line_id=l_document_line_id
and action_type='DERIVED'
and new_price is not null;
UpdateReqLine(l_document_line_id,
null,
l_new_price1,
l_new_currency_unit_price1, null, null);
select nvl(pcr1.new_start_date, pcr2.new_start_date),
nvl(pcr1.new_expiration_date, pcr2.new_expiration_date),
pcr1.change_request_id,
pcr2.change_request_id
into l_new_start_date,
l_new_expiration_date,
l_date_change_id1,
l_date_change_id
from po_change_requests pcr1, po_change_requests pcr2
where pcr1.change_request_group_id = pcr2.change_request_group_id
-- and pcr1.change_request_id in
and pcr1.change_request_group_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='BUYER_APP'
and action_type='MODIFICATION')
and (pcr2.new_start_date is not null
or pcr2.new_expiration_date is not null)
and pcr1.change_request_id <> pcr2.change_request_id
and (pcr1.new_start_date is not null
or pcr1.new_expiration_date is not null)
and pcr2.request_level='LINE'
and pcr2.action_type='MODIFICATION';
UpdateReqLine(l_document_line_id,
l_new_need_by_date,
l_new_price,
l_new_currency_unit_price,
l_new_start_date,
l_new_expiration_date);
UpdateReqDistribution(l_document_line_id,
l_document_distribution_id,
l_new_quantity,
l_old_quantity,
l_new_amount,
l_old_amount,
l_new_currency_amount,
l_old_currency_amount);
select pcr.response_date,
pcr.response_reason,
pcr.responded_by
into l_temp_date,
l_temp_reason,
l_temp_responder
from po_change_requests pcr
where pcr.change_request_group_id=p_change_request_group_id
and pcr.action_type='MODIFICATION'
and rownum=1;
update PO_CHANGE_REQUESTS pcr1
set (pcr1.request_status,
pcr1.change_active_flag,
pcr1.response_date,
pcr1.response_reason,
pcr1.responded_by,
pcr1.last_updated_by,
pcr1.last_update_login,
pcr1.last_update_date) =
(select 'ACCEPTED',
'N',
l_temp_date,
l_temp_reason,
l_temp_responder,
fnd_global.user_id,
fnd_global.login_id,
sysdate
from dual)
where pcr1.change_request_group_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and action_type='MODIFICATION');
update PO_CHANGE_REQUESTS pcr1
set (pcr1.request_status,
pcr1.change_active_flag,
pcr1.response_date,
pcr1.response_reason,
pcr1.responded_by,
pcr1.last_updated_by,
pcr1.last_update_login,
pcr1.last_update_date) =
(select 'ACCEPTED',
'N',
pcr2.response_date,
pcr2.response_reason,
pcr2.responded_by,
fnd_global.user_id,
fnd_global.login_id,
sysdate
from po_change_requests pcr2
where pcr2.parent_change_request_id=l_date_change_id1
and pcr2.change_request_group_id=p_change_request_group_id
and pcr2.action_type='MODIFICATION'
and rownum=1)
where pcr1.change_request_id = l_date_change_id;
update PO_CHANGE_REQUESTS
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
validation_error=l_err_msg
where change_request_group_id in (select parent_change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and action_type='MODIFICATION');
update PO_CHANGE_REQUESTS
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
validation_error=l_err_msg
where change_request_group_id=p_change_request_group_id
and action_type='MODIFICATION';
update PO_CHANGE_REQUESTS
set last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate,
request_status=p_request_status,
response_date=sysdate,
responded_by=fnd_global.user_id,
response_reason = nvl(p_response_reason, response_reason),
change_active_flag=decode(p_request_status, 'ACCEPTED', 'N',
'REJECTED', 'N', 'Y')
where change_request_group_id=p_change_request_group_id
and request_status not in ('ACCEPTED', 'REJECTED');
select * from (
select max(pll.po_header_id) document_header_id,
max(pll.po_release_id) po_release_id,
max(decode(pll.po_release_id, null, poh.segment1, poh.segment1||'-'||to_char(por.release_num))) document_num,
max(pcr.action_type) action_type,
max(decode(pll.po_release_id, null, 'PO', 'RELEASE')) document_type,
'LINE' request_level,
max(pcr.document_revision_num) document_revision_num,
max(pcr.created_by) created_by,
max(pol.po_line_id) document_line_id,
max(pol.line_num) document_line_number,
to_number(null) document_line_location_id,
to_number(null) document_shipment_number,
to_number(null) document_distribution_id,
to_number(null) document_distribution_num,
max(pcr.request_reason) request_reason,
max(nvl(pcr.old_need_by_date, pll.need_by_date)) old_need_by_date,
max(pcr.new_need_by_date) new_need_by_date,
max((pcr.old_price)/nvl(poh.rate,1)) old_price, -- If Order is in trn currency divide price by rate, else leave it as it is
max((pcr.new_price)/nvl(poh.rate,1)) new_price,
to_number(null) old_quantity,
to_number(null) new_quantity,
max(pcr.change_request_id) parent_change_request_id,
max(pll.quantity) ship_quantity,
to_number(null) dist_quantity,
max(pll.SHIP_TO_ORGANIZATION_ID),
max(pll.SHIP_TO_LOCATION_ID),
max(prl.item_id),
max(prl.unit_meas_lookup_code) req_uom,
max(nvl(pll.unit_meas_lookup_code,pol.unit_meas_lookup_code)) po_uom,
max(pol.start_date) old_start_date,
max(pcr.new_start_date) new_start_date,
max(pol.expiration_date) old_expiration_date,
max(pcr.new_expiration_date) new_expiration_date,
to_number(null) old_amount,
to_number(null) new_amount,
max(pll.amount) ship_amount,
to_number(null) dist_amount,
max(prl.matching_basis),
max(pcr.requester_id) requester_id -- Bug # 3862383
from po_change_requests pcr,
po_line_locations_all pll,
po_requisition_lines_all prl,
po_headers_all poh,
po_releases_all por,
po_lines_all pol
where pcr.change_request_group_id=l_change_request_group_id
and pcr.request_status='MGR_APP'
and pcr.document_distribution_id is null
and pcr.document_line_id=prl.requisition_line_id
and prl.purchase_basis = 'TEMP LABOR'
and prl.line_location_id=pll.line_location_id
and poh.po_header_id=pll.po_header_id
and por.po_release_id(+)=pll.po_release_id
and pol.po_line_id=pll.po_line_id
group by pol.po_line_id
union select pll.po_header_id document_header_id,
pll.po_release_id po_release_id,
decode(pll.po_release_id, null, poh.segment1, poh.segment1||'-'||to_char(por.release_num)) document_num,
pcr.action_type action_type,
decode(pll.po_release_id, null, 'PO', 'RELEASE') document_type,
decode(pcr.new_price, null, 'SHIPMENT', 'LINE') request_level,
pcr.document_revision_num document_revision_num,
pcr.created_by created_by,
pol.po_line_id document_line_id,
pol.line_num document_line_number,
decode(pcr.new_price, null, pll.line_location_id, null) document_line_location_id,
decode(pcr.new_price, null, pll.shipment_num, null) document_shipment_number,
to_number(null) document_distribution_id,
to_number(null) document_distribution_num,
pcr.request_reason request_reason,
nvl(pcr.old_need_by_date, pll.need_by_date) old_need_by_date,
pcr.new_need_by_date new_need_by_date,
(pcr.old_price)/nvl(poh.rate,1) old_price, -- If Order is in trn currency divide price by rate, else leave it as it is
(pcr.new_price)/nvl(poh.rate,1) new_price,
to_number(null) old_quantity,
to_number(null) new_quantity,
pcr.change_request_id parent_change_request_id,
pll.quantity ship_quantity,
to_number(null) dist_quantity,
pll.SHIP_TO_ORGANIZATION_ID,
pll.SHIP_TO_LOCATION_ID,
prl.item_id,
prl.unit_meas_lookup_code req_uom,
nvl(pll.unit_meas_lookup_code,pol.unit_meas_lookup_code) po_uom,
pol.start_date old_start_date,
pcr.new_start_date new_start_date,
pol.expiration_date old_expiration_date,
pcr.new_expiration_date new_expiration_date,
to_number(null) old_amount,
to_number(null) new_amount,
pll.amount ship_amount,
to_number(null) dist_amount,
prl.matching_basis,
pcr.requester_id requester_id -- Bug # 3862383
from po_change_requests pcr,
po_line_locations_all pll,
po_requisition_lines_all prl,
po_headers_all poh,
po_releases_all por,
po_lines_all pol
where pcr.change_request_group_id=l_change_request_group_id
and pcr.request_status='MGR_APP'
and pcr.document_distribution_id is null
and pcr.document_line_id=prl.requisition_line_id
and prl.purchase_basis <> 'TEMP LABOR'
and prl.line_location_id=pll.line_location_id
and poh.po_header_id=pll.po_header_id
and por.po_release_id(+)=pll.po_release_id
and pol.po_line_id=pll.po_line_id
union
select pll.po_header_id document_header_id,
pll.po_release_id po_release_id,
decode(pll.po_release_id, null, poh.segment1, poh.segment1||'-'||to_char(por.release_num)) document_num,
pcr.action_type action_type,
decode(pll.po_release_id, null, 'PO', 'RELEASE') document_type,
'DISTRIBUTION' request_level,
pcr.document_revision_num document_revision_num,
pcr.created_by created_by,
pol.po_line_id document_line_id,
pol.line_num document_line_number,
pll.line_location_id document_line_location_id,
pll.shipment_num document_shipment_number,
pod.po_distribution_id document_distribution_id,
pod.distribution_num document_distribution_num,
pcr.request_reason request_reason,
nvl(pcr.old_need_by_date, pll.need_by_date) old_need_by_date,
pcr.new_need_by_date new_need_by_date,
decode(prl.ORDER_TYPE_LOOKUP_CODE,'AMOUNT',pcr.old_price,(pcr.old_price)/nvl(pod.rate,1)) old_price, /* If Order is in trn currency divide price by rate if order_type is not AMOUNT. Beacuse we are dividing qty by rate in that case. */
decode(prl.ORDER_TYPE_LOOKUP_CODE,'AMOUNT',pcr.new_price,(pcr.new_price)/nvl(pod.rate,1)) new_price,
decode(prl.ORDER_TYPE_LOOKUP_CODE,'AMOUNT', (pcr.old_quantity/nvl(pod.RATE,1)), pcr.old_quantity) old_quantity,--Divide qty by rate for amt based reqs only
decode(prl.ORDER_TYPE_LOOKUP_CODE,'AMOUNT', (pcr.new_quantity/nvl(pod.RATE,1)), pcr.new_quantity) new_quantity,
pcr.change_request_id parent_change_request_id,
pll.quantity ship_quantity,
pod.quantity_ordered dist_quantity,
pll.SHIP_TO_ORGANIZATION_ID,
pll.SHIP_TO_LOCATION_ID,
prl.item_id,
prl.unit_meas_lookup_code req_uom,
nvl(pll.unit_meas_lookup_code,pol.unit_meas_lookup_code) po_uom,
to_date(null) old_start_date,
to_date(null) new_start_date,
to_date(null) old_expiration_date,
to_date(null) new_expiration_date,
(pcr.old_amount)/nvl(pod.rate,1) old_amount,
(pcr.new_amount)/nvl(pod.rate,1) new_amount,
pll.amount ship_amount,
pod.amount_ordered dist_amount,
prl.matching_basis,
pcr.requester_id requester_id -- Bug # 3862383
from po_change_requests pcr,
po_line_locations_all pll,
po_requisition_lines_all prl,
po_headers_all poh,
po_releases_all por,
po_distributions_all pod,
po_req_distributions_all prd,
po_lines_all pol
where pcr.change_request_group_id=l_change_request_group_id
and pcr.request_status='MGR_APP'
and pcr.document_distribution_id is not null
and pcr.document_line_id=prl.requisition_line_id
and prl.line_location_id=pll.line_location_id
and poh.po_header_id=pll.po_header_id
and por.po_release_id(+)=pll.po_release_id
and pcr.document_distribution_id=prd.distribution_id
and pcr.document_distribution_id=pod.req_distribution_id
and pll.line_location_id=pod.line_location_id
and pol.po_line_id=pll.po_line_id
)
order by document_header_id, po_release_id, document_line_id,
document_line_location_id, document_distribution_id;
update PO_CHANGE_REQUESTS
set last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate,
request_status='MGR_APP'
where change_request_group_id=p_change_request_group_id
and action_type='CANCELLATION'
and request_status='NEW';
update PO_CHANGE_REQUESTS
set last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate,
request_status='MGR_APP'
where change_request_group_id=p_change_request_group_id
and action_type='MODIFICATION'
and request_status in ('NEW', 'MGR_PRE_APP');
update PO_CHANGE_REQUESTS
set last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate,
request_status='MGR_PRE_APP'
where change_request_group_id=p_change_request_group_id
and action_type='MODIFICATION'
and request_status ='NEW';
update PO_CHANGE_REQUESTS
set last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate,
request_status='REJECTED',
change_active_flag='N',
responded_by=p_responder_id,
response_reason=p_response_reason,
response_date = sysdate
where change_request_group_id=p_change_request_group_id
and action_type='MODIFICATION'
and request_status in ('NEW', 'MGR_PRE_APP');
update po_requisition_headers_all
set last_updated_by = nvl(fnd_global.user_id, last_updated_by),
last_update_login = nvl(fnd_global.login_id, last_update_login),
last_update_date = sysdate,
change_pending_flag=p_change_flag
where requisition_header_id = p_document_id;
update po_change_requests
set wf_item_type=p_itemtype,
wf_item_key=p_itemkey
where change_request_group_id=p_change_request_group_id;
* Private Procedure: InsertActionHist
* Effects: insert into action history table.
*
* It is called when the change request is submitted (by requester
* or buyer) and when buyer responds to the change request.
*
* the action can be 'SUBMIT CHANGE', 'ACCEPTED', 'REJECTED'
* or 'RESPOND'
*
* the process will commit when it exits.
*
* Returns:
************************************************************************/
PROCEDURE InsertActionHist(
itemtype varchar2,
itemkey varchar2,
p_doc_id number,
p_doc_type varchar2,
p_doc_subtype varchar2,
p_employee_id number,
p_action varchar2,
p_note varchar2,
p_path_id number) is
pragma AUTONOMOUS_TRANSACTION;
select max(sequence_num)
from po_action_history
where object_id= doc_id and
object_type_code = doc_type;
select action_code
from po_action_history
where object_id = doc_id and
object_type_code = doc_type and
sequence_num = seq_num;
** First insert a row with a SUBMIT action.
** Then insert a row with a NULL ACTION_CODE to simulate the forward-to
*/
x_progress := '001';
select revision_num
into l_revision_num
from PO_HEADERS_ALL
where po_header_id = p_doc_id;
select revision_num
into l_revision_num
from PO_RELEASES_ALL
where po_release_id = p_doc_id;
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(p_doc_id,
p_doc_type,
p_doc_subtype,
l_sequence_num,
sysdate,
nvl(fnd_global.user_id, 1),
sysdate,
nvl(fnd_global.user_id, 1),
p_action,
decode(p_action, '',to_date('', 'YYYY-MM-DD'), sysdate),
p_employee_id,
p_note,
l_revision_num,
nvl(fnd_global.login_id, 1),
0,
0,
0,
'',
l_approval_path_id,
'' );
UPDATE PO_ACTION_HISTORY
set object_id = p_doc_id,
object_type_code = p_doc_type,
object_sub_type_code = p_doc_subtype,
sequence_num = l_sequence_num,
last_update_date = sysdate,
last_updated_by = nvl(fnd_global.user_id, 1),
creation_date = sysdate,
created_by = nvl(fnd_global.user_id, 1),
action_code = p_action,
action_date = decode(p_action, '',to_date('', 'YYYY-MM-DD'), sysdate),
employee_id = p_employee_id,
note = p_note,
object_revision_num = l_revision_num,
last_update_login = nvl(fnd_global.login_id, 1),
request_id = 0,
program_application_id = 0,
program_id = 0,
program_update_date = '',
approval_path_id = l_approval_path_id,
offline_code = ''
WHERE
object_id= p_doc_id and
object_type_code = p_doc_type and
sequence_num = l_sequence_num;
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(p_doc_id,
p_doc_type,
p_doc_subtype,
l_sequence_num + 1,
sysdate,
nvl(fnd_global.user_id, 1),
sysdate,
nvl(fnd_global.user_id, 1),
NULL, -- ACTION_CODE
decode(p_action, '',to_date('', 'YYYY-MM-DD'), sysdate),
p_employee_id,
NULL,
l_revision_num,
nvl(fnd_global.login_id,1),
0,
0,
0,
'',
0,
'' );
'InsertActionHist'||sqlerrm,x_progress);
END InsertActionHist;
update po_headers_all set
AUTHORIZATION_STATUS = 'IN PROCESS',
-- approved_flag='N',
CHANGE_REQUESTED_BY='REQUESTER',
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_header_id = p_document_id;
UPDATE po_line_locations_all
SET
approved_flag='R',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE
line_location_id in
(select document_line_location_id
from po_change_requests
where
request_level = 'SHIPMENT' and
document_header_id = p_document_id and
action_type IN ('MODIFICATION', 'CANCELLATION') and
initiator = 'REQUESTER' and
request_status ='PENDING') and
approved_flag='Y';
update po_releases_all set
AUTHORIZATION_STATUS = 'IN PROCESS',
-- approved_flag='N',
CHANGE_REQUESTED_BY='REQUESTER',
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_release_id = p_document_id;
UPDATE po_line_locations_all
SET
approved_flag = 'R',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE line_location_id in (select document_line_location_id
from po_change_requests
where request_level = 'SHIPMENT' and
po_release_id = p_document_id and
action_type IN ('MODIFICATION', 'CANCELLATION') and
initiator = 'REQUESTER' and
request_status = 'PENDING') and
approved_flag='Y';
select pcr.wf_item_type, pcr.wf_item_key
from po_change_requests pcr, po_change_requests pcr2
where pcr2.change_request_group_id=l_change_request_group_id
and pcr.change_request_id=pcr2.parent_change_request_id
and pcr.wf_item_type is not null;
select PO_REQUESTER_CHANGE_WF_S.nextval into l_count from dual;
select document_header_id, nvl(pcr.requester_id, por.preparer_id),
document_num
from po_change_requests pcr, po_requisition_headers_all por
where pcr.change_request_group_id=l_change_request_group_id
and pcr.document_header_id=por.requisition_header_id;
select org_id
into l_org_id
from po_requisition_headers_all
where requisition_header_id=l_document_id;
select document_type,
document_header_id,
document_revision_num,
po_release_id,
wf_item_type,
wf_item_key
from po_change_requests
where change_request_group_id=l_change_request_group_id;
select por.preparer_id
from po_requisition_headers_all por,
po_change_requests pcr1,
po_change_requests pcr2
where pcr2.change_request_group_id=l_change_request_group_id
and pcr2.parent_change_request_id=pcr1.change_request_id
and pcr1.document_header_id=por.requisition_header_id;
select change_request_group_id
from po_change_requests
where change_request_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=l_change_request_group_id);
select prh.segment1, pcr.wf_item_type, pcr.wf_item_key, prh.requisition_header_id
from po_requisition_headers_all prh, po_change_requests pcr
where prh.requisition_header_id=pcr.document_header_id
and pcr.change_request_group_id=l_change_request_group_id;
select por.org_id, por.agent_id, poh.segment1||'-'||to_char(por.release_num)
into l_org_id, l_agent_id, l_document_num
from po_releases_all por, po_headers_all poh
where por.po_release_id=l_document_id
and por.po_header_id=poh.po_header_id;
select org_id, agent_id, segment1
into l_org_id, l_agent_id, l_document_num
from po_headers_all
where po_header_id=l_document_id;
SELECT gsb.currency_code
INTO l_currency_code
FROM financials_system_params_all fsp,
gl_sets_of_books gsb
WHERE fsp.set_of_books_id = gsb.set_of_books_id
AND fsp.org_id = l_org_id;
SELECT nvl(SUM(nvl(decode(matching_basis, 'AMOUNT', amount, quantity * unit_price), 0)), 0)
into l_req_amount
FROM po_requisition_lines_all
WHERE requisition_header_id = l_req_header_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT nvl(sum(nonrecoverable_tax), 0)
into l_tax_amount
FROM po_requisition_lines_all rl,
po_req_distributions_all rd
WHERE rl.requisition_header_id = l_req_header_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.cancel_flag,'N') = 'N'
AND NVL(rl.modified_by_agent_flag, 'N') = 'N';
update po_change_requests
set wf_item_type=item_type,
wf_item_key=item_key
where change_request_group_id=p_change_request_group_id;
procedure UpdatePODocHeaderTables(p_document_type varchar2, p_document_id number)
is
pragma AUTONOMOUS_TRANSACTION;
x_progress := 'PO_ReqChangeRequestWF_PVT.UpdatePODocHeaderTables';
update po_headers_all
set change_requested_by = null,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_header_id = p_document_id;
update po_releases_all
set change_requested_by = null,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_release_id = p_document_id;
wf_core.context('PO_ReqChangeRequestWF_PVT','UpdatePODocHeaderTables',x_progress|| sqlerrm);
END UpdatePODocHeaderTables;
select max(change_request_group_id)
from po_change_requests
where document_header_id=l_doc_id
and initiator='REQUESTER'
and request_status='NEW';
* Public Procedure: Update_Req_Change_Flag
* Effects: workflow procedure, called at the beginning of POREQCHA
*
* set the change_pending_flag in the po_requisition_headers_all
* table to 'Y'
*
************************************************************************/
procedure Update_Req_Change_Flag(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_document_id NUMBER;
x_progress := 'PO_ReqChangeRequestWF_PVT.Update_Req_Change_Flag: 01';
x_progress := 'PO_REQAPPROVAL_INIT1.Update_Req_Change_Flag: 02';
'Update_Req_Change_Flag',x_progress);
sqlerrm, 'PO_ReqChangeRequestWF_PVT.Update_Req_Change_Flag');
END Update_Req_Change_Flag;
* Public Procedure: Insert_into_History_CHGsubmit
* Effects: workflow procedure, called in workflow POREQCHA and
* PORPOCHA (INFORM_BUYER_PO_CHANGE)
*
* inserting into action history table 'submit change'
*
************************************************************************/
procedure Insert_into_History_CHGsubmit(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_doc_id number;
select requester_id
from po_change_requests
where change_request_group_id=l_change_request_group_id;
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit: 01';
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit: 02';
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit: 03';
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit: 04';
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit: 05';
select agent_id
into l_employee_id
from po_headers_all
where po_header_id=l_doc_id;
select preparer_id
into l_employee_id
from po_requisition_headers_all
where requisition_header_id=l_doc_id;
select agent_id
into l_employee_id
from po_releases_all
where po_release_id=l_doc_id;
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit: 06';
InsertActionHist(itemtype,itemkey,l_doc_id, l_doc_type,
l_doc_subtype, l_employee_id,
'SUBMIT CHANGE', l_note, null);
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit: 07';
'Insert_into_History_CHGsubmit',x_progress||sqlerrm);
'PO_ReqChangeRequestWF_PVT.Insert_into_History_CHGsubmit');
END Insert_into_History_CHGsubmit;
select approval_required_flag
from po_change_requests
where change_request_group_id=l_change_request_group_id
and action_type='MODIFICATION'
and request_status = 'NEW';
select change_request_id
from po_change_requests
where change_request_group_id=l_change_request_group_id
and request_status='MGR_PRE_APP';
select request_status
from po_change_requests
where change_request_group_id=l_change_request_group_id
and request_status='MGR_APP';
procedure Update_Action_History(p_object_id in number,
p_employee_id in number,
p_action_code in varchar2) is
pragma AUTONOMOUS_TRANSACTION;
po_forward_sv1.update_action_history (
p_object_id,
'REQUISITION',
p_employee_id,
p_action_code,
null,
fnd_global.user_id,
fnd_global.login_id
);
end Update_Action_History;
procedure Insert_Action_History(l_document_id in NUMBER,
l_document_type in VARCHAR2,
l_document_subtype in VARCHAR2,
l_sequence_num in NUMBER,
l_employee_id in NUMBER,
l_new_action_code in VARCHAR2,
l_object_rev_num in NUMBER,
l_approval_path_id in NUMBER) is
pragma AUTONOMOUS_TRANSACTION;
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(l_document_id,
l_document_type,
l_document_subtype,
l_sequence_num + 1,
sysdate,
l_employee_id,
sysdate,
l_employee_id,
l_new_action_code,
sysdate,
l_employee_id,
NULL,
l_object_rev_num,
l_employee_id,
0,
0,
0,
'',
l_approval_path_id,
'' );
end Insert_Action_History;
* Public Procedure: Update_Action_History_App_Rej
* Effects: workflow procedure, used in POREQCHA
*
* if a change request is responded because of PO Cancel
* This procedure will insert into the action history table
* a record with action 'RETURN'
*
************************************************************************/
procedure Update_Action_History_App_Rej(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(100) := '000';
select change_request_id
from po_change_requests
where change_request_group_id=grp_id
and request_status='MGR_APP'
and action_type='MODIFICATION';
select change_request_id
from po_change_requests
where change_request_group_id=grp_id
and request_status='REJECTED'
and action_type='MODIFICATION';
select change_request_id
from po_change_requests
where change_request_group_id=grp_id
and request_status='MGR_APP'
and action_type='CANCELLATION';
l_progress := 'Update_Action_History_App_Rej: 001';
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, 'Entering Update_Action_History_App_Rej...' );
SELECT max(sequence_num)
INTO l_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = l_document_id;
select action_code, employee_id, object_revision_num, approval_path_id
into l_action_code, l_employee_id, l_object_rev_num, l_approval_path_id
from po_action_history
where object_id=l_document_id
and object_type_code='REQUISITION'
and sequence_num=l_sequence_num;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, ' l_action_code = ' || l_action_code );
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, ' l_sequence_num = ' || l_sequence_num );
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, ' l_new_action_code = ' || l_new_action_code );
Insert_Action_History(l_document_id,
l_document_type,
l_document_subtype,
l_sequence_num,
l_employee_id,
l_new_action_code,
l_object_rev_num,
l_approval_path_id);
Update_Action_History(l_document_id,
l_employee_id,
l_new_action_code);
l_progress := 'Update_Action_History_App_Rej: 006';
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, 'Leaving Update_Action_History_App_Rej...');
l_progress := 'Update_Action_History_App_Rej: 999';
wf_core.context('PO_ReqChangeRequestWF_PVT','Update_Action_History_App_Rej',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_ReqChangeRequestWF_PVT.Update_Action_History_App_Rej');
END Update_Action_History_App_Rej;
* Public Procedure: Update_Action_History_Return
* Effects: workflow procedure, used in POREQCHA
*
* if a change request is responded because of PO Cancel
* This procedure will insert into the action history table
* a record with action 'RETURN'
*
************************************************************************/
procedure Update_Action_History_Return(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(100) := '000';
l_progress := 'Update_Action_History_Return: 001';
l_progress := 'Update_Action_History_Return: 002-'||
to_char(l_document_id)||'-'||
l_document_type||'-'||l_document_subtype;
/* update po action history */
PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
itemkey=>itemkey,
x_action=>l_action,
x_req_header_id=>l_document_id,
x_last_approver=>l_result,
x_note=>l_note);
l_progress := 'Update_Action_History_Return: 006';
l_progress := 'Update_Action_History_Return: 999';
wf_core.context('PO_ReqChangeRequestWF_PVT','Update_Action_History_Return',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_ReqChangeRequestWF_PVT.Update_Action_History_Return');
END Update_Action_History_Return;
select distinct pcr1.change_request_group_id
from po_change_requests pcr1, po_change_requests pcr2
where pcr1.parent_change_request_id=pcr2.change_request_id
and pcr2.change_request_group_id=l_change_request_group_id
and pcr1.request_status='PENDING';
select nvl(sum(nvl(decode(pcr4.action_type, 'CANCELLATION', 0,
decode(prl.unit_price, 0, 0, decode(prl.matching_basis, 'AMOUNT',nvl(pcr3.new_amount, prl.amount) * por_view_reqs_pkg.get_line_nonrec_tax_total(
prl.requisition_line_id)/prl.amount,
nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity, prl.quantity)*
por_view_reqs_pkg.get_line_nonrec_tax_total(
prl.requisition_line_id)/
(prl.unit_price*prl.quantity)))),0)),0),
nvl(sum(decode(pcr4.action_type, 'CANCELLATION', 0, decode(prl.matching_basis, 'AMOUNT', nvl(pcr3.new_amount, prl.amount),
nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity, prl.quantity)))), 0)
into l_new_tax_amount, l_new_req_amount
from po_requisition_lines_all prl,
po_change_requests pcr1,
po_change_requests pcr2,
po_change_requests pcr3,
po_change_requests pcr4
where prl.requisition_line_id=pcr1.document_line_id(+)
and pcr1.change_request_group_id(+)=l_change_request_group_id
and pcr1.request_level(+)='LINE'
and pcr1.new_price(+) is not null
and prl.requisition_line_id=pcr2.document_line_id(+)
and pcr2.change_request_group_id(+)=l_change_request_group_id
and pcr2.request_level(+)='LINE'
and pcr2.action_type(+)='DERIVED'
and pcr2.new_quantity(+) is not null
and prl.requisition_line_id=pcr3.document_line_id(+)
and pcr3.change_request_group_id(+)=l_change_request_group_id
and pcr3.request_level(+)='LINE'
and pcr3.action_type(+)='DERIVED'
and pcr3.new_amount(+) is not null
and prl.requisition_line_id=pcr4.document_line_id(+)
and pcr4.change_request_group_id(+)=l_change_request_group_id
and pcr4.request_level(+)='LINE'
and pcr4.action_type(+)='CANCELLATION'
and prl.requisition_header_id=l_document_id
AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
and NVL(prl.cancel_flag, 'N')='N';
select sum(decode(pcr1.action_type, 'CANCELLATION', 0,
decode(prl.unit_price, 0, 0,
nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity,prd.req_line_quantity)*
prd.nonrecoverable_tax /
(prl.unit_price*prd.req_line_quantity)))),
nvl(sum(decode(pcr1.action_type, 'CANCELLATION', 0,
nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity,prd.req_line_quantity))), 0)
into l_new_tax_amount, l_new_req_amount
from po_requisition_lines_all prl,
po_req_distributions_all prd,
po_change_requests pcr1,
po_change_requests pcr2
where prl.requisition_line_id=pcr1.document_line_id(+)
and pcr1.change_request_group_id(+)=l_change_request_group_id
and pcr1.request_level(+)='LINE'
and pcr1.change_active_flag(+)='Y'
and prl.requisition_line_id=prd.requisition_line_id
and prd.distribution_id=pcr2.document_distribution_id(+)
and pcr2.change_request_group_id(+)=l_change_request_group_id
and pcr2.change_active_flag(+)='Y'
and prl.requisition_header_id=l_document_id
AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
and NVL(prl.cancel_flag, 'N')='N';
select AUTHORIZATION_STATUS
into l_authorization_status
from po_headers_all
where PO_HEADER_ID = l_doc_id;
select AUTHORIZATION_STATUS
into l_authorization_status
from po_releases_all
where PO_RELEASE_ID = l_doc_id;
select revision_num
into l_doc_revision
from po_headers_all
where po_header_id=l_doc_id;
select revision_num
into l_doc_revision
from po_releases_all
where po_release_id=l_doc_id;
* this function will update the status to 'REJECTED'
*
************************************************************************/
procedure Record_Buyer_Rejection(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_doc_id number;
* this function will update the status to 'BUYER_APP'
*
************************************************************************/
procedure Record_Buyer_Acceptance(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_doc_id number;
* Public Procedure: Insert_Buyer_Action_History
* Effects: workflow procedure, called in workflow
* PORPOCHA (PROCESS_BUYER_RESPONSE)
*
* inserting into action history table buyer's response
*
************************************************************************/
procedure Insert_Buyer_Action_History(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_doc_id number;
select request_status
from po_change_requests
where change_request_group_id=l_change_request_group_id;
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_Buyer_Action_History: 01';
select count(distinct(request_status))
into l_count
from po_change_requests
where change_request_group_id=l_change_request_group_id;
select agent_id
into l_employee_id
from po_headers_all
where PO_HEADER_ID = l_doc_id;
select agent_id
into l_employee_id
from po_releases_all
where PO_RELEASE_ID = l_doc_id;
InsertActionHist(itemtype,itemkey,l_doc_id, l_doc_type,
l_doc_subtype, l_employee_id, l_action, l_note, null);
x_progress := 'PO_ReqChangeRequestWF_PVT.Insert_Buyer_Action_History: 02';
'Insert_Buyer_Action_History',x_progress);
'PO_ReqChangeRequestWF_PVT.Insert_Buyer_Action_History');
END Insert_Buyer_Action_History;
* which is rejected by the buyer by update the related req change
* requests to 'REJECTED'.
*
* it commits when it exits.
*
************************************************************************/
procedure Process_Buyer_Rejection(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 )
is
l_orgid number;
* which is accepted by the buyer by update the related req change
* requests to 'accepted', and cancel the req line.
*
* it commits when it exits.
*
************************************************************************/
procedure Process_Cancel_Acceptance(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 )
is
l_orgid number;
select change_request_id
from po_change_requests
where change_request_group_id=l_change_request_group_id
and request_status='BUYER_APP';
* which is accepted by the buyer by update the related req change
* requests to 'accepted', and update the req line/distribution.
* it will also call MoveChangeToPO to move the changes to PO
*
* it commits when it exits.
*
************************************************************************/
procedure Process_Change_Acceptance(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 )
is
l_orgid number;
select document_header_id, po_release_id
from po_change_requests
where change_request_group_id=l_change_request_group_id
and request_status='BUYER_APP';
select document_header_id, po_release_id
from po_change_requests
where change_request_group_id=l_change_request_group_id
and request_status='REJECTED';
UPDATE po_releases_all
SET authorization_status='APPROVED'
WHERE po_release_id = l_release_id;
UPDATE po_headers_all
SET authorization_status='APPROVED'
WHERE po_header_id = l_header_id;
select pcr3.change_request_id
from po_change_requests pcr1,
po_change_requests pcr2,
po_change_requests pcr3
where pcr2.change_request_group_id=p_change_request_group_id
and pcr2.parent_change_request_id=pcr1.change_request_id
and pcr1.change_request_group_id=pcr3.change_request_group_id
and pcr3.action_type in ('MODIFICATION', 'CANCELLATION')
and pcr3.request_status not in ('ACCEPTED', 'REJECTED');
SELECT wf_item_type, wf_item_key
INTO l_req_item_type, l_req_item_key
FROM po_change_requests
WHERE
change_request_group_id = l_req_change_group_id and rownum=1;
select 'Y'
into l_rco_wf_available
from wf_items
where item_type = l_req_item_type
and item_key = l_req_item_key;
SELECT nvl(sum(nvl(nonrecoverable_tax, 0)), 0)
INTO l_new_tax_currency
FROM po_requisition_lines_all rl,
po_req_distributions_all rd
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.modified_by_agent_flag, 'N') = 'N'
and NVL(rl.cancel_flag, 'N')='N';
SELECT nvl(SUM(nvl(decode(matching_basis, 'AMOUNT', amount, quantity * unit_price), 0)), 0)
into l_new_amount_currency
FROM po_requisition_lines_all
WHERE requisition_header_id = l_document_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT document_header_id
INTO l_document_id
FROM po_change_requests pcr
WHERE pcr.change_request_group_id = l_req_change_group_id and rownum=1;
select NVL(CONTRACTOR_REQUISITION_FLAG, 'N'), NOTE_TO_AUTHORIZER
into l_contractor_req_flag,l_note
from po_requisition_headers_all
where REQUISITION_HEADER_ID = l_document_id;
SELECT gsb.currency_code
INTO l_functional_currency
FROM financials_system_params_all fsp,
gl_sets_of_books gsb
WHERE fsp.set_of_books_id = gsb.set_of_books_id
AND fsp.org_id = l_orgid;
SELECT nvl(SUM(nvl(decode(matching_basis, 'AMOUNT', amount, quantity * unit_price), 0)), 0)
into l_req_amount
FROM po_requisition_lines_all
WHERE requisition_header_id = l_document_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT nvl(sum(nonrecoverable_tax), 0)
into l_tax_amount
FROM po_requisition_lines_all rl,
po_req_distributions_all rd
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.cancel_flag,'N') = 'N'
AND NVL(rl.modified_by_agent_flag, 'N') = 'N';
select pcr1.change_request_group_id
from po_change_requests pcr1, po_change_requests pcr2
where pcr2.change_request_group_id=p_change_request_group_id
and pcr2.parent_change_request_id=pcr1.change_request_id;
select pcr1.change_request_id
from po_change_requests pcr1
where pcr1.change_request_group_id=p_change_request_group_id
and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
select pcr1.change_request_id
from po_change_requests pcr1, po_change_requests pcr2
where pcr2.change_request_group_id=p_change_request_group_id
and pcr1.parent_change_request_id=pcr2.change_request_id
and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
select change_request_group_id, wf_item_type, wf_item_key
from po_change_requests
where document_header_id=l_document_id
and document_type=l_document_type
and initiator='REQUESTER'
and request_status in ('PENDING', 'BUYER_APP');
select change_request_group_id, wf_item_type, wf_item_key
from po_change_requests
where po_release_id=l_document_id
and document_type=l_document_type
and initiator='REQUESTER'
and request_status in ('PENDING', 'BUYER_APP');
select change_request_group_id
from po_change_requests
where change_request_id in
(select parent_change_request_id
from po_change_requests
where change_request_group_id=l_change_request_group_id);
select nvl(pcr.requester_id, por.preparer_id)
from po_change_requests pcr, po_requisition_headers_all por
where pcr.change_request_group_id=l_change_request_group_id
and pcr.document_header_id=por.requisition_header_id;
select change_request_id
from po_change_requests
where change_request_group_id=p_change_request_group_id
and request_status='PENDING';
UpdatePODocHeaderTables(l_document_type, l_document_id);
* when the po is approved, update the status of the corresponding
* PO change requests to ACCEPTED
*
************************************************************************/
procedure Record_PO_Approval(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_orgid number;
* when the po is rejected, update the status of the corresponding
* PO change requests to REJECTED
*
************************************************************************/
procedure Record_PO_Rejection(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 )is
l_orgid number;
select change_request_id
from po_change_requests
where document_header_id=l_document_id
and request_status in ('BUYER_APP', 'PENDING')
and initiator='REQUESTER'
and document_type='PO';
select change_request_id
from po_change_requests
where po_release_id=l_document_id
and request_status in ('BUYER_APP', 'PENDING')
and initiator='REQUESTER'
and document_type='RELEASE';
select PO_REQUESTER_CHANGE_WF_S.nextval into l_seq from dual;
select change_request_id
from po_change_requests
where change_request_group_id=group_id
and request_status in ('NEW', 'MGR_PRE_APP');
select pcr1.change_request_id, pcr1.document_header_id,
pcr1.action_type, pcr1.change_request_group_id,
pcr1.wf_item_type, pcr1.wf_item_key
from po_change_requests pcr1
where pcr1.document_type='REQ'
and pcr1.document_line_id=requisition_line_id
and pcr1.action_type <>'DERIVED'
and pcr1.request_status in ('NEW', 'MGR_PRE_APP', 'MGR_APP')
and not exists
(select pcr2.change_request_id
from po_change_requests pcr2
where pcr2.parent_change_request_id=pcr1.change_request_id);
update po_requisition_lines_all
set cancel_flag='Y'
where requisition_line_id=p_CanceledReqLineIDs_tbl(l_index);
update po_change_requests
set request_status='ACCEPTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_CANCELLED')
where change_request_id=l_change_request_id;
UPDATE po_requisition_headers_all h
SET h.AUTHORIZATION_STATUS = 'CANCELLED'
WHERE h.REQUISITION_HEADER_ID = l_document_id
AND NOT EXISTS
(SELECT 'UNCANCELLED LINE EXISTS'
FROM po_requisition_lines_all prl
WHERE prl.requisition_header_id = l_document_id
AND NVL(prl.cancel_flag,'N') = 'N'
);
update po_change_requests
set request_status='REJECTED',
change_active_flag='N',
response_date=sysdate,
response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_CANCELLED')
where change_request_group_id=l_change_request_group_id
and document_line_id=p_CanceledReqLineIDs_tbl(l_index);
PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(
itemtype=>l_wf_item_type,
itemkey=>l_wf_item_key,
x_action=>l_action,
x_req_header_id=>l_document_id,
x_last_approver=>l_result,
x_note=>l_note);
select max(change_request_group_id)
from po_change_requests
where document_header_id = p_req_header_id
and initiator='REQUESTER'
and request_status='NEW';
SELECT to_char(p_req_header_id) || '-'
||to_char(l_change_request_group_id)||'-'
|| to_char(PO_REQUESTER_CHANGE_WF_S.nextval)
INTO p_item_key
FROM sys.dual;
select preparer_id, segment1
into l_preparer_id, l_req_num
from po_requisition_headers_all
where requisition_header_id= p_req_header_id;
select pcr.wf_item_type, pcr.wf_item_key
from po_change_requests pcr, po_change_requests pcr2
where pcr2.change_request_group_id=l_change_request_group_id
and pcr.change_request_id=pcr2.parent_change_request_id
and pcr.wf_item_type is not null;
select PO_REQUESTER_CHANGE_WF_S.nextval into l_count from dual;
select wfn.status, wfn.notification_id
from wf_item_activity_statuses_v was, wf_notifications wfn
where was.item_type=l_item_type
and was.item_key=l_item_key
and was.activity_name=activity
and was.notification_id=wfn.notification_id;
update po_change_requests
set request_status=p_acceptance_flag,
change_active_flag=decode(p_acceptance_flag, 'REJECTED', 'N', 'Y'),
response_date=sysdate,
response_reason=p_response_reason,
responded_by=p_responded_by
where change_request_id=p_change_request_id;
select wf_item_type, wf_item_key
into l_item_type, l_item_key
from po_change_requests
where change_request_id=p_change_request_id;
update wf_notifications
set status='CLOSED'
where notification_id=l_notification_id;
select pcr.wf_item_type, pcr.wf_item_key
from po_change_requests pcr, po_change_requests pcr2
where pcr2.change_request_group_id=l_change_request_group_id
and pcr.change_request_id=pcr2.parent_change_request_id
and pcr.wf_item_type is not null;
select PO_REQUESTER_CHANGE_WF_S.nextval into l_count from dual;
select 'Y'
into l_parent_wf_available
from wf_items
where item_type = l_parent_item_type
and item_key = l_parent_item_key;
select max(change_request_id)
into l_change_request_id
from po_change_requests
where po_release_id=l_document_id
and request_status in ('BUYER_APP', 'PENDING')
and initiator='REQUESTER'
and document_type='RELEASE'
and action_type='CANCELLATION';
select max(change_request_id)
into l_change_request_id
from po_change_requests
where document_header_id=l_document_id
and request_status in ('BUYER_APP', 'PENDING')
and initiator='REQUESTER'
and document_type='PO'
and action_type='CANCELLATION';
select pcr.document_header_id,
pcr.document_line_id,
pcr.document_line_location_id,
pcr.old_amount,
pol.amount,
pol.quantity,
pcr.old_price,
pll.amount,
pll.quantity,
pll.quantity_cancelled,
pll.price_override,
pol.matching_basis,
pcr.new_amount,
pcr.new_price,
pcr.action_type,
pol.item_id,
pll.unit_meas_lookup_code,
pol.unit_price
FROM po_change_requests pcr, po_lines_all pol,
po_line_locations_all pll
WHERE pcr.change_request_group_id= p_change_request_group_id
AND pcr.request_status IN ('PENDING', 'ACCEPTED')
AND pcr.document_header_id=pol.po_header_id
AND pcr.document_line_id=pol.po_line_id
AND nvl(pcr.document_line_location_id,-1)=pll.line_location_id(+)
AND pcr.request_level<>'DISTRIBUTION';
SELECT sob.currency_code
INTO l_functional_currency_code
FROM gl_sets_of_books sob, financials_system_params_all fsp
WHERE fsp.org_id = p_org_id
AND fsp.set_of_books_id = sob.set_of_books_id;
select poh.rate
into l_rate
from po_headers_all poh
where poh.po_header_id in (
select document_header_id from po_change_requests
where change_request_group_id = p_change_request_group_id ) ;
SELECT 1
into l_shipment_chg_exists_amt
FROM po_change_requests pcr
WHERE pcr.change_request_group_id= p_change_request_group_id
AND pcr.request_status IN ('PENDING', 'ACCEPTED')
AND pcr.document_header_id= l_header_id
AND pcr.document_line_id= l_line_id
AND pcr.document_line_location_id IS NOT NULL
AND pcr.request_level='SHIPMENT';
SELECT 1
into l_shipment_chg_exists_qty
FROM po_change_requests pcr
WHERE pcr.change_request_group_id= p_change_request_group_id
AND pcr.request_status IN ('PENDING', 'ACCEPTED')
AND pcr.document_header_id= l_header_id
AND pcr.document_line_id= l_line_id
AND pcr.document_line_location_id IS NOT NULL
AND pcr.request_level='SHIPMENT';
select creation_date, currency_code
into l_order_date, l_po_currency
from po_headers_all
where PO_HEADER_ID = l_doc_id;
select pr.creation_date, ph.currency_code
into l_order_date, l_po_currency
from po_releases_all pr, po_headers_all ph
where pr.po_release_id = l_doc_id
and pr.po_header_id = ph.po_header_id;
select change_request_group_id
INTO l_po_request_group_id
FROM po_change_requests
WHERE parent_change_request_id = l_change_request_group_id
and rownum=1;
SELECT document_header_id, document_revision_num, document_type, po_release_id
INTO l_document_header_id, l_document_revision_num, l_document_type, l_release_id
FROM po_change_requests
WHERE change_request_group_id = l_po_request_group_id
AND rownum=1;
SELECT change_request_group_id
INTO l_po_chg_request_group_id
FROM po_change_requests
WHERE parent_change_request_id = l_change_request_group_id
AND rownum=1;
* Kickoff_POChange_WF cannot be updated because of upgrade issues.
* That is why we created this new procedure to handle the new
* functionality.
************************************************************************/
PROCEDURE Start_POChange_WF( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2 )
IS
l_po_chg_request_group_id number;
PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,'PO_CO_Tolerances_Grp.get_tolerances API failed:' || l_return_status || ' ' || l_msg_data);
SELECT poh.document_creation_method, poh.org_id
INTO l_creation_method, l_po_org_id
FROM
po_headers_all poh,
po_change_requests pcr
WHERE poh.po_header_id =pcr.document_header_id
AND pcr.change_request_group_id = l_po_chg_group_id
AND rownum=1;
SELECT por.document_creation_method, por.org_id
INTO l_creation_method, l_po_org_id
FROM
po_releases_all por,
po_change_requests pcr
WHERE por.po_release_id = pcr.po_release_id
AND pcr.change_request_group_id = l_po_chg_group_id
AND rownum = 1;
SELECT MIN(po_change.change_request_group_id)
INTO l_next_po_grp_id
FROM
po_change_requests po_change,
po_change_requests req_change
WHERE
po_change.parent_change_request_id = req_change.change_request_id AND
req_change.change_request_group_id = l_change_request_grp_id AND
po_change.change_request_group_id > l_po_chg_request_group_id
ORDER BY po_change.change_request_group_id;
SELECT document_type, document_revision_num,
decode(document_type, 'RELEASE', po_release_id,document_header_id)
into l_po_document_type, l_po_document_rev, l_po_document_id
FROM po_change_requests
WHERE change_request_group_id = l_next_po_grp_id AND rownum=1;
SELECT document_type, decode(document_type, 'RELEASE', po_release_id, document_header_id), document_num
INTO l_document_type, l_document_id, l_document_number
FROM po_change_requests
WHERE change_request_group_id = l_po_change_request_group_id
AND rownum=1;
SELECT agent_id, creation_date
INTO l_buyer_id, l_order_date
FROM po_headers_all
WHERE PO_HEADER_ID = l_document_id;
SELECT agent_id, creation_date
INTO l_buyer_id, l_order_date
FROM po_releases_all
WHERE PO_RELEASE_ID = l_document_id;
InsertActionHist(itemtype, itemkey, l_document_id, l_document_type,
l_document_subtype, l_buyer_id, 'ACCEPT', '', null);
SELECT document_header_id, po_release_id
INTO l_po_header_id, l_po_release_id
FROM po_change_requests
WHERE change_request_group_id=l_po_change_request_group_id
and request_status='BUYER_APP' and rownum=1;
SELECT currency_code
INTO l_po_currency
FROM po_headers_all
WHERE PO_HEADER_ID = l_po_doc_id;
SELECT ph.currency_code
INTO l_po_currency
FROM po_releases_all pr, po_headers_all ph
WHERE pr.po_release_id = l_po_doc_id
and pr.po_header_id = ph.po_header_id;
SELECT 'N'
INTO l_return_val
FROM dual
WHERE exists (
SELECT 'N'
FROM
po_change_requests pcr, -- for quantity/amount change
po_change_requests pcr1, -- for unit price change
po_lines_all pl,
po_distributions_all pod
WHERE pl.po_line_id = pod.po_line_id
AND pcr.change_request_group_id = p_pochggrp_id
AND pcr.action_type(+) = 'MODIFICATION'
AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
AND pcr.request_level(+) = 'DISTRIBUTION'
AND pcr.initiator(+) = 'REQUESTER'
AND pcr.document_distribution_id(+) = pod.po_distribution_id
AND pcr1.change_request_group_id(+) = p_pochggrp_id
AND pcr1.document_line_id(+) = pl.po_line_id
AND pcr1.action_type(+) = 'MODIFICATION'
AND pcr1.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
AND pcr1.request_level (+) = 'LINE'
AND pcr1.initiator(+) = 'REQUESTER'
AND pcr1.new_price(+) IS NOT NULL
GROUP BY pcr.document_line_location_id
HAVING
((PO_RCOTOLERANCE_PVT.changes_within_tol(
sum(decode(pl.matching_basis, 'AMOUNT', pod.amount_ordered, pl.unit_price * (pod.quantity_ordered-nvl(pod.quantity_cancelled,0)))),
sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr.new_amount, pod.amount_ordered), nvl(pcr.new_quantity,pod.quantity_ordered)*nvl(pcr1.new_price, pl.unit_price))),
p_tolerances_tab(TOL_SHIPAMT_IND).max_increment,
p_tolerances_tab(TOL_SHIPAMT_IND).max_decrement,
p_tolerances_tab(TOL_SHIPAMT_AMT_IND).max_increment,
p_tolerances_tab(TOL_SHIPAMT_AMT_IND).max_decrement) = 'N')
OR
(PO_RCOTOLERANCE_PVT.change_within_tol_percent(
sum(pod.quantity_ordered-nvl(pod.quantity_cancelled,0)),
sum(nvl(pcr.new_quantity, pod.quantity_ordered-nvl(pod.quantity_cancelled, 0))),
p_tolerances_tab(TOL_SHIPQTY_IND).max_increment,
p_tolerances_tab(TOL_SHIPQTY_IND).max_decrement) = 'N')));
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value:' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value(No data Found):Y');
select distinct pcr.document_line_id, document_type
from po_change_requests pcr,
po_lines_all pol
where change_request_group_id = p_pochggrp_id
and pol.po_line_id = pcr.document_line_id
and action_type <> 'DERIVED'
and request_status not in ('ACCEPTED', 'REJECTED')
and pcr.initiator(+) = 'REQUESTER'
and pcr.request_level <> 'DISTRIBUTION';
SELECT 'N'
INTO l_return_val
FROM dual
WHERE exists (
SELECT 'N'
FROM po_change_requests pcr
WHERE change_request_group_id = p_pochggrp_id
AND action_type='MODIFICATION'
AND request_status not in ('ACCEPTED', 'REJECTED')
AND request_level='SHIPMENT'
AND
PO_RCOTOLERANCE_PVT.change_within_tol_date(
old_need_by_date,
new_need_by_date,
p_tolerances_tab(TOL_NEEDBY_IND).max_increment,
p_tolerances_tab(TOL_NEEDBY_IND).max_decrement) = 'N');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value(need by date check):' || l_return_val);
SELECT 'N'
INTO l_return_val
FROM dual
WHERE exists (
SELECT 'N'
FROM po_change_requests pcr
WHERE change_request_group_id = p_pochggrp_id
AND action_type='MODIFICATION'
AND request_status not in ('ACCEPTED', 'REJECTED')
AND request_level='LINE'
AND (
(PO_RCOTOLERANCE_PVT.change_within_tol_date(
old_start_date,
new_start_date,
p_tolerances_tab(TOL_STARTDATE_IND).max_increment,
p_tolerances_tab(TOL_STARTDATE_IND).max_decrement) = 'N')
OR
(PO_RCOTOLERANCE_PVT.change_within_tol_date(
old_expiration_date,
new_expiration_date,
p_tolerances_tab(TOL_ENDDATE_IND).max_increment,
p_tolerances_tab(TOL_ENDDATE_IND).max_decrement) = 'N')
OR
(PO_RCOTOLERANCE_PVT.change_within_tol_percent(
old_price,
new_price,
p_tolerances_tab(TOL_UNITPRICE_IND).max_increment,
p_tolerances_tab(TOL_UNITPRICE_IND).max_decrement) = 'N'))
);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value(start/end date check):' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value(line amount check):' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value(line level check):' || l_return_val);
select distinct document_type
into l_document_type
from po_change_requests
where change_request_group_id = p_pochggrp_id;
SELECT PO_RCOTOLERANCE_PVT.changes_within_tol(
sum(decode(pl.matching_basis,
'AMOUNT',
pod.amount_ordered,
pl.unit_price * (pod.quantity_ordered-nvl(pod.quantity_cancelled,0)))),
sum(decode(pcr2.action_type, 'CANCELLATION',
0,
decode(pl.matching_basis,
'AMOUNT',
decode(pcr2.action_type,
'CANCELLATION',
0,
nvl(pcr.new_amount, pod.amount_ordered)),
nvl(pcr.new_quantity, pod.quantity_ordered) * nvl(pcr1.new_price, pl.unit_price)))),
p_tolerances_tab(TOL_POTOTAL_IND).max_increment,
p_tolerances_tab(TOL_POTOTAL_IND).max_decrement,
p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_increment,
p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_decrement) INTO l_return_val
FROM
po_change_requests pcr,
po_change_requests pcr1,
po_change_requests pcr2,
po_lines_all pl,
po_distributions_all pod
WHERE pl.po_line_id = pod.po_line_id
AND pcr.change_request_group_id(+) = p_pochggrp_id
AND pcr.action_type(+) = 'MODIFICATION'
AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
AND pcr.request_level(+) = 'DISTRIBUTION'
AND pcr.initiator(+) = 'REQUESTER'
AND pcr.document_distribution_id(+) = pod.po_distribution_id
-- AND pcr.document_line_id = pcr1.document_line_id
AND pcr1.change_request_group_id(+) = p_pochggrp_id
AND pl.po_header_id = p_poheader_id
AND pcr1.document_line_id(+) = pl.po_line_id
AND pcr1.action_type(+) = 'MODIFICATION'
AND pcr1.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
AND pcr1.request_level (+) = 'LINE'
AND pcr1.initiator(+) = 'REQUESTER'
AND pcr1.new_price(+) IS NOT NULL
AND pcr2.change_request_group_id(+) = p_pochggrp_id
AND pcr2.document_line_id(+) = pl.po_line_id
AND pcr2.action_type(+) = 'CANCELLATION';
SELECT
sum ( nvl(pcr.old_quantity, nvl(pll.quantity,0))
*pll.price_override ),
sum ( decode ( pcr.document_line_location_id, null, 0,
decode (pcr.action_type , 'CANCELLATION', 0,
PO_ReqChangeRequestNotif_PVT.get_goods_shipment_new_amount
( pol.org_id,p_pochggrp_id,pol.po_line_id,
pol.item_id,pll.unit_meas_lookup_code,
nvl(pcr.old_price, nvl(pll.price_override, pol.unit_price)),
pll.line_location_id)) ) )
into l_old_amount_release, l_new_amount_release
FROM po_change_requests pcr,
po_lines_all pol,
po_line_locations_all pll
WHERE pcr.change_request_group_id= p_pochggrp_id
AND pcr.po_release_id = p_poheader_id
AND pcr.document_line_id = pol.po_line_id
AND pcr.request_status NOT IN ('ACCEPTED', 'REJECTED')
AND pcr.document_line_location_id =pll.line_location_id (+)
AND pcr.request_level<>'DISTRIBUTION'
AND pcr.initiator(+) = 'REQUESTER';
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value:' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_api_name || ' Return Value(No Data Found):Y');
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_api_name || '.Begin');
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, 'l_pochggrp_id:' || l_pochggrp_id || ' l_poheader_id:' || l_poheader_id);
select poh.currency_code, poh.org_id
into l_po_currency_code, l_org_id
from po_headers_all poh, po_releases_all pr
where pr.po_release_id = l_poheader_id
and poh.po_header_id = pr.po_header_id;
select poh.currency_code, poh.org_id
into l_po_currency_code, l_org_id
from po_headers_all poh
where poh.po_header_id = l_poheader_id;
SELECT sob.currency_code
INTO l_functional_currency_code
FROM gl_sets_of_books sob, financials_system_params_all fsp
WHERE fsp.org_id = l_org_id
AND fsp.set_of_books_id = sob.set_of_books_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, 'doc type: '||l_po_doc_type|| ' func currency code: '||l_functional_currency_code||' po curr code: '||l_po_currency_code );
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, 'Return Value(shipmnt_level_changes_wthn_tol):' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, 'Return Value(line_level_changes_wthn_tol):' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, 'Return Value(doc_level_changes_wthn_tol):' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_api_name || ' Return Value:' || l_return_val);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress || 'SQL ERROR:' || sqlerrm);
SELECT MIN(req_change.change_request_group_id)
INTO l_next_req_grp_id
FROM
po_change_requests po_change,
po_change_requests req_change
WHERE
po_change.change_request_group_id = l_po_request_group_id AND
po_change.parent_change_request_id = req_change.change_request_id AND
req_change.change_request_group_id > l_req_request_group_id;
SELECT document_header_id, document_num
INTO l_document_id, l_document_num
FROM po_change_requests
WHERE change_request_group_id = l_next_req_grp_id AND rownum=1;
SELECT po_requester_change_wf_s.nextval INTO l_count FROM dual;
select document_line_location_id,action_type,
decode(pol.matching_basis, 'AMOUNT',
( nvl(pll.amount,0)-nvl(pll.amount_cancelled,0) ),
( nvl(pll.quantity,0)- nvl(pll.quantity_cancelled,0)) * pol.unit_price
)
from po_change_requests,
po_lines_all pol,
po_line_locations_all pll
where change_request_group_id = p_chg_request_grp_id
and document_line_id = p_po_line_id
and document_line_location_id = pll.line_location_id
and pol.po_line_id = document_line_id
and request_status not in ('ACCEPTED', 'REJECTED')
and request_level = 'SHIPMENT'
and action_type <> 'DERIVED' ;
select matching_basis,org_id,item_id,unit_price
into l_po_matching_basis,l_po_org_id,l_item_id,l_unit_price
from po_lines_all
where po_line_id = p_po_line_id;
select sum( decode(l_po_matching_basis, 'AMOUNT',
( nvl(pll.amount,0)-nvl(pll.amount_cancelled,0) ),
( nvl(pll.quantity,0)- nvl(pll.quantity_cancelled,0)) * pol.unit_price
)
)
into l_old_amount
FROM po_lines_all pol,
po_line_locations_all pll
WHERE pol.po_line_id = p_po_line_id
AND pol.po_line_id = pll.po_line_id;
SELECT pcr.new_price,pcr.new_price
into l_pcr_new_price,l_new_price
FROM po_change_requests pcr
WHERE pcr.change_request_group_id = p_chg_request_grp_id
AND pcr.document_line_id = p_po_line_id
AND pcr.action_type(+) = 'MODIFICATION'
AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
AND pcr.initiator(+) = 'REQUESTER'
AND pcr.request_level = 'LINE'
AND pcr.new_price is not null;
select pol.unit_price
into l_new_price
from po_lines_all pol
where pol.po_line_id = p_po_line_id;
select (pcr.new_amount - pll.amount)
into l_shipmt_amt_increase
from po_change_requests pcr,
po_line_locations_all pll
where pcr.change_request_group_id = p_chg_request_grp_id
and pcr.document_line_location_id = l_pcr_line_loc_id
and pll.line_location_id = l_pcr_line_loc_id
and pcr.request_level = 'SHIPMENT'
and pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
and pcr.initiator(+) = 'REQUESTER'
and pcr.new_amount is not null;
SELECT pcr.new_quantity,pcr.new_quantity,pll.unit_meas_lookup_code,pll.price_override
into l_pcr_new_quantity,l_new_quantity,l_unit_lookup_code,l_price_override
FROM po_change_requests pcr,
po_line_locations_all pll
WHERE pcr.change_request_group_id = p_chg_request_grp_id
AND pcr.document_line_location_id = l_pcr_line_loc_id
AND pll.line_location_id = l_pcr_line_loc_id
AND pcr.action_type(+) = 'MODIFICATION'
AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
AND pcr.initiator(+) = 'REQUESTER'
AND pcr.request_level = 'SHIPMENT'
AND pcr.new_quantity is not null;
select (pll.quantity - pll.quantity_cancelled)
into l_new_quantity
from po_line_locations_all pll
where pll.line_location_id = l_pcr_line_loc_id;
select ( (l_new_price * l_new_quantity) -
nvl(pll.price_override,pol.unit_price) * pll.quantity )
into l_shipmt_amt_increase
from po_lines_all pol,
po_line_locations_all pll
where pll.line_location_id = l_pcr_line_loc_id
and pol.po_line_id = pll.po_line_id ;
select decode(pol.matching_basis, 'AMOUNT',pol.amount,
(nvl(pcr.new_price,pol.unit_price) * pol.quantity ))
into l_new_amount
from po_change_requests pcr,
po_lines_all pol
where pcr.change_request_group_id = p_chg_request_grp_id
and pol.po_line_id = p_po_line_id
and pcr.document_line_id = p_po_line_id
and pcr.request_level = 'LINE'
and pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
and pcr.initiator(+) = 'REQUESTER' ;
SELECT MAX(change_request_group_id)
FROM po_change_requests
WHERE document_header_id = p_req_header_id
AND initiator = 'REQUESTER'
AND request_status = 'NEW';
SELECT DISTINCT document_line_id
FROM po_change_requests
WHERE change_request_group_id = grp_id;
SELECT to_char(p_req_header_id) || '-'
|| to_char(l_change_request_group_id) || '-'
|| to_char(po_requester_change_wf_s.nextval)
INTO p_item_key
FROM sys.dual;
SELECT preparer_id, segment1,TYPE_LOOKUP_CODE
INTO l_preparer_id, l_req_num,p_document_subtype
FROM po_requisition_headers_all
WHERE requisition_header_id = p_req_header_id;
SELECT prl.requisition_header_id,
prl.requisition_line_id,
prl.line_num,
pcr.old_quantity,
pcr.new_quantity,
pcr.old_need_by_date,
pcr.new_need_by_date,
pcr.action_type
FROM po_change_requests pcr,
po_requisition_lines_all prl
WHERE pcr.change_request_group_id = l_change_request_group_id
AND pcr.request_status = 'MGR_APP'
AND pcr.document_line_id = prl.requisition_line_id
ORDER BY prl.line_num;
SELECT prl.requisition_header_id,
prl.requisition_line_id
FROM po_change_requests pcr,
po_requisition_lines_all prl
WHERE pcr.change_request_group_id = l_change_request_group_id
AND pcr.request_status = 'REJECTED'
AND pcr.document_line_id = prl.requisition_line_id
ORDER BY prl.line_num;
SELECT min(CHANGE_REQUEST_ID)
into L_CHANGE_REQUEST_ID
FROM po_change_requests
WHERE change_request_group_id = l_change_request_group_id
AND request_status = 'ACCEPTED';
- for failed lines update change request as rejected and return
= rollback to save point
- for successful lines update the change request as accepted
= adjust encumbrance
= update po req tables
= update mtl_supply
*/
SAVEPOINT convertintosochange_sp;
select requisition_header_id
into l_document_header_id
from po_requisition_lines_all
where requisition_line_id = p_document_line_id;
UPDATE po_change_requests
SET request_status = 'ACCEPTED'
WHERE change_request_group_id = p_chn_request_group_id
AND document_header_id = l_document_header_id
AND document_line_id = p_document_line_id ;
update_req_line_date_changes( p_req_line_id=>p_document_line_id,
p_need_by_date=> p_new_need_by_date,
x_return_status =>l_return_status);
update_reqline_quan_changes(p_req_line_id=>p_document_line_id,
p_delta_quantity=> l_delta_quantity,
x_return_status =>l_return_status);
- for successful lines update the change request as accepted and failed lines update change request as rejected
- update po req tables */
l_progress := '012';
UPDATE po_change_requests
SET request_status = 'ACCEPTED'
WHERE change_request_group_id = p_chn_request_group_id
AND document_header_id = l_document_header_id
AND document_line_id = p_document_line_id ;
po_debug.debug_stmt(l_log_head, l_progress,'UPDATED po_change_requests');
po_debug.debug_stmt(l_log_head, l_progress,'update the change request as rejected');
UPDATE po_change_requests
SET request_status = 'REJECTED',
change_active_flag = 'N',
request_reason=l_msg_data
WHERE change_request_group_id = p_chn_request_group_id
AND document_header_id = l_document_header_id
AND document_line_id = p_document_line_id ;
The procedure updates the requisition line with changes
of quntity.
It retrives the existing quantity and adds the delta quntity
to compute the new quantity
*/
PROCEDURE update_reqline_quan_changes(p_req_line_id IN NUMBER,
p_delta_quantity IN NUMBER,
p_uom IN VARCHAR2 default null,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_mtl_quantity number;
l_api_name CONSTANT VARCHAR(30) := 'update_reqline_quan_changes';
select DISTRIBUTION_ID
from PO_REQ_DISTRIBUTIONS_ALL
where REQUISITION_LINE_ID= req_line_id;
SELECT -- any quantity change
prda.distribution_id,
prla.unit_price,
prla.quantity
FROM
po_req_distributions_all prda,
po_requisition_lines_all prla
WHERE
prla.requisition_line_id = req_line_id AND
prla.requisition_line_id = prda.requisition_line_id;
Step 2: Update the req line and dist with the quantity changes
Step 3: Update the mtl_supply by the PO API
*/
-- Step 1: ADJUST the encumberance
l_progress := '001';
select prh.preparer_id into l_preparer_id
from po_requisition_headers_all prh,
po_requisition_lines_all prl
where prl.requisition_line_id = p_req_line_id
and prl.requisition_header_id = prh.requisition_header_id;
l_distribution_id_tbl.delete;
UPDATE po_encumbrance_gt
SET
amount_ordered = l_new_amount,
quantity_ordered = l_new_quantity,
price = l_new_price,
nonrecoverable_tax = l_new_tax
WHERE
distribution_id = l_req_dist_id AND
adjustment_status = po_document_funds_grp.g_adjustment_status_new;
UPDATE po_requisition_lines_all
SET quantity = quantity + p_delta_quantity
WHERE requisition_line_id = p_req_line_id ;
UPDATE po_req_distributions_all
SET req_line_quantity = req_line_quantity + p_delta_quantity
WHERE requisition_line_id = p_req_line_id ;
select quantity into l_mtl_quantity
from mtl_supply
where supply_type_code = 'REQ'
and req_line_id = p_req_line_id;
, p_action => 'Update_Req_Line_Qty'
, p_recreate_flag => FALSE
, p_qty => l_mtl_quantity
, p_receipt_date => NULL
);
po_debug.debug_STmt(l_log_head, l_progress, 'Updated the req line and dist and mtl_supply with the quantity changes');
po_debug.debug_STmt(l_log_head, l_progress, 'Returning from update_reqline_quan_changes');
END update_reqline_quan_changes;
The procedure updates the requisition line with changes
of need by date
It retrives the existing quantity and adds the delta quntity
to compute the new quantity
*/
PROCEDURE update_req_line_date_changes(p_req_line_id IN NUMBER,
p_need_by_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2)
IS
x_progress varchar2(3);
l_api_name VARCHAR2(50) := 'update_req_line_date_changes';
Algorithm : Step 1: Update the req line and dist with the need by date changes
Step 2: Update the mtl_supply by the PO API
*/
IF (p_need_by_date IS NOT NULL) THEN
x_progress := '001';
UPDATE po_requisition_lines_all
SET need_by_date = p_need_by_date
WHERE requisition_line_id = p_req_line_id ;
, p_action => 'Update_Req_Line_Date'
, p_recreate_flag => FALSE
, p_qty => NULL
, p_receipt_date => p_need_by_date
);
END update_req_line_date_changes;
UPDATE po_requisition_lines_all
SET cancel_flag = 'Y',
-- quantity_cancelled = p_old_quantity,
cancel_date = SYSDATE
WHERE requisition_line_id = p_req_line_id ;
Select REQUISITION_HEADER_ID into l_document_id
from po_requisition_lines_all
where requisition_line_id = p_req_line_id ;
UPDATE po_requisition_headers_all h
SET h.AUTHORIZATION_STATUS = 'CANCELLED'
WHERE h.REQUISITION_HEADER_ID = l_document_id
AND NOT EXISTS
(SELECT 'UNCANCELLED LINE EXISTS'
FROM po_requisition_lines_all prl
WHERE prl.requisition_header_id = l_document_id
AND NVL(prl.cancel_flag,'N') = 'N'
);
po_debug.debug_stmt(l_log_head, x_progress,'Mtl_supply deleted and cancelling funds');
po_debug.debug_stmt(l_log_head, x_progress,'Mtl_supply deleted and cancelling funds');
SELECT mp.EMPLOYEE_ID
into l_planner_id
FROM po_change_requests pcr,
po_requisition_lines_all prl,
mtl_system_items_b mi,
financials_system_params_all fsp,
mtl_planners mp
WHERE pcr.change_request_group_id =l_change_request_group_id
AND pcr.change_request_id =L_CHANGE_REQUEST_ID
AND pcr.request_status = 'ACCEPTED'
AND pcr.DOCUMENT_LINE_ID = prl.requisition_line_id
and prl.org_id = fsp.org_id
AND prl.ITEM_ID = mi.INVENTORY_ITEM_ID
AND mi.organization_id = fsp.inventory_organization_id
and mi.PLANNER_CODE = mp.planner_code
AND mi.organization_id = mp.organization_id
and prl.source_type_code = 'INVENTORY'
and mp.EMPLOYEE_ID NOT IN (planners already notified);
SELECT mp.EMPLOYEE_ID
FROM po_change_requests pcr,
po_requisition_lines_all prl,
mtl_system_items_b mi,
financials_system_params_all fsp,
mtl_planners mp
WHERE pcr.change_request_group_id =:1
AND pcr.change_request_id =:2
AND pcr.request_status = ''ACCEPTED''
AND pcr.DOCUMENT_LINE_ID = prl.requisition_line_id
and prl.org_id = fsp.org_id
AND prl.ITEM_ID = mi.INVENTORY_ITEM_ID
AND mi.organization_id = fsp.inventory_organization_id
and mi.PLANNER_CODE = mp.planner_code
AND mi.organization_id = mp.organization_id
and prl.source_type_code = ''INVENTORY''
';
SELECT min(CHANGE_REQUEST_ID)
into L_NEW_CHANGE_REQUEST_ID
FROM po_change_requests
WHERE change_request_group_id = l_change_request_group_id
AND request_status = 'ACCEPTED'
and CHANGE_REQUEST_ID > l_old_change_request_id;