[Home] [Help]
PACKAGE BODY: APPS.ITG_SYNCFIELDINBOUND_PVT
Source
1 PACKAGE BODY ITG_SyncFieldInbound_PVT AS
2 /* ARCS: $Header: itgvsfib.pls 120.3 2006/01/23 03:47:54 bsaratna noship $
3 * CVS: itgvsfib.pls,v 1.14 2002/12/23 21:20:30 ecoe Exp
4 */
5
6 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ITG_SyncFieldInbound_PVT';
8 g_action VARCHAR2(200);
9
10 PROCEDURE Process_PoNumber(
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_reqid IN NUMBER,
15 p_reqlinenum IN NUMBER,
16 p_poid IN NUMBER,
17 p_org IN NUMBER
18 ) IS
19 l_tmp varchar2(30);
20 l_req_hdr_id varchar2(500);
21 l_api_name CONSTANT VARCHAR2(30) := 'Sync_PoLookup';
22 l_api_version CONSTANT NUMBER := 1.0;
23 BEGIN
24 g_action := 'Field sync';
25 /* Initialize return status */
26 x_return_status := FND_API.G_RET_STS_SUCCESS;
27
28 IF (l_Debug_Level <= 2) THEN
29 itg_debug_pub.Add('ENTERING PPN-Process_PoNumber',2);
30 END IF;
31
32 BEGIN
33 SAVEPOINT Process_PoNumber_PVT;
34 BEGIN
35 FND_Client_Info.set_org_context(p_org); /*bug 4073707*/
36 MO_GLOBAL.set_policy_context('S', p_org); -- MOAC
37 EXCEPTION
38 WHEN OTHERS THEN
39 itg_msg.invalid_org(p_org);
40 RAISE FND_API.G_EXC_ERROR;
41 END;
42
43 ITG_Debug.setup(
44 p_reset => TRUE,
45 p_pkg_name => G_PKG_NAME,
46 p_proc_name => l_api_name);
47
48 --Now in wrapper
49 --FND_MSG_PUB.Initialize;
50 IF (l_Debug_Level <= 1) THEN
51 itg_debug_pub.Add('PPN - Top of procedure.' ,1);
52 itg_debug_pub.Add('PPN - p_reqid'||p_reqid ,1);
53 itg_debug_pub.Add('PPN - p_reqlinenum'||p_reqlinenum,1);
54 itg_debug_pub.Add('PPN - p_poid'||p_poid ,1);
55 itg_debug_pub.Add('PPN - p_org'||p_org ,1);
56 END IF;
57
58 g_action := 'Sync-field parameter validation';
59
60 IF (l_Debug_Level <= 1) THEN
61 itg_debug_pub.Add('PPN - Validating inputs' ,1);
62 END IF;
63
64 ITG_BOAPI_Utils.validate('FIELDID', NULL, NULL, FALSE, p_reqid);
65 ITG_BOAPI_Utils.validate('ORACLEITG.REQLINENUM', NULL, NULL, FALSE, p_reqlinenum);
66 ITG_BOAPI_Utils.validate('ORACLEITG.POID', NULL, NULL, FALSE, p_poid);
67 ITG_BOAPI_Utils.validate('ORACLEITG.POENTITY', NULL, NULL, FALSE, p_org);
68
69 IF (l_Debug_Level <= 1) THEN
70 itg_debug_pub.Add('PPN - Updating requisition info' ,1);
71 END IF;
72 /* 2683558 : multiple po numbers from SAP for the same Req. Updates the table
73 with "," seperated. */
74
75 BEGIN
76 SELECT requisition_header_id
77 INTO l_req_hdr_id
78 FROM po_requisition_headers_all
79 WHERE segment1 = to_char(p_reqid)
80 AND org_id = p_org;
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 itg_msg.no_req_hdr(p_reqid,p_org);
84 RAISE FND_API.G_EXC_ERROR;
85 END;
86
87 g_action := 'Requisition-line update';
88
89 UPDATE po_requisition_lines_all
90 SET attribute13 = attribute13 ||DECODE(NVL(attribute13,'FIRST'),'FIRST','',',')
91 ||to_char(p_poid)
92 WHERE requisition_header_id = l_req_hdr_id
93 AND line_num = p_reqlinenum;
94
95 IF (l_Debug_Level <= 1) THEN
96 itg_debug_pub.Add('PPN - Update done' ,1);
97 END IF;
98
99 IF SQL%ROWCOUNT <> 1 THEN
100 IF (l_Debug_Level <= 1) THEN
101 itg_debug_pub.Add('PPN - Update failed' ,1);
102 END IF;
103
104 ITG_MSG.no_req_line(p_reqid, p_reqlinenum,p_org);
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107
108 COMMIT WORK;
109 EXCEPTION
110 WHEN FND_API.G_EXC_ERROR THEN
111 ROLLBACK TO Process_PoNumber_PVT;
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 ITG_msg.checked_error(g_action);
114
115 WHEN OTHERS THEN
116 ROLLBACK TO Process_PoNumber_PVT;
117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
118 itg_debug.msg('Unexpected error (Field sync) - ' || substr(SQLERRM,1,255),true);
119 ITG_msg.unexpected_error(g_action);
120 END;
121
122 IF (l_Debug_Level <= 2) THEN
123 itg_debug_pub.Add('EXITING PPN-Process_PoNumber',2);
124 END IF;
125
126 --Removed FND_MSG_PUB.Count_And_Get
127 END Process_PoNumber;
128 END ITG_SyncFieldInbound_PVT;