PO Receiving Account Distribution

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Receiving Account Distribution Report
Application: Purchasing
Source: Receiving Account Distribution Report (XML)
Short Name: POXRRVDR_XML
DB package: PO_POXRRVDR_XMLP_PKG
SELECT    
	1				C_dummy_break,
	rdv.po_number			po_number,
	rdv.code_combination_id		ccid,
	null			C_FLEX_ITEM,
	null 			C_FLEX_ACC,
	null			C_FLEX_CAT,
	rdv.item_id			item_id,
	rdv.transaction_id 			trx_id,
	rdv.accounting_date 		acct_date,
	round((decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) *	decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity)  *
                          rdv.source_doc_quantity / rdv.total_source_doc_quantity, :P_qty_precision)  qty,
	decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 
	 Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) *	decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity  *
                          rdv.source_doc_quantity / rdv.total_source_doc_quantity primary_qty,
	decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 
	Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) *	decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity raw_qty,
	rdv.primary_uom	 		uom,
	round(rdv.po_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) primary_unit_price,
	round(rdv.prior_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) prior_unit_price,
	round(rdv.unit_landed_cost
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) primary_landed_cost,
	rdv.line_number  			line_num,
	rdv.destination_type			dest_type,
	rdv.deliver_to_location 		deliver_to_location,
	rdv.currency_code 			currency_code,
                   NVL(rdv.currency_conversion_rate ,1)           curr_conv_rate,
                   (rdv.total_source_doc_quantity/rdv.primary_quantity) po_to_pr_uom_rate,
	rdv.receipt_number			receipt_num,
	rdv.item_revision			rev,
	rdv.category_id			cat,
	rdv.item_description			item_des,		
	NVL(raet.description, rdv.transaction_type)		transaction_type,
	rdv.organization_id			organization_id1,
	rdv.packing_slip			packing_slip,
	round(rdv.source_doc_quantity,
		:P_qty_precision)		source_doc_quantity,
	rdv.entered_dr			entered_dr,
	rdv.primary_quantity		primary_quantity,
	pov.vendor_name			vendor_name,
	pov.segment1			vendor_number,
	decode(:P_sort_id,1,rdv.primary_uom)      c_uom,
	decode(:P_sort_id,1,rdv.item_revision)  	c_rev,
	decode(:P_sort_id,1,rdv.item_description	) c_des,
	decode(:P_sort_id,1,msv.concatenated_segments,
		               2,pov.vendor_name,
		               3,rdv.receipt_number,1)  C_break_column,
	mp.organization_code		to_org,
	gsob.chart_of_accounts_id		coa_id,
	gsob.currency_code			func_currency_code,
	rdv.accounting_event_id		acc_event_id,
	rdv.event_type_id			event_type_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ACC_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_FLEX_ACC_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP,
	PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )) C_PRIMARY_UNIT_PRICE1,
	PO_POXRRVDR_XMLP_PKG.c_sort_main_dispformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_sort_main_disp, 
	PO_POXRRVDR_XMLP_PKG.c_cat_dispformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')) C_cat_disp,
	PO_POXRRVDR_XMLP_PKG.c_vendorformula(pov.vendor_name) C_vendor, 
	PO_POXRRVDR_XMLP_PKG.c_vendor_numberformula(pov.segment1) C_vendor_number, 
	PO_POXRRVDR_XMLP_PKG.display_column(rdv.deliver_to_location,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_item_loc_disp, 
	PO_POXRRVDR_XMLP_PKG.price(rdv.entered_dr,round(rdv.po_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8)) C_primary_unit_price, 
	PO_POXRRVDR_XMLP_PKG.calc_trx_value(rdv.currency_code,
	:C_PRECISION,
	rdv.event_type_id,
	PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )),
	decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 
	decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity  *
                          rdv.source_doc_quantity / rdv.total_source_doc_quantity,
	NVL(rdv.currency_conversion_rate ,1) ,
	round(rdv.prior_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8), 
    round(rdv.unit_landed_cost, 8),
    rdv.accounting_line_type) C_TRX_VALUE, 
	PO_POXRRVDR_XMLP_PKG.c_quantityformula(rdv.event_type_id,round((decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) *	decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity)  *
                          rdv.source_doc_quantity / rdv.total_source_doc_quantity, :P_qty_precision)) C_QUANTITY, 
	PO_POXRRVDR_XMLP_PKG.c_primary_unit_price2formula(rdv.event_type_id, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )),
	round(rdv.unit_landed_cost , 8),
	round(rdv.prior_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8),
    rdv.accounting_line_type,
	NVL(rdv.currency_conversion_rate ,1)) C_PRIMARY_UNIT_PRICE2,
    rdv.accounting_line_type
FROM	mtl_system_items_kfv		msv
,	rcv_receiving_acct_distr_v		rdv
,	mtl_item_categories 			mic
,	mtl_system_items			msi
,	gl_code_combinations		gcc
,	mtl_categories			mca
,	po_vendors			pov
,	mtl_parameters			mp
,	gl_sets_of_books			gsob
,   rcv_accounting_event_types  raet
WHERE   	msi.inventory_item_id           =  rdv.item_id
AND rdv.actual_flag = 'A'
AND	msi.organization_id	            =  :organization_id
AND 	rdv.item_id  is not null
AND 	mic.organization_id  = :organization_id
AND  	mic.inventory_item_id  = rdv.item_id
AND 	mic.category_set_id  = nvl(:P_cat_set_id,mic.category_set_id)
AND 	mic.category_id  = mca.category_id
AND           msv.inventory_item_id       =    rdv.item_id
AND           nvl(msv.organization_id,:organization_id) = :organization_id
AND	gcc.code_combination_id       =   rdv.code_combination_id
AND    	&P_WHERE_CAT
AND 	&P_WHERE_ITEM
AND	&P_WHERE_ACC 
AND           &P_WHERE_ACCOUNTING_DATE_FROM
AND           &P_WHERE_ACCOUNTING_DATE_TO
AND          &P_where_trx_date_from
AND          &P_Where_trx_date_to
AND   	pov.vendor_id 	          =  rdv.vendor_id
AND 	rdv.organization_id	 =   nvl(:P_org_id,rdv.organization_id)
AND 	rdv.organization_id    =  mp.organization_id
AND          &P_WHERE_VENDOR
AND          &P_WHERE_RECEIPT_NUM_FROM
AND          &P_WHERE_RECEIPT_NUM_TO
AND	rdv.set_of_books_id 	= gsob.set_of_books_id
AND rdv.event_type_id = raet.event_type_id (+)
UNION ALL
SELECT    
	1				C_dummy_break,
	rdv.po_number			po_number,
	rdv.code_combination_id		ccid,
	NULL				C_FLEX_ITEM,
	null 			C_FLEX_ACC,
	null			C_FLEX_CAT,
	rdv.item_id			item_id,
	rdv.transaction_id 			trx_id,
	rdv.accounting_date 			acct_date,
	round(decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) *
	          rdv.source_doc_quantity / rdv.total_source_doc_quantity,:P_qty_precision)  qty,
                decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) *
	          rdv.source_doc_quantity / rdv.total_source_doc_quantity  primary_qty,
	decode(rdv.entered_dr,NULL, rdv.primary_quantity* -1,
	      rdv.primary_quantity) raw_qty,
	rdv.primary_uom	 		uom,
	round(rdv.po_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) primary_unit_price,
	round(rdv.prior_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) prior_unit_price,
    NULL primary_landed_cost,
	rdv.line_number  			line_num,
	rdv.destination_type			dest_type,
	rdv.deliver_to_location 		deliver_to_location,
	rdv.currency_code 			currency_code,
                   NVL(rdv.currency_conversion_rate,1)            curr_conv_rate,
                   (rdv.total_source_doc_quantity/rdv.primary_quantity) po_to_pr_uom_rate,
	rdv.receipt_number			receipt_num,
	rdv.item_revision			rev,
	rdv.category_id			cat,
	rdv.item_description			item_des,		
	rdv.transaction_type			transaction_type,
	rdv.organization_id			organization_id1,
	rdv.packing_slip			packing_slip,
	round(rdv.source_doc_quantity,
		:P_qty_precision)		source_doc_quantity,
	rdv.entered_dr			entered_dr,
	rdv.primary_quantity			primary_quantity,
	pov.vendor_name			vendor_name,
	pov.segment1			vendor_number,
	decode(:P_sort_id,1,rdv.primary_uom)      	c_uom,
	decode(:P_sort_id,1,rdv.item_revision)  	c_rev,
	decode(:P_sort_id,1,rdv.item_description	) c_des,
	decode(:P_sort_id,1,NULL,
		               2,pov.vendor_name,
		               3,rdv.receipt_number,1)  C_break_column,
	mp.organization_code		to_org,
	gsob.chart_of_accounts_id		coa_id,
	gsob.currency_code			func_currency_code,
	rdv.accounting_event_id		acc_event_id,
	rdv.event_type_id			event_type_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ACC_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_FLEX_ACC_DESC,
	NULL                C_FLEX_ITEM_DISP,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP,
	PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )) C_PRIMARY_UNIT_PRICE1,
	PO_POXRRVDR_XMLP_PKG.c_sort_main_dispformula(NULL) C_sort_main_disp, 
	PO_POXRRVDR_XMLP_PKG.c_cat_dispformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')) C_cat_disp,
	PO_POXRRVDR_XMLP_PKG.c_vendorformula(pov.vendor_name) C_vendor, 
	PO_POXRRVDR_XMLP_PKG.c_vendor_numberformula(pov.segment1) C_vendor_number, 
	PO_POXRRVDR_XMLP_PKG.display_column(rdv.deliver_to_location, NULL) C_item_loc_disp, 
	PO_POXRRVDR_XMLP_PKG.price(rdv.entered_dr,round(rdv.po_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8)) C_primary_unit_price, 
	PO_POXRRVDR_XMLP_PKG.calc_trx_value(rdv.currency_code , 
	:C_PRECISION, 
	rdv.event_type_id, 
	PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )),
	decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) *
	          rdv.source_doc_quantity / rdv.total_source_doc_quantity,NVL(rdv.currency_conversion_rate,1),round(rdv.prior_unit_price 
                        * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8), NULL, NULL) C_TRX_VALUE, 
	PO_POXRRVDR_XMLP_PKG.c_quantityformula(rdv.event_type_id, round(decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) *
	          rdv.source_doc_quantity / rdv.total_source_doc_quantity,:P_qty_precision)) C_QUANTITY, 
	PO_POXRRVDR_XMLP_PKG.c_primary_unit_price2formula(rdv.accounting_event_id, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity ))
	, NULL, NULL, NULL, NVL(rdv.currency_conversion_rate,1) ) C_PRIMARY_UNIT_PRICE2    
    ,rdv.accounting_line_type
FROM	rcv_receiving_acct_distr_v	rdv
,	gl_code_combinations			gcc
,	mtl_categories			mca
,	po_vendors			pov
,	mtl_parameters			mp
,	gl_sets_of_books			gsob
WHERE      rdv.item_id  is Null
AND rdv.actual_flag = 'A'
AND 	:P_ITEM_FROM is NULL 
AND           :P_ITEM_TO is NULL
AND	gcc.code_combination_id = rdv.code_combination_id
AND	mca.category_id  =  rdv.category_id
AND 	exists   (select category_set_id from mtl_default_sets_view
                                                    where functional_area_id = 2
                                                    AND category_set_id = :P_cat_set_id)
AND    	&P_WHERE_CAT
AND	&P_WHERE_ACC 
AND	pov.vendor_id  =  rdv.vendor_id
AND 	rdv.organization_id  =   nvl(:P_org_id,rdv.organization_id)
AND 	rdv.organization_id  =  mp.organization_id
AND     &P_WHERE_ACCOUNTING_DATE_FROM
AND     &P_WHERE_ACCOUNTING_DATE_TO
AND     &P_where_trx_date_from
AND     &P_where_trx_date_to
AND          &p_where_vendor
AND          &P_WHERE_RECEIPT_NUM_FROM
AND          &P_WHERE_RECEIPT_NUM_TO
AND 	gsob.set_of_books_id	=	rdv.set_of_books_id
order by C_dummy_break
,	C_FLEX_ACC_DISP
,	coa_id
,	func_currency_code
,	C_break_column
,	primary_qty
,	prior_unit_price
,	event_type_id
,	acc_event_id
,	po_number
,	curr_conv_rate
,	C_FLEX_ITEM_DISP
,	receipt_num
,	acct_date
,	trx_id
,	primary_quantity
,	entered_dr
,	qty
,	raw_qty
,	deliver_to_location
,	currency_code
,	line_num
,	transaction_type
,	dest_type
Parameter NameSQL textValidation
Operating Unit
 
LOV
Title
 
Char
Sort Option
 
LOV Oracle
Organization Name
 
LOV Oracle
Date From
 
Date
Date To
 
Date
Item From
 
Char
Item to
 
Char
Category Set
 
LOV Oracle
Category From
 
Char
Category To
 
Char
Vendor From
 
LOV Oracle
Vendor To
 
LOV Oracle
Receipt Number From
 
LOV Oracle
Receipt Number To
 
LOV Oracle
Account From
 
Char
Account To
 
Char
Dynamic Precision Option
 
LOV Oracle
Download
Blitz Report™