1 package body ar_extract_document as
2 /*$Header: AREXTDCB.pls 120.11 2011/10/24 16:49:55 rravikir ship $ */
3 /*Removed gscc warnings NOCOPY hint Bug 4462243*/
4 procedure extract_documents(errbuf out NOCOPY varchar2,
5 retcode out NOCOPY varchar2,
6 argument1 in varchar2,
7 argument2 in varchar2,
8 argument3 in varchar2,
9 argument4 in varchar2,
10 argument5 in varchar2,
11 argument6 in varchar2,
12 argument7 in varchar2,
13 argument8 in varchar2) is
14
15 l_trx_class VARCHAR2(20);
16 l_trx_type_id NUMBER;
17 l_trx_number_low VARCHAR2(20);
18 l_trx_number_high VARCHAR2(20);
19 l_cust_class VARCHAR2(30);
20 l_cust_account_id NUMBER;
21 l_trx_date_low DATE;
22 l_trx_date_high DATE;
23
24 l_sqlerrm VARCHAR2(2000);
25 l_subject VARCHAR2(200);
26
27 CURSOR c_pending_trx IS
28 select trx.customer_trx_id
29 from ar_document_transfers xfr,
30 ra_customer_trx trx
31 where xfr.status = 'WAITING' and
32 xfr.source_id = trx.customer_trx_id and
33 trx.PRINTING_PENDING <> 'N' and
34 trx.PRINTING_COUNT is null and
35 trx.PRINTING_LAST_PRINTED is null and
36 trx.PRINTING_ORIGINAL_DATE is null and
37 trx.LAST_PRINTED_SEQUENCE_NUM is null;
38
39 begin
40 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents(+)'); end if;
41 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents(+)'); end if;
42 l_trx_class := argument1;
43 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_trx_class:'||l_trx_class); end if;
44 l_trx_type_id := argument2;
45 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_trx_type_id:'||to_char(l_trx_type_id)); end if;
46 l_trx_number_low := argument3;
47 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_trx_number_low:'||l_trx_number_low); end if;
48 l_trx_number_high := argument4;
49 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_trx_number_high:'||l_trx_number_high); end if;
50 l_cust_class := argument5;
51 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_cust_class:'||l_cust_class); end if;
52 l_cust_account_id := argument6;
53 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_cust_account_id:'||to_char(l_cust_account_id)); end if;
54 l_trx_date_low := to_date(argument7,'yyyy/mm/dd hh24:mi:ss'); /* Bug 5110228 - added Mask for fnd_standard_date */
55 /* l_trx_date_low := argument7; */
56 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_trx_date_low:'||to_char(l_trx_date_low)); end if;
57 l_trx_date_high := to_date(argument8,'yyyy/mm/dd hh24:mi:ss'); /* Bug 5110228 - added Mask for fnd_standard_date */
58 /* l_trx_date_high := argument8; */
59 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_trx_date_high:'||to_char(l_trx_date_high)); end if;
60
61 ar_doc_transfer_standard.updateStatus;
62
63 insert into ar_document_transfers
64 ( DOCUMENT_TRANSFER_ID,
65 SOURCE_ID,
66 SOURCE_TABLE,
67 TP_SOURCE_TABLE,
68 TP_SOURCE_ID,
69 ECX_TRX_TYPE,
70 ECX_TRX_SUBTYPE,
71 ECX_PARTY_TYPE,
72 EVENT_NAME,
73 STATUS,
74 REQUEST_ID,
75 APPLICATION_ID,
76 RESPONSIBILITY_ID,
77 CREATION_DATE,
78 CREATED_BY,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 LAST_SUBMISSION_DATE,
82 DOCUMENT_MSGID
83 )
84 (select AR_DOCUMENT_TRANSFERS_S.nextval,
85 SOURCE_ID,
86 SOURCE_TABLE,
87 TP_SOURCE_TABLE,
88 TP_SOURCE_ID,
89 ECX_TRX_TYPE,
90 ECX_TRX_SUBTYPE,
91 ECX_PARTY_TYPE,
92 EVENT_NAME,
93 'WAITING',
94 FND_GLOBAL.CONC_REQUEST_ID,
95 FND_GLOBAL.RESP_APPL_ID,
96 FND_GLOBAL.RESP_ID,
97 SYSDATE,
98 FND_GLOBAL.USER_ID,
99 SYSDATE,
100 FND_GLOBAL.USER_ID,
101 NULL,
102 NULL
103 from ar_document_transfers_v d,
104 ra_customer_trx trx
105 where (l_trx_class is null or d.trx_class = l_trx_class) and
106 (l_trx_type_id is null or d.trx_type_id = l_trx_type_id) and
107 (l_trx_number_low is null or l_trx_number_low <= d.trx_number) and
108 (l_trx_number_high is null or d.trx_number <= l_trx_number_high) and
109 (l_cust_class is null or d.cust_class = l_cust_class) and
110 (l_cust_account_id is null or d.cust_account_id = l_cust_account_id) and
111 (l_trx_date_low is null or l_trx_date_low <= d.trx_date) and
112 (l_trx_date_high is null or d.trx_date <= l_trx_date_high) and
113 d.source_id = trx.customer_trx_id and
114 trx.printing_pending = 'Y'); -- For bug 12797264
115 /* 4188835 - If we are updating the printing columns for a trx,
116 we need to freeze it in the eyes of eTax */
117 FOR trx in c_pending_trx LOOP
118 arp_etax_util.global_document_update(trx.customer_trx_id,null,'PRINT');
119 END LOOP;
120
121 update ra_customer_trx
122 set PRINTING_PENDING = 'N',
123 PRINTING_COUNT = 1,
124 PRINTING_LAST_PRINTED = sysdate,
125 PRINTING_ORIGINAL_DATE = sysdate,
126 LAST_PRINTED_SEQUENCE_NUM = 1
127 where customer_trx_id in (select source_id from ar_document_transfers where status = 'WAITING') and
128 PRINTING_PENDING <> 'N' and
129 PRINTING_COUNT is null and
130 PRINTING_LAST_PRINTED is null and
131 PRINTING_ORIGINAL_DATE is null and
132 LAST_PRINTED_SEQUENCE_NUM is null;
133
134 retcode := 0; -- SUCCESS
135 if ar_doc_transfer_standard.isDebugOn then
136 ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents(-)');
137 end if;
138
139 exception
140 when no_data_found then
141 if ar_doc_transfer_standard.isDebugOn then
142 ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents(): NO Invoice Fetched');
143 end if;
144
145 when others then
146 l_sqlerrm := sqlerrm(sqlcode) || fnd_global.newline ||
147 'Location: AR_EXTRACT_DOCUMENT.extract_documents()'||fnd_global.newline||
148 'Time: '||to_char(sysdate, 'DD-MON-RRRR HH:MI:SS');
149 retcode := 2;
150 fnd_message.set_name('AR','AR_DOC_EXT_SBJ');
151 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
152 l_subject := fnd_message.get;
153 if ar_doc_transfer_standard.isDebugOn then
154 ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents()EXCEPTION:'||l_sqlerrm);
155 end if;
156 ar_notification_standard.notifyToSysadmin(l_subject,
157 l_sqlerrm);
158 end;
159 begin
160 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT(+)'); end if;
161 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT(-)'); end if;
162 end;