TXT

moban

By Lorraine Boyd,2014-05-16 22:43
12 views 0
moban

    ï??CREATE OR REPLACE PACKAGE BODY giti_itemweight_reports_pkg IS

     g_package_name VARCHAR2(100) := 'giti_itemweight_reports_pkg';

     /*==========================================================================+

     | Copyright (c) Giti IT All Rights Reserved. |

     +===========================================================================+

     | Package : GITI_TMS_ITEMWEIGHT_REPORTS_PKG |

     | Author : YEHONG |

     | Company : Giti |

     | Created : 2010-10-15 ä?‹å?ˆ 01:19:49 |

     | Purpose : TMSèƒŢé‡?æŠ?è??ç?‹åº?包 |

     | |

     | History : |

     | 2010-10-15 YEHONG created new package |

     | |

     +==========================================================================*/

     /* =================================================

     * Purpose: è?Ŗ出åˆ?dbms output﵌窕ç ?æ??è?Œ255å?ŕ节的é??åˆ?

     *

     * HISTORY:

     * 2009-9-27 12:53:23 ye.hong@giti.com Create

     *

     * ================================================*/

     PROCEDURE to_dbms_output(p_msg IN LONG) IS

     l_buff LONG := p_msg;

     l_line_buff VARCHAR2(255);

     l_exit_flag VARCHAR2(1) := 0;

     BEGIN

     dbms_output.enable(200000);

     LOOP

     IF lengthb(l_buff) > 255

     THEN

     IF instrb(l_buff

     ,chr(10)) > 0

     THEN

     l_line_buff := substrb(l_buff

     ,1

     ,instrb(l_buff

     ,chr(10)) - 1);

     l_buff := substrb(l_buff

     ,instrb(l_buff

     ,chr(10)) + 1);

     ELSE

     l_line_buff := substrb(l_buff

     ,1

     ,255);

     l_buff := substrb(l_buff

     ,256);

     END IF;

     ELSE

     l_line_buff := l_buff;

     l_exit_flag := 1;

     END IF;

     dbms_output.put(a => l_line_buff);

     dbms_output.new_line;

     IF l_exit_flag = 1

     THEN

     EXIT;

     END IF;

     END LOOP;

     END;

     /* =================================================

     * Purpose: è?Ŗ出åˆ?LOG

     * åœ?è?ƒåµ?æ??åµ?ä?‹è?Ŗ出åˆ?dbms_outputïµ›

     * å??ä??çµ?è?›ïµŒé??è??æ??有

     * HISTORY:

     * 2009-9-27 12:53:23 ye.hong@giti.com Create

     *

     * ================================================*/

     PROCEDURE to_log(p_msg IN LONG

     ,p_pre_num IN NUMBER DEFAULT 0

     ,p_pre_char IN VARCHAR2 DEFAULT ' ') IS

     l_msg VARCHAR2(2000);

     l_pre_char VARCHAR2(1);

     BEGIN

     --å?„ç?†å‰?å?µå?ŕç?ŤïµŒè??åˆ?çµ?è?›ç›?çš„

     IF p_pre_num > 0

     THEN

     IF p_pre_char IS NOT NULL

     THEN

     l_pre_char := substr(p_pre_char

     ,1

     ,1);

     ELSE

     l_pre_char := ' ';

     END IF;

     l_msg := ' ';

     l_msg := rpad(l_msg

     ,p_pre_num + 1

     ,l_pre_char);

     l_msg := substr(l_msg

     ,1

     ,p_pre_num) || p_msg;

     ELSE

     l_msg := p_msg;

     END IF;

     IF apps.fnd_global.conc_request_id > 0

     THEN

     apps.fnd_file.put_line(which => apps.fnd_file.log

     ,buff => l_msg);

     ELSE

     to_dbms_output(l_msg);

     END IF;

     END;

     /* =================================================

     * Purpose: è?Ŗ出åˆ?Out Put

     * åœ?è?ƒåµ?æ??åµ?ä?‹è?Ŗ出åˆ?dbms_outputïµ›

     * å??ä??çµ?è?›ïµŒé??è??æ??有

     * HISTORY:

     * 2009-9-27 12:53:23 ye.hong@giti.com Create

     *

     * ================================================*/

     PROCEDURE to_output(p_msg IN LONG

     ,p_pre_num IN NUMBER DEFAULT 0

     ,p_pre_char IN VARCHAR2 DEFAULT ' ') IS

     l_msg VARCHAR2(2000);

     l_pre_char VARCHAR2(1);

     BEGIN

     --å?„ç?†å‰?å?µå?ŕç?ŤïµŒè??åˆ?çµ?è?›ç›?çš„

     IF p_pre_num > 0

     THEN

     IF p_pre_char IS NOT NULL

     THEN

     l_pre_char := substr(p_pre_char

     ,1

     ,1);

     ELSE

     l_pre_char := ' ';

     END IF;

     l_msg := ' ';

     l_msg := rpad(l_msg

     ,p_pre_num + 1

     ,l_pre_char);

     l_msg := substr(l_msg

     ,1

     ,p_pre_num) || p_msg;

     ELSE

     l_msg := p_msg;

     END IF;

     IF apps.fnd_global.conc_request_id > 0

     THEN

     apps.fnd_file.put_line(which => apps.fnd_file.output

     ,buff => l_msg);

     ELSE

     to_dbms_output(l_msg);

     END IF;

     END;

     /* ===============================================

     * Purpose: æ??ä??åŒŔprint_line

     *

     * HISTORY:

     * 2009-9-27 12:53:23 ye.hong@giti.com Create

     *

     * ==============================================*/

     PROCEDURE print_line(g_srs_flag IN VARCHAR2

     ,p_line_type IN VARCHAR2

     ,p_line_content IN VARCHAR2) IS

     BEGIN

     IF g_srs_flag = 'N'

     THEN

     htp.p(p_line_content);

     ELSE

     IF p_line_type = 'OUTFILE'

     THEN

     fnd_file.put_line(fnd_file.output

     ,p_line_content);

     ELSIF p_line_type = 'LOGFILE'

     THEN

     fnd_file.put_line(fnd_file.log

     ,p_line_content);

     END IF;

     to_dbms_output(p_line_content);

     END IF;

     END print_line;

     FUNCTION is_numeric(p_data IN VARCHAR2) RETURN VARCHAR2 IS

     BEGIN

     IF to_number(p_data) > 0

     THEN

     RETURN 'Y';

     END IF;

     RETURN 'N';

     EXCEPTION

     WHEN OTHERS THEN

     RETURN 'N';

     END is_numeric;

     -- ç›?å?•å†ŒèƒŢé‡?å?ţæŕ?æŠ?è??

     PROCEDURE itemweight_report(errbuf OUT VARCHAR2

     ,retcode OUT VARCHAR2

     ,p_item_from VARCHAR2

     ,p_item_to VARCHAR2) IS

     g_procedure_name VARCHAR2(100) := 'itemweight_report';

     l_srs_flag VARCHAR2(1) := 'Y';

     BEGIN

--------------------------------------------------------------------------------

     -- Build the HTML

--------------------------------------------------------------------------------

     print_line(l_srs_flag, 'OUTFILE', '<html>');

     print_line(l_srs_flag, 'OUTFILE', '<head>');

     print_line(l_srs_flag, 'OUTFILE',

    '<title>ç›?å?•å†ŒèƒŢé‡?å?ţæŕ?æŠ?è??</title>');

     print_line(l_srs_flag, 'OUTFILE', '<!--

    ========================================================= -->');

     print_line(l_srs_flag, 'OUTFILE', '<!-- Copyright (c) 2006 GITI Tires

    Company -->');

     print_line(l_srs_flag, 'OUTFILE', '<!-- All rights reserved

    -->');

     print_line(l_srs_flag, 'OUTFILE', '<!--

    ========================================================= -->');

     print_line(l_srs_flag, 'OUTFILE', '<meta http-equiv="Content-Type"

content="text/html; charset=GBK">');

     print_line(l_srs_flag, 'OUTFILE', '<style type="text/css">');

     print_line(l_srs_flag, 'OUTFILE', ' div.breakafter { page-break-after:always; }');

     print_line(l_srs_flag, 'OUTFILE', ' .tableheader { background: #cccc99; color:

    #336699;}');

     print_line(l_srs_flag, 'OUTFILE', ' div.breakbefore { page-break-before:always; }');

     print_line(l_srs_flag, 'OUTFILE', ' body {font-family: Arial, Helvetica, Geneva,

    sans-serif; font-size: 9pt; color: black}');

     print_line(l_srs_flag, 'OUTFILE', ' table {font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; color: black}');

     print_line(l_srs_flag, 'OUTFILE', '</style>');

     print_line(l_srs_flag, 'OUTFILE', '<script type="text/javascript">');

     print_line(l_srs_flag, 'OUTFILE', ' function TrBgOn(TrBgid){ ');

     print_line(l_srs_flag, 'OUTFILE', '

     TrBgid.style.backgroundColor="#ADD8E6";');

     print_line(l_srs_flag, 'OUTFILE', ' TrBgid.style.cursor="hand";');

     print_line(l_srs_flag, 'OUTFILE', ' }');

     print_line(l_srs_flag, 'OUTFILE', ' function TrBgOff(TrBgid){ ');

     print_line(l_srs_flag, 'OUTFILE', '

     TrBgid.style.backgroundColor="#ffffff";');

     print_line(l_srs_flag, 'OUTFILE', ' }');

     print_line(l_srs_flag, 'OUTFILE', '</script>');

     print_line(l_srs_flag, 'OUTFILE', '</head>');

     print_line(l_srs_flag, 'OUTFILE', '<body>');

     print_line(l_srs_flag

     ,'OUTFILE', '<h4>ç›?å?•å†ŒèƒŢé‡?å?ţæŕ?æŠ?è??</h4>');

     -- åµ?å?‹è??æ ?

     print_line(l_srs_flag, 'OUTFILE', '<BR>');

     print_line(l_srs_flag, 'OUTFILE', '<table border=1 cellspacing=0 align = left>');

     -- åµ?å?‹æ ‡é??

     print_line(l_srs_flag, 'OUTFILE', '<th width="120pt" class="tableheader"><b>ç‰?æŔ?çµŔç •</b></th>');

     print_line(l_srs_flag, 'OUTFILE', '<th width="120pt" class="tableheader"><b>ç‰?æŔ?æ??è??</b></th>');

     print_line(l_srs_flag, 'OUTFILE', '<th width="120pt" class="tableheader"><b>ç‰?æŔ?çŠ?æ?•</b></th>');

     print_line(l_srs_flag, 'OUTFILE', '<th width="120pt" class="tableheader"><b>èƒŢé‡?</b></th>');

     -- æ??æ ‡å?ªçŢ?å?Ŕ出æ•?æ??

     FOR c_rec IN (SELECT msi.segment1 AS ç‰?æŔ?çµŔç •

     ,msi.description AS ç‰?æŔ?æ??è??

     ,msi.inventory_item_status_code AS ç‰?æŔ?çŠ?æ?

     ,mdev.element_value AS èƒŢé‡?

     FROM inv.mtl_system_items_b msi

     ,inv.mtl_descr_element_values mdev

     WHERE msi.organization_id = 83 -- ä??é?šç‰?æŔ?ç??æŠ?ä??ç?„ç?‡

     AND msi.inventory_item_status_code <> 'Inactive' -- 未å??效

     AND msi.inventory_item_id = mdev.inventory_item_id

     AND mdev.element_name = 'é‡?é‡?﵈Kg﵉'

     AND msi.segment1 BETWEEN p_item_from AND p_item_to

     AND nvl(mdev.element_value

     ,0) <> 0

     AND is_numeric(mdev.element_value) = 'Y')

     LOOP

     -- è?Ŗ出æ•?æ??

     print_line(l_srs_flag, 'OUTFILE', '<tr bgcolor="#FFFFFF" onMouseOver="TrBgOn(this);" onMouseOut="TrBgOff(this);">');

     print_line(l_srs_flag, 'OUTFILE', '<td>'||c_rec.ç‰?æŔ?çµŔç •||'</td>');

     print_line(l_srs_flag, 'OUTFILE', '<td>'||c_rec.ç‰?æŔ?æ??è??||'</td>');

     print_line(l_srs_flag, 'OUTFILE', '<td>'||c_rec.ç‰?æŔ?çŠ?æ?•||'</td>');

     print_line(l_srs_flag, 'OUTFILE', '<td

    align=right>'||c_rec.èƒŢé‡?||'</td>');

     print_line(l_srs_flag, 'OUTFILE','</tr>');

     END LOOP;

     print_line(l_srs_flag, 'OUTFILE', '</table>');

     print_line(l_srs_flag, 'OUTFILE', '</body>');

     print_line(l_srs_flag, 'OUTFILE', '</html>');

     -- 嵂å??å?„ç?†

     EXCEPTION

     WHEN OTHERS THEN

     errbuf := g_package_name || g_procedure_name || 'error.';

     retcode := SQLERRM;

     print_line(l_srs_flag, 'OUTFILE', '<b>嵂å??éŗ?è??å?ŘçŗŸ</b>');

     print_line(l_srs_flag, 'OUTFILE', '<br>');

     print_line(l_srs_flag, 'OUTFILE', '<b>'||sqlerrm||'</b>');

     print_line(l_srs_flag, 'OUTFILE', '<br>');

     print_line(l_srs_flag, 'OUTFILE', '</body>');

     print_line(l_srs_flag, 'OUTFILE', '</html>');

     END itemweight_report;

END giti_itemweight_reports_pkg;

Report this document

For any questions or suggestions please email
cust-service@docsford.com