APPS.PN_RECOVERY_EXTRACT_PKG dependencies on PN_REC_ARCL

Line 11: TYPE area_cls_line_dtl_tbl IS TABLE OF pn_rec_arcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;

7:
8: TYPE exp_cls_line_use_tbl IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
9: TYPE exp_cls_line_mst_tbl IS TABLE OF pn_rec_expcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
10: TYPE exp_cls_line_dtl_tbl IS TABLE OF pn_rec_expcl_dtlacc%ROWTYPE INDEX BY BINARY_INTEGER;
11: TYPE area_cls_line_dtl_tbl IS TABLE OF pn_rec_arcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
12: TYPE area_cls_line_hdr_tbl IS TABLE OF pn_rec_arcl_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
13: TYPE area_cls_exc_tbl IS TABLE OF pn_rec_arcl_exc%ROWTYPE INDEX BY BINARY_INTEGER;
14:
15: bad_input_exception EXCEPTION;

Line 12: TYPE area_cls_line_hdr_tbl IS TABLE OF pn_rec_arcl_dtl%ROWTYPE INDEX BY BINARY_INTEGER;

8: TYPE exp_cls_line_use_tbl IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
9: TYPE exp_cls_line_mst_tbl IS TABLE OF pn_rec_expcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
10: TYPE exp_cls_line_dtl_tbl IS TABLE OF pn_rec_expcl_dtlacc%ROWTYPE INDEX BY BINARY_INTEGER;
11: TYPE area_cls_line_dtl_tbl IS TABLE OF pn_rec_arcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
12: TYPE area_cls_line_hdr_tbl IS TABLE OF pn_rec_arcl_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
13: TYPE area_cls_exc_tbl IS TABLE OF pn_rec_arcl_exc%ROWTYPE INDEX BY BINARY_INTEGER;
14:
15: bad_input_exception EXCEPTION;
16: uom_exception EXCEPTION;

Line 13: TYPE area_cls_exc_tbl IS TABLE OF pn_rec_arcl_exc%ROWTYPE INDEX BY BINARY_INTEGER;

9: TYPE exp_cls_line_mst_tbl IS TABLE OF pn_rec_expcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
10: TYPE exp_cls_line_dtl_tbl IS TABLE OF pn_rec_expcl_dtlacc%ROWTYPE INDEX BY BINARY_INTEGER;
11: TYPE area_cls_line_dtl_tbl IS TABLE OF pn_rec_arcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
12: TYPE area_cls_line_hdr_tbl IS TABLE OF pn_rec_arcl_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
13: TYPE area_cls_exc_tbl IS TABLE OF pn_rec_arcl_exc%ROWTYPE INDEX BY BINARY_INTEGER;
14:
15: bad_input_exception EXCEPTION;
16: uom_exception EXCEPTION;
17: currency_exception EXCEPTION;

Line 252: p_loc_id pn_rec_arcl_dtlln.location_id%TYPE,

248: ------------------------------------------------------------------------------+
249:
250: PROCEDURE find_area_ovr_values(
251: p_ovr area_cls_line_dtl_tbl,
252: p_loc_id pn_rec_arcl_dtlln.location_id%TYPE,
253: p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,
254: p_from pn_rec_arcl_dtlln.from_date%TYPE,
255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,

Line 253: p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,

249:
250: PROCEDURE find_area_ovr_values(
251: p_ovr area_cls_line_dtl_tbl,
252: p_loc_id pn_rec_arcl_dtlln.location_id%TYPE,
253: p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,
254: p_from pn_rec_arcl_dtlln.from_date%TYPE,
255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,

Line 254: p_from pn_rec_arcl_dtlln.from_date%TYPE,

250: PROCEDURE find_area_ovr_values(
251: p_ovr area_cls_line_dtl_tbl,
252: p_loc_id pn_rec_arcl_dtlln.location_id%TYPE,
253: p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,
254: p_from pn_rec_arcl_dtlln.from_date%TYPE,
255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,

Line 255: p_to pn_rec_arcl_dtlln.to_date%TYPE,

251: p_ovr area_cls_line_dtl_tbl,
252: p_loc_id pn_rec_arcl_dtlln.location_id%TYPE,
253: p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,
254: p_from pn_rec_arcl_dtlln.from_date%TYPE,
255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,

Line 256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,

252: p_loc_id pn_rec_arcl_dtlln.location_id%TYPE,
253: p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,
254: p_from pn_rec_arcl_dtlln.from_date%TYPE,
255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
260: p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,

Line 257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,

253: p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,
254: p_from pn_rec_arcl_dtlln.from_date%TYPE,
255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
260: p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
261: p_area_cls_dtl_line_id OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,

Line 258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,

254: p_from pn_rec_arcl_dtlln.from_date%TYPE,
255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
260: p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
261: p_area_cls_dtl_line_id OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,
262: p_found IN OUT NOCOPY BOOLEAN,

Line 259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,

255: p_to pn_rec_arcl_dtlln.to_date%TYPE,
256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
260: p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
261: p_area_cls_dtl_line_id OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,
262: p_found IN OUT NOCOPY BOOLEAN,
263: p_keep_override VARCHAR2

Line 260: p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,

256: p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
260: p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
261: p_area_cls_dtl_line_id OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,
262: p_found IN OUT NOCOPY BOOLEAN,
263: p_keep_override VARCHAR2
264: )

Line 261: p_area_cls_dtl_line_id OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,

257: p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258: p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259: p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
260: p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
261: p_area_cls_dtl_line_id OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,
262: p_found IN OUT NOCOPY BOOLEAN,
263: p_keep_override VARCHAR2
264: )
265: IS

Line 353: p_occup_pct OUT NOCOPY pn_rec_arcl_dtlln.occupancy_pct%TYPE,

349: p_rec_to_date IN OUT NOCOPY DATE,
350: p_as_of_date DATE,
351: p_assigned_area NUMBER,
352: p_exc_type_code VARCHAR2,
353: p_occup_pct OUT NOCOPY pn_rec_arcl_dtlln.occupancy_pct%TYPE,
354: p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,
355: p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,
356: p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
357: p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,

Line 354: p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,

350: p_as_of_date DATE,
351: p_assigned_area NUMBER,
352: p_exc_type_code VARCHAR2,
353: p_occup_pct OUT NOCOPY pn_rec_arcl_dtlln.occupancy_pct%TYPE,
354: p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,
355: p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,
356: p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
357: p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
358: p_include_flag OUT NOCOPY pn_rec_arcl_dtlln.include_flag%TYPE)

Line 355: p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,

351: p_assigned_area NUMBER,
352: p_exc_type_code VARCHAR2,
353: p_occup_pct OUT NOCOPY pn_rec_arcl_dtlln.occupancy_pct%TYPE,
354: p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,
355: p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,
356: p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
357: p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
358: p_include_flag OUT NOCOPY pn_rec_arcl_dtlln.include_flag%TYPE)
359: IS

Line 356: p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,

352: p_exc_type_code VARCHAR2,
353: p_occup_pct OUT NOCOPY pn_rec_arcl_dtlln.occupancy_pct%TYPE,
354: p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,
355: p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,
356: p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
357: p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
358: p_include_flag OUT NOCOPY pn_rec_arcl_dtlln.include_flag%TYPE)
359: IS
360: l_info VARCHAR2(300);

Line 357: p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,

353: p_occup_pct OUT NOCOPY pn_rec_arcl_dtlln.occupancy_pct%TYPE,
354: p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,
355: p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,
356: p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
357: p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
358: p_include_flag OUT NOCOPY pn_rec_arcl_dtlln.include_flag%TYPE)
359: IS
360: l_info VARCHAR2(300);
361: l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.get_area_cls_dtl_calc' ;

Line 358: p_include_flag OUT NOCOPY pn_rec_arcl_dtlln.include_flag%TYPE)

354: p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,
355: p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,
356: p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
357: p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
358: p_include_flag OUT NOCOPY pn_rec_arcl_dtlln.include_flag%TYPE)
359: IS
360: l_info VARCHAR2(300);
361: l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.get_area_cls_dtl_calc' ;
362: BEGIN

Line 724: p_hdr_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE

720: ------------------------------------------------------------------------------+
721: PROCEDURE process_area_class_line_data(
722: p_old_data area_cls_line_dtl_tbl,
723: p_new_data area_cls_line_dtl_tbl,
724: p_hdr_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE
725: )
726: IS
727: keep_table number_table_type;
728: delete_table number_table_type;

Line 750: pn_rec_arcl_dtlln_pkg.insert_row(

746:
747: l_info := ' inserting row into area cls dtl line table ';
748: pnp_debug_pkg.log(l_info);
749:
750: pn_rec_arcl_dtlln_pkg.insert_row(
751: x_org_id => pn_mo_cache_utils.get_current_org_id,
752: x_area_class_dtl_id => p_hdr_id,
753: x_area_class_dtl_line_id => l_area_cls_dtl_line_id,
754: x_from_date => p_new_data(i).from_date,

Line 790: pn_rec_arcl_dtlln_pkg.update_row(

786:
787: l_info := ' updating row in area cls dtl line table: ' || p_new_data(i).area_class_dtl_line_id ;
788: pnp_debug_pkg.log(l_info);
789:
790: pn_rec_arcl_dtlln_pkg.update_row(
791: x_area_class_dtl_line_id => l_area_cls_dtl_line_id,
792: x_from_date => p_new_data(i).from_date,
793: x_to_date => p_new_data(i).to_date,
794: x_location_id => p_new_data(i).location_id,

Line 843: DELETE FROM pn_rec_arcl_dtlln_all

839: IF NOT l_is_in THEN delete_table(delete_table.COUNT) := p_old_data(i).area_class_dtl_line_id; END IF;
840: END LOOP;
841:
842: FORALL i IN 0 .. delete_table.COUNT - 1
843: DELETE FROM pn_rec_arcl_dtlln_all
844: WHERE area_class_dtl_line_id = delete_table(i);
845:
846: pnp_debug_pkg.log(l_desc ||' (-)');
847:

Line 922: p_area_class_id IN pn_rec_arcl.area_class_id%TYPE,

918:
919: PROCEDURE extract_area(
920: errbuf OUT NOCOPY VARCHAR2,
921: retcode OUT NOCOPY VARCHAR2,
922: p_area_class_id IN pn_rec_arcl.area_class_id%TYPE,
923: p_as_of_date IN VARCHAR2,
924: p_from_date IN VARCHAR2,
925: p_to_date IN VARCHAR2,
926: p_keep_override IN VARCHAR2)

Line 938: FROM pn_rec_arcl_all class,

934: excl_dtl.area,
935: excl_dtl.area_class_exclusion_id,
936: excl_dtl.recovery_space_std_code,
937: excl_dtl.recovery_type_code
938: FROM pn_rec_arcl_all class,
939: pn_rec_arcl_exc_all excl_dtl
940: WHERE class.area_class_id = excl_dtl.area_class_id (+)
941: AND class.area_class_id = p_area_class_id;
942:

Line 939: pn_rec_arcl_exc_all excl_dtl

935: excl_dtl.area_class_exclusion_id,
936: excl_dtl.recovery_space_std_code,
937: excl_dtl.recovery_type_code
938: FROM pn_rec_arcl_all class,
939: pn_rec_arcl_exc_all excl_dtl
940: WHERE class.area_class_id = excl_dtl.area_class_id (+)
941: AND class.area_class_id = p_area_class_id;
942:
943: CURSOR get_location_info(

Line 1012: FROM pn_rec_arcl_dtlln_all dtl,

1008: dtl.area_class_dtl_line_id,
1009: hdr.area_class_dtl_id,
1010: hdr.status,
1011: setup.area_class_name
1012: FROM pn_rec_arcl_dtlln_all dtl,
1013: pn_rec_arcl_dtl_all hdr,
1014: pn_rec_arcl_all setup
1015: WHERE dtl.area_class_dtl_id (+) = hdr.area_class_dtl_id
1016: AND hdr.area_class_id = p_area_class_id

Line 1013: pn_rec_arcl_dtl_all hdr,

1009: hdr.area_class_dtl_id,
1010: hdr.status,
1011: setup.area_class_name
1012: FROM pn_rec_arcl_dtlln_all dtl,
1013: pn_rec_arcl_dtl_all hdr,
1014: pn_rec_arcl_all setup
1015: WHERE dtl.area_class_dtl_id (+) = hdr.area_class_dtl_id
1016: AND hdr.area_class_id = p_area_class_id
1017: AND TRUNC(hdr.as_of_date) = TRUNC(fnd_date.canonical_to_date(p_as_of_date))

Line 1014: pn_rec_arcl_all setup

1010: hdr.status,
1011: setup.area_class_name
1012: FROM pn_rec_arcl_dtlln_all dtl,
1013: pn_rec_arcl_dtl_all hdr,
1014: pn_rec_arcl_all setup
1015: WHERE dtl.area_class_dtl_id (+) = hdr.area_class_dtl_id
1016: AND hdr.area_class_id = p_area_class_id
1017: AND TRUNC(hdr.as_of_date) = TRUNC(fnd_date.canonical_to_date(p_as_of_date))
1018: AND TRUNC(hdr.from_date) = TRUNC(fnd_date.canonical_to_date(p_from_date))

Line 1024: FROM pn_rec_arcl_dtl_all

1020: AND setup.area_class_id = hdr.area_class_id;
1021:
1022: CURSOR get_prior_cls_dtl_id IS
1023: SELECT area_class_dtl_id
1024: FROM pn_rec_arcl_dtl_all
1025: WHERE from_date < fnd_date.canonical_to_date(p_to_date)
1026: AND to_date <= fnd_date.canonical_to_date(p_to_date)
1027: AND as_of_date < fnd_date.canonical_to_date(p_as_of_date)
1028: AND area_class_id = p_area_class_id

Line 1031: CURSOR get_ovr_from_prior(p_prior_cls_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE) IS

1027: AND as_of_date < fnd_date.canonical_to_date(p_as_of_date)
1028: AND area_class_id = p_area_class_id
1029: ORDER BY as_of_date DESC, to_date DESC , from_date DESC;
1030:
1031: CURSOR get_ovr_from_prior(p_prior_cls_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE) IS
1032: SELECT location_id,
1033: from_date,
1034: to_date,
1035: cust_account_id,

Line 1045: FROM pn_rec_arcl_dtlln_all

1041: exclude_area_flag,
1042: exclude_area_ovr_flag,
1043: recovery_space_std_code,
1044: recovery_type_code
1045: FROM pn_rec_arcl_dtlln_all
1046: WHERE area_class_dtl_id = p_prior_cls_dtl_id;
1047:
1048: l_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1049: l_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE;

Line 1048: l_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;

1044: recovery_type_code
1045: FROM pn_rec_arcl_dtlln_all
1046: WHERE area_class_dtl_id = p_prior_cls_dtl_id;
1047:
1048: l_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1049: l_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE;
1050: l_dummy_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1051: l_count NUMBER;
1052: l_data_tbl_counter NUMBER;

Line 1049: l_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE;

1045: FROM pn_rec_arcl_dtlln_all
1046: WHERE area_class_dtl_id = p_prior_cls_dtl_id;
1047:
1048: l_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1049: l_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE;
1050: l_dummy_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1051: l_count NUMBER;
1052: l_data_tbl_counter NUMBER;
1053: l_regenerate VARCHAR2(1);

Line 1050: l_dummy_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;

1046: WHERE area_class_dtl_id = p_prior_cls_dtl_id;
1047:
1048: l_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1049: l_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE;
1050: l_dummy_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1051: l_count NUMBER;
1052: l_data_tbl_counter NUMBER;
1053: l_regenerate VARCHAR2(1);
1054: l_found BOOLEAN;

Line 1058: l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;

1054: l_found BOOLEAN;
1055: l_token VARCHAR2(100);
1056: l_is_assigned BOOLEAN;
1057: l_meets_criteria BOOLEAN;
1058: l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;
1059: l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;

Line 1059: l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;

1055: l_token VARCHAR2(100);
1056: l_is_assigned BOOLEAN;
1057: l_meets_criteria BOOLEAN;
1058: l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;
1059: l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;

Line 1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;

1056: l_is_assigned BOOLEAN;
1057: l_meets_criteria BOOLEAN;
1058: l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;
1059: l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;

Line 1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;

1057: l_meets_criteria BOOLEAN;
1058: l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;
1059: l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;

Line 1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;

1058: l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;
1059: l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;

Line 1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;

1059: l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;

Line 1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;

1060: l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;

Line 1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;

1061: l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;

Line 1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;

1062: l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;

Line 1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;

1063: l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;

Line 1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;

1064: l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;

Line 1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;

1065: l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1073: l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;

Line 1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;

1066: l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1073: l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;
1074: l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;

Line 1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;

1067: l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1073: l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;
1074: l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;
1075: l_vacancy_num_table number_table_type;

Line 1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;

1068: l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1073: l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;
1074: l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;
1075: l_vacancy_num_table number_table_type;
1076: l_vacancy_date_table date_table_type;

Line 1073: l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;

1069: l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1073: l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;
1074: l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;
1075: l_vacancy_num_table number_table_type;
1076: l_vacancy_date_table date_table_type;
1077: l_ref_vacancy_num_table number_table_type;

Line 1074: l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;

1070: l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071: l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072: l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1073: l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;
1074: l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;
1075: l_vacancy_num_table number_table_type;
1076: l_vacancy_date_table date_table_type;
1077: l_ref_vacancy_num_table number_table_type;
1078: l_ref_vacancy_date_table date_table_type;

Line 1083: l_arcl_locid pn_rec_arcl.location_id%TYPE;

1079: l_area_cls_ln_data_tbl area_cls_line_dtl_tbl;
1080: l_area_cls_ln_curnt_ovr area_cls_line_dtl_tbl;
1081: l_area_cls_ln_prior_ovr area_cls_line_dtl_tbl;
1082: l_area_total_tbl area_cls_line_hdr_tbl;
1083: l_arcl_locid pn_rec_arcl.location_id%TYPE;
1084: l_arcl_propid pn_rec_arcl.property_id%TYPE;
1085: l_arcl_exc_table area_cls_exc_tbl;
1086: l_uom_code pn_locations_all.uom_code%TYPE;
1087: l_info VARCHAR2(100);

Line 1084: l_arcl_propid pn_rec_arcl.property_id%TYPE;

1080: l_area_cls_ln_curnt_ovr area_cls_line_dtl_tbl;
1081: l_area_cls_ln_prior_ovr area_cls_line_dtl_tbl;
1082: l_area_total_tbl area_cls_line_hdr_tbl;
1083: l_arcl_locid pn_rec_arcl.location_id%TYPE;
1084: l_arcl_propid pn_rec_arcl.property_id%TYPE;
1085: l_arcl_exc_table area_cls_exc_tbl;
1086: l_uom_code pn_locations_all.uom_code%TYPE;
1087: l_info VARCHAR2(100);
1088: l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_area' ;

Line 1095: fnd_message.set_name('PN','PN_REC_ARCL_DTL_CP_INFO');

1091: BEGIN
1092:
1093: pnp_debug_pkg.log(l_desc ||' (+)');
1094:
1095: fnd_message.set_name('PN','PN_REC_ARCL_DTL_CP_INFO');
1096: fnd_message.set_token('ARCL' , to_char(p_area_class_id));
1097: fnd_message.set_token('STR' , p_from_date);
1098: fnd_message.set_token('END' , p_to_date);
1099: fnd_message.set_token('AOD' , p_as_of_date);

Line 1174: fnd_message.set_name('PN','PN_REC_ARCL_DTL');

1170:
1171: FOR get_ovr_rec IN get_ovr_from_curnt LOOP
1172:
1173: IF get_ovr_rec.status = 'LOCKED' THEN
1174: fnd_message.set_name('PN','PN_REC_ARCL_DTL');
1175: l_token := fnd_message.get;
1176: fnd_message.set_name('PN','PN_REC_NO_REGEN_LOCKED');
1177: fnd_message.set_token('MODULE',l_token);
1178: fnd_message.set_token('FDATE', p_from_date);

Line 1540: pn_rec_arcl_dtl_pkg.update_row(

1536: pnp_debug_pkg.log(l_info);
1537:
1538: IF l_area_class_dtl_id IS NOT NULL THEN
1539:
1540: pn_rec_arcl_dtl_pkg.update_row(
1541: x_area_class_id => p_area_class_id,
1542: x_area_class_dtl_id => l_area_class_dtl_id,
1543: x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1544: x_from_date => fnd_date.canonical_to_date(p_from_date),

Line 1564: pn_rec_arcl_dtl_pkg.insert_row(

1560: x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
1561: x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
1562: );
1563: ELSE
1564: pn_rec_arcl_dtl_pkg.insert_row(
1565: x_org_id => pn_mo_cache_utils.get_current_org_id,
1566: x_area_class_id => p_area_class_id,
1567: x_area_class_dtl_id => l_area_class_dtl_id,
1568: x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),

Line 2953: FROM pn_rec_arcl_all

2949: IS
2950:
2951: CURSOR get_arcl_by_prop (l_propid pn_rec_exp_line.property_id%TYPE) IS
2952: SELECT area_class_id
2953: FROM pn_rec_arcl_all
2954: WHERE property_id = l_propid
2955: AND location_id IS NULL;
2956:
2957: CURSOR get_arcl_by_locn (l_locnid pn_rec_exp_line.location_id%TYPE) IS

Line 2959: FROM pn_rec_arcl_all

2955: AND location_id IS NULL;
2956:
2957: CURSOR get_arcl_by_locn (l_locnid pn_rec_exp_line.location_id%TYPE) IS
2958: SELECT area_class_id
2959: FROM pn_rec_arcl_all
2960: WHERE location_id = l_locnid;
2961:
2962: CURSOR get_locn_prop_id IS
2963: SELECT property_id,

Line 3043: fnd_message.set_name('PN','PN_REC_ARCL_DTL');

3039: END LOOP;
3040:
3041: END IF;
3042:
3043: fnd_message.set_name('PN','PN_REC_ARCL_DTL');
3044: pnp_debug_pkg.put_log_msg('');
3045: pnp_debug_pkg.put_log_msg(fnd_message.get);
3046:
3047: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');