PACKAGE BODY: APPS.MSD_COLLECT_ORGANIZATIONS

Source

1 PACKAGE BODY MSD_COLLECT_ORGANIZATIONS AS
2 /* $Header: msdcorgb.pls 115.2 2002/11/06 23:01:24 pinamati ship $ */
3 
4 
5 procedure collect_organizations(
6                         errbuf              OUT NOCOPY VARCHAR2,
7                         retcode             OUT NOCOPY VARCHAR2,
8                         p_instance_id       IN  NUMBER) IS
9 
10 x_dest_table    varchar2(50) ;
11 x_retcode 	number;
12 v_icode		varchar2(4);
13 v_lang		varchar2(4);
14 v_dblink	varchar2(128);
15 v_sql_stmt	varchar2(2000);
16 
17 Begin
18 
19 
20 	retcode :=0;
21 
22 	v_lang := 'US';
23 
24         msd_common_utilities.get_db_link(p_instance_id, v_dblink, x_retcode);
25         if (x_retcode = -1) then
26                 retcode :=-1;
27                 errbuf := 'Error while getting db_link';
28                 return;
29         end if;
30 
31 	select instance_code
32 	into v_icode
33 	from msc_apps_instances
34 	where instance_id = p_instance_id;
35 
36 	v_icode := v_icode || ':';
37 
38 	delete from msc_trading_partners
39 	where sr_instance_id = p_instance_id;
40 
41 v_sql_stmt:=
42 'insert into MSC_TRADING_PARTNERS'
43 ||'  ( PARTNER_ID,'
44 ||'    ORGANIZATION_CODE,'
45 ||'    ORGANIZATION_TYPE,'
46 ||'    SR_TP_ID,'
47 ||'    MASTER_ORGANIZATION,'
48 ||'    SOURCE_ORG_ID,'
49 ||'    PARTNER_TYPE,'
50 ||'    PARTNER_NAME,'
51 ||'    CALENDAR_CODE,'
52 ||'    CALENDAR_EXCEPTION_SET_ID,'
53 ||'    OPERATING_UNIT,'
54 ||'    SR_INSTANCE_ID,'
55 ||'    LAST_UPDATE_DATE,'
56 ||'    LAST_UPDATED_BY,'
57 ||'    CREATION_DATE,'
58 ||'    CREATED_BY)'
59 ||'  select'
60 ||'    msc_trading_partners_s.nextval,'
61 ||'    :v_icode||x.ORGANIZATION_CODE,'
62 ||'    1,'           -- set to discrete as the default value.
63 ||'    x.SR_TP_ID,'
64 ||'    x.MASTER_ORGANIZATION,'
65 ||'    x.SOURCE_ORG_ID,'
66 ||'    x.PARTNER_TYPE,'
67 ||'    :v_icode||x.PARTNER_NAME,'
68 ||'    :v_icode||x.CALENDAR_CODE,'
69 ||'    x.CALENDAR_EXCEPTION_SET_ID,'
70 ||'    x.OPERATING_UNIT,'
71 ||'    :p_instance_id,'
72 ||'    SYSDATE,'
73 ||'    1,'
74 ||'    SYSDATE,'
75 ||'    1'
76 ||'  from MRP_AP_ORGANIZATIONS_V'||v_dblink||' x'
77 ||'  where NVL( x.LANGUAGE, :v_lang)= :v_lang';
78 
79 EXECUTE IMMEDIATE v_sql_stmt USING v_icode,
80                                    v_icode,
81                                    v_icode,
82                                    p_instance_id,
83                                    v_lang,
84                                    v_lang;
85 
86 COMMIT;
87 
88 	 exception
89 
90 	  when others then
91 
92 		errbuf := substr(SQLERRM,1,150);
93 		retcode := -1 ;
94 
95 
96 End collect_organizations ;
97 
98 
99 END MSD_COLLECT_ORGANIZATIONS;