APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_DEMANDS

Line 641: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code

637: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
638: application_short_name => 'MSC',
639: statement_type => AD_DDL.CREATE_INDEX,
640: statement =>
641: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code
642: ||' on MSC_ST_DEMANDS '
643: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
644: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
645: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);

Line 642: ||' on MSC_ST_DEMANDS '

638: application_short_name => 'MSC',
639: statement_type => AD_DDL.CREATE_INDEX,
640: statement =>
641: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code
642: ||' on MSC_ST_DEMANDS '
643: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
644: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
645: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);
646: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N1_'||v_instance_code);

Line 645: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);

641: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code
642: ||' on MSC_ST_DEMANDS '
643: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
644: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
645: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);
646: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N1_'||v_instance_code);
647:
648:
649: EXCEPTION

Line 646: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N1_'||v_instance_code);

642: ||' on MSC_ST_DEMANDS '
643: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
644: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
645: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);
646: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N1_'||v_instance_code);
647:
648:
649: EXCEPTION
650: WHEN OTHERS THEN

Line 651: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

647:
648:
649: EXCEPTION
650: WHEN OTHERS THEN
651: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
652: END;
653:
654: BEGIN
655: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 659: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code

655: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
656: application_short_name => 'MSC',
657: statement_type => AD_DDL.CREATE_INDEX,
658: statement =>
659: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code
660: ||' on MSC_ST_DEMANDS '
661: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
662: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
663: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);

Line 660: ||' on MSC_ST_DEMANDS '

656: application_short_name => 'MSC',
657: statement_type => AD_DDL.CREATE_INDEX,
658: statement =>
659: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code
660: ||' on MSC_ST_DEMANDS '
661: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
662: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
663: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);
664:

Line 663: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);

659: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code
660: ||' on MSC_ST_DEMANDS '
661: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
662: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
663: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);
664:
665: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N2_'||v_instance_code);
666:
667: EXCEPTION

Line 665: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N2_'||v_instance_code);

661: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
662: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
663: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);
664:
665: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N2_'||v_instance_code);
666:
667: EXCEPTION
668: WHEN OTHERS THEN
669: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

Line 669: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

665: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N2_'||v_instance_code);
666:
667: EXCEPTION
668: WHEN OTHERS THEN
669: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
670: END;
671:
672: BEGIN
673: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 690: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);

686: WHEN OTHERS THEN
687: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SO_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
688: END;
689:
690: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);
691: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SALES_ORDERS', v_instance_id, -1);
692:
693: END IF;
694:

Line 1727: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code

1723: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1724: application_short_name => 'MSC',
1725: statement_type => AD_DDL.CREATE_INDEX,
1726: statement =>
1727: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code
1728: ||' on MSC_ST_DEMANDS '
1729: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1730: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1731: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);

Line 1728: ||' on MSC_ST_DEMANDS '

1724: application_short_name => 'MSC',
1725: statement_type => AD_DDL.CREATE_INDEX,
1726: statement =>
1727: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code
1728: ||' on MSC_ST_DEMANDS '
1729: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1730: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1731: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1732:

Line 1731: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);

1727: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code
1728: ||' on MSC_ST_DEMANDS '
1729: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1730: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1731: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1732:
1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1734:
1735: EXCEPTION

Line 1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N3_'||v_instance_code);

1729: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1730: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1731: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1732:
1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1734:
1735: EXCEPTION
1736: WHEN OTHERS THEN
1737: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

Line 1737: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1734:
1735: EXCEPTION
1736: WHEN OTHERS THEN
1737: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1738: END;
1739:
1740: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);
1741:

Line 1740: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);

1736: WHEN OTHERS THEN
1737: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1738: END;
1739:
1740: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);
1741:
1742: ELSIF p_batch_index = 'SO' THEN
1743:
1744: BEGIN

Line 1914: 'drop index MSC_ST_DEMANDS_N1_'||v_instance_code,

1910: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1911: application_short_name => 'MSC',
1912: statement_type => AD_DDL.DROP_INDEX,
1913: statement =>
1914: 'drop index MSC_ST_DEMANDS_N1_'||v_instance_code,
1915: object_name => 'MSC_ST_DEMANDS_N1_'||v_instance_code);
1916:
1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1918: EXCEPTION

Line 1915: object_name => 'MSC_ST_DEMANDS_N1_'||v_instance_code);

1911: application_short_name => 'MSC',
1912: statement_type => AD_DDL.DROP_INDEX,
1913: statement =>
1914: 'drop index MSC_ST_DEMANDS_N1_'||v_instance_code,
1915: object_name => 'MSC_ST_DEMANDS_N1_'||v_instance_code);
1916:
1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1918: EXCEPTION
1919: WHEN OTHERS THEN

Line 1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);

1913: statement =>
1914: 'drop index MSC_ST_DEMANDS_N1_'||v_instance_code,
1915: object_name => 'MSC_ST_DEMANDS_N1_'||v_instance_code);
1916:
1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1918: EXCEPTION
1919: WHEN OTHERS THEN
1920: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1921: END;

Line 1920: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1916:
1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1918: EXCEPTION
1919: WHEN OTHERS THEN
1920: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1921: END;
1922:
1923: BEGIN
1924: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 1928: 'drop index MSC_ST_DEMANDS_N2_'||v_instance_code,

1924: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1925: application_short_name => 'MSC',
1926: statement_type => AD_DDL.DROP_INDEX,
1927: statement =>
1928: 'drop index MSC_ST_DEMANDS_N2_'||v_instance_code,
1929: object_name => 'MSC_ST_DEMANDS_N2_'||v_instance_code);
1930:
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1932: EXCEPTION

Line 1929: object_name => 'MSC_ST_DEMANDS_N2_'||v_instance_code);

1925: application_short_name => 'MSC',
1926: statement_type => AD_DDL.DROP_INDEX,
1927: statement =>
1928: 'drop index MSC_ST_DEMANDS_N2_'||v_instance_code,
1929: object_name => 'MSC_ST_DEMANDS_N2_'||v_instance_code);
1930:
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1932: EXCEPTION
1933: WHEN OTHERS THEN

Line 1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);

1927: statement =>
1928: 'drop index MSC_ST_DEMANDS_N2_'||v_instance_code,
1929: object_name => 'MSC_ST_DEMANDS_N2_'||v_instance_code);
1930:
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1932: EXCEPTION
1933: WHEN OTHERS THEN
1934: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1935: END;

Line 1934: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1930:
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1932: EXCEPTION
1933: WHEN OTHERS THEN
1934: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1935: END;
1936:
1937: BEGIN
1938: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 1942: 'drop index MSC_ST_DEMANDS_N3_'||v_instance_code,

1938: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1939: application_short_name => 'MSC',
1940: statement_type => AD_DDL.DROP_INDEX,
1941: statement =>
1942: 'drop index MSC_ST_DEMANDS_N3_'||v_instance_code,
1943: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1944:
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1946:

Line 1943: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);

1939: application_short_name => 'MSC',
1940: statement_type => AD_DDL.DROP_INDEX,
1941: statement =>
1942: 'drop index MSC_ST_DEMANDS_N3_'||v_instance_code,
1943: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1944:
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1946:
1947: EXCEPTION

Line 1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);

1941: statement =>
1942: 'drop index MSC_ST_DEMANDS_N3_'||v_instance_code,
1943: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1944:
1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1946:
1947: EXCEPTION
1948: WHEN OTHERS THEN
1949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

Line 1949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1946:
1947: EXCEPTION
1948: WHEN OTHERS THEN
1949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1950: END;
1951:
1952: BEGIN
1953: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 33836: | records from the msc_st_demands table. The following |

33832: END LOAD_RESERVATIONS ;
33833:
33834: /*==========================================================================+
33835: | DESCRIPTION : This procedure is called for validating demand related |
33836: | records from the msc_st_demands table. The following |
33837: | types are considered for the validation. |
33838: | Demand origination Origination Type |
33839: | ------------------------------------------------- |
33840: | MDS Demand 8 |

Line 33860: FROM msc_st_demands

33856: ex_logging_err EXCEPTION;
33857:
33858: CURSOR c1(p_batch_id NUMBER) IS
33859: SELECT rowid
33860: FROM msc_st_demands
33861: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
33862: AND origination_type IN (7,8,29)
33863: AND batch_id = p_batch_id
33864: AND sr_instance_code = v_instance_code ;

Line 33868: FROM msc_st_demands

33864: AND sr_instance_code = v_instance_code ;
33865:
33866: CURSOR c2(p_batch_id NUMBER) IS
33867: SELECT rowid
33868: FROM msc_st_demands
33869: WHERE process_flag = G_IN_PROCESS
33870: AND sr_instance_code = v_instance_code
33871: AND batch_id = p_batch_id
33872: AND NVL(disposition_id,NULL_VALUE) = NULL_VALUE

Line 33878: FROM msc_st_demands

33874: AND deleted_flag = SYS_NO;
33875:
33876: CURSOR c3(p_batch_id NUMBER) IS
33877: SELECT rowid
33878: FROM msc_st_demands
33879: WHERE process_flag = G_IN_PROCESS
33880: AND sr_instance_code = v_instance_code
33881: AND batch_id = p_batch_id
33882: AND NVL(sales_order_line_id,NULL_VALUE) = NULL_VALUE

Line 33927: UPDATE msc_st_demands

33923: CLOSE c1;
33924:
33925: v_sql_stmt := 03;
33926: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
33927: UPDATE msc_st_demands
33928: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
33929: refresh_id = v_refresh_id,
33930: last_update_date = v_current_date,
33931: last_updated_by = v_current_user,

Line 33928: SET st_transaction_id = msc_st_demands_s.NEXTVAL,

33924:
33925: v_sql_stmt := 03;
33926: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
33927: UPDATE msc_st_demands
33928: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
33929: refresh_id = v_refresh_id,
33930: last_update_date = v_current_date,
33931: last_updated_by = v_current_user,
33932: creation_date = v_current_date,

Line 33958: (p_table_name => 'MSC_ST_DEMANDS',

33954: lv_where_str :=
33955: ' AND NVL(deleted_flag,'||NULL_VALUE||') NOT IN(1,2)';
33956:
33957: lv_return:= MSC_ST_UTIL.LOG_ERROR
33958: (p_table_name => 'MSC_ST_DEMANDS',
33959: p_instance_code => v_instance_code,
33960: p_row => lv_column_names,
33961: p_severity => G_SEV_WARNING,
33962: p_message_text => lv_message_text,

Line 33994: 'UPDATE msc_st_demands '

33990: -- Error out records where USING_ASSEMBLY_DEMAND_DATE is NULL
33991:
33992: v_sql_stmt := 04;
33993: lv_sql_stmt :=
33994: 'UPDATE msc_st_demands '
33995: ||' SET process_flag = '||G_ERROR_FLG||','
33996: ||' error_text = '||''''||lv_message_text||''''
33997: ||' WHERE NVL(using_assembly_demand_date,SYSDATE-36500) = SYSDATE-36500'
33998: ||' AND process_flag = '||G_IN_PROCESS

Line 34028: 'UPDATE msc_st_demands '

34024: -- and deleted_flag = SYS_NO
34025:
34026: v_sql_stmt := 05;
34027: lv_sql_stmt :=
34028: 'UPDATE msc_st_demands '
34029: ||' SET process_flag = '||G_ERROR_FLG||','
34030: ||' error_text = '||''''||lv_message_text||''''
34031: ||' WHERE NVL(using_requirement_quantity,'||NULL_VALUE||') ='||NULL_VALUE
34032: ||' AND process_flag = '||G_IN_PROCESS

Line 34058: (p_table_name => 'MSC_ST_DEMANDS',

34054: END IF;
34055:
34056: --Derive Organization_id
34057: lv_return:= MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
34058: (p_table_name => 'MSC_ST_DEMANDS',
34059: p_org_partner_name => 'ORGANIZATION_CODE',
34060: p_org_partner_id => 'ORGANIZATION_ID',
34061: p_instance_code => v_instance_code,
34062: p_partner_type => G_ORGANIZATION,

Line 34091: (p_table_name => 'MSC_ST_DEMANDS',

34087:
34088:
34089: --Derive Inventory_item_id
34090: lv_return:= MSC_ST_UTIL.DERIVE_ITEM_ID
34091: (p_table_name => 'MSC_ST_DEMANDS',
34092: p_item_col_name => 'ITEM_NAME',
34093: p_item_col_id => 'INVENTORY_ITEM_ID',
34094: p_instance_id => v_instance_id,
34095: p_instance_code => v_instance_code,

Line 34122: (p_table_name => 'MSC_ST_DEMANDS',

34118: END IF;
34119:
34120: --Derive Inventory_item_id
34121: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
34122: (p_table_name => 'MSC_ST_DEMANDS',
34123: p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
34124: p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
34125: p_instance_id => v_instance_id,
34126: p_instance_code => v_instance_code,

Line 34155: MSC_ST_UTIL.VALIDATE_DMD_CLASS(p_table_name => 'MSC_ST_DEMANDS',

34151: -- Validate Demand Class, if value provided it should exists
34152: -- in ODS or staging table
34153:
34154: lv_return :=
34155: MSC_ST_UTIL.VALIDATE_DMD_CLASS(p_table_name => 'MSC_ST_DEMANDS',
34156: p_dmd_class_column => 'DEMAND_CLASS',
34157: p_instance_id => v_instance_id,
34158: p_instance_code => v_instance_code,
34159: p_severity => G_SEV3_ERROR,

Line 34175: ' UPDATE msc_st_demands'

34171: -- Update using_assembly_item_id = inventory_item_id
34172:
34173: v_sql_stmt := 06;
34174: lv_sql_stmt :=
34175: ' UPDATE msc_st_demands'
34176: ||' SET using_assembly_item_id = inventory_item_id'
34177: ||' WHERE process_flag = '||G_IN_PROCESS
34178: ||' AND NVL(using_assembly_item_id,'||NULL_VALUE||') = '||NULL_VALUE
34179: ||' AND deleted_flag = '||SYS_NO

Line 34197: ' UPDATE msc_st_demands'

34193:
34194:
34195: v_sql_stmt := 07;
34196: lv_sql_stmt :=
34197: ' UPDATE msc_st_demands'
34198: ||' SET schedule_date = using_assembly_demand_date '
34199: ||' WHERE process_flag = '||G_IN_PROCESS
34200: ||' AND deleted_flag = '||SYS_NO
34201: ||' AND origination_type IN (7,8,29)'

Line 34214: 'UPDATE msc_st_demands msd '

34210: -- Derive disposition_id for MDS
34211:
34212: v_sql_stmt := 08;
34213: lv_sql_stmt :=
34214: 'UPDATE msc_st_demands msd '
34215: ||' SET disposition_id = (SELECT local_id'
34216: ||' FROM msc_local_id_demand mlid'
34217: ||' WHERE mlid.char1 = msd.sr_instance_code'
34218: ||' AND NVL(mlid.char2,'||''''||NULL_CHAR||''''||') '

Line 34254: 'UPDATE msc_st_demands '

34250:
34251: v_sql_stmt := 09;
34252:
34253: lv_sql_stmt :=
34254: 'UPDATE msc_st_demands '
34255: ||' SET process_flag ='||G_ERROR_FLG||','
34256: ||' error_text = '||''''||lv_message_text||''''
34257: ||' WHERE NVL(disposition_id,'||NULL_VALUE||') = '||NULL_VALUE
34258: ||' AND deleted_flag ='||SYS_YES

Line 34273: 'UPDATE msc_st_demands msd '

34269: -- Derive sales_order_line_id for forecast
34270:
34271: v_sql_stmt := 10;
34272: lv_sql_stmt :=
34273: 'UPDATE msc_st_demands msd '
34274: ||' SET sales_order_line_id = (SELECT local_id'
34275: ||' FROM msc_local_id_demand mlid'
34276: ||' WHERE mlid.char1 = msd.sr_instance_code'
34277: ||' AND NVL(mlid.char2,'||''''||NULL_CHAR||''''||') '

Line 34314: 'UPDATE msc_st_demands '

34310:
34311: v_sql_stmt := 11;
34312:
34313: lv_sql_stmt :=
34314: 'UPDATE msc_st_demands '
34315: ||' SET process_flag ='||G_ERROR_FLG||','
34316: ||' error_text = '||''''||lv_message_text||''''
34317: ||' WHERE NVL(sales_order_line_id,'||NULL_VALUE||') = '||NULL_VALUE
34318: ||' AND deleted_flag ='||SYS_YES

Line 34345: (p_table_name => 'MSC_ST_DEMANDS',

34341: END IF;
34342:
34343: --Derive Project Id.
34344: lv_return:= MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
34345: (p_table_name => 'MSC_ST_DEMANDS',
34346: p_proj_col_name => 'PROJECT_NUMBER',
34347: p_proj_task_col_id => 'PROJECT_ID',
34348: p_instance_code => v_instance_code,
34349: p_entity_name => 'PROJECT_ID',

Line 34379: (p_table_name => 'MSC_ST_DEMANDS',

34375: END IF;
34376:
34377: --Derive Task Id.
34378: lv_return:= MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
34379: (p_table_name => 'MSC_ST_DEMANDS',
34380: p_proj_col_name => 'PROJECT_NUMBER',
34381: p_proj_task_col_id => 'TASK_ID',
34382: p_instance_code => v_instance_code,
34383: p_entity_name => 'TASK_ID',

Line 34411: p_token_value3 => 'MSC_ST_DEMANDS' );

34407: ||' DEMAND_SCHEDULE_NAME',
34408: p_token2 => 'MASTER_TABLE',
34409: p_token_value2 => 'MSC_ST_DESIGNATORS',
34410: p_token3 => 'CHILD_TABLE' ,
34411: p_token_value3 => 'MSC_ST_DEMANDS' );
34412:
34413: IF lv_return <> 0 THEN
34414: RAISE ex_logging_err;
34415: END IF;

Line 34421: 'UPDATE msc_st_demands msdm'

34417: -- For MDS check whether designator exists
34418:
34419: v_sql_stmt := 12;
34420: lv_sql_stmt :=
34421: 'UPDATE msc_st_demands msdm'
34422: ||' SET process_flag = '||G_ERROR_FLG||','
34423: ||' error_text = '||''''||lv_message_text||''''
34424: ||' WHERE NOT EXISTS(SELECT 1'
34425: ||' FROM msc_st_designators msd'

Line 34479: 'UPDATE msc_st_demands '

34475:
34476: v_sql_stmt := 13;
34477:
34478: lv_sql_stmt :=
34479: 'UPDATE msc_st_demands '
34480: ||' SET process_flag = '||G_ERROR_FLG||','
34481: ||' error_text = '||''''||lv_message_text||''''
34482: ||' WHERE (demand_type = 2'
34483: ||' OR assembly_demand_comp_date IS NOT NULL '

Line 34509: p_token_value3 => 'MSC_ST_DEMANDS' );

34505: ||'ORGANIZATION_CODE,FORECAST_DESIGNATOR',
34506: p_token2 => 'MASTER_TABLE',
34507: p_token_value2 => 'MSC_ST_DESIGNATORS',
34508: p_token3 => 'CHILD_TABLE' ,
34509: p_token_value3 => 'MSC_ST_DEMANDS' );
34510:
34511: IF lv_return <> 0 THEN
34512: RAISE ex_logging_err;
34513: END IF;

Line 34519: 'UPDATE msc_st_demands msdm'

34515: -- For Forecast,check whether designator exists
34516:
34517: v_sql_stmt := 14;
34518: lv_sql_stmt :=
34519: 'UPDATE msc_st_demands msdm'
34520: ||' SET process_flag = '||G_ERROR_FLG||','
34521: ||' error_text = '||''''||lv_message_text||''''
34522: ||' WHERE NOT EXISTS(SELECT 1'
34523: ||' FROM msc_st_designators msd'

Line 34577: (p_table_name => 'MSC_ST_DEMANDS',

34573: ||' AND NVL(demand_type,'||NULL_VALUE||') <> 1'
34574: ||' AND origination_type = 29 AND deleted_flag ='||SYS_NO ;
34575:
34576: lv_return:= MSC_ST_UTIL.LOG_ERROR
34577: (p_table_name => 'MSC_ST_DEMANDS',
34578: p_instance_code => v_instance_code,
34579: p_row => lv_column_names,
34580: p_severity => G_SEV_WARNING,
34581: p_message_text => lv_message_text,

Line 34615: (p_table_name => 'MSC_ST_DEMANDS',

34611: ||' AND NVL(demand_type,'||NULL_VALUE||') <> 2'
34612: ||' AND origination_type = 29 AND deleted_flag ='||SYS_NO ;
34613:
34614: lv_return:= MSC_ST_UTIL.LOG_ERROR
34615: (p_table_name => 'MSC_ST_DEMANDS',
34616: p_instance_code => v_instance_code,
34617: p_row => lv_column_names,
34618: p_severity => G_SEV_WARNING,
34619: p_message_text => lv_message_text,

Line 34652: (p_table_name => 'MSC_ST_DEMANDS',

34648: lv_where_str := ' AND NVL(bucket_type,'||NULL_VALUE||') NOT IN (1,2,3)'
34649: ||' AND origination_type = 29 AND deleted_flag ='||SYS_NO ;
34650:
34651: lv_return:= MSC_ST_UTIL.LOG_ERROR
34652: (p_table_name => 'MSC_ST_DEMANDS',
34653: p_instance_code => v_instance_code,
34654: p_row => lv_column_names,
34655: p_severity => G_SEV_WARNING,
34656: p_message_text => lv_message_text,

Line 34685: (p_table_name => 'MSC_ST_DEMANDS',

34681:
34682: -- Derive customer_id
34683:
34684: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
34685: (p_table_name => 'MSC_ST_DEMANDS',
34686: p_org_partner_name => 'CUSTOMER_NAME',
34687: p_org_partner_id => 'CUSTOMER_ID',
34688: p_instance_code => v_instance_code,
34689: p_partner_type => G_CUSTOMER,

Line 34718: (p_table_name => 'MSC_ST_DEMANDS',

34714:
34715: -- Derive customer_site_id
34716:
34717: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
34718: (p_table_name => 'MSC_ST_DEMANDS',
34719: p_partner_name => 'CUSTOMER_NAME',
34720: p_partner_site_code => 'CUSTOMER_SITE_CODE',
34721: p_partner_site_id => 'CUSTOMER_SITE_ID',
34722: p_instance_code => v_instance_code,

Line 34751: (p_table_name => 'MSC_ST_DEMANDS',

34747:
34748:
34749: -- Derive bill_to_site_id
34750: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
34751: (p_table_name => 'MSC_ST_DEMANDS',
34752: p_partner_name => 'CUSTOMER_NAME',
34753: p_partner_site_code => 'BILL_CODE',
34754: p_partner_site_id => 'BILL_ID',
34755: p_instance_code => v_instance_code,

Line 34791: (p_table_name => 'MSC_ST_DEMANDS',

34787: ||' NOT IN (1,2) AND origination_type in (7, 8)'
34788: ||' AND NVL(order_date_type_code,'||NULL_VALUE||')<> '||NULL_VALUE;
34789:
34790: lv_return := MSC_ST_UTIL.LOG_ERROR
34791: (p_table_name => 'MSC_ST_DEMANDS',
34792: p_instance_code => v_instance_code,
34793: p_row => lv_column_names,
34794: p_severity => G_SEV_WARNING,
34795: p_message_text => lv_message_text,

Line 34813: pEntityName => 'MSC_ST_DEMANDS',

34809: (ERRBUF => lv_error_text,
34810: RETCODE => lv_return,
34811: pBatchID => p_batch_id,
34812: pInstanceCode => v_instance_code,
34813: pEntityName => 'MSC_ST_DEMANDS',
34814: pInstanceID => v_instance_id);
34815:
34816: IF NVL(lv_return,0) <> 0 THEN
34817: RAISE ex_logging_err;

Line 34828: UPDATE msc_st_demands

34824:
34825: IF c2%ROWCOUNT > 0 THEN
34826: v_sql_stmt := 15;
34827: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
34828: UPDATE msc_st_demands
34829: SET disposition_id = msc_st_demand_disposition_id_s.NEXTVAL
34830: WHERE rowid = lb_rowid(j);
34831:
34832: v_sql_stmt := 16;

Line 34888: FROM msc_st_demands

34884: v_current_date,
34885: v_current_user,
34886: v_current_date,
34887: v_current_user
34888: FROM msc_st_demands
34889: WHERE rowid = lb_rowid(j);
34890:
34891: END IF;
34892: CLOSE c2 ;

Line 34903: UPDATE msc_st_demands

34899:
34900: IF c3%ROWCOUNT > 0 THEN
34901: v_sql_stmt := 17;
34902: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
34903: UPDATE msc_st_demands
34904: SET sales_order_line_id = msc_st_demand_disposition_id_s.NEXTVAL
34905: WHERE rowid = lb_rowid(j);
34906:
34907: v_sql_stmt := 18;

Line 34963: FROM msc_st_demands

34959: v_current_date,
34960: v_current_user,
34961: v_current_date,
34962: v_current_user
34963: FROM msc_st_demands
34964: WHERE rowid = lb_rowid(j);
34965:
34966: END IF;
34967: CLOSE c3 ;

Line 34970: (p_table_name => 'MSC_ST_DEMANDS',

34966: END IF;
34967: CLOSE c3 ;
34968:
34969: lv_return:= MSC_ST_UTIL.SET_PROCESS_FLAG
34970: (p_table_name => 'MSC_ST_DEMANDS',
34971: p_instance_id => v_instance_id,
34972: p_instance_code => v_instance_code,
34973: p_process_flag => G_VALID,
34974: p_error_text => lv_error_text,

Line 34983: (p_table_name => 'MSC_ST_DEMANDS',

34979: RAISE ex_logging_err;
34980: END IF;
34981:
34982: lv_return:= MSC_ST_UTIL.LOG_ERROR
34983: (p_table_name => 'MSC_ST_DEMANDS',
34984: p_instance_code => v_instance_code,
34985: p_row => lv_column_names,
34986: p_severity => G_SEV_ERROR,
34987: p_message_text => NULL,

Line 44752: | from WIP Supply and is for msc_st_demands table. Following |

44748:
44749:
44750: /*==========================================================================+
44751: | DESCRIPTION : This procedure is called for validating demand originating |
44752: | from WIP Supply and is for msc_st_demands table. Following |
44753: | types are considered for the validation. |
44754: | Demand origination Origination Type |
44755: | ------------------------------------------------- |
44756: | Std Job Demand 2 |

Line 44775: lv_batch_id msc_st_demands.batch_id%TYPE;

44771: lv_error_text VARCHAR2(250);
44772: lv_where_str VARCHAR2(5000);
44773: lv_sql_stmt VARCHAR2(5000);
44774: lv_cursor_stmt VARCHAR2(5000);
44775: lv_batch_id msc_st_demands.batch_id%TYPE;
44776: lv_message_text msc_errors.error_text%TYPE;
44777:
44778: ex_logging_err EXCEPTION;
44779:

Line 44782: FROM msc_st_demands

44778: ex_logging_err EXCEPTION;
44779:
44780: CURSOR c1(p_batch_id NUMBER) IS
44781: SELECT rowid
44782: FROM msc_st_demands
44783: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
44784: AND origination_type IN (2,3,25)
44785: AND batch_id = p_batch_id
44786: AND sr_instance_code = v_instance_code;

Line 44790: FROM msc_st_demands

44786: AND sr_instance_code = v_instance_code;
44787:
44788: CURSOR c2(p_batch_id NUMBER) IS
44789: SELECT max(rowid)
44790: FROM msc_st_demands
44791: WHERE process_flag = G_IN_PROCESS
44792: AND sr_instance_code = v_instance_code
44793: AND batch_id = p_batch_id
44794: AND origination_type IN (2,3) -- Not for flow schedule

Line 44823: 'UPDATE msc_st_demands msd1'

44819:
44820: v_sql_stmt := 01;
44821:
44822: lv_sql_stmt :=
44823: 'UPDATE msc_st_demands msd1'
44824: ||' SET process_flag = '||G_ERROR_FLG||','
44825: ||' error_text = '||''''||lv_message_text||''''
44826: ||' WHERE message_id < (SELECT MAX(message_id)'
44827: ||' FROM msc_st_demands msd2'

Line 44827: ||' FROM msc_st_demands msd2'

44823: 'UPDATE msc_st_demands msd1'
44824: ||' SET process_flag = '||G_ERROR_FLG||','
44825: ||' error_text = '||''''||lv_message_text||''''
44826: ||' WHERE message_id < (SELECT MAX(message_id)'
44827: ||' FROM msc_st_demands msd2'
44828: ||' WHERE msd2.sr_instance_code '
44829: ||' = msd1.sr_instance_code '
44830: ||' AND msd2.organization_code '
44831: ||' = msd1.organization_code '

Line 44868: 'UPDATE msc_st_demands msd1'

44864:
44865: v_sql_stmt := 02;
44866:
44867: lv_sql_stmt :=
44868: 'UPDATE msc_st_demands msd1'
44869: ||' SET process_flag = '||G_ERROR_FLG||','
44870: ||' error_text = '||''''||lv_message_text||''''
44871: ||' WHERE EXISTS( SELECT 1 '
44872: ||' FROM msc_st_demands msd2'

Line 44872: ||' FROM msc_st_demands msd2'

44868: 'UPDATE msc_st_demands msd1'
44869: ||' SET process_flag = '||G_ERROR_FLG||','
44870: ||' error_text = '||''''||lv_message_text||''''
44871: ||' WHERE EXISTS( SELECT 1 '
44872: ||' FROM msc_st_demands msd2'
44873: ||' WHERE msd2.sr_instance_code '
44874: ||' = msd1.sr_instance_code '
44875: ||' AND msd2.organization_code '
44876: ||' = msd1.organization_code '

Line 44931: ' UPDATE msc_st_demands '

44927: FROM DUAL;
44928:
44929: v_sql_stmt := 04;
44930: lv_sql_stmt :=
44931: ' UPDATE msc_st_demands '
44932: ||' SET batch_id = :lv_batch_id'
44933: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
44934: ||' AND sr_instance_code = :v_instance_code'
44935: ||' AND origination_type IN (2,3,25)'

Line 44955: UPDATE msc_st_demands

44951: CLOSE c1;
44952:
44953: v_sql_stmt := 03;
44954: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
44955: UPDATE msc_st_demands
44956: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
44957: refresh_id = v_refresh_id,
44958: last_update_date = v_current_date,
44959: last_updated_by = v_current_user,

Line 44956: SET st_transaction_id = msc_st_demands_s.NEXTVAL,

44952:
44953: v_sql_stmt := 03;
44954: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
44955: UPDATE msc_st_demands
44956: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
44957: refresh_id = v_refresh_id,
44958: last_update_date = v_current_date,
44959: last_updated_by = v_current_user,
44960: creation_date = v_current_date,

Line 44985: (p_table_name => 'MSC_ST_DEMANDS',

44981: lv_where_str :=
44982: ' AND NVL(deleted_flag,'||NULL_VALUE||') NOT IN(1,2)';
44983:
44984: lv_return := MSC_ST_UTIL.LOG_ERROR
44985: (p_table_name => 'MSC_ST_DEMANDS',
44986: p_instance_code => v_instance_code,
44987: p_row => lv_column_names,
44988: p_severity => G_SEV_WARNING,
44989: p_message_text => lv_message_text,

Line 45016: (p_table_name => 'MSC_ST_DEMANDS',

45012: END IF;
45013:
45014: --Derive Organization_id
45015: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
45016: (p_table_name => 'MSC_ST_DEMANDS',
45017: p_org_partner_name => 'ORGANIZATION_CODE',
45018: p_org_partner_id => 'ORGANIZATION_ID',
45019: p_instance_code => v_instance_code,
45020: p_partner_type => G_ORGANIZATION,

Line 45047: (p_table_name => 'MSC_ST_DEMANDS',

45043: END IF;
45044:
45045: --Derive Inventory_item_id
45046: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
45047: (p_table_name => 'MSC_ST_DEMANDS',
45048: p_item_col_name => 'ITEM_NAME',
45049: p_item_col_id => 'INVENTORY_ITEM_ID',
45050: p_instance_id => v_instance_id,
45051: p_instance_code => v_instance_code,

Line 45078: (p_table_name => 'MSC_ST_DEMANDS',

45074: END IF;
45075:
45076: --Derive Using_assembly_item_id
45077: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
45078: (p_table_name => 'MSC_ST_DEMANDS',
45079: p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
45080: p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
45081: p_instance_id => v_instance_id,
45082: p_instance_code => v_instance_code,

Line 45098: 'UPDATE msc_st_demands msd'

45094: -- Derive WIP_ENTITY_ID
45095: v_sql_stmt := 04;
45096:
45097: lv_sql_stmt :=
45098: 'UPDATE msc_st_demands msd'
45099: ||' SET wip_entity_id = ( SELECT local_id '
45100: ||' FROM msc_local_id_supply mlid'
45101: ||' WHERE mlid.char1 = msd.sr_instance_code'
45102: ||' AND NVL(mlid.char2,'||''''||NULL_CHAR||''''||') '

Line 45129: p_token_value3 => 'MSC_ST_DEMANDS' );

45125: ||' ORGANIZATION_CODE,WIP_ENTITY_NAME',
45126: p_token2 => 'MASTER_TABLE',
45127: p_token_value2 => 'MSC_ST_SUPPLIES',
45128: p_token3 => 'CHILD_TABLE' ,
45129: p_token_value3 => 'MSC_ST_DEMANDS' );
45130:
45131: IF lv_return <> 0 THEN
45132: RAISE ex_logging_err;
45133: END IF;

Line 45139: 'UPDATE msc_st_demands '

45135: -- Error out records where WIP_ENTITY_ID is NULL;
45136:
45137: v_sql_stmt := 05;
45138: lv_sql_stmt :=
45139: 'UPDATE msc_st_demands '
45140: ||' SET process_flag = '||G_ERROR_FLG||','
45141: ||' error_text = '||''''||lv_message_text||''''
45142: ||' WHERE NVL(wip_entity_id,'||NULL_VALUE||') ='||NULL_VALUE
45143: ||' AND process_flag = '||G_IN_PROCESS

Line 45174: 'UPDATE msc_st_demands '

45170: -- Error out records where using_assembly_demand_date is NULL
45171:
45172: v_sql_stmt := 06;
45173: lv_sql_stmt :=
45174: 'UPDATE msc_st_demands '
45175: ||' SET process_flag = '||G_ERROR_FLG||','
45176: ||' error_text = '||''''||lv_message_text||''''
45177: ||' WHERE (NVL(using_requirement_quantity,'||NULL_VALUE||')='||NULL_VALUE
45178: ||' OR NVL(using_assembly_demand_date,SYSDATE-36500) = SYSDATE-36500 )'

Line 45195: ' UPDATE msc_st_demands'

45191: -- Update using_assembly_item_id = inventory_item_id
45192:
45193: v_sql_stmt := 07;
45194: lv_sql_stmt :=
45195: ' UPDATE msc_st_demands'
45196: ||' SET using_assembly_item_id = inventory_item_id'
45197: ||' WHERE process_flag = '||G_IN_PROCESS
45198: ||' AND NVL(using_assembly_item_id,'||NULL_VALUE||') = '||NULL_VALUE
45199: ||' AND deleted_flag = '||SYS_NO

Line 45215: ' UPDATE msc_st_demands'

45211: -- Update order_number = wip_entity_name
45212:
45213: v_sql_stmt := 08;
45214: lv_sql_stmt :=
45215: ' UPDATE msc_st_demands'
45216: ||' SET order_number = wip_entity_name'
45217: ||' WHERE process_flag = '||G_IN_PROCESS
45218: ||' AND deleted_flag = '||SYS_NO
45219: ||' AND process_flag = '||G_IN_PROCESS

Line 45234: ' UPDATE msc_st_demands'

45230: -- Update disposition_id = wip_entity_id
45231:
45232: v_sql_stmt := 09;
45233: lv_sql_stmt :=
45234: ' UPDATE msc_st_demands'
45235: ||' SET disposition_id = wip_entity_id'
45236: ||' WHERE process_flag = '||G_IN_PROCESS
45237: ||' AND NVL(wip_entity_id,'||NULL_VALUE||') = '||NULL_VALUE
45238: ||' AND deleted_flag = '||SYS_NO

Line 45256: ' UPDATE msc_st_demands'

45252: -- This is not reqd for flow schedule
45253:
45254: v_sql_stmt := 10;
45255: lv_sql_stmt :=
45256: ' UPDATE msc_st_demands'
45257: ||' SET mps_date_required = using_assembly_demand_date'
45258: ||' WHERE process_flag = '||G_IN_PROCESS
45259: ||' AND NVL(mps_date_required,SYSDATE-36500) = SYSDATE-36500'
45260: ||' AND process_flag = '||G_IN_PROCESS

Line 45294: (p_table_name => 'MSC_ST_DEMANDS',

45290: ||' = '||''''||NULL_CHAR||''''
45291: ||' AND origination_type IN (2,3)';
45292:
45293: lv_return := MSC_ST_UTIL.LOG_ERROR
45294: (p_table_name => 'MSC_ST_DEMANDS',
45295: p_instance_code => v_instance_code,
45296: p_row => lv_column_names,
45297: p_severity => G_SEV_WARNING,
45298: p_message_text => lv_message_text,

Line 45332: (p_table_name => 'MSC_ST_DEMANDS',

45328: ' AND NVL(operation_effectivity_date,SYSDATE-36500 ) = SYSDATE-36500 '
45329: ||' AND origination_type IN (2,3)' ;
45330:
45331: lv_return := MSC_ST_UTIL.LOG_ERROR
45332: (p_table_name => 'MSC_ST_DEMANDS',
45333: p_instance_code => v_instance_code,
45334: p_row => lv_column_names,
45335: p_severity => G_SEV_WARNING,
45336: p_message_text => lv_message_text,

Line 45352: ' UPDATE msc_st_demands'

45348: -- If routing_name is is NULL populate the item_name in routing name
45349:
45350: v_sql_stmt := 11;
45351: lv_sql_stmt :=
45352: ' UPDATE msc_st_demands'
45353: ||' SET routing_name = nvl(USING_ASSEMBLY_ITEM_NAME,item_name)' /* bug 3768813 */
45354: ||' WHERE process_flag = '||G_IN_PROCESS
45355: ||' AND NVL(routing_name,'||''''||NULL_CHAR||''''||')'
45356: ||' = '||''''||NULL_CHAR||''''

Line 45373: (p_table_name => 'MSC_ST_DEMANDS',

45369:
45370: -- Derive the ROUTING_SEQUENCE_ID from LOCAL ID table
45371:
45372: lv_return :=msc_st_util.derive_routing_sequence_id
45373: (p_table_name => 'MSC_ST_DEMANDS',
45374: p_rtg_col_name => 'ROUTING_NAME',
45375: p_rtg_col_id =>'ROUTING_SEQUENCE_ID',
45376: p_instance_code => v_instance_code,
45377: p_batch_id => lv_batch_id,

Line 45388: 'update msc_st_demands msd'

45384: end if;
45385:
45386: v_sql_stmt := 11;
45387: lv_sql_stmt:=
45388: 'update msc_st_demands msd'
45389: ||' set operation_seq_num = '
45390: ||' (select operation_seq_num '
45391: ||' from msc_routing_operations mro '
45392: ||' where mro.routing_sequence_id = msd.routing_sequence_id and '

Line 45413: 'UPDATE msc_st_demands msd'

45409: -- Derive operation seq num from local id table
45410:
45411: v_sql_stmt := 12;
45412: lv_sql_stmt:=
45413: 'UPDATE msc_st_demands msd'
45414: ||' SET operation_seq_num= (SELECT number1'
45415: ||' FROM msc_local_id_setup mlis'
45416: ||' WHERE mlis.char1 = msd.sr_instance_code'
45417: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '

Line 45455: 'UPDATE msc_st_demands '

45451:
45452: v_sql_stmt := 13;
45453:
45454: lv_sql_stmt :=
45455: 'UPDATE msc_st_demands '
45456: ||' SET process_flag ='||G_ERROR_FLG||','
45457: ||' error_text = '||''''||lv_message_text||''''
45458: ||' WHERE NVL(operation_seq_num,'||NULL_VALUE||') = '||NULL_VALUE
45459: ||' AND deleted_flag ='||SYS_YES

Line 45487: (p_table_name => 'MSC_ST_DEMANDS',

45483: END IF;
45484:
45485: --Derive Project Id.
45486: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
45487: (p_table_name => 'MSC_ST_DEMANDS',
45488: p_proj_col_name => 'PROJECT_NUMBER',
45489: p_proj_task_col_id => 'PROJECT_ID',
45490: p_instance_code => v_instance_code,
45491: p_entity_name => 'PROJECT_ID',

Line 45521: (p_table_name => 'MSC_ST_DEMANDS',

45517: END IF;
45518:
45519: --Derive Task Id.
45520: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
45521: (p_table_name => 'MSC_ST_DEMANDS',
45522: p_proj_col_name => 'PROJECT_NUMBER',
45523: p_proj_task_col_id => 'TASK_ID',
45524: p_instance_code => v_instance_code,
45525: p_entity_name => 'TASK_ID',

Line 45559: (p_table_name => 'MSC_ST_DEMANDS',

45555: lv_where_str := ' AND NVL(demand_type,'||NULL_VALUE||') <> '||G_DEMAND_TYPE
45556: ||' AND origination_type IN (2,3) AND deleted_flag ='||SYS_NO ;
45557:
45558: lv_return := MSC_ST_UTIL.LOG_ERROR
45559: (p_table_name => 'MSC_ST_DEMANDS',
45560: p_instance_code => v_instance_code,
45561: p_row => lv_column_names,
45562: p_severity => G_SEV_WARNING,
45563: p_message_text => lv_message_text,

Line 45582: (p_table_name => 'MSC_ST_DEMANDS',

45578: lv_where_str := ' AND NVL(demand_type,'||NULL_VALUE||') <> 2'
45579: ||' AND origination_type = 25 AND deleted_flag ='||SYS_NO ;
45580:
45581: lv_return := MSC_ST_UTIL.LOG_ERROR
45582: (p_table_name => 'MSC_ST_DEMANDS',
45583: p_instance_code => v_instance_code,
45584: p_row => lv_column_names,
45585: p_severity => G_SEV_WARNING,
45586: p_message_text => lv_message_text,

Line 45620: (p_table_name => 'MSC_ST_DEMANDS',

45616: lv_where_str := ' AND NVL(wip_supply_type,'||NULL_VALUE||') NOT IN (1,2,3,4,5,6,7)'
45617: ||' AND origination_type IN (2,3) AND deleted_flag ='||SYS_NO ;
45618:
45619: lv_return := MSC_ST_UTIL.LOG_ERROR
45620: (p_table_name => 'MSC_ST_DEMANDS',
45621: p_instance_code => v_instance_code,
45622: p_row => lv_column_names,
45623: p_severity => G_SEV_WARNING,
45624: p_message_text => lv_message_text,

Line 45658: (p_table_name => 'MSC_ST_DEMANDS',

45654: lv_where_str := ' AND NVL(wip_status_code,'||NULL_VALUE||') NOT IN (1,3,4,6,7,12)'
45655: ||' AND origination_type IN (2,3,25)AND deleted_flag ='||SYS_NO ;
45656:
45657: lv_return := MSC_ST_UTIL.LOG_ERROR
45658: (p_table_name => 'MSC_ST_DEMANDS',
45659: p_instance_code => v_instance_code,
45660: p_row => lv_column_names,
45661: p_severity => G_SEV_WARNING,
45662: p_message_text => lv_message_text,

Line 45680: pEntityName => 'MSC_ST_DEMANDS',

45676: (ERRBUF => lv_error_text,
45677: RETCODE => lv_return,
45678: pBatchID => lv_batch_id,
45679: pInstanceCode => v_instance_code,
45680: pEntityName => 'MSC_ST_DEMANDS',
45681: pInstanceID => v_instance_id);
45682:
45683: IF NVL(lv_return,0) <> 0 THEN
45684: RAISE ex_logging_err;

Line 45700: UPDATE msc_st_demands

45696: -- SELECT msc_st_operation_sequence_id_s.NEXTVAL
45697: -- INTO lv_local_id
45698: -- FROM DUAL;
45699:
45700: UPDATE msc_st_demands
45701: SET operation_seq_num =
45702: to_number(decode(length(rtrim(operation_seq_code,'0123456789')),
45703: NULL,operation_seq_code,'1'))
45704: WHERE rowid = lb_rowid(j);

Line 45760: FROM msc_st_demands

45756: v_current_date,
45757: v_current_user,
45758: v_current_date,
45759: v_current_user
45760: FROM msc_st_demands
45761: WHERE rowid = lb_rowid(j) ;
45762:
45763: END IF;
45764: CLOSE c2;

Line 45770: 'UPDATE msc_st_demands msd'

45766: -- Update operation seq num from local id table
45767:
45768: v_sql_stmt := 12;
45769: lv_sql_stmt:=
45770: 'UPDATE msc_st_demands msd'
45771: ||' SET operation_seq_num= (SELECT number1'
45772: ||' FROM msc_local_id_setup mlis'
45773: ||' WHERE mlis.char1 = msd.sr_instance_code'
45774: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '

Line 45796: (p_table_name => 'MSC_ST_DEMANDS',

45792: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code,lv_batch_id;
45793:
45794:
45795: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
45796: (p_table_name => 'MSC_ST_DEMANDS',
45797: p_instance_id => v_instance_id,
45798: p_instance_code => v_instance_code,
45799: p_process_flag => G_VALID,
45800: p_error_text => lv_error_text,

Line 45809: (p_table_name => 'MSC_ST_DEMANDS',

45805: RAISE ex_logging_err;
45806: END IF;
45807:
45808: lv_return := MSC_ST_UTIL.LOG_ERROR
45809: (p_table_name => 'MSC_ST_DEMANDS',
45810: p_instance_code => v_instance_code,
45811: p_row => lv_column_names,
45812: p_severity => G_SEV_ERROR,
45813: p_message_text => NULL,

Line 49674: 'UPDATE msc_st_demands msd1'

49670: --For MDS and Forecast (origination_type 8 and 29)
49671:
49672: -- MDS
49673: lv_sql_stmt :=
49674: 'UPDATE msc_st_demands msd1'
49675: ||' SET process_flag = '||G_ERROR_FLG||','
49676: ||' error_text = '||''''||lv_message_text||''''
49677: ||' WHERE message_id < (SELECT MAX(message_id)'
49678: ||' FROM msc_st_demands msd2'

Line 49678: ||' FROM msc_st_demands msd2'

49674: 'UPDATE msc_st_demands msd1'
49675: ||' SET process_flag = '||G_ERROR_FLG||','
49676: ||' error_text = '||''''||lv_message_text||''''
49677: ||' WHERE message_id < (SELECT MAX(message_id)'
49678: ||' FROM msc_st_demands msd2'
49679: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code '
49680: ||' AND nvl(msd2.using_assembly_demand_date,SYSDATE-365000 ) = nvl(msd1.using_assembly_demand_date,SYSDATE-365000 ) '
49681: ||' AND msd2.item_name = msd1.item_name'
49682: ||' AND msd2.organization_code = msd1.organization_code '

Line 49704: 'UPDATE msc_st_demands msd1'

49700:
49701: -- Forecast
49702:
49703: lv_sql_stmt :=
49704: 'UPDATE msc_st_demands msd1'
49705: ||' SET process_flag = '||G_ERROR_FLG||','
49706: ||' error_text = '||''''||lv_message_text||''''
49707: ||' WHERE message_id < (SELECT MAX(message_id)'
49708: ||' FROM msc_st_demands msd2'

Line 49708: ||' FROM msc_st_demands msd2'

49704: 'UPDATE msc_st_demands msd1'
49705: ||' SET process_flag = '||G_ERROR_FLG||','
49706: ||' error_text = '||''''||lv_message_text||''''
49707: ||' WHERE message_id < (SELECT MAX(message_id)'
49708: ||' FROM msc_st_demands msd2'
49709: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code '
49710: ||' AND msd2.schedule_date = msd1.schedule_date '
49711: ||' AND msd2.item_name = msd1.item_name'
49712: ||' AND msd2.forecast_designator = msd1.forecast_designator '

Line 49747: 'UPDATE msc_st_demands msd1'

49743: -- Forecast
49744: v_sql_stmt := 12;
49745:
49746: lv_sql_stmt :=
49747: 'UPDATE msc_st_demands msd1'
49748: ||' SET process_flag = '||G_ERROR_FLG||','
49749: ||' error_text = '||''''||lv_message_text||''''
49750: ||' WHERE EXISTS( SELECT 1 '
49751: ||' FROM msc_st_demands msd2'

Line 49751: ||' FROM msc_st_demands msd2'

49747: 'UPDATE msc_st_demands msd1'
49748: ||' SET process_flag = '||G_ERROR_FLG||','
49749: ||' error_text = '||''''||lv_message_text||''''
49750: ||' WHERE EXISTS( SELECT 1 '
49751: ||' FROM msc_st_demands msd2'
49752: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code '
49753: ||' AND msd2.organization_code = msd1.organization_code '
49754: ||' AND NVL(msd2.company_name,'||''''||NULL_CHAR||''''||') '
49755: ||' = NVL(msd1.company_name,'||''''||NULL_CHAR||''''||') '

Line 49779: 'UPDATE msc_st_demands msd1'

49775: -- MDS
49776: v_sql_stmt := 12;
49777:
49778: lv_sql_stmt :=
49779: 'UPDATE msc_st_demands msd1'
49780: ||' SET process_flag = '||G_ERROR_FLG||','
49781: ||' error_text = '||''''||lv_message_text||''''
49782: ||' WHERE EXISTS( SELECT 1 '
49783: ||' FROM msc_st_demands msd2'

Line 49783: ||' FROM msc_st_demands msd2'

49779: 'UPDATE msc_st_demands msd1'
49780: ||' SET process_flag = '||G_ERROR_FLG||','
49781: ||' error_text = '||''''||lv_message_text||''''
49782: ||' WHERE EXISTS( SELECT 1 '
49783: ||' FROM msc_st_demands msd2'
49784: ||' WHERE msd2.sr_instance_code '
49785: ||' = msd1.sr_instance_code '
49786: ||' AND msd2.organization_code '
49787: ||' = msd1.organization_code '

Line 50827: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_DEMANDS', p_erp_enabled => 'Y');

50823: BEGIN
50824: --- ===== Enter the ASCP tables to be set here ======
50825:
50826: IF v_mat_dmd_enabled = SYS_YES OR v_iro_enabled = SYS_YES or v_ero_enabled = SYS_YES THEN
50827: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_DEMANDS', p_erp_enabled => 'Y');
50828: IF lv_count > 0 Then
50829: prec.mds_flag:= SYS_YES;
50830: prec.forecast_flag:= SYS_YES;
50831: prec.wip_flag:= SYS_YES;

Line 53933: cursor c2 is SELECT rowid FROM msc_st_demands

53929: lb_batch NumTblTyp := NumTblTyp(0);
53930: lv_batch_id PLS_INTEGER;
53931: lv_batch_start PLS_INTEGER;
53932: lv_batch_last PLS_INTEGER;
53933: cursor c2 is SELECT rowid FROM msc_st_demands
53934: WHERE PROCESS_FLAG IN (G_IN_PROCESS,G_ERROR_FLG)
53935: AND origination_type IN (7,8,29)
53936: AND NVL(batch_id,NULL_VALUE) = NULL_VALUE
53937: AND sr_instance_code = v_instance_code;

Line 53962: UPDATE msc_st_demands

53958:
53959: lv_batch_last := lv_batch_id;
53960:
53961: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
53962: UPDATE msc_st_demands
53963: SET batch_id = lb_batch(j)
53964: WHERE rowid = lb_rowid(j);
53965:
53966: commit;