[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
1021:
1022: /* Fetches the group id which starts on a specified date for a VR agreement */
1023: CURSOR group_cur (p_start_date DATE) IS
1024: SELECT grp_end_date, grp_date_id
1025: FROM pn_var_grp_dates_all
1026: WHERE var_rent_id = p_var_rent_id
1027: AND grp_start_date = p_start_date;
1028:
1029: /* Fetches the period id which starts on a specified date for a VR agreement */
1035:
1036: /* Fetches the group id in which a specified invoice date lies for a VR agreement */
1037: CURSOR invoice_cur(p_inv_start_date DATE, p_inv_end_date DATE, p_period_id NUMBER) IS
1038: SELECT inv_start_date, inv_end_date
1039: FROM pn_var_grp_dates_all
1040: WHERE inv_start_date = p_inv_start_date
1041: AND inv_end_date = p_inv_end_date
1042: AND period_id = p_period_id;
1043:
1370:
1371: /* group does not exist in the database and needs to be added */
1372: pnp_debug_pkg.debug ('l_group_exists = N');
1373: ------------------------------------------------------------
1374: --call to insert into PN_VAR_GRP_DATES;
1375: ------------------------------------------------------------
1376: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1377: (X_ROWID => l_rowId,
1378: X_GRP_DATE_ID => l_grpDateId,
1410: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1411: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1412: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1413: /* Updte the end date and proration factor for the group */
1414: UPDATE pn_var_grp_dates_all
1415: SET grp_end_date = p_grp_end_date,
1416: proration_Factor = round(p_proration_factor,10),
1417: reptg_due_date = p_due_date --Bug # 5937888
1418: WHERE grp_date_id = l_grp_date_id;
1477: /* group does not exist in the database and needs to be added */
1478: pnp_debug_pkg.debug ('l_group_exists = N');
1479:
1480: ------------------------------------------------------------
1481: --call to insert into PN_VAR_GRP_DATES;
1482: ------------------------------------------------------------
1483: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1484: (X_ROWID => l_rowId,
1485: X_GRP_DATE_ID => l_grpDateId,
1517: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1518: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1519: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1520: /* Updte the end date and proration factor for the group */
1521: UPDATE pn_var_grp_dates_all
1522: SET grp_end_date = p_grp_end_date,
1523: proration_Factor = round(p_proration_factor,10),
1524: reptg_due_date = p_due_date --Bug # 5937888
1525: WHERE grp_date_id = l_grp_date_id;
1585: /* group does not exist in the database and needs to be added */
1586: pnp_debug_pkg.debug ('l_group_exists = N');
1587:
1588: ------------------------------------------------------------
1589: --call to insert into PN_VAR_GRP_DATES;
1590: ------------------------------------------------------------
1591:
1592: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1593: (X_ROWID => l_rowId,
1626: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1627: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1628: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1629: /* Updte the end date and proration factor for the group */
1630: UPDATE pn_var_grp_dates_all
1631: SET grp_end_date = p_grp_end_date,
1632: proration_Factor = round(p_proration_factor,10),
1633: reptg_due_date = p_due_date --Bug # 5937888
1634: WHERE grp_date_id = l_grp_date_id;
1694: /* group does not exist in the database and needs to be added */
1695: pnp_debug_pkg.debug ('l_group_exists = N');
1696:
1697: ------------------------------------------------------------
1698: --call to insert into PN_VAR_GRP_DATES;
1699: ------------------------------------------------------------
1700: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1701: (X_ROWID => l_rowId,
1702: X_GRP_DATE_ID => l_grpDateId,
1734: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1735: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1736: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1737: /* Updte the end date and proration factor for the group */
1738: UPDATE pn_var_grp_dates_all
1739: SET grp_end_date = p_grp_end_date,
1740: proration_Factor = round(p_proration_factor,10),
1741: reptg_due_date = p_due_date --Bug # 5937888
1742: WHERE grp_date_id = l_grp_date_id;
1829: END LOOP;
1830:
1831: IF l_invoice_exists = 'N' THEN
1832: ------------------------------------------------------------
1833: --call to update PN_VAR_GRP_DATES;
1834: ------------------------------------------------------------
1835:
1836: UPDATE PN_VAR_GRP_DATES
1837: SET inv_start_date = p_inv_start_date,
1832: ------------------------------------------------------------
1833: --call to update PN_VAR_GRP_DATES;
1834: ------------------------------------------------------------
1835:
1836: UPDATE PN_VAR_GRP_DATES
1837: SET inv_start_date = p_inv_start_date,
1838: inv_end_date = p_inv_end_date,
1839: invoice_date = p_invoice_date,
1840: inv_schedule_date = p_schedule_date
1838: inv_end_date = p_inv_end_date,
1839: invoice_date = p_invoice_date,
1840: inv_schedule_date = p_schedule_date
1841: WHERE grp_date_id in (SELECT grp_date_id
1842: FROM pn_var_grp_dates
1843: WHERE grp_start_date <= p_inv_end_date
1844: AND grp_end_date >= p_inv_start_date
1845: AND period_id = p_period_id
1846: AND var_rent_id = p_var_rent_id)
1902: END LOOP;
1903:
1904: IF l_invoice_exists = 'N' THEN
1905: ------------------------------------------------------------
1906: --call to update PN_VAR_GRP_DATES;
1907: ------------------------------------------------------------
1908: UPDATE PN_VAR_GRP_DATES
1909: SET inv_start_date = p_inv_start_date,
1910: inv_end_date = p_inv_end_date,
1904: IF l_invoice_exists = 'N' THEN
1905: ------------------------------------------------------------
1906: --call to update PN_VAR_GRP_DATES;
1907: ------------------------------------------------------------
1908: UPDATE PN_VAR_GRP_DATES
1909: SET inv_start_date = p_inv_start_date,
1910: inv_end_date = p_inv_end_date,
1911: invoice_date = p_invoice_date,
1912: inv_schedule_date = p_schedule_date
1910: inv_end_date = p_inv_end_date,
1911: invoice_date = p_invoice_date,
1912: inv_schedule_date = p_schedule_date
1913: WHERE grp_date_id in (SELECT grp_date_id
1914: FROM pn_var_grp_dates
1915: WHERE grp_start_date <= p_inv_end_date
1916: AND grp_end_date >= p_inv_start_date
1917: AND period_id = p_period_id
1918: AND var_rent_id = p_var_rent_id)
1973: END LOOP;
1974:
1975: IF l_invoice_exists = 'N' THEN
1976: ------------------------------------------------------------
1977: --call to update PN_VAR_GRP_DATES;
1978: ------------------------------------------------------------
1979: UPDATE PN_VAR_GRP_DATES
1980: SET inv_start_date = p_inv_start_date,
1981: inv_end_date = p_inv_end_date,
1975: IF l_invoice_exists = 'N' THEN
1976: ------------------------------------------------------------
1977: --call to update PN_VAR_GRP_DATES;
1978: ------------------------------------------------------------
1979: UPDATE PN_VAR_GRP_DATES
1980: SET inv_start_date = p_inv_start_date,
1981: inv_end_date = p_inv_end_date,
1982: invoice_date = p_invoice_date,
1983: inv_schedule_date = p_schedule_date
1981: inv_end_date = p_inv_end_date,
1982: invoice_date = p_invoice_date,
1983: inv_schedule_date = p_schedule_date
1984: WHERE grp_date_id in (SELECT grp_date_id
1985: FROM pn_var_grp_dates
1986: WHERE grp_start_date <= p_inv_end_date
1987: AND grp_end_date >= p_inv_start_date
1988: AND period_id = p_period_id
1989: AND var_rent_id = p_var_rent_id)
2043: END LOOP;
2044:
2045: IF l_invoice_exists = 'N' THEN
2046: ------------------------------------------------------------
2047: --call to update PN_VAR_GRP_DATES;
2048: ------------------------------------------------------------
2049:
2050: UPDATE PN_VAR_GRP_DATES
2051: SET inv_start_date = p_inv_start_date,
2046: ------------------------------------------------------------
2047: --call to update PN_VAR_GRP_DATES;
2048: ------------------------------------------------------------
2049:
2050: UPDATE PN_VAR_GRP_DATES
2051: SET inv_start_date = p_inv_start_date,
2052: inv_end_date = p_inv_end_date,
2053: invoice_date = p_invoice_date,
2054: inv_schedule_date = p_schedule_date
2052: inv_end_date = p_inv_end_date,
2053: invoice_date = p_invoice_date,
2054: inv_schedule_date = p_schedule_date
2055: WHERE grp_date_id in (SELECT grp_date_id
2056: FROM pn_var_grp_dates
2057: WHERE grp_start_date <= p_inv_end_date
2058: AND grp_end_date >= p_inv_start_date
2059: AND period_id = p_period_id
2060: AND var_rent_id = p_var_rent_id)
2080: /*=============================================================================+
2081: -- NAME : CREATE_VAR_RENT_PERIODS_NOCAL
2082: -- DESCRIPTION : Create variable rent periods recORd IN PN_VAR_PERIODS AND
2083: -- corresponding group date/invoice date records in the
2084: -- PN_VAR_GRP_DATES table for a variable rent recORd when a
2085: -- GL calendar is not specified in the VR agreement.
2086: -- PURPOSE :
2087: -- INVOKED FROM :
2088: -- ARGUMENTS : IN:
2094: --
2095: -- REFERENCE : PN_COMMON.debug()
2096: -- NOTES : Create variable rent periods recORd IN PN_VAR_PERIODS AND
2097: -- corresponding group date/invoice date records in the
2098: -- PN_VAR_GRP_DATES table for a variable rent record for
2099: -- yearly VR periods depending on the year start date.
2100: -- Calls INSERT_PERIODS_ROW AND INSERT_GRP_DATE_ROW procedures
2101: -- HISTORY :
2102: -- 02-SEP-02 kkhegde o Created
2198:
2199: /* Fetches the group id which starts on a specified date for a VR agreement */
2200: CURSOR group_cur (p_start_date DATE) IS
2201: SELECT grp_end_date, grp_date_id
2202: FROM pn_var_grp_dates_all
2203: WHERE var_rent_id = p_var_rent_id
2204: AND grp_start_date = p_start_date;
2205:
2206: /* Fetches the period id which starts on a specified date for a VR agreement */
2605: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2606: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2607: /* Updte the end date and proration factor for the group */
2608:
2609: UPDATE pn_var_grp_dates_all
2610: SET grp_end_date = VR_grp_dates(j).l_grp_end_date,
2611: proration_Factor = round(l_proration_Factor,10),
2612: reptg_due_date = VR_grp_dates(j).l_reptg_due_date --Bug # 5937888
2613: WHERE grp_date_id = l_grp_date_id;
2635: | DELETE_VAR_RENT_PERIODS
2636: |
2637: | DESCRIPTION
2638: | Delete variable rent periods record in PN_VAR_PERIODS, corresponding
2639: | group date/invoice date records in the PN_VAR_GRP_DATES table,
2640: | corresponding line items in the PN_VAR_LINES table
2641: | for a variable rent record
2642: |
2643: | SCOPE - PUBLIC
2653: | RETURNS : None
2654: |
2655: | NOTES : Delete variable rent periods record in PN_VAR_PERIODS,
2656: | corresponding group date/invoice date records in the
2657: | PN_VAR_GRP_DATES table, corresponding line items in the
2658: | PN_VAR_LINES table for a variable rent record
2659: | Calls DELETE_PERIODS_ROW, DELETE_GRP_DATE_ROW,
2660: | DELETE_VAR_RENT_LINES procedures
2661: |
2805: | UPDATE_VAR_RENT_PERIODS
2806: |
2807: | DESCRIPTION
2808: | Update variable rent periods record in PN_VAR_PERIODS, corresponding
2809: | group date/invoice date records in the PN_VAR_GRP_DATES table
2810: | for a variable rent record contraction
2811: |
2812: | SCOPE - PUBLIC
2813: |
2821: |
2822: | RETURNS : None
2823: |
2824: | NOTES : Update variable rent periods record in PN_VAR_PERIODS, corresponding
2825: | group date/invoice date records in the PN_VAR_GRP_DATES table
2826: | for a variable rent record contraction
2827: |
2828: | MODIFICATION HISTORY
2829: |
2884: -- updated with the new proration factor after lease contraction
2885:
2886: SELECT grp.grp_start_date
2887: INTO p_grp_start_date
2888: FROM pn_var_grp_dates_all grp
2889: WHERE grp.var_rent_id = p_var_rent_id
2890: AND grp.grp_start_date <= p_term_date
2891: AND grp.grp_end_date >= p_term_date
2892: AND rownum < 2;
2929: END IF;
2930:
2931: END IF;
2932:
2933: --call to update PN_VAR_GRP_DATES;
2934:
2935: UPDATE pn_var_grp_dates_all
2936: SET grp_end_date = p_term_date,
2937: proration_factor = round(p_proration_factor,10),
2931: END IF;
2932:
2933: --call to update PN_VAR_GRP_DATES;
2934:
2935: UPDATE pn_var_grp_dates_all
2936: SET grp_end_date = p_term_date,
2937: proration_factor = round(p_proration_factor,10),
2938: last_update_date = p_last_update_date,
2939: last_updated_by = p_last_updated_by,
4287: |
4288: | MODIFICATION HISTORY
4289: |
4290: | 26-OCT-2001 Daniel Thota o Created
4291: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4292: +===========================================================================*/
4293:
4294: FUNCTION find_reporting_periods (p_period_id NUMBER) RETURN NUMBER IS
4295:
4300: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (+)');
4301:
4302: SELECT count(GRP_START_DATE)
4303: INTO l_reporting_periods
4304: FROM pn_var_grp_dates_all
4305: WHERE period_id = p_period_id;
4306:
4307: RETURN l_reporting_periods;
4308:
4507: | MODIFICATION HISTORY
4508: |
4509: | 16-NOV-01 Daniel Thota o Created
4510: | 21-NOV-03 Daniel Thota o Added forecasted_exp_code to SELECT -- Fix for bug # 2435455
4511: | 14-JUN-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4512: +===========================================================================*/
4513:
4514: FUNCTION get_grp_date_info (p_var_rent_id IN NUMBER,
4515: p_period_id IN NUMBER,
4533: invoice_date,
4534: inv_schedule_date,
4535: forecasted_exp_code
4536: INTO l_grp_date_info
4537: FROM pn_var_grp_dates_all
4538: WHERE var_rent_id = p_var_rent_id
4539: AND period_id = p_period_id
4540: AND grp_start_date <= p_start_date
4541: AND grp_start_date <= p_end_date
4580: |
4581: | MODIFICATION HISTORY
4582: |
4583: | 27-DEC-01 Daniel Thota o Created
4584: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4585: +===========================================================================*/
4586:
4587: FUNCTION get_proration_factor(p_var_rent_id IN NUMBER)
4588: RETURN PRORATION_FACTOR_REC IS
4603: (g2.grp_end_date-g2.grp_start_date)+1 last_group_pro_days,
4604: g2.proration_factor last_group_gl_days
4605: INTO l_proration_factor
4606: FROM pn_var_periods_ALL p1, pn_var_periods_ALL p2,
4607: pn_var_grp_dates_ALL g1, pn_var_grp_dates_ALL g2
4608: WHERE p1.var_rent_id = p2.var_rent_id
4609: AND p1.var_rent_id = p_var_rent_id
4610: AND g1.var_rent_id = g2.var_rent_id
4611: AND g1.var_rent_id = p_var_rent_id
4615: AND p2.period_id = (SELECT max(period_id)
4616: FROM pn_var_periods_ALL
4617: WHERE var_rent_id = p_var_rent_id)
4618: AND g1.grp_date_id = (SELECT min(grp_date_id)
4619: FROM pn_var_grp_dates
4620: WHERE var_rent_id = p_var_rent_id)
4621: AND g2.grp_date_id = (SELECT max(grp_date_id)
4622: FROM pn_var_grp_dates
4623: WHERE var_rent_id = p_var_rent_id);
4618: AND g1.grp_date_id = (SELECT min(grp_date_id)
4619: FROM pn_var_grp_dates
4620: WHERE var_rent_id = p_var_rent_id)
4621: AND g2.grp_date_id = (SELECT max(grp_date_id)
4622: FROM pn_var_grp_dates
4623: WHERE var_rent_id = p_var_rent_id);
4624:
4625: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_PRORATION_FACTOR (-)');
4626:
4770: | called FROM with p_block AS PERIODS_INV_BLK
4771: | Added brackets so that the join condition
4772: | IS evaluated correctly.
4773: | Bug 4026980
4774: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4775: | 12-Mar-07 Shabda o Bug 5911819 - Volume records are considered exported
4776: | when actual or variance exp_code = y (Not forecasted exp_code)
4777: +===========================================================================*/
4778:
4792: SELECT 1
4793: INTO l_exported
4794: FROM dual
4795: WHERE EXISTS ( SELECT grp_date_id
4796: FROM pn_var_grp_dates_all
4797: WHERE var_rent_id = p_id
4798: AND (actual_exp_code = 'Y' OR
4799: forecasted_exp_code = 'Y')
4800: );
4804: SELECT 1
4805: INTO l_exported
4806: FROM dual
4807: WHERE EXISTS ( SELECT grp_date_id
4808: FROM pn_var_grp_dates_all
4809: WHERE period_id = p_id
4810: AND (actual_exp_code = 'Y' OR
4811: forecasted_exp_code = 'Y')
4812: );
4816: SELECT 1
4817: INTO l_exported
4818: FROM dual
4819: WHERE EXISTS ( SELECT grp_date_id
4820: FROM pn_var_grp_dates_all
4821: WHERE period_id IN (SELECT period_id
4822: FROM pn_var_lines_ALL
4823: WHERE line_item_id = p_id)
4824: AND (actual_exp_code = 'Y' OR
4830: SELECT 1
4831: INTO l_exported
4832: FROM dual
4833: WHERE EXISTS ( SELECT grp_date_id
4834: FROM pn_var_grp_dates_all
4835: WHERE period_id IN (SELECT period_id
4836: FROM pn_var_lines_ALL
4837: WHERE line_default_id = p_id)
4838: AND (actual_exp_code = 'Y' OR
4843: SELECT 1
4844: INTO l_exported
4845: FROM dual
4846: WHERE EXISTS ( SELECT grp_date_id
4847: FROM pn_var_grp_dates_all a,
4848: pn_var_periods_all b,
4849: pn_var_lines_all c
4850: WHERE a.period_id = b.period_id
4851: AND b.period_id = c.period_id
4977: | 21-Jun-02 Ashish Kumar Fix BUG#2096829 and BUG#2096810 Change the Where clause of the
4978: | Completed and Reconciled Select stmts
4979: | 11-Jul-02 Ashish Kumar Fix for BUG#2452276 In the Reconcile query change the
4980: | Forcasted_exp_code to Variance_exp_code
4981: | 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
4982: +===========================================================================*/
4983:
4984: FUNCTION find_status (p_period_id NUMBER) RETURN VARCHAR2 IS
4985:
4993: INTO l_status
4994: FROM dual
4995: WHERE NOT EXISTS
4996: (SELECT grp_date_id
4997: FROM pn_var_grp_dates_all
4998: WHERE period_id = p_period_id
4999: AND ((actual_exp_code = 'N' AND FORECASTED_exp_code ='N' AND variance_exp_code = 'N') OR
5000: (actual_exp_code = 'N' AND FORECASTED_exp_code ='Y' AND variance_exp_code = 'Y')
5001: )
5021: SELECT 'RECONCILED'
5022: INTO l_status
5023: FROM dual
5024: WHERE NOT EXISTS(SELECT grp_date_id
5025: FROM pn_var_grp_dates_all
5026: WHERE period_id = p_period_id
5027: AND variance_exp_code = 'N')
5028: AND NOT EXISTS(SELECT vol_hist_id
5029: FROM pn_var_vol_hist_all
5282: FROM pn_var_vol_hist_all vh
5283: WHERE actual_exp_code = 'N'
5284: AND period_id = p_period_id
5285: AND EXISTS (SELECT grp.grp_date_id
5286: FROM pn_var_grp_dates_all grp
5287: WHERE actual_exp_code = 'Y'
5288: AND grp.grp_date_id = vh.grp_date_id));
5289:
5290: ELSIF p_invoice_on = 'FORECASTED' THEN
5296: FROM pn_var_vol_hist_all vh
5297: WHERE forecasted_exp_code = 'N'
5298: AND period_id = p_period_id
5299: AND EXISTS (SELECT grp.grp_date_id
5300: FROM pn_var_grp_dates_all grp
5301: WHERE forecasted_exp_code = 'Y'
5302: AND grp.grp_date_id = vh.grp_date_id));
5303:
5304: END IF;
6554: SELECT start_date, end_date ,proration_factor
6555: FROM pn_var_periods_all
6556: WHERE period_id = p_period_id;
6557:
6558: CURSOR pn_var_grp_dates_cur
6559: IS
6560: SELECT *
6561: FROM pn_var_grp_dates_all
6562: WHERE period_id = p_period_id
6557:
6558: CURSOR pn_var_grp_dates_cur
6559: IS
6560: SELECT *
6561: FROM pn_var_grp_dates_all
6562: WHERE period_id = p_period_id
6563: ORDER BY grp_start_date;
6564:
6565: v_inv_dates_tbl group_dates_tbl;
6803: l_inv_counter := l_inv_counter + 1;
6804: END LOOP;
6805: END IF; -- end if of gl_calendar = Y
6806:
6807: FOR pn_var_grp_dates_rec IN pn_var_grp_dates_cur
6808: LOOP
6809: FOR i IN vr_grp_dates.FIRST .. vr_grp_dates.LAST
6810: LOOP
6811: IF (vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date AND
6807: FOR pn_var_grp_dates_rec IN pn_var_grp_dates_cur
6808: LOOP
6809: FOR i IN vr_grp_dates.FIRST .. vr_grp_dates.LAST
6810: LOOP
6811: IF (vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date AND
6812: vr_grp_dates(i).l_grp_end_date = pn_var_grp_dates_rec.grp_end_date )
6813: THEN
6814: --put_log('Same groups found');
6815: vr_grp_dates(i).l_rec_found := 'X';
6808: LOOP
6809: FOR i IN vr_grp_dates.FIRST .. vr_grp_dates.LAST
6810: LOOP
6811: IF (vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date AND
6812: vr_grp_dates(i).l_grp_end_date = pn_var_grp_dates_rec.grp_end_date )
6813: THEN
6814: --put_log('Same groups found');
6815: vr_grp_dates(i).l_rec_found := 'X';
6816: END IF;
6814: --put_log('Same groups found');
6815: vr_grp_dates(i).l_rec_found := 'X';
6816: END IF;
6817:
6818: IF vr_grp_dates(i).l_grp_end_date <> pn_var_grp_dates_rec.grp_end_date AND
6819: vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date THEN
6820: --put_log('Updating end dates');
6821: vr_grp_dates(i).l_rec_found := 'X';
6822: l_new_st_date := nvl(l_new_st_date,pn_var_grp_dates_rec.grp_start_date);
6815: vr_grp_dates(i).l_rec_found := 'X';
6816: END IF;
6817:
6818: IF vr_grp_dates(i).l_grp_end_date <> pn_var_grp_dates_rec.grp_end_date AND
6819: vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date THEN
6820: --put_log('Updating end dates');
6821: vr_grp_dates(i).l_rec_found := 'X';
6822: l_new_st_date := nvl(l_new_st_date,pn_var_grp_dates_rec.grp_start_date);
6823: l_new_end_date := vr_grp_dates(i).l_grp_end_date;
6818: IF vr_grp_dates(i).l_grp_end_date <> pn_var_grp_dates_rec.grp_end_date AND
6819: vr_grp_dates(i).l_grp_start_date = pn_var_grp_dates_rec.grp_start_date THEN
6820: --put_log('Updating end dates');
6821: vr_grp_dates(i).l_rec_found := 'X';
6822: l_new_st_date := nvl(l_new_st_date,pn_var_grp_dates_rec.grp_start_date);
6823: l_new_end_date := vr_grp_dates(i).l_grp_end_date;
6824:
6825: IF p_pn_var_rent_dates_rec.reptg_day_of_month IS NOT NULL THEN
6826: l_rptg_date :=(ADD_MONTHS(FIRST_DAY(vr_grp_dates(i).l_grp_end_date),1)-1)+
6856: put_log(' Group Date While Updating Group Dates:'||vr_grp_dates(i).l_group_date);
6857: --vr_grp_dates(i).l_proration_factor := 1;
6858: END IF;
6859:
6860: UPDATE pn_var_grp_dates_all
6861: SET grp_end_date = vr_grp_dates(i).l_grp_end_date ,
6862: proration_factor = round(vr_grp_dates(i).l_proration_factor,10), --Chris.T. 20MAY2004
6863: last_update_date = sysdate,
6864: last_updated_by = FND_GLOBAL.USER_ID,
6862: proration_factor = round(vr_grp_dates(i).l_proration_factor,10), --Chris.T. 20MAY2004
6863: last_update_date = sysdate,
6864: last_updated_by = FND_GLOBAL.USER_ID,
6865: last_update_login = FND_GLOBAL.LOGIN_ID
6866: WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;
6867:
6868: /*UPDATE pn_var_transactions_all
6869: SET bkpt_end_date = vr_grp_dates(i).l_grp_end_date,
6870: last_update_date = SYSDATE,
6869: SET bkpt_end_date = vr_grp_dates(i).l_grp_end_date,
6870: last_update_date = SYSDATE,
6871: last_updated_by = FND_GLOBAL.USER_ID,
6872: last_update_login = FND_GLOBAL.LOGIN_ID
6873: WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;*/
6874:
6875: END IF; -- end if of end date not being equal
6876: END LOOP; -- end loop for groups table.
6877: END LOOP; -- end loop for pn_var_grp_dates_rec
6873: WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;*/
6874:
6875: END IF; -- end if of end date not being equal
6876: END LOOP; -- end loop for groups table.
6877: END LOOP; -- end loop for pn_var_grp_dates_rec
6878:
6879: FOR i in vr_grp_dates.FIRST .. vr_grp_dates.LAST
6880: LOOP
6881: IF vr_grp_dates(i).l_rec_found is NULL THEN
7877: WHERE var_rent_id = p_var_rent_id);
7878:
7879: DELETE pn_var_rent_summ_all
7880: WHERE grp_date_id IN (SELECT grp_date_id
7881: FROM pn_var_grp_dates_all
7882: WHERE var_rent_id = p_var_rent_id
7883: AND grp_start_date > p_new_termn_date);
7884:
7885: DELETE pn_var_constraints_all
8120: ELSE
8121:
8122: pnp_debug_pkg.debug ('deleting group date and periods ...');
8123: /* Delete group dates and periods for which there exists no invoice */
8124: DELETE pn_var_grp_dates_all
8125: WHERE period_id = period_rec.period_id;
8126:
8127: DELETE pn_var_periods_all
8128: WHERE period_id = period_rec.period_id;
8300: inv_end_date,
8301: group_date,
8302: invoice_date,
8303: period_id
8304: FROM pn_var_grp_dates_all
8305: WHERE var_rent_id = p_var_rent_id
8306: AND p_new_termn_date BETWEEN grp_start_date AND grp_end_date;
8307:
8308: /* Get the details of breakpoint header default */
8337:
8338: /* Get the max invoice end date corresponding to the new termination date*/
8339: CURSOR inv_end_dt_cur(p_var_rent_id IN NUMBER) IS
8340: SELECT max(inv_end_date) inv_end_date
8341: FROM pn_var_grp_dates_all
8342: WHERE var_rent_id = p_var_rent_id
8343: AND inv_end_date <= p_new_termn_date;
8344:
8345: l_reptg_day_of_month NUMBER := 0;
8407: /*l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date( inv_start_date => l_invoice_date
8408: ,vr_id => p_var_rent_id
8409: ,p_period_id => l_period_id);*/
8410:
8411: UPDATE pn_var_grp_dates_all
8412: SET grp_end_date = p_new_termn_date,
8413: proration_factor = round(l_proration_factor,10),
8414: reptg_due_date = l_due_date
8415: WHERE var_rent_id = p_var_rent_id
8427: l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date( inv_start_date => l_invoice_date
8428: ,vr_id => p_var_rent_id
8429: ,p_period_id => l_period_id);
8430:
8431: UPDATE pn_var_grp_dates_all
8432: SET inv_end_date = p_new_termn_date,
8433: inv_schedule_date = l_inv_sch_date
8434: WHERE var_rent_id = p_var_rent_id
8435: AND p_new_termn_date BETWEEN inv_start_date AND inv_end_date;
8771: WHERE var_rent_id = p_var_rent_id);
8772:
8773: DELETE pn_var_rent_summ_all
8774: WHERE grp_date_id IN (SELECT grp_date_id
8775: FROM pn_var_grp_dates_all
8776: WHERE var_rent_id = p_var_rent_id);
8777:
8778: DELETE pn_var_constraints_all
8779: WHERE period_id IN (SELECT period_id
8806:
8807: DELETE pn_var_rent_inv_all
8808: WHERE var_rent_id = p_var_rent_id;
8809:
8810: DELETE pn_var_grp_dates_all
8811: WHERE var_rent_id = p_var_rent_id;
8812:
8813: DELETE pn_var_periods_all
8814: WHERE var_rent_id = p_var_rent_id;
8904:
8905: /* Get the max invoice end date corresponding to the new termination date*/
8906: CURSOR inv_end_dt_cur(p_var_rent_id IN NUMBER) IS
8907: SELECT max(inv_end_date) inv_end_date
8908: FROM pn_var_grp_dates_all
8909: WHERE var_rent_id = p_var_rent_id
8910: AND inv_end_date <= p_new_termn_date;
8911:
8912: BEGIN