Oralce fusion Query to Get Project Details for Subledger Tables

 PA - project revenue

exception
querySELECT pcdl.prvdr_gl_period_name          ei_latest_gl_period_name,
       gcc.segment1                       r_company,
       peia.revenue_recognized_flag       ei_revenue_distributed_flag,
       ppat.NAME                          ei_project_name,
       ppab.segment1                      ei_project_number,
       pet.expenditure_type_name          ei_expenditure_type,
       pec.expenditure_category_name      ei_expenditure_category,
       ppn.full_name                      ei_employee_name,
       Trunc (peia.expenditure_item_date) ei_expenditure_item_date,
       peia.receipt_currency_code         ei_receipt_currency_code,
       peia.project_currency_code         ei_project_currency_code,
       hr.NAME                            ei_expenditure_org_name,
       op.NAME                            ei_prvdr_org_name,
       hr1.NAME                           project_owning_org,
       CASE
              WHEN peia.bill_trans_curr_rev_amt = 0 THEN 'N'
              ELSE 'Y'
       END billed_flag,
       ptv.task_number,
       (
              SELECT message_text
              FROM   fnd_messages
              WHERE  message_name = pe.error_code) rejection_code,
       pcdl.project_raw_cost                       ei_project_raw_revenue,
       prd.project_curr_revenue_amt                r_project_revenue_amount,
       pcdl.quantity                               ei_quantity,
       prd.bill_rate                               ei_bill_rate,
       peia.expenditure_item_id                    transaction_number,
       peia.billable_flag,
       (
              SELECT flv.meaning
              FROM   fnd_lookup_values flv
              WHERE  flv.lookup_type = 'PJB_EVT_INVOICED_FLAG'
              AND    lookup_code = peia.invoiced_flag) invoice_status,
       (
              SELECT flv1.meaning
              FROM   fnd_lookup_values flv1
              WHERE  flv1.lookup_type = 'PJB_EVT_REVENUE_RECOGNZD'
              AND    flv1.lookup_code = peia.revenue_recognized_flag) revenue_status,
       (
              SELECT pj.NAME
              FROM   per_jobs pj
              WHERE  pj.job_id = peia.person_job_id) employee_job,
       peia.unit_of_measure
FROM   pjc_exp_items_all peia,
       pjf_exp_types_vl pet,
       pjf_tasks_v ptv,
       pjf_projects_all_b ppab,
       pjf_projects_all_tl ppat,
       hr_all_organization_units hr,
       hr_all_organization_units hr1,
       hr_operating_units op,
       pjc_cost_dist_lines_all pcdl,
       pjf_exp_categories_tl pec,
       gl_code_combinations gcc,
       per_person_names_f ppn,
       pjb_rev_distributions prd,
       pjb_errors pe,
       xla_distribution_links xda,
       xla_ae_lines xal
WHERE  pet.expenditure_type_id = peia.expenditure_type_id
AND    peia.revenue_exception_flag = 'E'
AND    pcdl.acct_source_code <> 'AP_INV'
AND    peia.task_id = ptv.task_id
AND    peia.project_id = ppab.project_id
AND    ppab.project_id = ppat.project_id
AND    Nvl (peia.override_to_organization_id, peia.incurred_by_organization_id) = hr.organization_id
AND    hr1.organization_id = ppab.carrying_out_organization_id
AND    peia.org_id = op.organization_id
AND    peia.expenditure_item_id = pcdl.expenditure_item_id
AND    pcdl.line_num_reversed IS NULL
AND    pcdl.reversed_flag IS NULL
AND    prd.line_num_reversed IS NULL
AND    prd.reversed_flag IS NULL
AND    pcdl.acct_event_id = xda.event_id(+)
AND    pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
AND    pcdl.line_num = xda.source_distribution_id_num_2(+)
AND    (
              xda.rounding_class_code NOT LIKE '%CLEARING'
       OR     xda.rounding_class_code IS NULL )
AND    xda.ae_header_id = xal.ae_header_id(+)
AND    xda.ae_line_num = xal.ae_line_num(+)
AND    gcc.code_combination_id = nvl (nvl (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid), xal.code_combination_id )
AND    pec.expenditure_category_id = pet.expenditure_category_id
AND    ppn.person_id = peia.incurred_by_person_id
AND    sysdate BETWEEN nvl (ppn.effective_start_date, sysdate - 1) AND    nvl (ppn.effective_end_date, sysdate + 1)
AND    ppn.name_type = 'GLOBAL'
AND    peia.expenditure_item_id = prd.transaction_id(+)
AND    pe.expenditure_item_id = peia.expenditure_item_id
AND    ppab.project_status_code = 'ACTIVE'
AND    EXISTS
       (
              SELECT 1
              FROM   okc_k_headers_all_b okh
              WHERE  okh.id = pe.contract_id
              AND    okh.sts_code = 'ACTIVE')
AND    nvl (net_zero_adjustment_flag, 'N') = 'N'
AND    peia.revenue_recognized_flag IN ('P',
                                        'U')
AND    peia.billable_flag = 'Y'
AND    pe.request_id IN
       (
              SELECT max (pe1.request_id)
              FROM   pjb_errors pe1
              WHERE  pe1.expenditure_item_id = pe.expenditure_item_id
              AND    pe1.erroring_process = 'REVENUE_GEN')
UNION ALL
SELECT pcdl.prvdr_gl_period_name          ei_latest_gl_period_name,
       gcc.segment1                       r_company,
       peia.revenue_recognized_flag       ei_revenue_distributed_flag,
       ppat.NAME                          ei_project_name,
       ppab.segment1                      ei_project_number,
       pet.expenditure_type_name          ei_expenditure_type,
       pec.expenditure_category_name      ei_expenditure_category,
       ppn.full_name                      ei_employee_name,
       trunc (peia.expenditure_item_date) ei_expenditure_item_date,
       peia.receipt_currency_code         ei_receipt_currency_code,
       peia.project_currency_code         ei_project_currency_code,
       hr.NAME                            ei_expenditure_org_name,
       op.NAME                            ei_prvdr_org_name,
       hr1.NAME                           project_owning_org,
       CASE
              WHEN peia.bill_trans_curr_rev_amt = 0 THEN 'N'
              ELSE 'Y'
       END billed_flag,
       ptv.task_number,
       (
              SELECT message_text
              FROM   fnd_messages
              WHERE  message_name = pe.error_code) rejection_code,
       pcdl.project_raw_cost                       ei_project_raw_revenue,
       --peia.project_curr_rev_amt
       prd.project_curr_revenue_amt r_project_revenue_amount,
       pcdl.quantity                ei_quantity,
       prd.bill_rate                ei_bill_rate,
       peia.expenditure_item_id     transaction_number,
       peia.billable_flag,
       (
              SELECT flv.meaning
              FROM   fnd_lookup_values flv
              WHERE  flv.lookup_type = 'PJB_EVT_INVOICED_FLAG'
              AND    lookup_code = peia.invoiced_flag) invoice_status,
       (
              SELECT flv1.meaning
              FROM   fnd_lookup_values flv1
              WHERE  flv1.lookup_type = 'PJB_EVT_REVENUE_RECOGNZD'
              AND    flv1.lookup_code = peia.revenue_recognized_flag) revenue_status,
       (
              SELECT pj.NAME
              FROM   per_jobs pj
              WHERE  pj.job_id = peia.person_job_id) employee_job,
       peia.unit_of_measure
FROM   pjc_exp_items_all peia,
       pjf_exp_types_vl pet,
       pjf_tasks_v ptv,
       pjf_projects_all_b ppab,
       pjf_projects_all_tl ppat,
       hr_all_organization_units hr,
       hr_all_organization_units hr1,
       hr_operating_units op,
       pjc_cost_dist_lines_all pcdl,
       pjf_exp_categories_tl pec,
       gl_code_combinations gcc,
       per_person_names_f ppn,
       pjb_rev_distributions prd,
       pjb_errors pe,
       xla_distribution_links xda,
       xla_ae_lines xal,
       ap_invoice_distributions_all apd
WHERE  pet.expenditure_type_id = peia.expenditure_type_id
AND    peia.revenue_exception_flag = 'E'
AND    apd.invoice_distribution_id = peia.original_dist_id
AND    pcdl.acct_source_code = 'AP_INV'
AND    peia.task_id = ptv.task_id
AND    peia.project_id = ppab.project_id
AND    ppab.project_id = ppat.project_id
AND    nvl (peia.override_to_organization_id, peia.incurred_by_organization_id) = hr.organization_id
AND    hr1.organization_id = ppab.carrying_out_organization_id
AND    peia.org_id = op.organization_id
AND    peia.expenditure_item_id = pcdl.expenditure_item_id
AND    pcdl.line_num_reversed IS NULL
AND    pcdl.reversed_flag IS NULL
AND    prd.line_num_reversed IS NULL
AND    prd.reversed_flag IS NULL
AND    pcdl.acct_event_id = xda.event_id(+)
AND    pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
AND    pcdl.line_num = xda.source_distribution_id_num_2(+)
AND    (
              xda.rounding_class_code NOT LIKE '%CLEARING'
       OR     xda.rounding_class_code IS NULL )
AND    xda.ae_header_id = xal.ae_header_id(+)
AND    xda.ae_line_num = xal.ae_line_num(+)
AND    gcc.code_combination_id = nvl (apd.dist_code_combination_id, xal.code_combination_id)
AND    pec.expenditure_category_id = pet.expenditure_category_id
AND    ppn.person_id = peia.incurred_by_person_id
AND    sysdate BETWEEN nvl (ppn.effective_start_date, sysdate - 1) AND    nvl (ppn.effective_end_date, sysdate + 1)
AND    ppn.name_type = 'GLOBAL'
AND    peia.expenditure_item_id = prd.transaction_id(+)
AND    pe.expenditure_item_id = peia.expenditure_item_id
AND    ppab.project_status_code = 'ACTIVE'
AND    EXISTS
       (
              SELECT 1
              FROM   okc_k_headers_all_b okh
              WHERE  okh.id = pe.contract_id
              AND    okh.sts_code = 'ACTIVE')
AND    nvl (net_zero_adjustment_flag, 'N') = 'N'
AND    peia.revenue_recognized_flag IN ('P',
                                        'U')
AND    peia.billable_flag = 'Y'
AND    pe.request_id IN
       (
              SELECT max (pe1.request_id)
              FROM   pjb_errors pe1
              WHERE  pe1.expenditure_item_id = pe.expenditure_item_id
              AND    pe1.erroring_process = 'REVENUE_GEN')

Query to get Oracle Receivables Details

 /*Query to get Oracle Receivables Details

==========================================*/

SELECT rcta.trx_number "INVOICE NUMBER",

rctta.name "TRANSACTION TYPE",

rcta.trx_date "INVOICE DATE",

apsa.due_date "DUE DATE",

apsa.invoice_currency_code "CURRENCY",

apsa.amount_due_original "AMOUNT",

apsa.amount_due_remaining "AMOUNT DUE",

hp_b.party_name "BILL TO CUSTOMER NAME",

hcsua_b.site_use_id "BILL TO SITE USE ID",

hl_b.address1,

hl_b.address2,

hl_b.address3,

hl_b.address4,

hl_b.city,

hl_b.state,

hl_b.postal_code,

hp_s.party_name "SHIP TO CUSTOMER NAME",

hcsua_s.site_use_id "SHIP TO SITE USE ID",

hl_s.address1,

hl_s.address2,

hl_s.address3,

hl_s.address4,

hl_s.city,

hl_s.state,

hl_s.postal_code

FROM RA_TERMS RT,

RA_CUSTOMER_TRX_ALL RCTA,

--RA_CUSTOMER_TRX_LINES_ALL RCTLA,

RA_CUST_TRX_TYPES_ALL RCTTA,

HZ_CUST_ACCOUNTS_ALL HCAA,

HZ_CUST_ACCT_SITES_ALL HCASA_S,

HZ_CUST_SITE_USES_ALL HCSUA_S,

HZ_PARTIES HP_S,

HZ_PARTY_SITES HPS_S,

HZ_LOCATIONS HL_S,

HZ_CUST_ACCT_SITES_ALL HCASA_B,

HZ_CUST_SITE_USES_ALL HCSUA_B,

HZ_PARTIES HP_B,

HZ_PARTY_SITES HPS_B,

HZ_LOCATIONS HL_B,

AR_PAYMENT_SCHEDULES_ALL APSA,

HR_LEGAL_ENTITIES HLE

WHERE --RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID

RCTA.CUST_TRX_TYPE_ID = RCTTA.CUST_TRX_TYPE_ID

AND RCTA.BILL_TO_CUSTOMER_ID = HCAA.CUST_ACCOUNT_ID

AND RCTA.ORG_ID = RCTTA.ORG_ID

AND APSA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

AND APSA.CUST_TRX_TYPE_ID = RCTTA.CUST_TRX_TYPE_ID

AND RCTA.TERM_ID = RT.TERM_ID(+)

AND HCAA.PARTY_ID = HP_S.PARTY_ID

AND HCAA.PARTY_ID = HP_B.PARTY_ID

--SHIP TO ADDRESS LINKS

AND RCTA.SHIP_TO_SITE_USE_ID = HCSUA_S.SITE_USE_ID

AND HCSUA_S.CUST_ACCT_SITE_ID = HCASA_S.CUST_ACCT_SITE_ID

AND HCASA_S.PARTY_SITE_ID = HPS_S.PARTY_SITE_ID

AND HPS_S.PARTY_ID = HP_S.PARTY_ID

AND HPS_S.LOCATION_ID = HL_S.LOCATION_ID

--------BILL TO ADDRESS LINKS

AND RCTA.BILL_TO_SITE_USE_ID = HCSUA_B.SITE_USE_ID

AND HCSUA_B.CUST_ACCT_SITE_ID = HCASA_B.CUST_ACCT_SITE_ID

AND HCASA_B.PARTY_SITE_ID = HPS_B.PARTY_SITE_ID

AND HPS_B.PARTY_ID = HP_B.PARTY_ID

AND HPS_B.LOCATION_ID = HL_B.LOCATION_ID

AND HLE.ORGANIZATION_ID = RCTA.LEGAL_ENTITY_ID

AND RCTA.TRX_NUMBER ='12149'

Dispute Notification Work Flow tables in Oracle fusion

 select

-- task summary :

b.TASK_NUMBER,

t.TASK_TITLE,

b.INITIATED_BY,

b.INITIATED_DATE,

b.COMPLETED_BY,

b.COMPLETED_DATE,

b.STATUS_CODE,

b.OUTCOME_CODE,

hb.version,

hb.from_user

from FND_BPM_TASK_B b, FND_BPM_TASK_TL t,

FND_BPM_TASK_HISTORY_B hb

where b.domain = 'ICDomain'

--and b.task_number = 200381

and b.task_definition_name = 'DisputeNotificationTask'

and b.task_id = t.task_id

and t.language = USERENV('LANG')

and t.task_id=hb.task_id

order by b.task_number,hb.version desc

Oracle Fusion Projects and Tasks Details

 SELECT TO_CHAR(PPA.PROJECT_ID) PROJECT_ID

,TO_CHAR(PPA.SEGMENT1) PROJECT_NUMBER

,PPA.NAME PROJECT_NAME

,PPA.DESCRIPTION

,PPA.START_DATE

,PPA.COMPLETION_DATE

,PPA.CLOSED_DATE

,PTV.TASK_NUMBER

,TO_CHAR(PTV.TASK_ID) TASK_ID

,TO_CHAR(PPA.CARRYING_OUT_ORGANIZATION_ID) ORGANIZATION_ID

,HRU.NAME ORGANIZATION_NAME

,PPT.PROJECT_TYPE PROJECT_TYPE

,PPA.GROUP_SPACE_TEMPLATE_NAME

,PPA.PM_PROJECT_REFERENCE

,XLE.NAME "LEGAL ENTITY"

,PPA.ACTUAL_START_DATE "PROJECT START DATE"

,PPA.ACTUAL_FINISH_DATE "PROJECT FINISH DATE"

,PPA.PROJECT_STATUS_CODE

,PTV.PM_TASK_REFERENCE "SOURCE TASK REFERENCE"

,PTV.DESCRIPTION "TASK DESCRIPTION"

,PTV1.TASK_NUMBER "PARENT TASK NUMBER"

,PTC.TXN_CTRL_REFERENCE

,PTC.CHARGEABLE_FLAG

,PTC.BILLABLE_FLAG

,PTC.CAPITALIZABLE_FLAG

,PET.EXPENDITURE_TYPE_NAME

,PEC.EXPENDITURE_CATEGORY_NAME

,PCT.CLASS_CODE

,PCC.CLASS_CATEGORY

FROM

PJF_PROJECTS_ALL_VL PPA

,PJF_TASKS_V PTV

,PJF_TASKS_V PTV1

,HR_ORGANIZATION_UNITS HRU

,PJF_PROJECT_TYPES_TL PPT

,XLE_ENTITY_PROFILES XLE

,PJC_TRANSACTION_CONTROLS PTC

,PJF_EXP_TYPES_TL PET

,PJF_EXP_CATEGORIES_TL PEC

,PJF_CLASS_CATEGORIES_TL PCC

,PJF_CLASS_CODES_TL PCT

,PJF_PROJECT_CLASSES PPC

WHERE 1=1

AND PPA.PROJECT_ID=PTV.PROJECT_ID

AND PTV1.PARENT_TASK_ID(+) = PTV.TASK_ID

AND PPA.CARRYING_OUT_ORGANIZATION_ID=HRU.ORGANIZATION_ID(+)

AND PPT.PROJECT_TYPE_ID=PPA.PROJECT_TYPE_ID

AND PPA.SEGMENT1='158901-230'

AND XLE.LEGAL_ENTITY_ID = PPA.LEGAL_ENTITY_ID

AND PPA.PROJECT_ID=PTC.PROJECT_ID(+)

AND PTC.TASK_ID(+) = PTV.TASK_ID

AND PET.EXPENDITURE_TYPE_ID = PTC.EXPENDITURE_TYPE_ID(+)

AND PET.LANGUAGE ='US'

AND NVL(PEC.LANGUAGE,'US') ='US'

AND PTC.EXPENDITURE_CATEGORY_ID =  PEC.EXPENDITURE_CATEGORY_ID(+)

AND PPC.CLASS_CATEGORY_ID  = PCC.CLASS_CATEGORY_ID(+)

AND PPC.PROJECT_ID(+) = PPA.PROJECT_ID 

AND PPC.CLASS_CODE_ID = PCT.CLASS_CODE_ID(+)

ORDER BY TO_CHAR(PPA.PROJECT_ID)