1 PACKAGE BODY PA_CLIENT_EXTN_GEN_ASSET_LINES AS
2 -- $Header: PAPGALCB.pls 120.3.12010000.2 2009/06/09 09:04:59 abjacob ship $
3
4 PROCEDURE CLIENT_ASSET_ASSIGNMENT(p_project_id IN NUMBER,
5 p_task_id IN NUMBER,
6 p_expnd_item_id IN NUMBER,
7 p_expnd_id IN NUMBER,
8 p_expnd_type IN VARCHAR2,
9 p_expnd_category IN VARCHAR2,
10 p_expnd_type_class IN VARCHAR2,
11 p_non_labor_org_id IN NUMBER,
12 p_non_labor_resource IN VARCHAR2,
13 p_invoice_id IN NUMBER,
14 p_inv_dist_line_number IN NUMBER,
15 p_vendor_id IN NUMBER,
16 p_employee_id IN NUMBER,
17 p_attribute1 IN VARCHAR2,
18 p_attribute2 IN VARCHAR2,
19 p_attribute3 IN VARCHAR2,
20 p_attribute4 IN VARCHAR2,
21 p_attribute5 IN VARCHAR2,
22 p_attribute6 IN VARCHAR2,
23 p_attribute7 IN VARCHAR2,
24 p_attribute8 IN VARCHAR2,
25 p_attribute9 IN VARCHAR2,
26 p_attribute10 IN VARCHAR2,
27 p_attribute_category IN VARCHAR2,
28 p_in_service_through_date IN DATE,
29 x_asset_id IN OUT NOCOPY NUMBER) IS
30
31 nl_installed VARCHAR2(1); -- bug 7524772
32
33 /* Bug 7524772: Broke this cursor into two cursors based on the condition IPA_ASSET_MECH_APIS_PKG.g_nl_installed to facilitate proper index to be picked up*/
34 /* cursor get_project_asset_id is
35 select project_asset_id
36 from pa_project_asset_assignments ppaa
37 where ppaa.task_id = p_task_id
38 and ppaa.project_id = p_project_id
39 and nvl(ppaa.attribute8,'~!@#') = nvl(p_attribute8,'~!@#')
40 and nvl(ppaa.attribute9,'~!@#') = nvl(p_attribute9,'~!@#')
41 and nvl(ppaa.attribute10,'~!@#') = nvl(p_attribute10,'~!@#')
42 /* Start Bug fix:2956569 : attribute6,7 Should be used only when the nl_installed flag = Y
43 AND ( (NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N') = 'Y'
44 AND nvl(ppaa.attribute6,'~!@#') = nvl(p_attribute6, '~!@#') --crl_inventory
45 AND nvl(ppaa.attribute7,'~!@#') = nvl(p_attribute7, '~!@#') --serial_number
46 )
47 OR
48 NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N') = 'N'
49 );
50 End Bug fix:2956569 */
51
52 cursor get_project_asset_id_nl_ins is
53 select project_asset_id
54 from pa_project_asset_assignments ppaa
55 where ppaa.task_id = p_task_id
56 and ppaa.project_id = p_project_id
57 AND nvl(ppaa.attribute6,'~!@#') = nvl(p_attribute6, '~!@#') --crl_inventory
58 AND nvl(ppaa.attribute7,'~!@#') = nvl(p_attribute7, '~!@#') --serial_number
59 and nvl(ppaa.attribute8,'~!@#') = nvl(p_attribute8,'~!@#')
60 and nvl(ppaa.attribute9,'~!@#') = nvl(p_attribute9,'~!@#')
61 and nvl(ppaa.attribute10,'~!@#') = nvl(p_attribute10,'~!@#')
62 ;
63
64 cursor get_project_asset_id_no_ins is
65 select project_asset_id
66 from pa_project_asset_assignments ppaa
67 where ppaa.task_id = p_task_id
68 and ppaa.project_id = p_project_id
69 and nvl(ppaa.attribute8,'~!@#') = nvl(p_attribute8,'~!@#')
70 and nvl(ppaa.attribute9,'~!@#') = nvl(p_attribute9,'~!@#')
71 and nvl(ppaa.attribute10,'~!@#') = nvl(p_attribute10,'~!@#')
72 ;
73
74 /* Bug 7524772 end*/
75
76
77
78 /* Adding cursor for IPA for bug 5637615 */
79 CURSOR get_crl_instal_rec is
80 SELECT asset_name_id
81 FROM ipa_asset_naming_conventions ;
82
83 dummy number ;
84 v_project_asset_id number;
85 l_crl_rec ipa_asset_naming_convents_all.asset_name_id%TYPE;
86 BEGIN
87
88 nl_installed:= NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N');
89
90 /* Adding another check for IPA for bug 5637615 */
91 OPEN get_crl_instal_rec;
92 FETCH get_crl_instal_rec into l_crl_rec;
93 IF get_crl_instal_rec%notfound then
94 l_crl_rec :=NULL;
95 END IF;
96 CLOSE get_crl_instal_rec;
97
98
99 /* This is the default code for CRL Auto Asset line assognment and this
100 portion of the code needs to be un commented if CRL is installed */
101 IF (PA_INSTALL.is_product_installed('IPA')) AND (l_crl_rec IS NOT NULL) THEN
102 Select 1 into dummy
103 from pa_project_types ppt,
104 pa_tasks pt,
105 pa_tasks pt2,
106 pa_projects_all ppr -- Changed to _ALL as part of MOAC changes
107 where p_task_id = pt.task_id and
108 p_project_id = pt.project_id and
109 pt.project_id = ppr.project_id and
110 ppr.template_flag <> 'Y' and
111 ppr.project_status_code <> 'CLOSED' and
112 ppr.project_type = ppt.project_type and
113 ppt.cip_grouping_method_code = 'CIPGCE' and
114 --nvl(ppt.attribute10,'N') = 'Y' and
115 ppt.project_type_class_code = 'CAPITAL' and
116 pt2.task_id = pt.top_task_id and
117 nvl(ppr.attribute10,'Y') ='Y' and
118 nvl(pt2.attribute9,'Y') ='Y' and
119 ppt.interface_asset_cost_code = 'F';
120
121 if (sql%found) then
122 if (nl_installed = 'Y') then -- Bug 7524772
123 open get_project_asset_id_nl_ins; -- Bug 7524772
124 fetch get_project_asset_id_nl_ins into v_project_asset_id;
125 if get_project_asset_id_nl_ins%notfound then
126 v_project_asset_id :=0;
127 end if;
128 close get_project_asset_id_nl_ins;
129 else
130 open get_project_asset_id_no_ins; -- Bug 7524772
131 fetch get_project_asset_id_no_ins into v_project_asset_id;
132 if get_project_asset_id_no_ins%notfound then
133 v_project_asset_id :=0;
134 end if;
135 close get_project_asset_id_no_ins;
136 end if;
137 end if;
138 x_asset_id := v_project_asset_id;
139 ELSE
140 null;
141 END IF;
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 x_asset_id := 0;
146 --null;
147 END;
148 END;
[Home] [Help]