PLSQL IF ELSE in Package procedure

Question

I have a package which has 4 parameters. All the parameters will come from concurrent request. In that based on transaction type parameter the package should execute the set of statements.I just started with oracle. Below is my code

CREATE OR replace PACKAGE BODY
  vat_reg_6
IS
PROCEDURE xx_po_vat_reg_proc_par(errbuf OUT VARCHAR2,
                                 retcode OUT VARCHAR2,
                                 p_startdate       DATE,
                                 p_enddate         DATE,
                                 p_legal_entity_id NUMBER,
                                 p_trantype        VARCHAR2)
IS
  CURSOR po_cursor IS
    --============================My Approach=================
    IF p_trantype='AP'-- Should execute below block
      SELECT     al.tax_rate_code  AS taxcode,
                 al.amount         AS netamount,
                 al.amount         AS taxamount,
                 ai.invoice_date   AS reportingdate,
                 ai.invoice_num    AS invoicenumber,
                 ai.invoice_date   AS invoicedate,
                 ai.invoice_amount AS grossamount,
                 ai.invoice_num    AS documentnumber ,
                 ai.invoice_date   AS documentdate,
                 ai.vendor_id      AS suplierid,
                 hz.tax_reference  AS suppliervatnumber,
                 gl.segment1       AS companycode,
                 'AP'              AS transactiontype
      FROM       apps.ap_invoice_lines_all al
      inner join apps.ap_invoices_all ai
      ON         ai.invoice_id=al.invoice_id
      inner join apps.hz_parties hz
      ON         ai.party_id=hz.party_id
      inner join apps.ap_invoice_distributions_all dl
      ON         dl.invoice_id=al.invoice_id
      inner join apps.gl_code_combinations gl
      ON         gl.code_combination_id=dl.dist_code_combination_id
      WHERE      ROWNUM<200
      AND        ai.invoice_date BETWEEN p_startdate AND        p_enddate
      AND        ai.legal_entity_id=p_legal_entity_id;

ELSE -------------------
  --===========This block=====================
BEGIN
  /*apps.fnd_file.put_line (
apps.fnd_file.Output,
'Program Started
'
);*/
  apps.fnd_file.put_line (apps.fnd_file.output,rpad('TaxCode',8)
  || rpad('NetAMount',15)
  || rpad('TaxAmount',15)
  || rpad('ReportingDate',20)
  || rpad('InvoiceNumber',20)
  || rpad('InvoiceDate',20)
  || rpad('GrossAmount',20)
  || rpad('DocumentNumber',20)
  || rpad('DocumentDate',20)
  || rpad('SuplierID',20)
  || rpad ('SupplierVATNumber',20)
  || rpad('CompanyCode',20)
  || rpad('TransactionType',20));
  apps.fnd_file.put_line (apps.fnd_file.output, '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------' );
  /*FND_FILE.put_line(FND_FILE.output,'Starting processing:');*/
  FOR po_rec IN po_cursor
  LOOP
    apps.fnd_file.put_line (apps.fnd_file.output, rpad(po_rec.taxcode,8)
    || rpad(po_rec.netamount,15)
    || rpad(po_rec.taxamount,15)
    || rpad(po_rec.reportingdate,20)
    || rpad(po_rec.invoicenumber,20)
    || rpad(po_rec.invoicedate,20)
    || rpad(po_rec.grossamount,20)
    || rpad(po_rec.documentnumber,20)
    || rpad(po_rec.documentdate,20)
    || rpad(po_rec.suplierid,20)
    || rpad (po_rec.suppliervatnumber,20)
    || rpad(po_rec.companycode,20)
    || rpad(po_rec.transactiontype,20));
    /*APPS.FND_FILE.put_line(APPS.FND_FILE.output,
po_rec.TaxCode || po_rec.NetAMount ||
po_rec.TaxAmount || po_rec.ReportingDate||po_rec.InvoiceNumber||po_rec.GrossAmount||po_rec.DocumentNumber||po_rec.DocumentDate||po_rec.SuplierID||
po_rec.SupplierVATNumber||po_rec.CompanyCode||po_rec.TransactionType);*/
    /*INSERT INTO APPS_RO.VAT_TEMP VALUES (po_rec.TaxCode,
po_rec.NetAMount,
po_rec.TaxAmount,
po_rec.ReportingDate,
po_rec.InvoiceNumber,
po_rec.InvoiceDate,
po_rec.GrossAmount,
po_rec.DocumentNumber,
po_rec.DocumentDate,
po_rec.SuplierID,
po_rec.SupplierVATNumber,
po_rec.CompanyCode,
po_rec.TransactionType);*/
  END LOOP;
  --FND_FILE.put_line(FND_FILE.output,'Done!');
  COMMIT;
  -- Return 0 for successful completion.
  errbuf :='';
  retcode := '0';
  /*exception
when others then
errbuf := sqlerrm;
retcode := '2';*/
END xx_po_vat_reg_proc_par;
END vat_reg_6;

Will my approach work? Please help me to get this done!

Answer 1

I think you should use dynamic sql statements for this approach. like:

declare 
sql_stmt varchar2(4000);
rec refcursor;
begin 
IF  p_trantype='AP'THEN                                                     -- Should execute below block
 sql_stmt:=  'SELECT al.tax_rate_code AS taxcode,
         al.amount AS netamount,
         al.amount AS taxamount,
         ai.invoice_date AS reportingdate,
         ai.invoice_num AS invoicenumber,
         ai.invoice_date AS invoicedate,
         ai.invoice_amount AS grossamount,
         ai.invoice_num AS documentnumber,
         ai.invoice_date AS documentdate,
         ai.vendor_id AS suplierid,
         hz.tax_reference AS suppliervatnumber,
         gl.segment1 AS companycode,
         'AP' AS transactiontype
       from...';
 else 
sql_stmt:=...
end if;
open rec for sql_stmt;
fetch rec into ...