jeudi 24 septembre 2015

PLSQL IF ELSE in Package procedure

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.!!!

Thanks in advance

Aucun commentaire:

Enregistrer un commentaire