[Home] [Help]
383: --
384: -----------------------------------------------------------------------------
385: -- Name form1 --
386: -- Purpose --
387: -- This procedure is used to populate per_ca_ee_report_lines for the --
388: -- form1 in the Employment Equity Report. --
389: -- --
390: -- --
391: -----------------------------------------------------------------------------
494: (
495: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
496: hsck.segment6 is null and
497: hsck.segment1 in (select segment3
498: from per_ca_ee_report_lines
499: where request_id = p_request_id and
500: context = 'FORM13' and
501: segment1 = 'NAIC' and
502: segment2 = p_naic_code)
510: (
511: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
512: hsck.segment6 is not null and
513: hsck.segment6 in (select segment3
514: from per_ca_ee_report_lines
515: where request_id = p_request_id and
516: context = 'FORM13' and
517: segment1 = 'NAIC')
518: )
520: (
521: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
522: hsck.segment6 is null and
523: hsck.segment1 in (select segment3
524: from per_ca_ee_report_lines
525: where request_id = p_request_id and
526: context = 'FORM13' and
527: segment1 = 'NAIC')
528: )
582: (
583: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
584: hsck.segment6 is null and
585: hsck.segment1 in (select segment3
586: from per_ca_ee_report_lines
587: where request_id = p_request_id and
588: context = 'FORM13' and
589: segment1 = 'NAIC' and
590: segment2 = p_naic_code)
598: (
599: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
600: hsck.segment6 is not null and
601: hsck.segment6 in (select segment3
602: from per_ca_ee_report_lines
603: where request_id = p_request_id and
604: context = 'FORM13' and
605: segment1 = 'NAIC')
606: )
608: (
609: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
610: hsck.segment6 is null and
611: hsck.segment1 in (select segment3
612: from per_ca_ee_report_lines
613: where request_id = p_request_id and
614: context = 'FORM13' and
615: segment1 = 'NAIC')
616: )
690: minus
691: select
692: ltrim(rtrim(segment2))
693: from
694: per_ca_ee_report_lines
695: where
696: request_id=p_request_id and
697: ltrim(rtrim(context))='FORM13' and
698: ltrim(rtrim(segment1))='CMA';
707: minus
708: select
709: ltrim(rtrim(segment2))
710: from
711: per_ca_ee_report_lines
712: where
713: request_id=p_request_id and
714: ltrim(rtrim(context))='FORM14' and
715: ltrim(rtrim(segment1))='PROVINCE';
854: v_organization_id,
855: v_short_name;
856: close cur_org_info;
857:
858: insert into per_ca_ee_report_lines
859: ( request_id,
860: line_number,
861: context,
862: segment1,
882: v_short_name);
883:
884: for i in cur_employee_info loop
885:
886: update per_ca_ee_report_lines set
887: segment9 = i.ceo_name, -- CEO name
888: segment10 = i.ceo_position, -- and his Position
889: segment11 = i.contact_name, -- EE relevant personnel
890: segment12 = i.contact_position, -- and his Position
933: if i = 1 then
934: v_max_naic := 'Y';
935: end if; */
936:
937: insert into per_ca_ee_report_lines
938: (
939: request_id,
940: line_number,
941: context,
962: v_max_naic := 'N';
963:
964: for gre_id in cur_gre_id(v_sorted_naic(i)) loop
965:
966: insert into per_ca_ee_report_lines
967: (
968: request_id,
969: line_number,
970: context,
993: loop
994:
995: if i.employment_category = 'FR' then
996:
997: update per_ca_ee_report_lines set
998: segment13 = i.count_category
999: where request_id = p_request_id and
1000: --line_number = per_ca_ee_extract_pkg.k and
1001: context = 'FORM11';
1001: context = 'FORM11';
1002:
1003: elsif i.employment_category = 'PR' then
1004:
1005: update per_ca_ee_report_lines set
1006: segment14 = i.count_category
1007: where request_id = p_request_id and
1008: --line_number = per_ca_ee_extract_pkg.k and
1009: context = 'FORM11';
1012: elsif i.employment_category = 'PT' then
1013:
1014: hr_utility.trace('Form1: Employment Category: ' || i.employment_category);
1015:
1016: update per_ca_ee_report_lines set
1017: segment15 = i.count_category
1018: where request_id = p_request_id and
1019: --line_number = per_ca_ee_extract_pkg.k and
1020: context = 'FORM11';
1040: else
1041: v_print := 'N';
1042: end if;
1043:
1044: insert into per_ca_ee_report_lines
1045: (request_id,
1046: line_number,
1047: context,
1048: segment1,
1064: for i in cur_cma_notfound loop
1065:
1066: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
1067:
1068: insert into per_ca_ee_report_lines
1069: (request_id,
1070: line_number,
1071: context,
1072: segment1,
1086: for i in cur_province_notfound loop
1087:
1088: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
1089:
1090: insert into per_ca_ee_report_lines
1091: (request_id,
1092: line_number,
1093: context,
1094: segment1,
1139: pert.segment3 tot_number_emp,
1140: pert.segment4 naic_code,
1141: pert.segment5 max_naic_flag
1142: from
1143: per_ca_ee_report_lines pert
1144: where
1145: pert.request_id = p_request_id and
1146: --(pert.segment5 = 'Y' OR
1147: -- to_number(pert.segment3) >= to_number(v_leg_info)) and
1146: --(pert.segment5 = 'Y' OR
1147: -- to_number(pert.segment3) >= to_number(v_leg_info)) and
1148: pert.context = 'FORM12' ;
1149:
1150: v_tot_number_emp per_ca_ee_report_lines.segment3%TYPE;
1151: v_naic_code hr_lookups.lookup_code%TYPE;
1152: v_max_naic_flag varchar2(1);
1153:
1154: cursor cur_min_max is select
1207: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1208: hsck.segment6 is not null and
1209: hsck.segment6 = v_naic_code OR
1210: hsck.segment6 in ( select segment4
1211: from per_ca_ee_report_lines
1212: where request_id = p_request_id and
1213: context = 'FORM12' and
1214: to_number(segment3) <= to_number(v_leg_info) and
1215: v_max_naic_flag = 'Y')
1218: (
1219: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1220: hsck.segment6 is null and
1221: hsck.segment1 in (select segment3
1222: from per_ca_ee_report_lines
1223: where request_id = p_request_id and
1224: context = 'FORM13' and
1225: segment1 = 'NAIC' and
1226: segment2 = v_naic_code OR
1225: segment1 = 'NAIC' and
1226: segment2 = v_naic_code OR
1227: segment2 in
1228: ( select segment4
1229: from per_ca_ee_report_lines
1230: where request_id = p_request_id and
1231: context = 'FORM12' and
1232: to_number(segment3)
1233: <= to_number(v_leg_info) and
1284: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1285: hsck.segment6 is not null and
1286: hsck.segment6 = v_naic_code OR
1287: hsck.segment6 in ( select segment4
1288: from per_ca_ee_report_lines
1289: where request_id = p_request_id and
1290: context = 'FORM12' and
1291: to_number(segment3) <= to_number(v_leg_info) and
1292: v_max_naic_flag = 'Y')
1295: (
1296: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1297: hsck.segment6 is null and
1298: hsck.segment1 in (select segment3
1299: from per_ca_ee_report_lines
1300: where request_id = p_request_id and
1301: context = 'FORM13' and
1302: segment1 = 'NAIC' and
1303: segment2 = v_naic_code OR
1302: segment1 = 'NAIC' and
1303: segment2 = v_naic_code OR
1304: segment2 in
1305: ( select segment4
1306: from per_ca_ee_report_lines
1307: where request_id = p_request_id and
1308: context = 'FORM12' and
1309: to_number(segment3)
1310: <= to_number(v_leg_info) and
1397: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1398: hsck.segment6 is not null and
1399: hsck.segment6 = v_naic_code OR
1400: hsck.segment6 in ( select segment4
1401: from per_ca_ee_report_lines
1402: where request_id = p_request_id and
1403: context = 'FORM12' and
1404: to_number(segment3) <= to_number(v_leg_info) and
1405: v_max_naic_flag = 'Y')
1408: (
1409: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1410: hsck.segment6 is null and
1411: hsck.segment1 in (select segment3
1412: from per_ca_ee_report_lines
1413: where request_id = p_request_id and
1414: context = 'FORM13' and
1415: segment1 = 'NAIC' and
1416: segment2 = v_naic_code OR
1415: segment1 = 'NAIC' and
1416: segment2 = v_naic_code OR
1417: segment2 in
1418: ( select segment4
1419: from per_ca_ee_report_lines
1420: where request_id = p_request_id and
1421: context = 'FORM12' and
1422: to_number(segment3)
1423: <= to_number(v_leg_info) and
1498: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1499: hsck.segment6 is not null and
1500: hsck.segment6 = v_naic_code OR
1501: hsck.segment6 in ( select segment4
1502: from per_ca_ee_report_lines
1503: where request_id = p_request_id and
1504: context = 'FORM12' and
1505: to_number(segment3) <= to_number(v_leg_info) and
1506: v_max_naic_flag = 'Y')
1510: (
1511: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
1512: hsck.segment6 is null and
1513: hsck.segment1 in (select segment3
1514: from per_ca_ee_report_lines
1515: where request_id = p_request_id and
1516: context = 'FORM13' and
1517: segment1 = 'NAIC' and
1518: segment2 = v_naic_code OR
1517: segment1 = 'NAIC' and
1518: segment2 = v_naic_code OR
1519: segment2 in
1520: ( select segment4
1521: from per_ca_ee_report_lines
1522: where request_id = p_request_id and
1523: context = 'FORM12' and
1524: to_number(segment3)
1525: <= to_number(v_leg_info) and
1825: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
1826:
1827: hr_utility.trace('If .....');
1828:
1829: insert into per_ca_ee_report_lines
1830: ( request_id,
1831: line_number,
1832: context,
1833: segment1,
1888: if v_sex = 'M' then
1889:
1890: hr_utility.trace('Update Male');
1891:
1892: update per_ca_ee_report_lines set
1893: segment6=segment6 + nvl(v_count,0),
1894: segment8=nvl(v_count,0)
1895: where request_id=p_request_id and
1896: line_number=per_ca_ee_extract_pkg.k and
1900: elsif v_sex = 'F' then
1901:
1902: hr_utility.trace('Update Female');
1903:
1904: update per_ca_ee_report_lines set
1905: segment6=segment6 + nvl(v_count,0),
1906: segment7=nvl(v_count,0)
1907: where request_id=p_request_id and
1908: line_number=per_ca_ee_extract_pkg.k and
1924: if j_flag = 'F' then
1925:
1926: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
1927:
1928: insert into per_ca_ee_report_lines
1929: ( request_id,
1930: line_number,
1931: context,
1932: segment1,
1996: if k = 1 then
1997:
1998: if l.sex = 'F' then
1999:
2000: update per_ca_ee_report_lines set
2001: segment9=nvl(segment9,0) + nvl(l.count,0),
2002: segment10=nvl(l.count,0)
2003: where
2004: request_id=p_request_id and
2011: segment21 = v_naic_code;
2012:
2013: elsif l.sex = 'M' then
2014:
2015: update per_ca_ee_report_lines set
2016: segment9=nvl(segment9,0) + nvl(l.count,0),
2017: segment11=nvl(l.count,0)
2018: where
2019: request_id=p_request_id and
2030: elsif k = 2 then
2031:
2032: if l.sex = 'F' then
2033:
2034: update per_ca_ee_report_lines set
2035: segment12=nvl(segment12,0) + nvl(l.count,0),
2036: segment13=nvl(l.count,0)
2037: where
2038: request_id=p_request_id and
2046: segment21 = v_naic_code;
2047:
2048: else
2049:
2050: update per_ca_ee_report_lines set
2051: segment12=nvl(segment12,0) + nvl(l.count,0),
2052: segment14=nvl(l.count,0)
2053: where
2054: request_id=p_request_id and
2062:
2063: elsif k = 3 then
2064:
2065: if l.sex = 'F' then
2066: update per_ca_ee_report_lines set
2067: segment15=nvl(segment15,0) + nvl(l.count,0),
2068: segment16=nvl(l.count,0)
2069: where
2070: request_id=p_request_id and
2075: = upper(ltrim(rtrim(v_employment_category))) and
2076: segment5 = to_char(j) and
2077: segment21 = v_naic_code;
2078: else
2079: update per_ca_ee_report_lines set
2080: segment15=nvl(segment15,0) + nvl(l.count,0),
2081: segment17=nvl(l.count,0)
2082: where
2083: request_id=p_request_id and
2138: pert.segment3 tot_number_emp,
2139: pert.segment4 naic_code,
2140: pert.segment5 max_naic_flag
2141: from
2142: per_ca_ee_report_lines pert
2143: where
2144: pert.request_id = p_request_id and
2145: --(pert.segment5 = 'Y' OR
2146: -- to_number(pert.segment3) >= to_number(v_leg_info)) and
2145: --(pert.segment5 = 'Y' OR
2146: -- to_number(pert.segment3) >= to_number(v_leg_info)) and
2147: pert.context = 'FORM12' ;
2148:
2149: v_tot_number_emp per_ca_ee_report_lines.segment3%TYPE;
2150: v_naic_code hr_lookups.lookup_code%TYPE;
2151: v_max_naic_flag varchar2(1);
2152:
2153: cursor cur_min_max(cma_province_count number) is
2174: per_people_f ppf,
2175: per_assignments_f paf,
2176: hr_locations hl1,
2177: per_person_types ppt,
2178: per_ca_ee_report_lines pert,
2179: hr_lookups hl2,
2180: hr_soft_coding_keyflex hsck,
2181: per_pay_bases ppb
2182: where
2226: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2227: hsck.segment6 is not null and
2228: hsck.segment6 = v_naic_code OR
2229: hsck.segment6 in ( select segment4
2230: from per_ca_ee_report_lines
2231: where request_id = p_request_id and
2232: context = 'FORM12' and
2233: to_number(segment3) < to_number(v_leg_info) and
2234: v_max_naic_flag = 'Y')
2237: (
2238: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2239: hsck.segment6 is null and
2240: hsck.segment1 in (select segment3
2241: from per_ca_ee_report_lines
2242: where request_id = p_request_id and
2243: context = 'FORM13' and
2244: segment1 = 'NAIC' and
2245: segment2 = v_naic_code OR
2244: segment1 = 'NAIC' and
2245: segment2 = v_naic_code OR
2246: segment2 in
2247: ( select segment4
2248: from per_ca_ee_report_lines
2249: where request_id = p_request_id and
2250: context = 'FORM12' and
2251: to_number(segment3)
2252: < to_number(v_leg_info) and
2271: per_people_f ppf,
2272: per_assignments_f paf,
2273: hr_locations hl1,
2274: per_person_types ppt,
2275: per_ca_ee_report_lines pert,
2276: hr_lookups hl2,
2277: hr_soft_coding_keyflex hsck,
2278: per_pay_bases ppb
2279: where
2322: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2323: hsck.segment6 is not null and
2324: hsck.segment6 = v_naic_code OR
2325: hsck.segment6 in ( select segment4
2326: from per_ca_ee_report_lines
2327: where request_id = p_request_id and
2328: context = 'FORM12' and
2329: to_number(segment3) < to_number(v_leg_info) and
2330: v_max_naic_flag = 'Y')
2333: (
2334: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2335: hsck.segment6 is null and
2336: hsck.segment1 in (select segment3
2337: from per_ca_ee_report_lines
2338: where request_id = p_request_id and
2339: context = 'FORM13' and
2340: segment1 = 'NAIC' and
2341: segment2 = v_naic_code OR
2340: segment1 = 'NAIC' and
2341: segment2 = v_naic_code OR
2342: segment2 in
2343: ( select segment4
2344: from per_ca_ee_report_lines
2345: where request_id = p_request_id and
2346: context = 'FORM12' and
2347: to_number(segment3)
2348: < to_number(v_leg_info) and
2454: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2455: hsck.segment6 is not null and
2456: hsck.segment6 = v_naic_code OR
2457: hsck.segment6 in ( select segment4
2458: from per_ca_ee_report_lines
2459: where request_id = p_request_id and
2460: context = 'FORM12' and
2461: to_number(segment3) < to_number(v_leg_info) and
2462: v_max_naic_flag = 'Y')
2465: (
2466: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2467: hsck.segment6 is null and
2468: hsck.segment1 in (select segment3
2469: from per_ca_ee_report_lines
2470: where request_id = p_request_id and
2471: context = 'FORM13' and
2472: segment1 = 'NAIC' and
2473: segment2 = v_naic_code OR
2472: segment1 = 'NAIC' and
2473: segment2 = v_naic_code OR
2474: segment2 in
2475: ( select segment4
2476: from per_ca_ee_report_lines
2477: where request_id = p_request_id and
2478: context = 'FORM12' and
2479: to_number(segment3)
2480: < to_number(v_leg_info) and
2563: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2564: hsck.segment6 is not null and
2565: hsck.segment6 = v_naic_code OR
2566: hsck.segment6 in ( select segment4
2567: from per_ca_ee_report_lines
2568: where request_id = p_request_id and
2569: context = 'FORM12' and
2570: to_number(segment3) < to_number(v_leg_info) and
2571: v_max_naic_flag = 'Y')
2574: (
2575: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2576: hsck.segment6 is null and
2577: hsck.segment1 in (select segment3
2578: from per_ca_ee_report_lines
2579: where request_id = p_request_id and
2580: context = 'FORM13' and
2581: segment1 = 'NAIC' and
2582: segment2 = v_naic_code OR
2581: segment1 = 'NAIC' and
2582: segment2 = v_naic_code OR
2583: segment2 in
2584: ( select segment4
2585: from per_ca_ee_report_lines
2586: where request_id = p_request_id and
2587: context = 'FORM12' and
2588: to_number(segment3)
2589: < to_number(v_leg_info) and
2884: ) then
2885:
2886: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
2887:
2888: insert into per_ca_ee_report_lines
2889: ( request_id,
2890: line_number,
2891: context,
2892: segment1,
2944: prev_sex <> v_sex then
2945:
2946: if v_sex = 'M' then
2947:
2948: update per_ca_ee_report_lines set
2949: segment7=segment7 + nvl(v_count,0),
2950: segment9=nvl(v_count,0)
2951: where request_id=p_request_id and
2952: line_number=per_ca_ee_extract_pkg.k and
2956: segment21 = v_naic_code;
2957:
2958: elsif v_sex = 'F' then
2959:
2960: update per_ca_ee_report_lines set
2961: segment7=segment7 + nvl(v_count,0),
2962: segment9=nvl(v_count,0)
2963: where request_id=p_request_id and
2964: line_number=per_ca_ee_extract_pkg.k and
2984: if j_flag = 'F' then
2985:
2986: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
2987:
2988: insert into per_ca_ee_report_lines
2989: ( request_id,
2990: line_number,
2991: context,
2992: segment1,
3053: if k = 1 then
3054:
3055: if l.sex = 'F' then
3056:
3057: update per_ca_ee_report_lines set
3058: segment10=nvl(segment10,0) + nvl(l.count,0),
3059: segment11=nvl(l.count,0)
3060: where
3061: request_id=p_request_id and
3070: segment21 = v_naic_code;
3071:
3072: elsif l.sex = 'M' then
3073:
3074: update per_ca_ee_report_lines set
3075: segment10=nvl(segment10,0) + nvl(l.count,0),
3076: segment12=nvl(l.count,0)
3077: where
3078: request_id=p_request_id and
3092: elsif k = 2 then
3093:
3094: if l.sex = 'F' then
3095:
3096: update per_ca_ee_report_lines set
3097: segment13=nvl(segment13,0) + nvl(l.count,0),
3098: segment14=nvl(l.count,0)
3099: where
3100: request_id=p_request_id and
3108: segment6=to_char(j) and
3109: segment21 = v_naic_code;
3110: else
3111:
3112: update per_ca_ee_report_lines set
3113: segment13=nvl(segment13,0) + nvl(l.count,0),
3114: segment15=nvl(l.count,0)
3115: where
3116: request_id=p_request_id and
3129: elsif k = 3 then
3130:
3131: if l.sex = 'F' then
3132:
3133: update per_ca_ee_report_lines set
3134: segment16=nvl(segment16,0) + nvl(l.count,0),
3135: segment17=nvl(l.count,0)
3136: where
3137: request_id=p_request_id and
3146: segment21 = v_naic_code;
3147:
3148: else
3149:
3150: update per_ca_ee_report_lines set
3151: segment16=nvl(segment16,0) + nvl(l.count,0),
3152: segment18=nvl(l.count,0)
3153: where
3154: request_id=p_request_id and
3202: cursor cur_naic is
3203: select
3204: pert.segment4 naic_code
3205: from
3206: per_ca_ee_report_lines pert
3207: where
3208: pert.request_id = p_request_id and
3209: pert.context = 'FORM12';
3210:
3235: per_people_f ppf,
3236: per_pay_proposals_v2 pppv,
3237: per_person_types ppt,
3238: hr_locations hl1,
3239: per_ca_ee_report_lines pert,
3240: hr_lookups hl2,
3241: hr_soft_coding_keyflex hsck,
3242: per_pay_bases ppb
3243: where
3295: (
3296: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3297: hsck.segment6 is null and
3298: hsck.segment1 in (select segment3
3299: from per_ca_ee_report_lines
3300: where request_id = p_request_id and
3301: context = 'FORM13' and
3302: segment1 = 'NAIC' and
3303: segment2 = v_naic_code)
3315: per_people_f ppf,
3316: per_pay_proposals_v2 pppv,
3317: per_person_types ppt,
3318: hr_locations hl1,
3319: per_ca_ee_report_lines pert,
3320: hr_lookups hl2,
3321: hr_soft_coding_keyflex hsck,
3322: per_pay_bases ppb
3323: where
3373: (
3374: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3375: hsck.segment6 is null and
3376: hsck.segment1 in (select segment3
3377: from per_ca_ee_report_lines
3378: where request_id = p_request_id and
3379: context = 'FORM13' and
3380: segment1 = 'NAIC' and
3381: segment2 = v_naic_code)
3427: per_people_f ppf,
3428: per_pay_proposals_v2 pppv,
3429: per_person_types ppt,
3430: hr_locations hl1,
3431: per_ca_ee_report_lines pert,
3432: hr_lookups hl2,
3433: hr_soft_coding_keyflex hsck,
3434: per_pay_bases ppb
3435: where
3491: (
3492: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3493: hsck.segment6 is null and
3494: hsck.segment1 in (select segment3
3495: from per_ca_ee_report_lines
3496: where request_id = p_request_id and
3497: context = 'FORM13' and
3498: segment1 = 'NAIC' and
3499: segment2 = v_naic_code)
3511: per_people_f ppf,
3512: per_pay_proposals_v2 pppv,
3513: per_person_types ppt,
3514: hr_locations hl1,
3515: per_ca_ee_report_lines pert,
3516: hr_lookups hl2,
3517: hr_soft_coding_keyflex hsck,
3518: per_pay_bases ppb
3519: where
3571: (
3572: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3573: hsck.segment6 is null and
3574: hsck.segment1 in (select segment3
3575: from per_ca_ee_report_lines
3576: where request_id = p_request_id and
3577: context = 'FORM13' and
3578: segment1 = 'NAIC' and
3579: segment2 = v_naic_code)
3594: v_min_range || ' - ' || v_max_range)
3595: max_min_range
3596:
3597: from
3598: per_ca_ee_report_lines
3599: where
3600: request_id = p_request_id and
3601: context=decode(pc,1,'FORM14','FORM13') and
3602: segment1 = decode(pc,1,'PROVINCE','CMA') and
3607: segment2,
3608: segment4,
3609: segment3
3610: from
3611: per_ca_ee_report_lines
3612: where
3613: request_id = p_request_id and
3614: context = 'FORM3' and
3615: segment1 = decode(pc,1,'PROVINCE','CMA') and
3631: sum(to_number(segment14)) segment14,
3632: sum(to_number(segment15)) segment15,
3633: sum(to_number(segment16)) segment16
3634: from
3635: per_ca_ee_report_lines
3636: where
3637: request_id = p_request_id and
3638: context = 'FORM3' and
3639: segment1 = 'PROVINCE' and
3800: hr_utility.trace('v_meaning = '|| v_meaning );
3801:
3802: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
3803:
3804: insert into per_ca_ee_report_lines
3805: ( request_id,
3806: line_number,
3807: context,
3808: segment1,
3853: prev_x = x and
3854: prev_sex <> v_sex then
3855:
3856: if v_sex = 'M' then
3857: update per_ca_ee_report_lines set
3858: segment5 = segment5 + nvl(v_count,0),
3859: segment7 = nvl(v_count,0)
3860: where
3861: request_id=p_request_id and
3870: segment21 = v_naic_code;
3871:
3872: elsif v_sex = 'F' then
3873:
3874: update per_ca_ee_report_lines set
3875: segment5=segment5 + nvl(v_count,0),
3876: segment6=nvl(v_count,0)
3877: where
3878: request_id=p_request_id and
3930:
3931: hr_utility.trace('Form3: Updation Designated Grp: k = 1. ');
3932: if l.sex = 'F' then
3933:
3934: update per_ca_ee_report_lines set
3935: segment8=nvl(segment8,0) + nvl(l.count,0),
3936: segment9=nvl(l.count,0)
3937: where
3938: request_id = p_request_id and
3947: segment21 = v_naic_code;
3948:
3949: elsif l.sex = 'M' then
3950:
3951: update per_ca_ee_report_lines set
3952: segment8 = nvl(segment8,0) + nvl(l.count,0),
3953: segment10 = nvl(l.count,0)
3954: where
3955: request_id = p_request_id and
3970: hr_utility.trace('Form3: Updation Designated Grp: k = 2. ');
3971:
3972: if l.sex = 'F' then
3973:
3974: update per_ca_ee_report_lines set
3975: segment11=nvl(segment11,0) + nvl(l.count,0),
3976: segment12=nvl(l.count,0)
3977: where
3978: request_id=p_request_id and
3987: segment21 = v_naic_code;
3988:
3989: else
3990:
3991: update per_ca_ee_report_lines set
3992: segment11=nvl(segment11,0) + nvl(l.count,0),
3993: segment13=nvl(l.count,0)
3994: where
3995: request_id=p_request_id and
4012: if l.sex = 'F' then
4013:
4014: hr_utility.trace('Form3: Updation Designated Grp: k = 3. F ');
4015:
4016: update per_ca_ee_report_lines set
4017: segment14=nvl(segment14,0) + nvl(l.count,0),
4018: segment16=nvl(l.count,0)
4019: where
4020: request_id=p_request_id and
4037: -- || v_employment_category);
4038: --hr_utility.trace('Form3: v_fr_min_range: ' || to_char(v_fr_min_range));
4039: --hr_utility.trace('Form3: v_fr_max_range: ' || to_char(v_fr_max_range));
4040:
4041: update per_ca_ee_report_lines set
4042: segment14=nvl(segment14,0) + nvl(l.count,0),
4043: segment15=nvl(l.count,0)
4044: where
4045: request_id=p_request_id and
4202: loop
4203:
4204: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
4205:
4206: insert into per_ca_ee_report_lines
4207: ( request_id,
4208: line_number,
4209: context,
4210: segment1,
4256: for count_national in cur_count_national loop
4257:
4258: hr_utility.trace('Form3: cur_count_national. ');
4259:
4260: insert into per_ca_ee_report_lines
4261: (request_id,
4262: line_number,
4263: context,
4264: segment1,
4327: cursor cur_naic is
4328: select
4329: pert.segment4 naic_code
4330: from
4331: per_ca_ee_report_lines pert
4332: where
4333: pert.request_id = p_request_id and
4334: pert.context = 'FORM12';
4335:
4356: per_assignments_f paf,
4357: per_people_f ppf,
4358: per_person_types ppt,
4359: hr_locations hl1,
4360: per_ca_ee_report_lines pert,
4361: hr_lookups hl2,
4362: hr_soft_coding_keyflex hsck
4363: where
4364: hl.lookup_type='EEOG' and
4406: (
4407: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4408: hsck.segment6 is null and
4409: hsck.segment1 in (select segment3
4410: from per_ca_ee_report_lines
4411: where request_id = p_request_id and
4412: context = 'FORM13' and
4413: segment1 = 'NAIC' and
4414: segment2 = v_naic_code)
4436: per_assignments_f paf,
4437: per_people_f ppf,
4438: per_person_types ppt,
4439: hr_locations hl1,
4440: per_ca_ee_report_lines pert,
4441: hr_lookups hl2,
4442: hr_soft_coding_keyflex hsck
4443: where
4444: hl.lookup_type='EEOG' and
4486: (
4487: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4488: hsck.segment6 is null and
4489: hsck.segment1 in (select segment3
4490: from per_ca_ee_report_lines
4491: where request_id = p_request_id and
4492: context = 'FORM13' and
4493: segment1 = 'NAIC' and
4494: segment2 = v_naic_code)
4550: per_assignments_f paf,
4551: per_people_f ppf,
4552: per_person_types ppt,
4553: hr_locations hl1,
4554: per_ca_ee_report_lines pert,
4555: hr_lookups hl2,
4556: hr_soft_coding_keyflex hsck
4557: where
4558: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4603: (
4604: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4605: hsck.segment6 is null and
4606: hsck.segment1 in (select segment3
4607: from per_ca_ee_report_lines
4608: where request_id = p_request_id and
4609: context = 'FORM13' and
4610: segment1 = 'NAIC' and
4611: segment2 = v_naic_code)
4633: per_assignments_f paf,
4634: per_people_f ppf,
4635: per_person_types ppt,
4636: hr_locations hl1,
4637: per_ca_ee_report_lines pert,
4638: hr_lookups hl2,
4639: hr_soft_coding_keyflex hsck
4640: where
4641: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4686: (
4687: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4688: hsck.segment6 is null and
4689: hsck.segment1 in (select segment3
4690: from per_ca_ee_report_lines
4691: where request_id = p_request_id and
4692: context = 'FORM13' and
4693: segment1 = 'NAIC' and
4694: segment2 = v_naic_code)
4720: segment2,
4721: v_meaning,
4722: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
4723: from
4724: per_ca_ee_report_lines where
4725: request_id=p_request_id and
4726: context='FORM14' and
4727: segment1='PROVINCE' and
4728: segment3 <> '0'
4731: segment2,
4732: segment3,
4733: segment4
4734: from
4735: per_ca_ee_report_lines
4736: where
4737: request_id=p_request_id and
4738: context='FORM4' and
4739: segment1='PROVINCE'and
4755: sum(to_number(segment14)) segment14,
4756: sum(to_number(segment15)) segment15,
4757: sum(to_number(segment16)) segment16
4758: from
4759: per_ca_ee_report_lines
4760: where
4761: request_id = p_request_id and
4762: context = 'FORM4' and
4763: segment1 = 'PROVINCE' and
4795: (ltrim(rtrim(prev_employment_category)) <> ltrim(rtrim(v_employment_category)))) then
4796:
4797: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
4798:
4799: insert into per_ca_ee_report_lines
4800: (request_id,
4801: line_number,
4802: context,
4803: segment1,
4847: prev_sex <> v_sex then
4848:
4849: if v_sex = 'M' then
4850:
4851: update per_ca_ee_report_lines set
4852: segment7=nvl(v_count,0),
4853: segment5=segment5 + nvl(v_count,0)
4854: where request_id=p_request_id and
4855: line_number=per_ca_ee_extract_pkg.k and
4861: segment21 = v_naic_code;
4862:
4863: elsif v_sex = 'F' then
4864:
4865: update per_ca_ee_report_lines set
4866: segment6=nvl(v_count,0),
4867: segment5=segment5 + nvl(v_count,0)
4868: where request_id=p_request_id and
4869: line_number=per_ca_ee_extract_pkg.k and
4903: close cur_meaning;
4904:
4905: if i = 1 then
4906: if v_sex = 'M' then
4907: update per_ca_ee_report_lines set
4908: segment8 = nvl(segment8,0) + nvl(v_count,0),
4909: segment10 = nvl(v_count,0)
4910: where
4911: request_id = p_request_id and
4914: segment2 = v_province_name and
4915: segment3 = v_meaning and
4916: segment4 = v_employment_category;
4917: elsif v_sex = 'F' then
4918: update per_ca_ee_report_lines set
4919: segment8 = nvl(segment8,0) + nvl(v_count,0),
4920: segment9 = nvl(v_count,0)
4921: where
4922: request_id = p_request_id and
4927: segment4 = v_employment_category;
4928: end if;
4929: elsif i = 2 then
4930: if v_sex = 'M' then
4931: update per_ca_ee_report_lines set
4932: segment11 = nvl(segment11,0) + nvl(v_count,0),
4933: segment13 = nvl(v_count,0)
4934: where
4935: request_id = p_request_id and
4938: segment2 = v_province_name and
4939: segment3 = v_meaning and
4940: segment4 = v_employment_category;
4941: elsif v_sex = 'F' then
4942: update per_ca_ee_report_lines set
4943: segment11 = nvl(segment11,0) + nvl(v_count,0),
4944: segment12 = nvl(v_count,0)
4945: where
4946: request_id = p_request_id and
4951: segment4 = v_employment_category;
4952: end if;
4953: elsif i = 3 then
4954: if v_sex = 'M' then
4955: update per_ca_ee_report_lines set
4956: segment14 = nvl(segment14,0) + nvl(v_count,0),
4957: segment16 = nvl(v_count,0)
4958: where
4959: request_id = p_request_id and
4962: segment2 = v_province_name and
4963: segment3 = v_meaning and
4964: segment4 = v_employment_category;
4965: elsif v_sex = 'F' then
4966: update per_ca_ee_report_lines set
4967: segment14 = nvl(segment14,0) + nvl(v_count,0),
4968: segment15 = nvl(v_count,0)
4969: where
4970: request_id = p_request_id and
4990: for x in cur_notfound(emp_cat) loop
4991:
4992: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
4993:
4994: insert into per_ca_ee_report_lines
4995: (request_id,
4996: line_number,
4997: context,
4998: segment1,
5045: for count_national in cur_count_national loop
5046:
5047: hr_utility.trace('Form4: cur_count_national. ');
5048:
5049: insert into per_ca_ee_report_lines
5050: (request_id,
5051: line_number,
5052: context,
5053: segment1,
5117: cursor cur_naic is
5118: select
5119: pert.segment4 naic_code
5120: from
5121: per_ca_ee_report_lines pert
5122: where
5123: pert.request_id = p_request_id and
5124: pert.context = 'FORM12';
5125:
5146: per_assignments_f paf,
5147: per_people_f ppf,
5148: per_person_types ppt,
5149: hr_locations hl1,
5150: per_ca_ee_report_lines pert,
5151: hr_lookups hl2,
5152: hr_soft_coding_keyflex hsck
5153: where
5154: hl.lookup_type='EEOG' and
5192: (
5193: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5194: hsck.segment6 is null and
5195: hsck.segment1 in (select segment3
5196: from per_ca_ee_report_lines
5197: where request_id = p_request_id and
5198: context = 'FORM13' and
5199: segment1 = 'NAIC' and
5200: segment2 = v_naic_code)
5230: per_assignments_f paf,
5231: per_people_f ppf,
5232: per_person_types ppt,
5233: hr_locations hl1,
5234: per_ca_ee_report_lines pert,
5235: hr_lookups hl2,
5236: hr_soft_coding_keyflex hsck
5237: where
5238: hl.lookup_type='EEOG' and
5276: (
5277: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5278: hsck.segment6 is null and
5279: hsck.segment1 in (select segment3
5280: from per_ca_ee_report_lines
5281: where request_id = p_request_id and
5282: context = 'FORM13' and
5283: segment1 = 'NAIC' and
5284: segment2 = v_naic_code)
5347: per_assignments_f paf,
5348: per_people_f ppf,
5349: per_person_types ppt,
5350: hr_locations hl1,
5351: per_ca_ee_report_lines pert,
5352: hr_lookups hl2,
5353: hr_soft_coding_keyflex hsck
5354: where
5355: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5395: (
5396: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5397: hsck.segment6 is null and
5398: hsck.segment1 in (select segment3
5399: from per_ca_ee_report_lines
5400: where request_id = p_request_id and
5401: context = 'FORM13' and
5402: segment1 = 'NAIC' and
5403: segment2 = v_naic_code)
5433: per_assignments_f paf,
5434: per_people_f ppf,
5435: per_person_types ppt,
5436: hr_locations hl1,
5437: per_ca_ee_report_lines pert,
5438: hr_lookups hl2,
5439: hr_soft_coding_keyflex hsck
5440: where
5441: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5481: (
5482: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5483: hsck.segment6 is null and
5484: hsck.segment1 in (select segment3
5485: from per_ca_ee_report_lines
5486: where request_id = p_request_id and
5487: context = 'FORM13' and
5488: segment1 = 'NAIC' and
5489: segment2 = v_naic_code)
5523: segment2,
5524: v_meaning,
5525: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
5526: from
5527: per_ca_ee_report_lines where
5528: request_id=p_request_id and
5529: context='FORM14' and
5530: segment1='PROVINCE' and
5531: segment3 <> '0'
5534: segment2,
5535: segment3,
5536: segment4
5537: from
5538: per_ca_ee_report_lines
5539: where
5540: request_id=p_request_id and
5541: context='FORM5' and
5542: segment1='PROVINCE'and
5558: sum(to_number(segment14)) segment14,
5559: sum(to_number(segment15)) segment15,
5560: sum(to_number(segment16)) segment16
5561: from
5562: per_ca_ee_report_lines
5563: where
5564: request_id = p_request_id and
5565: context = 'FORM5' and
5566: segment1 = 'PROVINCE' and
5586: per_assignments_f paf,
5587: per_people_f ppf,
5588: per_person_types ppt,
5589: hr_locations hl1,
5590: per_ca_ee_report_lines pert,
5591: hr_lookups hl2,
5592: hr_soft_coding_keyflex hsck,
5593: per_pay_proposals_v2 pppv
5594: where
5633: (
5634: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5635: hsck.segment6 is null and
5636: hsck.segment1 in (select segment3
5637: from per_ca_ee_report_lines
5638: where request_id = p_request_id and
5639: context = 'FORM13' and
5640: segment1 = 'NAIC' and
5641: segment2 = v_naic_code)
5664: per_assignments_f paf,
5665: per_people_f ppf,
5666: per_person_types ppt,
5667: hr_locations hl1,
5668: per_ca_ee_report_lines pert,
5669: hr_lookups hl2,
5670: hr_soft_coding_keyflex hsck,
5671: per_pay_proposals_v2 pppv
5672: where
5711: (
5712: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5713: hsck.segment6 is null and
5714: hsck.segment1 in (select segment3
5715: from per_ca_ee_report_lines
5716: where request_id = p_request_id and
5717: context = 'FORM13' and
5718: segment1 = 'NAIC' and
5719: segment2 = v_naic_code)
5754: per_assignments_f paf,
5755: per_people_f ppf,
5756: per_person_types ppt,
5757: hr_locations hl1,
5758: per_ca_ee_report_lines pert,
5759: hr_lookups hl2,
5760: hr_soft_coding_keyflex hsck,
5761: per_pay_proposals_v2 pppv
5762: where
5801: (
5802: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5803: hsck.segment6 is null and
5804: hsck.segment1 in (select segment3
5805: from per_ca_ee_report_lines
5806: where request_id = p_request_id and
5807: context = 'FORM13' and
5808: segment1 = 'NAIC' and
5809: segment2 = v_naic_code)
5832: per_assignments_f paf,
5833: per_people_f ppf,
5834: per_person_types ppt,
5835: hr_locations hl1,
5836: per_ca_ee_report_lines pert,
5837: hr_lookups hl2,
5838: hr_soft_coding_keyflex hsck,
5839: per_pay_proposals_v2 pppv
5840: where
5879: (
5880: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5881: hsck.segment6 is null and
5882: hsck.segment1 in (select segment3
5883: from per_ca_ee_report_lines
5884: where request_id = p_request_id and
5885: context = 'FORM13' and
5886: segment1 = 'NAIC' and
5887: segment2 = v_naic_code)
5921: per_assignments_f paf,
5922: per_people_f ppf,
5923: per_person_types ppt,
5924: hr_locations hl1,
5925: per_ca_ee_report_lines pert,
5926: hr_lookups hl2,
5927: hr_soft_coding_keyflex hsck,
5928: per_pay_proposals_v2 pppv
5929: where
5971: (
5972: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5973: hsck.segment6 is null and
5974: hsck.segment1 in (select segment3
5975: from per_ca_ee_report_lines
5976: where request_id = p_request_id and
5977: context = 'FORM13' and
5978: segment1 = 'NAIC' and
5979: segment2 = v_naic_code)
6002: per_assignments_f paf,
6003: per_people_f ppf,
6004: per_person_types ppt,
6005: hr_locations hl1,
6006: per_ca_ee_report_lines pert,
6007: hr_lookups hl2,
6008: hr_soft_coding_keyflex hsck,
6009: per_pay_proposals_v2 pppv
6010: where
6052: (
6053: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
6054: hsck.segment6 is null and
6055: hsck.segment1 in (select segment3
6056: from per_ca_ee_report_lines
6057: where request_id = p_request_id and
6058: context = 'FORM13' and
6059: segment1 = 'NAIC' and
6060: segment2 = v_naic_code)
6090: sum(to_number(segment13)) segment13,
6091: sum(to_number(segment14)) segment14,
6092: sum(to_number(segment15)) segment15
6093: from
6094: per_ca_ee_report_lines
6095: where
6096: request_id = p_request_id and
6097: context = 'FORM5P' and
6098: segment1 = 'PROVINCE' and
6103: select
6104: segment2,
6105: decode(emp_cat,1,'FR','2','PR',3,'PT') emp_category
6106: from
6107: per_ca_ee_report_lines
6108: where
6109: request_id = p_request_id and
6110: segment3 <> '0' and
6111: context = 'FORM14'
6113: select
6114: segment2,
6115: segment3
6116: from
6117: per_ca_ee_report_lines
6118: where
6119: request_id = p_request_id and
6120: context = 'FORM5P' and
6121: segment1 = 'PROVINCE' ;
6148: ltrim(rtrim(v_employment_category)))) then
6149:
6150: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
6151:
6152: insert into per_ca_ee_report_lines
6153: (request_id,
6154: line_number,
6155: context,
6156: segment1,
6199: prev_sex <> v_sex then
6200:
6201: if v_sex = 'M' then
6202:
6203: update per_ca_ee_report_lines set
6204: segment7=nvl(v_count,0),
6205: segment5=segment5 + nvl(v_count,0)
6206: where request_id=p_request_id and
6207: line_number = per_ca_ee_extract_pkg.k and
6213: segment21=v_naic_code;
6214:
6215: elsif v_sex = 'F' then
6216:
6217: update per_ca_ee_report_lines set
6218: segment6=nvl(v_count,0),
6219: segment5=segment5 + nvl(v_count,0)
6220: where request_id=p_request_id and
6221: line_number = per_ca_ee_extract_pkg.k and
6258: if i = 1 then
6259:
6260: if v_sex = 'M' then
6261:
6262: update per_ca_ee_report_lines set
6263: segment8 = nvl(segment8,0) + nvl(v_count,0),
6264: segment10 = nvl(v_count,0)
6265: where
6266: request_id = p_request_id and
6272: segment21 = v_naic_code;
6273:
6274: elsif v_sex = 'F' then
6275:
6276: update per_ca_ee_report_lines set
6277: segment8 = nvl(segment8,0) + nvl(v_count,0),
6278: segment9 = nvl(v_count,0)
6279: where
6280: request_id = p_request_id and
6290: elsif i = 2 then
6291:
6292: if v_sex = 'M' then
6293:
6294: update per_ca_ee_report_lines set
6295: segment11 = nvl(segment11,0) + nvl(v_count,0),
6296: segment13 = nvl(v_count,0)
6297: where
6298: request_id = p_request_id and
6304: segment21 = v_naic_code;
6305:
6306: elsif v_sex = 'F' then
6307:
6308: update per_ca_ee_report_lines set
6309: segment11 = nvl(segment11,0) + nvl(v_count,0),
6310: segment12 = nvl(v_count,0)
6311: where
6312: request_id = p_request_id and
6322: elsif i = 3 then
6323:
6324: if v_sex = 'M' then
6325:
6326: update per_ca_ee_report_lines set
6327: segment14 = nvl(segment14,0) + nvl(v_count,0),
6328: segment16 = nvl(v_count,0)
6329: where
6330: request_id = p_request_id and
6336: segment21 = v_naic_code;
6337:
6338: elsif v_sex = 'F' then
6339:
6340: update per_ca_ee_report_lines set
6341: segment14 = nvl(segment14,0) + nvl(v_count,0),
6342: segment15 = nvl(v_count,0)
6343: where
6344: request_id = p_request_id and
6365: for x in cur_notfound(emp_cat) loop
6366:
6367: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
6368:
6369: insert into per_ca_ee_report_lines
6370: (request_id,
6371: line_number,
6372: context,
6373: segment1,
6446:
6447: hr_utility.trace('Form5P: cur_total_promotions : Inside If');
6448: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
6449:
6450: insert into per_ca_ee_report_lines
6451: (request_id,
6452: line_number,
6453: context,
6454: segment1,
6495: prev_sex <> v_sex then
6496:
6497: if v_sex = 'M' then
6498:
6499: update per_ca_ee_report_lines set
6500: segment5=nvl(v_count,0),
6501: segment4=segment4 + nvl(v_count,0)
6502: where request_id=p_request_id and
6503: line_number = per_ca_ee_extract_pkg.k and
6508: segment21=v_naic_code;
6509:
6510: elsif v_sex = 'F' then
6511:
6512: update per_ca_ee_report_lines set
6513: segment6=nvl(v_count,0),
6514: segment4=segment4 + nvl(v_count,0)
6515: where request_id=p_request_id and
6516: line_number = per_ca_ee_extract_pkg.k and
6550: if i = 1 then
6551:
6552: if v_sex = 'M' then
6553:
6554: update per_ca_ee_report_lines set
6555: segment7 = nvl(segment7,0) + nvl(v_count,0),
6556: segment8 = nvl(v_count,0)
6557: where
6558: request_id = p_request_id and
6563: segment21 = v_naic_code;
6564:
6565: elsif v_sex = 'F' then
6566:
6567: update per_ca_ee_report_lines set
6568: segment7 = nvl(segment7,0) + nvl(v_count,0),
6569: segment9 = nvl(v_count,0)
6570: where
6571: request_id = p_request_id and
6580: elsif i = 2 then
6581:
6582: if v_sex = 'M' then
6583:
6584: update per_ca_ee_report_lines set
6585: segment10 = nvl(segment10,0) + nvl(v_count,0),
6586: segment11 = nvl(v_count,0)
6587: where
6588: request_id = p_request_id and
6593: segment21 = v_naic_code;
6594:
6595: elsif v_sex = 'F' then
6596:
6597: update per_ca_ee_report_lines set
6598: segment10 = nvl(segment10,0) + nvl(v_count,0),
6599: segment12 = nvl(v_count,0)
6600: where
6601: request_id = p_request_id and
6610: elsif i = 3 then
6611:
6612: if v_sex = 'M' then
6613:
6614: update per_ca_ee_report_lines set
6615: segment13 = nvl(segment13,0) + nvl(v_count,0),
6616: segment14 = nvl(v_count,0)
6617: where
6618: request_id = p_request_id and
6623: segment21 = v_naic_code;
6624:
6625: elsif v_sex = 'F' then
6626:
6627: update per_ca_ee_report_lines set
6628: segment13 = nvl(segment13,0) + nvl(v_count,0),
6629: segment15 = nvl(v_count,0)
6630: where
6631: request_id = p_request_id and
6645:
6646: for i in 1..3 loop
6647: for j in cur_notfound_promotions(i) loop
6648:
6649: insert into per_ca_ee_report_lines
6650: (request_id,
6651: line_number,
6652: context,
6653: segment1,
6690: end loop; -- End loop for designated group members.
6691:
6692: for i in cur_count_national_promotions loop
6693:
6694: insert into per_ca_ee_report_lines
6695: (request_id,
6696: line_number,
6697: context,
6698: segment1,
6735: for count_national in cur_count_national loop
6736:
6737: hr_utility.trace('Form5: cur_count_national. ');
6738:
6739: insert into per_ca_ee_report_lines
6740: (request_id,
6741: line_number,
6742: context,
6743: segment1,
6804: declare
6805:
6806: cursor cur_naic is
6807: select pert.segment4 naic_code
6808: from per_ca_ee_report_lines pert
6809: where pert.request_id = p_request_id
6810: and pert.context = 'FORM12';
6811:
6812: v_naic_code hr_lookups.lookup_code%TYPE;
6852: from
6853: per_assignments_f paf,
6854: per_people_f ppf,
6855: hr_locations hl1,
6856: per_ca_ee_report_lines pert,
6857: hr_lookups hl2,
6858: hr_soft_coding_keyflex hsck
6859: where job_exists(paf.job_id) is not null
6860: and paf.primary_flag = 'Y'
6913: (
6914: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
6915: hsck.segment6 is null and
6916: hsck.segment1 in (select segment3
6917: from per_ca_ee_report_lines
6918: where request_id = p_request_id
6919: and context = 'FORM13'
6920: and segment1 = 'NAIC'
6921: and segment2 = v_naic_code)
6936: from
6937: per_assignments_f paf,
6938: per_people_f ppf,
6939: hr_locations hl1,
6940: per_ca_ee_report_lines pert,
6941: hr_lookups hl2,
6942: hr_soft_coding_keyflex hsck
6943: where
6944: job_exists(paf.job_id) is not null
7000: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
7001: hsck.segment6 is null and
7002: hsck.segment1 in
7003: (select segment3
7004: from per_ca_ee_report_lines
7005: where request_id = p_request_id
7006: and context = 'FORM13'
7007: and segment1 = 'NAIC'
7008: and segment2 = v_naic_code)
7041: segment2,
7042: v_meaning,
7043: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
7044: from
7045: per_ca_ee_report_lines where
7046: request_id=p_request_id and
7047: context='FORM14' and
7048: segment1='PROVINCE' and
7049: segment3 <> '0'
7052: segment2,
7053: segment3,
7054: segment4
7055: from
7056: per_ca_ee_report_lines
7057: where
7058: request_id=p_request_id and
7059: context='FORM5' and
7060: segment1='PROVINCE'and
7076: sum(to_number(segment14)) segment14,
7077: sum(to_number(segment15)) segment15,
7078: sum(to_number(segment16)) segment16
7079: from
7080: per_ca_ee_report_lines
7081: where
7082: request_id = p_request_id and
7083: context = 'FORM5' and
7084: segment1 = 'PROVINCE' and
7106: from
7107: per_assignments_f paf,
7108: per_people_f ppf,
7109: hr_locations hl1,
7110: per_ca_ee_report_lines pert,
7111: hr_lookups hl2,
7112: hr_soft_coding_keyflex hsck
7113: where
7114: job_exists(paf.job_id) is not null
7168: (
7169: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
7170: hsck.segment6 is null and
7171: hsck.segment1 in (select segment3
7172: from per_ca_ee_report_lines
7173: where request_id = p_request_id and
7174: context = 'FORM13' and
7175: segment1 = 'NAIC' and
7176: segment2 = v_naic_code)
7190: from
7191: per_assignments_f paf,
7192: per_people_f ppf,
7193: hr_locations hl1,
7194: per_ca_ee_report_lines pert,
7195: hr_lookups hl2,
7196: hr_soft_coding_keyflex hsck
7197: where
7198: job_exists(paf.job_id) is not null
7254: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
7255: hsck.segment6 is null and
7256: hsck.segment1 in
7257: (select segment3
7258: from per_ca_ee_report_lines
7259: where request_id = p_request_id
7260: and context = 'FORM13'
7261: and segment1 = 'NAIC'
7262: and segment2 = v_naic_code)
7281: sum(to_number(segment13)) segment13,
7282: sum(to_number(segment14)) segment14,
7283: sum(to_number(segment15)) segment15
7284: from
7285: per_ca_ee_report_lines
7286: where
7287: request_id = p_request_id and
7288: context = 'FORM5P' and
7289: segment1 = 'PROVINCE' and
7294: select
7295: segment2,
7296: decode(emp_cat,1,'FR','2','PR',3,'PT') emp_category
7297: from
7298: per_ca_ee_report_lines
7299: where
7300: request_id = p_request_id and
7301: segment3 <> '0' and
7302: context = 'FORM14'
7304: select
7305: segment2,
7306: segment3
7307: from
7308: per_ca_ee_report_lines
7309: where
7310: request_id = p_request_id and
7311: context = 'FORM5P' and
7312: segment1 = 'PROVINCE' ;
7373: ltrim(rtrim(v_employment_category)))) then
7374:
7375: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
7376:
7377: insert into per_ca_ee_report_lines
7378: (request_id,
7379: line_number,
7380: context,
7381: segment1,
7424: prev_sex <> v_sex then
7425:
7426: if v_sex = 'M' then
7427:
7428: update per_ca_ee_report_lines set
7429: segment7=nvl(v_count,0),
7430: segment5=segment5 + nvl(v_count,0)
7431: where request_id=p_request_id and
7432: line_number = per_ca_ee_extract_pkg.k and
7438: segment21=v_naic_code;
7439:
7440: elsif v_sex = 'F' then
7441:
7442: update per_ca_ee_report_lines set
7443: segment6=nvl(v_count,0),
7444: segment5=segment5 + nvl(v_count,0)
7445: where request_id=p_request_id and
7446: line_number = per_ca_ee_extract_pkg.k and
7465: elsif i = 1 then
7466:
7467: if v_sex = 'M' then
7468:
7469: update per_ca_ee_report_lines set
7470: segment8 = nvl(segment8,0) + nvl(v_count,0),
7471: segment10 = nvl(v_count,0)
7472: where
7473: request_id = p_request_id and
7479: segment21 = v_naic_code;
7480:
7481: elsif v_sex = 'F' then
7482:
7483: update per_ca_ee_report_lines set
7484: segment8 = nvl(segment8,0) + nvl(v_count,0),
7485: segment9 = nvl(v_count,0)
7486: where
7487: request_id = p_request_id and
7497: elsif i = 2 then
7498:
7499: if v_sex = 'M' then
7500:
7501: update per_ca_ee_report_lines set
7502: segment11 = nvl(segment11,0) + nvl(v_count,0),
7503: segment13 = nvl(v_count,0)
7504: where
7505: request_id = p_request_id and
7511: segment21 = v_naic_code;
7512:
7513: elsif v_sex = 'F' then
7514:
7515: update per_ca_ee_report_lines set
7516: segment11 = nvl(segment11,0) + nvl(v_count,0),
7517: segment12 = nvl(v_count,0)
7518: where
7519: request_id = p_request_id and
7529: elsif i = 3 then
7530:
7531: if v_sex = 'M' then
7532:
7533: update per_ca_ee_report_lines set
7534: segment14 = nvl(segment14,0) + nvl(v_count,0),
7535: segment16 = nvl(v_count,0)
7536: where
7537: request_id = p_request_id and
7543: segment21 = v_naic_code;
7544:
7545: elsif v_sex = 'F' then
7546:
7547: update per_ca_ee_report_lines set
7548: segment14 = nvl(segment14,0) + nvl(v_count,0),
7549: segment15 = nvl(v_count,0)
7550: where
7551: request_id = p_request_id and
7573: for x in cur_notfound(emp_cat) loop
7574:
7575: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
7576:
7577: insert into per_ca_ee_report_lines
7578: (request_id,
7579: line_number,
7580: context,
7581: segment1,
7660:
7661: hr_utility.trace('Form5P: cur_total_promotions : Inside If');
7662: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
7663:
7664: insert into per_ca_ee_report_lines
7665: (request_id,
7666: line_number,
7667: context,
7668: segment1,
7709: prev_sex <> v_sex then
7710:
7711: if v_sex = 'M' then
7712:
7713: update per_ca_ee_report_lines set
7714: segment5=nvl(v_count,0),
7715: segment4=segment4 + nvl(v_count,0)
7716: where request_id=p_request_id and
7717: line_number = per_ca_ee_extract_pkg.k and
7722: segment21=v_naic_code;
7723:
7724: elsif v_sex = 'F' then
7725:
7726: update per_ca_ee_report_lines set
7727: segment6=nvl(v_count,0),
7728: segment4=segment4 + nvl(v_count,0)
7729: where request_id=p_request_id and
7730: line_number = per_ca_ee_extract_pkg.k and
7749:
7750:
7751: if v_sex = 'M' then
7752:
7753: update per_ca_ee_report_lines set
7754: segment7 = nvl(segment7,0) + nvl(v_count,0),
7755: segment8 = nvl(v_count,0)
7756: where
7757: request_id = p_request_id and
7762: segment21 = v_naic_code;
7763:
7764: elsif v_sex = 'F' then
7765:
7766: update per_ca_ee_report_lines set
7767: segment7 = nvl(segment7,0) + nvl(v_count,0),
7768: segment9 = nvl(v_count,0)
7769: where
7770: request_id = p_request_id and
7779: elsif i = 2 then
7780:
7781: if v_sex = 'M' then
7782:
7783: update per_ca_ee_report_lines set
7784: segment10 = nvl(segment10,0) + nvl(v_count,0),
7785: segment11 = nvl(v_count,0)
7786: where
7787: request_id = p_request_id and
7792: segment21 = v_naic_code;
7793:
7794: elsif v_sex = 'F' then
7795:
7796: update per_ca_ee_report_lines set
7797: segment10 = nvl(segment10,0) + nvl(v_count,0),
7798: segment12 = nvl(v_count,0)
7799: where
7800: request_id = p_request_id and
7809: elsif i = 3 then
7810:
7811: if v_sex = 'M' then
7812:
7813: update per_ca_ee_report_lines set
7814: segment13 = nvl(segment13,0) + nvl(v_count,0),
7815: segment14 = nvl(v_count,0)
7816: where
7817: request_id = p_request_id and
7822: segment21 = v_naic_code;
7823:
7824: elsif v_sex = 'F' then
7825:
7826: update per_ca_ee_report_lines set
7827: segment13 = nvl(segment13,0) + nvl(v_count,0),
7828: segment15 = nvl(v_count,0)
7829: where
7830: request_id = p_request_id and
7843:
7844: for i in 1..3 loop
7845: for j in cur_notfound_promotions(i) loop
7846:
7847: insert into per_ca_ee_report_lines
7848: (request_id,
7849: line_number,
7850: context,
7851: segment1,
7888: end loop; -- End loop for designated group members.
7889:
7890: for i in cur_count_national_promotions loop
7891:
7892: insert into per_ca_ee_report_lines
7893: (request_id,
7894: line_number,
7895: context,
7896: segment1,
7932: for count_national in cur_count_national loop
7933:
7934: hr_utility.trace('Form5: cur_count_national. ');
7935:
7936: insert into per_ca_ee_report_lines
7937: (request_id,
7938: line_number,
7939: context,
7940: segment1,
8002: cursor cur_naic is
8003: select
8004: pert.segment4 naic_code
8005: from
8006: per_ca_ee_report_lines pert
8007: where
8008: pert.request_id = p_request_id and
8009: pert.context = 'FORM12';
8010:
8070: (
8071: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8072: hsck.segment6 is null and
8073: hsck.segment1 in (select segment3
8074: from per_ca_ee_report_lines
8075: where request_id = p_request_id and
8076: context = 'FORM13' and
8077: segment1 = 'NAIC' and
8078: segment2 = v_naic_code)
8139: (
8140: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8141: hsck.segment6 is null and
8142: hsck.segment1 in (select segment3
8143: from per_ca_ee_report_lines
8144: where request_id = p_request_id and
8145: context = 'FORM13' and
8146: segment1 = 'NAIC' and
8147: segment2 = v_naic_code)
8246: (
8247: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8248: hsck.segment6 is null and
8249: hsck.segment1 in (select segment3
8250: from per_ca_ee_report_lines
8251: where request_id = p_request_id and
8252: context = 'FORM13' and
8253: segment1 = 'NAIC' and
8254: segment2 = v_naic_code)
8318: (
8319: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8320: hsck.segment6 is null and
8321: hsck.segment1 in (select segment3
8322: from per_ca_ee_report_lines
8323: where request_id = p_request_id and
8324: context = 'FORM13' and
8325: segment1 = 'NAIC' and
8326: segment2 = v_naic_code)
8352: segment2,
8353: v_meaning,
8354: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
8355: from
8356: per_ca_ee_report_lines where
8357: request_id=p_request_id and
8358: context='FORM14' and
8359: segment1='PROVINCE' and
8360: segment3 <> '0'
8363: segment2,
8364: segment3,
8365: segment4
8366: from
8367: per_ca_ee_report_lines
8368: where
8369: request_id=p_request_id and
8370: context='FORM6' and
8371: segment1='PROVINCE'and
8387: sum(to_number(segment14)) segment14,
8388: sum(to_number(segment15)) segment15,
8389: sum(to_number(segment16)) segment16
8390: from
8391: per_ca_ee_report_lines
8392: where
8393: request_id = p_request_id and
8394: context = 'FORM6' and
8395: segment1 = 'PROVINCE' and
8426: ltrim(rtrim(v_employment_category)))) then
8427:
8428: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
8429:
8430: insert into per_ca_ee_report_lines
8431: (request_id,
8432: line_number,
8433: context,
8434: segment1,
8480: prev_sex <> v_sex then
8481:
8482: if v_sex = 'M' then
8483:
8484: update per_ca_ee_report_lines set
8485: segment7=nvl(v_count,0),
8486: segment5=segment5 + nvl(v_count,0)
8487: where request_id=p_request_id and
8488: line_number = per_ca_ee_extract_pkg.k and
8494: segment21=v_naic_code;
8495:
8496: elsif v_sex = 'F' then
8497:
8498: update per_ca_ee_report_lines set
8499: segment6=nvl(v_count,0),
8500: segment5=segment5 + nvl(v_count,0)
8501: where request_id=p_request_id and
8502: line_number = per_ca_ee_extract_pkg.k and
8539: if i = 1 then
8540:
8541: if v_sex = 'M' then
8542:
8543: update per_ca_ee_report_lines set
8544: segment8 = nvl(segment8,0) + nvl(v_count,0),
8545: segment9 = 0,
8546: segment10 = nvl(v_count,0)
8547: where
8554: segment21 = v_naic_code;
8555:
8556: elsif v_sex = 'F' then
8557:
8558: update per_ca_ee_report_lines set
8559: segment8 = nvl(segment8,0) + nvl(v_count,0),
8560: segment9 = nvl(v_count,0),
8561: segment10 = 0
8562: where
8573: elsif i = 2 then
8574:
8575: if v_sex = 'M' then
8576:
8577: update per_ca_ee_report_lines set
8578: segment11 = nvl(segment11,0) + nvl(v_count,0),
8579: segment12 = 0,
8580: segment13 = nvl(v_count,0)
8581: where
8588: segment21 = v_naic_code;
8589:
8590: elsif v_sex = 'F' then
8591:
8592: update per_ca_ee_report_lines set
8593: segment11 = nvl(segment11,0) + nvl(v_count,0),
8594: segment12 = nvl(v_count,0),
8595: segment13 = 0
8596: where
8607: elsif i = 3 then
8608:
8609: if v_sex = 'M' then
8610:
8611: update per_ca_ee_report_lines set
8612: segment14 = nvl(segment14,0) + nvl(v_count,0),
8613: segment15 = 0,
8614: segment16 = nvl(v_count,0)
8615: where
8622: segment21 = v_naic_code;
8623:
8624: elsif v_sex = 'F' then
8625:
8626: update per_ca_ee_report_lines set
8627: segment14 = nvl(segment14,0) + nvl(v_count,0),
8628: segment15 = nvl(v_count,0),
8629: segment16 = 0
8630: where
8657: hr_utility.trace('Form6: cur_notfound' );
8658:
8659: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
8660:
8661: insert into per_ca_ee_report_lines
8662: (request_id,
8663: line_number,
8664: context,
8665: segment1,
8709: for count_national in cur_count_national loop
8710:
8711: hr_utility.trace('Form6: cur_count_national. ');
8712:
8713: insert into per_ca_ee_report_lines
8714: (request_id,
8715: line_number,
8716: context,
8717: segment1,
8772: segment13,
8773: segment14,
8774: segment15
8775: from
8776: per_ca_ee_report_lines
8777: where
8778: request_id=p_request_id and
8779: context='FORM11';
8780:
8786: select
8787: segment4 max_naic_code,
8788: max(to_number(segment3))
8789: from
8790: per_ca_ee_report_lines
8791: where
8792: request_id = p_request_id and
8793: context = 'FORM12' and
8794: segment1 = 'NAIC'
8800: cursor cur_less_than_max_naic is
8801: select
8802: segment4 naic_code
8803: from
8804: per_ca_ee_report_lines
8805: where request_id = p_request_id
8806: and to_number(segment3) < (select to_number(lookup_code)
8807: from pay_ca_legislation_info
8808: where lookup_type = 'EER1')
8834: segment18,
8835: segment19,
8836: segment20
8837: segment21
8838: from per_ca_ee_report_lines
8839: where
8840: request_id = p_request_id and
8841: context in ('FORM3','FORM4','FORM5','FORM6') and
8842: segment1 = 'NATIONAL' and
8859:
8860: if
8861: ( ((nvl(v_tot_pt,0)/(nvl(v_tot_fr,0) + nvl(v_tot_pr,0) + nvl(v_tot_pt,0))) * 100) >= 20 ) then
8862:
8863: update per_ca_ee_report_lines set
8864: segment20 = 'Y'
8865: WHERE
8866: request_id=p_request_id and
8867: context in ('FORM2','FORM3','FORM4','FORM5','FORM6');
8866: request_id=p_request_id and
8867: context in ('FORM2','FORM3','FORM4','FORM5','FORM6');
8868: else
8869:
8870: update per_ca_ee_report_lines set
8871: segment20 = decode(segment3,'PT','N','Y')
8872: WHERE
8873: request_id=p_request_id and
8874: context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
8873: request_id=p_request_id and
8874: context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
8875: segment1 = 'NATIONAL';
8876:
8877: update per_ca_ee_report_lines set
8878: segment20 = decode(segment4,'PT','N','Y')
8879: WHERE
8880: request_id=p_request_id and
8881: context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
8905: for i in cur_not_max_naic_data loop
8906:
8907: hr_utility.trace('UPDATE_REC: Form3 - 6' );
8908:
8909: update per_ca_ee_report_lines
8910: set
8911: segment4 = segment4 + i.segment4,
8912: segment5 = segment5 + i.segment5,
8913: segment6 = segment6 + i.segment6,
8931: hr_utility.trace('UPDATE_REC: Form3 - 6 End' );
8932:
8933: end loop; -- End loop cur_not_max_naic_data
8934:
8935: delete from per_ca_ee_report_lines
8936: where request_id = p_request_id and
8937: segment21 = v_not_max_naic;
8938: end loop;
8939: