AR AGING REPORT

select customer_number, customer_name,
paymt,
Day_030 ,
Day_031_060,
Day_061_090,
Day_091_120,
Day_121_150,
Day_151_180,
Day_181,
no_ofTrx
from
(select pay.org_id,pay.customer_id
--        ,pay.trx_number, pay.trx_date
--   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING,to_Date ('asofdate') - trunc(pay.trx_date)
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) <=30   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id)  end ) ) Day_030
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 31  and 60   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_031_060   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 61  and 90   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_061_090
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 91  and 120  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_091_120   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 121 and 150  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_121_150
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 151 and 180  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_151_180   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) >= 181               then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_181
,count(*) no_ofTrx
,''
from       apps.ar_payment_Schedules_all pay
where  pay.class in ('XX','YY','ZZ') and pay.org_id = &3
and pay.gl_date  <= to_Date ('&1')
and pay.GL_DATE_CLOSED >  to_Date ('&1')
and exists   ( select 'x' from  apps.gl_code_combinations gl
,apps.ra_cust_trx_line_gl_dist_all distgl
where  (trim (('&2')) ='0' or gl.segment4 in ('&2'))   --gl.segment4 in ('&2')
and gl.CODE_COMBINATION_ID =  distgl.CODE_COMBINATION_ID
and distgl.ACCOUNT_CLASS ='REC'
and distgl.CUSTOMER_TRX_ID  =pay.CUSTOMER_TRX_ID
)           
group by pay.org_id,pay.customer_id
--   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING ,pay.trx_number, pay.trx_date
)  invag ,
(select  pay.org_id,pay.customer_id, sum(receiptL.AMOUNT_APPLIED) paymt
from  apps.ar_payment_Schedules_all pay
,AR_RECEIVABLE_APPLICATIONS_ALL receiptL
where  pay.class ='PMT' and receiptl.status in ('ACC','UNAPP') and pay.org_id =&3
and   pay.PAYMENT_SCHEDULE_ID =     receiptL.PAYMENT_SCHEDULE_ID
and pay.gl_date  <= to_Date ('&1')
and pay.GL_DATE_CLOSED >  to_Date ('&1')
group by pay.org_id,pay.customer_id
) payment,
apps.ra_customers cust
where cust.customer_id =  invag.customer_id (+)
and   cust.customer_id =  payment.customer_id (+)
and  (invag.org_id is not null or payment.org_id is not null)
/

BOM ROUTING RESOURCES & OPERATION

BOM OPERATION ROUTING


SELECT
--bor.routing_sequence_id ,
bos.operation_sequence_id op_seq_id ,
bos.operation_seq_num op_seq ,
bos.operation_description ,
--bos.standard_operation_id ,
bos.department_id ,
bos.operation_type ,
bor.routing_type ,
bso.operation_code operation_code,
bor.completion_subinventory comp_subinv,
bor.completion_locator_id comp_loc ,
bd.department_code department_code
FROM
bom_standard_operations bso,
bom_departments bd,
bom_operation_sequences bos,
bom_operational_routings bor
WHEREe bor.assembly_item_id = 149
AND bor.organization_id =207
AND bor.routing_sequence_id=bos.routing_sequence_id
AND bos.department_id = bd.department_id
AND bos.standard_operation_id = bso.standard_operation_id (+)
ORDER BY bos.operation_sequence_id
/


BOM ROUTING RESOURCES

SELECT
bor.routing_sequence_id ,
bos.operation_sequence_id op_seq_id,
bos.operation_seq_num op_seq,
bso.operation_code op_code,
bos.operation_description ,
bos.standard_operation_id std_op_id,
bos.department_id dept,
bos.operation_type op_type,
boc.resource_seq_num res_seq,
boc.resource_id ,
boc.activity_id ,
boc.basis_type basis,
br.resource_code res_code,
bor.completion_subinventory comp_subinv,
bor.completion_locator_id comp_loc,
bor.routing_type rout_type,
boc.usage_rate_or_amount rate
FROM
bom_standard_operations bso,
bom_resources br,
bom_operation_resources boc,
bom_operation_sequences bos,
bom_operational_routings bor
WHERE
bor.assembly_item_id = 149
AND bor.organization_id =207
AND bor.routing_sequence_id=bos.routing_sequence_id
AND bos.operation_sequence_id=boc.operation_sequence_id
AND br.resource_id = boc.resource_id
AND bos.standard_operation_id = bso.standard_operation_id (+)
ORDER BY 1
/
FIND ATTACHMENTS FOR BOM 


SELECT to_number(AD.PK1_VALUE) operation_sequence_id,
TL.DESCRIPTION instruction_code,
ST.SHORT_TEXT instruction_description
FROM FND_DOCUMENTS D,
FND_DOCUMENTS_TL TL,
FND_DOCUMENTS_SHORT_TEXT ST,
FND_ATTACHED_DOCUMENTS AD,
bom_operation_sequences bos
WHERE bos.operation_sequence_id =166
and bos.operation_sequence_id = to_number(AD.PK1_VALUE)
AND D.DOCUMENT_ID = TL.DOCUMENT_ID
AND TL.DOCUMENT_ID = AD.DOCUMENT_ID
AND TL.MEDIA_ID = ST.MEDIA_ID
AND SYSDATE BETWEEN TRUNC(NVL(D.START_DATE_ACTIVE, SYSDATE))
AND TRUNC(NVL(D.END_DATE_ACTIVE, SYSDATE))+1
AND AD.ENTITY_NAME like 'BOM_OPERATION_SEQUENCES'
AND TL.LANGUAGE = userenv('LANG')
ORDER BY instruction_code

find ALL items assigned TO categories OF a category SET :

-- find ALL items assigned TO categories OF a category SET :

SELECT mcats.category_set_name,

mcat.segment1 || '.' || mcat.segment2 CATEGORY, msi.segment1 item,

msi.description item_desc

FROM mtl_item_categories micat,

mtl_category_sets mcats,

mtl_categories mcat,

mtl_system_items_vl msi

WHERE mcats.category_set_name LIKE 'Inv%'

AND micat.category_set_id = mcats.category_set_id

AND micat.category_id = mcat.category_id

AND mcat.segment1 LIKE 'N%'

AND msi.inventory_item_id = micat.inventory_item_id

AND msi.organization_id = micat.organization_id

AND msi.organization_id = 204

ORDER BY 1, 2, 3

find Customer items :

find Customer items :

SELECT hp.party_name customer, ci.customer_item_number,

ci.customer_item_desc, msi.segment1 item, msi.description item_desc,

ci.customer_category_code, ci.item_definition_level,

ci.commodity_code_id, ci.address_id

FROM hz_parties hp,

hz_cust_accounts hca,

mtl_system_items msi,

mtl_customer_items ci,

mtl_customer_item_xrefs ix

WHERE ci.customer_item_id = ix.customer_item_id

AND ix.inventory_item_id = msi.inventory_item_id

AND ix.master_organization_id = msi.organization_id

AND ci.customer_id = hca.cust_account_id

AND hca.party_id = hp.party_id

ORDER BY 1, 2

find an Item attribute info :

SELECT segment1 item, msi.description, inventory_item_id,

ml.meaning item_type,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.inventory_item_status_code

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.inventory_item_status_code')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.purchasing_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.purchasing_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.shippable_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.shippable_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.mtl_transactions_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.mtl_transactions_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.so_transactions_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.so_transactions_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.internal_order_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.internal_order_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.customer_order_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.customer_order_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.purchasing_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.purchasing_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.inventory_asset_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.inventory_asset_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.eng_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) = 'mtl_system_items.eng_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.inventory_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.inventory_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name

|| '.'

|| msi.service_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.service_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.internal_order_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.internal_order_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.build_in_wip_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.build_in_wip_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.bom_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.bom_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.stock_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.stock_enabled_flag')

ATTRIBUTE

FROM fnd_lookup_values ml, mtl_system_items msi

WHERE msi.segment1 LIKE 'AS18947%'

AND msi.organization_id = 204

AND msi.item_type = ml.lookup_code(+)

AND ml.lookup_type(+) = 'ITEM_TYPE'

ORDER BY 1, 2

/