Please note that this script is from R12 vision instance
DROP VIEW APPS.PA_DRAFT_INVOICES_V;
/* Formatted on 2014/04/17 22:50 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW apps.pa_draft_invoices_v (row_id,
project_id,
project_number,
project_name,
distribution_rule,
distribution_rule_m,
project_type,
project_status_m,
project_status_code,
project_organization_id,
project_organization_name,
customer_id,
customer_name,
customer_number,
agreement_id,
agreement_num,
agreement_type,
term_id,
term_name,
draft_invoice_num,
transfer_status_code,
invoice_status_code,
invoice_status_m,
chk_status,
bill_through_date,
approved_date,
approved_by_person_id,
approved_by_person_name,
approved_by_person_number,
released_date,
released_by_person_id,
released_by_person_name,
released_by_person_number,
invoice_date,
ra_invoice_number,
pa_date,
gl_date,
creation_date,
invoice_comment,
draft_invoice_num_credited,
canceled_flag,
cancel_credit_memo_flag,
write_off_flag,
converted_flag,
extracted_date,
retention_percentage,
ra_invoice_currency_code,
inv_currency_code,
inv_rate_type,
inv_rate_date,
inv_exchange_rate,
inv_user_rate_type,
project_currency_code,
invoice_class,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
generation_error_flag,
transferred_date,
transfer_rejection_reason,
exception_flag,
unearned_revenue_cr,
unbilled_receivable_dr,
system_reference,
customer_bill_split,
invoice_set_id,
invoice_amount,
amount,
original_balance,
balance_due,
amount_applied,
amount_credited,
write_off,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
bill_to_address_id,
ship_to_address_id,
update_allowed,
view_labor_costs_allowed,
receivable_code_combination_id,
rounding_code_combination_id,
unbilled_code_combination_id,
unearned_code_combination_id,
woff_code_combination_id,
recv_inv_amt,
round_amt,
LANGUAGE,
nls_language,
acctd_curr_code,
acctd_rate_type,
acctd_rate_date,
acctd_exchg_rate,
tax_amount,
invoice_category,
invoice_batch_source_id,
cc_ic_ar_batch_source_id,
cc_project_id,
ib_ap_transfer_status_code,
ib_ap_transfer_error_code,
invproc_currency_code,
invproc_bill_amount,
projfunc_currency_code,
projfunc_bill_amount,
proj_bill_amount,
funding_currency_code,
funding_bill_amount,
projfunc_invtrans_rate_type,
projfunc_invtrans_rate_ty_name,
projfunc_invtrans_rate_date,
projfunc_invtrans_ex_rate,
retention_invoice_flag,
retn_code_combination_id,
credit_memo_reason_code,
bill_to_customer_id,
ship_to_customer_id,
bill_to_customer_number,
bill_to_customer_name,
ship_to_customer_number,
ship_to_customer_name,
concession_flag,
record_version_number,
last_credit_request_id,
enable_top_task_inv_mth_flag,
revenue_accrual_method,
invoice_method,
revenue_accrual_method_disp,
invoice_method_disp,
bill_to_party_id,
ship_to_party_id,
bill_to_site_use_id,
ship_to_site_use_id,
customer_order_number,
line_of_account,
payment_set_id
)
AS
SELECT i.ROWID row_id, i.project_id, p.segment1 project_number,
p.NAME project_name, p.distribution_rule,
(SELECT dr.meaning distribution_rule_m
FROM pa_distribution_rules dr
WHERE dr.distribution_rule = p.distribution_rule),
p.project_type,
(SELECT ps.project_status_name
FROM pa_project_statuses ps
WHERE ps.project_status_code =
p.project_status_code
AND ps.status_type = 'PROJECT') project_status_m,
p.project_status_code project_status_code,
org.organization_id project_organization_id,
org.NAME project_organization_name, a.customer_id,
c.customer_name, c.customer_number, i.agreement_id,
a.agreement_num, a.agreement_type, a.term_id,
(SELECT t.NAME
FROM ra_terms t
WHERE t.term_id = a.term_id) term_name, i.draft_invoice_num,
i.transfer_status_code, lk.lookup_code invoice_status_code,
lk.meaning invoice_status_m,
DECODE
(i.generation_error_flag,
'Y', 'ERROR',
DECODE (i.approved_date,
NULL, 'UNAPPROVED',
DECODE (i.released_date,
NULL, 'UNRELEASED',
DECODE (i.transfer_status_code,
'P', 'RELEASED',
'X', 'REJECTED',
'T', 'TRANSFERRED',
'A', 'ACCEPTED',
'R', 'REJECTED',
'OTHER'
)
)
)
) chk_status,
i.bill_through_date, i.approved_date, i.approved_by_person_id,
emp2.full_name approved_by_person_name,
DECODE (emp2.current_npw_flag,
'Y', emp2.npw_number,
emp2.employee_number
) approved_by_person_number,
i.released_date, i.released_by_person_id,
emp.full_name released_by_person_name,
DECODE (emp.current_npw_flag,
'Y', emp.npw_number,
emp.employee_number
) released_by_person_number,
i.invoice_date, i.ra_invoice_number, i.pa_date, i.gl_date,
i.creation_date, i.invoice_comment, i.draft_invoice_num_credited,
i.canceled_flag, i.cancel_credit_memo_flag, i.write_off_flag,
i.converted_flag, i.extracted_date, i.retention_percentage,
i.inv_currency_code, i.inv_currency_code, i.inv_rate_type,
i.inv_rate_date, i.inv_exchange_rate,
(SELECT con.user_conversion_type
FROM pa_conversion_types_v con
WHERE con.conversion_type = i.inv_rate_type),
p.project_currency_code,
(SELECT lk3.meaning
FROM pa_lookups lk3
WHERE lk3.lookup_type = 'INVOICE_CLASS'
AND lk3.lookup_code =
DECODE
(org_inv.canceled_flag,
'Y', 'CANCEL',
DECODE
(i.write_off_flag,
'Y', 'WRITE_OFF',
DECODE
(i.concession_flag,
'Y', 'CONCESSION',
DECODE (NVL (i.draft_invoice_num_credited,
0
),
0, 'INVOICE',
'CREDIT_MEMO'
)
)
)
)
AND lk3.enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (lk3.start_date_active,
SYSDATE - 1
)
)
AND TRUNC (NVL (lk3.end_date_active,
SYSDATE
)
)) invoice_class,
i.attribute_category, i.attribute1, i.attribute2, i.attribute3,
i.attribute4, i.attribute5, i.attribute6, i.attribute7,
i.attribute8, i.attribute9, i.attribute10,
i.generation_error_flag, i.transferred_date,
i.transfer_rejection_reason,
DECODE (i.generation_error_flag,
'Y', 'Y',
DECODE (i.transfer_status_code, 'R', 'Y', 'X', 'Y', 'N')
) exception_flag,
SUM (NVL (ii.unearned_revenue_cr, 0)),
SUM (NVL (ii.unbilled_receivable_dr, 0)), i.system_reference,
i.customer_bill_split, i.invoice_set_id,
SUM (ii.inv_amount) invoice_amount,
SUM (NVL (ii.amount, 0)) amount,
DECODE (i.system_reference,
NULL, SUM (NVL (ii.inv_amount, 0)),
NULL
) original_balance,
DECODE (i.system_reference,
NULL, SUM (NVL (ii.inv_amount, 0)),
NULL
) balance_due,
NULL amount_applied, NULL amount_credited, NULL write_off,
i.last_update_date, i.last_updated_by, i.last_update_login,
i.program_application_id, i.program_id, i.program_update_date,
i.bill_to_address_id, i.ship_to_address_id,
pa_security.allow_update (i.project_id),
pa_security.view_labor_costs (i.project_id),
i.receivable_code_combination_id, i.rounding_code_combination_id,
i.unbilled_code_combination_id, i.unearned_code_combination_id,
i.woff_code_combination_id,
SUM (DECODE (i.transfer_status_code, 'A', ii.acct_amount, 0)
) recv_inv_amt,
SUM (DECODE (i.transfer_status_code, 'A', ii.rounding_amount, 0)
) round_amt,
i.LANGUAGE, (SELECT f.nls_language
FROM fnd_languages f
WHERE f.language_code = i.LANGUAGE),
DECODE (i.transfer_status_code, 'A', i.acctd_curr_code, NULL),
DECODE (i.transfer_status_code, 'A', i.acctd_rate_type, NULL),
DECODE (i.transfer_status_code, 'A', i.acctd_rate_date, NULL),
DECODE (i.transfer_status_code, 'A', i.acctd_exchg_rate, NULL),
pa_output_tax.get_draft_invoice_tax_amt (i.system_reference),
(SELECT DECODE
(NVL (pt.cc_prvdr_flag, 'N'),
'Y', 'INTERCOMPANY INVOICE',
DECODE (NVL (pc.bill_another_project_flag, 'N'),
'Y', 'INTER-PROJECT INVOICE',
'EXTERNAL INVOICE'
)
)
FROM pa_project_types pt
WHERE pt.project_type = p.project_type) invoice_category,
pi.invoice_batch_source_id, pi.cc_ic_ar_batch_source_id,
i.cc_project_id, i.ib_ap_transfer_status_code,
i.ib_ap_transfer_error_code, ii.invproc_currency_code,
SUM (ii.amount) invprc_bill_amount, ii.projfunc_currency_code,
SUM (NVL (ii.projfunc_bill_amount, 0)) projfunc_bill_amount,
SUM (ii.project_bill_amount) project_bill_amount,
ii.funding_currency_code,
SUM (ii.funding_bill_amount) funding_bill_amount,
i.projfunc_invtrans_rate_type projfunc_invtrans_rate_type,
pa_multi_currency.get_user_conversion_type
(i.projfunc_invtrans_rate_type),
i.projfunc_invtrans_rate_date projfunc_invtrans_rate_date,
TO_NUMBER (i.projfunc_invtrans_ex_rate) projfunc_invtrans_ex_rate,
i.retention_invoice_flag, i.retn_code_combination_id,
i.credit_memo_reason_code, i.bill_to_customer_id,
i.ship_to_customer_id, bill_c.customer_number,
bill_c.customer_name, ship_c.customer_number,
ship_c.customer_name, i.concession_flag, i.record_version_number,
i.last_credit_request_id, p.enable_top_task_inv_mth_flag,
p.revenue_accrual_method, p.invoice_method,
(SELECT lk4.meaning
FROM pa_lookups lk4
WHERE lk4.lookup_type = 'REVENUE ACCRUAL METHOD'
AND lk4.lookup_code = p.revenue_accrual_method),
(SELECT lk5.meaning
FROM pa_lookups lk5
WHERE lk5.lookup_type = 'INVOICE METHOD'
AND lk5.lookup_code = p.invoice_method),
bill_c.party_id, ship_c.party_id,
bill_site.site_use_id bill_to_site_use_id,
ship_site.site_use_id ship_to_site_use_id,
a.customer_order_number, a.line_of_account, i.payment_set_id
FROM pa_draft_invoices_all i,
pa_draft_inv_items_bas ii,
pa_projects_all p,
pa_project_customers pc,
hr_all_organization_units org,
pa_agreements_all a,
pa_draft_invoices_all org_inv,
pa_implementations pi,
pa_customers_v c,
per_all_people_f emp,
per_all_people_f emp2,
pa_lookups lk,
pa_customers_v bill_c,
pa_customers_v ship_c,
hz_cust_site_uses bill_site,
hz_cust_site_uses ship_site
WHERE 'Y' IN (SELECT pa_security.allow_query (i.project_id)
FROM SYS.DUAL)
AND p.project_id = pc.project_id
AND a.customer_id = pc.customer_id
AND p.project_id = i.project_id
AND i.agreement_id = a.agreement_id
AND p.org_id = pi.org_id
AND org_inv.project_id(+) = i.project_id
AND org_inv.draft_invoice_num(+) = i.draft_invoice_num_credited
AND ii.project_id(+) = i.project_id
AND ii.draft_invoice_num(+) = i.draft_invoice_num
AND p.carrying_out_organization_id = org.organization_id
AND c.customer_id = a.customer_id
AND emp.person_id(+) = i.released_by_person_id
AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+)
AND emp2.person_id(+) = i.approved_by_person_id
AND TRUNC (SYSDATE) BETWEEN emp2.effective_start_date(+) AND emp2.effective_end_date(+)
AND lk.lookup_type = 'INVOICE STATUS'
AND lk.lookup_code =
DECODE (i.generation_error_flag,
'Y', 'GENERATION ERROR',
DECODE (i.approved_date,
NULL, 'UNAPPROVED',
DECODE (i.released_date,
NULL, 'APPROVED',
DECODE (i.transfer_status_code,
'P', 'RELEASED',
'X', 'REJECTED IN TRANSFER',
'T', 'TRANSFERRED',
'A', 'ACCEPTED',
'R', 'REJECTED'
)
)
)
)
AND bill_c.customer_id(+) = i.bill_to_customer_id
AND ship_c.customer_id(+) = i.ship_to_customer_id
AND bill_site.cust_acct_site_id(+) = i.bill_to_address_id
AND bill_site.site_use_code(+) = 'BILL_TO'
AND bill_site.status(+) = 'A'
AND ship_site.cust_acct_site_id(+) = i.ship_to_address_id
AND ship_site.site_use_code(+) = 'SHIP_TO'
AND ship_site.status(+) = 'A'
GROUP BY i.ROWID,
i.project_id,
p.segment1,
p.distribution_rule,
p.project_type,
p.project_status_code,
org.organization_id,
a.customer_id,
c.customer_name,
c.customer_number,
i.agreement_id,
a.agreement_num,
a.agreement_type,
a.term_id,
i.draft_invoice_num,
i.transfer_status_code,
lk.lookup_code,
lk.meaning,
DECODE (i.generation_error_flag,
'Y', 'ERROR',
DECODE (i.approved_date,
NULL, 'UNAPPROVED',
DECODE (i.released_date,
NULL, 'UNRELEASED',
DECODE (i.transfer_status_code,
'P', 'RELEASED',
'X', 'REJECTED IN TRANSFER',
'T', 'TRANSFERRED',
'A', 'ACCEPTED',
'R', 'REJECTED',
'OTHER'
)
)
)
),
i.bill_through_date,
i.approved_date,
i.approved_by_person_id,
emp2.full_name,
DECODE (emp2.current_npw_flag,
'Y', emp2.npw_number,
emp2.employee_number
),
i.released_date,
i.released_by_person_id,
emp.full_name,
DECODE (emp.current_npw_flag,
'Y', emp.npw_number,
emp.employee_number
),
i.invoice_date,
i.ra_invoice_number,
i.pa_date,
i.gl_date,
i.creation_date,
i.invoice_comment,
i.draft_invoice_num_credited,
i.canceled_flag,
i.cancel_credit_memo_flag,
i.write_off_flag,
i.converted_flag,
i.extracted_date,
i.retention_percentage,
i.inv_currency_code,
i.inv_rate_type,
i.inv_rate_date,
i.inv_exchange_rate,
p.project_currency_code,
org_inv.canceled_flag,
i.attribute_category,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.generation_error_flag,
i.transferred_date,
i.transfer_rejection_reason,
DECODE (i.generation_error_flag,
'Y', 'Y',
DECODE (i.transfer_status_code, 'R', 'Y', 'X', 'Y', 'N')
),
i.system_reference,
i.customer_bill_split,
i.invoice_set_id,
i.last_update_date,
i.last_updated_by,
i.last_update_login,
i.program_application_id,
i.program_id,
i.program_update_date,
i.bill_to_address_id,
i.ship_to_address_id,
p.template_flag,
i.receivable_code_combination_id,
i.rounding_code_combination_id,
i.unbilled_code_combination_id,
i.unearned_code_combination_id,
i.woff_code_combination_id,
i.LANGUAGE,
i.acctd_curr_code,
i.acctd_rate_type,
i.acctd_rate_date,
i.acctd_exchg_rate,
p.project_type,
pc.bill_another_project_flag,
pi.invoice_batch_source_id,
pi.cc_ic_ar_batch_source_id,
i.cc_project_id,
i.ib_ap_transfer_status_code,
i.ib_ap_transfer_error_code,
ii.invproc_currency_code,
ii.projfunc_currency_code,
ii.funding_currency_code,
i.projfunc_invtrans_rate_type,
i.projfunc_invtrans_rate_date,
TO_NUMBER (i.projfunc_invtrans_ex_rate),
i.retention_invoice_flag,
i.retn_code_combination_id,
i.credit_memo_reason_code,
i.bill_to_customer_id,
i.ship_to_customer_id,
bill_c.customer_number,
bill_c.customer_name,
ship_c.customer_number,
ship_c.customer_name,
i.concession_flag,
i.record_version_number,
i.last_credit_request_id,
p.enable_top_task_inv_mth_flag,
p.revenue_accrual_method,
p.invoice_method,
bill_c.party_id,
ship_c.party_id,
bill_site.site_use_id,
ship_site.site_use_id,
a.customer_order_number,
a.line_of_account,
i.payment_set_id;
No comments:
Post a Comment