[Home] [Help]
PACKAGE BODY: APPS.GMF_INTERNAL_ORDERS
Source
1 PACKAGE BODY GMF_INTERNAL_ORDERS AS
2 /* $Header: GMFINORB.pls 115.5 2004/01/22 20:46:54 sschinch noship $ */
3
4
5 /*===============================================================================
6 *Package: GMF_INTERNAL_ORDERS
7 * Procedure: GET_INTERNAL_ORDER_STS
8 * Description: This procedure returns status to indicate how to book an entry
9 *
10 * Inputs:
11 * 1. p_req_line_id - requisition_line_id
12 * 2. p_entry_type - entry type
13 * M -- Indicates Middle Entry.
14 * Output:
15 * Status of the row if it is old or new or a middle entry.
16 * Old -- O 0 Invoiced Not Invoiced Not Invoiced but Processed
17 * New -- N 1 3 2 6
18 * Middle -- X 4
19 =================================================================================*/
20
21
22 FUNCTION GET_INTERNAL_ORDER_STS(preq_line_id NUMBER,
23 pentry_type VARCHAR2) RETURN VARCHAR2 IS
24 l_flag_posted VARCHAR2(2);
25 l_invoiced_flag NUMBER := 0;
26 l_flag_value NUMBER := 1;
27
28 CURSOR Cur_Get_flags (c_req_line_id NUMBER,
29 c_entry_type VARCHAR2) IS
30 SELECT DECODE(min(nvl(t.intorder_posted_ind,-1)),-1,DECODE(c_entry_type,'M','X','O'),'N'),
31 DECODE(NVL(min(t.invoiced_flag),'-1'),'-1',0,'P',2,1)
32 FROM po_requisition_headers_all prh,
33 po_requisition_lines_all prl,
34 oe_order_lines_all oel,
35 ic_tran_pnd t
36 WHERE prl.requisition_header_id = prh.requisition_header_id
37 and prh.segment1 = oel.orig_sys_document_ref
38 and prl.requisition_line_id = oel.source_document_line_id
39 and oel.order_source_id = 10
40 and oel.line_id = t.line_id
41 and t.doc_type = 'OMSO'
42 and t.completed_ind = 1
43 and t.delete_mark = 0
44 and prl.requisition_line_id = c_req_line_id;
45
46 BEGIN
47 IF (preq_line_id IS NOT NULL)
48 THEN
49 OPEN Cur_get_flags(preq_line_id,pentry_type);
50 FETCH Cur_get_flags INTO l_flag_posted,l_invoiced_flag;
51 IF (Cur_get_flags%NOTFOUND) THEN
52 l_flag_value := 1;
53 l_flag_posted := 'N';
54 l_invoiced_flag := 0;
55 END IF;
56 CLOSE Cur_Get_flags;
57 IF (l_flag_posted = 'N') THEN
58 l_flag_value := 1; /* New Entry */
59 ELSIF(l_flag_posted = 'O') THEN
60 l_flag_value := 0; /* Old Entry */
61 ELSIF (l_flag_posted = 'X') THEN
62 l_flag_value := 4; /* Discard Entry*/
63 END IF;
64
65 IF (l_invoiced_flag = 0 AND l_flag_value = 1) THEN
66 l_flag_value := l_flag_value + 1; /* Not Invoiced and New */
67 ELSIF (l_invoiced_flag = 1 AND l_flag_value = 1) THEN
68 l_flag_value := l_flag_value + 2; /* Invoiced and New*/
69 ELSIF(l_invoiced_flag = 2 AND l_flag_value = 1) THEN
70 l_flag_value := l_flag_value + 5; /* Processed and New */
71 END IF;
72 ELSE
73 RETURN('2');
74 END IF;
75
76 RETURN (TO_CHAR(l_flag_value));
77
78 END GET_INTERNAL_ORDER_STS;
79
80
81 /*===============================================================================
82 *Package: GMF_INTERNAL_ORDERS
83 * Procedure: GET_TRANSFER_PRICE
84 * Description: This procedure returns transfer price in shipping operation unit
85 * currency.
86 *
87 * Inputs:
88 * 1. p_ship_ou_id - Shipping Operating Unit
89 * 2. p_recv_ou_id - Receiving Operating Unit
90 * 3. p_trans_um - Transaction Unit Of Measure.
91 * 4. p_inv_item_id - Inventory Item Id
92 * 5. p_currency_code - Shipping Company Currency
93 * 6. p_trans_date - the date for which the conversion rate is used
94 * Output:
95 * 1. x_return_status - return status
96 * 2. x_transfer_price -- transfer price.
97 =================================================================================*/
98
99 PROCEDURE GET_TRANSFER_PRICE(p_ship_ou_id IN NUMBER
100 ,p_recv_ou_id IN NUMBER
101 ,p_trans_um IN VARCHAR2
102 ,p_inv_item_id IN NUMBER
103 ,p_trans_id IN NUMBER
104 ,p_currency_code IN VARCHAR2
105 ,p_trans_date IN DATE
106 ,p_inv_org_id IN NUMBER
107 ,x_return_status OUT NOCOPY VARCHAR2
108 ,x_transfer_price OUT NOCOPY NUMBER)
109 IS
110
111 CURSOR Cur_get_trans(cp_inv_item_id NUMBER,
112 cp_trans_id NUMBER) IS
113 SELECT max(t.trans_id)
114 FROM po_requisition_headers_all prh,
115 po_requisition_lines_all prl,
116 oe_order_lines_all oel,
117 ic_tran_pnd t
118 WHERE prl.requisition_header_id = prh.requisition_header_id
119 and prh.segment1 = oel.orig_sys_document_ref
120 and prl.requisition_line_id = oel.source_document_line_id
121 and oel.order_source_id = 10
122 and oel.line_id = t.line_id
123 and t.doc_type = 'OMSO'
124 and t.completed_ind = 1
125 and t.delete_mark = 0
126 and oel.inventory_item_id = cp_inv_item_id
127 and prl.requisition_line_id = (SELECT t.requisition_line_id
128 FROM rcv_transactions t
129 ,ic_tran_pnd pnd
130 WHERE t.shipment_header_id = pnd.doc_id
131 and t.transaction_id = pnd.line_id
132 and pnd.doc_type = 'PORC'
133 and pnd.completed_ind = 1
134 and pnd.delete_mark = 0
135 and pnd.trans_id = cp_trans_id
136 );
137
138 l_api_version NUMBER := 1.0;
139 x_msg_count NUMBER;
140 x_msg_data VARCHAR2(2000);
141 l_currency_code VARCHAR2(10);
142 l_transfer_price NUMBER;
143 l_return_status VARCHAR2(2);
144 l_func_currency_code VARCHAR2(10);
145 l_inv_transfer_price NUMBER;
146 l_inv_currency_code VARCHAR2(4);
147 l_trans_id NUMBER := NULL;
148 BEGIN
149
150 OPEN cur_get_trans(p_inv_item_id,p_trans_id);
151 FETCH Cur_get_trans INTO l_trans_id;
152 CLOSE Cur_get_trans;
153
154 IF (l_trans_id IS NULL) THEN
155 l_trans_id := p_trans_id;
156 END IF;
157
158
159
160 INV_TRANSACTION_FLOW_PUB.GET_TRANSFER_PRICE
161 (x_return_status => x_return_status
162 ,x_msg_data => x_msg_data
163 ,x_msg_count => x_msg_count
164 ,x_transfer_price => x_transfer_price
165 ,x_currency_code => l_currency_code
166 ,x_incr_transfer_price => l_inv_transfer_price
167 ,x_incr_currency_code => l_inv_currency_code
168 ,p_api_version => l_api_version
169 ,p_init_msg_list => 'T'
170 ,p_from_org_id => p_ship_ou_id
171 ,p_to_org_id => p_recv_ou_id
172 ,p_transaction_uom => p_trans_um
173 ,p_inventory_item_id => p_inv_item_id
174 ,p_from_organization_id => p_inv_org_id
175 ,p_transaction_id => l_trans_id
176 ,p_global_procurement_flag => 'N');
177
178
179 IF (l_currency_code IS NOT NULL) THEN
180 IF (l_currency_code <> p_currency_code) THEN
181 l_transfer_price := x_transfer_price;
182
183 x_transfer_price :=INV_TRANSACTION_FLOW_PUB.CONVERT_CURRENCY (
184 p_org_id => p_ship_ou_id
185 , p_transfer_price => l_transfer_price
186 , p_currency_code => l_currency_code
187 , p_transaction_date => p_trans_date
188 , x_functional_currency_code => l_func_currency_code
189 , x_return_status => l_return_status
190 , x_msg_data => x_msg_data
191 , x_msg_count => x_msg_count);
192
193 IF (l_return_status = 'S') THEN
194 x_transfer_price := l_transfer_price;
195 x_return_status := 'S';
196 ELSE
197 x_transfer_price := 0;
198 x_return_status := 'E';
199 END IF;
200 END IF;
201 END IF;
202
203 END GET_TRANSFER_PRICE;
204
205 END GMF_INTERNAL_ORDERS;