set echo off
set pause off
set feed off
set veri off
set termout off
set linesize 6888
set pages 0
set serveroutput on size 999999
spool /u03/extracts/XXCG_Royalty_Review_extract.csv
select 'Order Year'||','||
'Item Type'||','||
'Organization'||','||
'Order #'||','||
'Order Type'||','||
'Customer Name'||','||
'Customer Number'||','||
'Sales Pro Name'||','||
'Sales Pro #'||','||
'Order Status'||','||
'Rec Interface Date'||','||
'Item name'||','||
'Item Description'||','||
'Qty'||','||
'UOM'||','||
'Sell Price'||','||
'Total Sale'||','||
'Ship Date'||','||
'Total Commission'||','||
'Customer Class'||','||
'Invoice Number'||','||
'Invoice Date'||','
from dual;
select soh.attribute1||','||
decode(msi.segment1,
'AT','Custom Signet',
'AS','Royalty Stole',
'Stole Of Gratitude')||','||
decode(soh.ship_from_org_id,104,'ARC','CHA')||','||
to_char(soh.order_number)||','||
sot.name ||','||
replace(hp.party_name,',','')||','||
hca.account_number||','||
replace(rs.name,',','')||','||
rs.salesrep_number||','||
XXCG_order_status(soh.order_number) ||','||
decode(XXCG_order_status(soh.order_number),'Receivables Interfaced',
trunc(XXCG_order_status_date(soh.order_number))) ||','||
substr(msi.segment1,1,2)||'.'||msi.segment2 ||','||
msi.description ||','||
to_char(sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) ||','||
sol.ORDER_QUANTITY_UOM ||','||
to_char(ccd.total_sell_price) ||','||
to_char((sol.ordered_quantity - nvl(sol.cancelled_quantity,0))
* ccd.total_sell_price) ||','||
XXCG_ship_date(soh.header_id) ||','||
to_char(ccd.total_line_commission) ||','||
hca.customer_class_code ||','||
rct.trx_number||','||
rct.trx_date||','
from hz_parties hp,
hz_cust_accounts hca,
oe_transaction_types_tl sot,
oe_order_headers_all soh,
ra_customer_trx_all rct,
jtf_rs_salesreps rs,
XXCG_commissions_details ccd,
oe_order_lines_all sol,
ra_customer_trx_lines_all rctl,
mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
( select ltrim(xxcg_parameter('&&1',',',1)) as order_year
,decode(xxcg_parameter('&&1',',',2),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,1)+ 1,
instr('&&1',',',1,2) - instr('&&1',',',1,1) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_from
,decode(xxcg_parameter('&&1',',',3),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,2)+ 1,
instr('&&1',',',1,3) - instr('&&1',',',1,2) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_to
,xxcg_parameter('&&1',',',4) status
from dual) fltr
where mc.segment1 in (
select XXCG_parameter(fvl.flex_value,'.',1)
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mc.segment1||'.'||mc.segment2||'.'||mc.segment3 in (
select fvl.flex_value
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mic.organization_id = 103
and mic.category_set_id = 1
and mc.CATEGORY_ID = mic.CATEGORY_ID
and mic.INVENTORY_ITEM_ID+0 = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and sol.inventory_item_id = msi.inventory_item_id
and soh.attribute1 = to_char(fltr.order_year)
and (sot.name Like 'Accessories%'
or sot.name Like 'ClassKeeper%'
or sot.name Like 'ClassRental%')
and soh.order_category_code = 'ORDER'
and exists (select null
from dual
where ( apps.XXCG_order_year = fltr.order_year
or (apps.XXCG_order_year != fltr.order_year
and soh.flow_status_code IN ('INVOICED','CLOSED'))))
and sol.header_id = soh.header_id
and soh.salesrep_id = rs.salesrep_id
and soh.order_type_id = sot.transaction_type_id
AND soh.sold_to_org_id = hca.cust_account_id
AND hp.party_id = hca.party_id
and sol.line_id = ccd.line_id
and sol.line_id = rctl.interface_line_attribute6
and rctl.customer_trx_id+0 = rct.customer_trx_id
and trunc(rct.trx_date) between nvl(fltr.date_from,trunc(rct.trx_date)) and nvl(fltr.date_to, trunc(rct.trx_date))
and nvl(fltr.status, 'RECEIVABLES INTERFACED') = 'Receivables Interfaced'
UNION
select soh.attribute1||','||
decode(msi.segment1,
'AT','Custom Signet',
'AS','Royalty Stole',
'Stole Of Gratitude')||','||
decode(soh.ship_from_org_id,104,'ARC','CHA')||','||
to_char(soh.order_number)||','||
sot.name ||','||
replace(hp.party_name,',','')||','||
hca.account_number||','||
replace(rs.name,',','')||','||
rs.salesrep_number||','||
XXCG_order_status(soh.order_number) ||','||
decode(XXCG_order_status(soh.order_number),'Receivables Interfaced',
trunc(XXCG_order_status_date(soh.order_number))) ||','||
substr(msi.segment1,1,2)||'.'||msi.segment2 ||','||
msi.description ||','||
to_char(sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) ||','||
sol.ORDER_QUANTITY_UOM ||','||
to_char(ccd.total_sell_price) ||','||
to_char((sol.ordered_quantity - nvl(sol.cancelled_quantity,0))
* ccd.total_sell_price) ||','||
XXCG_ship_date(soh.header_id) ||','||
to_char(ccd.total_line_commission) ||','||
hca.customer_class_code ||','||
' ,'||
' ,'
from hz_parties hp,
hz_cust_accounts hca,
oe_transaction_types_tl sot,
oe_order_headers_all soh,
jtf_rs_salesreps rs,
XXCG_commissions_details ccd,
oe_order_lines_all sol,
mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
( select ltrim(xxcg_parameter('&&1',',',1)) as order_year
,decode(xxcg_parameter('&&1',',',2),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,1)+ 1,
instr('&&1',',',1,2) - instr('&&1',',',1,1) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_from
,decode(xxcg_parameter('&&1',',',3),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,2)+ 1,
instr('&&1',',',1,3) - instr('&&1',',',1,2) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_to
,xxcg_parameter('&&1',',',4) status
from dual) fltr
where mc.segment1 in (
select XXCG_parameter(fvl.flex_value,'.',1)
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mc.segment1||'.'||mc.segment2||'.'||mc.segment3 in (
select fvl.flex_value
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mic.organization_id = 103
and mic.category_set_id = 1
and mc.CATEGORY_ID = mic.CATEGORY_ID
and mic.INVENTORY_ITEM_ID+0 = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and sol.inventory_item_id = msi.inventory_item_id
and soh.attribute1 = to_char(fltr.order_year)
and (sot.name Like 'Accessories%'
or sot.name Like 'ClassKeeper%'
or sot.name Like 'ClassRental%')
and soh.order_category_code = 'ORDER'
and sol.header_id = soh.header_id
and soh.salesrep_id = rs.salesrep_id
and soh.order_type_id = sot.transaction_type_id
AND soh.sold_to_org_id = hca.cust_account_id
AND hp.party_id = hca.party_id
and sol.line_id = ccd.line_id
and soh.flow_status_code not in ('INVOICED','CLOSED')
and XXCG_order_status(soh.order_number) = nvl(fltr.status,XXCG_order_status(soh.order_number))
and fltr.date_from is null
and fltr.date_to is null
;
spool off
rollback;
set pause off
set feed off
set veri off
set termout off
set linesize 6888
set pages 0
set serveroutput on size 999999
spool /u03/extracts/XXCG_Royalty_Review_extract.csv
select 'Order Year'||','||
'Item Type'||','||
'Organization'||','||
'Order #'||','||
'Order Type'||','||
'Customer Name'||','||
'Customer Number'||','||
'Sales Pro Name'||','||
'Sales Pro #'||','||
'Order Status'||','||
'Rec Interface Date'||','||
'Item name'||','||
'Item Description'||','||
'Qty'||','||
'UOM'||','||
'Sell Price'||','||
'Total Sale'||','||
'Ship Date'||','||
'Total Commission'||','||
'Customer Class'||','||
'Invoice Number'||','||
'Invoice Date'||','
from dual;
select soh.attribute1||','||
decode(msi.segment1,
'AT','Custom Signet',
'AS','Royalty Stole',
'Stole Of Gratitude')||','||
decode(soh.ship_from_org_id,104,'ARC','CHA')||','||
to_char(soh.order_number)||','||
sot.name ||','||
replace(hp.party_name,',','')||','||
hca.account_number||','||
replace(rs.name,',','')||','||
rs.salesrep_number||','||
XXCG_order_status(soh.order_number) ||','||
decode(XXCG_order_status(soh.order_number),'Receivables Interfaced',
trunc(XXCG_order_status_date(soh.order_number))) ||','||
substr(msi.segment1,1,2)||'.'||msi.segment2 ||','||
msi.description ||','||
to_char(sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) ||','||
sol.ORDER_QUANTITY_UOM ||','||
to_char(ccd.total_sell_price) ||','||
to_char((sol.ordered_quantity - nvl(sol.cancelled_quantity,0))
* ccd.total_sell_price) ||','||
XXCG_ship_date(soh.header_id) ||','||
to_char(ccd.total_line_commission) ||','||
hca.customer_class_code ||','||
rct.trx_number||','||
rct.trx_date||','
from hz_parties hp,
hz_cust_accounts hca,
oe_transaction_types_tl sot,
oe_order_headers_all soh,
ra_customer_trx_all rct,
jtf_rs_salesreps rs,
XXCG_commissions_details ccd,
oe_order_lines_all sol,
ra_customer_trx_lines_all rctl,
mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
( select ltrim(xxcg_parameter('&&1',',',1)) as order_year
,decode(xxcg_parameter('&&1',',',2),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,1)+ 1,
instr('&&1',',',1,2) - instr('&&1',',',1,1) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_from
,decode(xxcg_parameter('&&1',',',3),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,2)+ 1,
instr('&&1',',',1,3) - instr('&&1',',',1,2) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_to
,xxcg_parameter('&&1',',',4) status
from dual) fltr
where mc.segment1 in (
select XXCG_parameter(fvl.flex_value,'.',1)
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mc.segment1||'.'||mc.segment2||'.'||mc.segment3 in (
select fvl.flex_value
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mic.organization_id = 103
and mic.category_set_id = 1
and mc.CATEGORY_ID = mic.CATEGORY_ID
and mic.INVENTORY_ITEM_ID+0 = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and sol.inventory_item_id = msi.inventory_item_id
and soh.attribute1 = to_char(fltr.order_year)
and (sot.name Like 'Accessories%'
or sot.name Like 'ClassKeeper%'
or sot.name Like 'ClassRental%')
and soh.order_category_code = 'ORDER'
and exists (select null
from dual
where ( apps.XXCG_order_year = fltr.order_year
or (apps.XXCG_order_year != fltr.order_year
and soh.flow_status_code IN ('INVOICED','CLOSED'))))
and sol.header_id = soh.header_id
and soh.salesrep_id = rs.salesrep_id
and soh.order_type_id = sot.transaction_type_id
AND soh.sold_to_org_id = hca.cust_account_id
AND hp.party_id = hca.party_id
and sol.line_id = ccd.line_id
and sol.line_id = rctl.interface_line_attribute6
and rctl.customer_trx_id+0 = rct.customer_trx_id
and trunc(rct.trx_date) between nvl(fltr.date_from,trunc(rct.trx_date)) and nvl(fltr.date_to, trunc(rct.trx_date))
and nvl(fltr.status, 'RECEIVABLES INTERFACED') = 'Receivables Interfaced'
UNION
select soh.attribute1||','||
decode(msi.segment1,
'AT','Custom Signet',
'AS','Royalty Stole',
'Stole Of Gratitude')||','||
decode(soh.ship_from_org_id,104,'ARC','CHA')||','||
to_char(soh.order_number)||','||
sot.name ||','||
replace(hp.party_name,',','')||','||
hca.account_number||','||
replace(rs.name,',','')||','||
rs.salesrep_number||','||
XXCG_order_status(soh.order_number) ||','||
decode(XXCG_order_status(soh.order_number),'Receivables Interfaced',
trunc(XXCG_order_status_date(soh.order_number))) ||','||
substr(msi.segment1,1,2)||'.'||msi.segment2 ||','||
msi.description ||','||
to_char(sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) ||','||
sol.ORDER_QUANTITY_UOM ||','||
to_char(ccd.total_sell_price) ||','||
to_char((sol.ordered_quantity - nvl(sol.cancelled_quantity,0))
* ccd.total_sell_price) ||','||
XXCG_ship_date(soh.header_id) ||','||
to_char(ccd.total_line_commission) ||','||
hca.customer_class_code ||','||
' ,'||
' ,'
from hz_parties hp,
hz_cust_accounts hca,
oe_transaction_types_tl sot,
oe_order_headers_all soh,
jtf_rs_salesreps rs,
XXCG_commissions_details ccd,
oe_order_lines_all sol,
mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
( select ltrim(xxcg_parameter('&&1',',',1)) as order_year
,decode(xxcg_parameter('&&1',',',2),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,1)+ 1,
instr('&&1',',',1,2) - instr('&&1',',',1,1) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_from
,decode(xxcg_parameter('&&1',',',3),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,2)+ 1,
instr('&&1',',',1,3) - instr('&&1',',',1,2) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_to
,xxcg_parameter('&&1',',',4) status
from dual) fltr
where mc.segment1 in (
select XXCG_parameter(fvl.flex_value,'.',1)
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mc.segment1||'.'||mc.segment2||'.'||mc.segment3 in (
select fvl.flex_value
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mic.organization_id = 103
and mic.category_set_id = 1
and mc.CATEGORY_ID = mic.CATEGORY_ID
and mic.INVENTORY_ITEM_ID+0 = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and sol.inventory_item_id = msi.inventory_item_id
and soh.attribute1 = to_char(fltr.order_year)
and (sot.name Like 'Accessories%'
or sot.name Like 'ClassKeeper%'
or sot.name Like 'ClassRental%')
and soh.order_category_code = 'ORDER'
and sol.header_id = soh.header_id
and soh.salesrep_id = rs.salesrep_id
and soh.order_type_id = sot.transaction_type_id
AND soh.sold_to_org_id = hca.cust_account_id
AND hp.party_id = hca.party_id
and sol.line_id = ccd.line_id
and soh.flow_status_code not in ('INVOICED','CLOSED')
and XXCG_order_status(soh.order_number) = nvl(fltr.status,XXCG_order_status(soh.order_number))
and fltr.date_from is null
and fltr.date_to is null
;
spool off
rollback;
