1 PACKAGE AP_SUPPLIER_INFO_PKG AS
2 /* $Header: apsupinfs.pls 120.1.12010000.3 2008/11/18 10:46:51 anarun noship $ */
3
4 -- Type Declarations
5
6 TYPE t_contacts_tab IS TABLE OF po_vendor_contacts%ROWTYPE
7 INDEX BY BINARY_INTEGER;
8
9 -- Declare a record for supplier site. Each record will have one
10 -- site record and a table of contacts associated with that site
11 TYPE t_site_con_rec IS RECORD ( site_rec ap_supplier_sites_all%ROWTYPE,
12 contact_tab t_contacts_tab );
13
14 -- Declare a table for supplier site with each record being of the
15 -- type of site record declared above
16 TYPE t_site_con_tab IS TABLE OF t_site_con_rec
17 INDEX BY BINARY_INTEGER;
18
19 -- Declare a record for supplier information. It will contain a record for
20 -- supplier and a table of supplier site (containing site rec and contact
21 -- table) as described above
22 TYPE t_supplier_info_rec IS RECORD( supp_rec ap_suppliers%ROWTYPE,
23 site_con_tab t_site_con_tab );
24
25 -- Declare a table for supplier information with each record being of the
26 -- type of supplier information record declared above
27 TYPE t_supplier_info_tab IS TABLE OF t_supplier_info_rec
28 INDEX BY BINARY_INTEGER;
29
30 -- Public Procedure Specifications
31
32 ------------------------------------------------------------------------------
33 ----------------------------- Supplier_Details -------------------------------
34 ------------------------------------------------------------------------------
35 /* Fetches the details of Supplier, Site and Contact
36 * This is an overloaded procedure
37 * Parameters : i_vendor_id - vendor_id of supplier
38 * i_vendor_site_id - vendor_site_id of supplier site
39 * i_vendor_contact_id - vendor_contact_id
40 * o_supplier_info - Supplier details are populated into
41 * this parameter
42 * o_success - If a valid combination of vendor_id,
43 * vendor_site_id and vendor_contact_id
44 * was not passed then o_success is set
45 * to FALSE
46 *
47 * Logic :
48 * 1) Validation check and query string formulation
49 * First check if the vendor_id exists in ap_suppliers.
50 * If vendor_site_id is available then
51 * a) check if it is valid
52 * b) append it to site query string
53 * c) If vendor_contact_id is available then
54 * i) check if this contact is valid for this combination of
55 * ( vendor_id, vendor_site_id )
56 * ii) append it to contact query string. Here we do not need to
57 * consider it for site query string because while opening
58 * contact cursor, if vendor_site_id is available then we will
59 * use it as a condition
60 * End If
61 * Else
62 * a) If vendor_contact_id is available then
63 * i) check if this contact is valid for this combination of
64 * ( vendor_id, vendor_site_id )
65 * ii) Get the list of vendor_site_id for this contact and add
66 * this list as a IN condition of site query string
67 * End If
68 * End If
69 * 2) Populate the suplier details
70 * 3) Loop through sites and get site details
71 * 4) Loop through contacts for each site to get contact details
72 *
73 */
74
75 PROCEDURE Supplier_Details(
76 i_vendor_id IN NUMBER ,
77 i_vendor_site_id IN NUMBER DEFAULT NULL,
78 i_vendor_contact_id IN NUMBER DEFAULT NULL,
79 o_supplier_info OUT NOCOPY t_supplier_info_rec,
80 o_success OUT NOCOPY BOOLEAN
81 );
82
83 ------------------------------------------------------------------------------
84 ----------------------------- Supplier_Details -------------------------------
85 ------------------------------------------------------------------------------
86 /* Fetches the details of Supplier, Site and Contact for a particular range
87 * of vendor_id.
88 * This is an overloaded procedure. The parameter o_supplier_info_tab can be
89 * used to distinguish between the calls. Here, both i_from_vendor_id and
90 * i_to_vendor_id are mandatory parameters.
91 * This procedure should be called with a reasonable range of vendor_id based
92 * on the resources available at customer's instance to avoid performance
93 * issues. If there is some performance issue then the only way to fix it will
94 * be to provide a lesser range of vendor_id.
95 *
96 * Parameters : i_from_vendor_id - Start range of vendor_id
97 * i_to_vendor_id - End range of vendor_id
98 * o_supplier_info_tab - Supplier details are populated into
99 * this parameter.
100 * o_success - If no vendor exists in the range of
101 * vendor_id for which this procedure is
102 * called then o_success is set to FALSE
103 *
104 */
105
106 PROCEDURE Supplier_Details(
107 i_from_vendor_id IN NUMBER ,
108 i_to_vendor_id IN NUMBER ,
109 o_supplier_info_tab OUT NOCOPY t_supplier_info_tab,
110 o_success OUT NOCOPY BOOLEAN
111 );
112
113 END AP_SUPPLIER_INFO_PKG;
114
[Home] [Help]