[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT change_request_id
FROM po_change_requests PCR
WHERE request_status = 'BUYER_APP'
AND change_request_group_id = p_change_request_group_id
AND request_level = 'SHIPMENT'
AND initiator = 'SUPPLIER'
AND 1 = (SELECT count(*)
FROM po_distributions POD
WHERE POD.line_location_id =
NVL(PCR.document_line_location_id, PCR.parent_line_location_id));
INSERT INTO po_change_requests
( Change_Request_Group_Id, Change_Request_Id, Initiator, Action_Type, Request_Reason,
Document_Type, Request_Level,
Request_Status, Document_Header_Id, Document_Num, Document_Revision_Num, Created_By, Creation_Date,
Last_Updated_By, Last_Update_Date, Last_Update_Login, Vendor_Id, Vendor_Site_Id, Vendor_Contact_Id,
Request_Expiration_Date, PO_Release_Id, Document_Line_Id, Document_Line_Number, Document_Line_Location_Id,
Document_Shipment_Number, Parent_Line_Location_Id, Document_distribution_id, Document_distribution_Number,
Old_Quantity, New_Quantity, Old_Promised_Date, New_Promised_Date,
Old_Supplier_Part_Number, New_Supplier_Part_Number, Old_Price, New_Price,
Old_Need_By_Date, New_Need_By_Date,
Old_Supplier_Reference_Number, New_Supplier_Reference_Number,
Requester_Id, Responded_by, Response_date, Response_Reason,
Old_Currency_Unit_Price, New_Currency_Unit_Price,
Recoverable_Tax, Nonrecoverable_Tax,
WF_Item_type, WF_Item_key, Parent_Change_Request_Id, Validation_Error,
Approval_Required_Flag, Old_Supplier_Order_Number, New_Supplier_Order_Number,
Old_Supplier_Order_Line_Number, New_Supplier_Order_Line_Number,Old_Amount,New_Amount) -- Added Amount for FPS Changes
SELECT
PCR.Change_Request_Group_Id,
PO_CHG_REQUEST_SEQ.nextval, -- CHANGE_REQUEST_ID
PCR.Initiator, PCR.Action_Type, PCR.Request_Reason,
PCR.Document_Type,
'DISTRIBUTION', -- REQUEST_LEVEL
PCR.Request_Status, PCR.Document_Header_Id, PCR.Document_Num,
PCR.Document_Revision_Num, PCR.Created_By, PCR.Creation_Date,
PCR.Last_Updated_By, PCR.Last_Update_Date, PCR.Last_Update_Login,
PCR.Vendor_Id, PCR.Vendor_Site_Id, PCR.Vendor_Contact_Id,
PCR.Request_Expiration_Date, PCR.PO_Release_Id, PCR.Document_Line_Id,
PCR.Document_Line_Number, PCR.Document_Line_Location_Id,
PCR.Document_Shipment_Number, PCR.Parent_Line_Location_Id,
Decode(PCR.Parent_Line_Location_Id, null, POD.po_distribution_id, null),
POD.distribution_num,
PCR.Old_Quantity, PCR.New_Quantity, PCR.Old_Promised_Date, PCR.New_Promised_Date,
PCR.Old_Supplier_Part_Number, PCR.New_Supplier_Part_Number,
PCR.Old_Price, PCR.New_Price,
PCR.Old_Need_By_Date, PCR.New_Need_By_Date,
PCR.Old_Supplier_Reference_Number, PCR.New_Supplier_Reference_Number,
PCR.Requester_Id, PCR.Responded_by, PCR.Response_date, PCR.Response_Reason,
PCR.Old_Currency_Unit_Price, PCR.New_Currency_Unit_Price,
PCR.Recoverable_Tax, PCR.Nonrecoverable_Tax,
PCR.WF_Item_type, PCR.WF_Item_key, PCR.Parent_Change_Request_Id,
PCR.Validation_Error, PCR.Approval_Required_Flag,
PCR.Old_Supplier_Order_Number, PCR.New_Supplier_Order_Number,
PCR.Old_Supplier_Order_Line_Number, PCR.New_Supplier_Order_Line_Number,PCR.Old_Amount,PCR.New_Amount
FROM po_change_requests PCR, po_distributions_all POD
WHERE PCR.change_request_id = l_change_request_id
AND POD.line_location_id =
NVL(PCR.document_line_location_id, PCR.parent_line_location_id);
select acceptance_required_flag,
authorization_status,
change_requested_by,
revision_num
into l_acceptance_required,
l_authorization_status,
l_change_requested_by,
l_revision_num
from po_headers_all
where po_header_id = p_po_header_id;
select acceptance_required_flag,
authorization_status,
change_requested_by,
revision_num
into l_acceptance_required,
l_authorization_status,
l_change_requested_by,
l_revision_num
from po_releases_all
where po_release_id = p_po_release_id;
select count(*) into l_acc_shipment_count
from po_acceptances
where po_header_id = p_po_header_id
and po_line_location_id is not null
and revision_num = l_revision_num
and po_release_id is null;
select count(*) into l_acc_shipment_count
from po_acceptances
where po_release_id = p_po_release_id
and po_line_location_id is not null
and revision_num = l_revision_num;
select count(*) into l_change_shipment_count
from po_change_requests
where document_header_id = p_po_header_id
and po_release_id is null
and change_request_group_id = p_change_request_group_id
and request_level = 'SHIPMENT'
and parent_line_location_id is null;
select count(*) into l_change_shipment_count
from po_change_requests
where po_release_id = p_po_release_id
and change_request_group_id = p_change_request_group_id
and request_level = 'SHIPMENT'
and parent_line_location_id is null;
select count(*) into l_total_shipment_count
from po_line_locations_all
where po_header_id = p_po_header_id
and po_release_id is null
and nvl(cancel_flag, 'N') <> 'Y'
and nvl(closed_code, 'OPEN') = 'OPEN'
and nvl(payment_type,'NULL')<>'ADVANCE'; --Bug 5132565
select count(*) into l_total_shipment_count
from po_line_locations_all
where po_header_id = p_po_header_id
and po_release_id = p_po_release_id
and nvl(cancel_flag, 'N') <> 'Y'
and nvl(closed_code, 'OPEN') = 'OPEN'
and nvl(payment_type,'NULL')<>'ADVANCE'; --Bug 5132565
* to which the buyer needs to respond before we call the PO validation and update procedures
* Return Value:
* This procedure returns p_change_pending with value 'Y' if there are some changes in pending state and
* with value 'N' if there are no changes in pending state
*/
PROCEDURE CheckChangePending (p_api_version in number,
x_return_status out NOCOPY varchar2,
p_po_header_id in number,
p_po_release_id in number,
x_change_pending out NOCOPY varchar2) is
l_pending_count NUMBER;
select revision_num
into l_revision_num
from po_headers_all
where po_header_id = p_po_header_id;
select count(*) into l_pending_count
from po_change_requests
where document_header_id = p_po_header_id
and document_revision_num = l_revision_num
and request_status = 'PENDING';
select count(*) into l_distribution_count
from po_distributions_all where line_location_id = p_document_line_location_id;
select count(*) into l_distribution_count
from po_distributions_all where line_location_id = p_parent_line_location_id;
* This procedure is used to perform validation of changes requested to PO and perform updates on the PO
* Usage:
* This procedure is called in the following scenario:
* 1. When the buyer completes responding to all the requested changes, this procedure is
* called by ProcessResponse procedure
* Return Value:
* This procedure returns return_code which has a value
* 0 if the validation was completely successful,
* 1 if the validation failed for some or all changes
* 2 if there was an unexpected error
*/
PROCEDURE MoveChangeToPO (p_api_version in number,
x_return_status out NOCOPY varchar2,
p_po_header_id in number,
p_po_release_id in number,
p_change_request_group_id in number,
p_user_id in number,
x_return_code out NOCOPY NUMBER,
x_err_msg out NOCOPY VARCHAR2,
x_doc_check_rec_type out NOCOPY POS_ERR_TYPE,
p_launch_approvals_flag IN VARCHAR2,
p_mass_update_releases IN VARCHAR2 DEFAULT NULL -- Bug 3373453
) is
CURSOR changed_lines_cursor IS
SELECT document_line_id, new_price, new_supplier_part_number,
new_start_date, new_expiration_date, new_amount
FROM po_change_requests
where request_status = 'BUYER_APP'
AND document_header_id = p_po_header_id
AND change_request_group_id = p_change_request_group_id
AND request_level = 'LINE';
SELECT document_line_location_id,
new_quantity, new_promised_date, new_need_by_date, new_price,
parent_line_location_id,
Decode(parent_line_location_id, null, null, document_shipment_number),
new_amount,
new_progress_type, -- << Complex work changes for R12 >>
new_pay_description,
new_supplier_order_line_number
FROM po_change_requests
where request_status = 'BUYER_APP'
AND document_header_id = p_po_header_id
AND change_request_group_id = p_change_request_group_id
AND request_level = 'SHIPMENT';
SELECT PCR.document_distribution_id, PCR.new_quantity, Decode(PCR.parent_line_location_id, null, null, PCR.document_shipment_number),
POD.po_distribution_id parent_distribution_id, PCR.new_amount
FROM po_change_requests PCR,
po_distributions POD
where PCR.request_status = 'BUYER_APP'
AND PCR.document_header_id = p_po_header_id
AND PCR.change_request_group_id = p_change_request_group_id
AND PCR.request_level = 'DISTRIBUTION'
-- Identify the parent of a split distribution:
AND POD.line_location_id (+)= PCR.parent_line_location_id
AND POD.distribution_num (+)= PCR.document_distribution_number
AND nvl(PCR.new_quantity,-1)<>0
AND nvl(PCR.new_amount,-1)<>0;
select org_id, release_type
into l_s_org_id, l_document_subtype
from po_releases_all
where po_release_id = p_po_release_id;
select org_id, type_lookup_code
into l_s_org_id, l_document_subtype
from po_headers_all
where po_header_id = p_po_header_id;
UPDATE po_headers_all
SET authorization_status = 'APPROVED'
WHERE po_header_id = p_po_header_id;
UPDATE po_releases_all
SET authorization_status = 'APPROVED'
WHERE po_release_id = p_po_release_id;
PO_DOCUMENT_UPDATE_GRP.update_document (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
x_return_status => l_return_status,
p_changes => l_changes,
p_run_submission_checks => FND_API.G_TRUE,
p_launch_approvals_flag => FND_API.G_FALSE,
p_buyer_id => NULL,
p_update_source => NULL, -- default
p_override_date => NULL,
x_api_errors => l_api_errors,
p_mass_update_releases => p_mass_update_releases
);
UPDATE po_change_requests
SET document_line_location_id =
l_changes.shipment_changes.po_line_location_id(i)
WHERE parent_line_location_id =
l_changes.shipment_changes.parent_line_location_id(i)
AND document_shipment_number =
l_changes.shipment_changes.split_shipment_num(i)
AND change_request_group_id = p_change_request_group_id;
PO_DOCUMENT_UPDATE_GRP.launch_po_approval_wf (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
x_return_status => l_return_status,
p_document_id => NVL(p_po_release_id, p_po_header_id),
p_document_type => l_document_type,
p_document_subtype => l_document_subtype,
p_preparer_id => NULL,
p_approval_background_flag => NULL,
p_mass_update_releases => p_mass_update_releases );
/** update_change_response
* -----------------------
* Purpose:
* This procedure is used to Update Request Statuses in Change_Table when Buyer Submits his Response
* from Response Page
* Usage:
* This is called from PosChangeResponseVO updateResponse procedure in a loop for all accepted/rejected changes
* Return Value:
* This procedure does not have a return value
*/
PROCEDURE update_change_response (p_request_status in VARCHAR2,
p_responded_by in NUMBER,
p_response_reason in VARCHAR2,
p_change_request_id in NUMBER,
p_request_level in VARCHAR2,
p_change_request_group_id in NUMBER,
p_line_location_id in NUMBER,
p_splitFlag in VARCHAR2,
p_cancel_backing_req in VARCHAR2) is
l_wf_item_key po_change_requests.wf_item_key%TYPE;
update po_change_requests
set request_status = p_request_status,
-- change_active_flag = 'N', /* commented out due to bug 3574114 */
responded_by = p_responded_by,
response_date = sysdate,
last_updated_by = p_responded_by,
last_update_date = sysdate,
response_reason = p_response_reason
where change_request_id = p_change_request_id;
update po_change_requests
set request_status = p_request_status,
cancel_backing_req = p_cancel_backing_req,
responded_by = p_responded_by,
response_date = sysdate,
last_updated_by = p_responded_by,
last_update_date = sysdate,
response_reason = p_response_reason
where change_request_id = p_change_request_id;
select wf_item_type, wf_item_key
into l_wf_item_type, l_wf_item_key
from po_change_requests
where change_request_id = p_change_request_id;
update po_change_requests
set request_status = p_request_status,
responded_by = p_responded_by,
response_date = sysdate,
last_updated_by = p_responded_by,
last_update_date = sysdate,
response_reason = p_response_reason
where change_request_group_id = p_change_request_group_id and
request_level = 'DISTRIBUTION' and
request_status = 'PENDING' and
document_line_location_id = p_line_location_id;
update po_line_locations_all
set approved_flag = 'Y'
where line_location_id = p_line_location_id;
update po_change_requests
set request_status = p_request_status,
responded_by = p_responded_by,
response_date = sysdate,
last_updated_by = p_responded_by,
last_update_date = sysdate,
response_reason = p_response_reason
where change_request_group_id = p_change_request_group_id and
request_level = 'DISTRIBUTION' and
request_status = 'PENDING' and
parent_line_location_id = p_line_location_id;
END update_change_response;
* encountered during PO update validation and we decide to roll back the transaction
*/
PROCEDURE roll_back_acceptance (p_change_request_group_id in NUMBER) is
BEGIN
update po_change_requests
set request_status = 'PENDING'
where change_request_group_id = p_change_request_group_id
and request_status in ('BUYER_APP', 'WAIT_MGR_APP');