[Home] [Help]
PACKAGE BODY: APPS.POR_APPRV_WF_UTIL_GRP
Source
1 PACKAGE BODY POR_APPRV_WF_UTIL_GRP AS
2 /* $Header: PORWFUTB.pls 120.0.12010000.3 2008/11/24 10:47:59 rojain ship $ */
3
4 /*===========================================================================
5 FUNCTION NAME: get_po_number
6
7 DESCRIPTION: Gets the po number for the given line_location_id
8
9 CHANGE HISTORY: 17-SEP-2003 sbgeorge Created
10 ===========================================================================*/
11 FUNCTION get_po_number(p_line_location_id IN NUMBER) RETURN VARCHAR2
12 IS
13 l_po_num varchar2(50);
14
15 BEGIN
16 SELECT ph.segment1|| DECODE(pr.release_num, NULL, '', '-' || pr.release_num)
17 INTO l_po_num
18 FROM
19 po_releases_all pr,
20 po_headers_all ph,
21 po_line_locations_all pll
22 WHERE
23 pll.line_location_id=p_line_location_id AND
24 pll.po_header_id = ph.po_header_id AND
25 pll.po_release_id = pr.po_release_id(+);
26 RETURN l_po_num;
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 RETURN '';
31 END get_po_number;
32
33 /*===========================================================================
34 FUNCTION NAME: get_so_number
35
36 DESCRIPTION: Gets the sales order number for the given
37 requisition_line_id
38
39 CHANGE HISTORY: 17-SEP-2003 sbgeorge Created
40 ===========================================================================*/
41 FUNCTION get_so_number(p_req_line_id IN NUMBER) RETURN NUMBER
42 IS
43 l_so_number oe_order_headers_all.order_number%type;
44 BEGIN
45 SELECT ooh.order_number
46 INTO l_so_number
47 from po_requisition_lines_all prl,
48 po_requisition_headers_all prh,
49 oe_order_headers_all ooh,
50 -- oe_order_lines_all ool,
51 po_system_parameters_all psp
52 WHERE prl.requisition_header_id = prh.requisition_header_id
53 AND prl.requisition_line_id = p_req_line_id
54 AND prh.requisition_header_id = ooh.source_document_id
55 AND prh.segment1 = ooh.orig_sys_document_ref
56 -- AND ool.header_id = ooh.header_id
57 -- AND ool.orig_sys_line_ref = TO_CHAR(prl.line_num)
58 AND psp.org_id = prh.org_id
59 AND psp.order_source_id = ooh.order_source_id;
60
61 return l_so_number;
62
63 EXCEPTION
64 WHEN no_data_found THEN
65 RETURN NULL;
66 WHEN others THEN
67 RETURN NULL;
68 END get_so_number;
69
70 /*===========================================================================
71 FUNCTION NAME: get_cost_center
72
73 DESCRIPTION: Gets the cost_center for the given requisition_line_id
74
75 CHANGE HISTORY: 17-SEP-2003 sbgeorge Created
76 ===========================================================================*/
77 FUNCTION get_cost_center(p_req_line_id IN NUMBER) RETURN VARCHAR2
78 IS
79 l_segment_num fnd_id_flex_segments.segment_num%TYPE;
80 l_cost_center VARCHAR2(200);
81 l_account_id gl_sets_of_books.chart_of_accounts_id%TYPE;
82 cost_center_1 VARCHAR2(200);
83 cc_Id po_req_distributions_all.code_combination_id%TYPE;
84 l_segments fnd_flex_ext.SegmentArray;
85 nsegments NUMBER;
86 multiple_cost_center VARCHAR2(100):= '';
87 dist_num NUMBER;
88
89 CURSOR ccId_csr(p_req_line_id NUMBER) IS
90 SELECT code_combination_id
91 FROM po_req_distributions_all
92 WHERE requisition_line_id = p_req_line_id;
93 BEGIN
94 multiple_cost_center := fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE');
95
96 BEGIN
97 SELECT fs.segment_num, gls.chart_of_accounts_id
98 INTO l_segment_num, l_account_id
99 FROM FND_ID_FLEX_SEGMENTS fs,
100 fnd_segment_attribute_values fsav,
101 financials_system_params_all fsp,
102 gl_sets_of_books gls,
103 po_requisition_lines_all prl
104 WHERE prl.requisition_line_id = p_req_line_id AND
105 --added NVL check for single org, bug#6705513
106 NVL(prl.org_id,-99) = NVL(fsp.org_id,-99) AND
107 fsp.set_of_books_id = gls.set_of_books_id AND
108 fsav.id_flex_num = gls.chart_of_accounts_id AND
109 fsav.id_flex_code = 'GL#' AND
110 fsav.application_id = 101 AND
111 fsav.segment_attribute_type = 'FA_COST_CTR' AND
112 fsav.id_flex_num = fs.id_flex_num AND
113 fsav.id_flex_code = fs.id_flex_code AND
114 fsav.application_id = fs.application_id AND
115 fsav.application_column_name = fs.application_column_name AND
116 fsav.attribute_value='Y';
117 EXCEPTION
118 WHEN OTHERS THEN
119 l_segment_num := -1;
120 END;
121
122 IF l_segment_num = -1 THEN
123 l_cost_center := '';
124 ELSE
125 l_cost_center := 'SINGLE';
126
127 dist_num := 1;
128
129 OPEN ccId_csr(p_req_line_id);
130 LOOP
131 FETCH ccId_csr INTO cc_Id;
132 EXIT WHEN ccid_csr%NOTFOUND;
133
134 IF fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) THEN
135 l_cost_center := l_segments(l_segment_num);
136 ELSE
137 l_cost_center := '';
138 END IF;
139
140 IF dist_num = 1 THEN
141 cost_center_1 := l_cost_center;
142 dist_num := 2;
143 ELSE
144 IF l_cost_center <> cost_center_1 THEN
145 l_cost_center := multiple_cost_center;
146 EXIT;
147 END IF;
148 END IF;
149 END LOOP;
150 CLOSE ccId_csr;
151 IF l_cost_center <> multiple_cost_center THEN
152 IF fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) THEN
153 l_cost_center := l_segments(l_segment_num);
154 ELSE
155 l_cost_center := '';
156 END IF;
157 END IF;
158 END IF; --if l_segment_num = -1
159 RETURN l_cost_center;
160 EXCEPTION --any exception while retrieving the cost center
161 WHEN OTHERS THEN
162 l_cost_center := '';
163 RETURN l_cost_center;
164 END get_cost_center;
165
166 END POR_APPRV_WF_UTIL_GRP;