[Home] [Help]
24751: ||' WHERE process_flag = '||G_IN_PROCESS
24752: ||' AND batch_id = :lv_batch_id'
24753: ||' AND sr_instance_code = :v_instance_code'
24754: ||' AND EXISTS'
24755: ||' (SELECT 1 FROM msc_item_substitutes mis2'
24756: ||' WHERE mis2.higher_item_id = mis1.higher_item_id'
24757: ||' AND mis2.lower_item_id = mis1.lower_item_id'
24758: ||' AND nvl(mis2.customer_id, '||NULL_VALUE||') = nvl(mis1.customer_id,'||NULL_VALUE||')'
24759: ||' AND nvl(mis2.customer_site_id, '||NULL_VALUE||') = nvl(mis1.customer_site_id,'||NULL_VALUE||')'
24859: --runs in the incremental mode.
24860:
24861: v_sql_stmt := 10;
24862: lv_sql_stmt :=
24863: 'INSERT INTO msc_item_substitutes'
24864: ||'( PLAN_ID,'
24865: ||' HIGHER_ITEM_ID,'
24866: ||' LOWER_ITEM_ID,'
24867: ||' RECIPROCAL_FLAG,'
36515: p_token1 => 'COLUMN_NAMES',
36516: p_token_value1 => 'ORIGINAL_ITEM,ITEM_NAME,'
36517: ||'RECIPROCAL_FLAG,SR_INSTANCE_CODE',
36518: p_token2 => 'MASTER_TABLE',
36519: p_token_value2 => 'MSC_ST_ITEM_SUBSTITUTES/MSC_ITEM_SUBSTITUTES',
36520: p_token3 => 'CHILD_TABLE' ,
36521: p_token_value3 => 'MSC_ST_SALES_ORDERS' );
36522:
36523: IF lv_return <> 0 THEN
36531: error_text = lv_message_text
36532: WHERE original_item_name is not null
36533: AND deleted_flag = SYS_NO
36534: AND NOT EXISTS(SELECT 1
36535: FROM msc_item_substitutes mis
36536: WHERE ((mis.reciprocal_flag = 1
36537: AND ((mis.lower_item_id = original_item_id
36538: AND mis.higher_item_id = inventory_item_id )
36539: OR (mis.higher_item_id = original_item_id