[Home] [Help]
120: and asg.effective_start_date <= l_period_end
121: instead of ppa.effective_date between
122: ASG.effective_start_date and ASG.effective_end_date
123: 02-Jun-2003 fusman 115.36 2965887 Checked for archive type in chk_gre_archive
124: and inserted archive_type in ff_archive_items.
125: 28-MAY-2003 tmehra 115.35 2981455 Made changes to the action_creation
126: Added code to error out in case
127: if the wage plan is missing at both
128: the Asg and the GRE level for CA.
208: quarter.
209:
210: 11/16/2000 asasthan 115.8 1494215 Added A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
211: and A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD
212: to update_ff_archive_items.
213: 22-AUG-2000 ashgupta 110.12 1382408 Changed the SQL statement of
214: c_rts_dup_wage cursor. This SQL was
215: changed due to Fidelity issue. Now
216: the cursor does not check for Multi
1713: begin
1714:
1715: l_step := 1;
1716:
1717: insert into ff_archive_items
1718: (ARCHIVE_ITEM_ID,
1719: USER_ENTITY_ID,
1720: CONTEXT1,
1721: VALUE,
1720: CONTEXT1,
1721: VALUE,
1722: ARCHIVE_TYPE)
1723: values
1724: (ff_archive_items_s.nextval,
1725: p_user_entity_id,
1726: p_context1,
1727: p_value,
1728: 'PPA'); /* Bug:2965887 */
1736: SEQUENCE_NO,
1737: CONTEXT,
1738: CONTEXT_ID)
1739: values
1740: (ff_archive_items_s.currval,
1741: p_sequence(i),
1742: p_context(i),
1743: p_context_id(i));
1744: end loop;
1745:
1746: exception
1747: when others then
1748: if l_step = 1 then
1749: hr_utility.trace('Error while inserting into ff_archive_items'
1750: || to_char(sqlcode));
1751: raise hr_utility.hr_error;
1752:
1753: elsif l_step = 2 then
1904: cursor c_chk_payroll_action is
1905: select 'Y'
1906: from dual
1907: where exists (select null
1908: from ff_archive_items fai
1909: where fai.context1 = p_payroll_action_id
1910: and archive_type = 'PPA'); /* Bug:2965887 */
1911: begin
1912:
2235: lead to wrong sum being calculated Correct this in below cursor*/
2236:
2237: CURSOR get_previous_fl_taxable IS
2238: select sum(to_number(nvl(value,'0')))
2239: from ff_archive_items ffai,
2240: pay_assignment_actions paa,
2241: pay_payroll_actions ppa
2242: where ffai.user_entity_id = l_user_entity_id
2243: and ffai.context1=to_char(paa.assignment_action_id)
2306:
2307: -- Cursor to get the sum of archived values in the Year for AR
2308: CURSOR get_previous_ar_value(l_user_entity_id IN NUMBER) IS
2309: select sum(to_number(nvl(value,'0')))
2310: from ff_archive_items ffai,
2311: pay_assignment_actions paa,
2312: pay_payroll_actions ppa
2313: where ffai.user_entity_id = l_user_entity_id
2314: and ffai.context1=to_char(paa.assignment_action_id)
2531:
2532: l_sui_oh_taxable_qtd := least(p_out_of_state_taxable,l_sui_oh_taxable_qtd);
2533:
2534:
2535: INSERT INTO ff_archive_items (archive_item_id,
2536: user_entity_id,
2537: context1,
2538: value)
2539: VALUES( ff_archive_items_s.NEXTVAL ,
2535: INSERT INTO ff_archive_items (archive_item_id,
2536: user_entity_id,
2537: context1,
2538: value)
2539: VALUES( ff_archive_items_s.NEXTVAL ,
2540: l_user_entity_id ,
2541: p_assactid ,
2542: l_sui_oh_taxable_qtd );
2543:
2549: WHERE context_name = 'TAX_UNIT_ID';
2550:
2551: INSERT INTO ff_archive_item_contexts
2552: (archive_item_id,sequence_no,context,context_id)
2553: VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
2554:
2555: SELECT context_id
2556: INTO l_context_id_jurisdiction_code
2557: FROM ff_contexts
2558: WHERE context_name = 'JURISDICTION_CODE';
2559:
2560: INSERT INTO ff_archive_item_contexts
2561: (archive_item_id,sequence_no,context,context_id)
2562: VALUES (ff_archive_items_s.CURRVAL,1,l_oh_jurisdiction_code,l_context_id_jurisdiction_code);
2563:
2564: /* Added for Bug# 14097843 Starts here */
2565: ELSIF (g_sqwl_state = 'AR') THEN
2566:
2819: END IF;
2820: END IF;
2821:
2822: /* Archive SUI Adjusted Taxable Wages */
2823: INSERT INTO ff_archive_items (archive_item_id,
2824: user_entity_id,
2825: context1,
2826: value)
2827: VALUES(ff_archive_items_s.NEXTVAL ,
2823: INSERT INTO ff_archive_items (archive_item_id,
2824: user_entity_id,
2825: context1,
2826: value)
2827: VALUES(ff_archive_items_s.NEXTVAL ,
2828: l_sui_ar_tax_user_ent_id ,
2829: p_assactid ,
2830: l_sui_ar_adj_taxable_qtd );
2831:
2837: WHERE context_name = 'TAX_UNIT_ID';
2838:
2839: INSERT INTO ff_archive_item_contexts
2840: (archive_item_id,sequence_no,context,context_id)
2841: VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
2842:
2843: SELECT context_id
2844: INTO l_context_id_jurisdiction_code
2845: FROM ff_contexts
2846: WHERE context_name = 'JURISDICTION_CODE';
2847:
2848: INSERT INTO ff_archive_item_contexts
2849: (archive_item_id,sequence_no,context,context_id)
2850: VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
2851:
2852: /* Archive Out of State Wages */
2853: INSERT INTO ff_archive_items (archive_item_id,
2854: user_entity_id,
2849: (archive_item_id,sequence_no,context,context_id)
2850: VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
2851:
2852: /* Archive Out of State Wages */
2853: INSERT INTO ff_archive_items (archive_item_id,
2854: user_entity_id,
2855: context1,
2856: value)
2857: VALUES(ff_archive_items_s.NEXTVAL ,
2853: INSERT INTO ff_archive_items (archive_item_id,
2854: user_entity_id,
2855: context1,
2856: value)
2857: VALUES(ff_archive_items_s.NEXTVAL ,
2858: l_sui_ar_oos_rpt_user_ent_id,
2859: p_assactid ,
2860: l_sui_ar_oos_rpt_qtd );
2861:
2862: hr_utility.trace('Archived the AR Out of State Wages');
2863:
2864: INSERT INTO ff_archive_item_contexts
2865: (archive_item_id,sequence_no,context,context_id)
2866: VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
2867:
2868: INSERT INTO ff_archive_item_contexts
2869: (archive_item_id,sequence_no,context,context_id)
2870: VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
2866: VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
2867:
2868: INSERT INTO ff_archive_item_contexts
2869: (archive_item_id,sequence_no,context,context_id)
2870: VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
2871:
2872: /* Archive Out of State Wages State Code */
2873: INSERT INTO ff_archive_items (archive_item_id,
2874: user_entity_id,
2869: (archive_item_id,sequence_no,context,context_id)
2870: VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
2871:
2872: /* Archive Out of State Wages State Code */
2873: INSERT INTO ff_archive_items (archive_item_id,
2874: user_entity_id,
2875: context1,
2876: value)
2877: VALUES(ff_archive_items_s.NEXTVAL ,
2873: INSERT INTO ff_archive_items (archive_item_id,
2874: user_entity_id,
2875: context1,
2876: value)
2877: VALUES(ff_archive_items_s.NEXTVAL ,
2878: l_sui_ar_oos_stcd_user_ent_id,
2879: p_assactid ,
2880: l_sui_ar_oos_state_code );
2881:
3022:
3023:
3024: hr_utility.trace('l_sui_fl_taxable_qtd after adjustment is'||l_sui_fl_taxable_qtd);
3025:
3026: INSERT INTO ff_archive_items (archive_item_id,
3027: user_entity_id,
3028: context1,
3029: value)
3030: VALUES( ff_archive_items_s.NEXTVAL ,
3026: INSERT INTO ff_archive_items (archive_item_id,
3027: user_entity_id,
3028: context1,
3029: value)
3030: VALUES( ff_archive_items_s.NEXTVAL ,
3031: l_user_entity_id ,
3032: p_assactid ,
3033: l_sui_fl_taxable_qtd );
3034:
3040: WHERE context_name = 'TAX_UNIT_ID';
3041:
3042: INSERT INTO ff_archive_item_contexts
3043: (archive_item_id,sequence_no,context,context_id)
3044: VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
3045:
3046: SELECT context_id
3047: INTO l_context_id_jurisdiction_code
3048: FROM ff_contexts
3049: WHERE context_name = 'JURISDICTION_CODE';
3050:
3051: INSERT INTO ff_archive_item_contexts
3052: (archive_item_id,sequence_no,context,context_id)
3053: VALUES (ff_archive_items_s.CURRVAL,1,fl_jurisdiction_code,l_context_id_jurisdiction_code);
3054:
3055: ELSIF (g_sqwl_state = 'CA') THEN
3056:
3057: l_user_entity_id :=get_user_entity_id('A_SCL_ASG_US_CA_WAGE_PLAN_CODE');
3086: -- EXIT WHEN c_archive_wage_plan_code_rts%NOTFOUND;
3087: -- END IF;
3088: --
3089: --
3090: -- INSERT INTO ff_archive_items (archive_item_id,
3091: -- user_entity_id,
3092: -- context1,
3093: -- value)
3094: -- VALUES( ff_archive_items_s.NEXTVAL ,
3090: -- INSERT INTO ff_archive_items (archive_item_id,
3091: -- user_entity_id,
3092: -- context1,
3093: -- value)
3094: -- VALUES( ff_archive_items_s.NEXTVAL ,
3095: -- l_user_entity_id ,
3096: -- p_assactid ,
3097: -- l_wage_plan_code );
3098: --
3100: -- INSERT INTO ff_archive_item_contexts (archive_item_id,
3101: -- sequence_no ,
3102: -- context ,
3103: -- context_id )
3104: -- VALUES (ff_archive_items_s.currval,
3105: -- 1 ,
3106: -- l_assignment_id ,
3107: -- l_context_id_assignment_id);
3108: -- END LOOP;
3125: hr_utility.trace('In Archive Wage Plan Code loop ');
3126:
3127: l_wage_plan_code := c_rec.aei_information3;
3128:
3129: INSERT INTO ff_archive_items (archive_item_id,
3130: user_entity_id,
3131: context1,
3132: value)
3133: VALUES( ff_archive_items_s.NEXTVAL ,
3129: INSERT INTO ff_archive_items (archive_item_id,
3130: user_entity_id,
3131: context1,
3132: value)
3133: VALUES( ff_archive_items_s.NEXTVAL ,
3134: l_user_entity_id ,
3135: p_assactid ,
3136: l_wage_plan_code );
3137:
3138: INSERT INTO ff_archive_item_contexts (archive_item_id,
3139: sequence_no ,
3140: context ,
3141: context_id )
3142: VALUES (ff_archive_items_s.currval,
3143: 1 ,
3144: asgid ,
3145: l_context_id_assignment_id);
3146:
3160: END LOOP;
3161:
3162:
3163:
3164: INSERT INTO ff_archive_items (archive_item_id,
3165: user_entity_id,
3166: context1,
3167: value)
3168: VALUES( ff_archive_items_s.NEXTVAL ,
3164: INSERT INTO ff_archive_items (archive_item_id,
3165: user_entity_id,
3166: context1,
3167: value)
3168: VALUES( ff_archive_items_s.NEXTVAL ,
3169: l_user_entity_id ,
3170: p_assactid ,
3171: l_wage_plan_code );
3172:
3173: INSERT INTO ff_archive_item_contexts (archive_item_id,
3174: sequence_no ,
3175: context ,
3176: context_id )
3177: VALUES (ff_archive_items_s.currval,
3178: 1 ,
3179: asgid ,
3180: l_context_id_assignment_id);
3181:
3303: hr_utility.trace('Problem in archive_data');
3304:
3305: end archive_data;
3306:
3307: /* Name : update_ff_archive_items
3308: Purpose : Given the SQWL payroll_action_id, identifies SQWL assignment actions for which
3309: serial number is set to 1 (those employee assgnment actions who doesnt have balances
3310: in the 4th Qtr while running 4th qtr new york SQWL report ) and update QTD balances
3311: to zero for the assignment action in ff_archive_items.
3307: /* Name : update_ff_archive_items
3308: Purpose : Given the SQWL payroll_action_id, identifies SQWL assignment actions for which
3309: serial number is set to 1 (those employee assgnment actions who doesnt have balances
3310: in the 4th Qtr while running 4th qtr new york SQWL report ) and update QTD balances
3311: to zero for the assignment action in ff_archive_items.
3312: Arguments : SQWL Payroll Action ID
3313: */
3314:
3315: /* added A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD and A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
3314:
3315: /* added A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD and A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
3316: for bug 1494215 of NY Q4 */
3317:
3318: FUNCTION Update_ff_archive_items (p_payroll_action_id in VARCHAR2)
3319: return varchar is
3320: BEGIN
3321: update ff_archive_items ffai
3322: set ffai.value = 0
3317:
3318: FUNCTION Update_ff_archive_items (p_payroll_action_id in VARCHAR2)
3319: return varchar is
3320: BEGIN
3321: update ff_archive_items ffai
3322: set ffai.value = 0
3323: where ffai.user_entity_id in (
3324: select user_entity_id
3325: from ff_database_items
3352: return 'Y';
3353:
3354: EXCEPTION
3355: when OTHERS then
3356: hr_utility.trace('Error while updating ff_archive_items ');
3357: return 'N';
3358: END Update_ff_archive_items;
3359:
3360:
3354: EXCEPTION
3355: when OTHERS then
3356: hr_utility.trace('Error while updating ff_archive_items ');
3357: return 'N';
3358: END Update_ff_archive_items;
3359:
3360:
3361: --Name
3362: -- preprocess_check
3551: ORDER BY ASG.effective_start_date desc ;
3552:
3553: l_location_id per_all_assignments_f.location_id%type;
3554: l_user_entity_id ff_user_entities.user_entity_id%type;
3555: l_archive_item_id ff_archive_items.archive_item_id%type;
3556: l_object_version_number ff_archive_items.object_version_number%type;
3557: l_some_warning boolean;
3558:
3559: l_procedure varchar2(16) := 'archive_asg_locs';
3552:
3553: l_location_id per_all_assignments_f.location_id%type;
3554: l_user_entity_id ff_user_entities.user_entity_id%type;
3555: l_archive_item_id ff_archive_items.archive_item_id%type;
3556: l_object_version_number ff_archive_items.object_version_number%type;
3557: l_some_warning boolean;
3558:
3559: l_procedure varchar2(16) := 'archive_asg_locs';
3560: