[Home] [Help]
PACKAGE BODY: APPS.POS_AP_CHECKS_PKG
Source
1 PACKAGE BODY POS_AP_CHECKS_PKG AS
2 /* $Header: POSAPCKB.pls 120.3 2010/04/03 09:13:42 ssreekum ship $ */
3
4
5 ----------------------------------------------------------------------
6 -- Procedure to return PO information associated with a payment
7 -- Returns:
8 -- p_po_switch = S/M/N/E (Single/Multiple/No/Exception)
9 -- p_po_num = <NUM>/'Multiple'
10 -- p_header_id = <HEADER_ID>
11 -- p_release_id = <RELEASE_ID>
12 -- @ABTRIVED
13 ----------------------------------------------------------------------
14 PROCEDURE get_po_info(l_check_id IN NUMBER,
15 p_po_switch OUT NOCOPY VARCHAR2,
16 p_po_num OUT NOCOPY VARCHAR2,
17 p_header_id OUT NOCOPY VARCHAR2,
18 p_release_id OUT NOCOPY VARCHAR2)
19 IS
20 po_num2 VARCHAR2(40);
21 header_id2 VARCHAR2(20);
22 release_id2 VARCHAR2(20);
23
24 -- Declare cursor to retrieve the PO number
25 CURSOR po_cursor IS
26 SELECT DISTINCT (ph.clm_document_number||'-'||pr.release_num), ph.po_header_id, pr.po_release_id
27 FROM ap_invoice_distributions_all aid,
28 po_distributions_all pd,
29 po_headers_all ph,
30 po_releases_all pr
31 WHERE aid.invoice_id in (select invoice_id
32 from ap_invoice_payments_all
33 where check_id = l_check_id)
34 AND aid.po_distribution_id = pd.po_distribution_id
35 AND pr.po_release_id = pd.po_release_id
36 AND ph.po_header_id = pr.po_header_id
37 AND ph.type_lookup_code = 'BLANKET'
38 UNION ALL
39 SELECT DISTINCT ph.clm_document_number, ph.po_header_id, null
40 FROM ap_invoice_distributions_all aid,
41 po_distributions_all pd,
42 po_headers_all ph
43 WHERE aid.invoice_id in (select invoice_id
44 from ap_invoice_payments_all
45 where check_id = l_check_id)
46 AND aid.po_distribution_id = pd.po_distribution_id
47 AND pd.po_header_id = ph.po_header_id
48 AND ph.type_lookup_code = 'STANDARD';
49
50 BEGIN
51
52
53 OPEN po_cursor;
54
55 FETCH po_cursor INTO p_po_num, p_header_id, p_release_id;
56 if (po_cursor%NOTFOUND) then
57 -- no po's
58 p_po_switch := 'N';
59 else
60 --atleast on po
61 FETCH po_cursor INTO po_num2, header_id2, release_id2;
62 if (po_cursor%NOTFOUND) then
63 --exactly one PO
64 p_po_switch := 'S';
65 else
66 -- multiple PO's
67 p_po_switch := 'M';
68 p_po_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
69 end if;
70 end if;
71
72 CLOSE po_cursor;
73
74 EXCEPTION WHEN OTHERS THEN
75
76 p_po_switch := 'E';
77
78 END get_po_info;
79
80
81 ----------------------------------------------------------------------
82 -- Procedure to return Invoice information associated with a payment
83 -- Returns:
84 -- p_invoice_switch = Po_Single/Po_Multiple/Po_No
85 -- p_invoice_num = <INVOICE_NUM>/'Multiple'
86 -- p_invoice_id = <INVOICE_ID>
87 -- @ABTRIVED
88 ----------------------------------------------------------------------
89 PROCEDURE get_invoice_info(l_check_id IN NUMBER,
90 p_invoice_switch OUT NOCOPY VARCHAR2,
91 p_invoice_num OUT NOCOPY VARCHAR2,
92 p_invoice_id OUT NOCOPY VARCHAR2)
93
94 IS
95 invoice_num2 VARCHAR2(225);
96 invoice_id2 VARCHAR2(225);
97
98 -- Declare cursor to return the Invoice number, Invoice Id
99 CURSOR inv_cursor IS
100 SELECT distinct ai.invoice_num, ai.invoice_id
101 FROM ap_invoices_all ai,
102 ap_invoice_payments_all aip
103 WHERE aip.check_id = l_check_id
104 AND aip.invoice_id = ai.invoice_id;
105
106 BEGIN
107
108 OPEN inv_cursor;
109
110 FETCH inv_cursor INTO p_invoice_num, p_invoice_id;
111 if (inv_cursor%NOTFOUND) then
112 -- no invoices
113 p_invoice_switch := 'N';
114 else
115 --atleast one invoice
116 FETCH inv_cursor INTO invoice_num2, invoice_id2;
117 if (inv_cursor%NOTFOUND) then
118 --exactly one invoice
119 p_invoice_switch := 'S';
120 else
121 -- multiple
122 p_invoice_switch := 'M';
123 p_invoice_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
124 end if;
125 end if;
126
127 CLOSE inv_cursor;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131
132 if inv_cursor%isopen then
133 close inv_cursor;
134 end if;
135 p_invoice_switch := 'E';
136
137 END get_invoice_info;
138
139 END POS_AP_CHECKS_PKG;