[Home] [Help]
2294: sp_out_10 OUT nocopy varchar2)
2295: return varchar2 IS
2296:
2297: l_err boolean;
2298: l_entity_id ff_database_items.user_entity_id%type;
2299: l_archived_value ff_archive_items.value%type;
2300: l_jurisdiction_code varchar2(25);
2301: l_message varchar2(1000);
2302: l_state_wage varchar2(100);
2302: l_state_wage varchar2(100);
2303: l_main_return varchar2(100);
2304:
2305: TYPE dbi_columns IS RECORD(
2306: p_user_name ff_database_items.user_name%type,
2307: p_archived_value ff_archive_items.value%type);
2308:
2309: dbi_rec dbi_columns;
2310:
2313:
2314: dbi_table dbi_infm;
2315:
2316: CURSOR get_user_entity_id
2317: (c_user_name ff_database_items.user_name%type) IS
2318: SELECT fdi.user_entity_id
2319: FROM ff_database_items fdi,
2320: ff_user_entities fue
2321: WHERE fue.legislation_code = 'US'
2315:
2316: CURSOR get_user_entity_id
2317: (c_user_name ff_database_items.user_name%type) IS
2318: SELECT fdi.user_entity_id
2319: FROM ff_database_items fdi,
2320: ff_user_entities fue
2321: WHERE fue.legislation_code = 'US'
2322: AND fue.user_entity_id = fdi.user_entity_id
2323: AND fdi.user_name = c_user_name;
2322: AND fue.user_entity_id = fdi.user_entity_id
2323: AND fdi.user_name = c_user_name;
2324:
2325: CURSOR get_archived_values(
2326: c_user_entity_id ff_database_items.user_entity_id%type,
2327: c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2328: c_tax_unit_id hr_organization_units.organization_id%type)
2329: IS
2330: SELECT target.value
2340: and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id);
2341: /*context of tax_unit_id */
2342:
2343: CURSOR get_jd_archived_values(
2344: c_user_entity_id ff_database_items.user_entity_id%type,
2345: c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2346: c_tax_unit_id hr_organization_units.organization_id%type,
2347: c_jurisdiction_code varchar2)
2348: IS
2794:
2795: CURSOR csr_defined_balance is
2796: SELECT to_number(UE.creator_id)
2797: FROM ff_user_entities UE,
2798: ff_database_items DI
2799: WHERE DI.user_name = p_db_item_name
2800: AND UE.user_entity_id = DI.user_entity_id
2801: AND Ue.creator_type = 'B'
2802: AND UE.legislation_code = 'US';
3023:
3024: Select fai.value
3025: into lv_employee_count
3026: from ff_archive_items fai,
3027: ff_database_items fdi
3028: where fdi.user_name = p_database_item_name /*eg A_SQWL_MONTH1_COUNT */
3029: and fai.user_entity_id = fdi.user_entity_id
3030: and fai.context1 = i.assignment_action_id ;
3031:
3058: p_state varchar2) IS
3059: SELECT count(*)
3060: FROM ff_archive_item_contexts faic,
3061: ff_archive_items fai,
3062: ff_database_items fdi,
3063: pay_assignment_actions paa,
3064: pay_payroll_actions ppa
3065: WHERE
3066: ppa.payroll_action_id = p_payroll_action_id
3100: SELECT distinct fdi1.user_name,fai1.value
3101: --sum(to_number(nvl( fai1.value,'0')))
3102: FROM ff_archive_item_contexts faic,
3103: ff_archive_items fai,
3104: ff_database_items fdi,
3105: pay_assignment_actions paa,
3106: pay_payroll_actions ppa,
3107: ff_archive_items fai1,
3108: ff_database_items fdi1,
3104: ff_database_items fdi,
3105: pay_assignment_actions paa,
3106: pay_payroll_actions ppa,
3107: ff_archive_items fai1,
3108: ff_database_items fdi1,
3109: ff_archive_item_contexts faic1,
3110: ff_contexts fc,
3111: pay_us_states pus
3112: WHERE
3168: SELECT fdi1.user_name,
3169: sum(to_number(nvl(fai1.value,'0')))
3170: FROM ff_archive_item_contexts faic,
3171: ff_archive_items fai,
3172: ff_database_items fdi,
3173: pay_assignment_actions paa,
3174: pay_payroll_actions ppa,
3175: ff_archive_items fai1,
3176: ff_database_items fdi1,
3172: ff_database_items fdi,
3173: pay_assignment_actions paa,
3174: pay_payroll_actions ppa,
3175: ff_archive_items fai1,
3176: ff_database_items fdi1,
3177: ff_archive_item_contexts faic1,
3178: ff_contexts fc,
3179: pay_us_states pus
3180: WHERE
3238: SELECT fdi1.user_name,
3239: sum(to_number(nvl(fai1.value,'0')))
3240: FROM ff_archive_item_contexts faic,
3241: ff_archive_items fai,
3242: ff_database_items fdi,
3243: pay_assignment_actions paa,
3244: pay_payroll_actions ppa,
3245: ff_archive_items fai1,
3246: ff_database_items fdi1
3242: ff_database_items fdi,
3243: pay_assignment_actions paa,
3244: pay_payroll_actions ppa,
3245: ff_archive_items fai1,
3246: ff_database_items fdi1
3247: --ff_archive_item_contexts faic1,
3248: --ff_contexts fc,
3249: --pay_us_states pus
3250: WHERE
3321: from pay_payroll_actions ppa,
3322: pay_assignment_actions paa,
3323: ff_archive_items fai,
3324: ff_archive_items fai1,
3325: ff_database_items fdi,
3326: ff_database_items fdi1
3327: where ppa.payroll_action_id = p_payroll_action_id
3328: and ppa.payroll_action_id = paa.payroll_action_id
3329: and paa.tax_unit_id = p_tax_unit_id
3322: pay_assignment_actions paa,
3323: ff_archive_items fai,
3324: ff_archive_items fai1,
3325: ff_database_items fdi,
3326: ff_database_items fdi1
3327: where ppa.payroll_action_id = p_payroll_action_id
3328: and ppa.payroll_action_id = paa.payroll_action_id
3329: and paa.tax_unit_id = p_tax_unit_id
3330: and fai.context1 = paa.assignment_action_id
3452: pay_assignment_actions paa,
3453: ff_archive_items fai,
3454: ff_archive_items fai1,
3455: ff_archive_items fai2,
3456: ff_database_items fdi,
3457: ff_database_items fdi1,
3458: ff_database_items fdi2
3459: where ppa.payroll_action_id = p_payroll_action_id
3460: and ppa.payroll_action_id = paa.payroll_action_id
3453: ff_archive_items fai,
3454: ff_archive_items fai1,
3455: ff_archive_items fai2,
3456: ff_database_items fdi,
3457: ff_database_items fdi1,
3458: ff_database_items fdi2
3459: where ppa.payroll_action_id = p_payroll_action_id
3460: and ppa.payroll_action_id = paa.payroll_action_id
3461: and paa.tax_unit_id = p_tax_unit_id
3454: ff_archive_items fai1,
3455: ff_archive_items fai2,
3456: ff_database_items fdi,
3457: ff_database_items fdi1,
3458: ff_database_items fdi2
3459: where ppa.payroll_action_id = p_payroll_action_id
3460: and ppa.payroll_action_id = paa.payroll_action_id
3461: and paa.tax_unit_id = p_tax_unit_id
3462: and fai.context1 = paa.assignment_action_id
3473: into p_withholding
3474: from pay_payroll_actions ppa,
3475: pay_assignment_actions paa,
3476: ff_archive_items fai,
3477: ff_database_items fdi
3478: where ppa.payroll_action_id = p_payroll_action_id
3479: and ppa.payroll_action_id = paa.payroll_action_id
3480: and paa.tax_unit_id = p_tax_unit_id
3481: and fai.context1 = paa.assignment_action_id
3491: pay_assignment_actions paa,
3492: ff_archive_items fai,
3493: ff_archive_items fai1,
3494: ff_archive_items fai2,
3495: ff_database_items fdi,
3496: ff_database_items fdi1,
3497: ff_database_items fdi2
3498: where ppa.payroll_action_id = p_payroll_action_id
3499: and ppa.payroll_action_id = paa.payroll_action_id
3492: ff_archive_items fai,
3493: ff_archive_items fai1,
3494: ff_archive_items fai2,
3495: ff_database_items fdi,
3496: ff_database_items fdi1,
3497: ff_database_items fdi2
3498: where ppa.payroll_action_id = p_payroll_action_id
3499: and ppa.payroll_action_id = paa.payroll_action_id
3500: and paa.tax_unit_id = p_tax_unit_id
3493: ff_archive_items fai1,
3494: ff_archive_items fai2,
3495: ff_database_items fdi,
3496: ff_database_items fdi1,
3497: ff_database_items fdi2
3498: where ppa.payroll_action_id = p_payroll_action_id
3499: and ppa.payroll_action_id = paa.payroll_action_id
3500: and paa.tax_unit_id = p_tax_unit_id
3501: and fai.context1 = paa.assignment_action_id
3554: ff_archive_items fai,
3555: ff_archive_items fai1,
3556: ff_archive_items fai2,
3557: ff_archive_items fai3,
3558: ff_database_items fdi,
3559: ff_database_items fdi1,
3560: ff_database_items fdi2,
3561: ff_database_items fdi3
3562: where ppa.payroll_action_id = p_payroll_action_id
3555: ff_archive_items fai1,
3556: ff_archive_items fai2,
3557: ff_archive_items fai3,
3558: ff_database_items fdi,
3559: ff_database_items fdi1,
3560: ff_database_items fdi2,
3561: ff_database_items fdi3
3562: where ppa.payroll_action_id = p_payroll_action_id
3563: and ppa.payroll_action_id = paa.payroll_action_id
3556: ff_archive_items fai2,
3557: ff_archive_items fai3,
3558: ff_database_items fdi,
3559: ff_database_items fdi1,
3560: ff_database_items fdi2,
3561: ff_database_items fdi3
3562: where ppa.payroll_action_id = p_payroll_action_id
3563: and ppa.payroll_action_id = paa.payroll_action_id
3564: and paa.tax_unit_id = p_tax_unit_id
3557: ff_archive_items fai3,
3558: ff_database_items fdi,
3559: ff_database_items fdi1,
3560: ff_database_items fdi2,
3561: ff_database_items fdi3
3562: where ppa.payroll_action_id = p_payroll_action_id
3563: and ppa.payroll_action_id = paa.payroll_action_id
3564: and paa.tax_unit_id = p_tax_unit_id
3565: and fai.context1 = paa.assignment_action_id
3589: into p_sui_taxable
3590: from pay_payroll_actions ppa,
3591: pay_assignment_actions paa,
3592: ff_archive_items fai,
3593: ff_database_items fdi
3594: where ppa.payroll_action_id = p_payroll_action_id
3595: and ppa.payroll_action_id = paa.payroll_action_id
3596: and paa.tax_unit_id = p_tax_unit_id
3597: and fai.context1 = paa.assignment_action_id
3617: into p_sui_taxable
3618: from pay_payroll_actions ppa,
3619: pay_assignment_actions paa,
3620: ff_archive_items fai,
3621: ff_database_items fdi
3622: where ppa.payroll_action_id = p_payroll_action_id
3623: and ppa.payroll_action_id = paa.payroll_action_id
3624: and paa.tax_unit_id = p_tax_unit_id
3625: and fai.context1 = paa.assignment_action_id