[Home] [Help]
1: package body pay_hk_ir56_archive AS
2: -- $Header: pyhk56ar.pkb 120.3.12010000.3 2008/08/06 07:22:41 ubhat 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.
895: and TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY'));
896:
897: rec_max_assign_action_id max_assign_action_id%ROWTYPE;
898:
899: l_emol_details pay_hk_ir56.g_emol_details_tab;
900: i Number := 1;
901: v_period_format Varchar2(100); -- Holds the period in the format yyyymmdd-yyyymmdd
902:
903: Begin
904: OPEN max_assign_action_id(g_assignment_id,p_reporting_year);
905: FETCH max_assign_action_id INTO rec_max_assign_action_id;
906: CLOSE max_assign_action_id;
907:
908: l_emol_details := PAY_HK_IR56.GET_EMOLUMENTS
909: (g_assignment_id,
910: rec_max_assign_action_id.assignment_action_id,
911: p_legal_entity_id,
912: p_reporting_year);
1009: max(b.QUARTERS_ER_TO_LANDLORD) QUARTERS_ER_TO_LANDLORD,
1010: max(b.QUARTERS_EE_TO_LANDLORD) QUARTERS_EE_TO_LANDLORD,
1011: max(b.QUARTERS_REFUND_TO_EE) QUARTERS_REFUND_TO_EE,
1012: max(b.QUARTERS_EE_TO_ER) QUARTERS_EE_TO_ER
1013: FROM pay_hk_ir56_quarters_info_v b,
1014: per_periods_of_service pps,
1015: per_assignments_f paa
1016: WHERE b.assignment_id = c_assignment_id
1017: AND paa.assignment_id = b.assignment_id
1017: AND paa.assignment_id = b.assignment_id
1018: AND paa.person_id = pps.person_id
1019: AND paa.period_of_service_id = pps.period_of_service_id /* Bug No : 2824718 */
1020: AND b.action_sequence = (SELECT max(action_sequence)
1021: FROM pay_hk_ir56_quarters_info_v
1022: WHERE assignment_id = b.assignment_id
1023: AND source_id = b.source_id /* Bug No : 2853776 */
1024: AND start_date between
1025: to_date('01/04/'||to_char(to_number(p_reporting_year)-1),'DD/MM/YYYY')
1187:
1188: CURSOR os_info (c_assignment_id in pay_assignment_actions.assignment_id%TYPE)
1189: IS
1190: SELECT *
1191: FROM pay_hk_ir56_overseas_concern_v
1192: WHERE assignment_id = c_assignment_id
1193: AND tax_reporting_year = p_reporting_year;
1194:
1195: os_rec os_info%ROWTYPE;
1746: End;
1747:
1748: End submit_report;
1749:
1750: End pay_hk_ir56_archive;