[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
2176: per_people_f ppf,
2177: per_assignments_f paf,
2178: hr_locations hl1,
2179: per_person_types ppt,
2180: per_ca_ee_report_lines pert,
2181: hr_lookups hl2,
2182: hr_soft_coding_keyflex hsck,
2183: per_pay_bases ppb
2184: where
2228: hsck.segment6 is not null and
2229: (hsck.segment6 = v_naic_code
2230: OR
2231: hsck.segment6 in ( select segment4
2232: from per_ca_ee_report_lines
2233: where request_id = p_request_id and
2234: context = 'FORM12' and
2235: to_number(segment3) < to_number(v_leg_info) and
2236: v_max_naic_flag = 'Y')
2239: OR
2240: (
2241: hsck.segment6 is null and
2242: hsck.segment1 in (select segment3
2243: from per_ca_ee_report_lines
2244: where request_id = p_request_id and
2245: context = 'FORM13' and
2246: segment1 = 'NAIC' and
2247: (
2248: segment2 = v_naic_code
2249: OR
2250: segment2 in
2251: ( select segment4
2252: from per_ca_ee_report_lines
2253: where request_id = p_request_id and
2254: context = 'FORM12' and
2255: to_number(segment3)
2256: < to_number(v_leg_info) and
2276: per_people_f ppf,
2277: per_assignments_f paf,
2278: hr_locations hl1,
2279: per_person_types ppt,
2280: per_ca_ee_report_lines pert,
2281: hr_lookups hl2,
2282: hr_soft_coding_keyflex hsck,
2283: per_pay_bases ppb
2284: where
2329: hsck.segment6 is not null and
2330: (hsck.segment6 = v_naic_code
2331: OR
2332: hsck.segment6 in ( select segment4
2333: from per_ca_ee_report_lines
2334: where request_id = p_request_id and
2335: context = 'FORM12' and
2336: to_number(segment3) < to_number(v_leg_info) and
2337: v_max_naic_flag = 'Y')
2340: OR
2341: (
2342: hsck.segment6 is null and
2343: hsck.segment1 in (select segment3
2344: from per_ca_ee_report_lines
2345: where request_id = p_request_id and
2346: context = 'FORM13' and
2347: segment1 = 'NAIC' and
2348: (
2349: segment2 = v_naic_code
2350: OR
2351: segment2 in
2352: ( select segment4
2353: from per_ca_ee_report_lines
2354: where request_id = p_request_id and
2355: context = 'FORM12' and
2356: to_number(segment3)
2357: < to_number(v_leg_info) and
2466: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2467: hsck.segment6 is not null and
2468: hsck.segment6 = v_naic_code OR
2469: hsck.segment6 in ( select segment4
2470: from per_ca_ee_report_lines
2471: where request_id = p_request_id and
2472: context = 'FORM12' and
2473: to_number(segment3) < to_number(v_leg_info) and
2474: v_max_naic_flag = 'Y')
2477: (
2478: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2479: hsck.segment6 is null and
2480: hsck.segment1 in (select segment3
2481: from per_ca_ee_report_lines
2482: where request_id = p_request_id and
2483: context = 'FORM13' and
2484: segment1 = 'NAIC' and
2485: segment2 = v_naic_code OR
2484: segment1 = 'NAIC' and
2485: segment2 = v_naic_code OR
2486: segment2 in
2487: ( select segment4
2488: from per_ca_ee_report_lines
2489: where request_id = p_request_id and
2490: context = 'FORM12' and
2491: to_number(segment3)
2492: < to_number(v_leg_info) and
2575: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2576: hsck.segment6 is not null and
2577: hsck.segment6 = v_naic_code OR
2578: hsck.segment6 in ( select segment4
2579: from per_ca_ee_report_lines
2580: where request_id = p_request_id and
2581: context = 'FORM12' and
2582: to_number(segment3) < to_number(v_leg_info) and
2583: v_max_naic_flag = 'Y')
2586: (
2587: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
2588: hsck.segment6 is null and
2589: hsck.segment1 in (select segment3
2590: from per_ca_ee_report_lines
2591: where request_id = p_request_id and
2592: context = 'FORM13' and
2593: segment1 = 'NAIC' and
2594: segment2 = v_naic_code OR
2593: segment1 = 'NAIC' and
2594: segment2 = v_naic_code OR
2595: segment2 in
2596: ( select segment4
2597: from per_ca_ee_report_lines
2598: where request_id = p_request_id and
2599: context = 'FORM12' and
2600: to_number(segment3)
2601: < to_number(v_leg_info) and
2896: ) then
2897:
2898: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
2899:
2900: insert into per_ca_ee_report_lines
2901: ( request_id,
2902: line_number,
2903: context,
2904: segment1,
2956: prev_sex <> v_sex then
2957:
2958: if v_sex = 'M' 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
2968: segment21 = v_naic_code;
2969:
2970: elsif v_sex = 'F' then
2971:
2972: update per_ca_ee_report_lines set
2973: segment7=segment7 + nvl(v_count,0),
2974: segment9=nvl(v_count,0)
2975: where request_id=p_request_id and
2976: line_number=per_ca_ee_extract_pkg.k and
2996: if j_flag = 'F' then
2997:
2998: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
2999:
3000: insert into per_ca_ee_report_lines
3001: ( request_id,
3002: line_number,
3003: context,
3004: segment1,
3065: if k = 1 then
3066:
3067: if l.sex = 'F' then
3068:
3069: update per_ca_ee_report_lines set
3070: segment10=nvl(segment10,0) + nvl(l.count,0),
3071: segment11=nvl(l.count,0)
3072: where
3073: request_id=p_request_id and
3082: segment21 = v_naic_code;
3083:
3084: elsif l.sex = 'M' then
3085:
3086: update per_ca_ee_report_lines set
3087: segment10=nvl(segment10,0) + nvl(l.count,0),
3088: segment12=nvl(l.count,0)
3089: where
3090: request_id=p_request_id and
3104: elsif k = 2 then
3105:
3106: if l.sex = 'F' then
3107:
3108: update per_ca_ee_report_lines set
3109: segment13=nvl(segment13,0) + nvl(l.count,0),
3110: segment14=nvl(l.count,0)
3111: where
3112: request_id=p_request_id and
3120: segment6=to_char(j) and
3121: segment21 = v_naic_code;
3122: else
3123:
3124: update per_ca_ee_report_lines set
3125: segment13=nvl(segment13,0) + nvl(l.count,0),
3126: segment15=nvl(l.count,0)
3127: where
3128: request_id=p_request_id and
3141: elsif k = 3 then
3142:
3143: if l.sex = 'F' then
3144:
3145: update per_ca_ee_report_lines set
3146: segment16=nvl(segment16,0) + nvl(l.count,0),
3147: segment17=nvl(l.count,0)
3148: where
3149: request_id=p_request_id and
3158: segment21 = v_naic_code;
3159:
3160: else
3161:
3162: update per_ca_ee_report_lines set
3163: segment16=nvl(segment16,0) + nvl(l.count,0),
3164: segment18=nvl(l.count,0)
3165: where
3166: request_id=p_request_id and
3214: cursor cur_naic is
3215: select
3216: pert.segment4 naic_code
3217: from
3218: per_ca_ee_report_lines pert
3219: where
3220: pert.request_id = p_request_id and
3221: pert.context = 'FORM12';
3222:
3247: per_people_f ppf,
3248: per_pay_proposals_v2 pppv,
3249: per_person_types ppt,
3250: hr_locations hl1,
3251: per_ca_ee_report_lines pert,
3252: hr_lookups hl2,
3253: hr_soft_coding_keyflex hsck,
3254: per_pay_bases ppb
3255: where
3307: (
3308: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3309: hsck.segment6 is null and
3310: hsck.segment1 in (select segment3
3311: from per_ca_ee_report_lines
3312: where request_id = p_request_id and
3313: context = 'FORM13' and
3314: segment1 = 'NAIC' and
3315: segment2 = v_naic_code)
3327: per_people_f ppf,
3328: per_pay_proposals_v2 pppv,
3329: per_person_types ppt,
3330: hr_locations hl1,
3331: per_ca_ee_report_lines pert,
3332: hr_lookups hl2,
3333: hr_soft_coding_keyflex hsck,
3334: per_pay_bases ppb
3335: where
3385: (
3386: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3387: hsck.segment6 is null and
3388: hsck.segment1 in (select segment3
3389: from per_ca_ee_report_lines
3390: where request_id = p_request_id and
3391: context = 'FORM13' and
3392: segment1 = 'NAIC' and
3393: segment2 = v_naic_code)
3439: per_people_f ppf,
3440: per_pay_proposals_v2 pppv,
3441: per_person_types ppt,
3442: hr_locations hl1,
3443: per_ca_ee_report_lines pert,
3444: hr_lookups hl2,
3445: hr_soft_coding_keyflex hsck,
3446: per_pay_bases ppb
3447: where
3503: (
3504: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3505: hsck.segment6 is null and
3506: hsck.segment1 in (select segment3
3507: from per_ca_ee_report_lines
3508: where request_id = p_request_id and
3509: context = 'FORM13' and
3510: segment1 = 'NAIC' and
3511: segment2 = v_naic_code)
3523: per_people_f ppf,
3524: per_pay_proposals_v2 pppv,
3525: per_person_types ppt,
3526: hr_locations hl1,
3527: per_ca_ee_report_lines pert,
3528: hr_lookups hl2,
3529: hr_soft_coding_keyflex hsck,
3530: per_pay_bases ppb
3531: where
3583: (
3584: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
3585: hsck.segment6 is null and
3586: hsck.segment1 in (select segment3
3587: from per_ca_ee_report_lines
3588: where request_id = p_request_id and
3589: context = 'FORM13' and
3590: segment1 = 'NAIC' and
3591: segment2 = v_naic_code)
3606: v_min_range || ' - ' || v_max_range)
3607: max_min_range
3608:
3609: from
3610: per_ca_ee_report_lines
3611: where
3612: request_id = p_request_id and
3613: context=decode(pc,1,'FORM14','FORM13') and
3614: segment1 = decode(pc,1,'PROVINCE','CMA') and
3619: segment2,
3620: segment4,
3621: segment3
3622: from
3623: per_ca_ee_report_lines
3624: where
3625: request_id = p_request_id and
3626: context = 'FORM3' and
3627: segment1 = decode(pc,1,'PROVINCE','CMA') and
3643: sum(to_number(segment14)) segment14,
3644: sum(to_number(segment15)) segment15,
3645: sum(to_number(segment16)) segment16
3646: from
3647: per_ca_ee_report_lines
3648: where
3649: request_id = p_request_id and
3650: context = 'FORM3' and
3651: segment1 = 'PROVINCE' and
3812: hr_utility.trace('v_meaning = '|| v_meaning );
3813:
3814: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
3815:
3816: insert into per_ca_ee_report_lines
3817: ( request_id,
3818: line_number,
3819: context,
3820: segment1,
3865: prev_x = x and
3866: prev_sex <> v_sex then
3867:
3868: if v_sex = 'M' then
3869: update per_ca_ee_report_lines set
3870: segment5 = segment5 + nvl(v_count,0),
3871: segment7 = nvl(v_count,0)
3872: where
3873: request_id=p_request_id and
3882: segment21 = v_naic_code;
3883:
3884: elsif v_sex = 'F' then
3885:
3886: update per_ca_ee_report_lines set
3887: segment5=segment5 + nvl(v_count,0),
3888: segment6=nvl(v_count,0)
3889: where
3890: request_id=p_request_id and
3942:
3943: hr_utility.trace('Form3: Updation Designated Grp: k = 1. ');
3944: if l.sex = 'F' then
3945:
3946: update per_ca_ee_report_lines set
3947: segment8=nvl(segment8,0) + nvl(l.count,0),
3948: segment9=nvl(l.count,0)
3949: where
3950: request_id = p_request_id and
3959: segment21 = v_naic_code;
3960:
3961: elsif l.sex = 'M' then
3962:
3963: update per_ca_ee_report_lines set
3964: segment8 = nvl(segment8,0) + nvl(l.count,0),
3965: segment10 = nvl(l.count,0)
3966: where
3967: request_id = p_request_id and
3982: hr_utility.trace('Form3: Updation Designated Grp: k = 2. ');
3983:
3984: if l.sex = 'F' then
3985:
3986: update per_ca_ee_report_lines set
3987: segment11=nvl(segment11,0) + nvl(l.count,0),
3988: segment12=nvl(l.count,0)
3989: where
3990: request_id=p_request_id and
3999: segment21 = v_naic_code;
4000:
4001: else
4002:
4003: update per_ca_ee_report_lines set
4004: segment11=nvl(segment11,0) + nvl(l.count,0),
4005: segment13=nvl(l.count,0)
4006: where
4007: request_id=p_request_id and
4024: if l.sex = 'F' then
4025:
4026: hr_utility.trace('Form3: Updation Designated Grp: k = 3. F ');
4027:
4028: update per_ca_ee_report_lines set
4029: segment14=nvl(segment14,0) + nvl(l.count,0),
4030: segment16=nvl(l.count,0)
4031: where
4032: request_id=p_request_id and
4049: -- || v_employment_category);
4050: --hr_utility.trace('Form3: v_fr_min_range: ' || to_char(v_fr_min_range));
4051: --hr_utility.trace('Form3: v_fr_max_range: ' || to_char(v_fr_max_range));
4052:
4053: update per_ca_ee_report_lines set
4054: segment14=nvl(segment14,0) + nvl(l.count,0),
4055: segment15=nvl(l.count,0)
4056: where
4057: request_id=p_request_id and
4214: loop
4215:
4216: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
4217:
4218: insert into per_ca_ee_report_lines
4219: ( request_id,
4220: line_number,
4221: context,
4222: segment1,
4268: for count_national in cur_count_national loop
4269:
4270: hr_utility.trace('Form3: cur_count_national. ');
4271:
4272: insert into per_ca_ee_report_lines
4273: (request_id,
4274: line_number,
4275: context,
4276: segment1,
4339: cursor cur_naic is
4340: select
4341: pert.segment4 naic_code
4342: from
4343: per_ca_ee_report_lines pert
4344: where
4345: pert.request_id = p_request_id and
4346: pert.context = 'FORM12';
4347:
4368: per_assignments_f paf,
4369: per_people_f ppf,
4370: per_person_types ppt,
4371: hr_locations hl1,
4372: per_ca_ee_report_lines pert,
4373: hr_lookups hl2,
4374: hr_soft_coding_keyflex hsck
4375: where
4376: hl.lookup_type='EEOG' and
4418: (
4419: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4420: hsck.segment6 is null and
4421: hsck.segment1 in (select segment3
4422: from per_ca_ee_report_lines
4423: where request_id = p_request_id and
4424: context = 'FORM13' and
4425: segment1 = 'NAIC' and
4426: segment2 = v_naic_code)
4448: per_assignments_f paf,
4449: per_people_f ppf,
4450: per_person_types ppt,
4451: hr_locations hl1,
4452: per_ca_ee_report_lines pert,
4453: hr_lookups hl2,
4454: hr_soft_coding_keyflex hsck
4455: where
4456: hl.lookup_type='EEOG' and
4498: (
4499: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4500: hsck.segment6 is null and
4501: hsck.segment1 in (select segment3
4502: from per_ca_ee_report_lines
4503: where request_id = p_request_id and
4504: context = 'FORM13' and
4505: segment1 = 'NAIC' and
4506: segment2 = v_naic_code)
4562: per_assignments_f paf,
4563: per_people_f ppf,
4564: per_person_types ppt,
4565: hr_locations hl1,
4566: per_ca_ee_report_lines pert,
4567: hr_lookups hl2,
4568: hr_soft_coding_keyflex hsck
4569: where
4570: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4616: (
4617: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4618: hsck.segment6 is null and
4619: hsck.segment1 in (select segment3
4620: from per_ca_ee_report_lines
4621: where request_id = p_request_id and
4622: context = 'FORM13' and
4623: segment1 = 'NAIC' and
4624: segment2 = v_naic_code)
4632: OR
4633: (
4634: hsck.segment6 is null and
4635: hsck.segment1 in (select segment3
4636: from per_ca_ee_report_lines
4637: where request_id = p_request_id and
4638: context = 'FORM13' and
4639: segment1 = 'NAIC' and
4640: segment2 = v_naic_code)
4662: per_assignments_f paf,
4663: per_people_f ppf,
4664: per_person_types ppt,
4665: hr_locations hl1,
4666: per_ca_ee_report_lines pert,
4667: hr_lookups hl2,
4668: hr_soft_coding_keyflex hsck
4669: where
4670: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4716: (
4717: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
4718: hsck.segment6 is null and
4719: hsck.segment1 in (select segment3
4720: from per_ca_ee_report_lines
4721: where request_id = p_request_id and
4722: context = 'FORM13' and
4723: segment1 = 'NAIC' and
4724: segment2 = v_naic_code)
4732: OR
4733: (
4734: hsck.segment6 is null and
4735: hsck.segment1 in (select segment3
4736: from per_ca_ee_report_lines
4737: where request_id = p_request_id and
4738: context = 'FORM13' and
4739: segment1 = 'NAIC' and
4740: segment2 = v_naic_code)
4766: segment2,
4767: v_meaning,
4768: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
4769: from
4770: per_ca_ee_report_lines where
4771: request_id=p_request_id and
4772: context='FORM14' and
4773: segment1='PROVINCE' and
4774: segment3 <> '0'
4777: segment2,
4778: segment3,
4779: segment4
4780: from
4781: per_ca_ee_report_lines
4782: where
4783: request_id=p_request_id and
4784: context='FORM4' and
4785: segment1='PROVINCE'and
4801: sum(to_number(segment14)) segment14,
4802: sum(to_number(segment15)) segment15,
4803: sum(to_number(segment16)) segment16
4804: from
4805: per_ca_ee_report_lines
4806: where
4807: request_id = p_request_id and
4808: context = 'FORM4' and
4809: segment1 = 'PROVINCE' and
4841: (ltrim(rtrim(prev_employment_category)) <> ltrim(rtrim(v_employment_category)))) then
4842:
4843: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
4844:
4845: insert into per_ca_ee_report_lines
4846: (request_id,
4847: line_number,
4848: context,
4849: segment1,
4893: prev_sex <> v_sex then
4894:
4895: if v_sex = 'M' then
4896:
4897: update per_ca_ee_report_lines set
4898: segment7=nvl(v_count,0),
4899: segment5=segment5 + nvl(v_count,0)
4900: where request_id=p_request_id and
4901: line_number=per_ca_ee_extract_pkg.k and
4907: segment21 = v_naic_code;
4908:
4909: elsif v_sex = 'F' then
4910:
4911: update per_ca_ee_report_lines set
4912: segment6=nvl(v_count,0),
4913: segment5=segment5 + nvl(v_count,0)
4914: where request_id=p_request_id and
4915: line_number=per_ca_ee_extract_pkg.k and
4949: close cur_meaning;
4950:
4951: if i = 1 then
4952: if v_sex = 'M' then
4953: update per_ca_ee_report_lines set
4954: segment8 = nvl(segment8,0) + nvl(v_count,0),
4955: segment10 = nvl(v_count,0)
4956: where
4957: request_id = p_request_id and
4960: segment2 = v_province_name and
4961: segment3 = v_meaning and
4962: segment4 = v_employment_category;
4963: elsif v_sex = 'F' then
4964: update per_ca_ee_report_lines set
4965: segment8 = nvl(segment8,0) + nvl(v_count,0),
4966: segment9 = nvl(v_count,0)
4967: where
4968: request_id = p_request_id and
4973: segment4 = v_employment_category;
4974: end if;
4975: elsif i = 2 then
4976: if v_sex = 'M' then
4977: update per_ca_ee_report_lines set
4978: segment11 = nvl(segment11,0) + nvl(v_count,0),
4979: segment13 = nvl(v_count,0)
4980: where
4981: request_id = p_request_id and
4984: segment2 = v_province_name and
4985: segment3 = v_meaning and
4986: segment4 = v_employment_category;
4987: elsif v_sex = 'F' then
4988: update per_ca_ee_report_lines set
4989: segment11 = nvl(segment11,0) + nvl(v_count,0),
4990: segment12 = nvl(v_count,0)
4991: where
4992: request_id = p_request_id and
4997: segment4 = v_employment_category;
4998: end if;
4999: elsif i = 3 then
5000: if v_sex = 'M' then
5001: update per_ca_ee_report_lines set
5002: segment14 = nvl(segment14,0) + nvl(v_count,0),
5003: segment16 = nvl(v_count,0)
5004: where
5005: request_id = p_request_id and
5008: segment2 = v_province_name and
5009: segment3 = v_meaning and
5010: segment4 = v_employment_category;
5011: elsif v_sex = 'F' then
5012: update per_ca_ee_report_lines set
5013: segment14 = nvl(segment14,0) + nvl(v_count,0),
5014: segment15 = nvl(v_count,0)
5015: where
5016: request_id = p_request_id and
5036: for x in cur_notfound(emp_cat) loop
5037:
5038: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
5039:
5040: insert into per_ca_ee_report_lines
5041: (request_id,
5042: line_number,
5043: context,
5044: segment1,
5091: for count_national in cur_count_national loop
5092:
5093: hr_utility.trace('Form4: cur_count_national. ');
5094:
5095: insert into per_ca_ee_report_lines
5096: (request_id,
5097: line_number,
5098: context,
5099: segment1,
5163: cursor cur_naic is
5164: select
5165: pert.segment4 naic_code
5166: from
5167: per_ca_ee_report_lines pert
5168: where
5169: pert.request_id = p_request_id and
5170: pert.context = 'FORM12';
5171:
5192: per_assignments_f paf,
5193: per_people_f ppf,
5194: per_person_types ppt,
5195: hr_locations hl1,
5196: per_ca_ee_report_lines pert,
5197: hr_lookups hl2,
5198: hr_soft_coding_keyflex hsck
5199: where
5200: hl.lookup_type='EEOG' and
5238: (
5239: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5240: hsck.segment6 is null and
5241: hsck.segment1 in (select segment3
5242: from per_ca_ee_report_lines
5243: where request_id = p_request_id and
5244: context = 'FORM13' and
5245: segment1 = 'NAIC' and
5246: segment2 = v_naic_code)
5276: per_assignments_f paf,
5277: per_people_f ppf,
5278: per_person_types ppt,
5279: hr_locations hl1,
5280: per_ca_ee_report_lines pert,
5281: hr_lookups hl2,
5282: hr_soft_coding_keyflex hsck
5283: where
5284: hl.lookup_type='EEOG' and
5322: (
5323: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5324: hsck.segment6 is null and
5325: hsck.segment1 in (select segment3
5326: from per_ca_ee_report_lines
5327: where request_id = p_request_id and
5328: context = 'FORM13' and
5329: segment1 = 'NAIC' and
5330: segment2 = v_naic_code)
5393: per_assignments_f paf,
5394: per_people_f ppf,
5395: per_person_types ppt,
5396: hr_locations hl1,
5397: per_ca_ee_report_lines pert,
5398: hr_lookups hl2,
5399: hr_soft_coding_keyflex hsck
5400: where
5401: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5441: (
5442: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5443: hsck.segment6 is null and
5444: hsck.segment1 in (select segment3
5445: from per_ca_ee_report_lines
5446: where request_id = p_request_id and
5447: context = 'FORM13' and
5448: segment1 = 'NAIC' and
5449: segment2 = v_naic_code)
5479: per_assignments_f paf,
5480: per_people_f ppf,
5481: per_person_types ppt,
5482: hr_locations hl1,
5483: per_ca_ee_report_lines pert,
5484: hr_lookups hl2,
5485: hr_soft_coding_keyflex hsck
5486: where
5487: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5527: (
5528: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5529: hsck.segment6 is null and
5530: hsck.segment1 in (select segment3
5531: from per_ca_ee_report_lines
5532: where request_id = p_request_id and
5533: context = 'FORM13' and
5534: segment1 = 'NAIC' and
5535: segment2 = v_naic_code)
5569: segment2,
5570: v_meaning,
5571: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
5572: from
5573: per_ca_ee_report_lines where
5574: request_id=p_request_id and
5575: context='FORM14' and
5576: segment1='PROVINCE' and
5577: segment3 <> '0'
5580: segment2,
5581: segment3,
5582: segment4
5583: from
5584: per_ca_ee_report_lines
5585: where
5586: request_id=p_request_id and
5587: context='FORM5' and
5588: segment1='PROVINCE'and
5604: sum(to_number(segment14)) segment14,
5605: sum(to_number(segment15)) segment15,
5606: sum(to_number(segment16)) segment16
5607: from
5608: per_ca_ee_report_lines
5609: where
5610: request_id = p_request_id and
5611: context = 'FORM5' and
5612: segment1 = 'PROVINCE' and
5632: per_assignments_f paf,
5633: per_people_f ppf,
5634: per_person_types ppt,
5635: hr_locations hl1,
5636: per_ca_ee_report_lines pert,
5637: hr_lookups hl2,
5638: hr_soft_coding_keyflex hsck,
5639: per_pay_proposals_v2 pppv
5640: where
5679: (
5680: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5681: hsck.segment6 is null and
5682: hsck.segment1 in (select segment3
5683: from per_ca_ee_report_lines
5684: where request_id = p_request_id and
5685: context = 'FORM13' and
5686: segment1 = 'NAIC' and
5687: segment2 = v_naic_code)
5710: per_assignments_f paf,
5711: per_people_f ppf,
5712: per_person_types ppt,
5713: hr_locations hl1,
5714: per_ca_ee_report_lines pert,
5715: hr_lookups hl2,
5716: hr_soft_coding_keyflex hsck,
5717: per_pay_proposals_v2 pppv
5718: where
5757: (
5758: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5759: hsck.segment6 is null and
5760: hsck.segment1 in (select segment3
5761: from per_ca_ee_report_lines
5762: where request_id = p_request_id and
5763: context = 'FORM13' and
5764: segment1 = 'NAIC' and
5765: segment2 = v_naic_code)
5800: per_assignments_f paf,
5801: per_people_f ppf,
5802: per_person_types ppt,
5803: hr_locations hl1,
5804: per_ca_ee_report_lines pert,
5805: hr_lookups hl2,
5806: hr_soft_coding_keyflex hsck,
5807: per_pay_proposals_v2 pppv
5808: where
5847: (
5848: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5849: hsck.segment6 is null and
5850: hsck.segment1 in (select segment3
5851: from per_ca_ee_report_lines
5852: where request_id = p_request_id and
5853: context = 'FORM13' and
5854: segment1 = 'NAIC' and
5855: segment2 = v_naic_code)
5878: per_assignments_f paf,
5879: per_people_f ppf,
5880: per_person_types ppt,
5881: hr_locations hl1,
5882: per_ca_ee_report_lines pert,
5883: hr_lookups hl2,
5884: hr_soft_coding_keyflex hsck,
5885: per_pay_proposals_v2 pppv
5886: where
5925: (
5926: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
5927: hsck.segment6 is null and
5928: hsck.segment1 in (select segment3
5929: from per_ca_ee_report_lines
5930: where request_id = p_request_id and
5931: context = 'FORM13' and
5932: segment1 = 'NAIC' and
5933: segment2 = v_naic_code)
5967: per_assignments_f paf,
5968: per_people_f ppf,
5969: per_person_types ppt,
5970: hr_locations hl1,
5971: per_ca_ee_report_lines pert,
5972: hr_lookups hl2,
5973: hr_soft_coding_keyflex hsck,
5974: per_pay_proposals_v2 pppv
5975: where
6017: (
6018: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
6019: hsck.segment6 is null and
6020: hsck.segment1 in (select segment3
6021: from per_ca_ee_report_lines
6022: where request_id = p_request_id and
6023: context = 'FORM13' and
6024: segment1 = 'NAIC' and
6025: segment2 = v_naic_code)
6048: per_assignments_f paf,
6049: per_people_f ppf,
6050: per_person_types ppt,
6051: hr_locations hl1,
6052: per_ca_ee_report_lines pert,
6053: hr_lookups hl2,
6054: hr_soft_coding_keyflex hsck,
6055: per_pay_proposals_v2 pppv
6056: where
6098: (
6099: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
6100: hsck.segment6 is null and
6101: hsck.segment1 in (select segment3
6102: from per_ca_ee_report_lines
6103: where request_id = p_request_id and
6104: context = 'FORM13' and
6105: segment1 = 'NAIC' and
6106: segment2 = v_naic_code)
6136: sum(to_number(segment13)) segment13,
6137: sum(to_number(segment14)) segment14,
6138: sum(to_number(segment15)) segment15
6139: from
6140: per_ca_ee_report_lines
6141: where
6142: request_id = p_request_id and
6143: context = 'FORM5P' and
6144: segment1 = 'PROVINCE' and
6149: select
6150: segment2,
6151: decode(emp_cat,1,'FR','2','PR',3,'PT') emp_category
6152: from
6153: per_ca_ee_report_lines
6154: where
6155: request_id = p_request_id and
6156: segment3 <> '0' and
6157: context = 'FORM14'
6159: select
6160: segment2,
6161: segment3
6162: from
6163: per_ca_ee_report_lines
6164: where
6165: request_id = p_request_id and
6166: context = 'FORM5P' and
6167: segment1 = 'PROVINCE' ;
6194: ltrim(rtrim(v_employment_category)))) then
6195:
6196: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
6197:
6198: insert into per_ca_ee_report_lines
6199: (request_id,
6200: line_number,
6201: context,
6202: segment1,
6245: prev_sex <> v_sex then
6246:
6247: if v_sex = 'M' then
6248:
6249: update per_ca_ee_report_lines set
6250: segment7=nvl(v_count,0),
6251: segment5=segment5 + nvl(v_count,0)
6252: where request_id=p_request_id and
6253: line_number = per_ca_ee_extract_pkg.k and
6259: segment21=v_naic_code;
6260:
6261: elsif v_sex = 'F' then
6262:
6263: update per_ca_ee_report_lines set
6264: segment6=nvl(v_count,0),
6265: segment5=segment5 + nvl(v_count,0)
6266: where request_id=p_request_id and
6267: line_number = per_ca_ee_extract_pkg.k and
6304: if i = 1 then
6305:
6306: if v_sex = 'M' then
6307:
6308: update per_ca_ee_report_lines set
6309: segment8 = nvl(segment8,0) + nvl(v_count,0),
6310: segment10 = nvl(v_count,0)
6311: where
6312: request_id = p_request_id and
6318: segment21 = v_naic_code;
6319:
6320: elsif v_sex = 'F' then
6321:
6322: update per_ca_ee_report_lines set
6323: segment8 = nvl(segment8,0) + nvl(v_count,0),
6324: segment9 = nvl(v_count,0)
6325: where
6326: request_id = p_request_id and
6336: elsif i = 2 then
6337:
6338: if v_sex = 'M' then
6339:
6340: update per_ca_ee_report_lines set
6341: segment11 = nvl(segment11,0) + nvl(v_count,0),
6342: segment13 = nvl(v_count,0)
6343: where
6344: request_id = p_request_id and
6350: segment21 = v_naic_code;
6351:
6352: elsif v_sex = 'F' then
6353:
6354: update per_ca_ee_report_lines set
6355: segment11 = nvl(segment11,0) + nvl(v_count,0),
6356: segment12 = nvl(v_count,0)
6357: where
6358: request_id = p_request_id and
6368: elsif i = 3 then
6369:
6370: if v_sex = 'M' then
6371:
6372: update per_ca_ee_report_lines set
6373: segment14 = nvl(segment14,0) + nvl(v_count,0),
6374: segment16 = nvl(v_count,0)
6375: where
6376: request_id = p_request_id and
6382: segment21 = v_naic_code;
6383:
6384: elsif v_sex = 'F' then
6385:
6386: update per_ca_ee_report_lines set
6387: segment14 = nvl(segment14,0) + nvl(v_count,0),
6388: segment15 = nvl(v_count,0)
6389: where
6390: request_id = p_request_id and
6411: for x in cur_notfound(emp_cat) loop
6412:
6413: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
6414:
6415: insert into per_ca_ee_report_lines
6416: (request_id,
6417: line_number,
6418: context,
6419: segment1,
6492:
6493: hr_utility.trace('Form5P: cur_total_promotions : Inside If');
6494: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
6495:
6496: insert into per_ca_ee_report_lines
6497: (request_id,
6498: line_number,
6499: context,
6500: segment1,
6541: prev_sex <> v_sex then
6542:
6543: if v_sex = 'M' then
6544:
6545: update per_ca_ee_report_lines set
6546: segment5=nvl(v_count,0),
6547: segment4=segment4 + nvl(v_count,0)
6548: where request_id=p_request_id and
6549: line_number = per_ca_ee_extract_pkg.k and
6554: segment21=v_naic_code;
6555:
6556: elsif v_sex = 'F' then
6557:
6558: update per_ca_ee_report_lines set
6559: segment6=nvl(v_count,0),
6560: segment4=segment4 + nvl(v_count,0)
6561: where request_id=p_request_id and
6562: line_number = per_ca_ee_extract_pkg.k and
6596: if i = 1 then
6597:
6598: if v_sex = 'M' then
6599:
6600: update per_ca_ee_report_lines set
6601: segment7 = nvl(segment7,0) + nvl(v_count,0),
6602: segment8 = nvl(v_count,0)
6603: where
6604: request_id = p_request_id and
6609: segment21 = v_naic_code;
6610:
6611: elsif v_sex = 'F' then
6612:
6613: update per_ca_ee_report_lines set
6614: segment7 = nvl(segment7,0) + nvl(v_count,0),
6615: segment9 = nvl(v_count,0)
6616: where
6617: request_id = p_request_id and
6626: elsif i = 2 then
6627:
6628: if v_sex = 'M' then
6629:
6630: update per_ca_ee_report_lines set
6631: segment10 = nvl(segment10,0) + nvl(v_count,0),
6632: segment11 = nvl(v_count,0)
6633: where
6634: request_id = p_request_id and
6639: segment21 = v_naic_code;
6640:
6641: elsif v_sex = 'F' then
6642:
6643: update per_ca_ee_report_lines set
6644: segment10 = nvl(segment10,0) + nvl(v_count,0),
6645: segment12 = nvl(v_count,0)
6646: where
6647: request_id = p_request_id and
6656: elsif i = 3 then
6657:
6658: if v_sex = 'M' then
6659:
6660: update per_ca_ee_report_lines set
6661: segment13 = nvl(segment13,0) + nvl(v_count,0),
6662: segment14 = nvl(v_count,0)
6663: where
6664: request_id = p_request_id and
6669: segment21 = v_naic_code;
6670:
6671: elsif v_sex = 'F' then
6672:
6673: update per_ca_ee_report_lines set
6674: segment13 = nvl(segment13,0) + nvl(v_count,0),
6675: segment15 = nvl(v_count,0)
6676: where
6677: request_id = p_request_id and
6691:
6692: for i in 1..3 loop
6693: for j in cur_notfound_promotions(i) loop
6694:
6695: insert into per_ca_ee_report_lines
6696: (request_id,
6697: line_number,
6698: context,
6699: segment1,
6736: end loop; -- End loop for designated group members.
6737:
6738: for i in cur_count_national_promotions loop
6739:
6740: insert into per_ca_ee_report_lines
6741: (request_id,
6742: line_number,
6743: context,
6744: segment1,
6781: for count_national in cur_count_national loop
6782:
6783: hr_utility.trace('Form5: cur_count_national. ');
6784:
6785: insert into per_ca_ee_report_lines
6786: (request_id,
6787: line_number,
6788: context,
6789: segment1,
6850: declare
6851:
6852: cursor cur_naic is
6853: select pert.segment4 naic_code
6854: from per_ca_ee_report_lines pert
6855: where pert.request_id = p_request_id
6856: and pert.context = 'FORM12';
6857:
6858: v_naic_code hr_lookups.lookup_code%TYPE;
6898: from
6899: per_assignments_f paf,
6900: per_people_f ppf,
6901: hr_locations hl1,
6902: per_ca_ee_report_lines pert,
6903: hr_lookups hl2,
6904: hr_soft_coding_keyflex hsck
6905: where job_exists(paf.job_id) is not null
6906: and paf.primary_flag = 'Y'
6959: (
6960: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
6961: hsck.segment6 is null and
6962: hsck.segment1 in (select segment3
6963: from per_ca_ee_report_lines
6964: where request_id = p_request_id
6965: and context = 'FORM13'
6966: and segment1 = 'NAIC'
6967: and segment2 = v_naic_code)
6982: from
6983: per_assignments_f paf,
6984: per_people_f ppf,
6985: hr_locations hl1,
6986: per_ca_ee_report_lines pert,
6987: hr_lookups hl2,
6988: hr_soft_coding_keyflex hsck
6989: where
6990: job_exists(paf.job_id) is not null
7046: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
7047: hsck.segment6 is null and
7048: hsck.segment1 in
7049: (select segment3
7050: from per_ca_ee_report_lines
7051: where request_id = p_request_id
7052: and context = 'FORM13'
7053: and segment1 = 'NAIC'
7054: and segment2 = v_naic_code)
7087: segment2,
7088: v_meaning,
7089: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
7090: from
7091: per_ca_ee_report_lines where
7092: request_id=p_request_id and
7093: context='FORM14' and
7094: segment1='PROVINCE' and
7095: segment3 <> '0'
7098: segment2,
7099: segment3,
7100: segment4
7101: from
7102: per_ca_ee_report_lines
7103: where
7104: request_id=p_request_id and
7105: context='FORM5' and
7106: segment1='PROVINCE'and
7122: sum(to_number(segment14)) segment14,
7123: sum(to_number(segment15)) segment15,
7124: sum(to_number(segment16)) segment16
7125: from
7126: per_ca_ee_report_lines
7127: where
7128: request_id = p_request_id and
7129: context = 'FORM5' and
7130: segment1 = 'PROVINCE' and
7152: from
7153: per_assignments_f paf,
7154: per_people_f ppf,
7155: hr_locations hl1,
7156: per_ca_ee_report_lines pert,
7157: hr_lookups hl2,
7158: hr_soft_coding_keyflex hsck
7159: where
7160: job_exists(paf.job_id) is not null
7214: (
7215: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
7216: hsck.segment6 is null and
7217: hsck.segment1 in (select segment3
7218: from per_ca_ee_report_lines
7219: where request_id = p_request_id and
7220: context = 'FORM13' and
7221: segment1 = 'NAIC' and
7222: segment2 = v_naic_code)
7236: from
7237: per_assignments_f paf,
7238: per_people_f ppf,
7239: hr_locations hl1,
7240: per_ca_ee_report_lines pert,
7241: hr_lookups hl2,
7242: hr_soft_coding_keyflex hsck
7243: where
7244: job_exists(paf.job_id) is not null
7300: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
7301: hsck.segment6 is null and
7302: hsck.segment1 in
7303: (select segment3
7304: from per_ca_ee_report_lines
7305: where request_id = p_request_id
7306: and context = 'FORM13'
7307: and segment1 = 'NAIC'
7308: and segment2 = v_naic_code)
7327: sum(to_number(segment13)) segment13,
7328: sum(to_number(segment14)) segment14,
7329: sum(to_number(segment15)) segment15
7330: from
7331: per_ca_ee_report_lines
7332: where
7333: request_id = p_request_id and
7334: context = 'FORM5P' and
7335: segment1 = 'PROVINCE' and
7340: select
7341: segment2,
7342: decode(emp_cat,1,'FR','2','PR',3,'PT') emp_category
7343: from
7344: per_ca_ee_report_lines
7345: where
7346: request_id = p_request_id and
7347: segment3 <> '0' and
7348: context = 'FORM14'
7350: select
7351: segment2,
7352: segment3
7353: from
7354: per_ca_ee_report_lines
7355: where
7356: request_id = p_request_id and
7357: context = 'FORM5P' and
7358: segment1 = 'PROVINCE' ;
7419: ltrim(rtrim(v_employment_category)))) then
7420:
7421: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
7422:
7423: insert into per_ca_ee_report_lines
7424: (request_id,
7425: line_number,
7426: context,
7427: segment1,
7470: prev_sex <> v_sex then
7471:
7472: if v_sex = 'M' then
7473:
7474: update per_ca_ee_report_lines set
7475: segment7=nvl(v_count,0),
7476: segment5=segment5 + nvl(v_count,0)
7477: where request_id=p_request_id and
7478: line_number = per_ca_ee_extract_pkg.k and
7484: segment21=v_naic_code;
7485:
7486: elsif v_sex = 'F' then
7487:
7488: update per_ca_ee_report_lines set
7489: segment6=nvl(v_count,0),
7490: segment5=segment5 + nvl(v_count,0)
7491: where request_id=p_request_id and
7492: line_number = per_ca_ee_extract_pkg.k and
7511: elsif i = 1 then
7512:
7513: if v_sex = 'M' then
7514:
7515: update per_ca_ee_report_lines set
7516: segment8 = nvl(segment8,0) + nvl(v_count,0),
7517: segment10 = nvl(v_count,0)
7518: where
7519: request_id = p_request_id and
7525: segment21 = v_naic_code;
7526:
7527: elsif v_sex = 'F' then
7528:
7529: update per_ca_ee_report_lines set
7530: segment8 = nvl(segment8,0) + nvl(v_count,0),
7531: segment9 = nvl(v_count,0)
7532: where
7533: request_id = p_request_id and
7543: elsif i = 2 then
7544:
7545: if v_sex = 'M' then
7546:
7547: update per_ca_ee_report_lines set
7548: segment11 = nvl(segment11,0) + nvl(v_count,0),
7549: segment13 = nvl(v_count,0)
7550: where
7551: request_id = p_request_id and
7557: segment21 = v_naic_code;
7558:
7559: elsif v_sex = 'F' then
7560:
7561: update per_ca_ee_report_lines set
7562: segment11 = nvl(segment11,0) + nvl(v_count,0),
7563: segment12 = nvl(v_count,0)
7564: where
7565: request_id = p_request_id and
7575: elsif i = 3 then
7576:
7577: if v_sex = 'M' then
7578:
7579: update per_ca_ee_report_lines set
7580: segment14 = nvl(segment14,0) + nvl(v_count,0),
7581: segment16 = nvl(v_count,0)
7582: where
7583: request_id = p_request_id and
7589: segment21 = v_naic_code;
7590:
7591: elsif v_sex = 'F' then
7592:
7593: update per_ca_ee_report_lines set
7594: segment14 = nvl(segment14,0) + nvl(v_count,0),
7595: segment15 = nvl(v_count,0)
7596: where
7597: request_id = p_request_id and
7619: for x in cur_notfound(emp_cat) loop
7620:
7621: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
7622:
7623: insert into per_ca_ee_report_lines
7624: (request_id,
7625: line_number,
7626: context,
7627: segment1,
7706:
7707: hr_utility.trace('Form5P: cur_total_promotions : Inside If');
7708: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
7709:
7710: insert into per_ca_ee_report_lines
7711: (request_id,
7712: line_number,
7713: context,
7714: segment1,
7755: prev_sex <> v_sex then
7756:
7757: if v_sex = 'M' then
7758:
7759: update per_ca_ee_report_lines set
7760: segment5=nvl(v_count,0),
7761: segment4=segment4 + nvl(v_count,0)
7762: where request_id=p_request_id and
7763: line_number = per_ca_ee_extract_pkg.k and
7768: segment21=v_naic_code;
7769:
7770: elsif v_sex = 'F' then
7771:
7772: update per_ca_ee_report_lines set
7773: segment6=nvl(v_count,0),
7774: segment4=segment4 + nvl(v_count,0)
7775: where request_id=p_request_id and
7776: line_number = per_ca_ee_extract_pkg.k and
7795:
7796:
7797: if v_sex = 'M' then
7798:
7799: update per_ca_ee_report_lines set
7800: segment7 = nvl(segment7,0) + nvl(v_count,0),
7801: segment8 = nvl(v_count,0)
7802: where
7803: request_id = p_request_id and
7808: segment21 = v_naic_code;
7809:
7810: elsif v_sex = 'F' then
7811:
7812: update per_ca_ee_report_lines set
7813: segment7 = nvl(segment7,0) + nvl(v_count,0),
7814: segment9 = nvl(v_count,0)
7815: where
7816: request_id = p_request_id and
7825: elsif i = 2 then
7826:
7827: if v_sex = 'M' then
7828:
7829: update per_ca_ee_report_lines set
7830: segment10 = nvl(segment10,0) + nvl(v_count,0),
7831: segment11 = nvl(v_count,0)
7832: where
7833: request_id = p_request_id and
7838: segment21 = v_naic_code;
7839:
7840: elsif v_sex = 'F' then
7841:
7842: update per_ca_ee_report_lines set
7843: segment10 = nvl(segment10,0) + nvl(v_count,0),
7844: segment12 = nvl(v_count,0)
7845: where
7846: request_id = p_request_id and
7855: elsif i = 3 then
7856:
7857: if v_sex = 'M' then
7858:
7859: update per_ca_ee_report_lines set
7860: segment13 = nvl(segment13,0) + nvl(v_count,0),
7861: segment14 = nvl(v_count,0)
7862: where
7863: request_id = p_request_id and
7868: segment21 = v_naic_code;
7869:
7870: elsif v_sex = 'F' then
7871:
7872: update per_ca_ee_report_lines set
7873: segment13 = nvl(segment13,0) + nvl(v_count,0),
7874: segment15 = nvl(v_count,0)
7875: where
7876: request_id = p_request_id and
7889:
7890: for i in 1..3 loop
7891: for j in cur_notfound_promotions(i) loop
7892:
7893: insert into per_ca_ee_report_lines
7894: (request_id,
7895: line_number,
7896: context,
7897: segment1,
7934: end loop; -- End loop for designated group members.
7935:
7936: for i in cur_count_national_promotions loop
7937:
7938: insert into per_ca_ee_report_lines
7939: (request_id,
7940: line_number,
7941: context,
7942: segment1,
7978: for count_national in cur_count_national loop
7979:
7980: hr_utility.trace('Form5: cur_count_national. ');
7981:
7982: insert into per_ca_ee_report_lines
7983: (request_id,
7984: line_number,
7985: context,
7986: segment1,
8048: cursor cur_naic is
8049: select
8050: pert.segment4 naic_code
8051: from
8052: per_ca_ee_report_lines pert
8053: where
8054: pert.request_id = p_request_id and
8055: pert.context = 'FORM12';
8056:
8116: (
8117: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8118: hsck.segment6 is null and
8119: hsck.segment1 in (select segment3
8120: from per_ca_ee_report_lines
8121: where request_id = p_request_id and
8122: context = 'FORM13' and
8123: segment1 = 'NAIC' and
8124: segment2 = v_naic_code)
8185: (
8186: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8187: hsck.segment6 is null and
8188: hsck.segment1 in (select segment3
8189: from per_ca_ee_report_lines
8190: where request_id = p_request_id and
8191: context = 'FORM13' and
8192: segment1 = 'NAIC' and
8193: segment2 = v_naic_code)
8292: (
8293: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8294: hsck.segment6 is null and
8295: hsck.segment1 in (select segment3
8296: from per_ca_ee_report_lines
8297: where request_id = p_request_id and
8298: context = 'FORM13' and
8299: segment1 = 'NAIC' and
8300: segment2 = v_naic_code)
8364: (
8365: hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
8366: hsck.segment6 is null and
8367: hsck.segment1 in (select segment3
8368: from per_ca_ee_report_lines
8369: where request_id = p_request_id and
8370: context = 'FORM13' and
8371: segment1 = 'NAIC' and
8372: segment2 = v_naic_code)
8398: segment2,
8399: v_meaning,
8400: decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
8401: from
8402: per_ca_ee_report_lines where
8403: request_id=p_request_id and
8404: context='FORM14' and
8405: segment1='PROVINCE' and
8406: segment3 <> '0'
8409: segment2,
8410: segment3,
8411: segment4
8412: from
8413: per_ca_ee_report_lines
8414: where
8415: request_id=p_request_id and
8416: context='FORM6' and
8417: segment1='PROVINCE'and
8433: sum(to_number(segment14)) segment14,
8434: sum(to_number(segment15)) segment15,
8435: sum(to_number(segment16)) segment16
8436: from
8437: per_ca_ee_report_lines
8438: where
8439: request_id = p_request_id and
8440: context = 'FORM6' and
8441: segment1 = 'PROVINCE' and
8472: ltrim(rtrim(v_employment_category)))) then
8473:
8474: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
8475:
8476: insert into per_ca_ee_report_lines
8477: (request_id,
8478: line_number,
8479: context,
8480: segment1,
8526: prev_sex <> v_sex then
8527:
8528: if v_sex = 'M' then
8529:
8530: update per_ca_ee_report_lines set
8531: segment7=nvl(v_count,0),
8532: segment5=segment5 + nvl(v_count,0)
8533: where request_id=p_request_id and
8534: line_number = per_ca_ee_extract_pkg.k and
8540: segment21=v_naic_code;
8541:
8542: elsif v_sex = 'F' then
8543:
8544: update per_ca_ee_report_lines set
8545: segment6=nvl(v_count,0),
8546: segment5=segment5 + nvl(v_count,0)
8547: where request_id=p_request_id and
8548: line_number = per_ca_ee_extract_pkg.k and
8585: if i = 1 then
8586:
8587: if v_sex = 'M' then
8588:
8589: update per_ca_ee_report_lines set
8590: segment8 = nvl(segment8,0) + nvl(v_count,0),
8591: segment9 = 0,
8592: segment10 = nvl(v_count,0)
8593: where
8600: segment21 = v_naic_code;
8601:
8602: elsif v_sex = 'F' then
8603:
8604: update per_ca_ee_report_lines set
8605: segment8 = nvl(segment8,0) + nvl(v_count,0),
8606: segment9 = nvl(v_count,0),
8607: segment10 = 0
8608: where
8619: elsif i = 2 then
8620:
8621: if v_sex = 'M' then
8622:
8623: update per_ca_ee_report_lines set
8624: segment11 = nvl(segment11,0) + nvl(v_count,0),
8625: segment12 = 0,
8626: segment13 = nvl(v_count,0)
8627: where
8634: segment21 = v_naic_code;
8635:
8636: elsif v_sex = 'F' then
8637:
8638: update per_ca_ee_report_lines set
8639: segment11 = nvl(segment11,0) + nvl(v_count,0),
8640: segment12 = nvl(v_count,0),
8641: segment13 = 0
8642: where
8653: elsif i = 3 then
8654:
8655: if v_sex = 'M' then
8656:
8657: update per_ca_ee_report_lines set
8658: segment14 = nvl(segment14,0) + nvl(v_count,0),
8659: segment15 = 0,
8660: segment16 = nvl(v_count,0)
8661: where
8668: segment21 = v_naic_code;
8669:
8670: elsif v_sex = 'F' then
8671:
8672: update per_ca_ee_report_lines set
8673: segment14 = nvl(segment14,0) + nvl(v_count,0),
8674: segment15 = nvl(v_count,0),
8675: segment16 = 0
8676: where
8703: hr_utility.trace('Form6: cur_notfound' );
8704:
8705: per_ca_ee_extract_pkg.k := per_ca_ee_extract_pkg.k + 1;
8706:
8707: insert into per_ca_ee_report_lines
8708: (request_id,
8709: line_number,
8710: context,
8711: segment1,
8755: for count_national in cur_count_national loop
8756:
8757: hr_utility.trace('Form6: cur_count_national. ');
8758:
8759: insert into per_ca_ee_report_lines
8760: (request_id,
8761: line_number,
8762: context,
8763: segment1,
8818: segment13,
8819: segment14,
8820: segment15
8821: from
8822: per_ca_ee_report_lines
8823: where
8824: request_id=p_request_id and
8825: context='FORM11';
8826:
8832: select
8833: segment4 max_naic_code,
8834: max(to_number(segment3))
8835: from
8836: per_ca_ee_report_lines
8837: where
8838: request_id = p_request_id and
8839: context = 'FORM12' and
8840: segment1 = 'NAIC'
8846: cursor cur_less_than_max_naic is
8847: select
8848: segment4 naic_code
8849: from
8850: per_ca_ee_report_lines
8851: where request_id = p_request_id
8852: and to_number(segment3) < (select to_number(lookup_code)
8853: from pay_ca_legislation_info
8854: where lookup_type = 'EER1')
8880: segment18,
8881: segment19,
8882: segment20
8883: segment21
8884: from per_ca_ee_report_lines
8885: where
8886: request_id = p_request_id and
8887: context in ('FORM3','FORM4','FORM5','FORM6') and
8888: segment1 = 'NATIONAL' and
8905:
8906: if
8907: ( ((nvl(v_tot_pt,0)/(nvl(v_tot_fr,0) + nvl(v_tot_pr,0) + nvl(v_tot_pt,0))) * 100) >= 20 ) then
8908:
8909: update per_ca_ee_report_lines set
8910: segment20 = 'Y'
8911: WHERE
8912: request_id=p_request_id and
8913: context in ('FORM2','FORM3','FORM4','FORM5','FORM6');
8912: request_id=p_request_id and
8913: context in ('FORM2','FORM3','FORM4','FORM5','FORM6');
8914: else
8915:
8916: update per_ca_ee_report_lines set
8917: segment20 = decode(segment3,'PT','N','Y')
8918: WHERE
8919: request_id=p_request_id and
8920: context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
8919: request_id=p_request_id and
8920: context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
8921: segment1 = 'NATIONAL';
8922:
8923: update per_ca_ee_report_lines set
8924: segment20 = decode(segment4,'PT','N','Y')
8925: WHERE
8926: request_id=p_request_id and
8927: context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
8951: for i in cur_not_max_naic_data loop
8952:
8953: hr_utility.trace('UPDATE_REC: Form3 - 6' );
8954:
8955: update per_ca_ee_report_lines
8956: set
8957: segment4 = segment4 + i.segment4,
8958: segment5 = segment5 + i.segment5,
8959: segment6 = segment6 + i.segment6,
8977: hr_utility.trace('UPDATE_REC: Form3 - 6 End' );
8978:
8979: end loop; -- End loop cur_not_max_naic_data
8980:
8981: delete from per_ca_ee_report_lines
8982: where request_id = p_request_id and
8983: segment21 = v_not_max_naic;
8984: end loop;
8985: