[Home] [Help]
1233: Key - Plan_id + Level_Id + SR_LEVEL_PK
1234: */
1235:
1236: if p_dest_table = MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE then
1237: delete from msd_st_level_values_lb a where
1238: a.plan_id = p_plan_id and
1239: rowid <> (select max(rowid) from msd_st_level_values_lb b
1240: where a.plan_id = b.plan_id and a.level_id = b.level_id and a.sr_level_pk = b.sr_level_pk);
1241: END IF ;
1235:
1236: if p_dest_table = MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE then
1237: delete from msd_st_level_values_lb a where
1238: a.plan_id = p_plan_id and
1239: rowid <> (select max(rowid) from msd_st_level_values_lb b
1240: where a.plan_id = b.plan_id and a.level_id = b.level_id and a.sr_level_pk = b.sr_level_pk);
1241: END IF ;
1242:
1243: End;
1383: /* Delete Staging Table only if delete flag = Yes */
1384: IF (p_delete_flag = 'Y') THEN
1385: /* First time to process this level_id */
1386: IF (p_update_lvl_table = 1) THEN
1387: DELETE FROM msd_st_level_values_lb
1388: WHERE plan_id = p_plan_id AND level_id = p_level_id;
1389: END IF;
1390:
1391: -- fnd_file.put_line(fnd_file.log,'5.1:translate Level Values ' );
1536: /* New Level values will be inserted into fact table
1537: and will get deleted from the staging */
1538: CURSOR c_insert IS
1539: select sr_level_pk
1540: from msd_st_level_values_lb
1541: where plan_id = p_plan_id and level_id = p_level_id
1542: MINUS
1543: select sr_level_pk
1544: from msd_level_values_lb
1550: */
1551: CURSOR c_update IS
1552: (select sr_level_pk, level_value,
1553: level_value_desc
1554: from msd_st_level_values_lb
1555: where plan_id = p_plan_id and level_id = p_level_id
1556: MINUS
1557: select sr_level_pk, level_value,
1558: level_value_desc
1560: where plan_id = p_plan_id and level_id = p_level_id);
1561:
1562:
1563:
1564: TYPE sr_level_pk_tab IS TABLE OF msd_st_level_values.sr_level_pk%TYPE;
1565: TYPE level_val_tab IS TABLE OF msd_st_level_values.level_value%TYPE;
1566: TYPE level_attribute_tab IS TABLE OF msd_st_level_values.level_value_desc%TYPE;
1567:
1568: x_level_pk NUMBER ;
1561:
1562:
1563:
1564: TYPE sr_level_pk_tab IS TABLE OF msd_st_level_values.sr_level_pk%TYPE;
1565: TYPE level_val_tab IS TABLE OF msd_st_level_values.level_value%TYPE;
1566: TYPE level_attribute_tab IS TABLE OF msd_st_level_values.level_value_desc%TYPE;
1567:
1568: x_level_pk NUMBER ;
1569: a_sr_level_pk sr_level_pk_tab;
1562:
1563:
1564: TYPE sr_level_pk_tab IS TABLE OF msd_st_level_values.sr_level_pk%TYPE;
1565: TYPE level_val_tab IS TABLE OF msd_st_level_values.level_value%TYPE;
1566: TYPE level_attribute_tab IS TABLE OF msd_st_level_values.level_value_desc%TYPE;
1567:
1568: x_level_pk NUMBER ;
1569: a_sr_level_pk sr_level_pk_tab;
1570: a_level_pk sr_level_pk_tab:= sr_level_pk_tab();
1582: Insert them into Fact Table.
1583: */
1584: ---fnd_file.put_line(fnd_file.log,'**In 2 PROCESS_LEVEL_VALUE_PER_ROW********');
1585: FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1586: DELETE FROM msd_st_level_values_lb
1587: WHERE plan_id = p_plan_id and
1588: level_id = p_level_id and
1589: sr_level_pk = a_sr_level_pk(i)
1590: RETURNING level_value, level_value_desc
1818: p_seq_num);
1819: ELSE
1820: /* Collect into Staging table*/
1821: --fnd_file.put_line(fnd_file.log,'2:PROCESS_TOP_LEVEL_VALUES '||p_parent_level_id );
1822: delete from msd_st_level_values_lb
1823: where plan_id = p_plan_id
1824: and level_id = p_parent_level_id ;
1825: --- fnd_file.put_line(fnd_file.log,'3:PROCESS_TOP_LEVEL_VALUES ' );
1826:
1984:
1985: if (nvl(retcode,0) = 0 ) then
1986:
1987:
1988: Delete from msd_st_level_values_lb
1989: where plan_id = Relationship_Rec.plan_id
1990: and level_id = Relationship_Rec.level_id ;
1991:
1992: Delete from msd_st_level_associations_lb
2005: End Loop ;
2006:
2007:
2008:
2009: Delete from msd_st_level_values_lb
2010: where level_id in (
2011: select level_id
2012: from msd_levels
2013: where level_type_code = '1'