[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.5 2009/05/26 16:28:20 rohbansa 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.source_document_line_id = prl.requisition_line_id
58 AND psp.org_id = prh.org_id
59 AND psp.order_source_id = ooh.order_source_id
60 AND rownum =1; /* To handle so line split case*/
61
62 return l_so_number;
63
64 EXCEPTION
65 WHEN no_data_found THEN
66 RETURN NULL;
67 WHEN others THEN
68 RETURN NULL;
69 END get_so_number;
70
71 /*===========================================================================
72 FUNCTION NAME: get_cost_center
73
74 DESCRIPTION: Gets the cost_center for the given requisition_line_id
75
76 CHANGE HISTORY: 17-SEP-2003 sbgeorge Created
77 ===========================================================================*/
78 FUNCTION get_cost_center(p_req_line_id IN NUMBER) RETURN VARCHAR2
79 IS
80 l_segment_num fnd_id_flex_segments.segment_num%TYPE;
81 l_cost_center VARCHAR2(200);
82 l_account_id gl_sets_of_books.chart_of_accounts_id%TYPE;
83 cost_center_1 VARCHAR2(200);
84 cc_Id po_req_distributions_all.code_combination_id%TYPE;
85 l_segments fnd_flex_ext.SegmentArray;
86 nsegments NUMBER;
87 multiple_cost_center VARCHAR2(100):= '';
88 dist_num NUMBER;
89
90 CURSOR ccId_csr(p_req_line_id NUMBER) IS
91 SELECT code_combination_id
92 FROM po_req_distributions_all
93 WHERE requisition_line_id = p_req_line_id;
94 BEGIN
95 multiple_cost_center := fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE');
96
97 BEGIN
98 SELECT fs.segment_num, gls.chart_of_accounts_id
99 INTO l_segment_num, l_account_id
100 FROM FND_ID_FLEX_SEGMENTS fs,
101 fnd_segment_attribute_values fsav,
102 financials_system_params_all fsp,
103 gl_sets_of_books gls,
104 po_requisition_lines_all prl
105 WHERE prl.requisition_line_id = p_req_line_id AND
106 --added NVL check for single org, bug#6705513
107 NVL(prl.org_id,-99) = NVL(fsp.org_id,-99) AND
108 fsp.set_of_books_id = gls.set_of_books_id AND
109 fsav.id_flex_num = gls.chart_of_accounts_id AND
110 fsav.id_flex_code = 'GL#' AND
111 fsav.application_id = 101 AND
112 fsav.segment_attribute_type = 'FA_COST_CTR' AND
113 fsav.id_flex_num = fs.id_flex_num AND
114 fsav.id_flex_code = fs.id_flex_code AND
115 fsav.application_id = fs.application_id AND
116 fsav.application_column_name = fs.application_column_name AND
117 fsav.attribute_value='Y';
118 EXCEPTION
119 WHEN OTHERS THEN
120 l_segment_num := -1;
121 END;
122
123 IF l_segment_num = -1 THEN
124 l_cost_center := '';
125 ELSE
126 l_cost_center := 'SINGLE';
127
128 dist_num := 1;
129
130 OPEN ccId_csr(p_req_line_id);
131 LOOP
132 FETCH ccId_csr INTO cc_Id;
133 EXIT WHEN ccid_csr%NOTFOUND;
134
135 IF fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) THEN
136 l_cost_center := l_segments(l_segment_num);
137 ELSE
138 l_cost_center := '';
139 END IF;
140
141 IF dist_num = 1 THEN
142 cost_center_1 := l_cost_center;
143 dist_num := 2;
144 ELSE
145 IF l_cost_center <> cost_center_1 THEN
146 l_cost_center := multiple_cost_center;
147 EXIT;
148 END IF;
149 END IF;
150 END LOOP;
151 CLOSE ccId_csr;
152 IF l_cost_center <> multiple_cost_center THEN
153 IF fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) THEN
154 l_cost_center := l_segments(l_segment_num);
155 ELSE
156 l_cost_center := '';
157 END IF;
158 END IF;
159 END IF; --if l_segment_num = -1
160 RETURN l_cost_center;
161 EXCEPTION --any exception while retrieving the cost center
162 WHEN OTHERS THEN
163 l_cost_center := '';
164 RETURN l_cost_center;
165 END get_cost_center;
166
167 END POR_APPRV_WF_UTIL_GRP;