1 PACKAGE BODY PA_CLIENT_EXTN_GEN_ASSET_LINES AS
2 -- $Header: PAPGALCB.pls 120.3 2006/11/10 00:31:08 skannoji noship $
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 cursor get_project_asset_id is
32 select project_asset_id
33 from pa_project_asset_assignments ppaa
34 where ppaa.task_id = p_task_id
35 and ppaa.project_id = p_project_id
36 and nvl(ppaa.attribute8,'~!@#') = nvl(p_attribute8,'~!@#')
37 and nvl(ppaa.attribute9,'~!@#') = nvl(p_attribute9,'~!@#')
38 and nvl(ppaa.attribute10,'~!@#') = nvl(p_attribute10,'~!@#')
39 /* Start Bug fix:2956569 : attribute6,7 Should be used only when the nl_installed flag = Y*/
40 AND ( (NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N') = 'Y'
41 AND nvl(ppaa.attribute6,'~!@#') = nvl(p_attribute6, '~!@#') --crl_inventory
42 AND nvl(ppaa.attribute7,'~!@#') = nvl(p_attribute7, '~!@#') --serial_number
43 )
44 OR
45 NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N') = 'N'
46 );
47 /* End Bug fix:2956569 */
48
49
50 /* Adding cursor for IPA for bug 5637615 */
51 CURSOR get_crl_instal_rec is
52 SELECT asset_name_id
53 FROM ipa_asset_naming_conventions ;
54
55 dummy number ;
56 v_project_asset_id number;
57 l_crl_rec ipa_asset_naming_convents_all.asset_name_id%TYPE;
58 BEGIN
59
60 /* Adding another check for IPA for bug 5637615 */
61 OPEN get_crl_instal_rec;
62 FETCH get_crl_instal_rec into l_crl_rec;
63 IF get_crl_instal_rec%notfound then
64 l_crl_rec :=NULL;
65 END IF;
66 CLOSE get_crl_instal_rec;
67
68
69 /* This is the default code for CRL Auto Asset line assognment and this
70 portion of the code needs to be un commented if CRL is installed */
71 IF (PA_INSTALL.is_product_installed('IPA')) AND (l_crl_rec IS NOT NULL) THEN
72 Select 1 into dummy
73 from pa_project_types ppt,
74 pa_tasks pt,
75 pa_tasks pt2,
76 pa_projects_all ppr -- Changed to _ALL as part of MOAC changes
77 where p_task_id = pt.task_id and
78 p_project_id = pt.project_id and
79 pt.project_id = ppr.project_id and
80 ppr.template_flag <> 'Y' and
81 ppr.project_status_code <> 'CLOSED' and
82 ppr.project_type = ppt.project_type and
83 ppt.cip_grouping_method_code = 'CIPGCE' and
84 --nvl(ppt.attribute10,'N') = 'Y' and
85 ppt.project_type_class_code = 'CAPITAL' and
86 pt2.task_id = pt.top_task_id and
87 nvl(ppr.attribute10,'Y') ='Y' and
88 nvl(pt2.attribute9,'Y') ='Y' and
89 ppt.interface_asset_cost_code = 'F';
90
91 if (sql%found) then
92 open get_project_asset_id;
93 fetch get_project_asset_id into v_project_asset_id;
94 if get_project_asset_id%notfound then
95 v_project_asset_id :=0;
96 end if;
97 close get_project_asset_id;
98 end if;
99 x_asset_id := v_project_asset_id;
100 ELSE
101 null;
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 x_asset_id := 0;
107 --null;
108 END;
109 END;