1 Package Body IBC_LOAD_CITEMS_PVT as
2 /* $Header: ibcvlcib.pls 120.1 2005/06/24 14:28:27 appldev ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Start of comments
6 -- API name : Get_Citems_To_Be_Loaded
7 -- Type : Private
8 -- Pre-reqs : None
9 -- Function : This implementation will return all the associated content items.
10 -- In addition to these content items' live version, all their labelled
11 -- versions would also be returned.
12 -- OUT : x_content_item_ids JTF_NUMBER_TABLE
13 -- x_citem_version_ids JTF_NUMBER_TABLE
14 -- x_label_codes JTF_VARCHAR2_TABLE_100
15 --------------------------------------------------------------------------------
16 PROCEDURE Get_Citems_To_Be_Loaded (
17 x_content_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
18 x_citem_version_ids OUT NOCOPY JTF_NUMBER_TABLE,
19 x_label_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100
20 ) AS
21 CURSOR Get_Citems IS
22 SELECT content_item_id, citem_version_id, label_code
23 FROM ibc_citem_version_labels
24 WHERE content_item_id IN (select distinct a.CONTENT_ITEM_ID
25 from ibc_associations a, ibc_content_items c
26 where a.content_item_id = c.content_item_id
27 and c.content_item_status = 'APPROVED'
28 and c.WD_RESTRICTED_FLAG = 'F'
29 and c.LIVE_CITEM_VERSION_ID IS NOT NULL)
30 AND ROWID IN (SELECT MAX(ROWID)
31 FROM ibc_citem_version_labels
32 GROUP BY content_item_id, citem_version_id)
33 UNION
34 SELECT a.*
35 FROM (select distinct a.CONTENT_ITEM_ID, c.live_citem_version_id as citem_version_id,
36 NULL as label_code
37 from ibc_associations a, ibc_content_items c
38 where a.content_item_id = c.content_item_id
39 and c.content_item_status = 'APPROVED'
40 and c.WD_RESTRICTED_FLAG = 'F'
41 and c.LIVE_CITEM_VERSION_ID IS NOT NULL) a
42 WHERE NOT EXISTS (SELECT NULL
43 FROM ibc_citem_version_labels b
44 WHERE b.citem_version_id = a.citem_version_id);
45
46 BEGIN
47 OPEN Get_Citems;
48 FETCH Get_Citems BULK COLLECT INTO x_content_item_ids, x_citem_version_ids, x_label_codes;
49 CLOSE Get_Citems;
50
51 END Get_Citems_To_Be_Loaded;
52
53
54
55
56 END IBC_LOAD_CITEMS_PVT;
[Home] [Help]