[Home] [Help]
1: package body pay_hk_ir56_archive AS
2: -- $Header: pyhk56ar.pkb 120.3.12010000.6 2009/07/28 22:58:41 jalin ship $
3:
4: -- Copyright (C) 1999 Oracle Corporation
5: -- All Rights Reserved
3:
4: -- Copyright (C) 1999 Oracle Corporation
5: -- All Rights Reserved
6: --
7: -- Script to create pay_hk_ir56_archive package body
8: -- for HK IR56B Archive
9: --
10: -- Change List
11: -- ===========
83: -- 11 Mar 2003 srrajago 2829320 In the cursor ir56_Employee_info, included substr function for
84: -- address_lines column so that 240 characters are only fetched.
85: -- In the procedure Archive_Employee_details -> record type archive_rec,
86: -- the item_name and item_value declarations modified.
87: -- 12 Mar 2003 srrajago 2843765 In the call to pay_hk_ir56.get_emoluments, the parameter value passed
88: -- for balance name was 'MAGTAPE_ORSO' and 'MAGTAPE_MPF'. These have been
89: -- modified to 'HK_MAGTAPE_ORSO' and 'HK_MAGTAPE_MPF' respectively.
90: -- 27 Mar 2003 srrajago 2853776 Included a join in the where clause of the cursor quarters_info to
91: -- pick up the correct quarters balance data when reversal is run.
115: -- Cursor 'ir56_Employee_info' also modified.A join with period_of_service_id
116: -- added.
117: -- 15 Dec 2003 srrajago 3193217 Modified the procedure 'archive_balance_details'. Call to the function
118: -- 'get_emoluments' and the return values substitution have been changed as package
119: -- 'pay_hk_ir56' modified. Cursor 'ir56_balances' removed.
120: -- 25 May 2004 avenkatk 3642506 #In Cursor ir56_Employee_info,modified the cursor to fetch the latest Person Details.
121: -- #In Cursor ir56_Spouse_info,modified the cursor to fetch the latest Spouse Info Details.
122: --
123: -- 09 Dec 2004 jkarouza 3916743 Modified cursor check_run for performance improvement for Bug 3916743.
964: and TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY'));
965:
966: rec_max_assign_action_id max_assign_action_id%ROWTYPE;
967:
968: l_emol_details pay_hk_ir56.g_emol_details_tab;
969: i Number := 1;
970: v_period_format Varchar2(100); -- Holds the period in the format yyyymmdd-yyyymmdd
971:
972: Begin
973: OPEN max_assign_action_id(g_assignment_id,p_reporting_year);
974: FETCH max_assign_action_id INTO rec_max_assign_action_id;
975: CLOSE max_assign_action_id;
976:
977: l_emol_details := PAY_HK_IR56.GET_EMOLUMENTS
978: (g_assignment_id,
979: rec_max_assign_action_id.assignment_action_id,
980: p_legal_entity_id,
981: p_reporting_year);
1078: max(b.QUARTERS_ER_TO_LANDLORD) QUARTERS_ER_TO_LANDLORD,
1079: max(b.QUARTERS_EE_TO_LANDLORD) QUARTERS_EE_TO_LANDLORD,
1080: max(b.QUARTERS_REFUND_TO_EE) QUARTERS_REFUND_TO_EE,
1081: max(b.QUARTERS_EE_TO_ER) QUARTERS_EE_TO_ER
1082: FROM pay_hk_ir56_quarters_info_v b,
1083: per_periods_of_service pps,
1084: per_assignments_f paa
1085: WHERE b.assignment_id = c_assignment_id
1086: AND paa.assignment_id = b.assignment_id
1086: AND paa.assignment_id = b.assignment_id
1087: AND paa.person_id = pps.person_id
1088: AND paa.period_of_service_id = pps.period_of_service_id /* Bug No : 2824718 */
1089: AND b.action_sequence = (SELECT max(action_sequence)
1090: FROM pay_hk_ir56_quarters_info_v
1091: WHERE assignment_id = b.assignment_id
1092: AND source_id = b.source_id /* Bug No : 2853776 */
1093: AND start_date between
1094: to_date('01/04/'||to_char(to_number(p_reporting_year)-1),'DD/MM/YYYY')
1256:
1257: CURSOR os_info (c_assignment_id in pay_assignment_actions.assignment_id%TYPE)
1258: IS
1259: SELECT *
1260: FROM pay_hk_ir56_overseas_concern_v
1261: WHERE assignment_id = c_assignment_id
1262: AND tax_reporting_year = p_reporting_year;
1263:
1264: os_rec os_info%ROWTYPE;
1815: End;
1816:
1817: End submit_report;
1818:
1819: End pay_hk_ir56_archive;