PO Report Quries

1.CANCELLED PO’S

SELECT PHA.SEGMENT1,

         PHA.CREATION_DATE,

         PLA.CANCEL_DATE,

         PLA.CANCEL_REASON,

         PLA.ITEM_DESCRIPTION,

         HRL.LOCATION_CODE,

         FU.USER_NAME,

         DECODE (PHA.CANCEL_FLAG,'Y',’CANCELLED’) STATUS

FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA, HR_LOCATIONS HRL, FND_USER FU

         WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

         AND HRL.LOCATION_ID = PHA.SHIP_TO_LOCATION_ID

         AND PHA.CREATED_BY = FU.USER_ID

         AND PHA.CANCEL_FLAG ='Y'

2.PENDING PO’S

SELECT   PHA.SEGMENT1 PO_NUM,

             PHA.CREATION_DATE PO_DATE,

             PLA.ITEM_DESCRIPTION,

             HL.LOCATION_CODE,

             PLA.CANCEL_DATE,

             PLA.CANCEL_REASON,

             FU.USER_NAME USERNAME,

             PHA.VENDOR_SITE_ID,

             DECODE (PHA.CANCEL_FLAG,'Y','CANCELLED','PENDING') STATUS

FROM     PO_HEADERS_ALL PHA,

             PO_LINES_ALL PLA,

             HR_LOCATIONS HL,

             FND_USER FU           

WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID

AND         PHA.SHIP_TO_LOCATION_ID=HL.LOCATION_ID

AND         PHA.CANCEL_FLAG! ='Y'

AND         PHA.CREATED_BY=FU.USER_ID

3.PO SUMMARY REPORT

SELECT PHA.SEGMENT1 PONO,

         PHA.CREATION_DATE PODATE,

         PLA.QUANTITY,

         PLA.UNIT_PRICE,

         PLA.ITEM_DESCRIPTION,

         MSI.SEGMENT1 ITEM,

         PV.VENDOR_NAME,

         PAV.AGENT_NAME

FROM PO_HEADERS_ALL PHA,

     PO_LINES_ALL PLA,

       MTL_SYSTEM_ITEMS_B MSI,

       PO_VENDORS PV,

       PO_AGENTS_V PAV

WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID

AND PLA.ORG_ID = MSI.ORGANIZATION_ID

AND PHA.VENDOR_ID = PV.VENDOR_ID

AND PAV.AGENT_ID = PHA.AGENT_ID

--AND PV.VENDOR_NAME = :SUPPLIER

--AND PAV.AGENT_NAME = :BUYER

4. SUBLEDGER DATA

SELECT        MTT.TRANSACTION_TYPE_NAME TRNX_TYPE,

              OOD.ORGANIZATION_NAME ORG,

              MSIB.SEGMENT1 ITEM,

              MMT.CURRENCY_CODE  BASE_CURRENCY,

              MMT.SUBINVENTORY_CODE SUB_INV,

              MMT.TRANSACTION_ID TRNX_ID,

              MMT.TRANSACTION_QUANTITY TRNX_QTY,

              TRUNC(MMT.TRANSACTION_DATE) TRNX_DATE,

              MTLN.LOT_NUMBER LOT_NO,

              GCCK.CONCATENATED_SEGMENTS ACCNTS,

              GJL.ACCOUNTED_DR BASE_DEBIT,

              GJL.ACCOUNTED_CR BASE_CREDIT,

              GJL.ENTERED_DR BILLING_DEBIT,

              GJL.ENTERED_CR BILLING_CREDIT,

          GJH.JE_SOURCE SOURCE

FROM      MTL_MATERIAL_TRANSACTIONS MMT, 

              MTL_TRANSACTION_TYPES MTT, 

              ORG_ORGANIZATION_DEFINITIONS OOD, 

              MTL_SYSTEM_ITEMS_B MSIB, 

              MTL_TRANSACTION_LOT_NUMBERS MTLN,

              HR_LEGAL_ENTITIES HLE,

              GL_CODE_COMBINATIONS_KFV GCCK,

              GL_JE_HEADERS GJH, 

              GL_JE_LINES GJL,

              HR_OPERATING_UNITS HOU,

              GL_PERIODS GP,

              GL_PERIOD_TYPES GPT,

              GL_SETS_OF_BOOKS GSOB

WHERE         MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID 

AND           MMT.ORGANIZATION_ID=OOD.ORGANIZATION_ID 

AND           MMT.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID 

AND           MMT.ORGANIZATION_ID=MSIB.ORGANIZATION_ID 

AND           MMT.TRANSACTION_ID=MTLN.TRANSACTION_ID 

--AND         MMT.TRANSACTION_DATE BETWEEN :FDATE AND :TDATE

AND     OOD.LEGAL_ENTITY=HLE.ORGANIZATION_ID

--AND         HLE.NAME=:P_LEGAL_ENTITY

AND           HOU.LEGAL_ENTITY_ID=HLE.ORGANIZATION_ID

AND           GJH.JE_HEADER_ID=GJL.JE_HEADER_ID

AND           GJL.CODE_COMBINATION_ID=GCCK.CODE_COMBINATION_ID

AND           GJH.JE_SOURCE='Inventory'

AND     GSOB.SET_OF_BOOKS_ID=HLE.SET_OF_BOOKS_ID

AND     GSOB.PERIOD_SET_NAME=GP.PERIOD_SET_NAME

--AND         GP.PERIOD_YEAR=:FISCAL_YEAR

--AND         GP.PERIOD_NUM=:PERIOD

5.PO-AP REPORT

SELECT PHA.SEGMENT1 PONO,

         PHA.CREATION_DATE PODATE,

         PHA.TYPE_LOOKUP_CODE POTYPE,

         PLA.LINE_NUM,

         PLA.ITEM_DESCRIPTION,

         PLA.UNIT_PRICE,

         PLA.QUANTITY,

         PV.VENDOR_NAME,

         PVS.VENDOR_SITE_CODE,

         PVC.LAST_NAME,

         AIA.INVOICE_NUM,

         AIA.INVOICE_DATE,

         AIA.INVOICE_ID

FROM PO_HEADERS_ALL PHA,

       PO_LINES_ALL PLA,

       PO_VENDORS PV,

       PO_VENDOR_SITES_ALL PVS,

       PO_VENDOR_CONTACTS PVC,

       AP_INVOICES_ALL AIA,

       AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

       PO_DISTRIBUTIONS_ALL POD

WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PV.VENDOR_ID = PHA.VENDOR_ID

AND PHA.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID

AND PVS.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID

AND AIDA.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID

AND AIDA.INVOICE_ID = AIA.INVOICE_ID

AND PLA.PO_LINE_ID = POD.PO_LINE_ID

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

6. MOVE ORDER REPORT

SELECT   MTRH.REQUEST_NUMBER,

             MTRH.HEADER_STATUS TASK_STATUS,

             MTRL.QUANTITY MOQTY,

             MSIB.SEGMENT1 ITEM_NUM,

             MSIB.DESCRIPTION ITEM_DESC,

             MMT.PRIMARY_QUANTITY,

             MMT.SUBINVENTORY_CODE SOURCE_SUB_INV,

             MMT.TRANSFER_SUBINVENTORY DEST_SUB_INV,

             MMT.TRANSACTION_QUANTITY

FROM  MTL_TXN_REQUEST_HEADERS MTRH,

             MTL_TXN_REQUEST_LINES MTRL,

             MTL_SYSTEM_ITEMS_B MSIB,

             MTL_MATERIAL_TRANSACTIONS MMT

WHERE MMT.ORGANIZATION_ID='204'

AND         MTRH.HEADER_ID=MTRL.HEADER_ID

AND         MTRH.ORGANIZATION_ID=MTRL.ORGANIZATION_ID

AND         MTRL.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID

AND         MTRL.ORGANIZATION_ID=MSIB.ORGANIZATION_ID

AND         MTRH.ORGANIZATION_ID=MMT.ORGANIZATION_ID

GL interface from legacy to GL

Create Or Replace Package Xxfa_Gl_Camra_Pkg Is
  Procedure Xxfa_Gl_Camra_Proc(Errbuf Out Varchar2, Retcode Out Number);
End Xxfa_Gl_Camra_Pkg;

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

Create Or Replace Package Body Xxfa_Gl_Camra_Pkg Is
  l_n_User_Id    Number := Fnd_Global.User_Id;
  l_n_Request_Id Number := Fnd_Global.Conc_Request_Id;
  l_c_Gl_App_Short_Name      Constant Varchar2(10) := 'SQLGL';
  l_c_Gl_Flex_Code           Constant Varchar2(10) := 'GL#';
  l_c_Gl_Flex_Structure_Code Constant Varchar2(30) := 'ACCOUNTING_FLEXFIELD';
  l_Err_Msg Varchar2(4000);

  ------ Procedure -----------
  Procedure Xxfa_Gl_Camra_Proc(Errbuf Out Varchar2, Retcode Out Number) Is
    v_Chart_Of_Accounts_Id Number;
    v_Stg_Status           Varchar2(100);
    --v_gl_segment_value           FND_FLEX_EXT.segmentarray;
    v_x_Oracle_String       Varchar2(4000);
    v_Segment_Con_Seg       Varchar2(4000);
    v_n_Code_Combination_Id Varchar2(4000);
    v_Status_Flag           Varchar2(3);
    v_Rows                  Number(10) := 0;
    v_Insert_Rows           Number(10) := 0;
    v_Error_Rows            Number(10) := 0;
    v_Enter_Cr              Number(10) := 0;
    v_Enter_Dr              Number(10) := 0;
    v_Error_Enter_Dr        Number(10) := 0;
    v_Error_Enter_Cr        Number(10) := 0;
    v_Total_Dr              Number(15) := 0;
    v_Total_Cr              Number(15) := 0;
 
    Cursor C1 Is
      Select Xgcst.Rowid Row_Id, Rownum, Xgcst.*
        From Xxfa_Gl_Camra_Stg_Tab Xgcst
       Where 1 = 1
         And Stg_Status In ('New', 'Error');
 
    Type Gl_Data_Tbl Is Table Of Xxfa_Gl_Camra_Stg_Tab_v%Rowtype Index By Binary_Integer;
 
    Rec_Gl_Balances Gl_Data_Tbl;
    Pragma Autonomous_Transaction;
  Begin
    -- Procedure begin
    Select Gsob.Chart_Of_Accounts_Id -- To  Get the Chart of Accounts id
      Into v_Chart_Of_Accounts_Id
      From Gl_Sets_Of_Books Gsob
     Where Gsob.Set_Of_Books_Id = Fnd_Profile.Value('GL_SET_OF_BKS_ID');
 
    Open C1; --Open The Cursor c1
 
    Loop
      Fetch C1 Bulk Collect
        Into Rec_Gl_Balances;
      --Move the data from cursor c1to pl/sql table type variable.
   
      Exit When C1%Notfound;
    End Loop;
 
    Close C1; -- Close the Cursor c1
 
    For i In 1 .. Rec_Gl_Balances.Count Loop
      v_Rows            := v_Rows + 1;
      v_Segment_Con_Seg := Rec_Gl_Balances(i)
                           .Segment1 || '.' || -- Concatinated  the all segments
                           Rec_Gl_Balances(i).Segment2 || '.' || Rec_Gl_Balances(i)
                           .Segment3 || '.' || Rec_Gl_Balances(i).Segment4 || '.' ||
                            '00000' || '.' || '0' || '.' || '0000' || '.' || '00' || '.' || '00';
      -- using api FND_FLEX_EXT.GET_CCID     to check the valid combination id .
      v_n_Code_Combination_Id := Fnd_Flex_Ext.Get_Ccid(l_c_Gl_App_Short_Name,
                                                       'GL#',
                                                       v_Chart_Of_Accounts_Id,
                                                       To_Char(Sysdate,
                                                               'YYYY/MM/DD HH24:MI:SS'),
                                                       v_Segment_Con_Seg);
   
      --Fnd_File.Put_Line(fnd_File.LOG, 'CCID ' || v_n_Code_Combination_Id);
      If v_n_Code_Combination_Id = 0 Then
        l_Err_Msg     := Fnd_Message.Get;
        v_Stg_Status  := 'Error';
        v_Status_Flag := 'E';
        -- Set the Status flag E if it is invalid combination id .
        Fnd_File.Put_Line(Fnd_File.Log,
                          'Message:' || l_Err_Msg || '---' ||
                          'Combinationid:' || v_Segment_Con_Seg);
      Else
        -- Set the Status flag P if it is valid combination id .
        --DBMS_OUTPUT.PUT_LINE( 'finalCCID not 0 ' || v_n_Code_Combination_Id);
        v_Stg_Status  := 'Process Completed';
        v_Status_Flag := 'P';
      End If;
   
      --fnd_file.put_line(fnd_file.log,v_x_Oracle_String);
      --fnd_file.put_line(fnd_file.log,fnd_message.get);
      If v_Status_Flag = 'E' Then
        -- IF  Record is errout then update the stage table with error status.
        v_Error_Rows := v_Error_Rows + 1;
     
        Update Xxfa_Gl_Camra_Stg_Tab Xgcst
           Set Stg_Status        = v_Stg_Status,
               Err_Description   = l_Err_Msg,
               Request_Id        = l_n_Request_Id,
               Last_Updated_By   = l_n_User_Id,
               Last_Updated_Date = Sysdate
         Where Xgcst.Rowid = Rec_Gl_Balances(i).Row_Id;
     
        v_Error_Enter_Dr := v_Error_Enter_Dr + Rec_Gl_Balances(i)
                           .Entered_Dr;
        v_Error_Enter_Cr := v_Error_Enter_Cr + Rec_Gl_Balances(i)
                           .Entered_Cr;
      Else
        -- IF record is not errorout then Inserting data into interface table
        Insert Into Gl_Interface
          (Status,
           Accounting_Date,
           Segment1,
           Segment2,
           Segment3,
           Segment4,
           Segment5,
           Segment6,
           Segment7,
           Segment8,
           Segment9,
           Entered_Dr,
           Entered_Cr,
           Set_Of_Books_Id,
           Currency_Code,
           Date_Created,
           Created_By,
           Actual_Flag,
           User_Je_Category_Name,
           User_Je_Source_Name,
           Accounted_Dr,
           Accounted_Cr,
           Group_Id,
           Request_Id,
           Status_Description)
        Values
          (Rec_Gl_Balances(i).Status,
           Rec_Gl_Balances(i).Accounting_Date,
           Rec_Gl_Balances(i).Segment1,
           Rec_Gl_Balances(i).Segment2,
           Rec_Gl_Balances(i).Segment3,
           Rec_Gl_Balances(i).Segment4,
           '00000',
           '0',
           '0000',
           '00',
           '00',
           Rec_Gl_Balances(i).Entered_Dr,
           Rec_Gl_Balances(i).Entered_Cr,
           Fnd_Profile.Value('GL_SET_OF_BKS_ID'),
           Rec_Gl_Balances(i).Currency_Code,
           Rec_Gl_Balances(i).Date_Created,
           l_n_User_Id,
           Rec_Gl_Balances(i).Actual_Flag,
           Rec_Gl_Balances(i).User_Je_Category_Name,
           Rec_Gl_Balances(i).User_Je_Source_Name,
           Rec_Gl_Balances(i).Accounted_Dr,
           Rec_Gl_Balances(i).Accounted_Cr,
           Rec_Gl_Balances(i).Group_Id,
           l_n_Request_Id,
           Rec_Gl_Balances(i).Status_Description);
     
        Update Xxfa_Gl_Camra_Stg_Tab Xgcst
        -- IF  Record insertd into Gl interafce then update the stage table with 'Process Completed 'status.
           Set Stg_Status        = v_Stg_Status,
               Request_Id        = l_n_Request_Id,
               Last_Updated_By   = l_n_User_Id,
               Last_Updated_Date = Sysdate
         Where Xgcst.Rowid = Rec_Gl_Balances(i).Row_Id;
     
        v_Insert_Rows := v_Insert_Rows + 1;
        v_Enter_Dr    := v_Enter_Dr + Rec_Gl_Balances(i).Entered_Dr;
        v_Enter_Cr    := v_Enter_Cr + Rec_Gl_Balances(i).Entered_Cr;
      End If;
   
      Commit;
    End Loop;
 
    v_Total_Dr := v_Enter_Dr + v_Error_Enter_Dr;
    v_Total_Cr := v_Enter_Cr + v_Error_Enter_Cr;
    Fnd_File.Put_Line(Fnd_File.Output,
                      '--------------------------------------------------------------');
    Fnd_File.Put_Line(Fnd_File.Output,
                      'No of records Processed                     :' ||
                      v_Rows);
    Fnd_File.Put_Line(Fnd_File.Output,
                      'No of records inserted to GL interface table:' ||
                      v_Insert_Rows);
    Fnd_File.Put_Line(Fnd_File.Output,
                      'No of records rejected                      :' ||
                      v_Error_Rows);
    Fnd_File.Put_Line(Fnd_File.Output,
                      '--------------------------------------------------------------');
    Fnd_File.Put_Line(Fnd_File.Output,
                      '--------------------------------------------------------------');
    Fnd_File.Put_Line(Fnd_File.Output,
                      'Total Debit  Amount                         :' ||
                      v_Total_Dr);
    Fnd_File.Put_Line(Fnd_File.Output,
                      'Total Credit Amount                         :' ||
                      v_Total_Cr);
    Fnd_File.Put_Line(Fnd_File.Output,
                      'dr_amount  enetered into interface table    :' ||
                      v_Enter_Dr);
    Fnd_File.Put_Line(Fnd_File.Output,
                      'cr_amount  enetered into interface table    :' ||
                      v_Enter_Cr);
    Fnd_File.Put_Line(Fnd_File.Output,
                      'Rejected   dr_amount                        :' ||
                      v_Error_Enter_Dr);
    Fnd_File.Put_Line(Fnd_File.Output,
                      'Rejected   cr_amount                        :' ||
                      v_Error_Enter_Cr);
    Fnd_File.Put_Line(Fnd_File.Output,
                      '--------------------------------------------------------------');
    Fnd_File.Put_Line(Fnd_File.Output, '  ');
    Fnd_File.Put_Line(Fnd_File.Output,
                      '** More Information about Rejected records check the stage table XXFA_GL_CAMRA_STG_TAB');
    Fnd_File.Put_Line(Fnd_File.Output,
                      '   Reason for  rejecting check the Error Description column **');
    ------------
    Fnd_File.Put_Line(Fnd_File.Log,
                      '------------------------------------------------------------');
    Fnd_File.Put_Line(Fnd_File.Log, 'Request id :' || l_n_Request_Id);
    Fnd_File.Put_Line(Fnd_File.Log, 'User  id   :' || l_n_User_Id);
    Fnd_File.Put_Line(Fnd_File.Log,
                      '------------------------------------------------------------');
  Exception
    When Others Then
      Fnd_File.Put_Line(Fnd_File.Log, 'EXCEPTION');
      Fnd_File.Put_Line(Fnd_File.Log,
                        '---------------------------------------------------');
      Fnd_File.Put_Line(Fnd_File.Log, 'Request id :' || l_n_Request_Id);
      --  fnd_file.put_line (fnd_file.log,'CCID      :'||v_n_Code_Combination_Id);
      Fnd_File.Put_Line(Fnd_File.Log,
                        'Errmsg    :' || Sqlcode || ' ' || Sqlerrm);
  End Xxfa_Gl_Camra_Proc; -- End procedure
End Xxfa_Gl_Camra_Pkg; -- End Pkg .