1 PACKAGE BODY PA_CLIENT_EXTN_PWP AS
2 -- $Header: PAPWPEXTB.pls 120.0.12010000.4 2009/01/12 04:49:35 jjgeorge noship $
3
4 PROCEDURE RELEASE_INV (
5 P_REQUEST_ID IN NUMBER
6 , P_PROJECT_TYPE IN VARCHAR2
7 , P_FROM_PROJ_NUM IN VARCHAR2
8 , P_TO_PROJ_NUM IN VARCHAR2
9 , P_CUSTOMER_NAME IN VARCHAR2
10 , P_CUSTOMER_NUMBER IN NUMBER
11 , P_REC_DATE_FROM IN VARCHAR2
12 , P_REC_DATE_TO IN VARCHAR2
13 ,x_return_status OUT NOCOPY VARCHAR2
14 ,x_error_message_code OUT NOCOPY VARCHAR2)
15 IS
16
17 l_user_id number(15);
18 l_date date ;
19 l_request_id number(15);
20 l_org_id number(15);
21
22 /*Cursor to select the records inserted by Oracle seeded code during concurrent program processing for a run i.e request_id*/
23
24 CURSOR C_REPORTED_REC IS
25 SELECT REQUEST_ID,PROJECT_ID,DRAFT_INVOICE_NUM,RA_INVOICE_NUM,AP_INVOICE_ID,LINK_TYPE FROM
26 PA_PWP_RELEASE_REPORT_ALL WHERE REQUEST_ID = P_REQUEST_ID ;
27
28
29
30 /*Sample cursor :As an example :To pick up all invoices rejected by Oracle code where invoice amount < 5000*/
31
32 CURSOR C_OVERRIDE IS
33 SELECT AP_INVOICE_ID
34 FROM
35 PA_PWP_RELEASE_REPORT PWP
36 ,AP_INVOICES APINV
37 WHERE
38 PWP.AP_INVOICE_ID = APINV.INVOICE_ID
39 AND PWP.REQUEST_ID = P_REQUEST_ID
40 AND PWP.RELEASE_FLAG = 'N'
41 AND APINV.INVOICE_AMOUNT < 5000 ;
42
43
44 /* To pick up all unlinked draft invoices */
45
46 CURSOR C_UNLINKED IS
47 SELECT REQUEST_ID,PROJECT_ID,DRAFT_INVOICE_NUM FROM
48 PA_PWP_RELEASE_REPORT_ALL
49 WHERE REQUEST_ID = P_REQUEST_ID
50 AND link_type = 'UNLINKED'
51 AND release_flag = 'X';
52
53
54
55 BEGIN
56 /*
57 For Link type 'AUTOMATIC' or 'MANUAL',
58 The client extension can update the CUSTOM_RELEASE_FLAG
59 to prevent or allow release of payment holds for a supplier invoice.
60 This will override the RELEASE_FLAG set by Oracle code.
61
62 FOR rec IN c_reported_rec LOOP
63
64 Update PA_PWP_RELEASE_REPORT_ALL
65 set CUSTOM_RELEASE_FLAG = 'Y'
66 where request_id = p_request_id
67 AND ........
68
69 End Loop;
70 */
71
72
73
74
75 /*
76 Oracle code will not release the supplier invoices unless they are fully paid.
77 If you want to release the hold on all supplier invoices that are below a threshold amount, you can add customer code here to override the results of the standard logic used.
78 The CUSTOM_RELEASE_FLAG column value updated by this package will always take precedence over the RELEASE_FLAG value updated by the standard Oracle code.
79
80 FOR rec IN C_OVERRIDE LOOP
81
82 Update PA_PWP_RELEASE_REPORT_ALL
83 set CUSTOM_RELEASE_FLAG = 'Y'
84 where request_id = p_request_id
85 AND AP_INVOICE_ID = rec.AP_INVOICE_ID
86 AND ....
87
88 End Loop;
89 */
90
91
92
93
94 /*
95 For draft invoices that are created based on an invoice distribution rule
96 as COST, there will be a template record in PA_PWP_RELEASE_REPORT with
97 LINK_TYPE as UNLINKED and RELEASE_FLAG as 'X'.
98 This record can be used as a reference to view all unlinked invoices
99 that got picked up by the concurrent request.
100 The PAID_STATUS column indicates wehther the invoice is paid or not.
101 If a user wants to associate a supplier invoice with any of these draft invoices and release the supplier invoice, they can do so by inserting a record in
102 the PA_PWP_RELEASE_REPORT_ALL table with CUSTOM_RELEASE_FLAG set to 'Y'.
103 Users should not modify the package to update the template record that has
104 RELEASE_FLAG value as 'X'.
105
106
107 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID();
108
109 SELECT org_id
110 INTO G_ORG_ID
111 FROM pa_implementations;
112
113 select fnd_global.user_id into l_user_id from dual;
114 select sysdate into l_date from dual;
115
116
117
118 FOR rec IN c_reported_rec LOOP
119
120 INSERT INTO PA_PWP_RELEASE_REPORT
121 (
122 ORG_ID,
123 REQUEST_ID,
124 PROJECT_ID,
125 DRAFT_INVOICE_NUM,
126 LINK_TYPE,
127 CUSTOM_RELEASE_FLAG,
128 AP_INVOICE_ID ,
129 CREATED_BY,
130 CREATION_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_DATE
133 )values
134 ( l_org_id
135 l_request_id
136 rec.project_id,
137 rec.DRAFT_INVOICE_NUM,
138 rec.link_type,
139 'Y',
140 l_user_id,
141 l_date,
142 l_user_id,
143 l_date
144 );
145
146 End Loop;
147
148
149 */
150
151 null;
152
153 end RELEASE_INV;
154
155 END PA_CLIENT_EXTN_PWP;
156
[Home] [Help]