Dynamics Community Forum Thread Details

CREATE OR REPLACE VIEW XXIR_SUM_INV_NUMBER_V
(SUM_INV_NUMBER,OUTPUT_LINE)
AS

SELECT
xi.invoice_number AS sum_inv_number
,|| xi.bill_compl_contr_hdr_number ||
(CASE
WHEN (xi.summary_invoice_number IS NULL) THEN
||
(CASE
WHEN (xcas2.site_name IS NULL) THEN
(
SELECT
cas.site_name AS cas_site_name
FROM
apps.xxmb_bill_compl_contr_hdrs_v bch
,apps.xxir_cust_acct_sites_v cas
WHERE
bch.contract_number(+) = xi.bill_compl_contr_hdr_number
AND cas.cust_acct_site_id(+) = bch.bill_to_cust_acct_site_id
AND ROWNUM = 1
)
ELSE
xcas2.site_name
END)
ELSE
||
(CASE
WHEN (xcas1.site_name IS NULL) THEN
(CASE
WHEN ((SUBSTR(xi.indiv_or_chg_cmpl_contr_number, 0, 1) = 'A') OR (SUBSTR(xi.indiv_or_chg_cmpl_contr_number, 0, 1) = 'D')) THEN
(
SELECT
cas.site_name AS cas_site_name
FROM
apps.xxir_sales_trading_contracts stc
,apps.xxir_cust_acct_sites_v cas
WHERE
stc.sales_trading_contract_number = xi.indiv_or_chg_cmpl_contr_number
AND stc.cust_acct_site_id = cas.cust_acct_site_id(+)
AND ROWNUM = 1
)
ELSE
(
SELECT
cas.site_name cas_site_name
FROM
apps.xxmb_indiv_contr_headers_v bmc
,apps.xxir_cust_acct_sites_v cas
WHERE
bmc.contract_number = xi.indiv_or_chg_cmpl_contr_number
AND bmc.cust_acct_site_id = cas.cust_acct_site_id(+)
AND bmc.contract_number_modifier =
(
SELECT
MAX(ich.contract_number_modifier)
FROM
apps.xxmb_indiv_contr_headers_s_v ich
WHERE
bmc.contract_number = ich.contract_number
)
AND ROWNUM = 1
)
END)
ELSE
xcas1.site_name
END)
END) AS output_line
FROM
apps.xxir_invoices xi
,apps.xxir_invoice_headers xih
,apps.xxir_cust_acct_sites_v xcas1
,apps.xxir_cust_acct_sites_v xcas2
WHERE
xi.invoice_id = xih.invoice_id
AND xi.cust_acct_site_code = xcas1.site_number(+)
AND xih.cust_acct_site_code = xcas2.site_number(+)
AND xi.chrg_compl_inv_num_modifier IN ('Z','000')
AND xi.invoice_progress IN ('Z','6')
AND xi.confirm_form_display_flag = 'N'
AND xi.invoice_edited_flag IN ('1','2')
AND xi.invalid_flag = '0'
AND xi.check_req_checked_flag <> '1'
AND xi.inp_slip_correction_flag = 'N'
AND (SUBSTRB(xi.indiv_or_chg_cmpl_contr_number,1,3) < 'A01'
OR SUBSTRB(xi.indiv_or_chg_cmpl_contr_number,1,3) > 'A99')
AND NOT EXISTS
(
SELECT
1
FROM
apps.xxir_summary_invoices xsi
WHERE
xi.summary_invoice_id = xsi.summary_invoice_id
AND xsi.chrg_compl_waiting_flg = 'Y'
)
UNION
SELECT
xsi.summary_invoice_number sum_inv_number
|| xsi.bill_compl_contr_hdr_number ||
||
(CASE
WHEN (xcas.site_name IS NULL) THEN
(
SELECT
cas.site_name cas_site_name
FROM
apps.xxmb_bill_compl_contr_hdrs_v bch
,apps.xxir_cust_acct_sites_v cas
WHERE
bch.contract_number(+) = xsi.bill_compl_contr_hdr_number
AND cas.cust_acct_site_id(+) = bch.bill_to_cust_acct_site_id
AND ROWNUM = 1
)
ELSE
xcas.site_name
END) output_line
FROM
apps.xxir_summary_invoices xsi
,apps.xxir_invoice_headers xih
,apps.xxir_cust_acct_sites_v xcas
WHERE
xsi.summary_invoice_id = xih.summary_invoice_id
AND xih.cust_acct_site_code = xcas.site_number(+)
AND xsi.bill_compl_contr_hdr_number NOT LIKE 'H%'
AND (SUBSTRB(xsi.bill_compl_contr_hdr_number,1,3) < 'A01'
OR SUBSTRB(xsi.bill_compl_contr_hdr_number,1,3) > 'A99')
AND xsi.summary_invoice_progress IN ('Z','6')
AND xsi.confirm_form_display_flag = 'N'
AND xsi.invoice_edited_flag IN ('1','2')
AND xsi.invalid_flag = '0'
AND xsi.chrg_compl_waiting_flg = 'N'
AND xsi.check_req_checked_flag <> '1'
AND NOT EXISTS
(
SELECT
1
FROM apps.xxir_invoices xi
WHERE
xi.summary_invoice_id = xsi.summary_invoice_id
AND xi.inp_slip_correction_flag = 'Y'
)
/
COMMENT ON TABLE XXIR_SUM_INV_NUMBER_V IS
/
COMMENT ON COLUMN XXIR_SUM_INV_NUMBER_V.SUM_INV_NUMBER IS
/
COMMENT ON COLUMN XXIR_SUM_INV_NUMBER_V.OUTPUT_LINE IS
/

Please tell me process how to replace below SQL to VIEW in Visual Studio

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates