1.PA 11i TRM:
https://docs.oracle.com/cd/A85964_01/acrobat/patrm.pdf

2.PA Manufacturing TRM:
https://docs.oracle.com/cd/A88418_01/acrobat/pjmtrm.pdf

3.PA Apis & Client Exxtension:
http://docs.oracle.com/cd/B40089_10/current/acrobat/120pjapi.pdf

4. SQL script to assist in the identification of Projects revenue issues

Project Billing

To provide an SQL script to assist in the identification of Projects (PA) revenue issues.

This script could prove very useful for PA revenue problems. It will sum up all the accrued and unearned revenue, thus providing a figure for the total revenue against a project.

select dr.draft_revenue_Num, sum(dri.amount) amt

from pa_draft_Revenues dr, pa_draft_revenue_items dr

where dri.project_id = dr.project_Id

and dri.draft_revenue_Num = dr.draft_revenue_Num

and dr.project_Id = &project_Id

group by dr.draft_revenue_Num;

5. Oracle Projects Billing Interface with Oracle Receivables

Oracle Projects (PA) generates draft invoices and uses Oracle Receivables (AR) features to create invoices and interface the accounting transactions to Oracle General Ledger (GL). When you interface invoices to Receivables, a Projects process is used to collect all eligible, released draft invoices in PA and interfaces them to the Receivables interface tables. This process also maintains project balances of Unbilled Receivables and unearned revenue and creates accounting transactions for these amounts.

Once in the AR interface tables, the draft invoices await further processing by the Oracle Receivables AutoInvoice process. After the AutoInvoice program is run to create invoices in Oracle Receivables, you need to run the tieback process to ensure that your data successfully loaded into Receivables and that there are no transactions requiring correction. If you have rejected invoices, you will need to correct and resubmit them.

Standard Oracle Projects reports can be used to track your invoices as you interface data between Projects and Receivables. You can also use AutoInvoice output reports to review imported transaction data and transaction data that fails when you run AutoInvoice. Modification Of Transaction Lines (PA/AR Out of Sync). It is possible to modify or delete invoice transactions transferred from Projects to Receivables using the Transaction Entry Workbench (ARXTWMAI).

THE MODIFICATION OR DELETION OF INVOICE TRANSACTIONS IN THIS MANNER IS NOT RECOMMENDED BECAUSE IT COULD CAUSE AR TO BE OUT OF SYNC WITH PA.

Refer to the scripts below to verify the information contained in the PA-AR invoice interface. Both scripts should normally return the same lines and revenue amounts:

SELECT l.interface_line_attribute2, l.interface_line_attribute6,

l.revenue_amount, l.interface_line_attribute1, h.reason_code,

l.interface_line_context

FROM ra_customer_trx_all h, ra_customer_trx_lines_all l

WHERE h.customer_trx_id = l.customer_trx_id

AND h.interface_header_attribute2 = l.interface_line_attribute2

AND h.trx_number = &Transaction_no.

ORDER BY l.interface_line_attribute6;

..and..

SELECT h.draft_invoice_num, l.line_num, amount, p.segment1,

h.transfer_status_code

FROM pa_draft_invoices_all h, pa_draft_invoice_items l, pa_projects_all p

WHERE h.ra_invoice_number = &Transaction_no.

AND h.project_id = l.project_id

AND h.draft_invoice_num = l.draft_invoice_num

AND l.project_id = p.project_id(+)

ORDER BY l.line_num;

6.Oracle Projects Invoice Flow

Oracle Projects Invoice Flow:

Below are Steps that needs to be followed for successfully creating a Projects Invoice and interfacing it to Oracle Receivables.

1. Creation of Contract Project (Mandatory)

2. Assign a Customer for the Project (Mandatory)

3. Create an Agreement with the Customer (Mandatory)

4. Fund the Project through the Customer Agreement (Mandatory)

5. Create an Approved Revenue Budget for the Project (optional).

6. Baseline the Funding (Mandatory).

7. Generate Draft Revenue (Optional).

8. Generate Draft Invoice (Mandatory).

9. Approve and Release the Draft Invoice (Mandatory).

10. Interface Draft Invoices to Oracle Receivables. (Mandatory)

11. Tieback invoices back to Oracle Projects. (Mandatory).

1. Creation of Contract Project:

Create a Project of ‘Contract’ Project Type. Only Contract Projects can be associated with a Customer. Create WBS Structure for the Project. Identify the Project Manager. Assign Team members if needed.

Tables Involved

PA_PROJECTS_ALL – Projects Table

PA_PROJECT_TYPES – Project Types Information

PA_TASKS – Tasks Associated with the Projects (WBS Information)

PA_PROJECT_PLAYERS – Contains the Key Member Assoications with the Project including the Project managers.

2. Assign a Customer for the Project:

Assign a Customer to the Project and the ‘Bill to ‘ Address for the customer, so that the customer can be billed. Also you can assign Billing and Shipping Contacts for the Customer in the Customers Options in the Projects Form.

Tables Involved

PA_PROJECT_CUSTOMERS – Projects-Customers Association

3. Create an Agreement for the Customer:

A Funding Agreement needs to be created for the Customer with the Terms and the Agreement Amount. This can be done using the Agreements Form.

Here you can specify the ‘Hard Limts’ . If the Hard Limit is set for Revenue, Revenue cannot be generated past the funded amount for the project. Similarly if the Hard Limit is set for the invoice, the customer cannot be billed past the funded amount in the agreement for that project.

Tables Involved

PA_AGREEMENTS_ALL – Agreement Header Information.

4. Fund the Project through the Customer Agreement:

Fund the Project using the Customer agreement created in Step 4. This can be done using the Fundings Section in the Agreement Form. If a customer agreement already exists for this customer, you can use the same agreement to fund this project.

Tables Involved

PA_SUMMARY_PROJECT_FUNDINGS – Project Funding Information.

5. Create an Approved Revenue Budget for the Project:

This Step is optional, if the ‘Baseline Funding without Budget‘ option is set at the Project level. If this option is not set, then an approved revenue budget for the project has to be created with the funding amount. Baselining this budget, baselines the funding automatically.

Tables Involved

PA_BUDGET_VERSIONS – Budget header info

PA_BUDGET_LINES : Budget Line level info

6. Baseline the Funding:

If ‘Baseline Funding without Budget’ is set then the funding can be baselined without the Approved Revenue Budget. Oracle Projects creates an internal Approved Revenue Budget with the budget amount equal to the Funding amount and baselines both the Budget and Funding.

7. Generate Draft Revenue:

This step is optional depending on the Distribution Rule for the invoice. If the invoice distribution rule is WORK, this step is mandatory. The process “PRC: Generate Draft Revenue for a single Project” is run for the Project. If revenue needs to be generated for multiple projects, run the “PRC: Generate Draft Revenue for range of Projects” process giving the Project Number ranges.

Tables Involved

PA_DRAFT_REVENUES_ALL – Revenue Header info

PA_DRAFT_REVENUE_ITEMS – Line level details.

PA_CUST_REV_DIST_LINES_ALL – Revenue distribution lines for the Expenditure items

PA_CUST_EVENT_RDL_ALL – Revenue distribution lines for the events

8. Generate Draft Invoice:

Run the Process “PRC:Generate Draft Invoice for a single Project”, giving the Project number as parameter. This will generate draft invoices only for that project. If you want to generate invoices for multiple projects, run “PRC: Generate Draft Invoice for a range of Projects” giving the Project number ranges.

Tables Involved

PA_DRAFT_INVOICES_ALL – Draft invoice header information

PA_DRAFT_INVOICE_ITEMS – Item level information

9. Approve and Release the Draft Invoice:

The invoices needs to be approved and released in order to interface them to AR. This can be done in the Invoice Review Form. Alternatively the Automatic Approval and release client extension can be used to automatically approve and release the invoices. But it all depends on the business scenario. Generally an invoice accountant will review the invoice, approve and release it.

The approval workflow can also be customized to have a multi-level approval mechanism.

10. Interface Draft Invoices to Oracle Receivables

Run the “PRC: Interface invoices to Receivables” process in order to interface the released projects invoices to AR. This process will populate the AR interface table. Once this process is run, in AR, the ” Autoinvoice import”process need to be run so that it will create AR invoices from the interface records.

Tables Involved

RA_INTERFACE_LINES_ALL

RA_INTERFACE_DISTRIBUTIONS_ALL

11. Tieback invoices back to Oracle Projects

Once the Autoinvoice import is successfully run, the “PRC: Tieback Invoices from Receivables” process is run to update the status of the invoice import in AR to the Projects Invoices

Integrated project with payables

Key items discussed for each integration point:

1. Data flow during each process and various tables involved which provides all the technical details of the processes.

2. Transactional flow between PA and AP module with all the functional details.

3. Required concurrent manager processes to push or pull data across the integration points and the order in which they should be invoked

4. Various Problems encountered during each process and error messages

5. Troubleshooting/Bug information whichever is applicable

6. Tips to overcome typical error messages.

7. New features in 11i and some of the setup parameters are also discussed briefly.

ENTERING EXPENSE REPORTS:
Expense Reports can be entered in Oracle Payables or Oracle Projects in the following different ways in 11i:
1. They can be entered in Oracle Projects directly through the pre-approved batches window in Oracle Projects.
2. They can be entered in Oracle Self -Service Expenses (Web Expenses/Internet Expenses) and imported into
Oracle Payables and then interfaced to Oracle Projects.
3. They can be entered directly in Oracle Payables using the Invoice and the Distributions window in AP and then interfaced into Oracle Projects.
4. They can be imported into Oracle Projects from an external source and then processed through the
Transaction Import Process.
(No longer supported in 11i are Oracle Project Time and Expense and Oracle Personal Time and Expense (PTE).)

SETTING UP IN PAYABLES AND ORACLE PROJECTS
Before interfacing project-related expense reports between Oracle Projects and Payables, we have to do the
following set-up in PA and AP. The set-up steps discussed here are based on Oracle Applications 11i.
Check Automatically Create Employee as Supplier (AP, Setup->Options->Payables->Expense Report)
Select the Expense Reimbursement Address as Office/Home (AP, Setup->Financials->HR)
Accept or override the employee address (AP, Setup->Options->Payment->Allow Address Change)
Define/Determine the Expense Report Cost Account (Auto accounting rules)
Define/Determine the Expense Report Liability Account (Auto accounting rules)
PA: Summarize Expense Report lines (SYSADMIN: Profile>System>Value–Application level)
PA: Allow Override of PA Distributions in AP/PO (Site, Application, Responsibility and User)
PA: Auto approve Expense Reports (Application level)
PA: Default Expenditure Organization in AP/PO (Site Level)
Invoice Batch Source = ”PA Invoices” (PA Setup/System/Implementation Option /Billing)
SUBMITTING THE INTERFACE STREAMLINE PROCESSES
Streamline processes submit two or more processes in one step. You can use streamline processes to interface expense reports to Payables, import the invoices, and tie back the invoices to Oracle Projects. Streamline processes submit each process sequentially. There are several streamline options available: DXES, DXEU, XES,XEU etc. Some processes use a lot of system resources (DXES, DXEU etc.). In Collective, We run the PRC:Distribute Expense Report Costs separately and then use XES – Interface Expense Report Costs to AP (Summarized) streamline option to submit PRC: Submit Interface Streamline process to complete the rest of the processes.

PA Ù AP INTEGRATION PROCESSES:

Oracle Payables integrates with Oracle Projects in such a way that Payables Invoices are created based on expense reports entered in Oracle Projects. Oracle Payables can also create pre-payments or advances in Oracle Payables and apply them to Oracle Projects expense reports. But in order to be able to apply pre-payments in Oracle Payables, the expense reports must have been already interfaced or loaded in to Oracle Payables.

We have a web-based Custom Expense application (fully integrated with Oracle Projects) using which all the employees fill up their expense reports online and assign them to the corresponding project & task codes along with all the expenditure details. Once the expense reports are verified by their respective Regional Coordinators, the status of the expense reports are set to ‘VERIFIED’ and sent to their respective Managers for the approval. After the expense reports are approved by their respective District Managers, the status of these expense reports are set to ‘APPROVED’.

A Concurrent Program (SQL Script) is set up in Oracle Projects to execute once every week to transfer the ‘APPROVED’ expense reports from the web interface into the Project Accounting interface table (Pa_transaction_interface_all). The following vital information is transferred at the web interface during the expense download: Employee_number, expenditure_item_date, project_number, task_number, expenditure_type, quantity, transaction_status_code and expenditure_ending_date.

Note: When populating the Pa_transaction_interface_all table by the import utility, make sure that the transaction_source column is populated exactly as defined in the Transaction Source window in Projects which is usually defined during the Implementation. (Setup->Expenditures->Transaction Sources).

Once the expense information is transferred into the Oracle Projects Interface, the following concurrent processes are invoked in order to complete the transfer into Oracle Payables in the same sequential order as shown below (either using Streamline process or invoking them individually)

1. PRC: Transaction Import

2.      PRC: Distribute Expense Report Costs
3.      PRC: Interface Expense Reports to Payables
4.      AP:   Payables Invoice Import
5.      PRC: Tieback Expense Reports from Payables.

PRC: TRANSACTION IMPORT

Transaction Import is an open Interface Process that allows us to validate and load transactions from external cost collection systems into Oracle Projects. Whenever we import the expense information from an external system

Oracle Projects records the transaction details and the source of the imported transactions during transaction import.

Transaction import loads transactions as pre-approved expenditure items and expenditure batches are created as having a status of ‘RELEASED’. This Import process transfers the data from the project interface table pa_transaction_interface_all in to the Project accounting tables pa_expenditure_batches_all, pa_expenditures_all, pa_expenditure_items_all.

pa_transaction_xface_ctrl_all is used internally by the transaction import program to control processing of pending transactions in the interface table by multiple concurrent requests. One record is inserted into this table for each unique combination of transaction_source/batch_name/expenditure type class record inserted into pa_transaction_interface_all. Each request for the Transaction Import process locks the unprocessed records in the pa_transaction_xface_ctrl_all table that meet the request’s selection criteria thereby preventing other requests from attempting to import the same transactions.

Each unique batch name becomes an expenditure batch, and each unique expenditure type class, employee number, and expenditure ending date combination becomes expenditure with in the expenditure batch. The ending date of the expenditure batch is set to the maximum ending date of all the expenditures created within the batch.

		PA_TRANSACTION_INTERFACE_ALL
		PA_TRANSACTION_SOURCES
		PA_TRANSACTION_XFACE_CTRL_ALL

PA_EXPENDITURE_GROUPS_ALL				PA_EXPENDITURE_ITEMS_ALL


	PA_EXPENDITURE_BATCHES		PA_EXPENDITURES_ALL
	PA_EXPENDITURE_BATCHES

Transaction Import automatically validates the data for compatibility with Oracle Projects by ensuring that the columns in the interface table reference the appropriate and active values and columns in Oracle Projects. This validates all the items with in expenditure before it creates expenditure. If at least one item in expenditure fails the validation, Oracle Projects rejects all the items in the expenditure. The rejection reason can be obtained from the transaction_rejection_code in pa_transaction_interface_all.

During validation, Transaction Import Process checks whether the expenditure item falls within the project dates, the project status allows transactions, transaction controls and extensions allow the type of charge being made, expenditure items fall with in task dates, the task is a lowest task and is chargeable, the expenditure type is active and the employee is active.

Transaction Import process generates two reports: An exception report listing all rejected transactions, and a summary report showing the successfully imported transactions. The exception report will provide rejection reasons for all rejected items. While only the items that are rejected appear on the exception report, Transaction import process rejects the entire expenditure and updates all the items in that expenditure with the status of rejected (sets Transaction_status_code =‘R’ in Pa_transaction_interface_alltable). You must correct the rejected transactions and then re-run the process.

Note: From version 11 onwards, a new form called review transactions has been added in Oracle Projects. (Navigation is:Expenditures -> Transaction Import -> Review Transactions). This form is extremely useful in expediting minor additions to the expenditure batches. This form can also be used to correct rejected transactions in the interface table using this form.

COMMON EXCEPTIONS ENCOUNTERED DURING THIS PROCESS:

Following are the most common rejections encountered during the Transaction Import Process by Collective. Each rejection code is described in detail along with the description and solution

Rejection Code: PA_EX_PROJECT_CLOSED

Description: This happens when the expenditure is assigned to a project, which is no longer active. Solution:Assign the expenses to a different Project.

2. Rejection Code: PA_EX_PROJECT_DATE

Description: This happens when the expenditure Item date is not within the active dates of the project. Solution: Assign the expenses to a different Project, which was active during the item date, or Change the project start date to an earlier date.

3. Rejection Code: EI_DATE_AFTER_END_DATE

Description: This happens when the expenditure item date is after the expenditure ending dates. Solution:Change the expenditure_item_date or the expenditure_ending_date.

4. Rejection Code: INVALID_END_DATE

Description: The value for the expenditure ending date is not a valid week ending date. Solution:Change the expenditure_ending_date to a valid weekending date.

5. Rejection Code: INVALID_PROJECT

Description: No project exists with the project number specified. Solution:Check to make sure if the project exists.

6. Rejection Code: INVALID_EMPLOYEE

Description: No employee exists with the employee number specified. Solution:Check to see if the employee information exists in Projects.

7. Rejection Code: PA_EXP_TASK_TC

Description: The transaction violates an expenditure transaction control at the task level. If the transaction control has overlapping assignments with in this expenditure_item_date/ expenditure_ending_date, the transaction gets rejected with the above code.

Solution: Make sure there are no overlaps in the PA_TRANSACTION_CONTROLS table for this task_id and project_id.

8. Rejection Code: PA_EXP_TYPE_INACTIVE

Description: The expenditure item date falls outside the effective dates of the expenditure type. Solution:Change the expenditure item date, expenditure type, or expenditure types.

9. Rejection Code: PA_EXP_TASK_EFF

Description: Expenditure item date is not within the active dates of the task. Solution: Change the expense report task_number to reflect the active task number.

10. Rejection Code: NO_ASSIGNMENT

Description: The employee does not have any valid assignment.

Solution: This was one of the issues with our Import. Even though the assignment information existed in all the HR tables, the assignment wouldn’t show up in the Applications. We kept getting the rejection code ‘NO ASSIGNMENT even after updating the member assignment in the forms. After doing some research on this issue, we figured out that:

The min (effective_start_date) for each employee in per_all_assignments_f (per_assignments_f in V10.7) table should match the effective_start_date in per_all_people_f (per_people_f in V 10.7) table and date_start column in per_periods_of_service table. If they don’t match, then the assignment won’t show up in the Applications. So make sure the start dates of the employees in all the HR tables are in sync.

PRC: DISTRIBUTE EXPENSE REPORT COSTS

Costing is the process whereby expenditures are calculated and are assigned to Projects. Costing also includes the assignment of the debit or expense account using Auto accounting Setup.

PRC: Distribute Expense Report costs computes the costs of expense report expenditure items, including adjustments, and determine the account to which to post the cost. It groups expenditure items into batches (inserts a row into Pa_expenditure_batches_all) of expense reports so that they can be interfaced to Oracle Payables. This process is the prerequisite for the generation of revenue and Invoices for expense report expenditure items. In order to limit the process to certain Expense reports, we can specify any of the following parameters: Expenditure Batch, Employee Name, and Through Week-ending date.

Behind the scenes, this program distributes the expense report data from Pa_expenditures_all and

Pa_expenditure_items_all tables in to pa_cost_distribution_lines_all table. This process picks up all the rows fromPa_expenditure_items_all table where the column cost_distributed_flag is set to ‘N’. Once the cost is distributed, thecost_distributed_flag will be set to ‘Y’

Auto accounting is a critical piece of project costing. When this distribute process runs, it invokes the Auto accounting function ‘Expense Report Cost Account’ and populates the respective Debit CCID (dr_code_combination_id column) in thePa_cost_distribution_lines_all table (cdl).

When distributing costs, Project Accounting refers to the transaction’s expenditure item date to determine the correct Project Accounting period to post the transaction to. The expenditure item date is the date the work was performed, or the date when the expense is expected to have incurred. If the respective Project Accounting period is closed, Project Accounting will post the transaction to the earliest open period, as PA Date is determined based on the first open or future period that is on or after the expenditure_item_date.

After the successful completion of the above process, a row will be created in pa_expenditure_batches_all table. Also, thetransfer_status_code column in the cdl table will be set to ‘P’ and batch_status_code column in Pa_expenditure_batches_all will be set to ‘RELEASED’

PA_EXPENDITURES_ALL

PA_EXPENDITURE_ITEMS_ALL		PA_EXPENDITURE_BATCHES

PA_COST_DISTRIBUTION_LINES_ALL

Flowchart indicating the distribution of cost data in PA

COMMON EXCEPTIONS ENCOUNTERED DURING THIS PROCESS:

Following are the some of the typical exceptions encountered during the PRC Distribute Expense Report Costs process:

1. Rejection Code: KEY_FLEX_FAIL-Invalid Accounting Flexfield andAA_NULL_CCID- Auto Accounting Error: null ccid

Description: The GL account returned by Auto Accounting does not pass validation rules defined for the gl account and the code combination id derived by AutoAccounting is invalid respectively.

Solution: Check to see if the project code, task code and Organization_id are correct. Find out which gl code the above combination will hit. Make sure you haven’t excluded this particular GL code in the Cross-validation rules and make sure dynamic inserts are turned on.

2. Rejection Code: AA_FAIL - Incomplete Autoaccounting Rules

Description: A GL account could not be determined for the cost distribution line, due to incomplete Auto Accounting setup

Solution: Check to see if the Project_code, task_code, incurred_by_organization_id are correct. Everything should be in sync in order to avoid this.

3. Rejection Code: NO_PA_DATE

Description: Project Accounting Period is not open Solution:Make sure your PA period is open.

PRC: INTERFACE EXPENSE REPORTS TO PAYABLES

The Interface Expense Reports to Payables process (PATTER process) collects eligible Cost distributed Expense reports in Oracle Projects and sends them to Payables interface tables. Any adjustments processed for existing transactions are attached to the original expense reports in Oracle Payables for Cash Basis Accounting Purposes.

This PATTER process should be invoked after successful completion of PRC: Distribute Expense Report Costs process. Irrespective of whether we run the interface process from PA to AP as a Streamline process or a Stand-alone process, this PRC: Interface Expense Reports to Payables Process is the nerve center of the integration between PA and AP.

The GL Date of the expense report cost determines the accounting period in which a transaction is posted to a GL account. In Oracle Projects, the GL Date for costs is the end date of the earliest open or future GL period that is on or after the latest PA Date of the cost distribution lines included in an expense report. All cost distribution lines for an expense report are sent together to Payables and use the same GL date. This GL date becomes the GL date of the invoice in payables.

This process invokes the autoaccounting function ‘Expense Report Liability Account’ to populate the liability account (cr_code_combination_id in pa_cost_distribution_lines_all and accts_pay_code_combination_id in ap_expense_report_headers_all) for the invoices. It is imperative to note that the PA Auto accounting rule takes precedence over the employee’s default expense account when determining the invoice liability account (and also it does not invoke Account Generator/ Flexbuilder).

This interface program interfaces and transfers data from pa_cost_distribution_lines_all table (Oracle Projects) intoap_expense_report_headers_all and ap_expense_report_lines_all tables (Oracle Payables Interface). This process checks fortransfer_status_code column in pa_cost_distribution_lines_all table. If the transfer_status_code column in cdl is set to ‘P’, then this process will interface those expense reports to AP. When this process starts, it will set the cost_distributed_flag column in thepa_expenditure_items_all table to ‘S’ so that no other process will pick up these rows when the above concurrent program is in process.

If this process completes successfully, it will set the cost_distributed_flag column in pa_expenditure_items_all to ‘Y’ andtransfer_status_code column in pa_cost_distribution_lines_all table to ‘T’ and purgeable_flag column in theap_expense_report_headers_all table to ‘No’. If the process aborts in between due to some reason, then transfer_status_code column in pa_cost_distribution_lines_all table will be set to ‘X’ and rejection_reason will indicate the reason why it failed andcost_distributed_flag in pa_expenditure_items_all table will remain as ‘S’

Note: If expense reports from any source fail to post to Payables, we may need to redistribute costs (using the PRC: Distribute Expense Report Costs process) before we send the expense reports to Payables again.

PA_COST_DISTRIBUTION_LINES_ALL

AP_EXPENSE_REPORT_HEADERS_ALL

AP_EXPENSE_REPORT_LINES_ALL

AP: PAYABLES INVOICE IMPORT

This process creates invoices and invoice distribution lines (ap_invoices_all and ap_invoice_distributions_all) from Oracle Projects expense report information that is loaded into Payables Interface tables (ap_expense_report_headers_all andap_expense_report_lines_all) . Payables group the invoices created from the expense reports you import and creates an invoice batch with the batch name you enter. You can enter a batch name only if you have enabled the Use Batch Control Payables Option, which in enabled in our case.

Payables processes all expense reports entered since the last time you submitted Payables Invoice Import and identifies the invoices you create from Oracle Projects expense reports with a source of Oracle Projects. If you enable the Automatically Create Employee As Supplier in the Payables Option, Payables automatically creates suppliers and supplier sites for employees who are not already suppliers. If you do not enable this option, you must manually enter the employee as a supplier before submitting Payables Invoice Import.

If PRC: Interface Expense Reports to Payables process is completed successfully, then this process will continue. Otherwise this will abort by itself.

AP_EXPENSE_REPORT_HEADERS_ALL

AP_EXPENSE_REPORT_LINES_ALL

AP_INVOICES_ALL		AP_INVOICE_DISTRIBUTIONS_ALL

Flowchart indicating the distribution of cost data from AP Interface to AP tables

PRC: TIEBACK EXPENSE REPORTS FROM PAYABLES

The tieback process is invoked in PA to ensure that AP transactions have been successfully interfaced. This process identifies expense reports rejected by Payables Invoice Import. This reconciles expense reports in Oracle Projects and the related invoices in payables. This process also updates the purgeable_flag from ‘No’ to ‘Yes’ for each expense report in the ap_expense_report_headers_all table.

Project and task creation api in project accounting

In project accounting, project and task creation is one of the major critical work.whatever the cost which occur during expenditure is assign through task only.

CREATE OR REPLACE PACKAGE XXPA_PROJECT_INTERFACE
IS

PROCEDURE XXPA_PROJECT_INTERFACE_VALID(i_source IN VARCHAR2,
i_batch_name IN VARCHAR2

);

PROCEDURE XXPA_SUBMIT_PROJECT_REQUEST(errbuf out varchar2,
retcode out varchar2,
i_file_path IN VARCHAR2,
i_file_name IN VARCHAR2
);

PROCEDURE xxpa_project_interface_p(i_source IN VARCHAR2,i_batch_name IN VARCHAR2);

PROCEDURE XXPA_ERRORS(i_batch_name IN VARCHAR2,
i_line_type IN VARCHAR2,

i_project_number IN VARCHAR2,

i_msg_data IN VARCHAR2,

i_msg_count IN NUMBER,

i_return_status IN VARCHAR2

);

END XXPA_PROJECT_INTERFACE;
/

CREATE OR REPLACE PACKAGE BODY XXPA_PROJECT_INTERFACE
IS

　

PROCEDURE XXPA_PROJECT_INTERFACE_VALID(i_source IN VARCHAR2,i_batch_name IN VARCHAR2) AS

l_org_name VARCHAR2(50);

BEGIN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
SELECT line_type,

batch_name,

NULL,

'Line Type Does Not Exist'

FROM xxpa_project_interface_lines

WHERE line_type IS NULL;

-- Checking organisation in hr_organization_units table

INSERT INTO xxpa_errors(xx_field, batch_name,

source,error_message)

SELECT distinct organisation_name

,i_batch_name

,i_source

,'Organisation Does Not Exist'

FROM xxpa_project_interface_headers

WHERE organisation_name NOT IN (SELECT name

FROM hr_organization_units);

-- Checking Project Status Code which is defined in the PA_LOOKUPS

INSERT INTO xxpa_errors(xx_field, batch_name,

source, error_message)

SELECT distinct project_status_code

,i_batch_name

,i_source

,'Project Status Code Not Exist'

FROM xxpa_project_interface_lines

WHERE project_status_code NOT IN(SELECT project_Status_name

FROM pa_project_statuses

WHERE status_type='PROJECT');

-- Checking Key member roles whether roles are defined in PA_PROJECT_ROLE_TYPE

INSERT INTO xxpa_errors(xx_field,batch_name,

source,error_message)

SELECT DISTINCT key_member_role_type

,i_batch_name

,i_source

,'Project Role Does Not Exist'

FROM xxpa_project_interface_lines

WHERE key_member_role_type NOT IN(SELECT meaning

FROM pa_project_role_types);

BEGIN

SELECT name

INTO l_org_name

FROM hr_organization_units;

EXCEPTION

WHEN OTHERS THEN

l_org_name:=NULL;

END;

-- Checking Key member exists or not

INSERT INTO xxpa_errors(xx_field,batch_name,
source,error_message)

SELECT DISTINCT employee_number

,i_batch_name

,i_source

,'Key Member Does Not Exist'

FROM xxpa_project_interface_lines

WHERE employee_number NOT IN

(SELECT f.employee_number

FROM per_people_f f,

hr_organization_units h

WHERE f.business_group_id =h.business_group_id);

--Checking Class Categories whether categories exist in PA_CLASS_CATEGORIES

INSERT INTO xxpa_errors (xx_field,batch_name,

source,error_message)

SELECT DISTINCT class_category

,i_batch_name

,i_source

,'Class Category Does Not Exist'

FROM xxpa_project_interface_lines

WHERE class_category NOT IN(SELECT class_category

FROM pa_class_categories);

-- Checking the task security, value should be 'Y' or 'N'

INSERT INTO xxpa_errors (xx_field,batch_name,
source,error_message)

SELECT DISTINCT task_security

,i_batch_name

,i_source

,'Task Security Does not Exist'

FROM xxpa_project_interface_lines

WHERE task_security NOT IN ('Y','N');


-- Validating Class Codes whether class codes exist in PA_CLASS_CODES

INSERT INTO xxpa_errors(xx_field,batch_name,
source, error_message)

SELECT DISTINCT class_code

,i_batch_name

,i_source

,'Class Code Does Not Exist'

FROM xxpa_project_interface_lines

WHERE class_code NOT IN(SELECT class_code

FROM pa_class_codes);

commit;

EXCEPTION
WHEN OTHERS THEN

--FND_FILE.PUT_LINE(FND_FILE.LOG,sqlcode||' '||sqlerrm);

NULL;

END XXPA_PROJECT_INTERFACE_VALID;

-- End Of the procedure XXPA_PROJECT_INTERFACE_VALID

　

PROCEDURE XXPA_SUBMIT_PROJECT_REQUEST(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,

i_file_path IN VARCHAR2,

i_file_name IN VARCHAR2

)

AS

-- Declaring Local Variables for the concurrent program
l_request_id NUMBER := 0; -- Variable to hold request id

l_wait_request_id BOOLEAN ; -- Variable to hold wait request

l_request_rep_id NUMBER := 0; -- Variable to hold request for report

l_phase VARCHAR2(50) := NULL; -- Out Variable for wait requsest

l_status VARCHAR2(50) := NULL; -- Out variable for wait requsest

l_dev_phase VARCHAR2(50) := NULL; -- Out variable for wait requsest

-- l_file_name VARCHAR2(100):= NULL; -- stores file name
l_file_name VARCHAR2(200):= NULL; -- stores file name
l_dev_status VARCHAR2(50) := NULL; -- Out variable for wait requsest
l_message VARCHAR2(50) := NULL; -- Out variable for wait requsest

l_source VARCHAR2(50) := NULL;

l_batch_name VARCHAR2(50) := NULL;

l_org_name VARCHAR2(50) := NULL;

l_error_msg VARCHAR2(50) := NULL;

l_err_count NUMBER := 0;

l_org_count NUMBER := 0;

l_line_batch_name VARCHAR2(50) := NULL;

BEGIN


--l_file_name:='$XXCUST_TOP/bin/'||i_file_name;
l_file_name:= rtrim(ltrim(i_file_path))||i_file_name;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Parameters Received --> FILE PATH : '||i_file_path );

FND_FILE.PUT_LINE(FND_FILE.LOG,'Parameters Received --> FILE NAME : '||i_file_name );

　

-- Submitting request for Interface Program through concurrent program

l_request_id := FND_REQUEST.SUBMIT_REQUEST
(application =>'XXCUI',

program =>'XXPAPRJ',

description =>'',

start_time =>NULL,

sub_request =>FALSE,

argument1 =>l_file_name

);

COMMIT;

l_wait_request_id:= FND_CONCURRENT.WAIT_FOR_REQUEST
(l_request_id

,10

,300

,l_phase

,l_status

,l_dev_phase

,l_dev_status

,l_message

);

BEGIN

SELECT DISTINCT source
INTO l_source

FROM xxpa_project_interface_headers

WHERE source IS NOT NULL;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Source',NULL,NULL,'More Than One Source Exists');

WHEN OTHERS THEN
l_error_msg:=SQLCODE||SQLERRM;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Source',NULL,NULL,l_error_msg);
END;

BEGIN
SELECT DISTINCT batch_name

INTO l_batch_name

FROM xxpa_project_interface_headers

WHERE batch_name IS NOT NULL;

EXCEPTION
WHEN TOO_MANY_ROWS THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,'More than one batch exists'

);

WHEN OTHERS THEN
l_error_msg:=SQLCODE||SQLERRM;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,l_error_msg);

END;

　

DELETE XXPA_ERRORS WHERE batch_name=l_batch_name;

COMMIT;


FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Comparing the batches..');

BEGIN
SELECT DISTINCT batch_name

INTO l_line_batch_name

FROM xxpa_project_interface_lines

WHERE batch_name IS NOT NULL;

IF l_line_batch_name <> l_batch_name THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'Comparing the batches..');

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,'Batch Name in Header Section and Detail Section must be Same');

END IF;

EXCEPTION

WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many rows exception ..'||l_batch_name||'>>'||l_line_batch_name);

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,'More Than One Distinct Batch Exists in the Detail Section for the Header Batch');

WHEN OTHERS THEN
l_error_msg:=SQLCODE||SQLERRM;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,NULL,l_error_msg);

END;

SELECT name
INTO l_org_name

FROM hr_organization_units

WHERE organization_id = FND_PROFILE.VALUE('ORG_ID');

SELECT count()
INTO l_org_count

FROM xxpa_project_interface_headers

WHERE organisation_name IS NOT NULL

AND organisation_name = l_org_name;

IF l_org_count=0 THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Organization',l_batch_name,l_source,'No Records exist for this Organzation');

END IF;

COMMIT;

　

FND_MESSAGE.CLEAR;

IF l_source IS NOT NULL AND l_batch_name IS NOT NULL THEN

-- Calling Procedure XXPA_PROJECT_INTERFACE_VALID

xxpa_project_interface_valid(l_source,l_batch_name);

COMMIT;

SELECT COUNT()
INTO l_err_count

FROM xxpa_errors

WHERE batch_name = l_batch_name;

IF l_err_count=0 THEN

-- Calling Interface Program to load data into PA modules using Project API's

XXPA_PROJECT_INTERFACE_P(l_source,l_batch_name);

END IF;

COMMIT;

END IF;

--Calling Report through concurrent program after Loading data into Projects

l_request_rep_id := FND_REQUEST.SUBMIT_REQUEST
(application =>'XXCUI',

program =>'XXPAPP',

description =>' Project Interface Error report',

start_time =>NULL,

sub_request =>FALSE,

argument1 => l_batch_name

);

END XXPA_SUBMIT_PROJECT_REQUEST;

　

PROCEDURE xxpa_errors(i_batch_name IN VARCHAR2,
i_line_type IN VARCHAR2,

i_project_number IN VARCHAR2,

i_msg_data IN VARCHAR2,

i_msg_count IN NUMBER,

i_return_status IN VARCHAR2)

AS

-- Declaring Local variables

l_data VARCHAR2(1000) := NULL; -- Variable to load ErrorMessage by API
l_project_number VARCHAR2(50) := NULL; -- Variable to load Project Number
l_name VARCHAR2(240) := NULL;

l_msg_index_out NUMBER := 0; -- Out parameter generated by API

BEGIN
IF i_return_status <> 'S' THEN -- Status returned from Project API

IF i_msg_count > 0 THEN -- Message count returned from Project API

FOR i IN 1..i_msg_count

LOOP

pa_interface_utils_pub.get_messages(

p_encoded =>'F',

p_msg_index => i,

p_msg_count =>i_msg_count,

p_msg_data =>i_msg_data,

p_data =>l_data,

p_msg_index_out=>l_msg_index_out);

-- Storing errors into xxpa_errors

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(i_project_number
,i_batch_name

,i_line_type

,l_data

);

END LOOP;

END IF;

ELSIF i_return_status = 'S' THEN -- Status return from Project API

IF i_msg_count = 0 THEN
SELECT name

INTO l_name

FROM hr_organization_units

WHERE organization_id=FND_PROFILE.VALUE('ORG_ID');

UPDATE xxpa_project_interface_lines

SET process_flag = 'Y'

WHERE project_number = i_project_number

AND line_type = i_line_type

AND org_name = l_name;

END IF;

END IF;

EXCEPTION
WHEN OTHERS THEN

--FND_FILE.PUT_LINE(FND_FILE.LOG,sqlcode||' '||sqlerrm);

NULL;

END xxpa_errors;

-- End of the procedure xxpa_errors

　

PROCEDURE XXPA_PROJECT_INTERFACE_P(i_source IN VARCHAR2,i_batch_name IN VARCHAR2) AS
CURSOR cur_line_in(l_org_name VARCHAR2) IS

SELECT DISTINCT line_type
FROM xxpa_project_interface_lines
WHERE process_flag IS NULL;

-- Cursor capturing Projects Information from XXPA_PROJECT_INTERFACE_LINES

-- proj_cur_in

CURSOR cur_proj_in(i_line_type varchar2,l_org_name VARCHAR2) IS
SELECT DISTINCT project_number

,line_type

,project_name

,project_long_name

,project_template

,project_status_code

,project_description

,project_start_date

,project_end_date

,customer_name

,org_name

,customer_relationship_code

FROM xxpa_project_interface_lines

WHERE line_type = i_line_type

-- AND org_name = l_org_name

AND process_flag IS NULL

AND project_number IS NOT NULL;


CURSOR cur_key_members(i_project_number VARCHAR2,
i_line_type VARCHAR2,

i_org_name VARCHAR2)

IS

SELECT distinct employee_number,

key_member_role_type,

key_member_start_date,

key_member_end_date

FROM xxpa_project_interface_lines

WHERE line_type = i_line_type

AND project_number = i_project_number

AND process_flag IS NULL

AND employee_number IS NOT NULL;

cursor cur_class_category(i_project_number VARCHAR2,
i_line_type VARCHAR2,

i_org_name VARCHAR2)

IS

SELECT distinct class_category
,class_code

FROM xxpa_project_interface_lines

WHERE line_type = i_line_type

AND process_flag IS NULL

AND project_number = i_project_number

AND class_code IS NOT NULL;


CURSOR cur_tasks_in(i_project_number varchar2,
i_line_type varchar2,

i_org_name varchar2)

IS

SELECT task_name

,task_number

,task_description

,task_start_date

,task_end_date

,task_security

,task_parent_name

,long_task_name

FROM xxpa_project_interface_lines

WHERE line_type = i_line_type

AND project_number = i_project_number

AND process_flag IS NULL

AND task_number IS NOT NULL

ORDER BY record_number;

-- Declaring Local Variable for API standard variable

l_api_version_number NUMBER := 1.0;
l_commit VARCHAR2(1) := 'F';

l_return_status VARCHAR2(1) := 'F';

l_init_msg_list VARCHAR2(1) := 'T';

l_msg_count NUMBER := 0 ;

l_msg_data VARCHAR2(2000) := NULL;

-- Declaring Tabletype Record Parameters
l_project_in pa_project_pub.project_in_rec_type ; -- record type parameter

l_project_out pa_project_pub.project_out_rec_type ; -- record type

l_key_members pa_project_pub.project_role_tbl_type; -- table type parameter

l_class_categories pa_project_pub.class_category_tbl_type; -- table type

l_tasks_in pa_project_pub.task_in_tbl_type; -- table type

l_tasks_out pa_project_pub.task_out_tbl_type; -- table type parameter

l_line_record NUMBER := 0; -- Variable to hold for Line cursor

l_cur_proj_in NUMBER := 0; -- Variable to hold Project Cursor

l_project_id NUMBER := 0; -- Variable to hold Project Id

l_template_id NUMBER := 0; -- Variable to hold Template

j NUMBER := 0; -- Variable to hold table type record

k NUMBER := 0; -- Variable to hold table type record

l NUMBER := 0; -- Variable to hold table type record

l_person_id VARCHAR2(30) := 0; -- Variable to hold person id

l_task_id NUMBER := 0; -- Variable to hold task id

l_task_reference VARCHAR2(50) := NULL; ---Variable to hold task reference

l_workflow_started VARCHAR2(2) := 0; -- Variable to hold workflow started by

l_source VARCHAR2(30) := 0; -- Variable to hold source (external)

l_err_msg VARCHAR2(30) := 0; -- Variable to hold error message

l_msg NUMBER := 0;

l_project_number VARCHAR2(50) := NULL;

l_org_name VARCHAR2(240) := NULL;

l_project_role VARCHAR2(50) := NULL;

l_customer_id NUMBER := 0;

l_batch_name VARCHAR2(50) := NULL;

l_parent_task_id NUMBER := 0;

l_project_status_code VARCHAR2(50) := NULL;

l_delete_project_id NUMBER := 0;

l_delete_task_id NUMBER := 0;

l_cp_count NUMBER := 0;

l_up_count NUMBER := 0;

l_dp_count NUMBER := 0;

l_dt_count NUMBER := 0;

l_class_count NUMBER := 0;

l_key_count NUMBER := 0;

l_project_role_type VARCHAR2(50) := NULL;

l_car_org_id NUMBER := NULL;

l_emp_count NUMBER := NULL;

l_lookup_code VARCHAR2(30) := NULL;

l_project_type_class_code VARCHAR2(30) := NULL;

BEGIN

-- Validating the Organization name

BEGIN

SELECT name

INTO l_org_name

FROM hr_organization_units

WHERE organization_id = fnd_profile.value('ORG_ID');

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES

('Organization',i_batch_name,i_source,'Organization Not Defined');

WHEN OTHERS THEN

l_err_msg := sqlcode||' '||sqlerrm;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES

('Organization',i_batch_name,i_source,l_err_msg);

END;

FOR l_line_record in cur_line_in(l_org_name) -- Line record cursor

LOOP

IF l_line_record.line_type='CP' THEN

INSERT INTO xxpa_errors(xx_field, batch_name,
source, error_message)

SELECT DISTINCT customer_name

,i_batch_name

,i_source

,'Customer Does Not exist'

FROM xxpa_project_interface_lines

WHERE line_type = 'CP'

AND customer_name NOT IN

(SELECT customer_number

FROM ra_customers);

END IF;

--FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_line_in cursor..');

FOR l_cur_proj_in in cur_proj_in(l_line_record.line_type,l_org_name) -- Projectcursor
LOOP

--FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_proj_in cursor..');

-- Retreving project Id for Update

IF l_line_record.line_type <> 'CP' THEN

BEGIN

SELECT project_id

INTO l_project_id

FROM pa_projects

WHERE segment1 = l_cur_proj_in.project_number;

l_project_in.pa_project_id := l_project_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.project_number,i_batch_name,

l_line_record.line_type, 'Invalid Project');

WHEN OTHERS THEN

l_err_msg := sqlcode||' '||sqlerrm;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.project_number,i_batch_name,

l_line_record.line_type, l_err_msg);

END;

END IF; -- End if of line type = 'CP'

--FND_FILE.PUT_LINE(FND_FILE.LOG,'Before template fetch..');

IF l_line_record.line_type = 'CP' THEN

BEGIN

SELECT project_id

INTO l_template_id

FROM pa_projects

WHERE segment1 = l_cur_proj_in.project_template

AND template_flag = 'Y';

EXCEPTION

WHEN NO_DATA_FOUND THEN

null;

END;

END IF;

IF l_line_record.line_type <> 'DP' THEN

BEGIN
SELECT ppt.project_type_class_code

INTO l_project_type_class_code

FROM pa_project_types ppt,

pa_projects ppa

WHERE ppa.project_type = ppt.project_type

AND ppa.segment1 = l_cur_proj_in.project_template

AND template_flag = 'Y';

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_cur_proj_in.project_template,

l_batch_name,l_line_record.line_type,'Project Type Not Defined in Project Template');

WHEN OTHERS THEN

l_err_msg := sqlcode||' '||sqlerrm||' In Fetching the Project Type from Project Template';

--FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED DURING TEMPLATE FETCH :'||l_err_msg);

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_cur_proj_in.project_template,

i_batch_name,l_line_record.line_type,l_err_msg);

END;

-- end of hanling indirect projects

IF l_project_type_class_code='CONTRACT' THEN

IF l_cur_proj_in.customer_name IS NOT NULL THEN

BEGIN

SELECT customer_id

INTO l_customer_id

FROM ra_customers

WHERE customer_number = l_cur_proj_in.customer_name;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(

l_cur_proj_in.customer_name,l_batch_name,l_line_record.line_type,

'More than one Customer with this Customer Name');

WHEN OTHERS THEN

l_err_msg := sqlcode||' '||sqlerrm;

--FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED DURING CUSTOMER FETCH :'||l_err_msg);

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.customer_name,

i_batch_name,l_line_record.line_type,l_err_msg);

END;

END IF;

BEGIN

SELECT lookup_code

INTO l_lookup_code

FROM FND_lookup_VALUES

WHERE lookup_type = 'CUSTOMER PROJECT RELATIONSHIP'

AND lookup_code = 'PRIMARY'

AND lookup_code = l_cur_proj_in.customer_relationship_code;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(

l_cur_proj_in.customer_name,l_batch_name,l_line_record.line_type,

'Relationship Code Not Defined for this Customer');

WHEN OTHERS THEN

l_err_msg := sqlcode||' '||sqlerrm||' - Error in Retreiving the Relationship Code for this Customer';

--FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED DURING CUSTOMER FETCH :'||l_err_msg);

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.customer_name,

i_batch_name,l_line_record.line_type,l_err_msg);

END;

-- ELSIF l_project_type_class_code = 'INDIRECT' THEN

END IF; -- end of l_project_type_class_code='CONTRACT'

IF l_cur_proj_in.project_status_code IS NOT NULL THEN

BEGIN

SELECT project_status_code

INTO l_project_status_code

FROM pa_project_statuses

WHERE project_status_name = l_cur_proj_in.project_status_code

AND status_type = 'PROJECT';

EXCEPTION

WHEN TOO_MANY_ROWS THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(

l_cur_proj_in.project_status_code,i_batch_name,l_line_record.line_type,

'More Than One Project Status code Exists..');

WHEN OTHERS THEN

l_err_msg := sqlcode||' '||sqlerrm;

--FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Occurred during Project Status Code Fetch :

--'||l_err_msg);

INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.project_status_code,

i_batch_name,l_line_record.line_type,l_err_msg);

END;

END IF;

END IF;

IF l_line_record.line_type NOT IN ('DP','DT','UP') AND l_cur_proj_in.project_Start_date IS NULL THEN

INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.project_start_date,

i_batch_name,i_source,'Project Start Date cannot be Null');

END IF;

IF (l_cur_proj_in.project_start_date > l_cur_proj_in.project_end_date) THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES l_cur_proj_in.project_number,i_batch_name,

l_line_record.line_type, 'Project Start Date should not be Greater than Project End Date');

END IF;

　
BEGIN

SELECT a.organization_id

INTO l_car_org_id

FROM hr_organization_information a,

hr_all_organization_units c,

pa_lookups b

WHERE a.org_information1 = b.LOOKUP_CODE

AND b.lookup_type='ALL_HIERARCHY_CLASS'

AND a.organization_id = c.organization_id

and c.name = l_cur_proj_in.org_name

and b.lookup_code = 'PA_PROJECT_ORG';

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.org_name,

i_batch_name,i_source,'Project Owning Organization Not Defined...');

WHEN OTHERS THEN

l_err_msg:='Error in Selecting the Project Owning Organization - '||sqlerrm;

INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)

VALUES(l_cur_proj_in.org_name,

i_batch_name,i_source,l_err_msg);

END;

　

l_project_in.pm_project_reference := l_cur_proj_in.project_number;

l_project_in.pa_project_number := l_cur_proj_in.project_number;

l_project_in.project_name := l_cur_proj_in.project_name;

l_project_in.created_from_project_id := l_template_id;

l_project_in.project_status_code := l_project_status_code;

l_project_in.Description := l_cur_proj_in.project_description;

l_project_in.long_name := l_cur_proj_in.project_long_name;

l_project_in.start_date := l_cur_proj_in.project_start_date;

l_project_in.completion_date := l_cur_proj_in.project_end_date;

l_project_in.carrying_out_organization_id := l_car_org_id;

　

IF l_project_type_class_code = 'CONTRACT' THEN

l_project_in.customer_id := l_customer_id;

l_project_in.project_relationship_code := l_cur_proj_in.customer_relationship_code;

ELSIF l_project_type_class_code = 'INDIRECT' THEN

l_project_in.customer_id := NULL;

l_project_in.project_relationship_code := NULL;

END IF;

　

-- Assiging key members information for table type

j:=0;

--FND_FILE.PUT_LINE(FND_FILE.LOG,'Before key members in cur..');

FOR cur_key_members_in in cur_key_members(l_cur_proj_in.project_number,

l_line_record.line_type,

l_org_name)

LOOP

j:= j+ 1;

-- Retreving Person id from per_people_f

BEGIN

SELECT person_id

INTO l_person_id

FROM per_people_f

WHERE employee_number = cur_key_members_in.employee_number

AND employee_number is NOT NULL

AND Business_group_id IN (SELECT business_group_id

FROM hr_organization_units

WHERE organization_id = FND_PROFILE.VALUE('ORG_ID'))

AND SYSDATE BETWEEN NVL(EFFECTIVE_START_DATE,SYSDATE)

AND NVL(EFFECTIVE_END_DATE,SYSDATE);

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.employee_number,i_batch_name,

l_line_record.line_type, 'Invalid Employee Number');

WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.employee_number,i_batch_name,

l_line_record.line_type, l_err_msg);

END;

SELECT COUNT()
INTO l_key_count

FROM pa_project_players a,

pa_project_role_types b

WHERE a.project_id = l_template_id

AND b.meaning = cur_key_members_in.key_member_role_type

AND a.project_role_type = b.project_role_type

AND A.project_role_type = 'PROJECT MANAGER' ;

IF cur_key_members_in.key_member_role_type='Project Manager' THEN

IF l_key_count<>0 THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(cur_key_members_in.employee_number,i_batch_name,l_line_record.line_type, 'Project Manager Already defined at the Project Template level');

END IF;

END IF;

SELECT COUNT()
INTO l_key_count

FROM pa_project_players a,

pa_project_role_types b

WHERE a.project_id = l_template_id

AND b.meaning = cur_key_members_in.key_member_role_type

AND a.project_role_type = b.project_role_type

AND A.project_role_type <> 'PROJECT MANAGER'

AND a.person_id = l_person_id;

IF cur_key_members_in.key_member_role_type<>'Project Manager' THEN

IF l_key_count<>0 THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)

VALUES(cur_key_members_in.employee_number,i_batch_name,l_line_record.line_type, 'Key Member with this role already defined at the Project Template level');

END IF;

END IF;

COMMIT;

BEGIN

SELECT project_role_type
INTO l_project_role_type

FROM pa_project_role_types

WHERE meaning = cur_key_members_in.key_member_role_type;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.key_member_role_type,i_batch_name,

l_line_record.line_type, 'Key member role type not defined..');

WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.key_member_role_type,i_batch_name,

l_line_record.line_type, l_err_msg);

END;

COMMIT;

-- Assiging Key members

l_key_members(j).person_id := l_person_id;
l_key_members(j).start_date := cur_key_members_in.key_member_start_date;

l_key_members(j).end_date := cur_key_members_in.key_member_end_date;

l_key_members(j).project_role_type := l_project_role_type;

END LOOP; --end Key members loop

-- Capturing Class Categories

K:= 0;

FOR cur_class_category_in in cur_class_category(l_cur_proj_in.project_number,
l_line_record.line_type,

l_org_name)

LOOP
k:= k+ 1;

COMMIT;

SELECT COUNT()
INTO l_class_count

FROM pa_project_classes

WHERE project_id = l_template_id

AND class_category = cur_class_category_in.class_category

AND class_code = cur_class_category_in.class_code;

IF l_class_count<>0 THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)

VALUES(cur_class_category_in.class_category,i_batch_name,l_line_record.line_type, 'Class with the given Category already defined at the template level');

END IF;

commit;

l_class_categories(k).class_category := cur_class_category_in.class_category;
l_class_categories(k).class_code := cur_class_category_in.class_code;

l_class_categories(k).code_percentage := NULL;

END LOOP; --End of category loop

l:= 0;

--FND_FILE.PUT_LINE(FND_FILE.LOG,'Before tasks cursor.');

FOR tasks_cur in cur_tasks_in (L_cur_proj_in.PROJECT_NUMBER,
L_LINE_RECORD.LINE_TYPE,

l_org_name)

LOOP

l:= l+1;

   COMMIT;

BEGIN

SELECT task_id,pm_task_reference
INTO l_task_id,l_task_reference

FROM pa_tasks

WHERE task_number =tasks_cur.task_parent_name

AND project_id = l_project_id;

EXCEPTION

WHEN OTHERS THEN

l_task_id:=NULL;

l_task_reference:=NULL;

END;

IF l_task_id IS NOT NULL THEN
l_tasks_in(l).pm_task_reference:= tasks_cur.task_number;

l_tasks_in(l).pa_parent_task_id:=l_task_id;

l_tasks_in(l).pa_task_number := tasks_cur.task_number;

l_tasks_in(l).task_name := tasks_cur.task_name;

l_tasks_in(l).task_description := tasks_cur.task_description;

l_tasks_in(l).task_start_date := tasks_cur.task_start_date ;

l_tasks_in(l).task_completion_date := tasks_cur.task_end_date;

l_tasks_in(l).attribute1 := tasks_cur.task_security;

l_tasks_in(l).long_task_name := tasks_cur.long_task_name;

END IF;

END LOOP; --tasks end loop

COMMIT;

BEGIN

SELECT count(error_message)

INTO l_msg

FROM xxpa_errors

where batch_name = i_batch_name;

--FND_FILE.PUT_LINE(FND_FILE.LOG,'error messages :'||l_msg);

IF l_msg = 0 THEN

-- For creating projects using project API

IF l_line_record.line_type = 'CP' THEN

-- Create Projects

--FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling created project API....');

pa_project_pub.create_project

(p_api_version_number=>l_api_version_number,

p_commit =>l_commit,

p_init_msg_list =>l_init_msg_list,

p_msg_count =>l_msg_count,

p_msg_data =>l_msg_data,

p_return_status =>l_return_status,

p_workflow_started =>l_workflow_started,

p_pm_product_code =>i_source,

p_project_in =>l_project_in,

p_project_out =>l_project_out,

p_key_members =>l_key_members,

p_class_categories =>l_class_categories,

p_tasks_in =>l_tasks_in,

p_tasks_out =>l_tasks_out

);

--FND_FILE.PUT_LINE(FND_FILE.LOG,'RETURN_STATUS:'||l_return_status||'l_msg_count :'||l_msg_count);

IF l_return_status='S' THEN

l_cp_count:=l_cp_count+1;

END IF;

--For updating projects

ELSIF l_line_record.line_type = 'UP' THEN

--FND_FILE.PUT_LINE(FND_FILE.LOG,'before calling update_project API');

pa_project_pub.update_project

( p_api_version_number =>l_api_version_number,

p_commit =>l_commit,

p_init_msg_list =>l_init_msg_list,

p_msg_count =>l_msg_count,

p_msg_data =>l_msg_data,

p_return_status =>l_return_status,

p_workflow_started =>l_workflow_started,

p_pm_product_code =>i_source,

p_project_in =>l_project_in,

p_project_out =>l_project_out,

p_key_members =>l_key_members,

p_class_categories =>l_class_categories,

p_tasks_in =>l_tasks_in,

p_tasks_out =>l_tasks_out

);

IF l_return_status='S' THEN

l_up_count:=l_up_count+1;

END IF;

-- For Deleting project

ELSIF l_line_record.line_type = 'DP' THEN

pa_project_pub.delete_project

(p_api_version_number =>l_api_version_number,

p_init_msg_list =>l_init_msg_list,

p_commit =>l_commit,

p_msg_count =>l_msg_count,

p_msg_data =>l_msg_data,

p_return_status =>l_return_status,

p_pm_product_code =>i_source,

p_pa_project_id =>l_project_id

);

IF l_return_status='S' THEN

l_dp_count:=l_dp_count+1;

END IF;

ELSIF l_line_record.line_type = 'DT' THEN

FOR i IN 1..l LOOP

BEGIN

SELECT task_id

INTO l_task_id

FROM pa_tasks

WHERE task_number = l_tasks_in(i).pa_task_number

AND project_id = l_project_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_tasks_in(i).pa_task_number,i_batch_name,

l_line_record.line_type, 'Invalid Task Number');

WHEN OTHERS THEN

l_err_msg := sqlcode||' '||sqlerrm;

INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_tasks_in(i).pa_task_number,i_batch_name,

l_line_record.line_type, l_err_msg);

END;

COMMIT;

SELECT COUNT()
INTO l_msg

FROM xxpa_errors

WHERE batch_name = i_batch_name;

IF l_msg=0 THEN

pa_project_pub.delete_task

(p_api_version_number =>l_api_version_number,

p_init_msg_list =>l_init_msg_list,

p_commit =>l_commit,

p_msg_count =>l_msg_count,

p_msg_data =>l_msg_data,

p_return_status =>l_return_status,

p_pm_product_code =>i_source,

p_pa_project_id =>l_project_id,

p_pa_task_id =>l_task_id, --(i).pm_task_reference,

p_project_id =>l_delete_project_id,

p_task_id =>l_delete_task_id

);

IF l_return_status='S' THEN

l_dt_count:=l_dt_count+1;

END IF;

END IF;

END LOOP;

END IF;

END IF; -- END for no errors

END; -- End of the API

xxpa_errors(i_batch_name,l_line_record.line_type,

l_cur_proj_in.project_number,l_msg_data,

l_msg_count,l_return_status);

COMMIT;

END LOOP; -- END of Project Cursor

END LOOP; -- ENd of the Line type cursor

FND_FILE.NEW_LINE(FND_FILE.OUTPUT,4);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Projects Interface Report');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ------------------------------');

FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2);

EXCEPTION

WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,sqlcode||' '||sqlerrm);

---NULL;

END XXPA_PROJECT_INTERFACE_P;

-- End of the Procedure

END XXPA_PROJECT_INTERFACE; -- End of the Package

Project Accounting APIs...

APIs Packages

Package Name: Create Agreement

Internal Name: PA_AGREEMENT_PUB

Description: This package contains the public AMG APIs for agreement and Funding procedures that will be used by the external system

APIs (Functions and Procedures):

Name

Internal Name

Description

Create Multiple Project Agreements - Clear

CLEAR_AGREEMENT

This API clears the globals that were set up during initialization In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Project Agreements - Execute Create

EXECUTE_CREATE_AGREEMENT

This API creates an agreement with the funding using the data stored in the global tables during the load phase In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Project Agreements-Initialize

INIT_AGREEMENT

This API sets the global tables used by load-execute-fetch procedures that create a new agreement or update an existing agreement In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Project Agreements-Load

LOAD_AGREEMENT

This API loads an agreement to a PL/SQL record In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Project Fundings - Fetch

FETCH_FUNDING

This API gets the return status that was returned during creation of funds and stored in a global PL/SQL table In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Project Fundings-load

LOAD_FUNDING

This API loads funding to a PL/SQL table In order to execute this API the following list of API's should be executed in the order of sequence.

Create Project Agreement

CREATE_AGREEMENT

This API creates an agreement with associated funds from an external system

Create Project Funding

ADD_FUNDING

This API adds funding to an agreement

Delete Project Agreement

DELETE_AGREEMENT

This API deletes an agreement with associated funds from an external system

Delete Project Funding

DELETE_FUNDING

This API deletes a fund from an agreement

Update Multiple Project Agreements

EXECUTE_UPDATE_AGREEMENT

This API updates an agreement with the funding using the data stored in the global tables during the load phase In order to execute this API the following list of API's should be executed in the order of sequence.

Update Project Agreement

UPDATE_AGREEMENT

This API updates an agreement and associated funds

Update Project Funding

UPDATE_FUNDING

This API updates a fund for an agreement

Validate Project Agreement Deletion

CHECK_DELETE_AGREEMENT_OK

This API checks whether an agreement can be deleted.

Validate Project Funding Creation

CHECK_ADD_FUNDING_OK

This API checks whether a fund can be added.

Validate Project Funding Deletion

CHECK_DELETE_FUNDING_OK

This API checks whether a fund can be deleted

Validate Project Funding Update

CHECK_UPDATE_FUNDING_OK

This API checks whether a fund can be added.

Package Name: Create Budget

Internal Name: PA_BUDGET_PUB

Description: This package contains the public AMG APIs for Budgets that will be used by the external system for preparing budget and use the API's to interface the budget and budget line into Oracle Projects.

APIs (Functions and Procedures):

Name

Internal Name

Description

Create Budget Line

ADD_BUDGET_LINE

This API is used to add a budget line to a working budget in Oracle Projects for a given project and budget type.

Create Draft Budget

CREATE_DRAFT_BUDGET

This API is used to create a draft budget and its budget lines in Oracle Projects for a given project, using a selected budget type and budget entry method.

Create Multiple Budgets- Load Budget Line

LOAD_BUDGET_LINE

This API is used to load a budget line to a global PL/SQL table.

Create Multiple Budgets-Clear

CLEAR_BUDGET

This API is used clear the global data structures set up during the Initialize step.

Create Multiple Budgets-Create Draft Budget

EXECUTE_CREATE_DRAFT_BUDGET

This API is used to create a budget and its budget lines using the data stored in the global tables during the Load process.

Create Multiple Budgets-Execute Calculate Amounts

EXECUTE_CALCULATE_AMOUNTS

This API is used to calculate the raw cost, burdened cost, and revenue amounts using existing budget lines for a given project and budget type In order to execute this API, the following list of API's should be executed in order of sequence.

Create Multiple Budgets-Fetch Calculate Amounts

FETCH_CALCULATE_AMOUNTS

This API is used to get the raw cost, burdened cost, and revenue amounts by budget line from global records updated by the API In order to execute this API, the following list of API's should be executed in order of sequence.

Create Multiple Budgets-Initialize

INIT_BUDGET

This API is used to set up the global data structures that other Load Execute Fetch procedures use to create a new or update an existing draft budget in Oracle Projects In order to execute this API, the following list of API's should be

Create Multiple Budgets-Initialize Calculate Amounts

INIT_CALCULATE_AMOUNTS

This API issued to set up the global data structures used by the Load Execute Fetch API CALCULATE_AMOUNTS In order to execute this API, the following list of API's should be executed in order of sequence.

Create Multiple Budgets-Load Fetch Line

FETCH_BUDGET_LINE

This API is used to retrieve the return status returned during the creation of a budget line from a global PL/SQL table.

Delete Budget Line

DELETE_BUDGET_LINE

This API is used to delete a budget line from a working budget in Oracle Projects for a given project and budget type.

Delete Draft Budget

DELETE_DRAFT_BUDGET

This API is used to delete a working budget in Oracle Projects for a given project and budget type.

Fetch Calculate Amounts

FETCH_CALCULATE_AMOUNTS

This API is used to get the raw cost, burdened cost, and revenue amounts by budget line from global records updated by the API

Get Budget Amounts

CALCULATE_AMOUNTS

Using the PA_CLIENT_EXTN_BUDGET extension, you can use the public API CALCULATE_AMOUNTS to recalculate raw cost, burdened cost, and revenue amounts for existing budget lines.

Update Budget

UPDATE_BUDGET

This API is used to update the working budget with its budget lines in Oracle Projects for a given project.

Update Budget Line

UPDATE_BUDGET_LINE

This API is used to update an existing budget line of a working budget in Oracle Projects for a given project and budget type.

Update Multiple Budgets

EXECUTE_UPDATE_BUDGET

This API is used to update a budget and its budget lines using the data stored in the global tables during the Load process.

Verify Budget Amounts

BASELINE_BUDGET

This API is used to baseline an existing budget in Oracle Projects for a given project and budget type

Package Name:

Internal Name: PA_RESOURCE_PUB

Description: This package contains the public AMG APIs that will be used to export the resource lists and the resources they include to the Oracle Projects. Oracle Projects updates its resource information accordingly.

APIs (Functions and Procedures):

Name

Internal Name

Description

Create Multiple Resource Lists-Clear

CLEAR_CREATE_RESOURCE_LIST

This API is used to clear the global data structures set up during the Initialize step.

Create Multiple Resource Lists-Execute Create

EXEC_CREATE_RESOURCE_LIST

This API is used to execute the composite API CREATE_RESOURCE_LIST In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Resource Lists-Fetch

FETCH_RESOURCE_LIST

This API is used to fetch one resource list identifier at a time from the global output structure for resource lists.

Create Multiple Resource Lists-Initialize

INIT_CREATE_RESOURCE_LIST

This API is used to set up the global data structures used by other Load Execute Fetch procedures.

Create Multiple Resource Lists-Load

LOAD_RESOURCE_LIST

This API is used to load the resource list global input structure.

Create Resource List

CREATE_RESOURCE_LIST

This API creates a resource list and optionally creates the resource list members.

Create Resource List Member

ADD_RESOURCE_LIST_MEMBER

This API adds a resource member to an existing resource list

Delete Resource List

DELETE_RESOURCE_LIST

This API Deletes a resource member from an existing resource list

Delete Resource List Member

DELETE_RESOURCE_LIST_MEMBER

This API deletes a given resource list member

Update Multiple Members-Load

LOAD_MEMBERS

This API is procedure used to load the resource list member global input structure.

Update Multiple Members-Clear

CLEAR_UPDATE_MEMBERS

This API is used to clear the global data structures that were set up during the Initialize step for the Load Execute Fetch update APIs.

Update Multiple Members-Fetch

FETCH_MEMBERS

This API is used to fetch resource members from the global output structure for resource list members.

Update Multiple Members-Initialize

INIT_UPDATE_MEMBERS

This API is used to set up the global data structures used by other Load Execute Fetch procedures.

Update Multiple Members-Sort

SORT_RESOURCE_LIST_MEMBERS

This API updates the sort order for resource members in a given resource list

Update Multiple Resource Lists

EXEC_UPDATE_RESOURCE_LIST

This API is used to execute the composite API UPDATE_RESOURCE_LIST.

Update Resource List

UPDATE_RESOURCE_LIST

This API is used to update an existing resource list, including updating existing or adding new resource list members.

Update Resource List Member

UPDATE_RESOURCE_LIST_MEMBER

This API updates the alias and enables or disables the resource list members.

Package Name: Project Pub

Internal Name: PA_PROJECT_PUB

Description: This package contains the public AMG APIs for project and task information

APIs (Functions and Procedures):

Name

Internal Name

Description

Create Multiple Projects-Clear Project

CLEAR_PROJECT

This API procedure is used to clear the global structures setup during the load process In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Projects-Execute Create Project

EXECUTE_CREATE_PROJECT

This Load Execute Fetch API is used to create a project and its tasks using the data stored in the global tables during the Load process In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Projects-Execute Update Project

EXECUTE_UPDATE_PROJECT

This API procedure is used to update an existing project, including changing or adding project data, adding new tasks, and updating existing tasks.

Create Multiple Projects-Init Project

INIT_PROJECT

This API procedure is used to the global data structures. Other Load-Execute-Fetch use the structures to create a new project in Oracle Projects In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Projects-Load Class Category

LOAD_CLASS_CATEGORY

This Load Execute Fetch API is used to load class categories to a global PL/SQL table.

Create Multiple Projects-Load Key Member

LOAD_KEY_MEMBER

This Load Execute Fetch API is used to use to load key members to a global PL/SQL table.

Create Multiple Projects-Load Project

LOAD_PROJECT

This API is used to use to load a project to a global PL/SQL record.

Create Multiple Tasks-Fetch

FETCH_TASK

This Load Execute Fetch API is used to fetch output parameters related to tasks.

Create Multiple Tasks-Load

LOAD_TASK

This API is used to load a task to a global PL/SQL table.

Create Project

CREATE_PROJECT

This API creates a project in the Oracle Projects using a Template or existing project.

Create Task

ADD_TASK

This API is used to add new subtasks to a task of a project in Oracle Projects.

Delete Project

DELETE_PROJECT

This API procedure is used to delete a project and its tasks from Oracle Projects

Delete Task

DELETE_TASK

This API is used to delete tasks of a project in Oracle Projects

Update Task

UPDATE_TASK

This API is used to update existing tasks of a project in Oracle Projects.

Update project

UPDATE_PROJECT

This API procedure pushes project and task information from your external system to Oracle Projects to reflect any changes you have made in the external system

Validate Project Deletion

CHECK_DELETE_PROJECT_OK

This API is used to determine if you can delete a project.

Validate Project Organization Change

CHECK_CHANGE_PROJECT_ORG_OK

This API is used to determine if you can change the CARRYING_OUT_ORGANIZATION_ID field for a particular project or task.

Verify Parent Change

CHECK_CHANGE_PARENT_OK

This API is used to determine if you can move a task from one parent task to another.

Verify Project Reference Uniqueness

CHECK_UNIQUE_PROJECT_REFERENCE

This API is used to determine if a new or changed project reference (PM_PROJECT_REFERENCE) is unique.

Verify Subtask Addition

CHECK_ADD_SUBTASK_OK

This API is used to determine if a subtask can be added to a parent task.

Verify Task Deletion

CHECK_DELETE_TASK_OK

This API is used to determine if you can delete a task.

Verify Task Number Change

CHECK_TASK_NUMBER_CHANGE_OK

This API is used to determine if you can change a tasks number.

Verify Task Number Uniqueness

CHECK_UNIQUE_TASK_NUMBER

This API is used to determine if a new or changed task number is unique within a project.

Verify Task Reference Uniqueness

CHECK_UNIQUE_TASK_REFERENCE

This API is used to determine if a new or changed task reference (PM_TASK_REFERENCE) is unique.

Package Name: Project Billing Events

Internal Name: PA_EVENT_PUB

Description: This package contains the public AMG APIs which provide an open interface for external systems to insert, update and delete events.

APIs (Functions and Procedures):

Name

Internal Name

Description

Create Billing Event

CREATE_EVENT

This API creates an event or a set of events.

Create Multiple Billing Events – Clear

CLEAR_EVENT

This API clears the globals that were set up during initialization.

Create Multiple Billing Events - Execute Create

EXECUTE_CREATE_EVENT

This API creates an event using the data which is stored in the global tables during the Load phase In order to execute this API the following list of API's should be executed in the order of sequence.

Create Multiple Billing Events – Fetch

FETCH_EVENT

This API gets the return_status that was returned during creation of an event and stored in a global PL/SQL table.

Create Multiple Billing Events – Load

LOAD_EVENT

This API loads an event to a PL/SQL record.

Create Multiple Billing Events-Initialize

INIT_EVENT

This API sets the global tables used by the Load Execute Fetch procedures that create a new event or update an existing event.

Delete Billing Event

DELETE_EVENT

This API deletes an event.

Update Billing Event

UPDATE_EVENT

This API updates an event or set of events.

Update Multiple Billing Events-Update

EXECUTE_UPDATE_EVENT

This API updates event data using the information stored in the global tables during the Load phase.

Validate Billing Event Deletion

CHECK_DELETE_EVENT_OK

This API checks whether an event can be deleted.

Open Interfaces

Name

Internal Name

Description

PRC: Interface Assets to Oracle Assets

PAXCPCAL

The Interface Assets process sends valid asset lines to Oracle Assets to become fixed assets. The process creates one mass addition line in Oracle Assets for each asset line in Oracle Projects, assigning the asset information you entered for the CIP asset to the mass addition line in Oracle Assets. Interface the costs to General Ledger before you run the Interface Assets process.

PRC: Interface Cross Charge Distributions to General Ledger

PACCGLTR

The process identifies the cross charged transactions that fit the parameters you specify and then interfaces the cross charged distributions to General Ledger

PRC: Interface Expense Reports from Payables

PAAPIMP

This process gets expense report information from Payables. This process creates pre- approved expense report batches from expense report information entered in Self-Service Expenses or in the invoices window (in payables).

PRC: Interface Expense Reports to Payables

PATTER

The Interface Expense Reports to Payables process collects all eligible expense reports, including adjustments, in Oracle Projects and interfaces them to the Oracle Payables interface tables.

PRC: Interface Intercompany Invoices to Receivables

PATTAR-IC

This process collects all eligible intercompany invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables.

PRC: Interface Invoices to Receivables

PATTAR

This process collects all eligible draft invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables. The process also maintains the project balances of unbilled receivable and unearned revenue and creates accounting transactions for these amounts.

PRC: Interface Labor Costs to General Ledger

PAGGLT

This process collects all eligible labor costs in Oracle Projects and interfaces them to the Oracle General Ledger interface tables. The interface process also determines the liability account for the labor costs.

PRC: Interface Revenue to General Ledger

PATTGL

This process collects all eligible revenue in Oracle Projects and interfaces it to the Oracle General Ledger interface tables. This process also maintains project balances for unbilled receivables and unearned revenue and creates accounting transactions for these amounts.

PRC: Interface Supplier Costs

PAAPIMP_SI

The PRC: Interface Supplier Invoices from Payables process retrieves the following items and interface them to Oracle Projects: All eligible posted, project related supplier invoices from Oracle Payables, Tax lines for project related intercompany invoices. The process first populates the Transaction Import Interface table, creating a cost distributed expenditure item and cost distribution line for each invoice distribution line, and expenditure for each invoice.

PRC: Interface Supplier Invoice Adjustment Costs to Payables

PAVTVC

This process collects all eligible supplier invoice adjustment costs in Oracle Projects and interfaces them to Oracle Payables. The process determines the liability account for the supplier invoice costs.

PRC: Interface Total Burdened Cost to GL

PACTFTBC

This process collects all eligible total burdened distribution lines in Oracle Projects and interfaces them to Oracle General Ledger.

PRC: Interface Usage and Miscellaneous Costs to General Ledger

PASGLT

This process collects all eligible cost distribution lines of the following transactions in Oracle Projects and interfaces them to the Oracle General Ledger interface tables: usage costs, miscellaneous transaction costs, burden transaction costs, and Inventory and WIP transactions not already costed or accounted. The interface process also determines the liability account for these costs.

PRC: Transaction Import

PAXTRTRX

Transaction Import is an open interface that enables you to load transactions from external cost collection systems into Oracle Projects. Transaction Import creates pre-approved expenditure items from transaction data entered in external cost collection systems.

Script To find Oracle API's for any module

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;

Project template creation in PA using standard API.

pa_proj_template_setup_pub.create_project_template to create Project template in Project Accounts

DECLARE

l_template_id NUMBER;

l_return_status VARCHAR2 (2);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (250);

l_msg_index_out NUMBER := 0;

l_msg_data1 VARCHAR2 (2000);

BEGIN

mo_global.init ('PA');

pa_proj_template_setup_pub.create_project_template

(p_api_version => 1,

p_init_msg_list => 'T',

p_commit => 'F',

p_validate_only => 'T',

p_validation_level => 1,

p_calling_module => 'SELF_SERVICE',

p_debug_mode => 'N',

p_max_msg_count => 1,

p_project_number => 'Cost',

p_project_name => 'Cost',

p_project_type => 'Construction',

p_organization_id => 246,

p_organization_name => 'VisionCorporation',

p_effective_from_date => '23-may-2011',

p_effective_to_date => '24-may-2011',

p_description => 'JUNK_CHARS',

p_security_level => 0,

p_long_name => NULL,

p_operating_unit_id => 202,

x_template_id => l_template_id,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data

);

DBMS_OUTPUT.put_line ('l_return_statusd: ' || l_return_status);

DBMS_OUTPUT.put_line ('l_msg_data: ' || l_msg_data);

DBMS_OUTPUT.put_line ('l_msg_count :' || l_msg_count);

DBMS_OUTPUT.put_line ('Template_id: ' || l_template_id);

IF (l_return_status = 'E' AND l_msg_count > 0)

THEN

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

fnd_msg_pub.get (p_msg_index => i,

p_data => l_msg_data,

p_encoded => 'F',

p_msg_index_out => l_msg_index_out

);

l_msg_data1 := l_msg_data1 || ' ' || l_msg_data;

DBMS_OUTPUT.put_line ('Value is' || l_msg_data1);

END LOOP;

END IF;

END;

Project Accounting Interfaces

PRC: Interface Assets to Oracle Assets (PAXCPCAL)
The Interface Assets process sends valid asset lines to Oracle Assets to become fixed assets. The process creates one mass addition line in Oracle Assets for each asset line in Oracle Projects, assigning the asset information you entered for the CIP asset to the mass addition line in Oracle Assets. Interface the costs to General Ledger before you run the Interface Assets process.

PRC: Interface Cross Charge Distributions to General Ledger (PACCGLTR)
The process identifies the cross charged transactions that fit the parameters you specify and then interfaces the cross charged distributions to General Ledger.

PRC: Interface Expense Reports from Payables(PAAPIMP)
This process gets expense report information from Payables. This process creates pre- approved expense report batches from expense report information entered in Self-Service Expenses or in the invoices window (in payables).

PRC: Interface Expense Reports to Payables (PATTER)
The Interface Expense Reports to Payables process collects all eligible expense reports, including adjustments, in Oracle Projects and interfaces them to the Oracle Payables interface tables.

PRC: Interface Intercompany Invoices to Receivables (PATTAR-IC)
This process collects all eligible intercompany invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables.

PRC: Interface Invoices to Receivables (PATTAR)
This process collects all eligible draft invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables. The process also maintains the project balances of unbilled receivable and unearned revenue and creates accounting transactions for these amounts.

PRC: Interface Labor Costs to General Ledger (PAGGLT)
This process collects all eligible labor costs in Oracle Projects and interfaces them to the Oracle General Ledger interface tables. The interface process also determines the liability account for the labor costs.

PRC: Interface Revenue to General Ledger (PATTGL)
This process collects all eligible revenue in Oracle Projects and interfaces it to the Oracle General Ledger interface tables. This process also maintains project balances for unbilled receivables and unearned revenue and creates accounting transactions for these amounts.
PRC: Interface Supplier Costs (PAAPIMP_SI)

The PRC: Interface Supplier Invoices from Payables process retrieves the following items and interface them to Oracle Projects: All eligible posted, project related supplier invoices from Oracle Payables, Tax lines for project related intercompany invoices. The process first populates the Transaction Import Interface table, creating a cost distributed expenditure item and cost distribution line for each invoice distribution line, and expenditure for each invoice.

PRC: Interface Supplier Invoice Adjustment Costs to Payables (PAVTVC)
This process collects all eligible supplier invoice adjustment costs in Oracle Projects and interfaces them to Oracle Payables. The process determines the liability account for the supplier invoice costs.
PRC: Interface Total Burdened Cost to GL PACTFTBC This process collects all eligible total burdened distribution lines in Oracle Projects and interfaces them to Oracle General Ledger.

PRC: Interface Usage and Miscellaneous Costs to General Ledger (PASGLT) This process collects all eligible cost distribution lines of the following transactions in Oracle Projects and interfaces them to the Oracle General Ledger interface tables: usage costs, miscellaneous transaction costs, burden transaction costs, and Inventory and WIP transactions not already costed or accounted. The interface process also determines the liability account for these costs.

PRC: Transaction Import PAXTRTRX Transaction Import
This is an open interface that enables you to load transactions from external cost collection systems into Oracle Projects. Transaction Import creates pre-approved expenditure items from transaction data entered in external cost collection systems.

Create Event in Oracle Projects - pa_event_pub.create_event

API to Create Event in Oracle Projects:

create or replace procedure xxpa_create_project_event(errbuf in varchar2,retcode in number)

l_msg_count NUMBER;

l_msg_data varchar2(2000);

l_return_status VARCHAR2(10);

l_event_in_tbl PA_EVENT_PUB.Event_In_Tbl_Type;

l_event_out_tbl PA_EVENT_PUB.Event_out_Tbl_Type;

ln_line_no number;

cursor cur is

select * from XXPA_CREATE_PRO_EVENT ;

begin

for i in cur

loop

ln_line_no := NVL(ln_line_no,0) + 1;

l_event_in_tbl (ln_line_no).p_pm_event_reference := i.project_number ;

l_event_in_tbl (ln_line_no).p_task_number := NULL;

l_event_in_tbl (ln_line_no).p_event_type :=

'Data Migration';

l_event_in_tbl (ln_line_no).p_description :=

'Data Migration';

l_event_in_tbl (ln_line_no).p_completion_date :=

to_date('31-DEC-2008');

l_event_in_tbl (ln_line_no).p_project_number :=

i.project_number;

l_event_in_tbl (ln_line_no).p_organization_name :=

'Eros Electricals';

l_event_in_tbl (ln_line_no).p_bill_trans_bill_amount := I.bill_amount;

l_event_in_tbl (ln_line_no).p_bill_trans_rev_amount := I.revenue_amount;

l_event_in_tbl (ln_line_no).p_bill_trans_currency_code :=

'AED';

end loop;

pa_event_pub.create_event (p_api_version_number => 1.0,

p_commit => fnd_api.g_false,

p_init_msg_list => 'T',

p_pm_product_code => 'XXXX',

p_event_in_tbl => l_event_in_tbl,

p_event_out_tbl => l_event_out_tbl,

p_msg_count => l_msg_count,

p_msg_data => l_msg_data,

p_return_status => l_return_status

);

Fnd_File.put_line(Fnd_File.LOG, 'Error Message: '||l_msg_data);

dbms_output.put_line(l_msg_data);

commit;

end;

Oracle Projects API (PA_PROJECT_PUB) and Function Security

A customer wanted to work with project module APIs to perform standard project tasks (create and update projects, create and update project tasks, add key members to projects, etc).

Oracle provides a number of APIs within the pa_project_pub API for just that purpose, those include:

pa_project_pub.create_project
pa_project_pub.update_project
pa_project_pub.create_task
pa_project_pub.update_task

These APIs are actually pretty straightforward and when I tested them in my system, they worked great. When my customer ran these APIs, he received the following error:

PA_PA_FUNCTION_SECURITY_ENFORCED_--Your current responsibility does not have access to this function

We were running the API in API Wizard (an Excel software product which works dynamically with Oracle EBS APIs) but we also tested at the command line and received the same issue. In both cases, we were running following initialization:

    fnd_global.apps_initialize (
        user_id         => 19616,
        resp_id         => 52896,
        resp_appl_id    => 275
        );

This responsibility (resp_id 52896) had the ability to create and update project tasks via the Oracle forms. It took as a while to figure this out but the issue was that the customer had created a custom menu for projects that did not include the Activity Management Gateway functions, such as:

PA_PM_CREATE_PROJECT - Activity Management Gateway:Create Project
PA_PM_UPDATE_PROJECT - Activity Management Gateway:Update Project
PA_PM_ADD_TASK       - Activity Management Gateway:Add Task
PA_PM_UPDATE_TASK    - Activity Management Gateway:Update Task

So we went to the Menus form (System Administrator > Application > Menu) and added them. Viola! Everything worked.

I verified that this is the same in R11 and R12.

While our issue was due to custom responsibility with a custom menu, not all of the delivered responsibilities using the standard menus have access to these functions, for example the Project Manager Super User does have access by default while the Project Costing Super User does not. So you can definitely run into this issue using a seeded responsibility. The key is making sure that the functions listed above are available.

Another thing to note is that if you use a responsibility based on a menu that has these functions, you must also ensure that there none of these are part of a function exclusion for the target responsibility.

A last note, Projects is the only module I have seen so far where access to be able to use APIs is restricted by a function assigned (or in this case unassigned or excluded) to a menu. If you've run into this in any other modules, please let me know!

Key Tables in Oracle Projects

Here is a brief description of the key tables in Oracle Projects.

Table	Description
PA_PROJECTS_ALL	It stores the highest units of work defined in Oracle Projects.
PA_PROJECT_ASSETS_ALL	It contains assets information defined for capital projects.
PA_PROJECT_ASSIGNMENTS	It stores details of all Assignments for a project.
PA_PROJECT_CLASSES	It contains the class codes of class categories that are used to classify projects.
PA_PROJECT_ROLE_TYPES	Implementation-defined responsibilities or positions assigned to employees on projects are stored here.
PA_PROJECT_STATUSES	It stores valid project status codes.
PA_PROJECT_TYPES_ALL	It stores implementation-defined project classifications that supply default information and drive some project processing.
PA_TASKS	It contains user-defined subdivisions of project work.
PA_TASK_TYPES	It stores implementation-defined classifications of task.
PA_TRANSACTION_INTERFACE_ALL	It is an interface table to import transactions from external sources into Oracle Projects.
PA_TRANSACTION_SOURCES	It stores implementation-defined sources of imported transactions originating in an external system.
PA_IMPLEMENTATIONS_ALL	It contains information about the configuration of an Oracle Projects installation.
PA_ACTION_SETS	It stores action set templates as well as action sets belonging to an object, such as projects, requirements, etc.
PA_ACTION_SET_LINES	It stores action set lines that belong to an action set or an action set template.
PA_ACTION_SET_TYPES	It stores attributes of action set types.
PA_AGREEMENTS_ALL	It has customer contracts that serve as the basis for work authorization.
PA_AGREEMENT_TYPES	Implementation-defined classifications of customer agreements.
PA_BILL_RATES_ALL	Information about bill rates and markups of standard bill rate schedules.
PA_BUDGETS	It stores budgets information.
PA_BUDGET_LINES	It stores detail lines of project and task budgets.
PA_BUDGET_TYPES	It contains implementation-defined classifications of types of budgets used for different business purposes.
PA_CLASS_CATEGORIES	It stores implementation-defined categories for classifying projects.
PA_CLASS_CODES	It stores implementation-defined values within class categories that can be used to classify projects.
PA_EVENTS	It stores entries assigned to tasks that generate revenue and/or billing but are not directly related to expenditure items.
PA_EVENT_TYPES	It stores implementation-defined classifications of events.
PA_EXPENDITURES_ALL	Groups of expenditure items incurred by employees or organizations for an expenditure period.
PA_EXPENDITURE_CATEGORIES	Implementation-defined groupings of expenditure types by type of cost.
PA_EXPENDITURE_ITEMS_ALL	It contains the smallest units of expenditure charged to projects and tasks.
PA_EXPENDITURE_TYPES	Implementation-defined classifications of expenditures charged to projects and tasks.
PA_PERIODS_ALL	Implementation-defined periods against which project performance is measured.
PA_RBS_DENORM	This table stores normalized resource breakdown structure information.
PA_RBS_ELEMENTS	This table stores the RBS element information and the parent-child relationship.
PA_RESOURCES	It contains resources used in budgeting and project summary amounts.
PA_ROLE_LISTS	It stores lists of roles defined with the system.
PA_SCHEDULES	It displays the schedule details for requirements and assignments. It also displays calendar schedules.

PA ALL TABLES

PA_PROJECTS_ALL: Information about projects

PA_AGREEMENTS_ALL: Customer contracts that serve as the basis for work authorization

PA_BILL_RATES_ALL: Information about bill rates and markups of standard bill rate schedules
PA_BILLING_ASSIGNMENTS_ALL: Assignments of billing extensions to a project type, project, or task
PA_COST_DISTRIBUTION_LINES_ALL: Information about expenditure item cost distribution
PA_CUST_REV_DIST_LINES_ALL: Information about expenditure item revenue distribution

PA_DRAFT_INVOICE_DETAILS_ALL: Inter company invoice details for cross charged transactions

PA_DRAFT_INVOICES_ALL: Information about draft invoices generated for projects

PA_DRAFT_REVENUES_ALL: Information about draft revenue generated for projects

PA_EXPENDITURE_COST_RATES_ALL: Cost rates for non-labor expenditure types

PA_EXPENDITURE_GROUPS_ALL: Groups of pre-approved expenditures

PA_EXPENDITURE_ITEMS_ALL: The smallest units of expenditure charged to projects and tasks

PA_EXPENDITURES_ALL: Groups of expenditure items incurred by employees or organizations for an expenditure period

PA_PERIODS_ALL: Implementation-defined periods against which project performance is measured

PA_PROJECT_ASSET_LINES_ALL: Summarized project CIP costs

PA_PROJECT_ASSETS_ALL: Assets defined for capital projects

PA_EXPENDITURE_TYPES: Implementation-defined classifications of expenditures charged to projects and tasks

PA_EXPENDITURE_CATEGORIES: Implementation-defined groupings of expenditure types by type of cost

PA_CLASS_CATEGORIES: Implementation-defined categories for classifying projects

PA_CLASS_CODES: Implementation-defined values within class categories that can be used to classify projects

PA_PROJECT_CLASSES: Class codes of class categories that are used to classify projects

PA_BUDGET_VERSIONS: Versions of project budgets

PA_BUDGET_LINES: Detail lines of project and task budgets

CREATE OR REPLACE VIEW DISC_PROJECT_PROG_MAP
(PROJECT_ID, ACCOUNT_VALUE, "INDIAN PROJECTS")
AS
SELECT PPA1.PROJECT_ID, PPA1.SEGMENT1 "ACCOUNT_VALUE", PPA2.SEGMENT1 "INDIAN PROJECTS"
FROM PA_TASKS PT, PA_PROJECTS_ALL PPA1,PA_PROJECTS_ALL PPA2, PA_PROJECT_CUSTOMERS PPC
,PA_PROJECT_TYPES_ALL PPT
WHERE PT.TASK_ID = PPC.RECEIVER_TASK_ID
AND PT.PROJECT_ID = PPA1.PROJECT_ID
AND PPT.PROJECT_TYPE = PPA2.PROJECT_TYPE
AND PPC.PROJECT_ID=PPA2.PROJECT_ID
AND PPT.PROJECT_TYPE_CLASS_CODE = 'CONTRACT'
AND PPA2.PROJECT_STATUS_CODE ='APPROVED'
AND PPA1.TEMPLATE_FLAG='N'
UNION
SELECT PPA.PROJECT_ID, PPA.SEGMENT1, PPA.SEGMENT1
FROM PA_PROJECTS_ALL PPA, PA_PROJECT_TYPES_ALL PPT
WHERE PPT.PROJECT_TYPE = PPA.PROJECT_TYPE
AND PPT.PROJECT_TYPE_CLASS_CODE <> 'CONTRACT'
AND PPA.TEMPLATE_FLAG='N'
UNION
SELECT PPA1.PROJECT_ID, PPA1.SEGMENT1 "ACCOUNT_VALUE", PPA1.SEGMENT1 "INDIAN PROJECTS"
FROM PA_PROJECTS_ALL PPA1, PA_PROJECT_CUSTOMERS PPC
,PA_PROJECT_TYPES_ALL PPT
WHERE PPT.PROJECT_TYPE = PPA1.PROJECT_TYPE
AND PPC.PROJECT_ID=PPA1.PROJECT_ID
AND PPT.PROJECT_TYPE_CLASS_CODE = 'CONTRACT'
AND PPC.BILL_ANOTHER_PROJECT_FLAG='N'
AND PPA1.PROJECT_STATUS_CODE ='APPROVED'
AND PPA1.TEMPLATE_FLAG='N'
/

SELECT ppa.project_id, ppa.NAME "PROJECT NAME",
ppa.long_name "PROJECT ALIAS",
ppa.description "PROJECT DESCRIPTION", ppa.start_date "START DATE",
ppa.completion_date "END DATE",
prc.customer_name "PRIMARY CUSTOMER",
prc1.customer_name "SECONDARY CUSTOMER", hou.NAME "BUSINESS UNIT",
LOB.class_code "LINE OF BUSINESS", pra.class_code "PRACTICE",
prloc.class_code "PROGRAM LOCATION",
so.class_code "SERVICE OFFERING", bm.class_code "BUSINESS MODEL",
sb.class_code "SETUP BILLING", 0 "PARENT PROJECT ID",
ppa.segment1 "PROJECT NUMBER", '0' "TASK NUMBER",
ppa.project_type "PROJECT TYPE", pm."PROJECT MANAGER", pgm."PROGRAM MANAGER", vpp."VICE PRESIDENT",
prc.project_relationship_code "PARENT RELATIONSHIP"
FROM pa_projects_all ppa,
hr_all_organization_units hou,
pa_project_customers_v prc,
disc_pa_sec_customers prc1,
pa_project_classes_v LOB,
pa_project_classes_v pra,
pa_project_classes_v prloc,
pa_project_classes_v so,
pa_project_classes_v sb,
disc_pa_biz_model bm,
--PA_PROJECT_CLASSES_V BIL,
disc_pa_pm pm,
disc_pa_pgm pgm,
disc_pa_vpp vpp
WHERE ppa.carrying_out_organization_id = hou.organization_id
AND ppa.template_flag != 'Y'
AND ppa.project_id = prc.project_id(+)
AND ppa.project_id = prc1.project_id(+)
AND ppa.project_id = LOB.project_id(+)
AND LOB.class_category = 'LINE OF BUSINESS'
AND ppa.project_id = pra.project_id(+)
AND pra.class_category = 'PRACTICE'
AND ppa.project_id = prloc.project_id(+)
AND prloc.class_category = 'PROGRAM LOCATION'
AND ppa.project_id = so.project_id(+)
AND so.class_category = 'SERVICE OFFERING'
AND ppa.project_id = sb.project_id(+)
AND sb.class_category = 'SETUP BILLING'
AND ppa.project_id = bm.project_id(+)
--AND BM.CLASS_CATEGORY = 'BUSINESS MODEL'
--AND PPA.PROJECT_ID = BIL.PROJECT_ID (+) AND BIL.CLASS_CATEGORY (+) =
--'FINAL PROJECTS FOR GO LIVE' AND BIL.CLASS_CODE = 'YES'
AND ppa.project_id = pm.project_id(+)
-- AND UPPER (prm.ROLE) = 'PROJECT MANAGER'
--AND UPPER(PPA.PROJECT_TYPE) LIKE 'PROGRAM%'
--AND PRC.PROJECT_RELATIONSHIP_CODE IN ('PARENT','INTERNAL');
AND ppa.project_id = pgm.project_id(+)
-- AND UPPER (pm.ROLE) = 'PROGRAM MANAGER'
AND ppa.project_id = vpp.project_id(+)
-- AND UPPER (vpp.ROLE) = 'VICE PRESIDENT-PROGRAM'
-- and ppa.segment1 = '10242'
/

PA Task Master

CREATE OR REPLACE VIEW DISC_PROJ_TASK_MASTER
(PROJECT_ID, PROJECT_NUMBER, PROJECT_NAME, TASK_ID, TASK_NUMBER,
TASK_NAME, TASK_SERVICE, TOP_TASK_ID, TOP_TASK_NUMBER, TOP_TASK_NAME,
TOP_TASK_SERVICE, SETUP_BILLING, EXECUTION_START_DATE)
AS
SELECT ppa.project_id, ppa.segment1 project_number, ppa.NAME project_name,
pt.task_id, pt.task_number, pt.task_name,
pt.service_type_code task_service, ptop.task_id top_task_id,
ptop.task_number top_task_number, ptop.task_name top_task_name,
--PTOP.START_DATE,
ptop.service_type_code top_task_service,
sb.class_code setup_billing, pexc.start_date execution_start_date
--PT.ATTRIBUTE1
FROM pa_projects_all ppa,
pa_tasks pt,
pa_tasks ptop,
pa_tasks pexc,
pa_project_classes_v sb
WHERE ppa.project_id = pt.project_id
--AND PT.BILLABLE_FLAG='Y'
AND ppa.template_flag = 'N'
AND pt.top_task_id <> pt.task_id
--AND UPPER(PPA.PROJECT_TYPE)='PROGRAM-US'
AND ptop.task_id = pt.top_task_id
AND ptop.top_task_id = ptop.task_id
AND ppa.project_id = sb.project_id(+)
AND sb.class_category = 'SETUP BILLING'
AND pexc.project_id = ppa.project_id
AND pexc.service_type_code = 'EXECUTION PHASE'
AND pexc.top_task_id = pexc.task_id
--AND PEXC.TASK_NUMBER = '2.0'
--and PPA.SEGMENT1 = '10189';
/

Sample Ad-Hoc SQL Queries for Oracle Projects

Labor Billing Rates

Find labor bill rates used to calculate T&M labor revenue for the existing labor transactions (timecards).

4 comments:

janarthananDecember 20, 2017 at 7:26 AM
Too good
UnknownMay 24, 2018 at 9:35 PM
Thanks for your information sharing,good information on your site.
Oracle Project Accounting Training in Ameerpet, Hyderabad
SUNLIFE_V_SMarch 23, 2019 at 2:45 PM
Can any comments be shared on how resources belonging to particular OU be leveraged for Projects across OU’s, in a 12.2.1 Oracle multi-.org environment? We see challenges reported by Project resources when they try to submit Time Cards (via OTL) when error like ‘missing Project Task/Expenditure .Org. ‘ shows up.
kavyaMay 12, 2021 at 12:05 AM
Thanks for sharing, it was informative. We play a small role in upskilling people providing the latest tech courses. Join us to upgradeORACLE PROJECT ACCOUNTING ONLINE TRAINING

RAVADA OA Blog

Total Pageviews

Saturday, April 25, 2015

Oracle Project Technical

4. SQL script to assist in the identification of Projects revenue issues

5. Oracle Projects Billing Interface with Oracle Receivables

Integrated project with payables

Project and task creation api in project accounting