APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_DEMANDS

Line 649: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code

645: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
646: application_short_name => 'MSC',
647: statement_type => AD_DDL.CREATE_INDEX,
648: statement =>
649: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code
650: ||' on MSC_ST_DEMANDS '
651: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
652: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
653: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);

Line 650: ||' on MSC_ST_DEMANDS '

646: application_short_name => 'MSC',
647: statement_type => AD_DDL.CREATE_INDEX,
648: statement =>
649: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code
650: ||' on MSC_ST_DEMANDS '
651: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
652: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
653: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);
654: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N1_'||v_instance_code);

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

649: 'create index MSC_ST_DEMANDS_N1_'||v_instance_code
650: ||' on MSC_ST_DEMANDS '
651: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
652: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
653: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);
654: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N1_'||v_instance_code);
655:
656:
657: EXCEPTION

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

650: ||' on MSC_ST_DEMANDS '
651: ||'(sr_instance_code, schedule_date, item_name, organization_code, forecast_designator, company_name) '
652: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
653: object_name =>'MSC_ST_DEMANDS_N1_'||v_instance_code);
654: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N1_'||v_instance_code);
655:
656:
657: EXCEPTION
658: WHEN OTHERS THEN

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

655:
656:
657: EXCEPTION
658: WHEN OTHERS THEN
659: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
660: END;
661:
662: BEGIN
663: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 667: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code

663: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
664: application_short_name => 'MSC',
665: statement_type => AD_DDL.CREATE_INDEX,
666: statement =>
667: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code
668: ||' on MSC_ST_DEMANDS '
669: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
670: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
671: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);

Line 668: ||' on MSC_ST_DEMANDS '

664: application_short_name => 'MSC',
665: statement_type => AD_DDL.CREATE_INDEX,
666: statement =>
667: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code
668: ||' on MSC_ST_DEMANDS '
669: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
670: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
671: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);
672:

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

667: 'create index MSC_ST_DEMANDS_N2_'||v_instance_code
668: ||' on MSC_ST_DEMANDS '
669: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
670: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
671: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);
672:
673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N2_'||v_instance_code);
674:
675: EXCEPTION

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

669: ||'(SR_INSTANCE_CODE, ITEM_NAME,WIP_ENTITY_NAME, ORGANIZATION_CODE, ORIGINATION_TYPE, OPERATION_SEQ_CODE, COMPANY_NAME) '
670: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
671: object_name =>'MSC_ST_DEMANDS_N2_'||v_instance_code);
672:
673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N2_'||v_instance_code);
674:
675: EXCEPTION
676: WHEN OTHERS THEN
677: 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 677: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N2_'||v_instance_code);
674:
675: EXCEPTION
676: WHEN OTHERS THEN
677: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
678: END;
679:
680: BEGIN
681: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

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

694: WHEN OTHERS THEN
695: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SO_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
696: END;
697:
698: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);
699: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SALES_ORDERS', v_instance_id, -1);
700:
701: END IF;
702:

Line 1735: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code

1731: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1732: application_short_name => 'MSC',
1733: statement_type => AD_DDL.CREATE_INDEX,
1734: statement =>
1735: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code
1736: ||' on MSC_ST_DEMANDS '
1737: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1738: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1739: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);

Line 1736: ||' on MSC_ST_DEMANDS '

1732: application_short_name => 'MSC',
1733: statement_type => AD_DDL.CREATE_INDEX,
1734: statement =>
1735: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code
1736: ||' on MSC_ST_DEMANDS '
1737: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1738: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1739: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1740:

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

1735: 'create index MSC_ST_DEMANDS_N3_'||v_instance_code
1736: ||' on MSC_ST_DEMANDS '
1737: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1738: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1739: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1740:
1741: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1742:
1743: EXCEPTION

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

1737: ||' (batch_id, origination_type, sr_instance_code, deleted_flag) '
1738: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1739: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1740:
1741: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1742:
1743: EXCEPTION
1744: WHEN OTHERS THEN
1745: 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 1745: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1741: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1742:
1743: EXCEPTION
1744: WHEN OTHERS THEN
1745: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1746: END;
1747:
1748: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);
1749:

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

1744: WHEN OTHERS THEN
1745: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1746: END;
1747:
1748: msc_analyse_tables_pk.analyse_table( 'MSC_ST_DEMANDS', v_instance_id, -1);
1749:
1750: ELSIF p_batch_index = 'SO' THEN
1751:
1752: BEGIN

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

1918: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1919: application_short_name => 'MSC',
1920: statement_type => AD_DDL.DROP_INDEX,
1921: statement =>
1922: 'drop index MSC_ST_DEMANDS_N1_'||v_instance_code,
1923: object_name => 'MSC_ST_DEMANDS_N1_'||v_instance_code);
1924:
1925: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1926: EXCEPTION

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

1919: application_short_name => 'MSC',
1920: statement_type => AD_DDL.DROP_INDEX,
1921: statement =>
1922: 'drop index MSC_ST_DEMANDS_N1_'||v_instance_code,
1923: object_name => 'MSC_ST_DEMANDS_N1_'||v_instance_code);
1924:
1925: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1926: EXCEPTION
1927: WHEN OTHERS THEN

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

1921: statement =>
1922: 'drop index MSC_ST_DEMANDS_N1_'||v_instance_code,
1923: object_name => 'MSC_ST_DEMANDS_N1_'||v_instance_code);
1924:
1925: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1926: EXCEPTION
1927: WHEN OTHERS THEN
1928: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1929: END;

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

1924:
1925: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N1_'||v_instance_code);
1926: EXCEPTION
1927: WHEN OTHERS THEN
1928: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1929: END;
1930:
1931: BEGIN
1932: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

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

1932: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1933: application_short_name => 'MSC',
1934: statement_type => AD_DDL.DROP_INDEX,
1935: statement =>
1936: 'drop index MSC_ST_DEMANDS_N2_'||v_instance_code,
1937: object_name => 'MSC_ST_DEMANDS_N2_'||v_instance_code);
1938:
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1940: EXCEPTION

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

1933: application_short_name => 'MSC',
1934: statement_type => AD_DDL.DROP_INDEX,
1935: statement =>
1936: 'drop index MSC_ST_DEMANDS_N2_'||v_instance_code,
1937: object_name => 'MSC_ST_DEMANDS_N2_'||v_instance_code);
1938:
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1940: EXCEPTION
1941: WHEN OTHERS THEN

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

1935: statement =>
1936: 'drop index MSC_ST_DEMANDS_N2_'||v_instance_code,
1937: object_name => 'MSC_ST_DEMANDS_N2_'||v_instance_code);
1938:
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1940: EXCEPTION
1941: WHEN OTHERS THEN
1942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1943: END;

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

1938:
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N2_'||v_instance_code);
1940: EXCEPTION
1941: WHEN OTHERS THEN
1942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1943: END;
1944:
1945: BEGIN
1946: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

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

1946: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1947: application_short_name => 'MSC',
1948: statement_type => AD_DDL.DROP_INDEX,
1949: statement =>
1950: 'drop index MSC_ST_DEMANDS_N3_'||v_instance_code,
1951: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1952:
1953: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1954:

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

1947: application_short_name => 'MSC',
1948: statement_type => AD_DDL.DROP_INDEX,
1949: statement =>
1950: 'drop index MSC_ST_DEMANDS_N3_'||v_instance_code,
1951: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1952:
1953: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1954:
1955: EXCEPTION

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

1949: statement =>
1950: 'drop index MSC_ST_DEMANDS_N3_'||v_instance_code,
1951: object_name => 'MSC_ST_DEMANDS_N3_'||v_instance_code);
1952:
1953: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1954:
1955: EXCEPTION
1956: WHEN OTHERS THEN
1957: 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 1957: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1953: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_DEMANDS_N3_'||v_instance_code);
1954:
1955: EXCEPTION
1956: WHEN OTHERS THEN
1957: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_DEMANDS_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1958: END;
1959:
1960: BEGIN
1961: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

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

38547: END LOAD_RESERVATIONS ;
38548:
38549: /*==========================================================================+
38550: | DESCRIPTION : This procedure is called for validating demand related |
38551: | records from the msc_st_demands table. The following |
38552: | types are considered for the validation. |
38553: | Demand origination Origination Type |
38554: | ------------------------------------------------- |
38555: | MDS Demand 8 |

Line 38575: FROM msc_st_demands

38571: ex_logging_err EXCEPTION;
38572:
38573: CURSOR c1(p_batch_id NUMBER) IS
38574: SELECT rowid
38575: FROM msc_st_demands
38576: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
38577: AND origination_type IN (7,8,29)
38578: AND batch_id = p_batch_id
38579: AND sr_instance_code = v_instance_code ;

Line 38583: FROM msc_st_demands

38579: AND sr_instance_code = v_instance_code ;
38580:
38581: CURSOR c2(p_batch_id NUMBER) IS
38582: SELECT rowid
38583: FROM msc_st_demands
38584: WHERE process_flag = G_IN_PROCESS
38585: AND sr_instance_code = v_instance_code
38586: AND batch_id = p_batch_id
38587: AND NVL(disposition_id,NULL_VALUE) = NULL_VALUE

Line 38593: FROM msc_st_demands

38589: AND deleted_flag = SYS_NO;
38590:
38591: CURSOR c3(p_batch_id NUMBER) IS
38592: SELECT rowid
38593: FROM msc_st_demands
38594: WHERE process_flag = G_IN_PROCESS
38595: AND sr_instance_code = v_instance_code
38596: AND batch_id = p_batch_id
38597: AND NVL(sales_order_line_id,NULL_VALUE) = NULL_VALUE

Line 38644: UPDATE msc_st_demands

38640: CLOSE c1;
38641:
38642: v_sql_stmt := 03;
38643: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
38644: UPDATE msc_st_demands
38645: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
38646: refresh_id = v_refresh_id,
38647: last_update_date = v_current_date,
38648: last_updated_by = v_current_user,

Line 38645: SET st_transaction_id = msc_st_demands_s.NEXTVAL,

38641:
38642: v_sql_stmt := 03;
38643: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
38644: UPDATE msc_st_demands
38645: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
38646: refresh_id = v_refresh_id,
38647: last_update_date = v_current_date,
38648: last_updated_by = v_current_user,
38649: creation_date = v_current_date,

Line 38675: (p_table_name => 'MSC_ST_DEMANDS',

38671: lv_where_str :=
38672: ' AND NVL(deleted_flag,'||NULL_VALUE||') NOT IN(1,2)';
38673:
38674: lv_return:= MSC_ST_UTIL.LOG_ERROR
38675: (p_table_name => 'MSC_ST_DEMANDS',
38676: p_instance_code => v_instance_code,
38677: p_row => lv_column_names,
38678: p_severity => G_SEV_WARNING,
38679: p_message_text => lv_message_text,

Line 38711: 'UPDATE msc_st_demands '

38707: -- Error out records where USING_ASSEMBLY_DEMAND_DATE is NULL
38708:
38709: v_sql_stmt := 04;
38710: lv_sql_stmt :=
38711: 'UPDATE msc_st_demands '
38712: ||' SET process_flag = '||G_ERROR_FLG||','
38713: ||' error_text = '||''''||lv_message_text||''''
38714: ||' WHERE NVL(using_assembly_demand_date,SYSDATE-36500) = SYSDATE-36500'
38715: ||' AND process_flag = '||G_IN_PROCESS

Line 38744: 'UPDATE msc_st_demands '

38740: -- Error out records if REVISED_DMD_PRIORITY is not a positive number
38741:
38742: v_sql_stmt := 04.5;
38743: lv_sql_stmt :=
38744: 'UPDATE msc_st_demands '
38745: ||' SET process_flag = '||G_ERROR_FLG||','
38746: ||' error_text = '||''''||lv_message_text||''''
38747: ||' WHERE NVL(REVISED_DMD_PRIORITY, 1) < 0'
38748: ||' AND process_flag = '||G_IN_PROCESS

Line 38779: (p_table_name => 'MSC_ST_DEMANDS',

38775: --Log a warning for those records where the revised_dmd_date is greater
38776: --than firm date(schedule_date)
38777:
38778: lv_return := MSC_ST_UTIL.LOG_ERROR
38779: (p_table_name => 'MSC_ST_DEMANDS',
38780: p_instance_code => v_instance_code,
38781: p_row => lv_column_names,
38782: p_severity => G_SEV_WARNING,
38783: p_message_text => lv_message_text,

Line 38812: 'UPDATE msc_st_demands '

38808: -- and deleted_flag = SYS_NO
38809:
38810: v_sql_stmt := 05;
38811: lv_sql_stmt :=
38812: 'UPDATE msc_st_demands '
38813: ||' SET process_flag = '||G_ERROR_FLG||','
38814: ||' error_text = '||''''||lv_message_text||''''
38815: ||' WHERE NVL(using_requirement_quantity,'||NULL_VALUE||') ='||NULL_VALUE
38816: ||' AND process_flag = '||G_IN_PROCESS

Line 38842: (p_table_name => 'MSC_ST_DEMANDS',

38838: END IF;
38839:
38840: --Derive Organization_id
38841: lv_return:= MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
38842: (p_table_name => 'MSC_ST_DEMANDS',
38843: p_org_partner_name => 'ORGANIZATION_CODE',
38844: p_org_partner_id => 'ORGANIZATION_ID',
38845: p_instance_code => v_instance_code,
38846: p_partner_type => G_ORGANIZATION,

Line 38875: (p_table_name => 'MSC_ST_DEMANDS',

38871:
38872:
38873: --Derive Inventory_item_id
38874: lv_return:= MSC_ST_UTIL.DERIVE_ITEM_ID
38875: (p_table_name => 'MSC_ST_DEMANDS',
38876: p_item_col_name => 'ITEM_NAME',
38877: p_item_col_id => 'INVENTORY_ITEM_ID',
38878: p_instance_id => v_instance_id,
38879: p_instance_code => v_instance_code,

Line 38906: (p_table_name => 'MSC_ST_DEMANDS',

38902: END IF;
38903:
38904: --Derive Inventory_item_id
38905: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
38906: (p_table_name => 'MSC_ST_DEMANDS',
38907: p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
38908: p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
38909: p_instance_id => v_instance_id,
38910: p_instance_code => v_instance_code,

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

38935: -- Validate Demand Class, if value provided it should exists
38936: -- in ODS or staging table
38937:
38938: lv_return :=
38939: MSC_ST_UTIL.VALIDATE_DMD_CLASS(p_table_name => 'MSC_ST_DEMANDS',
38940: p_dmd_class_column => 'DEMAND_CLASS',
38941: p_instance_id => v_instance_id,
38942: p_instance_code => v_instance_code,
38943: p_severity => G_SEV3_ERROR,

Line 38959: ' UPDATE msc_st_demands'

38955: -- Update using_assembly_item_id = inventory_item_id
38956:
38957: v_sql_stmt := 06;
38958: lv_sql_stmt :=
38959: ' UPDATE msc_st_demands'
38960: ||' SET using_assembly_item_id = inventory_item_id'
38961: ||' WHERE process_flag = '||G_IN_PROCESS
38962: ||' AND NVL(using_assembly_item_id,'||NULL_VALUE||') = '||NULL_VALUE
38963: ||' AND deleted_flag = '||SYS_NO

Line 38981: ' UPDATE msc_st_demands'

38977:
38978:
38979: v_sql_stmt := 07;
38980: lv_sql_stmt :=
38981: ' UPDATE msc_st_demands'
38982: ||' SET schedule_date = using_assembly_demand_date '
38983: ||' WHERE process_flag = '||G_IN_PROCESS
38984: ||' AND deleted_flag = '||SYS_NO
38985: ||' AND origination_type IN (7,8,29)'

Line 38998: 'UPDATE msc_st_demands msd '

38994: -- Derive disposition_id for MDS
38995:
38996: v_sql_stmt := 08;
38997: lv_sql_stmt :=
38998: 'UPDATE msc_st_demands msd '
38999: ||' SET disposition_id = (SELECT local_id'
39000: ||' FROM msc_local_id_demand mlid'
39001: ||' WHERE mlid.char1 = msd.sr_instance_code'
39002: ||' AND NVL(mlid.char2,'||''''||NULL_CHAR||''''||') '

Line 39038: 'UPDATE msc_st_demands '

39034:
39035: v_sql_stmt := 09;
39036:
39037: lv_sql_stmt :=
39038: 'UPDATE msc_st_demands '
39039: ||' SET process_flag ='||G_ERROR_FLG||','
39040: ||' error_text = '||''''||lv_message_text||''''
39041: ||' WHERE NVL(disposition_id,'||NULL_VALUE||') = '||NULL_VALUE
39042: ||' AND deleted_flag ='||SYS_YES

Line 39057: 'UPDATE msc_st_demands msd '

39053: -- Derive sales_order_line_id for forecast
39054:
39055: v_sql_stmt := 10;
39056: lv_sql_stmt :=
39057: 'UPDATE msc_st_demands msd '
39058: ||' SET sales_order_line_id = (SELECT local_id'
39059: ||' FROM msc_local_id_demand mlid'
39060: ||' WHERE mlid.char1 = msd.sr_instance_code'
39061: ||' AND NVL(mlid.char2,'||''''||NULL_CHAR||''''||') '

Line 39098: 'UPDATE msc_st_demands '

39094:
39095: v_sql_stmt := 11;
39096:
39097: lv_sql_stmt :=
39098: 'UPDATE msc_st_demands '
39099: ||' SET process_flag ='||G_ERROR_FLG||','
39100: ||' error_text = '||''''||lv_message_text||''''
39101: ||' WHERE NVL(sales_order_line_id,'||NULL_VALUE||') = '||NULL_VALUE
39102: ||' AND deleted_flag ='||SYS_YES

Line 39129: (p_table_name => 'MSC_ST_DEMANDS',

39125: END IF;
39126:
39127: --Derive Project Id.
39128: lv_return:= MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
39129: (p_table_name => 'MSC_ST_DEMANDS',
39130: p_proj_col_name => 'PROJECT_NUMBER',
39131: p_proj_task_col_id => 'PROJECT_ID',
39132: p_instance_code => v_instance_code,
39133: p_entity_name => 'PROJECT_ID',

Line 39163: (p_table_name => 'MSC_ST_DEMANDS',

39159: END IF;
39160:
39161: --Derive Task Id.
39162: lv_return:= MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
39163: (p_table_name => 'MSC_ST_DEMANDS',
39164: p_proj_col_name => 'PROJECT_NUMBER',
39165: p_proj_task_col_id => 'TASK_ID',
39166: p_instance_code => v_instance_code,
39167: p_entity_name => 'TASK_ID',

Line 39195: p_token_value3 => 'MSC_ST_DEMANDS' );

39191: ||' DEMAND_SCHEDULE_NAME',
39192: p_token2 => 'MASTER_TABLE',
39193: p_token_value2 => 'MSC_ST_DESIGNATORS',
39194: p_token3 => 'CHILD_TABLE' ,
39195: p_token_value3 => 'MSC_ST_DEMANDS' );
39196:
39197: IF lv_return <> 0 THEN
39198: RAISE ex_logging_err;
39199: END IF;

Line 39205: 'UPDATE msc_st_demands msdm'

39201: -- For MDS check whether designator exists
39202:
39203: v_sql_stmt := 12;
39204: lv_sql_stmt :=
39205: 'UPDATE msc_st_demands msdm'
39206: ||' SET process_flag = '||G_ERROR_FLG||','
39207: ||' error_text = '||''''||lv_message_text||''''
39208: ||' WHERE NOT EXISTS(SELECT 1'
39209: ||' FROM msc_st_designators msd'

Line 39263: 'UPDATE msc_st_demands '

39259:
39260: v_sql_stmt := 13;
39261:
39262: lv_sql_stmt :=
39263: 'UPDATE msc_st_demands '
39264: ||' SET process_flag = '||G_ERROR_FLG||','
39265: ||' error_text = '||''''||lv_message_text||''''
39266: ||' WHERE (demand_type = 2'
39267: ||' OR assembly_demand_comp_date IS NOT NULL '

Line 39293: p_token_value3 => 'MSC_ST_DEMANDS' );

39289: ||'ORGANIZATION_CODE,FORECAST_DESIGNATOR',
39290: p_token2 => 'MASTER_TABLE',
39291: p_token_value2 => 'MSC_ST_DESIGNATORS',
39292: p_token3 => 'CHILD_TABLE' ,
39293: p_token_value3 => 'MSC_ST_DEMANDS' );
39294:
39295: IF lv_return <> 0 THEN
39296: RAISE ex_logging_err;
39297: END IF;

Line 39303: 'UPDATE msc_st_demands msdm'

39299: -- For Forecast,check whether designator exists
39300:
39301: v_sql_stmt := 14;
39302: lv_sql_stmt :=
39303: 'UPDATE msc_st_demands msdm'
39304: ||' SET process_flag = '||G_ERROR_FLG||','
39305: ||' error_text = '||''''||lv_message_text||''''
39306: ||' WHERE NOT EXISTS(SELECT 1'
39307: ||' FROM msc_st_designators msd'

Line 39361: (p_table_name => 'MSC_ST_DEMANDS',

39357: ||' AND NVL(demand_type,'||NULL_VALUE||') <> 1'
39358: ||' AND origination_type = 29 AND deleted_flag ='||SYS_NO ;
39359:
39360: lv_return:= MSC_ST_UTIL.LOG_ERROR
39361: (p_table_name => 'MSC_ST_DEMANDS',
39362: p_instance_code => v_instance_code,
39363: p_row => lv_column_names,
39364: p_severity => G_SEV_WARNING,
39365: p_message_text => lv_message_text,

Line 39399: (p_table_name => 'MSC_ST_DEMANDS',

39395: ||' AND NVL(demand_type,'||NULL_VALUE||') <> 2'
39396: ||' AND origination_type = 29 AND deleted_flag ='||SYS_NO ;
39397:
39398: lv_return:= MSC_ST_UTIL.LOG_ERROR
39399: (p_table_name => 'MSC_ST_DEMANDS',
39400: p_instance_code => v_instance_code,
39401: p_row => lv_column_names,
39402: p_severity => G_SEV_WARNING,
39403: p_message_text => lv_message_text,

Line 39436: (p_table_name => 'MSC_ST_DEMANDS',

39432: lv_where_str := ' AND NVL(bucket_type,'||NULL_VALUE||') NOT IN (1,2,3)'
39433: ||' AND origination_type = 29 AND deleted_flag ='||SYS_NO ;
39434:
39435: lv_return:= MSC_ST_UTIL.LOG_ERROR
39436: (p_table_name => 'MSC_ST_DEMANDS',
39437: p_instance_code => v_instance_code,
39438: p_row => lv_column_names,
39439: p_severity => G_SEV_WARNING,
39440: p_message_text => lv_message_text,

Line 39469: (p_table_name => 'MSC_ST_DEMANDS',

39465:
39466: -- Derive customer_id
39467:
39468: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
39469: (p_table_name => 'MSC_ST_DEMANDS',
39470: p_org_partner_name => 'CUSTOMER_NAME',
39471: p_org_partner_id => 'CUSTOMER_ID',
39472: p_instance_code => v_instance_code,
39473: p_partner_type => G_CUSTOMER,

Line 39502: (p_table_name => 'MSC_ST_DEMANDS',

39498:
39499: -- Derive customer_site_id
39500:
39501: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
39502: (p_table_name => 'MSC_ST_DEMANDS',
39503: p_partner_name => 'CUSTOMER_NAME',
39504: p_partner_site_code => 'CUSTOMER_SITE_CODE',
39505: p_partner_site_id => 'CUSTOMER_SITE_ID',
39506: p_instance_code => v_instance_code,

Line 39535: (p_table_name => 'MSC_ST_DEMANDS',

39531:
39532:
39533: -- Derive bill_to_site_id
39534: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
39535: (p_table_name => 'MSC_ST_DEMANDS',
39536: p_partner_name => 'CUSTOMER_NAME',
39537: p_partner_site_code => 'BILL_CODE',
39538: p_partner_site_id => 'BILL_ID',
39539: p_instance_code => v_instance_code,

Line 39575: (p_table_name => 'MSC_ST_DEMANDS',

39571: ||' NOT IN (1,2) AND origination_type in (7, 8)'
39572: ||' AND NVL(order_date_type_code,'||NULL_VALUE||')<> '||NULL_VALUE;
39573:
39574: lv_return := MSC_ST_UTIL.LOG_ERROR
39575: (p_table_name => 'MSC_ST_DEMANDS',
39576: p_instance_code => v_instance_code,
39577: p_row => lv_column_names,
39578: p_severity => G_SEV_WARNING,
39579: p_message_text => lv_message_text,

Line 39597: pEntityName => 'MSC_ST_DEMANDS',

39593: (ERRBUF => lv_error_text,
39594: RETCODE => lv_return,
39595: pBatchID => p_batch_id,
39596: pInstanceCode => v_instance_code,
39597: pEntityName => 'MSC_ST_DEMANDS',
39598: pInstanceID => v_instance_id);
39599:
39600: IF NVL(lv_return,0) <> 0 THEN
39601: RAISE ex_logging_err;

Line 39612: UPDATE msc_st_demands

39608:
39609: IF c2%ROWCOUNT > 0 THEN
39610: v_sql_stmt := 15;
39611: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
39612: UPDATE msc_st_demands
39613: SET disposition_id = msc_st_demand_disposition_id_s.NEXTVAL
39614: WHERE rowid = lb_rowid(j);
39615:
39616: v_sql_stmt := 16;

Line 39672: FROM msc_st_demands

39668: v_current_date,
39669: v_current_user,
39670: v_current_date,
39671: v_current_user
39672: FROM msc_st_demands
39673: WHERE rowid = lb_rowid(j);
39674:
39675: END IF;
39676: CLOSE c2 ;

Line 39687: UPDATE msc_st_demands

39683:
39684: IF c3%ROWCOUNT > 0 THEN
39685: v_sql_stmt := 17;
39686: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
39687: UPDATE msc_st_demands
39688: SET sales_order_line_id = msc_st_demand_disposition_id_s.NEXTVAL
39689: WHERE rowid = lb_rowid(j);
39690:
39691: v_sql_stmt := 18;

Line 39747: FROM msc_st_demands

39743: v_current_date,
39744: v_current_user,
39745: v_current_date,
39746: v_current_user
39747: FROM msc_st_demands
39748: WHERE rowid = lb_rowid(j);
39749:
39750: END IF;
39751: CLOSE c3 ;

Line 39754: (p_table_name => 'MSC_ST_DEMANDS',

39750: END IF;
39751: CLOSE c3 ;
39752:
39753: lv_return:= MSC_ST_UTIL.SET_PROCESS_FLAG
39754: (p_table_name => 'MSC_ST_DEMANDS',
39755: p_instance_id => v_instance_id,
39756: p_instance_code => v_instance_code,
39757: p_process_flag => G_VALID,
39758: p_error_text => lv_error_text,

Line 39767: (p_table_name => 'MSC_ST_DEMANDS',

39763: RAISE ex_logging_err;
39764: END IF;
39765:
39766: lv_return:= MSC_ST_UTIL.LOG_ERROR
39767: (p_table_name => 'MSC_ST_DEMANDS',
39768: p_instance_code => v_instance_code,
39769: p_row => lv_column_names,
39770: p_severity => G_SEV_ERROR,
39771: p_message_text => NULL,

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

49632:
49633:
49634: /*==========================================================================+
49635: | DESCRIPTION : This procedure is called for validating demand originating |
49636: | from WIP Supply and is for msc_st_demands table. Following |
49637: | types are considered for the validation. |
49638: | Demand origination Origination Type |
49639: | ------------------------------------------------- |
49640: | Std Job Demand 2 |

Line 49659: lv_batch_id msc_st_demands.batch_id%TYPE;

49655: lv_error_text VARCHAR2(250);
49656: lv_where_str VARCHAR2(5000);
49657: lv_sql_stmt VARCHAR2(5000);
49658: lv_cursor_stmt VARCHAR2(5000);
49659: lv_batch_id msc_st_demands.batch_id%TYPE;
49660: lv_message_text msc_errors.error_text%TYPE;
49661:
49662: ex_logging_err EXCEPTION;
49663:

Line 49666: FROM msc_st_demands

49662: ex_logging_err EXCEPTION;
49663:
49664: CURSOR c1(p_batch_id NUMBER) IS
49665: SELECT rowid
49666: FROM msc_st_demands
49667: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
49668: AND origination_type IN (2,3,25)
49669: AND batch_id = p_batch_id
49670: AND sr_instance_code = v_instance_code;

Line 49674: FROM msc_st_demands

49670: AND sr_instance_code = v_instance_code;
49671:
49672: CURSOR c2(p_batch_id NUMBER) IS
49673: SELECT max(rowid)
49674: FROM msc_st_demands
49675: WHERE process_flag = G_IN_PROCESS
49676: AND sr_instance_code = v_instance_code
49677: AND batch_id = p_batch_id
49678: AND origination_type IN (2,3) -- Not for flow schedule

Line 49707: 'UPDATE msc_st_demands msd1'

49703:
49704: v_sql_stmt := 01;
49705:
49706: lv_sql_stmt :=
49707: 'UPDATE msc_st_demands msd1'
49708: ||' SET process_flag = '||G_ERROR_FLG||','
49709: ||' error_text = '||''''||lv_message_text||''''
49710: ||' WHERE message_id < (SELECT MAX(message_id)'
49711: ||' FROM msc_st_demands msd2'

Line 49711: ||' FROM msc_st_demands msd2'

49707: 'UPDATE msc_st_demands msd1'
49708: ||' SET process_flag = '||G_ERROR_FLG||','
49709: ||' error_text = '||''''||lv_message_text||''''
49710: ||' WHERE message_id < (SELECT MAX(message_id)'
49711: ||' FROM msc_st_demands msd2'
49712: ||' WHERE msd2.sr_instance_code '
49713: ||' = msd1.sr_instance_code '
49714: ||' AND msd2.organization_code '
49715: ||' = msd1.organization_code '

Line 49752: 'UPDATE msc_st_demands msd1'

49748:
49749: v_sql_stmt := 02;
49750:
49751: lv_sql_stmt :=
49752: 'UPDATE msc_st_demands msd1'
49753: ||' SET process_flag = '||G_ERROR_FLG||','
49754: ||' error_text = '||''''||lv_message_text||''''
49755: ||' WHERE EXISTS( SELECT 1 '
49756: ||' FROM msc_st_demands msd2'

Line 49756: ||' FROM msc_st_demands msd2'

49752: 'UPDATE msc_st_demands msd1'
49753: ||' SET process_flag = '||G_ERROR_FLG||','
49754: ||' error_text = '||''''||lv_message_text||''''
49755: ||' WHERE EXISTS( SELECT 1 '
49756: ||' FROM msc_st_demands msd2'
49757: ||' WHERE msd2.sr_instance_code '
49758: ||' = msd1.sr_instance_code '
49759: ||' AND msd2.organization_code '
49760: ||' = msd1.organization_code '

Line 49815: ' UPDATE msc_st_demands '

49811: FROM DUAL;
49812:
49813: v_sql_stmt := 04;
49814: lv_sql_stmt :=
49815: ' UPDATE msc_st_demands '
49816: ||' SET batch_id = :lv_batch_id'
49817: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
49818: ||' AND sr_instance_code = :v_instance_code'
49819: ||' AND origination_type IN (2,3,25)'

Line 49839: UPDATE msc_st_demands

49835: CLOSE c1;
49836:
49837: v_sql_stmt := 03;
49838: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
49839: UPDATE msc_st_demands
49840: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
49841: refresh_id = v_refresh_id,
49842: last_update_date = v_current_date,
49843: last_updated_by = v_current_user,

Line 49840: SET st_transaction_id = msc_st_demands_s.NEXTVAL,

49836:
49837: v_sql_stmt := 03;
49838: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
49839: UPDATE msc_st_demands
49840: SET st_transaction_id = msc_st_demands_s.NEXTVAL,
49841: refresh_id = v_refresh_id,
49842: last_update_date = v_current_date,
49843: last_updated_by = v_current_user,
49844: creation_date = v_current_date,

Line 49869: (p_table_name => 'MSC_ST_DEMANDS',

49865: lv_where_str :=
49866: ' AND NVL(deleted_flag,'||NULL_VALUE||') NOT IN(1,2)';
49867:
49868: lv_return := MSC_ST_UTIL.LOG_ERROR
49869: (p_table_name => 'MSC_ST_DEMANDS',
49870: p_instance_code => v_instance_code,
49871: p_row => lv_column_names,
49872: p_severity => G_SEV_WARNING,
49873: p_message_text => lv_message_text,

Line 49900: (p_table_name => 'MSC_ST_DEMANDS',

49896: END IF;
49897:
49898: --Derive Organization_id
49899: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
49900: (p_table_name => 'MSC_ST_DEMANDS',
49901: p_org_partner_name => 'ORGANIZATION_CODE',
49902: p_org_partner_id => 'ORGANIZATION_ID',
49903: p_instance_code => v_instance_code,
49904: p_partner_type => G_ORGANIZATION,

Line 49931: (p_table_name => 'MSC_ST_DEMANDS',

49927: END IF;
49928:
49929: --Derive Inventory_item_id
49930: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
49931: (p_table_name => 'MSC_ST_DEMANDS',
49932: p_item_col_name => 'ITEM_NAME',
49933: p_item_col_id => 'INVENTORY_ITEM_ID',
49934: p_instance_id => v_instance_id,
49935: p_instance_code => v_instance_code,

Line 49962: (p_table_name => 'MSC_ST_DEMANDS',

49958: END IF;
49959:
49960: --Derive Using_assembly_item_id
49961: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
49962: (p_table_name => 'MSC_ST_DEMANDS',
49963: p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
49964: p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
49965: p_instance_id => v_instance_id,
49966: p_instance_code => v_instance_code,

Line 49982: 'UPDATE msc_st_demands msd'

49978: -- Derive WIP_ENTITY_ID
49979: v_sql_stmt := 04;
49980:
49981: lv_sql_stmt :=
49982: 'UPDATE msc_st_demands msd'
49983: ||' SET wip_entity_id = ( SELECT local_id '
49984: ||' FROM msc_local_id_supply mlid'
49985: ||' WHERE mlid.char1 = msd.sr_instance_code'
49986: ||' AND NVL(mlid.char2,'||''''||NULL_CHAR||''''||') '

Line 50013: p_token_value3 => 'MSC_ST_DEMANDS' );

50009: ||' ORGANIZATION_CODE,WIP_ENTITY_NAME',
50010: p_token2 => 'MASTER_TABLE',
50011: p_token_value2 => 'MSC_ST_SUPPLIES',
50012: p_token3 => 'CHILD_TABLE' ,
50013: p_token_value3 => 'MSC_ST_DEMANDS' );
50014:
50015: IF lv_return <> 0 THEN
50016: RAISE ex_logging_err;
50017: END IF;

Line 50023: 'UPDATE msc_st_demands '

50019: -- Error out records where WIP_ENTITY_ID is NULL;
50020:
50021: v_sql_stmt := 05;
50022: lv_sql_stmt :=
50023: 'UPDATE msc_st_demands '
50024: ||' SET process_flag = '||G_ERROR_FLG||','
50025: ||' error_text = '||''''||lv_message_text||''''
50026: ||' WHERE NVL(wip_entity_id,'||NULL_VALUE||') ='||NULL_VALUE
50027: ||' AND process_flag = '||G_IN_PROCESS

Line 50058: 'UPDATE msc_st_demands '

50054: -- Error out records where using_assembly_demand_date is NULL
50055:
50056: v_sql_stmt := 06;
50057: lv_sql_stmt :=
50058: 'UPDATE msc_st_demands '
50059: ||' SET process_flag = '||G_ERROR_FLG||','
50060: ||' error_text = '||''''||lv_message_text||''''
50061: ||' WHERE (NVL(using_requirement_quantity,'||NULL_VALUE||')='||NULL_VALUE
50062: ||' OR NVL(using_assembly_demand_date,SYSDATE-36500) = SYSDATE-36500 )'

Line 50079: ' UPDATE msc_st_demands'

50075: -- Update using_assembly_item_id = inventory_item_id
50076:
50077: v_sql_stmt := 07;
50078: lv_sql_stmt :=
50079: ' UPDATE msc_st_demands'
50080: ||' SET using_assembly_item_id = inventory_item_id'
50081: ||' WHERE process_flag = '||G_IN_PROCESS
50082: ||' AND NVL(using_assembly_item_id,'||NULL_VALUE||') = '||NULL_VALUE
50083: ||' AND deleted_flag = '||SYS_NO

Line 50099: ' UPDATE msc_st_demands'

50095: -- Update order_number = wip_entity_name
50096:
50097: v_sql_stmt := 08;
50098: lv_sql_stmt :=
50099: ' UPDATE msc_st_demands'
50100: ||' SET order_number = wip_entity_name'
50101: ||' WHERE process_flag = '||G_IN_PROCESS
50102: ||' AND deleted_flag = '||SYS_NO
50103: ||' AND process_flag = '||G_IN_PROCESS

Line 50118: ' UPDATE msc_st_demands'

50114: -- Update disposition_id = wip_entity_id
50115:
50116: v_sql_stmt := 09;
50117: lv_sql_stmt :=
50118: ' UPDATE msc_st_demands'
50119: ||' SET disposition_id = wip_entity_id'
50120: ||' WHERE process_flag = '||G_IN_PROCESS
50121: ||' AND NVL(wip_entity_id,'||NULL_VALUE||') = '||NULL_VALUE
50122: ||' AND deleted_flag = '||SYS_NO

Line 50140: ' UPDATE msc_st_demands'

50136: -- This is not reqd for flow schedule
50137:
50138: v_sql_stmt := 10;
50139: lv_sql_stmt :=
50140: ' UPDATE msc_st_demands'
50141: ||' SET mps_date_required = using_assembly_demand_date'
50142: ||' WHERE process_flag = '||G_IN_PROCESS
50143: ||' AND NVL(mps_date_required,SYSDATE-36500) = SYSDATE-36500'
50144: ||' AND process_flag = '||G_IN_PROCESS

Line 50178: (p_table_name => 'MSC_ST_DEMANDS',

50174: ||' = '||''''||NULL_CHAR||''''
50175: ||' AND origination_type IN (2,3)';
50176:
50177: lv_return := MSC_ST_UTIL.LOG_ERROR
50178: (p_table_name => 'MSC_ST_DEMANDS',
50179: p_instance_code => v_instance_code,
50180: p_row => lv_column_names,
50181: p_severity => G_SEV_WARNING,
50182: p_message_text => lv_message_text,

Line 50216: (p_table_name => 'MSC_ST_DEMANDS',

50212: ' AND NVL(operation_effectivity_date,SYSDATE-36500 ) = SYSDATE-36500 '
50213: ||' AND origination_type IN (2,3)' ;
50214:
50215: lv_return := MSC_ST_UTIL.LOG_ERROR
50216: (p_table_name => 'MSC_ST_DEMANDS',
50217: p_instance_code => v_instance_code,
50218: p_row => lv_column_names,
50219: p_severity => G_SEV_WARNING,
50220: p_message_text => lv_message_text,

Line 50236: ' UPDATE msc_st_demands'

50232: -- If routing_name is is NULL populate the item_name in routing name
50233:
50234: v_sql_stmt := 11;
50235: lv_sql_stmt :=
50236: ' UPDATE msc_st_demands'
50237: ||' SET routing_name = nvl(USING_ASSEMBLY_ITEM_NAME,item_name)' /* bug 3768813 */
50238: ||' WHERE process_flag = '||G_IN_PROCESS
50239: ||' AND NVL(routing_name,'||''''||NULL_CHAR||''''||')'
50240: ||' = '||''''||NULL_CHAR||''''

Line 50257: (p_table_name => 'MSC_ST_DEMANDS',

50253:
50254: -- Derive the ROUTING_SEQUENCE_ID from LOCAL ID table
50255:
50256: lv_return :=msc_st_util.derive_routing_sequence_id
50257: (p_table_name => 'MSC_ST_DEMANDS',
50258: p_rtg_col_name => 'ROUTING_NAME',
50259: p_rtg_col_id =>'ROUTING_SEQUENCE_ID',
50260: p_instance_code => v_instance_code,
50261: p_batch_id => lv_batch_id,

Line 50272: 'update msc_st_demands msd'

50268: end if;
50269:
50270: v_sql_stmt := 11;
50271: lv_sql_stmt:=
50272: 'update msc_st_demands msd'
50273: ||' set operation_seq_num = '
50274: ||' (select operation_seq_num '
50275: ||' from msc_routing_operations mro '
50276: ||' where mro.routing_sequence_id = msd.routing_sequence_id and '

Line 50297: 'UPDATE msc_st_demands msd'

50293: -- Derive operation seq num from local id table
50294:
50295: v_sql_stmt := 12;
50296: lv_sql_stmt:=
50297: 'UPDATE msc_st_demands msd'
50298: ||' SET operation_seq_num= (SELECT number1'
50299: ||' FROM msc_local_id_setup mlis'
50300: ||' WHERE mlis.char1 = msd.sr_instance_code'
50301: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '

Line 50339: 'UPDATE msc_st_demands '

50335:
50336: v_sql_stmt := 13;
50337:
50338: lv_sql_stmt :=
50339: 'UPDATE msc_st_demands '
50340: ||' SET process_flag ='||G_ERROR_FLG||','
50341: ||' error_text = '||''''||lv_message_text||''''
50342: ||' WHERE NVL(operation_seq_num,'||NULL_VALUE||') = '||NULL_VALUE
50343: ||' AND deleted_flag ='||SYS_YES

Line 50371: (p_table_name => 'MSC_ST_DEMANDS',

50367: END IF;
50368:
50369: --Derive Project Id.
50370: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
50371: (p_table_name => 'MSC_ST_DEMANDS',
50372: p_proj_col_name => 'PROJECT_NUMBER',
50373: p_proj_task_col_id => 'PROJECT_ID',
50374: p_instance_code => v_instance_code,
50375: p_entity_name => 'PROJECT_ID',

Line 50405: (p_table_name => 'MSC_ST_DEMANDS',

50401: END IF;
50402:
50403: --Derive Task Id.
50404: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
50405: (p_table_name => 'MSC_ST_DEMANDS',
50406: p_proj_col_name => 'PROJECT_NUMBER',
50407: p_proj_task_col_id => 'TASK_ID',
50408: p_instance_code => v_instance_code,
50409: p_entity_name => 'TASK_ID',

Line 50443: (p_table_name => 'MSC_ST_DEMANDS',

50439: lv_where_str := ' AND NVL(demand_type,'||NULL_VALUE||') <> '||G_DEMAND_TYPE
50440: ||' AND origination_type IN (2,3) AND deleted_flag ='||SYS_NO ;
50441:
50442: lv_return := MSC_ST_UTIL.LOG_ERROR
50443: (p_table_name => 'MSC_ST_DEMANDS',
50444: p_instance_code => v_instance_code,
50445: p_row => lv_column_names,
50446: p_severity => G_SEV_WARNING,
50447: p_message_text => lv_message_text,

Line 50466: (p_table_name => 'MSC_ST_DEMANDS',

50462: lv_where_str := ' AND NVL(demand_type,'||NULL_VALUE||') <> 2'
50463: ||' AND origination_type = 25 AND deleted_flag ='||SYS_NO ;
50464:
50465: lv_return := MSC_ST_UTIL.LOG_ERROR
50466: (p_table_name => 'MSC_ST_DEMANDS',
50467: p_instance_code => v_instance_code,
50468: p_row => lv_column_names,
50469: p_severity => G_SEV_WARNING,
50470: p_message_text => lv_message_text,

Line 50504: (p_table_name => 'MSC_ST_DEMANDS',

50500: lv_where_str := ' AND NVL(wip_supply_type,'||NULL_VALUE||') NOT IN (1,2,3,4,5,6,7)'
50501: ||' AND origination_type IN (2,3) AND deleted_flag ='||SYS_NO ;
50502:
50503: lv_return := MSC_ST_UTIL.LOG_ERROR
50504: (p_table_name => 'MSC_ST_DEMANDS',
50505: p_instance_code => v_instance_code,
50506: p_row => lv_column_names,
50507: p_severity => G_SEV_WARNING,
50508: p_message_text => lv_message_text,

Line 50542: (p_table_name => 'MSC_ST_DEMANDS',

50538: lv_where_str := ' AND NVL(wip_status_code,'||NULL_VALUE||') NOT IN (1,3,4,6,7,12)'
50539: ||' AND origination_type IN (2,3,25)AND deleted_flag ='||SYS_NO ;
50540:
50541: lv_return := MSC_ST_UTIL.LOG_ERROR
50542: (p_table_name => 'MSC_ST_DEMANDS',
50543: p_instance_code => v_instance_code,
50544: p_row => lv_column_names,
50545: p_severity => G_SEV_WARNING,
50546: p_message_text => lv_message_text,

Line 50564: pEntityName => 'MSC_ST_DEMANDS',

50560: (ERRBUF => lv_error_text,
50561: RETCODE => lv_return,
50562: pBatchID => lv_batch_id,
50563: pInstanceCode => v_instance_code,
50564: pEntityName => 'MSC_ST_DEMANDS',
50565: pInstanceID => v_instance_id);
50566:
50567: IF NVL(lv_return,0) <> 0 THEN
50568: RAISE ex_logging_err;

Line 50584: UPDATE msc_st_demands

50580: -- SELECT msc_st_operation_sequence_id_s.NEXTVAL
50581: -- INTO lv_local_id
50582: -- FROM DUAL;
50583:
50584: UPDATE msc_st_demands
50585: SET operation_seq_num =
50586: to_number(decode(length(rtrim(operation_seq_code,'0123456789')),
50587: NULL,operation_seq_code,'1'))
50588: WHERE rowid = lb_rowid(j);

Line 50644: FROM msc_st_demands

50640: v_current_date,
50641: v_current_user,
50642: v_current_date,
50643: v_current_user
50644: FROM msc_st_demands
50645: WHERE rowid = lb_rowid(j) ;
50646:
50647: END IF;
50648: CLOSE c2;

Line 50654: 'UPDATE msc_st_demands msd'

50650: -- Update operation seq num from local id table
50651:
50652: v_sql_stmt := 12;
50653: lv_sql_stmt:=
50654: 'UPDATE msc_st_demands msd'
50655: ||' SET operation_seq_num= (SELECT number1'
50656: ||' FROM msc_local_id_setup mlis'
50657: ||' WHERE mlis.char1 = msd.sr_instance_code'
50658: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '

Line 50680: (p_table_name => 'MSC_ST_DEMANDS',

50676: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code,lv_batch_id;
50677:
50678:
50679: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
50680: (p_table_name => 'MSC_ST_DEMANDS',
50681: p_instance_id => v_instance_id,
50682: p_instance_code => v_instance_code,
50683: p_process_flag => G_VALID,
50684: p_error_text => lv_error_text,

Line 50693: (p_table_name => 'MSC_ST_DEMANDS',

50689: RAISE ex_logging_err;
50690: END IF;
50691:
50692: lv_return := MSC_ST_UTIL.LOG_ERROR
50693: (p_table_name => 'MSC_ST_DEMANDS',
50694: p_instance_code => v_instance_code,
50695: p_row => lv_column_names,
50696: p_severity => G_SEV_ERROR,
50697: p_message_text => NULL,

Line 54588: 'UPDATE msc_st_demands msd1'

54584: --For MDS and Forecast (origination_type 8 and 29)
54585:
54586: -- MDS
54587: lv_sql_stmt :=
54588: 'UPDATE msc_st_demands msd1'
54589: ||' SET process_flag = '||G_ERROR_FLG||','
54590: ||' error_text = '||''''||lv_message_text||''''
54591: ||' WHERE message_id < (SELECT MAX(message_id)'
54592: ||' FROM msc_st_demands msd2'

Line 54592: ||' FROM msc_st_demands msd2'

54588: 'UPDATE msc_st_demands msd1'
54589: ||' SET process_flag = '||G_ERROR_FLG||','
54590: ||' error_text = '||''''||lv_message_text||''''
54591: ||' WHERE message_id < (SELECT MAX(message_id)'
54592: ||' FROM msc_st_demands msd2'
54593: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code '
54594: ||' AND nvl(msd2.using_assembly_demand_date,SYSDATE-365000 ) = nvl(msd1.using_assembly_demand_date,SYSDATE-365000 ) '
54595: ||' AND msd2.item_name = msd1.item_name'
54596: ||' AND msd2.organization_code = msd1.organization_code '

Line 54618: 'UPDATE msc_st_demands msd1'

54614:
54615: -- Forecast
54616:
54617: lv_sql_stmt :=
54618: 'UPDATE msc_st_demands msd1'
54619: ||' SET process_flag = '||G_ERROR_FLG||','
54620: ||' error_text = '||''''||lv_message_text||''''
54621: ||' WHERE message_id < (SELECT MAX(message_id)'
54622: ||' FROM msc_st_demands msd2'

Line 54622: ||' FROM msc_st_demands msd2'

54618: 'UPDATE msc_st_demands msd1'
54619: ||' SET process_flag = '||G_ERROR_FLG||','
54620: ||' error_text = '||''''||lv_message_text||''''
54621: ||' WHERE message_id < (SELECT MAX(message_id)'
54622: ||' FROM msc_st_demands msd2'
54623: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code '
54624: ||' AND msd2.schedule_date = msd1.schedule_date '
54625: ||' AND msd2.item_name = msd1.item_name'
54626: ||' AND msd2.forecast_designator = msd1.forecast_designator '

Line 54661: 'UPDATE msc_st_demands msd1'

54657: -- Forecast
54658: v_sql_stmt := 12;
54659:
54660: lv_sql_stmt :=
54661: 'UPDATE msc_st_demands msd1'
54662: ||' SET process_flag = '||G_ERROR_FLG||','
54663: ||' error_text = '||''''||lv_message_text||''''
54664: ||' WHERE EXISTS( SELECT 1 '
54665: ||' FROM msc_st_demands msd2'

Line 54665: ||' FROM msc_st_demands msd2'

54661: 'UPDATE msc_st_demands msd1'
54662: ||' SET process_flag = '||G_ERROR_FLG||','
54663: ||' error_text = '||''''||lv_message_text||''''
54664: ||' WHERE EXISTS( SELECT 1 '
54665: ||' FROM msc_st_demands msd2'
54666: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code '
54667: ||' AND msd2.organization_code = msd1.organization_code '
54668: ||' AND NVL(msd2.company_name,'||''''||NULL_CHAR||''''||') '
54669: ||' = NVL(msd1.company_name,'||''''||NULL_CHAR||''''||') '

Line 54693: 'UPDATE msc_st_demands msd1'

54689: -- MDS
54690: v_sql_stmt := 12;
54691:
54692: lv_sql_stmt :=
54693: 'UPDATE msc_st_demands msd1'
54694: ||' SET process_flag = '||G_ERROR_FLG||','
54695: ||' error_text = '||''''||lv_message_text||''''
54696: ||' WHERE EXISTS( SELECT 1 '
54697: ||' FROM msc_st_demands msd2'

Line 54697: ||' FROM msc_st_demands msd2'

54693: 'UPDATE msc_st_demands msd1'
54694: ||' SET process_flag = '||G_ERROR_FLG||','
54695: ||' error_text = '||''''||lv_message_text||''''
54696: ||' WHERE EXISTS( SELECT 1 '
54697: ||' FROM msc_st_demands msd2'
54698: ||' WHERE msd2.sr_instance_code '
54699: ||' = msd1.sr_instance_code '
54700: ||' AND msd2.organization_code '
54701: ||' = msd1.organization_code '

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

55736: BEGIN
55737: --- ===== Enter the ASCP tables to be set here ======
55738:
55739: IF v_mat_dmd_enabled = SYS_YES OR v_iro_enabled = SYS_YES or v_ero_enabled = SYS_YES THEN
55740: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_DEMANDS', p_erp_enabled => 'Y');
55741: IF lv_count > 0 Then
55742: prec.mds_flag:= SYS_YES;
55743: prec.forecast_flag:= SYS_YES;
55744: prec.wip_flag:= SYS_YES;

Line 59026: cursor c2 is SELECT rowid FROM msc_st_demands

59022: lb_batch NumTblTyp := NumTblTyp(0);
59023: lv_batch_id PLS_INTEGER;
59024: lv_batch_start PLS_INTEGER;
59025: lv_batch_last PLS_INTEGER;
59026: cursor c2 is SELECT rowid FROM msc_st_demands
59027: WHERE PROCESS_FLAG IN (G_IN_PROCESS,G_ERROR_FLG)
59028: AND origination_type IN (7,8,29)
59029: AND NVL(batch_id,NULL_VALUE) = NULL_VALUE
59030: AND sr_instance_code = v_instance_code;

Line 59055: UPDATE msc_st_demands

59051:
59052: lv_batch_last := lv_batch_id;
59053:
59054: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
59055: UPDATE msc_st_demands
59056: SET batch_id = lb_batch(j)
59057: WHERE rowid = lb_rowid(j);
59058:
59059: commit;