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_DESIGNATORS

Line 17819: | msc_st_designators. |

17815: END LOAD_UOM_CLASS_CONV;
17816:
17817: /*==========================================================================+
17818: | DESCRIPTION : This procedure validates the records and updates the table |
17819: | msc_st_designators. |
17820: | |
17821: | Designator Types Type Code |
17822: | ---------------------------------------------------------- |
17823: | Manual MDS 1 |

Line 17836: lv_batch_id msc_st_designators.batch_id%TYPE;

17832: lv_where_str VARCHAR2(5000);
17833: lv_sql_stmt VARCHAR2(5000);
17834: lv_return NUMBER;
17835:
17836: lv_batch_id msc_st_designators.batch_id%TYPE;
17837: lv_message_text msc_errors.error_text%TYPE;
17838: ex_logging_err EXCEPTION;
17839:
17840: CURSOR c1(lv_batch_id NUMBER) IS

Line 17842: FROM msc_st_designators

17838: ex_logging_err EXCEPTION;
17839:
17840: CURSOR c1(lv_batch_id NUMBER) IS
17841: SELECT rowid
17842: FROM msc_st_designators
17843: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
17844: AND sr_instance_code = v_instance_code
17845: AND batch_id = lv_batch_id;
17846:

Line 17863: 'UPDATE msc_st_designators msd1 '

17859: --Duplicate records check for the records whose source is XML
17860: --Based on bug#2736771 UDK has been changed.
17861: v_sql_stmt := 01;
17862: lv_sql_stmt :=
17863: 'UPDATE msc_st_designators msd1 '
17864: ||' SET process_flag = '||G_ERROR_FLG||','
17865: ||' error_text = '||''''||lv_message_text||''''
17866: ||' WHERE message_id < (SELECT MAX(message_id)'
17867: ||' FROM msc_st_designators msd2'

Line 17867: ||' FROM msc_st_designators msd2'

17863: 'UPDATE msc_st_designators msd1 '
17864: ||' SET process_flag = '||G_ERROR_FLG||','
17865: ||' error_text = '||''''||lv_message_text||''''
17866: ||' WHERE message_id < (SELECT MAX(message_id)'
17867: ||' FROM msc_st_designators msd2'
17868: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code'
17869: ||' AND NVL(msd2.company_name, '||''''||NULL_CHAR||''''||') = '
17870: ||' NVL(msd1.company_name, '||''''||NULL_CHAR||''''||')'
17871: ||' AND msd2.organization_code = msd1.organization_code'

Line 17901: 'UPDATE msc_st_designators msd1'

17897: --Different SQL is used because in XML we can identify the latest records
17898: --whereas in batch load we cannot.
17899: v_sql_stmt := 02;
17900: lv_sql_stmt :=
17901: 'UPDATE msc_st_designators msd1'
17902: ||' SET process_flag = '||G_ERROR_FLG||','
17903: ||' error_text = '||''''||lv_message_text||''''
17904: ||' WHERE EXISTS( SELECT 1 '
17905: ||' FROM msc_st_designators msd2'

Line 17905: ||' FROM msc_st_designators msd2'

17901: 'UPDATE msc_st_designators msd1'
17902: ||' SET process_flag = '||G_ERROR_FLG||','
17903: ||' error_text = '||''''||lv_message_text||''''
17904: ||' WHERE EXISTS( SELECT 1 '
17905: ||' FROM msc_st_designators msd2'
17906: ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code'
17907: ||' AND NVL(msd2.company_name, '||''''||NULL_CHAR||''''||') = '
17908: ||' NVL(msd1.company_name, '||''''||NULL_CHAR||''''||')'
17909: ||' AND msd2.organization_code = msd1.organization_code'

Line 17963: 'UPDATE msc_st_designators '

17959: FROM dual;
17960:
17961: v_sql_stmt := 04;
17962: lv_sql_stmt :=
17963: 'UPDATE msc_st_designators '
17964: ||' SET batch_id = :lv_batch_id'
17965: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
17966: ||' AND sr_instance_code = :v_instance_code'
17967: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 17985: UPDATE msc_st_designators

17981: CLOSE c1;
17982:
17983: v_sql_stmt := 05;
17984: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
17985: UPDATE msc_st_designators
17986: SET st_transaction_id = msc_st_designators_s.NEXTVAL,
17987: refresh_id = v_refresh_id,
17988: last_update_date = v_current_date,
17989: last_updated_by = v_current_user,

Line 17986: SET st_transaction_id = msc_st_designators_s.NEXTVAL,

17982:
17983: v_sql_stmt := 05;
17984: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
17985: UPDATE msc_st_designators
17986: SET st_transaction_id = msc_st_designators_s.NEXTVAL,
17987: refresh_id = v_refresh_id,
17988: last_update_date = v_current_date,
17989: last_updated_by = v_current_user,
17990: creation_date = v_current_date,

Line 18008: 'UPDATE msc_st_designators '

18004: END IF;
18005:
18006: v_sql_stmt := 06;
18007: lv_sql_stmt :=
18008: 'UPDATE msc_st_designators '
18009: ||' SET error_text = '||''''||lv_message_text||''''||','
18010: ||' process_flag = '||G_ERROR_FLG
18011: ||' WHERE nvl(designator_type,'||NULL_VALUE||') NOT IN(1,2,6)'
18012: ||' AND deleted_flag = '||SYS_NO

Line 18032: p_token_value1 => 'MSC_ST_DESIGNATORS');

18028: p_error_code => 'MSC_PP_NO_DELETION',
18029: p_message_text => lv_message_text,
18030: p_error_text => lv_error_text,
18031: p_token1 => 'TABLE_NAME',
18032: p_token_value1 => 'MSC_ST_DESIGNATORS');
18033:
18034: IF lv_return <> 0 THEN
18035: RAISE ex_logging_err;
18036: END IF;

Line 18041: 'UPDATE msc_st_designators '

18037:
18038: -- Deletion is not allowed for this table
18039: v_sql_stmt := 07;
18040: lv_sql_stmt :=
18041: 'UPDATE msc_st_designators '
18042: ||' SET process_flag = '||G_ERROR_FLG||','
18043: ||' error_text = '||''''||lv_message_text||''''
18044: ||' WHERE deleted_flag = '||SYS_YES
18045: ||' AND process_flag = '||G_IN_PROCESS

Line 18078: (p_table_name => 'MSC_ST_DESIGNATORS',

18074:
18075: --Log a warning for those records where the deleted_flag has a value other
18076: --SYS_NO
18077: lv_return := MSC_ST_UTIL.LOG_ERROR
18078: (p_table_name => 'MSC_ST_DESIGNATORS',
18079: p_instance_code => v_instance_code,
18080: p_row => lv_column_names,
18081: p_severity => G_SEV_WARNING,
18082: p_message_text => lv_message_text,

Line 18108: (p_table_name => 'MSC_ST_DESIGNATORS',

18104: END IF;
18105:
18106: --Derive Organization_id
18107: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
18108: (p_table_name => 'MSC_ST_DESIGNATORS',
18109: p_org_partner_name => 'ORGANIZATION_CODE',
18110: p_org_partner_id => 'ORGANIZATION_ID',
18111: p_instance_code => v_instance_code,
18112: p_partner_type => G_ORGANIZATION,

Line 18138: 'UPDATE msc_st_designators '

18134: END IF;
18135:
18136: v_sql_stmt := 08;
18137: lv_sql_stmt :=
18138: 'UPDATE msc_st_designators '
18139: ||' SET error_text = '||''''||lv_message_text||''''||','
18140: ||' process_flag = '||G_ERROR_FLG
18141: ||' WHERE NVL(designator,'||''''||NULL_CHAR||''''||')'
18142: ||' = '||''''||NULL_CHAR||''''

Line 18170: MSC_ST_UTIL.VALIDATE_DMD_CLASS(p_table_name => 'MSC_ST_DESIGNATORS',

18166:
18167: -- Validate Demand Class, if value provided it should exists
18168: -- in ODS or staging table
18169: lv_return :=
18170: MSC_ST_UTIL.VALIDATE_DMD_CLASS(p_table_name => 'MSC_ST_DESIGNATORS',
18171: p_dmd_class_column => 'DEMAND_CLASS',
18172: p_instance_id => v_instance_id,
18173: p_instance_code => v_instance_code,
18174: p_severity => G_SEV3_ERROR,

Line 18187: 'UPDATE msc_st_designators '

18183:
18184:
18185: v_sql_stmt := 10;
18186: lv_sql_stmt :=
18187: 'UPDATE msc_st_designators '
18188: ||' SET inventory_atp_flag = '||G_FOR_INV_ATP_FLAG
18189: --||' mps_relief = '||G_FOR_MPS_RELIEF ||','
18190: --||' consume_forecast = '||G_CONSUME_FORECAST ||','
18191: --||' update_type = '||G_UPDATE_TYPE

Line 18207: 'UPDATE msc_st_designators '

18203: v_instance_code;
18204:
18205: v_sql_stmt := 11;
18206: lv_sql_stmt :=
18207: 'UPDATE msc_st_designators '
18208: ||' SET inventory_atp_flag = '||G_INV_ATP_FLAG
18209: ||' WHERE NVL(inventory_atp_flag,'||NULL_VALUE||') NOT IN(1,2)'
18210: ||' AND NVL(designator_type,'||NULL_VALUE||') IN(1,2)'
18211: ||' AND process_flag = '||G_IN_PROCESS

Line 18224: 'UPDATE msc_st_designators '

18220: v_instance_code;
18221:
18222: v_sql_stmt := 12;
18223: lv_sql_stmt :=
18224: 'UPDATE msc_st_designators '
18225: ||' SET mps_relief = '||G_MPS_RELIEF
18226: ||' WHERE designator_type IN(1,2)'
18227: ||' AND process_flag ='|| G_IN_PROCESS
18228: ||' AND batch_id = :lv_batch_id'

Line 18240: ' UPDATE msc_st_designators '

18236: v_instance_code;
18237:
18238: v_sql_stmt := 13;
18239: lv_sql_stmt :=
18240: ' UPDATE msc_st_designators '
18241: ||' SET bucket_type = '||G_BUCKET_TYPE
18242: ||' WHERE NVL(bucket_type,'||NULL_VALUE||') NOT IN(1,2,3,'||NULL_VALUE||')'
18243: ||' AND designator_type = 6'
18244: ||' AND process_flag = '||G_IN_PROCESS

Line 18270: (p_table_name => 'MSC_ST_DESIGNATORS',

18266: END IF;
18267:
18268: --Derive Customer_id
18269: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
18270: (p_table_name => 'MSC_ST_DESIGNATORS',
18271: p_org_partner_name => 'CUSTOMER_NAME',
18272: p_org_partner_id => 'CUSTOMER_ID',
18273: p_instance_code => v_instance_code,
18274: p_partner_type => G_CUSTOMER,

Line 18300: (p_table_name => 'MSC_ST_DESIGNATORS',

18296: END IF;
18297:
18298: --Derive Bill_id
18299: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
18300: (p_table_name => 'MSC_ST_DESIGNATORS',
18301: p_partner_name => 'CUSTOMER_NAME',
18302: p_partner_site_code => 'BILL_TO_SITE_CODE',
18303: p_partner_site_id => 'BILL_ID',
18304: p_instance_code => v_instance_code,

Line 18331: (p_table_name => 'MSC_ST_DESIGNATORS',

18327: END IF;
18328:
18329: --Derive Ship_id
18330: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
18331: (p_table_name => 'MSC_ST_DESIGNATORS',
18332: p_partner_name => 'CUSTOMER_NAME',
18333: p_partner_site_code => 'SHIP_TO_SITE_CODE',
18334: p_partner_site_id => 'SHIP_ID',
18335: p_instance_code => v_instance_code,

Line 18354: pEntityName => 'MSC_ST_DESIGNATORS',

18350: (ERRBUF => lv_error_text,
18351: RETCODE => lv_return,
18352: pBatchID => lv_batch_id,
18353: pInstanceCode => v_instance_code,
18354: pEntityName => 'MSC_ST_DESIGNATORS',
18355: pInstanceID => v_instance_id);
18356:
18357: IF NVL(lv_return,0) <> 0 THEN
18358: RAISE ex_logging_err;

Line 18380: 'UPDATE msc_st_designators msd'

18376: END IF;
18377:
18378:
18379: lv_sql_stmt :=
18380: 'UPDATE msc_st_designators msd'
18381: ||' SET error_text = '||''''||lv_message_text||''''||','
18382: ||' process_flag = '||G_ERROR_FLG
18383: ||' WHERE EXISTS (SELECT 1 '
18384: ||' FROM msc_designators md'

Line 18409: 'insert into MSC_ST_DESIGNATORS'

18405: -- Insert record in staging for forecast set
18406:
18407: v_sql_stmt := 14;
18408: lv_sql_stmt :=
18409: 'insert into MSC_ST_DESIGNATORS'
18410: ||' ( DESIGNATOR,'
18411: ||' FORECAST_SET,'
18412: ||' COMPANY_ID,'
18413: ||' ORGANIZATION_ID,'

Line 18465: ||' from MSC_ST_DESIGNATORS msd'

18461: ||' MESSAGE_ID,'
18462: ||' DATA_SOURCE_TYPE,'
18463: || G_IN_PROCESS||','
18464: ||' SR_INSTANCE_CODE'
18465: ||' from MSC_ST_DESIGNATORS msd'
18466: ||' WHERE NOT EXISTS ('
18467: ||' SELECT 1 FROM MSC_DESIGNATORS md'
18468: ||' WHERE md.designator = msd.forecast_set'
18469: ||' AND NVL(md.forecast_set_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 18474: ||' FROM msc_st_designators msd1'

18470: ||' AND md.organization_id = msd.organization_id'
18471: ||' AND md.sr_instance_id = :v_instance_id'
18472: ||' UNION'
18473: ||' SELECT 1'
18474: ||' FROM msc_st_designators msd1'
18475: ||' WHERE msd.forecast_set = msd1.designator'
18476: ||' AND msd.organization_code = msd1.organization_code'
18477: ||' AND msd1.sr_instance_code = :v_instance_code'
18478: ||' AND msd1.designator_type = 6'

Line 18503: (p_table_name => 'MSC_ST_DESIGNATORS',

18499:
18500:
18501:
18502: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
18503: (p_table_name => 'MSC_ST_DESIGNATORS',
18504: p_instance_id => v_instance_id,
18505: p_instance_code => v_instance_code,
18506: p_process_flag => G_VALID,
18507: p_error_text => lv_error_text,

Line 18516: (p_table_name => 'MSC_ST_DESIGNATORS',

18512: RAISE ex_logging_err;
18513: END IF;
18514:
18515: lv_return := MSC_ST_UTIL.LOG_ERROR
18516: (p_table_name => 'MSC_ST_DESIGNATORS',
18517: p_instance_code => v_instance_code,
18518: p_row => lv_column_names,
18519: p_severity => G_SEV_ERROR,
18520: p_message_text => NULL,

Line 30362: p_token_value2 => 'MSC_ST_DESIGNATORS',

30358: ||' ORGANIZATION_CODE,'
30359: ||' DESIGNATOR_TYPE AND'
30360: ||' DESIGNATOR',
30361: p_token2 => 'MASTER_TABLE',
30362: p_token_value2 => 'MSC_ST_DESIGNATORS',
30363: p_token3 => 'CHILD_TABLE',
30364: p_token_value3 => 'MSC_ST_SUPPLIES');
30365:
30366: IF lv_return <> 0 THEN

Line 30376: ||' FROM msc_st_designators msd'

30372: 'UPDATE msc_st_supplies mss'
30373: ||' SET process_flag = '||G_ERROR_FLG||','
30374: ||' error_text = '||''''||lv_message_text||''''
30375: ||' WHERE NOT EXISTS(SELECT 1'
30376: ||' FROM msc_st_designators msd'
30377: ||' WHERE msd.designator = mss.schedule_designator'
30378: ||' AND msd.organization_code = mss.organization_code'
30379: ||' AND NVL(msd.company_name,'||''''||NULL_CHAR||''''||') = '
30380: ||' NVL(mss.company_name,'||''''||NULL_CHAR||''''||') '

Line 30420: p_token_value2 => 'MSC_ST_DESIGNATORS',

30416: p_error_text => lv_error_text,
30417: p_token1 => 'COLUMN_NAMES',
30418: p_token_value1 => 'SHIP_TO_PARTY_NAME',
30419: p_token2 => 'MASTER_TABLE',
30420: p_token_value2 => 'MSC_ST_DESIGNATORS',
30421: p_token3 => 'CHILD_TABLE',
30422: p_token_value3 => 'MSC_ST_SUPPLIES');
30423:
30424: IF lv_return <> 0 THEN

Line 30459: p_token_value2 => 'MSC_ST_DESIGNATORS',

30455: p_token1 => 'COLUMN_NAMES',
30456: p_token_value1 => 'SHIP_TO_PARTY_NAME AND'
30457: ||' SHIP_TO_SITE_CODE',
30458: p_token2 => 'MASTER_TABLE',
30459: p_token_value2 => 'MSC_ST_DESIGNATORS',
30460: p_token3 => 'CHILD_TABLE',
30461: p_token_value3 => 'MSC_ST_SUPPLIES');
30462:
30463: IF lv_return <> 0 THEN

Line 34409: p_token_value2 => 'MSC_ST_DESIGNATORS',

34405: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
34406: ||' ORGANIZATION_CODE,'
34407: ||' DEMAND_SCHEDULE_NAME',
34408: p_token2 => 'MASTER_TABLE',
34409: p_token_value2 => 'MSC_ST_DESIGNATORS',
34410: p_token3 => 'CHILD_TABLE' ,
34411: p_token_value3 => 'MSC_ST_DEMANDS' );
34412:
34413: IF lv_return <> 0 THEN

Line 34425: ||' FROM msc_st_designators msd'

34421: 'UPDATE msc_st_demands msdm'
34422: ||' SET process_flag = '||G_ERROR_FLG||','
34423: ||' error_text = '||''''||lv_message_text||''''
34424: ||' WHERE NOT EXISTS(SELECT 1'
34425: ||' FROM msc_st_designators msd'
34426: ||' WHERE msd.designator = msdm.demand_schedule_name'
34427: ||' AND NVL(msd.company_name,'||''''||NULL_CHAR||''''||') '
34428: ||' = NVL(msdm.company_name,'||''''||NULL_CHAR||''''||') '
34429: ||' AND msd.organization_code = msdm.organization_code'

Line 34507: p_token_value2 => 'MSC_ST_DESIGNATORS',

34503: p_token1 => 'COLUMN_NAMES',
34504: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
34505: ||'ORGANIZATION_CODE,FORECAST_DESIGNATOR',
34506: p_token2 => 'MASTER_TABLE',
34507: p_token_value2 => 'MSC_ST_DESIGNATORS',
34508: p_token3 => 'CHILD_TABLE' ,
34509: p_token_value3 => 'MSC_ST_DEMANDS' );
34510:
34511: IF lv_return <> 0 THEN

Line 34523: ||' FROM msc_st_designators msd'

34519: 'UPDATE msc_st_demands msdm'
34520: ||' SET process_flag = '||G_ERROR_FLG||','
34521: ||' error_text = '||''''||lv_message_text||''''
34522: ||' WHERE NOT EXISTS(SELECT 1'
34523: ||' FROM msc_st_designators msd'
34524: ||' WHERE msd.designator = msdm.forecast_designator'
34525: ||' AND NVL(msd.company_name,'||''''||NULL_CHAR||''''||') '
34526: ||' = NVL(msd.company_name,'||''''||NULL_CHAR||''''||') '
34527: ||' AND msd.organization_code = msdm.organization_code'

Line 50879: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_DESIGNATORS',p_erp_enabled => 'Y');

50875: End IF;
50876: END IF;
50877:
50878: IF v_desig_enabled = SYS_YES THEN
50879: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_DESIGNATORS',p_erp_enabled => 'Y');
50880: IF lv_count > 0 Then
50881: prec.mds_flag:= SYS_YES;
50882: prec.forecast_flag:= SYS_YES;
50883: prec.mps_flag:= SYS_YES;