[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure UpdateWorkflowInfo
(
p_itemtype in varchar2,
p_itemkey in varchar2,
p_shipment_header_id in varchar2);
select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
into l_seq_for_item_key
from sys.dual;
select count(*)
into l_wf_created
from wf_items
where item_type = p_itemtype
and item_key = l_itemkey;
select shipment_num
into l_work_confirmation_number
from rcv_shipment_headers
where shipment_header_id = p_work_confirmation_id
and nvl(asn_type,'STD') = 'WC';
select max(po_header_id)
into l_po_header_id
from rcv_shipment_lines
where shipment_header_id = p_work_confirmation_id;
** Update the old notifications to closed status for this document.
*/
procedure Close_old_notif
(
p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2 ) IS
l_work_confirmation_id Number;
select wf_item_key
from rcv_shipment_headers
where shipment_header_id = p_header_id;
select created_by
into l_wc_preparer_id
from rcv_shipment_headers
where shipment_header_id= l_work_confirmation_id;
select employee_id
into l_employee_id
from fnd_user
where user_id = l_wc_preparer_id;
select poh.segment1,
poh.agent_id
into l_po_number,
l_po_preparer_id
from po_headers_all poh
where po_header_id = l_po_header_id;
procedure update_workflow_info( p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2) is
l_shipment_header_id number;
l_api_name varchar2(50) := p_itemkey || ' update_workflow_info';
l_progress := 'POS_WCAPPROVE_PVT.update_workflow_info: 01.';
l_api_name,'Enter in update_workflow_info ' || l_progress);
l_progress := 'POS_WCAPPROVE_PVT.update_workflow_info: 02.';
UpdateWorkflowInfo(p_itemtype,p_itemkey,l_shipment_header_id);
l_progress := 'POS_WCAPPROVE_PVT.update_workflow_info: 02.';
l_api_name,'Leave update_workflow_info ' || l_progress);
l_api_name,'Exception in update_workflow_info '
|| l_progress);
end update_workflow_info;
select pk1_value
from fnd_attached_documents
where entity_name='RCV_LINES'
and pk1_value in (select shipment_line_id
from rcv_shipment_lines
where shipment_header_id=l_shipment_header_id);
select count(*)
into l_attach_count
from fnd_attached_documents
where entity_name =' RCV_LINES'
and pk1_value in (select shipment_line_id
from rcv_shipment_lines
where shipment_header_id=l_shipment_header_id);
/* Insert a submit action row in po_action_history to signal the
* submisinon of the document for Approval. Also insert an
* additional row with a NULL ACTION_CODE to simulate a
* forward-to. We need to do this for each shipment line in this
* document.
*/
procedure Ins_actionhist_submit(p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2)is
l_progress varchar2(200);
select shipment_line_id
from rcv_shipment_lines
where shipment_header_id=l_shipment_header_id;
select max(sequence_num)
from po_action_history
where object_id=l_shipment_header_id
and object_type_code = 'WC';
select action_code
from po_action_history
where object_id = l_shipment_header_id
and object_type_code='WC'
and sequence_num = l_seq_num;
l_api_name,'before call to InsertPOActionHistory ' ||
l_sequence_num);
InsertPOActionHistory(
p_object_id => l_shipment_header_id,
p_object_type_code=>'WC',
p_object_sub_type_code => NULL,
p_sequence_num =>l_sequence_num ,
p_action_code =>'SUBMIT' ,
p_action_date =>sysdate ,
p_employee_id => null, --l_wc_preparer_id forsupplier
p_approval_path_id => NULL ,
p_note => NULL,
p_object_revision_num => NULL,
p_offline_code => '',
p_request_id => 0,
p_program_application_id => 0,
p_program_id =>0 ,
p_program_date => sysdate ,
p_user_id => fnd_global.user_id ,
p_login_id => fnd_global.login_id);
/* Insert the null action code*/
IF (g_asn_debug = 'Y') THEN
debug_log(FND_LOG.LEVEL_STATEMENT,
l_api_name,'insert null action_code ' ||
l_sequence_num);
/*InsertPOActionHistory(
p_object_id => l_shipment_header_id,
p_object_type_code=>'WC',
p_object_sub_type_code => NULL,
p_sequence_num =>l_sequence_num+1 ,
p_action_code =>NULL ,
p_action_date =>sysdate ,
p_employee_id => null,--l_wc_preparer_id for supplier
p_approval_path_id => NULL ,
p_note => NULL,
p_object_revision_num => NULL,
p_offline_code => '',
p_request_id => 0,
p_program_application_id => 0,
p_program_id =>0 ,
p_program_date => sysdate ,
p_user_id => fnd_global.user_id ,
p_login_id => fnd_global.login_id); */
l_insertion_type VARCHAR2(30);
SELECT person_id, full_name
into l_next_approver_id,l_next_approver_name
FROM (
SELECT person.person_id, person.full_name
FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = l_next_approver(1).orig_system_id
and trunc(sysdate) between person.effective_start_date
and nvl(person.effective_end_date, trunc(sysdate))
and person.person_id = asg.person_id
and asg.primary_flag = 'Y'
and asg.assignment_type in ('E','C')
and ( person.current_employee_flag = 'Y'
or person.current_npw_flag = 'Y' )
and asg.assignment_status_type_id not in
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
)
and trunc(sysdate) between asg.effective_start_date
and asg.effective_end_date
order by person.last_name
) where rownum = 1;
SELECT employee_id
into l_next_approver_id
FROM fnd_user
WHERE user_id = l_next_approver(1).orig_system_id
and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
l_api_name,'l_insertion_type '
|| l_insertion_type);
aname => 'AME_INSERTION_TYPE' ,
avalue => l_insertion_type);
procedure Insert_Action_History(p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2) is
l_progress varchar2(200);
l_api_name varchar2(50) := p_itemkey || ' Insert_Action_History';
select shipment_line_id
from rcv_shipment_lines
where shipment_header_id=l_shipment_header_id;
SELECT object_id,
object_type_code,
object_sub_type_code,
sequence_num,
object_revision_num,
request_id,
program_application_id,
program_date,
program_id,
last_update_date,
employee_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'WC'
AND object_id = l_shipment_header_id
AND sequence_num = l_sequence_num;
l_progress := 'POS_WCAPPROVE_PVT.Insert_Action_History: 01.';
l_api_name,'Enter in Insert_Action_History '
|| l_progress);
l_progress := 'POS_WCAPPROVE_PVT.Insert_Action_History: 04.';
SELECT count(*)
INTO l_count
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'WC'
AND object_id = l_shipment_header_id
AND action_code IS NULL;
SELECT max(sequence_num)
INTO l_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'WC'
AND object_id = l_shipment_header_id;
** want to update the first NULL row in POAH
** with FORWARD action
*/
If l_count = 1 then
IF (g_asn_debug = 'Y') THEN
debug_log(FND_LOG.LEVEL_STATEMENT,
l_api_name,
'update Action history ' );
UpdatePOActionHistory(
p_object_id => Recinfo.object_id,
p_object_type_code => Recinfo.object_type_code,
p_employee_id => Recinfo.employee_id,
p_action_code => 'FORWARD',
p_note => NULL,
p_user_id => fnd_global.user_id,
p_login_id => fnd_global.login_id);
'Insert Action history ' );
InsertPOActionHistory(
p_object_id => Recinfo.object_id,
p_object_type_code=>Recinfo.object_type_code,
p_object_sub_type_code => NULL,
p_sequence_num => Recinfo.sequence_num+1 ,
p_action_code =>NULL ,
p_action_date =>NULL ,
p_employee_id => l_approver_id,
p_approval_path_id => NULL ,
p_note => NULL,
p_object_revision_num => NULL,
p_offline_code => NULL,
p_request_id => Recinfo.request_id,
p_program_application_id => Recinfo.program_application_id,
p_program_id =>Recinfo.program_id ,
p_program_date => Recinfo.program_date ,
p_user_id => fnd_global.user_id ,
p_login_id => fnd_global.login_id);
'Exit Insert Action history ' );
l_api_name,'invalid_action in Insert_Action_History '
|| l_progress);
l_api_name,'invalid_data in Insert_Action_History '
|| l_progress);
l_api_name,'Exception in Insert_Action_History '
|| l_progress);
end Insert_Action_History;
SELECT notification_id
FROM wf_item_activity_statuses_v
WHERE item_type = p_itemtype
AND item_key = p_itemkey
AND activity_name = p_activity_name
ORDER BY activity_end_date DESC;
SELECT attribute_value
into l_note
FROM wf_notification_attr_resp_v
WHERE group_id = l_notification_id
AND attribute_name = 'NOTE';
update_approval_status(p_shipment_header_id => l_shipment_header_id,
p_note => l_note,
p_approval_status => 'APPROVED',
p_level => 'LINES',
x_resultout => l_result);
SELECT notification_id
FROM wf_item_activity_statuses_v
WHERE item_type = p_itemtype
AND item_key = p_itemkey
AND activity_name = p_activity_name
ORDER BY activity_end_date DESC;
SELECT attribute_value
into l_note
FROM wf_notification_attr_resp_v
WHERE group_id = l_notification_id
AND attribute_name = 'NOTE';
update_approval_status(p_shipment_header_id => l_shipment_header_id,
p_note => l_note,
p_approval_status => 'REJECTED',
p_level => 'LINES',
x_resultout => l_result);
select count(*)
into l_reject_lines
from rcv_shipment_lines
where shipment_header_id = l_shipment_header_id
and approval_status = 'REJECTED';
procedure Update_Approval_List_Response
(p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2) IS
CURSOR c_group_id (p_itemtype VARCHAR2,
p_itemkey VARCHAR2,
p_activity_name VARCHAR2) IS
SELECT notification_id
FROM wf_item_activity_statuses_v
WHERE item_type = p_itemtype
AND item_key = p_itemkey
AND activity_name = p_activity_name
ORDER BY activity_end_date DESC;
SELECT '1'
FROM WF_NOTIFICATIONS
WHERE notification_id = p_notif_id
AND status = 'CANCELED';
SELECT recipient_role, attribute_value
FROM wf_notification_attr_resp_v
WHERE group_id = p_group_id
AND attribute_name = 'RESULT';
SELECT attribute_value
FROM wf_notification_attr_resp_v
WHERE group_id = p_group_id
AND attribute_name = 'NOTE';
SELECT nvl((wfu.orig_system_id), -9996)
FROM wf_users wfu
WHERE wfu.name = p_responder
AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
l_api_name varchar2(50) := p_itemkey || ' update_approval_list_response';
l_insertion_type VARCHAR2(30);
l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response: 01.';
'Enter in Update_Approval_List_Response '
|| l_progress);
l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
|| ': 02.';
SELECT wfn.responder, wfn.recipient_role, wfn.end_date
INTO l_responder, l_recipient_role, l_response_end_date
FROM wf_notifications wfn
WHERE wfn.notification_id = l_group_id;
l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
|| ':02.';
l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
|| ': 03.';
l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
|| ': 04.';
l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
|| ': 05.';
l_insertion_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
itemkey => p_itemkey,
aname => 'AME_INSERTION_TYPE');
' l_insertion_type '
|| l_insertion_type);
select count(*)
into l_reject_lines
from rcv_shipment_lines
where shipment_header_id = l_shipment_header_id
and approval_status = 'REJECTED';
ame_api2.updateApprovalStatus(applicationIdIn=>l_application_Id, transactionIdIn=>l_shipment_header_id,
approverIn=>l_current_approver,
transactionTypeIn=>l_transaction_type);
l_api_name,'Exception in update_approval_list_response '
|| l_progress);
end Update_Approval_List_Response;
procedure Update_Action_History_Approve
(p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2) IS
l_progress varchar2(300);
l_api_name varchar2(50) := p_itemkey || ' update_action_history_approve';
l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_History_Approve: 01.';
'Enter in Update_Action_History_Approve '
|| l_progress);
Update_Action_History(p_itemtype => p_itemtype,
p_itemkey => p_itemkey,
p_action_code => 'APPROVE');
'Leave in Update_Action_History_Approve '
|| l_progress);
l_api_name,'Exception in update_action_history_approve '
|| l_progress);
end Update_Action_History_Approve;
procedure Update_Action_History_Reject
(p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2) IS
l_progress varchar2(300);
l_api_name varchar2(50) := p_itemkey || ' update_action_history_Reject';
l_progress := 'POS_WCAPPROVE_PVT.Update_action_history_reject: 01.';
'Enter in Update_Action_History_Reject '
|| l_progress);
Update_Action_History(p_itemtype => p_itemtype,
p_itemkey => p_itemkey,
p_action_code => 'REJECT');
'Leave in Update_Action_History_Reject '
|| l_progress);
l_api_name,'Exception in update_action_history_Reject '
|| l_progress);
end Update_Action_History_REJECT;
procedure Update_Action_History
(p_itemtype in varchar2,
p_itemkey in varchar2,
p_action_code in varchar2) IS
CURSOR c_responderid(l_responder VARCHAR2) IS
SELECT nvl((wfu.orig_system_id), -9996)
FROM wf_users wfu
WHERE wfu.name = l_responder
AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
select rsl.shipment_line_id ,
nvl(rsl.comments,rsh.comments),
rsl.approval_status
from rcv_shipment_lines rsl,
rcv_shipment_headers rsh
where rsh.shipment_header_id = l_shipment_header_id
and rsh.shipment_header_id= rsl.shipment_header_id;
l_api_name varchar2(50) := p_itemkey || ' update_action_history';
l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 01.';
'Enter in Update_Action_History '
|| l_progress);
l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 02.';
SELECT nvl(max(wf.notification_id), -9995)
into l_notification_id
FROM wf_notifications wf,
wf_item_activity_statuses wias
WHERE wias.item_type = p_itemtype
and wias.item_key = p_itemkey
and wias.notification_id = wf.group_id;
l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 03.';
SELECT wfn.responder, wfn.recipient_role,
wfn.original_recipient, wfn.more_info_role
INTO l_responder, l_recipient_role,
l_original_recipient, l_more_info_role
FROM wf_notifications wfn
WHERE wfn.notification_id = l_notification_id;
SELECT wfu.orig_system_id
INTO l_original_recipient_id
FROM wf_roles wfu
WHERE wfu.name = l_original_recipient
AND wfu.orig_system NOT IN ('POS', 'ENG_LIST', 'CUST_CONT');
l_progress := 'POS_WCAPPROVE_PVT.update_approval_history: 04.';
UpdateActionHistory(l_more_origsysid,
l_original_recipient_id,
l_responder_id,
FALSE,
p_action_code,
l_comments,
l_shipment_header_id);
l_api_name,'After updateactionhistory' ||
l_shipment_header_id);
l_api_name,'Leave update_action_history' );
l_api_name,'Exception in Update_Action_History '
|| l_progress);
END Update_Action_History;
PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
p_original_recipient_id IN NUMBER,
p_responder_id IN NUMBER,
p_last_approver IN BOOLEAN,
p_action_code IN VARCHAR2,
p_comments IN VARCHAR2,
p_shipment_header_id IN NUMBER)
IS
-- pragma AUTONOMOUS_TRANSACTION;
l_api_name varchar2(50) := 'UpdateActionHistory';
SELECT ph.action_code action_code ,
ph.object_type_code object_type_code ,
ph.sequence_num sequence_num,
ph.approval_path_id approval_path_id,
ph.request_id request_id ,
ph.program_application_id program_application_id,
ph.program_date program_date ,
ph.program_id program_id ,
ph.last_update_date last_update_date,
ph.object_id object_id,
ph.employee_id employee_id
FROM
rcv_shipment_headers rsh,
po_action_history ph
WHERE rsh.shipment_header_id = ph.object_id
and rsh.shipment_header_id=p_shipment_header_id
and ph.sequence_num = l_sequence_num;
l_progress := 'POS_WCAPPROVE_PVT.UpdateActionHistory: 01.';
l_api_name,'Enter in UpdateActionHistory '
|| l_progress);
SELECT max(sequence_num)
INTO l_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'WC'
AND object_id = p_shipment_header_id;
'Before call to InsertPOActionHistory ');
InsertPOActionHistory(
p_object_id => Recinfo.object_id,
p_object_type_code=>Recinfo.object_type_code,
p_object_sub_type_code => NULL,
p_sequence_num => Recinfo.sequence_num+1 ,
p_action_code =>NULL ,
p_action_date =>NULL ,
p_employee_id => p_original_recipient_id,
p_approval_path_id => NULL ,
p_note => NULL,
p_object_revision_num => NULL,
p_offline_code => NULL,
p_request_id => Recinfo.request_id,
p_program_application_id => Recinfo.program_application_id,
p_program_id =>Recinfo.program_id ,
p_program_date => Recinfo.program_date ,
p_user_id => fnd_global.user_id ,
p_login_id => fnd_global.login_id);
** post notification function so that the update
** to action history can be viewed at the moment of
** reassignment. The following code is used to handle
** request for more info:
** 1. at the moment an approver requests for more info,
** action history is updated (performed within post
** notification)
** 2. if the approver approve/reject the requisition
** before the more info request is responded
** then we need to update the action history
** to reflect 'no action' from the more info
*/
IF (p_more_info_id is not null) THEN --{
/*
** update the original NULL row for the
** original approver with
** action code of 'NO ACTION'
*/
UpdatePOActionHistory(
p_object_id => Recinfo.object_id,
p_object_type_code => Recinfo.object_type_code,
p_employee_id => p_more_info_id,
p_action_code => 'NO ACTION',
p_note => NULL,
p_user_id => fnd_global.user_id,
p_login_id => fnd_global.login_id);
** insert a new NULL row into PO_ACTION_HISTORY for
** the new approver
*/
InsertPOActionHistory(
p_object_id => Recinfo.object_id,
p_object_type_code=>Recinfo.object_type_code,
p_object_sub_type_code => NULL,
p_sequence_num => Recinfo.sequence_num+1 ,
p_action_code =>NULL ,
p_action_date =>NULL ,
p_employee_id => p_responder_id,
p_approval_path_id => NULL ,
p_note => NULL,
p_object_revision_num => NULL,
p_offline_code => NULL,
p_request_id => Recinfo.request_id,
p_program_application_id => Recinfo.program_application_id,
p_program_id =>Recinfo.program_id ,
p_program_date => Recinfo.program_date ,
p_user_id => fnd_global.user_id ,
p_login_id => fnd_global.login_id);
l_progress := 'POS_WCAPPROVE_PVT.UpdateActionHistory: 02.';
** update pending row of action history with approval action
*/
IF (g_asn_debug = 'Y') THEN
debug_log(FND_LOG.LEVEL_STATEMENT,
l_api_name,
'Before call to UpdatePOActionHistory '
||l_progress);
UpdatePOActionHistory(
p_object_id => Recinfo.object_id,
p_object_type_code => Recinfo.object_type_code,
p_employee_id => Recinfo.employee_id,
p_action_code => p_action_code,
p_note =>substrb(p_comments,1,4000),
p_user_id => fnd_global.user_id,
p_login_id => fnd_global.login_id);
'No data found in UpdateActionHistory '
|| l_progress);
l_api_name,'Exception in UpdateActionHistory '
|| l_progress);
END UpdateActionHistory;
PROCEDURE UpdatePOActionHistory (p_object_id IN NUMBER,
p_object_type_code IN VARCHAR2,
p_employee_id IN NUMBER,
p_action_code IN VARCHAR2,
p_note IN VARCHAR2,
p_user_id IN NUMBER,
p_login_id IN NUMBER)
IS
l_progress VARCHAR2(250) := '';
l_api_name varchar2(50) := ' UpdatePOActionHistory';
l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 01.';
l_api_name,'Enter in UpdatePOActionHistory '
|| l_progress);
SELECT HR.EMPLOYEE_ID
INTO l_employee_id
FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
WHERE FND.USER_ID = NVL(p_user_id, fnd_global.user_id)
AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID;
l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 02.';
l_api_name,'Before Update '
|| l_progress);
select comments
into l_note
from rcv_shipment_headers
where shipment_header_id=p_object_id;
UPDATE PO_ACTION_HISTORY
SET last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
employee_id = p_employee_id,
--employee_id = NVL(l_employee_id, employee_id),
action_date = sysdate,
action_code = p_action_code,
note = nvl(p_note,l_note)
WHERE object_id = p_object_id
AND object_type_code = p_object_type_code
AND action_code IS NULL;
l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 02.';
l_api_name,'invalid_action in UpdatePOActionHistory '
|| l_progress);
l_api_name,'Exception in UpdatePOActionHistory '
|| l_progress);
END UpdatePOActionHistory;
PROCEDURE InsertPOActionHistory (p_object_id IN NUMBER,
p_object_type_code IN VARCHAR2,
p_object_sub_type_code IN VARCHAR2,
p_sequence_num IN NUMBER,
p_action_code IN VARCHAR2,
p_action_date IN DATE,
p_employee_id IN NUMBER,
p_approval_path_id IN NUMBER,
p_note IN VARCHAR2,
p_object_revision_num IN NUMBER,
p_offline_code IN VARCHAR2,
p_request_id IN NUMBER,
p_program_application_id IN NUMBER,
p_program_id IN NUMBER,
p_program_date IN DATE,
p_user_id IN NUMBER,
p_login_id IN NUMBER)
IS
-- pragma AUTONOMOUS_TRANSACTION;
l_api_name varchar2(50) := ' InsertPOActionHistory';
l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 01.';
l_api_name,'Enter in InsertPOActionHistory '
|| l_progress);
l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 02.';
SELECT MAX(sequence_num)
INTO l_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_id = p_object_id
AND object_type_code = p_object_type_code;
l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 03.';
l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 04.';
l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 05.';
l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 06.';
l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 07.';
l_api_name,'l_sequence_num before insert '
|| 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,
employee_id,
action_code,
action_date,
note,
object_revision_num,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_date,
approval_path_id,
offline_code,
program_update_date)
VALUES (p_object_id,
'WC',
'WC',
l_sequence_num,
sysdate,
p_user_id,
p_employee_id,
p_action_code,
p_action_date,
l_note,
p_object_revision_num,
p_login_id,
sysdate,
p_user_id,
p_request_id,
p_program_application_id,
p_program_id,
p_program_date,
p_approval_path_id,
p_offline_code,
sysdate);
l_api_name,'Exception in InsertPOActionHistory '
|| l_progress);
END InsertPOActionHistory;
select rsl.shipment_line_id
from rcv_shipment_lines rsl
where rsl.shipment_header_id = l_shipment_header_id;
SELECT object_id,
object_type_code,
object_sub_type_code,
sequence_num,
object_revision_num,
request_id,
program_application_id,
program_date,
program_id,
last_update_date,
employee_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'WC'
AND object_id = l_shipment_header_id
AND sequence_num = l_sequence_num;
select max(sequence_num)
into l_sequence_num
from po_action_history
where object_type_code ='WC'
and object_id = l_shipment_header_id;
UpdatePOActionHistory( p_object_id => Recinfo.object_id,
p_object_type_code => Recinfo.object_type_code,
p_employee_id => Recinfo.employee_id,
p_action_code => l_action,
p_note => wf_engine.context_user_comment,
p_user_id => fnd_global.user_id,
p_login_id => fnd_global.login_id);
InsertPOActionHistory( p_object_id => Recinfo.object_id,
p_object_type_code=>Recinfo.object_type_code,
p_object_sub_type_code => NULL,
p_sequence_num => Recinfo.sequence_num+1 ,
p_action_code =>NULL ,
p_action_date =>NULL ,
p_employee_id => l_new_recipient_id,
p_approval_path_id => NULL ,
p_note => NULL,
p_object_revision_num => NULL,
p_offline_code => NULL,
p_request_id => Recinfo.request_id,
p_program_application_id => Recinfo.program_application_id,
p_program_id =>Recinfo.program_id ,
p_program_date => Recinfo.program_date ,
p_user_id => fnd_global.user_id ,
p_login_id => fnd_global.login_id);
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;
update_approval_status(p_shipment_header_id => l_shipment_header_id,
p_note => null,
p_approval_status => l_approval_status,
p_level => 'HEADER',
x_resultout => l_result);
update_approval_status(p_shipment_header_id => l_shipment_header_id,
p_note => null,
p_approval_status => l_approval_status,
p_level => 'HEADER',
x_resultout => l_result);
procedure update_approval_status
(p_shipment_header_id in number,
p_note in varchar2,
p_approval_status in varchar2,
p_level in varchar2,
x_resultout out NOCOPY varchar2) IS
cursor lock_rsh(l_shipment_header_id number) is
select null
from rcv_shipment_headers
where shipment_header_id = l_shipment_header_id
for update of shipment_header_id nowait;
l_api_name varchar2(50) := ' update_approval_status';
l_progress := 'POS_WCAPPROVE_PVT.update_approval_status: 01.';
'Enter in update_approval_status '
|| l_progress);
update rcv_shipment_headers
set approval_status = p_approval_status,
comments = nvl(p_note,comments)
where shipment_header_id = p_shipment_header_id;
* So update the header with the comments.
*/
update rcv_shipment_lines
set approval_status = p_approval_status
where shipment_header_id=p_shipment_header_id ;
update rcv_shipment_headers
set comments = nvl(p_note,comments)
where shipment_header_id = p_shipment_header_id;
l_api_name,'Exception in update_approval_status '
|| l_progress);
END update_approval_status;
procedure insert_into_rti(p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
x_resultout out NOCOPY varchar2) IS
l_progress varchar2(300);
l_api_name varchar2(50) := p_itemkey || ' insert_into_rti';
SELECT rsl.po_line_location_id,
pll.unit_meas_lookup_code,
rsl.unit_of_measure unit_of_measure,
rsl.unit_of_measure primary_unit_of_measure,
rsl.unit_of_measure source_doc_unit_of_measure,
NVL(pll.promised_date, pll.need_by_date) promised_date,
rsl.to_organization_id ship_to_organization_id,
null quantity_ordered,
null amount_ordered,
null quantity_funded,
null amount_funded,
null partial_funded_flag,
NVL(pll.price_override, pl.unit_price) po_unit_price,
pll.match_option,
rsl.category_id,
rsl.item_description,
pl.po_line_id,
ph.currency_code,
ph.rate_type currency_conversion_type,
ph.segment1 document_num,
null po_distribution_id, --pod.po_distribution_id,
rsl.req_distribution_id,
rsl.requisition_line_id,
rsl.deliver_to_location_id deliver_to_location_id,
rsl.deliver_to_location_id location_id,
rsl.deliver_to_person_id,
null currency_conversion_date, --pod.rate_date currency_conversion_date,
null currency_conversion_rate, --pod.rate currency_conversion_rate,
rsl.destination_type_code destination_type_code,
rsl.destination_type_code destination_context,
null charge_account_id, --pod.code_combination_id ,
null destination_organization_id, --pod.destination_organization_id,
null subinventory, --pod.destination_subinventory ,
rsl.ship_to_location_id,
rsl.comments,
rsl.attribute_category attribute_category,
rsl.attribute1 attribute1,
rsl.attribute2 attribute2,
rsl.attribute3 attribute3,
rsl.attribute4 attribute4,
rsl.attribute5 attribute5,
rsl.attribute6 attribute6,
rsl.attribute7 attribute7,
rsl.attribute8 attribute8,
rsl.attribute9 attribute9,
rsl.attribute10 attribute10,
rsl.attribute11 attribute11,
rsl.attribute12 attribute12,
rsl.attribute13 attribute13,
rsl.attribute14 attribute14,
rsl.attribute15 attribute15,
NVL(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code,
rsl.shipment_line_id,
rsl.item_id,
rsl.item_revision,
rsh.vendor_id,
rsh.shipment_num,
rsh.freight_carrier_code,
rsh.bill_of_lading,
rsh.packing_slip,
rsh.shipped_date,
rsh.expected_receipt_date,
rsh.waybill_airbill_num ,
rsh.vendor_site_id,
rsl.to_organization_id,
rsl.routing_header_id,
rsl.vendor_item_num,
rsl.vendor_lot_num,
rsl.ussgl_transaction_code,
rsl.government_context,
pll.po_header_id,
ph.revision_num po_revision_num,
pl.line_num document_line_num,
pll.shipment_num document_shipment_line_num,
null document_distribution_num , --pod.distribution_num
pll.po_release_id,
pl.job_id,
ph.org_id,
rsl.amount_shipped amount,
rsl.quantity_shipped quantity,
rsl.quantity_shipped source_doc_quantity,
rsl.quantity_shipped primary_quantity,
rsl.quantity_shipped quantity_shipped,
rsl.amount_shipped amount_shipped,
rsl.requested_amount requested_amount,
rsl.material_stored_amount material_stored_amount,
pll.matching_basis,
Nvl(pdsh.clm_flag,'N') clm_flag
FROM
--po_distributions_all pod,
po_line_locations_all pll,
po_lines_all pl,
po_headers_all ph,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
po_doc_style_headers pdsh
WHERE
rsh.shipment_header_id = l_shipment_header_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rsl.po_header_id = ph.po_header_id
--and pod.po_header_id = ph.po_header_id
--and pod.line_location_id = pll.line_location_id
and rsl.po_line_id = pl.po_line_id
and rsl.po_line_location_id = pll.line_location_id
and rsh.receipt_source_code = 'VENDOR'
and pll.po_line_id = pl.po_line_id
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
AND NVL(ph.STYLE_ID, 1) = PDSH.STYLE_ID (+)
AND PDSH.STATUS (+) = 'ACTIVE';
select pod.po_distribution_id,
pod.rate_date currency_conversion_date,
pod.rate currency_conversion_rate,
pod.code_combination_id charge_account_id,
pod.destination_organization_id,
pod.destination_subinventory subinventory,
pod.distribution_num document_distribution_num,
pod.quantity_ordered,
pod.amount_ordered,
pod.quantity_funded,
pod.amount_funded,
Nvl(pod.partial_funded_flag,'N') partial_funded_flag,
pod.destination_type_code destination_type_code,
pod.destination_type_code destination_context
from po_distributions_all pod
where pod.line_location_id = l_line_location_id
order by pod.distribution_num;
select rsl.shipment_line_id
from rcv_shipment_lines rsl
where rsl.shipment_header_id = l_shipment_header_id;
l_insert_into_rti boolean := TRUE;
l_req_amount_inserted BOOLEAN := FALSE;
l_mat_stored_inserted BOOLEAN := FALSE;
l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti: 01.';
'Enter in insert_into_rti '
|| l_progress);
SELECT rcv_headers_interface_s.NEXTVAL
INTO l_header_interface_id
FROM SYS.DUAL;
select rcv_interface_groups_s.nextval
into l_group_id
from dual;
Modified the earlier logic used to insert data in RTI tables, since it was not populating data correctly in the RTI tables.
Removed the earlier commented code to incorporate the new logic.
Please refer to comments and detailed technical resolution data in bug 88100238 BCT for more information
******************************************************************** */
-- opening the work confirmation cursor at pay item level
open get_wcr_info(l_shipment_header_id);
l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti:02.';
select muom.uom_code
into l_uom_code
from mtl_units_of_measure muom
WHERE muom.unit_of_measure = wcr_line_info.unit_of_measure;
SELECT Count(*)
INTO l_max_dist
FROM po_distributions_all pod
where pod.line_location_id = wcr_line_info.po_line_location_id;
l_req_amount_inserted := FALSE;
l_mat_stored_inserted := FALSE;
-- looping through the distributions cursor to insert data in RTI
loop --{
l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti:03.';
select nvl(sum(amount),0)
into l_transacted_amount
from rcv_transactions
where po_distribution_id= wcr_line_info.po_distribution_id
and destination_type_code = 'RECEIVING';
select nvl(sum(amount),0)
into l_interface_amount
from rcv_transactions_interface
where po_distribution_id= wcr_line_info.po_distribution_id
and processing_status_code='PENDING'
and transaction_status_code = 'PENDING'
and transaction_type = 'RECEIVE';
-- last distribution for pay item, insert the l_remaining_amount completely
IF (l_clm_flag = 'Y' and l_partial_funded_flag = 'Y') THEN
IF(l_available_amount > 0) THEN
IF(l_carry_over_amount > 0) THEN
wcr_line_info.amount := l_available_amount;
l_insert_into_rti := TRUE;
l_insert_into_rti := FALSE;
l_insert_into_rti := TRUE;
IF(l_req_amount_inserted) THEN
wcr_line_info.requested_amount := null;
IF(l_mat_stored_inserted) THEN
wcr_line_info.material_stored_amount := null;
l_req_amount_inserted := TRUE;
l_mat_stored_inserted := TRUE;
-- not the last distribution for the pay item, check if we need to insert or not
IF(l_available_amount > 0) THEN
-- this distribution is not yet completely filled,
-- so we "need to insert" depending on l_remaining_amount and l_available_amount
IF(l_carry_over_amount > 0) THEN
-- the shipped amount is greater than the l_available_amount
wcr_line_info.amount := l_available_amount;
IF(l_req_amount_inserted) THEN
wcr_line_info.requested_amount := null;
IF(l_mat_stored_inserted) THEN
wcr_line_info.material_stored_amount := null;
l_req_amount_inserted := TRUE;
l_mat_stored_inserted := TRUE;
l_insert_into_rti := TRUE;
-- l_available_amount < 0, so "no need to insert"
l_insert_into_rti := FALSE;
select nvl(sum(quantity),0)
into l_transacted_quantity
from rcv_transactions
where po_distribution_id= wcr_line_info.po_distribution_id
and destination_type_code = 'RECEIVING';
select nvl(sum(quantity),0)
into l_interface_quantity
from rcv_transactions_interface
where po_distribution_id= wcr_line_info.po_distribution_id
and processing_status_code='PENDING'
and transaction_status_code = 'PENDING'
and transaction_type = 'RECEIVE';
l_insert_into_rti := TRUE;
l_insert_into_rti := FALSE;
l_insert_into_rti := TRUE;
-- not the last distribution for the pay item, check if we need to insert or not
IF(l_available_quantity > 0) THEN
-- this distribution is not yet completely filled,
-- so we "need to insert" depending on l_remaining_quantity and l_available_quantity
IF(l_carry_over_quantity > 0) THEN
-- the shipped quantity is greater than the l_available_quantity
wcr_line_info.quantity := l_available_quantity;
l_insert_into_rti := TRUE;
-- l_available_amount < 0, so "no need to insert"
l_insert_into_rti := FALSE;
If (l_insert_into_rti) then --{
select rcv_transactions_interface_s.nextval
into l_interface_id
from dual;
SELECT set_of_books_id
INTO x_sob_Id
FROM financials_system_parameters WHERE org_id = wcr_line_info.org_id;
rcv_asn_interface_trx_ins_pkg.insert_row
(l_row_id,
l_interface_id,--interface_id
l_group_id, --group_id
sysdate, --last_updated_date
fnd_global.user_id, --last_updated_by,
sysdate, --creation_date,
fnd_global.login_id, --created_by,
fnd_global.login_id, -- last_update_login,
NULL, --request_id,
null, --program_application_id,
null, --program_id,
null, --program_update_date,
'RECEIVE', --transaction_type,
sysdate, --transaction_date,
'PENDING', --processing_status_code,
'IMMEDIATE', --processing_mode_code,
--'BATCH',
null, --processing_request_id,
'PENDING', --.transaction_status_code,
wcr_line_info.category_id,
wcr_line_info.quantity, --quantity
wcr_line_info.unit_of_measure,
'ISP', --.interface_source_code,
NULL, --.interface_source_line_id,
NULL, --.inv_transaction_id,
wcr_line_info.item_id,
wcr_line_info.item_description,
wcr_line_info.item_revision,
l_uom_code, --uom_code,
x_emp_id, --employee_id,
'DELIVER', --auto_transact_code,
l_shipment_header_id, --l_shipment_header_id
wcr_line_info.shipment_line_id,
wcr_line_info.ship_to_location_id,
wcr_line_info.primary_quantity,
wcr_line_info.primary_unit_of_measure,
'VENDOR', --.receipt_source_code,
wcr_line_info.vendor_id,
wcr_line_info.vendor_site_id,
NULL, --from_organization_id,
NULL, --from_subinventory,
wcr_line_info.to_organization_id,
NULL, --.intransit_owning_org_id,
wcr_line_info.routing_header_id,
NULL, --.routing_step_id,
'PO', --source_document_code,
NULL, --.parent_transaction_id,
wcr_line_info.po_header_id,
wcr_line_info.po_revision_num,
wcr_line_info.po_release_id,
wcr_line_info.po_line_id,
wcr_line_info.po_line_location_id,
wcr_line_info.po_unit_price,
wcr_line_info.currency_code,
wcr_line_info.currency_conversion_type,
l_currency_conversion_rate, --Bug #: 13924722 wcr_line_info.currency_conversion_rate
l_currency_conversion_date, --Bug #: 13924722 wcr_line_info.currency_conversion_date
wcr_line_info.po_distribution_id,
wcr_line_info.requisition_line_id,
wcr_line_info.req_distribution_id,
wcr_line_info.charge_account_id,
NULL, --.substitute_unordered_code,
NULL, --.receipt_exception_flag,
NULL, --.accrual_status_code,
'NOT INSPECTED' ,--.inspection_status_code,
NULL, --.inspection_quality_code,
wcr_line_info.destination_type_code,
wcr_line_info.deliver_to_person_id,
wcr_line_info.location_id,
wcr_line_info.deliver_to_location_id,
NULL, --.subinventory,
NULL, --.locator_id,
NULL, --.wip_entity_id,
NULL, --.wip_line_id,
NULL, --.department_code,
NULL, --.wip_repetitive_schedule_id,
NULL, --.wip_operation_seq_num,
NULL, --.wip_resource_seq_num,
NULL, --.bom_resource_id,
wcr_line_info.shipment_num,
wcr_line_info.freight_carrier_code,
wcr_line_info.bill_of_lading,
wcr_line_info.packing_slip,
wcr_line_info.shipped_date,
wcr_line_info.expected_receipt_date,
NULL, --.actual_cost,
NULL, --.transfer_cost,
NULL, --.transportation_cost,
NULL, --.transportation_account_id,
NULL, --.num_of_containers,
wcr_line_info.waybill_airbill_num,
wcr_line_info.vendor_item_num,
wcr_line_info.vendor_lot_num,
NULL,--.rma_reference,
wcr_line_info.comments,
wcr_line_info.attribute_category,
wcr_line_info.attribute1,
wcr_line_info.attribute2,
wcr_line_info.attribute3,
wcr_line_info.attribute4,
wcr_line_info.attribute5,
wcr_line_info.attribute6,
wcr_line_info.attribute7,
wcr_line_info.attribute8,
wcr_line_info.attribute9,
wcr_line_info.attribute10,
wcr_line_info.attribute11,
wcr_line_info.attribute12,
wcr_line_info.attribute13,
wcr_line_info.attribute14,
wcr_line_info.attribute15,
NULL, --.ship_head_attribute_category,
NULL, --.ship_head_attribute1,
NULL, --.ship_head_attribute2,
NULL, --.ship_head_attribute3,
NULL, --.ship_head_attribute4,
NULL, --.ship_head_attribute5,
NULL, --.ship_head_attribute6,
NULL, --.ship_head_attribute7,
NULL, --.ship_head_attribute8,
NULL, --.ship_head_attribute9,
NULL, --.ship_head_attribute10,
NULL, --.ship_head_attribute11,
NULL, --.ship_head_attribute12,
NULL, --.ship_head_attribute13,
NULL, --.ship_head_attribute14,
NULL, --.ship_head_attribute15,
NULL, --.ship_line_attribute_category,
NULL, --.ship_line_attribute1,
NULL, --.ship_line_attribute2,
NULL, --.ship_line_attribute3,
NULL, --.ship_line_attribute4,
NULL, --.ship_line_attribute5,
NULL, --.ship_line_attribute6,
NULL, --.ship_line_attribute7,
NULL, --.ship_line_attribute8,
NULL, --.ship_line_attribute9,
NULL, --.ship_line_attribute10,
NULL, --.ship_line_attribute11,
NULL, --.ship_line_attribute12,
NULL, --.ship_line_attribute13,
NULL, --.ship_line_attribute14,
NULL, --.ship_line_attribute15,
wcr_line_info.ussgl_transaction_code,
wcr_line_info.government_context,
NULL, --.reason_id,
wcr_line_info.destination_context,
wcr_line_info.source_doc_quantity,
wcr_line_info.source_doc_unit_of_measure,
NULL, --.movement_id,
NULL, --l_header_interface_id, --.header_interface_id,
NULL, --.vendor_cum_shipped_qty,
NULL, --.item_num,
wcr_line_info.document_num,
wcr_line_info.document_line_num,
NULL, --.truck_num,
NULL, --.ship_to_location_code,
NULL, --.container_num,
NULL, --.substitute_item_num,
NULL, --.notice_unit_price,
NULL, --.item_category,
NULL, --.location_code,
NULL, --.vendor_name,
NULL, --.vendor_num,
NULL, --.vendor_site_code,
NULL, --.from_organization_code,
NULL, --.to_organization_code,
NULL, --.intransit_owning_org_code,
NULL, --.routing_code,
NULL, --.routing_step,
NULL, --.release_num,
wcr_line_info.document_shipment_line_num,
wcr_line_info.document_distribution_num,
NULL, --.deliver_to_person_name,
NULL, --.deliver_to_location_code,
NULL, --.use_mtl_lot,
NULL, --.use_mtl_serial,
NULL, --.LOCATOR,
NULL, --.reason_name,
NULL, --.validation_flag,
NULL, --.substitute_item_id,
NULL, --.quantity_shipped,
NULL, --.quantity_invoiced,
NULL, --.tax_name,
NULL, --.tax_amount,
NULL, --.req_num,
NULL, --.req_line_num,
NULL, --.req_distribution_num,
NULL, --.wip_entity_name,
NULL, --.wip_line_code,
NULL, --.resource_code,
NULL, --.shipment_line_status_code,
NULL, --.barcode_label,
NULL, --.country_of_origin_code,
NULL, --.from_locator_id, --WMS Change
NULL, --.qa_collection_id,
NULL, --.oe_order_header_id,
NULL, --.oe_order_line_id,
NULL, --.customer_id,
NULL, --.customer_site_id,
NULL, --.customer_item_num,
NULL, --.create_debit_memo_flag,
NULL, --.put_away_rule_id,
NULL, --.put_away_strategy_id,
NULL, --.lpn_id,
NULL, --.transfer_lpn_id,
NULL, --.cost_group_id,
NULL, --.mobile_txn,
NULL, --.mmtt_temp_id,
NULL, --.transfer_cost_group_id,
NULL, --.secondary_quantity,
NULL, --.secondary_unit_of_measure,
NULL, --.secondary_uom_code,
NULL, --.qc_grade,
NULL, --.oe_order_num,
NULL, --.oe_order_line_num,
NULL, --.customer_account_number,
NULL, --.customer_party_name,
NULL, --.source_transaction_num,
NULL, --.parent_source_transaction_num,
NULL, --.parent_interface_txn_id,
NULL, --.customer_item_id,
NULL, --.interface_available_qty,
NULL, --.interface_transaction_qty,
NULL, --.from_locator,
NULL, --.lpn_group_id,
NULL, --.order_transaction_id,
NULL, --.license_plate_number,
NULL, --.transfer_license_plate_number,
wcr_line_info.amount,
wcr_line_info.job_id,
NULL, --.project_id,
NULL, --.task_id,
NULL, --.asn_attach_id,
NULL, --.timecard_id,
NULL, --.timecard_ovn,
NULL, --.interface_available_amt,
NULL, --.interface_transaction_amt
wcr_line_info.org_id, --
wcr_line_info.matching_basis,
NULL, --wcr_line_info.amount_shipped, --amount_shipped
wcr_line_info.requested_amount,
wcr_line_info.material_stored_amount
);
l_api_name,'After insert ' );
select min(pod.po_distribution_id)
INTO l_full_fund_dist_id
from po_distributions_all pod
where pod.line_location_id = wcr_line_info.po_line_location_id AND
Nvl(pod.partial_funded_flag,'N') = 'N';
SELECT INTERFACE_TRANSACTION_ID,
AMOUNT
INTO l_int_transaction_id,
l_full_fund_amt
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE PO_DISTRIBUTION_ID = l_full_fund_dist_id AND
GROUP_ID = l_group_id;
UPDATE RCV_TRANSACTIONS_INTERFACE RTI
SET AMOUNT = l_full_fund_amt
WHERE INTERFACE_TRANSACTION_ID = l_int_transaction_id AND
GROUP_ID = l_group_id;
SELECT INTERFACE_TRANSACTION_ID,
QUANTITY
INTO l_int_transaction_id,
l_full_fund_qty
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE PO_DISTRIBUTION_ID = l_full_fund_dist_id AND
GROUP_ID = l_group_id;
UPDATE RCV_TRANSACTIONS_INTERFACE RTI
SET QUANTITY = l_full_fund_qty
WHERE INTERFACE_TRANSACTION_ID = l_int_transaction_id AND
GROUP_ID = l_group_id;
update rcv_shipment_headers
set receipt_num= l_receipt_num,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where shipment_header_id = l_shipment_header_id;
l_api_name,'Leave insert_into_rti ' );
l_api_name,'Exception in insert_into_rti '
|| l_progress);
END insert_into_rti;
UPDATE rcv_transactions_interface
set transaction_status_code = 'ERROR'
where group_id = l_group_id;
update wf_notifications set status = 'CLOSED'
where notification_id in (
select ias.notification_id
from wf_item_activity_statuses ias,
wf_notifications ntf
where ias.item_type = p_itemtype
and ias.item_key = p_itemkey
and ntf.notification_id = ias.notification_id);
procedure UpdateWorkflowInfo
(
p_itemtype in varchar2,
p_itemkey in varchar2,
p_shipment_header_id in varchar2) IS
-- pragma AUTONOMOUS_TRANSACTION;
l_api_name varchar2(50) := p_itemkey || ' UpdateWorkflowInfo';
l_progress := 'POS_WCAPPROVE_PVT.UpdateWorkflowInfo: 01';
l_api_name,'Enter in UpdateWorkflowInfo ' || l_progress);
UPDATE rcv_shipment_headers
SET WF_ITEM_TYPE = p_itemtype,
WF_ITEM_KEY = p_itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
WHERE shipment_header_id = p_shipment_header_id;
l_api_name,'Exception in UpdateWorkflowInfo ' || l_progress);
end UpdateWorkflowInfo;
UPDATE PO_ACTION_HISTORY
set object_id = p_object_id,
object_type_code = p_object_type_code,
sequence_num = p_sequence_num,
last_update_date = sysdate,
last_updated_by = p_user_id,
creation_date = sysdate,
created_by = p_user_id,
action_code = p_action_code,
action_date = sysdate,
employee_id = p_employee_id,
last_update_login = p_login_id,
request_id = 0,
program_application_id = 0,
program_id = 0,
program_update_date = '',
offline_code = ''
WHERE
object_id= p_object_id and
object_type_code = p_object_type_code and
sequence_num = p_sequence_num;
select org_id
from po_headers_all
where po_header_id = p_document_id;
select distinct full_name
into l_value
from per_all_people_f hre
where hre.person_id = p_approver_id
and trunc(sysdate) BETWEEN effective_start_date
and effective_end_date;
select max(po_header_id)
into l_po_header_id
from rcv_shipment_lines
where shipment_header_id = p_shipment_header_id;
select ship_to_org_id
into l_ship_to_org_id
from rcv_shipment_headers
where shipment_header_id=p_shipment_header_id;
SELECT (next_receipt_num + 1)
INTO x_receipt_num
FROM rcv_parameters
WHERE organization_id = l_ship_to_org_id
FOR UPDATE OF next_receipt_num;
SELECT COUNT(*)
INTO l_count
FROM rcv_shipment_headers
WHERE receipt_num = x_receipt_num
AND ship_to_org_id = l_ship_to_org_id;
UPDATE rcv_parameters
SET next_receipt_num = x_receipt_num
WHERE organization_id = l_ship_to_org_id;
POS_WC_CREATE_UPDATE_PVT.GET_PAY_ITEM_PROGRESS
(p_wc_id,
p_wc_stage,
l_progress,
l_return_status,
l_return_msg);
select count(*)
into l_count
from gms_awards_all awd,
gms_award_distributions adl,
po_distributions_all pod
where adl.award_id = awd.award_id
and adl.adl_line_num = 1
and adl.po_distribution_id =pod.po_distribution_id
and adl.award_set_id = pod.award_id
and pod.po_distribution_id = p_wc_id;
select awd.award_number
into l_award_number
from gms_awards_all awd,
gms_award_distributions adl,
po_distributions_all pod
where adl.award_id = awd.award_id
and adl.adl_line_num = 1
and adl.po_distribution_id =pod.po_distribution_id
and adl.award_set_id = pod.award_id
and pod.po_header_id = p_wc_id;
select count(*)
into l_count
from hr_locations_all_tl hl,
po_distributions_all pod
where pod.deliver_to_location_id = hl.location_id and
hl.language (+) =userenv('LANG') and
pod.po_distribution_id = p_wc_id;
select hl.location_code
into l_deliver_to_location
from hr_locations_all_tl hl,
po_distributions_all pod
where pod.deliver_to_location_id = hl.location_id and
hl.language (+) =userenv('LANG') and
pod.po_distribution_id = p_wc_id;
select count(*)
into l_count
from po_line_locations_all poll
where poll.line_location_id = p_wc_id
and nvl(poll.matching_basis,'QUANTITY')='AMOUNT';
select poll.amount
into l_amount
from po_line_locations_all poll
where poll.line_location_id = p_wc_id
and nvl(poll.matching_basis,'QUANTITY')='AMOUNT';
select count(*)
into l_count
from po_line_locations_all poll
where poll.line_location_id = p_wc_id
and nvl(poll.matching_basis,'QUANTITY')='QUANTITY';
select poll.quantity
into l_quantity
from po_line_locations_all poll
where poll.line_location_id = p_wc_id
and nvl(poll.matching_basis,'QUANTITY')='QUANTITY';
select count(*)
into l_count
from pa_projects_all pa,
po_distributions_all pod
where pod.project_id = pa.project_id and
pod.po_distribution_id = p_wc_id;
select pa.name
into l_project_name
from pa_projects_all pa,
po_distributions_all pod
where pod.project_id = pa.project_id and
pod.po_distribution_id = p_wc_id;
select count(*)
into l_count
from pa_tasks_expend_v pa,
po_distributions_all pod
where pod.task_id = pa.task_id and
pod.po_distribution_id = p_wc_id;
select pa.task_name
into l_task_name
from pa_tasks_expend_v pa,
po_distributions_all pod
where pod.task_id = pa.task_id and
pod.po_distribution_id = p_wc_id;
select count(*)
into l_count
from gl_code_combinations_kfv glc,
po_distributions_all pod
where pod.code_combination_id = glc.code_combination_id and
pod.po_distribution_id = p_wc_id;
select glc.concatenated_segments
into l_segments
from gl_code_combinations_kfv glc,
po_distributions_all pod
where pod.code_combination_id = glc.code_combination_id and
pod.po_distribution_id = p_wc_id;
select count(*)
into l_count
from org_organization_definitions ood,
po_distributions_all pod
where pod.expenditure_organization_id = ood.organization_id
and pod.po_distribution_id = p_wc_id;
select ood.organization_name
into l_org_name
from org_organization_definitions ood,
po_distributions_all pod
where pod.expenditure_organization_id = ood.organization_id
and pod.po_distribution_id = p_wc_id;
Added the procedure POWC_SELECTOR to set the user context properly before
launching the concurrent request */
-------------------------------------------------------------------------------
-- Start of Comments
-- Name: POWC_SELECTOR
-- Pre-reqs: None.
-- Modifies:
-- Application user id
-- Application responsibility id
-- Application application id
-- Locks: None.
-- Function:
-- This procedure sets the correct application context when a process is
-- picked up by the workflow background engine. When called in
-- TEST_CTX mode it compares workflow attribute org id with the current
-- org id and workflow attributes user id, responsibility id and
-- application id with their corresponding profile values. It returns TRUE
-- if these values match and FALSE otherwise. When called in SET_CTX mode
-- it sets the correct apps context based on workflow parameters.
-- Parameters:
-- IN:
-- p_itemtype
-- Specifies the itemtype of the workflow process
-- p_itemkey
-- Specifies the itemkey of the workflow process
-- p_actid
-- activity id passed by the workflow
-- p_funcmode
-- Input values can be TEST_CTX or SET_CTX (RUN not implemented)
-- TEST_CTX to test if current context is correct
-- SET_CTX to set the correct context if current context is wrong
-- IN OUT:
-- p_x_result
-- For TEST_CTX a TRUE value means that the context is correct and
-- SET_CTX need not be called. A FALSE value means that current context
-- is incorrect and SET_CTX need to set correct context
-- Testing:
-- There is not script to test this procedure but the correct functioning
-- may be tested by verifying from the debug_message in table po_wf_debug
-- that if at any time the workflow process gets started with a wrong
-- context then the selector is called in TEST_CTX and SET_CTX modes and
-- correct context is set.
-- End of Comments
-------------------------------------------------------------------------------
PROCEDURE POWC_SELECTOR ( p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_x_result IN OUT NOCOPY VARCHAR2) IS
-- Declare context setting variables start
l_session_user_id NUMBER;
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','Inside POWC_SELECTOR procedure');
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_itemtype : '||p_itemtype);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_itemkey : '||p_itemkey);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_actid : '||p_actid);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_funcmode : '||p_funcmode);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress1 : '||l_progress);
l_progress := '020 selection fn responder id not null';
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_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.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress3 : '||l_progress);
l_progress := '040 selector fn responder id null';
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_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.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress4 : '||l_progress);
fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','Exception in Selector Procedure');
WF_CORE.context('POS_WCAPPROVE_PVT', 'POWC_SELECTOR', p_itemtype, p_itemkey, p_actid, p_funcmode);
END POWC_SELECTOR;