[Home] [Help]
PACKAGE BODY: APPS.GMF_SUBLEDGER_REPORT
Source
1 PACKAGE BODY GMF_SUBLEDGER_REPORT AS
2 /* $Header: gmfsubrb.pls 115.45 2004/07/23 17:09:50 dvadivel ship $ */
3
4 /* variables for break processing in the report. */
5 last_voucher_id gl_subr_led_vw.voucher_id%TYPE;
6 last_sevt_code gl_subr_led_vw.sub_event_code%TYPE;
7 last_doc_no gl_subr_led_vw.doc_no%TYPE;
8 /* BUG 2302794 */
9 doc_no_sav gl_subr_led_vw.doc_no%TYPE;
10 last_orgn_code gl_subr_led_vw.orgn_code%TYPE;
11 last_line_id gl_subr_led_vw.line_id%TYPE; /* B2262087 changed type from line_no to line_id */
12 line_no number;
13 page_no number;
14 lines_per_page NUMBER := 60; /* Bug 2048108 */
15
16 /* variables for storing amounts and totals. */
17 dr_base gl_subr_led_vw.amount_base%TYPE;
18 cr_base gl_subr_led_vw.amount_base%TYPE;
19 dr_trans gl_subr_led_vw.amount_trans%TYPE;
20 cr_trans gl_subr_led_vw.amount_trans%TYPE;
21 format_base varchar2(34); --B1316233 umoogala 08/17/01: Increased from 24 to 34
22 format_trans varchar2(34); --B1316233 umoogala 08/17/01: Increased from 24 to 34
23 local_format_base varchar2(24); -- B1316233
24 local_format_trans varchar2(24); -- B1316233
25 line_total_dr gl_subr_led_vw.amount_base%TYPE;
26 line_total_cr gl_subr_led_vw.amount_base%TYPE;
27 voucher_total_dr gl_subr_led_vw.amount_base%TYPE;
28 voucher_total_cr gl_subr_led_vw.amount_base%TYPE;
29 sevt_total_dr gl_subr_led_vw.amount_base%TYPE;
30 sevt_total_cr gl_subr_led_vw.amount_base%TYPE;
31 doc_total_dr gl_subr_led_vw.amount_base%TYPE;
32 doc_total_cr gl_subr_led_vw.amount_base%TYPE;
33 rep_total_dr gl_subr_led_vw.amount_base%TYPE;
34 rep_total_cr gl_subr_led_vw.amount_base%TYPE;
35 amount_constant constant number := 1000000000;
36
37 /* Report Title */
38 rep_title VARCHAR2(180);
39
40 /* Variable to translate canonical dates to apps dates */
41 vstart_date DATE;
42 vend_date DATE;
43
44 /* Begin Bug#2424449 Piyush K. Mishra
45 Incorporated B#2255269 */
46 min_date DATE;
47 /* End Bug#2424449 */
48
49 /* Main procedure which runs the report */
50 PROCEDURE RUN(
51 errbuf OUT NOCOPY VARCHAR2,
52 retcode OUT NOCOPY VARCHAR2,
53 preference_no IN VARCHAR2,
54 pco_code IN VARCHAR2,
55 pcurrency_code IN VARCHAR2,
56 pfiscal_year IN VARCHAR2,
57 pperiod IN VARCHAR2,
58 pstart_date IN VARCHAR2,
59 pend_date IN VARCHAR2,
60 pfrom_voucher_no IN VARCHAR2,
61 pto_voucher_no IN VARCHAR2,
62 pfrom_source_code IN VARCHAR2,
63 pto_source_code IN VARCHAR2,
64 pfrom_sub_event_code IN VARCHAR2,
65 pto_sub_event_code IN VARCHAR2,
66 report_on IN VARCHAR2,
67 rep_mode IN VARCHAR2,
68 plines_per_page IN VARCHAR2,
69 ppage_size IN NUMBER DEFAULT 132) -- Bug 2804810
70 IS
71 /* Dynamically order the report based upon the parameter selected.
72 Cursor for running report on actual subledger table */
73 CURSOR c_gl_subr_led_vw IS
74 SELECT
75 co_code,
76 fiscal_year,
77 period,
78 sub_event_code,
79 voucher_id,
80 doc_type,
81 doc_id,
82 line_id,
83 acct_ttl_code,
84 acctg_unit_no,
85 acct_no,
86 DECODE(SUM(amount_base*debit_credit_sign), 0, 1,
87 (SUM(amount_base*debit_credit_sign)/ABS(SUM(amount_base*debit_credit_sign)))) debit_credit_sign,
88 acctg_unit_desc,
89 acct_desc,
90 ABS(SUM(amount_base*debit_credit_sign)) amount_base,
91 ABS(SUM(amount_trans*debit_credit_sign)) amount_trans,
92 currency_base,
93 currency_trans,
94 SUM(jv_quantity) jv_quantity,
95 jv_quantity_um,
96 sub_event_desc,
97 trans_source_code,
98 trans_source_desc,
99 -- gl_trans_date,
100 orgn_code,
101 doc_no,
102 doc_date,
103 line_no,
104 resource_item_no,
105 resource_item_no_desc,
106 trans_date,
107 whse_code,
108 trans_qty_usage,
109 trans_qty_usage_um,
110 reference_no
111 FROM gl_subr_led_vw
112 WHERE
113 reference_no = nvl(preference_no,reference_no) and --bug# 1801491
114 co_code = pco_code and
115 fiscal_year = to_number(pfiscal_year) and
116 period = to_number(pperiod) and
117 gl_trans_date >= vstart_date and
118 gl_trans_date <= vend_date and
119 trans_source_code >= nvl(pfrom_source_code, trans_source_code) and
120 trans_source_code <= nvl(pto_source_code, trans_source_code) and
121 sub_event_code >= nvl(pfrom_sub_event_code, sub_event_code) and
122 sub_event_code <= nvl(pto_sub_event_code, sub_event_code) and
123 nvl(voucher_id,-99) >= nvl(to_number(pfrom_voucher_no), nvl(voucher_id,-99)) and
124 nvl(voucher_id,-99) <= nvl(to_number(pto_voucher_no), nvl(voucher_id,-99))
125 GROUP BY
126 co_code,
127 fiscal_year,
128 period,
129 decode(rep_mode, 'SDV',sub_event_code, 'SVD',sub_event_code,
130 'VSD',nvl(voucher_id,0), 'VDS',nvl(voucher_id,0),
131 'DSV',doc_type||doc_no, 'DVS',doc_type||doc_no),
132 decode(rep_mode, 'SDV',doc_type||doc_no, 'SVD',nvl(voucher_id,0),
133 'VSD',sub_event_code, 'VDS', doc_type||doc_no,
134 'DSV',sub_event_code, 'DVS',nvl(voucher_id,0)),
135 decode(rep_mode, 'SVD',doc_type||doc_no, 'SDV',nvl(voucher_id,0),
136 'VSD',doc_type||doc_no, 'VDS',sub_event_code,
137 'DSV',nvl(voucher_id,0), 'DVS',sub_event_code),
138 line_id, /* Everything from this point onwards will have no effect on the ordering */
139 sub_event_code,
140 voucher_id,
141 doc_type,
142 doc_no,
143 doc_id,
144 acct_ttl_code,
145 acctg_unit_no,
146 acct_no,
147 acctg_unit_desc,
148 acct_desc,
149 currency_base,
150 currency_trans,
151 jv_quantity_um,
152 sub_event_desc,
153 trans_source_code,
154 trans_source_desc,
155 -- gl_trans_date,
156 orgn_code,
157 doc_date,
158 line_no,
159 resource_item_no,
160 resource_item_no_desc,
161 trans_date,
162 whse_code,
163 trans_qty_usage,
164 trans_qty_usage_um,
165 reference_no;
166
167 /* Cursor for running report on test subledger table */
168 CURSOR c_gl_subr_tst_vw IS
169 SELECT
170 co_code,
171 fiscal_year,
172 period,
173 sub_event_code,
174 voucher_id,
175 doc_type,
176 doc_id,
177 line_id,
178 acct_ttl_code,
179 acctg_unit_no,
180 acct_no,
181 DECODE(SUM(amount_base*debit_credit_sign), 0, 1,
182 (SUM(amount_base*debit_credit_sign)/ABS(SUM(amount_base*debit_credit_sign)))) debit_credit_sign,
183 acctg_unit_desc,
184 acct_desc,
185 ABS(SUM(amount_base*debit_credit_sign)) amount_base,
186 ABS(SUM(amount_trans*debit_credit_sign)) amount_trans,
187 currency_base,
188 currency_trans,
189 SUM(jv_quantity) jv_quantity,
190 jv_quantity_um,
191 sub_event_desc,
192 trans_source_code,
193 trans_source_desc,
194 -- gl_trans_date,
195 orgn_code,
196 doc_no,
197 doc_date,
198 line_no,
199 resource_item_no,
200 resource_item_no_desc,
201 trans_date,
202 whse_code,
203 trans_qty_usage,
204 trans_qty_usage_um,
205 reference_no
206 FROM gl_subr_tst_vw
207 WHERE
208 reference_no = nvl(preference_no,reference_no) and --bug# 1801491
209 co_code = pco_code and
210 fiscal_year = to_number(pfiscal_year) and
211 period = to_number(pperiod) and
212 gl_trans_date >= vstart_date and
213 gl_trans_date <= vend_date and
214 trans_source_code >= nvl(pfrom_source_code, trans_source_code) and
215 trans_source_code <= nvl(pto_source_code, trans_source_code) and
216 sub_event_code >= nvl(pfrom_sub_event_code, sub_event_code) and
217 sub_event_code <= nvl(pto_sub_event_code, sub_event_code) and
218 nvl(voucher_id,-99) >= nvl(to_number(pfrom_voucher_no), nvl(voucher_id,-99)) and
219 nvl(voucher_id,-99) <= nvl(to_number(pto_voucher_no), nvl(voucher_id,-99))
220 GROUP BY
221 co_code,
222 fiscal_year,
223 period,
224 decode(rep_mode, 'SDV',sub_event_code, 'SVD',sub_event_code,
225 'VSD',nvl(voucher_id,0), 'VDS',nvl(voucher_id,0),
226 'DSV',doc_type||doc_no, 'DVS',doc_type||doc_no),
227 decode(rep_mode, 'SDV',doc_type||doc_no, 'SVD',nvl(voucher_id,0),
228 'VSD',sub_event_code, 'VDS', doc_type||doc_no,
229 'DSV',sub_event_code, 'DVS',nvl(voucher_id,0)),
230 decode(rep_mode, 'SVD',doc_type||doc_no, 'SDV',nvl(voucher_id,0),
231 'VSD',doc_type||doc_no, 'VDS',sub_event_code,
232 'DSV',nvl(voucher_id,0), 'DVS',sub_event_code),
233 line_id, /* Everything from this point onwards will have no effect on the ordering */
234 sub_event_code,
235 voucher_id,
236 doc_type,
237 doc_no,
238 doc_id,
239 acct_ttl_code,
240 acctg_unit_no,
241 acct_no,
242 acctg_unit_desc,
243 acct_desc,
244 currency_base,
245 currency_trans,
246 jv_quantity_um,
247 sub_event_desc,
248 trans_source_code,
249 trans_source_desc,
250 -- gl_trans_date,
251 orgn_code,
252 doc_date,
253 line_no,
254 resource_item_no,
255 resource_item_no_desc,
256 trans_date,
257 whse_code,
258 trans_qty_usage,
259 trans_qty_usage_um,
260 reference_no;
261
262 /* VC Bug 1924250 - Detail info on documents */
263 /* Cursor for running report on test subledger table */
264 CURSOR c_po_recv_dtl(v_doc_id NUMBER, v_line_id NUMBER) IS
265 SELECT
266 h.billing_currency billing_currency,
267 h.receipt_exchange_rate receipt_exchange_rate,
268 v.vendor_no vendor_no,
269 v.vendor_name vendor_name,
270 nvl(d.po_id,0) po_id,
271 nvl(d.poline_id,0) poline_id
272 FROM
273 po_recv_dtl d,
274 po_recv_hdr h,
275 po_vend_mst v
276 WHERE
277 d.recv_id = v_doc_id
278 AND d.line_id = v_line_id
279 AND d.recv_id = h.recv_id
280 AND d.shipvend_id = v.vendor_id;
281 c_po_recv c_po_recv_dtl%ROWTYPE;
282
283 CURSOR c_po_ordr_hdr(v_po_id NUMBER) IS
284 SELECT
285 h.orgn_code orgn_code,
286 h.po_no po_no
287 FROM
288 po_ordr_hdr h
289 WHERE
290 h.po_id = v_po_id;
291 c_po_ordr c_po_ordr_hdr%ROWTYPE;
292
293 CURSOR c_po_rtrn_dtl(v_doc_id NUMBER, v_line_id NUMBER) IS
294 SELECT
295 r.orgn_code orgn_code,
296 r.recv_no recv_no,
297 r.recv_date recv_date,
298 r.billing_currency billing_currency,
299 r.receipt_exchange_rate receipt_exchange_rate,
300 v.vendor_no vendor_no,
301 v.vendor_name vendor_name,
302 nvl(rd.po_id, 0) po_id,
303 nvl(rd.poline_id,0) poline_id
304 FROM
305 po_rtrn_dtl rd,
306 po_rtrn_hdr rh,
307 po_recv_hdr r,
308 po_vend_mst v
309 WHERE
310 rd.line_id = v_line_id
311 AND rd.return_id = v_doc_id
312 AND rd.recv_id = r.recv_id
313 AND rh.return_vendor_id = v.vendor_id;
314 c_po_rtrn c_po_rtrn_dtl%ROWTYPE;
315
316 CURSOR c_op_ordr_dtl(v_doc_id NUMBER, v_line_id NUMBER) IS
317 SELECT
318 h.orgn_code orgn_code,
319 h.order_no order_no,
320 h.order_date order_date,
321 c.cust_no as cust_no,
322 c.cust_name cust_name
323 FROM
324 op_ordr_dtl d,
325 op_ordr_hdr h,
326 op_cust_mst c
327 WHERE
328 d.line_id = v_line_id
329 AND d.bol_id = v_doc_id
330 AND d.order_id = h.order_id
331 AND d.shipcust_id = c.cust_id;
332 c_op_ordr c_op_ordr_dtl%ROWTYPE;
333
334 CURSOR c_pm_btch_hdr(v_batch_id NUMBER) IS
335 SELECT
336 b.plant_code ,
337 b.batch_no ,
338 b.wip_whse_code,
339 b.actual_start_date,
340 b.actual_cmplt_date,
341 nvl(b.routing_id,0) routing_id,
342 f.formula_no,
343 f.formula_vers,
344 t.meaning
345 FROM
346 gme_batch_header b,
347 fm_form_mst f,
348 gem_lookups t
349 WHERE
350 b.batch_id = v_batch_id
351 AND b.formula_id = f.formula_id
352 AND to_char(b.batch_status) = t.lookup_code
353 AND t.lookup_type = upper('batch_status');
354 c_pm_btch c_pm_btch_hdr%ROWTYPE;
355
356 CURSOR c_fm_rout_hdr(v_routing_id NUMBER) IS
357 SELECT
358 r.routing_no,
359 r.routing_vers
360 FROM
361 fm_rout_hdr r
362 WHERE
363 r.routing_id = v_routing_id;
364 c_fm_rout c_fm_rout_hdr%ROWTYPE;
365
366 /* Begin Bug#2088655 P.Raghu - Detail info on Document Type 'PROC' */
367 CURSOR c_pur_ship_dtl(v_doc_id NUMBER, v_line_id NUMBER) IS
368 SELECT
369 nvl(t.currency_conversion_rate, 1.0) exchange_rate,
370 t.currency_code billing_currency,
371 NVL(poh.segment1,' ') po_no,
372 NVL(v.vendor_no,' ') vendor_no,
373 NVL(v.vendor_name,' ') vendor_name,
374 nvl(t.po_unit_price, 0.0) po_unit_price,
375 uom1.um_code price_um
376 FROM
377 rcv_transactions t,
378 sy_uoms_mst uom1,
379 po_headers_all poh,
380 po_vend_mst v
381 WHERE
382 t.shipment_header_id = v_doc_id
383 AND t.transaction_id = v_line_id
384 AND t.source_doc_unit_of_measure = uom1.unit_of_measure
385 AND t.po_header_id = poh.po_header_id (+)
386 AND t.vendor_site_id = v.of_vendor_site_id (+)
387 AND nvl(v.co_code, pco_code) = pco_code ;
388
389 c_pur_ship c_pur_ship_dtl%ROWTYPE;
390 /* End Bug#2088655 */
391
392 /* VC Bug 2048108 - Detail info on lines */
393 CURSOR c_po_recv_hst(v_sub_event_code VARCHAR2,
394 v_recv_id NUMBER, v_recv_line_id NUMBER) IS
395 SELECT
396 h.net_price,
397 h.price_um
398 FROM
399 po_recv_hst h,
400 gl_sevt_mst sb
401 WHERE
402 h.recv_id = v_recv_id
403 AND h.recv_line_id = v_recv_line_id
404 AND sb.sub_event_type = h.sub_event_type
405 AND sb.sub_event_code = v_sub_event_code;
406
407 c_recv_hst c_po_recv_hst%ROWTYPE;
408
409 CURSOR c_ic_tran_pnd(v_doc_type VARCHAR2,
410 v_doc_id NUMBER, v_line_id NUMBER) IS
411 SELECT
412 reason_code
413 FROM
414 ic_tran_pnd
415 WHERE
416 doc_type = v_doc_type
417 AND doc_id = v_doc_id
418 AND line_id = v_line_id
419 UNION ALL
420 SELECT
421 reason_code
422 FROM
423 ic_tran_cmp
424 WHERE
425 doc_type = v_doc_type
426 AND doc_id = v_doc_id
427 AND line_id = v_line_id;
428 c_ic_tran c_ic_tran_pnd%ROWTYPE;
429
430 --Begin Bug#3072197
431 --To retrieve Reason code for Invetory Transfers (XFER).
432 CURSOR c_ic_xfer_tran(v_trans_id NUMBER) IS
433 SELECT
434 reason_code
435 FROM
436 ic_tran_pnd
437 WHERE
438 trans_id = v_trans_id;
439 --End Bug#3072197
440
441 CURSOR c_pm_matl_dtl(v_line_id NUMBER) IS
442 SELECT
443 d.wip_plan_qty,
444 d.original_qty, -- Bug# 3772552 - Fwd port for 3544905
445 t.whse_code,
446 t.trans_um
447 FROM
448 ic_tran_pnd t,
449 gme_material_details d
450 WHERE
451 t.doc_type = 'PROD'
452 AND t.line_id = v_line_id
453 AND d.material_detail_id = t.line_id;
454
455 c_pm_matl c_pm_matl_dtl%ROWTYPE;
456
457 /* Begin Bug#2365391 - Nayini Vikranth */
458 /* Get item_no, item_desc1, whse_code, trans_qty, trans_um */
459 CURSOR c_ic_piph_picy_info(v_doc_type VARCHAR2,
460 v_doc_id NUMBER, v_line_id NUMBER) IS
461 SELECT
462 t.doc_id,
463 t.line_id,
464 t.whse_code,
465 i.item_no,
466 i.item_desc1,
467 sum(t.trans_qty) trans_qty,
468 t.trans_um
469 FROM
470 ic_tran_cmp t,
471 ic_item_mst i
472 WHERE
473 t.doc_type = v_doc_type
474 AND t.doc_id = v_doc_id
475 AND t.line_id = v_line_id
476 AND t.item_id = i.item_id
477 GROUP BY
478 t.doc_id,
479 t.line_id,
480 t.whse_code,
481 i.item_no,
482 i.item_desc1,
483 t.trans_um;
484 c_ic_piph_picy c_ic_piph_picy_info%ROWTYPE;
485 /* End Bug#2365391 */
486
487 /* Begin Bug 2932095 */
488 CURSOR c_ic_piph_picy_doc(v_doc_id NUMBER) IS
489 SELECT c.orgn_code, c.cycle_no
490 FROM ic_cycl_hdr c
491 WHERE c.cycle_id = v_doc_id;
492
493 /* Begin Bug# 3772552 : Fwd port for 3601833 Added the cursor c_ic_rval_quantity */
494 CURSOR c_ic_rval_quantity(v_doc_id NUMBER,v_line_id NUMBER,
495 v_reference_no NUMBER) IS
496 SELECT sum(ic.loct_onhand)
497 FROM ic_perd_bal ic
498 ,ic_whse_mst wh
499 ,gl_subr_sta st
500 WHERE ic.whse_code = wh.whse_code
501 AND wh.mtl_organization_id = v_doc_id
502 AND ic.item_id = v_line_id
503 AND st.reference_no = v_reference_no
504 AND st.crev_inv_prev_cal = ic.fiscal_year
505 AND st.crev_inv_prev_per = ic.period
506 GROUP BY ic.item_id,ic.whse_code
507 HAVING SUM(ic.loct_onhand) <> 0;
508 /*End Bug#3772552 */
509
510
511 i NUMBER;
512 j NUMBER;
513 r c_gl_subr_led_vw%ROWTYPE;
514 l_fiscal_year VARCHAR2(4);
515 l_quantity NUMBER;
516 l_period NUMBER;
517 BEGIN
518 /* Begin Bug#2424449 Piyush K. Mishra
519 Incorporated B#2255269 */
520
521 min_date := GMA_GLOBAL_GRP.SY$MIN_DATE;
522 /*
523 * min_date := to_date(ggm_constant.get_constant('SY$MAX_DATE'),'DD-MON-YYYY HH24:MI:SS');
524 * -- Bug #2662570 (JKB) Removed reference to GMA_GLOBAL_GRP.SY$MAX_DATE above.
525 */
526 --END Bug#2424449
527
528 /* Intialize the labels with translated values */
529 INITIALIZE_LABELS;
530 /* Convert canonical dates to app dates */
531 vstart_date := fnd_date.canonical_to_date(pstart_date);
532 vend_date := fnd_date.canonical_to_date(pend_date);
533
534 lines_per_page := nvl(plines_per_page, 60); /* Bug 2048108 */
535
536 rep_total_dr := 0;
537 rep_total_cr := 0;
538
539 /* Format the report title */
540 rep_title := '|'||' '||RPAD(to_char(sysdate),36,' ');
541 IF report_on = 2 THEN rep_title := substrb(rep_title || L_GMF_TEST || ' ',1,132); END IF; /* B1309946 */
542 rep_title := substrb(rep_title || L_GMF_SUBLEDGER_REPORT||' ',1,132); /* B1309946 */
543
544 /* RS Bug 1878244 - truncate rep_title if lengthb greater than 132 */
545 FOR i in 1..3 LOOP
546 IF (substr(rep_mode,i,1)='D') THEN rep_title := substrb(rep_title|| L_GMF_DOCUMENT||'/',1,132); END IF;
547 IF (substr(rep_mode,i,1)='S') THEN rep_title := substrb(rep_title|| L_GMF_SUB_EVENT||'/',1,132); END IF;
548 IF (substr(rep_mode,i,1)='V') THEN rep_title := substrb(rep_title|| L_GMF_VOUCHER||'/',1,132); END IF;
549 END LOOP;
550
551 /* get rid of the last slash */
552 rep_title := substr (rep_title, 1, length(rep_title)-1);
553 PRINT_LINE (substr(rep_title||' '||LPAD(L_GMF_PAGE_NO, 15,' ')||':'||
554 RPAD(to_char(page_no), 6,' '),1,132)); /* Bug 2048108 */
555
556 /* Print report header */
557 PRINT_LINE ('|');
558 /* Begin bug# 1801491 */
559 IF preference_no IS NOT NULL THEN
560 PRINT_LINE ('|'||LPAD(L_GMF_REFERENCE_NO,16,' ')||': '||preference_no);
561 END IF;
562 /* End of bug# 1801491 */
563 PRINT_LINE ('|'||LPAD(L_GMF_COMPANY,16,' ')||': '||pco_code);
564 PRINT_LINE ('|'||LPAD(L_GMF_CURRENCY,16,' ')||': '||pcurrency_code);
565 PRINT_LINE ('|'||LPAD(L_GMF_FISCAL_YEAR,16,' ')||': '||pfiscal_year);
566 PRINT_LINE ('|'||LPAD(L_GMF_PERIOD,16,' ')||': '||pperiod);
567 PRINT_LINE ('|'||LPAD(L_GMF_START_DATE,16,' ')||': '||to_char(vstart_date));
568 PRINT_LINE ('|'||LPAD(L_GMF_END_DATE,16,' ')||': '||to_char(vend_date));
569 PRINT_LINE ('|'||LPAD(L_GMF_FROM,34,' ')||' '||LPAD(L_GMF_TO,16,' '));
570 PRINT_LINE ('|'||LPAD(L_GMF_VOUCHER,16,' ')||': '||LPAD(pfrom_voucher_no,16,' ')||
571 ' '||LPAD(pto_voucher_no,16,' '));
572 PRINT_LINE ('|'||LPAD(L_GMF_SOURCE,16,' ')||': '||LPAD(pfrom_source_code,16,' ')||' '||
573 LPAD(pto_source_code,16,' '));
574 PRINT_LINE ('|'||LPAD(L_GMF_SUB_EVENT,16,' ')||': '||LPAD(pfrom_sub_event_code,16,' ')||
575 ' '||LPAD(pto_sub_event_code,16,' '));
576
577 /* Open the right cursor based upon the user specified parameter */
578 IF report_on = 1 THEN
579 OPEN c_gl_subr_led_vw;
580 ELSE
581 OPEN c_gl_subr_tst_vw;
582 END IF;
583
584 /* Start the report processing */
585 WHILE TRUE
586 LOOP
587 /* Fetch row form the right cursor based upon the user specified parameter */
588 /* BUG 2302794 */
589 doc_no_sav := last_doc_no ;
590
591 IF report_on = 1 THEN
592 FETCH c_gl_subr_led_vw INTO r;
593 EXIT WHEN c_gl_subr_led_vw%NOTFOUND;
594 ELSE
595 FETCH c_gl_subr_tst_vw INTO r;
596 EXIT WHEN c_gl_subr_tst_vw%NOTFOUND;
597 END IF;
598 /* Begin Bug#2424449 Piyush K. Mishra
599 Incorporated B#2255269 */
600 if (r.doc_date <= min_date) then
601 r.doc_date := NULL ;
602 END IF;
603 if (r.trans_date <= min_date) then
604 r.trans_date := NULL ;
605 END IF;
606 --End Bug#2424449
607
608 /* Print Totals. This needs to handled first because we can only know if a
609 break occured after getting a new row */
610
611 /* Begin Bug#2365391 Nayini Vikranth */
612 /* Get orgn_code and doc_no for piph and picy trans */
613 /* Bug 2932095 Changed to cursor */
614 IF (r.doc_type in ('PIPH','PICY')) THEN
615 OPEN c_ic_piph_picy_doc(r.doc_id);
616 FETCH c_ic_piph_picy_doc INTO r.orgn_code, r.doc_no;
617 CLOSE c_ic_piph_picy_doc;
618 /* line_no is null for piph, picy trans. set it to '1' */
619 r.line_no := '1';
620 /* Begin Bug 2230751 */
621 ELSIF (r.doc_type = 'RVAL') THEN
622 IF r.doc_id <> -9 /* Bug 3196846: added if condition */
623 THEN
624 /************* Bug 3772552****************/
625 /*SELECT sum(ic.loct_onhand)
626 INTO l_quantity
627 FROM ic_perd_bal ic
628 ,ic_whse_mst wh
629 ,gl_subr_sta st
630 WHERE ic.whse_code = wh.whse_code
631 AND wh.mtl_organization_id = r.doc_id
632 AND ic.item_id = r.line_id
633 AND st.reference_no = r.reference_no
634 AND st.crev_inv_prev_cal = ic.fiscal_year
635 AND st.crev_inv_prev_per = ic.period
636 GROUP BY ic.item_id,ic.whse_code
637 HAVING SUM(ic.loct_onhand) <> 0; */ -- Commented and added a cursor c_ic_rval_quantity
638
639 OPEN c_ic_rval_quantity(r.doc_id, r.line_id, r.reference_no);
640 FETCH c_ic_rval_quantity INTO l_quantity;
641 IF (c_ic_rval_quantity%NOTFOUND) THEN
642 l_quantity := 0;
643 END IF;
644 CLOSE c_ic_rval_quantity;
645 /************** Bug 3772552 ****************/
646
647 ELSIF r.doc_id = -9
648 THEN
649 /* This is Lot Cost Adjustment */
650 SELECT onhand_qty
651 INTO l_quantity
652 FROM gmf_lot_cost_adjustments
653 WHERE adjustment_id = r.line_id;
654 END IF; /* End bug 3196846 */
655 /* End bug 2230751 */
656 END IF;
657 /* End Bug#2365391 */
658
659 PRINT_TOTALS (r.line_id, r.orgn_code||r.doc_no, r.sub_event_code, r.voucher_id, rep_mode, ppage_size); -- Bug 2804810
660
661 /* If a break occured for sub-event, voucher or document, display a new header */
662 /* RS Bug 1878244 - lpad/rpad L_GMF_DOC_NO and L_GMF_DOC_DATE */
663
664 /* Begin Bug#3437426 D.Sailaja */
665 /* Transaction Date of rcv_transactions table instead of Shipped date from rcv_shipment_headers */
666 IF (r.doc_type = 'PORC') THEN
667 r.doc_date := r.trans_date;
668 END IF;
669 /* End Bug#3437426 */
670
671 IF (last_sevt_code IS NULL OR last_sevt_code <> r.sub_event_code OR
672 last_voucher_id IS NULL OR last_voucher_id <> nvl(r.voucher_id, 0) OR
673 last_orgn_code||last_doc_no IS NULL OR last_orgn_code||last_doc_no <> r.orgn_code||r.doc_no) THEN
674
675 IF ppage_size = 132 THEN PRINT_LINE ( RPAD('|',132,'-')); -- Bug 2804810
676 ELSIF ppage_size = 180 THEN PRINT_LINE ( RPAD('|',180,'-'));
677 END IF;
678
679 PRINT_LINE ( '|'||LPAD(L_GMF_SUB_EVENT,16,' ')||': '||RPAD(r.sub_event_desc,61,' ')||
680 LPAD(L_GMF_VOUCHER,18,' ')||': '||RPAD(to_char(r.voucher_id),15,' '));
681 PRINT_LINE ( '|'||LPAD(L_GMF_DOC_TYPE,16,' ')|| ': '||RPAD(r.doc_type,4,' ') || ' '||
682 LPAD(L_GMF_DOC_NO,16,' ')||': '||RPAD((r.orgn_code || ' ' ||r.doc_no),37,' ')||
683 ' '||LPAD(L_GMF_DOC_DATE,18,' ')||': '||r.doc_date ); /* Bug 2641704 */
684
685 /* VC Bug 1924250 - Detail info on documents */
686 IF (r.doc_type = 'RECV') THEN
687 OPEN c_po_recv_dtl(r.doc_id, r.line_id);
688 FETCH c_po_recv_dtl INTO c_po_recv;
689 CLOSE c_po_recv_dtl;
690 PRINT_LINE ( '|'||LPAD(L_GMF_VENDOR,16,' ')||': '||RPAD(c_po_recv.vendor_no,32,' ')||
691 RPAD(c_po_recv.vendor_name,40,' '));
692 IF (c_po_recv.po_id > 0) THEN
693 OPEN c_po_ordr_hdr(c_po_recv.po_id);
694 FETCH c_po_ordr_hdr INTO c_po_ordr;
695 CLOSE c_po_ordr_hdr;
696 /* Bug 2641704 Replaced with next line PRINT_LINE ( '|'||LPAD(L_GMF_PO_NO,16,' ')||': '||RPAD(c_po_ordr.orgn_code,4,' ')||' '||RPAD(c_po_ordr.po_no,32,' ')|| */
697 PRINT_LINE ( '|'||LPAD(L_GMF_PO_NO,16,' ')||': '||RPAD((c_po_ordr.orgn_code||' '||c_po_ordr.po_no),37,' ')||
698 LPAD(L_GMF_BILL_CURR,16,' ')||': '||RPAD(c_po_recv.billing_currency,4,' ')||
699 LPAD(L_GMF_XCHG_RATE,18,' ')||': '||RPAD(to_char(c_po_recv.receipt_exchange_rate),15,' '));
700 ELSE
701 PRINT_LINE ( '|'||LPAD(L_GMF_PO_NO,16,' ')||': '||RPAD(' ',4,' ')||' '||LPAD(' ',32,' ')||
702 LPAD(L_GMF_BILL_CURR,16,' ')||': '||RPAD(c_po_recv.billing_currency,4,' ')||
703 LPAD(L_GMF_XCHG_RATE,18,' ')||': '||RPAD(to_char(c_po_recv.receipt_exchange_rate),15,' '));
704 END IF;
705 ELSIF (r.doc_type = 'RTRN') THEN
706 OPEN c_po_rtrn_dtl(r.doc_id, r.line_id);
707 FETCH c_po_rtrn_dtl INTO c_po_rtrn;
708 CLOSE c_po_rtrn_dtl;
709 /* Bug 2641704 Replaced with next line PRINT_LINE ( '|'||LPAD(L_GMF_RECEIPT,16,' ')||': '||RPAD(c_po_rtrn.orgn_code,4,' ') || ' ' ||RPAD(c_po_rtrn.recv_no,16,' ')||' '|| */
710 PRINT_LINE ( '|'||LPAD(L_GMF_RECEIPT,16,' ')||': '||RPAD((c_po_rtrn.orgn_code||' '||c_po_rtrn.recv_no),37,' ')||' '||
711 LPAD(L_GMF_RECEIPT_DATE,14,' ')||': '||to_char(c_po_rtrn.recv_date)||' '||LPAD(L_GMF_VENDOR,12,' ')||': '||RPAD(c_po_rtrn.vendor_no,16,' ')|| RPAD(c_po_rtrn.vendor_name,16,' '));
712 IF (c_po_rtrn.po_id > 0) THEN
713 OPEN c_po_ordr_hdr(c_po_rtrn.po_id);
714 FETCH c_po_ordr_hdr INTO c_po_ordr;
715 CLOSE c_po_ordr_hdr;
716 /* Bug 2641704 Replaced with next line PRINT_LINE ( '|'||LPAD(L_GMF_PO_NO,16,' ')||': '||RPAD(c_po_ordr.orgn_code,4,' ')||' '||RPAD(c_po_ordr.po_no,32,' ')|| */
717 PRINT_LINE ( '|'||LPAD(L_GMF_PO_NO,16,' ')||': '||RPAD((c_po_ordr.orgn_code||' '||c_po_ordr.po_no),37,' ')||
718 LPAD(L_GMF_BILL_CURR,16,' ')||': '||RPAD(c_po_rtrn.billing_currency,4,' ')||
719 LPAD(L_GMF_XCHG_RATE,18,' ')||': '||RPAD(to_char(c_po_rtrn.receipt_exchange_rate),15,' '));
720 ELSE
721 PRINT_LINE ( '|'||LPAD(L_GMF_PO_NO,16,' ')||': '||RPAD(' ',4,' ')||' '||RPAD(' ',32,' ')||
722 LPAD(L_GMF_BILL_CURR,16,' ')||': '||RPAD(c_po_rtrn.billing_currency,4,' ')||
723 LPAD(L_GMF_XCHG_RATE,18,' ')||': '||RPAD(to_char(c_po_rtrn.receipt_exchange_rate),15,' '));
724 END IF;
725 ELSIF (r.doc_type = 'OPSP') THEN
726 OPEN c_op_ordr_dtl(r.doc_id, r.line_id);
727 FETCH c_op_ordr_dtl INTO c_op_ordr;
728 CLOSE c_op_ordr_dtl;
729 PRINT_LINE ( '|'||LPAD(L_GMF_CUSTOMER,16,' ')||': '||RPAD(c_op_ordr.cust_no,32,' ')|| RPAD(c_op_ordr.cust_name,40,' '));
730 /* Bug 2641704 Replaced with next line PRINT_LINE ( '|'||LPAD(L_GMF_SO_NO,16,' ')||': '||RPAD(c_op_ordr.orgn_code,4,' ')||' '||RPAD(c_op_ordr.order_no,32,' ')|| */
731 PRINT_LINE ( '|'||LPAD(L_GMF_SO_NO,16,' ')||': '||RPAD((c_op_ordr.orgn_code||' '||c_op_ordr.order_no),37,' ')||
732 LPAD(L_GMF_SO_DATE,18,' ')||': '||to_char(c_op_ordr.order_date));
733 /* Begin Bug#2088655 P.Raghu */
734 ELSIF (r.doc_type = 'PORC') THEN
735 OPEN c_pur_ship_dtl(r.doc_id, r.line_id);
736 FETCH c_pur_ship_dtl INTO c_pur_ship;
737 CLOSE c_pur_ship_dtl;
738 PRINT_LINE ( '|'||LPAD(L_GMF_VENDOR,16,' ')||': '||RPAD(c_pur_ship.vendor_no,32,' ')||
739 RPAD(c_pur_ship.vendor_name,40,' '));
740 PRINT_LINE ( '|'||LPAD(L_GMF_PO_NO,16,' ')||': '||RPAD(c_pur_ship.po_no,20,' ')||
741 LPAD(L_GMF_BILL_CURR,16,' ')||': '||RPAD(c_pur_ship.billing_currency,4,' ')||
742 LPAD(L_GMF_XCHG_RATE,18,' ')||': '||RPAD(to_char(c_pur_ship.exchange_rate),15,' '));
743 /* End Bug#2088655 */
744 ELSIF (r.doc_type = 'PROD') THEN
745 OPEN c_pm_btch_hdr(r.doc_id);
746 FETCH c_pm_btch_hdr INTO c_pm_btch;
747 CLOSE c_pm_btch_hdr;
748 /* Begin Bug#2424449 Piyush K. Mishra
749 Incorporated B#2255269 */
750 if (c_pm_btch.actual_start_date <= min_date) then
751 c_pm_btch.actual_start_date := NULL ;
752 END IF;
753 if (c_pm_btch.actual_cmplt_date <= min_date) then
754 c_pm_btch.actual_cmplt_date := NULL ;
755 END IF;
756 --End Bug#2424449
757
758 PRINT_LINE ( '|'||LPAD(L_GMF_FORMULA,16,' ')||': '||RPAD(c_pm_btch.formula_no,32,' ')||' '||LPAD(L_GMF_VERSION,16,' ')||': '||
759 RPAD(c_pm_btch.formula_vers,5,' ')||' '||LPAD(L_GMF_ACTUAL_START_DATE,22,' ')||': '||RPAD(to_char(c_pm_btch.actual_start_date),18,' '));
760 IF (c_pm_btch.routing_id > 0) THEN
761 OPEN c_fm_rout_hdr(c_pm_btch.routing_id);
762 FETCH c_fm_rout_hdr INTO c_fm_rout;
763 CLOSE c_fm_rout_hdr;
764 PRINT_LINE ( '|'||LPAD(L_GMF_ROUTING,16,' ')||': '||
765 RPAD(c_fm_rout.routing_no,32,' ')||' '||LPAD(L_GMF_VERSION,16,' ')||': '||RPAD(c_fm_rout.routing_vers,5,' ')||' '||
766 LPAD(L_GMF_ACTUAL_CMPLT_DATE,22,' ')||': '||RPAD(to_char(c_pm_btch.actual_cmplt_date),18,' '));
767 ELSE
768 PRINT_LINE ( '|'||LPAD(L_GMF_ROUTING,16,' ')||': '||
769 RPAD(' ',32,' ')||' '||LPAD(L_GMF_VERSION,16,' ')||': '||RPAD(' ',5,' ')||' '||LPAD(L_GMF_ACTUAL_CMPLT_DATE,22,' ')||': '||
770 RPAD(to_char(c_pm_btch.actual_cmplt_date),18,' '));
771 END IF;
772 PRINT_LINE ( '|'||LPAD(L_GMF_BATCH_STATUS,16,' ')||': '||RPAD(c_pm_btch.meaning,32,' ')||' '||LPAD(L_GMF_WIP_WHSE,16,' ')||': '||RPAD(c_pm_btch.wip_whse_code,4,' '));
773 END IF;
774
775 /* VC Bug 1924250 - End of Detail info on documents */
776
777 IF ppage_size = 132 THEN PRINT_LINE ( RPAD('|',132,'-')); -- Bug 2804810
778 ELSIF ppage_size = 180 THEN PRINT_LINE ( RPAD('|',180,'-'));
779 END IF;
780 END IF;
781 /* If break occured for sub-event, reset its totals */
782 IF (last_sevt_code IS NULL OR last_sevt_code <> r.sub_event_code ) THEN
783 last_sevt_code := r.sub_event_code;
784 sevt_total_dr := 0;
785 sevt_total_cr := 0;
786 /* Also reset totals for breaks with break level lower then sub-event */
787 IF (rep_mode = 'SVD' OR rep_mode = 'SDV' OR rep_mode = 'DSV') THEN
788 voucher_total_dr := 0;
789 voucher_total_cr := 0;
790 END IF;
791 IF (rep_mode = 'SVD' OR rep_mode = 'SDV' OR rep_mode = 'VSD') THEN
792 doc_total_dr := 0;
793 doc_total_cr := 0;
794 END IF;
795 END IF;
796
797 /* If break occured for Voucher, reset its totals */
798 IF (last_voucher_id IS NULL OR last_voucher_id <> nvl(r.voucher_id, 0)) THEN
799 last_voucher_id := nvl(r.voucher_id,0);
800 voucher_total_dr := 0;
801 voucher_total_cr := 0;
802 /* Also reset totals for breaks with break level lower then voucher */
803 IF (rep_mode = 'VSD' OR rep_mode = 'VDS' OR rep_mode = 'DVS') THEN
804 sevt_total_dr := 0;
805 sevt_total_cr := 0;
806 END IF;
807 IF (rep_mode = 'VSD' OR rep_mode = 'VDS' OR rep_mode = 'SVD') THEN
808 doc_total_dr := 0;
809 doc_total_cr := 0;
810 END IF;
811 END IF;
812
813 /* If break occured for Document, reset its totals */
814 IF (last_orgn_code||last_doc_no IS NULL OR last_orgn_code||last_doc_no <> r.orgn_code||r.doc_no) THEN
815 last_doc_no := r.doc_no;
816 last_orgn_code := r.orgn_code;
817 doc_total_dr := 0;
818 doc_total_cr := 0;
819 /* Also reset totals for breaks with break level lower then document */
820 IF (rep_mode = 'DSV' OR rep_mode = 'DVS' OR rep_mode = 'SDV') THEN
821 voucher_total_dr := 0;
822 voucher_total_cr := 0;
823 END IF;
824 IF (rep_mode = 'DSV' OR rep_mode = 'DVS' OR rep_mode = 'VDS') THEN
825 sevt_total_dr := 0;
826 sevt_total_cr := 0;
827 END IF;
828 END IF;
829
830 /* If this is a new line, display line level document information */
831 /* BUG 2302794
832 IF (last_line_id IS NULL OR (last_line_id <> r.line_id)) THEN
833 */
834 IF (last_line_id IS NULL OR (last_line_id <> r.line_id) OR (doc_no_sav <> r.doc_no)) THEN
835 last_line_id := r.line_id;
836 line_total_dr := 0;
837 line_total_cr := 0;
838 IF ppage_size = 132 THEN -- Bug 2804810
839 format_trans := fnd_currency.get_format_mask(r.currency_trans,20); -- B2048108
840 ELSIF ppage_size = 180 THEN
841 format_trans := fnd_currency.get_format_mask(r.currency_trans,30); -- Bug 2804810
842 END IF;
843
844 IF (r.line_no IS NOT NULL) THEN
845 /* Begin Bug#2088655 P.Raghu */
846 /* Added 'PORC' Doc Type */
847 IF (r.doc_type = 'RECV' OR r.doc_type = 'RTRN' OR r.doc_type = 'PORC') THEN
848 IF (r.doc_type = 'PORC') THEN
849 --BEGIN BUG#3359584
850 OPEN c_pur_ship_dtl(r.doc_id, r.line_id);
851 FETCH c_pur_ship_dtl INTO c_pur_ship;
852 CLOSE c_pur_ship_dtl;
853 --END BUG#3359584
854 c_recv_hst.net_price := c_pur_ship.po_unit_price;
855 c_recv_hst.price_um := c_pur_ship.price_um;
856 ELSE
857 -- Bug 2048108 Print Unit Price for po receipts and returns
858 OPEN c_po_recv_hst(r.sub_event_code, r.doc_id, r.line_id);
859 FETCH c_po_recv_hst INTO c_recv_hst;
860 IF ( c_po_recv_hst%NOTFOUND ) THEN
861 c_recv_hst.net_price := NULL;
862 c_recv_hst.price_um := NULL;
863 END IF;
864 CLOSE c_po_recv_hst;
865 END IF;
866 /* End Bug#2088655 */
867 PRINT_LINE ('|'||LPAD(L_GMF_LINE, 8, ' ')||' '||RPAD(L_GMF_ITEM,32,' ')||' '||
868 RPAD(L_GMF_TRANS_DATE,13,' ')||' '||RPAD(L_GMF_WHSE, 6, ' ')||' '||
869 LPAD(L_GMF_QUANTITY,14,' ')||' '||RPAD(L_GMF_UOM, 6, ' ')||' '||LPAD(L_GMF_UNIT_PRICE,14,' ')||' '||RPAD(L_GMF_UOM, 6, ' '));
870 PRINT_LINE ('|'||LPAD('------', 8, ' ')||' '||RPAD('----',32,' ')||' '||
871 RPAD('----------',13,' ')||' '||RPAD('----', 6, ' ')||' '||LPAD('----------',14,' ')||
872 ' '||RPAD('--', 6, ' ')||' '||LPAD('----------',14,' ')||' '||RPAD('--', 6,' '));
873 /* Bug 2262087. Modified to_char(r.line_no) to r.line_no */
874 PRINT_LINE ( '|'||LPAD(r.line_no,8,' ')||' '||RPAD(r.resource_item_no, 32, ' ')||
875 ' '|| RPAD(nvl(to_char(r.trans_date),' '), 13, ' ')||' '||RPAD(r.whse_code, 6, ' ')||' '||
876 LPAD(to_char(r.trans_qty_usage,'9999999999D999'),14,' ')||' '||RPAD(r.trans_qty_usage_um, 6, ' ')||
877 ' '|| LPAD(nvl(to_char(c_recv_hst.net_price,format_trans),' '),14,' ')||' '||
878 RPAD(nvl(c_recv_hst.price_um,' '), 6, ' '));
879 PRINT_LINE ( '|'||' '||' '||r.resource_item_no_desc);
880 ELSIF (r.trans_source_code = 'IC') THEN
881 -- Bug 2048108 Print reason code
882 --Begin Bug#3072197
883 IF r.doc_type = 'XFER' THEN
884 OPEN c_ic_xfer_tran(r.line_id);
885 FETCH c_ic_xfer_tran INTO c_ic_tran;
886 IF ( c_ic_xfer_tran%NOTFOUND ) THEN
887 c_ic_tran.reason_code := NULL;
888 END IF;
889 CLOSE c_ic_xfer_tran;
890 ELSE
891 --End Bug#3072197
892 OPEN c_ic_tran_pnd(r.doc_type, r.doc_id, r.line_id);
893 FETCH c_ic_tran_pnd INTO c_ic_tran;
894 IF ( c_ic_tran_pnd%NOTFOUND ) THEN
895 c_ic_tran.reason_code := NULL;
896 END IF;
897 CLOSE c_ic_tran_pnd;
898 --Begin Bug#3072197
899 END IF;
900 --End Bug#3072197
901 /* Begin Bug#2365391 - Nayini Vikranth */
902 /* Get item_no, item_desc1, whse_code, trans_qty, trans_um for piph and picy trans */
903 IF (r.doc_type in ('PIPH','PICY')) THEN
904 OPEN c_ic_piph_picy_info(r.doc_type, r.doc_id, r.line_id);
905 FETCH c_ic_piph_picy_info INTO c_ic_piph_picy;
906 IF ( c_ic_piph_picy_info%NOTFOUND ) THEN
907 NULL;
908 ELSE
909 r.resource_item_no := c_ic_piph_picy.item_no;
910 r.resource_item_no_desc := c_ic_piph_picy.item_desc1;
911 r.whse_code := c_ic_piph_picy.whse_code;
912 r.trans_qty_usage := c_ic_piph_picy.trans_qty;
913 r.trans_qty_usage_um := c_ic_piph_picy.trans_um;
914 END IF;
915 CLOSE c_ic_piph_picy_info;
916 END IF;
917 /* End Bug#2365391 */
918 PRINT_LINE ('|'||LPAD(L_GMF_LINE, 8, ' ')||' '||RPAD(L_GMF_ITEM,32,' ')||' '||
919 RPAD(L_GMF_TRANS_DATE,13,' ')||' '||RPAD(L_GMF_WHSE, 6, ' ')||' '||
920 LPAD(L_GMF_QUANTITY,14,' ')||' '||RPAD(L_GMF_UOM, 6, ' ')||' '||RPAD(L_GMF_REAS_CODE,6,' '));
921 PRINT_LINE ('|'||LPAD('------', 8, ' ')||' '||RPAD('----',32,' ')||' '||
922 RPAD('----------',13,' ')||' '||RPAD('----', 6, ' ')||' '||LPAD('----------',14,' ')||
923 ' '||RPAD('--',6, ' ')||' '||LPAD('----',6,' '));
924 PRINT_LINE ( '|'||LPAD(r.line_no,8,' ')||' '||RPAD(r.resource_item_no, 32, ' ')||
925 ' '|| RPAD(nvl(to_char(r.trans_date),' '), 13, ' ')||' '||RPAD(r.whse_code, 6, ' ')||' '||
926 LPAD(to_char(r.trans_qty_usage,'9999999999D999'),14,' ')||' '||RPAD(r.trans_qty_usage_um, 6, ' ')||
927 ' '|| LPAD(nvl(c_ic_tran.reason_code,' '),6,' '));
928 PRINT_LINE ( '|'||' '||' '||r.resource_item_no_desc);
929
930
931
932 ELSIF (r.doc_type = 'PROD' AND (r.sub_event_code = 'RELE' OR r.sub_event_code = 'CERT')) THEN
933 -- Bug 2048108 Print Plan Qty and Actual Qty for RELE and CERT sub-events
934 -- B2275872 Moved IF stmt below to previous ELSIF
935 -- IF (r.sub_event_code = 'RELE' OR r.sub_event_code = 'CERT') THEN
936 OPEN c_pm_matl_dtl(r.line_id);
937 FETCH c_pm_matl_dtl INTO c_pm_matl;
938 IF ( c_pm_matl_dtl%NOTFOUND ) THEN
939 c_pm_matl.wip_plan_qty := NULL;
940 c_pm_matl.whse_code := NULL;
941 c_pm_matl.original_qty := NULL; --Bug# 3772552 - Fwd port for 3544905
942 END IF;
943 CLOSE c_pm_matl_dtl;
944 PRINT_LINE ('|'||LPAD(L_GMF_LINE, 8, ' ')||' '||RPAD(L_GMF_ITEM,32,' ')||' '||
945 RPAD(L_GMF_TRANS_DATE,13,' ')||' '||RPAD(L_GMF_WHSE, 6, ' ')||' '||
946 LPAD(L_GMF_PLAN_QTY,14,' ')||' '||RPAD(L_GMF_UOM, 6, ' ')||' '||LPAD(L_GMF_ACTL_QTY,14,' ')||' '||RPAD(L_GMF_UOM, 6,' ')||' '||LPAD(L_GMF_ORIG_QTY,14,' ')||' '||RPAD(L_GMF_UOM, 6,' ')); -- Bug 3772552
947 PRINT_LINE ('|'||LPAD('------', 8, ' ')||' '||RPAD('----',32,' ')||' '||
948 RPAD('----------',13,' ')||' '||RPAD('----', 6, ' ')||' '||LPAD('----------',14,' ')||
949 ' '||RPAD('--', 6,' ')||' '||LPAD('----------',14,' ')||' '||RPAD('--', 6, ' ')||' '||LPAD('----------',14,' ')||' '||RPAD('--', 6, ' ')); -- Bug 3772552 - extra underscores for original quantity
950 PRINT_LINE ( '|'||LPAD(r.line_no,8,' ')||' '||RPAD(r.resource_item_no, 32, ' ')||
951 ' '|| RPAD(nvl(to_char(r.trans_date),' '), 13, ' ')||' '||RPAD(nvl(c_pm_matl.whse_code,' '), 6, ' ')||' '||
952 LPAD(nvl(to_char(c_pm_matl.wip_plan_qty,'9999999999D999'),' '),14,' ')||' '||RPAD(r.trans_qty_usage_um, 6,' ')||
953 ' '|| LPAD(to_char(r.trans_qty_usage,'9999999999D999'),14,' ')||' '||RPAD(r.trans_qty_usage_um, 6, ' ')||
954 ' '|| LPAD(nvl(to_char(c_pm_matl.original_qty,'9999999999D999'),' '),14,' ')||' '||RPAD(r.trans_qty_usage_um, 6,' ')); -- Bug 3772552
955
956 /* Bug 3133895 ... print item description */
957 PRINT_LINE ( '|'||' '||' '||r.resource_item_no_desc);
958 -- END IF;
959 /* Begin Bug 2230751 */
960 ELSIF(r.doc_type = 'RVAL') THEN
961 PRINT_LINE ( '|');
962 PRINT_LINE ('|'||LPAD(L_GMF_LINE, 8, ' ')||' '||RPAD(L_GMF_ITEM,32,' ')||' '||
963 RPAD(L_GMF_TRANS_DATE,13,' ')||' '||RPAD(L_GMF_WHSE, 6, ' ')||' '||
964 LPAD(L_GMF_QUANTITY,14,' ')||' '||RPAD(L_GMF_UOM, 6, ' '));
965 PRINT_LINE ('|'||LPAD('------', 8, ' ')||' '||RPAD('----',32,' ')||' '||
966 RPAD('----------',13,' ')||' '||RPAD('----', 6, ' ')||' '||LPAD('----------',14,' ')||
967 ' '||RPAD('--',6, ' '));
968 PRINT_LINE ( '|'||LPAD(r.line_no,8,' ')||' '||RPAD(r.resource_item_no, 32, ' ')||
969 ' '|| RPAD(nvl(to_char(r.trans_date),' '), 13, ' ')||' '||RPAD(r.whse_code, 6, ' ')||' '||
970 LPAD(to_char(l_quantity,'9999999999D999'),14,' ')||' '||RPAD(r.trans_qty_usage_um, 6, ' '));
971 PRINT_LINE ( '|'||' '||' '||r.resource_item_no_desc);
972 /* End bug 2230751 */
973
974 ELSE
975 PRINT_LINE ('|'||LPAD(L_GMF_LINE, 8, ' ')||' '||RPAD(L_GMF_ITEM,32,' ')||' '||
976 RPAD(L_GMF_TRANS_DATE,13,' ')||' '||RPAD(L_GMF_WHSE, 6, ' ')||' '||
977 LPAD(L_GMF_QUANTITY,14,' ')||' '||L_GMF_UOM);
978 PRINT_LINE ('|'||LPAD('------', 8, ' ')||' '||RPAD('----',32,' ')||' '||
979 RPAD('----------',13,' ')||' '||RPAD('----', 6, ' ')||' '||LPAD('----------',14,' ')||
980 ' '||'--');
981 PRINT_LINE ( '|'||LPAD(r.line_no,8,' ')||' '||RPAD(r.resource_item_no, 32, ' ')||
982 ' '|| RPAD(nvl(to_char(r.trans_date),' '), 13, ' ')||' '||RPAD(r.whse_code, 6, ' ')||' '||
983 LPAD(to_char(r.trans_qty_usage,'9999999999D999'),14,' ')||' '||r.trans_qty_usage_um);
984 PRINT_LINE ( '|'||' '||' '||r.resource_item_no_desc);
985
986
987
988
989
990
991 END IF;
992 END IF;
993 /* Also print the heading for the accounting information */
994 PRINT_LINE ( '|');
995 IF ppage_size = 132 THEN -- Bug 2804810
996
997 PRINT_LINE ( RPAD('|',46,' ')||RPAD('----'||L_GMF_BASE_CURR,29,'-')||' '||
998 RPAD('----'||L_GMF_BILL_CURR,29,'-'));
999 PRINT_LINE ( '|'||LPAD(L_GMF_TTL,7,' ')||' '||RPAD(L_GMF_AU_AND_ACCT,36,' ')||' '||
1000 LPAD(L_GMF_DEBIT,14,' ')||' '||LPAD(L_GMF_CREDIT,14,' ')||' '||
1001 LPAD(L_GMF_DEBIT,14,' ')||' '||LPAD(L_GMF_CREDIT,14,' ')||' '||
1002 RPAD(L_GMF_CURRENCY,4, ' ')||' '||LPAD(L_GMF_JV_QTY,14,' ')||' '||L_GMF_UOM);
1003 PRINT_LINE ( '|'||LPAD('---',7,' ')||' '||RPAD('-------------------------',36,' ')||' '||
1004 '-------------- -------------- -------------- -------------- '||
1005 '---- -------------- ----');
1006
1007 ELSIF ppage_size = 180 THEN -- Bug 2804810
1008
1009 PRINT_LINE ( RPAD('|',46,' ')||RPAD('----'||L_GMF_BASE_CURR,53,'-')||' '||
1010 RPAD('----'||L_GMF_BILL_CURR,53,'-'));
1011 PRINT_LINE ( '|'||LPAD(L_GMF_TTL,7,' ')||' '||RPAD(L_GMF_AU_AND_ACCT,36,' ')||' '||
1012 LPAD(L_GMF_DEBIT,26,' ')||' '||LPAD(L_GMF_CREDIT,26,' ')||' '||
1013 LPAD(L_GMF_DEBIT,26,' ')||' '||LPAD(L_GMF_CREDIT,26,' ')||' '||
1014 RPAD(L_GMF_CURRENCY,4, ' ')||' '||LPAD(L_GMF_JV_QTY,14,' ')||' '||L_GMF_UOM);
1015 PRINT_LINE ( '|'||LPAD('---',7,' ')||' '||RPAD('-------------------------',36,' ')||' '||
1016 '-------------------------- -------------------------- ' ||
1017 '-------------------------- -------------------------- ' ||
1018 '---- -------------- ----');
1019
1020 END IF;
1021 END IF;
1022
1023 /* Display the subledger information for the document line and maintian totals */
1024 IF r.debit_credit_sign = 1 THEN
1025 dr_base := r.amount_base;
1026 cr_base := NULL;
1027 line_total_dr := line_total_dr + dr_base;
1028 doc_total_dr := doc_total_dr + dr_base;
1029 voucher_total_dr := voucher_total_dr + dr_base;
1030 sevt_total_dr := sevt_total_dr + dr_base;
1031 rep_total_dr := rep_total_dr + dr_base;
1032 dr_trans := r.amount_trans;
1033 cr_trans := NULL;
1034
1035 IF ppage_size = 132 THEN -- Bug 2804810
1036 format_base := fnd_currency.get_format_mask(r.currency_base,20); -- B1316233
1037 format_trans := fnd_currency.get_format_mask(r.currency_trans,20); -- B1316233
1038 ELSIF ppage_size = 180 THEN -- Bug 2804810
1039 format_base := fnd_currency.get_format_mask(r.currency_base,30); -- B1316233
1040 format_trans := fnd_currency.get_format_mask(r.currency_trans,30); -- B1316233
1041 END IF;
1042
1043 ELSE
1044 dr_base := NULL;
1045 cr_base := r.amount_base;
1046 line_total_cr := line_total_cr + cr_base;
1047 doc_total_cr := doc_total_cr + cr_base;
1048 voucher_total_cr := voucher_total_cr + cr_base;
1049 sevt_total_cr := sevt_total_cr + cr_base;
1050 rep_total_cr := rep_total_cr + cr_base;
1051 dr_trans := NULL;
1052 cr_trans := r.amount_trans;
1053
1054 IF ppage_size = 132 THEN -- Bug 2804810
1055 format_base := fnd_currency.get_format_mask(r.currency_base,20); -- B1316233
1056 format_trans := fnd_currency.get_format_mask(r.currency_trans,20); -- B1316233
1057 ELSIF ppage_size = 180 THEN
1058 format_base := fnd_currency.get_format_mask(r.currency_base,30); -- B1316233
1059 format_trans := fnd_currency.get_format_mask(r.currency_trans,30); -- B1316233
1060 END IF;
1061
1062 END IF;
1063
1064 /* Begin bug# 1316233 */
1065 IF (nvl(dr_base,0) < amount_constant AND
1066 nvl(cr_base,0) < amount_constant AND
1067 nvl(dr_trans,0) < amount_constant AND
1068 nvl(cr_trans,0) <amount_constant) THEN
1069
1070 IF ppage_size = 132 THEN -- Bug 2804810
1071 PRINT_LINE ( '| '||r.acct_ttl_code||' '||RPAD(r.acctg_unit_no||' '||r.acct_no,36,' ')||
1072 ' '|| LPAD(nvl(to_char(dr_base,format_base),' '),14,' ')||' '||
1073 LPAD(nvl(to_char(cr_base,format_base),' '),14,' ')||' '||
1074 LPAD(nvl(to_char(dr_trans,format_trans),' '),14,' ')||' '||
1075 LPAD(nvl(to_char(cr_trans,format_trans),' '),14,' ')||' '||RPAD(r.currency_trans,4,' ')||
1076 ' '|| LPAD(nvl(to_char(r.jv_quantity,'9999999999D999'),' '),14,' ')||' '||r.jv_quantity_um);
1077 ELSIF ppage_size = 180 THEN
1078 PRINT_LINE ( '| '||r.acct_ttl_code||' '||RPAD(r.acctg_unit_no||' '||r.acct_no,36,' ')||
1079 ' '|| LPAD(nvl(to_char(dr_base,format_base),' '),26,' ')||' '||
1080 LPAD(nvl(to_char(cr_base,format_base),' '),26,' ')||' '||
1081 LPAD(nvl(to_char(dr_trans,format_trans),' '),26,' ')||' '||
1082 LPAD(nvl(to_char(cr_trans,format_trans),' '),26,' ')||' '||RPAD(r.currency_trans,4,' ')||
1083 ' '|| LPAD(nvl(to_char(r.jv_quantity,'9999999999D999'),' '),14,' ')||' '||r.jv_quantity_um);
1084 END IF;
1085
1086 ELSE
1087 IF ppage_size = 132 THEN -- Bug 2804810
1088 PRINT_LINE ( '| '||r.acct_ttl_code||' '||RPAD(r.acctg_unit_no||' '||r.acct_no,36,' ')||
1089 ' '|| LPAD(nvl(to_char(dr_base,local_format_base),' '),14,' ')||' '||
1090 LPAD(nvl(to_char(cr_base,local_format_base),' '),14,' ')||' '||
1091 LPAD(nvl(to_char(dr_trans,local_format_trans),' '),14,' ')||' '||
1092 LPAD(nvl(to_char(cr_trans,local_format_trans),' '),14,' ')||' '||RPAD(r.currency_trans,4,' ')||
1093 ' '|| LPAD(nvl(to_char(r.jv_quantity,'9999999999D999'),' '),14,' ')||' '||r.jv_quantity_um);
1094 ELSIF ppage_size = 180 THEN
1095 PRINT_LINE ( '| '||r.acct_ttl_code||' '||RPAD(r.acctg_unit_no||' '||r.acct_no,36,' ')||
1096 ' '|| LPAD(nvl(to_char(dr_base,format_base),' '),26,' ')||' '||
1097 LPAD(nvl(to_char(cr_base,format_base),' '),26,' ')||' '||
1098 LPAD(nvl(to_char(dr_trans,format_trans),' '),26,' ')||' '||
1099 LPAD(nvl(to_char(cr_trans,format_trans),' '),26,' ')||' '||RPAD(r.currency_trans,4,' ')||
1100 ' '|| LPAD(nvl(to_char(r.jv_quantity,'9999999999D999'),' '),14,' ')||' '||r.jv_quantity_um);
1101 END IF;
1102
1103 END IF;
1104
1105 /*
1106 PRINT_LINE ( '| '||r.acct_ttl_code||' '||RPAD(r.acctg_unit_no||' '||r.acct_no,36,' ')||
1107 ' '|| LPAD(nvl(to_char(dr_base,format_base),' '),14,' ')||' '||
1108 LPAD(nvl(to_char(cr_base,format_base),' '),14,' ')||' '||
1109 LPAD(nvl(to_char(dr_trans,format_trans),' '),14,' ')||' '||
1110 LPAD(nvl(to_char(cr_trans,format_trans),' '),14,' ')||' '||RPAD(r.currency_trans,4,' ')||
1111 ' '|| LPAD(nvl(to_char(r.jv_quantity,'9999999999D999'),' '),14,' ')||' '||r.jv_quantity_um);
1112 */
1113 /* End of bug# 1316233 */
1114 /* B# 2302747
1115 j := 36;
1116 */
1117 j := 37;
1118 /* Begin Bug#2424449 Piyush K. Mishra
1119 Incorporated B#2413793
1120 WHILE length(r.acctg_unit_no||' '||r.acct_no) > j AND j <= 240 LOOP
1121 */
1122 WHILE length(r.acctg_unit_no||' '||r.acct_no) >= j AND j <= 240 LOOP
1123 --End Bug#2424449
1124
1125 PRINT_LINE ( '| '||substrb(r.acctg_unit_no||' '||r.acct_no, j, 36));
1126 j := j+36;
1127 END LOOP;
1128 j := 1;
1129 WHILE lengthb(r.acctg_unit_desc||' '||r.acct_desc) > j AND j <= 240 LOOP
1130 PRINT_LINE ( '| '||substrb(r.acctg_unit_desc||' '||r.acct_desc, j, 36));
1131 j := j+36;
1132 END LOOP;
1133 END LOOP;
1134
1135 /* Close the te right cursor base upon user specified parameter */
1136 IF report_on = 1 THEN
1137 CLOSE c_gl_subr_led_vw;
1138 ELSE
1139 CLOSE c_gl_subr_tst_vw;
1140 END IF;
1141
1142 /* Print the totals for the very last line */
1143 PRINT_TOTALS (-888,'-888','-888',-888 , rep_mode, ppage_size); -- Bug 2804810
1144 IF ppage_size = 132 THEN PRINT_LINE ( RPAD('|',132,'-')); -- Bug 2804810
1145 ELSIF ppage_size = 180 THEN PRINT_LINE ( RPAD('|',180,'-'));
1146 END IF;
1147 END;
1148
1149 /* procedure to print totals */
1150 PROCEDURE PRINT_TOTALS(
1151 line_id IN VARCHAR2,
1152 doc_no IN VARCHAR2,
1153 sub_event_code IN VARCHAR2,
1154 voucher_id IN NUMBER,
1155 rep_mode IN VARCHAR2,
1156 ppage_size IN NUMBER DEFAULT 132) IS -- Bug 2804810
1157 BEGIN
1158 /* If the line has changed, print line level totals */
1159 IF (last_line_id IS NOT NULL AND (last_line_id <> line_id)) THEN
1160 IF ppage_size = 132 THEN -- Bug 2804810
1161 PRINT_LINE ( RPAD('|',45,' ')||' '||'============== ==============');
1162 PRINT_LINE ( RPAD('|',45,' ')||' '||LPAD(nvl(to_char(line_total_dr,format_base),' '),14,' ')||
1163 ' '|| LPAD(nvl(to_char(line_total_cr,format_base),' '),14,' '));
1164 ELSIF ppage_size = 180 THEN -- Bug 2804810
1165 PRINT_LINE ( RPAD('|',45,' ')||' '||'========================== ==========================');
1166 PRINT_LINE ( RPAD('|',45,' ')||' '||LPAD(nvl(to_char(line_total_dr,format_base),' '),26,' ')||
1167 ' '|| LPAD(nvl(to_char(line_total_cr,format_base),' '),26,' '));
1168 END IF;
1169 END IF;
1170
1171 /* Display other totals in the order report is being run */
1172 FOR i in 1..3 LOOP
1173 IF ( substr(rep_mode,i,1) = 'D' AND (last_orgn_code||last_doc_no IS NOT NULL AND (last_orgn_code||last_doc_no <> doc_no))) THEN
1174 IF ppage_size = 132 THEN -- Bug 2804810
1175 PRINT_LINE ( RPAD('|',45,' ')||' '||'============== ==============');
1176 ELSIF ppage_size = 180 THEN
1177 PRINT_LINE ( RPAD('|',45,' ')||' '||'========================== ==========================');
1178 END IF;
1179
1180 /* Begin bug# 1316233 */
1181 IF (nvl(doc_total_dr,0) < amount_constant AND
1182 nvl(doc_total_cr,0) < amount_constant) THEN
1183
1184 IF ppage_size = 132 THEN -- Bug 2804810
1185 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_DOCUMENT||':',20,' ')||' '||
1186 LPAD(nvl(to_char(doc_total_dr,format_base),' '),14,' ')||' '||
1187 LPAD(nvl(to_char(doc_total_cr,format_base),' '),14,' '));
1188 ELSIF ppage_size = 180 THEN
1189 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_DOCUMENT||':',20,' ')||' '||
1190 LPAD(nvl(to_char(doc_total_dr,format_base),' '),26,' ')||' '||
1191 LPAD(nvl(to_char(doc_total_cr,format_base),' '),26,' '));
1192 END IF;
1193
1194 ELSE
1195 IF ppage_size = 132 THEN -- Bug 2804810
1196 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_DOCUMENT||':',20,' ')||' '||
1197 LPAD(nvl(to_char(doc_total_dr,local_format_base),' '),14,' ')||' '||
1198 LPAD(nvl(to_char(doc_total_cr,local_format_base),' '),14,' '));
1199 ELSIF ppage_size = 180 THEN
1200 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_DOCUMENT||':',20,' ')||' '||
1201 LPAD(nvl(to_char(doc_total_dr,format_base),' '),26,' ')||' '||
1202 LPAD(nvl(to_char(doc_total_cr,format_base),' '),26,' '));
1203 END IF;
1204 END IF;
1205
1206 /*
1207 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_DOCUMENT||':',20,' ')||' '||
1208 LPAD(nvl(to_char(doc_total_dr,format_base),' '),14,' ')||' '||
1209 LPAD(nvl(to_char(doc_total_cr,format_base),' '),14,' '));
1210 */
1211 /* End of bug# 1316233 */
1212
1213 /* If there are lower level breaks, force them to break also
1214 Their totals will be displayed in subsequent iterations of the FOR loop */
1215 IF (i=1 OR (i=2 AND substr(rep_mode,i+1,1)='V')) THEN
1216 last_voucher_id := '-999';
1217 END IF;
1218 IF (i=1 OR (i=2 AND substr(rep_mode,i+1,1)='S')) THEN
1219 last_sevt_code := '-999';
1220 END IF;
1221 END IF;
1222 IF ( substr(rep_mode,i,1) = 'S' AND (last_sevt_code IS NOT NULL AND (last_sevt_code <> sub_event_code))) THEN
1223 IF ppage_size = 132 THEN -- Bug 2804810
1224 PRINT_LINE ( RPAD('|',45,' ')||' '||'============== ==============');
1225 ELSIF ppage_size = 180 THEN
1226 PRINT_LINE ( RPAD('|',45,' ')||' '||'========================== ==========================');
1227 END IF;
1228
1229 /* Begin bug# 1316233 */
1230 IF (nvl(sevt_total_dr,0) < amount_constant AND
1231 nvl(sevt_total_cr,0) < amount_constant) THEN
1232 IF ppage_size = 132 THEN -- Bug 2804810
1233 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_SUB_EVENT||':',20,' ')||' '||
1234 LPAD(nvl(to_char(sevt_total_dr,format_base),' '),14,' ')||' '||
1235 LPAD(nvl(to_char(sevt_total_cr,format_base),' '),14,' '));
1236 ELSIF ppage_size = 180 THEN
1237 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_SUB_EVENT||':',20,' ')||' '||
1238 LPAD(nvl(to_char(sevt_total_dr,format_base),' '),26,' ')||' '||
1239 LPAD(nvl(to_char(sevt_total_cr,format_base),' '),26,' '));
1240 END IF;
1241 ELSE
1242 IF ppage_size = 132 THEN -- Bug 2804810
1243 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_SUB_EVENT||':',20,' ')||' '||
1244 LPAD(nvl(to_char(sevt_total_dr,local_format_base),' '),14,' ')||' '||
1245 LPAD(nvl(to_char(sevt_total_cr,local_format_base),' '),14,' '));
1246 ELSIF ppage_size = 180 THEN
1247 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_SUB_EVENT||':',20,' ')||' '||
1248 LPAD(nvl(to_char(sevt_total_dr,format_base),' '),26,' ')||' '||
1249 LPAD(nvl(to_char(sevt_total_cr,format_base),' '),26,' '));
1250 END IF;
1251 END IF;
1252 /*
1253 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_SUB_EVENT||':',20,' ')||' '||
1254 LPAD(nvl(to_char(sevt_total_dr,format_base),' '),14,' ')||' '||
1255 LPAD(nvl(to_char(sevt_total_cr,format_base),' '),14,' '));
1256 */
1257 /* End of bug# 1316233 */
1258
1259 /* If there are lower level breaks, force them to break also
1260 Their totals will be displayed in subsequent iterations of the FOR loop */
1261 IF (i=1 OR (i=2 AND substr(rep_mode,i+1,1)='V')) THEN
1262 last_voucher_id := '-999';
1263 END IF;
1264 IF (i=1 OR (i=2 AND substr(rep_mode,i+1,1)='D')) THEN
1265 last_doc_no := '-999';
1266 END IF;
1267 END IF;
1268 IF (substr(rep_mode,i,1) = 'V' AND (last_voucher_id IS NOT NULL AND (last_voucher_id <> nvl(voucher_id,0)))) THEN
1269 IF ppage_size = 132 THEN -- Bug 2804810
1270 PRINT_LINE ( RPAD('|',45,' ')||' '||'============== ==============');
1271 ELSIF ppage_size = 180 THEN
1272 PRINT_LINE ( RPAD('|',45,' ')||' '||'========================== ==========================');
1273 END IF;
1274
1275 /* Begin bug# 1316233 */
1276 IF (nvl(voucher_total_dr,0) < amount_constant AND
1277 nvl(voucher_total_cr,0) < amount_constant) THEN
1278 IF ppage_size = 132 THEN -- Bug 2804810
1279 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_VOUCHER||':',20,' ')||' '||
1280 LPAD(nvl(to_char(voucher_total_dr,format_base),' '),14,' ')||' '||
1281 LPAD(nvl(to_char(voucher_total_cr,format_base),' '),14,' '));
1282 ELSIF ppage_size = 180 THEN
1283 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_VOUCHER||':',20,' ')||' '||
1284 LPAD(nvl(to_char(voucher_total_dr,format_base),' '),26,' ')||' '||
1285 LPAD(nvl(to_char(voucher_total_cr,format_base),' '),26,' '));
1286 END IF;
1287 ELSE
1288 IF ppage_size = 132 THEN -- Bug 2804810
1289 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_VOUCHER||':',20,' ')||' '||
1290 LPAD(nvl(to_char(voucher_total_dr,local_format_base),' '),14,' ')||' '||
1291 LPAD(nvl(to_char(voucher_total_cr,local_format_base),' '),14,' '));
1292 ELSIF ppage_size = 180 THEN
1293 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_VOUCHER||':',20,' ')||' '||
1294 LPAD(nvl(to_char(voucher_total_dr,format_base),' '),26,' ')||' '||
1295 LPAD(nvl(to_char(voucher_total_cr,format_base),' '),26,' '));
1296 END IF;
1297 END IF;
1298 /*
1299 PRINT_LINE ( RPAD('|',25,' ')||LPAD(L_GMF_VOUCHER||':',20,' ')||' '||
1300 LPAD(nvl(to_char(voucher_total_dr,format_base),' '),14,' ')||' '||
1301 LPAD(nvl(to_char(voucher_total_cr,format_base),' '),14,' '));
1302 */
1303 /* End of bug# 1316233 */
1304 -- If there are lower level breaks, force them to break also
1305 -- Their totals will be displayed in subsequent iterations of the FOR loop
1306 IF (i=1 OR (i=2 AND substr(rep_mode,i+1,1)='S')) THEN
1307 last_sevt_code := '-999';
1308 END IF;
1309 IF (i=1 OR (i=2 AND substr(rep_mode,i+1,1)='D')) THEN
1310 last_doc_no := '-999';
1311 END IF;
1312 END IF;
1313 END LOOP;
1314 END;
1315
1316 /* Wrapper for printing report line */
1317 PROCEDURE PRINT_LINE
1318 (line_text IN VARCHAR2) IS
1319 BEGIN
1320 FND_FILE.PUT_LINE ( FND_FILE.OUTPUT,line_text);
1321 /* DBMS_OUTPUT.PUT_LINE ( line_text); */
1322 line_no := line_no + 1;
1323 IF (line_no = lines_per_page) THEN
1324 page_no := page_no + 1;
1325 FND_FILE.PUT_LINE ( FND_FILE.OUTPUT, substr(rep_title||' '||LPAD(L_GMF_PAGE_NO, 15,' ')||':'||RPAD(to_char(page_no), 6,' '),1,132)); /* Bug 2048108 */
1326 FND_FILE.PUT_LINE ( FND_FILE.OUTPUT, '|');
1327 line_no := 2;
1328 END IF;
1329 END;
1330
1331 /* Procedure initialize labels and variables. */
1332 PROCEDURE INITIALIZE_LABELS IS
1333 BEGIN
1334 FND_MESSAGE.SET_NAME ('GMF','GMF_SUBLEDGER_REPORT'); L_GMF_SUBLEDGER_REPORT := FND_MESSAGE.GET;
1335 FND_MESSAGE.SET_NAME ('GMF','GMF_SOURCE'); L_GMF_SOURCE := FND_MESSAGE.GET;
1336 FND_MESSAGE.SET_NAME ('GMF','GMF_SUB_EVENT'); L_GMF_SUB_EVENT := FND_MESSAGE.GET;
1337 FND_MESSAGE.SET_NAME ('GMF','GMF_VOUCHER'); L_GMF_VOUCHER := FND_MESSAGE.GET;
1338 FND_MESSAGE.SET_NAME ('GMF','GMF_DOCUMENT'); L_GMF_DOCUMENT := FND_MESSAGE.GET;
1339 FND_MESSAGE.SET_NAME ('GMF','GMF_DOC_NO'); L_GMF_DOC_NO := FND_MESSAGE.GET;
1340 FND_MESSAGE.SET_NAME ('GMF','GMF_DOC_TYPE'); L_GMF_DOC_TYPE := FND_MESSAGE.GET;
1341 FND_MESSAGE.SET_NAME ('GMF','GMF_DOC_DATE'); L_GMF_DOC_DATE := FND_MESSAGE.GET;
1342 FND_MESSAGE.SET_NAME ('GMF','GMF_LINE'); L_GMF_LINE := FND_MESSAGE.GET;
1343 FND_MESSAGE.SET_NAME ('GMF','GMF_ITEM'); L_GMF_ITEM := FND_MESSAGE.GET;
1344 FND_MESSAGE.SET_NAME ('GMF','GMF_TRANS_DATE'); L_GMF_TRANS_DATE := FND_MESSAGE.GET;
1345 FND_MESSAGE.SET_NAME ('GMF','GMF_WHSE'); L_GMF_WHSE := FND_MESSAGE.GET;
1346 FND_MESSAGE.SET_NAME ('GMF','GMF_QUANTITY'); L_GMF_QUANTITY := FND_MESSAGE.GET;
1347 FND_MESSAGE.SET_NAME ('GMF','GMF_UOM'); L_GMF_UOM := FND_MESSAGE.GET;
1348 FND_MESSAGE.SET_NAME ('GMF','GMF_TTL'); L_GMF_TTL := FND_MESSAGE.GET;
1349 FND_MESSAGE.SET_NAME ('GMF','GMF_BASE_CURR'); L_GMF_BASE_CURR := FND_MESSAGE.GET;
1350 FND_MESSAGE.SET_NAME ('GMF','GMF_BILL_CURR'); L_GMF_BILL_CURR := FND_MESSAGE.GET;
1351 FND_MESSAGE.SET_NAME ('GMF','GMF_DEBIT'); L_GMF_DEBIT := FND_MESSAGE.GET;
1352 FND_MESSAGE.SET_NAME ('GMF','GMF_CREDIT'); L_GMF_CREDIT := FND_MESSAGE.GET;
1353 FND_MESSAGE.SET_NAME ('GMF','GMF_JV_QTY'); L_GMF_JV_QTY := FND_MESSAGE.GET;
1354 FND_MESSAGE.SET_NAME ('GMF','GMF_REFERENCE_NO'); L_GMF_REFERENCE_NO := FND_MESSAGE.GET;
1355 FND_MESSAGE.SET_NAME ('GMF','GMF_COMPANY'); L_GMF_COMPANY := FND_MESSAGE.GET;
1356 FND_MESSAGE.SET_NAME ('GMF','GMF_CURRENCY'); L_GMF_CURRENCY := FND_MESSAGE.GET;
1357 FND_MESSAGE.SET_NAME ('GMF','GMF_FISCAL_YEAR'); L_GMF_FISCAL_YEAR := FND_MESSAGE.GET;
1358 FND_MESSAGE.SET_NAME ('GMF','GMF_PERIOD'); L_GMF_PERIOD := FND_MESSAGE.GET;
1359 FND_MESSAGE.SET_NAME ('GMF','GMF_START_DATE'); L_GMF_START_DATE := FND_MESSAGE.GET;
1360 FND_MESSAGE.SET_NAME ('GMF','GMF_END_DATE'); L_GMF_END_DATE := FND_MESSAGE.GET;
1361 FND_MESSAGE.SET_NAME ('GMF','GMF_FROM'); L_GMF_FROM := FND_MESSAGE.GET;
1362 FND_MESSAGE.SET_NAME ('GMF','GMF_TO'); L_GMF_TO := FND_MESSAGE.GET;
1363 FND_MESSAGE.SET_NAME ('GMF','GMF_AU_AND_ACCT'); L_GMF_AU_AND_ACCT := FND_MESSAGE.GET;
1364 FND_MESSAGE.SET_NAME ('GMF','GMF_TEST'); L_GMF_TEST := FND_MESSAGE.GET;
1365 /* Bug 1924250 */
1366 FND_MESSAGE.SET_NAME ('GMF','GMF_VENDOR'); L_GMF_VENDOR := FND_MESSAGE.GET;
1367 FND_MESSAGE.SET_NAME ('GMF','GMF_PO_NO'); L_GMF_PO_NO := FND_MESSAGE.GET;
1368 FND_MESSAGE.SET_NAME ('GMF','GMF_XCHG_RATE'); L_GMF_XCHG_RATE := FND_MESSAGE.GET;
1369 FND_MESSAGE.SET_NAME ('GMF','GMF_RECEIPT'); L_GMF_RECEIPT := FND_MESSAGE.GET;
1370 FND_MESSAGE.SET_NAME ('GMF','GMF_RECEIPT_DATE'); L_GMF_RECEIPT_DATE := FND_MESSAGE.GET;
1371 FND_MESSAGE.SET_NAME ('GMF','GMF_FORMULA'); L_GMF_FORMULA := FND_MESSAGE.GET;
1372 FND_MESSAGE.SET_NAME ('GMF','GMF_VERSION'); L_GMF_VERSION := FND_MESSAGE.GET;
1373 FND_MESSAGE.SET_NAME ('GMF','GMF_ACTUAL_START_DATE'); L_GMF_ACTUAL_START_DATE := FND_MESSAGE.GET;
1374 FND_MESSAGE.SET_NAME ('GMF','GMF_ACTUAL_CMPLT_DATE'); L_GMF_ACTUAL_CMPLT_DATE := FND_MESSAGE.GET;
1375 FND_MESSAGE.SET_NAME ('GMF','GMF_ROUTING'); L_GMF_ROUTING := FND_MESSAGE.GET;
1376 FND_MESSAGE.SET_NAME ('GMF','GMF_CUSTOMER'); L_GMF_CUSTOMER := FND_MESSAGE.GET;
1377 FND_MESSAGE.SET_NAME ('GMF','GMF_SO_NO'); L_GMF_SO_NO := FND_MESSAGE.GET;
1378 FND_MESSAGE.SET_NAME ('GMF','GMF_SO_DATE'); L_GMF_SO_DATE := FND_MESSAGE.GET;
1379 FND_MESSAGE.SET_NAME ('GMF','GMF_WIP_WHSE'); L_GMF_WIP_WHSE := FND_MESSAGE.GET;
1380 /* End of code for Bug 1924250 */
1381 /* Bug 2048108 */
1382 FND_MESSAGE.SET_NAME ('GMF','GMF_UNIT_PRICE'); L_GMF_UNIT_PRICE := FND_MESSAGE.GET;
1383 FND_MESSAGE.SET_NAME ('GMF','GMF_REAS_CODE'); L_GMF_REAS_CODE := FND_MESSAGE.GET;
1384 FND_MESSAGE.SET_NAME ('GMF','GMF_PLAN_QTY'); L_GMF_PLAN_QTY := FND_MESSAGE.GET;
1385 FND_MESSAGE.SET_NAME ('GMF','GMF_ACTL_QTY'); L_GMF_ACTL_QTY := FND_MESSAGE.GET;
1386 FND_MESSAGE.SET_NAME ('GMF','GMF_ORIG_QTY'); L_GMF_ORIG_QTY := FND_MESSAGE.GET; -- Bug# 3772552
1387 FND_MESSAGE.SET_NAME ('GMF','GMF_BATCH_STATUS'); L_GMF_BATCH_STATUS := FND_MESSAGE.GET;
1388 FND_MESSAGE.SET_NAME ('GMF','GMF_PAGE_NO'); L_GMF_PAGE_NO := FND_MESSAGE.GET;
1389 /* End of code for Bug 2048108 */
1390
1391 /* Initialize the variables */
1392 last_voucher_id := NULL;
1393 last_sevt_code := NULL;
1394 last_doc_no := NULL;
1395 last_orgn_code := NULL;
1396 last_line_id := NULL;
1397 line_no := 0;
1398 page_no := 1;
1399
1400 -- Bug 1316233 Initialize local format for numbers
1401 local_format_base := 'FM99999999990D00PT';
1402 local_format_trans := 'FM99999999990D00PT';
1403
1404 dr_base := NULL;
1405 cr_base := NULL;
1406 dr_trans := NULL;
1407 cr_trans := NULL;
1408 format_base := NULL;
1409 format_trans := NULL;
1410 line_total_dr := NULL;
1411 line_total_cr := NULL;
1412 voucher_total_dr := NULL;
1413 voucher_total_cr := NULL;
1414 sevt_total_dr := NULL;
1415 sevt_total_cr := NULL;
1416 doc_total_dr := NULL;
1417 doc_total_cr := NULL;
1418 rep_total_dr := NULL;
1419 rep_total_cr := NULL;
1420
1421 END;
1422
1423 END;