[Home] [Help]
115: lc_error_process_status CONSTANT NUMBER := 3;
116:
117:
118: Cursor check_ml_interface_errors IS
119: select 'exists' from wip_job_schedule_interface where
120: group_id = p_group_id
121: and process_status = lc_error_process_status;
122:
123: l_error_exists VARCHAR2(6);
222: wjdi.inventory_item_id_new,
223: wjdi.resource_id_new
224: from
225: wip_interface_errors_v wie,
226: wip_job_schedule_interface wjsi,
227: wip_entities we,
228: wip_job_dtls_interface wjdi
229: where wie.interface_id = wjsi.interface_id
230: and we.wip_entity_id = wjsi.wip_entity_id
274: END add_wip_interface_errors;
275:
276: -- This procedure checks if the specified Job name exists in the
277: -- specified organization. It checks if it exists in
278: -- wip_entities or wip_job_schedule_interface table.
279: -- If it exists, then an Error status is returned.
280: -- If it does not exist in either of the tables, then
281: -- a Sucess status is returned.
282: -- This procedure is used whenever a job_name is generated, to confirm that
306: -- Job does not exist in WIP_entities, check if it is already inserted in the interface table by another
307: -- process and so may be in the process of getting into WIP.
308: -- If it exists, do not want to use this job name, so return Error
309:
310: Select count(*) into l_job_count from wip_job_schedule_interface where job_name = p_job_name and
311: organization_id = p_organization_id ;
312:
313: IF l_job_count = 0 THEN
314:
319: RETURN;
320:
321: ELSE
322:
323: -- Job exists in wip_job_schedule_interface table, hence return Error status
324:
325: x_return_status := FND_API.G_RET_STS_ERROR;
326: RETURN;
327:
393: END generate_job_name;
394:
395:
396: -- This procedure accepts job header, bills and routing information and inserts it into
397: -- WIP_JOB_SCHEDULE_INTERFACE table.
398:
399: PROCEDURE insert_job_header
400: (
401: p_job_header_rec IN wip_job_schedule_interface%ROWTYPE,
397: -- WIP_JOB_SCHEDULE_INTERFACE table.
398:
399: PROCEDURE insert_job_header
400: (
401: p_job_header_rec IN wip_job_schedule_interface%ROWTYPE,
402: x_return_status OUT NOCOPY VARCHAR2
403: )
404: IS
405:
403: )
404: IS
405:
406: -- Job Record to hold the Job header, bills and routing information being inserted
407: -- into wip_job_schedule_interface
408:
409: l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
410:
411:
405:
406: -- Job Record to hold the Job header, bills and routing information being inserted
407: -- into wip_job_schedule_interface
408:
409: l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
410:
411:
412: -- variables used for FND_LOG debug messages
413:
415: l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
416: l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
417:
418:
419: -- Constants Used for Inserting into wip_job_schedule_interface,
420: -- These are the values needed for WIP Mass Load to pick up the records
421:
422: -- Indicates that the process Phase is Validation
423: lc_validation_phase CONSTANT NUMBER := 2;
465: l_job_header_rec.last_updated_by := fnd_global.user_id;
466: l_job_header_rec.last_update_login := fnd_global.login_id;
467:
468:
469: --insert into table wip_job_schedule_interface
470: BEGIN
471: INSERT INTO wip_job_schedule_interface
472: (
473: wip_entity_id,
467:
468:
469: --insert into table wip_job_schedule_interface
470: BEGIN
471: INSERT INTO wip_job_schedule_interface
472: (
473: wip_entity_id,
474: interface_id,
475: last_update_date,
580: l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
581: l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
582:
583:
584: -- Constants Used for Inserting into wip_job_schedule_interface,
585: -- These are the values needed for WIP Mass Load to pick up the records
586:
587: -- Indicates that the process Phase is Validation
588: lc_validation_phase CONSTANT NUMBER := 2;
2837: lc_inv_lv_loc_cntrl CONSTANT NUMBER := 5;
2838: lc_MTI_source_table CONSTANT NUMBER := 1;
2839:
2840:
2841: -- Constants Used for Inserting into wip_job_schedule_interface,
2842: -- and details interface tables
2843:
2844: lc_non_std_update_load_type CONSTANT NUMBER := 3;
2845: --lc_non_std_update_load_type CONSTANT NUMBER := 9;
2863: -- and mtl_transactions_interface data
2864:
2865: l_transactions_interface_rec mtl_transactions_interface%ROWTYPE;
2866: l_srl_nmbrs_interface_rec mtl_serial_numbers_interface%ROWTYPE;
2867: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
2868: l_job_details_rec wip_job_dtls_interface%ROWTYPE;
2869:
2870:
2871:
2994: -- l_job_details_rec.wip_supply_type := lc_push_wip_supply_type;
2995: l_job_details_rec.wip_supply_type := null;
2996: -- Get the Group_id to be used for WIP Mass Load,
2997:
2998: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
2999:
3000: -- l_job_header_rec.group_id := l_job_header_rec.header_id;
3001:
3002: l_job_details_rec.group_id := l_job_header_rec.group_id;
3359:
3360:
3361: -- Get the Group_id to be used for WIP Mass Load,
3362:
3363: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.header_id FROM dual;
3364:
3365: -- l_job_header_rec.group_id := l_job_header_rec.header_id;
3366:
3367: -- l_job_details_rec.group_id := l_job_header_rec.header_id;
3368: l_job_details_rec.parent_header_id := l_job_header_rec.header_id;
3369:
3370:
3371:
3372: -- SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
3373:
3374:
3375: --Commenting out as don't think this is needed
3376: -- Need to remove ro_quantity as well, later on if not neede
4130:
4131: lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_transact_res_txn';
4132:
4133:
4134: -- Constants Used for Inserting into wip_job_schedule_interface,
4135: -- and details interface tables
4136:
4137: lc_non_std_update_load_type CONSTANT NUMBER := 3;
4138: lc_load_res_type CONSTANT NUMBER := 1;
4147: -- Records to hold the Job header,details
4148: -- and wip_cost_txn_interface data
4149:
4150: l_wip_cost_txn_interface_rec wip_cost_txn_interface%ROWTYPE;
4151: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
4152: l_job_details_rec wip_job_dtls_interface%ROWTYPE;
4153:
4154:
4155: l_last_update_date DATE;
4263:
4264:
4265: -- Get the Group_id to be used for WIP Mass Load,
4266:
4267: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
4268:
4269: -- l_job_header_rec.group_id := l_job_header_rec.header_id;
4270:
4271: l_job_details_rec.group_id := l_job_header_rec.group_id;
4362: -- l_job_details_rec.quantity_per_assembly := l_required_quantity / p_ro_quantity;
4363:
4364: -- Get the Group_id to be used for WIP Mass Load,
4365:
4366: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.header_id FROM dual;
4367:
4368: -- l_job_header_rec.group_id := l_job_header_rec.header_id;
4369:
4370: -- l_job_details_rec.group_id := l_job_header_rec.group_id;
4648:
4649: -- constants used for FND_LOG debug messages
4650: lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_save_mtl_txn_dtls';
4651:
4652: -- Constants Used for Inserting into wip_job_schedule_interface,
4653: -- These are the values needed for WIP Mass Load to pick up the records
4654:
4655: -- Non Standard Update Discrete Job Load Type
4656: lc_non_std_update_load_type CONSTANT NUMBER := 3;
4666: -- lc_push_wip_supply_type CONSTANT NUMBER := 1;
4667:
4668:
4669: -- Job Records to hold the Job header and details information
4670: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
4671: l_job_details_rec wip_job_dtls_interface%ROWTYPE;
4672:
4673:
4674: l_creation_date DATE;
4880: end if;
4881:
4882:
4883: -- Get the Group_id to be used for WIP Mass Load,
4884: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
4885:
4886:
4887: -- get job_quantity
4888: open get_job_quantity ( p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id);
5087:
5088: l_job_details_rec.substitution_type := lc_substitution_change_type;
5089:
5090: -- Get the Group_id to be used for WIP Mass Load,
5091: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
5092:
5093: l_job_header_rec.header_id := l_job_header_rec.group_id;
5094: l_job_header_rec.wip_entity_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
5095: l_job_header_rec.organization_id := p_mtl_txn_dtls_tbl(mtl_ctr).organization_id;
5450:
5451: lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
5452:
5453:
5454: -- Constants Used for Inserting into wip_job_schedule_interface,
5455: -- These are the values needed for WIP Mass Load to pick up the records
5456:
5457: -- Non Standard Update Discrete Job Load Type
5458: lc_non_std_update_load_type CONSTANT NUMBER := 3;
5469:
5470:
5471: -- Job Records to hold the Job header and details information
5472:
5473: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
5474: l_job_details_rec wip_job_dtls_interface%ROWTYPE;
5475:
5476:
5477: l_creation_date DATE;
5571: If p_res_txn_dtls_tbl(res_ctr).new_row = 'Y' then
5572:
5573: -- Get the Group_id to be used for WIP Mass Load,
5574:
5575: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
5576:
5577: -- get job_quantity
5578: open get_job_quantity ( p_res_txn_dtls_tbl(res_ctr).wip_entity_id);
5579: fetch get_job_quantity into l_job_quantity;
5909:
5910: -- constants used for FND_LOG debug messages
5911: lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.process_save_op_dtls.';
5912:
5913: -- Constants Used for Inserting into wip_job_schedule_interface,
5914: -- These are the values needed for WIP Mass Load to pick up the records
5915: lc_non_std_update_load_type CONSTANT NUMBER := 3; -- load type for update non standard discrete job
5916:
5917: -- Constants Used for Inserting into wip_job_dtls_interface
5921: lc_process_validation_phase CONSTANT NUMBER := 2; -- must be 2 for WIP to pick up record
5922: lc_process_pending_status CONSTANT NUMBER := 1; -- must be 1 for WIP to pick up record
5923:
5924: -- Job Records to hold the Job header and details information
5925: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
5926: l_job_details_rec wip_job_dtls_interface%ROWTYPE;
5927:
5928: -- variables for WHO columns
5929: l_creation_date DATE;
5984:
5985: IF p_op_dtls_tbl(op_ctr).operation_seq_num is not null THEN
5986: -- Get the Group_id to be used for WIP Mass Load,
5987:
5988: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
5989:
5990: -- set job header info
5991: l_job_header_rec.header_id := l_job_header_rec.group_id;
5992: l_job_header_rec.wip_entity_id := p_op_dtls_tbl(op_ctr).wip_entity_id;
6304: )
6305: IS
6306:
6307: -- Job Record to hold the Job header, bills and routing information being inserted
6308: -- into wip_job_schedule_interface
6309:
6310: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
6311:
6312: lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_WIP_JOB';
6306:
6307: -- Job Record to hold the Job header, bills and routing information being inserted
6308: -- into wip_job_schedule_interface
6309:
6310: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
6311:
6312: lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_WIP_JOB';
6313: lc_api_version_number CONSTANT NUMBER := 1.0;
6314:
6525:
6526:
6527: -- Get the Group_id to be used for WIP Create Job,
6528:
6529: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
6530:
6531: -- nnadig bug 9263438
6532: -- interface id should use sequence number from wip_interface_s
6533: -- wip_job_schedule_interface_s is for wjsi.group_id, and wip_interface_s is for wjsi.interface_id.
6529: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
6530:
6531: -- nnadig bug 9263438
6532: -- interface id should use sequence number from wip_interface_s
6533: -- wip_job_schedule_interface_s is for wjsi.group_id, and wip_interface_s is for wjsi.interface_id.
6534: SELECT wip_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
6535: --l_job_header_rec.interface_id := l_job_header_rec.group_id;
6536:
6537: generate_job_name ( p_job_prefix =>l_job_prefix,
6549: END IF;
6550:
6551: -- Call procedure to insert job header and bills, routing
6552: -- information
6553: -- into wip_job_schedule_interface table
6554:
6555:
6556: insert_job_header( p_job_header_rec =>
6557: l_job_header_rec,
7056: p_service_code_tbl IN service_code_tbl_type
7057: )
7058: IS
7059: -- Job Record to hold the Job header, bills and routing information being inserted
7060: -- into wip_job_schedule_interface
7061: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
7062:
7063: lc_api_name CONSTANT VARCHAR2(30) := 'GENERATE_WIP_JOBS_FROM_SCS';
7064: lc_api_version_number CONSTANT NUMBER := 1.0;
7057: )
7058: IS
7059: -- Job Record to hold the Job header, bills and routing information being inserted
7060: -- into wip_job_schedule_interface
7061: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
7062:
7063: lc_api_name CONSTANT VARCHAR2(30) := 'GENERATE_WIP_JOBS_FROM_SCS';
7064: lc_api_version_number CONSTANT NUMBER := 1.0;
7065:
7329: -- table. This is passed to the insert_job_header
7330: -- procedure
7331:
7332: -- Get the Group_id to be used for WIP Create Job,
7333: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
7334:
7335: -- nnadig: bug 9263438
7336: -- interface id should use sequence number from wip_interface_s
7337: -- wip_job_schedule_interface_s is for wjsi.group_id, wip_interface_s is for wjsi.interface_id.
7333: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
7334:
7335: -- nnadig: bug 9263438
7336: -- interface id should use sequence number from wip_interface_s
7337: -- wip_job_schedule_interface_s is for wjsi.group_id, wip_interface_s is for wjsi.interface_id.
7338: SELECT wip_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
7339:
7340:
7341: --l_job_header_rec.interface_id := l_job_header_rec.group_id;
7358: 'Just before calling insert_job_header');
7359: END IF;
7360:
7361: -- Call procedure to insert job header and bills, routing
7362: -- information into wip_job_schedule_interface table
7363:
7364: insert_job_header( p_job_header_rec => l_job_header_rec,
7365: x_return_status => x_return_status );
7366:
9162:
9163: -- constants used for FND_LOG debug messages
9164: lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_delete_mtl_txn_dtl';
9165:
9166: -- Constants Used for Inserting into wip_job_schedule_interface,
9167: -- These are the values needed for WIP Mass Load to pick up the records
9168:
9169: -- Constants for WIP_JOB_SCHEDULE_INTERFACE table
9170: lc_non_std_update_load_type CONSTANT NUMBER := 3; -- update non-standard job
9165:
9166: -- Constants Used for Inserting into wip_job_schedule_interface,
9167: -- These are the values needed for WIP Mass Load to pick up the records
9168:
9169: -- Constants for WIP_JOB_SCHEDULE_INTERFACE table
9170: lc_non_std_update_load_type CONSTANT NUMBER := 3; -- update non-standard job
9171:
9172: -- Constants for WIP_JOB_DTLS_INTERFACE table
9173: lc_load_mtl_type CONSTANT NUMBER := 2; -- material
9174: lc_substitution_del_type CONSTANT NUMBER := 1; -- delete
9175:
9176:
9177: -- Job Records to hold the Job header and details information
9178: l_job_header_rec wip_job_schedule_interface%ROWTYPE;
9179: l_job_details_rec wip_job_dtls_interface%ROWTYPE;
9180:
9181: -- wip
9182: l_wip_transaction_detail_id NUMBER := NULL;
9219:
9220: --
9221: -- populate l_job_header_rec values
9222: --
9223: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
9224: l_job_header_rec.load_type := lc_non_std_update_load_type;
9225: l_job_header_rec.header_id := l_job_header_rec.group_id;
9226: l_job_header_rec.wip_entity_id := p_mtl_txn_dtls.wip_entity_id;
9227: l_job_header_rec.organization_id := p_mtl_txn_dtls.organization_id;