1 package body poa_dbi_items_pkg as
2 /* $Header: poadbiitemb.pls 120.4 2006/10/13 13:50:51 nchava noship $ */
3
4
5 function insertion (p_item_id IN number,
6 p_org_id IN number,
7 p_category_id IN number,
8 p_vendor_product_num IN VARCHAR2,
9 p_vendor_id IN NUMBER,
10 p_description IN VARCHAR2) return number;
11
12
13 function getItemKey(p_item_id IN number,
14 p_org_id IN number,
15 p_category_id IN number,
16 p_vendor_product_num IN varchar2,
17 p_vendor_id IN NUMBER,
18 p_description IN VARCHAR2,
19 p_auto_insert_flag boolean) return number
20 is
21 l_item_key number;
22 l_category_id number := p_category_id;
23 l_vendor_id NUMBER := p_vendor_id;
24 l_org_id NUMBER := null;
25 l_description VARCHAR2(240) := p_description;
26 l_vendor_prod po_lines_all.vendor_product_num%TYPE := p_vendor_product_num;
27 BEGIN
28 begin
29 if(p_item_id is not null) then
30 l_category_id := null;
31 l_vendor_id := NULL;
32 l_vendor_prod := NULL;
33 l_org_id := p_org_id;
34
35 if (l_org_id is null) then
36 bis_collection_utilities.log('Item ' || p_item_id || ' has problems with its defining org', 2);
37 return -1;
38 end if;
39
40 SELECT /*+ FIRST_ROWS */ po_item_id
41 into l_item_key
42 from poa_items
43 where item_id = p_item_id
44 and organization_id = l_org_id
45 --and category_id = p_category_id
46 ;
47 ELSIF (p_vendor_product_num is NULL or p_vendor_id IS null) THEN /* null vendor_id should only happen to req-based items */
48 l_vendor_id := NULL;
49 l_vendor_prod := NULL;
50 IF (p_category_id IS NULL) THEN
51 raise_application_error (-20001, 'category_id is null for the record being collected.
52 itemdescription=' || p_description
53 ||' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), true );
54 END IF;
55 IF (p_description IS NULL) THEN
56 raise_application_error (-20002, 'item_description is null for the record being collected.
57 category=' || p_category_id
58 ||' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), true );
59 END IF;
60
61 select /*+ FIRST_ROWS */ po_item_id
62 into l_item_key
63 from poa_items
64 where item_id is NULL
65 AND organization_id IS null
66 and category_id = p_category_id
67 and description = p_description
68 and vendor_product_num is NULL
69 and vendor_id IS null ;
70 else
71 IF (p_description IS NULL) THEN
72 raise_application_error (-20002, 'item_description is null for the record being collected.
73 category=' || p_category_id
74 ||' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), true );
75 END IF;
76 select /*+ FIRST_ROWS */ po_item_id
77 into l_item_key
78 from poa_items
79 where item_id is NULL
80 AND organization_id IS null
81 and vendor_product_num = p_vendor_product_num
82 AND vendor_id = p_vendor_id;
83 end if;
84 exception
85 when no_data_found then
86 if(p_auto_insert_flag) THEN
87 l_item_key := insertion (p_item_id,l_org_id,l_category_id,l_vendor_prod,l_vendor_id, l_description);
88 else
89 return 0; -- EDW unassigned
90 end if;
91 when others then
92 raise;
93 end getItemKey;
94 return l_item_key;
95 end getItemKey;
96
97 function insertion (p_item_id IN number,
98 p_org_id IN number,
99 p_category_id IN number,
100 p_vendor_product_num IN VARCHAR2,
101 p_vendor_id IN number,
102 p_description IN varchar2) return number
103 IS
104 pragma AUTONOMOUS_TRANSACTION;
105 l_item_key NUMBER ;
106 begin
107 --lock table poa_items in exclusive mode;
108 insert into poa_items (po_item_id, item_id, organization_id, category_id, vendor_product_num, vendor_id, description)
109 values (poa_items_s.nextval,p_item_id,p_org_id,p_category_id,p_vendor_product_num, p_vendor_id, p_description)
110 returning po_item_id INTO l_item_key;
111 commit;
112 return l_item_key;
113 EXCEPTION
114 WHEN dup_val_on_index THEN
115 RETURN getitemkey(p_item_id, p_org_id, p_category_id, p_vendor_product_num, p_vendor_id, p_description);
116 WHEN OTHERS THEN
117 RAISE;
118 end insertion;
119
120
121
122 PROCEDURE refresh(Errbuf in out NOCOPY Varchar2,
123 Retcode in out NOCOPY VARCHAR2 ) IS
124 BEGIN
125 POA_DBI_UTIL_PKG.refresh('poa_items_mv');
126 EXCEPTION
127 WHEN OTHERS THEN
128 Errbuf:= Sqlerrm;
129 Retcode:=sqlcode;
130
131 ROLLBACK;
132 POA_LOG.debug_line('poa_dbi_items_pkg.refresh' || Sqlerrm || sqlcode || sysdate);
133 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
134 END refresh;
135
136
137 end poa_dbi_items_pkg;
[Home] [Help]