SQL & PL/SQL

Hi all,

I have to write code review of a pakage which contain procedure to generate invoice in XML.The procedure contain two
files .pkb and .pks.All i know is that it will deliver elemt of the post script layout.?But i dont understand what it mean nor i am able to understand the code.I dont have any prior knowledge about this.Please help me and tell me any tutorial to understand and know how this is done.


Also i have pasted some part of code here .please help me in understanding what is going on.


help help help

/*
 * GET_INVOICE_XML
 */
FUNCTION GET_INVOICE_XML(inInvId NUMBER) RETURN CLOB IS
BEGIN
  IF NOT isClobReady THEN
    dbms_lob.createtemporary(invClob, TRUE);
    isClobReady := TRUE;
  ELSE
    dbms_lob.trim(invClob, 0);
  END IF;

  vInvoice :=
    XML_VER_STR || NL ||
    ' <!DOCTYPE INVOICE_FILE SYSTEM "invoicfile.dtd">' || NL ||
    '<INVOICE_FILE VERSION="1.1">' || NL;

  GEN_PROLOG(InvId);

  GEN_INVOICE(InvId, 1);

  vInvoice := vInvoice ||
    '</INVOICE_FILE>' || NL || NL;

  APPEND_INVOICE(0);

  RETURN invClob;
END GET_INVOICE_XML;

PROCEDURE GEN_INVOICE(InvId NUMBER, inITBMode NUMBER) IS
vP1    PLS_INTEGER;
vP2    PLS_INTEGER;
vAtt     BOOLEAN;
inv      ABCINVOICE%ROWTYPE;
cdr_no   NUMBER;
t0       TIMESTAMP;
t1       TIMESTAMP;
execTime NUMBER(12,6);
BEGIN
  t0 := LOCALTIMESTAMP;

  SELECT * INTO inv FROM ABCINVOICE WHERE OACT = InvId;

  dti := inv.DTI;

  SELECT NVL(SUM(cdrNO),0) INTO cdr_no FROM ABCCONTRACTS WHERE OACT = InvId AND ITB IN ('Y', 'F');

  vInvoice := vInvoice ||
    ' <INVOICE>' || NL ||
    '  <HEADER>' || NL ||
    '   <SEQ>' || inv.SEQ || '/' || inv.PROC_ID || '</SEQ>' || NL ||
    '   <LEN ';

  vPos1 := INSTR(inv.LEN, '|', 1, 1);
  vInvoice := vInvoice || 'INV="' || SUBSTR(inv.LEN, 1, vPos1-1) || '" ';

  vPos2 := INSTR(inv.LEN, '|', vPos1+1, 1);
  vInvoice := vInvoice || 'BAL="' || SUBSTR(inv.LEN, vPos1+1, vPos2-vPos1-1) || '" ';

  vPos1 := INSTR(inv.LEN, '|', vPos2+1, 1);
  vInvoice := vInvoice || 'DET="' || SUBSTR(inv.LEN, vPos2+1, vPos1-vPos2-1) || '" ';

  vPos2 := INSTR(inv.LEN, '|', vPos1+1, 1);
  vInvoice := vInvoice || 'PDET="' || SUBSTR(inv.LEN, vPos1+1, vPos2-vPos1-1) || '" ';

  vPos1 := INSTR(inv.LEN, '|', vPos2+1, 1);
  vInvoice := vInvoice || 'ITM="' || SUBSTR(inv.LEN, vPos2+1, vPos1-vPos2-1) || '" ';

  vPos2 := INSTR(inv.LEN, '|', vPos1+1, 1);

  vInvoice := vInvoice || 'PITM="' || SUBSTR(inv.LEN, vPos1+1, vPos2-vPos1-1) || '" NTF="' ||
    SUBSTR(inv.LEN, vPos2+1) || '"/>' || NL;

  vInvoice := vInvoice ||
    '   <BAR>' || inv.BAR || '</BAR>' || NL ||
    '   <BCD>' || inv.BCD || '</BCD>' || NL ||
    CASE WHEN inv.BIC IS NULL THEN '' ELSE '   <BIC>' || inv.BIC || '</BIC>' || NL END ||
    '   <REF>' || inv.REF || '</REF>' || NL ||
    '   <OHX>' || inv.OHXACT || '</OHX>' || NL ||
    '   <CID>' || inv.CID || '</CID>' || NL ||
    '   <CCO>' || inv.CCO || '</CCO>' || NL ||
    '   <CND>' || inv.CND || '</CND>' || NL ||
    '   <PBF>' || inv.PBF || '</PBF>' || NL ||
    '   <DTI>' || inv.DTI || '</DTI>' || NL ||
    '   <DTD>' || inv.DTD || '</DTD>' || NL ||
    '   <DPS>' || inv.DPS || '</DPS>' || NL ||
    '   <DPE>' || inv.DPE || '</DPE>' || NL ||
    '   <DAS>' || inv.DAS || '</DAS>' || NL ||
    '   <DAE>' || inv.DAE || '</DAE>' || NL ||
    '   <PAY>' || inv.PAY || '</PAY>' || NL ||
    '   <NAM>' || ABCUTIL.CONVERT_POL_CHAR(inv.NAM) || '</NAM>' || NL ||
    CASE WHEN inv.ADS1 IS NULL THEN '' ELSE '   <ADS>' || ABCUTIL.CONVERT_POL_CHAR(inv.ADS1) || '</ADS>' || NL END ||
    CASE WHEN inv.ADS2 IS NULL THEN '' ELSE '   <ADS>' || ABCUTIL.CONVERT_POL_CHAR(inv.ADS2) || '</ADS>' || NL END ||
    '   <ADZ>' || ABCUTIL.CONVERT_POL_CHAR(inv.ADZ) || '</ADZ>' || NL ||
    '   <NIP>' || inv.NIP || '</NIP>' || NL ||
    '   <REG>' || inv.REG || '</REG>' || NL ||
    '   <PES>' || inv.PES || '</PES>' || NL ||
    '   <BCY>' || inv.BCY || '</BCY>' || NL ||
    '   <TXT>' || ABCUTIL.CONVERT_POL_CHAR(inv.TXT) || '</TXT>' || NL ||
    '   <GRO>' || TO_CHAR(inv.GRO, 'FM999990.90') || '</GRO>' || NL ||
    '   <FBL>' || inv.FBL || '</FBL>' || NL;



  IF inv.STP > 0 THEN
    GEN_TEXT(inInvId, 1);
  END IF;



  vInvoice := vInvoice ||
    '  </HEADER>' || NL ||
    '  <ADDRESS>' || NL ||
    '   <ENV>' || inv.ENV || '</ENV>' || NL ||
    '   <TNA>' || ABCUTIL.CONVERT_POL_CHAR(inv.TNA) || '</TNA>' || NL ||
    CASE WHEN inv.TAS1 IS NULL THEN '' ELSE '   <TAS>' || ABCUTIL.CONVERT_POL_CHAR(inv.TAS1) || '</TAS>' || NL END ||
    CASE WHEN inv.TAS2 IS NULL THEN '' ELSE '   <TAS>' || ABCUTIL.CONVERT_POL_CHAR(inv.TAS2) || '</TAS>' || NL END ||
    '   <TAZ>' ||ABCUTIL.CONVERT_POL_CHAR(inv.TAZ) || '</TAZ>' || NL;

  GEN_TEXT(inInvId, 2);

  vInvoice := vInvoice ||
    CASE WHEN inv.MTX  IS NULL THEN '' ELSE '   <MTX>' || inv.MTX  || '</MTX>' || NL END ||
    '  </ADDRESS>' || NL;

  APPEND_INVOICE(0);

  IF inv.BCY <> '99' THEN

    GEN_INVOICE_BILLING(inInvId);


    GEN_BALANCE(inInvId);

    vPos1 := INSTR(inv.LEN, '|', 1, 2) + 1;
    vPos2 := INSTR(inv.LEN, '|', vPos1, 1);
    vPos1 := SUBSTR(inv.LEN, vPos1, vPos2 - vPos1);

    IF vPos1 > 1 THEN
      GEN_SUMMARY(inInvId);
    END IF;

  END IF;


  vInvoice := vInvoice || ' </INVOICE>' || NL;

  APPEND_INVOICE(0);

END GEN_INVOICE;

Edited by: BluShadow on 28-Aug-2012 09:46
added {noformat}

{noformat} tags for readability. Please read: {message:id=9360002}