APPS.MSC_ATP_CTO dependencies on MSC_CTO_SOURCES

Line 366: update msc_cto_sources

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

Line 379: delete msc_cto_sources

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

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

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

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

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

Line 421: insert into msc_cto_sources

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

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

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

Line 475: MAKE_FLAG, refresh_number, session_id from msc_cto_sources

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

Line 726: from msc_cto_sources mcs

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

Line 740: from msc_cto_sources mcs,

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

Line 765: from msc_cto_sources mcs,

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

Line 796: from msc_cto_sources mcs,

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

Line 824: from msc_cto_sources mcs,

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

Line 848: from msc_cto_sources mcs,

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

Line 1261: Delete from msc_cto_sources

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

Line 1269: Delete from msc_cto_sources

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

Line 1276: UPDATE msc_cto_sources

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