AR Invoice Dm Cm Activity Excl Tax Lines With Cust Item Report
SELECT DISTINCT al1.company_code co,
al1.delivery_name,
al1.period_mon_yy period,
al1.corp_code,
al1.corp_name,
al1.bill_to_cust_code bill_to_cust_cd,
al1.bill_to_cust_name,
al1.ship_to_cust_code,
al1.invoice_number inv_num,
SUM(al1.line_amount) line_amount,
--Al1.Reference_Number Ref_Num,
--AL1.GUI_Number GUI_Number,
CASE
WHEN gui_number IS NOT NULL
AND reference_number IS NOT NULL THEN reference_number
||'-'
||gui_number
WHEN gui_number IS NOT NULL
AND reference_number IS NULL THEN gui_number
WHEN reference_number IS NOT NULL
AND gui_number IS NULL THEN reference_number
ELSE reference_number
END ref_num,
al1.invoice_date inv_date,
al1.hdr_creation_date hdr_creation_date,
al1.doc_sequence doc_seq,
al1.transaction_type trx_type,
al1.transaction_name trx_name,
al1.reason_code,
al1.customer_po,
al1.line_type,
al1.revenue_account,
al1.posted_status,
al1.receivable_account,
al1.currency_code cy_cd,
Sum(al1.extended_amount) extended_amt,
Sum(al1.invoice_amt_usd) invoice_amt_usd,
al1.sales_order,
al1.sales_order_line,
al1.order_line_line,
case when al1.line_type ='LINE' AND Al1.Business_Unit is not null then al1.Business_Unit
WHEN al1.line_type ='LINE' AND Al1.Business_Unit IS NULL THEN 'DFT'
ELSE NULL end Business_Unit ,
--Al1.Business_Unit,
al1.ship_bank,
al1.ship_to_cust_name,
al1.part_number part_num,
al1.ship_to_city,
al1.ship_to_country_name,
al1.payment_terms,
al1.waybill_number,
al1.business_class,
al1.ship_method,
-- Al1.Line_Quantity,
CASE
WHEN al1.transaction_type ='CM' THEN NULL
ELSE al1.line_quantity
END AS line_quantity,
CASE
WHEN al1.transaction_type ='CM' THEN al1.line_quantity
ELSE al1.qty_credited
END qty_credited,
al1.fob_code,
al1.fob_ship_to,
al1.inco_terms,
al1.tariff_code,
al1.customer_item,
al1.invoice_line,
al1.unit_price,
al1.price_source,
al1.number_cartons,
al1.line_information line_info,
al1.ship_area,
al1.country_of_origin,
al1.sgd_hist_rate,
al1.tax_rate,
al1.tax_code,
Round(al1.funct_rate,2) funct_rate,
al1.batch_source,
--sysdate timestamp,
To_char(sysdate,'MM/DD/YYYY','NLS_DATE_LANGUAGE=ENGLISH') timestamp,
al1.customer_trx_id,
al1.code_combination_id
FROM (
/*WITH BUSINESS_UNIT AS (SELECT DISTINCT gcc.segment6, xte.transaction_number
FROM xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcC
WHERE 1 = 1
--and transaction_number in ('523010000017','523010000028')
--AND xte.transaction_number(+) = ctx.trx_number
and xah.entity_id(+) = xte.entity_id
AND xah.ae_header_id(+) = xal.ae_header_id
AND gcc.code_combination_id = xal.code_combination_id
AND xal.gl_sl_link_table = 'XLAJEL'
--and segment6 NOT IN ('DFT')
--AND xal.ACCOUNTING_CLASS_CODE in ('RECEIVABLE')
AND xal.accounting_class_code = 'REVENUE'
--AND xdl.ae_header_id = xah.ae_header_id(+)
--AND xdl.ae_line_num = xal.ae_line_num(+)
AND xal.application_id = 222
--AND xdl.source_distribution_id_num_1(+) = ctd.cust_trx_line_gl_dist_id
--and gcc.code_combination_id = ctd.code_combination_id
--AND gir.gl_sl_link_id = xal.gl_sl_link_id
--AND gjh.je_header_id = gir.je_header_id
--AND gjl.je_header_id = gjh.je_header_id
--AND gjl.je_line_num = gir.je_line_num
--AND Nvl(gjh.conversion_flag, 'Y') <> 'N'
GROUP BY segment6,xte.transaction_number )*/
SELECT --ctx.*,cut.type
DISTINCT wd.delivery_name,
--Ctx.customer_trx_id,
ctx.org_id org_id,
ctx.trx_number invoice_number,
ctl.line_number invoice_line,
ctl.line_type line_type,
ctl.unit_selling_price unit_price,
cac.account_number corp_code,
par.party_name corp_name,
ctl.extended_amount extended_amount,
ctx.customer_trx_id customer_trx_id,
ctx.bill_to_customer_id bill_to_cust_id,
ctx.ship_to_customer_id ship_to_cust_id,
ctx.bill_to_site_use_id bill_to_site_use_id,
cst.location bill_to_cust_code,
sit.attribute1 bill_to_cust_name,
/*Changing as per Defect GM-9200*/
--Pts.party_site_number Bill_To_Cust_Name,
-- Par.party_name Bill_To_Cust_Name,
/* (Select PARTY_NAME
From hz_party_sites a,
Hz_party_site_uses b
where a.party_site_id = b.party_site_id
and b.party_site_use_id(+) = ctx.bill_to_site_use_id) Bill_To_Cust_Name, */
loc.city bill_to_city,
loc.postal_code bill_to_postal_cd,
shp.tax_reference fiscal_acct_num,
-- Shp.location Ship_To_Cust_Code,
(
SELECT a.party_site_name
FROM hz_party_sites a,
hz_party_site_uses b
WHERE a.party_site_id = b.party_site_id
AND b.party_site_use_id = ctx.ship_to_party_site_use_id) ship_to_cust_code,
--Shp.attribute8 Business_Class,
cac.customer_class_code business_class,
shp.attribute1 cust_type,
cut.attribute1 reason_code,
--ctx.REASON_CODE Reason_Code,
cut.attribute2 auto_accrual_rev,
cut.attribute3 accrl_rev_deb_act,
cut.attribute4 recharge_161,
cut.attribute5 true_bill,
cut.attribute6 int_ext,
cut.attribute8 qty_override,
cut.attribute10 obr_rebate,
cut.attribute11 accrl_rev_cred_act,
cut.attribute12 accr_rev_journal_desc,
cut.attribute13 obr_dcd_accrual,
cut.attribute14 obr_dsc_accrual,
cut.attribute15 obr_dsa_accrual,
Substr(opr.NAME, 1, 3) company_code,
--Pys.party_site_number Ship_To_Cust_Name,
(
SELECT DISTINCT b.attribute1
FROM hz_party_site_uses a,
hz_cust_acct_sites_all b
WHERE 1=1
AND a.party_site_id = b.party_site_id
AND a.party_site_use_id = ctx.ship_to_party_site_use_id) ship_to_cust_name,
--Changing as per Defect GM-9200
/* ( Select PARTY_name
From hz_party_sites a,
Hz_party_site_uses b
where a.party_site_id = b.party_site_id
and b.party_site_use_id = ctx.SHIP_TO_PARTY_SITE_USE_ID) Ship_To_Cust_Name, */
-- sts.attribute1 Ship_To_Cust_Name,
/* ( Select distinct hp1.PARTY_NAME
From hz_parties hp1, hz_party_sites a,
Hz_party_site_uses b
where a.party_id = hp1.party_id
and a.party_site_id = b.party_site_id
and b.party_site_use_id = ctx.SHIP_TO_PARTY_SITE_USE_ID
and rownum < 2 ) Ship_To_Cust_Name, */
-- Lcs.city Ship_To_City,
(
SELECT c.city
FROM hz_party_sites a,
hz_party_site_uses b,
--ra_customer_trx_all ctx,
hz_locations c
WHERE a.party_site_id = b.party_site_id
AND b.party_site_use_id = ctx.ship_to_party_site_use_id
--and ctx.trx_number ='291010000060'
AND a.location_id =c.location_id) ship_to_city,
lcs.postal_code ship_to_postal_cd,
msb.item_number part_number,
drt.conversion_rate usd_corp_rate,
ctx.trx_date invoice_date,
--TO_CHAR((FROM_TZ(CAST(ctx.trx_date AS TIMESTAMP),'GMT') AT TIME ZONE 'MST'),'MM/DD/YYYY HH:MI:SS PM') Invoice_Date,
ctx.invoice_currency_code currency_code,
btc.NAME batch_source,
pmt.term_id pmt_term_id,
To_char(pmt.due_date, 'DD-MON-RRRR') due_date,
trm.NAME payment_terms,
To_char(gl1.gl_posted_date, 'DD-MON-RRRR') gl_posted_date,
CASE
WHEN ctx.invoice_currency_code = trl.currency_code THEN ctl.extended_amount
ELSE Round(( ctl.extended_amount * trl.conversion_rate ), 2)
END invoice_amt_funct,
CASE
WHEN ctx.invoice_currency_code = 'EUR' THEN ctl.extended_amount
ELSE Round(( ctl.extended_amount * ecb.conversion_rate ), 2)
END amt_eur_stat_ecb,
loc.province bill_to_province,
lcs.province ship_to_province,
loc.state bill_to_state,
lcs.state ship_to_state,
loc.county bill_to_county,
lcs.county ship_to_county,
loc.country bill_to_country,
lcs.country ship_to_country,
cyb.territory_short_name bill_to_country_name,
--Cys.territory_short_name Ship_To_Country_Name,
(
SELECT d.territory_short_name
FROM hz_party_sites a,
hz_party_site_uses b,
--ra_customer_trx_all ctx,
hz_locations c,
fnd_territories_tl d
WHERE a.party_site_id = b.party_site_id
AND b.party_site_use_id = ctx.ship_to_party_site_use_id
--and ctx.trx_number ='291010000060'
AND a.location_id =c.location_id
AND c.country = d.territory_code
AND d.language ='US') ship_to_country_name,
prd.period_name period_mon_yy,
ctx.creation_date hdr_creation_date,
ctx.attribute7 gui_number,
ctx.ct_reference reference_number,
ctx.doc_sequence_value doc_sequence,
CASE
WHEN ctx.invoice_currency_code = 'USD' THEN ctl.extended_amount
ELSE Round(( ctl.extended_amount * drt.conversion_rate ), 2)
END invoice_amt_usd,
ctl.taxable_flag taxable_flag,
ctl.tax_rate tax_rate,
ctl.tax_recoverable recoverable_tax_amt,
gl3.receivable_account receivable_account,
gl1.revenue_account revenue_account,
ctl.extended_amount line_amount,
CASE
WHEN gl1.gl_posted_date IS NULL THEN 'No'
ELSE 'Yes'
END posted_status,
BU.segment6 Business_unit,
/* CASE
-- when ctl.line_type ='TAX' then NULL
WHEN ctl.line_type ='LINE' and GL1.business_unit ='DFT' THEN GL2.business_unit
ELSE (SELECT distinct cc2.segment6 Business_unit
from ra_cust_trx_line_gl_dist_all gld,
ra_customer_trx_lines_all ctll,
gl_code_combinations Cc2
WHERE 1=1
-- Gld.account_class = 'REV'
and rownum=1
AND Cc2.code_combination_id = Gld.code_combination_id(+)
and gld.customer_trx_line_id = ctll.customer_trx_line_id(+)
and ctx.customer_trx_id = ctll.customer_trx_id(+)
and ctl.customer_trx_line_id = ctll.customer_trx_line_id(+)
AND SEGMENT6 <> 'DFT') END AS Business_Unit, */
/* (select distinct gcc.segment6
from xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl
where 1=1
--and transaction_number in ('523010000017','523010000028')
and xte.transaction_number(+)=ctx.trx_number
--and xah.entity_id(+) = xte.entity_id
and xah.AE_HEADER_ID(+) = xal.ae_header_id
and gcc.code_combination_id = xal.code_combination_id
and xal.GL_SL_LINK_TABLE ='XLAJEL'
--and segment6 NOT IN ('DFT')
--AND xal.ACCOUNTING_CLASS_CODE in ('RECEIVABLE')
AND xal.accounting_class_code = 'REVENUE'
AND xdl.ae_header_id = xah.ae_header_id(+)
AND xdl.ae_line_num = xal.ae_line_num(+)
AND xal.APPLICATION_ID =222
AND xdl.source_distribution_id_num_1(+) = ctd.cust_trx_line_gl_dist_id
--and gcc.code_combination_id = ctd.code_combination_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gir.je_line_num
and nvl(gjh.CONVERSION_FLAG,'Y') <> 'N'
group by segment6) Business_Unit,*/
ctl.quantity_invoiced line_quantity,
ctl.quantity_credited qty_credited,
ctl.description line_description,
ctx.status_trx invoice_status,
/*Changing as per Defect GM-9200*/
--Cut.name Transaction_Name,
cut.description transaction_name,
-- Cut.TYPE Transaction_Type,
(
SELECT type
FROM ra_cust_trx_types_all
WHERE 1=1
AND cust_trx_type_id(+) = cut.cust_trx_type_id) transaction_type,
cut.accounting_affect_flag affect_accounting,
cut.post_to_gl post_to_gl,
ctx.complete_flag complete_flag,
ctl.sales_order sales_order,
/*Changing as per Defect GM-9200*/
--Ctl.sales_order_line Sales_Order_Line,
(
SELECT DISTINCT dla.line_number
FROM ra_customer_trx_lines_all rctl,
doo_fulfill_lines_all dfla,
doo_lines_all dla
WHERE 1 = 1
AND rctl.interface_line_attribute5 IS NOT NULL
AND rctl.interface_line_context = 'DOO'
AND to_number(rctl.interface_line_attribute5) = dfla.fulfill_line_id
AND dla.line_id = dfla.line_id
AND rctl.customer_trx_line_id = ctl.customer_trx_line_id
AND rownum < 2
----and dfla.FULFILL_LINE_ID = 300000487182899
) sales_order_line,
(
SELECT DISTINCT dfla.fulfill_line_number
FROM ra_customer_trx_lines_all rctl,
doo_fulfill_lines_all dfla,
doo_lines_all dla
WHERE 1 = 1
AND rctl.interface_line_attribute5 IS NOT NULL
AND rctl.interface_line_context = 'DOO'
AND to_number(rctl.interface_line_attribute5) = dfla.fulfill_line_id
AND dla.line_id = dfla.line_id
AND rctl.customer_trx_line_id = ctl.customer_trx_line_id
AND rownum < 2) order_line_line,
ctx.attribute1 trx_business_class,
ctx.attribute2 statutory_rate,
ctl.attribute13 price_source,
-- Ctl.attribute4 Tariff_Code,
eie.attribute_char2 tariff_code,
ctl.interface_line_attribute2 line_information,
--Ctl.interface_line_attribute3 Inco_Terms,
(
SELECT DISTINCT flv.description
FROM fnd_lookup_values flv
WHERE flv.enabled_flag = 'Y'
AND flv.lookup_type = 'FOB'
AND flv.language = userenv('LANG')
AND nvl(flv.end_date_active, sysdate) >= sysdate
AND flv.lookup_code = ctx.fob_point
AND rownum < 2 ) inco_terms,
/*Changing as per Defect GM-9200*/
ctl.interface_line_attribute4 customer_item,
/*(
SELECT
DISTINCT mre.extn_attribute_char072
FROM
RA_CUSTOMER_TRX_LINES_ALL RCTL,
MKT_REF_ENTITIES mre
WHERE
1 = 1
AND RCTL.INTERFACE_LINE_ATTRIBUTE5 IS NOT NULL
AND RCTL.INTERFACE_LINE_CONTEXT = 'DOO'
AND TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE5) = mre.EXTN_ATTRIBUTE_NUMBER014
AND RCTL.CUSTOMER_TRX_LINE_ID = Ctl.CUSTOMER_TRX_LINE_ID
AND ROWNUM < 2
) Customer_Item, */
ctl.interface_line_attribute5 eccn_number,
ctl.interface_line_attribute6 htsus_number,
-- Ctl.interface_line_attribute7 Country_Of_Origin,
--null Country_Of_Origin,
(
SELECT DISTINCT extn_attribute_char011
FROM svc_ref_entities
WHERE extn_attribute_char026 = ctl.sales_order) country_of_origin,
ctl.interface_line_attribute9 cust_ship_ref,
ctl.interface_line_attribute10 consignment_po,
ctl.interface_line_attribute11 citi_number,
---Ctl.interface_line_attribute12 Waybill_Number,
decode(ctl.interface_line_context, 'DOO',ctl.interface_line_attribute8) waybill_number,
-- Ovr.Ship_Area Ship_Area,
(
SELECT cst.attribute19
FROM hz_party_sites a,
hz_party_site_uses b,
--ra_customer_trx_all ctx,
hz_cust_acct_sites_all c,
hz_cust_site_uses_all cst
WHERE a.party_site_id = b.party_site_id
AND b.party_site_use_id = ctx.ship_to_party_site_use_id
--and ctx.trx_number ='291010000060'
AND a.party_site_id = c.party_site_id
AND c.cust_acct_site_id= cst.cust_acct_site_id
AND cst.site_use_code = 'SHIP_TO'
AND cst.status = 'A'
AND (
CASE
WHEN cst.attribute19 IN (:P_SHIP_AREA) THEN 1
WHEN (
COALESCE(NULL,:P_SHIP_AREA) IS NULL ) THEN 1
END = 1 )) ship_area,
-- Ovr.Purchase_Order Customer_Po,
ctx.purchase_order customer_po,
ctl.customer_trx_line_id cust_trx_line_id,
ctl.vat_tax_id vat_tax_id,
ctl.inventory_item_id inventory_item_id,
-- Ovr.Italy_Invoice_Sequence_Num Italy_Seq_Num,
-- Ovr.Bill_Source_Code Bill_Source,
-- Ovr.Ship_Method Ship_Method,
(
SELECT cst.attribute18
FROM hz_party_sites a,
hz_party_site_uses b,
--ra_customer_trx_all ctx,
hz_cust_acct_sites_all c,
hz_cust_site_uses_all cst
WHERE a.party_site_id = b.party_site_id
AND b.party_site_use_id = ctx.ship_to_party_site_use_id
--and ctx.trx_number ='291010000060'
AND a.party_site_id = c.party_site_id
AND c.cust_acct_site_id= cst.cust_acct_site_id
AND cst.site_use_code = 'SHIP_TO'
AND cst.status = 'A') ship_method,
-- Ovr.Customer_Tax_Id_Number Cust_Tax_Id,
-- Ovr.Number_Of_Cartons Number_Cartons,
NULL number_cartons,
-- Ovr.Gross_Weight Gross_Weight,
trl.conversion_rate funct_rate,
NULL sgd_hist_rate,
ctx.fob_point fob_code,
(
SELECT cst.attribute14
FROM hz_party_sites a,
hz_party_site_uses b,
--ra_customer_trx_all ctx,
hz_cust_acct_sites_all c,
hz_cust_site_uses_all cst
WHERE a.party_site_id = b.party_site_id
AND b.party_site_use_id = ctx.ship_to_party_site_use_id
--and ctx.trx_number ='291010000060'
AND a.party_site_id = c.party_site_id
AND c.cust_acct_site_id= cst.cust_acct_site_id
AND cst.site_use_code = 'SHIP_TO'
AND cst.status = 'A') fob_ship_to,
NULL ship_bank,
-- Cst.Fob_Point Fob_Code,
-- Shp.Fob_Point Fob_Ship_To,
ctx.attribute6 biz_ownership_cd,
cst.tax_reference bill_to_fisc_acct,
hou.NAME org_name,
CASE
WHEN :P_DISP_TAX_CODE <> 'NO' THEN
--( CASE WHEN Ctl.Line_Type = 'LINE' THEN
(
SELECT min(zxl.tax_rate_code)
FROM zx_lines zxl
WHERE zxl.application_id = 222
AND zxl.entity_code = 'TRANSACTIONS'
AND zxl.event_class_code IN ('INVOICE',
'CREDIT_MEMO',
'DEBIT_MEMO')
AND zxr.tax_rate_id=zxl.tax_rate_id
/* AND Zxl.Trx_Id = Ctl.Customer_Trx_Id
AND Zxl.Trx_Line_Id = Ctl.Customer_Trx_Line_Id */
)
--ELSE Zxr.Tax_Rate_Name END )
END tax_code,
ctd.code_combination_id
FROM ra_cust_trx_types_all cut,
ra_customer_trx_lines_all ctl,
ra_cust_trx_line_gl_dist_all ctd,
-- gl_code_combinations glcc,
ra_customer_trx_all ctx,
hz_cust_accounts cac,
hz_parties par,
hz_cust_site_uses_all cst,
hz_cust_acct_sites_all sit,
hz_party_sites pts,
hz_locations loc,
fnd_territories_tl cyb,
hz_cust_site_uses_all shp,
hr_operating_units opr,
hz_party_sites pys,
hz_locations lcs,
fnd_territories_tl cys,
hz_cust_acct_sites_all sts,
egp_system_items_b msb,
gl_periods prd,
gl_daily_rates drt,
ra_batch_sources_all btc,
ar_payment_schedules_all pmt,
ra_terms_tl trm,
zx_rates_tl zxr,
hr_organization_units_f_tl hou,
ego_item_eff_b eie,
(SELECT DISTINCT gcc.segment6, xte.transaction_number
FROM xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcC
WHERE 1 = 1
and xah.entity_id = xte.entity_id
AND xah.ae_header_id = xal.ae_header_id
AND gcc.code_combination_id = xal.code_combination_id
AND xal.gl_sl_link_table = 'XLAJEL'
AND xal.accounting_class_code = 'REVENUE'
AND xal.application_id = 222
GROUP BY segment6,xte.transaction_number) BU,
(
SELECT fnc.from_currency,
fnc.conversion_rate,
txn.customer_trx_id,
lgr.currency_code
FROM gl_daily_rates fnc,
gl_ledgers lgr,
ra_customer_trx_all txn
WHERE fnc.conversion_type = 'Corporate'
AND lgr.ledger_id = txn.set_of_books_id
AND fnc.conversion_date = txn.trx_date
AND fnc.to_currency = lgr.currency_code) trl,
(
SELECT ecr.from_currency,
ecr.conversion_rate,
txn.customer_trx_id
FROM gl_daily_rates ecr,
ra_customer_trx_all txn
WHERE ecr.conversion_type = '1024'
AND ecr.conversion_date = txn.trx_date
AND ecr.to_currency = 'EUR') ecb,
(
SELECT gld.customer_trx_line_id,
gld.gl_posted_date,
gld.amount,
cc2.segment3 revenue_account,
cc2.segment6 business_unit
FROM ra_cust_trx_line_gl_dist_all gld,
gl_code_combinations cc2
WHERE gld.account_class = 'REV'
AND cc2.code_combination_id = gld.code_combination_id
AND gld.event_id =
(
SELECT DISTINCT max(event_id) OVER()
FROM ra_cust_trx_line_gl_dist_all gld2
WHERE gld2.customer_trx_line_id = gld.customer_trx_line_id)) gl1,
(
SELECT DISTINCT gld.customer_trx_id,
customer_trx_line_id,
gld.gl_posted_date,
cc2.segment3 receivable_account
FROM ra_cust_trx_line_gl_dist_all gld,
gl_code_combinations cc2
WHERE gld.account_class = 'REC'
AND cc2.code_combination_id = gld.code_combination_id
--AND Gld.event_id = (SELECT DISTINCT Max(event_id)
-- over()
-- FROM ra_cust_trx_line_gl_dist_all Gld2
-- WHERE Gld2.customer_trx_line_id =
-- Gld.customer_trx_line_id)
) gl3,
(
SELECT wnd.delivery_name,
wdd.source_header_number
FROM wsh_new_deliveries wnd,
wsh_delivery_assignments wds,
wsh_delivery_details wdd
WHERE 1=1--wdd.source_header_number =
AND wdd.delivery_detail_id = wds.delivery_detail_id
AND wds.delivery_id = wnd.delivery_id ) wd
/*(SELECT Gld.customer_trx_id,
Gld.gl_posted_date,
Cc2.segment3 receivable_account
-- Cc2.segment6 Business_Unit
FROM ra_cust_trx_line_gl_dist_all Gld,
gl_code_combinations Cc2
WHERE Gld.account_class = 'REC'
AND Cc2.code_combination_id = Gld.code_combination_id
AND ROWNUM=1) gl3,*/
/*(select distinct gcc.segment6 Business_Unit,transaction_number--,gcc.code_combination_id
from xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc
where 1=1
--AND transaction_number='291010000041'
and xah.entity_id = xte.entity_id
and xah.AE_HEADER_ID = xal.ae_header_id
and gcc.code_combination_id = xal.code_combination_id
and xal.GL_SL_LINK_TABLE ='XLAJEL'
and segment6 NOT IN ('0000','DFT')
--AND xal.ACCOUNTING_CLASS_CODE in ('RECEIVABLE')
AND xal.APPLICATION_ID =222
--and rownum >2
group by transaction_number,gcc.segment6--,gcc.code_combination_id
)gl2 */
WHERE 1=1
AND ctx.trx_number = bu.transaction_number (+)
AND ctl.sales_order = wd.source_header_number (+)
--and ctx.trx_number in ('RAGE9I','524051023414')
AND ctx.customer_trx_id = ctl.customer_trx_id
AND ctx.customer_trx_id = ctd.customer_trx_id
AND ctl.customer_trx_line_id(+) = ctd.customer_trx_line_id
--and ctd.code_combination_id = gcc.code_combination_id
AND cut.cust_trx_type_seq_id (+) = ctx.cust_trx_type_seq_id -- need to check
--AND Cut.Org_Id = Ctx.Org_Id(+)
--AND ctx.trx_number IN ( '291010000060') --( '572010000007', '151010000011', '291010000008' )
--and gl2.transaction_number (+)= ctx.trx_number
--and gl3.transaction_number (+)= ctx.trx_number
--and gl2.ae_line_num (+)= ctl.line_number
AND cac.cust_account_id(+) = ctx.bill_to_customer_id
AND par.party_id(+) = cac.party_id
AND cst.site_use_id(+) = ctx.bill_to_site_use_id
AND sit.cust_acct_site_id(+) = cst.cust_acct_site_id
AND pts.party_site_id(+) = sit.party_site_id
AND loc.location_id(+) = pts.location_id
AND cyb.territory_code(+) = loc.country
AND cyb.language(+) = 'US'
AND shp.site_use_id(+) = ctx.ship_to_site_use_id
AND opr.organization_id = ctx.org_id
AND pys.party_site_id(+) = sts.party_site_id
AND lcs.location_id(+) = pys.location_id
AND cys.territory_code(+) = lcs.country
AND sts.cust_acct_site_id(+) = shp.cust_acct_site_id
AND trunc(ctx.trx_date) BETWEEN trunc(prd.start_date) AND trunc(prd.end_date)
AND prd.period_set_name = '4-4-5'
-- AND Prd.Period_Type = 'Month'
AND msb.inventory_item_id(+) = ctl.inventory_item_id
/*Changing as per Defect GM-9200*/
--AND msb.organization_id(+) = 300000458568237
-- AND msb.organization_id(+) = ctl.org_id
AND drt.conversion_type(+) = 'Corporate'
AND drt.to_currency(+) = 'USD'
AND drt.from_currency(+) = ctx.invoice_currency_code
AND trunc(drt.conversion_date(+)) = trunc(ctx.trx_date)
AND btc.batch_source_seq_id(+) = ctx.batch_source_seq_id
AND pmt.customer_trx_id(+) = ctx.customer_trx_id
AND trm.term_id(+) = ctx.term_id
AND trm.language(+) = 'US'
AND zxr.tax_rate_id(+) = ctl.vat_tax_id
AND zxr.language(+) = 'US'
AND gl1.customer_trx_line_id(+) = ctl.customer_trx_line_id
AND gl1.amount(+) = ctl.extended_amount
AND gl3.customer_trx_id(+) = ctl.customer_trx_id
--AND Gl3.amount(+) = Ctl.extended_amount
--AND Gl3.customer_trx_id(+) = Ctx.customer_trx_id
--AND Gl3.amount(+) = Ctl.extended_amount
--AND Gl2.amount(+) = Ctl.extended_amount
AND ecb.from_currency(+) = ctx.invoice_currency_code
AND ecb.customer_trx_id(+) = ctx.customer_trx_id
-- AND Cc1.Code_Combination_Id(+) = Cut.GL_ID_REV
AND trl.from_currency(+) = ctx.invoice_currency_code
AND trl.customer_trx_id(+) = ctx.customer_trx_id
AND hou.organization_id = ctx.org_id
AND hou.language = 'US'
AND eie.inventory_item_id(+) = ctl.inventory_item_id
AND eie.context_code(+) ='XXON_ERP_ATT_AG'
AND (
CASE
WHEN prd.period_name IN (:P_PERIOD_NAME) THEN 1
WHEN (
COALESCE(NULL,:P_PERIOD_NAME) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN substr(opr.NAME, 1, 3) IN (:P_CO) THEN 1
WHEN (
COALESCE(NULL,:P_CO) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN cut.accounting_affect_flag IN (:P_AFFECT_ACCOUNTING) THEN 1
WHEN (
COALESCE(NULL,:P_AFFECT_ACCOUNTING) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctl.extended_amount IN (:P_EXTENDED_AMOUNT) THEN 1
WHEN (
COALESCE(NULL,:P_EXTENDED_AMOUNT) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctl.line_type IN (:P_LINE_TYPE) THEN 1
WHEN (
COALESCE(NULL,:P_LINE_TYPE) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN cut.type IN (:P_TRANSACTION_TYPE) THEN 1
WHEN (
COALESCE(NULL,:P_TRANSACTION_TYPE) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctx.doc_sequence_value IN (:P_DOC_SEQ) THEN 1
WHEN (
COALESCE(NULL,:P_DOC_SEQ) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN cut.attribute1 IN (:P_REASON_CODE) THEN 1
WHEN (
COALESCE(NULL,:P_REASON_CODE) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctx.ct_reference IN (:P_REFERENCE_NUMBER) THEN 1
WHEN (
COALESCE(NULL,:P_REFERENCE_NUMBER) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctx.trx_number IN (:P_INV_NUM) THEN 1
WHEN (
COALESCE(NULL,:P_INV_NUM) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctl.sales_order IN (:P_SALES_ORDER) THEN 1
WHEN (
COALESCE(NULL,:P_SALES_ORDER) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctl.interface_line_attribute12 IN (:P_WAYBILL_NUMBER) THEN 1
WHEN (
COALESCE(NULL,:P_WAYBILL_NUMBER) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ctx.fob_point IN (:P_FOB_POINT) THEN 1
WHEN (
COALESCE (NULL, :P_FOB_POINT) IS NULL) THEN 1
END =1)
AND (
CASE
WHEN cut.NAME IN (:P_TRX_NAME) THEN 1
WHEN (
COALESCE(NULL,:P_TRX_NAME) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN hou.NAME IN (:P_ORG_NAME) THEN 1
WHEN (
COALESCE(NULL,:P_ORG_NAME) IS NULL ) THEN 1
END = 1 ) ) al1
WHERE 1=1
AND (
CASE
WHEN al1.revenue_account IN (:P_GL_ACCOUNT) THEN 1
WHEN (
COALESCE(NULL,:P_GL_ACCOUNT) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN al1.corp_code IN (:P_CORP_CD) THEN 1
WHEN (
COALESCE(NULL,:P_CORP_CD) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN al1.bill_to_cust_code IN (:P_BILL_TO_CUST_CD) THEN 1
WHEN (
COALESCE(NULL,:P_BILL_TO_CUST_CD) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN al1.part_number IN (:P_PART_NUM) THEN 1
WHEN (
COALESCE(NULL,:P_PART_NUM) IS NULL ) THEN 1
END = 1 )
AND ( trunc(Al1.Invoice_Date) >= nvl((:P_INV_FROM_DATE),trunc(Al1.Invoice_Date))
and trunc(Al1.Invoice_Date) <= nvl((:P_INV_TO_DATE),trunc(Al1.Invoice_Date)) )
--and TRUNC(Al1.Invoice_Date) >= nvl(to_timestamp(to_char(:P_INV_FROM_DATE,'MM/DD/YYYY','NLS_DATE_LANGUAGE=ENGLISH'),'MM/DD/YYYY'),TRUNC(Al1.Invoice_Date))
-- and TRUNC(Al1.Invoice_Date) <=nvl(to_timestamp(to_char(:P_INV_TO_DATE,'MM/DD/YYYY'),'MM/DD/YYYY','NLS_DATE_LANGUAGE=ENGLISH'),TRUNC(Al1.Invoice_Date))
GROUP BY al1.company_code ,
al1.period_mon_yy ,
al1.corp_code,
al1.corp_name,
al1.bill_to_cust_code ,
al1.bill_to_cust_name,
al1.ship_to_cust_code,
al1.invoice_number,
al1.invoice_date ,
al1.hdr_creation_date ,
al1.doc_sequence ,
al1.transaction_type ,
al1.transaction_name ,
al1.reason_code,
al1.customer_po,
al1.line_type,
al1.revenue_account,
al1.currency_code ,
al1.sales_order,
al1.sales_order_line,
al1.order_line_line,
--Al1.Business_Unit,
al1.delivery_name,
al1.ship_bank,
al1.ship_to_cust_name,
al1.part_number ,
al1.ship_to_city,
al1.ship_to_country_name,
al1.payment_terms,
al1.waybill_number,
al1.business_class,
al1.ship_method,
al1.line_quantity,
al1.fob_code,
al1.fob_ship_to,
al1.inco_terms,
al1.tariff_code,
al1.customer_item,
al1.invoice_line,
al1.unit_price,
al1.price_source,
al1.number_cartons,
al1.line_information ,
al1.ship_area,
al1.country_of_origin,
al1.sgd_hist_rate,
al1.tax_rate,
al1.tax_code,
al1.funct_rate,
al1.batch_source,
gui_number,
reference_number,
al1.customer_trx_id,
al1.posted_status,
al1.receivable_account,
al1.code_combination_id,
/*CASE when GUI_Number is not null AND Reference_Number IS NOT NULL then Reference_Number ||'-'||GUI_Number
WHEN GUI_Number IS NOT NULL AND Reference_Number IS NULL THEN GUI_Number
WHEN Reference_Number IS NOT NULL AND GUI_Number IS NULL THEN Reference_Number
ELSE Reference_Number
END Ref_Num*/
/*CASE WHEN AL1.TRANSACTION_TYPE ='CM' THEN AL1.Line_Quantity
ELSE al1.Qty_Credited END Qty_Credited*/
al1.qty_credited,
al1.transaction_type,
al1.line_quantity,
--line_amount,
Al1.Business_Unit,
sysdate
ORDER BY al1.corp_code,
al1.bill_to_cust_code,
al1.invoice_number
No comments:
Post a Comment