[Home] [Help]
24: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_WIP_JOB_PVT';
25: g_job_prefix VARCHAR2(30) := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
26:
27: -- This procedure accepts job header, bills and routing information and inserts it into
28: -- WIP_JOB_SCHEDULE_INTERFACE table. This procedure inserts one record at a time, hence
29: -- this procedure needs to be called in a loop for multiple jobs being submitted
30: -- to WIP Mass Load. If all the records need to be processed by a single WIP Mass Load
31: -- request, they should all be passed in the the same group_id.
32:
40: )
41: IS
42:
43: -- Job Record to hold the Job header, bills and routing information being inserted
44: -- into wip_job_schedule_interface
45:
46: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
47:
48:
42:
43: -- Job Record to hold the Job header, bills and routing information being inserted
44: -- into wip_job_schedule_interface
45:
46: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
47:
48:
49: -- variables used for FND_LOG debug messages
50:
52: l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
53: l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
54:
55:
56: -- Constants Used for Inserting into wip_job_schedule_interface,
57: -- These are the values needed for WIP Mass Load to pick up the records
58:
59: -- Indicates that the process Phase is Validation
60: l_validation_phase CONSTANT NUMBER := 2;
97:
98: l_job_header_rec.group_id := p_group_id;
99:
100: -- nnadig: bug 9263438 - Use wip_interface_s for interface_id, not sequence from group_id
101: -- According to wip_job_schedule_interface documentation, interface ID should be NULL
102: -- and will be populated by WIP later. However, for defaulting material, CreateOneJob
103: -- is used which requires an interface ID, so we will generate an interface ID for this
104: -- case only and pass it back to the calling procedure to use.
105:
157: l_job_header_rec.completion_subinventory := p_job_bill_routing_rec.completion_subinventory;
158: l_job_header_rec.completion_locator_id := p_job_bill_routing_rec.completion_locator_id;
159:
160:
161: --insert into table wip_job_schedule_interface
162: BEGIN
163: INSERT INTO wip_job_schedule_interface
164: (
165: last_update_date,
159:
160:
161: --insert into table wip_job_schedule_interface
162: BEGIN
163: INSERT INTO wip_job_schedule_interface
164: (
165: last_update_date,
166: last_updated_by,
167: creation_date,
247:
248: END insert_job_header;
249:
250: -- overloaded version of insert job header. receives the job header rec and inserts into the
251: -- wip_job_schedule_interface table.
252: -- The job header rec is created as ROWTYPE of wip_job_schedule_interface.
253: -- 12.1 Create Job from Estimates change, subhat.
254:
255: PROCEDURE insert_job_header
248: END insert_job_header;
249:
250: -- overloaded version of insert job header. receives the job header rec and inserts into the
251: -- wip_job_schedule_interface table.
252: -- The job header rec is created as ROWTYPE of wip_job_schedule_interface.
253: -- 12.1 Create Job from Estimates change, subhat.
254:
255: PROCEDURE insert_job_header
256: (
253: -- 12.1 Create Job from Estimates change, subhat.
254:
255: PROCEDURE insert_job_header
256: (
257: p_job_header_rec IN wip_job_schedule_interface%ROWTYPE,
258: x_return_status OUT NOCOPY VARCHAR2
259: )
260: IS
261:
259: )
260: IS
261:
262: -- Job Record to hold the Job header, bills and routing information being inserted
263: -- into wip_job_schedule_interface
264:
265: l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
266:
267:
261:
262: -- Job Record to hold the Job header, bills and routing information being inserted
263: -- into wip_job_schedule_interface
264:
265: l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
266:
267:
268: -- variables used for FND_LOG debug messages
269:
271: l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
272: l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
273:
274:
275: -- Constants Used for Inserting into wip_job_schedule_interface,
276: -- These are the values needed for WIP Mass Load to pick up the records
277:
278: -- Indicates that the process Phase is Validation
279: lc_validation_phase CONSTANT NUMBER := 2;
321: l_job_header_rec.last_updated_by := fnd_global.user_id;
322: l_job_header_rec.last_update_login := fnd_global.login_id;
323:
324:
325: --insert into table wip_job_schedule_interface
326: BEGIN
327: INSERT INTO wip_job_schedule_interface
328: (
329: wip_entity_id,
323:
324:
325: --insert into table wip_job_schedule_interface
326: BEGIN
327: INSERT INTO wip_job_schedule_interface
328: (
329: wip_entity_id,
330: interface_id,
331: last_update_date,
484:
485:
486: -- This procedure checks if the specified Job name exists in the
487: -- specified organization. It checks if it exists in
488: -- wip_entities or wip_job_schedule_interface table.
489: -- If it exists, then an Error status is returned.
490: -- If it does not exist in either of the tables, then
491: -- a Sucess status is returned.
492: -- This procedure is used whenever a job_name is generated, to confirm that
516: -- Job does not exist in WIP_entities, check if it is already inserted in the interface table by another
517: -- process and so may be in the process of getting into WIP.
518: -- If it exists, do not want to use this job name, so return Error
519:
520: Select count(*) into l_job_count from wip_job_schedule_interface where job_name = p_job_name and
521: organization_id = p_organization_id ;
522:
523: IF l_job_count = 0 THEN
524:
529: RETURN;
530:
531: ELSE
532:
533: -- Job exists in wip_job_schedule_interface table, hence return Error status
534:
535: x_return_status := FND_API.G_RET_STS_ERROR;
536: RETURN;
537:
793: -- WIP Mass Load and updates CSD tables with the newly created jobs information.
794: -- It achieves this by calling helper procedures.
795: --
796: -- This API inserts Job header, Bills and Routing information passed in into
797: -- WIP_JOB_SCHEDULE_INTERFACE table by calling procedure insert_job_header.
798: --
799: -- If job name is not passed in, then it is generated here by appending a
800: -- sequence generated number to the job_name_prefix passed in.
801: -- If job name is passed in, it is validated to make sure that it is unique
963:
964: l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
965:
966: -- Get the Group_id to be used for WIP Mass Load, All the records inserted into
967: -- wip_job_schedule_interface have the same group_id , so that one WIP Mass Load
968: -- request can process all the records
969: --if (l_default_ro_item = 'N') then
970: --SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
971:
966: -- Get the Group_id to be used for WIP Mass Load, All the records inserted into
967: -- wip_job_schedule_interface have the same group_id , so that one WIP Mass Load
968: -- request can process all the records
969: --if (l_default_ro_item = 'N') then
970: --SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
971:
972: if (l_default_ro_item = 'Y') then
973: OPEN c_repair_line_info(p_repair_line_id);
974: FETCH c_repair_line_info into
1012:
1013: IF p_x_job_bill_routing_tbl.COUNT = 0 THEN
1014:
1015: -- if (l_default_ro_item = 'Y') then
1016: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1017: -- End if;
1018:
1019: p_x_job_bill_routing_tbl(0).group_id := l_group_id;
1020:
1040: END IF;
1041:
1042:
1043: -- Call procedure to insert job header and job name information
1044: -- into wip_job_schedule_interface table
1045:
1046:
1047: insert_job_header( p_job_header_rec => p_job_header_rec,
1048: p_job_bill_routing_rec => l_job_bill_routing_rec,
1170:
1171: LOOP
1172:
1173: --if (l_default_ro_item = 'Y') then
1174: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1175: --End if;
1176:
1177: p_x_job_bill_routing_tbl(rt_ctr).group_id := l_group_id;
1178:
1227: 'Just before calling insert_job_header');
1228: END IF;
1229:
1230: -- Call procedure to insert job header and bills, routing information
1231: -- into wip_job_schedule_interface table
1232: -- All the records inserted into the WIP interface table
1233: -- are passed the same group_id and hence will be processed by one WIP Mass Load
1234: -- request.
1235:
1586: -- the process save mtl line API overwrites it to 512.
1587: -- subhat, bug#13586864.
1588: SELECT source_line_id
1589: BULK COLLECT INTO l_repair_line_ids
1590: FROM wip_job_schedule_interface
1591: WHERE group_id = p_group_id;
1592:
1593: -- submit concurrent request for WIP Mass Load
1594:
1941: -- to the WIP API. When successfully finished, the procedure will return WIP Entity ID and Job Name
1942: -- to the calling routine.
1943: Code change history:
1944: -- 4/2/2010 120.13.12010000.3 nnadig: Bug fix 9263438,
1945: Use wip_interface_s for interface_id instead of wip_job_schedule_interface_s
1946: ***************/
1947: PROCEDURE create_job_from_estimate(
1948: p_api_version_number IN NUMBER,
1949: p_init_msg_list IN VARCHAR2 ,
1957: p_ESTM_JOB_DETLS_REC_TYPE IN ESTM_JOB_DETLS_REC_TYPE
1958: ) IS
1959:
1960: -- Job Record to hold the Job header, bills and routing information being inserted
1961: -- into wip_job_schedule_interface
1962:
1963: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
1964:
1965: lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_WIP_JOB';
1959:
1960: -- Job Record to hold the Job header, bills and routing information being inserted
1961: -- into wip_job_schedule_interface
1962:
1963: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
1964:
1965: lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_WIP_JOB';
1966: lc_api_version_number CONSTANT NUMBER := 1.0;
1967:
2087:
2088:
2089: -- Get the Group_id to be used for WIP Create Job,
2090:
2091: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
2092:
2093: -- Bug 9263438: Use wip_interface_s for interface_id instead of wip_job_schedule_interface_s
2094: SELECT wip_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
2095:
2089: -- Get the Group_id to be used for WIP Create Job,
2090:
2091: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
2092:
2093: -- Bug 9263438: Use wip_interface_s for interface_id instead of wip_job_schedule_interface_s
2094: SELECT wip_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
2095:
2096:
2097: generate_job_name ( p_job_prefix =>l_job_prefix,
2146: END IF;
2147:
2148: -- Call procedure to insert job header and bills, routing
2149: -- information
2150: -- into wip_job_schedule_interface table
2151:
2152:
2153: insert_job_header( p_job_header_rec =>
2154: l_job_header_rec,
2727: END IF;
2728:
2729: IF l_group_id = 0
2730: THEN
2731: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
2732: END IF;
2733:
2734: l_job_header_tbl(l_job_header_index).group_id := l_group_id;
2735: l_job_header_index := l_job_header_index + 1;
2782: px_job_names(i) := lx_job_name;
2783:
2784: IF l_group_id = 0
2785: THEN
2786: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
2787: END IF;
2788:
2789: l_job_header_tbl(l_job_header_index).group_id := l_group_id;
2790: l_job_header_index := l_job_header_index + 1;
2796: END IF;
2797:
2798: END LOOP;
2799:
2800: -- insert the job header rec into wip_job_schedule_interface_table and call the CP.
2801: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2802: fnd_log.string(fnd_log.level_statement, lc_module_name,'Bulk insert into wip_job_schedule_interface. Total number of records to be inserted is '||l_job_header_tbl.COUNT);
2803: fnd_log.string(fnd_log.level_statement, lc_module_name,'The group id for the batch is = '||l_group_id);
2804: END IF;
2798: END LOOP;
2799:
2800: -- insert the job header rec into wip_job_schedule_interface_table and call the CP.
2801: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2802: fnd_log.string(fnd_log.level_statement, lc_module_name,'Bulk insert into wip_job_schedule_interface. Total number of records to be inserted is '||l_job_header_tbl.COUNT);
2803: fnd_log.string(fnd_log.level_statement, lc_module_name,'The group id for the batch is = '||l_group_id);
2804: END IF;
2805:
2806: FORALL j IN 1 ..l_job_header_tbl.COUNT
2803: fnd_log.string(fnd_log.level_statement, lc_module_name,'The group id for the batch is = '||l_group_id);
2804: END IF;
2805:
2806: FORALL j IN 1 ..l_job_header_tbl.COUNT
2807: INSERT INTO wip_job_schedule_interface VALUES l_job_header_tbl(j);
2808:
2809: -- insert the records inot the csd xref table.
2810: -- instead of table handler, I am doing direct table update, since the table handlers
2811: -- process one row at a time. For an online batch processing, this is not ideal.
2888: END bulk_submit_jobs;
2889:
2890: --bug#12316893
2891: /* Delete_Completed_Wip_Records (interface_id)
2892: This procedure deletes records from the WIP_JOB_SCHEDULE_INTERFACE table
2893: that were successfully loaded by the Mass Load Process
2894: -WIP_MASSLOAD_PUB.createOneJob.
2895: This procedure is similar to WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records
2896: Please check WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records for
2900: PROCEDURE Delete_Completed_Wip_Records(p_interface_id IN NUMBER) IS
2901:
2902: cursor old_requests is
2903: select rowid, interface_id, group_id, header_id
2904: from wip_job_schedule_interface
2905: where
2906: interface_id = p_interface_id and process_status= WIP_CONSTANTS.ML_COMPLETE
2907: for update;
2908:
2916:
2917: delete from wip_interface_errors
2918: where interface_id = old_request.interface_id;
2919:
2920: delete from wip_job_schedule_interface
2921: where rowid = old_request.rowid;
2922:
2923: -- delete from wip_job_dtls_interface
2924: -- where group_id = old_request.group_id