[Home] [Help]
PACKAGE BODY: APPS.PO_REQ_TEMPLATE_SV3
Source
1 PACKAGE BODY po_req_template_sv3 AS
2 /* $Header: POXRQT3B.pls 115.5 2003/10/21 20:51:43 nipagarw ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: get_po_line_info
7
8 ===========================================================================*/
9
10 PROCEDURE get_po_line_info (
11 x_rowid IN VARCHAR2,
12 x_inv_org_id IN NUMBER,
13 x_item_id IN OUT NOCOPY NUMBER,
14 x_item_revision IN OUT NOCOPY VARCHAR2,
15 x_item_description IN OUT NOCOPY VARCHAR2,
16 x_category_id IN OUT NOCOPY NUMBER,
17 x_unit_meas_lookup_code IN OUT NOCOPY VARCHAR2,
18 x_unit_price IN OUT NOCOPY NUMBER,
19 x_vendor_id IN OUT NOCOPY NUMBER,
20 x_vendor_site_id IN OUT NOCOPY NUMBER,
21 x_vendor_contact_id IN OUT NOCOPY NUMBER,
22 x_vendor_product_code IN OUT NOCOPY VARCHAR2,
23 x_suggested_buyer_id IN OUT NOCOPY NUMBER,
24 x_source_type_code IN OUT NOCOPY VARCHAR2,
25 x_po_header_id IN OUT NOCOPY NUMBER,
26 x_po_line_id IN OUT NOCOPY NUMBER,
27 x_line_type_id IN OUT NOCOPY NUMBER,
28 x_org_id IN OUT NOCOPY NUMBER,
29 x_line_type IN OUT NOCOPY VARCHAR2,
30 x_order_type_lookup_code IN OUT NOCOPY VARCHAR2,
31 x_source_type IN OUT NOCOPY VARCHAR2,
32 x_suggested_buyer IN OUT NOCOPY VARCHAR2,
33 x_vendor_name IN OUT NOCOPY VARCHAR2,
34 x_vendor_contact IN OUT NOCOPY VARCHAR2,
35 x_vendor_site IN OUT NOCOPY VARCHAR2,
36 x_amount IN OUT NOCOPY NUMBER, -- <SERVICES FPJ>
37 x_negotiated_by_preparer_flag IN OUT NOCOPY VARCHAR2 --<DBI FPJ>
38 )
39 IS
40 BEGIN
41
42 IF (x_rowid IS NOT NULL AND x_inv_org_id IS NOT NULL) THEN
43
44 -- <SERVICES FPJ>
45 -- Added the column amount to retrieve its value for
46 -- Fixed Price Services lines
47 SELECT pol.item_id,
48 pol.item_revision,
49 pol.item_description,
50 pol.category_id,
51 pol.unit_meas_lookup_code,
52 pol.unit_price,
53 poh.vendor_id,
54 poh.vendor_site_id,
55 poh.vendor_contact_id,
56 pol.vendor_product_num,
57 poh.agent_id,
58 'VENDOR',
59 decode(poh.type_lookup_code,
60 'BLANKET', pol.po_header_id, null),
61 decode(poh.type_lookup_code,
62 'BLANKET', pol.po_line_id, null),
63 pol.line_type_id,
64 pol.org_id,
65 plt.line_type,
66 plt.order_type_lookup_code,
67 plc.displayed_field,
68 po_inq_sv.get_person_name(poh.agent_id),
69 v.vendor_name,
70 decode (vc.last_name, NULL, NULL, vc.last_name||', '||vc.first_name),
71 vs.vendor_site_code,
72 pol.amount,
73 pol.negotiated_by_preparer_flag --<DBI FPJ>
74 INTO x_item_id,
75 x_item_revision ,
76 x_item_description,
77 x_category_id,
78 x_unit_meas_lookup_code ,
79 x_unit_price,
80 x_vendor_id,
81 x_vendor_site_id,
82 x_vendor_contact_id,
83 x_vendor_product_code,
84 x_suggested_buyer_id,
85 x_source_type_code,
86 x_po_header_id,
87 x_po_line_id,
88 x_line_type_id,
89 x_org_id, -- debug: what is this for?
90 x_line_type,
91 x_order_type_lookup_code,
92 x_source_type,
93 x_suggested_buyer,
94 x_vendor_name,
95 x_vendor_contact,
96 x_vendor_site,
97 x_amount,
98 x_negotiated_by_preparer_flag --<DBI FPJ>
99 FROM po_lookup_codes plc,
100 po_line_types plt,
101 po_vendor_contacts vc,
102 po_vendor_sites vs,
103 po_vendors v,
104 mtl_system_items msi,
105 po_headers poh,
106 po_lines pol
107 WHERE pol.rowid = x_rowid
108 AND poh.po_header_id = pol.po_header_id
109 AND nvl(pol.cancel_flag,'N') = 'N'
110 AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
111 AND pol.item_id = msi.inventory_item_id (+)
112 AND nvl(msi.organization_id, x_inv_org_id)
113 = x_inv_org_id
114 AND nvl(msi.outside_operation_flag,'N') = 'N'
115 AND plt.line_type_id = pol.line_type_id
116 AND plc.lookup_type (+) = 'REQUISITION SOURCE TYPE'
117 AND plc.lookup_code (+) = 'VENDOR'
118 AND v.vendor_id (+) = poh.vendor_id
119 AND vc.vendor_contact_id (+) = poh.vendor_contact_id
120 AND vs.vendor_site_id (+) = poh.vendor_site_id;
121
122 END IF;
123
124 EXCEPTION
125 WHEN OTHERS THEN RAISE;
126 END;
127
128 END PO_REQ_TEMPLATE_SV3;