1 PACKAGE BODY PAY_SA_PAYMENT_STATUS_PKG
2 /* $Header: pysastat.pkb 120.0.12010000.1 2009/06/09 10:56:36 bkeshary noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_sa_payment_status_pkg
21
22 Description : Package contains functions for checking the status of payment
23 for Saudi Payroll Register
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31
32 08-Jun-2009 BKeshary 115.0 7648285 Created.
33 ****************************************************************************/
34 AS
35
36 function get_sa_payment_status (p_assignment_action_id in number)
37 return varchar2 is
38
39 cursor c_purge_run(p_assignment_action_id in number) is
40 select 1
41 from pay_assignment_actions paa
42 where paa.assignment_action_id = p_assignment_action_id
43 and not exists
44 (select 1
45 from pay_action_interlocks ai
46 where ai.locking_action_id = paa.assignment_action_id )
47 and exists
48 (select 1 from pay_assignment_actions paa2,
49 pay_payroll_actions ppa2
50 where paa2.assignment_id = paa.assignment_id
51 and paa2.payroll_action_id = ppa2.payroll_action_id
52 and ppa2.action_type = 'Z');
53
54 cursor c_get_archived(p_assignment_action_id in number) is
55 select count(*)
56 from pay_action_information
57 where action_context_id = p_assignment_action_id
58 and action_information_category = 'EMEA ELEMENT INFO'
59 and action_information3 in ('E','D');
60
61 cursor c_get_prepay_id(p_assignment_action_id in number) is
62 SELECT paa.assignment_action_id,ppp.pre_payment_id
63 FROM pay_action_interlocks pai
64 ,pay_assignment_actions paa
65 ,pay_payroll_actions ppa
66 ,pay_pre_payments ppp
67 WHERE pai.locked_action_id = paa.assignment_action_id
68 AND paa.payroll_action_id = ppa.payroll_action_id
69 AND ppa.action_type in ('P','U')
70 AND ppa.action_status = 'C'
71 AND paa.assignment_action_id = ppp.assignment_action_id
72 AND pai.locking_action_id = p_assignment_action_id;
73
74
75
76 l_purge_run VARCHAR2(1);
77 ln_count NUMBER;
78 l_status VARCHAR2(10);
79 l_pre_pay_asg_id NUMBER;
80 l_pre_pay_id NUMBER;
81
82 begin
83
84 ln_count := 0;
85
86 open c_purge_run(p_assignment_action_id);
87 fetch c_purge_run into l_purge_run;
88 if c_purge_run%found then
89 open c_get_archived(p_assignment_action_id);
90 fetch c_get_archived into ln_count;
91 /*Check whether archive data exists for this assignment_action_id*/
92 if (ln_count <> 0) then
93 return('Unavailable');
94 end if;
95 close c_get_archived;
96 else
97 open c_get_prepay_id(p_assignment_action_id);
98 fetch c_get_prepay_id into l_pre_pay_asg_id,l_pre_pay_id;
99 /* Fetch the Pre-payment assignmnt action id, pre payment id */
100 if c_get_prepay_id%found then
101 l_status := pay_assignment_actions_pkg.get_payment_status(l_pre_pay_asg_id,l_pre_pay_id);
102 return l_status;
103 end if;
104 close c_get_prepay_id;
105 end if;
106 close c_purge_run;
107
108 end get_sa_payment_status;
109
110 --
111 END PAY_SA_PAYMENT_STATUS_PKG;
[Home] [Help]