eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PN_SCHEDULES_ITEMS dependencies on PN_PAYMENT_SCHEDULES_ALL

Line 464: pn_payment_schedules_all pps,

460: --Fix for bug#14143342
461: CURSOR leave_alone_amt(p_lease_id Number) IS
462: SELECT NVL(SUM(ppi.actual_amount),0)
463: FROM pn_payment_items_all ppi,
464: pn_payment_schedules_all pps,
465: pn_payment_terms_all ppt
466: WHERE ppt.payment_term_id = ppi.payment_term_id
467: AND ppt.lease_id = p_lease_id
468: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'

Line 495: pn_payment_schedules_all pps,

491: ppi.grouping_rule_id,
492: sum(ppi.actual_amount) actual_amount,
493: sum(nvl(ppi.export_currency_amount,0)) export_currency_amount
494: FROM pn_payment_items_all ppi,
495: pn_payment_schedules_all pps,
496: pn_payment_terms_all ppt
497: WHERE ppt.payment_term_id = ppi.payment_term_id
498: AND ppt.lease_id = p_lease_id
499: AND ppt.normalize = 'Y'

Line 505: FROM pn_payment_schedules_all ppsa

501: AND pps.payment_schedule_id = ppi.payment_schedule_id
502: AND ppi.actual_amount <> 0
503: AND first_day(pps.schedule_date) < first_day(l_commencement_date)
504: AND EXISTS (SELECT 'x'
505: FROM pn_payment_schedules_all ppsa
506: WHERE ppsa.lease_id = p_lease_id
507: AND ppsa.schedule_date = pps.schedule_date
508: AND first_day(pps.schedule_date) < first_day(l_commencement_date)
509: AND ppsa.payment_status_lookup_code = 'APPROVED')

Line 570: pn_payment_schedules_all b,

566: --Get the last Payment_item_id, where the Approved amount will be reversed
567: CURSOR get_payment_item(p_lease_id Number, p_payment_term_id Number) IS
568: SELECT MAX(c.payment_item_id)
569: FROM pn_payment_terms_all a,
570: pn_payment_schedules_all b,
571: pn_payment_items_all c
572: WHERE a.lease_id = p_lease_id
573: AND a.payment_term_id = p_payment_term_id
574: AND a.normalize = 'Y'

Line 864: FROM pn_payment_schedules_all pps,

860:
861: CURSOR lst_sch_dt_cur (p_term_id NUMBER)
862: IS
863: SELECT MAX(pps.schedule_date), COUNT(pps.schedule_date)
864: FROM pn_payment_schedules_all pps,
865: pn_payment_items_all ppi
866: WHERE ppi.payment_term_id = p_term_id
867: AND pps.payment_schedule_id = ppi.payment_schedule_id
868: AND ppi.actual_amount <> 0

Line 876: FROM pn_payment_schedules_all pps

872: p_start_date DATE)
873: IS
874:
875: SELECT First_Day(MIN(pps.schedule_date))
876: FROM pn_payment_schedules_all pps
877: WHERE pps.lease_id = p_lease_id
878: AND First_Day(pps.schedule_date) >= First_Day(p_start_date)
879: AND TO_CHAR(pps.schedule_date,'DD') = p_sch_day
880: AND pps.payment_status_lookup_code = 'DRAFT';

Line 887: FROM pn_payment_schedules_all pps

883: p_start_date DATE)
884: IS
885:
886: SELECT ADD_MONTHS(First_Day(MAX(pps.schedule_date)) , 1)
887: FROM pn_payment_schedules_all pps
888: WHERE pps.lease_id = p_lease_id
889: AND First_Day(pps.schedule_date) >= First_Day(p_start_date)
890: AND TO_CHAR(pps.schedule_date,'DD') = p_sch_day
891: AND pps.payment_status_lookup_code = 'APPROVED';

Line 1692: FROM pn_payment_schedules_all

1688: /*--- Bug#7149537---*/
1689: CURSOR check_sch IS
1690: SELECT payment_schedule_id,
1691: payment_status_lookup_code
1692: FROM pn_payment_schedules_all
1693: WHERE schedule_date = p_sch_dt
1694: AND lease_id = p_lease_id
1695: ORDER BY payment_status_lookup_code DESC;
1696:

Line 1706: pn_payment_schedules_all sched

1702:
1703: CURSOR sched_exists(p_payment_term_id NUMBER,p_sch_dt DATE) IS
1704: SELECT sched.payment_schedule_id, payment_status_lookup_code
1705: FROM pn_payment_items_all item,
1706: pn_payment_schedules_all sched
1707: WHERE item.payment_term_id = p_payment_term_id
1708: AND item.payment_schedule_id = sched.payment_schedule_id
1709: AND sched.schedule_date = p_sch_dt;
1710:

Line 1732: INSERT INTO pn_payment_schedules_all

1728: FROM DUAL;
1729:
1730: l_pymnt_st_lkp_cd := 'DRAFT';
1731:
1732: INSERT INTO pn_payment_schedules_all
1733:
1734: (payment_schedule_id,
1735: last_update_date,
1736: last_updated_by,

Line 1798: FROM pn_payment_schedules_all

1794:
1795: SELECT payment_schedule_id,
1796: payment_status_lookup_code
1797: INTO l_sch_id, l_pymnt_st_lkp_cd
1798: FROM pn_payment_schedules_all
1799: WHERE lease_id = p_lease_id
1800: AND schedule_date = l_schd_date
1801: AND payment_status_lookup_code = l_pymnt_st_lkp_cd
1802: AND ROWNUM = 1;

Line 1810: INSERT INTO pn_payment_schedules_all(

1806: EXCEPTION WHEN NO_DATA_FOUND THEN
1807: SELECT pn_payment_schedules_s.NEXTVAL INTO l_sch_id
1808: FROM DUAL;
1809: pnp_debug_pkg.log('create_schedule: l_sch_id ' || l_sch_id);
1810: INSERT INTO pn_payment_schedules_all(
1811: payment_schedule_id,
1812: last_update_date,
1813: last_updated_by,
1814: creation_date,

Line 2125: pn_payment_schedules_all pps

2121: -- schedules exist for the same period
2122: CURSOR get_drf_sch_date(p_term_id NUMBER) IS
2123: SELECT distinct schedule_date
2124: FROM pn_payment_items_all ppi,
2125: pn_payment_schedules_all pps
2126: WHERE ppi.payment_term_id = p_term_id
2127: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2128: AND pps.payment_schedule_id = ppi.payment_schedule_id
2129: AND pps.payment_status_lookup_code <> 'DRAFT'

Line 2137: ,pn_payment_schedules_all pps

2133:
2134: CURSOR get_drf_sch(l_norm_sch_dt DATE) IS
2135: select distinct ppi.payment_schedule_id
2136: from pn_payment_items_all ppi
2137: ,pn_payment_schedules_all pps
2138: where exists
2139: (select 1
2140: from pn_payment_items_all ppi , pn_payment_schedules_all pps
2141: where ppi.payment_term_id = p_term_id

Line 2140: from pn_payment_items_all ppi , pn_payment_schedules_all pps

2136: from pn_payment_items_all ppi
2137: ,pn_payment_schedules_all pps
2138: where exists
2139: (select 1
2140: from pn_payment_items_all ppi , pn_payment_schedules_all pps
2141: where ppi.payment_term_id = p_term_id
2142: and pps.payment_schedule_id = ppi.payment_schedule_id
2143: and payment_status_lookup_code <> 'DRAFT'
2144: and due_date = l_norm_sch_dt

Line 2205: pn_payment_schedules_all pps

2201:
2202: Select NVL(SUM(ppi.actual_amount),0)
2203: into l_term_amt
2204: FROM pn_payment_items_all ppi,
2205: pn_payment_schedules_all pps
2206: WHERE ppi.payment_term_id = p_term_id
2207: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2208: AND pps.payment_schedule_id = ppi.payment_schedule_id
2209: AND pps.payment_status_lookup_code <> 'DRAFT'

Line 2225: pn_payment_schedules_all pps

2221:
2222: SELECT NVL(SUM(ppi.actual_amount),0)
2223: INTO l_app_amt
2224: FROM pn_payment_items_all ppi,
2225: pn_payment_schedules_all pps
2226: WHERE ppi.payment_term_id = p_term_id
2227: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2228: AND pps.payment_schedule_id = ppi.payment_schedule_id
2229: AND pps.payment_status_lookup_code <> 'DRAFT'

Line 2246: pn_payment_schedules_all pps

2242: -- Draft Schedules should not be deleted before ACD
2243: SELECT NVL(SUM(ppi.actual_amount),0)
2244: INTO l_dft_amt
2245: FROM pn_payment_items_all ppi,
2246: pn_payment_schedules_all pps
2247: WHERE ppi.payment_term_id = p_term_id
2248: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2249: AND pps.payment_schedule_id = ppi.payment_schedule_id
2250: AND pps.payment_status_lookup_code = 'DRAFT'

Line 2298: pn_payment_schedules_all pps

2294:
2295: SELECT NVL(SUM(ppi.actual_amount),0)
2296: INTO l_la_amt
2297: FROM pn_payment_items_all ppi,
2298: pn_payment_schedules_all pps
2299: WHERE ppi.payment_term_id = p_term_id
2300: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2301: AND pps.payment_schedule_id = ppi.payment_schedule_id
2302: AND pps.payment_status_lookup_code <> 'DRAFT'; --Bug#6825797 'APPROVED';

Line 2326: pn_payment_schedules_all pps

2322:
2323: SELECT LEAST(p_norm_end_dt, NVL(LAST_DAY(MAX(pps.schedule_date)), p_norm_end_dt))
2324: INTO l_norm_end_dt
2325: FROM pn_payment_items_all ppi,
2326: pn_payment_schedules_all pps
2327: WHERE ppi.payment_term_id = p_term_id
2328: AND pps.payment_schedule_id = ppi.payment_schedule_id
2329: AND pps.payment_status_lookup_code = 'DRAFT';
2330:

Line 2336: pn_payment_schedules_all pps

2332:
2333: SELECT COUNT(*)
2334: INTO l_app_sch
2335: FROM pn_payment_items_all ppi,
2336: pn_payment_schedules_all pps
2337: WHERE ppi.payment_term_id = p_term_id
2338: AND pps.payment_schedule_id = ppi.payment_schedule_id
2339: AND pps.payment_status_lookup_code = 'APPROVED'
2340: AND ppi.payment_item_type_lookup_code = 'CASH'

Line 2344: FROM pn_payment_schedules_all ppsi,

2340: AND ppi.payment_item_type_lookup_code = 'CASH'
2341: AND pps.schedule_date between First_Day(p_norm_str_dt) and LAST_DAY(l_norm_end_dt)
2342: AND NOT EXISTS (
2343: SELECT 1
2344: FROM pn_payment_schedules_all ppsi,
2345: pn_payment_items_all ppii
2346: WHERE ppsi.schedule_date = pps.schedule_date
2347: AND ppii.payment_term_id = ppi.payment_term_id
2348: AND ppsi.payment_schedule_id = ppii.payment_schedule_id

Line 2444: pn_payment_schedules_all pps

2440: --Fix for bug#14143342
2441: DELETE pn_payment_items_all ppi
2442: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
2443: FROM pn_payment_items_all ppi1,
2444: pn_payment_schedules_all pps
2445: WHERE ppi1.payment_term_id = p_term_id
2446: AND ppi1.payment_item_type_lookup_code = 'CASH'
2447: AND pps.payment_schedule_id = ppi1.payment_schedule_id
2448: AND pps.payment_status_lookup_code = 'DRAFT'

Line 2542: pn_payment_schedules_all pps

2538:
2539: SELECT NVL(sum(actual_amount),0) /* 9457938 */
2540: into l_act_amt
2541: FROM pn_payment_items_all ppi,
2542: pn_payment_schedules_all pps
2543: WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
2544: AND pps.payment_schedule_id = ppi.payment_schedule_id
2545: AND ppi.payment_term_id = p_term_id
2546: AND pps.payment_status_lookup_code <> 'DRAFT'

Line 2661: pn_payment_schedules_all pps

2657:
2658: SELECT NVL(sum(actual_amount),0)
2659: into l_act_amt
2660: FROM pn_payment_items_all ppi,
2661: pn_payment_schedules_all pps
2662: WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
2663: AND pps.payment_schedule_id = ppi.payment_schedule_id
2664: AND ppi.payment_term_id = p_term_id
2665: AND pps.payment_status_lookup_code <> 'DRAFT'

Line 2705: FROM pn_payment_schedules_all pps,

2701: ppi.last_update_date = SYSDATE,
2702: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2703: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2704: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
2705: FROM pn_payment_schedules_all pps,
2706: pn_payment_items_all ppi1
2707: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2708: AND pps.payment_status_lookup_code = 'DRAFT'
2709: AND ppi1.payment_term_id = p_term_id)

Line 2736: FROM pn_payment_schedules_all pps,

2732: ppi.last_update_date = SYSDATE,
2733: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2734: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2735: WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
2736: FROM pn_payment_schedules_all pps,
2737: pn_payment_items_all ppi1
2738: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2739: AND pps.payment_status_lookup_code = 'DRAFT'
2740: AND ppi1.payment_term_id = p_term_id

Line 2770: FROM pn_payment_schedules_all pps,

2766: ppi.last_update_date = SYSDATE,
2767: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2768: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2769: WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
2770: FROM pn_payment_schedules_all pps,
2771: pn_payment_items_all ppi1
2772: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2773: AND pps.payment_status_lookup_code = 'DRAFT'
2774: AND ppi1.payment_term_id = p_term_id

Line 3473: pn_payment_schedules_all pps

3469: into l_cash_act_amt_appr,
3470: l_cash_est_amt_appr,
3471: l_cash_exp_amt_appr
3472: from pn_payment_items_all ppi,
3473: pn_payment_schedules_all pps
3474: where pps.lease_id = g_lease_id
3475: and pps.payment_status_lookup_code = 'APPROVED'
3476: and pps.payment_schedule_id = ppi.payment_schedule_id
3477: and ppi.payment_term_id = p_term_id

Line 3492: pn_payment_schedules_all pps

3488: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
3489: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
3490: WHERE payment_item_id = (SELECT ppi1.payment_item_id
3491: FROM pn_payment_items_all ppi1,
3492: pn_payment_schedules_all pps
3493: WHERE ppi1.payment_term_id = p_term_id
3494: AND ppi1.payment_item_type_lookup_code = 'CASH'
3495: AND pps.payment_status_lookup_code in ('DRAFT','ON_HOLD') --Fix for bug#12427309
3496: AND pps.payment_schedule_id = ppi1.payment_schedule_id

Line 3557: FROM pn_payment_schedules_all pps,

3553: AND frequency_code <> 'OT';
3554:
3555: CURSOR get_last_schedule (p_payment_term_id NUMBER) IS
3556: SELECT MAX(schedule_date)
3557: FROM pn_payment_schedules_all pps,
3558: pn_payment_items_all ppi
3559: WHERE ppi.payment_term_id = p_payment_term_id
3560: AND ppi.payment_item_type_lookup_code = 'CASH'
3561: AND ppi.actual_amount <> 0

Line 4096: FROM pn_payment_schedules_all

4092: pnp_debug_pkg.log('CON - MAIN - Deleting Payment Items');
4093:
4094: DELETE pn_payment_items_all
4095: WHERE payment_schedule_id IN (SELECT payment_schedule_id
4096: FROM pn_payment_schedules_all
4097: WHERE lease_id = p_lease_id
4098: AND schedule_date > p_new_lea_term_dt
4099: AND payment_status_lookup_code = 'DRAFT')
4100: AND payment_term_id NOT IN (SELECT payment_term_id

Line 4110: DELETE pn_payment_schedules_all psch

4106: and schedule date is greater than lease termination date. */
4107:
4108: pnp_debug_pkg.log('CON - MAIN - Deleting Payment Schedules');
4109:
4110: DELETE pn_payment_schedules_all psch
4111: WHERE lease_id = p_lease_id
4112: AND schedule_date > p_new_lea_term_dt
4113: AND payment_status_lookup_code = 'DRAFT'
4114: AND NOT EXISTS (SELECT null

Line 4297: pn_payment_schedules_all pps

4293:
4294: CURSOR get_drf_sch_date(p_payment_term_id NUMBER) IS
4295: SELECT distinct schedule_date
4296: FROM pn_payment_items_all ppi,
4297: pn_payment_schedules_all pps
4298: WHERE ppi.payment_term_id = p_payment_term_id
4299: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
4300: AND pps.payment_schedule_id = ppi.payment_schedule_id
4301: AND pps.payment_status_lookup_code <> 'DRAFT'

Line 4395: FROM pn_payment_schedules_all

4391: pnp_debug_pkg.log('+++++++++++Deleting All Items for term:'||l_item_end_dt_tbl(i).term_id||
4392: ' with schedule_date > ' ||l_item_end_dt_tbl(i).item_end_dt);
4393: DELETE pn_payment_items_all
4394: WHERE payment_schedule_id IN (SELECT payment_schedule_id
4395: FROM pn_payment_schedules_all
4396: WHERE lease_id = p_lease_id
4397: AND schedule_date > l_item_end_dt_tbl(i).item_end_dt
4398: AND payment_status_lookup_code <> 'APPROVED') /* 9547514 */
4399: AND payment_term_id = l_item_end_dt_tbl(i).term_id;

Line 4408: DELETE pn_payment_schedules_all psch

4404:
4405: pnp_debug_pkg.log('CONTRACTION_BY_ITM_END_DT - MAIN - Deleting Payment Schedules');
4406: pnp_debug_pkg.log('+++++++++++Deleting Draft Empty Schedules for lease:'||p_lease_id||
4407: ' with schedule_date > '||p_new_lea_term_dt);
4408: DELETE pn_payment_schedules_all psch
4409: WHERE lease_id = p_lease_id
4410: AND schedule_date > p_new_lea_term_dt
4411: AND payment_status_lookup_code <> 'APPROVED' /* 9547514 */
4412: AND NOT EXISTS (SELECT 1

Line 4427: pn_payment_schedules_all schd

4423: AND start_date > p_new_lea_term_dt
4424: AND index_period_id IS NULL
4425: AND NOT EXISTS(SELECT 1
4426: FROM pn_payment_items_all item,
4427: pn_payment_schedules_all schd
4428: WHERE item.payment_term_id = term.payment_term_id
4429: AND item.payment_schedule_id = schd.payment_schedule_id
4430: AND schd.payment_status_lookup_code = 'APPROVED');
4431:

Line 4658: FROM pn_payment_schedules_all pps

4654: AND changes.lease_change_id = details.lease_change_id;
4655:
4656: CURSOR get_last_appr_schd_dt (p_lease_id NUMBER) IS
4657: SELECT MAX(pps.schedule_date) lst_schedule_date
4658: FROM pn_payment_schedules_all pps
4659: WHERE pps.payment_status_lookup_code = 'APPROVED'
4660: AND pps.lease_id = p_lease_id;
4661:
4662: -- Bug 7184211

Line 4668: FROM pn_payment_schedules_all pps

4664: p_start_date DATE)
4665: IS
4666:
4667: SELECT First_Day(MIN(pps.schedule_date))
4668: FROM pn_payment_schedules_all pps
4669: WHERE pps.lease_id = p_lease_id
4670: AND First_Day(pps.schedule_date) >= First_Day(p_start_date)
4671: AND TO_CHAR(pps.schedule_date,'DD') = l_schd_day
4672: AND pps.payment_status_lookup_code = 'DRAFT';

Line 5470: FROM pn_payment_schedules_all pps,

5466: CURSOR lst_cash_sch_dt_cur (p_term_id NUMBER,
5467: p_term_end_dt DATE)
5468: IS
5469: SELECT MAX(pps.schedule_date)
5470: FROM pn_payment_schedules_all pps,
5471: pn_payment_items_all ppi
5472: WHERE pps.payment_schedule_id = ppi.payment_schedule_id
5473: AND ppi.payment_term_id = p_term_id
5474: AND ppi.actual_amount IS NOT NULL

Line 5569: pn_payment_schedules_all pps

5565: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
5566: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
5567: WHERE payment_item_id IN (SELECT ppi1.payment_item_id
5568: FROM pn_payment_items_all ppi1,
5569: pn_payment_schedules_all pps
5570: WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
5571: AND ppi1.payment_item_type_lookup_code = 'CASH'
5572: AND pps.payment_status_lookup_code IN ('DRAFT','ON_HOLD')
5573: AND pps.payment_schedule_id = ppi1.payment_schedule_id

Line 5583: FROM pn_payment_schedules_all pps1,

5579:
5580: DELETE pn_payment_items_all ppi
5581: WHERE ppi.payment_term_id = pay_term_con_cur.payment_term_id
5582: AND ppi.payment_schedule_id IN (SELECT pps1.payment_schedule_id
5583: FROM pn_payment_schedules_all pps1,
5584: pn_payment_items_all ppi1
5585: WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
5586: AND pps1.payment_schedule_id = ppi1.payment_schedule_id
5587: AND pps1.payment_status_lookup_code IN ('DRAFT','ON_HOLD')

Line 5590: DELETE pn_payment_schedules_all pps

5586: AND pps1.payment_schedule_id = ppi1.payment_schedule_id
5587: AND pps1.payment_status_lookup_code IN ('DRAFT','ON_HOLD')
5588: AND pps1.schedule_date > l_lst_cash_sch_dt);
5589:
5590: DELETE pn_payment_schedules_all pps
5591: WHERE pps.lease_id = p_lease_id
5592: AND pps.payment_schedule_id IN (SELECT pps1.payment_schedule_id
5593: FROM pn_payment_schedules_all pps1
5594: WHERE pps1.lease_id = p_lease_id

Line 5593: FROM pn_payment_schedules_all pps1

5589:
5590: DELETE pn_payment_schedules_all pps
5591: WHERE pps.lease_id = p_lease_id
5592: AND pps.payment_schedule_id IN (SELECT pps1.payment_schedule_id
5593: FROM pn_payment_schedules_all pps1
5594: WHERE pps1.lease_id = p_lease_id
5595: AND TO_NUMBER(TO_CHAR(pps1.schedule_date,'DD'))
5596: = pay_term_con_cur.schedule_day
5597: AND pps1.schedule_date > l_lst_cash_sch_dt

Line 5756: FROM pn_payment_schedules_all

5752: l_found_apprvd_sch BOOLEAN := FALSE;
5753:
5754: CURSOR check_sch_date (p_schedule_date DATE) IS
5755: SELECT payment_schedule_id
5756: FROM pn_payment_schedules_all
5757: WHERE lease_id = p_lease_id
5758: AND schedule_date = p_schedule_date
5759: AND payment_status_lookup_code = 'APPROVED';
5760: BEGIN

Line 6080: pn_payment_schedules_all ps

6076:
6077: CURSOR exist_appr_item_amt IS
6078: SELECT NVL(SUM(pi.actual_amount),0) amount
6079: FROM pn_payment_items_all pi,
6080: pn_payment_schedules_all ps
6081: WHERE pi.payment_term_id = p_payment_term_rec.payment_term_id
6082: AND pi.payment_schedule_id = ps.payment_schedule_id
6083: AND ps.schedule_date = l_sch_dt
6084: AND ps.payment_status_lookup_code = 'APPROVED'

Line 6298: DELETE FROM PN_PAYMENT_SCHEDULES_ALL

6294: p_sob_id => p_payment_term_rec.set_of_books_id,
6295: p_curr_code => p_payment_term_rec.currency_code,
6296: p_rate => p_payment_term_rec.rate);
6297: ELSE
6298: DELETE FROM PN_PAYMENT_SCHEDULES_ALL
6299: WHERE payment_schedule_id = l_sch_id
6300: AND NOT EXISTS (select 'Y'
6301: from pn_payment_items_all
6302: where payment_schedule_id = l_sch_id); /* Added Bug 9542483 */

Line 6343: DELETE FROM PN_PAYMENT_SCHEDULES_ALL

6339: p_sob_id => p_payment_term_rec.set_of_books_id,
6340: p_curr_code => p_payment_term_rec.currency_code,
6341: p_rate => p_payment_term_rec.rate);
6342: ELSE
6343: DELETE FROM PN_PAYMENT_SCHEDULES_ALL
6344: WHERE payment_schedule_id = l_sch_id
6345: and not exists (select NULL
6346: from pn_payment_items_all
6347: where payment_schedule_id = l_sch_id); /* 9322649 */

Line 6546: FROM pn_payment_schedules_all sch,

6542: AND end_date = p_date;
6543:
6544: CURSOR last_sch_cur (p_term_id NUMBER) IS
6545: SELECT MAX(sch.schedule_date)
6546: FROM pn_payment_schedules_all sch,
6547: pn_payment_items_all itm
6548: WHERE itm.payment_term_id = p_term_id
6549: AND itm.payment_schedule_id = sch.payment_schedule_id
6550: AND itm.payment_item_type_lookup_code = 'CASH';

Line 6573: l_payment_status pn_payment_schedules_all.payment_status_lookup_code%TYPE;

6569: l_freq NUMBER;
6570: l_no_of_sch NUMBER;
6571: l_last_sch_date DATE;
6572: l_duration_in_months NUMBER;
6573: l_payment_status pn_payment_schedules_all.payment_status_lookup_code%TYPE;
6574: l_create_term_flag VARCHAR2(1);
6575:
6576: BEGIN
6577:

Line 6663: from pn_payment_schedules_all

6659: OPEN last_sch_cur(l_payment_term_rec.payment_term_id);
6660: FETCH last_sch_cur INTO l_last_sch_date;
6661:
6662: select payment_status_lookup_code INTO l_payment_status
6663: from pn_payment_schedules_all
6664: where schedule_date = l_last_sch_date
6665: and lease_id = p_lease_id;
6666:
6667: CLOSE last_sch_cur;

Line 6959: pn_payment_schedules_all pps,

6955: DECODE(ppt.normalize,'Y',NVL(ppi.actual_amount,0), 0), 0)) cash_norm_amt,
6956: SUM(DECODE(ppi.payment_item_type_lookup_code, 'NORMALIZED',
6957: NVL(ppi.actual_amount,0), 0)) norm_amt
6958: FROM pn_payment_items_all ppi,
6959: pn_payment_schedules_all pps,
6960: pn_payment_terms_all ppt
6961: WHERE ppt.lease_id = g_lease_id
6962: AND ppt.lease_id = pps.lease_id
6963: AND pps.payment_schedule_id = ppi.payment_schedule_id

Line 7326: pn_payment_schedules_all pps

7322: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
7323: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
7324: WHERE payment_item_id = (SELECT ppi.payment_item_id
7325: FROM pn_payment_items_all ppi,
7326: pn_payment_schedules_all pps
7327: WHERE ppi.payment_term_id = p_term_id
7328: AND ppi.payment_item_type_lookup_code = 'CASH'
7329: AND pps.payment_schedule_id = ppi.payment_schedule_id
7330: AND pps.payment_status_lookup_code = 'DRAFT'

Line 7499: l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%TYPE;

7495: l_lease_termination_date DATE;
7496: l_active_lease_change_id pn_lease_details.lease_change_id%TYPE;
7497: l_payment_schedule_id pn_payment_items_all.payment_schedule_id%TYPE;
7498: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
7499: l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%TYPE;
7500: l_adjustment VARCHAR2(1);
7501: l_adj_amount NUMBER;
7502:
7503: l_sched_tbl pn_retro_adjustment_pkg.payment_item_tbl_type;

Line 7523: FROM pn_payment_schedules_all pps

7519:
7520: /* check if exists draft schedule for the given date for a lease */
7521: CURSOR draft_schedule_exists_cur (p_sched_date DATE) IS
7522: SELECT pps.payment_schedule_id
7523: FROM pn_payment_schedules_all pps
7524: WHERE pps.schedule_date = p_sched_date
7525: AND pps.lease_id = p_lease_id
7526: AND pps.payment_status_lookup_code = 'DRAFT';
7527:

Line 7531: pn_payment_schedules_all pps

7527:
7528: CURSOR get_drf_sch_date(p_payment_term_id NUMBER) IS
7529: SELECT distinct schedule_date
7530: FROM pn_payment_items_all ppi,
7531: pn_payment_schedules_all pps
7532: WHERE ppi.payment_term_id = p_payment_term_id
7533: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
7534: AND pps.payment_schedule_id = ppi.payment_schedule_id
7535: AND pps.payment_status_lookup_code <> 'DRAFT'

Line 7597: FROM pn_payment_schedules_all pps

7593:
7594: ELSIF pn_schedules_items.g_norm_dt_avl IS NULL THEN /* 9231686 */
7595: SELECT NVL(First_Day(MIN(pps.schedule_date)),l_amd_comn_date)
7596: into l_nrm_st_dt
7597: FROM pn_payment_schedules_all pps
7598: WHERE pps.lease_id = p_lease_id
7599: AND pps.payment_status_lookup_code = 'DRAFT'
7600: AND TO_CHAR(pps.schedule_date,'DD') = l_sch_dy ;
7601:

Line 7644: FROM pn_payment_schedules_all

7640: /* delete items beyond the end date */
7641: DELETE pn_payment_items_all
7642: WHERE payment_schedule_id IN
7643: (SELECT payment_schedule_id
7644: FROM pn_payment_schedules_all
7645: WHERE lease_id = p_lease_id
7646: AND schedule_date > pay_term_con_cur.end_date
7647: AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
7648: AND payment_term_id = pay_term_con_cur.payment_term_id;

Line 7783: FROM pn_payment_schedules_all

7779:
7780: DELETE pn_payment_items_all
7781: WHERE payment_schedule_id IN
7782: (SELECT payment_schedule_id
7783: FROM pn_payment_schedules_all
7784: WHERE lease_id = p_lease_id
7785: AND schedule_date > l_lst_cash_sch_dt
7786: AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
7787: AND payment_term_id = pay_term_con_cur.payment_term_id;

Line 7791: FROM pn_payment_schedules_all pps,

7787: AND payment_term_id = pay_term_con_cur.payment_term_id;
7788: l_count := 0;
7789: -- Copied from PN_NORM_RENORM_PKG.NORMALIZE_RENORMALIZE
7790: SELECT count(*) into l_count
7791: FROM pn_payment_schedules_all pps,
7792: pn_payment_items_all ppi
7793: WHERE pps.lease_id = p_lease_id
7794: AND pps.schedule_date BETWEEN PN_SCHEDULES_ITEMS.FIRST_DAY(NVL(l_amd_comn_date,l_norm_str_dt)) /* 7149537 */
7795: AND LAST_DAY(g_new_lea_term_dt)

Line 7953: FROM pn_payment_schedules_all

7949: -- retrieves the approved schedule for a payment term for a lease
7950: CURSOR approved_sched_exist_cur(p_lease_ID IN NUMBER, p_payment_term_id IN NUMBER)
7951: IS
7952: SELECT payment_schedule_id
7953: FROM pn_payment_schedules_all
7954: WHERE lease_id = p_lease_ID
7955: AND payment_status_lookup_code = 'APPROVED'
7956: AND payment_schedule_id IN (SELECT payment_schedule_id
7957: FROM pn_payment_items_all