[Home] [Help]
21262: ||' WHERE process_flag = '||G_IN_PROCESS
21263: ||' AND batch_id = :lv_batch_id'
21264: ||' AND sr_instance_code = :v_instance_code'
21265: ||' AND EXISTS'
21266: ||' (SELECT 1 FROM msc_item_substitutes mis2'
21267: ||' WHERE mis2.higher_item_id = mis1.higher_item_id'
21268: ||' AND mis2.lower_item_id = mis1.lower_item_id'
21269: ||' AND nvl(mis2.customer_id, '||NULL_VALUE||') = nvl(mis1.customer_id,'||NULL_VALUE||')'
21270: ||' AND nvl(mis2.customer_site_id, '||NULL_VALUE||') = nvl(mis1.customer_site_id,'||NULL_VALUE||')'
21370: --runs in the incremental mode.
21371:
21372: v_sql_stmt := 10;
21373: lv_sql_stmt :=
21374: 'INSERT INTO msc_item_substitutes'
21375: ||'( PLAN_ID,'
21376: ||' HIGHER_ITEM_ID,'
21377: ||' LOWER_ITEM_ID,'
21378: ||' RECIPROCAL_FLAG,'
31796: p_token1 => 'COLUMN_NAMES',
31797: p_token_value1 => 'ORIGINAL_ITEM,ITEM_NAME,'
31798: ||'RECIPROCAL_FLAG,SR_INSTANCE_CODE',
31799: p_token2 => 'MASTER_TABLE',
31800: p_token_value2 => 'MSC_ST_ITEM_SUBSTITUTES/MSC_ITEM_SUBSTITUTES',
31801: p_token3 => 'CHILD_TABLE' ,
31802: p_token_value3 => 'MSC_ST_SALES_ORDERS' );
31803:
31804: IF lv_return <> 0 THEN
31812: error_text = lv_message_text
31813: WHERE original_item_name is not null
31814: AND deleted_flag = SYS_NO
31815: AND NOT EXISTS(SELECT 1
31816: FROM msc_item_substitutes mis
31817: WHERE ((mis.reciprocal_flag = 1
31818: AND ((mis.lower_item_id = original_item_id
31819: AND mis.higher_item_id = inventory_item_id )
31820: OR (mis.higher_item_id = original_item_id