[Home] [Help]
PACKAGE BODY: APPS.AR_BR_FORMAT_WRAPPER_PKG
Source
1 PACKAGE BODY AR_BR_FORMAT_WRAPPER_PKG AS
2 /* $Header: ARBRFMTB.pls 120.2 2006/05/15 06:25:03 ggadhams ship $*/
3
4
5 PROCEDURE SUBMIT_FORMATS(
6 errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 FORMAT IN VARCHAR2,
9 BR IN NUMBER,
10 AFROM IN NUMBER default NULL,
11 ATO IN NUMBER default NULL,
12 SOB IN NUMBER
13 )
14 IS
15
16 v_format VARCHAR2(30):= (FORMAT);
17 v_br_id NUMBER := (BR);
18 v_amount_from NUMBER := (AFROM);
19 v_amount_to NUMBER := (ATO);
20 v_sob NUMBER := (SOB);
21
22 req_id NUMBER;
23
24 --Bug 5222370
25 l_org_id NUMBER;
26
27 -- Cursor For BR batches
28 CURSOR c_programs_br IS
29 select distinct prog.program_name program_name
30 from ra_cust_trx_types_all types
31 , ap_payment_programs prog
32 , ra_customer_trx trx
33 where types.format_program_id = prog.program_id
34 and types.cust_trx_type_id = trx.cust_trx_type_id
35 and trx.batch_id = v_br_id;
36
37 -- Cursor for Remittance batches
38 CURSOR c_programs_remit IS
39 select distinct prog.program_name program_name
40 from ra_cust_trx_types_all types
41 , ap_payment_programs prog
42 , ra_customer_trx trx
43 , ar_payment_schedules sched
44 where types.format_program_id = prog.program_id
45 and types.cust_trx_type_id = trx.cust_trx_type_id
46 and sched.customer_trx_id = trx.customer_trx_id
47 and sched.reserved_type = 'REMITTANCE'
48 and sched.reserved_value = v_br_id;
49
50 -- For Individual BRs
51 CURSOR c_programs_ind IS
52 select distinct prog.program_name program_name
53 from ra_cust_trx_types_all types
54 , ap_payment_programs prog
55 , ra_customer_trx trx
56 where types.format_program_id = prog.program_id
57 and types.cust_trx_type_id = trx.cust_trx_type_id
58 and trx.customer_trx_id = v_br_id;
59
60 begin
61
62 --Bug 5222370
63 select org_id
64 into l_org_id
65 from fnd_concurrent_requests
66 where request_id = FND_PROFILE.value('CONC_REQUEST_ID');
67
68 IF v_format = 'BR BATCH' THEN
69 for c_programs_br_rec in c_programs_br LOOP
70 fnd_request.set_org_id(l_org_id);
71 req_id:=fnd_request.submit_request('AR'
72 ,c_programs_br_rec.program_name
73 ,NULL
74 ,NULL
75 ,NULL
76 ,'P_FORMAT_OPTION="'||v_format||'"'
77 ,'P_BR_IDENTIFIER="'||v_br_id||'"'
78 ,'P_SET_OF_BOOKS_ID="'||v_sob||'"'
79 ,'P_AMOUNT_FROM="'||v_amount_from||'"'
80 ,'P_AMOUNT_TO="'||v_amount_to||'"'
81 );
82
83 if (req_id = 0) then
84 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured submiting request for BR Batch');
85 else
86 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request - '||c_programs_br_rec.program_name||'. Request Id: '||to_char(req_id));
87 end if;
88 commit;
89 END LOOP;
90 ELSIF v_format = 'REMIT BATCH' THEN
91 for c_programs_remit_rec in c_programs_remit LOOP
92
93 fnd_request.set_org_id(l_org_id);
94 req_id:=fnd_request.submit_request('AR'
95 ,c_programs_remit_rec.program_name
96 ,NULL
97 ,NULL
98 ,NULL
99 ,'P_FORMAT_OPTION="'||v_format||'"'
100 ,'P_BR_IDENTIFIER="'||v_br_id||'"'
101 ,'P_SET_OF_BOOKS_ID="'||v_sob||'"'
102 ,'P_AMOUNT_FROM="'||v_amount_from||'"'
103 ,'P_AMOUNT_TO="'||v_amount_to||'"'
104 );
105
106 if (req_id = 0) then
107 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured submiting request for Remittance Batch');
108 else
109 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request - '||c_programs_remit_rec.program_name||'. Request Id: '||to_char(req_id));
110 end if;
111 commit;
112 END LOOP;
113
114 ELSIF v_format = 'IND' THEN
115
116 for c_programs_ind_rec in c_programs_ind LOOP
117
118 fnd_request.set_org_id(l_org_id);
119 req_id:=fnd_request.submit_request('AR'
120 ,c_programs_ind_rec.program_name
121 ,NULL
122 ,NULL
123 ,NULL
124 ,'P_FORMAT_OPTION="'||v_format||'"'
125 ,'P_BR_IDENTIFIER="'||v_br_id||'"'
126 ,'P_SET_OF_BOOKS_ID="'||v_sob||'"'
127 ,'P_AMOUNT_FROM="'||v_amount_from||'"'
128 ,'P_AMOUNT_TO="'||v_amount_to||'"'
129 );
130
131 if (req_id = 0) then
132 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured submiting request for Individual BR');
133 else
134 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request - '||c_programs_ind_rec.program_name||'. Request Id: '||to_char(req_id));
135 end if;
136 commit;
137 END LOOP;
138 ELSE
139 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Format option');
140 END IF;
141
142 EXCEPTION
143 WHEN FND_FILE.UTL_FILE_ERROR then
144 errbuf:= substr(fnd_message.get,1,254);
145 retcode:=2;
146 WHEN OTHERS then
147 errbuf:= substr(SQLERRM,1,254);
148 retcode:=SQLCODE;
149
150 end SUBMIT_FORMATS;
151
152
153 end AR_BR_FORMAT_WRAPPER_PKG;