1 package body IGS_OR_ADDR_PKG AS
2 /* $Header: IGSOI07B.pls 115.5 2002/02/12 17:08:20 pkm ship $ */
3 /*************************************************************
4 Changed By : smanglm
5 Date : 2000/08/25
6 Purpose : to remove the procedures like insert_row, update_row,lock_row,
7 before DML, check_parent_existence,check_child_existence,
8 check_constraints, set_column_values, after_dml for
9 TCA related work as this would now be taken care through TCA
10 table handlers.
11 Know limitations, enhancements or remarks
12 Change History
13 Who When What
14
15 (reverse chronological order - newest change first)
16 ***************************************************************/
17
18 l_rowid VARCHAR2(25);
19
20 FUNCTION Get_PK_For_Validation (
21 x_org_unit_cd IN VARCHAR2,
22 x_ou_start_dt IN DATE,
23 x_addr_type IN VARCHAR2,
24 x_start_dt IN DATE
25 )RETURN BOOLEAN AS
26 CURSOR cur_rowid is
27 SELECT rowid
28 FROM IGS_OR_ADDR
29 WHERE org_unit_cd = x_org_unit_cd
30 AND ou_start_dt = x_ou_start_dt
31 AND addr_type = x_addr_type
32 AND start_dt = x_start_dt
33 FOR UPDATE NOWAIT;
34 lv_rowid cur_rowid%RowType;
35 BEGIN
36 Open cur_rowid;
37 Fetch cur_rowid INTO lv_rowid;
38 IF (cur_rowid%FOUND) THEN
39 Close cur_rowid;
40 RETURN(TRUE);
41 ELSE
42 Close cur_rowid;
43 RETURN(FALSE);
44 END IF;
45 END Get_PK_For_Validation;
46
47 PROCEDURE GET_FK_IGS_CO_ADDR_TYPE (
48 x_addr_type IN VARCHAR2
49 ) AS
50 CURSOR cur_rowid IS
51 SELECT rowid
52 FROM IGS_OR_ADDR
53 WHERE addr_type = x_addr_type ;
54 lv_rowid cur_rowid%RowType;
55 BEGIN
56 Open cur_rowid;
57 Fetch cur_rowid INTO lv_rowid;
58 IF (cur_rowid%FOUND) THEN
59 Close cur_rowid;
60 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OA_ADT_FK');
61 IGS_GE_MSG_STACK.ADD;
62 App_Exception.Raise_Exception;
63 Return;
64 END IF;
65 Close cur_rowid;
66 END GET_FK_IGS_CO_ADDR_TYPE;
67
68 PROCEDURE GET_FK_IGS_OR_UNIT (
69 x_org_unit_cd IN VARCHAR2,
70 x_start_dt IN DATE
71 ) AS
72 CURSOR cur_rowid IS
73 SELECT rowid
74 FROM IGS_OR_ADDR
75 WHERE org_unit_cd = x_org_unit_cd
76 AND ou_start_dt = x_start_dt ;
77 lv_rowid cur_rowid%RowType;
78 BEGIN
79 Open cur_rowid;
80 Fetch cur_rowid INTO lv_rowid;
81 IF (cur_rowid%FOUND) THEN
82 Close cur_rowid;
83 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OA_OU_FK');
84 IGS_GE_MSG_STACK.ADD;
85 App_Exception.Raise_Exception;
86 Return;
87 END IF;
88 Close cur_rowid;
89 END GET_FK_IGS_OR_UNIT;
90
91 PROCEDURE GET_FK_IGS_PE_SUBURB_POSTCD (
92 x_postcode IN NUMBER
93 ) AS
94 CURSOR cur_rowid IS
95 SELECT rowid
96 FROM IGS_OR_ADDR
97 WHERE postal_code = x_postcode ;
98 lv_rowid cur_rowid%RowType;
99 BEGIN
100 Open cur_rowid;
101 Fetch cur_rowid INTO lv_rowid;
102 IF (cur_rowid%FOUND) THEN
103 Close cur_rowid;
104 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OA_SP_FK');
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 Return;
108 END IF;
109 Close cur_rowid;
110 END GET_FK_IGS_PE_SUBURB_POSTCD;
111
112 end IGS_OR_ADDR_PKG;