[Home] [Help]
PACKAGE BODY: APPS.PO_CONTROL_CHECKS
Source
1 PACKAGE BODY PO_CONTROL_CHECKS AS
2 /* $Header: POXPOSCB.pls 120.3 2006/06/06 22:11:52 tpoon noship $ */
3
4 -- Constants :
5
6 -- Read the profile option that enables/disables the debug log
7 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8
9
10 -- <Doc Manager Rewrite R12>: Removed po_check function, get_debug function
11 -- Also removed unnecessary private global variables.
12
13 --<DropShip FPJ Start>
14 FUNCTION chk_drop_ship(
15 p_doctyp IN VARCHAR2,
16 p_docid IN NUMBER,
17 p_lineid IN NUMBER,
18 p_shipid IN NUMBER,
19 p_reportid IN NUMBER,
20 p_action IN VARCHAR2,
21 p_return_code IN OUT NOCOPY VARCHAR2)
22
23 RETURN BOOLEAN IS
24
25 l_po_header_id PO_TBL_NUMBER;
26 l_po_release_id PO_TBL_NUMBER;
27 l_po_line_id PO_TBL_NUMBER;
28 l_line_location_id PO_TBL_NUMBER;
29 l_shipnum PO_TBL_NUMBER;
30 l_updatable_flag VARCHAR2(1);
31 l_on_hold VARCHAR2(30);
32 l_order_line_status NUMBER;
33 l_return_status VARCHAR2(30);
34 l_msg_data VARCHAR2(3000);
35 l_msg_count NUMBER;
36 l_message VARCHAR2(30);
37
38 l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_CONTROL_CHECKS.CHK_DROP_SHIP';
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_progress VARCHAR2(3) := '000';
41
42 BEGIN
43
44 IF g_fnd_debug = 'Y' THEN
45 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
46 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.' || l_progress,
47 'Entered Procedure. DocType:' || p_doctyp || ' DocID:' || p_docid
48 || ' p_lineid:' || p_lineid || ' p_shipid:' || p_shipid
49 || ' p_reportid:' || p_reportid || ' p_action:' || p_action);
50 END IF;
51 END IF;
52
53 -- Bug 3370387 START
54 -- For a PO or release, we only need to check the sales order references for
55 -- Finally Close. We do not need to check for the other actions (i.e. Cancel).
56 IF (p_doctyp IN ('PO', 'RELEASE')) AND (p_action <> 'FINALLY CLOSE') THEN
57 RETURN TRUE;
58 END IF;
59 -- Bug 3370387 END
60
61 l_progress := '010';
62 --SQL What: Finds DropShip Shipments for this Entity
63 --Bug 3648769: tweaked the WHERE conditions on p_doctyp for performance
64 --Bug 5277112 Added NVLs around p_lineid and p_shipid. With the Doc Manager
65 --Rewrite, this procedure could now be called with these parameters passed
66 --as NULL instead of 0.
67 SELECT s.po_header_id, s.po_release_id, s.po_line_id, s.line_location_id, s.shipment_num
68 BULK COLLECT INTO l_po_header_id, l_po_release_id, l_po_line_id, l_line_location_id, l_shipnum
69 FROM po_requisition_lines_all rl, po_line_locations_all s
70 WHERE rl.line_location_id = s.line_location_id
71 AND ( (p_doctyp = 'REQUISITION' and rl.requisition_header_id = p_docid)
72 OR (p_doctyp = 'PO' and s.po_header_id = p_docid)
73 OR (p_doctyp = 'RELEASE' and s.po_release_id = p_docid)
74 )
75 AND (NVL(p_lineid,0) = 0 OR s.po_line_id = p_lineid)
76 AND (NVL(p_shipid,0) = 0 OR s.line_location_id = p_shipid)
77 AND nvl(s.drop_ship_flag, 'N') = 'Y';
78
79 l_progress := '020';
80 -- If any of the Drop Ship Requisition Lines have open sales orders, return error
81 FOR I IN 1..l_line_location_id.count LOOP
82
83 OE_DROP_SHIP_GRP.Get_Order_Line_Status(
84 p_api_version => 1.0,
85 p_po_header_id => l_po_header_id(i),
86 p_po_release_id => l_po_release_id(i),
87 p_po_line_id => l_po_line_id(i),
88 p_po_line_location_id => l_line_location_id(i),
89 p_mode => 0, --To Check if Updatable
90 x_updatable_flag => l_updatable_flag,
91 x_on_hold => l_on_hold,
92 x_order_line_status => l_order_line_status,
93 x_return_status => l_return_status,
94 x_msg_data => l_msg_data,
95 x_msg_count => l_msg_count);
96
97 IF g_fnd_debug = 'Y' THEN
98 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
99 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || l_progress,
100 'After Call to OE_DROP_SHIP_GRP.Get_Order_Line_Status RetStatus: ' || l_return_status
101 || 'POHeader:' || l_po_header_id(i) || ' Release:' || l_po_release_id(i)
102 || ' Line:' || l_po_line_id(i) || ' LineLoc:' || l_line_location_id(i)
103 || ' SOUpdatable:' || l_updatable_flag);
104 END IF;
105 END IF;
106
107 IF (l_return_status IS NULL) THEN
108 l_return_status := FND_API.g_ret_sts_success;
109 END IF;
110
111 IF (l_return_status = FND_API.g_ret_sts_error) THEN
112 RAISE FND_API.g_exc_error;
113 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
114 RAISE FND_API.g_exc_unexpected_error;
115 END IF;
116
117 l_progress := '030';
118 IF (l_updatable_flag = 'Y') THEN
119 -- There are open Sales Order Lines, do not allow PO Finally Close, Requisition Cancel
120 -- Set p_return_code to SUBMISSION_FAILED and Insert Error into PO_ONLINE_REPORT
121 IF p_action = 'FINALLY CLOSE' THEN
122 l_message := 'PO_DROPSHIP_CANT_FIN_CLOSE';
123 ELSE -- cancel requisition
124 l_message := 'PO_DROPSHIP_CANT_CANCEL';
125 END IF;
126 p_return_code := 'SUBMISSION_FAILED';
127
128 IF g_fnd_debug = 'Y' THEN
129 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
130 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || l_progress,
131 'Submission Failed, ReturnCode:' || p_return_code
132 || 'l_message:' || l_message);
133 END IF;
134 END IF;
135
136 insert into po_online_report_text(online_report_id,
137 last_update_login,
138 last_updated_by,
139 last_update_date,
140 created_by,
141 creation_date,
142 line_num,
143 shipment_num,
144 distribution_num,
145 sequence,
146 text_line)
147 values (p_reportid,
148 FND_GLOBAL.LOGIN_ID,
149 FND_GLOBAL.USER_ID,
150 sysdate,
151 FND_GLOBAL.USER_ID,
152 sysdate,
153 NULL,
154 l_shipnum(i),
155 NULL,
156 1,
157 FND_MESSAGE.GET_STRING('PO', l_message));
158 return TRUE;
159
160 END IF;
161
162 END LOOP;
163
164 l_progress := '090';
165
166 return TRUE;
167
168 EXCEPTION
169 WHEN FND_API.G_EXC_ERROR THEN
170 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
171 l_return_status := FND_API.G_RET_STS_ERROR;
172 return FALSE;
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
175 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 return FALSE;
177 WHEN OTHERS THEN
178 FND_MSG_PUB.add_exc_msg('', l_api_name || '.' || l_progress);
179 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
180 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 return FALSE;
182 END chk_drop_ship;
183 --<DropShip FPJ End>
184
185
186
187
188 END PO_CONTROL_CHECKS;