[Home] [Help]
942: l_pr_end NUMBER;
943:
944: BEGIN
945:
946: --pnp_debug_pkg.enable_file_debug(l_pathname,l_filename);
947: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
948: PNP_DEBUG_PKG.log(' Parameters :');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
943:
944: BEGIN
945:
946: --pnp_debug_pkg.enable_file_debug(l_pathname,l_filename);
947: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
948: PNP_DEBUG_PKG.log(' Parameters :');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
944: BEGIN
945:
946: --pnp_debug_pkg.enable_file_debug(l_pathname,l_filename);
947: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
948: PNP_DEBUG_PKG.log(' Parameters :');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
945:
946: --pnp_debug_pkg.enable_file_debug(l_pathname,l_filename);
947: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
948: PNP_DEBUG_PKG.log(' Parameters :');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
946: --pnp_debug_pkg.enable_file_debug(l_pathname,l_filename);
947: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
948: PNP_DEBUG_PKG.log(' Parameters :');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
954: pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
947: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
948: PNP_DEBUG_PKG.log(' Parameters :');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
954: pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
955: pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
948: PNP_DEBUG_PKG.log(' Parameters :');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
954: pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
955: pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
956: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
949: pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
954: pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
955: pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
956: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
957:
950: pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
954: pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
955: pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
956: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
957:
958: l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id =>p_var_rent_id);
951: pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
954: pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
955: pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
956: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
957:
958: l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id =>p_var_rent_id);
959: pnp_debug_pkg.log(' Proration Rule = '||l_proration_rule);
952: PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953: PNP_DEBUG_PKG.DEBUG('Parameters :');
954: pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
955: pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
956: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
957:
958: l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id =>p_var_rent_id);
959: pnp_debug_pkg.log(' Proration Rule = '||l_proration_rule);
960: pnp_debug_pkg.debug('Proration Rule = '||l_proration_rule);
955: pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
956: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
957:
958: l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id =>p_var_rent_id);
959: pnp_debug_pkg.log(' Proration Rule = '||l_proration_rule);
960: pnp_debug_pkg.debug('Proration Rule = '||l_proration_rule);
961:
962: SELECT termination_date, cumulative_vol
963: INTO l_vr_term_dt, l_cumulative_vol
956: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
957:
958: l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id =>p_var_rent_id);
959: pnp_debug_pkg.log(' Proration Rule = '||l_proration_rule);
960: pnp_debug_pkg.debug('Proration Rule = '||l_proration_rule);
961:
962: SELECT termination_date, cumulative_vol
963: INTO l_vr_term_dt, l_cumulative_vol
964: FROM pn_var_rents_all
962: SELECT termination_date, cumulative_vol
963: INTO l_vr_term_dt, l_cumulative_vol
964: FROM pn_var_rents_all
965: WHERE var_rent_id = p_var_rent_id;
966: pnp_debug_pkg.log(' Variable Rent Termination Date:'||l_vr_term_dt||' Cumulative Volume:'||l_cumulative_vol);
967: pnp_debug_pkg.debug(' Variable Rent Termination Date:'||l_vr_term_dt);
968:
969: IF l_proration_rule in ('FY','LY','FLY') THEN
970: OPEN pn_var_grp_dt(p_var_rent_id);
963: INTO l_vr_term_dt, l_cumulative_vol
964: FROM pn_var_rents_all
965: WHERE var_rent_id = p_var_rent_id;
966: pnp_debug_pkg.log(' Variable Rent Termination Date:'||l_vr_term_dt||' Cumulative Volume:'||l_cumulative_vol);
967: pnp_debug_pkg.debug(' Variable Rent Termination Date:'||l_vr_term_dt);
968:
969: IF l_proration_rule in ('FY','LY','FLY') THEN
970: OPEN pn_var_grp_dt(p_var_rent_id);
971: FETCH pn_var_grp_dt INTO
979: INTO l_ly_365_end_dt, l_ly_365_start_dt
980: FROM pn_var_periods_all
981: WHERE var_rent_id = p_var_rent_id;
982:
983: --pnp_debug_pkg.log(' l_fy_start_date = '|| l_fy_start_date);
984: --pnp_debug_pkg.log(' l_fy_end_date = '|| l_fy_end_date);
985: --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
980: FROM pn_var_periods_all
981: WHERE var_rent_id = p_var_rent_id;
982:
983: --pnp_debug_pkg.log(' l_fy_start_date = '|| l_fy_start_date);
984: --pnp_debug_pkg.log(' l_fy_end_date = '|| l_fy_end_date);
985: --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988: pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
981: WHERE var_rent_id = p_var_rent_id;
982:
983: --pnp_debug_pkg.log(' l_fy_start_date = '|| l_fy_start_date);
984: --pnp_debug_pkg.log(' l_fy_end_date = '|| l_fy_end_date);
985: --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988: pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
989: pnp_debug_pkg.debug('l_ly_start_date = '|| l_ly_start_date);
982:
983: --pnp_debug_pkg.log(' l_fy_start_date = '|| l_fy_start_date);
984: --pnp_debug_pkg.log(' l_fy_end_date = '|| l_fy_end_date);
985: --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988: pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
989: pnp_debug_pkg.debug('l_ly_start_date = '|| l_ly_start_date);
990: pnp_debug_pkg.debug('l_ly_end_date = '|| l_ly_end_date);
983: --pnp_debug_pkg.log(' l_fy_start_date = '|| l_fy_start_date);
984: --pnp_debug_pkg.log(' l_fy_end_date = '|| l_fy_end_date);
985: --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988: pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
989: pnp_debug_pkg.debug('l_ly_start_date = '|| l_ly_start_date);
990: pnp_debug_pkg.debug('l_ly_end_date = '|| l_ly_end_date);
991: /*DBMS_OUTPUT.PUT_LINE(' l_fy_start_date = '|| l_fy_start_date);
984: --pnp_debug_pkg.log(' l_fy_end_date = '|| l_fy_end_date);
985: --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988: pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
989: pnp_debug_pkg.debug('l_ly_start_date = '|| l_ly_start_date);
990: pnp_debug_pkg.debug('l_ly_end_date = '|| l_ly_end_date);
991: /*DBMS_OUTPUT.PUT_LINE(' l_fy_start_date = '|| l_fy_start_date);
992: DBMS_OUTPUT.PUT_LINE(' l_fy_end_date = '|| l_fy_end_date);
985: --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988: pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
989: pnp_debug_pkg.debug('l_ly_start_date = '|| l_ly_start_date);
990: pnp_debug_pkg.debug('l_ly_end_date = '|| l_ly_end_date);
991: /*DBMS_OUTPUT.PUT_LINE(' l_fy_start_date = '|| l_fy_start_date);
992: DBMS_OUTPUT.PUT_LINE(' l_fy_end_date = '|| l_fy_end_date);
993: DBMS_OUTPUT.PUT_LINE(' l_ly_start_date = '|| l_ly_start_date);
986: --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987: pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988: pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
989: pnp_debug_pkg.debug('l_ly_start_date = '|| l_ly_start_date);
990: pnp_debug_pkg.debug('l_ly_end_date = '|| l_ly_end_date);
991: /*DBMS_OUTPUT.PUT_LINE(' l_fy_start_date = '|| l_fy_start_date);
992: DBMS_OUTPUT.PUT_LINE(' l_fy_end_date = '|| l_fy_end_date);
993: DBMS_OUTPUT.PUT_LINE(' l_ly_start_date = '|| l_ly_start_date);
994: DBMS_OUTPUT.PUT_LINE(' l_ly_end_date = '|| l_ly_end_date);*/
995: END IF;
996: /*dbms_output.put_line('Step - 1'); */
997: FOR c_grp IN csr_get_groups (p_var_rent_id,p_period_id) LOOP
998: l_commencement_date := c_grp.commencement_date;
999: --PNP_DEBUG_PKG.log(' Calling csr_get_groups');
1000: pnp_debug_pkg.debug('Cursor csr_get_groups');
1001: l_period_start := c_grp.start_date;
1002: l_period_end := c_grp.end_date;
1003: --pnp_debug_pkg.log(' Period start = '|| l_period_start);
996: /*dbms_output.put_line('Step - 1'); */
997: FOR c_grp IN csr_get_groups (p_var_rent_id,p_period_id) LOOP
998: l_commencement_date := c_grp.commencement_date;
999: --PNP_DEBUG_PKG.log(' Calling csr_get_groups');
1000: pnp_debug_pkg.debug('Cursor csr_get_groups');
1001: l_period_start := c_grp.start_date;
1002: l_period_end := c_grp.end_date;
1003: --pnp_debug_pkg.log(' Period start = '|| l_period_start);
1004: --pnp_debug_pkg.log(' Period end = '|| l_period_end);
999: --PNP_DEBUG_PKG.log(' Calling csr_get_groups');
1000: pnp_debug_pkg.debug('Cursor csr_get_groups');
1001: l_period_start := c_grp.start_date;
1002: l_period_end := c_grp.end_date;
1003: --pnp_debug_pkg.log(' Period start = '|| l_period_start);
1004: --pnp_debug_pkg.log(' Period end = '|| l_period_end);
1005: pnp_debug_pkg.debug('Period start = '|| l_period_start);
1006: pnp_debug_pkg.debug('Period end = '|| l_period_end);
1007:
1000: pnp_debug_pkg.debug('Cursor csr_get_groups');
1001: l_period_start := c_grp.start_date;
1002: l_period_end := c_grp.end_date;
1003: --pnp_debug_pkg.log(' Period start = '|| l_period_start);
1004: --pnp_debug_pkg.log(' Period end = '|| l_period_end);
1005: pnp_debug_pkg.debug('Period start = '|| l_period_start);
1006: pnp_debug_pkg.debug('Period end = '|| l_period_end);
1007:
1008: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_get_bkpts ');
1001: l_period_start := c_grp.start_date;
1002: l_period_end := c_grp.end_date;
1003: --pnp_debug_pkg.log(' Period start = '|| l_period_start);
1004: --pnp_debug_pkg.log(' Period end = '|| l_period_end);
1005: pnp_debug_pkg.debug('Period start = '|| l_period_start);
1006: pnp_debug_pkg.debug('Period end = '|| l_period_end);
1007:
1008: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_get_bkpts ');
1009: /* dbms_output.put_line('Step - 2'); */
1002: l_period_end := c_grp.end_date;
1003: --pnp_debug_pkg.log(' Period start = '|| l_period_start);
1004: --pnp_debug_pkg.log(' Period end = '|| l_period_end);
1005: pnp_debug_pkg.debug('Period start = '|| l_period_start);
1006: pnp_debug_pkg.debug('Period end = '|| l_period_end);
1007:
1008: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_get_bkpts ');
1009: /* dbms_output.put_line('Step - 2'); */
1010: FOR c_bkpt IN csr_get_bkpts (c_grp.period_id) LOOP
1004: --pnp_debug_pkg.log(' Period end = '|| l_period_end);
1005: pnp_debug_pkg.debug('Period start = '|| l_period_start);
1006: pnp_debug_pkg.debug('Period end = '|| l_period_end);
1007:
1008: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_get_bkpts ');
1009: /* dbms_output.put_line('Step - 2'); */
1010: FOR c_bkpt IN csr_get_bkpts (c_grp.period_id) LOOP
1011: l_found := 0;
1012:
1009: /* dbms_output.put_line('Step - 2'); */
1010: FOR c_bkpt IN csr_get_bkpts (c_grp.period_id) LOOP
1011: l_found := 0;
1012:
1013: --pnp_debug_pkg.log(' GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1014: --pnp_debug_pkg.log(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1015: --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1010: FOR c_bkpt IN csr_get_bkpts (c_grp.period_id) LOOP
1011: l_found := 0;
1012:
1013: --pnp_debug_pkg.log(' GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1014: --pnp_debug_pkg.log(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1015: --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1011: l_found := 0;
1012:
1013: --pnp_debug_pkg.log(' GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1014: --pnp_debug_pkg.log(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1015: --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019: pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1012:
1013: --pnp_debug_pkg.log(' GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1014: --pnp_debug_pkg.log(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1015: --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019: pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1020: pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1013: --pnp_debug_pkg.log(' GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1014: --pnp_debug_pkg.log(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1015: --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019: pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1020: pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1021: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_trans_exists ');
1014: --pnp_debug_pkg.log(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1015: --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019: pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1020: pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1021: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_trans_exists ');
1022: l_resets_flag := 'N';
1015: --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019: pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1020: pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1021: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_trans_exists ');
1022: l_resets_flag := 'N';
1023:
1016: --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019: pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1020: pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1021: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_trans_exists ');
1022: l_resets_flag := 'N';
1023:
1024: pnp_debug_pkg.debug('l_found = ' || l_found);
1017: pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018: pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019: pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1020: pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1021: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_trans_exists ');
1022: l_resets_flag := 'N';
1023:
1024: pnp_debug_pkg.debug('l_found = ' || l_found);
1025:
1020: pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1021: PNP_DEBUG_PKG.DEBUG('Opening cursor csr_trans_exists ');
1022: l_resets_flag := 'N';
1023:
1024: pnp_debug_pkg.debug('l_found = ' || l_found);
1025:
1026: /* dbms_output.put_line('Step - 3'); */
1027: IF c_grp.grp_start_date between c_bkpt.bkpt_start_date and c_bkpt.bkpt_end_date
1028: OR c_grp.grp_end_date between c_bkpt.bkpt_start_date and c_bkpt.bkpt_end_date
1028: OR c_grp.grp_end_date between c_bkpt.bkpt_start_date and c_bkpt.bkpt_end_date
1029: OR c_bkpt.bkpt_start_date between c_grp.grp_start_date and c_grp.grp_end_date
1030: OR c_bkpt.bkpt_end_date between c_grp.grp_start_date and c_grp.grp_end_date THEN
1031:
1032: --pnp_debug_pkg.log(' Group Start Date OR End Date Between BKPT Start date or End Date');
1033: pnp_debug_pkg.debug('Group Start Date OR End Date Between BKPT Start date or End Date');
1034:
1035: IF c_grp.grp_start_date >= c_bkpt.bkpt_start_date THEN
1036: l_start_date := c_grp.grp_start_date;
1029: OR c_bkpt.bkpt_start_date between c_grp.grp_start_date and c_grp.grp_end_date
1030: OR c_bkpt.bkpt_end_date between c_grp.grp_start_date and c_grp.grp_end_date THEN
1031:
1032: --pnp_debug_pkg.log(' Group Start Date OR End Date Between BKPT Start date or End Date');
1033: pnp_debug_pkg.debug('Group Start Date OR End Date Between BKPT Start date or End Date');
1034:
1035: IF c_grp.grp_start_date >= c_bkpt.bkpt_start_date THEN
1036: l_start_date := c_grp.grp_start_date;
1037: ELSE
1075: END IF;
1076: /* DBMS_OUTPUT.PUT_LINE(' l_prorate_start = '||l_prorate_start);
1077: DBMS_OUTPUT.PUT_LINE(' l_prorate_end = '||l_prorate_end); */
1078:
1079: pnp_debug_pkg.debug('Prorated GRP Vol Start '||to_char(l_prorate_start));
1080: pnp_debug_pkg.debug('Prorated GRP Vol End '||to_char(l_prorate_end));
1081: PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1076: /* DBMS_OUTPUT.PUT_LINE(' l_prorate_start = '||l_prorate_start);
1077: DBMS_OUTPUT.PUT_LINE(' l_prorate_end = '||l_prorate_end); */
1078:
1079: pnp_debug_pkg.debug('Prorated GRP Vol Start '||to_char(l_prorate_start));
1080: pnp_debug_pkg.debug('Prorated GRP Vol End '||to_char(l_prorate_end));
1081: PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1077: DBMS_OUTPUT.PUT_LINE(' l_prorate_end = '||l_prorate_end); */
1078:
1079: pnp_debug_pkg.debug('Prorated GRP Vol Start '||to_char(l_prorate_start));
1080: pnp_debug_pkg.debug('Prorated GRP Vol End '||to_char(l_prorate_end));
1081: PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1078:
1079: pnp_debug_pkg.debug('Prorated GRP Vol Start '||to_char(l_prorate_start));
1080: pnp_debug_pkg.debug('Prorated GRP Vol End '||to_char(l_prorate_end));
1081: PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1079: pnp_debug_pkg.debug('Prorated GRP Vol Start '||to_char(l_prorate_start));
1080: pnp_debug_pkg.debug('Prorated GRP Vol End '||to_char(l_prorate_end));
1081: PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1080: pnp_debug_pkg.debug('Prorated GRP Vol End '||to_char(l_prorate_end));
1081: PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1081: PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1082: PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090:
1083: PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090:
1091: PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
1084: PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090:
1091: PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
1092: pnp_debug_pkg.debug('l_startdate = '|| l_start_date);
1085: PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086: PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090:
1091: PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
1092: pnp_debug_pkg.debug('l_startdate = '|| l_start_date);
1093: pnp_debug_pkg.debug('l_enddae = '|| l_end_date);
1087: PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090:
1091: PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
1092: pnp_debug_pkg.debug('l_startdate = '|| l_start_date);
1093: pnp_debug_pkg.debug('l_enddae = '|| l_end_date);
1094: l_fy_factor := 1;
1095: l_ly_factor := 1;
1088: PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090:
1091: PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
1092: pnp_debug_pkg.debug('l_startdate = '|| l_start_date);
1093: pnp_debug_pkg.debug('l_enddae = '|| l_end_date);
1094: l_fy_factor := 1;
1095: l_ly_factor := 1;
1096: l_factor := l_proration_factor;
1089: PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090:
1091: PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
1092: pnp_debug_pkg.debug('l_startdate = '|| l_start_date);
1093: pnp_debug_pkg.debug('l_enddae = '|| l_end_date);
1094: l_fy_factor := 1;
1095: l_ly_factor := 1;
1096: l_factor := l_proration_factor;
1097: l_pr_prorate_start := l_prorate_start ;
1095: l_ly_factor := 1;
1096: l_factor := l_proration_factor;
1097: l_pr_prorate_start := l_prorate_start ;
1098: l_pr_prorate_end := l_prorate_end ;
1099: pnp_debug_pkg.debug('l_proration_rule='|| l_proration_rule);
1100: IF l_proration_rule in ('FY','LY','FLY') THEN
1101:
1102: IF l_fy_end_date BETWEEN l_start_date and l_end_date THEN
1103: IF l_proration_rule = 'LY' AND l_fy_end_date > l_ly_start_date THEN
1105: ELSE
1106: l_fy_factor := ((l_fy_end_date - l_start_date)+1)/((l_end_Date-l_start_date)+1);
1107: l_pr_prorate_start := l_pr_prorate_start * l_fy_factor;
1108: l_pr_prorate_end := l_pr_prorate_end * l_fy_factor;
1109: pnp_debug_pkg.debug('l_fy_factor='|| l_fy_factor);
1110: l_factor := l_fy_factor;
1111: /*DBMS_OUTPUT.PUT_LINE('********* l_fy_factor ********* : '||l_fy_factor); */
1112: END IF;
1113: END IF;
1118: ELSE
1119: l_ly_factor := ((l_end_date - l_ly_start_date)+1)/((l_end_date-l_start_date)+1);
1120: l_pr_prorate_start := l_pr_prorate_start * l_ly_factor;
1121: l_pr_prorate_end := l_pr_prorate_end * l_ly_factor;
1122: pnp_debug_pkg.debug('l_ly_factor='|| l_ly_factor);
1123: l_factor := l_ly_factor;
1124: /* DBMS_OUTPUT.PUT_LINE('********* l_ly_factor ********* : '||l_ly_factor); */
1125: END IF;
1126: END IF;
1137: ,l_end_date);
1138: FETCH l_row_exists_cur into l_row_exists;
1139: CLOSE l_row_exists_cur;*/
1140:
1141: --PNP_DEBUG_PKG.log(' l_row_exists = '||l_row_exists);
1142: PNP_DEBUG_PKG.DEBUG('l_row_exists = '||l_row_exists);
1143: pnp_debug_pkg.debug('l_pr_prorate_start = ' || l_pr_prorate_start);
1144: pnp_debug_pkg.debug('l_pr_prorate_end = ' || l_pr_prorate_end);
1145: /* DBMS_OUTPUT.PUT_LINE('p_var_rent_id = '||p_var_rent_id);
1138: FETCH l_row_exists_cur into l_row_exists;
1139: CLOSE l_row_exists_cur;*/
1140:
1141: --PNP_DEBUG_PKG.log(' l_row_exists = '||l_row_exists);
1142: PNP_DEBUG_PKG.DEBUG('l_row_exists = '||l_row_exists);
1143: pnp_debug_pkg.debug('l_pr_prorate_start = ' || l_pr_prorate_start);
1144: pnp_debug_pkg.debug('l_pr_prorate_end = ' || l_pr_prorate_end);
1145: /* DBMS_OUTPUT.PUT_LINE('p_var_rent_id = '||p_var_rent_id);
1146: DBMS_OUTPUT.PUT_LINE('c_grp.grp_date_id = '||c_grp.grp_date_id);
1139: CLOSE l_row_exists_cur;*/
1140:
1141: --PNP_DEBUG_PKG.log(' l_row_exists = '||l_row_exists);
1142: PNP_DEBUG_PKG.DEBUG('l_row_exists = '||l_row_exists);
1143: pnp_debug_pkg.debug('l_pr_prorate_start = ' || l_pr_prorate_start);
1144: pnp_debug_pkg.debug('l_pr_prorate_end = ' || l_pr_prorate_end);
1145: /* DBMS_OUTPUT.PUT_LINE('p_var_rent_id = '||p_var_rent_id);
1146: DBMS_OUTPUT.PUT_LINE('c_grp.grp_date_id = '||c_grp.grp_date_id);
1147: DBMS_OUTPUT.PUT_LINE('c_bkpt.line_item_id = '||c_bkpt.line_item_id);
1140:
1141: --PNP_DEBUG_PKG.log(' l_row_exists = '||l_row_exists);
1142: PNP_DEBUG_PKG.DEBUG('l_row_exists = '||l_row_exists);
1143: pnp_debug_pkg.debug('l_pr_prorate_start = ' || l_pr_prorate_start);
1144: pnp_debug_pkg.debug('l_pr_prorate_end = ' || l_pr_prorate_end);
1145: /* DBMS_OUTPUT.PUT_LINE('p_var_rent_id = '||p_var_rent_id);
1146: DBMS_OUTPUT.PUT_LINE('c_grp.grp_date_id = '||c_grp.grp_date_id);
1147: DBMS_OUTPUT.PUT_LINE('c_bkpt.line_item_id = '||c_bkpt.line_item_id);
1148: DBMS_OUTPUT.PUT_LINE('c_bkpt.bkpt_detail_id = '||c_bkpt.bkpt_detail_id);
1152: DBMS_OUTPUT.PUT_LINE('l_proration_factor = '||l_proration_factor);
1153: DBMS_OUTPUT.PUT_LINE('l_proration_rule = '||l_proration_rule);*/
1154: IF l_row_exists IS NULL THEN
1155: /* dbms_output.put_line('Step - 5 - Insert'); */
1156: PNP_DEBUG_PKG.DEBUG('inserting into pn_var_Transactions');
1157: pnp_debug_pkg.debug('l_factor = ' || l_factor);
1158: /*INSERT INTO pn_var_transactions_all (
1159: transaction_id
1160: ,grp_date_id
1153: DBMS_OUTPUT.PUT_LINE('l_proration_rule = '||l_proration_rule);*/
1154: IF l_row_exists IS NULL THEN
1155: /* dbms_output.put_line('Step - 5 - Insert'); */
1156: PNP_DEBUG_PKG.DEBUG('inserting into pn_var_Transactions');
1157: pnp_debug_pkg.debug('l_factor = ' || l_factor);
1158: /*INSERT INTO pn_var_transactions_all (
1159: transaction_id
1160: ,grp_date_id
1161: ,bkpt_detail_id
1213: ,NVL(fnd_profile.value('USER_ID'),0)
1214: ,NVL(fnd_profile.value('USER_ID'),0)
1215: ,to_number(decode(substr(userenv('CLIENT_INFO'),1,1),' ',null,substr(userenv('CLIENT_INFO'),1,10)))
1216: );*/
1217: PNP_DEBUG_PKG.DEBUG('Rows Inserted='||to_char(sql%rowcount));
1218:
1219: ELSE
1220: /*dbms_output.put_line('Step - 5 - update'); */
1221: --PNP_DEBUG_PKG.log(' update pn_var_Transactions');
1217: PNP_DEBUG_PKG.DEBUG('Rows Inserted='||to_char(sql%rowcount));
1218:
1219: ELSE
1220: /*dbms_output.put_line('Step - 5 - update'); */
1221: --PNP_DEBUG_PKG.log(' update pn_var_Transactions');
1222: --PNP_DEBUG_PKG.log(' l_factor ='|| l_factor);
1223: PNP_DEBUG_PKG.DEBUG('update pn_var_Transactions');
1224: PNP_DEBUG_PKG.DEBUG('l_factor ='|| l_factor);
1225: /*UPDATE pn_var_transactions_all
1218:
1219: ELSE
1220: /*dbms_output.put_line('Step - 5 - update'); */
1221: --PNP_DEBUG_PKG.log(' update pn_var_Transactions');
1222: --PNP_DEBUG_PKG.log(' l_factor ='|| l_factor);
1223: PNP_DEBUG_PKG.DEBUG('update pn_var_Transactions');
1224: PNP_DEBUG_PKG.DEBUG('l_factor ='|| l_factor);
1225: /*UPDATE pn_var_transactions_all
1226: SET no_of_group_days =c_grp.no_of_group_days
1219: ELSE
1220: /*dbms_output.put_line('Step - 5 - update'); */
1221: --PNP_DEBUG_PKG.log(' update pn_var_Transactions');
1222: --PNP_DEBUG_PKG.log(' l_factor ='|| l_factor);
1223: PNP_DEBUG_PKG.DEBUG('update pn_var_Transactions');
1224: PNP_DEBUG_PKG.DEBUG('l_factor ='|| l_factor);
1225: /*UPDATE pn_var_transactions_all
1226: SET no_of_group_days =c_grp.no_of_group_days
1227: ,no_of_bkpt_days =l_calc_days
1220: /*dbms_output.put_line('Step - 5 - update'); */
1221: --PNP_DEBUG_PKG.log(' update pn_var_Transactions');
1222: --PNP_DEBUG_PKG.log(' l_factor ='|| l_factor);
1223: PNP_DEBUG_PKG.DEBUG('update pn_var_Transactions');
1224: PNP_DEBUG_PKG.DEBUG('l_factor ='|| l_factor);
1225: /*UPDATE pn_var_transactions_all
1226: SET no_of_group_days =c_grp.no_of_group_days
1227: ,no_of_bkpt_days =l_calc_days
1228: ,prorated_grp_vol_start =l_prorate_start
1251:
1252: -------------------------
1253: -- Get Proration Rule
1254: -------------------------
1255: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
1256: l_proration_rule := pn_var_rent_pkg.get_proration_rule(
1257: p_var_rent_id =>p_var_rent_id);
1258: pnp_debug_pkg.debug('Proration Rule = '||l_proration_rule);
1259:
1254: -------------------------
1255: pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
1256: l_proration_rule := pn_var_rent_pkg.get_proration_rule(
1257: p_var_rent_id =>p_var_rent_id);
1258: pnp_debug_pkg.debug('Proration Rule = '||l_proration_rule);
1259:
1260: ------------------------
1261: -- Initialize - reset_grp_id_cnt and proration_reset_grp_id_cnt
1262: ------------------------
1260: ------------------------
1261: -- Initialize - reset_grp_id_cnt and proration_reset_grp_id_cnt
1262: ------------------------
1263: -- 11-AUG-03 Chris.T. - Proration Rule Specific - End --
1264: pnp_debug_pkg.debug('Determine_resets');
1265: l_reset_group_id_cnt := 0; -- 24-JUL-03 Chris T --
1266: l_pro_reset_group_id_cnt := NULL; -- 11-AUG-03 Chris.T --
1267:
1268: ---------------------------------------
1302: END IF;
1303:
1304: /*FOR c_flag IN csr_find_resets (p_var_rent_id) LOOP
1305:
1306: --pnp_debug_pkg.log(' Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1307: --pnp_debug_pkg.log(' Period ID ='||to_char(c_flag.period_id));
1308: --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1303:
1304: /*FOR c_flag IN csr_find_resets (p_var_rent_id) LOOP
1305:
1306: --pnp_debug_pkg.log(' Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1307: --pnp_debug_pkg.log(' Period ID ='||to_char(c_flag.period_id));
1308: --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1304: /*FOR c_flag IN csr_find_resets (p_var_rent_id) LOOP
1305:
1306: --pnp_debug_pkg.log(' Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1307: --pnp_debug_pkg.log(' Period ID ='||to_char(c_flag.period_id));
1308: --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1305:
1306: --pnp_debug_pkg.log(' Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1307: --pnp_debug_pkg.log(' Period ID ='||to_char(c_flag.period_id));
1308: --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1306: --pnp_debug_pkg.log(' Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1307: --pnp_debug_pkg.log(' Period ID ='||to_char(c_flag.period_id));
1308: --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1307: --pnp_debug_pkg.log(' Period ID ='||to_char(c_flag.period_id));
1308: --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315: pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1308: --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315: pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1316: pnp_debug_pkg.debug('Sales Type Code ='||c_flag.sales_type_code);
1309: --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315: pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1316: pnp_debug_pkg.debug('Sales Type Code ='||c_flag.sales_type_code);
1317: pnp_debug_pkg.debug('Group Date ='||c_flag.group_date);
1310: --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315: pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1316: pnp_debug_pkg.debug('Sales Type Code ='||c_flag.sales_type_code);
1317: pnp_debug_pkg.debug('Group Date ='||c_flag.group_date);
1318: determine_reset_flag (p_var_rent_id => p_var_rent_id,
1311: --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315: pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1316: pnp_debug_pkg.debug('Sales Type Code ='||c_flag.sales_type_code);
1317: pnp_debug_pkg.debug('Group Date ='||c_flag.group_date);
1318: determine_reset_flag (p_var_rent_id => p_var_rent_id,
1319: p_period_id => c_flag.period_id,
1312: pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315: pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1316: pnp_debug_pkg.debug('Sales Type Code ='||c_flag.sales_type_code);
1317: pnp_debug_pkg.debug('Group Date ='||c_flag.group_date);
1318: determine_reset_flag (p_var_rent_id => p_var_rent_id,
1319: p_period_id => c_flag.period_id,
1320: p_item_category_code => c_flag.item_category_code,
1313: pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314: pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315: pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1316: pnp_debug_pkg.debug('Sales Type Code ='||c_flag.sales_type_code);
1317: pnp_debug_pkg.debug('Group Date ='||c_flag.group_date);
1318: determine_reset_flag (p_var_rent_id => p_var_rent_id,
1319: p_period_id => c_flag.period_id,
1320: p_item_category_code => c_flag.item_category_code,
1321: p_sales_type_code => c_flag.sales_type_code,
1320: p_item_category_code => c_flag.item_category_code,
1321: p_sales_type_code => c_flag.sales_type_code,
1322: p_start_date => c_flag.bkpt_start_date,
1323: x_reset_flag => l_reset_flag);
1324: pnp_debug_pkg.debug('Update pn_var_Transactions with reset flag= '||l_reset_flag);
1325: -- 24-JUL-03 Chris T - Start --
1326: IF l_reset_flag = 'Y' THEN
1327: l_reset_group_id_cnt := l_reset_group_id_cnt + 1;
1328: --pnp_debug_pkg.log(' Reset Group ID Count = '||to_char(l_reset_group_id_cnt));
1324: pnp_debug_pkg.debug('Update pn_var_Transactions with reset flag= '||l_reset_flag);
1325: -- 24-JUL-03 Chris T - Start --
1326: IF l_reset_flag = 'Y' THEN
1327: l_reset_group_id_cnt := l_reset_group_id_cnt + 1;
1328: --pnp_debug_pkg.log(' Reset Group ID Count = '||to_char(l_reset_group_id_cnt));
1329: pnp_debug_pkg.debug('Reset Group ID Count = '||to_char(l_reset_group_id_cnt));
1330: END IF;
1331:
1332: /*UPDATE pn_var_transactions_all
1325: -- 24-JUL-03 Chris T - Start --
1326: IF l_reset_flag = 'Y' THEN
1327: l_reset_group_id_cnt := l_reset_group_id_cnt + 1;
1328: --pnp_debug_pkg.log(' Reset Group ID Count = '||to_char(l_reset_group_id_cnt));
1329: pnp_debug_pkg.debug('Reset Group ID Count = '||to_char(l_reset_group_id_cnt));
1330: END IF;
1331:
1332: /*UPDATE pn_var_transactions_all
1333: SET reset_group_id = l_reset_group_id_cnt --24-JUL-03 Chris T--
1362: l_pro_reset_group_id_cnt := nvl(l_pro_reset_group_id_cnt ,0)+ 1;
1363: g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1364: --l_pro_reset_group_id_cnt := nvl(g_pro_reset_group_id_cnt ,0)+ 1;
1365: --g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1366: --pnp_debug_pkg.log(' 365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1367: pnp_debug_pkg.debug('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1368: /* dbms_output.put_line('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1369: pnp_debug_pkg.debug('365 New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1370: END IF;
1363: g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1364: --l_pro_reset_group_id_cnt := nvl(g_pro_reset_group_id_cnt ,0)+ 1;
1365: --g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1366: --pnp_debug_pkg.log(' 365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1367: pnp_debug_pkg.debug('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1368: /* dbms_output.put_line('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1369: pnp_debug_pkg.debug('365 New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1370: END IF;
1371: --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1365: --g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1366: --pnp_debug_pkg.log(' 365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1367: pnp_debug_pkg.debug('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1368: /* dbms_output.put_line('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1369: pnp_debug_pkg.debug('365 New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1370: END IF;
1371: --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1372: --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1367: pnp_debug_pkg.debug('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1368: /* dbms_output.put_line('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1369: pnp_debug_pkg.debug('365 New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1370: END IF;
1371: --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1372: --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1368: /* dbms_output.put_line('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1369: pnp_debug_pkg.debug('365 New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1370: END IF;
1371: --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1372: --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1369: pnp_debug_pkg.debug('365 New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1370: END IF;
1371: --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1372: --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1370: END IF;
1371: --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1372: --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378: pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1371: --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1372: --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378: pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1379: pnp_debug_pkg.debug('l_fy_365_end_date = '||l_fy_365_end_date);
1372: --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378: pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1379: pnp_debug_pkg.debug('l_fy_365_end_date = '||l_fy_365_end_date);
1380: pnp_debug_pkg.debug('l_ly_365_start_date = '||l_ly_365_start_date);
1373: --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378: pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1379: pnp_debug_pkg.debug('l_fy_365_end_date = '||l_fy_365_end_date);
1380: pnp_debug_pkg.debug('l_ly_365_start_date = '||l_ly_365_start_date);
1381: /* dbms_output.put_line('l_reset_flag='||l_reset_flag);
1374: --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378: pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1379: pnp_debug_pkg.debug('l_fy_365_end_date = '||l_fy_365_end_date);
1380: pnp_debug_pkg.debug('l_ly_365_start_date = '||l_ly_365_start_date);
1381: /* dbms_output.put_line('l_reset_flag='||l_reset_flag);
1382: dbms_output.put_line('l_proration_rule = '||l_proration_rule);
1375: --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378: pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1379: pnp_debug_pkg.debug('l_fy_365_end_date = '||l_fy_365_end_date);
1380: pnp_debug_pkg.debug('l_ly_365_start_date = '||l_ly_365_start_date);
1381: /* dbms_output.put_line('l_reset_flag='||l_reset_flag);
1382: dbms_output.put_line('l_proration_rule = '||l_proration_rule);
1383: dbms_output.put_line('Group Date = '||c_flag.group_date);
1376: pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377: pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378: pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1379: pnp_debug_pkg.debug('l_fy_365_end_date = '||l_fy_365_end_date);
1380: pnp_debug_pkg.debug('l_ly_365_start_date = '||l_ly_365_start_date);
1381: /* dbms_output.put_line('l_reset_flag='||l_reset_flag);
1382: dbms_output.put_line('l_proration_rule = '||l_proration_rule);
1383: dbms_output.put_line('Group Date = '||c_flag.group_date);
1384: dbms_output.put_line('l_fy_365_end_date = '||l_fy_365_end_date);
1386: IF l_reset_flag = 'Y' THEN
1387: IF l_proration_rule = 'FY' AND
1388: c_flag.group_date > l_fy_365_end_date THEN
1389: l_pro_reset_group_id := NULL;
1390: pnp_debug_pkg.debug('FY');
1391: ELSIF l_proration_rule = 'LY' AND
1392: c_flag.group_date < l_ly_365_start_date THEN
1393: l_pro_reset_group_id := NULL;
1394: /* dbms_output.put_line('aaa');
1391: ELSIF l_proration_rule = 'LY' AND
1392: c_flag.group_date < l_ly_365_start_date THEN
1393: l_pro_reset_group_id := NULL;
1394: /* dbms_output.put_line('aaa');
1395: pnp_debug_pkg.debug('LY');
1396: ELSIF l_proration_rule = 'FLY' and
1397: (c_flag.group_Date > l_fy_365_end_date and
1398: c_flag.group_Date < l_ly_365_start_date) THEN
1399: l_pro_reset_group_id := NULL;
1396: ELSIF l_proration_rule = 'FLY' and
1397: (c_flag.group_Date > l_fy_365_end_date and
1398: c_flag.group_Date < l_ly_365_start_date) THEN
1399: l_pro_reset_group_id := NULL;
1400: pnp_debug_pkg.debug('FLY');
1401: /* dbms_output.put_line('Reset to NULL FLY');
1402: ELSE
1403: l_pro_reset_group_id_cnt := nvl(l_pro_reset_group_id_cnt ,0)+ 1;
1404: g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1401: /* dbms_output.put_line('Reset to NULL FLY');
1402: ELSE
1403: l_pro_reset_group_id_cnt := nvl(l_pro_reset_group_id_cnt ,0)+ 1;
1404: g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1405: pnp_debug_pkg.debug('New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1406: pnp_debug_pkg.debug('New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1407: END IF;
1408: ELSE
1409: IF l_proration_rule = 'FY' AND
1402: ELSE
1403: l_pro_reset_group_id_cnt := nvl(l_pro_reset_group_id_cnt ,0)+ 1;
1404: g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1405: pnp_debug_pkg.debug('New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1406: pnp_debug_pkg.debug('New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1407: END IF;
1408: ELSE
1409: IF l_proration_rule = 'FY' AND
1410: c_flag.group_date > l_fy_365_end_date THEN
1413: c_flag.group_date < l_ly_365_start_date THEN
1414: l_pro_reset_group_id := NULL;
1415: /* dbms_output.put_line('aaa');
1416: ELSIF l_proration_rule = 'FLY' THEN -- and
1417: pnp_debug_pkg.debug('FLY');
1418: --pnp_debug_pkg.log(' FLY');
1419: IF c_flag.group_Date > l_fy_365_end_date and --Chris.T. 17MAR2004
1420: c_flag.group_Date < l_ly_365_start_date THEN
1421: l_pro_reset_group_id := NULL;
1414: l_pro_reset_group_id := NULL;
1415: /* dbms_output.put_line('aaa');
1416: ELSIF l_proration_rule = 'FLY' THEN -- and
1417: pnp_debug_pkg.debug('FLY');
1418: --pnp_debug_pkg.log(' FLY');
1419: IF c_flag.group_Date > l_fy_365_end_date and --Chris.T. 17MAR2004
1420: c_flag.group_Date < l_ly_365_start_date THEN
1421: l_pro_reset_group_id := NULL;
1422: /* dbms_output.put_line('Reset to NULL FLY');
1435: AND bkpt_start_date = c_flag.bkpt_start_date;
1436:
1437: END IF; --IF l_proration_rule in ( 'CYNP','CYP','FY','LY','FLY') THEN
1438:
1439: pnp_debug_pkg.debug('------------------------------- ');
1440: END LOOP;*/
1441: NULL;
1442:
1443: --Start Srini 08SEP2004
1448: INTO l_invg_freq
1449: FROM pn_var_rent_dates_all
1450: WHERE var_rent_id = p_var_rent_id;
1451: /* DBMS_OUTPUT.PUT_LINE(' l_invg_freq:'||l_invg_freq); */
1452: pnp_debug_pkg.log(' l_invg_freq:'||l_invg_freq);
1453: EXCEPTION
1454: WHEN OTHERS THEN
1455: l_invg_freq := 'MON';
1456: END;
1534: AND NVL(proration_reset_group_id, 0) = NVL(i4.proration_reset_group_id, 0)
1535: --AND NVL(reset_group_id, 0) = NVL(i4.reset_group_id, 0)
1536: AND NVL(pr_grp_blended_vol_end, 0) <> 0;
1537: --AND prorated_group_sales IS NOT NULL;
1538: pnp_debug_pkg.log(' l_bkpt_days1 = '||l_bkpt_days1);
1539: /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days1:'||l_bkpt_days1);
1540:
1541: --Determine no of days for which bkpt is missing
1542: l_bkpt_days2 := (l_ly_365_end_dt - ADD_MONTHS(l_ly_365_end_dt, -12)) - l_bkpt_days1;
1539: /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days1:'||l_bkpt_days1);
1540:
1541: --Determine no of days for which bkpt is missing
1542: l_bkpt_days2 := (l_ly_365_end_dt - ADD_MONTHS(l_ly_365_end_dt, -12)) - l_bkpt_days1;
1543: pnp_debug_pkg.log(' l_bkpt_days2 = '||l_bkpt_days2);
1544: /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days2:'||l_bkpt_days2);
1545:
1546: EXCEPTION
1547: WHEN OTHERS THEN
1578: l_prorate_start := l_pr_start;
1579: l_prorate_end := l_pr_end;
1580: END IF;
1581:
1582: pnp_debug_pkg.log(' l_prorate_start = '||l_prorate_start);
1583: pnp_debug_pkg.log(' l_prorate_end = '||l_prorate_end);
1584: /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start = '||l_prorate_start);
1585: DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_end = '||l_prorate_end);
1586:
1579: l_prorate_end := l_pr_end;
1580: END IF;
1581:
1582: pnp_debug_pkg.log(' l_prorate_start = '||l_prorate_start);
1583: pnp_debug_pkg.log(' l_prorate_end = '||l_prorate_end);
1584: /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start = '||l_prorate_start);
1585: DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_end = '||l_prorate_end);
1586:
1587: UPDATE pn_var_transactions_all
1683: AND NVL(proration_reset_group_id, 0) = NVL(i4.proration_reset_group_id, 0)
1684: --AND NVL(reset_group_id, 0) = NVL(i4.reset_group_id, 0)
1685: AND NVL(pr_grp_blended_vol_end, 0) = 0;
1686: --AND prorated_group_sales IS NOT NULL;
1687: pnp_debug_pkg.log(' l_bkpt_days1 = '||l_bkpt_days1);
1688: /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days1:'||l_bkpt_days1);
1689:
1690: --Determine no of days for which bkpt is missing
1691: l_bkpt_days2 := (l_ly_365_end_dt - ADD_MONTHS(l_ly_365_end_dt, -12)) - l_bkpt_days1;
1688: /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days1:'||l_bkpt_days1);
1689:
1690: --Determine no of days for which bkpt is missing
1691: l_bkpt_days2 := (l_ly_365_end_dt - ADD_MONTHS(l_ly_365_end_dt, -12)) - l_bkpt_days1;
1692: pnp_debug_pkg.log(' l_bkpt_days2 = '||l_bkpt_days2);
1693: /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days2:'||l_bkpt_days2);
1694:
1695: EXCEPTION
1696: WHEN OTHERS THEN
1727: l_prorate_start := l_pr_start;
1728: l_prorate_end := l_pr_end;
1729: END IF;
1730:
1731: pnp_debug_pkg.log(' l_prorate_start = '||l_prorate_start);
1732: pnp_debug_pkg.log(' l_prorate_end = '||l_prorate_end);
1733: /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start = '||l_prorate_start);
1734: DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_end = '||l_prorate_end);
1735:
1728: l_prorate_end := l_pr_end;
1729: END IF;
1730:
1731: pnp_debug_pkg.log(' l_prorate_start = '||l_prorate_start);
1732: pnp_debug_pkg.log(' l_prorate_end = '||l_prorate_end);
1733: /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start = '||l_prorate_start);
1734: DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_end = '||l_prorate_end);
1735:
1736: UPDATE pn_var_transactions_all
1761: -- by period_id, line_item_id, reset_group_id and bkpt_rate
1762: -- for the current var_rent_id
1763: -- 24-JUL-03 Chris.T.
1764: ---------------------------------------------------------
1765: pnp_debug_pkg.log(' Call to Update Blended Period Volume - Start and END');
1766: pnp_debug_pkg.debug('Call to Update Blended Period Volume - Start and END');
1767:
1768:
1769: IF l_proration_rule IN ('CYNP','CYP') THEN
1762: -- for the current var_rent_id
1763: -- 24-JUL-03 Chris.T.
1764: ---------------------------------------------------------
1765: pnp_debug_pkg.log(' Call to Update Blended Period Volume - Start and END');
1766: pnp_debug_pkg.debug('Call to Update Blended Period Volume - Start and END');
1767:
1768:
1769: IF l_proration_rule IN ('CYNP','CYP') THEN
1770: update_blended_period(p_var_rent_id => p_var_rent_id,
1775: update_blended_period(p_var_rent_id => p_var_rent_id);
1776: --END IF; --Chris.T. 10FEB2004
1777: END IF;
1778:
1779: pnp_debug_pkg.log(' Call update_ytd_bkpts');
1780: pnp_debug_pkg.debug('Call update_ytd_bkpts');
1781: update_ytd_bkpts ( p_var_rent_id => p_var_rent_id,
1782: p_period_id => p_period_id);
1783:
1776: --END IF; --Chris.T. 10FEB2004
1777: END IF;
1778:
1779: pnp_debug_pkg.log(' Call update_ytd_bkpts');
1780: pnp_debug_pkg.debug('Call update_ytd_bkpts');
1781: update_ytd_bkpts ( p_var_rent_id => p_var_rent_id,
1782: p_period_id => p_period_id);
1783:
1784: COMMIT;
1782: p_period_id => p_period_id);
1783:
1784: COMMIT;
1785:
1786: pnp_debug_pkg.debug('End of Populate_Transactions');
1787: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (-)');
1788: --pnp_debug_pkg.disable_file_debug;
1789: EXCEPTION
1790: When OTHERS THEN
1783:
1784: COMMIT;
1785:
1786: pnp_debug_pkg.debug('End of Populate_Transactions');
1787: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (-)');
1788: --pnp_debug_pkg.disable_file_debug;
1789: EXCEPTION
1790: When OTHERS THEN
1791: /* dbms_output.put_line(' Error While Running Populate Transactions:' || SQLERRM); */
1784: COMMIT;
1785:
1786: pnp_debug_pkg.debug('End of Populate_Transactions');
1787: PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (-)');
1788: --pnp_debug_pkg.disable_file_debug;
1789: EXCEPTION
1790: When OTHERS THEN
1791: /* dbms_output.put_line(' Error While Running Populate Transactions:' || SQLERRM); */
1792: null;
1954: l_fy_365_end_Date DATE;
1955: BEGIN
1956:
1957: /*13-AUG-03 Chris.T. -Start*/
1958: pnp_debug_pkg.debug('UPDATE_YTD_BKPTS (+)');
1959: pnp_debug_pkg.debug('Parameter : p_var_rent_id = '|| p_var_rent_id);
1960: pnp_debug_pkg.debug('Parameter : p_period_id = '|| p_period_id);
1961: OPEN pn_var_grp_dt(p_var_rent_id);
1962: FETCH pn_var_grp_dt into l_max_grp_dt, l_min_grp_dt, l_min_grp_dt_364;
1955: BEGIN
1956:
1957: /*13-AUG-03 Chris.T. -Start*/
1958: pnp_debug_pkg.debug('UPDATE_YTD_BKPTS (+)');
1959: pnp_debug_pkg.debug('Parameter : p_var_rent_id = '|| p_var_rent_id);
1960: pnp_debug_pkg.debug('Parameter : p_period_id = '|| p_period_id);
1961: OPEN pn_var_grp_dt(p_var_rent_id);
1962: FETCH pn_var_grp_dt into l_max_grp_dt, l_min_grp_dt, l_min_grp_dt_364;
1963: l_first_partial_year := to_char(l_min_grp_dt,'YYYY');
1956:
1957: /*13-AUG-03 Chris.T. -Start*/
1958: pnp_debug_pkg.debug('UPDATE_YTD_BKPTS (+)');
1959: pnp_debug_pkg.debug('Parameter : p_var_rent_id = '|| p_var_rent_id);
1960: pnp_debug_pkg.debug('Parameter : p_period_id = '|| p_period_id);
1961: OPEN pn_var_grp_dt(p_var_rent_id);
1962: FETCH pn_var_grp_dt into l_max_grp_dt, l_min_grp_dt, l_min_grp_dt_364;
1963: l_first_partial_year := to_char(l_min_grp_dt,'YYYY');
1964: l_last_partial_year := to_char(l_max_grp_dt,'YYYY');
1963: l_first_partial_year := to_char(l_min_grp_dt,'YYYY');
1964: l_last_partial_year := to_char(l_max_grp_dt,'YYYY');
1965: IF pn_var_grp_dt%NOTFOUND THEN
1966: CLOSE pn_var_grp_dt;
1967: pnp_debug_pkg.debug('Raising no data found');
1968: RAISE NO_DATA_FOUND;
1969: ELSE
1970: pnp_debug_pkg.debug('Max = '||to_char(l_max_grp_dt,'MM/DD/YY')||
1971: 'Min = '||to_char(l_min_grp_dt,'MM/DD/YY')||
1966: CLOSE pn_var_grp_dt;
1967: pnp_debug_pkg.debug('Raising no data found');
1968: RAISE NO_DATA_FOUND;
1969: ELSE
1970: pnp_debug_pkg.debug('Max = '||to_char(l_max_grp_dt,'MM/DD/YY')||
1971: 'Min = '||to_char(l_min_grp_dt,'MM/DD/YY')||
1972: 'Min_364 = '||to_char(l_min_grp_dt_364,'MM/DD/YY'));
1973: END IF;
1974: CLOSE pn_var_grp_dt;
1973: END IF;
1974: CLOSE pn_var_grp_dt;
1975: /*13-AUG-03 Chris.T. -End*/
1976:
1977: pnp_debug_pkg.debug('opening periods cursor');
1978:
1979: /*FOR pn_var_trx_rec in pn_var_trx_cur LOOP
1980:
1981: IF l_old_reset_group_id <> pn_var_trx_rec.reset_group_id
1983: OR l_old_line_item_id <> pn_var_trx_rec.line_item_id
1984: OR l_old_bkpt_rate <> pn_var_trx_rec.bkpt_rate THEN
1985: l_summ_vol_start := 0;
1986: l_summ_vol_end := 0;
1987: pnp_debug_pkg.debug('Reset/Initialize YTD Group Vol Start/End');
1988: END IF;
1989:
1990: l_summ_vol_start := l_summ_vol_start + pn_var_trx_rec.prorated_grp_vol_start;
1991: l_summ_vol_end := l_summ_vol_end + pn_var_trx_rec.prorated_grp_vol_end;
1998: AND bkpt_rate = pn_var_trx_rec.bkpt_rate
1999: AND line_item_id = pn_var_trx_rec.line_item_id
2000: AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
2001: AND reset_group_id = pn_var_trx_rec.reset_group_id;
2002: pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
2003:
2004: l_sales_type_code := pn_var_trx_rec.sales_type_code;
2005: l_item_category_code := pn_var_trx_rec.item_category_code;
2006: /* 11-AUG-03 Chris.T. - Proration Rule Specific -- End
2028:
2029: IF (l_old_period_id <> pn_var_trx_rec.period_id)THEN
2030: l_prv_partial_period := l_old_partial_period; /*13-AUG-03 Chris.T.
2031: --Chris.T 19-NOV-03 Start
2032: pnp_debug_pkg.debug('Prev partial period = '||pn_var_trx_rec.partial_period);
2033: pnp_debug_pkg.debug('-----------------------------------------------');
2034:
2035: END IF;
2036: l_pro_invoice_flag := 'X'; /*Chris.T. 13-Aug-03
2029: IF (l_old_period_id <> pn_var_trx_rec.period_id)THEN
2030: l_prv_partial_period := l_old_partial_period; /*13-AUG-03 Chris.T.
2031: --Chris.T 19-NOV-03 Start
2032: pnp_debug_pkg.debug('Prev partial period = '||pn_var_trx_rec.partial_period);
2033: pnp_debug_pkg.debug('-----------------------------------------------');
2034:
2035: END IF;
2036: l_pro_invoice_flag := 'X'; /*Chris.T. 13-Aug-03
2037: l_curr_grp_date_year := to_char(pn_var_trx_rec.group_date,'YYYY'); /*Chris.T. 18-Aug-03
2038:
2039: --
2040: -- Proration Rule Based Processing
2041: --
2042: pnp_debug_pkg.debug('proration rule = '|| pn_var_trx_rec.proration_rule);
2043: IF pn_var_trx_rec.proration_rule IN ('FY','LY','FLY','CYNP','CYP') THEN /*Chris.T. 18-Aug-03
2044:
2045: IF l_ly_365_start_date IS NULL THEN
2046: l_ly_365_start_date := get_ly_365_start_date ( p_var_rent_id => p_var_rent_id);
2060: END IF;
2061:
2062: /* 11-AUG-03 Chris.T. - Proration Rule Specific -- Start
2063:
2064: pnp_debug_pkg.debug(' group_date = ' || pn_var_trx_rec.group_date);
2065: pnp_debug_pkg.debug(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2066: pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2061:
2062: /* 11-AUG-03 Chris.T. - Proration Rule Specific -- Start
2063:
2064: pnp_debug_pkg.debug(' group_date = ' || pn_var_trx_rec.group_date);
2065: pnp_debug_pkg.debug(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2066: pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2062: /* 11-AUG-03 Chris.T. - Proration Rule Specific -- Start
2063:
2064: pnp_debug_pkg.debug(' group_date = ' || pn_var_trx_rec.group_date);
2065: pnp_debug_pkg.debug(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2066: pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2063:
2064: pnp_debug_pkg.debug(' group_date = ' || pn_var_trx_rec.group_date);
2065: pnp_debug_pkg.debug(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2066: pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2064: pnp_debug_pkg.debug(' group_date = ' || pn_var_trx_rec.group_date);
2065: pnp_debug_pkg.debug(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2066: pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072: pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2065: pnp_debug_pkg.debug(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2066: pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072: pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2073: pnp_debug_pkg.debug(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2066: pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072: pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2073: pnp_debug_pkg.debug(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2074: pnp_debug_pkg.debug(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2067: pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072: pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2073: pnp_debug_pkg.debug(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2074: pnp_debug_pkg.debug(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2075:
2068: pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072: pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2073: pnp_debug_pkg.debug(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2074: pnp_debug_pkg.debug(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2075:
2076: /*dbms_output.put_line('Current group_date = ' || pn_var_trx_rec.group_date);
2069: pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072: pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2073: pnp_debug_pkg.debug(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2074: pnp_debug_pkg.debug(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2075:
2076: /*dbms_output.put_line('Current group_date = ' || pn_var_trx_rec.group_date);
2077: --dbms_output.put_line(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2070: pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071: pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072: pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2073: pnp_debug_pkg.debug(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2074: pnp_debug_pkg.debug(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2075:
2076: /*dbms_output.put_line('Current group_date = ' || pn_var_trx_rec.group_date);
2077: --dbms_output.put_line(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2078: --dbms_output.put_line(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2094: OR l_old_bkpt_rate <> pn_var_trx_rec.bkpt_rate THEN
2095:
2096: l_pro_bkpt_vol_start := 0;
2097: l_pro_bkpt_vol_end := 0;
2098: pnp_debug_pkg.debug('Reset/Initialize Pro Group Vol Start/End');
2099:
2100: END IF;
2101:
2102: IF pn_var_trx_rec.proration_rule IN ('CYP','CYNP') AND --Chris.T. 17MAR2004 Start
2105: nvl(pn_var_trx_rec.invoice_flag,'X') <> 'P' THEN
2106:
2107: l_pro_bkpt_vol_start := 0;
2108: l_pro_bkpt_vol_end := 0;
2109: pnp_debug_pkg.debug(' CYNP - Reset/Initialize Pro Group Vol Start/End');
2110: /* dbms_output.put_line(' CYNP - Reset/Initialize Pro Group Vol Start/End');
2111:
2112: END IF; --Chris.T. 17MAR2004 End
2113:
2128: AND bkpt_rate = pn_var_trx_rec.bkpt_rate
2129: AND line_item_id = pn_var_trx_rec.line_item_id
2130: AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
2131: AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id;
2132: pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
2133:
2134: ELSIF pn_var_trx_rec.proration_rule IN ('CYNP','CYP') THEN
2135: UPDATE pn_var_transactions_all
2136: SET pr_ytd_blended_vol_start = l_pro_bkpt_vol_start,
2142: AND bkpt_rate = pn_var_trx_rec.bkpt_rate
2143: AND line_item_id = pn_var_trx_rec.line_item_id
2144: AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
2145: AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id;
2146: pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
2147:
2148: END IF; --IF pn_var_trx_rec.proration_rule IN ('FY','LY','FLY','CYNP','CYP')
2149:
2150: IF pn_var_trx_rec.partial_period = 'Y' THEN -- Partial Year
2147:
2148: END IF; --IF pn_var_trx_rec.proration_rule IN ('FY','LY','FLY','CYNP','CYP')
2149:
2150: IF pn_var_trx_rec.partial_period = 'Y' THEN -- Partial Year
2151: pnp_debug_pkg.debug('Partial Period');
2152: /* dbms_output.put_line(' Partial Period ');
2153: l_max_per_grp_dt := NULL;
2154: OPEN get_max_per_grp_dt (pn_var_trx_rec.var_rent_id
2155: ,pn_var_trx_rec.period_id);
2256: /*dbms_output.put_line(' Step2 l_pro_invoice_flag := N');
2257: END IF;--IF pn_var_trx_rec.group_date = l_max_grp_dt
2258: END IF;--IF pn_var_trx_rec.proration_rule IN ('FY','FLY')
2259:
2260: pnp_debug_pkg.debug('Invoice Flag = '||l_pro_invoice_flag);
2261: /* dbms_output.put_line(' Invoice Flag = '||l_pro_invoice_flag);
2262:
2263: ELSE -- Complete Year
2264: pnp_debug_pkg.debug('Complete Year');
2260: pnp_debug_pkg.debug('Invoice Flag = '||l_pro_invoice_flag);
2261: /* dbms_output.put_line(' Invoice Flag = '||l_pro_invoice_flag);
2262:
2263: ELSE -- Complete Year
2264: pnp_debug_pkg.debug('Complete Year');
2265: /* dbms_output.put_line('Complete Year '||pn_var_trx_rec.period_id );
2266: DBMS_OUTPUT.PUT_LINE(' Previous Partial Period :'||l_prv_partial_period);
2267: l_pro_invoice_flag := NULL;
2268: IF (pn_var_trx_rec.proration_rule IN ('FY','FLY')) AND
2276: CLOSE get_invoice_grp_dt;
2277: RAISE NO_DATA_FOUND;
2278: END IF;
2279: CLOSE get_invoice_grp_dt;
2280: pnp_debug_pkg.debug('l_invoice_grp_date = ' || l_invoice_grp_dt);
2281: pnp_debug_pkg.debug('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2282: /* dbms_output.put_line('l_invoice_grp_date = ' || l_invoice_grp_dt);
2283: dbms_output.put_line('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2284: dbms_output.put_line('l_365_grp_dt = ' || l_365_grp_dt);
2277: RAISE NO_DATA_FOUND;
2278: END IF;
2279: CLOSE get_invoice_grp_dt;
2280: pnp_debug_pkg.debug('l_invoice_grp_date = ' || l_invoice_grp_dt);
2281: pnp_debug_pkg.debug('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2282: /* dbms_output.put_line('l_invoice_grp_date = ' || l_invoice_grp_dt);
2283: dbms_output.put_line('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2284: dbms_output.put_line('l_365_grp_dt = ' || l_365_grp_dt);
2285:
2305: CLOSE get_invoice_grp_dt;
2306: RAISE NO_DATA_FOUND;
2307: END IF;
2308: CLOSE get_invoice_grp_dt;
2309: pnp_debug_pkg.debug('l_invoice_grp_date = ' || l_invoice_grp_dt);
2310: pnp_debug_pkg.debug('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2311:
2312: /* dbms_output.put_line(' Step1');
2313: dbms_output.put_line(' l_old_group_date:'|| l_old_group_date);
2306: RAISE NO_DATA_FOUND;
2307: END IF;
2308: CLOSE get_invoice_grp_dt;
2309: pnp_debug_pkg.debug('l_invoice_grp_date = ' || l_invoice_grp_dt);
2310: pnp_debug_pkg.debug('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2311:
2312: /* dbms_output.put_line(' Step1');
2313: dbms_output.put_line(' l_old_group_date:'|| l_old_group_date);
2314: dbms_output.put_line(' l_365_grp_dt:'|| l_365_grp_dt);
2331: END IF;--IF pn_var_trx_rec.group_date = l_invoice_grp_dt
2332:
2333: END IF;
2334: /* dbms_output.put_line(' l_pro_invoice_flag for LY or FLY = ' || l_pro_invoice_flag);
2335: pnp_debug_pkg.debug('Complete Year Invoice Flag = '||l_pro_invoice_flag);
2336:
2337: END IF; --IF pn_var_trx_rec.partial_period = 'Y'
2338:
2339: IF l_pro_invoice_flag IN ('N','I') THEN
2344: AND line_item_id = pn_var_trx_rec.line_item_id
2345: AND NVL(proration_reset_group_id,0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
2346: --AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id; Srini 11AUG2004
2347:
2348: pnp_debug_pkg.debug('Current Updated for N,I = '||to_char(sql%rowcount));
2349: /* dbms_output.put_line(' UPDATE for N or I, SQL%ROWCOUNT := '||SQL%ROWCOUNT);
2350: ELSIF l_pro_invoice_flag IN ('F','L') THEN
2351: --IF pn_var_trx_rec.partial_period = 'Y' AND
2352: -- pn_var_trx_rec.proration_rule = 'FLY' THEN
2355: WHERE group_date = l_old_group_date
2356: AND line_item_id = l_old_line_item_id
2357: AND NVL(proration_reset_group_id, 0) = NVL(l_old_pro_reset_group_id, 0);
2358: --AND proration_reset_group_id = l_old_pro_reset_group_id; Srini 11AUG2004
2359: pnp_debug_pkg.debug('Previous Updated for F,L = '||to_char(sql%rowcount));
2360: /* dbms_output.put_line(' UPDATE for F or L, SQL%ROWCOUNT := '||SQL%ROWCOUNT);
2361:
2362:
2363: --Following should not happen
2385: AND NVL(proration_reset_group_id, 0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
2386: --AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id; Srini 11AUG2004
2387:
2388: /* dbms_output.put_line(' Current Updated for N,I = '||to_char(sql%rowcount));
2389: pnp_debug_pkg.debug('Current Updated for N,I = '||to_char(sql%rowcount));
2390: END IF;
2391:
2392: END IF;
2393:
2525: v_rate_tbl_2(i).rate := distinct_rates_rec.bkpt_rate;
2526: END LOOP;
2527:
2528: l_x := 'v_rate_tbl_1.count = ' || v_rate_tbl_1.count;
2529: pnp_debug_pkg.debug(l_x);
2530: l_x := 'v_rate_tbl_2.count = ' || v_rate_tbl_2.count;
2531: pnp_debug_pkg.debug(l_x);
2532:
2533: IF v_rate_tbl_1.count <> v_rate_tbl_2.count THEN
2527:
2528: l_x := 'v_rate_tbl_1.count = ' || v_rate_tbl_1.count;
2529: pnp_debug_pkg.debug(l_x);
2530: l_x := 'v_rate_tbl_2.count = ' || v_rate_tbl_2.count;
2531: pnp_debug_pkg.debug(l_x);
2532:
2533: IF v_rate_tbl_1.count <> v_rate_tbl_2.count THEN
2534: x_reset_flag := 'Y';
2535: ELSE
2535: ELSE
2536: FOR i in v_rate_tbl_1.first .. v_rate_tbl_1.last LOOP
2537:
2538: l_x := 'i= '|| i;
2539: pnp_debug_pkg.debug(l_x);
2540: l_x := 'v_rate_tbl_1= '|| v_rate_tbl_1(i).rate;
2541: pnp_debug_pkg.debug(l_x);
2542: l_x := 'v_rate_tbl_2= '|| v_rate_tbl_2(i).rate;
2543: pnp_debug_pkg.debug(l_x);
2537:
2538: l_x := 'i= '|| i;
2539: pnp_debug_pkg.debug(l_x);
2540: l_x := 'v_rate_tbl_1= '|| v_rate_tbl_1(i).rate;
2541: pnp_debug_pkg.debug(l_x);
2542: l_x := 'v_rate_tbl_2= '|| v_rate_tbl_2(i).rate;
2543: pnp_debug_pkg.debug(l_x);
2544: IF v_rate_tbl_1(i).rate <> v_rate_tbl_2(i).rate THEN
2545: x_reset_flag := 'Y';
2539: pnp_debug_pkg.debug(l_x);
2540: l_x := 'v_rate_tbl_1= '|| v_rate_tbl_1(i).rate;
2541: pnp_debug_pkg.debug(l_x);
2542: l_x := 'v_rate_tbl_2= '|| v_rate_tbl_2(i).rate;
2543: pnp_debug_pkg.debug(l_x);
2544: IF v_rate_tbl_1(i).rate <> v_rate_tbl_2(i).rate THEN
2545: x_reset_flag := 'Y';
2546: END IF;
2547:
2599: BEGIN
2600:
2601: --Get Proration Rule for VR
2602: /* l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id => p_var_rent_id);
2603: pnp_debug_pkg.log(' Proration Rule = '||l_proration_rule);
2604:
2605: IF l_proration_rule = 'NP' THEN --Chris.T. 11FEB2004
2606: FOR get_prorated_grp_vol_rec IN get_prorated_grp_vol_np_cur
2607: LOOP
2614: AND line_item_id = get_prorated_grp_vol_rec.line_item_id
2615: AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
2616: AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
2617:
2618: pnp_debug_pkg.debug('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
2619: ||to_char(get_prorated_grp_vol_rec.period_id)||'/'
2620: ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2621: ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2622: ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2619: ||to_char(get_prorated_grp_vol_rec.period_id)||'/'
2620: ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2621: ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2622: ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2623: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
2624: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
2625:
2626: END LOOP;
2627:
2620: ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2621: ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2622: ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2623: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
2624: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
2625:
2626: END LOOP;
2627:
2628: ELSE
2638: AND line_item_id = get_prorated_grp_vol_rec.line_item_id
2639: AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
2640: AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
2641:
2642: pnp_debug_pkg.debug('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
2643: ||to_char(get_prorated_grp_vol_rec.period_id)||'/'
2644: ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2645: ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2646: ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2643: ||to_char(get_prorated_grp_vol_rec.period_id)||'/'
2644: ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2645: ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2646: ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2647: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
2648: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
2649:
2650: END LOOP;
2651:
2644: ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2645: ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2646: ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2647: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
2648: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
2649:
2650: END LOOP;
2651:
2652: END IF; --IF l_proration_rule = 'NP' THEN --Chris.T. 11FEB2004*/
2811: SELECT termination_date
2812: INTO l_vr_term_dt
2813: FROM pn_var_rents_all
2814: WHERE var_rent_id = p_var_rent_id;
2815: pnp_debug_pkg.log(' Variable Rent Termination Date:'||l_vr_term_dt);
2816: pnp_debug_pkg.debug(' Variable Rent Termination Date:'||l_vr_term_dt);
2817:
2818: OPEN first_partial_period_cur;
2819: FETCH first_partial_period_cur INTO
2812: INTO l_vr_term_dt
2813: FROM pn_var_rents_all
2814: WHERE var_rent_id = p_var_rent_id;
2815: pnp_debug_pkg.log(' Variable Rent Termination Date:'||l_vr_term_dt);
2816: pnp_debug_pkg.debug(' Variable Rent Termination Date:'||l_vr_term_dt);
2817:
2818: OPEN first_partial_period_cur;
2819: FETCH first_partial_period_cur INTO
2820: l_partial_period_id ,
2862: END;
2863: --End Srini 10SEP2004
2864: END IF;
2865:
2866: --pnp_debug_pkg.log('Proration Rule = '|| p_proration_rule);
2867: --pnp_debug_pkg.log('Months in complete period = '|| l_months_in_complete_period);
2868: --pnp_debug_pkg.log('Months in partial period = '|| l_months_in_partial_period);
2869: --pnp_debug_pkg.log('Total period/Months = '|| l_total_months);
2870: --pnp_debug_pkg.log('l_complete_period_end_date = '|| l_complete_period_end_date);
2863: --End Srini 10SEP2004
2864: END IF;
2865:
2866: --pnp_debug_pkg.log('Proration Rule = '|| p_proration_rule);
2867: --pnp_debug_pkg.log('Months in complete period = '|| l_months_in_complete_period);
2868: --pnp_debug_pkg.log('Months in partial period = '|| l_months_in_partial_period);
2869: --pnp_debug_pkg.log('Total period/Months = '|| l_total_months);
2870: --pnp_debug_pkg.log('l_complete_period_end_date = '|| l_complete_period_end_date);
2871:
2864: END IF;
2865:
2866: --pnp_debug_pkg.log('Proration Rule = '|| p_proration_rule);
2867: --pnp_debug_pkg.log('Months in complete period = '|| l_months_in_complete_period);
2868: --pnp_debug_pkg.log('Months in partial period = '|| l_months_in_partial_period);
2869: --pnp_debug_pkg.log('Total period/Months = '|| l_total_months);
2870: --pnp_debug_pkg.log('l_complete_period_end_date = '|| l_complete_period_end_date);
2871:
2872:
2865:
2866: --pnp_debug_pkg.log('Proration Rule = '|| p_proration_rule);
2867: --pnp_debug_pkg.log('Months in complete period = '|| l_months_in_complete_period);
2868: --pnp_debug_pkg.log('Months in partial period = '|| l_months_in_partial_period);
2869: --pnp_debug_pkg.log('Total period/Months = '|| l_total_months);
2870: --pnp_debug_pkg.log('l_complete_period_end_date = '|| l_complete_period_end_date);
2871:
2872:
2873: FOR annual_blended_bkpts_rec IN
2866: --pnp_debug_pkg.log('Proration Rule = '|| p_proration_rule);
2867: --pnp_debug_pkg.log('Months in complete period = '|| l_months_in_complete_period);
2868: --pnp_debug_pkg.log('Months in partial period = '|| l_months_in_partial_period);
2869: --pnp_debug_pkg.log('Total period/Months = '|| l_total_months);
2870: --pnp_debug_pkg.log('l_complete_period_end_date = '|| l_complete_period_end_date);
2871:
2872:
2873: FOR annual_blended_bkpts_rec IN
2874: annual_blended_bkpts_cur (l_complete_period_end_date)
2960:
2961: IF p_proration_rule = 'CYNP' THEN
2962: /* DBMS_OUTPUT.PUT_LINE(' l_period_from = '||l_period_from);
2963: DBMS_OUTPUT.PUT_LINE(' l_period_to = '||l_period_to);
2964: --pnp_debug_pkg.log('updating for CYNP');
2965:
2966: UPDATE pn_var_transactions_all trx
2967: SET trx.pr_grp_blended_vol_start = l_group_from
2968: ,trx.pr_grp_blended_vol_end = l_group_to
3074: null;
3075: END;
3076:
3077: ELSIF p_proration_rule = 'CYP' THEN
3078: --pnp_debug_pkg.log('updating for CYP');
3079: UPDATE pn_var_transactions_all
3080: SET blended_period_vol_start = l_period_from
3081: ,blended_period_vol_end = l_period_to
3082: ,invoice_flag = 'P' --denote first partial or complete period
3107: l_months_in_group := ROUND(MONTHS_BETWEEN(get_prorated_grp_vol_rec.max_group_date + 1,
3108: get_prorated_grp_vol_rec.min_group_date), 1);
3109:
3110: IF p_proration_rule = 'CYNP' THEN
3111: pnp_debug_pkg.debug('updating CYNP');
3112: --pnp_debug_pkg.log('updating for CYNP');
3113:
3114: l_period_from := get_prorated_grp_vol_rec.blend_period_start;
3115: l_period_to := get_prorated_grp_vol_rec.blend_period_end;
3108: get_prorated_grp_vol_rec.min_group_date), 1);
3109:
3110: IF p_proration_rule = 'CYNP' THEN
3111: pnp_debug_pkg.debug('updating CYNP');
3112: --pnp_debug_pkg.log('updating for CYNP');
3113:
3114: l_period_from := get_prorated_grp_vol_rec.blend_period_start;
3115: l_period_to := get_prorated_grp_vol_rec.blend_period_end;
3116: l_group_from := (l_period_from/l_months_in_group)*l_invg_freq_code;
3159:
3160: END LOOP;
3161:
3162: ELSIF p_proration_rule = 'CYP' THEN
3163: pnp_debug_pkg.debug('updating CYP');
3164: --pnp_debug_pkg.log('updating for CYP');
3165:
3166: UPDATE pn_var_transactions_all
3167: SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
3160: END LOOP;
3161:
3162: ELSIF p_proration_rule = 'CYP' THEN
3163: pnp_debug_pkg.debug('updating CYP');
3164: --pnp_debug_pkg.log('updating for CYP');
3165:
3166: UPDATE pn_var_transactions_all
3167: SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
3168: ,blended_period_vol_end = get_prorated_grp_vol_rec.blend_period_end
3173: AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
3174:
3175: END IF;
3176:
3177: pnp_debug_pkg.debug('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3178: ||to_char(get_prorated_grp_vol_rec.period_id)
3179: ||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3180: ||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3181: ||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3178: ||to_char(get_prorated_grp_vol_rec.period_id)
3179: ||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3180: ||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3181: ||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3182: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3183: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3184: pnp_debug_pkg.debug('Pr Grp Blended Period Vol Start = '||l_group_from);
3185: pnp_debug_pkg.debug('Pr Grp Blended Period Vol End = '||l_group_to);
3186: --pnp_debug_pkg.log('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3179: ||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3180: ||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3181: ||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3182: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3183: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3184: pnp_debug_pkg.debug('Pr Grp Blended Period Vol Start = '||l_group_from);
3185: pnp_debug_pkg.debug('Pr Grp Blended Period Vol End = '||l_group_to);
3186: --pnp_debug_pkg.log('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3187: --||to_char(get_prorated_grp_vol_rec.period_id)
3180: ||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3181: ||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3182: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3183: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3184: pnp_debug_pkg.debug('Pr Grp Blended Period Vol Start = '||l_group_from);
3185: pnp_debug_pkg.debug('Pr Grp Blended Period Vol End = '||l_group_to);
3186: --pnp_debug_pkg.log('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3187: --||to_char(get_prorated_grp_vol_rec.period_id)
3188: --||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3181: ||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3182: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3183: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3184: pnp_debug_pkg.debug('Pr Grp Blended Period Vol Start = '||l_group_from);
3185: pnp_debug_pkg.debug('Pr Grp Blended Period Vol End = '||l_group_to);
3186: --pnp_debug_pkg.log('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3187: --||to_char(get_prorated_grp_vol_rec.period_id)
3188: --||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3189: --||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3182: pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3183: pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3184: pnp_debug_pkg.debug('Pr Grp Blended Period Vol Start = '||l_group_from);
3185: pnp_debug_pkg.debug('Pr Grp Blended Period Vol End = '||l_group_to);
3186: --pnp_debug_pkg.log('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3187: --||to_char(get_prorated_grp_vol_rec.period_id)
3188: --||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3189: --||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3190: --||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3187: --||to_char(get_prorated_grp_vol_rec.period_id)
3188: --||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3189: --||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3190: --||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3191: --pnp_debug_pkg.log('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3192: --pnp_debug_pkg.log('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3193: --pnp_debug_pkg.log('Pr Grp Blended Period Vol Start = '||l_group_from);
3194: --pnp_debug_pkg.log('Pr Grp Blended Period Vol End = '||l_group_to);
3195:
3188: --||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3189: --||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3190: --||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3191: --pnp_debug_pkg.log('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3192: --pnp_debug_pkg.log('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3193: --pnp_debug_pkg.log('Pr Grp Blended Period Vol Start = '||l_group_from);
3194: --pnp_debug_pkg.log('Pr Grp Blended Period Vol End = '||l_group_to);
3195:
3196: END LOOP;*/
3189: --||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3190: --||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3191: --pnp_debug_pkg.log('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3192: --pnp_debug_pkg.log('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3193: --pnp_debug_pkg.log('Pr Grp Blended Period Vol Start = '||l_group_from);
3194: --pnp_debug_pkg.log('Pr Grp Blended Period Vol End = '||l_group_to);
3195:
3196: END LOOP;*/
3197: NULL;
3190: --||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3191: --pnp_debug_pkg.log('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3192: --pnp_debug_pkg.log('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3193: --pnp_debug_pkg.log('Pr Grp Blended Period Vol Start = '||l_group_from);
3194: --pnp_debug_pkg.log('Pr Grp Blended Period Vol End = '||l_group_to);
3195:
3196: END LOOP;*/
3197: NULL;
3198: