AR Invoices with Delivery Options Report
select
al1.operating_unit,
al1.corp_code,
al1.corp_name,
al1.bill_to_cust_cd,
al1.invoice_number,
al1.inv_currency,
to_char(al1.transaction_date,'mm/dd/yyyy hh:mi:ss AM') transaction_date,
to_char(al1.invoice_creation_date,'mm/dd/yyyy hh:mi:ss AM') invoice_creation_date,
--al1.transaction_date,
-- al1.invoice_creation_date,
al1.transaction_name,
NULL print_cust_master_dt,
NULL ema_cust_master_dt,
NULL esa_cust_master_dt,
NULL edi_cust_master_dt,
NULL doculynx_cust_master_dt,
NULL email_address,
NULL email_delivery_status,
to_char(sysdate,'mm/dd/yyyy hh:mi:ss AM') timestamp
FROM
(SELECT ivtx.operating_unit Operating_Unit,
ivtx.corp_code Corp_Code,
ivtx.corp_name Corp_Name,
ivtx.bill_to_cust_cd Bill_To_Cust_Cd,
ivtx.invoice_number Invoice_Number,
ivtx.inv_currency Inv_Currency,
ivtx.transaction_date Transaction_Date,
ivtx.invoice_creation_date Invoice_Creation_Date,
ivtx.transaction_name Transaction_Name,
ivtx.ORG_ID Org_Id,
ivtx.customer_Trx_id Customer_Trx_Id,
ivtx.transaction_type Transaction_Type,
ivtx.ACCTG_AFFECT_FLAG Acctg_Affect_Flag,
1 Count_Invoices
/*case when ivtx.transaction_type = 'INV' then nvl(opti.print,optn.print)
when ivtx.transaction_type = 'CM' then nvl(optc.print,optn.print)
when ivtx.transaction_type = 'DM' then nvl(optd.print,optn.print)
else null
end Print,
case when ivtx.transaction_type = 'INV' then nvl(opti.ema,optn.ema)
when ivtx.transaction_type = 'CM' then nvl(optc.ema,optn.ema)
when ivtx.transaction_type = 'DM' then nvl(optd.ema,optn.ema)
else null
end EMA,
case when ivtx.transaction_type = 'INV' then nvl(opti.esa,optn.esa)
when ivtx.transaction_type = 'CM' then nvl(optc.esa,optn.esa)
when ivtx.transaction_type = 'DM' then nvl(optd.esa,optn.esa)
else null
end ESA,
case when ivtx.transaction_type = 'INV' then nvl(opti.edi,optn.edi)
when ivtx.transaction_type = 'CM' then nvl(optc.edi,optn.edi)
when ivtx.transaction_type = 'DM' then nvl(optd.edi,optn.edi)
else null
end EDI,
case when ivtx.transaction_type = 'INV' then nvl(opti.doculynx,optn.doculynx)
when ivtx.transaction_type = 'CM' then nvl(optc.doculynx,optn.doculynx)
when ivtx.transaction_type = 'DM' then nvl(optd.doculynx,optn.doculynx)
else null
end Doculynx
-- ivtx.emaildt
*/
FROM (select rct.ORG_ID Org_Id,
SUBSTR(OPR.NAME,1,3) Operating_Unit,
rct.customer_Trx_id Customer_Trx_Id,
CAC.ACCOUNT_NUMBER Corp_Code,
PAR.PARTY_NAME Corp_Name,
hcsu.location Bill_To_Cust_Cd,
rct.trx_number Invoice_Number,
rct.invoice_currency_code Inv_Currency,
trunc(rct.trx_date) Transaction_Date,
trunc(rct.creation_Date) Invoice_Creation_Date,
rctt.type Transaction_Type,
rctt.name Transaction_Name,
rctt.ACCOUNTING_AFFECT_FLAG Acctg_Affect_Flag
--xag.emaildt
from ra_customer_trx_all rct,
ra_cust_trx_types_all rctt,
hz_cust_site_uses_all hcsu,
hz_cust_Acct_Sites_All hca,
HR_OPERATING_UNITS OPR,
HZ_CUST_ACCOUNTS CAC,
HZ_PARTIES PAR
/*(
SELECT
emaildt,
customer_trx_id,
rowct
FROM
(
SELECT
coalesce(email_address, '%')
|| '~'
|| coalesce(email_delivery_status, '%') emaildt,
customer_trx_id,
ROW_NUMBER()
OVER(PARTITION BY customer_trx_id
ORDER BY
last_updated_date
) rowct
FROM
xxon_ar_generate_doc_status
)
WHERE
rowct = 1
) xag*/
WHERE rct.CUST_TRX_TYPE_SEQ_ID = rctt.CUST_TRX_TYPE_SEQ_ID
--AND rct.org_id = rctt.org_id
AND hcsu.site_use_id = rct.bill_to_site_use_id
and hca.cust_Acct_Site_id = hcsu.cust_Acct_site_id
AND OPR.ORGANIZATION_ID = rct.ORG_ID
and CAC.CUST_ACCOUNT_ID = rct.BILL_TO_CUSTOMER_ID
AND PAR.PARTY_ID(+) = CAC.PARTY_ID
-- AND rct.customer_trx_id = xag.customer_trx_id (+)
AND NOT EXISTS
(SELECT 1
FROM ar_lookups
WHERE lookup_type = 'XXON_AR_TRX_TYPE_EXCEPTIONS'
AND enabled_flag = 'Y'
AND lookup_code = rctt.name)
) ivtx
/*(select pnt1.cust5_code,
pnt1.print,
pnt1.ema,
pnt1.esa,
pnt1.edi,
pnt1.doculynx
from XXON_AR_CUST5_PRINT_OPTIONS_T pnt1
where pnt1.transaction_name = 'Invoice'
) opti,
(select pnt2.cust5_code,
pnt2.print,
pnt2.ema,
pnt2.esa,
pnt2.edi,
pnt2.doculynx
from XXON_AR_CUST5_PRINT_OPTIONS_T pnt2
where pnt2.transaction_name = 'Credit Memo'
) optc,
(select pnt3.cust5_code,
pnt3.print,
pnt3.ema,
pnt3.esa,
pnt3.edi,
pnt3.doculynx
from XXON_AR_CUST5_PRINT_OPTIONS_T pnt3
where pnt3.transaction_name = 'Debit Memo'
) optd,
(select pnt4.cust5_code,
pnt4.print,
pnt4.ema,
pnt4.esa,
pnt4.edi,
pnt4.doculynx
from XXON_AR_CUST5_PRINT_OPTIONS_T pnt4
where pnt4.transaction_name is null
) optn*/
--WHERE opti.cust5_code(+) = ivtx.Bill_To_Cust_Cd
-- and optc.cust5_code(+) = ivtx.Bill_To_Cust_Cd
-- and optd.cust5_code(+) = ivtx.Bill_To_Cust_Cd
-- and optn.cust5_code(+) = ivtx.Bill_To_Cust_Cd
) AL1
WHERE 1=1
AND AL1.Acctg_Affect_Flag = nvl(:p_Acctg_Affect_Flag,AL1.Acctg_Affect_Flag)
AND AL1.Operating_Unit = nvl(:p_Operating_Unit,AL1.Operating_Unit)
AND AL1.Org_Id = nvl(:p_Org_Id,AL1.Org_Id)
AND trunc(AL1.Transaction_Date) between nvl(to_char(:p_from_Transaction_Date, 'yyyy/MM/dd'),AL1.Transaction_Date)
and nvl(to_char(:p_to_Transaction_Date, 'yyyy/MM/dd'),AL1.Transaction_Date)
AND AL1.invoice_number = nvl(:p_invoice_number,AL1.invoice_number)
ORDER BY Operating_Unit, Corp_Name, Invoice_Number
No comments:
Post a Comment