1 PACKAGE CSD_WIP_JOB_PVT AUTHID CURRENT_USER AS
2 /* $Header: csdvwjbs.pls 120.6.12020000.3 2013/04/10 01:04:00 takwong ship $ */
3 -- Start of Comments
4 -- Package name : CSD_WIP_JOB_PVT
5 -- Purpose : This package submits and creates WIP jobs using WIP Mass Load.
6 -- Submit_Jobs is the API which uses various helper procedures to
7 -- Submit WIP Mass Load, waits for it to complete successfully, then
8 -- calls WIP_UPDATE API to update CSD_REPAIR_JOB_XREF with the
9 -- newly created wip_entitity_id values.
10 -- Besides these procedure, this package has a helper function
11 -- is_dmf_patchset_level_j which is used by the client application
12 -- to check if the discrete manufacturing patchset level is at 'j' or
13 -- beyond. Based on this, the client application decides how to call
14 -- the WIP completion form.
15 --
16 -- History : 08/20/2003, Created by Shiv Ragunathan
17 -- History :
18 -- History :
19 -- NOTE :
20 -- End of Comments
21
22
23 -- Record Type for job header information
24
25 TYPE JOB_HEADER_REC_TYPE IS RECORD (
26 JOB_PREFIX VARCHAR2(80),
27 ORGANIZATION_ID NUMBER,
28 STATUS_type NUMBER,
29 SCHEDULED_START_DATE DATE,
30 SCHEDULED_END_DATE DATE,
31 INVENTORY_ITEM_ID NUMBER,
32 CLASS_CODE VARCHAR2(10),
33 QUANTITY NUMBER,
34 -- rfieldma, project integration, new parameters project_id, task_id, unit_number
35 PROJECT_ID NUMBER := null, --CSD_PROCESS_UTIL.G_MISS_NUM, -- rfieldma, default?
36 TASK_ID NUMBER := null, --CSD_PROCESS_UTIL.G_MISS_NUM, -- rfieldma, default?
37 UNIT_NUMBER VARCHAR2(30) := null --CSD_PROCESS_UTIL.G_MISS_CHAR -- rfieldma, default?
38 );
39
40
41 -- Record Type for bills and routings information. This also has
42 -- source_type and source information
43
44 TYPE JOB_BILL_ROUTING_REC_TYPE IS RECORD (
45 routing_reference_id NUMBER,
46 bom_reference_id NUMBER,
47 alternate_routing_designator VARCHAR2(10),
48 alternate_bom_designator VARCHAR2(10),
49 COMPLETION_SUBINVENTORY VARCHAR2(10),
50 COMPLETION_LOCATOR_ID NUMBER,
51 JOB_NAME VARCHAR2(240),
52 source_type_code VARCHAR2(30),
53 source_id1 NUMBER,
54 ro_service_code_id NUMBER,
55 group_id NUMBER := NULL
56 );
57
58
59 -- Table Type corresponding to JOB_BILL_ROUTING_REC_TYPE
60
61 TYPE JOB_BILL_ROUTING_TBL_TYPE IS TABLE OF JOB_BILL_ROUTING_REC_TYPE INDEX BY BINARY_INTEGER;
62
63 --12.1 create job from repair estimate tab, subhat.
64 -- Record type for having the UI details from the estimates tab. This also consists project --integration information
65
66 TYPE ESTM_JOB_DETLS_REC_TYPE IS RECORD (
67 repair_line_id NUMBER,
68 inventory_item_id NUMBER,
69 repair_quantity NUMBER,
70 project_id NUMBER,
71 task_id NUMBER,
72 --bug#6930575,subhat
73 --unit_number NUMBER
74 unit_number VARCHAR2(30)
75 );
76
77 TYPE job_header_tbl IS TABLE OF wip_job_schedule_interface%ROWTYPE INDEX BY BINARY_INTEGER;
78 -- API
79 -- SUBMIT_JOBS
80 --
81 -- Purpose
82 -- This API creates WIP Jobs by submitting the passed in Job information to
83 -- WIP Mass Load and updates CSD tables with the newly created jobs information.
84 -- It achieves this by calling helper procedures.
85 --
86 -- This API inserts Job header, Bills and Routing information passed in into
87 -- WIP_JOB_SCHEDULE_INTERFACE table by calling procedure insert_job_header.
88 --
89 -- If job name is not passed in, then it is generated here by appending a
90 -- sequence generated number to the job_name_prefix passed in.
91 -- If job name is passed in, it is validated to make sure that it is unique
92 -- for the specified organization.
93 --
94 -- This API then submits the concurrent request for concurrent
95 -- program 'Depot Repair WIP Job Submission', which submits WIP Mass Load,
96 -- waits for it to complete and then runs the WIP Update program to update WIP
97 -- information in CSD tables.
98 --
99 -- If no routings or bills are passed in, jobs are submitted to WIP Mass Load based
100 -- on the header information to create jobs with no operations, material requirements
101 -- or resource requirements.
102 --
103 -- Arguments
104 -- p_repair_line_id - Repair Line Id of the repair order for which the jobs are being created.
105 -- WIP Update program is run for the specified repair order.
106 -- If jobs are being submitted for more than one repair order, then this is
107 -- passed in as null and the WIP Update program is run for all the eligible
108 -- repair orders.
109 -- p_job_header_rec - Job header Information record. This is the same for all the jobs being created.
110 -- p_x_job_bill_routing_tbl - Table of Bill and Routing information records. Each record results in a
111 -- new job. If a record here has a not null Job Name specified, then the job name
112 -- specified here is used, instead of generating it. This is done only when one job
113 -- is being submitted and the profile option 'Use CSD as Job Prefix' is set to 'N'.
114 -- This is a IN OUT parameter as the generated Job names are passed back to the
115 -- calling program in this table.
116 -- x_group_id - Group_id used for the WIP Mass Load concurrent request submission. This is returned
117 -- to the calling program.
118 -- x_request_id - Concurrent Request id of the concurrent request submitted for concurrent program
119 -- 'Depot Repair WIP Job Submission'. This is passed back to the calling program.
120 --
121 -- Note, p_commit is not specified as a parameter to this API, as for successful submission of a concurrent
122 -- request, a commit is required always, so this API always commits. For the same reason, this API is
123 -- declared as an AUTONOMOUS Transaction. For a AUTONOMOUS Transaction, we cannot rollback to a specified
124 -- SAVEPOINT, hence SAVEPOINT is not specified.
125
126 PROCEDURE submit_jobs
127 (
128 p_api_version IN NUMBER,
129 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
130 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
131 x_return_status OUT NOCOPY VARCHAR2,
132 x_msg_count OUT NOCOPY NUMBER,
133 x_msg_data OUT NOCOPY VARCHAR2,
134 p_repair_line_id IN NUMBER,
135 p_job_header_rec IN job_header_rec_type,
136 p_x_job_bill_routing_tbl IN OUT NOCOPY job_bill_routing_tbl_type,
137 x_group_id OUT NOCOPY NUMBER,
138 x_request_id OUT NOCOPY NUMBER
139 );
140
141
142
143 -- This is the executable procedure for concurrent program
144 -- 'Depot Repair WIP Job Submission'. This program is submitted from
145 -- submit_jobs API.
146 -- This procedure submits WIP Mass Load, waits for it to complete successfully,
147 -- then calls the WIP_Update API to associate new records
148 -- created in csd_repair_job_xref table with corresponding newly
149 -- created wip_entity_ids.
150 -- This concurrent program is passed in group_id and repair_line_id as
151 -- parameters. If repair_line_id is null, then the WIP Update program is
152 -- run for all the eligible repair orders, otherwise the WIP Update porgram
153 -- is run for the specified repair_line_id.
154
155 procedure submit_wip_mass_load_conc
156 (
157 errbuf OUT NOCOPY VARCHAR2,
158 retcode OUT NOCOPY VARCHAR2,
159 p_group_id IN NUMBER,
160 p_repair_line_id IN NUMBER
161 );
162
163
164 -- This function checks if the discrete manufacturing patchset level is
165 -- at j or beyond and if so, returns true. For now, this is used from
166 -- Repair Jobs tab, when COMPLETE_JOB button is pressed. If the patchset level
167 -- is at j or beyond, then the new WIP Completion form is called, hence
168 -- new parameters are passed, If not, the old WIP Completion form is called, hence
169 -- the new parameters are not passed. The new parameters are wip_entity_id and
170 -- transaction_quantity which are used to default the WIP job information, when the
171 -- WIP Completion form opens.
172
173 FUNCTION is_dmf_patchset_level_j RETURN BOOLEAN;
174
175 -- This procedure creates WIP job, when the create job process is invoked from repair estimate tab.
176 -- the procedure defaults all the required job header information from the profile values.
177 -- This procedure inserts the job information into WIP interface table, and the WIP api picks up from
178 -- interface table to create the job. This is the new feature in 12.1 subhat
179
180 PROCEDURE create_job_from_estimate(
181 p_api_version_number IN NUMBER,
182 p_init_msg_list IN VARCHAR2 ,
183 p_commit IN VARCHAR2 ,
184 p_validation_level IN NUMBER,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2,
188 x_job_name OUT NOCOPY VARCHAR2,
189 x_wip_entity_id OUT NOCOPY NUMBER,
190 p_ESTM_JOB_DETLS_REC_TYPE IN ESTM_JOB_DETLS_REC_TYPE
191 );
192
193 -- This procedure creates the material requirements for the job using the material estimate lines.
194 -- the procedure is invoked after a job is successfuly completed. This procedure inturn calls one -- more routine to get the material requirements table type.
195 -- 12.1 Create Job from estimate function subhat.
196
197 PROCEDURE matrl_reqmnt_from_estms(
198 p_api_version_number IN NUMBER,
199 p_init_msg_list IN VARCHAR2,
200 p_commit IN VARCHAR2,
201 p_validation_level IN NUMBER,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 x_op_created OUT NOCOPY VARCHAR2,
206 p_rep_line_id IN NUMBER,
207 p_wip_entity_id IN NUMBER
208 );
209
210 -- 12.1.3 changes, subhat.
211 -- Exposing this API. This is for the use of internal depot dev team only. No other use is supported.
212 -- This procedure generates a job name by appending a sequence generated number
213 -- to the passed in Job_Prefix
214 -- It Validates that the generated job name is unique for the specified organization,
215 -- It keeps looping and appending the subsequent sequence generated number, till a
216 -- unique Job name is generated
217
218
219 PROCEDURE generate_job_name
220 (
221 p_job_prefix IN VARCHAR2,
222 p_organization_id IN NUMBER,
223 x_job_name OUT NOCOPY VARCHAR2
224 );
225
226 -- Mass create jobs functionality for HW and many more customers.
227 -- Returns a table of concatenated job names delimited by ",". It also
228 -- returns the concurrent request id for depot repair create jobs program.
229 -- rest of the paramters are self explanatory. Pass a valid value or null.
230 -- do NOT pass FND_API.G_MISS_* for unknown values.
231
232 PROCEDURE bulk_submit_jobs(
233 p_api_version IN NUMBER,
234 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
235 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 px_job_names IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
240 p_repair_line_ids IN JTF_NUMBER_TABLE,
241 p_inventory_item_ids IN JTF_NUMBER_TABLE,
242 p_quantities IN JTF_NUMBER_TABLE,
243 p_inventory_org_id IN NUMBER,
244 p_start_date IN DATE,
245 p_end_date IN DATE,
246 p_accouting_class IN VARCHAR2,
247 p_job_type IN NUMBER DEFAULT 1,
248 p_bill_reference_id IN NUMBER,
249 p_route_reference_id IN NUMBER,
250 p_alternate_bom IN VARCHAR2,
251 p_alternate_route IN VARCHAR2,
252 p_service_code_id IN NUMBER,
253 p_upgrade_to_item_id IN NUMBER DEFAULT NULL,
254 p_job_status IN NUMBER,
255 x_request_id OUT NOCOPY NUMBER,
256 p_scheduling_priority IN NUMBER DEFAULT NULL
257 );
258
259 --bug#12316893
260 /* Delete_Completed_Wip_Records (interface_id)
261 This procedure deletes records from the WIP_JOB_SCHEDULE_INTERFACE table
262 that were successfully loaded by the Mass Load Process
263 -WIP_MASSLOAD_PUB.createOneJob.
264 */
265 PROCEDURE Delete_Completed_Wip_Records
266 (
267 p_interface_id IN NUMBER
268 );
269 --bug#12316893
270
271
272 END CSD_WIP_JOB_PVT;
[Home] [Help]