1 PACKAGE BODY PO_REQIMP_VAL_PVT AS
2 /* $Header: POXVRIVB.pls 115.3 2004/01/07 18:17:42 jskim ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_REQIMP_VAL_PVT';
5 l_bulk_limit CONSTANT NUMBER := 1000; -- bulk collect limit - number of rows
6
7 /**
8 * Private Procedure: val_pjm_proj_references
9 * Requires: none
10 * Modifies: PO_INTERFACE_ERRORS, concurrent program log
11 * Parameters:
12 * p_interface_table_code - the interface table to be validated;
13 * if PO_REQIMP_VAL_PVT.G_PO_REQUISITIONS_INTERFACE,
14 use PO_REQUISITIONS_INTERFACE
15 * if PO_REQIMP_VAL_PVT.G_PO_REQ_DIST_INTERFACE,
16 use PO_REQ_DIST_INTERFACE
17 * Effects: For all records with the given request ID in the specified
18 * interface table, calls the PJM validation API to validate
19 * project and task information. Writes any validation errors to
20 * PO_INTERFACE_ERRORS and any validation warnings to the concurrent
21 * program log.
22 * Returns:
23 * x_return_status -
24 * FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurs
25 * FND_API.G_RET_STS_SUCCESS otherwise
26 */
27 PROCEDURE val_pjm_proj_references (
28 p_api_version IN NUMBER,
29 x_return_status OUT NOCOPY VARCHAR2,
30 p_interface_table_code IN VARCHAR2,
31 p_request_id IN NUMBER,
32 p_user_id IN NUMBER,
33 p_login_id IN NUMBER,
34 p_prog_application_id IN NUMBER,
35 p_program_id IN NUMBER
36 ) IS
37 l_api_name CONSTANT VARCHAR2(30) := 'VAL_PJM_PROJ_REFERENCES';
38 l_api_version CONSTANT NUMBER := 1.0;
39
40 TYPE project_info_cv_type IS REF CURSOR;
41 project_info_cv project_info_cv_type;
42 l_interface_table_name VARCHAR2(80);
43
44 TYPE l_number_tbl_type IS TABLE of NUMBER;
45 TYPE l_date_tbl_type IS TABLE of DATE;
46 l_project_id_tbl l_number_tbl_type;
47 l_task_id_tbl l_number_tbl_type;
48 l_destination_org_id_tbl l_number_tbl_type;
49 l_need_by_date_tbl l_date_tbl_type;
50 l_transaction_id_tbl l_number_tbl_type;
51 l_destination_ou_id_tbl l_number_tbl_type;
52
53 TYPE l_errors_tbl_type IS TABLE of
54 PO_INTERFACE_ERRORS.error_message%TYPE
55 INDEX BY BINARY_INTEGER;
56 TYPE l_err_transaction_id_tbl_type IS TABLE of
57 PO_INTERFACE_ERRORS.interface_transaction_id%TYPE
58 INDEX BY BINARY_INTEGER;
59 l_errors_tbl l_errors_tbl_type;
60 l_err_transaction_id_tbl l_err_transaction_id_tbl_type;
61 l_errorI NUMBER := 1;
62
63 l_val_proj_result VARCHAR(1);
64 l_val_proj_error_code VARCHAR2(80);
65
66 BEGIN
67 IF NOT FND_API.compatible_api_call(l_api_version,p_api_version,
68 l_api_name, G_PKG_NAME ) THEN
69 RAISE FND_API.g_exc_unexpected_error;
70 END IF;
71 x_return_status := FND_API.g_ret_sts_success;
72
73 IF (p_interface_table_code =
74 PO_REQIMP_VAL_PVT.G_PO_REQUISITIONS_INTERFACE) THEN
75 l_interface_table_name := 'PO_REQUISITIONS_INTERFACE';
76
77 OPEN project_info_cv FOR
78 --< Bug 3265539 Start >
79 -- Need to derive the OU of the dest inventory org as well.
80 --
81 -- SQL What: Retrieve project-related information from
82 -- PO_REQUISITIONS_INTERFACE.
83 -- SQL Why: To validate project information using the PJM API.
84 SELECT pri.project_id,
85 pri.task_id,
86 pri.destination_organization_id,
87 pri.need_by_date,
88 pri.transaction_id,
89 TO_NUMBER(hoi.org_information3)
90 FROM po_requisitions_interface pri,
91 hr_organization_information hoi
92 WHERE pri.project_accounting_context = 'Y'
93 AND pri.request_id = p_request_id
94 -- Bug 3043971 We should only perform PJM validation on Inventory and
95 -- Shop Floor lines (i.e. not Expense):
96 AND NVL(pri.destination_type_code, 'EXPENSE') IN ('INVENTORY', 'SHOP FLOOR')
97 AND pri.destination_organization_id = hoi.organization_id
98 AND hoi.org_information_context = 'Accounting Information';
99 --< Bug 3265539 End >
100
101 ELSIF (p_interface_table_code =
102 PO_REQIMP_VAL_PVT.G_PO_REQ_DIST_INTERFACE) THEN
103 l_interface_table_name := 'PO_REQ_DIST_INTERFACE';
104
105 OPEN project_info_cv FOR
106 --< Bug 3265539 Start >
107 -- Need to derive the OU of the dest inventory org as well.
108 --
109 -- SQL What: Retrieve project-related information from
110 -- PO_REQUISITIONS_INTERFACE and PO_REQ_DIST_INTERFACE.
111 -- SQL Why: To validate project information using the PJM API.
112 -- SQL Join: po_req_dist_interface.dist_sequence_id =
113 -- po_requisitions_interface.req_dist_sequence_id
114 SELECT d.project_id,
115 d.task_id,
116 d.destination_organization_id,
117 r.need_by_date,
118 d.transaction_id,
119 TO_NUMBER(hoi.org_information3)
120 FROM po_req_dist_interface d,
121 po_requisitions_interface r,
122 hr_organization_information hoi
123 WHERE d.project_accounting_context = 'Y'
124 AND d.request_id = p_request_id
125 AND d.dist_sequence_id = r.req_dist_sequence_id
126 -- Bug 3043971 We should only perform PJM validation on Inventory and
127 -- Shop Floor lines (i.e. not Expense):
128 AND NVL(d.destination_type_code, 'EXPENSE') IN ('INVENTORY', 'SHOP FLOOR')
129 AND d.destination_organization_id = hoi.organization_id
130 AND hoi.org_information_context = 'Accounting Information';
131 --< Bug 3265539 End >
132
133 ELSE
134 FND_MSG_PUB.build_exc_msg(G_PKG_NAME,l_api_name,
135 'Invalid value for p_interface_table_code: ' || p_interface_table_code);
136 RAISE FND_API.g_exc_unexpected_error;
137 END IF; -- p_interface_table_code
138
139 -- Bulk collect the project information into PL/SQL tables,
140 -- l_bulk_limit records at a time, and perform validation.
141 LOOP
142
143 FETCH project_info_cv BULK COLLECT
144 INTO l_project_id_tbl, l_task_id_tbl, l_destination_org_id_tbl,
145 l_need_by_date_tbl, l_transaction_id_tbl,
146 l_destination_ou_id_tbl --< Bug 3265539 >
147 LIMIT l_bulk_limit;
148
149 EXIT WHEN
150 l_project_id_tbl.COUNT = 0; -- no records fetched in this iteration
151
152 -- Validate each record in our PL/SQL tables
153 FOR i IN l_project_id_tbl.FIRST..l_project_id_tbl.LAST LOOP
154 --< Bug 3265539 Start >
155 -- Call PO wrapper procedure to validate the PJM project
156 PO_PROJECT_DETAILS_SV.validate_proj_references_wpr
157 (p_inventory_org_id => l_destination_org_id_tbl(i),
158 p_operating_unit => l_destination_ou_id_tbl(i),
159 p_project_id => l_project_id_tbl(i),
160 p_task_id => l_task_id_tbl(i),
161 p_date1 => l_need_by_date_tbl(i),
162 p_date2 => NULL,
163 p_calling_function => 'REQIMPORT',
164 x_error_code => l_val_proj_error_code,
165 x_return_code => l_val_proj_result);
166
167 IF (l_val_proj_result = PO_PROJECT_DETAILS_SV.pjm_validate_failure) THEN
168 -- Add the error and the current transaction ID to PL/SQL tables
169 l_errors_tbl(l_errorI) := fnd_message.get;
170 l_err_transaction_id_tbl(l_errorI) := l_transaction_id_tbl(i);
171 l_errorI := l_errorI + 1;
172 ELSIF (l_val_proj_result = PO_PROJECT_DETAILS_SV.pjm_validate_warning) THEN
173 -- Write the warning to the concurrent program log
174 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.get);
175 END IF;
176 --< Bug 3265539 End >
177
178 END LOOP; -- Validate each record...
179
180 EXIT WHEN project_info_cv%NOTFOUND; -- all records have been fetched
181
182 END LOOP; -- Bulk collect...
183
184 CLOSE project_info_cv;
185
186 IF (l_errors_tbl.COUNT = 0) THEN
187 RETURN;
188 END IF;
189
190 -- Bulk insert all the errors into PO_INTERFACE_ERRORS
191 FORALL i IN l_errors_tbl.FIRST..l_errors_tbl.LAST
192 INSERT INTO po_interface_errors
193 (interface_type,interface_transaction_id,column_name,
194 error_message,
195 creation_date,created_by,last_update_date,last_updated_by,
196 last_update_login,request_id,program_application_id,program_id,
197 program_update_date, table_name)
198 VALUES
199 ('REQIMPORT',l_err_transaction_id_tbl(i),'PROJECT ACCOUNTING COLUMNS',
200 l_errors_tbl(i),
201 SYSDATE,p_user_id,SYSDATE,p_user_id,
202 p_login_id,p_request_id,p_prog_application_id,
203 p_program_id,SYSDATE,l_interface_table_name);
204
205 EXCEPTION
206 WHEN FND_API.g_exc_unexpected_error THEN
207 x_return_status := FND_API.g_ret_sts_unexp_error;
208 IF project_info_cv%ISOPEN THEN
209 CLOSE project_info_cv;
210 END IF;
211 WHEN OTHERS THEN
212 x_return_status := FND_API.g_ret_sts_unexp_error;
213 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
214 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name);
215 END IF;
216 IF project_info_cv%ISOPEN THEN
217 CLOSE project_info_cv;
218 END IF;
219 END val_pjm_proj_references;
220
221 END PO_REQIMP_VAL_PVT;
[Home] [Help]