[Home] [Help]
50: l_org_id NUMBER(15);
51:
52: CURSOR amounts_cursor IS
53: SELECT actual_amount, accounted_amount, currency_code, due_date, rate, org_id
54: FROM pn_payment_items_all
55: WHERE payment_schedule_id = p_payment_schedule_id
56: AND payment_item_type_lookup_code = 'CASH';
57:
58: BEGIN
135: BEGIN
136:
137: SELECT NVL(SUM(ppi.actual_amount),0)
138: INTO l_totalTermAmt
139: FROM pn_payment_items_all ppi
140: WHERE ppi.PAYMENT_TERM_ID = p_paymentTermId
141: AND ppi.payment_item_type_lookup_code = 'CASH';
142:
143: RETURN l_totalTermAmt;
2623: FROM dual
2624: WHERE EXISTS
2625: (SELECT 1
2626: FROM pn_payment_schedules_all s,
2627: pn_payment_items_all i
2628: WHERE i.payment_term_id IN
2629: (SELECT payment_term_id
2630: FROM pn_payment_terms_all
2631: WHERE lease_id = p_lease_ID
2639:
2640: CURSOR max_appr_sched_norm(p_lease_ID IN NUMBER) IS
2641: SELECT MAX(s.schedule_date) AS schedule_date
2642: FROM pn_payment_schedules_all s,
2643: pn_payment_items_all i
2644: WHERE i.payment_term_id IN
2645: (SELECT t.payment_term_id
2646: FROM pn_payment_terms_all t
2647: WHERE t.lease_id = p_lease_ID
2656: /* ONE TIME */
2657: CURSOR max_appr_sched_ot(p_lease_ID IN NUMBER) IS
2658: SELECT MAX(s.schedule_date) AS schedule_date
2659: FROM pn_payment_schedules_all s,
2660: pn_payment_items_all i
2661: WHERE i.payment_term_id IN
2662: (SELECT t.payment_term_id
2663: FROM pn_payment_terms_all t
2664: WHERE t.lease_id = p_lease_ID
2671: /* MONTHLY */
2672: CURSOR max_appr_sched_mon(p_lease_ID IN NUMBER) IS
2673: SELECT MAX(s.schedule_date) AS schedule_date
2674: FROM pn_payment_schedules_all s,
2675: pn_payment_items_all i
2676: WHERE i.payment_term_id IN
2677: (SELECT t.payment_term_id
2678: FROM pn_payment_terms_all t
2679: WHERE t.lease_id = p_lease_ID
2686: /* QUARTERLY, SEMI_ANNUAL, ANNUAL */
2687: CURSOR max_appr_sched_other(p_lease_ID IN NUMBER) IS
2688: SELECT MAX(s.schedule_date) AS schedule_date
2689: FROM pn_payment_schedules_all s,
2690: pn_payment_items_all i
2691: WHERE i.payment_term_id IN
2692: (SELECT t.payment_term_id
2693: FROM pn_payment_terms_all t
2694: WHERE t.lease_id = p_lease_ID
2709: FROM pn_payment_terms_all
2710: WHERE lease_ID = p_lease_ID
2711: AND payment_term_ID IN
2712: (SELECT DISTINCT i.payment_term_ID
2713: FROM pn_payment_items_all i
2714: ,pn_payment_schedules_all s
2715: WHERE s.schedule_date = p_sched_date
2716: AND s.lease_ID = p_lease_ID
2717: AND s.payment_status_lookup_code = 'APPROVED'
2722: CURSOR max_appr_sched_term( p_lease_ID IN NUMBER
2723: ,p_term_ID IN NUMBER) IS
2724: SELECT MAX(s.schedule_date) AS schedule_date
2725: FROM pn_payment_schedules_all s,
2726: pn_payment_items_all i
2727: WHERE i.payment_term_id = p_term_ID
2728: AND i.payment_schedule_id = s.payment_schedule_id
2729: AND s.payment_status_lookup_code = 'APPROVED'
2730: AND s.lease_id = p_lease_ID;
3055: l_item_end_dt DATE;
3056:
3057: CURSOR csr_ot_item_end_date(p_term_id IN NUMBER) IS
3058: SELECT MAX(schedule_date) AS item_end_date
3059: FROM pn_payment_items_all item,
3060: pn_payment_terms_all term,
3061: pn_payment_schedules_all schd
3062: WHERE term.payment_term_id = p_term_id
3063: AND item.payment_term_id = term.payment_term_id
3067: AND schd.payment_status_lookup_code = 'APPROVED';
3068:
3069: CURSOR csr_mon_item_end_date(p_term_id IN NUMBER) IS
3070: SELECT LAST_DAY(MAX(schedule_date)) AS item_end_date
3071: FROM pn_payment_items_all item,
3072: pn_payment_terms_all term,
3073: pn_payment_schedules_all schd
3074: WHERE term.payment_term_id = p_term_id
3075: AND item.payment_term_id = term.payment_term_id
3079: AND schd.payment_status_lookup_code = 'APPROVED';
3080:
3081: CURSOR csr_qtr_item_end_date(p_term_id IN NUMBER) IS
3082: SELECT ADD_MONTHS(MAX(schedule_date),3) - 1 AS item_end_date
3083: FROM pn_payment_items_all item,
3084: pn_payment_terms_all term,
3085: pn_payment_schedules_all schd
3086: WHERE term.payment_term_id = p_term_id
3087: AND item.payment_term_id = term.payment_term_id
3091: AND schd.payment_status_lookup_code = 'APPROVED';
3092:
3093: CURSOR csr_sa_item_end_date(p_term_id IN NUMBER) IS
3094: SELECT ADD_MONTHS(MAX(schedule_date),6) - 1 AS item_end_date
3095: FROM pn_payment_items_all item,
3096: pn_payment_terms_all term,
3097: pn_payment_schedules_all schd
3098: WHERE term.payment_term_id = p_term_id
3099: AND item.payment_term_id = term.payment_term_id
3103: AND schd.payment_status_lookup_code = 'APPROVED';
3104:
3105: CURSOR csr_yr_item_end_date(p_term_id IN NUMBER) IS
3106: SELECT ADD_MONTHS(MAX(schedule_date),12) - 1 AS item_end_date
3107: FROM pn_payment_items_all item,
3108: pn_payment_terms_all term,
3109: pn_payment_schedules_all schd
3110: WHERE term.payment_term_id = p_term_id
3111: AND item.payment_term_id = term.payment_term_id
7871:
7872: CURSOR search_for_items (p_payment_term_id NUMBER) IS
7873: SELECT 'Y' answer
7874: FROM dual
7875: WHERE EXISTS (SELECT 'Y' FROM pn_payment_items_all
7876: WHERE payment_term_id = p_payment_term_id);
7877:
7878: BEGIN
7879: FOR chk_area IN get_lease_area LOOP
8108:
8109: CURSOR search_for_items (p_payment_term_id NUMBER) IS
8110: SELECT 'Y' answer
8111: FROM dual
8112: WHERE EXISTS (SELECT 'Y' FROM pn_payment_items_all
8113: WHERE payment_term_id = p_payment_term_id);
8114:
8115: -- note that the old office area is excluded
8116: CURSOR get_flr_area (p_as_of_date DATE) IS