CREATE OR REPLACE PACKAGE BODY APPS.xx_po_approval_pkg
AS
/* --------------History -----------------------------*/
-- Created By Vishal 18 Mar 2015 For Adding Approver In PO Approval Workflow-----
-- Purpose
--1 - Check By Budget Amount
--2 - Getting Notification Note
--3 - Showing The Budget Details
--4 - Creating Action History
/* ------------End -----------------------------------*/
PROCEDURE check_budget_amt (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2)
IS
l_po_header_id NUMBER;
l_planned VARCHAR2 (2);
l_task_id NUMBER;
l_expenditure_type VARCHAR2 (50);
l_dist_attribute1 VARCHAR2 (20);
l_budget_details VARCHAR2 (3000);
l_amount NUMBER;
l_note VARCHAR2 (500);
l_performer VARCHAR2 (100);
l_performer_id NUMBER;
l_line_details VARCHAR2 (4000);
l_check_eastern_org NUMBER;
l_xx_total_approver_count NUMBER := 0;
l_xx_current_approver_number NUMBER := 0;
BEGIN
IF (funcmode = 'RUN')
THEN
IF fnd_profile.VALUE ('ORG_ID') = 721
THEN
l_po_header_id :=
wf_engine.getitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'DOCUMENT_ID');
IF EST_PKG.GET_STATUS_PO_HEADER (l_po_header_id) = 1
THEN
SELECT COUNT (*)
INTO l_xx_total_approver_count
FROM xxea_po_budget_approver
WHERE STATUS = 'Y';
wf_engine.setitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_TOTAL_APPROVER_COUNT',
avalue => l_xx_total_approver_count);
IF NVL (l_xx_total_approver_count, 0) > 0
THEN
l_xx_current_approver_number := 0;
wf_engine.setitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XXCURRENT_APPROVER_NUMBER',
avalue => l_xx_current_approver_number);
l_planned := 'N';
BEGIN
l_line_details :=
'PLSQL:APPS.XX_PO_APPROVAL_PKG.GET_LINE_DETAILS/'
|| l_po_header_id;
wf_engine.setitemattrtext (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XXLINE_DETAILS',
avalue => l_line_details);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
BUDGET_OUTPUT_MEG (l_po_header_id, l_budget_details);
wf_engine.setitemattrtext (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_BUDGET_DETAILS',
avalue => l_budget_details);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
ELSE
l_planned := 'Y';
END IF;
ELSE
l_planned := 'Y';
END IF;
ELSE
l_planned := 'Y';
END IF;
ELSE
l_planned := 'Y';
END IF;
IF l_planned = 'N'
THEN
resultout := 'N';
RETURN;
ELSE
resultout := 'Y';
RETURN;
END IF;
EXCEPTION
WHEN OTHERS
THEN
wf_core.context ('XX_PO_APPROVAL_PKG.CHECK_BUDGET_AMT',
itemtype,
itemkey,
TO_CHAR (actid),
funcmode,
SUBSTR (SQLERRM, 1, 300));
RAISE;
END check_budget_amt;
PROCEDURE get_next_approver (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2)
IS
l_po_header_id NUMBER;
l_xx_total_approver_count NUMBER := 0;
l_xx_current_approver_number NUMBER := 0;
l_XXPERFORMER VARCHAR2 (100);
XX_BUDGET_PERFORMER_ID NUMBER;
l_temp_employee_id NUMBER;
l_planned VARCHAR2 (10);
BEGIN
l_planned := 'N';
IF (funcmode = 'RUN')
THEN
l_po_header_id :=
wf_engine.getitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'DOCUMENT_ID');
l_xx_current_approver_number :=
wf_engine.getitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XXCURRENT_APPROVER_NUMBER');
l_xx_total_approver_count :=
wf_engine.getitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_TOTAL_APPROVER_COUNT');
IF l_xx_total_approver_count > l_xx_current_approver_number
THEN
l_xx_current_approver_number := l_xx_current_approver_number + 1;
SELECT USER_NAME, USER_ID
INTO l_XXPERFORMER, XX_BUDGET_PERFORMER_ID
FROM xxea_po_budget_approver
WHERE STATUS = 'Y' AND l_level = l_xx_current_approver_number;
SELECT employee_id
INTO g_system_employee_id
FROM PO_ACTION_HISTORY
WHERE object_id = l_po_header_id
AND sequence_num = (SELECT MAX (sequence_num)
FROM PO_ACTION_HISTORY
WHERE object_id = l_po_header_id)
AND ROWNUM = 1;
SELECT employee_id
INTO l_temp_employee_id
FROM fnd_user
WHERE user_id = XX_BUDGET_PERFORMER_ID;
UPDATE PO_ACTION_HISTORY
SET employee_id = l_temp_employee_id
WHERE object_id = l_po_header_id
AND sequence_num = (SELECT MAX (sequence_num)
FROM PO_ACTION_HISTORY
WHERE object_id = l_po_header_id)
AND ROWNUM = 1;
wf_engine.setitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_BUDGET_PERFORMER_ID',
avalue => XX_BUDGET_PERFORMER_ID);
wf_engine.setitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XXCURRENT_APPROVER_NUMBER',
avalue => l_xx_current_approver_number);
wf_engine.setitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'XXPERFORMER',
avalue => l_XXPERFORMER);
l_planned := 'Y';
END IF;
END IF;
IF l_planned = 'N'
THEN
resultout := 'N';
RETURN;
ELSE
resultout := 'Y';
RETURN;
END IF;
EXCEPTION
WHEN OTHERS
THEN
wf_core.context ('XX_PO_APPROVAL_PKG.CHECK_BUDGET_AMT',
itemtype,
itemkey,
TO_CHAR (actid),
funcmode,
SUBSTR (SQLERRM, 1, 300));
RAISE;
END get_next_approver;
PROCEDURE update_approval (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2)
IS
l_po_header_id NUMBER;
l_planned VARCHAR2 (10);
l_xx_current_approver_number NUMBER;
l_xx_total_approver_count NUMBER;
BEGIN
IF (funcmode = 'RUN')
THEN
l_po_header_id :=
wf_engine.getitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'DOCUMENT_ID');
l_xx_current_approver_number :=
wf_engine.getitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XXCURRENT_APPROVER_NUMBER');
l_xx_total_approver_count :=
wf_engine.getitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_TOTAL_APPROVER_COUNT');
IF l_xx_total_approver_count <= l_xx_current_approver_number
THEN
UPDATE PO_ACTION_HISTORY
SET employee_id = g_system_employee_id
WHERE object_id = l_po_header_id
AND sequence_num = (SELECT MAX (sequence_num)
FROM PO_ACTION_HISTORY
WHERE object_id = l_po_header_id)
AND ROWNUM = 1;
l_planned := 'Y';
ELSE
l_planned := 'N';
END IF;
IF l_planned = 'N'
THEN
resultout := 'N';
RETURN;
ELSE
resultout := 'Y';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
wf_core.context ('XX_PO_APPROVAL_PKG.CHECK_BUDGET_AMT',
itemtype,
itemkey,
TO_CHAR (actid),
funcmode,
SUBSTR (SQLERRM, 1, 300));
RAISE;
END update_approval;
PROCEDURE NOTIFICATION_NOTE (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
RESULT OUT VARCHAR2)
IS
l_nid NUMBER;
Note VARCHAR2 (480);
l_performer VARCHAR2 (100);
l_performer_id NUMBER;
l_po_header_id NUMBER;
l_person_id NUMBER;
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
Note := wf_notification.getattrtext (l_nid, 'BUDGET_NOTE');
l_po_header_id :=
wf_engine.getitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'DOCUMENT_ID');
l_performer_id :=
wf_engine.getitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_BUDGET_PERFORMER_ID');
SELECT employee_id
INTO l_person_id
FROM fnd_user
WHERE user_id = l_performer_id;
CREATE_ACTION_HISTORY (l_po_header_id, l_person_id, Note);
wf_engine.setitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'NOTE',
avalue => Note);
END IF;
RESULT := NULL;
EXCEPTION
WHEN OTHERS
THEN
RESULT := SQLERRM;
END;
PROCEDURE budget_output_meg (document_id NUMBER, l_doc_attr OUT VARCHAR2)
IS
l_doc_attr_val VARCHAR2 (3000);
p_task_id NUMBER := 0;
p_expenditure_type VARCHAR2 (200);
p_amount NUMBER := 0;
l_dist_attribute1 VARCHAR2 (200);
BEGIN
SELECT pda.task_id, expenditure_type, pda.attribute1
INTO p_task_id, p_expenditure_type, l_dist_attribute1
FROM po_headers_all pha, po_lines_all pla, po_distributions_all pda
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pda.po_line_id
AND pha.po_header_id = document_id
GROUP BY pda.task_id, expenditure_type, pda.attribute1;
l_doc_attr :=
EST_PKG.GET_AMT_DET (p_task_id,
NVL (p_expenditure_type, l_dist_attribute1),
0);
END;
PROCEDURE CREATE_ACTION_HISTORY (document_id NUMBER,
performer_id NUMBER,
note VARCHAR2)
IS
l_doc_attr_val VARCHAR2 (3000);
CURSOR c1
IS
SELECT *
FROM PO_ACTION_HISTORY
WHERE object_id = document_id
AND sequence_num = (SELECT MAX (sequence_num)
FROM PO_ACTION_HISTORY
WHERE object_id = document_id)
AND ROWNUM = 1;
BEGIN
FOR i IN c1
LOOP
-- DBMS_OUTPUT.put_line ('Start');
UPDATE PO_ACTION_HISTORY
SET sequence_num = i.sequence_num + 1 ,ACTION_DATE = sysdate
WHERE object_id = document_id
AND sequence_num = (SELECT MAX (sequence_num)
FROM PO_ACTION_HISTORY
WHERE object_id = document_id)
AND ROWNUM = 1;
INSERT INTO PO.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 (document_id,
i.object_type_code,
i.object_sub_type_code,
i.sequence_num,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
'FORWARD',
SYSDATE,
performer_id,
note,
0,
fnd_global.login_id,
0,
0,
0,
'',
i.approval_path_id,
'');
-- DBMS_OUTPUT.put_line ('After Insert');
END LOOP;
COMMIT;
-- DBMS_OUTPUT.put_line ('After Commit');
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ('Exception : ' || SUBSTR (SQLERRM, 1, 100));
END;
/* ------------End -----------------------------------*/
PROCEDURE PR_check_budget_amt (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2)
IS
l_po_header_id NUMBER;
l_planned VARCHAR2 (2);
l_task_id NUMBER;
l_expenditure_type VARCHAR2 (50);
l_dist_attribute1 VARCHAR2 (20);
l_budget_details VARCHAR2 (3000);
l_amount NUMBER;
l_note VARCHAR2 (500);
l_performer VARCHAR2 (100);
l_performer_id NUMBER;
BEGIN
IF (funcmode = 'RUN')
THEN
l_po_header_id :=
wf_engine.getitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'DOCUMENT_ID');
l_planned := 'Y';
BEGIN
SELECT pord.task_id,
expenditure_type,
pord.attribute1,
NVL (
SUM (pord.req_line_quantity * NVL (porl.unit_price, 1)),
0)
amt
INTO l_task_id,
l_expenditure_type,
l_dist_attribute1,
l_amount
FROM apps.po_requisition_headers_all porh,
apps.po_requisition_lines_all porl,
apps.po_req_distributions_all pord
WHERE porh.requisition_header_id = porl.requisition_header_id -- AND porl.item_id IS NOT NULL
AND porl.requisition_line_id = pord.requisition_line_id
AND porh.REQUISITION_HEADER_ID = l_po_header_id
GROUP BY pord.task_id, expenditure_type, pord.attribute1;
IF fnd_profile.VALUE ('ORG_ID') = 721 AND l_task_id IS NOT NULL
THEN
IF est_pkg.get_status (
l_task_id,
NVL (l_expenditure_type, l_dist_attribute1),
l_amount) = 1
THEN
l_planned := 'N';
BEGIN
SELECT USER_NAME, EMPLOYEE_ID
INTO l_performer, l_performer_id
FROM fnd_user
WHERE user_name LIKE 'VISHAL.JACOB';
IF l_performer IS NULL
THEN
l_performer := 'SYSADMIN';
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
l_performer := 'SYSADMIN';
END;
BEGIN
BEGIN
wf_engine.setitemattrtext (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_BUDGET_APPROVER',
avalue => l_performer);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
PR_BUDGET_OUTPUT_MEG (l_po_header_id,
l_budget_details);
wf_engine.setitemattrtext (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_BUDGET_DETAILS',
avalue => l_budget_details);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
EXCEPTION
WHEN OTHERS
THEN
l_budget_details := NULL;
END;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_planned := 'Y';
END;
-- l_planned := 'N' ; ---- Checking the workflow remove this line
IF l_planned = 'N'
THEN
resultout := 'N';
RETURN;
ELSE
resultout := 'Y';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
wf_core.context ('XX_PO_APPROVAL_PKG.PR_CHECK_BUDGET_AMT',
itemtype,
itemkey,
TO_CHAR (actid),
funcmode,
SUBSTR (SQLERRM, 1, 300));
RAISE;
END PR_check_budget_amt;
PROCEDURE PR_NOTIFICATION_NOTE (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
RESULT OUT VARCHAR2)
IS
l_nid NUMBER;
Note VARCHAR2 (480);
l_performer VARCHAR2 (100);
l_performer_id NUMBER;
l_po_header_id NUMBER;
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
Note := wf_notification.getattrtext (l_nid, 'BUDGET_NOTE');
l_po_header_id :=
wf_engine.getitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'DOCUMENT_ID');
l_performer_id :=
wf_engine.getitemattrnumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'XX_BUDGET_PERFORMER_ID');
PR_CREATE_ACTION_HISTORY (l_po_header_id, l_performer_id, Note);
wf_engine.setitemattrtext (itemtype => itemtype,
itemkey => itemkey,
aname => 'NOTE',
avalue => Note);
END IF;
RESULT := NULL;
EXCEPTION
WHEN OTHERS
THEN
RESULT := SQLERRM;
END PR_NOTIFICATION_NOTE;
PROCEDURE PR_budget_output_meg (document_id NUMBER,
l_doc_attr OUT VARCHAR2)
IS
l_doc_attr_val VARCHAR2 (3000);
CURSOR proj_cur (
pl_task_id NUMBER)
IS
SELECT pha.project_id,
t.task_id,
pha.segment1 project_num,
t.task_number || ' (' || t.task_name || ')' task_name,
pb.resource_list_id,
(SELECT pp.organization_id
FROM pjm_project_parameters pp
WHERE pp.project_id = pha.project_id AND ROWNUM = 1)
organization_id,
org_id
FROM pa_projects_all pha, pa_budget_versions pb, pa_tasks t
WHERE pha.project_id = pb.project_id
AND pb.budget_status_code = 'B'
AND pb.current_flag = 'Y'
AND pb.budget_type_code = 'AC'
AND pb.project_id = t.project_id
AND t.task_id = pl_task_id;
CURSOR exp_cur (
p_list_id NUMBER,
pl_expenditure_type VARCHAR2)
IS
SELECT t.expenditure_type,
t.expenditure_category,
(SELECT r.resource_list_member_id
FROM xx_pa_resource_v r
WHERE r.resource_list_id = p_list_id
AND r.expenditure_type = t.expenditure_type /* Modified by Kazi on 30 - Mar - 2015 */
AND ROWNUM = 1)
resource_list_member_id
FROM pa_expenditure_types t
WHERE t.expenditure_type = pl_expenditure_type;
v_proj_rec proj_cur%ROWTYPE;
v_expenditure_category VARCHAR2 (200);
v_expenditure_type VARCHAR2 (200);
v_resource_list_member_id NUMBER;
v_resource_name VARCHAR2 (200);
v_organization_id NUMBER;
v_proj_bgt NUMBER;
v_task_bgt NUMBER;
v_res_bgt NUMBER;
v_cst_act NUMBER := 0;
v_cst_unp NUMBER := 0;
v_cst_cmt NUMBER := 0;
v_cst_stk NUMBER := 0;
v_cst_po NUMBER := 0;
v_cst_req NUMBER := 0;
v_cst_tot NUMBER := 0;
v_rem_bgt NUMBER := 0;
v_msg VARCHAR2 (2000);
p_task_id NUMBER := 0;
p_expenditure_type VARCHAR2 (200);
p_amount NUMBER := 0;
BEGIN
v_msg := NULL;
SELECT pord.task_id,
expenditure_type,
NVL (SUM (pord.req_line_quantity * NVL (porl.unit_price, 1)), 0)
amt
INTO p_task_id, p_expenditure_type, -- l_dist_attribute1,
p_amount
FROM apps.po_requisition_headers_all porh,
apps.po_requisition_lines_all porl,
apps.po_req_distributions_all pord
WHERE porh.requisition_header_id = porl.requisition_header_id
-- AND porl.item_id IS NOT NULL
AND porl.requisition_line_id = pord.requisition_line_id
AND porh.REQUISITION_HEADER_ID = document_id
GROUP BY pord.task_id, expenditure_type;
DBMS_OUTPUT.put_line ('v_over_bgt 2 ' || v_over_bgt);
OPEN proj_cur (p_task_id);
FETCH proj_cur INTO v_proj_rec;
CLOSE proj_cur;
OPEN exp_cur (v_proj_rec.resource_list_id, p_expenditure_type);
FETCH exp_cur
INTO v_expenditure_type,
v_expenditure_category,
v_resource_list_member_id;
CLOSE exp_cur;
IF v_resource_list_member_id IS NULL
THEN
v_resource_name := ' Unclassified';
ELSE
v_resource_name := v_expenditure_category;
END IF;
SELECT NVL (SUM (l.burdened_cost), 0) proj_bgt,
NVL (SUM (DECODE (l.task_id, p_task_id, l.burdened_cost, 0)), 0)
task_bgt,
NVL (
SUM (
DECODE (l.resource_list_member_id,
v_resource_list_member_id, l.burdened_cost,
0)),
0)
res_bgt
INTO v_proj_bgt, v_task_bgt, v_res_bgt
FROM xx_pa_budget_lines l
WHERE project_id = v_proj_rec.project_id;
SELECT NVL (SUM (cs.project_burdened_cost), 0) cst_act,
NVL (
SUM (
CASE
WHEN cs.project_burdened_cost IS NULL THEN cs.quantity
ELSE 0
END),
0)
cst_unp
INTO v_cst_act, v_cst_unp
FROM pa_expenditure_items_all cs
WHERE cs.project_id = v_proj_rec.project_id
AND cs.task_id = p_task_id
AND cs.expenditure_type = p_expenditure_type;
SELECT NVL (SUM (cs.proj_burdened_cost), 0)
INTO v_cst_cmt
FROM pa_commitment_txns cs
WHERE cs.project_id = v_proj_rec.project_id AND cs.task_id = p_task_id
AND EXISTS
(SELECT 1
FROM pa_expenditure_types t
WHERE t.expenditure_type = cs.expenditure_type
AND t.expenditure_category =
v_expenditure_category);
SELECT ROUND (
NVL (
SUM (
(NVL (plla.quantity, 0)
- NVL (plla.quantity_received, 0))
* NVL (pla.unit_price, 0)
* NVL (pha.rate, 1)),
0),
2)
amt
INTO v_cst_po
FROM po_headers_all pha, po_lines_all pla, po_line_locations_all plla
WHERE pha.po_header_id = pla.po_header_id
AND pha.org_id = pla.org_id
AND pla.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.org_id = plla.org_id
AND pha.type_lookup_code = 'STANDARD'
--AND pha.authorization_status = 'APPROVED'
AND pha.org_id = v_proj_rec.org_id
AND pla.item_id IS NOT NULL
AND plla.quantity <> plla.quantity_received
AND NVL (pla.closed_code, ' ') NOT IN
('CLOSED', 'FINALLY CLOSED')
AND EXISTS
(SELECT 1
FROM po_distributions_all pda, pa_expenditure_types t
WHERE pda.line_location_id = plla.line_location_id
AND pda.project_id = v_proj_rec.project_id
AND pda.task_id = p_task_id
AND DECODE (pda.attribute_category,
'Cost Code', pda.attribute1) =
t.expenditure_type
AND t.expenditure_category =
v_expenditure_category);
SELECT NVL (SUM (pord.req_line_quantity * NVL (porl.unit_price, 1)), 0)
amt
INTO v_cst_req
FROM apps.po_requisition_headers_all porh,
apps.po_requisition_lines_all porl,
apps.po_req_distributions_all pord
WHERE porh.requisition_header_id = porl.requisition_header_id
AND porl.item_id IS NOT NULL
AND porl.requisition_line_id = pord.requisition_line_id
AND porh.segment1 = porh.segment1 || ''
AND porl.line_num = porl.line_num + 0
AND porh.org_id = v_proj_rec.org_id
AND pord.project_id = v_proj_rec.project_id
AND pord.task_id = p_task_id
AND NOT EXISTS
(SELECT 1
FROM po_distributions_all pod
WHERE pod.req_distribution_id = pord.distribution_id)
AND EXISTS
(SELECT 1
FROM pa_expenditure_types t
WHERE DECODE (pord.attribute_category,
'Cost Code', pord.attribute1) =
t.expenditure_type
AND t.expenditure_category =
v_expenditure_category);
v_cst_po := v_cst_po + v_cst_req;
v_cst_tot := v_cst_act + v_cst_unp + v_cst_cmt + v_cst_po + v_cst_stk;
v_rem_bgt := v_res_bgt - v_cst_tot;
IF v_rem_bgt < NVL (p_amount, 0)
THEN
v_over_bgt := 'Y';
ELSE
v_over_bgt := 'N';
END IF;
DBMS_OUTPUT.put_line ('v_over_bgt ' || v_over_bgt);
l_doc_attr_val :=
'Project : '
|| v_proj_rec.project_num
|| '
'
|| 'Task : '
|| v_proj_rec.task_name
|| '
'
|| 'Exp Cat : '
|| v_expenditure_category
|| '
'
|| 'Resource : '
|| v_expenditure_type --v_resource_name
|| '
===================================
'
|| 'Project bgt : '
|| TO_CHAR (v_proj_bgt, '9,999,999,999,999.00')
|| '
'
|| 'Task bgt : '
|| TO_CHAR (v_task_bgt, '9,999,999,999,999.00')
|| '
'
|| 'Resource bgt(A) : '
|| TO_CHAR (v_res_bgt, '9,999,999,999,999.00')
|| '
----------------------------
'
|| 'Actual Cost :'
|| TO_CHAR (v_cst_act, '9,999,999,999,999.00')
|| '
'
|| 'Unprocsd Cost :'
|| TO_CHAR (v_cst_unp, '9,999,999,999,999.00')
|| '
'
|| 'Commitments :'
|| TO_CHAR (v_cst_cmt, '9,999,999,999,999.00')
|| '
'
|| 'Inv Stocks :'
|| TO_CHAR (v_cst_stk, '9,999,999,999,999.00')
|| '
'
|| 'Pend. PO/REQ :'
|| TO_CHAR (v_cst_po, '9,999,999,999,999.00')
|| '
'
|| 'Total Cost (B) :'
|| TO_CHAR (v_cst_tot, '9,999,999,999,999.00')
|| '
----------------------------
'
|| 'Remn. Bgt(A-B) :'
|| TO_CHAR (v_rem_bgt, '9,999,999,999,999.00');
l_doc_attr := l_doc_attr_val;
-- DBMS_OUTPUT.put_line (v_msg);
END PR_budget_output_meg;
PROCEDURE PR_CREATE_ACTION_HISTORY (document_id NUMBER,
performer_id NUMBER,
note VARCHAR2)
IS
l_doc_attr_val VARCHAR2 (3000);
CURSOR c1
IS
SELECT *
FROM PO_ACTION_HISTORY
WHERE object_id = document_id
AND sequence_num = (SELECT MAX (sequence_num)
FROM PO_ACTION_HISTORY
WHERE object_id = document_id)
AND ROWNUM = 1;
BEGIN
FOR i IN c1
LOOP
-- DBMS_OUTPUT.put_line ('Start');
UPDATE PO_ACTION_HISTORY
SET sequence_num = i.sequence_num + 1
WHERE object_id = document_id
AND sequence_num = (SELECT MAX (sequence_num)
FROM PO_ACTION_HISTORY
WHERE object_id = document_id)
AND ROWNUM = 1;
INSERT INTO PO.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 (document_id,
i.object_type_code,
i.object_sub_type_code,
i.sequence_num,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
'FORWARD',
SYSDATE,
performer_id,
note,
0,
fnd_global.login_id,
0,
0,
0,
'',
i.approval_path_id,
'');
-- DBMS_OUTPUT.put_line ('After Insert');
END LOOP;
COMMIT;
-- DBMS_OUTPUT.put_line ('After Commit');
EXCEPTION
WHEN OTHERS
THEN
NULL;
-- DBMS_OUTPUT.put_line ('Exception : ' || SUBSTR (SQLERRM, 1, 100));
END PR_CREATE_ACTION_HISTORY;
/* ------------End -----------------------------------*/
PROCEDURE get_line_details (document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT NOCOPY VARCHAR2,
document_type IN OUT NOCOPY VARCHAR2)
AS
l_item_type wf_items.item_type%TYPE;
l_item_key wf_items.item_key%TYPE;
TYPE po_number_tbl_type IS TABLE OF VARCHAR2 (20);
TYPE po_date_tbl_type IS TABLE OF DATE;
TYPE line_num_tbl_type IS TABLE OF VARCHAR2 (20);
TYPE category_tbl_type IS TABLE OF VARCHAR2 (200);
TYPE item_name_tbl_type IS TABLE OF VARCHAR2 (200);
TYPE item_description_tbl_type IS TABLE OF VARCHAR2 (250);
TYPE supplier_name_tbl_type IS TABLE OF VARCHAR2 (200);
TYPE quantity_tbl_type IS TABLE OF NUMBER;
TYPE unit_price_tbl_type IS TABLE OF NUMBER;
TYPE unit_meas_lookup_code_tbl_type IS TABLE OF VARCHAR2 (20);
TYPE amount_tbl_type IS TABLE OF NUMBER;
TYPE project_name_tbl_type IS TABLE OF VARCHAR2 (20);
TYPE task_number_tbl_type IS TABLE OF VARCHAR2 (25);
TYPE expenditure_type_tbl_type IS TABLE OF VARCHAR2 (25);
TYPE budget_details_tbl_type IS TABLE OF NUMBER;
po_number_tbl po_number_tbl_type;
po_date_tbl po_date_tbl_type;
line_num_tbl line_num_tbl_type;
category_tbl category_tbl_type;
item_name_tbl item_name_tbl_type;
item_description_tbl item_description_tbl_type;
supplier_name_tbl supplier_name_tbl_type;
quantity_tbl quantity_tbl_type;
unit_price_tbl unit_price_tbl_type;
unit_meas_lookup_code_tbl unit_meas_lookup_code_tbl_type;
amount_tbl amount_tbl_type;
project_name_tbl project_name_tbl_type;
task_number_tbl task_number_tbl_type;
expenditure_type_tbl expenditure_type_tbl_type;
budget_details_tbl budget_details_tbl_type;
nl VARCHAR2 (1) := fnd_global.newline;
l_approver_count NUMBER;
l_pr_header_id NUMBER;
l_document VARCHAR2 (32000) := '';
CURSOR c1
IS
SELECT pha.segment1 po_number,
TO_CHAR (pha.creation_date, 'DD-MON-YYYY') po_date,
pla.line_num,
mc.concatenated_segments category,
msi.concatenated_segments item_name,
pla.item_description item_description,
ap.vendor_name supplier_name,
pla.quantity,
pla.unit_price,
pla.unit_meas_lookup_code,
(pla.quantity * pla.unit_price) amount,
(SELECT segment1
FROM pa_projects_all ppa
WHERE ppa.project_id = pda.project_id)
project_name,
(SELECT task_number
FROM pa_tasks ppa
WHERE ppa.task_id = pda.task_id)
task_number,
NVL (pda.EXPENDITURE_TYPE, pda.attribute1) EXPENDITURE_TYPE,
ROUND (
EST_PKG.get_rem_budget (
pda.task_id,
NVL (pda.EXPENDITURE_TYPE, pda.attribute1),
(pla.quantity * pla.unit_price)),
2)
budget_details
FROM po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
mtl_categories_kfv mc,
mtl_system_items_kfv msi,
ap_suppliers ap,
ap_supplier_sites_all aps
WHERE pha.po_header_id = pla.po_header_id
AND mc.category_id(+) = pla.category_id
AND msi.inventory_item_id(+) = pla.item_id
AND pda.po_line_id = pla.po_line_id
AND ap.vendor_id(+) = pha.vendor_id
AND aps.vendor_site_id(+) = pha.vendor_site_id
AND (msi.organization_id =
oe_sys_parameters.VALUE ('MASTER_ORGANIZATION_ID',
pla.org_id)
OR msi.organization_id IS NULL)
AND pha.po_header_id = l_pr_header_id
ORDER BY pha.po_header_id, pla.line_num;
i NUMBER := 0;
max_approver_dsp NUMBER := 100;
l_num_records_to_display NUMBER;
curr_len NUMBER := 0;
prior_len NUMBER := 0;
action_mesg fnd_new_messages.MESSAGE_TEXT%TYPE;
lc_error VARCHAR2 (4000);
BEGIN
l_pr_header_id := TO_NUMBER (document_id);
IF l_pr_header_id IS NULL
THEN
document := l_document;
RETURN;
END IF;
IF max_approver_dsp IS NULL
THEN
max_approver_dsp := 100;
END IF;
OPEN c1;
FETCH c1
BULK COLLECT INTO po_number_tbl,
po_date_tbl,
line_num_tbl,
category_tbl,
item_name_tbl,
item_description_tbl,
supplier_name_tbl,
quantity_tbl,
unit_price_tbl,
unit_meas_lookup_code_tbl,
amount_tbl,
project_name_tbl,
task_number_tbl,
expenditure_type_tbl,
budget_details_tbl;
l_approver_count := c1%ROWCOUNT;
CLOSE c1;
IF (l_approver_count > max_approver_dsp)
THEN
l_num_records_to_display := max_approver_dsp;
ELSE
l_num_records_to_display := l_approver_count;
action_mesg := NULL;
END IF;
IF NVL (display_type, 'text/html') = 'text/html'
THEN
l_document :=
nl || nl || '<!-- Requisition Lines -->' || nl || nl || '<P><B>';
-- l_document := l_document || fnd_message.get_string ('po', 'po_wf_notif_action_history')|| nl;
l_document := l_document || '</B>' || nl || nl || '<P>';
IF (action_mesg IS NOT NULL)
THEN
l_document := l_document || action_mesg || '<P>' || nl;
END IF;
l_document :=
l_document
|| '<TABLE class="xxpostyle" '
|| ' Purchase Order Line Details '
|| '">'
|| nl;
l_document := l_document || '<TR>';
-- l_document := l_document || '<TH id="Url_1">-</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>PO Number</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Line Number</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Category</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Item Name</B> </TH>' || nl;
l_document :=
l_document
|| '<TH id="Url_1"><B>Item Description</B> </TH>'
|| nl;
l_document :=
l_document || '<TH id="Url_1"><B>Supplier Name</B> </TH>' || nl;
-- l_document :=
-- l_document || '<TH id="Url_1"><B>Site Name</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Quantity</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Unit Price</B> </TH>' || nl;
l_document := l_document || '<TH id="Url_1"><B>Unit</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Amount</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Project No.</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>BOQ Item No.</B> </TH>' || nl;
l_document :=
l_document || '<TH id="Url_1"><B>Cost Code</B> </TH>' || nl;
l_document :=
l_document
|| '<TH id="Url_1"><B>Remaining Budget</B> </TH>'
|| nl;
l_document := l_document || '</TR>' || nl;
curr_len := LENGTHB (l_document);
prior_len := curr_len;
FOR i IN 1 .. l_num_records_to_display
LOOP
IF (curr_len + (2 * (curr_len - prior_len))) >= 32000
THEN
EXIT;
END IF;
l_document := l_document || '<TR>' || nl;
l_document :=
l_document
|| '<TD nowrap align=center headers="po_num_1">'
|| NVL (po_number_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="linenum_1">'
|| NVL (line_num_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="category_1">'
|| NVL (category_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="item_name_1">'
|| NVL (item_name_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="item_description_1">'
|| NVL (item_description_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="supplier_name_1">'
|| NVL (supplier_name_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="quantity_1">'
|| NVL (quantity_tbl (i), '0')
|| '</TD>'
|| nl;
l_document :=
l_document || '<TD nowrap headers="unit_price_1">'
|| NVL (
TO_CHAR (unit_price_tbl (i), '999,999,999,999,999.00'),
0)
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="unit_meas_1">'
|| NVL (unit_meas_lookup_code_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="amount_1">'
|| NVL (amount_tbl (i), 0)
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="project_name_1">'
|| NVL (project_name_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="task_number_1">'
|| NVL (task_number_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document
|| '<TD nowrap headers="expenditure_type_1">'
|| NVL (expenditure_type_tbl (i), '-')
|| '</TD>'
|| nl;
l_document :=
l_document || '<TD nowrap headers="budget_details_1">'
|| NVL (
TO_CHAR (budget_details_tbl (i),
'999,999,999,999,999.00'),
'0')
|| '</TD>'
|| nl;
l_document := l_document || '</TR>' || nl;
curr_len := LENGTHB (l_document);
END LOOP;
l_document := l_document || '</TABLE></P>' || nl;
document := l_document;
document_type := 'text/html';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error');
lc_error := SQLERRM;
document := 'Error';
END get_line_details;
END;
/
