1 PACKAGE BODY po_calculatereqtotal_pvt AS
2 /* $Header: POXVRTCB.pls 115.8 2004/08/13 00:31:05 jinwang ship $ */
3
4 /**
5 * Public FUNCTION get_req_distribution_total
6 * Requires: Requisition Header Id
7 * Requisition Line Id
8 * Requisition Distribution Id
9 * Modifies: None.
10 * Effects: Return updated distribution qty X new price
11 from po_change_requests if any changes were made
12 * Else return req_line_quantity X unit price
13 * from po_req_distributions and po_requisition_lines
14 * If line was cancelled return 0
15 * Returns:
16 * Revised req total coming from a distribution
17 * If something fails returns 0
18 */
19 FUNCTION get_req_distribution_total(p_header_id IN NUMBER,
20 p_line_id IN NUMBER,
21 p_distribution_id IN NUMBER)
22 RETURN NUMBER
23 IS
24 l_matching_basis po_requisition_lines_all.matching_basis%type;
25 l_action_type po_change_requests.action_type%type;
26 l_dist_total NUMBER := 0;
27 l_change_request_group_id po_change_requests.change_request_group_id%type;
28 BEGIN
29
30 SELECT prl.matching_basis, pcr.action_type,
31 decode (prl.matching_basis, 'AMOUNT' , prd.req_line_amount,
32 prd.req_line_quantity*prl.unit_price) +
33 nvl(prd.nonrecoverable_tax,0),
34 pcr.change_request_group_id
35 INTO l_matching_basis, l_action_type, l_dist_total,
36 l_change_request_group_id
37 FROM po_requisition_lines_all prl,
38 po_req_distributions_all prd,
39 po_change_requests pcr
40 WHERE prl.requisition_line_id = p_line_id
41 AND prl.requisition_line_id = prd.requisition_line_id
42 AND prd.distribution_id = p_distribution_id
43 AND nvl(prl.cancel_flag,'N') = 'N'
44 AND nvl(prl.modified_by_agent_flag, 'N') = 'N'
45 AND pcr.document_line_id(+) = prl.requisition_line_id
46 AND pcr.document_type(+) = 'REQ'
47 AND pcr.action_type(+) <> 'DERIVED'
48 AND pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
49 AND rownum =1;
50
51 IF (l_action_type is null) THEN
52 RETURN l_dist_total;
53 ELSIF (l_action_type = 'CANCELLATION') THEN
54 RETURN 0;
55 ELSE
56 RETURN get_new_distribution_total(p_header_id, p_line_id,
57 p_distribution_id, l_matching_basis,
58 l_change_request_group_id);
59 END IF;
60
61 EXCEPTION WHEN OTHERS THEN
62 RETURN 0;
63 END get_req_distribution_total;
64
65
66 /**
67 * Public FUNCTION get_new_distribution_total
68 * Requires: Requisition Header Id
69 * Requisition Line Id
70 * Distribution Id
71 * Assumes that the line is not cancelled
72 * Modifies: None.
73 * Effects: Return updated requisition total
74 * for a distribution
75 * using revised values of distribution qty and line price
76 * Returns:
77 * Revised req total coming from a distribution
78 * If something fails returns 0
79 */
80
81 FUNCTION get_new_distribution_total(p_header_id IN NUMBER,
82 p_line_id IN NUMBER,
83 p_distribution_id IN NUMBER,
84 p_matching_basis IN VARCHAR2,
85 p_change_request_group_id IN NUMBER)
86 RETURN NUMBER
87 IS
88 l_nonrec_tax NUMBER := 0;
89 l_old_dist_total NUMBER := 0;
90 l_new_dist_total NUMBER := 0;
91 BEGIN
92 IF (p_matching_basis = 'AMOUNT') THEN
93
94 -- get updated amount
95 SELECT prd.req_line_amount,
96 nvl(pcr.new_amount, prd.req_line_amount),
97 prd.nonrecoverable_tax
98 INTO l_old_dist_total, l_new_dist_total, l_nonrec_tax
99 FROM po_change_requests pcr,
100 po_req_distributions_all prd
101 WHERE prd.distribution_id = p_distribution_id
102 AND pcr.document_line_id(+) = prd.requisition_line_id
103 AND pcr.document_distribution_id(+) = prd.distribution_id
104 AND pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED');
105
106 ELSE
107
108 -- get updated line price and quantity
109 SELECT prd.req_line_quantity*prl.unit_price,
110 nvl(pcr1.new_quantity, prd.req_line_quantity)*nvl(pcr.new_price, prl.unit_price),
111 prd.nonrecoverable_tax
112 INTO l_old_dist_total, l_new_dist_total, l_nonrec_tax
113 FROM po_change_requests pcr,
114 po_change_requests pcr1,
115 po_requisition_lines_all prl,
116 po_req_distributions_all prd
117 WHERE prd.distribution_id = p_distribution_id
118 AND pcr1.document_distribution_id(+) = prd.distribution_id
119 AND pcr1.document_line_id(+) = prd.requisition_line_id
120 AND pcr1.request_status(+) NOT IN ('ACCEPTED','REJECTED')
121 AND prd.requisition_line_id = prl.requisition_line_id
122 AND pcr.document_line_id(+) = prl.requisition_line_id
123 AND pcr.new_price(+) IS NOT NULL
124 AND pcr.old_price(+) IS NOT NULL
125 AND pcr.document_type(+) = 'REQ'
126 AND pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED')
127 AND pcr.request_level(+) = 'LINE'
128 AND pcr.action_type(+) <> 'CANCELLATION'
129 AND pcr.change_request_group_id(+) = p_change_request_group_id;
130
131 END IF;
132
133 IF (l_old_dist_total IS NOT NULL AND l_old_dist_total > 0) THEN
134 l_nonrec_tax := NVL((l_nonrec_tax / l_old_dist_total) * l_new_dist_total, 0);
135 RETURN NVL(l_nonrec_tax + l_new_dist_total, 0);
136 ELSE
137 RETURN NVL(l_new_dist_total, 0);
138 END IF;
139
140 EXCEPTION WHEN OTHERS THEN
141 RETURN 0;
142 END get_new_distribution_total;
143
144 END PO_CALCULATEREQTOTAL_PVT;
[Home] [Help]