SQL & PL/SQL

SELECT DISTINCT NVL (jtf.NAME, 'No Sales Credit') NAME, ract.customer_trx_id,
ract.trx_number, ract.bill_to_customer_id,
NVL (ract.primary_salesrep_id, -3) primary_salesrep_id,
rac.customer_name custname,
/*xx_nyl_sa_order_type
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
) S_TYPE,RACTL.INTERFACE_LINE_ATTRIBUTE2,*/
xx_nyl_cust_address
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
) address,
NVL (ractl.extended_amount, 0)
* NVL (ract.exchange_rate, 1) itm_sales,
NVL (ractl.quantity_invoiced, 0)
+ NVL (ractl.quantity_credited, 0) qty,
item_dtl.itemid itemid, item_dtl.itemno itemno,
item_dtl.segment2, item_dtl.segment3,
item_dtl.attribute7 attribute7,
item_dtl.attribute8 attribute8,
item_dtl.attribute12 attribute12, item_dtl.itemdesc itemdesc,
ractl.inventory_item_id invid,
--ract.ship_to_site_use_id,
-- ract.bill_to_site_use_id,
ractl.customer_trx_line_id
FROM ra_customer_trx_all ract,
ra_customer_trx_lines_all ractl,
jtf_rs_salesreps jtf,
ra_customers rac,
ra_cust_trx_types_all ractt,
hr_all_organization_units hr,
(SELECT DISTINCT iim.item_id itemid,
msi.inventory_item_id invid,
iim.item_no itemno,
stk_dtl.segment2 segment2,
stk_dtl.segment3 segment3,
iim.attribute7 attribute7,
iim.attribute8 attribute8,
iim.attribute12 attribute12,
iim.item_desc1 itemdesc
FROM ic_item_mst iim,
mtl_system_items_b msi,
(SELECT mtl.segment2, mtl.segment3,
gmi.item_id
FROM mtl_categories_b mtl,
fnd_id_flex_structures_vl fndi,
gmi_item_categories gmi
WHERE gmi.category_id = mtl.category_id
AND fndi.id_flex_structure_name =
'Item Group'
AND fndi.id_flex_num = mtl.structure_id) stk_dtl
WHERE iim.item_no = msi.segment1
AND stk_dtl.item_id = iim.item_id) item_dtl
WHERE ract.customer_trx_id = ractl.customer_trx_id
AND ractl.line_type = 'LINE'
AND ract.cust_trx_type_id = ractt.cust_trx_type_id
AND hr.organization_id = ractl.org_id
AND JTF.ORG_ID = RACT.ORG_ID
-- AND ractl.inventory_item_id = 8880
AND item_dtl.invid = ractl.inventory_item_id
AND ractt.default_status NOT LIKE 'VD'
AND ractl.inventory_item_id IS NOT NULL
AND jtf.salesrep_id(+) = ract.primary_salesrep_id
AND rac.customer_id = ract.bill_to_customer_id
AND NVL
(ractl.interface_line_attribute2,
NVL
(xx_nyl_sa_order_type
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
),
'Order'
)
) =
NVL
(:p_sales_type,
NVL
(ractl.interface_line_attribute2,
NVL
(xx_nyl_sa_order_type
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
),
'Order'
)
)
)
AND NVL (jtf.NAME, 'No Sales Credit')
BETWEEN NVL (:p_salesmen_from,
NVL (jtf.NAME, 'No Sales Credit')
)
AND NVL (:p_salesmen_to,
NVL (jtf.NAME, 'No Sales Credit')
)
AND rac.customer_name BETWEEN NVL (:p_customer_from,
rac.customer_name
)
AND NVL (:p_customer_to,
rac.customer_name
)
AND trunc(to_date)(ract.trx_date) BETWEEN trunc(:p_year_from) AND trunc(:p_year_to)
UNION
SELECT NVL (gl.attribute6, 'No Sales Credit') NAME, --
1, --
'1', --
-- 1,--
ship.customer_id,
jtf.salesrep_id, NVL (gl.attribute3, 'NA') custname,
ship.address1 address, 1, 1, 1, '',
NVL (gl.attribute2, 'NA') segment2, '', '', '', '', '', 1,
-- SHIP.site_use_id,
--1,
1
FROM gl_je_lines gl,
gl_code_combinations gl1,
jtf_rs_salesreps jtf,
(SELECT rac.customer_name, rac.customer_id, hzsu.bill_to_site_use_id,
hzl.address1, hzsu.site_use_id, hzsu.site_use_code,
hzsu.primary_flag, hzsu.org_id
FROM ra_customers rac,
hz_party_sites hzps,
hz_parties hzp,
hz_cust_accounts hzca,
hz_cust_acct_sites hzas,
hz_cust_site_uses hzsu,
hz_locations hzl
WHERE rac.party_id = hzp.party_id
AND hzl.location_id = hzps.location_id
AND hzps.party_site_id = hzas.party_site_id
AND hzca.party_id = hzp.party_id
AND hzca.cust_account_id = hzas.cust_account_id
AND hzsu.cust_acct_site_id = hzas.cust_acct_site_id
-- and hzsu.SITE_USE_ID=:P
--and rac.customer_name='FON LOONG TRADING'
AND hzsu.site_use_code = 'SHIP_TO'
AND hzsu.primary_flag = 'Y') ship
WHERE gl.attribute4 = ANY ('SALES', 'COST')
AND gl.code_combination_id = gl1.code_combination_id
AND jtf.NAME = gl.attribute6
AND jtf.ORG_ID = ship.org_id
AND gl.attribute3 = ship.customer_name
AND gl.effective_date BETWEEN :p_year_from AND :p_year_to
-- AND gl1.segment1 = :CF_Comp_Code -------:p_com_code
AND NVL (gl.attribute6, 'No Sales Credit')
BETWEEN NVL (:p_salesmen_from,
NVL (gl.attribute6, 'No Sales Credit')
)
AND NVL (:p_salesmen_to,
NVL (gl.attribute6, 'No Sales Credit')
)
AND gl.attribute3 BETWEEN NVL (:p_customer_from, gl.attribute3)
AND NVL (:p_customer_to, gl.attribute3)
GROUP BY gl.attribute6,
gl.attribute3,
gl.attribute2,
ship.site_use_id,
ship.customer_id,
jtf.salesrep_id,
ship.address1
union
select
NVL (jtf.NAME, 'No Sales Credit') NAME
, 1
,'1'
,ract.bill_to_customer_id,NVL (ract.primary_salesrep_id, -4)
,rac.customer_name custname
, xx_nyl_cust_address
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
) address
,1, 1, 1, ''
,arr.attribute1 segment2
,'', '', '', '', '', 1,1
from
ar_receivable_applications_all arr,
ra_customer_trx_all ract,
jtf_rs_salesreps jtf,
hr_all_organization_units hr,
ra_customers rac
where arr.applied_customer_trx_id = ract.customer_trx_id
AND hr.organization_id = ract.org_id
AND trunc(to_date(arr.apply_date)) BETWEEN trunc(:p_year_from) AND trunc(:p_year_to)
AND jtf.s(to_date)alesrep_id(+) = ract.primary_salesrep_id
AND JTF.ORG_ID = RACT.ORG_ID
AND rac.customer_id = ract.bill_to_customer_id
AND hr.NAME = :CF_ORG_NAME :p_orgn_name
--and arr.attribute1 = :segment1
AND xx_nyl_sa_order_type (NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
) =
NVL (:p_sales_type,
xx_nyl_sa_order_type (NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
)
)
AND NVL (jtf.NAME, 'No Sales Credit')
BETWEEN NVL (:p_salesmen_from,
NVL (jtf.NAME, 'No Sales Credit')
)
AND NVL (:p_salesmen_to,
NVL (jtf.NAME, 'No Sales Credit')
)
AND rac.customer_name BETWEEN NVL (:p_customer_from,
rac.customer_name
)
AND NVL (:p_customer_to,
rac.customer_name
)
UNION
SELECT DISTINCT NVL (jtf.NAME, 'No Sales Credit') NAME, ract.customer_trx_id,
ract.trx_number, ract.bill_to_customer_id,
NVL (ract.primary_salesrep_id, -3) primary_salesrep_id,
rac.customer_name custname,
/*xx_nyl_sa_order_type
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
) S_TYPE,RACTL.INTERFACE_LINE_ATTRIBUTE2,*/
xx_nyl_cust_address
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
) address,
NVL (ractl.extended_amount, 0)
* NVL (ract.exchange_rate, 1) itm_sales,
NVL (ractl.quantity_invoiced, 0)
+ NVL (ractl.quantity_credited, 0) qty,
1, '',
nvl(ractl.ATTRIBUTE3,arr.ATTRIBUTE1 ) segment2, '',
'',
'',
'', '',
ractl.inventory_item_id invid,
--ract.ship_to_site_use_id,
-- ract.bill_to_site_use_id,
ractl.customer_trx_line_id --, ractl.INTERFACE_LINE_ATTRIBUTE2
FROM ra_customer_trx_all ract,
ra_customer_trx_lines_all ractl,
jtf_rs_salesreps jtf,
ra_customers rac,
ra_cust_trx_types_all ractt,
hr_all_organization_units hr,
ar_receivable_applications_all arr
WHERE ract.customer_trx_id = ractl.customer_trx_id
AND arr.APPLIED_CUSTOMER_TRX_ID(+) = ract.customer_trx_id
AND ractl.line_type = 'LINE'
AND ract.cust_trx_type_id = ractt.cust_trx_type_id
AND hr.organization_id = ractl.org_id
AND JTF.ORG_ID = RACT.ORG_ID
-- AND ractl.inventory_item_id = 8880
-- AND item_dtl.invid = ractl.inventory_item_id
AND ractt.default_status NOT LIKE 'VD'
AND ractl.inventory_item_id IS NULL
-- AND ractl.ATTRIBUTE3 is not null
-- AND ract.ATTRIBUTE3 is not null
-- and ractl.EXTENDED_AMOUNT > 0
AND jtf.salesrep_id(+) = ract.primary_salesrep_id
AND rac.customer_id = ract.bill_to_customer_id
AND NVL
(ractl.interface_line_attribute2,
NVL
(xx_nyl_sa_order_type
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
),
'Order'
)
) =
NVL
(:p_sales_type,
NVL
(ractl.interface_line_attribute2,
NVL
(xx_nyl_sa_order_type
(NVL (ract.ship_to_site_use_id,
ract.bill_to_site_use_id
),
DECODE (ract.ship_to_site_use_id,
NULL, 'B',
'S'
)
),
'Order'
)
)
)
AND NVL (jtf.NAME, 'No Sales Credit')
BETWEEN NVL (:p_salesmen_from,
NVL (jtf.NAME, 'No Sales Credit')
)
AND NVL (:p_salesmen_to,
NVL (jtf.NAME, 'No Sales Credit')
)
AND rac.customer_name BETWEEN NVL (:p_customer_from,
rac.customer_name
)
AND NVL (:p_customer_to,
rac.customer_name
)
AND ( trunc(to_date(ract.trx_date))) BETWEEN trunc(:p_year_from) AND trunc(:p_year_to)
or
trunc(to_date(arr.APPLY_DATE)) BETWEEN trunc(:p_year_from) AND trunc(:p_year_to) )
ORDER BY NAME, custname, address, segment2