--**** Project Not Accruing Revenue Due to Expenditure Exception ****--
SELECT PE.accounting_period AS GL_PERIOD
,OKH.contract_number
,OKL.line_number
,PPA.segment1 AS project_number
,PPA.name AS project_name
,PT.task_number
,PT.task_name
,PEI.expenditure_item_id trx_number
,PEC.expenditure_category_name
,NVL(PPN.full_name, PS.vendor_name) AS full_name
,NVL(PPF.person_number, PS.segment1) AS person_number
,DECODE(PEI.incurred_by_person_id, NULL, 'Supplier'
,(SELECT FL.meaning FROM fnd_lookups FL
WHERE FL.lookup_type = 'PJC_PERSON_TYPE'
AND FL.lookup_code = PEI.person_type)
) AS PERSON_TYPE
,TRUNC(PEI.expenditure_item_date) expenditure_item_date
,DECODE(PEC.expenditure_category_name, 'Expenses', NULL, 'Material', NULL,
ROUND((PE.unrec_rev_amount/((PEI.quantity/100) * (100 - NVL(PEI.revenue_recog_percentage, 0)))), 2)) AS BILL_RATE
,(SELECT FM.message_text FROM fnd_messages FM
WHERE FM.message_name = PE.error_code) AS EXCEPTION
,ROUND(((PEI.quantity * DECODE(PEI.denom_currency_code, PE.currency_code, 1
,(SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type = 'Corporate'
AND gdr.to_currency = PE.currency_code
AND gdr.from_currency = PEI.denom_currency_code
AND gdr.conversion_date = PEI.expenditure_item_date))
)/100) * ROUND(100 - NVL(PEI.revenue_recog_percentage, 0), 2)
,2 ) AS Quantity
,PE.currency_code
,PE.unrec_rev_amount AS amount
,HAOU1.name exp_organization_name
,HAOU3.name contract_org
FROM (SELECT PER.expenditure_item_id, PER.contract_id, PER.contract_line_id, PER.major_version
,PER.currency_code, PER.unrec_rev_amount, PER.error_code, PER.accounting_period, PER.error_id
FROM pjb_errors PER
WHERE PER.erroring_process = 'REVENUE_GEN'
AND PER.transaction_type_code = 'EI'
AND PER.billing_type_code = 'EX'
AND PER.unrec_rev_amount <> 0
AND PER.txn_date <= :P_AS_OF_DATE
) PE
,pjc_exp_items_all PEI
,pjf_projects_all_vl PPA
,pjf_tasks_v PT
,okc_k_headers_all_b OKH
,okc_k_lines_b OKL
,pjf_txn_sources_vl PTS
,hr_operating_units HOU
,pjf_exp_types_vl PET
,pjf_exp_categories_vl PEC
,per_person_names_f PPN
,per_all_people_f PPF
,poz_suppliers_v PS
,hr_all_organization_units HAOU1
,hr_all_organization_units HAOU3
WHERE PE.expenditure_item_id = PEI.expenditure_item_id
AND PEI.project_id = PPA.project_id
AND PPA.project_status_code <> 'CLOSED' --> Other than closed Projects
AND PEI.task_id = PT.task_id
AND PE.contract_id = OKH.id
AND PE.major_version = OKH.major_version
AND :P_AS_OF_DATE BETWEEN OKH.start_date AND NVL(OKH.end_date, SYSDATE)
AND PE.contract_line_id = OKL.id
AND PE.major_version = OKL.major_version
AND :P_AS_OF_DATE BETWEEN OKL.start_date AND NVL(OKL.end_date, SYSDATE)
AND OKH.sts_code <> 'EXPIRED' --> Other than closed Contracts
AND NVL(PEI.revenue_recognized_flag, 'X') <> 'F' --> Not Fully Recognized
AND NVL(PEI.billable_flag, 'X') = 'Y' --> Only Billable Transactions
AND PEI.expenditure_item_date <= :P_AS_OF_DATE
AND PEI.transaction_source_id = PTS.transaction_source_id
AND OKH.org_id = HOU.organization_id
AND PEI.expenditure_type_id = PET.expenditure_type_id
AND PET.expenditure_category_id = PEC.expenditure_category_id
AND PEI.incurred_by_person_id = PPN.person_id (+)
AND PEI.incurred_by_person_id = PPF.person_id (+)
AND PPN.name_type (+) = 'GLOBAL'
AND PEI.expenditure_item_date BETWEEN NVL(PPN.effective_start_date, PEI.expenditure_item_date) AND NVL (PPN.effective_end_date, PEI.expenditure_item_date)
AND PEI.expenditure_item_date BETWEEN NVL(PPF.effective_start_date, PEI.expenditure_item_date) AND NVL (PPF.effective_end_date, PEI.expenditure_item_date)
AND PEI.vendor_id = PS.vendor_id (+)
AND PEI.expenditure_organization_id = HAOU1.organization_id
AND OKH.owning_org_id = HAOU3.organization_id
AND (PE.error_id, OKH.major_version) IN (SELECT MAX(PE2.error_id),MAX(PE2.major_version) FROM pjb_errors PE2
WHERE PE2.expenditure_item_id = PE.expenditure_item_id
AND PE2.erroring_process = 'REVENUE_GEN'
AND PE2.transaction_type_code = 'EI'
AND PE2.billing_type_code = 'EX'
AND PE2.unrec_rev_amount <> 0
AND PE2.txn_date <= :P_AS_OF_DATE
)
--**** Project Not Accruing Revenue Due to Event Exception ****--
SELECT (SELECT PERIOD_NAME
FROM gl_periods GP
,gl_ledgers GL
WHERE GP.period_set_name = GL.period_set_name
AND GL.ledger_id = HOU.set_of_books_id
AND GP.adjustment_period_flag = 'N'
AND PEI.completion_date BETWEEN GP.start_date AND GP.end_date) AS GL_PERIOD
,HOU.name
,OKH.contract_number
,OKL.line_number
,PPA.segment1 AS project_number
,PPA.name AS project_name
,PT.task_number
,PT.task_name
,PEI.event_num
,TRUNC(PEI.completion_date) expenditure_item_date
,(SELECT FM.message_text FROM fnd_messages FM
WHERE FM.message_name = PE.error_code) AS EXCEPTION
,PE.currency_code
,((PE.unrec_rev_amount/(100-NVL(PEI.revenue_recognzd_percentage, 0))) * 100) AS ORIG_AMOUNT
,PE.unrec_rev_amount AS amount
,HAOU3.name contract_org
FROM pjb_errors PE
,pjb_billing_events PEI
,pjf_projects_all_vl PPA
,pjf_tasks_v PT
,okc_k_headers_all_b OKH
,okc_k_lines_b OKL
,hr_operating_units HOU
,hr_all_organization_units HAOU1
,hr_all_organization_units HAOU3
,pjf_event_types_vl PET
WHERE PE.event_id = PEI.event_id
AND PE.erroring_process = 'REVENUE_GEN'
AND PE.transaction_type_code = 'EVT'
AND PEI.project_id = PPA.project_id (+)
AND PPA.project_status_code <> 'CLOSED' --> Other than closed Projects
AND PEI.task_id = PT.task_id (+)
AND PE.contract_id = OKH.id
AND PE.major_version = OKH.major_version
AND :P_AS_OF_DATE BETWEEN OKH.start_date AND NVL(OKH.end_date, SYSDATE)
AND PE.contract_line_id = OKL.id
AND PE.major_version = OKL.major_version
AND :P_AS_OF_DATE BETWEEN OKL.start_date AND NVL(OKL.end_date, SYSDATE)
AND OKH.sts_code <> 'EXPIRED' --> Other than closed Contracts
AND NVL(PEI.revenue_recognzd_flag, 'X') <> 'F' --> Not Fully Recognized
AND NVL(PEI.event_type_code, 'X') = 'R'
AND PE.billing_type_code = 'EX' --> External Billing Type
AND PEI.completion_date <= :P_AS_OF_DATE
AND PEI.event_type_id = PET.event_type_id
AND OKH.org_id = HOU.organization_id
AND PEI.organization_id = HAOU1.organization_id
AND OKH.owning_org_id = HAOU3.organization_id
SELECT PE.accounting_period AS GL_PERIOD
,OKH.contract_number
,OKL.line_number
,PPA.segment1 AS project_number
,PPA.name AS project_name
,PT.task_number
,PT.task_name
,PEI.expenditure_item_id trx_number
,PEC.expenditure_category_name
,NVL(PPN.full_name, PS.vendor_name) AS full_name
,NVL(PPF.person_number, PS.segment1) AS person_number
,DECODE(PEI.incurred_by_person_id, NULL, 'Supplier'
,(SELECT FL.meaning FROM fnd_lookups FL
WHERE FL.lookup_type = 'PJC_PERSON_TYPE'
AND FL.lookup_code = PEI.person_type)
) AS PERSON_TYPE
,TRUNC(PEI.expenditure_item_date) expenditure_item_date
,DECODE(PEC.expenditure_category_name, 'Expenses', NULL, 'Material', NULL,
ROUND((PE.unrec_rev_amount/((PEI.quantity/100) * (100 - NVL(PEI.revenue_recog_percentage, 0)))), 2)) AS BILL_RATE
,(SELECT FM.message_text FROM fnd_messages FM
WHERE FM.message_name = PE.error_code) AS EXCEPTION
,ROUND(((PEI.quantity * DECODE(PEI.denom_currency_code, PE.currency_code, 1
,(SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type = 'Corporate'
AND gdr.to_currency = PE.currency_code
AND gdr.from_currency = PEI.denom_currency_code
AND gdr.conversion_date = PEI.expenditure_item_date))
)/100) * ROUND(100 - NVL(PEI.revenue_recog_percentage, 0), 2)
,2 ) AS Quantity
,PE.currency_code
,PE.unrec_rev_amount AS amount
,HAOU1.name exp_organization_name
,HAOU3.name contract_org
FROM (SELECT PER.expenditure_item_id, PER.contract_id, PER.contract_line_id, PER.major_version
,PER.currency_code, PER.unrec_rev_amount, PER.error_code, PER.accounting_period, PER.error_id
FROM pjb_errors PER
WHERE PER.erroring_process = 'REVENUE_GEN'
AND PER.transaction_type_code = 'EI'
AND PER.billing_type_code = 'EX'
AND PER.unrec_rev_amount <> 0
AND PER.txn_date <= :P_AS_OF_DATE
) PE
,pjc_exp_items_all PEI
,pjf_projects_all_vl PPA
,pjf_tasks_v PT
,okc_k_headers_all_b OKH
,okc_k_lines_b OKL
,pjf_txn_sources_vl PTS
,hr_operating_units HOU
,pjf_exp_types_vl PET
,pjf_exp_categories_vl PEC
,per_person_names_f PPN
,per_all_people_f PPF
,poz_suppliers_v PS
,hr_all_organization_units HAOU1
,hr_all_organization_units HAOU3
WHERE PE.expenditure_item_id = PEI.expenditure_item_id
AND PEI.project_id = PPA.project_id
AND PPA.project_status_code <> 'CLOSED' --> Other than closed Projects
AND PEI.task_id = PT.task_id
AND PE.contract_id = OKH.id
AND PE.major_version = OKH.major_version
AND :P_AS_OF_DATE BETWEEN OKH.start_date AND NVL(OKH.end_date, SYSDATE)
AND PE.contract_line_id = OKL.id
AND PE.major_version = OKL.major_version
AND :P_AS_OF_DATE BETWEEN OKL.start_date AND NVL(OKL.end_date, SYSDATE)
AND OKH.sts_code <> 'EXPIRED' --> Other than closed Contracts
AND NVL(PEI.revenue_recognized_flag, 'X') <> 'F' --> Not Fully Recognized
AND NVL(PEI.billable_flag, 'X') = 'Y' --> Only Billable Transactions
AND PEI.expenditure_item_date <= :P_AS_OF_DATE
AND PEI.transaction_source_id = PTS.transaction_source_id
AND OKH.org_id = HOU.organization_id
AND PEI.expenditure_type_id = PET.expenditure_type_id
AND PET.expenditure_category_id = PEC.expenditure_category_id
AND PEI.incurred_by_person_id = PPN.person_id (+)
AND PEI.incurred_by_person_id = PPF.person_id (+)
AND PPN.name_type (+) = 'GLOBAL'
AND PEI.expenditure_item_date BETWEEN NVL(PPN.effective_start_date, PEI.expenditure_item_date) AND NVL (PPN.effective_end_date, PEI.expenditure_item_date)
AND PEI.expenditure_item_date BETWEEN NVL(PPF.effective_start_date, PEI.expenditure_item_date) AND NVL (PPF.effective_end_date, PEI.expenditure_item_date)
AND PEI.vendor_id = PS.vendor_id (+)
AND PEI.expenditure_organization_id = HAOU1.organization_id
AND OKH.owning_org_id = HAOU3.organization_id
AND (PE.error_id, OKH.major_version) IN (SELECT MAX(PE2.error_id),MAX(PE2.major_version) FROM pjb_errors PE2
WHERE PE2.expenditure_item_id = PE.expenditure_item_id
AND PE2.erroring_process = 'REVENUE_GEN'
AND PE2.transaction_type_code = 'EI'
AND PE2.billing_type_code = 'EX'
AND PE2.unrec_rev_amount <> 0
AND PE2.txn_date <= :P_AS_OF_DATE
)
--**** Project Not Accruing Revenue Due to Event Exception ****--
SELECT (SELECT PERIOD_NAME
FROM gl_periods GP
,gl_ledgers GL
WHERE GP.period_set_name = GL.period_set_name
AND GL.ledger_id = HOU.set_of_books_id
AND GP.adjustment_period_flag = 'N'
AND PEI.completion_date BETWEEN GP.start_date AND GP.end_date) AS GL_PERIOD
,HOU.name
,OKH.contract_number
,OKL.line_number
,PPA.segment1 AS project_number
,PPA.name AS project_name
,PT.task_number
,PT.task_name
,PEI.event_num
,TRUNC(PEI.completion_date) expenditure_item_date
,(SELECT FM.message_text FROM fnd_messages FM
WHERE FM.message_name = PE.error_code) AS EXCEPTION
,PE.currency_code
,((PE.unrec_rev_amount/(100-NVL(PEI.revenue_recognzd_percentage, 0))) * 100) AS ORIG_AMOUNT
,PE.unrec_rev_amount AS amount
,HAOU3.name contract_org
FROM pjb_errors PE
,pjb_billing_events PEI
,pjf_projects_all_vl PPA
,pjf_tasks_v PT
,okc_k_headers_all_b OKH
,okc_k_lines_b OKL
,hr_operating_units HOU
,hr_all_organization_units HAOU1
,hr_all_organization_units HAOU3
,pjf_event_types_vl PET
WHERE PE.event_id = PEI.event_id
AND PE.erroring_process = 'REVENUE_GEN'
AND PE.transaction_type_code = 'EVT'
AND PEI.project_id = PPA.project_id (+)
AND PPA.project_status_code <> 'CLOSED' --> Other than closed Projects
AND PEI.task_id = PT.task_id (+)
AND PE.contract_id = OKH.id
AND PE.major_version = OKH.major_version
AND :P_AS_OF_DATE BETWEEN OKH.start_date AND NVL(OKH.end_date, SYSDATE)
AND PE.contract_line_id = OKL.id
AND PE.major_version = OKL.major_version
AND :P_AS_OF_DATE BETWEEN OKL.start_date AND NVL(OKL.end_date, SYSDATE)
AND OKH.sts_code <> 'EXPIRED' --> Other than closed Contracts
AND NVL(PEI.revenue_recognzd_flag, 'X') <> 'F' --> Not Fully Recognized
AND NVL(PEI.event_type_code, 'X') = 'R'
AND PE.billing_type_code = 'EX' --> External Billing Type
AND PEI.completion_date <= :P_AS_OF_DATE
AND PEI.event_type_id = PET.event_type_id
AND OKH.org_id = HOU.organization_id
AND PEI.organization_id = HAOU1.organization_id
AND OKH.owning_org_id = HAOU3.organization_id
No comments:
Post a Comment