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