1 package body ar_ll_rcv_grouping_hook as
2 /*$Header: ARRWGHKB.pls 120.2 2007/12/31 11:49:17 spdixit ship $ */
3
4
5 procedure update_source_data_keys (x_customer_Trx_id in number) is
6 --This sample procedure inserts dummy group names into Source_data_key5
7 -- and the corresponding dummy group id to source_data_key4
8 begin
9 --
10 -- Uncomment the following update, to do your own grouping
11 -- Remember not to update Source_data_key4 with 0, as it is reserved
12 -- for lines without any groups
13 --
14 --update ra_customer_Trx_lines
15 --set source_data_key5 = decode(mod(line_number,4),1,'Printers',2,'Scanners',3,'Monitors')
16 -- ,source_data_key4 = mod(line_number,4)
17 --where interface_line_attribute9 <> 'Service';
18 --
19 -- Don't change any of the updates below.
20
21 update ra_customer_Trx_lines
22 set source_data_key5 = get_group_name (source_data_key1, source_data_key2)
23 ,source_data_key4 = get_group_id (source_data_key1, source_data_key2)
24 where customer_trx_id = x_customer_trx_id
25 AND interface_line_attribute9 = 'Service';
26
27
28 exception
29 when others then
30 raise;
31 end;
32
33
34 function get_group_id (sdk1 in varchar2, sdk2 in varchar2) return number is
35 --This sample procedure gets dummy group id from Source_data_key2
36 -- if the line is not a 'Service' line interfaced from OKS product
37 group_id number;
38 begin
39
40 select srv.id
41 into group_id
42 from oks_billprst_srvline_v srv
43 where srv.id(+) = sdk1
44 and srv.bcl_id(+) = sdk2
45 and rownum = 1;
46
47 return group_id;
48 exception
49 when others then
50 raise;
51 end;
52
53
54 function get_group_name (sdk1 in varchar2, sdk2 in varchar2) return varchar2 is
55 --This sample procedure gets dummy group names from Source_data_key1
56 -- if the line is not a 'Service' line interfaced from OKS product
57 group_name varchar2(150);
58 begin
59 select srv.name
60 into group_name
61 from oks_billprst_srvline_v srv
62 where srv.id(+) = sdk1
63 and srv.bcl_id(+) = sdk2
64 and rownum = 1;
65
66 return group_name;
67 exception
68 when others then
69 raise;
70 end;
71
72
73 END AR_LL_RCV_GROUPING_HOOK;