CST Shop Floor Invoice Variance

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Shop Floor Invoice Variance Report
Application: Bills of Material
Source: Shop Floor Invoice Variance Report (XML)
Short Name: CSTRIVTW_XML
DB package: BOM_CSTRIVTW_XMLP_PKG
Run CST Shop Floor Invoice Variance and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT     
	 cavh.batch_id,
                 we.wip_entity_id,
                 we.wip_entity_name,
                 cavh.inventory_item_id,          
                 null item_name,
                 pla.item_description,
                 pha.segment1 po_number,
                 pla.line_num po_line_num,
                 pda.distribution_num po_distribution_num,
                 null po_dist_account,
                 cavh.variance_header_id source_line,
                 null adjustment_account,
                 ROUND(cavh.var_amount*1/:ROUND_UNIT)* :ROUND_UNIT header_var_amount,
                 sum(decode(wta.cost_element_id,5,0,nvl(wta.base_transaction_value,0))) applied_value,
                 sum(decode(wta.cost_element_id,5,0,nvl(wta.primary_quantity,0))) applied_quantity,
                 aia.invoice_num,
                 cavl.distribution_line_number,
                 ROUND(cavl.var_amount*1/:ROUND_UNIT)* :ROUND_UNIT line_var_amount,
                 cavh.transaction_date,
				 to_char(cavh.transaction_date,'DD-MON-YY') dsp_transaction_date,
                 ppa.segment1 project_number, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('adjustment_account_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') adjustment_account_seg, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') ITEM_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('po_dist_account_seg', 'SQLGL', 'GL#', CC2.CHART_OF_ACCOUNTS_ID, NULL, CC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') po_dist_account_seg
FROM        cst_ap_variance_headers cavh,
                 cst_ap_variance_lines cavl,
                 cst_ap_variance_batches cavb,
                 po_distributions_all pda,
                 po_line_locations_all plla,
                 po_lines_all pla,
                 po_headers_all pha,
                 pa_projects_all ppa,
                 ap_invoices_all aia,
                 mtl_system_items msi,
                 gl_code_combinations cc1,
                 gl_code_combinations cc2,
                 rcv_transactions rt,
                 wip_transactions wt,
                 wip_transaction_accounts wta,
                 wip_entities we
WHERE      cavb.batch_id = nvl(:lp_batch_id, -1)
AND           cavh.batch_id = cavb.batch_id
AND           cavl.variance_header_id = cavh.variance_header_id
AND           pda.po_distribution_id = cavh.po_distribution_id
AND           plla.line_location_id = pda.line_location_id
AND           pla.po_line_id = plla.po_line_id
AND           pha.po_header_id = plla.po_header_id
AND           ppa.project_id (+) = cavl.project_id
AND           aia.invoice_id = cavl.invoice_id
AND           msi.organization_id (+) = :p_organization_id1
AND           msi.inventory_item_id (+) = cavh.inventory_item_id
AND           cc1.code_combination_id = cavb.adjustment_account
AND           cc2.code_combination_id = pda.accrual_account_id
AND           rt.po_distribution_id (+) = cavh.po_distribution_id
AND           wt.rcv_transaction_id (+) = rt.transaction_id
AND           wta.transaction_id (+) = wt.transaction_id
AND           wta.accounting_line_type (+) = 7
AND           we.wip_entity_id = pda.wip_entity_id
GROUP BY cavh.batch_id,
                  we.wip_entity_id,
                  we.wip_entity_name,
                  cavh.inventory_item_id,          
                  &P_ITEM_SEG,
                  pla.item_description,
                  pha.segment1,
                  pla.line_num,
                  pda.distribution_num,
                  &P_ACCT_SEG2,
                  cavh.variance_header_id,
                  &P_ACCT_SEG1,
                  cavh.var_amount,
                  aia.invoice_num,
                  cavl.distribution_line_number,
                  cavl.var_amount,
                  cavh.transaction_date,
                  ppa.segment1,
				  fnd_flex_xml_publisher_apis.process_kff_combination_1('adjustment_account_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('po_dist_account_seg', 'SQLGL', 'GL#', CC2.CHART_OF_ACCOUNTS_ID, NULL, CC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
Parameter Name SQL text Validation
Batch_Date 
 
 Date
Batch Id 
 
 LOV Oracle
Download
Related Blitz Reports
CST Simulate Transfer Shop Floor Invoice Variance CAC Invoice Price Variance AP Invoices and Lines GL Account Analysis (Distributions) PO Purchase Price Variance GL Account Analysis PO Headers and Lines XXT OF: PO Headers and Lines XXSUB PO Headers and Lines
Blitz Report™

Blitz Report™ provides multiple benefits: