PACKAGE BODY: APPS.MSD_ASCP

Source

1 PACKAGE BODY MSD_ASCP AS
2 /* $Header: msdascpb.pls 115.7 2002/05/10 17:01:30 pkm ship      $ */
3 
4 function partner_id(p_level_id	     VARCHAR2,
5                     p_level_pk 	     VARCHAR2)   RETURN NUMBER IS
6 
7 v_ret NUMBER := null;
8 v_parent_pk NUMBER;
9 
10 BEGIN
11 
12   IF p_level_id = 15 THEN
13     select mtp.partner_id into v_ret
14     from msc_trading_partners mtp, msd_level_values mlv
15     where  mtp.sr_instance_id = mlv.instance
16       and  mlv.level_id       = p_level_id
17       and  mlv.level_pk       = p_level_pk
18       and  mtp.partner_type   = 2
19       and  mtp.sr_tp_id       = mlv.sr_level_pk;
20  ELSIF p_level_id = 11 THEN
21     select mtp.partner_id into v_ret
22     from msc_trading_partners mtp,
23          msd_level_values site,
24          msd_level_associations mla
25     where  site.level_id         = 11
26       and  site.level_pk         = p_level_pk
27       and  site.instance         = mla.instance
28       and  site.sr_level_pk      = mla.sr_level_pk
29       and  mtp.partner_type      = 2
30       and  to_char(mtp.sr_tp_id) = mla.sr_parent_level_pk
31       and  mtp.sr_instance_id    = site.instance;
32   END IF;
33 
34   return v_ret;
35 
36 EXCEPTION
37   WHEN NO_DATA_FOUND THEN
38     return null;
39 
40 END partner_id;
41 
42 
43 function partner_site_id(p_level_id  VARCHAR2,
44                          p_level_pk  VARCHAR2)   RETURN NUMBER IS
45 
46 v_ret NUMBER := null;
47 v_sr_parent_pk NUMBER;
48 
49 BEGIN
50 
51   IF p_level_id = 11 THEN
52    /* look up unique reference in tps */
53     select distinct mtps.partner_site_id into v_ret
54     from msc_trading_partner_sites mtps,
55          msd_level_values site,
56          msd_level_associations mla
57     where  site.level_id       = p_level_id
58       and  site.level_pk       = p_level_pk
59       and  mtps.sr_instance_id = site.instance
60       and  mtps.partner_type   = 2
61       and  mtps.sr_tp_site_id  = site.sr_level_pk
62       and  mtps.sr_tp_id       = mla.sr_parent_level_pk
63       /* and the customer is... */
64       and  mla.sr_level_pk     = site.sr_level_pk
65       and  mla.level_id        = 11
66       and  mla.parent_level_id = 15
67       and  mla.instance        = site.instance;
68   END IF;
69 
70   return v_ret;
71 
72 EXCEPTION
73   WHEN NO_DATA_FOUND THEN
74     return null;
75 
76 END partner_site_id;
77 
78 END MSD_ASCP;