[Home] [Help]
271: SELECT GRP_DATE_ID,
272: GRP_START_DATE,
273: GRP_END_DATE,
274: ORG_ID
275: FROM pn_var_grp_dates_all
276: WHERE var_rent_id = p_var_rent_id;
277:
278:
279: CURSOR get_dates_frequency IS
365:
366: /*============================================================================+
367: -- NAME : INSERT_REPORT_DATE_ROW
368: -- DESCRIPTION : create records in the pn_var_report_dates table based on
369: -- values in pn_var_grp_dates_all
370: -- PURPOSE :
371: -- INVOKED FROM :
372: -- ARGUMENTS : IN:
373: -- X_ROWID
403: -- X_REPORT_DATE_ID
404: --
405: -- REFERENCE : PN_COMMON.debug()
406: -- NOTES : create records in the pn_var_report_dates table based on values
407: -- in pn_var_grp_dates_all
408: -- HISTORY :
409: --
410: -- 09-JAN-2006 Prabhakar o Created.
411: +=============================================================================*/
596:
597:
598: /*============================================================================+
599: -- NAME : INSERT_GRP_DATE_ROW
600: -- DESCRIPTION : create records in the pn_var_grp_dates table based on
601: -- values in pn_var_periods
602: -- PURPOSE :
603: -- INVOKED FROM :
604: -- ARGUMENTS : IN:
622: -- X_ROWID
623: -- X_GRP_DATE_ID
624: --
625: -- REFERENCE : PN_COMMON.debug()
626: -- NOTES : create records in the pn_var_grp_dates table based on values
627: -- in pn_var_periods
628: -- HISTORY :
629: --
630: -- 31-AUG-01 DThota o Created
630: -- 31-AUG-01 DThota o Created
631: -- 01-NOV-01 DThota o Added columns PRORATION_FACTOR,ACTUAL_EXP_CODE,
632: -- FORECASTED_EXP_CODE,VARIANCE_EXP_CODE
633: -- 19-DEC-01 DThota o Added columns REPTG_DUE_DATE,INV_SCHEDULE_DATE
634: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_GRP_DATES with _ALL table.
635: -- 26-OCT-05 piagrawa o Bug#4702709 - Replaced select statement with cursor
636: -- and added org id to signature
637: +=============================================================================*/
638: PROCEDURE INSERT_GRP_DATE_ROW
662: ) IS
663:
664: CURSOR C IS
665: SELECT ROWID
666: FROM PN_VAR_GRP_DATES_ALL
667: WHERE GRP_DATE_ID = X_GRP_DATE_ID;
668:
669: CURSOR org_id_cur IS
670: SELECT org_id
680: -------------------------------------------------------
681: -- SELECT the nextval FOR group date id
682: -------------------------------------------------------
683: IF ( X_GRP_DATE_ID IS NULL) THEN
684: SELECT pn_var_grp_dates_s.nextval
685: INTO X_GRP_DATE_ID
686: FROM dual;
687: END IF;
688:
693: ELSE
694: l_org_id := X_ORG_ID;
695: END IF;
696:
697: INSERT INTO PN_VAR_GRP_DATES_ALL
698: (
699: GRP_DATE_ID,
700: LAST_UPDATE_DATE,
701: LAST_UPDATED_BY,
759: | PROCEDURE
760: | DELETE_GRP_DATE_ROW
761: |
762: | DESCRIPTION
763: | DELETE recORds FROM the PN_VAR_GRP_DATES
764: |
765: | SCOPE - PUBLIC
766: |
767: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
772: | OUT:
773: |
774: | RETURNS : None
775: |
776: | NOTES : DELETE recORds FROM the PN_VAR_GRP_DATES table
777: |
778: | MODIFICATION HISTORY
779: |
780: | 03-SEP-2001 Daniel Thota o Created
778: | MODIFICATION HISTORY
779: |
780: | 03-SEP-2001 Daniel Thota o Created
781: | 27-DEC-2001 Daniel Thota o INcluded parameter x_term_date
782: | 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_GRP_DATES with _ALL table.
783: +===========================================================================*/
784: PROCEDURE DELETE_GRP_DATE_ROW (
785: X_VAR_RENT_ID IN NUMBER,
786: X_TERM_DATE IN DATE
793: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_GRP_DATE_ROW (+)');
794:
795: l_date := NVL(x_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
796:
797: DELETE FROM PN_VAR_GRP_DATES_ALL
798: WHERE VAR_RENT_ID = X_VAR_RENT_ID
799: AND GRP_START_DATE > l_date
800: AND GRP_END_DATE > l_date;
801:
814: /*=============================================================================+
815: -- NAME : CREATE_VAR_RENT_PERIODS
816: -- DESCRIPTION : create variable rent periods record in PN_VAR_PERIODS and
817: -- corresponding group date/invoice date records in the
818: -- PN_VAR_GRP_DATES table for a variable rent record.
819: --
820: -- PURPOSE :
821: -- INVOKED FROM :
822: -- ARGUMENTS : IN:
828: --
829: -- REFERENCE : PN_COMMON.debug()
830: -- NOTES : create variable rent periods record in pn_var_periods and
831: -- corresponding group date/invoice date records in the
832: -- pn_var_grp_dates table for a variable rent record.
833: -- calls insert_periods_row and insert_grp_date_row procedures
834: -- HISTORY :
835: --
836: -- 31-AUG-01 Daniel o Created
851: -- support
852: -- 01-MAR-04 Vivek o Fix FOR bug#4215699. ModIFied the WHERE clause OF
853: -- CURSOR cal_periods to add END_date >= p_vr_comm_date
854: -- AND remove the period_year condition.
855: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL tbl
856: -- 26-OCT-05 piagrawa o Bug#4702709 - Passed org id in insert row handler.
857: -- pass org id to PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
858: -- 28-nov-05 pikhar o Replaced pn_var_periods with _ALL table
859: -- 11-JAN-07 Pseeram o Added the call to cretae_report_dates
1025:
1026: /* Fetches the group id which starts on a specified date for a VR agreement */
1027: CURSOR group_cur (p_start_date DATE) IS
1028: SELECT grp_end_date, grp_date_id
1029: FROM pn_var_grp_dates_all
1030: WHERE var_rent_id = p_var_rent_id
1031: AND grp_start_date = p_start_date;
1032:
1033: /* Fetches the period id which starts on a specified date for a VR agreement */
1039:
1040: /* Fetches the group id in which a specified invoice date lies for a VR agreement */
1041: CURSOR invoice_cur(p_inv_start_date DATE, p_inv_end_date DATE, p_period_id NUMBER) IS
1042: SELECT inv_start_date, inv_end_date
1043: FROM pn_var_grp_dates_all
1044: WHERE inv_start_date = p_inv_start_date
1045: AND inv_end_date = p_inv_end_date
1046: AND period_id = p_period_id;
1047:
1374:
1375: /* group does not exist in the database and needs to be added */
1376: pnp_debug_pkg.debug ('l_group_exists = N');
1377: ------------------------------------------------------------
1378: --call to insert into PN_VAR_GRP_DATES;
1379: ------------------------------------------------------------
1380: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1381: (X_ROWID => l_rowId,
1382: X_GRP_DATE_ID => l_grpDateId,
1414: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1415: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1416: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1417: /* Updte the end date and proration factor for the group */
1418: UPDATE pn_var_grp_dates_all
1419: SET grp_end_date = p_grp_end_date,
1420: proration_Factor = round(p_proration_factor,10),
1421: reptg_due_date = p_due_date --Bug # 5937888
1422: WHERE grp_date_id = l_grp_date_id;
1481: /* group does not exist in the database and needs to be added */
1482: pnp_debug_pkg.debug ('l_group_exists = N');
1483:
1484: ------------------------------------------------------------
1485: --call to insert into PN_VAR_GRP_DATES;
1486: ------------------------------------------------------------
1487: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1488: (X_ROWID => l_rowId,
1489: X_GRP_DATE_ID => l_grpDateId,
1521: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1522: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1523: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1524: /* Updte the end date and proration factor for the group */
1525: UPDATE pn_var_grp_dates_all
1526: SET grp_end_date = p_grp_end_date,
1527: proration_Factor = round(p_proration_factor,10),
1528: reptg_due_date = p_due_date --Bug # 5937888
1529: WHERE grp_date_id = l_grp_date_id;
1589: /* group does not exist in the database and needs to be added */
1590: pnp_debug_pkg.debug ('l_group_exists = N');
1591:
1592: ------------------------------------------------------------
1593: --call to insert into PN_VAR_GRP_DATES;
1594: ------------------------------------------------------------
1595:
1596: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1597: (X_ROWID => l_rowId,
1630: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1631: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1632: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1633: /* Updte the end date and proration factor for the group */
1634: UPDATE pn_var_grp_dates_all
1635: SET grp_end_date = p_grp_end_date,
1636: proration_Factor = round(p_proration_factor,10),
1637: reptg_due_date = p_due_date --Bug # 5937888
1638: WHERE grp_date_id = l_grp_date_id;
1698: /* group does not exist in the database and needs to be added */
1699: pnp_debug_pkg.debug ('l_group_exists = N');
1700:
1701: ------------------------------------------------------------
1702: --call to insert into PN_VAR_GRP_DATES;
1703: ------------------------------------------------------------
1704: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1705: (X_ROWID => l_rowId,
1706: X_GRP_DATE_ID => l_grpDateId,
1738: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1739: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1740: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1741: /* Updte the end date and proration factor for the group */
1742: UPDATE pn_var_grp_dates_all
1743: SET grp_end_date = p_grp_end_date,
1744: proration_Factor = round(p_proration_factor,10),
1745: reptg_due_date = p_due_date --Bug # 5937888
1746: WHERE grp_date_id = l_grp_date_id;
1833: END LOOP;
1834:
1835: IF l_invoice_exists = 'N' THEN
1836: ------------------------------------------------------------
1837: --call to update PN_VAR_GRP_DATES;
1838: ------------------------------------------------------------
1839:
1840: UPDATE PN_VAR_GRP_DATES
1841: SET inv_start_date = p_inv_start_date,
1836: ------------------------------------------------------------
1837: --call to update PN_VAR_GRP_DATES;
1838: ------------------------------------------------------------
1839:
1840: UPDATE PN_VAR_GRP_DATES
1841: SET inv_start_date = p_inv_start_date,
1842: inv_end_date = p_inv_end_date,
1843: invoice_date = p_invoice_date,
1844: inv_schedule_date = p_schedule_date
1842: inv_end_date = p_inv_end_date,
1843: invoice_date = p_invoice_date,
1844: inv_schedule_date = p_schedule_date
1845: WHERE grp_date_id in (SELECT grp_date_id
1846: FROM pn_var_grp_dates
1847: WHERE grp_start_date <= p_inv_end_date
1848: AND grp_end_date >= p_inv_start_date
1849: AND period_id = p_period_id
1850: AND var_rent_id = p_var_rent_id)
1906: END LOOP;
1907:
1908: IF l_invoice_exists = 'N' THEN
1909: ------------------------------------------------------------
1910: --call to update PN_VAR_GRP_DATES;
1911: ------------------------------------------------------------
1912: UPDATE PN_VAR_GRP_DATES
1913: SET inv_start_date = p_inv_start_date,
1914: inv_end_date = p_inv_end_date,
1908: IF l_invoice_exists = 'N' THEN
1909: ------------------------------------------------------------
1910: --call to update PN_VAR_GRP_DATES;
1911: ------------------------------------------------------------
1912: UPDATE PN_VAR_GRP_DATES
1913: SET inv_start_date = p_inv_start_date,
1914: inv_end_date = p_inv_end_date,
1915: invoice_date = p_invoice_date,
1916: inv_schedule_date = p_schedule_date
1914: inv_end_date = p_inv_end_date,
1915: invoice_date = p_invoice_date,
1916: inv_schedule_date = p_schedule_date
1917: WHERE grp_date_id in (SELECT grp_date_id
1918: FROM pn_var_grp_dates
1919: WHERE grp_start_date <= p_inv_end_date
1920: AND grp_end_date >= p_inv_start_date
1921: AND period_id = p_period_id
1922: AND var_rent_id = p_var_rent_id)
1977: END LOOP;
1978:
1979: IF l_invoice_exists = 'N' THEN
1980: ------------------------------------------------------------
1981: --call to update PN_VAR_GRP_DATES;
1982: ------------------------------------------------------------
1983: UPDATE PN_VAR_GRP_DATES
1984: SET inv_start_date = p_inv_start_date,
1985: inv_end_date = p_inv_end_date,
1979: IF l_invoice_exists = 'N' THEN
1980: ------------------------------------------------------------
1981: --call to update PN_VAR_GRP_DATES;
1982: ------------------------------------------------------------
1983: UPDATE PN_VAR_GRP_DATES
1984: SET inv_start_date = p_inv_start_date,
1985: inv_end_date = p_inv_end_date,
1986: invoice_date = p_invoice_date,
1987: inv_schedule_date = p_schedule_date
1985: inv_end_date = p_inv_end_date,
1986: invoice_date = p_invoice_date,
1987: inv_schedule_date = p_schedule_date
1988: WHERE grp_date_id in (SELECT grp_date_id
1989: FROM pn_var_grp_dates
1990: WHERE grp_start_date <= p_inv_end_date
1991: AND grp_end_date >= p_inv_start_date
1992: AND period_id = p_period_id
1993: AND var_rent_id = p_var_rent_id)
2047: END LOOP;
2048:
2049: IF l_invoice_exists = 'N' THEN
2050: ------------------------------------------------------------
2051: --call to update PN_VAR_GRP_DATES;
2052: ------------------------------------------------------------
2053:
2054: UPDATE PN_VAR_GRP_DATES
2055: SET inv_start_date = p_inv_start_date,
2050: ------------------------------------------------------------
2051: --call to update PN_VAR_GRP_DATES;
2052: ------------------------------------------------------------
2053:
2054: UPDATE PN_VAR_GRP_DATES
2055: SET inv_start_date = p_inv_start_date,
2056: inv_end_date = p_inv_end_date,
2057: invoice_date = p_invoice_date,
2058: inv_schedule_date = p_schedule_date
2056: inv_end_date = p_inv_end_date,
2057: invoice_date = p_invoice_date,
2058: inv_schedule_date = p_schedule_date
2059: WHERE grp_date_id in (SELECT grp_date_id
2060: FROM pn_var_grp_dates
2061: WHERE grp_start_date <= p_inv_end_date
2062: AND grp_end_date >= p_inv_start_date
2063: AND period_id = p_period_id
2064: AND var_rent_id = p_var_rent_id)
2084: /*=============================================================================+
2085: -- NAME : CREATE_VAR_RENT_PERIODS_NOCAL
2086: -- DESCRIPTION : Create variable rent periods recORd IN PN_VAR_PERIODS AND
2087: -- corresponding group date/invoice date records in the
2088: -- PN_VAR_GRP_DATES table for a variable rent recORd when a
2089: -- GL calendar is not specified in the VR agreement.
2090: -- PURPOSE :
2091: -- INVOKED FROM :
2092: -- ARGUMENTS : IN:
2098: --
2099: -- REFERENCE : PN_COMMON.debug()
2100: -- NOTES : Create variable rent periods recORd IN PN_VAR_PERIODS AND
2101: -- corresponding group date/invoice date records in the
2102: -- PN_VAR_GRP_DATES table for a variable rent record for
2103: -- yearly VR periods depending on the year start date.
2104: -- Calls INSERT_PERIODS_ROW AND INSERT_GRP_DATE_ROW procedures
2105: -- HISTORY :
2106: -- 02-SEP-02 kkhegde o Created
2202:
2203: /* Fetches the group id which starts on a specified date for a VR agreement */
2204: CURSOR group_cur (p_start_date DATE) IS
2205: SELECT grp_end_date, grp_date_id
2206: FROM pn_var_grp_dates_all
2207: WHERE var_rent_id = p_var_rent_id
2208: AND grp_start_date = p_start_date;
2209:
2210: /* Fetches the period id which starts on a specified date for a VR agreement */
2609: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2610: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2611: /* Updte the end date and proration factor for the group */
2612:
2613: UPDATE pn_var_grp_dates_all
2614: SET grp_end_date = VR_grp_dates(j).l_grp_end_date,
2615: proration_Factor = round(l_proration_Factor,10),
2616: reptg_due_date = VR_grp_dates(j).l_reptg_due_date --Bug # 5937888
2617: WHERE grp_date_id = l_grp_date_id;
2639: | DELETE_VAR_RENT_PERIODS
2640: |
2641: | DESCRIPTION
2642: | Delete variable rent periods record in PN_VAR_PERIODS, corresponding
2643: | group date/invoice date records in the PN_VAR_GRP_DATES table,
2644: | corresponding line items in the PN_VAR_LINES table
2645: | for a variable rent record
2646: |
2647: | SCOPE - PUBLIC
2657: | RETURNS : None
2658: |
2659: | NOTES : Delete variable rent periods record in PN_VAR_PERIODS,
2660: | corresponding group date/invoice date records in the
2661: | PN_VAR_GRP_DATES table, corresponding line items in the
2662: | PN_VAR_LINES table for a variable rent record
2663: | Calls DELETE_PERIODS_ROW, DELETE_GRP_DATE_ROW,
2664: | DELETE_VAR_RENT_LINES procedures
2665: |
2809: | UPDATE_VAR_RENT_PERIODS
2810: |
2811: | DESCRIPTION
2812: | Update variable rent periods record in PN_VAR_PERIODS, corresponding
2813: | group date/invoice date records in the PN_VAR_GRP_DATES table
2814: | for a variable rent record contraction
2815: |
2816: | SCOPE - PUBLIC
2817: |
2825: |
2826: | RETURNS : None
2827: |
2828: | NOTES : Update variable rent periods record in PN_VAR_PERIODS, corresponding
2829: | group date/invoice date records in the PN_VAR_GRP_DATES table
2830: | for a variable rent record contraction
2831: |
2832: | MODIFICATION HISTORY
2833: |
2888: -- updated with the new proration factor after lease contraction
2889:
2890: SELECT grp.grp_start_date
2891: INTO p_grp_start_date
2892: FROM pn_var_grp_dates_all grp
2893: WHERE grp.var_rent_id = p_var_rent_id
2894: AND grp.grp_start_date <= p_term_date
2895: AND grp.grp_end_date >= p_term_date
2896: AND rownum < 2;
2933: END IF;
2934:
2935: END IF;
2936:
2937: --call to update PN_VAR_GRP_DATES;
2938:
2939: UPDATE pn_var_grp_dates_all
2940: SET grp_end_date = p_term_date,
2941: proration_factor = round(p_proration_factor,10),
2935: END IF;
2936:
2937: --call to update PN_VAR_GRP_DATES;
2938:
2939: UPDATE pn_var_grp_dates_all
2940: SET grp_end_date = p_term_date,
2941: proration_factor = round(p_proration_factor,10),
2942: last_update_date = p_last_update_date,
2943: last_updated_by = p_last_updated_by,
4291: |
4292: | MODIFICATION HISTORY
4293: |
4294: | 26-OCT-2001 Daniel Thota o Created
4295: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4296: +===========================================================================*/
4297:
4298: FUNCTION find_reporting_periods (p_period_id NUMBER) RETURN NUMBER IS
4299:
4304: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (+)');
4305:
4306: SELECT count(GRP_START_DATE)
4307: INTO l_reporting_periods
4308: FROM pn_var_grp_dates_all
4309: WHERE period_id = p_period_id;
4310:
4311: RETURN l_reporting_periods;
4312:
4511: | MODIFICATION HISTORY
4512: |
4513: | 16-NOV-01 Daniel Thota o Created
4514: | 21-NOV-03 Daniel Thota o Added forecasted_exp_code to SELECT -- Fix for bug # 2435455
4515: | 14-JUN-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4516: +===========================================================================*/
4517:
4518: FUNCTION get_grp_date_info (p_var_rent_id IN NUMBER,
4519: p_period_id IN NUMBER,
4537: invoice_date,
4538: inv_schedule_date,
4539: forecasted_exp_code
4540: INTO l_grp_date_info
4541: FROM pn_var_grp_dates_all
4542: WHERE var_rent_id = p_var_rent_id
4543: AND period_id = p_period_id
4544: AND grp_start_date <= p_start_date
4545: AND grp_start_date <= p_end_date
4584: |
4585: | MODIFICATION HISTORY
4586: |
4587: | 27-DEC-01 Daniel Thota o Created
4588: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4589: +===========================================================================*/
4590:
4591: FUNCTION get_proration_factor(p_var_rent_id IN NUMBER)
4592: RETURN PRORATION_FACTOR_REC IS
4607: (g2.grp_end_date-g2.grp_start_date)+1 last_group_pro_days,
4608: g2.proration_factor last_group_gl_days
4609: INTO l_proration_factor
4610: FROM pn_var_periods_ALL p1, pn_var_periods_ALL p2,
4611: pn_var_grp_dates_ALL g1, pn_var_grp_dates_ALL g2
4612: WHERE p1.var_rent_id = p2.var_rent_id
4613: AND p1.var_rent_id = p_var_rent_id
4614: AND g1.var_rent_id = g2.var_rent_id
4615: AND g1.var_rent_id = p_var_rent_id
4619: AND p2.period_id = (SELECT max(period_id)
4620: FROM pn_var_periods_ALL
4621: WHERE var_rent_id = p_var_rent_id)
4622: AND g1.grp_date_id = (SELECT min(grp_date_id)
4623: FROM pn_var_grp_dates
4624: WHERE var_rent_id = p_var_rent_id)
4625: AND g2.grp_date_id = (SELECT max(grp_date_id)
4626: FROM pn_var_grp_dates
4627: WHERE var_rent_id = p_var_rent_id);
4622: AND g1.grp_date_id = (SELECT min(grp_date_id)
4623: FROM pn_var_grp_dates
4624: WHERE var_rent_id = p_var_rent_id)
4625: AND g2.grp_date_id = (SELECT max(grp_date_id)
4626: FROM pn_var_grp_dates
4627: WHERE var_rent_id = p_var_rent_id);
4628:
4629: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_PRORATION_FACTOR (-)');
4630:
4774: | called FROM with p_block AS PERIODS_INV_BLK
4775: | Added brackets so that the join condition
4776: | IS evaluated correctly.
4777: | Bug 4026980
4778: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4779: | 12-Mar-07 Shabda o Bug 5911819 - Volume records are considered exported
4780: | when actual or variance exp_code = y (Not forecasted exp_code)
4781: +===========================================================================*/
4782:
4796: SELECT 1
4797: INTO l_exported
4798: FROM dual
4799: WHERE EXISTS ( SELECT grp_date_id
4800: FROM pn_var_grp_dates_all
4801: WHERE var_rent_id = p_id
4802: AND (actual_exp_code = 'Y' OR
4803: forecasted_exp_code = 'Y')
4804: );
4808: SELECT 1
4809: INTO l_exported
4810: FROM dual
4811: WHERE EXISTS ( SELECT grp_date_id
4812: FROM pn_var_grp_dates_all
4813: WHERE period_id = p_id
4814: AND (actual_exp_code = 'Y' OR
4815: forecasted_exp_code = 'Y')
4816: );
4820: SELECT 1
4821: INTO l_exported
4822: FROM dual
4823: WHERE EXISTS ( SELECT grp_date_id
4824: FROM pn_var_grp_dates_all
4825: WHERE period_id IN (SELECT period_id
4826: FROM pn_var_lines_ALL
4827: WHERE line_item_id = p_id)
4828: AND (actual_exp_code = 'Y' OR
4834: SELECT 1
4835: INTO l_exported
4836: FROM dual
4837: WHERE EXISTS ( SELECT grp_date_id
4838: FROM pn_var_grp_dates_all
4839: WHERE period_id IN (SELECT period_id
4840: FROM pn_var_lines_ALL
4841: WHERE line_default_id = p_id)
4842: AND (actual_exp_code = 'Y' OR
4847: SELECT 1
4848: INTO l_exported
4849: FROM dual
4850: WHERE EXISTS ( SELECT grp_date_id
4851: FROM pn_var_grp_dates_all a,
4852: pn_var_periods_all b,
4853: pn_var_lines_all c
4854: WHERE a.period_id = b.period_id
4855: AND b.period_id = c.period_id
4981: | 21-Jun-02 Ashish Kumar Fix BUG#2096829 and BUG#2096810 Change the Where clause of the
4982: | Completed and Reconciled Select stmts
4983: | 11-Jul-02 Ashish Kumar Fix for BUG#2452276 In the Reconcile query change the
4984: | Forcasted_exp_code to Variance_exp_code
4985: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4986: +===========================================================================*/
4987:
4988: FUNCTION find_status (p_period_id NUMBER) RETURN VARCHAR2 IS
4989:
4997: INTO l_status
4998: FROM dual
4999: WHERE NOT EXISTS
5000: (SELECT grp_date_id
5001: FROM pn_var_grp_dates_all
5002: WHERE period_id = p_period_id
5003: AND ((actual_exp_code = 'N' AND FORECASTED_exp_code ='N' AND variance_exp_code = 'N') OR
5004: (actual_exp_code = 'N' AND FORECASTED_exp_code ='Y' AND variance_exp_code = 'Y')
5005: )
5025: SELECT 'RECONCILED'
5026: INTO l_status
5027: FROM dual
5028: WHERE NOT EXISTS(SELECT grp_date_id
5029: FROM pn_var_grp_dates_all
5030: WHERE period_id = p_period_id
5031: AND variance_exp_code = 'N')
5032: AND NOT EXISTS(SELECT vol_hist_id
5033: FROM pn_var_vol_hist_all
5286: FROM pn_var_vol_hist_all vh
5287: WHERE actual_exp_code = 'N'
5288: AND period_id = p_period_id
5289: AND EXISTS (SELECT grp.grp_date_id
5290: FROM pn_var_grp_dates_all grp
5291: WHERE actual_exp_code = 'Y'
5292: AND grp.grp_date_id = vh.grp_date_id));
5293:
5294: ELSIF p_invoice_on = 'FORECASTED' THEN
5300: FROM pn_var_vol_hist_all vh
5301: WHERE forecasted_exp_code = 'N'
5302: AND period_id = p_period_id
5303: AND EXISTS (SELECT grp.grp_date_id
5304: FROM pn_var_grp_dates_all grp
5305: WHERE forecasted_exp_code = 'Y'
5306: AND grp.grp_date_id = vh.grp_date_id));
5307:
5308: END IF;
6558: SELECT start_date, end_date ,proration_factor
6559: FROM pn_var_periods_all
6560: WHERE period_id = p_period_id;
6561:
6562: CURSOR pn_var_grp_dates_cur
6563: IS
6564: SELECT *
6565: FROM pn_var_grp_dates_all
6566: WHERE period_id = p_period_id
6561:
6562: CURSOR pn_var_grp_dates_cur
6563: IS
6564: SELECT *
6565: FROM pn_var_grp_dates_all
6566: WHERE period_id = p_period_id
6567: ORDER BY grp_start_date;
6568:
6569: v_inv_dates_tbl group_dates_tbl;
6807: l_inv_counter := l_inv_counter + 1;
6808: END LOOP;
6809: END IF; -- end if of gl_calendar = Y
6810:
6811: FOR pn_var_grp_dates_rec IN pn_var_grp_dates_cur
6812: LOOP
6813: FOR i IN vr_grp_dates.FIRST .. vr_grp_dates.LAST
6814: LOOP
6815: IF (vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date AND
6811: FOR pn_var_grp_dates_rec IN pn_var_grp_dates_cur
6812: LOOP
6813: FOR i IN vr_grp_dates.FIRST .. vr_grp_dates.LAST
6814: LOOP
6815: IF (vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date AND
6816: vr_grp_dates(i).l_grp_end_date = pn_var_grp_dates_rec.grp_end_date )
6817: THEN
6818: --put_log('Same groups found');
6819: vr_grp_dates(i).l_rec_found := 'X';
6812: LOOP
6813: FOR i IN vr_grp_dates.FIRST .. vr_grp_dates.LAST
6814: LOOP
6815: IF (vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date AND
6816: vr_grp_dates(i).l_grp_end_date = pn_var_grp_dates_rec.grp_end_date )
6817: THEN
6818: --put_log('Same groups found');
6819: vr_grp_dates(i).l_rec_found := 'X';
6820: END IF;
6818: --put_log('Same groups found');
6819: vr_grp_dates(i).l_rec_found := 'X';
6820: END IF;
6821:
6822: IF vr_grp_dates(i).l_grp_end_date <> pn_var_grp_dates_rec.grp_end_date AND
6823: vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date THEN
6824: --put_log('Updating end dates');
6825: vr_grp_dates(i).l_rec_found := 'X';
6826: l_new_st_date := nvl(l_new_st_date,pn_var_grp_dates_rec.grp_start_date);
6819: vr_grp_dates(i).l_rec_found := 'X';
6820: END IF;
6821:
6822: IF vr_grp_dates(i).l_grp_end_date <> pn_var_grp_dates_rec.grp_end_date AND
6823: vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date THEN
6824: --put_log('Updating end dates');
6825: vr_grp_dates(i).l_rec_found := 'X';
6826: l_new_st_date := nvl(l_new_st_date,pn_var_grp_dates_rec.grp_start_date);
6827: l_new_end_date := vr_grp_dates(i).l_grp_end_date;
6822: IF vr_grp_dates(i).l_grp_end_date <> pn_var_grp_dates_rec.grp_end_date AND
6823: vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date THEN
6824: --put_log('Updating end dates');
6825: vr_grp_dates(i).l_rec_found := 'X';
6826: l_new_st_date := nvl(l_new_st_date,pn_var_grp_dates_rec.grp_start_date);
6827: l_new_end_date := vr_grp_dates(i).l_grp_end_date;
6828:
6829: IF p_pn_var_rent_dates_rec.reptg_day_of_month IS NOT NULL THEN
6830: l_rptg_date :=(ADD_MONTHS(FIRST_DAY(vr_grp_dates(i).l_grp_end_date),1)-1)+
6860: put_log(' Group Date While Updating Group Dates:'||vr_grp_dates(i).l_group_date);
6861: --vr_grp_dates(i).l_proration_factor := 1;
6862: END IF;
6863:
6864: UPDATE pn_var_grp_dates_all
6865: SET grp_end_date = vr_grp_dates(i).l_grp_end_date ,
6866: proration_factor = round(vr_grp_dates(i).l_proration_factor,10), --Chris.T. 20MAY2004
6867: last_update_date = sysdate,
6868: last_updated_by = FND_GLOBAL.USER_ID,
6866: proration_factor = round(vr_grp_dates(i).l_proration_factor,10), --Chris.T. 20MAY2004
6867: last_update_date = sysdate,
6868: last_updated_by = FND_GLOBAL.USER_ID,
6869: last_update_login = FND_GLOBAL.LOGIN_ID
6870: WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;
6871:
6872: /*UPDATE pn_var_transactions_all
6873: SET bkpt_end_date = vr_grp_dates(i).l_grp_end_date,
6874: last_update_date = SYSDATE,
6873: SET bkpt_end_date = vr_grp_dates(i).l_grp_end_date,
6874: last_update_date = SYSDATE,
6875: last_updated_by = FND_GLOBAL.USER_ID,
6876: last_update_login = FND_GLOBAL.LOGIN_ID
6877: WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;*/
6878:
6879: END IF; -- end if of end date not being equal
6880: END LOOP; -- end loop for groups table.
6881: END LOOP; -- end loop for pn_var_grp_dates_rec
6877: WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;*/
6878:
6879: END IF; -- end if of end date not being equal
6880: END LOOP; -- end loop for groups table.
6881: END LOOP; -- end loop for pn_var_grp_dates_rec
6882:
6883: FOR i in vr_grp_dates.FIRST .. vr_grp_dates.LAST
6884: LOOP
6885: IF vr_grp_dates(i).l_rec_found is NULL THEN
7883: WHERE var_rent_id = p_var_rent_id);
7884:
7885: DELETE pn_var_rent_summ_all
7886: WHERE grp_date_id IN (SELECT grp_date_id
7887: FROM pn_var_grp_dates_all
7888: WHERE var_rent_id = p_var_rent_id
7889: AND grp_start_date > p_new_termn_date);
7890:
7891: DELETE pn_var_constraints_all
8126: ELSE
8127:
8128: pnp_debug_pkg.debug ('deleting group date and periods ...');
8129: /* Delete group dates and periods for which there exists no invoice */
8130: DELETE pn_var_grp_dates_all
8131: WHERE period_id = period_rec.period_id;
8132:
8133: DELETE pn_var_periods_all
8134: WHERE period_id = period_rec.period_id;
8310: inv_end_date,
8311: group_date,
8312: invoice_date,
8313: period_id
8314: FROM pn_var_grp_dates_all
8315: WHERE var_rent_id = p_var_rent_id
8316: AND p_new_termn_date BETWEEN grp_start_date AND grp_end_date;
8317:
8318: /* Get the details of breakpoint header default */
8347:
8348: /* Get the max invoice end date corresponding to the new termination date*/
8349: CURSOR inv_end_dt_cur(p_var_rent_id IN NUMBER) IS
8350: SELECT max(inv_end_date) inv_end_date
8351: FROM pn_var_grp_dates_all
8352: WHERE var_rent_id = p_var_rent_id
8353: AND inv_end_date <= p_new_termn_date;
8354:
8355: l_reptg_day_of_month NUMBER := 0;
8403: l_inv_end_dt := grp_date_rec.inv_end_date;
8404: END LOOP;
8405:
8406: -- Bug13711733
8407: DELETE pn_var_grp_dates_all
8408: WHERE period_id = p_period_id
8409: and grp_start_date > p_new_termn_date
8410: and var_rent_id = p_var_rent_id;
8411:
8424: ,vr_id => p_var_rent_id
8425: ,p_period_id => l_period_id);*/
8426:
8427:
8428: UPDATE pn_var_grp_dates_all
8429: SET grp_end_date = p_new_termn_date,
8430: proration_factor = round(l_proration_factor,10),
8431: reptg_due_date = l_due_date
8432: WHERE var_rent_id = p_var_rent_id
8444: l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date( inv_start_date => l_invoice_date
8445: ,vr_id => p_var_rent_id
8446: ,p_period_id => l_period_id);
8447:
8448: UPDATE pn_var_grp_dates_all
8449: SET inv_end_date = p_new_termn_date,
8450: inv_schedule_date = l_inv_sch_date
8451: WHERE var_rent_id = p_var_rent_id
8452: AND p_new_termn_date BETWEEN inv_start_date AND inv_end_date;
8788: WHERE var_rent_id = p_var_rent_id);
8789:
8790: DELETE pn_var_rent_summ_all
8791: WHERE grp_date_id IN (SELECT grp_date_id
8792: FROM pn_var_grp_dates_all
8793: WHERE var_rent_id = p_var_rent_id);
8794:
8795: DELETE pn_var_constraints_all
8796: WHERE period_id IN (SELECT period_id
8823:
8824: DELETE pn_var_rent_inv_all
8825: WHERE var_rent_id = p_var_rent_id;
8826:
8827: DELETE pn_var_grp_dates_all
8828: WHERE var_rent_id = p_var_rent_id;
8829:
8830: DELETE pn_var_periods_all
8831: WHERE var_rent_id = p_var_rent_id;
8921:
8922: /* Get the max invoice end date corresponding to the new termination date*/
8923: CURSOR inv_end_dt_cur(p_var_rent_id IN NUMBER) IS
8924: SELECT max(inv_end_date) inv_end_date
8925: FROM pn_var_grp_dates_all
8926: WHERE var_rent_id = p_var_rent_id
8927: AND inv_end_date <= p_new_termn_date;
8928:
8929: BEGIN