'
'
|| fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 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(
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);
Procedure Insert_Acc_Rejection_Row( p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_flag in varchar2);
Procedure Update_Chg_Req_If_Po_Apprvd( p_header_id in number,
p_release_id in number);
select PO_SUPPLIER_CHANGE_WF_S.nextval into l_seq from dual;
select user_name
into l_supplier_username
from fnd_user
where user_id = fnd_global.user_id;
select PO_SUPPLIER_CHANGE_WF_S.nextval into l_seq from dual;
select user_name
into l_supplier_username
from fnd_user
where user_id = fnd_global.user_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(
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(null,'DD/MM/YYYY'), 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(null,'DD/MM/YYYY'), 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;
'InsertActionHist'||sqlerrm,x_progress);
END InsertActionHist;
PROCEDURE Update_Chg_Req_If_Po_Apprvd(p_header_id number ,
p_release_id number)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name varchar2(50) :='Update_Chg_Req_If_Po_Apprvd';
update po_releases_all
set change_requested_by = null
where po_release_id = p_release_id;
update po_headers_all
set change_requested_by = null
where po_header_id = p_header_id;
End Update_Chg_Req_If_Po_Apprvd;
update po_change_requests
set request_status = 'WAIT_CANCEL_APP'
where request_status = 'BUYER_APP'
and action_type = 'CANCELLATION'
and request_level = 'SHIPMENT'
and document_line_location_id = p_can_line_loc_id;
update po_headers_all
set authorization_status = 'APPROVED'
where po_header_id = p_can_hdr_id;
update po_releases_all
set authorization_status = 'APPROVED'
where po_release_id = p_can_rel_id;
select cancel_flag into l_cancel_flag
from po_headers_all
where po_header_id = p_can_hdr_id;
select cancel_flag into l_cancel_flag
from po_line_locations_all
where line_location_id = p_can_line_loc_id;
update po_headers_all
set authorization_status = 'IN PROCESS'
where po_header_id = p_can_hdr_id;
select cancel_flag into l_cancel_flag
from po_releases_all
where po_release_id = p_can_rel_id;
select cancel_flag into l_cancel_flag
from po_line_locations_all
where line_location_id = p_can_line_loc_id;
update po_releases_all
set authorization_status = 'IN PROCESS'
where po_release_id = p_can_rel_id;
update po_headers_all
set authorization_status = 'IN PROCESS'
where po_header_id = p_can_hdr_id;
update po_releases_all
set authorization_status = 'IN PROCESS'
where po_release_id = p_can_rel_id;
select
change_request_id,
document_header_id,
po_release_id,
document_line_id,
document_line_location_id,
request_reason,
document_type,
cancel_backing_req
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'SHIPMENT'
and request_status = 'BUYER_APP';
select
change_request_id,
document_header_id,
po_release_id,
request_reason
,document_type
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER'
and request_status = 'BUYER_APP';
select count(*) into l_cancel_count
from po_change_requests
where change_request_group_id=p_chg_req_grp_id
and request_status='BUYER_APP'
and request_level = 'SHIPMENT'
and action_type='CANCELLATION';
select type_lookup_code
into l_type_lookup_code
from po_headers_all
where po_header_id = l_can_hdr_id;
update po_change_requests
set request_status = 'REJECTED',
response_reason = l_ship_can_err_msg,
validation_error = l_ship_can_err_msg
-- change_active_flag = 'N' /* commented out due to bug 3574114 */
where change_request_id = l_can_req_id;
select type_lookup_code
into l_type_lookup_code
from po_headers_all
where po_header_id = l_can_hdr_id;
update po_change_requests
set request_status = 'REJECTED' ,
response_reason = l_hdr_can_err_msg,
validation_error = l_hdr_can_err_msg,
change_active_flag = 'N'
where change_request_id = l_can_req_id;
Procedure Insert_Acc_Rejection_Row(p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_flag in varchar2)
is
l_api_name varchar2(50) := 'Insert_Acc_Rejection_Row';
l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
l_Last_Update_Date date;
l_Last_Updated_By number;
SELECT po_acceptances_s.nextval into l_Acceptance_id FROM sys.dual;
select
revision_num,
agent_id
into
l_revision_num,
l_employee_id
from po_headers
where po_header_id = l_document_id;
select
po_header_id,
revision_num,
agent_id
into
l_Po_Header_Id,
l_revision_num,
l_employee_id
from po_releases
where po_release_id = l_document_id;
select user_id into l_Last_Updated_By
from fnd_user
where user_name = upper(l_rspndr_usr_name);
l_Last_Updated_By := l_Created_By;
l_Last_Update_Login := l_Last_Updated_By;
PO_ACCEPTANCES_INS_PVT.insert_row(
x_rowid => l_rowid,
x_acceptance_id => l_Acceptance_id,
x_Last_Update_Date => l_Last_Update_Date,
x_Last_Updated_By => l_Last_Updated_By,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => l_Creation_Date,
p_created_by => l_Last_Updated_By,
p_po_header_id => l_acc_po_header_id,
p_po_release_id => l_Po_Release_Id,
p_action => l_Action,
p_action_date => l_Action_Date,
p_employee_id => NULL,
p_revision_num => l_Revision_Num,
p_accepted_flag => l_Accepted_Flag,
p_note => l_acceptance_note,
p_accepting_party => l_accepting_party
);
update po_releases
set acceptance_required_flag = 'N',
LAST_UPDATE_DATE = SYSDATE,
acceptance_due_date = ''
where po_release_id = l_po_release_id;
update po_headers
set acceptance_required_flag = 'N',
LAST_UPDATE_DATE = SYSDATE,
acceptance_due_date = ''
where po_header_id = l_po_header_id;
*PO_REQAPPROVAL_INIT1.Register_rejection will be updated to support older version of poxwfpoa.wft
*In other words, initial version of poxwfpoa.wft only calls PO_REQ_APPROVAL_INIT1.Register_rejection.
*New version will call PO_ChangeOrderWF_PVT.IS_PO_HDR_REJECTED followed by PO_ChangeOrderWF_PVT.Register_rejection.
*In order for older version of workflow to have the new functionality, PO_REQ_APPROVAL_INIT1.Register_rejection will
*need to include the logic of PO_ChangeOrderWF_PVT.IS_PO_HDR_REJECTED within.
*/
procedure Register_rejection ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out NOCOPY varchar2 )
is
l_api_name varchar2(50):= 'Register_rejection';
select pov.vendor_name
into l_vendor
from
po_vendors pov,
po_headers poh
where pov.vendor_id = poh.vendor_id
and poh.po_header_id=l_document_id;
select pov.vendor_name
into l_vendor
from
po_releases por,
po_headers_all poh, --
po_vendors pov
where por.po_release_id = l_document_id
and por.po_header_id = poh.po_header_id
and poh.vendor_id = pov.vendor_id;
select a.notification_id, a.recipient_role
INTO l_nid, l_ntf_role_name
from wf_notifications a,
wf_item_activity_statuses wa
where itemkey=wa.item_key
and itemtype=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'CLOSED';
select a.notification_id, a.recipient_role
INTO l_nid, l_ntf_role_name
from wf_notifications a,
wf_item_activity_statuses wa
where itemkey=wa.item_key
and itemtype=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'CLOSED';
Insert_Acc_Rejection_Row(itemtype, itemkey, actid, 'N');
select por.release_type,
por.org_id ,
poh.segment1,
por.agent_id
into l_Document_SubType,
l_org_id,
l_document_num,
l_agent_id
from po_releases_all por,
po_headers_all poh
where po_release_id = p_release_id
and por.po_header_id=poh.po_header_id;
select
poh.segment1,
poh.agent_id,
poh.type_lookup_code,
poh.org_id
into
l_document_num,
l_agent_id,
l_document_subtype,
l_org_id
from po_headers_all poh
where
poh.po_header_id = p_header_id;
select wf_item_type, wf_item_key
into l_orig_itemtype, l_orig_itemkey
from po_headers_all
where po_header_id = l_document_id;
select wf_item_type, wf_item_key
into l_orig_itemtype, l_orig_itemkey
from po_releases_all
where po_release_id = l_document_id;
select
wf_approval_itemtype,
wf_approval_process
into
l_Item_Type,
l_Workflow_Process
from PO_DOCUMENT_TYPES_V
where DOCUMENT_TYPE_CODE = l_Document_Type_Code
and DOCUMENT_SUBTYPE = l_Document_Subtype;
select to_char(PO_WF_ITEMKEY_S.NEXTVAL) into l_seq from sys.dual;
select revision_num
into l_revision_num
from po_headers_all
where po_header_id = l_document_id;
select revision_num
into l_revision_num
from po_releases_all
where po_release_id = l_document_id;
select change_request_id
from po_change_requests
where document_header_id = hdr_id
and document_revision_num = rev_num
and request_status = 'PENDING'
and initiator = 'SUPPLIER'
union all
select acceptance_id
from po_acceptances
where po_header_id = hdr_id
and revision_num = rev_num
and po_line_location_id is not null;
select change_request_id
from po_change_requests
where po_release_id = rel_id
and document_revision_num = rev_num
and request_status = 'PENDING'
and initiator = 'SUPPLIER'
union all
select acceptance_id
from po_acceptances
where po_release_id = rel_id
and revision_num = rev_num
and po_line_location_id is not null;
select
to_char(LINE_NUM),
to_char(SHIPMENT_NUM),
BUYER_PT_NUM,
OLD_SUP_PT_NUM,
NEW_SUP_PT_NUM,
to_char(OLD_PROM_DATE,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
to_char(NEW_PROM_DATE, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
to_char(OLD_QTY),
to_char(NEW_QTY),
to_char(OLD_PRICE),
to_char(NEW_PRICE),
ACTION_TYPE,
ITEM_DESCRIPTION,
UOM,
SHIP_TO_LOCATION,
ACTION_CODE,
REASON,
SPLIT
from(
-- LINE CHANGES for Standard PO
select
pcr.document_line_number LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
pcr.new_supplier_part_number NEW_SUP_PT_NUM,
to_date(null,'DD/MM/YYYY') OLD_PROM_DATE,
to_date(null,'DD/MM/YYYY') NEW_PROM_DATE,
pla.quantity OLD_QTY,
to_number(null) NEW_QTY,
pla.unit_price OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),
'CANCELLATION',fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
pla.unit_meas_lookup_code UOM,
null SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.request_reason REASON,
null SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where pla.po_line_id = pcr.document_line_id
and pcr.change_request_group_id =grp_id
and pcr.request_level = 'LINE'
and pcr.request_status in ('PENDING','REQ_APP')
and pla.latest_external_flag = 'Y'
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
and exists (select 1 from po_distributions_all
where po_line_id = pcr.document_line_id
and deliver_to_person_id = l_requestor_id)
UNION ALL
-- SHIPMENT CHANGES for Standard PO and Releases
select
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
nvl(pcr.old_promised_date, nvl(plla.promised_date,plla.need_by_date)) OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
plla.quantity OLD_QTY,
pcr.new_quantity NEW_QTY,
decode(pla.matching_basis,'AMOUNT',DECODE(plla.payment_type,'RATE',nvl(pcr.old_price,plla.price_override),nvl(pcr.old_amount,plla.amount)),nvl(pcr.old_price,plla.price_override)) OLD_PRICE,
decode(pla.matching_basis,'AMOUNT',DECODE(plla.payment_type,'RATE',pcr.new_price,pcr.new_amount),pcr.new_price) NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),'CANCELLATION',
fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.REQUEST_REASON REASON,
null SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where pcr.change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.document_line_location_id
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and pcr.request_status in ('PENDING','REQ_APP')
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
and exists (select 1 from po_distributions_all
where line_location_id = pcr.document_line_location_id
and deliver_to_person_id = l_requestor_id)
UNION ALL
--SPLIT SHIPMENTS
select
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
nvl(pcr.old_promised_date, nvl(plla.promised_date,plla.need_by_date)) OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
plla.quantity OLD_QTY,
pcr.new_quantity NEW_QTY,
decode(pla.matching_basis,'AMOUNT',DECODE(nvl(pcr.new_progress_type,plla.payment_type),'RATE',nvl(pcr.new_price,plla.price_override),nvl(pcr.old_amount,plla.amount)),nvl(pcr.old_price,plla.price_override)) OLD_PRICE,
decode(pla.matching_basis,'AMOUNT',DECODE(nvl(pcr.new_progress_type,plla.payment_type),'RATE',pcr.new_price,pcr.new_amount),pcr.new_price) NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),'CANCELLATION',
fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.REQUEST_REASON REASON,
fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where pcr.change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.parent_line_location_id
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and pcr.request_status in ('PENDING','REQ_APP')
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
and exists (select 1 from po_distributions_all
where line_location_id = pcr.parent_line_location_id
and deliver_to_person_id = l_requestor_id)
)
order by LINE_NUM,nvl(SHIPMENT_NUM,0);
select change_request_id,
request_status
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select additional_changes
from po_change_requests
where change_request_group_id = grp_id
and request_level = 'HEADER'
and additional_changes is not null;
select segment1,
revision_num,
pos_totals_po_sv.get_po_total(po_header_id),
currency_code,
vendor_id,
vendor_site_id,
to_char(creation_date, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) , 'GREGORIAN' ) || ''''),
fob_lookup_code,
ship_via_lookup_code,
ship_to_location_id,
type_lookup_code,
GLOBAL_AGREEMENT_FLAG
into
l_po_num,
l_revision_num,
l_po_total,
l_po_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code,
l_global_agreement_flag
from po_headers_all
where po_header_id = l_header_id;
select
ph.segment1,
pr.release_num,
pr.revision_num,
pos_totals_po_sv.get_release_total(pr.po_release_id),
ph.currency_code,
ph.vendor_id,
ph.vendor_site_id,
to_char(pr.creation_date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
ph.fob_lookup_code,
ph.ship_via_lookup_code,
ph.ship_to_location_id,
ph.type_lookup_code
into
l_blanket_num,
l_release_num,
l_revision_num,
l_rel_total,
l_rel_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code
from po_releases_all pr,
po_headers_all ph
where pr.po_release_id = l_release_id
and pr.po_header_id = ph.po_header_id;
select
vendor_name
into
l_supplier_name
from po_vendors
where vendor_id = l_vendor_id;
select
address_line1,
address_line2,
address_line3,
city,
state,
zip
into
l_sup_address_line1,
l_sup_address_line2,
l_sup_address_line3,
l_sup_city,
l_sup_state,
l_sup_zip
from po_vendor_sites_all
where vendor_site_id = l_vendor_site_id;
select
address_line_1,
address_line_2,
address_line_3,
town_or_city,
region_1,
postal_code
into
l_ship_addr_l1,
l_ship_addr_l2,
l_ship_addr_l3,
l_ship_city,
l_ship_state,
l_ship_zip
from hr_locations_all
where location_id = l_ship_to_id;
''
|| fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
';
';
';
';
';
';
select
distinct
CHANGE_ID,
to_char(LINE_NUM),
to_char(SHIPMENT_NUM),
BUYER_PT_NUM,
OLD_SUP_PT_NUM,
NEW_SUP_PT_NUM,
to_char(OLD_PROM_DATE, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
to_char(NEW_PROM_DATE, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
to_char(OLD_QTY),
to_char(NEW_QTY),
to_char(OLD_PRICE),
to_char(NEW_PRICE),
ACTION_TYPE,
ITEM_DESCRIPTION,
UOM,
SHIP_TO_LOCATION,
ACTION_CODE,
REASON,
SPLIT
from(
-- LINE CHANGES for Standard PO
select
pcr.change_request_id CHANGE_ID,
pcr.document_line_number LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
pcr.new_supplier_part_number NEW_SUP_PT_NUM,
to_date(null,'DD/MM/YYYY') OLD_PROM_DATE,
to_date(null,'DD/MM/YYYY') NEW_PROM_DATE,
pla.quantity OLD_QTY,
to_number(null) NEW_QTY,
pla.unit_price OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),
'CANCELLATION',fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
pla.unit_meas_lookup_code UOM,
null SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.request_reason REASON,
null SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
mtl_system_items_kfv msi,
mtl_planners mtp,
financials_system_params_all fsp
where pla.po_line_id = pcr.document_line_id
and pcr.change_request_group_id =grp_id
and pcr.request_level = 'LINE'
and pcr.request_status in ('PENDING','REQ_APP')
and pla.latest_external_flag = 'Y'
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and fsp.org_id = pla.org_id
and msi.organization_id = fsp.inventory_organization_id
and msi.planner_code = mtp.planner_code
and msi.organization_id = mtp.organization_id
and mtp.employee_id = l_planner_id
UNION ALL
-- SHIPMENT CHANGES for Standard PO and Releases
select
pcr.change_request_id CHANGE_ID,
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
nvl(pcr.old_promised_date, nvl(plla.promised_date,plla.need_by_date)) OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
plla.quantity OLD_QTY,
pcr.new_quantity NEW_QTY,
decode(pla.matching_basis,'AMOUNT',DECODE(plla.payment_type,'RATE',nvl(pcr.old_price,plla.price_override),nvl(pcr.old_amount,plla.amount)),nvl(pcr.old_price,plla.price_override)) OLD_PRICE,
decode(pla.matching_basis,'AMOUNT',DECODE(plla.payment_type,'RATE',pcr.new_price,pcr.new_amount),pcr.new_price) NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),'CANCELLATION',
fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.REQUEST_REASON REASON,
null SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
mtl_planners mtp,
financials_system_params_all fsp
where pcr.change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.document_line_location_id
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and pcr.request_status in ('PENDING','REQ_APP')
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and fsp.org_id = pla.org_id
and msi.organization_id = fsp.inventory_organization_id
and msi.planner_code = mtp.planner_code
and msi.organization_id = mtp.organization_id
and mtp.employee_id = l_planner_id
UNION ALL
--SPLIT SHIPMENTS
select
pcr.change_request_id CHANGE_ID,
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
nvl(pcr.old_promised_date, nvl(plla.promised_date,plla.need_by_date)) OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
plla.quantity OLD_QTY,
pcr.new_quantity NEW_QTY,
decode(pla.matching_basis,'AMOUNT',DECODE(nvl(pcr.new_progress_type,plla.payment_type),'RATE',nvl(pcr.new_price,plla.price_override),nvl(pcr.old_amount,plla.amount)),nvl(pcr.old_price,plla.price_override)) OLD_PRICE,
decode(pla.matching_basis,'AMOUNT',DECODE(nvl(pcr.new_progress_type,plla.payment_type),'RATE',pcr.new_price,pcr.new_amount),pcr.new_price) NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),'CANCELLATION',
fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.REQUEST_REASON REASON,
fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
mtl_planners mtp,
financials_system_params_all fsp
where pcr.change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.parent_line_location_id
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and pcr.request_status in ('PENDING','REQ_APP')
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and fsp.org_id = pla.org_id
and msi.organization_id = fsp.inventory_organization_id
and msi.planner_code = mtp.planner_code
and msi.organization_id = mtp.organization_id
and mtp.employee_id = l_planner_id
)
order by LINE_NUM,nvl(SHIPMENT_NUM,0);
select change_request_id,
request_status
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select additional_changes
from po_change_requests
where change_request_group_id = grp_id
and request_level = 'HEADER'
and additional_changes is not null;
select segment1,
revision_num,
pos_totals_po_sv.get_po_total(po_header_id),
currency_code,
vendor_id,
vendor_site_id,
to_char(creation_date, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
fob_lookup_code,
ship_via_lookup_code,
ship_to_location_id,
type_lookup_code,
GLOBAL_AGREEMENT_FLAG
into
l_po_num,
l_revision_num,
l_po_total,
l_po_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code,
l_global_agreement_flag
from po_headers_all
where po_header_id = l_header_id;
select
ph.segment1,
pr.release_num,
pr.revision_num,
pos_totals_po_sv.get_release_total(pr.po_release_id),
ph.currency_code,
ph.vendor_id,
ph.vendor_site_id,
to_char(pr.creation_date, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),
ph.fob_lookup_code,
ph.ship_via_lookup_code,
ph.ship_to_location_id,
ph.type_lookup_code
into
l_blanket_num,
l_release_num,
l_revision_num,
l_rel_total,
l_rel_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code
from po_releases_all pr,
po_headers_all ph
where pr.po_release_id = l_release_id
and pr.po_header_id = ph.po_header_id;
select
vendor_name
into
l_supplier_name
from po_vendors
where vendor_id = l_vendor_id;
select
address_line1,
address_line2,
address_line3,
city,
state,
zip
into
l_sup_address_line1,
l_sup_address_line2,
l_sup_address_line3,
l_sup_city,
l_sup_state,
l_sup_zip
from po_vendor_sites_all
where vendor_site_id = l_vendor_site_id;
select
address_line_1,
address_line_2,
address_line_3,
town_or_city,
region_1,
postal_code
into
l_ship_addr_l1,
l_ship_addr_l2,
l_ship_addr_l3,
l_ship_city,
l_ship_state,
l_ship_zip
from hr_locations_all
where location_id = l_ship_to_id;
''
|| fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
';
';
';
';
';
';
select
to_char(LINE_NUM),
to_char(SHIPMENT_NUM),
BUYER_PT_NUM,
OLD_SUP_PT_NUM,
NEW_SUP_PT_NUM,
OLD_PROM_DATE,
NEW_PROM_DATE,
to_char(OLD_QTY),
to_char(NEW_QTY),
to_char(OLD_PRICE),
to_char(NEW_PRICE),
ACTION_TYPE,
ITEM_DESCRIPTION,
UOM,
SHIP_TO_LOCATION,
ACTION_CODE,
REASON,
SPLIT
from(
-- LINE CHANGES for Standard PO
select
pcr.document_line_number LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
pcr.new_supplier_part_number NEW_SUP_PT_NUM,
to_date(null,'DD/MM/YYYY') OLD_PROM_DATE,
to_date(null,'DD/MM/YYYY') NEW_PROM_DATE,
pla.quantity OLD_QTY,
to_number(null) NEW_QTY,
pla.unit_price OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),
'CANCELLATION',fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
pla.unit_meas_lookup_code UOM,
null SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.request_reason REASON,
null SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where pla.po_line_id = pcr.document_line_id
and pcr.change_request_group_id =grp_id
and pcr.request_level = 'LINE'
and pcr.request_status = 'PENDING'
and pla.latest_external_flag = 'Y'
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
UNION ALL
-- SHIPMENT CHANGES for Standard PO and Releases
select
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
decode(pcr.new_promised_date, null, plla.promised_date, pcr.old_promised_date) OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
plla.quantity OLD_QTY,
pcr.new_quantity NEW_QTY,
plla.price_override OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),'CANCELLATION',
fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.REQUEST_REASON REASON,
null SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where pcr.change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.document_line_location_id
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and pcr.request_status = 'PENDING'
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
UNION ALL
--SPLIT SHIPMENTS
select
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
decode(pcr.new_promised_date, null, plla.promised_date, pcr.old_promised_date) OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
plla.quantity OLD_QTY,
pcr.new_quantity NEW_QTY,
plla.price_override OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.ACTION_TYPE,'MODIFICATION',fnd_message.get_string('PO','PO_WF_NOTIF_CHANGE'),'CANCELLATION',
fnd_message.get_string('PO','PO_WF_NOTIF_CANCEL'),'Error') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.ACTION_TYPE ACTION_CODE,
pcr.REQUEST_REASON REASON,
fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
from
po_change_requests pcr,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where pcr.change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.parent_line_location_id
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and pcr.request_status = 'PENDING'
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
)
order by LINE_NUM,nvl(SHIPMENT_NUM,0);
select
to_char(LINE_NUM),
to_char(SHIPMENT_NUM),
BUYER_PT_NUM,
OLD_SUP_PT_NUM,
NEW_SUP_PT_NUM,
OLD_PROM_DATE,
NEW_PROM_DATE,
to_char(OLD_QTY),
to_char(NEW_QTY),
to_char(OLD_PRICE),
to_char(NEW_PRICE),
ACTION_TYPE,
ITEM_DESCRIPTION,
UOM,
SHIP_TO_LOCATION,
NOTE
from(
-- SHIPMENT ACCEPTANCE/REJECTION for Standard PO
select
pla.line_num LINE_NUM,
plla.shipment_num SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.VENDOR_PRODUCT_NUM OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
nvl(plla.promised_date,plla.need_by_date) OLD_PROM_DATE,
to_date(null,'DD/MM/YYYY') NEW_PROM_DATE,
plla.quantity OLD_QTY,
to_number(null) NEW_QTY,
plla.price_override OLD_PRICE,
to_number(null) NEW_PRICE,
decode(pa.accepted_flag,'Y',fnd_message.get_string('PO','PO_WF_NOTIF_ACCEPT'),
'N',fnd_message.get_string('PO','PO_WF_NOTIF_REJECT'),'NA') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pa.note NOTE
from
po_acceptances pa,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where plla.po_header_id = l_po_document_id
and plla.po_line_id = pla.po_line_id
and pa.po_line_location_id = plla.line_location_id
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
--and plla.latest_external_flag = 'Y'
and pla.latest_external_flag = 'Y'
and pa.revision_num = l_rev
and plla.revision_num = (select max(revision_num)
from po_line_locations_archive_all plla2
where plla2.line_location_id = plla.line_location_id and
plla.revision_num <= l_rev)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
UNION ALL
-- SHIPMENT ACCEPTANCE/REJECTION for Releases
select
pla.line_num LINE_NUM,
plla.shipment_num SHIPMENT_NUM,
msi.concatenated_segments BUYER_PT_NUM,
pla.VENDOR_PRODUCT_NUM OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
nvl(plla.promised_date,plla.need_by_date) OLD_PROM_DATE,
to_date(null,'DD/MM/YYYY') NEW_PROM_DATE,
plla.quantity OLD_QTY,
to_number(null) NEW_QTY,
plla.price_override OLD_PRICE,
to_number(null) NEW_PRICE,
decode(pa.accepted_flag,'Y',fnd_message.get_string('PO','PO_WF_NOTIF_ACCEPT'),'N',fnd_message.get_string('PO','PO_WF_NOTIF_REJECT'),'NA') ACTION_TYPE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pa.NOTE NOTE
from
po_acceptances pa,
po_lines_archive_all pla,
po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz,
mtl_system_items_kfv msi,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where plla.po_release_id = l_rel_document_id
and plla.po_line_id = pla.po_line_id
and pa.po_line_location_id = plla.line_location_id
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
--and plla.latest_external_flag = 'Y'
and pla.latest_external_flag = 'Y'
and pa.revision_num = l_rev
and plla.revision_num = (select max(revision_num)
from po_line_locations_archive_all plla2
where plla2.line_location_id = plla.line_location_id and
plla.revision_num <= l_rev)
and msi.inventory_item_id(+) = pla.item_id
--and msi.organization_id(+) = pla.org_id
and NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
)
order by LINE_NUM,SHIPMENT_NUM;
select change_request_id,
request_status
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select additional_changes
from po_change_requests
where change_request_group_id = grp_id
and request_level = 'HEADER'
and additional_changes is not null;
select segment1,
revision_num,
pos_totals_po_sv.get_po_total(po_header_id),
currency_code,
vendor_id,
vendor_site_id,
to_char(creation_date, fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
fob_lookup_code,
ship_via_lookup_code,
ship_to_location_id,
type_lookup_code,
GLOBAL_AGREEMENT_FLAG
into
l_po_num,
l_revision_num,
l_po_total,
l_po_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code,
l_global_agreement_flag
from po_headers_all
where po_header_id = l_header_id;
select
ph.segment1,
pr.release_num,
pr.revision_num,
pos_totals_po_sv.get_release_total(pr.po_release_id),
ph.currency_code,
ph.vendor_id,
ph.vendor_site_id,
to_char(pr.creation_date, fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
ph.fob_lookup_code,
ph.ship_via_lookup_code,
ph.ship_to_location_id,
ph.type_lookup_code
into
l_blanket_num,
l_release_num,
l_revision_num,
l_rel_total,
l_rel_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code
from po_releases_all pr,
po_headers_all ph
where pr.po_release_id = l_release_id
and pr.po_header_id = ph.po_header_id;
select
vendor_name
into
l_supplier_name
from po_vendors
where vendor_id = l_vendor_id;
select
address_line1,
address_line2,
address_line3,
city,
state,
zip
into
l_sup_address_line1,
l_sup_address_line2,
l_sup_address_line3,
l_sup_city,
l_sup_state,
l_sup_zip
from po_vendor_sites_all
where vendor_site_id = l_vendor_site_id;
select
address_line_1,
address_line_2,
address_line_3,
town_or_city,
region_1,
postal_code
into
l_ship_addr_l1,
l_ship_addr_l2,
l_ship_addr_l3,
l_ship_city,
l_ship_state,
l_ship_zip
from hr_locations_all
where location_id = l_ship_to_id;
''
|| fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
';
';
';
';
';
';
select
to_char(LINE_NUM),
to_char(SHIPMENT_NUM),
OLD_SUP_PT_NUM,
NEW_SUP_PT_NUM,
OLD_PROM_DATE,
NEW_PROM_DATE,
to_char(OLD_QTY),
to_char(NEW_QTY),
to_char(OLD_PRICE),
to_char(NEW_PRICE),
RESPONSE,
ITEM_DESCRIPTION,
UOM,
SHIP_TO_LOCATION,
REASON,
SPLIT,
ACTION_CODE
from(
-- Respond to Changes on Line for Standard PO
select
pcr.document_line_number LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
pcr.old_supplier_part_number OLD_SUP_PT_NUM,
pcr.new_supplier_part_number NEW_SUP_PT_NUM,
to_date(null,'DD/MM/YYYY') OLD_PROM_DATE,
to_date(null,'DD/MM/YYYY') NEW_PROM_DATE,
nvl(pcr.old_quantity, pla.quantity) OLD_QTY,
to_number(null) NEW_QTY,
nvl(pcr.old_price,pla.unit_price) OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.REQUEST_STATUS,'ACCEPTED',fnd_message.get_string('PO','PO_WF_NOTIF_ACCEPTED'),
'REJECTED',fnd_message.get_string('PO','PO_WF_NOTIF_REJ'),'Error') RESPONSE,
pla.item_description ITEM_DESCRIPTION,
pla.unit_meas_lookup_code UOM,
null SHIP_TO_LOCATION,
pcr.response_reason REASON,
null SPLIT,
pcr.action_type ACTION_CODE
from po_change_requests pcr, po_lines_archive_all pla
where pla.po_line_id = pcr.document_line_id
and pcr.change_request_group_id =grp_id
--and pcr.document_revision_num = pla.revision_num
and pcr.request_level = 'LINE'
and pla.latest_external_flag = 'Y'
UNION ALL
--Respond to changes on Shipment for Releases AND Standard PO
select
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
pcr.old_promised_date OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
nvl(pcr.old_quantity,plla.quantity) OLD_QTY,
pcr.new_quantity NEW_QTY,
nvl(pcr.old_price,plla.price_override) OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.REQUEST_STATUS,'ACCEPTED',fnd_message.get_string('PO','PO_WF_NOTIF_ACCEPTED'),
'REJECTED',fnd_message.get_string('PO','PO_WF_NOTIF_REJ'),'Error') RESPONSE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.response_reason REASON,
decode(pcr.parent_line_location_id, null, null, fnd_message.get_string('PO','PO_WF_NOTIF_YES')) SPLIT,
pcr.action_type ACTION_CODE
from po_change_requests pcr, po_lines_archive_all pla, po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz
where change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.document_line_location_id
--and pcr.document_revision_num = pla.revision_num
--and pcr.document_revision_num = plla.revision_num
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and pcr.new_supplier_order_line_number is null
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
and pcr.parent_line_location_id is null
UNION ALL
--Respond to changes on Split Shipment
select
pla.line_num LINE_NUM,
pcr.document_shipment_number SHIPMENT_NUM,
pla.vendor_product_num OLD_SUP_PT_NUM,
null NEW_SUP_PT_NUM,
pcr.old_promised_date OLD_PROM_DATE,
pcr.new_promised_date NEW_PROM_DATE,
pcr.old_quantity OLD_QTY,
pcr.new_quantity NEW_QTY,
nvl(pcr.old_price,plla.price_override) OLD_PRICE,
pcr.new_price NEW_PRICE,
decode(pcr.REQUEST_STATUS,'ACCEPTED',fnd_message.get_string('PO','PO_WF_NOTIF_ACCEPTED'),
'REJECTED',fnd_message.get_string('PO','PO_WF_NOTIF_REJ'),'Error') RESPONSE,
pla.item_description ITEM_DESCRIPTION,
nvl(plla.unit_meas_lookup_code,pla.unit_meas_lookup_code) UOM,
nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
pcr.response_reason REASON,
decode(pcr.parent_line_location_id, null, null, fnd_message.get_string('PO','PO_WF_NOTIF_YES')) SPLIT,
pcr.action_type ACTION_CODE
from po_change_requests pcr, po_lines_archive_all pla, po_line_locations_archive_all plla,
hr_locations_all hla, hz_locations hz
where change_request_group_id =grp_id
and pla.po_line_id = pcr.document_line_id
and pla.latest_external_flag = 'Y'
and plla.line_location_id = pcr.parent_line_location_id
--and pcr.document_revision_num = pla.revision_num
--and pcr.document_revision_num = plla.revision_num
and plla.latest_external_flag = 'Y'
and request_level = 'SHIPMENT'
and hla.location_id(+) = plla.ship_to_location_id
and plla.SHIP_TO_LOCATION_ID = hz.LOCATION_ID(+)
)
order by LINE_NUM,nvl(SHIPMENT_NUM,0);
select change_request_id,
request_status
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select additional_changes, request_status
from po_change_requests
where change_request_group_id = grp_id
and request_level = 'HEADER'
and additional_changes is not null; */
/* select distinct
document_header_id,
document_type,
po_release_id
into
l_document_header_id,
l_document_type,
l_po_release_id
from po_change_requests
where change_request_group_id = p_chg_req_grp_id;
select segment1,
revision_num,
pos_totals_po_sv.get_po_total(po_header_id),
currency_code,
vendor_id,
vendor_site_id,
to_char(creation_date, fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
fob_lookup_code,
ship_via_lookup_code,
ship_to_location_id,
type_lookup_code,
GLOBAL_AGREEMENT_FLAG
into
l_po_num,
l_revision_num,
l_po_total,
l_po_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code,
l_global_agreement_flag
from po_headers_all
where po_header_id = l_document_header_id;
select ph.segment1,
pr.release_num,
pr.revision_num,
pos_totals_po_sv.get_release_total(pr.po_release_id),
ph.currency_code,
ph.vendor_id,
ph.vendor_site_id,
to_char(pr.creation_date, fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
ph.fob_lookup_code,
ph.ship_via_lookup_code,
ph.ship_to_location_id,
ph.type_lookup_code,
global_agreement_flag
into
l_blanket_num,
l_release_num,
l_revision_num,
l_rel_total,
l_rel_currency,
l_vendor_id,
l_vendor_site_id,
l_order_date,
l_fob,
l_carrier,
l_ship_to_id,
l_type_lookup_code,
l_global_agreement_flag
from po_releases_all pr,
po_headers_all ph
where pr.po_release_id = l_po_release_id
and pr.po_header_id = ph.po_header_id;
select vendor_name
into l_supplier_name
from po_vendors
where vendor_id = l_vendor_id;
select
address_line1,
address_line2,
address_line3,
city,
state,
zip
into
l_sup_address_line1,
l_sup_address_line2,
l_sup_address_line3,
l_sup_city,
l_sup_state,
l_sup_zip
from po_vendor_sites_all
where vendor_site_id = l_vendor_site_id;
select
address_line_1,
address_line_2,
address_line_3,
town_or_city,
region_1,
postal_code
into
l_ship_addr_l1,
l_ship_addr_l2,
l_ship_addr_l3,
l_ship_city,
l_ship_state,
l_ship_zip
from hr_locations_all
where location_id = l_ship_to_id;
''
|| fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
';
';
';
';
';
';
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select
pv.vendor_name,
pha.segment1,
pha.type_lookup_code,
pha.GLOBAL_AGREEMENT_FLAG,
pha.revision_num,
pos_totals_po_sv.get_po_total(pha.po_header_id),
pha.currency_code,
PDSL.DISPLAY_NAME
into
l_supplier_name,
l_po_num,
l_type_lookup_code,
l_global_agreement_flag,
l_revision_num,
l_po_total,
l_currency_code,
l_display_name
from
po_headers_all pha,
po_vendors pv,
PO_ALL_DOC_STYLE_LINES PDSL
where pha.po_header_id = l_header_id
and pha.vendor_id = pv.vendor_id
AND NVL(PHA.STYLE_ID, 0) = PDSL.STYLE_ID (+)
AND PHA.TYPE_LOOKUP_CODE = PDSL.DOCUMENT_SUBTYPE (+)
AND PDSL.ENABLED_FLAG (+) = 'Y' AND PDSL.STATUS (+) = 'ACTIVE'
AND PDSL.LANGUAGE (+) = USERENV('LANG');
select
pv.vendor_name,
pha.segment1,
pra.release_num,
pha.type_lookup_code,
pra.revision_num,
pos_totals_po_sv.get_release_total(pra.po_release_id),
pha.currency_code
into
l_supplier_name,
l_blanket_num,
l_release_num,
l_type_lookup_code,
l_rel_revision_num,
l_rel_total,
l_rel_currency_code
from
po_releases_all pra,
po_headers_all pha,
po_vendors pv
where pra.po_release_id = l_release_id
and pra.po_header_id = pha.po_header_id
and pha.vendor_id = pv.vendor_id;
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select pha.segment1,
hou.name,
pha.type_lookup_code,
pha.global_agreement_flag,
PDSL.DISPLAY_NAME
into l_po_num,
l_buyer_name,
l_type_lookup_code,
l_global_agreement_flag,
l_display_name
from po_headers_all pha,
hr_all_organization_units_tl hou,
PO_ALL_DOC_STYLE_LINES PDSL
where pha.po_header_id = l_header_id
and pha.org_id = hou.organization_id(+)
and hou.language(+) = userenv('LANG')
AND NVL(PHA.STYLE_ID, 0) = PDSL.STYLE_ID (+)
AND PHA.TYPE_LOOKUP_CODE = PDSL.DOCUMENT_SUBTYPE (+)
AND PDSL.ENABLED_FLAG (+) = 'Y' AND PDSL.STATUS (+) = 'ACTIVE'
AND PDSL.LANGUAGE (+) = USERENV('LANG');
select pha.segment1,
pra.release_num,
hou.name,
pha.type_lookup_code
into l_blanket_num,
l_release_num,
l_buyer_name,
l_type_lookup_code
from po_headers_all pha,
po_releases_all pra,
hr_all_organization_units_tl hou
where pra.po_release_id = l_release_id
and pha.po_header_id = pra.po_header_id
and pha.org_id = hou.organization_id(+)
and hou.language(+) = userenv('LANG');
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select pha.type_lookup_code,
pha.segment1,
pv.vendor_name,
pha.global_agreement_flag,
PDSL.display_name
into l_type_lookup_code,
l_po_num,
l_supplier_name,
l_global_agreement_flag,
l_display_name
from po_headers_all pha,
po_vendors pv,
PO_ALL_DOC_STYLE_LINES PDSL
where pha.po_header_id = l_header_id
and pv.vendor_id = pha.vendor_id
AND NVL(PHA.STYLE_ID, 0) = PDSL.STYLE_ID (+)
AND PHA.TYPE_LOOKUP_CODE = PDSL.DOCUMENT_SUBTYPE (+)
AND PDSL.ENABLED_FLAG (+) = 'Y' AND PDSL.STATUS (+) = 'ACTIVE'
AND PDSL.LANGUAGE (+) = USERENV('LANG');
select pha.type_lookup_code,
pha.segment1,
pra.release_num,
pv.vendor_name
into l_type_lookup_code,
l_blanket_num,
l_release_num,
l_supplier_name
from po_headers_all pha,
po_releases_all pra,
po_vendors pv
where pra.po_release_id = l_release_id
and pra.po_header_id = pha.po_header_id
and pv.vendor_id = pha.vendor_id;
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select pha.type_lookup_code,
pha.segment1,
pv.vendor_name,
pha.global_agreement_flag,
PDSL.display_name
into l_type_lookup_code,
l_po_num,
l_supplier_name,
l_global_agreement_flag,
l_display_name
from po_headers_all pha,
po_vendors pv,
PO_ALL_DOC_STYLE_LINES PDSL
where pha.po_header_id = l_header_id
and pv.vendor_id = pha.vendor_id
AND NVL(PHA.STYLE_ID, 0) = PDSL.STYLE_ID (+)
AND PHA.TYPE_LOOKUP_CODE = PDSL.DOCUMENT_SUBTYPE (+)
AND PDSL.ENABLED_FLAG (+) = 'Y' AND PDSL.STATUS (+) = 'ACTIVE'
AND PDSL.LANGUAGE (+) = USERENV('LANG');
select pha.type_lookup_code,
pha.segment1,
pra.release_num,
pv.vendor_name
into l_type_lookup_code,
l_blanket_num,
l_release_num,
l_supplier_name
from po_headers_all pha,
po_releases_all pra,
po_vendors pv
where pra.po_release_id = l_release_id
and pra.po_header_id = pha.po_header_id
and pv.vendor_id = pha.vendor_id;
select pcr.document_line_locatiON_id
from po_change_requests pcr,
po_distributions_all pda
where pcr.document_line_location_id = pda.line_location_id
and pcr.change_request_group_id = id -- added checks FPS Shipment Amount, Shipment price Prorate
and (pcr.new_quantity is not null or pcr.new_amount is not null or pcr.new_price is not null)
group by pcr.document_line_location_id
having count(1) > 1;
select count(*) into l_retro_count
from po_change_requests pcr, po_headers_all poh
where poh.po_header_id = pcr.document_header_id
and poh.po_header_id = l_po_header_id
and poh.type_lookup_code = 'BLANKET'
and pcr.change_request_group_id = l_chg_req_grp_id
and pcr.po_release_id is null
and pcr.request_level in ('LINE', 'SHIPMENT')
and pcr.action_type='MODIFICATION'
and ((pcr.new_price is not null) or (pcr.new_quantity is not null))
and pcr.request_status in ('PENDING');
select count(*)
into l_header_change
from po_change_requests
where change_request_group_id = l_chg_req_grp_id
and request_level = 'HEADER'
and additional_changes is not null;
*update authorization_status of PO to "APPROVED".
*/
procedure CHG_STATUS_TO_APPROVED( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2)
IS
l_header_id number;
select count(*)
into l_add_changes_accepted
from po_change_requests
where change_request_group_id = l_chg_req_grp_id
-- and request_status = 'ACCEPTED'
and request_status = 'BUYER_APP' /* fix for bug 3691061 */
and request_level = 'HEADER'
and additional_changes is not null;
select max(responded_by) into l_responded_by
from po_change_requests
where request_level = 'HEADER'
-- and request_status = 'ACCEPTED'
and request_status = 'BUYER_APP' /* fix for bug 3691061 */
and additional_changes is not null
and change_request_group_id = l_chg_req_grp_id;
update po_releases_all set
authorization_status = 'REQUIRES REAPPROVAL',
revision_num = revision_num + 1,
revised_date = sysdate,
last_update_date = sysdate,
last_updated_by = nvl(l_responded_by, last_updated_by),
change_requested_by = null,
approved_flag = 'R'
where po_release_id = l_release_id;
update po_headers_all set
authorization_status = 'REQUIRES REAPPROVAL',
revision_num = revision_num + 1,
revised_date = sysdate,
last_update_date = sysdate,
last_updated_by = nvl(l_responded_by, last_updated_by),
change_requested_by = null,
approved_flag = 'R'
where po_header_id = l_header_id;
update po_change_requests
set change_active_flag = 'N'
where change_request_group_id = l_chg_req_grp_id;
update po_releases_all
set authorization_status = l_authorization_status,
change_requested_by = null
where po_release_id = l_release_id;
update po_headers_all
set authorization_status = l_authorization_status,
change_requested_by = null
where po_header_id = l_header_id;
select revision_num
into l_revision_num
from po_releases_all
where po_release_id = l_document_id;
select revision_num
into l_revision_num
from po_headers_all
where po_header_id = l_document_id;
select acceptance_required_flag
into l_acc_flag
from po_releases_all
where po_release_id = l_document_id;
select acceptance_required_flag
into l_acc_flag
from po_headers_all
where po_header_id = l_document_id;
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and action_type = 'CANCELLATION'
and request_level = 'HEADER';
select distinct created_by ,
change_request_group_id
from po_change_requests
where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
and ((document_header_id = x_document_id) OR (po_release_id = x_document_id))
and initiator = 'SUPPLIER'
and document_type = x_document_type;
SELECT document_line_location_id
FROM PO_CHANGE_REQUESTS
WHERE change_request_group_id = l_chg_req_grp_id_csr
AND request_level = 'SHIPMENT'
AND request_status = 'BUYER_APP'
AND (NEW_AMOUNT is not null OR NEW_QUANTITY is not null);
SELECT distinct fndu.employee_id
FROM po_change_requests pcr,
fnd_user fndu
WHERE pcr.change_request_group_id = l_chg_req_grp_id_csr
AND fndu.user_id = pcr.responded_by;
select distinct created_by,
change_request_group_id
into l_supplier_user_id ,
l_change_request_group_id
from po_change_requests
where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
and po_release_id = l_document_id
and initiator = 'SUPPLIER'
and document_type = 'RELEASE';
select pha.segment1,
pha.po_header_id, -- RDP changes
pra.release_num,
pra.revision_num, -- RDP changes
hou.name,
pra.authorization_status,
pha.type_lookup_code,
pra.agent_id,
pha.vendor_id,
pra.acceptance_required_flag -- bug 4868859
into l_blanket_num,
l_po_header_id,
l_release_num,
l_revision_num,
l_buyer_name ,
l_authorization_status,
l_type_lookup_code,
l_buyer_agent_id,
l_vendor_id,
l_acceptance_required_flag
from po_headers_all pha,
po_releases_all pra,
hr_all_organization_units_tl hou
where pra.po_release_id = l_document_id
and pha.po_header_id = pra.po_header_id
and pha.org_id = hou.organization_id(+)
and hou.language(+) = userenv('LANG');
select revision_num
into l_revision_num2
from po_releases_archive_all
where po_release_id = l_document_id
and latest_external_flag='Y';
update po_releases_all
set change_requested_by = null
where po_release_id = l_document_id;
Update_Chg_Req_If_Po_Apprvd(null,l_document_id);
select distinct created_by ,
change_request_group_id
into l_supplier_user_id ,
l_change_request_group_id
from po_change_requests
where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
and document_header_id = l_document_id
and initiator = 'SUPPLIER'
and document_type = 'PO';
select pha.segment1,
hou.name,
pha.authorization_status,
pha.revision_num,
pha.type_lookup_code,
pha.agent_id,
pha.GLOBAL_AGREEMENT_FLAG,
pha.vendor_id,
pha.acceptance_required_flag
into l_po_num,
l_buyer_name,
l_authorization_status,
l_revision_num,
l_type_lookup_code,
l_buyer_agent_id,
l_global_agreement_flag,
l_vendor_id,
l_acceptance_required_flag
from po_headers_all pha,
hr_all_organization_units_tl hou
where pha.po_header_id = l_document_id
and pha.org_id = hou.organization_id(+)
and hou.language(+) = userenv('LANG');
select revision_num
into l_revision_num2
from po_headers_archive_all
where po_header_id=l_document_id
and latest_external_flag='Y';
update po_headers_all
set change_requested_by = null
where po_header_id = l_document_id;
Update_Chg_Req_If_Po_Apprvd(l_document_id,null);
select user_name
INTO l_user_name
from fnd_user
where user_id = l_supplier_user_id;
update po_change_requests
set request_status = 'ACCEPTED'
where change_request_group_id = l_change_request_group_id
and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
/* added code for bug 3574114 - update change active flag due to final buyer response */
update po_change_requests
set change_active_flag = 'N'
where change_request_group_id = l_change_request_group_id;
update po_change_requests
set request_status = 'REJECTED',
response_reason = null
where change_request_group_id = l_change_request_group_id
and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
/* added code for bug 3574114 - update change active flag due to final buyer response */
update po_change_requests
set change_active_flag = 'N'
where change_request_group_id = l_change_request_group_id;
select user_name
into l_supplier_username
from fnd_user
where user_id = l_supplier_user_id;
select vendor_name
into l_role_display_name
from po_vendors
where vendor_id=l_vendor_id;
select max(need_by_date)+180
into l_expiration_date
from po_line_locations
where po_header_id = to_number(l_document_id)
and cancel_flag = 'N';
select max(need_by_date)+180
into l_expiration_date
from po_line_locations
where po_release_id = to_number(l_document_id)
and cancel_flag = 'N';
select PO_SUPPLIER_CHANGE_WF_S.nextval into l_seq from dual;
select change_request_id from
po_change_requests
where action_type = 'MODIFICATION'
and change_request_group_id = grp_id;
select
type_lookup_code,
agent_id
into
l_doc_subtype,
l_employee_id
from po_headers_all
where po_header_id = l_header_id;
select
pha.type_lookup_code,
pra.agent_id
into
l_doc_subtype,
l_employee_id
from po_headers_all pha, po_releases_all pra
where pra.po_release_id = l_release_id
and pra.po_header_id = pha.po_header_id;
InsertActionHist(
p_doc_id => l_doc_id,
p_doc_type => l_doc_type,
p_doc_subtype => l_doc_subtype,
p_employee_id => null,
p_action => 'SUBMIT CHANGE',
p_note => null,
p_path_id => null);
select count(1)
into l_count
from po_change_requests
where initiator = 'SUPPLIER'
and document_type = 'RELEASE'
and po_release_id = l_document_id
and request_status = 'BUYER_APP';
select count(1)
into l_cancel_app_count
from po_change_requests
where initiator = 'SUPPLIER'
and document_type = 'RELEASE'
and po_release_id = l_document_id
and request_status = 'WAIT_CANCEL_APP';
select distinct(change_request_group_id)
into l_change_request_group_id
from po_change_requests
where initiator = 'SUPPLIER'
and document_type = 'RELEASE'
and po_release_id = l_document_id
and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
update po_change_requests set request_status = 'ACCEPTED'
where initiator = 'SUPPLIER'
and document_type = 'RELEASE'
and po_release_id = l_document_id
and request_status = 'WAIT_CANCEL_APP';
select count(1)
into l_count
from po_change_requests
where initiator = 'SUPPLIER'
and document_type = 'PO'
and document_header_id = l_document_id
and request_status = 'BUYER_APP';
select count(1)
into l_cancel_app_count
from po_change_requests
where initiator = 'SUPPLIER'
and document_type = 'PO'
and document_header_id = l_document_id
and request_status = 'WAIT_CANCEL_APP';
select distinct(change_request_group_id)
into l_change_request_group_id
from po_change_requests
where initiator = 'SUPPLIER'
and document_type = 'PO'
and document_header_id = l_document_id
and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
update po_change_requests set request_status = 'ACCEPTED'
where initiator = 'SUPPLIER'
and document_type = 'PO'
and document_header_id = l_document_id
and request_status = 'WAIT_CANCEL_APP';
SELECT distinct fndu.employee_id
FROM po_change_requests pcr,
fnd_user fndu
WHERE pcr.change_request_group_id = l_chg_req_grp_id_csr
AND fndu.user_id = pcr.responded_by;
select PO_SUPPLIER_CHANGE_WF_S.nextval into l_seq from dual;
select pha.agent_id
into l_buyer_agent_id
from po_headers_all pha
where pha.po_header_id = p_header_id;
select pra.agent_id
into l_buyer_agent_id
from po_releases_all pra
where pra.po_release_id = p_release_id;
select max(created_by)
into l_supplier_user_id
from po_change_requests
where change_request_group_id = p_chg_req_grp_id;
select user_name
into l_supplier_username
from fnd_user
where user_id = l_supplier_user_id;
select change_request_group_id
into l_chn_req_grp_id
from po_change_requests
where document_header_id = p_header_id
and request_status = 'PENDING'
and request_level = 'HEADER'
and initiator = 'SUPPLIER'
and action_type ='CANCELLATION';
select change_request_group_id
into l_chn_req_grp_id
from po_change_requests
where po_release_id = p_release_id
and request_status = 'PENDING'
and request_level = 'HEADER'
and initiator = 'SUPPLIER'
and action_type ='CANCELLATION';
update po_change_requests
set request_status = 'BUYER_APP',
responded_by = p_user_id,
response_date = sysdate,
response_reason = p_reason
where change_request_group_id = l_chn_req_grp_id;
update po_change_requests
set request_status = 'REJECTED',
change_active_flag = 'N',
responded_by = p_user_id,
response_date = sysdate,
response_reason = p_reason
where change_request_group_id = l_chn_req_grp_id;
p_mass_update_releases IN VARCHAR2 DEFAULT NULL -- Bug 3373453
)
IS
l_api_name varchar2(50):= 'ProcessResponse';
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and request_status in ('ACCEPTED','BUYER_APP');
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and request_status = 'REJECTED';
select change_request_id
from po_change_requests
where change_request_group_id = grp_id
and request_status = 'PENDING';
select
por.org_id ,
por.agent_id,
pha.type_lookup_code,
nvl(por.closed_code,'OPEN')
into
l_org_id,
l_agent_id,
l_doc_subtype,
l_closed_code
from po_releases_all por,
po_headers_all pha
where po_release_id = p_release_id
and por.po_header_id= pha.po_header_id;
select
poh.agent_id,
poh.org_id,
poh.type_lookup_code,
nvl(poh.closed_code,'OPEN')
into
l_agent_id,
l_org_id,
l_doc_subtype,
l_closed_code
from po_headers_all poh
where poh.po_header_id = p_header_id ;
select employee_id into l_employee_id from fnd_user where user_id = p_user_id;
InsertActionHist(
p_doc_id => l_doc_id,
p_doc_type => l_doc_type,
p_doc_subtype => l_doc_subtype,
p_employee_id => l_employee_id,
-- p_employee_id => l_agent_id,
-- p_employee_id => p_user_id, -- RDP ( instead of agent_id , user_id should be used)
p_action => l_action,
p_note => null,
p_path_id => null);
select distinct wf_item_key
into l_item_key
from po_change_requests
where change_request_group_id = p_chg_req_grp_id;
update po_change_requests
set request_status = 'ACCEPTED'
-- change_active_flag = 'N' /* commented out due to bug 3574114 */
where change_request_group_id = p_chg_req_grp_id
and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
and action_type = 'CANCELLATION';
update po_line_locations_all
set approved_flag = 'Y'
where po_header_id=p_header_id
and po_release_id is null;
update po_line_locations_all
set approved_flag = 'Y'
where po_header_id=p_header_id
and po_release_id = p_release_id;
select count(1)
into l_number_of_buyer_app
from po_change_requests
where change_request_group_id = p_chg_req_grp_id
and request_status = 'BUYER_APP';
p_mass_update_releases
);
update po_change_requests
set request_status = 'WAIT_MGR_APP'
where change_request_group_id = p_chg_req_grp_id
and request_status = 'BUYER_APP';
select pda.deliver_to_person_id
from
po_change_requests pcr,
po_distributions_all pda,
po_line_locations_all pll
where pcr.change_request_group_id = grp_id
and pcr.request_level = 'LINE'
and pcr.document_line_id = pda.po_line_id
and pda.line_location_id = pll.line_location_id
and pll.shipment_type = 'STANDARD'
union
select pda.deliver_to_person_id
from
po_change_requests pcr,
po_distributions_all pda
where pcr.change_request_group_id = grp_id
and pcr.request_level = 'SHIPMENT'
and pcr.document_line_location_id = pda.line_location_id;
select
distinct mtp.employee_id
from
mtl_system_items msi,
mtl_planners mtp,
po_change_requests pcr,
po_lines_all pla,
financials_system_params_all fsp
where pcr.change_request_group_id = grp_id
and pcr.document_line_id = pla.po_line_id
and pla.item_id = msi.inventory_item_id
and pla.org_id = fsp.org_id
and fsp.inventory_organization_id= msi.organization_id
and msi.planner_code = mtp.planner_code
and msi.organization_id = mtp.organization_id;
select agent_id
into l_agent_id
from po_headers_all
where po_header_id = l_header_id;
select agent_id
into l_agent_id
from po_releases_all
where po_release_id = l_release_id;
update po_change_requests
set request_status ='BUYER_APP',
responded_by = l_responded_by,
response_date = sysdate
where change_request_group_id = l_chg_req_grp_id
and request_status in ('PENDING','REQ_APP'); -- RDP ( Update records lying in REQ_APP status too)
select distinct porh.requisition_header_id
from po_requisition_headers_all porh,
po_requisition_lines_all porl,
po_headers_all poh,
po_line_locations_all poll
where porh.requisition_header_id = porl.requisition_header_id AND
porl.line_location_id = poll.line_location_id AND
poh.po_header_id = poll.po_header_id AND
poh.po_header_id = p_po_header_id_csr;
update po_requisition_headers_all
set change_pending_flag = 'N'
where requisition_header_id = l_req_hdr_id
AND change_pending_flag = 'Y';
update po_change_requests
set request_status ='REJECTED',
change_active_flag = 'N',
responded_by = fnd_global.user_id,
response_date = sysdate
where change_request_group_id = l_chg_req_grp_id
and request_status in ('PENDING','REQ_APP'); -- RDP ( Update records lying in REQ_APP status too)
select PO_SUPPLIER_CHANGE_WF_S.nextval into l_seq from dual;
select user_name
into l_supplier_username
from fnd_user
where user_id = fnd_global.user_id;
select agent_id
into l_buyer_agent_id
from po_headers_all
where po_headers_all.po_header_id = p_header_id;
select pra.agent_id
into l_buyer_agent_id
from po_releases_all pra,
po_headers_all pha
where pra.po_release_id = p_release_id
and pra.po_header_id = pha.po_header_id;
/*update data to include wf item key and item type. p_chg_req_grp_id could be null in the case where user accepts/rejects
all shipments and no changes was made. Thus, in this case, we will NOT store the wf_item_key or wf_item_type anywhere.
*/
if(p_chg_req_grp_id is not null) then
update po_change_requests
set wf_item_type = 'POSCHORD',wf_item_key = l_item_key
where change_request_group_id = p_chg_req_grp_id;
select fr.responsibility_id,fu.user_id
from wf_local_user_roles wur,
fnd_responsibility fr,
financials_system_params_all fsp,
fnd_user fu
where wur.user_name = fu.user_name
and wur.role_orig_system = 'FND_RESP'
and wur.role_orig_system_id = fr.responsibility_id
and wur.partition_id = 2
and (((wur.start_date is NULL) or (trunc(sysdate) >= trunc(wur.start_date)))
and ((wur.expiration_date is NULL) or
(trunc(sysdate) < trunc(wur.expiration_date)))
and ((wur.user_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.user_start_date)))
and ((wur.user_end_date is NULL) or
(trunc(sysdate) < trunc(wur.user_end_date)))
and ((wur.role_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.role_start_date)))
and ((wur.role_end_date is NULL) or
(trunc(sysdate) < trunc(wur.role_end_date))))
and fr.application_id = 201
and fr.start_date < sysdate
and nvl(fr.end_date, sysdate +1) >= sysdate
and nvl(fsp.org_id,-1) = nvl(p_org_id,-1)
and nvl(fsp.business_group_id,-1) = nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL, fr.responsibility_id, fr.application_id),-1);
SELECT FND.user_id
INTO l_user_id
FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
WHERE HR.EMPLOYEE_ID = p_employee_id
AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
AND ROWNUM = 1;
select fr.responsibility_id
into l_resp_id
from wf_local_user_roles wur,
fnd_responsibility fr,
financials_system_params_all fsp
where
wur.role_orig_system = 'FND_RESP'
and wur.role_orig_system_id = fr.responsibility_id
and wur.partition_id = 2
and (((wur.start_date is NULL) or (trunc(sysdate) >= trunc(wur.start_date)))
and ((wur.expiration_date is NULL) or
(trunc(sysdate) < trunc(wur.expiration_date)))
and ((wur.user_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.user_start_date)))
and ((wur.user_end_date is NULL) or
(trunc(sysdate) < trunc(wur.user_end_date)))
and ((wur.role_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.role_start_date)))
and ((wur.role_end_date is NULL) or
(trunc(sysdate) < trunc(wur.role_end_date))))
and fr.application_id = 201
and fr.start_date < sysdate
and nvl(fr.end_date, sysdate +1) >= sysdate
and nvl(fsp.org_id,-1) = nvl(p_org_id,-1);
select MIN(fr.responsibility_id)
into l_resp_id
from wf_local_user_roles wur,
fnd_responsibility fr,
financials_system_params_all fsp,
fnd_user fu
where wur.user_name = fu.user_name
and fu.user_id = l_user_id
and wur.role_orig_system = 'FND_RESP'
and wur.role_orig_system_id = fr.responsibility_id
and wur.partition_id = 2
and (((wur.start_date is NULL) or (trunc(sysdate) >= trunc(wur.start_date)))
and ((wur.expiration_date is NULL) or
(trunc(sysdate) < trunc(wur.expiration_date)))
and ((wur.user_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.user_start_date)))
and ((wur.user_end_date is NULL) or
(trunc(sysdate) < trunc(wur.user_end_date)))
and ((wur.role_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.role_start_date)))
and ((wur.role_end_date is NULL) or
(trunc(sysdate) < trunc(wur.role_end_date))))
and fr.application_id = l_resp_appl_id
and fr.start_date < sysdate
and nvl(fr.end_date, sysdate +1) >= sysdate
and nvl(fsp.org_id,-1) = nvl(p_org_id,-1);
select MIN(fr.responsibility_id),fu.user_id
into l_resp_id,l_user_id
from wf_local_user_roles wur,
fnd_responsibility fr,
financials_system_params_all fsp,
fnd_user fu
where wur.user_name = fu.user_name
and wur.role_orig_system = 'FND_RESP'
and wur.role_orig_system_id = fr.responsibility_id
and wur.partition_id = 2
and (((wur.start_date is NULL) or (trunc(sysdate) >= trunc(wur.start_date)))
and ((wur.expiration_date is NULL) or
(trunc(sysdate) < trunc(wur.expiration_date)))
and ((wur.user_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.user_start_date)))
and ((wur.user_end_date is NULL) or
(trunc(sysdate) < trunc(wur.user_end_date)))
and ((wur.role_start_date is NULL) or
(trunc(sysdate) >= trunc(wur.role_start_date)))
and ((wur.role_end_date is NULL) or
(trunc(sysdate) < trunc(wur.role_end_date))))
and fr.application_id = l_resp_appl_id
and fr.start_date < sysdate
and nvl(fr.end_date, sysdate +1) >= sysdate
and nvl(fsp.org_id,-1) = nvl(p_org_id,-1);
select wur.user_name
into l_user_name
from wf_user_roles wur,wf_users wr
where wur.role_name=p_ntf_role_name and
wr.name =wur.user_name and
upper(wr.email_address)=upper(l_email);
select additional_changes
from po_change_requests
where change_request_group_id = chg_req_grp_id_csr
and request_level = 'HEADER'
and additional_changes is not null;
SELECT COUNT(1) FROM po_change_requests WHERE NOT EXISTS ( SELECT 1
FROM po_change_requests
WHERE change_request_group_id = chg_req_grp_id_csr
AND request_level IN ('LINE','SHIPMENT'))
AND change_request_group_id = chg_req_grp_id_csr
AND action_type = 'MODIFICATION';
select distinct(porla.requisition_header_id)
from po_requisition_lines_all porla,
po_line_locations_all polla
where polla.po_header_id = c_po_header_id
and porla.line_location_id = polla.line_location_id;
select porha.segment1
from po_requisition_headers_all porha
where requisition_header_id = c_req_header_id;
select distinct(porla.requisition_header_id)
from po_requisition_lines_all porla,
po_line_locations_all polla
where polla.po_header_id = c_po_header_id
and polla.po_release_id = c_po_release_id
and porla.line_location_id = polla.line_location_id;
select decode(count(distinct(action_type)),2,'BOTH',1,'CHG') into
l_req_mode
from
po_change_requests
where
change_request_group_id = p_chg_req_grp_id
and request_level in('LINE','SHIPMENT')
and action_type in('MODIFICATION','CANCELLATION');
select distinct(action_type) into
l_temp
from
po_change_requests
where
change_request_group_id = p_chg_req_grp_id
and request_level in('LINE','SHIPMENT')
and action_type in('MODIFICATION','CANCELLATION');
SELECT 'N'
INTO l_ship_invalid_for_ctrl_actn
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM po_line_locations_all poll1,
po_line_locations_all poll2
WHERE poll1.line_location_id = po_line_location_id
AND poll1.po_line_id = poll2.po_line_id
AND NVL(poll2.cancel_flag,'N') <> 'Y'
AND NVL(poll2.payment_type, 'NULL') NOT IN ('ADVANCE', 'DELIVERY') --
AND NVL(poll2.closed_code, 'OPEN')
<> 'FINALLY CLOSED'
AND poll2.line_location_id <> po_line_location_id);
SELECT count(*)
FROM po_change_requests pcr
WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr
AND pcr.REQUEST_LEVEL = 'HEADER'
AND pcr.ACTION_TYPE = 'CANCELLATION';
SELECT pcr.action_type,
pcr.request_status,
poh.type_lookup_code,
pcr.document_num
FROM PO_CHANGE_REQUESTS pcr,
PO_HEADERS_ALL poh
WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr
AND pcr.REQUEST_LEVEL = 'HEADER'
AND pcr.ACTION_TYPE = 'CANCELLATION'
AND pcr.document_header_id = poh.po_header_id;
SELECT distinct(pcr.document_revision_num)
FROM po_change_requests pcr
WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr;
SELECT revision_num
FROM po_headers_all
WHERE po_header_id = po_header_id_csr;
SELECT pora.revision_num
FROM po_releases_all pora
WHERE pora.po_header_id = po_header_id_csr
AND pora.po_release_id = po_release_id_csr;
SELECT fu.USER_ID
INTO l_responder_id
FROM fnd_user fu,
wf_notifications wfn
WHERE wfn.notification_id = l_notification_id
AND wfn.original_recipient = fu.user_name;
PROCEDURE PO_SUPCHG_SELECTOR ( p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
resultout IN OUT NOCOPY VARCHAR2) IS
-- Declare context setting variables start
l_session_user_id NUMBER;
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','Inside PO_SUPCHG_SELECTOR procedure');
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','p_itemtype : '||p_itemtype);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','p_itemkey : '||p_itemkey);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','p_actid : '||p_actid);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','p_funcmode : '||p_funcmode);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','l_progress1 : '||l_progress);
l_progress := '020 selection fn responder id not null';
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','l_progress2 : '||l_progress);
l_progress :='030 selector fn : setting user id :'||l_responder_id ||' resp id '||l_resp_id_to_set||' l_appl id '||l_appl_id_to_set;
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','l_progress3 : '||l_progress);
l_progress := '040 selector fn responder id null';
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','l_progress4 : '||l_progress);
l_progress := '050 selector fn : set user '||l_user_id_to_set||' resp id ' ||l_resp_id_to_set||' appl id '||l_appl_id_to_set;
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','l_progress4 : '||l_progress);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.PO_ChangeOrderWF_PVT.PO_SUPCHG_SELECTOR.invoked','Exception in Selector Procedure');
WF_CORE.context('PO_ChangeOrderWF_PVT', 'PO_SUPCHG_SELECTOR', p_itemtype, p_itemkey, p_actid, p_funcmode);
END PO_SUPCHG_SELECTOR;