eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_SUPPLIES

Line 572: 'create index MSC_ST_SUPPLIES_N1_'||v_instance_code

568: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
569: application_short_name => 'MSC',
570: statement_type => AD_DDL.CREATE_INDEX,
571: statement =>
572: 'create index MSC_ST_SUPPLIES_N1_'||v_instance_code
573: ||' on MSC_ST_SUPPLIES '
574: ||'(SR_INSTANCE_CODE, ORDER_NUMBER, PURCH_LINE_NUM, ORDER_TYPE, COMPANY_NAME) '
575: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
576: object_name =>'MSC_ST_SUPPLIES_N1_'||v_instance_code);

Line 573: ||' on MSC_ST_SUPPLIES '

569: application_short_name => 'MSC',
570: statement_type => AD_DDL.CREATE_INDEX,
571: statement =>
572: 'create index MSC_ST_SUPPLIES_N1_'||v_instance_code
573: ||' on MSC_ST_SUPPLIES '
574: ||'(SR_INSTANCE_CODE, ORDER_NUMBER, PURCH_LINE_NUM, ORDER_TYPE, COMPANY_NAME) '
575: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
576: object_name =>'MSC_ST_SUPPLIES_N1_'||v_instance_code);
577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N1_'||v_instance_code);

Line 576: object_name =>'MSC_ST_SUPPLIES_N1_'||v_instance_code);

572: 'create index MSC_ST_SUPPLIES_N1_'||v_instance_code
573: ||' on MSC_ST_SUPPLIES '
574: ||'(SR_INSTANCE_CODE, ORDER_NUMBER, PURCH_LINE_NUM, ORDER_TYPE, COMPANY_NAME) '
575: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
576: object_name =>'MSC_ST_SUPPLIES_N1_'||v_instance_code);
577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N1_'||v_instance_code);
578:
579: EXCEPTION
580: WHEN OTHERS THEN

Line 577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N1_'||v_instance_code);

573: ||' on MSC_ST_SUPPLIES '
574: ||'(SR_INSTANCE_CODE, ORDER_NUMBER, PURCH_LINE_NUM, ORDER_TYPE, COMPANY_NAME) '
575: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
576: object_name =>'MSC_ST_SUPPLIES_N1_'||v_instance_code);
577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N1_'||v_instance_code);
578:
579: EXCEPTION
580: WHEN OTHERS THEN
581: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Error creating Index MSC_ST_SUPPLIES_N1_'||v_instance_code ||'. Error:'||substr(SQLERRM,1,240));

Line 581: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Error creating Index MSC_ST_SUPPLIES_N1_'||v_instance_code ||'. Error:'||substr(SQLERRM,1,240));

577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N1_'||v_instance_code);
578:
579: EXCEPTION
580: WHEN OTHERS THEN
581: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Error creating Index MSC_ST_SUPPLIES_N1_'||v_instance_code ||'. Error:'||substr(SQLERRM,1,240));
582: END;
583:
584: BEGIN
585: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 589: 'create index MSC_ST_SUPPLIES_N2_'||v_instance_code

585: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
586: application_short_name => 'MSC',
587: statement_type => AD_DDL.CREATE_INDEX,
588: statement =>
589: 'create index MSC_ST_SUPPLIES_N2_'||v_instance_code
590: ||' on MSC_ST_SUPPLIES '
591: ||'(sr_instance_code, schedule_line_num, schedule_designator, organization_code, order_type, company_name) '
592: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
593: object_name =>'MSC_ST_SUPPLIES_N2_'||v_instance_code);

Line 590: ||' on MSC_ST_SUPPLIES '

586: application_short_name => 'MSC',
587: statement_type => AD_DDL.CREATE_INDEX,
588: statement =>
589: 'create index MSC_ST_SUPPLIES_N2_'||v_instance_code
590: ||' on MSC_ST_SUPPLIES '
591: ||'(sr_instance_code, schedule_line_num, schedule_designator, organization_code, order_type, company_name) '
592: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
593: object_name =>'MSC_ST_SUPPLIES_N2_'||v_instance_code);
594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N2_'||v_instance_code);

Line 593: object_name =>'MSC_ST_SUPPLIES_N2_'||v_instance_code);

589: 'create index MSC_ST_SUPPLIES_N2_'||v_instance_code
590: ||' on MSC_ST_SUPPLIES '
591: ||'(sr_instance_code, schedule_line_num, schedule_designator, organization_code, order_type, company_name) '
592: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
593: object_name =>'MSC_ST_SUPPLIES_N2_'||v_instance_code);
594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N2_'||v_instance_code);
595:
596: EXCEPTION
597: WHEN OTHERS THEN

Line 594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N2_'||v_instance_code);

590: ||' on MSC_ST_SUPPLIES '
591: ||'(sr_instance_code, schedule_line_num, schedule_designator, organization_code, order_type, company_name) '
592: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
593: object_name =>'MSC_ST_SUPPLIES_N2_'||v_instance_code);
594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N2_'||v_instance_code);
595:
596: EXCEPTION
597: WHEN OTHERS THEN
598: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Error creating Index MSC_ST_SUPPLIES_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

Line 598: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Error creating Index MSC_ST_SUPPLIES_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Created Index MSC_ST_SUPPLIES_N2_'||v_instance_code);
595:
596: EXCEPTION
597: WHEN OTHERS THEN
598: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Error creating Index MSC_ST_SUPPLIES_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
599: END;
600:
601: BEGIN
602: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 606: 'create index MSC_ST_SUPPLIES_N3_'||v_instance_code

602: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
603: application_short_name => 'MSC',
604: statement_type => AD_DDL.CREATE_INDEX,
605: statement =>
606: 'create index MSC_ST_SUPPLIES_N3_'||v_instance_code
607: ||' on MSC_ST_SUPPLIES '
608: ||'(sr_instance_code, item_name, organization_code, order_type, company_name) '
609: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
610: object_name =>'MSC_ST_SUPPLIES_N3_'||v_instance_code);

Line 607: ||' on MSC_ST_SUPPLIES '

603: application_short_name => 'MSC',
604: statement_type => AD_DDL.CREATE_INDEX,
605: statement =>
606: 'create index MSC_ST_SUPPLIES_N3_'||v_instance_code
607: ||' on MSC_ST_SUPPLIES '
608: ||'(sr_instance_code, item_name, organization_code, order_type, company_name) '
609: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
610: object_name =>'MSC_ST_SUPPLIES_N3_'||v_instance_code);
611:

Line 610: object_name =>'MSC_ST_SUPPLIES_N3_'||v_instance_code);

606: 'create index MSC_ST_SUPPLIES_N3_'||v_instance_code
607: ||' on MSC_ST_SUPPLIES '
608: ||'(sr_instance_code, item_name, organization_code, order_type, company_name) '
609: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
610: object_name =>'MSC_ST_SUPPLIES_N3_'||v_instance_code);
611:
612: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N3_'||v_instance_code);
613:
614:

Line 612: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N3_'||v_instance_code);

608: ||'(sr_instance_code, item_name, organization_code, order_type, company_name) '
609: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
610: object_name =>'MSC_ST_SUPPLIES_N3_'||v_instance_code);
611:
612: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N3_'||v_instance_code);
613:
614:
615: EXCEPTION
616: WHEN OTHERS THEN

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

613:
614:
615: EXCEPTION
616: WHEN OTHERS THEN
617: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SUPPLIES_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
618: END;
619:
620: BEGIN
621: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 625: 'create index MSC_ST_SUPPLIES_N4_'||v_instance_code

621: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
622: application_short_name => 'MSC',
623: statement_type => AD_DDL.CREATE_INDEX,
624: statement =>
625: 'create index MSC_ST_SUPPLIES_N4_'||v_instance_code
626: ||' on MSC_ST_SUPPLIES '
627: ||'(sr_instance_code,wip_entity_name, organization_code,order_type, company_name) '
628: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
629: object_name =>'MSC_ST_SUPPLIES_N4_'||v_instance_code);

Line 626: ||' on MSC_ST_SUPPLIES '

622: application_short_name => 'MSC',
623: statement_type => AD_DDL.CREATE_INDEX,
624: statement =>
625: 'create index MSC_ST_SUPPLIES_N4_'||v_instance_code
626: ||' on MSC_ST_SUPPLIES '
627: ||'(sr_instance_code,wip_entity_name, organization_code,order_type, company_name) '
628: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
629: object_name =>'MSC_ST_SUPPLIES_N4_'||v_instance_code);
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N4_'||v_instance_code);

Line 629: object_name =>'MSC_ST_SUPPLIES_N4_'||v_instance_code);

625: 'create index MSC_ST_SUPPLIES_N4_'||v_instance_code
626: ||' on MSC_ST_SUPPLIES '
627: ||'(sr_instance_code,wip_entity_name, organization_code,order_type, company_name) '
628: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
629: object_name =>'MSC_ST_SUPPLIES_N4_'||v_instance_code);
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N4_'||v_instance_code);
631:
632:
633: EXCEPTION

Line 630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N4_'||v_instance_code);

626: ||' on MSC_ST_SUPPLIES '
627: ||'(sr_instance_code,wip_entity_name, organization_code,order_type, company_name) '
628: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
629: object_name =>'MSC_ST_SUPPLIES_N4_'||v_instance_code);
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N4_'||v_instance_code);
631:
632:
633: EXCEPTION
634: WHEN OTHERS THEN

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

631:
632:
633: EXCEPTION
634: WHEN OTHERS THEN
635: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SUPPLIES_N4_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
636: END;
637:
638: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIES', v_instance_id, -1);
639:

Line 638: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIES', v_instance_id, -1);

634: WHEN OTHERS THEN
635: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SUPPLIES_N4_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
636: END;
637:
638: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIES', v_instance_id, -1);
639:
640: END IF;
641:
642: IF v_mat_dmd_enabled = SYS_YES THEN

Line 1712: 'create index MSC_ST_SUPPLIES_N5_'||v_instance_code

1708: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1709: application_short_name => 'MSC',
1710: statement_type => AD_DDL.CREATE_INDEX,
1711: statement =>
1712: 'create index MSC_ST_SUPPLIES_N5_'||v_instance_code
1713: ||' on MSC_ST_SUPPLIES '
1714: ||' (batch_id, order_type, sr_instance_code, deleted_flag) '
1715: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1716: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);

Line 1713: ||' on MSC_ST_SUPPLIES '

1709: application_short_name => 'MSC',
1710: statement_type => AD_DDL.CREATE_INDEX,
1711: statement =>
1712: 'create index MSC_ST_SUPPLIES_N5_'||v_instance_code
1713: ||' on MSC_ST_SUPPLIES '
1714: ||' (batch_id, order_type, sr_instance_code, deleted_flag) '
1715: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1716: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);
1717:

Line 1716: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);

1712: 'create index MSC_ST_SUPPLIES_N5_'||v_instance_code
1713: ||' on MSC_ST_SUPPLIES '
1714: ||' (batch_id, order_type, sr_instance_code, deleted_flag) '
1715: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1716: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);
1717:
1718: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N5_'||v_instance_code);
1719:
1720: EXCEPTION

Line 1718: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N5_'||v_instance_code);

1714: ||' (batch_id, order_type, sr_instance_code, deleted_flag) '
1715: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1716: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);
1717:
1718: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N5_'||v_instance_code);
1719:
1720: EXCEPTION
1721: WHEN OTHERS THEN
1722: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SUPPLIES_N5_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

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

1718: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_SUPPLIES_N5_'||v_instance_code);
1719:
1720: EXCEPTION
1721: WHEN OTHERS THEN
1722: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SUPPLIES_N5_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1723: END;
1724:
1725: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIES', v_instance_id, -1);
1726:

Line 1725: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIES', v_instance_id, -1);

1721: WHEN OTHERS THEN
1722: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SUPPLIES_N5_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1723: END;
1724:
1725: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIES', v_instance_id, -1);
1726:
1727: ELSIF p_batch_index = 'DEMAND' THEN
1728:
1729: BEGIN

Line 1844: 'drop index MSC_ST_SUPPLIES_N1_'||v_instance_code,

1840: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1841: application_short_name => 'MSC',
1842: statement_type => AD_DDL.DROP_INDEX,
1843: statement =>
1844: 'drop index MSC_ST_SUPPLIES_N1_'||v_instance_code,
1845: object_name => 'MSC_ST_SUPPLIES_N1_'||v_instance_code);
1846:
1847: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N1_'||v_instance_code);
1848:

Line 1845: object_name => 'MSC_ST_SUPPLIES_N1_'||v_instance_code);

1841: application_short_name => 'MSC',
1842: statement_type => AD_DDL.DROP_INDEX,
1843: statement =>
1844: 'drop index MSC_ST_SUPPLIES_N1_'||v_instance_code,
1845: object_name => 'MSC_ST_SUPPLIES_N1_'||v_instance_code);
1846:
1847: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N1_'||v_instance_code);
1848:
1849: EXCEPTION

Line 1847: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N1_'||v_instance_code);

1843: statement =>
1844: 'drop index MSC_ST_SUPPLIES_N1_'||v_instance_code,
1845: object_name => 'MSC_ST_SUPPLIES_N1_'||v_instance_code);
1846:
1847: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N1_'||v_instance_code);
1848:
1849: EXCEPTION
1850: WHEN OTHERS THEN
1851: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

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

1847: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N1_'||v_instance_code);
1848:
1849: EXCEPTION
1850: WHEN OTHERS THEN
1851: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1852: END;
1853:
1854: BEGIN
1855: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 1859: 'drop index MSC_ST_SUPPLIES_N2_'||v_instance_code,

1855: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1856: application_short_name => 'MSC',
1857: statement_type => AD_DDL.DROP_INDEX,
1858: statement =>
1859: 'drop index MSC_ST_SUPPLIES_N2_'||v_instance_code,
1860: object_name => 'MSC_ST_SUPPLIES_N2_'||v_instance_code);
1861:
1862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N2_'||v_instance_code);
1863: EXCEPTION

Line 1860: object_name => 'MSC_ST_SUPPLIES_N2_'||v_instance_code);

1856: application_short_name => 'MSC',
1857: statement_type => AD_DDL.DROP_INDEX,
1858: statement =>
1859: 'drop index MSC_ST_SUPPLIES_N2_'||v_instance_code,
1860: object_name => 'MSC_ST_SUPPLIES_N2_'||v_instance_code);
1861:
1862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N2_'||v_instance_code);
1863: EXCEPTION
1864: WHEN OTHERS THEN

Line 1862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N2_'||v_instance_code);

1858: statement =>
1859: 'drop index MSC_ST_SUPPLIES_N2_'||v_instance_code,
1860: object_name => 'MSC_ST_SUPPLIES_N2_'||v_instance_code);
1861:
1862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N2_'||v_instance_code);
1863: EXCEPTION
1864: WHEN OTHERS THEN
1865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1866: END;

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

1861:
1862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N2_'||v_instance_code);
1863: EXCEPTION
1864: WHEN OTHERS THEN
1865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N2_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1866: END;
1867:
1868: BEGIN
1869: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 1873: 'drop index MSC_ST_SUPPLIES_N3_'||v_instance_code,

1869: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1870: application_short_name => 'MSC',
1871: statement_type => AD_DDL.DROP_INDEX,
1872: statement =>
1873: 'drop index MSC_ST_SUPPLIES_N3_'||v_instance_code,
1874: object_name => 'MSC_ST_SUPPLIES_N3_'||v_instance_code);
1875:
1876: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N3_'||v_instance_code);
1877: EXCEPTION

Line 1874: object_name => 'MSC_ST_SUPPLIES_N3_'||v_instance_code);

1870: application_short_name => 'MSC',
1871: statement_type => AD_DDL.DROP_INDEX,
1872: statement =>
1873: 'drop index MSC_ST_SUPPLIES_N3_'||v_instance_code,
1874: object_name => 'MSC_ST_SUPPLIES_N3_'||v_instance_code);
1875:
1876: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N3_'||v_instance_code);
1877: EXCEPTION
1878: WHEN OTHERS THEN

Line 1876: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N3_'||v_instance_code);

1872: statement =>
1873: 'drop index MSC_ST_SUPPLIES_N3_'||v_instance_code,
1874: object_name => 'MSC_ST_SUPPLIES_N3_'||v_instance_code);
1875:
1876: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N3_'||v_instance_code);
1877: EXCEPTION
1878: WHEN OTHERS THEN
1879: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1880: END;

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

1875:
1876: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N3_'||v_instance_code);
1877: EXCEPTION
1878: WHEN OTHERS THEN
1879: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N3_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1880: END;
1881:
1882: BEGIN
1883: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 1887: 'drop index MSC_ST_SUPPLIES_N4_'||v_instance_code,

1883: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1884: application_short_name => 'MSC',
1885: statement_type => AD_DDL.DROP_INDEX,
1886: statement =>
1887: 'drop index MSC_ST_SUPPLIES_N4_'||v_instance_code,
1888: object_name => 'MSC_ST_SUPPLIES_N4_'||v_instance_code);
1889:
1890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N4_'||v_instance_code);
1891: EXCEPTION

Line 1888: object_name => 'MSC_ST_SUPPLIES_N4_'||v_instance_code);

1884: application_short_name => 'MSC',
1885: statement_type => AD_DDL.DROP_INDEX,
1886: statement =>
1887: 'drop index MSC_ST_SUPPLIES_N4_'||v_instance_code,
1888: object_name => 'MSC_ST_SUPPLIES_N4_'||v_instance_code);
1889:
1890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N4_'||v_instance_code);
1891: EXCEPTION
1892: WHEN OTHERS THEN

Line 1890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N4_'||v_instance_code);

1886: statement =>
1887: 'drop index MSC_ST_SUPPLIES_N4_'||v_instance_code,
1888: object_name => 'MSC_ST_SUPPLIES_N4_'||v_instance_code);
1889:
1890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N4_'||v_instance_code);
1891: EXCEPTION
1892: WHEN OTHERS THEN
1893: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N4_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1894: END;

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

1889:
1890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N4_'||v_instance_code);
1891: EXCEPTION
1892: WHEN OTHERS THEN
1893: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N4_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1894: END;
1895:
1896: BEGIN
1897: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 1901: 'drop index MSC_ST_SUPPLIES_N5_'||v_instance_code,

1897: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1898: application_short_name => 'MSC',
1899: statement_type => AD_DDL.DROP_INDEX,
1900: statement =>
1901: 'drop index MSC_ST_SUPPLIES_N5_'||v_instance_code,
1902: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);
1903:
1904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N5_'||v_instance_code);
1905:

Line 1902: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);

1898: application_short_name => 'MSC',
1899: statement_type => AD_DDL.DROP_INDEX,
1900: statement =>
1901: 'drop index MSC_ST_SUPPLIES_N5_'||v_instance_code,
1902: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);
1903:
1904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N5_'||v_instance_code);
1905:
1906: EXCEPTION

Line 1904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N5_'||v_instance_code);

1900: statement =>
1901: 'drop index MSC_ST_SUPPLIES_N5_'||v_instance_code,
1902: object_name => 'MSC_ST_SUPPLIES_N5_'||v_instance_code);
1903:
1904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N5_'||v_instance_code);
1905:
1906: EXCEPTION
1907: WHEN OTHERS THEN
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N5_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

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

1904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_SUPPLIES_N5_'||v_instance_code);
1905:
1906: EXCEPTION
1907: WHEN OTHERS THEN
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_SUPPLIES_N5_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1909: END;
1910:
1911: msc_analyse_tables_pk.analyse_table( 'MSC_LOCAL_ID_SUPPLY', v_instance_id, -1);
1912:

Line 33917: | records from the msc_st_supplies table. The following |

33913:
33914:
33915: /*==========================================================================+
33916: | DESCRIPTION : This procedure is called for validating supplies related |
33917: | records from the msc_st_supplies table. The following |
33918: | types are considered for the validation. |
33919: | Supply Source Order Type |
33920: | -------------------------------------------- |
33921: | Purchase Order 1 |

Line 33947: FROM msc_st_supplies

33943: PRAGMA EXCEPTION_INIT(busy, -54);
33944:
33945: CURSOR c1(p_batch_id NUMBER) IS
33946: SELECT rowid
33947: FROM msc_st_supplies
33948: WHERE order_type IN(1,2,5,8,11,12,18,73,74,87,81)
33949: AND process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
33950: AND NVL(batch_id,NULL_VALUE)=p_batch_id
33951: AND sr_instance_code = v_instance_code;

Line 33955: FROM msc_st_supplies

33951: AND sr_instance_code = v_instance_code;
33952:
33953: CURSOR c2(p_batch_id NUMBER) IS
33954: SELECT rowid
33955: FROM msc_st_supplies
33956: WHERE NVL(sr_mtl_supply_id,NULL_VALUE) = NULL_VALUE
33957: AND order_type IN(1,2,8,11,12,73,74,87)
33958: AND process_flag = G_IN_PROCESS
33959: AND NVL(batch_id,NULL_VALUE) =p_batch_id

Line 33964: FROM msc_st_supplies

33960: AND sr_instance_code = v_instance_code;
33961:
33962: CURSOR c3(p_batch_id NUMBER) IS
33963: SELECT max(rowid)
33964: FROM msc_st_supplies
33965: WHERE NVL(disposition_id,NULL_VALUE) = NULL_VALUE
33966: AND order_type IN(1,2,8,11,12,73,74,87)
33967: AND deleted_flag = SYS_NO
33968: AND process_flag = G_IN_PROCESS

Line 33975: FROM msc_st_supplies

33971: GROUP BY sr_instance_code,company_name,order_number,order_type;
33972:
33973: CURSOR c4(p_batch_id NUMBER) IS
33974: SELECT rowid
33975: FROM msc_st_supplies
33976: WHERE NVL(po_line_id,NULL_VALUE) = NULL_VALUE
33977: AND deleted_flag = SYS_NO
33978: AND order_type IN(1,2,8,11,12,73,74,87)
33979: AND process_flag = G_IN_PROCESS

Line 33985: FROM msc_st_supplies

33981: AND sr_instance_code = v_instance_code;
33982:
33983: CURSOR c5(p_batch_id NUMBER) IS
33984: SELECT rowid
33985: FROM msc_st_supplies
33986: WHERE NVL(disposition_id,NULL_VALUE) = NULL_VALUE
33987: AND order_type IN (5,81)
33988: AND process_flag = G_IN_PROCESS
33989: AND NVL(batch_id,NULL_VALUE) =p_batch_id

Line 33994: FROM msc_st_supplies

33990: AND sr_instance_code = v_instance_code;
33991:
33992: CURSOR c6(p_batch_id NUMBER) IS
33993: SELECT rowid
33994: FROM msc_st_supplies
33995: WHERE order_type = 18
33996: AND process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
33997: AND NVL(batch_id,NULL_VALUE)=p_batch_id
33998: AND sr_instance_code = v_instance_code;

Line 34002: FROM msc_st_supplies ms1 ,

33998: AND sr_instance_code = v_instance_code;
33999:
34000: CURSOR c_lock(p_batch_id NUMBER) IS
34001: SELECT 'X'
34002: FROM msc_st_supplies ms1 ,
34003: msc_st_supplies ms2
34004: WHERE ms1.batch_id = p_batch_id
34005: AND ms1.batch_id <> nvl(ms2.batch_id,NULL_VALUE)
34006: AND ms1.disposition_id IS NULL

Line 34003: msc_st_supplies ms2

33999:
34000: CURSOR c_lock(p_batch_id NUMBER) IS
34001: SELECT 'X'
34002: FROM msc_st_supplies ms1 ,
34003: msc_st_supplies ms2
34004: WHERE ms1.batch_id = p_batch_id
34005: AND ms1.batch_id <> nvl(ms2.batch_id,NULL_VALUE)
34006: AND ms1.disposition_id IS NULL
34007: AND ms2.disposition_id IS NULL

Line 34065: 'UPDATE msc_st_supplies '

34061: lv_error_text := substr('MSC_CL_PRE_PROCESS.LOAD_SUPPLY'||'(' ||v_sql_stmt||')'|| SQLERRM, 1, 240);
34062: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_error_text);
34063: v_sql_stmt := 2.0;
34064: lv_sql_stmt :=
34065: 'UPDATE msc_st_supplies '
34066: ||' SET process_flag = 1, '
34067: ||' batch_id = NULL'
34068: ||' WHERE batch_id = :p_batch_id'
34069: ||' AND order_type IN(1,2,8,11,12,73,74,87)'

Line 34084: 'UPDATE msc_st_supplies mss '

34080: END LOOP ;
34081:
34082: v_sql_stmt := 2.1;
34083: lv_sql_stmt :=
34084: 'UPDATE msc_st_supplies mss '
34085: ||' SET disposition_id = (SELECT local_id'
34086: ||' FROM msc_local_id_supply mls'
34087: ||' WHERE mls.char3 = mss.order_number'
34088: ||' AND mls.number1 = mss.order_type'

Line 34117: UPDATE msc_st_supplies

34113: v_sql_stmt := 3.0;
34114:
34115:
34116: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
34117: UPDATE msc_st_supplies
34118: SET disposition_id = msc_st_disposition_id_s.NEXTVAL
34119: WHERE rowid = lb_rowid(j);
34120:
34121: v_sql_stmt := 3.1;

Line 34153: FROM msc_st_supplies

34149: v_current_date,
34150: v_current_user,
34151: v_current_date,
34152: v_current_user
34153: FROM msc_st_supplies
34154: WHERE rowid = lb_rowid(j);
34155: END IF;
34156: CLOSE c3 ;
34157:

Line 34160: 'UPDATE msc_st_supplies mss '

34156: CLOSE c3 ;
34157:
34158: v_sql_stmt := 4.0;
34159: lv_sql_stmt :=
34160: 'UPDATE msc_st_supplies mss '
34161: ||' SET disposition_id = (SELECT local_id'
34162: ||' FROM msc_local_id_supply mls'
34163: ||' WHERE mls.char3 = mss.order_number'
34164: ||' AND mls.number1 = mss.order_type'

Line 34196: UPDATE msc_st_supplies

34192: IF c1%ROWCOUNT > 0 THEN
34193:
34194: v_sql_stmt := 5.0;
34195: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
34196: UPDATE msc_st_supplies
34197: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
34198: refresh_id = v_refresh_id,
34199: last_update_date = v_current_date,
34200: last_updated_by = v_current_user,

Line 34197: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,

34193:
34194: v_sql_stmt := 5.0;
34195: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
34196: UPDATE msc_st_supplies
34197: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
34198: refresh_id = v_refresh_id,
34199: last_update_date = v_current_date,
34200: last_updated_by = v_current_user,
34201: creation_date = v_current_date,

Line 34215: (p_table_name => 'MSC_ST_SUPPLIES',

34211:
34212: --Log a warning for those records where the deleted_flag has a value other
34213: --SYS_NO
34214: lv_return := MSC_ST_UTIL.LOG_ERROR
34215: (p_table_name => 'MSC_ST_SUPPLIES',
34216: p_instance_code => v_instance_code,
34217: p_row => lv_column_names,
34218: p_severity => G_SEV_WARNING,
34219: p_message_text => lv_message_text,

Line 34248: 'UPDATE msc_st_supplies '

34244: END IF;
34245:
34246: v_sql_stmt := 5.1;
34247: lv_sql_stmt :=
34248: 'UPDATE msc_st_supplies '
34249: ||' SET error_text = ''' || lv_message_text || ''','
34250: ||' process_flag = '||G_ERROR_FLG
34251: ||' WHERE NEED_BY_DATE is null '
34252: ||' AND PROMISED_DATE is null '

Line 34281: (p_table_name => 'MSC_ST_SUPPLIES',

34277: END IF;
34278:
34279: --Derive Organization_id
34280: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
34281: (p_table_name => 'MSC_ST_SUPPLIES',
34282: p_org_partner_name => 'ORGANIZATION_CODE',
34283: p_org_partner_id => 'ORGANIZATION_ID',
34284: p_instance_code => v_instance_code,
34285: p_partner_type => G_ORGANIZATION,

Line 34306: UPDATE msc_st_supplies mss

34302: IF c6%ROWCOUNT > 0 THEN
34303:
34304: v_sql_stmt := 2.2;
34305: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
34306: UPDATE msc_st_supplies mss
34307: SET mss.condition_type = (SELECT nvl(msi.condition_type,'G' )
34308: FROM msc_sub_inventories msi
34309: WHERE msi.organization_id = mss.organization_id
34310: AND msi.plan_id = -1

Line 34325: UPDATE msc_st_supplies mss

34321: msc_util.initialize_common_globals (v_instance_id);
34322:
34323: v_sql_stmt := 2.3;
34324: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
34325: UPDATE msc_st_supplies mss
34326: SET mss.ITEM_TYPE_ID = MSC_UTIL.G_PARTCONDN_ITEMTYPEID,
34327: mss.ITEM_TYPE_VALUE =DECODE(mss.condition_type,'G',MSC_UTIL.G_PARTCONDN_GOOD,'B',MSC_UTIL.G_PARTCONDN_BAD,MSC_UTIL.G_PARTCONDN_GOOD)
34328: WHERE rowid = lb_rowid(j);
34329:

Line 34347: UPDATE MSC_ST_SUPPLIES

34343: IF lv_return <> 0 THEN
34344: RAISE ex_logging_err;
34345: END IF;
34346:
34347: UPDATE MSC_ST_SUPPLIES
34348: SET PLANNING_PARTNER_SITE_ID = (SELECT local_id
34349: FROM msc_local_id_setup
34350: WHERE char1 = sr_instance_code
34351: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)

Line 34364: UPDATE MSC_ST_SUPPLIES

34360: AND ORDER_TYPE = 18;
34361:
34362:
34363:
34364: UPDATE MSC_ST_SUPPLIES
34365: SET PLANNING_PARTNER_SITE_ID = (SELECT local_id
34366: FROM msc_local_id_setup
34367: WHERE char1 = sr_instance_code
34368: AND char3 = PLANNING_PARTNER_SITE_CODE

Line 34378: UPDATE MSC_ST_SUPPLIES mss1

34374: AND process_flag = G_IN_PROCESS
34375: AND PLANNING_TP_TYPE = 2
34376: AND ORDER_TYPE =18;
34377:
34378: UPDATE MSC_ST_SUPPLIES mss1
34379: SET process_flag = G_ERROR_FLG,
34380: error_text = lv_message_text
34381: WHERE EXISTS( SELECT 1
34382: FROM MSC_ST_SUPPLIES mss2

Line 34382: FROM MSC_ST_SUPPLIES mss2

34378: UPDATE MSC_ST_SUPPLIES mss1
34379: SET process_flag = G_ERROR_FLG,
34380: error_text = lv_message_text
34381: WHERE EXISTS( SELECT 1
34382: FROM MSC_ST_SUPPLIES mss2
34383: WHERE mss2.sr_instance_code = mss1.sr_instance_code
34384: AND mss2.process_flag = G_IN_PROCESS
34385: AND NVL(mss2.planning_partner_site_id,NULL_VALUE) = NULL_VALUE
34386: GROUP BY sr_instance_code HAVING COUNT(*) > 0)

Line 34393: UPDATE MSC_ST_SUPPLIES

34389: AND NVL(batch_id,NULL_VALUE) = p_batch_id -- bug 8547376
34390: AND NVL(mss1.planning_partner_site_id,NULL_VALUE) = NULL_VALUE
34391: AND ORDER_TYPE=18;
34392:
34393: UPDATE MSC_ST_SUPPLIES
34394: SET OWNING_PARTNER_SITE_ID = (SELECT local_id
34395: FROM msc_local_id_setup
34396: WHERE char1 = sr_instance_code
34397: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)

Line 34408: UPDATE MSC_ST_SUPPLIES

34404: AND process_flag = G_IN_PROCESS
34405: AND OWNING_TP_TYPE = 1
34406: AND ORDER_TYPE=18;
34407:
34408: UPDATE MSC_ST_SUPPLIES
34409: SET OWNING_PARTNER_SITE_ID = (SELECT local_id
34410: FROM msc_local_id_setup
34411: WHERE char1 = sr_instance_code
34412: AND char3 = OWNING_PARTNER_SITE_CODE

Line 34434: UPDATE MSC_ST_SUPPLIES mss1

34430: IF lv_return <> 0 THEN
34431: RAISE ex_logging_err;
34432: END IF;
34433:
34434: UPDATE MSC_ST_SUPPLIES mss1
34435: SET process_flag = G_ERROR_FLG,
34436: error_text = lv_message_text
34437: WHERE EXISTS( SELECT 1
34438: FROM MSC_ST_SUPPLIES mss2

Line 34438: FROM MSC_ST_SUPPLIES mss2

34434: UPDATE MSC_ST_SUPPLIES mss1
34435: SET process_flag = G_ERROR_FLG,
34436: error_text = lv_message_text
34437: WHERE EXISTS( SELECT 1
34438: FROM MSC_ST_SUPPLIES mss2
34439: WHERE mss2.sr_instance_code = mss1.sr_instance_code
34440: AND mss2.process_flag = G_IN_PROCESS
34441: AND NVL(mss2.owning_partner_site_id,NULL_VALUE) = NULL_VALUE
34442: GROUP BY sr_instance_code HAVING COUNT(*) > 0)

Line 34453: UPDATE MSC_ST_SUPPLIES t1

34449: -----------------start of changes for flatfile load into ERP instance-----------------------
34450:
34451: ELSE
34452:
34453: UPDATE MSC_ST_SUPPLIES t1
34454: SET PLANNING_PARTNER_SITE_ID =
34455: (SELECT mtps.sr_tp_site_id
34456: FROM msc_trading_partner_sites mtps
34457: WHERE mtps.partner_type = G_VENDOR

Line 34468: UPDATE MSC_ST_SUPPLIES t1

34464: AND process_flag = G_IN_PROCESS
34465: AND PLANNING_TP_TYPE = 1
34466: AND order_type=18;
34467:
34468: UPDATE MSC_ST_SUPPLIES t1
34469: SET PLANNING_PARTNER_SITE_ID =
34470: (SELECT sr_tp_id
34471: FROM msc_trading_partners mtp
34472: WHERE mtp.partner_type = G_ORGANIZATION

Line 34495: UPDATE MSC_ST_SUPPLIES mss1

34491: IF lv_return <> 0 THEN
34492: RAISE ex_logging_err;
34493: END IF;
34494:
34495: UPDATE MSC_ST_SUPPLIES mss1
34496: SET process_flag = G_ERROR_FLG,
34497: error_text = lv_message_text
34498: WHERE mss1.process_flag = G_IN_PROCESS
34499: AND mss1.sr_instance_code = v_instance_code

Line 34504: UPDATE MSC_ST_SUPPLIES t1

34500: AND NVL(batch_id,NULL_VALUE) = p_batch_id -- bug 8547376
34501: AND NVL(mss1.planning_partner_site_id,NULL_VALUE) = NULL_VALUE
34502: AND order_type=18 ;
34503:
34504: UPDATE MSC_ST_SUPPLIES t1
34505: SET OWNING_PARTNER_SITE_ID =
34506: (SELECT mtps.sr_tp_site_id
34507: FROM msc_trading_partner_sites mtps
34508: WHERE mtps.partner_type = G_VENDOR

Line 34519: UPDATE MSC_ST_SUPPLIES t1

34515: AND process_flag = G_IN_PROCESS
34516: AND OWNING_TP_TYPE = 1
34517: AND order_type=18;
34518:
34519: UPDATE MSC_ST_SUPPLIES t1
34520: SET OWNING_PARTNER_SITE_ID =
34521: (SELECT sr_tp_id
34522: FROM msc_trading_partners mtp
34523: WHERE mtp.partner_type = G_ORGANIZATION

Line 34545: UPDATE MSC_ST_SUPPLIES mss1

34541: IF lv_return <> 0 THEN
34542: RAISE ex_logging_err;
34543: END IF;
34544:
34545: UPDATE MSC_ST_SUPPLIES mss1
34546: SET process_flag = G_ERROR_FLG,
34547: error_text = lv_message_text
34548: WHERE mss1.process_flag = G_IN_PROCESS
34549: AND mss1.sr_instance_code = v_instance_code

Line 34572: (p_table_name => 'MSC_ST_SUPPLIES',

34568: END IF;
34569:
34570: --Derive Inventory_item_id
34571: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
34572: (p_table_name => 'MSC_ST_SUPPLIES',
34573: p_item_col_name => 'ITEM_NAME',
34574: p_item_col_id => 'INVENTORY_ITEM_ID',
34575: p_instance_id => v_instance_id,
34576: p_instance_code => v_instance_code,

Line 34602: (p_table_name => 'MSC_ST_SUPPLIES',

34598: END IF;
34599:
34600: --Derive Supplier_id
34601: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
34602: (p_table_name => 'MSC_ST_SUPPLIES',
34603: p_org_partner_name => 'SUPPLIER_NAME',
34604: p_org_partner_id => 'SUPPLIER_ID',
34605: p_instance_code => v_instance_code,
34606: p_partner_type => G_VENDOR,

Line 34633: (p_table_name => 'MSC_ST_SUPPLIES',

34629: END IF;
34630:
34631: --Derive Supplier_site_id
34632: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
34633: (p_table_name => 'MSC_ST_SUPPLIES',
34634: p_partner_name => 'SUPPLIER_NAME',
34635: p_partner_site_code => 'SUPPLIER_SITE_CODE',
34636: p_partner_site_id => 'SUPPLIER_SITE_ID',
34637: p_instance_code => v_instance_code,

Line 34669: 'UPDATE msc_st_supplies '

34665: END IF;
34666:
34667: v_sql_stmt := 10;
34668: lv_sql_stmt :=
34669: 'UPDATE msc_st_supplies '
34670: ||' SET error_text = '||''''||lv_message_text||''''||','
34671: ||' process_flag = '||G_ERROR_FLG
34672: ||' WHERE (((NVL(order_number, '||''''||NULL_CHAR||''''||') '
34673: ||' = '||''''||NULL_CHAR||''''

Line 34723: (p_table_name => 'MSC_ST_SUPPLIES',

34719: ||' AND order_type IN(2,11,12,73,87)'
34720: ||' AND process_flag = '||G_IN_PROCESS;
34721:
34722: lv_return := MSC_ST_UTIL.LOG_ERROR
34723: (p_table_name => 'MSC_ST_SUPPLIES',
34724: p_instance_code => v_instance_code,
34725: p_row => lv_column_names,
34726: p_severity => G_SEV_WARNING,
34727: p_message_text => lv_message_text,

Line 34740: 'UPDATE msc_st_supplies '

34736: -- end
34737:
34738: /*
34739: lv_sql_stmt :=
34740: 'UPDATE msc_st_supplies '
34741: ||' SET process_flag = '||G_ERROR_FLG||','
34742: ||' error_text = '||''''||lv_message_text||''''
34743: ||' WHERE NVL(new_dock_date,SYSDATE-36500) > NVL(new_schedule_date,SYSDATE-36500)'
34744: ||' AND deleted_flag ='||SYS_NO

Line 34773: 'UPDATE msc_st_supplies'

34769:
34770: --Negative quantities are allowed only for the order type On-Hand(18)
34771: v_sql_stmt := 11;
34772: lv_sql_stmt :=
34773: 'UPDATE msc_st_supplies'
34774: ||' SET process_flag = '||G_ERROR_FLG||','
34775: ||' error_text = '||''''||lv_message_text||''''
34776: ||' WHERE new_order_quantity <= 0'
34777: ||' AND order_type <> 18'

Line 34804: p_token_value3 => 'MSC_ST_SUPPLIES');

34800: ||' DESIGNATOR',
34801: p_token2 => 'MASTER_TABLE',
34802: p_token_value2 => 'MSC_ST_DESIGNATORS',
34803: p_token3 => 'CHILD_TABLE',
34804: p_token_value3 => 'MSC_ST_SUPPLIES');
34805:
34806: IF lv_return <> 0 THEN
34807: RAISE ex_logging_err;
34808: END IF;

Line 34812: 'UPDATE msc_st_supplies mss'

34808: END IF;
34809:
34810: v_sql_stmt := 12;
34811: lv_sql_stmt :=
34812: 'UPDATE msc_st_supplies mss'
34813: ||' SET process_flag = '||G_ERROR_FLG||','
34814: ||' error_text = '||''''||lv_message_text||''''
34815: ||' WHERE NOT EXISTS(SELECT 1'
34816: ||' FROM msc_st_designators msd'

Line 34850: 'UPDATE msc_st_supplies mss'

34846:
34847: -- 13839374 validating designators for returns forecast
34848: v_sql_stmt := 14;
34849: lv_sql_stmt :=
34850: 'UPDATE msc_st_supplies mss'
34851: ||' SET process_flag = '||G_ERROR_FLG||','
34852: ||' error_text = '||''''||lv_message_text||''''
34853: ||' WHERE NOT EXISTS(SELECT 1'
34854: ||' FROM msc_st_designators msd'

Line 34902: p_token_value3 => 'MSC_ST_SUPPLIES');

34898: p_token_value1 => 'SHIP_TO_PARTY_NAME',
34899: p_token2 => 'MASTER_TABLE',
34900: p_token_value2 => 'MSC_ST_DESIGNATORS',
34901: p_token3 => 'CHILD_TABLE',
34902: p_token_value3 => 'MSC_ST_SUPPLIES');
34903:
34904: IF lv_return <> 0 THEN
34905: RAISE ex_logging_err;
34906: END IF;

Line 34910: 'UPDATE msc_st_supplies '

34906: END IF;
34907:
34908: v_sql_stmt := 13;
34909: lv_sql_stmt :=
34910: 'UPDATE msc_st_supplies '
34911: ||' SET process_flag = '||G_ERROR_FLG||','
34912: ||' error_text = '||''''||lv_message_text||''''
34913: ||' WHERE NOT EXISTS (SELECT 1'
34914: ||' FROM hz_parties '

Line 34941: p_token_value3 => 'MSC_ST_SUPPLIES');

34937: ||' SHIP_TO_SITE_CODE',
34938: p_token2 => 'MASTER_TABLE',
34939: p_token_value2 => 'MSC_ST_DESIGNATORS',
34940: p_token3 => 'CHILD_TABLE',
34941: p_token_value3 => 'MSC_ST_SUPPLIES');
34942:
34943: IF lv_return <> 0 THEN
34944: RAISE ex_logging_err;
34945: END IF;

Line 34949: 'UPDATE msc_st_supplies ms'

34945: END IF;
34946:
34947: v_sql_stmt := 14;
34948: lv_sql_stmt :=
34949: 'UPDATE msc_st_supplies ms'
34950: ||' SET process_flag = '||G_ERROR_FLG||','
34951: ||' error_text = '||''''||lv_message_text||''''
34952: ||' WHERE NOT EXISTS (SELECT 1'
34953: ||' FROM hz_parties hp,hz_party_sites hps'

Line 34992: (p_table_name => 'MSC_ST_SUPPLIES',

34988:
34989: --Log a warning for those records where the firm_planned_type has a value other
34990: --NOT IN (1,2)
34991: lv_return := MSC_ST_UTIL.LOG_ERROR
34992: (p_table_name => 'MSC_ST_SUPPLIES',
34993: p_instance_code => v_instance_code,
34994: p_row => lv_column_names,
34995: p_severity => G_SEV_WARNING,
34996: p_message_text => lv_message_text,

Line 35025: (p_table_name => 'MSC_ST_SUPPLIES',

35021: END IF;
35022:
35023: --Derive Project Id.
35024: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
35025: (p_table_name => 'MSC_ST_SUPPLIES',
35026: p_proj_col_name => 'PROJECT_NUMBER',
35027: p_proj_task_col_id => 'PROJECT_ID',
35028: p_instance_code => v_instance_code,
35029: p_entity_name => 'PROJECT_ID',

Line 35058: (p_table_name => 'MSC_ST_SUPPLIES',

35054: END IF;
35055:
35056: --Derive Task Id.
35057: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
35058: (p_table_name => 'MSC_ST_SUPPLIES',
35059: p_proj_col_name => 'PROJECT_NUMBER',
35060: p_proj_task_col_id => 'TASK_ID',
35061: p_instance_code => v_instance_code,
35062: p_entity_name => 'TASK_ID',

Line 35089: (p_table_name => 'MSC_ST_SUPPLIES',

35085: END IF;
35086:
35087: --Derive FROM_ORGANIZATION_ID
35088: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
35089: (p_table_name => 'MSC_ST_SUPPLIES',
35090: p_org_partner_name => 'FROM_ORGANIZATION_CODE',
35091: p_org_partner_id => 'FROM_ORGANIZATION_ID',
35092: p_instance_code => v_instance_code,
35093: p_partner_type => G_ORGANIZATION,

Line 35108: 'UPDATE msc_st_supplies mss'

35104:
35105: --Derive source_Organization_id and source_sr_instance_id,
35106: v_sql_stmt := 15;
35107: lv_sql_stmt :=
35108: 'UPDATE msc_st_supplies mss'
35109: ||' SET source_organization_id = from_organization_id,'
35110: ||' source_sr_instance_id = (select instance_id'
35111: ||' FROM msc_apps_instances mai'
35112: ||' WHERE mai.instance_code = mss.source_sr_instance_code)'

Line 35129: 'UPDATE msc_st_supplies mss '

35125: --Deriving disposition_id and po_line_id for the supply type PO, Purchase
35126: --Req. and intransit supplies
35127: v_sql_stmt := 16;
35128: lv_sql_stmt :=
35129: 'UPDATE msc_st_supplies mss '
35130: ||' SET disposition_id = (SELECT local_id'
35131: ||' FROM msc_local_id_supply mls'
35132: ||' WHERE mls.char3 = mss.order_number'
35133: ||' AND mls.number1 = mss.order_type'

Line 35162: 'UPDATE msc_st_supplies mss'

35158: v_instance_code;
35159:
35160: v_sql_stmt := 17;
35161: lv_sql_stmt :=
35162: 'UPDATE msc_st_supplies mss'
35163: ||' SET sr_mtl_supply_id = (SELECT local_id'
35164: ||' FROM msc_local_id_supply mls'
35165: ||' WHERE mls.char4 = mss.order_number'
35166: ||' AND mls.number1 = mss.purch_line_num'

Line 35201: 'UPDATE msc_st_supplies '

35197: --the record will be errored out.
35198: v_sql_stmt := 18;
35199:
35200: lv_sql_stmt :=
35201: 'UPDATE msc_st_supplies '
35202: ||' SET process_flag = '||G_ERROR_FLG||','
35203: ||' error_text = '||''''||lv_message_text||''''
35204: ||' WHERE NVL(sr_mtl_supply_id,'||NULL_VALUE||') = '||NULL_VALUE
35205: ||' AND deleted_flag ='||SYS_YES

Line 35221: 'UPDATE msc_st_supplies mss'

35217:
35218: --Deriving disposition_id for the Plan Orders.
35219: v_sql_stmt := 19;
35220: lv_sql_stmt :=
35221: 'UPDATE msc_st_supplies mss'
35222: ||' SET disposition_id = (SELECT local_id'
35223: ||' FROM msc_local_id_supply mls'
35224: ||' WHERE mls.char4 = mss.schedule_designator'
35225: ||' AND mls.char5 = mss.schedule_line_num'

Line 35250: 'UPDATE msc_st_supplies mss'

35246:
35247: --Deriving disposition_id for the Return Forecasts
35248: v_sql_stmt := 19;
35249: lv_sql_stmt :=
35250: 'UPDATE msc_st_supplies mss'
35251: ||' SET disposition_id = (SELECT local_id'
35252: ||' FROM msc_local_id_supply mls'
35253: ||' WHERE mls.char4 = mss.schedule_designator'
35254: ||' AND mls.number1 = mss.order_type'

Line 35279: 'UPDATE msc_st_supplies '

35275: --errored out.
35276: v_sql_stmt := 20;
35277:
35278: lv_sql_stmt :=
35279: 'UPDATE msc_st_supplies '
35280: ||' SET process_flag = '||G_ERROR_FLG||','
35281: ||' error_text = '||''''||lv_message_text||''''
35282: ||' WHERE NVL(disposition_id,'||NULL_VALUE||') = '||NULL_VALUE
35283: ||' AND deleted_flag ='||SYS_YES

Line 35302: pEntityName => 'MSC_ST_SUPPLIES',

35298: (ERRBUF => lv_error_text,
35299: RETCODE => lv_return,
35300: pBatchID => p_batch_id,
35301: pInstanceCode => v_instance_code,
35302: pEntityName => 'MSC_ST_SUPPLIES',
35303: pInstanceID => v_instance_id);
35304:
35305: IF NVL(lv_return,0) <> 0 THEN
35306: RAISE ex_logging_err;

Line 35318: UPDATE msc_st_supplies

35314: IF c2%ROWCOUNT > 0 THEN
35315:
35316: v_sql_stmt := 22;
35317: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
35318: UPDATE msc_st_supplies
35319: SET sr_mtl_supply_id = msc_st_sr_mtl_supply_id_s.NEXTVAL
35320: WHERE rowid = lb_rowid(j);
35321:
35322: v_sql_stmt := 23;

Line 35374: FROM msc_st_supplies

35370: v_current_date,
35371: v_current_user,
35372: v_current_date,
35373: v_current_user
35374: FROM msc_st_supplies
35375: WHERE rowid = lb_rowid(j);
35376: END IF;
35377: CLOSE c2 ;
35378:

Line 35387: UPDATE msc_st_supplies

35383:
35384: IF c4%ROWCOUNT > 0 THEN
35385: v_sql_stmt := 26;
35386: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
35387: UPDATE msc_st_supplies
35388: SET po_line_id = msc_st_po_line_id_s.NEXTVAL
35389: WHERE rowid = lb_rowid(j);
35390:
35391: v_sql_stmt := 27;

Line 35441: FROM msc_st_supplies

35437: v_current_date,
35438: v_current_user,
35439: v_current_date,
35440: v_current_user
35441: FROM msc_st_supplies
35442: WHERE rowid = lb_rowid(j);
35443:
35444: END IF;
35445: CLOSE c4 ;

Line 35454: UPDATE msc_st_supplies

35450:
35451: IF c5%ROWCOUNT > 0 THEN
35452: v_sql_stmt := 28;
35453: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
35454: UPDATE msc_st_supplies
35455: SET disposition_id = msc_st_disposition_id_s.NEXTVAL
35456: WHERE rowid = lb_rowid(j);
35457:
35458: v_sql_stmt := 29;

Line 35510: FROM msc_st_supplies

35506: v_current_date,
35507: v_current_user,
35508: v_current_date,
35509: v_current_user
35510: FROM msc_st_supplies
35511: WHERE rowid = lb_rowid(j);
35512:
35513: END IF;
35514: CLOSE c5 ;

Line 35519: 'UPDATE msc_st_supplies mss'

35515:
35516: --Updating line_id using disposition_id for the Plan Orders.
35517: v_sql_stmt := 30;
35518: lv_sql_stmt :=
35519: 'UPDATE msc_st_supplies mss'
35520: ||' SET line_id = disposition_id'
35521: ||' WHERE order_type = 5'
35522: ||' AND process_flag = '||G_IN_PROCESS
35523: ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'

Line 35535: 'UPDATE msc_st_supplies mss '

35531: v_instance_code;
35532:
35533: v_sql_stmt := 31;
35534: lv_sql_stmt :=
35535: 'UPDATE msc_st_supplies mss '
35536: ||' SET disposition_id = (SELECT local_id'
35537: ||' FROM msc_local_id_supply mls'
35538: ||' WHERE mls.char3 = mss.order_number'
35539: ||' AND mls.number1 = mss.order_type'

Line 35566: ' UPDATE msc_st_supplies mss '

35562: -- in msc_item_suppliers and that particular ASL is VMI enabled
35563:
35564: v_sql_stmt := 32;
35565: lv_sql_stmt :=
35566: ' UPDATE msc_st_supplies mss '
35567: ||' SET vmi_flag = ''Y'''
35568: ||' WHERE exists (SELECT 1 FROM MSC_TP_ID_LID mtil,'
35569: ||' MSC_TP_SITE_ID_LID mtsil,'
35570: ||' MSC_ITEM_ID_LID t1,'

Line 35666: 'UPDATE msc_st_supplies mss'

35662:
35663: -- bug 2903052 defaulting the new_schedule_date as sysdate for onhand supply
35664:
35665: lv_sql_stmt :=
35666: 'UPDATE msc_st_supplies mss'
35667: ||' SET new_schedule_date = SYSDATE'
35668: ||' WHERE deleted_flag ='|| SYS_NO
35669: ||' AND process_flag ='|| G_IN_PROCESS
35670: ||' AND order_type = 18'

Line 35685: (p_table_name => 'MSC_ST_SUPPLIES',

35681:
35682:
35683:
35684: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
35685: (p_table_name => 'MSC_ST_SUPPLIES',
35686: p_instance_id => v_instance_id,
35687: p_instance_code => v_instance_code,
35688: p_process_flag => G_VALID,
35689: p_error_text => lv_error_text,

Line 35698: (p_table_name => 'MSC_ST_SUPPLIES',

35694: RAISE ex_logging_err;
35695: END IF;
35696:
35697: lv_return := MSC_ST_UTIL.LOG_ERROR
35698: (p_table_name => 'MSC_ST_SUPPLIES',
35699: p_instance_code => v_instance_code,
35700: p_row => lv_column_names,
35701: p_severity => G_SEV_ERROR,
35702: p_message_text => NULL,

Line 47286: | in the msc_st_supplies table. Order_types considered are |

47282:
47283:
47284: /*==========================================================================+
47285: | DESCRIPTION : This procedure validates Work Order supply related reocrds |
47286: | in the msc_st_supplies table. Order_types considered are |
47287: | Supply Source Order Type |
47288: | -------------------------------------------- |
47289: | Discrete Job 3 |
47290: | Non-Standard Job 7 |

Line 47304: lv_batch_id msc_st_supplies.batch_id%TYPE;

47300: lv_where_str VARCHAR2(5000);
47301: lv_sql_stmt VARCHAR2(5000);
47302: lv_column_names VARCHAR2(5000); --stores concatenated column names
47303: lv_message_text msc_errors.error_text%TYPE;
47304: lv_batch_id msc_st_supplies.batch_id%TYPE;
47305: ex_logging_err EXCEPTION;
47306:
47307: CURSOR c1(p_batch_id NUMBER) IS
47308: SELECT rowid

Line 47309: FROM msc_st_supplies

47305: ex_logging_err EXCEPTION;
47306:
47307: CURSOR c1(p_batch_id NUMBER) IS
47308: SELECT rowid
47309: FROM msc_st_supplies
47310: WHERE order_type IN(3,7,27,14,15)
47311: AND process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
47312: AND NVL(batch_id,NULL_VALUE)=p_batch_id
47313: AND sr_instance_code = v_instance_code;

Line 47317: FROM msc_st_supplies

47313: AND sr_instance_code = v_instance_code;
47314:
47315: CURSOR c2(p_batch_id NUMBER) IS
47316: SELECT rowid
47317: FROM msc_st_supplies
47318: WHERE NVL(wip_entity_id,NULL_VALUE) = NULL_VALUE
47319: AND process_flag = G_IN_PROCESS
47320: AND NVL(batch_id,NULL_VALUE) =p_batch_id
47321: AND sr_instance_code = v_instance_code;

Line 47325: FROM msc_st_supplies

47321: AND sr_instance_code = v_instance_code;
47322:
47323: CURSOR c3(p_batch_id NUMBER) IS
47324: SELECT max(rowid)
47325: FROM msc_st_supplies
47326: WHERE NVL(schedule_group_id,NULL_VALUE) = NULL_VALUE
47327: AND deleted_flag = SYS_NO
47328: AND process_flag = G_IN_PROCESS
47329: AND NVL(batch_id,NULL_VALUE) = p_batch_id

Line 47335: FROM msc_st_supplies

47331: GROUP BY sr_instance_code,company_name,organization_code,schedule_group_name;
47332:
47333: CURSOR c4(p_batch_id NUMBER) IS
47334: SELECT rowid
47335: FROM msc_st_supplies
47336: WHERE process_flag = G_IN_PROCESS
47337: AND sr_instance_code = v_instance_code
47338: AND batch_id = p_batch_id
47339: AND NVL(JOB_OP_SEQ_NUM, NULL_VALUE) = NULL_VALUE

Line 47360: 'UPDATE msc_st_supplies mss1'

47356: --Duplicate records check for the records whose source is XML for
47357: --WO supplies
47358: v_sql_stmt := 01;
47359: lv_sql_stmt :=
47360: 'UPDATE msc_st_supplies mss1'
47361: ||' SET process_flag = '||G_ERROR_FLG||','
47362: ||' error_text = '||''''||lv_message_text||''''
47363: ||' WHERE message_id < (SELECT MAX(message_id)'
47364: ||' FROM msc_st_supplies mss2'

Line 47364: ||' FROM msc_st_supplies mss2'

47360: 'UPDATE msc_st_supplies mss1'
47361: ||' SET process_flag = '||G_ERROR_FLG||','
47362: ||' error_text = '||''''||lv_message_text||''''
47363: ||' WHERE message_id < (SELECT MAX(message_id)'
47364: ||' FROM msc_st_supplies mss2'
47365: ||' WHERE mss2.sr_instance_code'
47366: ||' = mss1.sr_instance_code'
47367: ||' AND NVL(mss2.company_name,'||''''||NULL_CHAR||''''||') = '
47368: ||' NVL(mss1.company_name,'||''''||NULL_CHAR||''''||')'

Line 47401: 'UPDATE msc_st_supplies mss1 '

47397: --Different SQL is used because in XML we can identify the latest records
47398: --whereas in batch load we cannot.
47399: v_sql_stmt := 02;
47400: lv_sql_stmt :=
47401: 'UPDATE msc_st_supplies mss1 '
47402: ||' SET process_flag = '||G_ERROR_FLG||','
47403: ||' error_text = '||''''||lv_message_text||''''
47404: ||' WHERE EXISTS( SELECT 1 '
47405: ||' FROM msc_st_supplies mss2'

Line 47405: ||' FROM msc_st_supplies mss2'

47401: 'UPDATE msc_st_supplies mss1 '
47402: ||' SET process_flag = '||G_ERROR_FLG||','
47403: ||' error_text = '||''''||lv_message_text||''''
47404: ||' WHERE EXISTS( SELECT 1 '
47405: ||' FROM msc_st_supplies mss2'
47406: ||' WHERE mss2.sr_instance_code'
47407: ||' = mss1.sr_instance_code'
47408: ||' AND NVL(mss2.company_name, '||''''||NULL_CHAR||''''||')= '
47409: ||' NVL(mss1.company_name, '||''''||NULL_CHAR||''''||')'

Line 47469: ' UPDATE msc_st_supplies '

47465: FROM dual;
47466:
47467: v_sql_stmt := 04;
47468: lv_sql_stmt :=
47469: ' UPDATE msc_st_supplies '
47470: ||' SET batch_id = :lv_batch_id'
47471: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
47472: ||' AND order_type IN(3,7,27,14,15)'
47473: ||' AND sr_instance_code = :v_instance_code'

Line 47492: UPDATE msc_st_supplies

47488: CLOSE c1;
47489:
47490: v_sql_stmt := 03;
47491: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
47492: UPDATE msc_st_supplies
47493: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
47494: refresh_id = v_refresh_id,
47495: last_update_date = v_current_date,
47496: last_updated_by = v_current_user,

Line 47493: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,

47489:
47490: v_sql_stmt := 03;
47491: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
47492: UPDATE msc_st_supplies
47493: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
47494: refresh_id = v_refresh_id,
47495: last_update_date = v_current_date,
47496: last_updated_by = v_current_user,
47497: creation_date = v_current_date,

Line 47520: (p_table_name => 'MSC_ST_SUPPLIES',

47516: ' AND NVL(deleted_flag,'||NULL_VALUE||') NOT IN(1,2)';
47517: --Log a warning for those records where the deleted_flag has a value other
47518: --SYS_NO
47519: lv_return := MSC_ST_UTIL.LOG_ERROR
47520: (p_table_name => 'MSC_ST_SUPPLIES',
47521: p_instance_code => v_instance_code,
47522: p_row => lv_column_names,
47523: p_severity => G_SEV_WARNING,
47524: p_message_text => lv_message_text,

Line 47550: (p_table_name => 'MSC_ST_SUPPLIES',

47546: END IF;
47547:
47548: --Derive Organization_id
47549: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
47550: (p_table_name => 'MSC_ST_SUPPLIES',
47551: p_org_partner_name => 'ORGANIZATION_CODE',
47552: p_org_partner_id => 'ORGANIZATION_ID',
47553: p_instance_code => v_instance_code,
47554: p_partner_type => G_ORGANIZATION,

Line 47580: (p_table_name => 'MSC_ST_SUPPLIES',

47576: END IF;
47577:
47578: --Derive Inventory_item_id
47579: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
47580: (p_table_name => 'MSC_ST_SUPPLIES',
47581: p_item_col_name => 'ITEM_NAME',
47582: p_item_col_id => 'INVENTORY_ITEM_ID',
47583: p_instance_id => v_instance_id,
47584: p_instance_code => v_instance_code,

Line 47610: 'UPDATE msc_st_supplies '

47606: END IF;
47607:
47608: v_sql_stmt := 04;
47609: lv_sql_stmt :=
47610: 'UPDATE msc_st_supplies '
47611: ||' SET error_text = '||''''||lv_message_text||''''||','
47612: ||' process_flag = '||G_ERROR_FLG
47613: ||' WHERE (NVL(new_schedule_date,sysdate-36500) = sysdate-36500'
47614: ||' OR NVL(new_order_quantity,'||NULL_VALUE|| ')= '||NULL_VALUE||')'

Line 47641: 'UPDATE msc_st_supplies '

47637: END IF;
47638:
47639: v_sql_stmt := 05;
47640: lv_sql_stmt :=
47641: 'UPDATE msc_st_supplies '
47642: ||' SET error_text = '||''''||lv_message_text||''''||','
47643: ||' process_flag = '||G_ERROR_FLG
47644: ||' WHERE NVL(wip_entity_name,'||''''||NULL_CHAR||''''||') '
47645: ||' = '||''''||NULL_CHAR||''''

Line 47679: (p_table_name => 'MSC_ST_SUPPLIES',

47675: --Log a warning for those records where the firm_planned_type has a value
47676: --other than 1 and 2
47677:
47678: lv_return := MSC_ST_UTIL.LOG_ERROR
47679: (p_table_name => 'MSC_ST_SUPPLIES',
47680: p_instance_code => v_instance_code,
47681: p_row => lv_column_names,
47682: p_severity => G_SEV_WARNING,
47683: p_message_text => lv_message_text,

Line 47717: (p_table_name => 'MSC_ST_SUPPLIES',

47713: --Log a warning for those records where the wip_status_code has a value other
47714: --than SYS_NO
47715:
47716: lv_return := MSC_ST_UTIL.LOG_ERROR
47717: (p_table_name => 'MSC_ST_SUPPLIES',
47718: p_instance_code => v_instance_code,
47719: p_row => lv_column_names,
47720: p_severity => G_SEV_WARNING,
47721: p_message_text => lv_message_text,

Line 47755: (p_table_name => 'MSC_ST_SUPPLIES',

47751: --Log a warning for those records where the firm_planned_type has a value other
47752: --than SYS_NO
47753:
47754: lv_return := MSC_ST_UTIL.LOG_ERROR
47755: (p_table_name => 'MSC_ST_SUPPLIES',
47756: p_instance_code => v_instance_code,
47757: p_row => lv_column_names,
47758: p_severity => G_SEV_WARNING,
47759: p_message_text => lv_message_text,

Line 47793: (p_table_name => 'MSC_ST_SUPPLIES',

47789: --Log a warning for those records where the wip_supply_type has a value other
47790: --than SYS_NO
47791:
47792: lv_return := MSC_ST_UTIL.LOG_ERROR
47793: (p_table_name => 'MSC_ST_SUPPLIES',
47794: p_instance_code => v_instance_code,
47795: p_row => lv_column_names,
47796: p_severity => G_SEV_WARNING,
47797: p_message_text => lv_message_text,

Line 47811: 'UPDATE msc_st_supplies'

47807: END IF;
47808:
47809: v_sql_stmt := 06;
47810: lv_sql_stmt :=
47811: 'UPDATE msc_st_supplies'
47812: ||' SET order_number = wip_entity_name'
47813: ||' WHERE NVL(order_number,'||''''||NULL_CHAR||''''||')'
47814: ||' = '||''''||NULL_CHAR||''''
47815: ||' AND deleted_flag = '||SYS_NO

Line 47832: 'UPDATE msc_st_supplies '

47828: -- ASSEMBLY NAME in BOM NAME column for all such records
47829:
47830: v_sql_stmt := 07;
47831: lv_sql_stmt :=
47832: 'UPDATE msc_st_supplies '
47833: ||' SET bill_name = item_name'
47834: ||' WHERE sr_instance_code = :v_instance_code'
47835: ||' AND order_type in (3, 14)'
47836: ||' AND process_flag ='||G_IN_PROCESS

Line 47854: 'UPDATE msc_st_supplies '

47850:
47851: v_sql_stmt := 08;
47852:
47853: lv_sql_stmt :=
47854: 'UPDATE msc_st_supplies '
47855: ||' SET routing_name = item_name'
47856: ||' WHERE sr_instance_code = :v_instance_code'
47857: ||' AND order_type in (3, 14)'
47858: ||' AND process_flag ='|| G_IN_PROCESS

Line 47871: (p_table_name => 'MSC_ST_SUPPLIES',

47867:
47868: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code,lv_batch_id;
47869:
47870: lv_return := MSC_ST_UTIL.DERIVE_BILL_SEQUENCE_ID
47871: (p_table_name => 'MSC_ST_SUPPLIES',
47872: p_bom_col_name => 'BILL_NAME',
47873: p_bom_col_id => 'BILL_SEQUENCE_ID',
47874: p_instance_code => v_instance_code,
47875: p_batch_id => lv_batch_id,

Line 47885: (p_table_name => 'MSC_ST_SUPPLIES',

47881: RAISE ex_logging_err;
47882: END IF;
47883:
47884: lv_return := MSC_ST_UTIL.DERIVE_ROUTING_SEQUENCE_ID
47885: (p_table_name => 'MSC_ST_SUPPLIES',
47886: p_rtg_col_name => 'ROUTING_NAME',
47887: p_rtg_col_id => 'ROUTING_SEQUENCE_ID',
47888: p_instance_code => v_instance_code,
47889: p_batch_id => lv_batch_id,

Line 47902: 'UPDATE msc_st_supplies mss'

47898:
47899:
47900: v_sql_stmt := 09;
47901: lv_sql_stmt :=
47902: 'UPDATE msc_st_supplies mss'
47903: ||' SET schedule_group_id = (SELECT local_id'
47904: ||' FROM msc_local_id_supply mls'
47905: ||' WHERE mls.char4 = mss.schedule_group_name'
47906: ||' AND mls.char3 = mss.organization_code'

Line 47967: (p_table_name => 'MSC_ST_SUPPLIES',

47963: --Log a warning for those records where the bill_sequence_id or
47964: --routing_sequence_id has null values
47965:
47966: lv_return := MSC_ST_UTIL.LOG_ERROR
47967: (p_table_name => 'MSC_ST_SUPPLIES',
47968: p_instance_code => v_instance_code,
47969: p_row => lv_column_names,
47970: p_severity => G_SEV_WARNING,
47971: p_message_text => lv_message_text,

Line 47984: 'UPDATE msc_st_supplies mss'

47980:
47981: --Deriving wip_entity_id
47982: v_sql_stmt := 10;
47983: lv_sql_stmt :=
47984: 'UPDATE msc_st_supplies mss'
47985: ||' SET wip_entity_id = (SELECT local_id'
47986: ||' FROM msc_local_id_supply mls'
47987: ||' WHERE mls.char4 = mss.wip_entity_name'
47988: ||' AND mls.char3 = mss.organization_code'

Line 48018: 'UPDATE msc_st_supplies '

48014:
48015: v_sql_stmt := 11;
48016:
48017: lv_sql_stmt :=
48018: 'UPDATE msc_st_supplies '
48019: ||' SET process_flag = '||G_ERROR_FLG||','
48020: ||' error_text = '||''''||lv_message_text||''''
48021: ||' WHERE NVL(wip_entity_id,'||NULL_VALUE||') = '||NULL_VALUE
48022: ||' AND deleted_flag ='||SYS_YES

Line 48038: 'UPDATE msc_st_supplies '

48034:
48035: -- update the jump_op_seq_num for lot based jobs for the operations jumped outside the network
48036:
48037: lv_sql_stmt :=
48038: 'UPDATE msc_st_supplies '
48039: ||' SET jump_op_seq_num = 50000'
48040: ||' WHERE NVL(jump_op_seq_code,'||''''||NULL_CHAR||''''||') '
48041: ||' = '||''''||50000||''''
48042: ||' AND process_flag = '||G_IN_PROCESS

Line 48056: 'UPDATE msc_st_supplies mss'

48052: v_instance_code;
48053:
48054:
48055: lv_sql_stmt :=
48056: 'UPDATE msc_st_supplies mss'
48057: ||' SET jump_op_seq_num = (SELECT number1'
48058: ||' FROM msc_local_id_setup mls'
48059: ||' WHERE NVL(mls.char5,'||''''||NULL_CHAR||''''||') = '
48060: ||' NVL(mss.jump_op_seq_code,'||''''||NULL_CHAR||''''||') '

Line 48091: pEntityName => 'MSC_ST_SUPPLIES_WO',

48087: (ERRBUF => lv_error_text,
48088: RETCODE => lv_return,
48089: pBatchID => lv_batch_id,
48090: pInstanceCode => v_instance_code,
48091: pEntityName => 'MSC_ST_SUPPLIES_WO',
48092: pInstanceID => v_instance_id);
48093:
48094: IF NVL(lv_return,0) <> 0 THEN
48095: RAISE ex_logging_err;

Line 48105: UPDATE msc_st_supplies

48101:
48102: IF c2%ROWCOUNT > 0 THEN
48103: v_sql_stmt := 12;
48104: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
48105: UPDATE msc_st_supplies
48106: SET wip_entity_id = msc_st_wip_entity_id_s.NEXTVAL
48107: WHERE rowid = lb_rowid(j);
48108:
48109: v_sql_stmt := 13;

Line 48151: FROM msc_st_supplies

48147: v_current_date,
48148: v_current_user,
48149: v_current_date,
48150: v_current_user
48151: FROM msc_st_supplies
48152: WHERE rowid = lb_rowid(j);
48153:
48154: END IF;
48155: CLOSE c2 ;

Line 48164: UPDATE msc_st_supplies

48160:
48161: IF c3%ROWCOUNT > 0 THEN
48162: v_sql_stmt := 14;
48163: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
48164: UPDATE msc_st_supplies
48165: SET schedule_group_id = msc_st_schedule_group_id_s.NEXTVAL
48166: WHERE rowid = lb_rowid(j);
48167:
48168: v_sql_stmt := 15;

Line 48210: FROM msc_st_supplies

48206: v_current_date,
48207: v_current_user,
48208: v_current_date,
48209: v_current_user
48210: FROM msc_st_supplies
48211: WHERE rowid = lb_rowid(j);
48212:
48213: END IF;
48214: CLOSE c3;

Line 48218: UPDATE msc_st_supplies

48214: CLOSE c3;
48215:
48216: --Update disposition_id with the wip_entity_id.
48217: v_sql_stmt := 16;
48218: UPDATE msc_st_supplies
48219: SET disposition_id = wip_entity_id
48220: WHERE process_flag = G_IN_PROCESS
48221: AND batch_id = lv_batch_id
48222: AND sr_instance_code = v_instance_code;

Line 48241: (p_table_name => 'MSC_ST_SUPPLIES',

48237: END IF;
48238:
48239: --Derive Project Id.
48240: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
48241: (p_table_name => 'MSC_ST_SUPPLIES',
48242: p_proj_col_name => 'PROJECT_NUMBER',
48243: p_proj_task_col_id => 'PROJECT_ID',
48244: p_instance_code => v_instance_code,
48245: p_entity_name => 'PROJECT_ID',

Line 48274: (p_table_name => 'MSC_ST_SUPPLIES',

48270: END IF;
48271:
48272: --Derive Task Id.
48273: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
48274: (p_table_name => 'MSC_ST_SUPPLIES',
48275: p_proj_col_name => 'PROJECT_NUMBER',
48276: p_proj_task_col_id => 'TASK_ID',
48277: p_instance_code => v_instance_code,
48278: p_entity_name => 'TASK_ID',

Line 48294: 'UPDATE msc_st_supplies mss '

48290:
48291:
48292: v_sql_stmt := 17;
48293: lv_sql_stmt :=
48294: 'UPDATE msc_st_supplies mss '
48295: ||' SET schedule_group_id = (SELECT local_id'
48296: ||' FROM msc_local_id_supply mls'
48297: ||' WHERE mls.char4 = mss.schedule_group_name'
48298: ||' AND mls.char3 = mss.organization_code'

Line 48323: UPDATE msc_st_supplies

48319: IF c4%ROWCOUNT > 0 THEN
48320:
48321: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
48322:
48323: UPDATE msc_st_supplies
48324: SET job_op_seq_num =
48325: to_number(decode(length(rtrim(job_op_seq_code,'0123456789')),
48326: NULL,job_op_seq_code,'1'))
48327: WHERE rowid = lb_rowid(j);

Line 48332: (p_table_name => 'MSC_ST_SUPPLIES',

48328: END IF;
48329: CLOSE c4;
48330:
48331: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
48332: (p_table_name => 'MSC_ST_SUPPLIES',
48333: p_instance_id => v_instance_id,
48334: p_instance_code => v_instance_code,
48335: p_process_flag => G_VALID,
48336: p_error_text => lv_error_text,

Line 48344: (p_table_name => 'MSC_ST_SUPPLIES',

48340: RAISE ex_logging_err;
48341: END IF;
48342:
48343: lv_return := MSC_ST_UTIL.LOG_ERROR
48344: (p_table_name => 'MSC_ST_SUPPLIES',
48345: p_instance_code => v_instance_code,
48346: p_row => lv_column_names,
48347: p_severity => G_SEV_ERROR,
48348: p_message_text => NULL,

Line 48693: p_token_value2 => 'MSC_ST_SUPPLIES',

48689: p_token1 => 'COLUMN_NAMES',
48690: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
48691: ||'ORGANIZATION_CODE,WIP_ENTITY_NAME',
48692: p_token2 => 'MASTER_TABLE',
48693: p_token_value2 => 'MSC_ST_SUPPLIES',
48694: p_token3 => 'CHILD_TABLE' ,
48695: p_token_value3 => 'MSC_ST_RESOURCE_REQUIREMENTS' );
48696:
48697: IF lv_return <> 0 THEN

Line 50011: p_token_value2 => 'MSC_ST_SUPPLIES',

50007: p_token1 => 'COLUMN_NAMES',
50008: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
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

Line 51219: p_token_value2 => 'MSC_ST_SUPPLIES',

51215: p_token1 => 'COLUMN_NAMES',
51216: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
51217: ||'ORGANIZATION_CODE,WIP_ENTITY_NAME',
51218: p_token2 => 'MASTER_TABLE',
51219: p_token_value2 => 'MSC_ST_SUPPLIES',
51220: p_token3 => 'CHILD_TABLE' ,
51221: p_token_value3 => 'MSC_ST_JOB_OPERATION_NETWORKS' );
51222:
51223: IF lv_return <> 0 THEN

Line 52031: p_token_value2 => 'MSC_ST_SUPPLIES',

52027: p_token1 => 'COLUMN_NAMES',
52028: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
52029: ||'ORGANIZATION_CODE,WIP_ENTITY_NAME',
52030: p_token2 => 'MASTER_TABLE',
52031: p_token_value2 => 'MSC_ST_SUPPLIES',
52032: p_token3 => 'CHILD_TABLE' ,
52033: p_token_value3 => 'MSC_ST_JOB_OPERATION_NETWORKS' );
52034:
52035: IF lv_return <> 0 THEN

Line 52621: p_token_value2 => 'MSC_ST_SUPPLIES',

52617: p_token1 => 'COLUMN_NAMES',
52618: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
52619: ||'ORGANIZATION_CODE,WIP_ENTITY_NAME',
52620: p_token2 => 'MASTER_TABLE',
52621: p_token_value2 => 'MSC_ST_SUPPLIES',
52622: p_token3 => 'CHILD_TABLE' ,
52623: p_token_value3 => 'MSC_ST_JOB_REQUIREMENT_OPS' );
52624:
52625: IF lv_return <> 0 THEN

Line 53449: p_token_value2 => 'MSC_ST_SUPPLIES',

53445: p_token1 => 'COLUMN_NAMES',
53446: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
53447: ||'ORGANIZATION_CODE,WIP_ENTITY_NAME',
53448: p_token2 => 'MASTER_TABLE',
53449: p_token_value2 => 'MSC_ST_SUPPLIES',
53450: p_token3 => 'CHILD_TABLE' ,
53451: p_token_value3 => 'MSC_ST_JOB_OP_RESOURCES' );
53452:
53453: IF lv_return <> 0 THEN

Line 54317: 'UPDATE msc_st_supplies mss1 '

54313: --Duplicate records check for the records whose source is XML for
54314: --PO, Purchase Req and Intransit supplies
54315: v_sql_stmt := 03;
54316: lv_sql_stmt :=
54317: 'UPDATE msc_st_supplies mss1 '
54318: ||' SET process_flag = '||G_ERROR_FLG||','
54319: ||' error_text = '||''''||lv_message_text||''''
54320: ||' WHERE message_id < (SELECT MAX(message_id)'
54321: ||' FROM msc_st_supplies mss2'

Line 54321: ||' FROM msc_st_supplies mss2'

54317: 'UPDATE msc_st_supplies mss1 '
54318: ||' SET process_flag = '||G_ERROR_FLG||','
54319: ||' error_text = '||''''||lv_message_text||''''
54320: ||' WHERE message_id < (SELECT MAX(message_id)'
54321: ||' FROM msc_st_supplies mss2'
54322: ||' WHERE mss2.sr_instance_code'
54323: ||' = mss1.sr_instance_code'
54324: ||' AND mss2.order_number = mss1.order_number'
54325: ||' AND mss2.purch_line_num = mss1.purch_line_num'

Line 54347: 'UPDATE msc_st_supplies mss1'

54343: --Duplicate records check for the records whose source is XML for
54344: --Plan Orders
54345: v_sql_stmt := 04;
54346: lv_sql_stmt :=
54347: 'UPDATE msc_st_supplies mss1'
54348: ||' SET process_flag = '||G_ERROR_FLG||','
54349: ||' error_text = '||''''||lv_message_text||''''
54350: ||' WHERE message_id < (SELECT MAX(message_id)'
54351: ||' FROM msc_st_supplies mss2'

Line 54351: ||' FROM msc_st_supplies mss2'

54347: 'UPDATE msc_st_supplies mss1'
54348: ||' SET process_flag = '||G_ERROR_FLG||','
54349: ||' error_text = '||''''||lv_message_text||''''
54350: ||' WHERE message_id < (SELECT MAX(message_id)'
54351: ||' FROM msc_st_supplies mss2'
54352: ||' WHERE mss2.sr_instance_code '
54353: ||' = mss1.sr_instance_code'
54354: ||' AND NVL(mss2.company_name,'||''''||NULL_CHAR||''''||') = '
54355: ||' NVL(mss1.company_name,'||''''||NULL_CHAR||''''||')'

Line 54378: 'UPDATE msc_st_supplies mss1 '

54374: --Duplicate records check for the records whose source is XML for
54375: --on hand supplies
54376: v_sql_stmt := 05;
54377: lv_sql_stmt :=
54378: 'UPDATE msc_st_supplies mss1 '
54379: ||' SET process_flag = '||G_ERROR_FLG||','
54380: ||' error_text = '||''''||lv_message_text||''''
54381: ||' WHERE message_id < (SELECT MAX(message_id)'
54382: ||' FROM msc_st_supplies mss2'

Line 54382: ||' FROM msc_st_supplies mss2'

54378: 'UPDATE msc_st_supplies mss1 '
54379: ||' SET process_flag = '||G_ERROR_FLG||','
54380: ||' error_text = '||''''||lv_message_text||''''
54381: ||' WHERE message_id < (SELECT MAX(message_id)'
54382: ||' FROM msc_st_supplies mss2'
54383: ||' WHERE mss2.sr_instance_code = mss1.sr_instance_code'
54384: ||' AND NVL(mss2.company_name,'||''''||NULL_CHAR||''''||') = '
54385: ||' NVL(mss1.company_name,'||''''||NULL_CHAR||''''||')'
54386: ||' AND mss2.organization_code = mss1.organization_code '

Line 54430: 'UPDATE msc_st_supplies mss1 '

54426: --whereas in batch load we cannot.
54427: --Check for PO, purchase req. and intransit supplies.
54428: v_sql_stmt := 06;
54429: lv_sql_stmt :=
54430: 'UPDATE msc_st_supplies mss1 '
54431: ||' SET process_flag = '||G_ERROR_FLG||','
54432: ||' error_text = '||''''||lv_message_text||''''
54433: ||' WHERE EXISTS( SELECT 1 '
54434: ||' FROM msc_st_supplies mss2'

Line 54434: ||' FROM msc_st_supplies mss2'

54430: 'UPDATE msc_st_supplies mss1 '
54431: ||' SET process_flag = '||G_ERROR_FLG||','
54432: ||' error_text = '||''''||lv_message_text||''''
54433: ||' WHERE EXISTS( SELECT 1 '
54434: ||' FROM msc_st_supplies mss2'
54435: ||' WHERE mss2.sr_instance_code'
54436: ||' = mss1.sr_instance_code'
54437: ||' AND NVL(mss2.company_name, '||''''||NULL_CHAR||''''||') = '
54438: ||' NVL(mss1.company_name, '||''''||NULL_CHAR||''''||')'

Line 54462: 'UPDATE msc_st_supplies mss1 '

54458:
54459: --Duplicate Check for plan orders and return forecasts(batch load).
54460: v_sql_stmt := 08;
54461: lv_sql_stmt :=
54462: 'UPDATE msc_st_supplies mss1 '
54463: ||' SET process_flag = '||G_ERROR_FLG||','
54464: ||' error_text = '||''''||lv_message_text||''''
54465: ||' WHERE EXISTS( SELECT 1 '
54466: ||' FROM msc_st_supplies mss2'

Line 54466: ||' FROM msc_st_supplies mss2'

54462: 'UPDATE msc_st_supplies mss1 '
54463: ||' SET process_flag = '||G_ERROR_FLG||','
54464: ||' error_text = '||''''||lv_message_text||''''
54465: ||' WHERE EXISTS( SELECT 1 '
54466: ||' FROM msc_st_supplies mss2'
54467: ||' WHERE mss2.sr_instance_code'
54468: ||' = mss1.sr_instance_code'
54469: ||' AND NVL(mss2.company_name,'||''''||NULL_CHAR||''''||') = '
54470: ||' NVL(mss1.company_name,'||''''||NULL_CHAR||''''||')'

Line 54494: 'UPDATE msc_st_supplies mss1'

54490:
54491: --Duplicate Check for on hand supplies(batch load).
54492: v_sql_stmt := 09;
54493: lv_sql_stmt :=
54494: 'UPDATE msc_st_supplies mss1'
54495: ||' SET process_flag = '||G_ERROR_FLG||','
54496: ||' error_text = '||''''||lv_message_text||''''
54497: ||' WHERE EXISTS( SELECT 1 '
54498: ||' FROM msc_st_supplies mss2'

Line 54498: ||' FROM msc_st_supplies mss2'

54494: 'UPDATE msc_st_supplies mss1'
54495: ||' SET process_flag = '||G_ERROR_FLG||','
54496: ||' error_text = '||''''||lv_message_text||''''
54497: ||' WHERE EXISTS( SELECT 1 '
54498: ||' FROM msc_st_supplies mss2'
54499: ||' WHERE mss2.sr_instance_code = mss1.sr_instance_code'
54500: ||' AND NVL(mss2.company_name,'||''''||NULL_CHAR||''''||') = '
54501: ||' NVL(mss1.company_name,'||''''||NULL_CHAR||''''||')'
54502: ||' AND mss2.organization_code = mss1.organization_code '

Line 54554: 'UPDATE msc_st_supplies '

54550:
54551:
54552: v_sql_stmt := 10;
54553: lv_sql_stmt :=
54554: 'UPDATE msc_st_supplies '
54555: ||' SET process_flag = '||G_ERROR_FLG||','
54556: ||' error_text = '||''''||lv_message_text||''''
54557: ||' WHERE order_type NOT IN(1,2,3,5,7,8,11,12,14,15,16,18,27,75,73,74,87,86,81)'
54558: ||' AND process_flag = '||G_IN_PROCESS

Line 55763: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SUPPLIES',p_erp_enabled => 'Y');

55759: END IF;
55760:
55761: IF v_mat_sup_enabled = SYS_YES OR v_iro_enabled = SYS_YES or v_ero_enabled = SYS_YES
55762: THEN
55763: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SUPPLIES',p_erp_enabled => 'Y');
55764: IF lv_count > 0 Then
55765: prec.oh_flag:= SYS_YES;
55766: prec.po_flag:= SYS_YES;
55767: prec.mps_flag:= SYS_YES;

Line 58952: cursor c2 is SELECT rowid FROM msc_st_supplies

58948: lb_batch NumTblTyp := NumTblTyp(0);
58949: lv_batch_id PLS_INTEGER;
58950: lv_batch_start PLS_INTEGER;
58951: lv_batch_last PLS_INTEGER;
58952: cursor c2 is SELECT rowid FROM msc_st_supplies
58953: WHERE PROCESS_FLAG IN(G_IN_PROCESS,G_ERROR_FLG)
58954: AND ORDER_TYPE IN(1,2,5,8,11,12,18,73,74,87,81)
58955: AND NVL(batch_id,NULL_VALUE) = NULL_VALUE
58956: AND sr_instance_code = v_instance_code;

Line 58980: UPDATE msc_st_supplies

58976:
58977: lv_batch_last := lv_batch_id;
58978:
58979: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
58980: UPDATE msc_st_supplies
58981: SET batch_id = lb_batch(j)
58982: WHERE rowid = lb_rowid(j);
58983:
58984: commit;