1 package body ar_extract_document as
2 /*$Header: AREXTDCB.pls 120.9 2006/04/05 13:44:02 nsomawar noship $ */
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 where (l_trx_class is null or d.trx_class = l_trx_class) and
105 (l_trx_type_id is null or d.trx_type_id = l_trx_type_id) and
106 (l_trx_number_low is null or l_trx_number_low <= d.trx_number) and
107 (l_trx_number_high is null or d.trx_number <= l_trx_number_high) and
108 (l_cust_class is null or d.cust_class = l_cust_class) and
109 (l_cust_account_id is null or d.cust_account_id = l_cust_account_id) and
110 (l_trx_date_low is null or l_trx_date_low <= d.trx_date) and
111 (l_trx_date_high is null or d.trx_date <= l_trx_date_high));
112 /* 4188835 - If we are updating the printing columns for a trx,
113 we need to freeze it in the eyes of eTax */
114 FOR trx in c_pending_trx LOOP
115 arp_etax_util.global_document_update(trx.customer_trx_id,null,'PRINT');
116 END LOOP;
117
118 update ra_customer_trx
119 set PRINTING_PENDING = 'N',
120 PRINTING_COUNT = 1,
121 PRINTING_LAST_PRINTED = sysdate,
122 PRINTING_ORIGINAL_DATE = sysdate,
123 LAST_PRINTED_SEQUENCE_NUM = 1
124 where customer_trx_id in (select source_id from ar_document_transfers where status = 'WAITING') and
125 PRINTING_PENDING <> 'N' and
126 PRINTING_COUNT is null and
127 PRINTING_LAST_PRINTED is null and
128 PRINTING_ORIGINAL_DATE is null and
129 LAST_PRINTED_SEQUENCE_NUM is null;
130
131 retcode := 0; -- SUCCESS
132 if ar_doc_transfer_standard.isDebugOn then
133 ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents(-)');
134 end if;
135
136 exception
137 when no_data_found then
138 if ar_doc_transfer_standard.isDebugOn then
139 ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents(): NO Invoice Fetched');
140 end if;
141
142 when others then
143 l_sqlerrm := sqlerrm(sqlcode) || fnd_global.newline ||
144 'Location: AR_EXTRACT_DOCUMENT.extract_documents()'||fnd_global.newline||
145 'Time: '||to_char(sysdate, 'DD-MON-RRRR HH:MI:SS');
146 retcode := 2;
147 fnd_message.set_name('AR','AR_DOC_EXT_SBJ');
148 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
149 l_subject := fnd_message.get;
150 if ar_doc_transfer_standard.isDebugOn then
151 ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT.extract_documents()EXCEPTION:'||l_sqlerrm);
152 end if;
153 ar_notification_standard.notifyToSysadmin(l_subject,
154 l_sqlerrm);
155 end;
156 begin
157 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT(+)'); end if;
158 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_EXTRACT_DOCUMENT(-)'); end if;
159 end;
[Home] [Help]