How to load Action History for PO through back end?
Base Table for PO Action History?
Important scenarios for PO action History?
Important Derivation/Validation:
Derive person id:
SELECT papf.person_id
FROM per_all_people_f papf
WHERE NVL(papf.employee_numbernpw_number) = p_emp_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
OR
SELECT papf.person_id
FROM per_all_people_f papf
WHERE NVL(papf.employee_numbernpw_number) = p_emp_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND current_employee_flag = 'Y';
Derive PO header id:
SELECT DISTINCT po_header_id
FROM po_headers_all
WHERE segment1= p_po_number;
Check History is already existing or not:
SELECT 'S'
INTO l_exist
FROM po.po_action_history poh
WHERE poh.object_id= p_po_header_id_
AND poh.sequence_num = p_sequence_num
AND poh.action_code= p_action_code
AND poh.employee_id= p_person_id;
PO.PO_ACTION_HISTORY (Base Table):
OBJECT_ID => p_po_header_id
OBJECT_TYPE_CODE => 'PO'
OBJECT_SUB_TYPE_CODE => 'STANDARD'
SEQUENCE_NUM => p_sequence_num
LAST_UPDATE_DATE => sysdate
LAST_UPDATED_BY => fnd_global.user_id
CREATION_DATE => sysdate
CREATED_BY => fnd_global.user_id
ACTION_CODE => p_action_code
ACTION_DATE => p_action_date
EMPLOYEE_ID => p_employee_id
APPROVAL_PATH_ID => p_approval_path_id
NOTE => p_note
OBJECT_REVISION_NUM => p_object_revision_num
OFFLINE_CODE => p_offline_code
LAST_UPDATE_LOGIN => fnd_global.login_id
REQUEST_ID => fnd_global.conc_request_id
PROGRAM_APPLICATION_ID => p_program_application_id
PROGRAM_ID => p_program_id
PROGRAM_UPDATE_DATE => p_program_update_date
PROGRAM_DATE => p_program_date
APPROVAL_GROUP_ID => p_approval_group_id
Base Table for PO Action History?
Important scenarios for PO action History?
Load Action History of PO through back end:
Important Derivation/Validation:
Derive person id:
SELECT papf.person_id
FROM per_all_people_f papf
WHERE NVL(papf.employee_numbernpw_number) = p_emp_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
OR
SELECT papf.person_id
FROM per_all_people_f papf
WHERE NVL(papf.employee_numbernpw_number) = p_emp_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND current_employee_flag = 'Y';
Derive PO header id:
SELECT DISTINCT po_header_id
FROM po_headers_all
WHERE segment1= p_po_number;
Check History is already existing or not:
SELECT 'S'
INTO l_exist
FROM po.po_action_history poh
WHERE poh.object_id= p_po_header_id_
AND poh.sequence_num = p_sequence_num
AND poh.action_code= p_action_code
AND poh.employee_id= p_person_id;
PO.PO_ACTION_HISTORY (Base Table):
OBJECT_ID => p_po_header_id
OBJECT_TYPE_CODE => 'PO'
OBJECT_SUB_TYPE_CODE => 'STANDARD'
SEQUENCE_NUM => p_sequence_num
LAST_UPDATE_DATE => sysdate
LAST_UPDATED_BY => fnd_global.user_id
CREATION_DATE => sysdate
CREATED_BY => fnd_global.user_id
ACTION_CODE => p_action_code
ACTION_DATE => p_action_date
EMPLOYEE_ID => p_employee_id
APPROVAL_PATH_ID => p_approval_path_id
NOTE => p_note
OBJECT_REVISION_NUM => p_object_revision_num
OFFLINE_CODE => p_offline_code
LAST_UPDATE_LOGIN => fnd_global.login_id
REQUEST_ID => fnd_global.conc_request_id
PROGRAM_APPLICATION_ID => p_program_application_id
PROGRAM_ID => p_program_id
PROGRAM_UPDATE_DATE => p_program_update_date
PROGRAM_DATE => p_program_date
APPROVAL_GROUP_ID => p_approval_group_id
COMMENTS