[Home] [Help]
PACKAGE BODY: APPS.PA_PWP_INVOICE_LINKS
Source
1 PACKAGE body PA_PWP_INVOICE_LINKS AS
2 /* $Header: PAAPINVB.pls 120.0.12010000.3 2008/12/18 12:59:19 jjgeorge noship $ */
3 PROCEDURE del_invoice_link
4 (PA_LINK_TAB IN PA_PWP_INVOICE_LINKS.LINK_TAB
5 ,x_return_status OUT NOCOPY VARCHAR2
6 ,x_msg_count OUT NOCOPY NUMBER
7 ,x_msg_data OUT NOCOPY VARCHAR2 )
8 IS
9
10
11 Cursor C1(P_Invoice_Id Number, P_Project_Id Number) Is
12 Select distinct draft_invoice_num,link_type From (
13 Select draft_invoice_num, 'M' link_type From PA_PWP_LINKED_INVOICES PWP
14 Where PWP.AP_INVOICE_ID = p_invoice_id
15 And PWP.PROJECT_ID = p_project_id
16 UNION ALL
17 Select pdii.draft_invoice_num, 'A' From PA_DRAFT_INVOICE_ITEMS PDII ,
18 PA_CUST_REV_DIST_LINES CRDL ,
19 PA_EXPENDITURE_ITEMS EI
20 Where PDII.project_id = crdl.project_id
21 And pdii.draft_invoice_num = crdl.draft_invoice_num
22 And pdii.line_num = crdl.draft_invoice_item_line_num
23 And crdl.expenditure_item_id = ei.expenditure_item_id
24 And ei.system_linkage_function = 'VI'
25 And ei.document_header_id =p_invoice_id
26 And ei.transaction_source like 'AP%'
27 And ei.project_id =p_project_id);
28
29 l_draft_inv_number Varchar2(2000):='';
30 l_draft_inv_link_type Varchar2(2000):='';
31
32 BEGIN
33 x_return_status := 'S';
34 x_msg_data := 'PA_INV_HOLD_RELEASE';
35
36 --Bug 7650431 Changed the FORALL to normal For Loop.
37 FOR i in PA_LINK_TAB.first..PA_LINK_TAB.last Loop
38 DELETE
39 FROM PA_PWP_LINKED_INVOICES
40 WHERE project_id = PA_LINK_TAB(i).PROJECT_ID
41 AND draft_invoice_num = PA_LINK_TAB(i).DRAFT_INVOICE_NUM
42 AND ap_invoice_id = PA_LINK_TAB(i).AP_INVOICE_ID;
43
44 End Loop;
45
46 FOR i in PA_LINK_TAB.first..PA_LINK_TAB.last LOOP
47 l_draft_inv_number := '';
48 FOR DRAFTINV_REC IN C1(PA_LINK_TAB(i).AP_INVOICE_ID,PA_LINK_TAB(i).PROJECT_ID) LOOP
49 IF l_draft_inv_number IS NULL THEN
50 l_draft_inv_number:=DRAFTINV_REC.draft_invoice_num;
51 l_draft_inv_link_type :=DRAFTINV_REC.link_type;
52 ELSE
53 l_draft_inv_number:=l_draft_inv_number||','||DRAFTINV_REC.draft_invoice_num;
54 l_draft_inv_link_type := l_draft_inv_link_type||','||DRAFTINV_REC.link_type;
55 END IF;
56 END LOOP;
57
58 UPDATE PA_PWP_AP_INV_HDR
59 SET LINKED_DRAFT_INVOICE_NUM = l_draft_inv_number,
60 LINKED_DRFAT_INV_TYPE = l_draft_inv_link_type
61 WHERE INVOICE_ID = PA_LINK_TAB(i).AP_INVOICE_ID
62 AND PROJECT_ID = PA_LINK_TAB(i).PROJECT_ID;
63 END LOOP;
64 COMMIT;
65 EXCEPTION
66 WHEN OTHERS THEN
67 x_return_status := 'U';
68 X_msg_data := SQLERRM;
69 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END del_invoice_link ;
71
72
73 PROCEDURE add_invoice_link
74 (PA_LINK_TAB IN PA_PWP_INVOICE_LINKS.LINK_TAB
75 ,x_return_status OUT NOCOPY VARCHAR2
76 ,x_msg_count OUT NOCOPY NUMBER
77 ,x_msg_data OUT NOCOPY VARCHAR2 )
78 IS
79 l_sysdate DATE;
80 l_created_by number;
81 l_orgid number;
82 BEGIN
83 x_return_status := 'S';
84 x_msg_data := 'PA_PWP_LINK_INV';
85
86 SELECT sysdate
87 INTO l_sysdate
88 FROM dual;
89 l_CREATED_BY :=FND_GLOBAL.user_id;
90
91 If PA_LINK_TAB.count > 0 then
92 SELECT org_id
93 INTO l_orgid
94 FROM pa_projects where project_id = PA_LINK_TAB(1).PROJECT_ID ;
95 End if;
96
97
98 --Bug 7650431 Changed the FORALL to normal For Loop.
99 FOR i in PA_LINK_TAB.first..PA_LINK_TAB.last Loop
100
101 INSERT
102 INTO PA_PWP_LINKED_INVOICES
103 ( ORG_ID,
104 PROJECT_ID ,
105 DRAFT_INVOICE_NUM ,
106 AP_INVOICE_ID ,
107 CREATED_BY ,
108 CREATION_DATE ,
109 LAST_UPDATED_BY ,
110 LAST_UPDATE_DATE
111 )
112 VALUES
113 ( l_orgid,
114 PA_LINK_TAB(i).PROJECT_ID ,
115 PA_LINK_TAB(i).DRAFT_INVOICE_NUM ,
116 PA_LINK_TAB(i).AP_INVOICE_ID ,
117 l_CREATED_BY ,
118 l_sysdate ,
119 l_CREATED_BY ,
120 l_sysdate
121 );
122 End Loop;
123
124 FOR i in PA_LINK_TAB.first..PA_LINK_TAB.last Loop
125 UPDATE PA_PWP_AP_INV_HDR
126 SET LINKED_DRAFT_INVOICE_NUM = DECODE(LINKED_DRAFT_INVOICE_NUM, NULL,
127 to_char(PA_LINK_TAB(i).DRAFT_INVOICE_NUM),
128 LINKED_DRAFT_INVOICE_NUM|| ',' ||
129 to_char(PA_LINK_TAB(i).DRAFT_INVOICE_NUM)),
130 LINKED_DRFAT_INV_TYPE = DECODE(LINKED_DRFAT_INV_TYPE,NULL,'M',
131 LINKED_DRFAT_INV_TYPE||','||'M')
132 WHERE INVOICE_ID = PA_LINK_TAB(i).AP_INVOICE_ID
133 AND PROJECT_ID = PA_LINK_TAB(i).PROJECT_ID;
134 End Loop;
135
136 COMMIT;
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 x_return_status := 'U';
141 X_msg_data := SQLERRM;
142 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 END add_invoice_link;
144
145 END PA_PWP_INVOICE_LINKS;