APPS.MSC_ATP_CTO dependencies on MSC_CTO_SOURCES

Line 364: update msc_cto_sources

360: l_dblink := '@' || p_dblink;
361:
362: /*s_cto_rearch: 24x7 --now CTO sources is session specific. We donot need to maintain sattsu flag
363: IF p_dblink is null then
364: update msc_cto_sources
365: set status_flag = 2
366: where ato_line_id in (select order_line_id
367: from mrp_atp_schedule_temp
368: where session_id = p_session_id

Line 377: delete msc_cto_sources

373: Else
374: IF PG_DEBUG in ('Y', 'C') THEN
375: msc_sch_wb.atp_debug('Delete CTO Sources locally');
376: END IF;
377: delete msc_cto_sources
378: where line_id in (select order_line_id
379: from mrp_atp_schedule_temp
380: where session_id = p_session_id
381: and order_line_id = ato_model_line_id);

Line 387: l_sql_stmt := 'Update msc_cto_sources' || l_dblink;

383: IF PG_DEBUG in ('Y', 'C') THEN
384: msc_sch_wb.atp_debug('Number of rows deleted := ' || SQL%ROWCOUNT);
385: msc_sch_wb.atp_debug('Update CTO Sources across DB');
386: END IF;
387: l_sql_stmt := 'Update msc_cto_sources' || l_dblink;
388: IF PG_DEBUG in ('Y', 'C') THEN
389: msc_sch_wb.atp_debug('sql stmt := ' || l_sql_stmt);
390: END IF;
391: l_sql_stmt := l_sql_stmt || ' set status_flag = 2 '

Line 413: --now insert the latest data into msc_cto_sources

409: msc_sch_wb.atp_debug('Now Insert New data');
410: END IF;
411:
412: IF p_cto_sources.line_id.count > 0 THEN
413: --now insert the latest data into msc_cto_sources
414: IF PG_DEBUG in ('Y', 'C') THEN
415: msc_sch_wb.atp_debug('Now Insert New data in Local Tbale');
416: END IF;
417:

Line 419: insert into msc_cto_sources

415: msc_sch_wb.atp_debug('Now Insert New data in Local Tbale');
416: END IF;
417:
418: FORALL i in 1..p_cto_sources.line_id.count --LOOP
419: insert into msc_cto_sources
420: (line_id,
421: inventory_item_id,
422: organization_id,
423: supplier_id,

Line 463: l_sql_stmt := 'Insert into msc_cto_sources' || l_dblink;

459: IF PG_DEBUG in ('Y', 'C') THEN
460: msc_sch_wb.atp_debug('Transfer Data Across DBLink');
461: END IF;
462: --now transfer the data across dblink
463: l_sql_stmt := 'Insert into msc_cto_sources' || l_dblink;
464: l_sql_stmt := l_sql_stmt || ' ( LINE_ID, ORGANIZATION_ID, SUPPLIER_ID,
465: SUPPLIER_SITE_CODE, STATUS_FLAG, INVENTORY_ITEM_ID,
466: SR_INSTANCE_ID, ATO_LINE_ID, CREATION_DATE,
467: CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE,

Line 473: MAKE_FLAG, refresh_number, session_id from msc_cto_sources

469: Select LINE_ID, ORGANIZATION_ID, SUPPLIER_ID,
470: SUPPLIER_SITE_CODE, STATUS_FLAG, INVENTORY_ITEM_ID,
471: SR_INSTANCE_ID, ATO_LINE_ID, CREATION_DATE,
472: CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE,
473: MAKE_FLAG, refresh_number, session_id from msc_cto_sources
474: where session_id = :p_session_id
475: and line_id in (select order_line_id
476: from mrp_atp_schedule_temp
477: where session_id = :p_session_id

Line 723: from msc_cto_sources mcs

719: */
720: to_number(null) tp_id,
721: to_number(null) partner_site_id,
722: make_flag
723: from msc_cto_sources mcs
724: where mcs.line_id = p_line_ids(1)
725: and mcs.sr_instance_id = p_instance_id
726: --and mcs.status_flag = 1
727: and mcs.session_id = p_session_id

Line 737: from msc_cto_sources mcs,

733: select to_number(null) organization_id,
734: mtil.tp_id,
735: mtps.partner_site_id,
736: make_flag
737: from msc_cto_sources mcs,
738: msc_tp_id_lid mtil,
739: msc_trading_partner_sites mtps
740: where mcs.line_id = p_line_ids(1)
741: and mcs.sr_instance_id = p_instance_id

Line 762: from msc_cto_sources mcs,

758: l_cto_source_list.organization_id,
759: l_cto_source_list.supplier_id,
760: l_cto_source_list.supplier_site_id,
761: l_cto_source_list.make_flag
762: from msc_cto_sources mcs,
763: msc_tp_id_lid mtil,
764: msc_trading_partner_sites mtps
765: where mcs.line_id = p_line_ids(1)
766: and mcs.sr_instance_id = p_instance_id

Line 793: from msc_cto_sources mcs,

789: BEGIN
790: ---first find out how many items have OSS specific rules
791: select count(distinct mcs.line_id)
792: into l_item_count
793: from msc_cto_sources mcs,
794: msc_ship_set_temp msst
795: where mcs.line_id = msst.line_id
796: and mcs.sr_instance_id = p_instance_id
797: --and mcs.status_flag = 1;

Line 821: from msc_cto_sources mcs,

817: bulk collect into
818: l_line_id,
819: l_org_id,
820: l_sup_id
821: from msc_cto_sources mcs,
822: msc_ship_set_temp msst
823: where mcs.line_id = msst.line_id
824: and mcs.sr_instance_id = p_instance_id
825: --and mcs.status_flag = 1;

Line 845: from msc_cto_sources mcs,

841: l_cto_source_list.organization_id,
842: l_cto_source_list.supplier_id,
843: l_cto_source_list.supplier_site_id,
844: l_cto_source_list.make_flag
845: from msc_cto_sources mcs,
846: msc_ship_set_temp msst
847: where mcs.line_id = msst.line_id
848: and mcs.sr_instance_id = p_instance_id
849: --and mcs.status_flag = 1

Line 1258: Delete from msc_cto_sources

1254:
1255: IF p_status = MSC_ATP_CTO.Success THEN
1256: -- delete the old data
1257: FORALL i in 1..p_atp_rec.inventory_item_id.count
1258: Delete from msc_cto_sources
1259: where sr_instance_id = p_instance_id
1260: and ato_line_id = p_atp_rec.identifier(i)
1261: and status_flag = 2;
1262:

Line 1266: Delete from msc_cto_sources

1262:
1263: ELSIF p_status = MSC_ATP_CTO.FAIL THEN
1264: --first delete the new data
1265: FORALL i in 1..p_atp_rec.inventory_item_id.count
1266: Delete from msc_cto_sources
1267: where sr_instance_id = p_instance_id
1268: and ato_line_id = p_atp_rec.identifier(i)
1269: and status_flag = 1;
1270:

Line 1273: UPDATE msc_cto_sources

1269: and status_flag = 1;
1270:
1271: --update the status flag to 1 on the old data
1272: FORALL i in 1..p_atp_rec.inventory_item_id.count
1273: UPDATE msc_cto_sources
1274: set status_flag = 1
1275: where sr_instance_id = p_instance_id
1276: and ato_line_id = p_atp_rec.identifier(i)
1277: and status_flag = 2;