[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_BOND_REGISTER_PKG
Source
1 PACKAGE BODY jai_cmn_bond_register_pkg
2 /* $Header: jai_cmn_bond_reg.plb 120.1 2005/07/20 12:57:03 avallabh ship $ */
3 as
4
5 /* --------------------------------------------------------------------------------------
6 Filename:
7
8 Change History:
9
10 Date Bug Remarks
11 --------- ---------- -------------------------------------------------------------
12 08-Jun-2005 Version 116.2 jai_cmn_bond_reg -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
13 as required for CASE COMPLAINCE.
14 */
15
16 Procedure get_register_id
17 (
18 p_organization_id in number,
19 p_location_id in number,
20 p_order_invoice_id in Number,
21 p_order_invoice_type in varchar2,
22 p_register_id out NOCOPY number,
23 p_register_code out NOCOPY varchar2
24 )
25 is
26 cursor c_get_register is
27 select hdr.register_id ,
28 bond_number ,hdr.register_code
29 from JAI_OM_OE_BOND_REG_HDRS hdr,
30 JAI_OM_OE_BOND_REG_DTLS dtl
31 where hdr.register_id = dtl.register_id
32 and hdr.organization_id = p_organization_id
33 and hdr.location_id = p_location_id
34 and dtl.order_flag = p_order_invoice_type
35 and dtl.order_type_id = p_order_invoice_id;
36
37 v_register_id Number;
38 v_bond_id Number;
39 v_bond_number JAI_OM_OE_BOND_REG_HDRS.bond_number%type;
40 v_reg_code JAI_OM_OE_BOND_REG_HDRS.register_code%type;
41
42 lv_object_name VARCHAR2(61); -- := '<Package_name>.<procedure_name>'; /* Added by Ramananda for bug#4407165 */
43
44 begin
45
46 lv_object_name := 'jai_cmn_bond_register_pkg.get_register_id'; /* Added by Ramananda for bug#4407165 */
47
48 open c_get_register;
49 fetch c_get_register into v_register_id , v_bond_number,v_reg_code;
50 close c_get_register;
51
52 p_register_id := v_register_id;
53 p_register_code := v_reg_code;
54
55 /* Added by Ramananda for bug#4407165 */
56 EXCEPTION
57 WHEN OTHERS THEN
58 p_register_id := null;
59 p_register_code := null;
60 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
61 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
62 app_exception.raise_exception;
63
64 End get_register_id;
65
66 Procedure get_register_details
67 (
68 p_register_id in number,
69 p_register_balance OUT NOCOPY number,
70 p_register_expiry_date OUT NOCOPY date,
71 p_lou_flag OUT NOCOPY varchar2
72 )
73 is
74 cursor c_get_register_details is
75 select hdr.register_id ,
76 hdr.bond_expiry_date, hdr.lou_flag , register_code
77 from JAI_OM_OE_BOND_REG_HDRS hdr
78 where hdr.register_id = p_register_id;
79
80 Cursor c_get_bond_register_balance is
81 Select register_balance
82 from JAI_OM_OE_BOND_TRXS
83 where register_id = p_register_id
84 and transaction_id =
85 (select max(transaction_id)
86 from JAI_OM_OE_BOND_TRXS
87 where register_id = p_register_id
88 );
89
90 Cursor c_get_other_register_balance is
91 Select RG23D_REGISTER_BALANCE
92 from JAI_OM_OE_BOND_TRXS
93 where register_id = p_register_id
94 and transaction_id =
95 (select max(transaction_id)
96 from JAI_OM_OE_BOND_TRXS
97 where register_id = p_register_id
98 );
99
100 v_register_id Number;
101 v_register_balance Number;
102 v_expiry_date Date;
103 v_lou_flag Varchar2(1);
104 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%type;
105 lv_object_name VARCHAR2(61); -- := '<Package_name>.<procedure_name>'; /* Added by Ramananda for bug#4407165 */
106
107 begin
108
109 lv_object_name := 'jai_cmn_bond_register_pkg.get_register_details'; /* Added by Ramananda for bug#4407165 */
110
111 open c_get_register_details ;
112 fetch c_get_register_details into v_register_id,v_expiry_date,v_lou_flag , v_register_code ;
113 close c_get_register_details ;
114
115 if NVL(UPPER(v_register_code),'N') = 'BOND_REG' then
116 open c_get_bond_register_balance;
117 fetch c_get_bond_register_balance into v_register_balance;
118 close c_get_bond_register_balance;
119 elsif NVL(UPPER(v_register_code),'N') = '23D_EXPORT_WITHOUT_EXCISE' then
120 open c_get_other_register_balance;
121 fetch c_get_other_register_balance into v_register_balance;
122 close c_get_other_register_balance;
123 end if;
124
125 p_register_balance := v_register_balance;
126 p_register_expiry_date := v_expiry_date;
127 p_lou_flag := v_lou_flag;
128
129 /* Added by Ramananda for bug#4407165 */
130 EXCEPTION
131 WHEN OTHERS THEN
132 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
133 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
134 app_exception.raise_exception;
135
136
137 End get_register_details;
138
139 end jai_cmn_bond_register_pkg;