1 PACKAGE BODY INV_FA_INTERFACE_PVT AS
2 /* $Header: INVFAAPB.pls 115.1 2003/08/14 23:11:25 vputcha noship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_FA_INTERFACE_PVT';
6 -- Package global
7
8 FUNCTION Get_IC_CCID(p_inv_dist_id IN NUMBER,
9 p_inv_cc_id IN NUMBER,
10 p_line_type IN VARCHAR2 )
11 RETURN NUMBER IS
12
13 l_trx_src_line_id NUMBER := NULL;
14 l_dist_cc_id NUMBER := NULL;
15 l_temp_cc_id NUMBER := NULL;
16 BEGIN
17
18 l_dist_cc_id := p_inv_cc_id;
19
20 BEGIN
21 SELECT MMT.trx_source_line_id
22 INTO l_trx_src_line_id
23 FROM ap_invoice_distributions_all APINVD
24 , ra_customer_trx_lines_all RCTL
25 , mtl_material_transactions MMT
26 WHERE APINVD.invoice_distribution_id = p_inv_dist_id
27 AND RCTL.customer_trx_line_id = to_number(APINVD.reference_1)
28 AND RCTL.interface_line_context = 'INTERCOMPANY'
29 AND MMT.transaction_id = to_number(RCTL.interface_line_attribute7)
30 AND MMT.transaction_source_type_id = 8
31 AND MMT.transaction_action_id = 21;
32 EXCEPTION WHEN OTHERS THEN
33 Return(l_dist_cc_id);
34 END;
35
36 if (l_trx_src_line_id IS NOT NULL ) then
37
38 BEGIN
39 SELECT PORD.code_combination_id
40 INTO l_temp_cc_id
41 FROM oe_order_lines_all OEL
42 , po_req_distributions_all PORD
43 WHERE OEL.line_id = l_trx_src_line_id
44 AND OEL.source_document_type_id = 10
45 AND PORD.requisition_line_id = OEL.source_document_line_id
46 GROUP BY PORD.code_combination_id;
47
48 l_dist_cc_id := l_temp_cc_id;
49
50 EXCEPTION WHEN OTHERS THEN
51 Return(l_dist_cc_id);
52 END;
53
54 end if;
55
56 Return(l_dist_cc_id);
57
58 EXCEPTION
59
60 WHEN OTHERS THEN
61 l_dist_cc_id := p_inv_cc_id;
62 Return(l_dist_cc_id);
63 END;
64
65
66
67 FUNCTION Get_REF_CCID(p_inv_ref_id IN VARCHAR2,
68 p_inv_cc_id IN NUMBER,
69 p_line_type IN VARCHAR2 )
70 RETURN NUMBER IS
71
72 l_trx_src_line_id NUMBER := NULL;
73 l_dist_cc_id NUMBER := NULL;
74 l_temp_cc_id NUMBER := NULL;
75 BEGIN
76
77 l_dist_cc_id := p_inv_cc_id;
78
79 if (p_inv_ref_id is NULL) then
80 Return(l_dist_cc_id);
81 end if;
82
83 BEGIN
84 SELECT MMT.trx_source_line_id
85 INTO l_trx_src_line_id
86 FROM ra_customer_trx_lines_all RCTL
87 , mtl_material_transactions MMT
88 WHERE RCTL.customer_trx_line_id = to_number(p_inv_ref_id)
89 AND RCTL.interface_line_context = 'INTERCOMPANY'
90 AND MMT.transaction_id = to_number(RCTL.interface_line_attribute7)
91 AND MMT.transaction_source_type_id = 8
92 AND MMT.transaction_action_id = 21;
93 EXCEPTION WHEN OTHERS THEN
94 Return(l_dist_cc_id);
95 END;
96
97 if (l_trx_src_line_id IS NOT NULL ) then
98
99 BEGIN
100 SELECT PORD.code_combination_id
101 INTO l_temp_cc_id
102 FROM oe_order_lines_all OEL
103 , po_req_distributions_all PORD
104 WHERE OEL.line_id = l_trx_src_line_id
105 AND OEL.source_document_type_id = 10
106 AND PORD.requisition_line_id = OEL.source_document_line_id
107 GROUP BY PORD.code_combination_id;
108
109 l_dist_cc_id := l_temp_cc_id;
110
111 EXCEPTION WHEN OTHERS THEN
112 Return(l_dist_cc_id);
113 END;
114
115 end if;
116
117 Return(l_dist_cc_id);
118
119 EXCEPTION
120
121 WHEN OTHERS THEN
122 l_dist_cc_id := p_inv_cc_id;
123 Return(l_dist_cc_id);
124 END;
125
126
127 END INV_FA_INTERFACE_PVT;
[Home] [Help]