PACKAGE BODY: APPS.POA_DNB_LTPRT

Source

1 PACKAGE BODY POA_DNB_LTPRT AS
2 /* $Header: poaltpb.pls 120.0 2005/06/01 13:49:00 appldev noship $ */
3 
4 
5 PROCEDURE poa_list_all_tprt(Errbuf  in out NOCOPY Varchar2,
6                             Retcode  in out NOCOPY Varchar2)
7 IS
8   CURSOR c_all_tprt IS
9      SELECT distinct TPLO_TPartner_Loc_PK TPartner_Loc_PK,
10             TPRT_Name Name,
11             TPLO_Address_Line1 Address_Line1,
12             TPLO_Address_Line2 Address_Line2,
13             TPLO_Address_Line3 Address_Line3,
14             TPLO_Address_Line4 Address_Line4,
15             TPLO_County County,
16             TPLO_City City,
17             TPLO_State State,
18             TPLO_Province Province,
19             TPLO_Country Country,
20             TPLO_Postal_Code Postal_Code
21       FROM EDW_TRD_PARTNER_M
22       where ((TPLO_Business_Type = 'VENDOR SITE') and
23              (TPLO_Level_Name = 'LOCATION'));
24 
25   v_buf    VARCHAR2(240) := NULL;
26 
27 BEGIN
28 
29   errbuf := NULL;
30   Retcode := 0;
31 
32   POA_LOG.setup('POALTPRT');
33   POA_LOG.debug_line('In List All Trading Partners');
34 
35   FOR ltprt in c_all_tprt LOOP
36     POA_LOG.output_line('"' ||
37                         ltprt.TPartner_Loc_PK || '","' ||
38                         ltprt.Name || '","' ||
39                         ltprt.Address_Line1 || '","' ||
40                         ltprt.Address_Line2 || '","' ||
41                         ltprt.Address_Line3 || '","' ||
42                         ltprt.Address_Line4 || '","' ||
43                         ltprt.County || '","' ||
44                         ltprt.City || '","' ||
45                         NVL(ltprt.State, ltprt.Province) || '","' ||
46                         ltprt.Country || '","' ||
47                         ltprt.Postal_Code || '"');
48 
49 
50   END LOOP;
51 
52   POA_LOG.put_line('POALTPRT.out generated');
53   POA_LOG.wrapup('SUCCESS');
54 
55 EXCEPTION WHEN OTHERS THEN
56      errbuf := sqlerrm;
57      retcode := sqlcode;
58 
59      POA_LOG.put_line('Error while listing Trading Partners:');
60      POA_LOG.put_line(sqlcode || ': ' || sqlerrm);
61 
62      v_buf := retcode || ':' || errbuf;
63      ROLLBACK;
64      POA_LOG.put_line(v_buf);
65      POA_LOG.wrapup('ERROR');
66 
67      RETURN;
68 
69 END poa_list_all_tprt;
70 
71 END POA_DNB_LTPRT;
72