ap_query

SELECT   APA.INVOICE_NUM,
         APA.INVOICE_TYPE_LOOKUP_CODE,
         APA.INVOICE_AMOUNT,
         APA.AMOUNT_PAID,
         DECODE(APA.PAYMENT_STATUS_FLAG,'Y','PAID','P','PARTIALLY PAID')STATUS
 FROM    AP_INVOICES_ALL APA,
         PO_DISTRIBUTIONS_ALL PDA
WHERE   APA.INVOICE_TYPE_LOOKUP_CODE='STANDARD'
AND     APA.PAYMENT_STATUS_FLAG IN('Y','P')



create or replace package AP_PKG as
procedure ap_proc(errbuf out varchar2,retcode out varchar2,p_fromdate in date,p_todate in date);
end ap_pkg;



create or replace package body ap_pkg as
PROCEDURE AP_PROC(errbuf OUT VARCHAR2,retcode OUT VARCHAR2,p_fromdate IN DATE,p_todate  IN DATE) AS
CURSOR c1 IS SELECT  APA.INVOICE_NUM "INVOICENUMBER",
                     APA.INVOICE_TYPE_LOOKUP_CODE "INVOICETYPE",
                     APA.INVOICE_AMOUNT "INVOICEAMOUNT",
                     APA.AMOUNT_PAID "AMOUNTPAID",
                     DECODE(APA.PAYMENT_STATUS_FLAG,'Y','PAID','P',
                    'PARTIALLYPAID') "STATUS"       
             FROM   AP_INVOICES_ALL APA
             WHERE  APA.invoice_TYPE_LOOKUP_CODE ='STANDARD'
             AND APA.payment_status_flag IN('Y','P')
             AND APA.INVOICE_DATE BETWEEN p_FROMDATE AND P_TODATE;   
BEGIN       
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'invoice from date is'||p_fromdate);
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'invoice from date is'||p_todate);
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'invoicenumber'||'~'||'invoicetype'||'~'||'invoiceamount'||'~'||
'amountpaid'||'~'||'status');
FOR I IN c1
LOOP
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,I.INVOICENUMBER||I.INVOICETYPE||I.INVOICEAMOUnT||I.AMOUNTPAID||i.status);
END LOOP;
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'***end of the report***');
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'***confidential***');
END AP_PROC;
end ap_pkg;

po_query2

SELECT
          PV.SEGMENT1
,        PV.VENDOR_NAME
,        PVSA.VENDOR_SITE_CODE
,        PVSA.ADDRESS_LINE1
,        PVSA.ADDRESS_LINE2
,        PVSA.CITY
,        PVSA.STATE
,        PVSA.ZIP
,        PVSA.COUNTRY
,        PVSA.AREA_CODE
,        PVSA.PHONE
,        AIA.INVOICE_NUM
,        AIA.INVOICE_DATE
,        AIA.DESCRIPTION
,        AIA.CREATION_DATE
,        PAPF.FULL_NAME
,        AIA.INVOICE_CURRENCY_CODE
,        AIA.INVOICE_AMOUNT
,        GSOB.CURRENCY_CODE
,        AIA.BASE_AMOUNT
,        AIDA.DISTRIBUTION_LINE_NUMBER
,        AIDA.ACCOUNTING_DATE
,        AIDA.DESCRIPTION
,        AIDA.AMOUNT
,        AIDA.BASE_AMOUNT
,        GCC.SEGMENT1
,        GCC.SEGMENT2
,        GCC.SEGMENT3
,        GCC.SEGMENT4
,        PHA.SEGMENT1
,        PLA.LINE_NUM
,        PPF.FULL_NAME
,        ACA.CURRENCY_CODE
,        ACA.CHECK_NUMBER
,        ACA.CHECK_DATE
,        ACA.AMOUNT
,        ACA.BASE_AMOUNT
FROM
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
AP_INVOICES_ALL AIA,
GL_SETS_OF_BOOKS GSOB,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
GL_CODE_COMBINATIONS GCC,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PER_PEOPLE_F PPF,
PER_ALL_PEOPLE_F PAPF,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA
WHERE
     PV.VENDOR_ID=PVSA.VENDOR_ID
AND     PVSA.VENDOR_SITE_ID=AIA.VENDOR_SITE_ID
AND     AIA.INVOICE_ID=AIDA.INVOICE_ID
AND     AIA.CREATED_BY=PPF.CREATED_BY
AND     AIA.INVOICE_ID=AIPA.INVOICE_ID
AND      AIPA.CHECK_ID=ACA.CHECK_ID
AND     AIA.SET_OF_BOOKS_ID=GSOB.SET_OF_BOOKS_ID
AND     AIDA.DIST_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND     AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND     PHA.AGENT_ID=PPF.PERSON_ID
AND     PPF.PERSON_ID=PAPF.PERSON_ID
AND     PDA.PO_LINE_ID(+)=PLA.PO_LINE_ID
AND     PLA.PO_HEADER_ID(+)=PHA.PO_HEADER_ID


AND  AIDA.ACCOUNTING_DATE BETWEEN TO_CHAR(:START_ACC_DATE ,'DD-MON-YYYY') AND TO_CHAR(:END_ACC_DATE,'DD-MON-YYYY')
   
AND     PV.VENDOR_NAME = NVL(:P_VENDOR_NAME,PV.VENDOR_NAME)

po_query1

SELECT
          PV.SEGMENT1
,        PV.VENDOR_NAME
,        PVSA.VENDOR_SITE_CODE
,        PVSA.ADDRESS_LINE1
,        PVSA.ADDRESS_LINE2
,        PVSA.CITY
,        PVSA.STATE
,        PVSA.ZIP
,        PVSA.COUNTRY
,        PVSA.AREA_CODE
,        PVSA.PHONE
,        AIA.INVOICE_NUM
,        AIA.INVOICE_DATE
,        AIA.DESCRIPTION
,        AIA.CREATION_DATE
,        PAPF.FULL_NAME
,        AIA.INVOICE_CURRENCY_CODE
,        AIA.INVOICE_AMOUNT
,        GSOB.CURRENCY_CODE
,        AIA.BASE_AMOUNT
,        AIDA.DISTRIBUTION_LINE_NUMBER
,        AIDA.ACCOUNTING_DATE
,        AIDA.DESCRIPTION
,        AIDA.AMOUNT
,        AIDA.BASE_AMOUNT
,        GCC.SEGMENT1
,        GCC.SEGMENT2
,        GCC.SEGMENT3
,        GCC.SEGMENT4
,        PHA.SEGMENT1
,        PLA.LINE_NUM
,        PPF.FULL_NAME
,        ACA.CURRENCY_CODE
,        ACA.CHECK_NUMBER
,        ACA.CHECK_DATE
,        ACA.AMOUNT
,        ACA.BASE_AMOUNT
FROM
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
AP_INVOICES_ALL AIA,
GL_SETS_OF_BOOKS GSOB,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
GL_CODE_COMBINATIONS GCC,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PER_PEOPLE_F PPF,
PER_ALL_PEOPLE_F PAPF,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA
WHERE
     PV.VENDOR_ID=PVSA.VENDOR_ID
AND     PVSA.VENDOR_SITE_ID=AIA.VENDOR_SITE_ID
AND     AIA.INVOICE_ID=AIDA.INVOICE_ID
AND     AIA.CREATED_BY=PPF.CREATED_BY
AND     AIA.SET_OF_BOOKS_ID=GSOB.SET_OF_BOOKS_ID
AND     AIDA.DIST_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND     AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND     PDA.PO_LINE_ID(+)=PLA.PO_LINE_ID
AND     PLA.PO_HEADER_ID(+)=PHA.PO_HEADER_ID
AND     AIA.INVOICE_ID=AIPA.INVOICE_ID
AND  AIPA.CHECK_ID=ACA.CHECK_ID
AND     PHA.AGENT_ID=PPF.PERSON_ID
AND     PPF.PERSON_ID=PAPF.PERSON_ID

AND  AIDA.ACCOUNTING_DATE BETWEEN TO_CHAR(:START_ACC_DATE ,'DD-MON-YYYY') AND TO_CHAR(:END_ACC_DATE,'DD-MON-YYYY')
   
AND     PV.VENDOR_NAME = NVL(:P_VENDOR_NAME,PV.VENDOR_NAME)

Project Vision Operations(USA):  PA

Flow	Navigations	Tables	Primary Key	Join
Projects Headers Task1,Task2	Projects Projects Number Tab. TASKS TASKS DETAILS	PA_PROJECTS_ALL PA_TASKS	PROJECT_ID TASK_ID	PROJECT_ID
Customers & Contacts	Projects Find Open Customers & contacts	PA_PROJECT_CUSTOMERS PA_PROJECT_CONTACTS	CUSTOMER_ID CONTACT_ID	CUSTOMER_ID PROJECT_ID FROM PA_PROJECTS_ALL
Agreements & Funding s	Billing Agreements	PA_AGREEMENTS_ALL PA_PROJECT_FUNDINGS	AGREEMENT_ID PROJECT_FUNDING_ID	AGREEMENT_ID PROJECT_ID FROM PA_PROJECTS_ALL
Budgets	Budgets	PA_BUDGET_VERSIONS PA_BUDGETS_LINES	BUDGET_VERSION_ID BUDGET_LINE_ID	PROJECT_ID FROM PA_PROJECTS_ALL
Events	Billing Events	PA_EVENTS_ALL	EVENT_ID	PROJECT_ID FROM PA_PROJECTS_ALL
Expenditures	Expenditure Pre approved Batches Enter	PA_EXPENDATURES_ALL PA_EXPENDATURE_ITEMS_ALL	EXPENDATURE_ID EXPENDATURE_ITEM_ID	EXPENDATURE_ID PROJECT_ID FROM PA_PROJECTS_ALL
Key Members	Projects Projects Number Tab. Open Key Members	PA_PROJECT_PLAYERS PER_ALL_PEOPLE_F	PROJECT_PARTY_ID PERSON_ID	PROJECT_ID FROM PA_PROJECTS_ALL
Transaction Control	Projects Projects Number Tab. Find Open Transaction Controal	PA_TRANSACTION_CONTROAL PER_ALL_PEOPLE_F	PROJECT_ID PERSON_ID TASK_ID	PROJECT_ID FROM PA_PROJECTS_ALL